Lab #859: Decouple prod ClickHouse from S3 (drop storage_policy=s3

Part of epic #847. Sibling of #853 (Unit 1 — land the local ClickHouse foundation). This issue tracks the prod changes that #853 deliberately does not make.

Problem

Prod ClickHouse (giga-swamp models/@giga-swamp/k8s-manifest/clickhouse.yaml) couples the serving engine to S3 in a way that isn't paying off at our scale, and leaves the schema un-migratable:

1. S3-coupled storage (storage_policy = 's3_main'). CH's own MergeTree parts live in a DO Space (clickhouse-data-swamp-club), fusing two concerns that should be independent failure domains:

serving — hot, latency-sensitive, disposable
durable log — the ndjson archive (events-archive-swamp-club), the real system of record

Consequences: an S3 hiccup degrades queries, not just ingestion; the MergeTree merge engine pays continuous S3 request/egress cost (GET/PUT/DELETE on every merge, insert, and TTL drop — forever, proportional to ingest, independent of query load), plus range-GETs on every cache miss; and it obscures the real DR story ("CH rebuilds from S3" means replaying the ndjson archive, not the s3_disk parts). The dataset is small (~4M LowCardinality rows, a few hundred MB compressed) and TTL-capped at 2 years, so the auto-tiering benefit an s3 storage policy exists for never triggers. The retained PVC + the archive already provide durability — the s3 storage policy is pure tax. S3 should be break-glass only: append one PUT/batch to the archive, read it back solely on a rare rebuild.

2. Schema is a one-shot ConfigMap with no migration path. The init schema is a ConfigMap (clickhouse-init-schema) mounted at /docker-entrypoint-initdb.d, which ClickHouse runs only on an empty data dir. With the retained clickhouse-data PVC, the data dir is non-empty after first boot, so editing the ConfigMap does not re-apply to the running pod. It looks like GitOps-managed schema but is a one-time bootstrap. Epic #847 Units 4–5 (score_grants + materialized views) add DDL that must land in prod, and there is currently no tracked mechanism to apply it. Related: CH MATERIALIZED columns compute at INSERT time only and never backfill, so new dimensions over history require re-ingesting raw properties from the archive.

(Also: the manifest's top comment still claims "emptyDir, no PVC, schema runs on every restart" — stale and contradictory with the shipped retained-PVC spec.)

Proposed solution

Drop storage_policy = 's3_main' — serve from a plain local-disk MergeTree on the existing retained clickhouse-data PVC. Remove the now-unused s3-storage.xml fragment / clickhouse-s3-config ConfigMap / clickhouse-s3-creds env wiring if nothing else needs them. DR path becomes: lose the volume → replay from the events-archive-swamp-club ndjson lake (backfill.sql).
Establish a real DDL migration path so Units 4–5 can ship schema changes to a running prod CH (the ConfigMap edit won't): e.g. a versioned-migration Job applied on deploy, or an idempotent CREATE/ALTER … IF NOT EXISTS apply step that runs against the live server instead of relying on initdb-on-empty-dir. Include a backfill story for new MATERIALIZED columns (re-ingest from the archive).
Fix the stale "emptyDir / no PVC" comment in the manifest.

Alternatives considered

Keep s3_main as a cold tier — only worth it if the dataset outgrows local-disk economics; at current scale + 2-year TTL it won't for years, and you'd still need the archive for DR, so it's redundant.
Bundle this into #853 — rejected. #853 is the local-dev foundation (acceptance criteria are all local), lives in swamp-club2, and prod CH already runs. This is a different repo (giga-swamp) and a different blast radius.

Acceptance

Prod CH serves from local disk; storage_policy = 's3_main' and the S3 disk config are gone (or justified if retained).
A repeatable, idempotent mechanism applies schema/DDL changes to a running prod CH (verified by applying a no-op/additive change).
DR is documented as archive-replay; S3 touched only on append + rebuild.
Stale manifest comment corrected.

Awards exceed the 10,000,000 cap once campaign and streak multipliers apply

Award grants (showcase approval, admin award points) skip campaign/streak multipliers

Disable the inbox notification email ("You have received a notification in Swamp Club")

Add LinkedIn as a login provider

Showcase reward backfill can double-pay 5,000,000 points when replicas start together

Showcase editor: caption counter is not announced to screen readers

Profile showcase read: approved list sorts on review.reviewedAt in memory

Showcase countByStatus: $match live statuses before the $group

Showcase admin queues: index-serve the updatedAt sort on approved/rejected

Block temporary email services on signup

auth login leaves auth.json apiKey empty and stores the key only in ~/.config/swamp/api-key, which the auth gate never reads

swamp-club.com serves every response uncompressed

CLI companion to #3396: private-source collectives can score repository-verified 14/14 in extension quality

Replayable state fragments: Git-reviewable desired state for resources changed by model methods

Use whoami entitlement to explain private-extension push refusals

Top-level swamp init never earns repo-init quest or tier credit

Docs: telemetry reference — the envelope we send and our redaction policy

Telemetry opt-out scope: one user-level setting that disables telemetry everywhere

Telemetry: mark paths where errors are built so redaction needs no guessing

Combat log: batch the extension visibility lookup into one query

CLI telemetry redaction: user values still transmitted (errorMessage, workflowContext, init path, history args, private types) while command words are over-redacted

Maximum call stack size exceeded once a resource passes ~129k versions (Math.max(...versions) in the data repository)

worker connect --ca-cert is not applied to the data plane; dispatches fail with 'fetch failed' unless DENO_CERT is set

Ephemeral worker fleets accumulate worker records and token bindings that are never reaped

Expired enrollment token cannot be re-minted unless a worker was connected when it expired

Billing telemetry: surface scheduled cancellations (cancelAtPeriodEnd) and emit subscription_canceled reliably

access token mint: support --server so worker server tokens can be minted remotely

Workload identity (Kubernetes ServiceAccount / OIDC) for worker enrollment

Feed grant projection stamps a site route that does not exist

Retire or rebuild /admin/nurture once the epic lands — it is a gate, and gates are single-use

feat(nurture): /admin/nurture — who would get what, and why

feat(nurture): the nurture_send ledger and the dry-run projector, with the cold-start seal

feat(nurture): batch cohort reads — account-grain identification that carries its evidence

feat(nurture): the flow catalog and the qualification contract (pure domain)

harness-marketing-funnel posts {batch:[...]} to /ingest, which is not a batch — every run silently measures zero

feat(cli): swamp inbox — read swamp-club notifications without leaving the terminal

feat(cli): tell operatives they have swamp-club notifications waiting

feat(api): a cheap unread-notification count for API-key clients

feat(nurture): deliver to the swamp-club inbox on the same dedupe decision as email

feat(nurture): arm needs-help-after-a-failed-run (NEEDS HELP)

feat(nurture): arm model-usage-without-workflows (BUILDING PRODUCT FEATURES)

feat(nurture): arm no-activation-after-30-days (ARRIVED, NEVER RAN)

feat(nurture): arm reactivation-after-7-days (DRIFTED)

feat(nurture): arm the first flow — extension published, email only, verified in prod

feat(nurture): the six nurture email templates, previewed before anything sends

feat(nurture): an outbound transport that reports its outcome, and per-channel send status

feat(nurture): suppression — opt-out, address-grain frequency caps, one-click unsubscribe

Epic: Product nurture — lifecycle identification, dedupe-safe delivery, and operator control (email, inbox, CLI)

SSO: SAML assertion attributes are never logged or persisted — cannot tell whether the IdP sent groups

IdP group memberships go stale indefinitely — user.idpGroups is only written by the SSO login hook

Extension version binaries never persisted — binaries security notice has never rendered

Genesis "New Blood" (recruit_first) only counts collective invites — a platform invite can never complete it

SCIM 2.0 provisioning endpoint for swamp-club (Users + Groups, deprovision)

SSO user profile fields are frozen at first login — overrideUserInfo is never set on any provider

@swamp/s3-datastore disables IMDS on plain EC2, so an attached instance profile cannot resolve

AWS credential handling is duplicated across five implementations — the #803 preflight and the #226/#228 SSO hints reached only one of them

A subscription stuck in incomplete has no path forward: it grants nothing, and the billing page offers no way to finish paying

Identifier lookups are exact-match and case-sensitive across username, email and slug

Invite failures emit no telemetry, so a rejected invitation leaves no trace anywhere

Collective invitations expire after 48h and the auto-claim is gated on that window, so a late signup is never bound

Invite typeahead searches username prefix only, so an operative cannot be found by name or email

Send the release headline from the release pipeline so New Release announcements say what shipped

Epic: /leaderboard and /u/{username} do not survive 10B events — measured audit + phased plan

Reconcile collective-token CLI activity to members (attribute → bonus → stamp → even-split)

deps-audit skips deno-runner and agent-runner lockfiles in ci.yml

Sync every signup to Attio, and backfill the existing population (#809 follow-up)

@swamp/issue-lifecycle probes /api/health, which swamp-club stopped serving when the Fresh scaffold stubs were removed

Lab lifecycle POST rejects any payload string that starts with a dollar sign, not just operator keys

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

$identify events are never consumed — anonymous web history never binds to the account

Finish the swamp_* attribute set on Attio users, and settle the two unprefixed ones (#809 follow-up)

aria-expanded is a raw boolean in 12 places — collapsed state is unannounced to screen readers

One standing read: every scalar score consumer reads board_totals, not the ledger

Profile Edit trigger is a fire-and-forget island: no aria-expanded, and a silent no-op if the editor island does not hydrate

Profile XP bar jumps ~25px on fill, and pays the full cumulative walk for two numbers board_totals already holds

Incremental activity projector + swamp.events username bloom index (spun off #925, stages 5+7)

Telemetry stats consumer: insert_id dedup commits before the write it guards — any upsert failure is silently swallowed on retry

score-rollup projector runs N× per cadence — the run gate is per-replica memory, not shared state

Leaderboard board windows re-anchor on wall-clock while the rest of the view is frozen at asOf

swamp CLI: surface the audience and resolvedInVersion fields swamp-club now serves