Skip to main content
← Back to list
01Issue
BugShippedSwamp ClubPublic
AssigneesNone

Relationships

#2216 Apply score_grants_from_recruit_mv to prod ClickHouse — recruit links mint zero until it lands

Opened by skunk-ape · 9/16/2026· Shipped 9/16/2026

PR #80: https://github.com/swamp-club/giga-swamp/pull/80

Problem

PR https://github.com/swamp-club/swamp-club/pull/1219 (#2179) ships the recruit-link lane: the link, the bind, the recruit event and the quest credit. It does not reach prod ClickHouse, and cannot.

infrastructure/clickhouse/init/03-grant-projections.sql only runs on an empty data dir, and a stored MV runs its stored definition forever. So on a live server the new score_grants_from_recruit_mv does not exist. Until this is applied:

  • recruit events accumulate in swamp.events and mint zero points
  • recruiters get no 200,000 grant, on a feature whose entire purpose is that payout
  • there is no symptom anywhere — a green CI run and a working local flow are both perfectly consistent with the feature paying nobody, indefinitely

Where it goes

The giga-swamp clickhouse-migrations model (models/@giga-swamp/k8s-manifest/…): a one-shot Job running ordered, idempotent NNN-*.sql against clickhouse.clickhouse.svc:9000. The prod schema-of-record is the ConfigMap at models/@giga-swamp/k8s-manifest/clickhouse.yaml; infrastructure/clickhouse/init/*.sql in swamp-club is a hand-maintained mirror of it.

Steps

  1. Add the MV verbatim from init/03-grant-projections.sql as a new ordered NNN-*.sql, using CREATE MATERIALIZED VIEW IF NOT EXISTS.
  2. Recompute the payload hash — cat migrations/*.sql | sha256sum | cut -c1-8 — and bump BOTH the Job and its ConfigMap name. Jobs are immutable: forget either and apply is FALSE-GREEN, reporting success while nothing re-runs. This is the step that actually bites.
  3. Verify against the live server rather than trusting the apply: SELECT count(), sum(amount) FROM swamp.score_grants WHERE grant_type = 'recruit' — non-zero once a recruit crosses ordinal 5.

Recovery if it lands late

infrastructure/clickhouse/score-grants-backfill.sql carries a mirrored INSERT … SELECT for recruit, so events emitted during the gap can be replayed. That is exactly why the MV and the backfill must stay in lockstep — if the MV is edited without the backfill, the replay silently prices history differently.

Full procedure and hazards: infrastructure/clickhouse/MIGRATION.md ("Pending prod migration") and infrastructure/clickhouse/CLAUDE.md.

Filed as its own issue because it is the highest-risk item in #2179 and it lives in a different repository — tracking it only in a markdown file and a PR description is how it gets skipped.

02Bog Flow
✓OPEN✓TRIAGED✓IN PROGRESS✓SHIPPED

Shipped

9/16/2026, 10:50:38 PM

No activity in this phase yet.

Linked PR #80https://github.com/swamp-club/giga-swamp/pull/80
03Sludge Pulse
Editable. Press Enter to edit.

swamp_lord commented 9/16/2026, 10:47:51 PM

Deployed to production ClickHouse on 2026-09-16 at 22:43 UTC through giga-swamp workflow clickhouse-production-apply-sql.

  • Run: 4909a65f-7cf8-49ad-a903-4d0d13865d81 — succeeded after explicit operator approval.
  • Full standalone pre-apply backup: premig-recruit-grants-mv-2026-09-16-2238; ClickHouse returned BACKUP_CREATED, backup ID 97be88b4-fce7-4178-87ed-6bd1e0985bb1.
  • SQL preview/lint: LINT_OK.
  • Created swamp.score_grants_from_recruit_mv using PR swamp-club/swamp-club#1219 at [REDACTED-SECRET-1], with ON CLUSTER clickhouse_production as the production topology adaptation. The SELECT is unchanged: 200,000 base points with event-time campaign multiplier; exact recruit predicate excludes recruit_landed.
  • Verified independently on ch-01/ch-02/ch-03: MV present with identical stored SELECT hashes; migration 2026-09-16-recruit-grants-mv recorded exactly once; no unfinished mutations, read-only replicas, expired Keeper sessions, or replication delay in the checked tables.
  • At verification there were zero recruit events and zero recruit grants on every replica, so no historical backfill was needed. First real payout remains an end-to-end check when a qualifying event arrives.

The current production path is the droplet-cluster SQL workflow, rather than the older Kubernetes migration Job described in this issue. The migration file and from-nil rebuild schema are prepared in giga-swamp; PR/merge reference to follow.

swamp_lord commented 9/16/2026, 10:50:49 PM

Shipped. The production deployment and rebuild schema are now merged into giga-swamp main:

  • PR: https://github.com/swamp-club/giga-swamp/pull/80
  • Merge commit: 7034581
  • Migration: extensions/files/clickhouse-production/migrations/2026-09-16-recruit-grants-mv.sql
  • Source MV: swamp-club PR #1219 at commit 2ebe733 (the full source commit in the earlier deployment comment was incorrectly redacted as a secret).

Production deployment evidence and the verified full backup are recorded above. All three replicas have the same MV and migration record; there were no recruit events requiring backfill. Issue status is now shipped and the merged PR is linked.

Sign in to post a ripple.