Skip to content
clickhouse-grafana-monitoring logo

ClickHouse & Grafana Monitoring (v2.0.0)

clickhouse-grafana-monitoring

ClickHouse analytics and Grafana dashboard configuration for Vigil Guard v2.0.0 monitoring. Use when querying logs, analyzing 3-branch detection metrics, creating dashboards, investigating events, working with n8n_logs database, managing retention policies, or monitoring branch performance (branc...

SKILL.md

Full skill instructions

ClickHouse & Grafana Monitoring (v2.0.0)

Overview

Analytics and monitoring stack for Vigil Guard v2.0.0 using ClickHouse database and Grafana dashboards for real-time 3-branch parallel detection analysis.

When to Use This Skill

  • Querying event logs from ClickHouse
  • Analyzing 3-branch detection metrics (Heuristics, Semantic, LLM Guard)
  • Creating/​modifying Grafana dashboards
  • Investigating specific threats or prompts
  • Monitoring branch performance and timing
  • Comparing arbiter decision accuracy
  • Managing data retention policies (TTL)
  • Troubleshooting logging issues

ClickHouse Schema (v2.0.0)

Database: n8n_logs

-- events_raw: Raw webhook inputs (90 days TTL)
CREATE TABLE n8n_logs.events_raw (
  timestamp DateTime64(3, 'UTC'),
  original_input String,
  session_id String
) ENGINE = MergeTree()
PARTITION BY partition_date
ORDER BY (timestamp, event_id)
TTL toDateTime(timestamp) + INTERVAL 90 DAY DELETE;

-- events_processed: Processed with 3-branch detection results (365 days TTL)
CREATE TABLE n8n_logs.events_processed (
  timestamp DateTime64(3, 'UTC'),
  original_input String,
  sanitized_output String,
  final_status String,  -- ALLOWED, SANITIZED, BLOCKED
  threat_score Float64,
  threat_labels Array(String),
  score_breakdown Map(String, Float64),

  -- v2.0.0: 3-Branch Detection Scores
  branch_a_score Float32,      -- Heuristics
  branch_b_score Float32,      -- Semantic
  branch_c_score Float32,      -- LLM Guard

  -- v2.0.0: Arbiter Decision
  arbiter_decision String,     -- ALLOW/​SANITIZE/​BLOCK
  arbiter_confidence Float32,

  -- v2.0.0: Branch Timing
  branch_a_timing_ms UInt32,
  branch_b_timing_ms UInt32,
  branch_c_timing_ms UInt32,
  total_timing_ms UInt32,

  -- v2.0.0: Degraded Branch Tracking
  branch_a_degraded UInt8,     -- 1 if timeout/​error
  branch_b_degraded UInt8,
  branch_c_degraded UInt8

) ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (timestamp, sessionId, id)
TTL toDateTime(timestamp) + INTERVAL 365 DAY DELETE;

-- retention_config: Data retention policy configuration
CREATE TABLE n8n_logs.retention_config (
  id UInt8 DEFAULT 1,
  events_raw_ttl_days UInt16 DEFAULT 90,
  events_processed_ttl_days UInt16 DEFAULT 365,
  warn_disk_usage_percent UInt8 DEFAULT 80,
  critical_disk_usage_percent UInt8 DEFAULT 90,
  last_modified_at DateTime DEFAULT now(),
  last_modified_by String DEFAULT 'system'
) ENGINE = MergeTree()
ORDER BY id;

3-Branch Detection Queries (v2.0.0)

Branch Performance Analysis

-- Average branch scores by decision
SELECT
  arbiter_decision,
  round(avg(branch_a_score), 2) as avg_heuristics,
  round(avg(branch_b_score), 2) as avg_semantic,
  round(avg(branch_c_score), 2) as avg_llm_guard,
  count() as total
FROM n8n_logs.events_processed
WHERE timestamp > now() - INTERVAL 1 DAY
GROUP BY arbiter_decision;

Branch Timing Analysis

-- Branch response times
SELECT
  round(avg(branch_a_timing_ms), 0) as avg_heuristics_ms,
  round(avg(branch_b_timing_ms), 0) as avg_semantic_ms,
  round(avg(branch_c_timing_ms), 0) as avg_llm_guard_ms,
  round(avg(total_timing_ms), 0) as avg_total_ms,
  round(percentile(branch_a_timing_ms, 0.95), 0) as p95_heuristics,
  round(percentile(branch_b_timing_ms, 0.95), 0) as p95_semantic,
  round(percentile(branch_c_timing_ms, 0.95), 0) as p95_llm_guard
FROM n8n_logs.events_processed
WHERE timestamp > now() - INTERVAL 1 HOUR;

Degraded Branch Detection

-- Count degraded branches (timeouts/​errors)
SELECT
  sum(branch_a_degraded) as heuristics_degraded,
  sum(branch_b_degraded) as semantic_degraded,
  sum(branch_c_degraded) as llm_guard_degraded,
  count() as total,
  round(sum(branch_a_degraded) / count() * 100, 2) as heuristics_pct,
  round(sum(branch_b_degraded) / count() * 100, 2) as semantic_pct,
  round(sum(branch_c_degraded) / count() * 100, 2) as llm_guard_pct
FROM n8n_logs.events_processed
WHERE timestamp > now() - INTERVAL 1 HOUR;

Primary Detector Analysis

-- Which branch triggered the most detections?
SELECT
  CASE
    WHEN branch_a_score > branch_b_score AND branch_a_score > branch_c_score THEN 'Heuristics'
    WHEN branch_b_score > branch_c_score THEN 'Semantic'
    ELSE 'LLM Guard'
  END as primary_detector,
  count() as detections,
  round(avg(threat_score), 2) as avg_final_score
FROM n8n_logs.events_processed
WHERE arbiter_decision != 'ALLOW'
  AND timestamp > now() - INTERVAL 7 DAY
GROUP BY primary_detector
ORDER BY detections DESC;

Branch Agreement Analysis

-- Cases where branches disagree
SELECT
  original_input,
  branch_a_score,
  branch_b_score,
  branch_c_score,
  arbiter_decision,
  abs(branch_a_score - branch_b_score) as ab_diff,
  abs(branch_b_score - branch_c_score) as bc_diff
FROM n8n_logs.events_processed
WHERE timestamp > now() - INTERVAL 1 DAY
  AND (
    abs(branch_a_score - branch_b_score) > 30 OR
    abs(branch_b_score - branch_c_score) > 30
  )
ORDER BY (ab_diff + bc_diff) DESC
LIMIT 20;

Common Queries

Recent Events

SELECT
  timestamp,
  original_input,
  arbiter_decision,
  branch_a_score,
  branch_b_score,
  branch_c_score,
  total_timing_ms
FROM n8n_logs.events_processed
ORDER BY timestamp DESC
LIMIT 20;

Status Distribution (Last 24h)

SELECT
  arbiter_decision,
  count() as count,
  round(count() * 100.0 / sum(count()) OVER (), 2) as percentage
FROM n8n_logs.events_processed
WHERE timestamp > now() - INTERVAL 1 DAY
GROUP BY arbiter_decision;

Score Breakdown Analysis

-- Top contributing categories (from heuristics)
SELECT
  arrayJoin(mapKeys(score_breakdown)) as category,
  count() as occurrences,
  round(avg(score_breakdown[category]), 2) as avg_score
FROM n8n_logs.events_processed
WHERE timestamp > now() - INTERVAL 7 DAY
  AND mapContains(score_breakdown, category)
GROUP BY category
ORDER BY occurrences DESC
LIMIT 15;

Search Prompts

SELECT
  timestamp,
  original_input,
  arbiter_decision,
  branch_a_score,
  branch_b_score,
  branch_c_score
FROM n8n_logs.events_processed
WHERE original_input LIKE '%SQL%'
  AND timestamp > now() - INTERVAL 7 DAY
ORDER BY timestamp DESC
LIMIT 50;

Blocked Events Analysis

SELECT
  original_input,
  branch_a_score as heuristics,
  branch_b_score as semantic,
  branch_c_score as llm_guard,
  threat_score as final_score,
  mapKeys(score_breakdown) as categories
FROM n8n_logs.events_processed
WHERE arbiter_decision = 'BLOCK'
  AND timestamp > now() - INTERVAL 7 DAY
ORDER BY threat_score DESC
LIMIT 20;

Map Type Usage

score_breakdown Field

-- Get score for specific category
SELECT
  original_input,
  score_breakdown['SQL_XSS_ATTACKS'] AS sql_score,
  score_breakdown['JAILBREAK_ATTEMPT'] AS jailbreak_score
FROM n8n_logs.events_processed
WHERE mapContains(score_breakdown, 'SQL_XSS_ATTACKS')
  AND timestamp > now() - INTERVAL 1 DAY;

-- Find events with specific category
SELECT * FROM n8n_logs.events_processed
WHERE mapContains(score_breakdown, 'SQL_XSS_ATTACKS')
  AND timestamp > now() - INTERVAL 1 DAY
LIMIT 10;

ClickHouse CLI

Access Container

# Interactive client
docker exec -it vigil-clickhouse clickhouse-client

# Single query
docker exec vigil-clickhouse clickhouse-client -q "SELECT count() FROM n8n_logs.events_processed"

# Pretty format
docker exec vigil-clickhouse clickhouse-client -q "
  SELECT
    arbiter_decision,
    branch_a_score,
    branch_b_score,
    branch_c_score
  FROM n8n_logs.events_processed
  LIMIT 5
  FORMAT Pretty
"

Connection Details

  • Host: vigil-clickhouse (internal) or localhost:8123 (HTTP)
  • Port: 8123 (HTTP), 9000 (native)
  • Database: n8n_logs
  • User: admin
  • Password: (from .env file)

Grafana Dashboards (v2.0.0)

3-Branch Detection Dashboard

Panels:

  1. Branch Score Comparison (time series) - 3 lines showing branch scores over time
  2. Arbiter Decision Distribution (pie chart) - ALLOW/​SANITIZE/​BLOCK
  3. Branch Timing Heatmap - Response times per branch
  4. Degraded Branch Alerts - Count of branch failures
  5. Primary Detector Stats - Which branch triggers most

Dashboard JSON Example

{
  "title": "Vigil Guard v2.0.0 - 3-Branch Detection",
  "panels": [
    {
      "title": "Branch Scores Over Time",
      "type": "timeseries",
      "targets": [{
        "rawSql": "SELECT timestamp, branch_a_score, branch_b_score, branch_c_score FROM n8n_logs.events_processed WHERE $__timeFilter(timestamp)"
      }]
    },
    {
      "title": "Branch Timing (ms)",
      "type": "gauge",
      "targets": [{
        "rawSql": "SELECT avg(branch_a_timing_ms) as heuristics, avg(branch_b_timing_ms) as semantic, avg(branch_c_timing_ms) as llm_guard FROM n8n_logs.events_processed WHERE $__timeFilter(timestamp)"
      }]
    }
  ]
}

Data Retention Policy

TTL Configuration

Default Retention Periods:

  • events_raw: 90 days (~1-2 GB)
  • events_processed: 365 days (~9-18 GB)
  • Total estimated: 10-20 GB/​year @ 5,000 prompts/​day

Retention Management UI

Location: Configuration → System → Data Retention URL: http://localhost/​ui/​config/​retention

Features:

  • View disk usage with color-coded thresholds
  • Edit TTL days (1-3650 range)
  • Force cleanup button (OPTIMIZE TABLE FINAL)
  • Audit trail

Force Cleanup

# Via ClickHouse CLI
docker exec vigil-clickhouse clickhouse-client -q "OPTIMIZE TABLE n8n_logs.events_raw FINAL"
docker exec vigil-clickhouse clickhouse-client -q "OPTIMIZE TABLE n8n_logs.events_processed FINAL"

# Via API
curl -X POST http://localhost:8787/​api/​retention/​cleanup \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/​json" \
  -d '{"table": "all"}'

Performance Monitoring

Branch Health Dashboard Query

-- Real-time branch health (last 5 minutes)
SELECT
  toStartOfMinute(timestamp) as minute,
  count() as requests,
  round(avg(branch_a_timing_ms), 0) as heuristics_ms,
  round(avg(branch_b_timing_ms), 0) as semantic_ms,
  round(avg(branch_c_timing_ms), 0) as llm_guard_ms,
  sum(branch_a_degraded) as heuristics_errors,
  sum(branch_b_degraded) as semantic_errors,
  sum(branch_c_degraded) as llm_guard_errors
FROM n8n_logs.events_processed
WHERE timestamp > now() - INTERVAL 5 MINUTE
GROUP BY minute
ORDER BY minute DESC;

SLA Monitoring

-- Branch SLA compliance (timeouts)
SELECT
  toStartOfHour(timestamp) as hour,
  countIf(branch_a_timing_ms <= 1000) / count() * 100 as heuristics_sla,
  countIf(branch_b_timing_ms <= 2000) / count() * 100 as semantic_sla,
  countIf(branch_c_timing_ms <= 3000) / count() * 100 as llm_guard_sla
FROM n8n_logs.events_processed
WHERE timestamp > now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour DESC;

Troubleshooting

No Branch Data

# Verify 3-branch services are running
curl http://localhost:5005/​health  # Heuristics
curl http://localhost:5006/​health  # Semantic
curl http://localhost:8000/​health  # LLM Guard

# Check if branch columns exist
docker exec vigil-clickhouse clickhouse-client -q "
  DESCRIBE TABLE n8n_logs.events_processed
" | grep branch

High Branch Degradation

# Check which branch is failing
docker exec vigil-clickhouse clickhouse-client -q "
  SELECT
    CASE
      WHEN branch_a_degraded = 1 THEN 'Heuristics'
      WHEN branch_b_degraded = 1 THEN 'Semantic'
      WHEN branch_c_degraded = 1 THEN 'LLM Guard'
      ELSE 'None'
    END as degraded_branch,
    count() as count
  FROM n8n_logs.events_processed
  WHERE timestamp > now() - INTERVAL 1 HOUR
    AND (branch_a_degraded = 1 OR branch_b_degraded = 1 OR branch_c_degraded = 1)
  GROUP BY degraded_branch
"

# Check service logs
docker logs vigil-heuristics-service --tail 50
docker logs vigil-semantic-service --tail 50
docker logs vigil-prompt-guard-api --tail 50

Connection Failed

# Test ClickHouse connection
docker exec vigil-clickhouse clickhouse-client -q "SELECT 1"

# Check credentials
grep CLICKHOUSE_ .env

Related Skills

  • n8n-vigil-workflow - Understanding 24-node pipeline and logging
  • docker-vigil-orchestration - 11 services management
  • pattern-library-manager - Understanding branch detection

References

  • Schema: services/​monitoring/​sql/​01-create-tables.sql
  • Dashboards: services/​monitoring/​grafana/​provisioning/​dashboards/
  • Retention docs: docs/​CLICKHOUSE_RETENTION.md
  • ClickHouse docs: https://clickhouse.com/docs

Version History

  • v2.0.0 (Current): 3-branch columns, arbiter decision, timing metrics
  • v1.6.11: Single-pipeline scoring, score_breakdown Map
  • v1.6.0: Added retention_config table

More skills from majiayu000

xiaohongshu logo
majiayu000/claude-arsenal

xiaohongshu

xiaohongshu

286 148
View
agent-task-conductor logo
majiayu000/claude-skill-registry

agent-task-conductor

Conduct multi-agent task orchestration and workflow coordination.

663 1
View
conductor-setup logo
majiayu000/claude-skill-registry

conductor-setup

Initialize project with Conductor artifacts (product definition,

663 1
View
animation-designer logo
majiayu000/claude-skill-registry

animation-designer

Expert in web animations, transitions, and motion design using Framer Motion and CSS

663 1
View
diagramming logo
majiayu000/claude-skill-registry

diagramming

Creates Mermaid and ASCII diagrams for flowcharts, architecture, ERDs, state machines, mindmaps, and more. Use when user mentions diagram, flowchart, mermaid, ASCII diagram, text diagram, terminal diagram, visualize, C4, mindmap, architecture diagram, sequence diagram, ERD, or needs visual docume...

663 1
View
h3-pg logo
majiayu000/claude-skill-registry-data

h3-pg

PostgreSQL bindings for H3 hexagonal grid system. Use when working with H3 cells in Postgres, including spatial indexing, geometry/geography integration, and raster analysis.

23 1
View
conductor-development logo
majiayu000/claude-skill-registry

conductor-development

Context-Driven Development skill for projects using Conductor. Use this skill when you detect a `conductor/` directory in the project, when working on tasks defined in a `plan.md` file, or when the user asks about tracks, specs, or plans. Automatically applies TDD workflow, tracks task completion...

663 1
View
conductor-status logo
majiayu000/claude-skill-registry

conductor-status

Display project status, active tracks, and next actions

663 1
View
dockerization logo
majiayu000/claude-skill-registry

dockerization

Official Stakpak application containerization standard operating procedure, a step-by-step guidline to properly dockerize applications. This is a rule book curated by the Stakpak Team.

663 1
View

Popular AI tools

Kaiber logo
Video

Kaiber

Generate, edit, and beat-sync AI video with leading models in one workspace.

Paid
View
Vimcal logo
Productivity

Vimcal

The world's fastest calendar for remote work

Free
View

Transform Your Design with AI Designer by ImgCreator.ai

Freemium
View
Akool AI logo
Content & writing

Akool AI

Revolutionizing Video Production with AI-Powered Creativity

Paid
View

Extend an image past the frame and let AI fill the new aspect ratio.

Freemium
View
StarByFace logo
Security

StarByFace

Discover your celebrity doppelgänger with StarByFace!

Free
View
C

ChainClarity explains 700+ crypto whitepapers in plain English, with layered summaries, comparisons, research tools, alerts, and a $4.99 Pro plan.

Freemium
View
Opus Clip logo
Coding & apps

Opus Clip

Opus.ai: Revolutionize Your Web Experience

Free
View