cogni-dao/cogni / .claude/skills/schema-update
schema-update
VerifiedUse 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
- 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 holdsnodes/operator+nodes/scheduler-workeronly (this package today; create one for a new node by forking poly's). Never reach intonodes/<node>/app/src/...for schema. pnpm db:generate:operator(or the node's own equivalent, defined in its repo — VERIFIED::operatorand:operator:doltgresare the only generate targets here).*_ Auto-gen emitsNNNN_<tag>.sql, the_journal.jsonentry, andNNNN_snapshot.jsontogether. Inspect the.sql— drop unintendedDROP TABLE "poly*copy_trade*_"from operator/resy gens (orphan tables, intentional).pnpm db:check. Validates: snapshot/prevIdchain (drizzle-kit), strict-monotonic journalwhen(scripts/db/check-journal-when.mjs), applied-migration immutability (check-migrations-immutable.mjs), and generate-cleanliness (check-generate-clean.mjs— fails ifdb:generate:operatorwould emit any drift, i.e. you edited schema TS without committing the migration). A clean tree ⇒db:generatesays "No schema changes". If it red-flags non-monotonicwhen, your auto-genDate.now()landed before a prior entry — bump the new entry'swhenpast 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.- Core table only: copy the new
NNNN_*.sql+ journal entry into every other node'smigrations/dir (each deployed DB has its own__drizzle_migrationstable; they need their own copy with matching journal idx +when). - Commit
.sql+_journal.json+NNNN_snapshot.jsontogether. Never--no-verifya schema PR. - Post-flight:
kubectl logs <pod> -c migratemust list your tag with✅ migrations applied. If it didn't run,db:checkwas 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))). Seehttps://node-template.cognidao.org/knowledge/rls-tenant-isolation. - Service-role-only (worker reads/writes via the BYPASSRLS role, no app-role path) →
ENABLE + FORCEwith 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):
- Write
NNNN_<tag>.sql. - Append journal entry;
when > max(prior when). cp meta/(N-1)_snapshot.json meta/NNNN_snapshot.json; regenerateid, setprevIdto prior snapshot'sid, edittablesto reflect your deltas.pnpm db:checkgreen.
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:
- Edit
<node-repo>/packages/doltgres-schema/src/— in the NODE'S own repo; fork thenode-templatepattern. pnpm db:generate:<node>:doltgres.- Same
whenmonotonicity check on the Doltgres journal. pnpm db:check(covers Doltgres configs too).- Adapter writes use
sql.unsafe()+ try-INSERT/catch-duplicate (extended-protocol params +ON CONFLICT EXCLUDEDare broken on Doltgres 0.56). Don't "modernize" them. - Migrator chains a trailing
SELECT dolt_commit('-Am', '...')to capture DDL intodolt_log. If you wire a new node's Doltgres pipeline, replicate this.
Common failure modes (rank-ordered by recent incidents)
- Future-dated
whensilently 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 exist60s into deploy. - Hand-authored when auto-gen would have worked — broke snapshot chain, missed journal entry, or both.
- Schema edited under
nodes/<node>/app/src/...— drizzle config doesn't see it;db:generateproduces no diff. - Auto-gen proposed
DROP TABLEfor another node's orphan tables — committed unread; tables vanish on next migrate. - Pushed with
--no-verify— skippeddb: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
whenpoisoning across deployed DBs → coordinate; touches__drizzle_migrationsrows on candidate-a + preview + prod.
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| 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: 119k | Updated | Claude CodeCodex | Verified | Install |
| 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: 119k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |