Skip to content

Design: DNS query log retention

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 default has the engine Replicated, so a CREATE in it reaches both replicas.
  • default.otel_logs is a ReplicatedMergeTree with PARTITION BY toDate(Timestamp), TTL toDateTime(Timestamp) + toIntervalDay(14) and ttl_only_drop_parts = 1. Timestamp is a DateTime64(9). The table has 16 stored columns and no JSON column.
  • The Query log of dns-control reads the fields client_ip, question_name, question_type, response_type and response_code from LogAttributes (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-limited dropped query lines. Their LogAttributes hold journald fields and no question_name. This was measured from 2026-10-08 to 2026-10-11.
  • scripts/clickstack/generate-bootstrap-ddl.sh generates argocd/app-configs/clickstack/bootstrap-schema-configmap.yaml from 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 hold SELECT ON default.*, and a databases row filter hides the DNS rows of otel_logs from them. A query as mcp_readonly on 2026-10-11 saw no DNS row, as intended.
  • schema_admin holds READ ON S3 and no WRITE ON S3. The s3 endpoint section of the chart values gives the B2 key of clickhouse/ to each statement whose URL starts with that prefix.
  • The CronJob clickhouse-backup runs BACKUP of each Replicated* table in default to fzymgc-k8s-backups/clickhouse/. Its failure raises the HyperDX alert ClickHouse backup FAILED, from a tile on the Job conditions in argocd/app-configs/clickstack-alerts/bootstrap-script.yaml.
  • tf/backblaze/cluster_backups.tf gives each writer of fzymgc-k8s-backups a 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-backblaze in tf/vault/policy-backblaze.tf names each path that tf/backblaze writes. main-cluster-vault applies a new path before main-cluster-backblaze can write it.
  • The nodes run k3s v1.36.0. CronJobs without timeZone use the node zone America/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:

  • 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_logs dropped before the first run.
  • A reader of the history in dns-control or HyperDX. The proposal leaves that to a later change.

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 = ReplicatedSummingMergeTree
PARTITION 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 AS
SELECT
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 queries
FROM default.otel_logs
WHERE 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 of otel_logs drops it with the rows.
  • A separate database for the rollup, which the MCP grants on default.* do not reach. The new database needs the engine Replicated and 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_daily
SELECT
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 queries
FROM default.otel_logs
WHERE ServiceName IN ('blocky', 'blocky-public')
AND LogAttributes['question_name'] != ''
AND Timestamp < toDateTime64('<boundary>', 9)
AND toDate(Timestamp) >= today() - 13
GROUP 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 DELETE for schema_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:

  1. Counts the DNS rows of the day in otel_logs (logged).

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

  3. If logged is 0, logs the day and goes on. If archived is at least logged, keeps the file. If archived is more than logged, it also logs a warning.

  4. Otherwise writes the file:

    INSERT INTO FUNCTION s3('<base>/<yyyy>/<mm>/<dd>.parquet', 'Parquet')
    SELECT * FROM default.otel_logs
    WHERE ServiceName IN ('blocky', 'blocky-public') AND toDate(Timestamp) = '<day>'
    ORDER BY toStartOfFiveMinutes(Timestamp), ServiceName, Timestamp
    SETTINGS 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
  5. 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 profile hyperdx_reader, the grant SELECT ON default.otel_logs, CREATE TEMPORARY TABLE ON *.*, and READ and WRITE on S3('^https://s3[.]us-west-000[.]backblazeb2[.]com/fzymgc-k8s-backups/dns-query-log/.*$'), and the row filter ServiceName IN ('blocky', 'blocky-public') on otel_logs. Every table function needs CREATE TEMPORARY TABLE. Its password is dns_query_archiver_password at the Vault path fzymgc-house/cluster/clickstack, which an operator seeds before the merge, as the runbook says for dns_control_reader_password.
  • The s3 endpoint dns_query_archive for https://s3.us-west-000.backblazeb2.com/fzymgc-k8s-backups/dns-query-log/, with the key of decision 1 from the new Secret dns-query-archive-b2.
  • The row filter "0" on dns_query_daily and on dns_query_daily_mv for mcp_readonly and mcp_readwrite, beside their filter on otel_logs. A SELECT on 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, and SHOW GRANTS shows GRANT S3 ON *.* for schema_admin.
  • With the setting, a write below clickhouse/ fails with “necessary to have the grant WRITE ON S3”. So does a clickhouse/ path that holds the archive URL inside it. A write below dns-query-log/ passes the grant check.
  • With the setting, READ ON S3 alone still allows BACKUP ... TO S3 and the s3() reads of clickhouse-backup.yaml. The setting takes the implicit write of schema_admin away, 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.

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.

  • [A view that fails makes each INSERT into otel_logs fail, so all log ingest stops] → Rehearse the SQL in a scratch database before the merge. The rollback is DROP VIEW default.dns_query_daily_mv SYNC as schema_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 loses INSERT on the table or is renamed, ingest stops] → The runbook says so beside the grants of schema_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_grants changes every S3 grant of the server, and the live server runs without it] → The only S3 grant besides the archive user is the READ ON S3 of schema_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 s3 endpoint section gives the archive key to every user whose S3 grant covers the URL. The user default holds GRANT ALL, and it answers requests with no password from the LAN and the tailnet, as the proposal clickstack-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 through clickhouse_backups] → Merge pull request 4 after #3026 locks default to the loopback addresses of its pod. A named collection does not help, because GRANT ALL covers named collections too. The other users of the server hold no S3 grant, and schema_admin holds READ only.

Each pull request merges after the one before it applies. Rollbacks:

  1. The Vault grant. Rollback: git revert <merge-sha> in a pull request, and the next main-cluster-vault run removes the grant.

  2. The archive prefix. Rollback: git revert <merge-sha> in a pull request. The next main-cluster-backblaze run deletes the key and the Vault path and removes the rule. The prefix holds no file yet.

  3. The rollups. Stop the count at once with DROP VIEW default.dns_query_daily_mv SYNC as schema_admin, then git revert <merge-sha> in a pull request. The table and its rows stay. DROP TABLE default.dns_query_daily SYNC deletes the history for good, so run it only on Sean’s decision.

  4. 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 holds deleteFiles:

    Terminal window
    b2 rm --recursive --versions b2://fzymgc-k8s-backups/dns-query-log/