Skip to content

cogni-dao/cogni / .claude/skills/schema-update

schema-update

Verified

Use any time you are about to add, change, or migrate a Postgres or Doltgres table — editing schema TS, running `pnpm db:generate:*`, writing a migration `.sql`, touching `meta/_journal.json` / `*_snapshot.json`, or debugging a migration that didn't apply on candidate-a. Mandatory before any schema edit.

schema-update

The minimal procedure for changing a table. Follow it; don't improvise.

Canon: https://node-template.cognidao.org/knowledge/database-migration-contract §2.6. This skill is the operational checklist.

Postgres — the only path

  1. Edit the schema TS. Core table → packages/db-schema/src/. Node-local → <node-repo>/packages/db-schema/src/ — in the NODE'S own repo; this monorepo holds nodes/operator + nodes/scheduler-worker only (this package today; create one for a new node by forking poly's). Never reach into nodes/<node>/app/src/... for schema.
  2. pnpm db:generate:operator (or the node's own equivalent, defined in its repo — VERIFIED: :operator and :operator:doltgres are the only generate targets here).*_ Auto-gen emits NNNN_<tag>.sql, the _journal.jsonentry, andNNNN_snapshot.jsontogether. Inspect the.sql— drop unintendedDROP TABLE "poly*copy_trade*_" from operator/resy gens (orphan tables, intentional).
  3. pnpm db:check. Validates: snapshot/prevId chain (drizzle-kit), strict-monotonic journal when (scripts/db/check-journal-when.mjs), applied-migration immutability (check-migrations-immutable.mjs), and generate-cleanliness (check-generate-clean.mjs — fails if db:generate:operator would emit any drift, i.e. you edited schema TS without committing the migration). A clean tree ⇒ db:generate says "No schema changes". If it red-flags non-monotonic when, your auto-gen Date.now() landed before a prior entry — bump the new entry's when past the violation it reports. Pre-existing future-dated entries on a node (poly idx 33 today, normalizes ~May 5 2026) emit a warning so you know your auto-gen will need a manual bump until the wall clock catches up; not fatal.
  4. Core table only: copy the new NNNN_*.sql + journal entry into every other node's migrations/ dir (each deployed DB has its own __drizzle_migrations table; they need their own copy with matching journal idx + when).
  5. Commit .sql + _journal.json + NNNN_snapshot.json together. Never --no-verify a schema PR.
  6. Post-flight: kubectl logs <pod> -c migrate must list your tag with ✅ migrations applied. If it didn't run, db:check was bypassed.

RLS coverage — mandatory for any table with a FK to users

Drizzle does not generate RLS. If your new table has a foreign key to users, you MUST add RLS in the same migration (hand-authored fallback below) or the coverage gate fails CI. Two correct shapes:

  • User-facing reads → an owner-scoped policy (USING (... = current_setting('app.current_user_id', true))). See https://node-template.cognidao.org/knowledge/rls-tenant-isolation.
  • Service-role-only (worker reads/writes via the BYPASSRLS role, no app-role path) → ENABLE + FORCE with no policy = deny-all (fail-closed). No fake policy needed.

The gate (tests/component/setup/testcontainers-postgres.global.ts) fails if any public table with a FK to users has relrowsecurity = false — invariant RLS_COVERAGE in https://node-template.cognidao.org/knowledge/rls-tenant-isolation. It exists because user-FK tables historically shipped with no RLS (the 0010→0032 epoch-ledger leak); the gate makes that class of drift impossible to merge.

Hand-authored fallback — only if drizzle-kit literally can't emit it

Valid triggers: RLS policies, ALTER POLICY, triggers, custom Postgres functions, ARRAY DEFAULTs the TS schema can't express. Plain ADD COLUMN, CHECK, partial index, FK — auto-gen handles all of this. When in doubt, try db:generate first.

Recipe (steps 1–4 atomic in one commit):

  1. Write NNNN_<tag>.sql.
  2. Append journal entry; when > max(prior when).
  3. cp meta/(N-1)_snapshot.json meta/NNNN_snapshot.json; regenerate id, set prevId to prior snapshot's id, edit tables to reflect your deltas.
  4. pnpm db:check green.

Never edit a previously committed snapshot's prevId to silence db:check. If the chain is broken, fix it forward.

Doltgres — same shape, parallel pipeline

Doltgres is for AI-written knowledge (knowledge table + companions); Postgres is for operational data. Default for any AI-edited content: a new row in knowledge with a different domain + tags, NOT a new table. See database-expert for the syntropy rule.

When you do need a new Doltgres table:

  1. Edit <node-repo>/packages/doltgres-schema/src/ — in the NODE'S own repo; fork the node-template pattern.
  2. pnpm db:generate:<node>:doltgres.
  3. Same when monotonicity check on the Doltgres journal.
  4. pnpm db:check (covers Doltgres configs too).
  5. Adapter writes use sql.unsafe() + try-INSERT/catch-duplicate (extended-protocol params + ON CONFLICT EXCLUDED are broken on Doltgres 0.56). Don't "modernize" them.
  6. Migrator chains a trailing SELECT dolt_commit('-Am', '...') to capture DDL into dolt_log. If you wire a new node's Doltgres pipeline, replicate this.

Common failure modes (rank-ordered by recent incidents)

  1. Future-dated when silently no-ops your migration on candidate-a. App pod has the schema code; DB doesn't have the column. Symptom: PostgresError: column "X" does not exist 60s into deploy.
  2. Hand-authored when auto-gen would have worked — broke snapshot chain, missed journal entry, or both.
  3. Schema edited under nodes/<node>/app/src/... — drizzle config doesn't see it; db:generate produces no diff.
  4. Auto-gen proposed DROP TABLE for another node's orphan tables — committed unread; tables vanish on next migrate.
  5. Pushed with --no-verify — skipped db:check, shipped a broken chain.

Quick reference

pnpm db:generate:<node>              # generate from schema diff
pnpm db:generate:<node>:doltgres     # generate Doltgres
pnpm db:check                        # validate all node chains (snapshot + prevId)
pnpm db:migrate:<node>                # apply locally

Schema package locations:

packages/db-schema/                                       core Postgres (cross-node)
nodes/<node>/packages/db-schema/        @cogni/<node>-db-schema           (in the NODE'S repo)
nodes/<node>/packages/doltgres-schema/  @cogni/<node>-doltgres-schema     (in the NODE'S repo)
nodes/<node>/drizzle.config.ts          schema array unions whatever applies
nodes/<node>/app/src/adapters/server/db/migrations/         Postgres history
nodes/<node>/app/src/adapters/server/db/doltgres-migrations/ Doltgres history

When to escalate

  • Need to choose Postgres vs Doltgres for a new table → database-expert (split rule + syntropy).
  • Migrator pod crash-looping or initContainer not firing → deploy-operator / devops-expert (image build wiring, Argo).
  • Normalizing when poisoning across deployed DBs → coordinate; touches __drizzle_migrations rows on candidate-a + preview + prod.
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall