skills/vibe-clickhouse/references/clickhouse-audit.md

# ClickHouse Audit

## Operation

Inspect the named boundary and report supported findings. Do not edit product code. Include concrete evidence, impact, the owning source, one remediation direction and a meaningful validation route. Severity follows actual impact, not a category example.

## Goal and scope

Audit ClickHouse table engines, partition/order keys, ingestion/deduplication, query pruning, replication/mutations, and workload limits as one analytics contract.

## Domain invariants

- Table engine, partition key, ORDER BY/primary key, sampling, TTL, codecs, and schema match dominant filters, data lifecycle, update model, and storage scale.
- Partition cardinality stays bounded and does not substitute for the ORDER BY key; queries can prune parts/granules using the actual predicates.
- Ingestion batch/async-insert, retries, dedup token/window, block identity, Distributed routing, and source offset define an explicit duplicate/loss contract.
- Replacing/Collapsing/Aggregating engines and materialized views encode their eventual merge semantics; users do not assume immediate uniqueness or final state without appropriate query logic.
- Queries bound scanned rows/bytes, joins, aggregation state, sorting, concurrency, timeout, memory, spill, and result size under representative cardinality/skew.
- Replication, quorum expectations, replica lag, Distributed tables, merges, part counts, disk, mutations, and failed inserts are observable and recoverable.
- Schema evolution/backfill/materialized-view changes account for existing parts, mixed writers/readers, mutation cost, and rollback/forward-fix.
- Connection pools, users/roles, quotas, settings profiles, workload scheduling, and read-only/tenant boundaries are deployment-wide and least privilege.

## Audit method

1. Map source batch/offset → insert path → local/distributed table → parts/replicas/views → query/user/settings → export/consumer.
2. Inspect table DDL and representative `EXPLAIN`/query-log/system-table evidence for pruning, rows/bytes, memory, parts, merges, and latency.
3. Trace retry/duplicate windows, partial insert, async insert acknowledgement, replica failure, Distributed queue, and source checkpoint boundaries.
4. Evaluate partition/key choices and materialized-view semantics against real filters, time ranges, tenant skew, late data, and update/delete requirements.
5. Calculate concurrent connections/queries/ingestion, quotas, memory, disk, merge bandwidth, and mutation/backfill pressure.

## Priority model

- **P0:** data loss or duplication in decision-critical pipelines, cluster overload, cross-tenant exposure, or unrecoverable schema/replication failure.
- **P1:** a material ingestion, partitioning, merge, query-load, distributed-table, or retention defect.
- **P2:** a lower-risk but concrete efficiency, observability, or maintainability issue.