ADR-0002: Migration files live with their owner; the runner is the platform's

Status: Accepted. The 2026-10-01 SQLite revision is superseded by ADR-0010 (2026-10-14): a schema per project (p_<slug>), a ledger per schema, an advisory lock, a comment-insensitive checksum.

Context

A user has many projects sharing one database, so a project's tables need somewhere to live that cannot collide with another project's.

Decision

• A project owns a schema inside its user's database: p_<slug>.
• Migration FILES live in the owning repo (projects/<slug>/db/migrations/NNN_*.sql). A user's own tables are the user's business and never appear in the platform tree.
• The platform owns only the RUNNER: a per-schema schema_migrations ledger, an advisory lock, checksums, one transaction per migration.
• Migrations run as a deploy step, never at request time.

Consequences

• Two projects can both own a tasks table while sharing one database and one set of credentials.
• A silently edited migration is caught by the checksum; the lock stops a redeploy and a manual run from interleaving.
• The runner needs (database, schema, directory) and nothing else. Provisioning derives that from the repo layout today.

Revision (2026-10-01): one file per project, run by the service host

This was written for PostgreSQL. With SQLite (ADR-0001, revised) there are no schemas and no advisory locks, and the runner (lib/migrate.ts in the shared library) changed with it:

• A project's storage is its own file, <data root>/projects/<slug>/data.sqlite, not a p_<slug> schema, and its schema_migrations ledger lives inside that file. Two projects can still both own a tasks table; they share nothing at all now.
• The runner is ordering, a ledger, a checksum and one transaction per migration. There is no lock: each project file has one writer, the user's own service.
• The files still live in the owning repo, and travel in the user's release as projects/<slug>/migrations. They run when the user's service host starts, as the user's uid, before anything is served -- which is still a deploy step (a release restarts the unit), never a request -- so every file they create is the user's from its first byte (ADR-0009).
• The platform's own control database uses the same runner, from api/db/control/, run by the reconciler before the API starts. Its checksum is why an applied migration, comments included, must never be edited.