The Data Lake: Postgres Hot Store, ClickHouse History

TL;DR: Postgres is the hot store: the console, the gateway and the jobs read and write it, and it keeps a short hot window of each kind of history. ClickHouse is the lake: every 15 minutes the clickhouse_sync job copies new rows into it, every night clickhouse_purge checks each day matches in both stores, and only then deletes Postgres rows older than the hot window. The lake keeps rows for 3650 days. Administrators read the lake on the Data lake page (/admin/system/lake) and, from Claude Code, through the read-only clickhouse MCP server.

Why two stores

Postgres serves the live product. It has to answer the console and the gateway quickly, and every gigabyte of old history makes its backups, vacuums and restores slower. On 2026-11-14 the production database was 103 GB, of which conversation transcripts (ai_request_messages) were 77 GB and raw request bodies (ai_request_payloads) 13 GB.

ClickHouse is built for the other job: scanning months of rows to count, sum and group them. It compresses history heavily and does not slow the live product down when someone asks for a year of cost by model.

So the split is: recent rows in Postgres, for the product; all rows in ClickHouse, for history. On 2026-11-14 the lake held 41.3 million transcript rows (41,265,799), the oldest from 2026-10-12, with identical counts in both stores.

What lives where

Dataset	Postgres source table	Hot window in Postgres	In the lake	Who deletes it from Postgres
ai_requests	ai_requests (gateway requests, tokens, cost)	60 days	Purged after export	clickhouse_purge, never ahead of its transcripts
ai_request_messages	ai_request_messages (transcripts)	14 days	Purged after export	clickhouse_purge
governance_decisions	governance_decisions	30 days	Purged after export	clickhouse_purge
mcp_tool_executions	mcp_tool_executions (and, by cascade, their artifacts)	120 days	Purged after export	clickhouse_purge
logs	logs	14 days	Purged after export	clickhouse_purge
agent_runs	agent_runs	Kept	Export-only	Nobody: the console and the engine read runs as live state
workflow_runs	workflow_runs	Kept	Export-only	Nobody, as above
workflow_steps	workflow_steps	Kept	Export-only	Nobody, as above
plugin_usage_events	plugin_usage_events (hook events, skill invocations)	90 days	Export-only	plugin_usage_retention, by age
analytics_events	analytics_events (traffic and behaviour events)	30 days	Export-only	Core's database_cleanup, by age

Purged after export means the export job itself deletes the Postgres row, and only once the lake has verified it. Export-only means the export copies rows and never deletes them; whatever deleted them before still does, on its own schedule. For the two event datasets the lake is the only copy once that age-based job has run.

Not in the lake: raw request and response bodies (ai_request_payloads, emptied after 7 days), the tool calls, scopes, attributions and safety findings attached to a request (they go with their request), and the daily rollups (kept in Postgres indefinitely).

The hot windows are set in services/clickhouse/config.yaml (hot_window_days, minimum 14). The lake's own expiry is ttl_days: 3650 in the same file: in practice the lake deletes nothing by itself, and deleting from it is a deliberate act.

The life of a row

1. Written. The gateway, the governance chain or a job writes the row to Postgres. The console sees it at once.
2. Exported, within about 15 minutes. clickhouse_sync runs every 15 minutes. Each dataset stays a little behind the present (10 minutes, or 60 for requests, transcripts and tool executions) so a row still being completed is not copied half-done. The job moves its cursor only after ClickHouse acknowledges the batch, so a failed insert is simply retried on the next run.
3. Verified, per day. At 03:30 UTC clickhouse_purge compares each whole UTC day in both stores: the row count and a checksum over the row ids. A day that matches moves the dataset's verified_through forward. The first day that does not match stops it there.
4. Purged after the hot window. In the same run, Postgres rows are deleted only if they are both older than the hot window and before verified_through. If ClickHouse cannot be reached, or a day does not match, nothing is deleted for that dataset and the run fails. Transcripts are purged before requests, because deleting a request also deletes its messages.
5. Kept in the lake. The row stays in ClickHouse, with all its typed columns and the whole original row as JSON in raw.

Core's own database_cleanup (04:00 UTC) still deletes some of these tables by age, as a backstop. Its windows are set at least 14 days longer than the hot windows, so while the export keeps up the verified purge is always the one that deletes. A build gate fails if that stops being true. The full order of the nightly deletion jobs is on Data Retention & Archives.

The Data lake page

Admin console → Platform → Data lake (/admin/system/lake). The page has two halves that fail independently:

• The analytics panels read ClickHouse as the read-only reader user, so the page can never change the lake. They cover the window chosen at the top of the page: 7, 30 (the default), 90 or 365 days.
• The status panels read the export's own records in Postgres (clickhouse_export_state and clickhouse_export_runs). They always show the current state, whatever window is chosen.

Panel	What it shows	How to read it
KPI tiles	Requests, cost, tokens, peak daily users and errors over the window	The same measures as the usage dashboard, for any window up to a year, not only the Postgres hot window
Requests per day, Cost per day	One line chart each, per UTC day	A drop on the most recent day or two usually means the export is behind, not that usage stopped: check Export status
Cost by model	A pie of cost share and a table of requests, cost and p95 latency per model, top 12 by cost	unrouted is a request that never reached a model
Governance decisions per day	allow, warn and deny counts per day	While governance runs in warn mode, warn is what enforce mode would have refused
Most denied	The policies and tools with the most deny decisions, top 10	Start here when someone reports being blocked
Tool executions	Calls, failures and p95 execution time per MCP tool, top 15 by calls	Failures count failed and timeout
Top users by cost	Requests and cost per user, top 10	Users are shown by id
Lake inventory	Rows and bytes per lake table	Growth here is expected; the lake keeps everything
Export status	One row per dataset: status, last sync, last verify, exported through, verified through, purged through, hot window, lag, rows exported and the last error	The health of the export itself; see below
Recent runs	The last 20 sync, verify and purge runs: dataset, phase, rows, the window covered and the outcome	The per-run detail behind a failing dataset; a failed run carries its error

If the reader secrets are not set, the page says ClickHouse reader is not configured and leaves out the analytics panels; the status panels still render. If ClickHouse is down, or one query takes longer than 5 seconds, that panel shows a fault notice and the rest of the page renders. Every load queries ClickHouse; nothing is cached. This is deliberate: when the lake is unreachable, the status panels are what you need.

Reading the export status

Each dataset row has one of three states:

State	Tone	Means
healthy	green	The last sync ran within 30 minutes and the last verify within 36 hours, with no error
stale	amber	No sync for more than 30 minutes, or no verify for more than 36 hours, or the dataset has never run. The last sync or last verify cell that is late is marked too
failing	red	The export reports an error (last_error), shown in the row. Nothing past the failure has been deleted

Lag is how far exported_through is behind now. It is normally under the dataset's built-in delay plus 15 minutes: about 25 minutes for most datasets, about 75 for requests, transcripts and tool executions.

Export-only datasets show export only as their hot window and no purged through; that is normal.

The retention jobs watch the same records. The daily retention_daily_report (as warnings) and the monthly health check (as P1 findings) report a dataset with an error, an export more than 2 hours behind its built-in delay, and a verify older than 36 hours, so a stalled export also shows up in the next morning's job run.

Querying the lake from Claude Code

The clickhouse MCP server gives read-only SQL over the lake.

Who can use it. Administrators only: the admin, platform_admin and super_admin roles (the mcp_server/clickhouse entry in services/access-control/rules.yaml), and the call must carry the admin scope. Anyone else is refused. These are the same roles that can already read every user's conversations in the console, so the lake does not widen access.

Connecting. Like every MCP server here, it is reached through the gateway at https://<your instance>/api/v1/mcp/clickhouse/mcp, with a short-lived gateway token exchanged from your personal access token. The exchange is shown in Agent Runs from Claude Code; use clickhouse in place of systemprompt in the server name and URL.

The four tools

Tool	Does
usage_summary	Requests, cost, tokens and errors over the last days (1 to 365, default 30), grouped by day, model, user or tool. Runs the same fixed queries as the Data lake page. Start here.
list_tables	The lake's tables, with engine, row count and size
describe_table	The columns and types of one table
query	One SELECT (or WITH … SELECT) that you, or Claude, write

Three things to know when writing SQL for query:

• Read every table as FROM <table> FINAL. The tables are ReplacingMergeTree, and a re-exported row can appear twice until ClickHouse merges it; FINAL collapses the duplicates.
• Every table has a ts column (DateTime64, UTC) to filter and group on. It is the row's creation time (for tool executions, the start time; for runs, the finish time).
• Money is cost_microdollars (Int64); divide by 1e6 for dollars. Do not give an aggregate the same alias as its column (sum(cost_microdollars) AS cost_microdollars): ClickHouse reads that as a cyclic alias.

Worked examples

You can ask Claude in plain words; these are the calls that answer each question.

"What did we spend per day over the last quarter?"

{"tool": "usage_summary", "arguments": {"days": 90, "group_by": "day"}}

"Which models cost the most this year?"

{"tool": "usage_summary", "arguments": {"days": 365, "group_by": "model"}}

"How has one person's spend moved month by month?"

SELECT toStartOfMonth(ts) AS month,
       count() AS requests,
       round(sum(cost_microdollars) / 1e6, 2) AS cost_usd
FROM ai_requests FINAL
WHERE user_id = '<user id>' AND ts >= now() - INTERVAL 365 DAY
GROUP BY month
ORDER BY month

"Which policies would have blocked the most tool calls each week since the summer?"

SELECT toStartOfWeek(ts) AS week, policy, count() AS decisions
FROM governance_decisions FINAL
WHERE decision = 'deny' AND ts >= now() - INTERVAL 180 DAY
GROUP BY week, policy
ORDER BY week, decisions DESC

While governance runs in warn mode, replace 'deny' with 'warn' to see what enforce mode would have refused.

"Which skills are used most, and by how many people?"

SELECT skill_ref, count() AS invocations, uniqExact(user_id) AS people
FROM plugin_usage_events FINAL
WHERE skill_ref IS NOT NULL AND skill_dedup_of IS NULL
  AND ts >= now() - INTERVAL 180 DAY
GROUP BY skill_ref
ORDER BY invocations DESC
LIMIT 20

skill_dedup_of IS NULL leaves out the duplicate record of an invocation already counted.

"How large is this old conversation before I open it?"

SELECT count() AS messages, sum(content_bytes) AS body_bytes
FROM ai_request_messages FINAL
WHERE request_id = '<ai_requests.id>'

ai_request_messages.request_id joins to ai_requests.id (not to ai_requests.request_id, which is the gateway's own request id).

Limits

• Read-only, three times over. The reader user astound_reader has SELECT on the lake database only and a read-only settings profile; the server sends caps with every request; and a statement guard refuses anything that is not a single SELECT, plus SETTINGS, FORMAT, INTO OUTFILE and table functions that reach outside the lake.
• Capped results. By default a query returns at most 1,000 rows and 8 MiB, and runs for at most 30 seconds (services/clickhouse/mcp.yaml). A query that passes a cap fails rather than returning a partial answer. Aggregate in SQL instead of pulling rows.
• History only. Rows reach the lake up to about 75 minutes after they are written. For the last hour, use the console.
• Not every column is typed. Anything not in a typed column is in raw, the original row as JSON. Use describe_table first.

Aggregate; do not pull bodies

Every tool result is itself recorded: core stores the output of each MCP call in mcp_tool_executions, and that table is exported to the lake. A query that returns message bodies (ai_request_messages.content) or raw columns copies them into the tool-call audit trail, readable by every administrator. Ask for counts, sums and groupings. Read one conversation's bodies only when you need that conversation, and size it first (the last example above).

When a status row turns amber or red

The status row names the dataset and what is late or failing. In plain terms:

What the row shows	What it means	What to do
failing, with an error	The last sync, verify or purge for this dataset failed. Nothing past the failure was deleted.	Read the error in Recent runs. A connection error means ClickHouse is down or its credentials changed; once that is fixed, the next 15-minute run catches up on its own.
stale, last sync late	clickhouse_sync has not run for more than 30 minutes.	Check the job's history (systemprompt infra jobs history). After an outage the backlog drains over a few runs with no action; the operator can drain a large backlog faster by running the job back to back.
stale, last verify late	clickhouse_purge has not verified for more than 36 hours. Nothing past the hot window is being deleted, so Postgres grows.	If the purge reports a verify mismatch, it names the day and both counts. If Postgres has rows the lake lacks, the operator re-syncs that dataset from the day before. If the lake has rows Postgres lacks (something else deleted them), the operator checks and moves the verified bound past that day by hand.
Lag climbing, row still green	The sync runs but is not keeping up. Past 2 hours the retention jobs report it.	Usually a large backlog after an outage or the first export of a dataset; watch it fall over the next runs.

A failing or stale row never means data was lost: the purge deletes nothing it has not verified. The risk is the backstop. If the export stays stalled for 14 days or more, core's age-based cleanup starts deleting rows the lake does not hold. Treat it as something to fix within days, not weeks.

The step-by-step procedures (re-sync, fixing a verify mismatch, disabling the purge, the production purge gate just gcp-purge-gate) are in the repository's docs/runbooks/clickhouse-export.md. Setting up the reader user in production (just gcp-ch-reader) is in docs/runbooks/clickhouse-mcp.md.