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...

mattnigh/skills_collection0installs24stars

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 mattnigh

spring-framework-patterns logo
mattnigh/skills_collection

spring-framework-patterns

Comprehensive Spring Framework and Spring Boot best practices including dependency injection patterns, bean lifecycle and scopes, REST API development, Spring Data JPA, service layer design, Spring Security, testing strategies, caching, AOP, async processing, error handling, and common anti-patte...

24 0
View

TypeScript厳格モードによる型安全性設計を専門とするスキル。 📖 参照書籍: - 『Effective TypeScript』(Dan Vanderkam): 型設計 📚 リソース参照: - `resources/Level1_basics.md`: レベル1の基礎ガイド - `resources/Level2_intermediate.md`: レベル2の実務ガイド - `resources/Level3_advanced.md`: レベル3の応用ガイド - `resources/Level4_expert.md`: レベル4の専門ガイド - `resources/di...

24 0
View
deploying-cloud-k8s logo
mattnigh/skills_collection

deploying-cloud-k8s

Deploys applications to cloud Kubernetes (AKS/GKE/DOKS) with CI/CD pipelines. Use when deploying to production, setting up GitHub Actions, troubleshooting deployments. Covers build-time vs runtime vars, architecture matching, and battle-tested debugging.

24 0
View
moai-alfred-agent-guide logo
mattnigh/skills_collection

moai-alfred-agent-guide

19-agent team structure, decision trees for agent selection, Haiku vs Sonnet model selection, and agent collaboration principles. Use when deciding which sub-agent to invoke, understanding team responsibilities, or learning multi-agent orchestration.

24 0
View
mermaid-diagram-generator logo
mattnigh/skills_collection

mermaid-diagram-generator

Converts architecture descriptions, module specs, or workflow docs into Mermaid diagrams. Use when visualizing brick module relationships, workflows (DDD, investigation), or system architecture. Supports: flowcharts, sequence diagrams, class diagrams, state machines, entity relationship diagrams,...

24 0
View
pre-flight-check logo
mattnigh/skills_collection

pre-flight-check

INVOKE FIRST before any code work. Validates git workflow (branch, issue, worklog) and checks approach. Use at START of every task and END before completing. Prevents skipped steps.

24 0
View
browser-dev-tools logo
mattnigh/skills_collection

browser-dev-tools

This skill should be used when working with browser-rendered artifacts (i.e. Bun, React/React Native) to proactively validate that development work on localhost is being built appropriately and to support debugging browser-rendered content. Use this skill after making frontend changes to verify t...

24 0
View
lead-dev logo
mattnigh/skills_collection

lead-dev

Lead Développeur - Coordination technique opérationnelle, code review, mentoring et livraison. Pair de web-dev-process au niveau OPÉRATIONS.

24 0
View
worker-monitor logo
mattnigh/skills_collection

worker-monitor

Monitor Docker workers, RQ queue health, and auto-scale workers (max 4). Use when: checking job progress, monitoring queue depth, scaling workers up/down, diagnosing slow processing, or waiting for jobs to complete. Referenced by data-quality skill during reprocessing.

24 0
View
quality-advisor logo
mattnigh/skills_collection

quality-advisor

Proactive quality guidance system that monitors artifact creation and provides real-time feedback on documentation quality

24 0
View
bump-version logo
mattnigh/skills_collection

bump-version

This skill should be used when the user wants to bump the version number in the workspace. It updates versions across all pyproject.toml files (root, tde, and tda packages) and the CHANGELOG.md to keep them synchronized.

24 0
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