feat(clickhouse): tracked archive→CH backfill tooling (backfill.sql)

The mentions+inbox system from #127 (shipped) covers @username mentions, but there is still no notification when there is activity on an issue I authored or participate in — and the only delivery channels today are email and the in-app inbox. As a frequent submitter, I find out an issue I filed got a reply or shipped only by manually revisiting it.

Two gaps:

Participant activity notifications (not just @-mentions):
- A ripple/comment is posted on an issue I authored or have commented on.
- The issue's status changes (open → shipped / closed / reopened) on an issue I authored.
Discord as a delivery channel. Notifications today land in email + the in-app inbox. Many of us live in the Lab Discord, not the swamp.club web inbox. Add Discord delivery so issue activity reaches us where we already are. This should also extend to the existing #127 @-mention notifications.

Desired behavior

When one of the trigger events above fires for an operative who is the author or a participant on the issue, deliver a notification through their configured channels:

In-app inbox (always-on baseline, reuse #127 plumbing).
Email (opt-in/opt-out, reuse the existing Resend integration).
Discord bot DM (new): the Lab Discord bot sends the operative a direct message with a one-line summary and a link back to the issue/comment.

Preferred Discord mechanism is a bot DM rather than an @-tag in the public automated-activity channel — DMs are private, targeted, and avoid adding noise to the shared feed. (If a DM can't be delivered, e.g. the user has DMs closed, falling back to an @-tag in the activity feed is acceptable.)

Scope suggestions

Link a swamp.club operative to their Discord identity (the role-sync work behind #633 already maps operatives ↔ Discord accounts — reuse that linkage rather than asking users to re-link).
Identify participants on an issue = author + anyone who has rippled. De-dupe so one event = one notification per operative.
Per-channel, per-event-type preferences (inbox always on; email and Discord opt-in/opt-out), with sensible defaults.
Don't notify an operative about their own action (self-ripple, self-status-change).
Respect existing mute/read state so Discord delivery doesn't re-notify something already handled.

Out of scope (first cut)

Digest/batched notifications.
Notifications for non-issue surfaces.
Configurable Discord channel routing beyond DM (+ activity-feed fallback).

Why this matters

Right now submitting an issue is fire-and-forget — there's no signal loop back to the author when the maintainers respond or ship it. Adding participant-activity notifications closes that loop, and routing them to Discord (where the community actually is) makes them far more likely to be seen than a web inbox we rarely open. It builds directly on the #127 mentions system and the #633 Discord role-sync linkage, so the user↔Discord mapping already exists.

02Bog Flow

Closed

6/26/2026, 8:00:29 PM

No activity in this phase yet.

03Sludge Pulse

keeb commented 6/26/2026, 8:00:26 PM

I love this @mgreten. I have a design for something a little more intricate I posted on discord and just @'d you about. I'm closing this issue.

mgreten commented 6/26/2026, 8:26:16 PM

nice!

feat(clickhouse): tracked archive→CH backfill tooling (backfill.sql)

feat(clickhouse): idempotent DDL migration path for running prod (#859 deliverable 2)

chore(clickhouse): retire S3-backed v1 + s3 objects after #859 cutover

Decouple prod ClickHouse from S3 (drop storage_policy=s3_main) + add a DDL migration path

Epic #847 · Unit 6: Document the Mongo-vs-ClickHouse storage-architecture split in scoring.md

Epic #847 · Unit 5: ClickHouse materialized-view projections + atomic leaderboard read-flip + delete Mongo OLAP

Epic #847 · Unit 4: Stream confirmed grants into ClickHouse score_grants (ReplacingMergeTree)

Epic #847 · Unit 3: Migrate the 5 recompute contributions to per-event grants; delete the recompute path

Epic #847 · Unit 2: score_grants append-only ledger write-model in Mongo (shadow, no read flip)

Epic #847 · Unit 1: Land the ClickHouse projection foundation (schema + init SQL + compose service)

Global skills should auto-sync when binary version advances

autoGc emits auto_gc_completed event on --json stdout, breaking single-parse consumers

Extension publish score is non-monotonic: yanking versions lowers a user's score

Live Swamp Club event console on /feed — scrolling stream of all non-sensitive events

Docs: add --ws-idle-timeout to serve flags reference

copy/rsync ignores transport extraOptions (and proxyCommand), unlike exec/script

Remove feed comments — consolidate discussion in Discord

Make serve WebSocket idle/keepalive timeout configurable (untunable default aborts runs when serve's loop briefly blocks)

Docs: update extension info reference with content metadata output

serve startup time regression: synchronous catalog init delays WebSocket listener by ~4.5 minutes

Expose run/job/step identifiers as SWAMP_* env vars + CEL values, and template placement selectors (extends #331's run.id)

fix: add .namespace.json to isInternalCacheFile() in datastore extensions

docs: document swamp serve daemon enable/disable/status subcommands

docs: document execution cancellation commands and cancelled status

docs: document autoGc config option for automatic garbage collection

Docs: document @env= and @file= webhook secret indirection in swamp-serve reference

fix: datastore sync --push deletes the namespace registration manifest (canonical namespace flow un-registers itself)

docs: bundled swamp agent skill lacks datastore-namespace guidance (giga-swamp)

data query --select crashes on BigInt: "Do not know how to serialize a BigInt" when CEL size() reaches the JSON renderer

Intra-namespace write concurrency: whole-index sync under the lock serializes fan-out workloads (split from shipped #666)

Telemetry recoverOrphaned startup race with multiple replicas (created_at-based)

Telemetry retry/failed path has the same non-atomic claim as #820

Batch / prefix delete for swamp data delete (single lock acquisition)

Surface extension type+method detail in CLI to eliminate expensive discovery loops

Skill guides lack progressive reveal boundaries — agents over-read by 4x

Opt-in automatic garbage collection for datastore data

UAT: swamp workflow evaluate/run with forEach dynamic workflowIdOrName targets

Telemetry watcher has no replica coordination: N replicas double-process the same batch (non-atomic find→updateMany claim)

Telemetry drain still capped ~80-100/s in prod: per-username full-history re-aggregate is O(users) sequential per batch (deferred #817 fix #4)

Telemetry ingest is consumer-bound: counter & stats dedup via O(N) sequential insertOne, throughput stuck ~20 events/s regardless of BATCH_SIZE

Resolve dynamic workflow task targets inside forEach

Leaderboard and profile streak not reporting

Same-namespace writers fully serialize on the per-namespace lock — could maintenance/append writes avoid holding it?

Could method-summary report artifacts get a default retention cap? They grow to dominate the datastore manifest

Docs: update vault inspect output in manual reference

Execution cancellation: abort stuck workflow runs and model method runs, bulk cleanup, and daemon-restart reaping

Docs: document .? optional select for null-safe CEL data access

Optional scheduled / automatic datastore GC (retention-policy-driven pruning)

Notify issue author/participants on ripples & status changes — with Discord bot DM as a delivery channel

Batch step 2 of enrichAuthorPlans (per-collective subscription reads)

Datastore should fail fast on unresolvable credentials instead of stalling on the AWS provider chain

Add SWAMP CLUB wordmark logo next to sc-mark.png in TRADEMARKS.md

Add SWAMP CLUB wordmark logo next to sc-mark.png in TRADEMARKS.md

pushChanged does not implement absence-on-disk deletion (markDirty contract rule #2)

pushChanged does not implement absence-on-disk deletion (markDirty contract rule #2)

Add `vault delete` support to @swamp/aws-sm extension

Add `vault delete` support to @swamp/azure-kv extension

Add `vault delete` support to @swamp/1password extension

Leaderboard window baseline: 90-day cutoff zeroes returning-dormant users (latent, 0 impact today)

swamp data gc prunes the catalog but never deletes objects from S3 datastores (markDirty hook not wired) — sync manifest never shrinks

SKILL.md Common Commands: model type search uses wrong command and syntax

SKILL.md Common Commands: model create uses wrong @<type> prefix

swamp issue bug times out posting to the Lab while swamp-club.com returns HTTP 200

telemetry stats fatally fails to load an installed datastore extension (auto-resolve path); all other commands load it fine

tf plan: FETCH_BUNDLE PAGE_FETCH_ERR / NO_STATES on cleanup-only plan (no resource changes)

Add deleteResource to MethodContext and document dataRepository.delete in skills

Homebrew formula

Yank semantics inconsistent: all-versions-yanked acts as a free hidden/private extension; extension-level yank hard-blocks re-push

Extension search returns edit-distance noise for short queries ("asdl" → "AWS DEADLINE")

workflow resume holds the global lock across the resumed step, deadlocking any datastore op the step performs

Trajectory chart: current-day x-axis label is clipped at the right edge

Telemetry not synced to swamp-club: local queue accumulating ~3 days despite valid auth

extension pull serves a stale version that disagrees with search (honors a legacy per-extension serverUrl)

serve --webhook usage string makes <header> look optional for generic scheme

serve: webhook scheme not surfaced in startup event, health endpoint, or log line

Slack webhook pre-body gate only checks signature header, not timestamp

Dead code: verifySignature in webhook.ts superseded by verifier abstraction

extension source: install skills from source-path extensions

Data-driven webhook signature verifiers (avoid a code change + release per provider)

swamp.club extension view: multi-line code fence in manifest description renders each line as a separate inline code span

Single global datastore lock serializes unrelated writes across all repos/namespaces