package-migrations.md

Package migrations

Convention for any package under packages/ that owns its own product tables — how its migrations are written, tracked, and applied.

The rules

• Literal SQL, not codegen. Every migration is a plain string in a { name, sql }[] array, hand-written and reviewed as-is — never a schema diff generated by drizzle-kit at apply time. The SQL a reviewer reads in the PR is exactly the SQL that runs against the database; there is no codegen step in between where the two could drift.
• A ledger table the package owns. Each package tracks which of its own migrations have run in a table named for itself (chat_migrations, routine_migrations, notify_migrations, webhook_triggers_migrations, insights_migrations, skills_migrations, …) — never the platform's own @intx/db drizzle journal, and never scripts/db-setup.ts's workbench_setup_migration table, which only tracks the platform's own migrations. A package's ledger stays inside the package: if it is ever extracted into its own repo, its migration history comes with it, with nothing to disentangle from the platform's.
• Every package owns a schema named for itself. No package table ever lives in public — public belongs to the platform's own @intx/db schema alone. Every package creates and owns a Postgres schema named for itself (mailbox, artifacts, memory, chat, routines, notify, webhook_triggers, insights, skills, …) and puts every table it owns there, including its own ledger table. This holds regardless of whether a package's rows carry a tenant_id / principal_id column: those are plain text identifiers, not foreign keys, so a row in chat.workbench_settings refers to a platform tenant by id exactly the same way whether it sits in public or in chat — the schema a table lives in is never load-bearing for that reference. A package landing in its own schema, never public, is what makes it possible to lift the whole package into another Interchange application without a name collision or a manual carve-out of "which public tables were actually ours."
• Every migration is transactional. A migration's SQL and its ledger INSERT run inside one sql.begin(...) transaction. If the SQL fails partway, nothing about that migration — not a table, not a column, not the ledger row — is left behind half-applied; the next run retries the same migration from a clean slate instead of getting stuck on a schema that looks partially migrated but has no ledger row to explain why.

Why not drizzle-kit codegen

Drizzle is used as a query builder and for the platform's own type definitions, but package migrations are never generated by pointing drizzle-kit at a schema and letting it diff. The SQL a PR reviewer approves must be the SQL that runs — a generated migration reviewed as a Drizzle schema diff, with the actual CREATE TABLE/ALTER TABLE statements produced later by a separate codegen pass, breaks that: the reviewed artifact and the applied artifact are two different things, and a codegen bug or a stale generated file becomes a way for them to diverge silently. Literal SQL closes that gap — what's in the file is what runs.

The three shapes

Looking at INSTALLED_PACKAGE_MIGRATIONS in scripts/db-setup.ts, a package's migration runner takes one of two live shapes today (a third, non-transactional self-contained shape existed until CL-6017 unified every package onto the transactional pattern — see below):

1. Self-contained, transactional — @corbits/chat, @corbits/notify, @corbits/webhook-triggers, @corbits/routines, @corbits/insights, @corbits/skills. The package's src/migrations.ts owns a literal { name, sql }[] array, opens its own short-lived postgres client, creates its ledger table if absent, and applies each not-yet-applied migration inside sql.begin(async (tx) => { ... }) — the migration's SQL and its ledger insert commit or roll back together. scripts/db-setup.ts imports the package's applyXMigrations(databaseUrl) function directly and calls it after the platform's own migrations. See packages/insights/src/migrations.ts for the reference implementation every other package now matches.
2. Delegated to an external package — @corbits/mailbox. The package ships and owns its entire migration story (its own literal SQL, its own ledger, its own mailbox schema) behind a single exported runner (runMailboxMigrations). scripts/db-setup.ts (via packages/inbox/src/migrations.ts's thin wrapper) only opens a connection and calls that runner — it has no opinion on what SQL runs. @corbits/artifacts and @corbits/memory follow the same delegated shape but aren't wired through scripts/db-setup.ts at all: they run their own migration runner at hub-boot mount time instead (see apps/hub/src/artifacts-mount.ts and apps/hub/src/memory-mount.ts), since both are optional engines that may not be configured in every deployment.

Before CL-6017, @corbits/chat, @corbits/notify, @corbits/webhook-triggers, and @corbits/routines were self-contained but not transactional: each migration's SQL and its ledger insert were two separate statements, so a failure between them could leave a table created with no ledger row to show for it. CL-6017 brought all four onto the same sql.begin pattern @corbits/insights already used, closing that gap.

Before CL-6005, @corbits/chat, @corbits/routines, @corbits/insights, @corbits/notify, and @corbits/webhook-triggers put their tables directly in public, on the reasoning that a tenant_id/principal_id column tied them to the platform's own schema. CL-6005 gave each of them a schema named for itself instead (chat, routines, insights, notify, webhook_triggers) — those columns are plain text, never a real foreign key, so nothing about the reference actually required sharing public. Schema changes like this are hard cutovers, not data-preserving migrations: pre-GA there is nothing to migrate, and an existing dev database picks up the new schema by running workbench reset rather than by an in-place ALTER TABLE ... SET SCHEMA.

Which shape to use for a new package

Transactional, self-contained (shape 1 above), in a Postgres schema named for the package. Copy packages/insights/src/migrations.ts's shape: a pgSchema("<name>") in schema.ts, a literal migration array whose SQL qualifies every table/index with that schema, a package-named ledger table living in the same schema, and each migration applied inside sql.begin. Reach for the delegated shape only when the package already ships its own migration runner as part of a larger, independently-owned engine (its own schema, its own connection handling) — not as a shortcut to skip writing a ledger table.