Migrations

Each layer keeps its own ordered list of migration files. An admin applies the pending files from the admin UI.

How it works

• A layer keeps its migrations in db/migrations/, as 001_baseline.sql, 002_admin_hardening.sql, and so on.
• The table public.pluto_migrations records one row for each applied file. The ledger key of @plutocms/supabase is core. The other layers use their package name without the scope.
• When DATABASE_URL is set, the server also applies the pending files of every layer when it starts.
• In development, after the server applies a new file, it regenerates shared/types/supabase.ts from the live schema.
• A banner in the admin tells you when a layer has pending migrations. Go to Migrations opens /admin/migrations.
• The page lists the pending layers and the applied layers.

Run the migrations

POST /api/_pluto/supabase/migrations/run applies every pending migration file across the layers. Only an admin can call it. The route checks profiles.is_admin directly, so it needs no migration to work.

Connection string

The route picks the connection string in this order:

1. process.env.DATABASE_URL, if it is set. A string in the request body is then ignored.
2. Otherwise, the connectionString in the request body.
3. Otherwise, the route fails with HTTP 400 and the key supabase.errors.connectionStringRequired. The error data has needsConnectionString: true.

GET /api/_pluto/supabase/migrations returns the status of the migrations.

A failed run is an HTTP 500 error with the key supabase.errors.migrationsFailed. It is not a 200 with success: false.

Rule 1 is the security model of the route. It stops an admin from pointing the server at another database host.

After a successful run, the server writes DATABASE_URL to .env. If the write fails, the migrations still succeed. The response reports persisted: false, and the page shows a step-by-step manual fix with the DATABASE_URL="..." line to copy.

Per-file transactions

The runner applies each file on its own.

• All statements of a file run in one transaction, together with the ledger row.
• If a statement fails, the whole file rolls back. Nothing from it is applied.
• A failure in one file stops the rest of that layer, because a later file often depends on an earlier one. Those files report Skipped: an earlier migration in this layer failed.
• A failure never blocks another layer. Each layer runs on its own.

Files that cannot run in a transaction

A statement such as create index concurrently cannot run in a transaction. Make -- pluto:no-transaction the first non-blank line of the file. The runner then runs the statements one at a time and records the ledger row after. A failure can leave the file partly applied, so write such a file with if not exists and on conflict guards.

Checksums

The runner records a checksum for each file (the first 16 hex characters of a SHA-256 digest). On a later boot, if an applied file has a different checksum, the runner logs a warning with the layer and the file name. It does not fail and does not run the file again. This warns you that someone edited a shipped migration.

Write a migration

Add a new file to db/migrations/ of your layer.

• Name it NNN_slug.sql. NNN is the next zero-padded number.
• Never edit a file that has shipped or that any database has applied.
• Never renumber a file. The number is part of its identity in the ledger.
• 001_baseline.sql is a reserved name. Do not reuse it.
• Write the file so it is safe to run on a database that has every earlier file applied.
• Use if not exists, on conflict, and drop ... if exists guards where they make sense.
• A migration that creates a table must grant it. See Data API grants.

Dev server restart

In development, writing .env restarts the Nuxt dev server. The HTTP response can be cut off even when the migration succeeded. In this case, the page shows a "reconnecting" toast. It checks the server every two seconds, for up to thirty seconds.

Migrations in the shipped layers