Design: DNS query log retention
Context
Section titled “Context”See openspec/changes/dns-query-log-retention/proposal.md, section “Why”, for the history that
the 14-day TTL removes. The requirements are in
openspec/changes/dns-query-log-retention/specs/dns-query-log-retention/spec.md.
The facts below come from the live ClickHouse and the repository on 2026-10-11:
- ClickHouse 26.7.5.10 runs as 2 replicas and 1 shard, with the server time zone UTC. The database
defaulthas the engineReplicated, so aCREATEin it reaches both replicas. default.otel_logsis aReplicatedMergeTreewithPARTITION BY toDate(Timestamp),TTL toDateTime(Timestamp) + toIntervalDay(14)andttl_only_drop_parts = 1.Timestampis aDateTime64(9). The table has 16 stored columns and noJSONcolumn.- The Query log of dns-control reads the fields
client_ip,question_name,question_type,response_typeandresponse_codefromLogAttributes(apps/dns-control/internal/querylog/querylog.go). The rollup uses the same fields. - Blocky also ships 400 to 1,200 lines a day that are not queries, under the same
ServiceName: list imports, upstream errors and rate-limiteddropped querylines. TheirLogAttributeshold journald fields and noquestion_name. This was measured from 2026-10-08 to 2026-10-11. scripts/clickstack/generate-bootstrap-ddl.shgeneratesargocd/app-configs/clickstack/bootstrap-schema-configmap.yamlfrom the seed schema of HyperDX. The file says “do not hand-edit”. A PreSync hook Job applies it.- The ClickHouse users are defined in
argocd/app-configs/clickstack-chart/values.yaml. The MCP users holdSELECT ON default.*, and adatabasesrow filter hides the DNS rows ofotel_logsfrom them. A query asmcp_readonlyon 2026-10-11 saw no DNS row, as intended. schema_adminholdsREAD ON S3and noWRITE ON S3. Thes3endpoint section of the chart values gives the B2 key ofclickhouse/to each statement whose URL starts with that prefix.- The CronJob
clickhouse-backuprunsBACKUPof eachReplicated*table indefaulttofzymgc-k8s-backups/clickhouse/. Its failure raises the HyperDX alertClickHouse backup FAILED, from a tile on the Job conditions inargocd/app-configs/clickstack-alerts/bootstrap-script.yaml. tf/backblaze/cluster_backups.tfgives each writer offzymgc-k8s-backupsa key limited to one prefix. A rule on a prefix sets the expiry of that prefix. B2 applies the smallest value of each property where two rules overlap, as pull request #2394 established.- The Vault policy
terraform-backblazeintf/vault/policy-backblaze.tfnames each path thattf/backblazewrites.main-cluster-vaultapplies a new path beforemain-cluster-backblazecan write it. - The nodes run k3s v1.36.0. CronJobs without
timeZoneuse the node zoneAmerica/New_York.
docs/reference/network.md stays the addressing authority, and this change allocates no address.
The new CronJob runs in the namespace clickstack under the existing NetworkPolicy
clickstack-default, as clickhouse-backup does. ClickHouse reaches B2 over the egress that the
backups use.
Goals / Non-Goals
Section titled “Goals / Non-Goals”Goals:
- Each DNS row is counted once in the rollup, on either replica, late rows included.
- The archive repairs itself. A missed night or a late row needs no person while the day is still in
otel_logs. - No new credential reaches a user that does not need it. The archive writer cannot write over the backups.
- No change to the schema that HyperDX owns, and no change to the Query log of dns-control.
Non-Goals:
- A rebuild of a rollup day after a fault. The view and the fill count each day once, and the design adds no delete privilege for a rebuild.
- An archive of the days that
otel_logsdropped before the first run. - A reader of the history in dns-control or HyperDX. The proposal leaves that to a later change.
Decisions
Section titled “Decisions”1. The archive is a prefix of the backup bucket
Section titled “1. The archive is a prefix of the backup bucket”The archive lives below dns-query-log/ in fzymgc-k8s-backups, as one more entry of
cluster_backup_writers in tf/backblaze/cluster_backups.tf. The entry is
clickstack-dns-archive, with the prefix dns-query-log/ and the Vault path
fzymgc-house/cluster/clickstack/dns-archive-b2. A second lifecycle rule on dns-query-log/ hides
a file 365 days after upload and deletes it one day after it is hidden. The rule on "" deletes a
hidden file after 7 days. B2 takes the smaller value, so a hidden archive file goes after one day.
B2 treats an earlier version of a rewritten file as hidden, so that rule also removes the version
that a rewrite replaces.
The B2 call b2_update_bucket replaces the whole lifecycleRules array. Each element is the wire
struct {fileNamePrefix, daysFromUploadingToHiding, daysFromHidingToDeleting}. The resource
b2_bucket.cluster_backups models every rule of the bucket in its lifecycle_rules blocks, so
Terraform writes the full array and drops no rule.
Alternative: a new bucket. It needs a new resource, two variables and its own Vault path variable.
It adds no isolation, because the key name prefix already limits the key to dns-query-log/.
The archive holds about 50 to 80 MiB a day of Parquet, so 18 to 30 GiB at the one-year horizon.
These numbers are an estimate from the ratio of otel_logs, and the first week measures them.
2. The Vault grant applies first, in its own pull request
Section titled “2. The Vault grant applies first, in its own pull request”tf/backblaze cannot write a path that terraform-backblaze does not grant. The grant goes to
main-cluster-vault in pull request 1, and the key goes to main-cluster-backblaze in pull request
2, as #2393 and #2394 did for the backup key. The proposal now lists four pull requests.
3. The rollup is a ReplicatedSummingMergeTree with a view on otel_logs
Section titled “3. The rollup is a ReplicatedSummingMergeTree with a view on otel_logs”CREATE TABLE IF NOT EXISTS default.dns_query_daily( day Date, service LowCardinality(String), client String, name String, type LowCardinality(String), result LowCardinality(String), code LowCardinality(String), queries UInt64)ENGINE = ReplicatedSummingMergeTreePARTITION BY toYYYYMM(day)ORDER BY (day, service, client, name, type, result, code);
CREATE MATERIALIZED VIEW IF NOT EXISTS default.dns_query_daily_mv TO default.dns_query_daily ASSELECT toDate(Timestamp) AS day, ServiceName AS service, LogAttributes['client_ip'] AS client, LogAttributes['question_name'] AS name, LogAttributes['question_type'] AS type, LogAttributes['response_type'] AS result, LogAttributes['response_code'] AS code, count() AS queriesFROM default.otel_logsWHERE ServiceName IN ('blocky', 'blocky-public') AND LogAttributes['question_name'] != '' AND Timestamp >= toDateTime64('<boundary>', 9)GROUP BY day, service, client, name, type, result, code;The view counts query rows only. A Blocky line that is not a query has no question_name, and it
lands in the archive but not in the rollup. Sean chose this on 2026-10-11, after the rehearsal of
pull request 3 found those lines as rollup rows with empty keys.
A materialized view runs on the replica that takes the INSERT. A part that the other replica
fetches does not run it. The target table is replicated, so both replicas hold each rollup row
once. The upstream view otel_logs_attr_kv_rollup_15m_mv runs the same way on this cluster today.
When the source table drops a retried block as a duplicate, ClickHouse inserts nothing into the
view, so a retry of the collector does not count a row twice.
The view counts a row when it arrives, by the day of its Timestamp. A late row goes to its own
day. SummingMergeTree adds up rows with the same key in the background, so a reader MUST use
sum(queries) with GROUP BY and never read one row as the count.
The view runs as its definer, schema_admin, the user of the Job that creates it.
The table has no TTL. It is in default and its engine starts with Replicated, so
clickhouse-backup holds it in each full backup without a change to the script.
Alternatives:
- A refreshable view that recounts the previous day each night. It misses each row that arrives after the refresh.
- A projection on
otel_logs. The TTL ofotel_logsdrops it with the rows. - A separate database for the rollup, which the MCP grants on
default.*do not reach. The new database needs the engineReplicatedand a change to the backup script. The row filter of decision 7 gives the same result with neither.
4. The rollup DDL has its own ConfigMap and a PostSync Job
Section titled “4. The rollup DDL has its own ConfigMap and a PostSync Job”argocd/app-configs/clickstack/dns-query-schema.yaml holds the ConfigMap
clickstack-dns-query-schema with the SQL of decision 3, and the Job clickstack-dns-query-schema.
The Job is an Argo CD PostSync hook with BeforeHookCreation. It runs clickhouse-client as
schema_admin, as bootstrap-schema-job.yaml does.
The proposal put this SQL in the bootstrap ConfigMap. That file is generated from the HyperDX seed,
so it is not a place for our SQL. A PreSync Job also runs before the sync applies a changed
ConfigMap, so a change there lands one sync late. At the merge of pull request #2005 on 2026-08-31,
the bootstrap Job completed one second before Argo CD applied the new ConfigMap, and it applied the
previous DDL with no error. The PostSync Job runs after the sync applies its
ConfigMap, and after otel_logs exists. A failure of the Job holds back no collector rollout.
Alternative: change the generator to add our SQL to its output. That keeps one Job, but each change to our SQL then needs the upstream seed, and our SQL joins the gate of the collector.
5. A fixed boundary splits the view from the one-time fill
Section titled “5. A fixed boundary splits the view from the one-time fill”The view counts each row with Timestamp at or after <boundary>. The boundary is a UTC midnight
at least one day after pull request 3 merges. The implementer sets it in the SQL before the merge,
and moves it if the merge slips past it. At least 6 hours after the boundary, an operator runs the
fill as schema_admin:
INSERT INTO default.dns_query_dailySELECT toDate(Timestamp) AS day, ServiceName AS service, LogAttributes['client_ip'] AS client, LogAttributes['question_name'] AS name, LogAttributes['question_type'] AS type, LogAttributes['response_type'] AS result, LogAttributes['response_code'] AS code, count() AS queriesFROM default.otel_logsWHERE ServiceName IN ('blocky', 'blocky-public') AND LogAttributes['question_name'] != '' AND Timestamp < toDateTime64('<boundary>', 9) AND toDate(Timestamp) >= today() - 13GROUP BY day, service, client, name, type, result, code;Each row falls on one side of the boundary, so the view and the fill count each day once. The fill
starts at the oldest day that otel_logs holds in full. The constant stays in the view and has no
effect after the boundary.
Alternatives:
- Create the view and fill the days before its first day. The day of the merge then stays short forever, by the hours before the view existed.
- Delete and rebuild the day of the merge. That needs
ALTER DELETEforschema_admin, a wider grant than this change needs.
6. The archive runs at 06:00 UTC and checks a 13-day window
Section titled “6. The archive runs at 06:00 UTC and checks a 13-day window”The CronJob dns-query-archive in clickstack has timeZone: Etc/UTC and the schedule
0 6 * * *. It is the first CronJob of the repository with timeZone, which k3s v1.36 supports.
The days of toDate(Timestamp) are UTC days, because the server runs in UTC. A UTC schedule keeps
the run 6 hours after the end of the day in both summer and winter. The run is clear of the Velero
runs at 02:00 to 04:00 UTC and of clickhouse-backup at 05:00 America/New_York.
The window runs from 13 days back to the previous day. A part of day D drops when its newest row is 14 days old, so the first part of day D drops at midnight of day D+14 at the earliest. At 06:00 on day T, day T-13 is whole and day T-14 can be partial. The run never archives a partial day.
For each day in the window, the script:
-
Counts the DNS rows of the day in
otel_logs(logged). -
Counts the rows of the file of the day with
s3_ignore_file_doesnt_exist = 1(archived). A missing file counts 0. A missing file has no schema to infer, so the count names one column,Timestamp DateTime64(9). The count of a Parquet file reads only its footer. -
If
loggedis 0, logs the day and goes on. Ifarchivedis at leastlogged, keeps the file. Ifarchivedis more thanlogged, it also logs a warning. -
Otherwise writes the file:
INSERT INTO FUNCTION s3('<base>/<yyyy>/<mm>/<dd>.parquet', 'Parquet')SELECT * FROM default.otel_logsWHERE ServiceName IN ('blocky', 'blocky-public') AND toDate(Timestamp) = '<day>'ORDER BY toStartOfFiveMinutes(Timestamp), ServiceName, TimestampSETTINGS s3_truncate_on_insert = 1, output_format_parquet_compression_method = 'zstd',output_format_parquet_row_group_size = 100000,output_format_parquet_row_group_size_bytes = 134217728, optimize_read_in_order = 1,max_block_size = 8192 -
Counts the file again, and marks the day failed if the count is lower than
logged.
The first live run on 2026-10-11 wrote with ORDER BY Timestamp and the default row group of
1,000,000 rows. Each of the 13 writes failed at the 2 GiB cap of the profile hyperdx_reader,
because a day holds fewer than 1,000,000 rows and the writer buffered the whole day. The sorting key
of otel_logs lets ClickHouse stream the rows in order with no full sort, and the small row groups
flush the writer often. A day of 810,546 rows and a day of 943,940 rows each peaked at 1.38 GiB, so
the peak does not grow with the day.
The first full day of the PowerDNS Recursor, 2026-10-16, failed at the cap again. Its 506,843 rows
average about 8.1 KB, four times the size of a Blocky row. The writer checks the byte size of a row
group only between blocks. A block of the default max_block_size of 65,409 rows held about 530 MB,
so each block became one row group of four times the target. With max_block_size = 8192, the day
peaked at 862 MiB, and a Blocky day of 454,457 rows peaked at 769 MiB. The peaks come from the same
SELECT over the HTTP interface, as schema_admin with the 2 GiB cap set on the query. The HTTP
interface encodes the Parquet file on the server, as the write to S3 does.
SELECT * returns the stored columns and leaves out each MATERIALIZED and ALIAS column. The
script logs one line for each day with logged, archived and the action. It checks every day
before it exits, and exits 1 if any day failed. The Job copies the shape of clickhouse-backup:
the image clickhouse/clickhouse-server at the pin of the kustomization, the script in a
ConfigMap, concurrencyPolicy: Forbid, backoffLimit: 1, the same security context, and
ttlSecondsAfterFinished: 86400, which the alert depends on.
The B2 upload of an INSERT INTO FUNCTION s3 completes only at the end of the write, so a failed
write leaves the earlier file or no file. It never leaves a partial file.
Alternatives:
- Write only the previous day each night. A missed night then needs a person, and a late row never reaches the archive.
- Rewrite every day of the window each night. That uploads about 700 MiB a night and makes 13 file versions a night for B2 to remove.
7. A user of its own writes the archive, and the MCP users read none of it
Section titled “7. A user of its own writes the archive, and the MCP users read none of it”The chart values gain:
- The user
dns_query_archiver, with the profilehyperdx_reader, the grantSELECT ON default.otel_logs,CREATE TEMPORARY TABLE ON *.*, andREADandWRITEonS3('^https://s3[.]us-west-000[.]backblazeb2[.]com/fzymgc-k8s-backups/dns-query-log/.*$'), and the row filterServiceName IN ('blocky', 'blocky-public')onotel_logs. Every table function needsCREATE TEMPORARY TABLE. Its password isdns_query_archiver_passwordat the Vault pathfzymgc-house/cluster/clickstack, which an operator seeds before the merge, as the runbook says fordns_control_reader_password. - The
s3endpointdns_query_archiveforhttps://s3.us-west-000.backblazeb2.com/fzymgc-k8s-backups/dns-query-log/, with the key of decision 1 from the new Secretdns-query-archive-b2. - The row filter
"0"ondns_query_dailyand ondns_query_daily_mvformcp_readonlyandmcp_readwrite, beside their filter onotel_logs. ASELECTon the view reads the target table, and ClickHouse looks up a row policy by the name of the table that a query names. The filter on the view closes that path. - The server setting
access_control_improvements.enable_read_write_grants: true.
ClickHouse grants READ and WRITE on an S3 URL pattern since 25.8. The scope matters: with an
unscoped WRITE ON S3, the archive user can write below clickhouse/ with the key of the backup
endpoint. The MCP users hold no S3 grant, so each read of the archive fails for them.
A rehearsal in a local ClickHouse 26.7.5.10 container on 2026-10-11 measured the grant:
- Without the setting, ClickHouse turns the scoped grant into
S3 ON *.*, with no scope. The live server runs without it, andSHOW GRANTSshowsGRANT S3 ON *.*forschema_admin. - With the setting, a write below
clickhouse/fails with “necessary to have the grant WRITE ON S3”. So does aclickhouse/path that holds the archive URL inside it. A write belowdns-query-log/passes the grant check. - With the setting,
READ ON S3alone still allowsBACKUP ... TO S3and thes3()reads ofclickhouse-backup.yaml. The setting takes the implicit write ofschema_adminaway, and the backup does not need it. - Against a local S3 server, the script wrote three files on its first run, kept them on its second run, and rewrote only the day that gained late rows on its third run.
schema_admin keeps its unscoped READ ON S3 and reads the archive through the new endpoint.
Alternative: run the archive as schema_admin. It holds CREATE and DROP on every database, it
has no WRITE ON S3, and the archive needs neither of the first two.
mcp_readwrite keeps INSERT ON default.*, so it can add rows to the rollup, as it can add rows to
otel_logs today. This change does not alter that grant.
8. A person reads the archive through s3() as schema_admin
Section titled “8. A person reads the archive through s3() as schema_admin”The runbook gives queries for one day, for a month with the glob <yyyy>/<mm>/*.parquet, and for
the names that one client looked up in a month. ClickHouse takes the key from the endpoint section,
so no key appears in a query, and the rows never leave the cluster before the query filters them.
If ClickHouse is down, clickhouse local on a workstation reads the same files with the key from
fzymgc-house/cluster/clickstack/dns-archive-b2. That key can also write the prefix, so the runbook
names this path as the fallback only.
9. The alert copies the backup alert
Section titled “9. The alert copies the backup alert”bootstrap-script.yaml gains the tile dns-query-archive-job-failed with the condition
Attributes['namespace'] = 'clickstack' AND Attributes['job_name'] LIKE 'dns-query-archive-%' AND Attributes['condition'] = 'true', and the tile alert DNS query archive FAILED, with the interval
and the threshold of ClickHouse backup FAILED. Its message points to the runbook section of the
archive.
Risks / Trade-offs
Section titled “Risks / Trade-offs”- [A view that fails makes each
INSERTintootel_logsfail, so all log ingest stops] → Rehearse the SQL in a scratch database before the merge. The rollback isDROP VIEW default.dns_query_daily_mv SYNCasschema_admin, which restores ingest at once. A revert of the pull request alone does not drop the view, because the Job only creates. - [The view runs as
schema_admin. If that user losesINSERTon the table or is renamed, ingest stops] → The runbook says so beside the grants ofschema_admin. - [A DNS row that arrives more than 13 days late, or after the fill, misses the archive or the rollup] → The collector delivers rows minutes late. The run logs the counts of each day.
- [A rollup row stays forever, with no way to rebuild it] → About 13.4 million rows a year, an estimated 50 to 100 MiB compressed, against 81 GiB free on each replica. The native backup holds the table.
- [The archive holds the lookups of every client for a year] → Sean decided on 2026-10-11 to keep
the raw rows with
Body. The key reaches one prefix, the files use SSE-B2, the archive user is scoped to the prefix, and the MCP users read none of it. - [Pull requests 3 and 4 change the ClickHouse configuration. A change to the users configuration restarted both replicas at #1611, and pull request 4 also changes the pod template] → Merge each one outside the backup window, and make sure that rows still arrive after the merge.
- [The setting
enable_read_write_grantschanges every S3 grant of the server, and the live server runs without it] → The only S3 grant besides the archive user is theREAD ON S3ofschema_admin. The rehearsal showed that the backup still works with it. The first backup run after pull request 4 merges proves it on the live server. - [B2 is down at 06:00] → The run fails, the alert fires, and the next night writes the missing day.
- [The
s3endpoint section gives the archive key to every user whose S3 grant covers the URL. The userdefaultholdsGRANT ALL, and it answers requests with no password from the LAN and the tailnet, as the proposalclickstack-user-lockdown(#3026) measured on 2026-10-11. Such a client can overwrite an archive file, and the lifecycle rule then deletes the earlier version after one day. The backup prefix has the same exposure throughclickhouse_backups] → Merge pull request 4 after #3026 locksdefaultto the loopback addresses of its pod. A named collection does not help, becauseGRANT ALLcovers named collections too. The other users of the server hold no S3 grant, andschema_adminholdsREADonly.
Migration Plan
Section titled “Migration Plan”Each pull request merges after the one before it applies. Rollbacks:
-
The Vault grant. Rollback:
git revert <merge-sha>in a pull request, and the nextmain-cluster-vaultrun removes the grant. -
The archive prefix. Rollback:
git revert <merge-sha>in a pull request. The nextmain-cluster-backblazerun deletes the key and the Vault path and removes the rule. The prefix holds no file yet. -
The rollups. Stop the count at once with
DROP VIEW default.dns_query_daily_mv SYNCasschema_admin, thengit revert <merge-sha>in a pull request. The table and its rows stay.DROP TABLE default.dns_query_daily SYNCdeletes the history for good, so run it only on Sean’s decision. -
The archive. Rollback:
git revert <merge-sha>in a pull request. Argo CD prunes the CronJob, and ClickHouse drops the user and the endpoint, with one more restart. The files stay until their rule expires them. To remove them earlier, Sean runs this command with a key that holdsdeleteFiles:Terminal window b2 rm --recursive --versions b2://fzymgc-k8s-backups/dns-query-log/