create-migration · diff
git:20260830.fd5c009 to git:20260928.2ccfbb7
36 added, 8 removed. Audit A to A.
---
name: create-migration
- description: Use this skill whenever you change the shape of the 0.5 backend database — a new table, a new column, an index, a constraint, a backfill, or a data reshape. It owns the authoring contract (one numbered .sql file under services/platform/backend/db/migrations/, applied at boot in filename order inside one advisory lock), the forward-only doctrine (every migration must be safe to apply to a live deployment mid-roll, because the previous image is still serving while the new one migrates), the org-config file lane (config trees are NOT database rows — they move through the scaffolder), and the proof duty (the real-Postgres integration check). Load it before adding anything under backend/db/migrations/.
+ description: Use this skill whenever you change the shape of the 0.5 backend database — a new table, a new column, an index, a constraint, a backfill, or a data reshape. It owns the authoring contract (one numbered .sql file under services/platform/backend/db/migrations/ — or a numbered .ts data migration when a backfill must decide with the app's own code — applied at boot in filename order inside one advisory lock), the forward-only doctrine (every migration must be safe to apply to a live deployment mid-roll, because the previous image is still serving while the new one migrates), the org-config file lane (config trees are NOT database rows — they move through the scaffolder), and the proof duty (the real-Postgres integration check). Load it before adding anything under backend/db/migrations/.
---
# Backend database migrations (0.5)
Every database-shape change ships as a numbered SQL file under
- `services/platform/backend/db/migrations/`. `runBootMigrations` (`backend/db/migrate.ts`) applies
- them **at every backend boot**, in filename order, each in its own transaction, tracked by
- filename in `app_migrations` — all inside one session-scoped advisory lock, so N concurrently
- booting containers (api + worker, or scaled replicas) apply everything exactly once while the
- others wait.
+ `services/platform/backend/db/migrations/` (a backfill that must decide with the app's own code
+ is a numbered `.ts` data migration there — see below). `runBootMigrations`
+ (`backend/db/migrate.ts`) applies them **at every backend boot**, in filename order, each in its
+ own transaction, tracked by filename in `app_migrations` — all inside one session-scoped advisory
+ lock, so N concurrently booting containers (api + worker, or scaled replicas) apply everything
+ exactly once while the others wait.
There is no `tale migrate up/down`, no versioned framework, no rollback ledger: a deployed image
is at its own schema by construction. `tale migrate` means something else entirely — re-provision
built-in defaults into every org (`/api/control/provision`).
> The 0.4 Convex versioned-migration framework (`defineDbMigration`, `migrations:runAll`,
> `tale migrate status/up/down`, the world corpus) is **retired**. 0.5 is a fresh instance and
> carries no data forward from it.
## The authoring contract
```
services/platform/backend/db/migrations/NNNN_snake_case_subject.sql
```
- **`NNNN`** is the next zero-padded number, no gaps, no reuse. Filename order IS apply order, and
the filename is the identity recorded in `app_migrations` — **never rename a file that has
shipped**, or every existing deployment re-applies it.
- **One subject per file.** The name says what it is (`0057_competence_records.sql`), not what you
did (`0057_fix.sql`).
- **Everything lands in the `app` schema** (`CREATE TABLE app.x`), the app's own namespace. Better
Auth owns the unqualified tables (`"user"`, `"member"`, `"organization"`) and migrates itself;
pg-boss owns `pgboss`. Never write either from here.
- **Comment the WHY at the top**, and on any column whose meaning is not obvious from its name —
these files are the schema's documentation. Look at `0057_competence_records.sql` for the house
style (what the table is for, which rule an index encodes, why a row is retained rather than
deleted).
- Timestamps are `bigint` epoch-millis columns named `*_at_ms` (the app's clock is JS). `id text
PRIMARY KEY DEFAULT gen_random_uuid()` is the standard key.
## Forward-only, and safe to apply under a rolling deploy
The previous image keeps serving while the new one migrates, so **every migration must leave the
OLD code working**. That is the whole discipline:
| Change | How |
| ----------------------- | ------------------------------------------------------------------------------------------ |
| New table | Just create it. |
| New column | Nullable, or `NOT NULL DEFAULT …`. Never bare `NOT NULL` on a populated table. |
| Retire a column | Stop reading it in code and ship that FIRST; drop it in a later release. |
| Rename a column | Two steps: add the new one + backfill, ship the code that writes both, then drop the old. |
| New constraint | Only if existing rows already satisfy it — otherwise clean the data in the same file, first. |
| New index | Plain `CREATE INDEX` (each migration is one transaction, so `CONCURRENTLY` is unavailable). |
- | Backfill | Set-based `UPDATE … WHERE` in the same file; it must be idempotent and bounded. |
+ | Backfill | Set-based `UPDATE … WHERE` in the same file; idempotent and bounded (`.ts`: see below). |
**Encode the rule in the schema when you can.** A partial unique index that says "at most one live
grant per member" is a rule the database cannot forget; the same rule written as a scan-and-compare
in a service is a rule the next handler will miss.
Use `IF NOT EXISTS` / `IF EXISTS` freely — a migration file runs once, but a re-run after a
half-failed deploy must not be a landmine.
+ ## Data migrations in TypeScript
+
+ A backfill whose decision is a rule the application already owns — which files an extractor reads
+ (`isSupported()`), which MIME types are media — must not freeze a SQL copy of that rule: the copy
+ is a second source of truth the moment the real one moves. Write it as a numbered `.ts` module in
+ the same directory instead:
+
+ ```
+ services/platform/backend/db/migrations/NNNN_snake_case_subject.ts
+ ```
+
+ - It exports `migrate(tx: TransactionSql): Promise<void>`. The migrator imports it by filename and
+ runs it inside the migration's own transaction, in the same filename order and the same
+ `app_migrations` ledger as the `.sql` files (`isMigrationFile` in `backend/db/migrate.ts`; a
+ `.test.ts` or `.d.ts` beside it is never applied).
+ - Same rules as SQL: WHY at the top, idempotent, bounded, rolling-deploy safe. Read only the rows
+ that can need the fill, lock them (`FOR UPDATE`), re-check the condition in the `UPDATE`, and
+ write set-based (`unnest` of the ids and values the app's rule decided). A status write that a
+ list renders emits its hint, once per organization, the way the lane writing it would.
+ - Schema changes stay `.sql` — those files are the schema's documentation. Scaffold with
+ `bun run gen:migration`, kind `ts`; `0128_rag_unsupported_type_codes.ts` is the reference — it
+ fills the code the indexer's rule implies on rows written before every lane wrote it.
+ - Prove it the way SQL backfills are proven: seed the rows in `backend/integration-check.ts`,
+ import the module and run `migrate` in a transaction twice (the second run changes nothing), and
+ pin its decision table with a unit test in `backend/db/data-migrations.test.ts`, which also
+ guards that every `.ts` migration exports `migrate`.
+
## What does NOT belong here
- **Org config files** (agents, automations, connectors, providers, skills, governance policies)
live on the config volume, not in Postgres. They move through the org scaffolder
(`backend/domains/organizations/scaffold.ts`), which is idempotent per domain and re-runnable
via `tale migrate` / `tale deploy --override-all`.
- **The knowledge corpus schema** has its own migrations under `services/db/migrations/knowledge-db/`,
applied by `ensureDefaultCorpusSchema()`; a BYO corpus bootstraps on first use.
- **pg-boss queues** — declared in `backend/jobs/boss.ts`, created by `ensureQueues`.
## Prove it
A migration is not done until something exercises the shape it created:
- `bun run --filter @tale/platform backend:integration` — the real-Postgres proof. It runs boot
migrations twice CONCURRENTLY (the advisory lock's own test) and then drives every domain over
the real schema. Add a probe for the behaviour your migration enables; see the backend README
for the throwaway-Postgres + MinIO invocation.
- `bunx vitest --run --project server` — the unit layer for the service that reads the new shape.
## Definition of done
- - [ ] One numbered `.sql` file, no gap, never renamed after shipping
+ - [ ] One numbered `.sql` file (or `.ts` data migration), no gap, never renamed after shipping
- [ ] Applies cleanly to a FRESH database and to one at the previous release
- [ ] The old code still works against the new schema (rolling-deploy safe)
- [ ] Rules that can be constraints/indexes are constraints/indexes
- [ ] A probe in `backend/integration-check.ts` covers what it enables
- [ ] `bun run --filter @tale/platform backend:integration` green