Deferred deliverable 2 of #859. The clickhouse-init-schema ConfigMap only runs on an empty data dir, so schema changes don't apply to a running prod ClickHouse. Build an idempotent, versioned DDL migration mechanism — e.g. a content-hash-suffixed one-shot Job that applies CREATE/ALTER ... IF NOT EXISTS to the live server, plus a migrator NetworkPolicy — so epic #847 Units 4-5 (score_grants + materialized views) can ship schema to prod. Include the MATERIALIZE COLUMN backfill story for new MATERIALIZED columns. The approved plan (v2) on #859's lifecycle has the full design.

02Bog Flow

Closed

6/27/2026, 3:53:23 PM

No activity in this phase yet.

03Sludge Pulse

keeb commented 6/27/2026, 3:44:43 PM

Built and proven against live prod (clickhouse-v2). The clickhouse-migrations model is an idempotent, content-hash-versioned one-shot Job that applies ordered NNN-*.sql (CREATE/ALTER ... IF NOT EXISTS) to the running server at clickhouse.clickhouse.svc:9000, with a migrator NetworkPolicy and pinned to the dedicated clickhouse node pool.

Proof: first apply created swamp.schema_migrations on the live server; bumping the content hash (171cc398 -> a9f55555) spawned a new Job that applied an additive ALTER; events kept ingesting untouched. Units 4-5 just drop their DDL into a new 00N-*.sql + bump the hash.

PR: https://github.com/swamp-club/giga-swamp/pull/27 (applied by hand, not CI — by design).

keeb commented 6/27/2026, 3:46:01 PM

How the idempotent DDL migration path works

The problem it solves. The clickhouse-init-schema ConfigMap is mounted at /docker-entrypoint-initdb.d, which ClickHouse only runs on an empty data dir — i.e. first boot. With a retained PVC the data dir is never empty again, so editing that ConfigMap never re-applies. It looks like GitOps-managed schema but is a one-shot bootstrap. This path replaces it with something that applies schema to a running server.

The pieces.

Migrations are ordered NNN-*.sql files in a ConfigMap (001-init.sql, 002-..., ...).
A one-shot k8s Job mounts that ConfigMap, iterates the files in sorted order, and pipes each into clickhouse-client --host clickhouse.clickhouse.svc.cluster.local --port 9000 — i.e. against the live server, not initdb. backoffLimit retries until the server is Ready.
A migrator NetworkPolicy admits the component=migrator pod to reach the server (the default policy only admits telemetry/grafana). Pinned to the dedicated clickhouse node pool.

Why it's idempotent. Every statement is CREATE ... IF NOT EXISTS / ALTER ... ADD COLUMN IF NOT EXISTS. The Job always re-runs the whole set (001..00N), not just the newest file — so the already-applied statements are no-ops and only the new DDL takes effect. That means there's no need to track which migrations ran: the DB's current state plus the IF NOT EXISTS guards are the bookkeeping. Re-running is always safe.

The clever bit — re-run only when content changes. Kubernetes Jobs are immutable: once created you can't change the pod template, and re-applying an unchanged Job is a no-op. That's a feature (no pointless re-runs) but also a trap: if you edit the SQL but the Job keeps its name, apply reports success while nothing re-runs (a false-green). So the Job + ConfigMap names carry a content-hash suffix of the migration payload:

cat migrations/*.sql | sha256sum | cut -c1-8

Change/add a .sql → new hash → new Job/ConfigMap name → apply creates a brand-new Job → it runs → new DDL lands.
No change → same hash → same name → Job already exists → apply is a no-op → nothing re-runs.

So 'idempotent' operates at two levels: the SQL is idempotent (safe to re-run), and the naming makes apply re-run the Job exactly when — and only when — the migration content actually changed.

Backfilling new MATERIALIZED columns. MATERIALIZED columns compute at INSERT time only, so ALTER ... ADD COLUMN IF NOT EXISTS ... MATERIALIZED ... populates new rows but leaves history empty. To backfill, follow it with ALTER TABLE swamp.events MATERIALIZE COLUMN <col> — ClickHouse recomputes the column for existing parts from the in-table properties string. No archive replay needed (that's reserved for full DR).

Proven live (clickhouse-v2). First apply created swamp.schema_migrations on the running server. Bumping the hash 171cc398 -> a9f55555 (adding 002, an additive ALTER) spawned a new Job that applied just the new statement; events kept ingesting untouched. Units 4-5 = drop score_grants + the materialized views into a new 00N-*.sql, bump the hash, apply.

Improve multi-word search query handling and guidance

swamp command doesn't check cwd

swamp auth login process fails when run remotely

serve boot OOM: findAllGlobalSince loads all workflow runs as full aggregates during reconciliation

extension search: channel-filtered results carry null version fields, and multiple --channel values collapse instead of unioning

s3-datastore: localHasAllRemoteEntries uses bare rel instead of localRelPath, always returns false in namespaced mode

datastoreSetupExtension migrates .swamp/ to cache root instead of namespace directory

swamp serve writes outputs/ outside the bound namespace — silently never persisted to the remote datastore

s3-datastore: bulk pull writes to cache root instead of the namespace, wedging push and arming a full-datastore delete

git: add pull, fetch --tags, and cherry-pick methods

Workflow runs with wide dataArtifact fan-out (~400/run) OOM both workflow history get and swamp serve boot, crash-looping the scheduler; rollback doesn't help (state-triggered, not release-triggered)

Show user:<name> instead of user:<id> in grant list output

Reusable long-lived invite link: one URL to bring people into the swamp

Exclude swamp internal model types from model list output

Skill docs use invalid two-argument 'vault get' form

access token mint prints an invalid retrieval command (vault get, not vault read-secret)

serve reload and model edit timeout over --server in Kubernetes

swamp auth whoami should report paid tier and trial status

Use whoami entitlement to explain private-extension push refusals

Document --restricted-commands flag for swamp serve

vault put: pass tags through to extension provider CreateSecret/setSecret calls

Docs: update manual for 23 new --server commands

Update VaultProvider interface in @systeminit/swamp-testing to support optional tags in put()

Docs: document --grants-file flag for swamp serve

Docs: document --server support for serve reload

Docs: add --tag flag to vault put reference

Docs: update manual for --detach-runs deprecation and HA auto-detection

Document the Codex plugin in swamp docs

Add --server support to CLI commands missing it

Document the swamp Claude Code plugin in the swamp documentation

Make --server a global CLI option

Add --server support to model cancel (server-side run cancellation)

Load grants from external source on swamp serve startup

swamp vault put should support tags for secret governance

Improve hot reload of the deamon, include extension source/bundle changes

Authenticated users must re-login after swamp serve restart

Add --server support to serve reload

Harden queue claim semantics: atomic claims + status-guarded markDelivered/markFailed across app queues

Auto-detect HA mode and deprecate --detach-runs flag

version-drift check (from #236) is a raw string-inequality, false-positives on multi-model manifests and metadata-only bumps

Resend newsletter audience sync: contacts, segments, backfill, unsubscribe sync-back

Move per-login token secrets from vault to encrypted control-plane store

Docs: document macOS TLS trust limitation and SSL_CERT_FILE workaround

Validate swamp serve deployment stack on startup

Docs: serve config file (.swamp/serve.yaml) and --config flag

Serve config file — read swamp serve configuration from YAML

Deno runtime does not trust ISRG Root YR — extensions fail with valid Let's Encrypt certs

Docs: update hot-reload reference to include trust list refresh

Cross-instance client re-attach for multi-instance swamp serve

vercel codegen: inline rawSyncResult in non-unwrap sync path

vercel codegen: unused listAll import blocks extension publish for 4 models

vercel codegen: add round-trip idempotency test for the pipeline

Operatives button on Collectives page does nothing

Track active runs in the control-plane store for cross-instance visibility

validateGlobalArgInputRefs checks step-level inputs but step schema rejects 'inputs' key

Docs: document continuous reconciliation and multi-instance serve flags

Docs: update workflow guard skip output to include guardExpression and guardResult

Make it aware hot reload on the deamon does not take into account trust list changes, or do take into account the trust list for the hot reload.

workflow validate: new 'GlobalArgument input references' check resolves model-definition ${{ inputs.* }} against workflow inputs, false-failing steps that supply the input

Continuous reconciliation for multi-instance swamp serve

Split the score-read circuit breaker so leaderboard pressure can't blank profile scores

oauth_sign_in telemetry records provider as the literal ":id" for every event

Ghost profile bypasses the score-read seam and 500s on a slow ledger

Profile score section blocks the render on a ClickHouse read

Council: set the scoring balance — command tiers, farm-decay parameters, cross-category ratio

Scoring coverage gaps: instrument drop-off, score ripples/mentions, reward invite-converts

Push down the two cheap leaderboard scans: ghost-gate dictionary and eventCounts owner filter

Materialize an active-days-by-id table for the streak board

score-rollup-projector: O(all-time ledger) closure + per-pod fan-out saturates prod ClickHouse CPU

Startup cache hydration for swamp serve instance replacement

Output a log line for steps skipped due to guards

extension quality scorer misreads a template-literal import specifier in generated code as an unresolvable bare import, hard-erroring the whole extension as UNSCORABLE

Add @swamp/kubernetes/statefulset model type

Docs: access token commands reference says plaintext is 'shown once'

Document --restricted-model-types flag for swamp serve

swamp auth server-login ignores SWAMP_SERVE_URL

s3-datastore extension: pullChanged writes namespace-stripped paths to local cache

serve oauth: an unrecognized device-token error kills the whole serve process instead of the bootstrap

s3-datastore: bidirectional sync pull writes namespace-stripped copies to cache root, wedging push with no supported recovery

CLI reports 'HTTP/2 not supported by this client' when the server actually returned 401 — masks auth failures as a transport error