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

Global skills (the built-in swamp and swamp-getting-started skills at ~/.claude/skills/swamp/) are currently refreshed as a side effect of swamp repo upgrade. This creates a disconnect: the skills are global (they apply everywhere, not per-repo), but the only update path requires being inside an initialized swamp repository.

If a user has no initialized repos, or simply runs swamp from a directory that isn't a repo, their global skills rot. The skills can drift from the binary version indefinitely until someone happens to run repo upgrade somewhere.

Observed behavior

Running swamp repo upgrade in an initialized repo shows "skillsUpdated": ["swamp", "swamp-getting-started"] and writes to ~/.claude/skills/swamp/. But there's no way to trigger this without a repo context. Running swamp update upgrades the binary but does NOT refresh the global skills unless repo upgrade is subsequently run in some repo.

Proposed solution: auto-sync on first invocation + explicit sync during `swamp update`

Two mechanisms, complementary:

A. Auto-sync on first invocation of a new binary version

The binary embeds skill content and a content hash.
On any swamp invocation, compare the embedded hash against a marker file (e.g., ~/.swamp/global-skills-hash).
If different, write the embedded skills to the global directory silently (or with a single log line like "Updated global skills to 20260626.x").
If the write fails (permissions, disk full), log a warning and proceed — stale skills are better than a broken command.

B. `swamp update` explicitly triggers the sync as a post-step

Since swamp update already upgrades the binary, it should write the matching skill files immediately after. This covers:

The --setup-auto path (auto-update delivers new binary silently → skills follow automatically)
The manual swamp update path (single ceremony: "run swamp update, you're done")

Combined effect

Skills and binary are always in sync by construction.
No repo context required. No command to forget to run.
swamp repo upgrade continues to do it too (backward compat), but is no longer the only path.
Follows promise theory: the agent observes its own state and self-corrects without external direction.

Alternatives considered

Dedicated swamp skills sync subcommand — Adds surface area for something that should be invisible. Still requires the user to discover and remember to run it. Doesn't solve the "user never runs it" problem.

Skills served from binary with no filesystem persistence (local socket, MCP resource, virtual FS) — Eliminates the sync problem entirely but breaks the current contract where tools discover skills by scanning ~/.claude/skills/. Requires per-tool integration work. Probably too large an architectural change for this gap.

Why this matters

The global skills are the primary routing table and reference material for agents working with swamp. If they drift from the binary, the agent gives stale guidance (wrong command flags, missing new features, deprecated patterns). Since agents load skills on every conversation start, even a brief drift window means incorrect advice.

Environment

swamp version: 20260626.202321.0
Skills update observed via swamp repo upgrade --json showing skillsUpdated field
No swamp skill or swamp skills subcommand exists currently

02Bog Flow

Open

6/27/2026, 1:55:10 AM

No activity in this phase yet.

03Sludge Pulse

evrardjp commented 6/27/2026, 9:46:46 AM(edited)

I would prefer that a swamp update automatically does the upgrade of the skills unless explicitly mentioned e.g. with a --no-skill-update flag.

I can see why a check at runtime works, but it feels weird from the long term maintenance perspective -- run is run, upgrade is upgrade :) (However, I guess it's none of my concern, as I don't maintain this ><... it's just a force of habit)

I do agree with the hashes however, this is a good safety net.

I would have taken another approch than use a marker file on all swamp installations: No marker file. Just compute the hash of each of the skill contents upon upgrade of skills, compare this with known hashes. For that, each skill can hold metadata of its current version. This means upgrading a skill is basically reading a file, check its version, lookup its version in a map, find the hash, compare the hash, done. This way you can even track if the skill is outdated by checking the skill directly... It's a more granular view (which might not be required, to be evaluated by the swampers!)

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