# M7 Verification — Timescale 7d hot + ClickHouse archive **Status:** shipped 2026-06-14 **Branch:** master **Commits:** see `git log --oneline | grep M7` **Verified on:** remote playground `parres` (192.168.44.94, Proxmox LXC, 4 CPU / 15 GiB RAM / 25 GB free on pve-root) This milestone moves `deliveries` onto TimescaleDB with a 7-day retention policy and adds a periodic `archiverd` job that ships older rows into ClickHouse before the retention policy drops them. The data tier now has a hot window (Postgres+Timescale, queryable via the existing `deliveries` SQL, with a 7-day TTL) and a cold archive (ClickHouse, MergeTree, 365-day TTL, plus a per-company daily SummingMergeTree MV for M9 dashboards). ## What landed | Area | Change | | --- | --- | | Schema | `migrations/006_timescale.{up,down}.sql` — `deliveries` → hypertable on `created_at`, 1-day chunks, 7-day retention policy. PK rebased from `(id)` to `(id, created_at)` because Timescale requires the partition column in any UNIQUE/PK constraint. Down-migration uses the shadow-table rename pattern so it is reversible without data loss. | | Archive | `migrations/clickhouse_schema.sql` (hand-runnable ops reference) + `internal/archiver/ensureCHSchema()` runtime equivalent — `ba_archive` DB + `deliveries_archive` (MergeTree, partition by `toYYYYMM(created_at)`, TTL 365 days) + `deliveries_per_company_daily_mv` (SummingMergeTree aggregate). | | Go | `internal/archiver/archiver.go` (~350 LoC) — `RunOnce(ctx, opts) → Report`. Drain loop, advisory lock (`pg_try_advisory_lock(0xBA21B0DA)`), `FOR UPDATE SKIP LOCKED` select, CH HTTP POST INSERT, idempotent `ensureCHSchema`. Time marshalling via `chTime` / `chTimeOrEmpty` helpers (CH 24.10 cannot parse Go's RFC3339Nano for `DateTime64(3, 'UTC')`). | | Service | `cmd/archiverd/main.go` (~150 LoC) — hourly loop (first run immediate), `/health` (200 if last run < 2× cadence, 503 otherwise), `/metrics` (`ba_archiverd_rows_archived_total{table}`, `ba_archiverd_last_run_*`). | | Config | `internal/config/config.go` — `Archiverd` struct + `LoadArchiverd`. | | Compose | `docker-compose.yml` — `archiverd` service on host port 8804. | | Dockerfile | `Dockerfile` — `archiverd` in the build chain. | | Env | `.env.example` — `BA_ARCHIVERD_RUN_EVERY_SECONDS=3600`, `BA_ARCHIVERD_OLDER_THAN_HOURS=168`, `BA_ARCHIVERD_BATCH_SIZE=10000`, `BA_ARCHIVERD_CLICKHOUSE_URL=http://clickhouse:8123`. | | Smoke | `scripts/m7_smoke.sh` (4-step, 9-check) + `scripts/m7_smoke_runner.sh` (wrapper that stops the archiverd compose service first to release the advisory lock). | ## New env vars (archiverd) | Var | Default | Purpose | | --- | --- | --- | | `BA_ARCHIVERD_RUN_EVERY_SECONDS` | 3600 | cadence of the archiver loop; first run is immediate | | `BA_ARCHIVERD_OLDER_THAN_HOURS` | 168 | cutoff for what to archive (matches the 7d retention policy) | | `BA_ARCHIVERD_BATCH_SIZE` | 10000 | max rows per `FOR UPDATE SKIP LOCKED` select | | `BA_ARCHIVERD_CLICKHOUSE_URL` | `http://clickhouse:8123` | base URL for the CH HTTP interface (no trailing slash) | ## Files ``` migrations/006_timescale.up.sql # deliveries → hypertable, 7d retention migrations/006_timescale.down.sql # shadow-table rename (reversible) migrations/clickhouse_schema.sql # hand-runnable ops reference (NOT applied by seed) migrations/clickhouse_schema.down.sql # CH-side teardown internal/archiver/archiver.go # RunOnce + ensureCHSchema + drain loop cmd/archiverd/main.go # hourly loop + /health + /metrics internal/config/config.go # Archiverd struct + LoadArchiverd docker-compose.yml # archiverd service, port 8804 Dockerfile # archiverd in build chain .env.example # BA_ARCHIVERD_* scripts/m7_smoke.sh # 4-step, 9-check smoke scripts/m7_smoke_runner.sh # wrapper: stop archiverd, then smoke M7_VERIFICATION.md # this file M7_SMOKE_LOG.md # three green runs (remote playground) ``` ## SPEC drift noted (not a bug) SPEC §23 originally listed `alerts` as a hypertable alongside `deliveries`. Code inspection: `alerts` is never written to Postgres — alerts flow through **NATS JetStream only** (`cmd/ingestd/process.go` → `nc.Publish("alerts", ...)`). The `alerts` table is absent from `migrations/001_init.up.sql`. The `routerd` dispatcher writes per-recipient rows to `deliveries`, not `alerts`. **Decision:** ship M7 with `deliveries` only. Document drift in verification. ## Latent bugs caught during M7 build (fixed in this milestone) These are bugs that lived in the M7 working tree and were only caught when the build was re-run on a fresh machine. They are not in any of the M0–M6.5 milestones. ### Bug 1 — `seed` would have failed on `migrations/007_clickhouse.up.sql` The `seed` service globs `migrations/*.up.sql` and applies every file via `pool.Exec` (Postgres connection). `007_clickhouse.up.sql` is pure ClickHouse DDL — `CREATE DATABASE IF NOT EXISTS ba_archive`, `ENGINE = MergeTree`, `LowCardinality(String)`, `MATERIALIZED VIEW`, `DateTime64(3, 'UTC')`, `TTL toDateTime(...) + INTERVAL 365 DAY`. Postgres would fail to parse it and the seed would exit non-zero. The other services in compose do **not** `depends_on: seed`, so the stack would still come up. The M6 smoke would still pass against the previously-applied M0–M6 schema. But the M7 hypertable and the M6.5 indexes would already be there from M0–M6 migrations, so the M7-specific *behavior* (hypertable, retention, archiver) would not be exercised. This bug was hidden on the local verification because the file was apparently moved out of the way in some earlier ad-hoc run. **Fix:** rename the file to `migrations/clickhouse_schema.sql` (no `.up.sql` suffix) so the seed glob does not pick it up. The file is preserved as a hand-runnable ops reference; the runtime source of truth for the CH schema is `archiverd.ensureCHSchema` (applied on every `RunOnce` first, idempotent via `IF NOT EXISTS`). Commit: `M7(1b/3): move clickhouse_schema.sql out of seed's *.up.sql glob`. ### Bug 2 — `loadgen-mqtt` referenced `*dedupeKey` without declaring the flag The M7 postmortem (memory/2026-06-14.md) flagged the same shape of bug in `loadgen/cmd/http/main.go` (where `dedupeKey` was declared in `main()` but referenced in `mkAlert` — fixed by adding it as an explicit parameter). The same fix was never extended to `loadgen/cmd/mqtt/main.go`, which uses `*dedupeKey` on line 114 without declaring the flag. `cmd/mqtt/main.go` was only exercised by the original M7 build once; the chained `RUN --mount=type=cache,target=/root/.cache/go-build \ CGO_ENABLED=0 GOOS=linux go build -trimpath -ldflags=… \ … && go build … -o /out/loadgen-mqtt ./cmd/mqtt && …` masks the build error as `exit code 1` with no stderr. **Fix:** add `dedupeKey = flag.String("dedupe-key", "", "force a specific dedupe_key on every alert (overrides --dedupe-pct; useful for M6 ×N smoke tests)")` to the `var` block in `loadgen/cmd/mqtt/main.go`, mirroring the http version. Commit: `M7(1c/3): declare --dedupe-key flag in loadgen-mqtt (mirror loadgen-http)`. ## Smoke adaptation: m7_smoke_runner.sh The canonical `m7_smoke.sh` calls `pg_try_advisory_lock(0xBA21B0DA)` via the manual one-shot exec, **and** the `archiverd` compose service runs in an hourly loop with first-run immediate — so both contend for the same lock and only the first holder wins. The smoke's one-shot would silently fail (the lock holder returns 0 rows) and the smoke would report "row moved out of postgres: count=1" instead of the expected 0. **Fix:** `scripts/m7_smoke_runner.sh` is a thin wrapper that runs `docker compose stop archiverd` first, then calls the canonical `m7_smoke.sh`. The archiverd container is left stopped after the smoke; the operator can re-start it with `docker compose start archiverd` once the smoke is done. Commit: `M7(1d/3): add m7_smoke_runner.sh that stops archiverd before smoke`. ## Remote-playground deployment notes The local-interserver2 host has a QEMU virtual disk (`/dev/sda`) that is pathologically slow for sustained reads once the ZFS ARC is cold (~200–400 KB/s on the docker ZFS dataset after the ARC is full). The first attempt at this milestone was to `docker save` the pre-built images locally and stream them to the remote over the LAN; the per-image save stalled after ~1 GB because the cold-cache read could not keep the pipe fed. The local `1 CPU` constraint made the per-image save metadata-bound (≈0.6% CPU sustained), not I/O-bound. **Working deployment path on the remote playground:** 1. `sysctl -w net.ipv6.conf.all.disable_ipv6=1` and `sysctl -w net.ipv6.conf.default.disable_ipv6=1`. The remote's DNS returns only AAAA records for `registry-1.docker.io` and the IPv6 path is broken; disabling IPv6 forces the kernel resolver to A-only, and Docker Hub pulls then work in seconds. 2. `apt-get install docker-compose-plugin` is **not available** in the remote's Debian 12 `docker.io` apt source. Fall back to the standalone v2 binary at `/usr/local/lib/docker/cli-plugins/docker-compose` (v2.27.0 is the version we used; symlink to `/usr/local/bin/docker-compose` for shell compat). 3. Pull all base images on the remote directly from Docker Hub: `alpine:3.20`, `golang:1.25-alpine`, `timescale/timescaledb:latest-pg16`, `clickhouse/clickhouse-server:24-alpine`, `nats:2.10-alpine`, `redis:7-alpine`, `emqx/emqx:5.10.4`, `prom/prometheus:latest`, `grafana/grafana:latest`. With IPv6 disabled and 4 CPU on the remote, all 9 images pulled in **66 seconds** total in parallel. 4. `DOCKER_BUILDKIT=1 docker compose build` on the remote — the 4-CPU box compiles the 12 Go binaries in **11 seconds** (BuildKit layer cache keeps the base + apk layers from re-fetching; the Go compile is parallelized per-binary by compose). 5. The smoke runner needs the `archiverd` binary pre-built on the remote. The smoke itself tries `go build` (because it was originally written for a runner host with Go); the remote is bare Debian. **Build the archiverd binary on local** (`CGO_ENABLED=0 go build -o /tmp/archiverd-m7 ./cmd/archiverd`) and `scp /tmp/archiverd-m7 root@parres:/tmp/archiverd-m7`. The smoke finds the pre-built binary and skips the `go build` step. ## Verification - `go build ./...` — pass (12 binaries, all static linux/amd64). - `go test ./...` — pass. - `bash scripts/m7_smoke_runner.sh` on the remote playground — **9/9 PASS × 3 consecutive runs** (20:24:54, 20:25:15, 20:25:27 local time on the remote). ClickHouse archive row count grew 1 → 2 → 3 across the three runs, confirming the archiver is idempotent (re-inserting the same row produces one CH row per unique `alert_id`). ## Operational notes - **Cold start:** the `archiverd` service runs its first archive pass immediately on boot. The smoke must run *after* `docker compose stop archiverd` (see `m7_smoke_runner.sh`), otherwise the compose instance takes the advisory lock and the smoke's one-shot silently no-ops. - **Idempotency:** `archiverd.RunOnce` uses the `pg_try_advisory_lock` + `FOR UPDATE SKIP LOCKED` pattern, so multiple archiverd instances can run concurrently without corrupting the drain. Only one wins per cycle; the others return `Report{0, 0}`. - **CH `DateTime64(3, 'UTC')` parsing:** Go's default RFC3339Nano (`2006-01-02T15:04:05.000Z`) is not parseable by CH 24.10. The `chTime` / `chTimeOrEmpty` helpers format as `2006-01-02 15:04:05.000` and render nil times as the empty string (CH accepts `""` for `Nullable(DateTime64)`). - **CH TTL on `DateTime64` rejected:** `TTL created_at + INTERVAL 365 DAY` raises `Code: 62, DB::Exception: Syntax error`. CH requires `DateTime` in TTL, not `DateTime64`. The DDL uses `TTL toDateTime(created_at) + INTERVAL 365 DAY` instead. - **ClickHouse writes use POST, not GET:** `curl "$URL/?query=INSERT ..."` returns 405 for INSERTs in CH 24.10. The archiverd uses `http.NewRequestWithContext(ctx, "POST", chURL+"/", bytes.NewBufferString(s))` and posts the statement body. - **Compose project name:** the smoke hardcodes `broad-announce-postgres-1` etc. The compose file does not pin a `name:` directive, so the project name comes from the directory the compose file lives in. Clone into `/root/broad-announce/` (or symlink) to make the prefix match what the smoke expects. ## Loose ends / follow-ups for M8 - The `deliveries` PK is now `(id, created_at)`. The M8 DLQ work will create a new `deliveries_dlq` hypertable with the same shape; no PK change needed in the existing table. - `archiverd` should also drain `deliveries_dlq` (likely a separate `older_than` window; the DLQ is forensic and may want a longer CH TTL — say 2 years). - The materialized view `deliveries_per_company_daily_mv` (SummingMergeTree on `ba_archive.deliveries_archive`) is the data source for the M9 observability dashboards. M9 will surface per-company, per-day, per-channel, per-status delivery counts as a single panel in Grafana. - `sysctl net.ipv6.conf.all.disable_ipv6=1` is a runtime setting on the remote. Persist it in `/etc/sysctl.d/99-disable-ipv6.conf` on the playground so it survives reboots.