Product migrations

A product whose profile entry declares an install list ships as migration files that Blueprint generates and applies. blueprint db migration new cuts them, blueprint db reset replays them locally (and installs a product that is absent), and blueprint db migration up deploys them. A product is declared only by its databases.<product>.install in the profile; Blueprint no longer installs products from supabase/config.toml's sql_paths.

The product ledger

Blueprint records every migration file it applies in its own table, blueprint_migrations.product_migrations, one row per product and version (the file's 14-digit stamp), with the rest of the file name, each statement as written, and a checksum of those statements:

CREATE TABLE blueprint_migrations.product_migrations (
  product    text NOT NULL,
  version    text NOT NULL,
  name       text NOT NULL,
  statements text[] NOT NULL,
  checksum   text NOT NULL,
  applied_at timestamptz NOT NULL DEFAULT clock_timestamp(),
  PRIMARY KEY (product, version)
);

The table is created the first time a command needs it. It lives in the blueprint_migrations schema, outside every product schema, so a reset does not drop it and a snapshot does not capture it.

The Supabase CLI keeps one history for a whole database (supabase_migrations.schema_migrations, keyed by version alone), so two repositories cannot both supabase db push into one database. With a row per product, each repository deploys its own products with blueprint db migration up and never reads or writes the other's rows, or the Supabase CLI's history.

Command	What it does with the ledger
db migration up	Applies the files with no row, and adds a row for each
db reset	Deletes the product's rows, replays every file, adds a row for each
db reset --from-tree	Deletes the product's rows and adds none
db migration adopt	Moves the product's rows in from the Supabase CLI's history, once
db migration squash	Writes a repair that swaps the old rows for the baseline's
db migration new, db status	Do not read it

The files stay in supabase/migrations/. A Supabase CLI that reads that folder (supabase db reset, supabase migration up, supabase db push) also applies them, and records them in its own history, not in the ledger. Use one tool per database for a product's files: where the Supabase CLI applied them, run blueprint db migration adopt before the next migration up.

Declare the product's SQL

List the product's SQL in the development profile (blueprint.dev.json):

{
  "databases": {
    "plant": {
      "command": ["dart", "run", "bin/blueprint.dart", "plant"],
      "install": [
        "supabase/schemas/plant/00_base/*.sql",
        "supabase/schemas/plant/*/*.sql",
        "supabase/seed/plant/reference/*.sql"
      ],
      "provision": ["supabase/seed/plant/provision/*.sql"]
    }
  }
}

• install is the ordered globs for schemas and reference seeds. They go into migrations.
• provision is the ordered globs for per-tenant defaults. They run after the migrations on reset, and are never part of a migration. provision requires install.
• Globs are relative to the profile directory and expand as supabase db reset expands sql_paths, each file once. Unknown keys, an empty install, and absolute globs are profile errors (exit 64).
• dependents (optional) lists the triggers, policies and functions the product owns in schemas it does not own: schema.table.name for a trigger or policy, schema.function(argtypes) for a function, one key per overload, such as sso.users.trg_plant_user_sync or sso.plant_sync_user(uuid, text). They are captured and planned with the product. dependents requires install, and a key in a schema the product owns is a profile error.
• command is the product's Blueprint target command. db emit runs it, and migration new runs it for the product's definition and its migration steps (see The definition a migration is cut from).

One owner per file. A product that declares install must not be installed from supabase/config.toml too. Its [db.seed] sql_paths may list only its provision files. Any other file under schemas/<NN>_<product>/ or seed/<product>/ (or in the install list) that sql_paths selects is a configuration error (exit 64) naming the entries.

Seed contributions may sit in one subfolder of seed/<product>/, such as reference/ and provision/. blueprint db emit manages header-marked files there, one level deep, as it does at the top of seed/<product>/.

The database a command works on

Every blueprint db command that touches a database works on one target and prints it first, on the error stream, without its password:

Target: postgresql://postgres:***@127.0.0.1:54322/postgres (from supabase_db_plant)

The target is --database-url URI, else the variable --database-env names (default BLUEPRINT_DATABASE_URL), else the checkout's local Supabase database. status, reset, migration new and migration squash all resolve it this way, and reach it over a connection, not through docker exec.

• reset follows BLUEPRINT_DATABASE_URL. With the variable set it drops and replays the database it names, not the local Supabase container. Read the Target: line.
• A host that is not local (a loopback address, localhost, host.docker.internal or a single-label Docker service name) needs --allow-remote. reset writes, so for it a single-label name such as db or prod-db needs it too, as for migration up. migration new and migration squash take local targets only.
• reset and migration squash refuse (exit 65), changing nothing, when --database-url and the variable name different databases.
• provisionCommands receive the resolved target in BLUEPRINT_DATABASE_URL.

migration up and migration adopt resolve the variable, else the local Supabase database, take no --database-url, and name the target as host:port/database (see Deploy).

blueprint db migration new

blueprint db migration new baseline --product plant   # the first one
blueprint db migration new add_notes --product plant  # later: a delta
blueprint db migration new --check --product plant    # is one needed?

It writes supabase/migrations/<yyyyMMddHHmmss>_<product>_<name>.sql, headed -- GENERATED from <product> blueprint migration <name>. Do not edit. The timestamp is the current UTC time, or one second after the newest migration when that would not sort last. --product may be omitted when the profile has one migrated product.

Baseline and delta

• Baseline (the first migration): the install list in order, each file after a -- Source: <path> header. Provision files are never included.
• Delta: the structure change the migration planner finds between the stored snapshot and the current structure, carrying the definition changes since the previous revision (see The definition a migration is cut from), then, in install order, each replayed whole: changed reference seeds (seed/<product>/), the runtime contract (90_runtime_contract/) when any of its files changed, and changed hand-written files marked -- blueprint: replayable whose objects the plan does not change.
• With nothing changed, nothing is written.

The definition a migration is cut from

Every cut asks the product's own command for its Blueprint definition (<databases.<product>.command> emit --out <tmp> --schema-dir <NN_product> --definition-out <file>) and stores it as supabase/blueprint/<product>/definition.json. A command that fails or writes none stops the cut with exit 70; one that emits another Blueprint's definition stops it with exit 65. emit --definition-out and migration-steps come with vyuh_blueprint_server 0.24.0, so the product's server package must be on 0.24.0 or later before its next cut. --check does not run the command.

When the structure changed and the previous cut stored a definition, the delta is planned from the two definitions, not from the two snapshots alone:

• Every change between them is detected and composed into one ordered plan. A renamed entity or field becomes ALTER ... RENAME, with every derived name (facet, audit, versions and revisions tables, constraints, indexes, triggers, policies) renamed with it, so rows and ids are kept. A conversion, backfill or label map runs in place of a drop and a create.
• Changes that need an answer are asked once. A rename to confirm, a backfill or conversion (a literal, a field, the column as it is, or a case map; each is a vyuh.expression document, MigrationValues declares them), a label map for a removed enum label or lifecycle state, a redirect for a retargeted reference, and a removal to consent to are asked on a terminal, or read from --decisions FILE (JSON or YAML) and --accept-renames[=old->new,...]. --allow-destructive consents to every removal. Without a terminal, a missing answer exits 65 and lists each one; so does an answer no change uses. A change plan refuses the rows a case map does not list unless it names an otherwise (else <value> at the prompt; else null writes NULL); a data step keeps them. A fractional literal for a whole-number column is refused, not rounded. A decisions.json of format version 1, or a value in the earlier {"kind": ...} shape, is refused; bun tools/migrate-migration-values.ts rewrites it.
• The answers are written to supabase/blueprint/<product>/decisions.json, keyed by the migration file, in the same atomic write as the migration. Answers of migrations no longer in supabase/migrations/ are dropped.
• Packages that store definition names rewrite them in the same transaction. The plan is handed to the product's command (migration-steps --plan <file> --schema <product> --out <file>), which returns the data steps of the migration participants the target lists in its databaseContributions: IamModule.migrationParticipant (action codes and resource types), the policy module's (policy layers of a renamed or removed entity) and the workflow module's (runs of a renamed entity). A participant a target does not list rewrites nothing. A refusal by a participant exits 65; a product command without migration-steps exits 70. The plan file carries format vyuh.blueprint.change-plan/v2 and the steps file vyuh.blueprint.migration-steps/v2; a file without its format, or with another, is refused (exit 65 for a plan, 70 for steps), and bun tools/migrate-migration-values.ts adds the marker.
• An entity that becomes governed adopts its records. Going from History.versions() to History.revisions() adds the revision tables and the effective revision pointer, and makes every existing record its revision 1 (create), effective at the version it holds, with every version it has. A record that holds no version at its own version stops the migration. The answer to the versioning path may name a boolean field of the entity (a field reference); a record whose latest version held false there is adopted retired, so an is_active the revisions replace can be removed in the same migration.
• A change the planner cannot carry out yet (a field moved between facets, a cardinality change, a move to another schema) exits 65 naming its path, and nothing is written.
• A definition change with no structure change writes no migration and only refreshes definition.json.

State cut by an earlier Blueprint has no definition.json. Its snapshot.json and files.json stay valid as they are. The first cut after the upgrade is planned from the snapshots alone, as before, and stores the definition; --decisions and --accept-renames are usage errors (exit 64) until then, and a rename in that one delta is a drop and a create. To record the definition without a structure change, run blueprint db migration new <name> before changing the model: it writes no migration and adds definition.json.

The runtime contract migrates exactly, keeping one revision back. Its rows are inserts that skip a row already held, so replaying them would keep a changed row's old content and every row the Blueprint retired. A delta instead stages every current contract file and, for every Blueprint the files name, keeps two revisions:

• the target, the files' revision, made exact: changed rows are updated and rows it no longer holds are deleted;
• the previous revision, the other revision of that Blueprint that was most recently the target, kept whole, so instances still running it keep working. A delta stamps its target's installed_at with the time it became the target, also when an older revision becomes the target again, so after a rollback (A, B, then A again) the next delta keeps A, the revision that was live, and deletes B.

Every older revision is deleted. A migrated database thus holds a fresh install's contract for the target, plus the revision before it. When the Blueprint version does not change and the revision id does not either (the 0.14 line keys a revision by <name>:<version>), the target is the same revision, made exact in place.

The rows to delete are locked first, so a reference made while the delta runs waits for it. Then, if anything still references a row the delta would delete (an archive, a migration plan, your own foreign key in any schema, even one with ON DELETE CASCADE), the delta fails with foreign_key_violation, names each referencing table and constraint, and changes nothing: move the reference to a kept revision first. The tables' immutability triggers are held for that transaction only and get back the mode they had (ENABLE, ENABLE ALWAYS or ENABLE REPLICA).

Deploy order. Migrate first, then roll the servers:

1. Apply the delta. The new revision is installed and the running servers' revision stays, as the previous one.
2. Roll out the new servers. Old and new instances run side by side, and an old instance that restarts still finds its revision.
3. To roll back one step, redeploy the previous servers; their revision is still installed. Do not roll the database back.

One contract delta at a time. Contract deltas of a product are meant to run one after another, as blueprint db migration up and blueprint db reset run them; never run two concurrently against one database. Inside a delta, the rows to delete are locked table by table, blueprint_revisions first and then the tables that reference it (blueprint_revision_refs, blueprint_manifests, entity_projections, projection_sources, action_capabilities, app_ui, policy_artifacts), each in key order, so two deltas that did overlap would wait for each other rather than deadlock.

Finish a rollout before applying the next contract delta: that delta keeps only the revision before it, so instances two revisions behind lose their contract and refuse to start (blueprint.contract.revision_not_installed).

Display text is not part of the contract. The revision digest and the contract rows leave out labels, titles, descriptions, help and icons (any of those keys whose value is text; data such as default values and payload mappings is kept whole). Clients read display text from the definition the server serves, never from the contract rows. A label-only change emits identical contract files, so it needs no delta, and a server built with it starts on the database as it is.

blueprint dev installs a missing contract locally. Before it starts servers, blueprint dev runs blueprint db emit and asks a loopback database for the build's revision. When it is missing, the database matches the stored snapshot and no install file but the contract changed since the last migration, dev inserts the contract rows from the emitted files, as a fresh install does, and reminds you to cut a migration. Otherwise it prints one line naming migration new, migration up or reset and starts no server. Deployed servers never run through blueprint dev and keep refusing.

File storage buckets migrate exactly, by name. A Blueprint that declares storage buckets emits five tables (storage_buckets, storage_upload_sessions, storage_objects, storage_object_events, storage_derivatives) with the runtime DDL, and one more contract file, 90_runtime_contract/500_storage_buckets.sql, with a row per bucket. A product without buckets gets none of these. The rows are not revision-keyed: a bucket is identified by its name, which storage_objects points at, and a rolling deploy sees a changed limit at once. A delta that carries the file keeps storage_buckets exact for the Blueprints the file names:

• Adding a bucket to a product that has none creates the tables and inserts the row; to a product that has some, inserts the row.
• Changing a limit, the MIME list, the access or the processors updates the row. A change to the title alone changes no row; the title is not stored.
• Removing a bucket deletes its row, unless an object still names it. The delta is then refused, naming the bucket and the count (foreign_key_violation, "cannot be removed while it still has objects"), and nothing changes. Soft-deleted and purged objects count, because their rows stay.
• Removing the last bucket drops the five tables, which is destructive: cut the migration with --allow-destructive. Applying it is refused while storage_object_events or storage_upload_sessions hold rows, with their counts; the owner archives the audit, disables the storage_object_events_immutable trigger, clears the rows, and applies again.
• A bucket name belongs to one Blueprint per runtime schema. A second Blueprint that declares a name already held in the same runtime schema is refused (23505), naming both.

Moving a bucket to another module of the same Blueprint is not a change.

Reference seeds are replayed as written: their conflict handling (DO NOTHING or DO UPDATE) is the seed's own decision, and rows a seed no longer inserts are not deleted.

Each file is one transaction of its own: BEGIN; after its header comments and COMMIT; at its end. migration up and reset run the statements between them in a transaction that also writes the file's ledger row, and record the file as written. The Supabase CLI sends a migration file's statements per statement or batched, never inside an explicit transaction block, where LOCK TABLE is refused (25P01) and SET LOCAL does nothing, so the file's own BEGIN and COMMIT also let a plain supabase db reset apply it as written. Every statement a cut can hold runs inside a transaction: the capture runs each install file inside one, so a file with CREATE INDEX CONCURRENTLY, VACUUM or its own transaction control fails there and no migration is written.

Enum labels get their own file, <ts>_<product>_<name>_enums.sql, written one second before the delta. Each file is committed before the next one runs, and PostgreSQL lets a new label be used only after the transaction that added it commits.

Destructive changes (drops and other operations that destroy data) are written only when a change accounts for each one:

• With a stored definition, each destructive operation must trace to a change of the plan: a removal you consented to (on the terminal, in --decisions, or with --allow-destructive) or a conversion you decided. An operation no change accounts for (the database drifted from the previous revision, or no decision covers it) is refused by name, whatever the flags (exit 65).
• --allow-destructive also covers operations on objects no definition derives (hand-written ones).
• Without a stored definition, --allow-destructive allows every destructive operation, as before, and prints a warning that renames were not detected. Without the flag the command exits 65 and says how to record the definition first.

How the current state is captured

The command captures the current structure on the local database (BLUEPRINT_DATABASE_URL when it is local, else the local Supabase database), inside one transaction that is always rolled back:

1. the product's owned schemas are dropped,
2. the install list runs file by file,
3. the owned schemas are captured, and the transaction rolls back.

Nothing is committed, and a killed run leaves nothing behind. Limits:

• The local database must be current. The install runs against what else is in it, so reset it from its migrations first (supabase db reset).
• It takes brief locks on the product's schemas while it runs.
• Sequence bumps are not transactional. PostgreSQL does not roll back nextval, so sequences the install touches may advance.
• The drop is refused while other schemas depend on the product's (as db reset refuses). --force drops those too, inside the same rolled-back transaction.
• --check also needs the local database. It runs locally, not in CI.

Planned and unplanned statements

Only captured structure in the product's own schemas is planned: tables (with their storage options, UNLOGGED, INHERITS (linked and unlinked in place between tables of the product), identity columns, a replica identity of DEFAULT, FULL or NOTHING, and column collations), partitioned tables and their partitions (PARTITION BY, PARTITION OF), views, materialized views, functions, procedures, aggregates, sequences, indexes, enum types, domains, composite types (CREATE TYPE … AS (…)), policies, triggers (with the enablement of each one by name, ALTER TABLE … ENABLE|DISABLE [REPLICA|ALWAYS] TRIGGER t), rewrite rules (CREATE RULE, DROP RULE, ALTER TABLE … ENABLE|DISABLE RULE), grants, comments and owners of any of these, the default privileges of the schemas (ALTER DEFAULT PRIVILEGES … IN SCHEMA), and extensions installed into one of the schemas (CREATE EXTENSION … SCHEMA <owned>), and with them the triggers, policies and functions the profile lists in databases.<product>.dependents. A dependent listed for the first time is preceded by DROP ... IF EXISTS, so one that is already live is replaced; a changed function body is CREATE OR REPLACE; removing a listed dependent outright needs --allow-destructive. Everything else is unplanned: extensions put anywhere else or pinned to a version, comments on anything else, default privileges without IN SCHEMA or in a schema the product does not own, publications, ENABLE|DISABLE TRIGGER ALL|USER (a snapshot records each user trigger by name; ALL reaches the internal ones, and either form would miss a trigger added later), an ON SELECT rule (it makes a view of a table), a replica identity USING INDEX, an INHERIT of a parent outside the product, ATTACH PARTITION and DETACH PARTITION (they move a table in or out of a partition set, with its rows), column storage and statistics, REFRESH MATERIALIZED VIEW (it loads rows, which no plan does), a materialized view's tablespace, clustering and column statistics, data, DO blocks, CALL, roles, ownership of anything else (an index, a ROUTINE, the database, a statement that mixes an owner change with other actions, CREATE SCHEMA … AUTHORIZATION), DDL on other schemas (such as a trigger on sso.users), and any unknown statement.

State a snapshot cannot hold is a blocker, named in the refusal, not a silent omission: a replica identity USING INDEX (the snapshot does not record which index), a non-heap access method, a tablespace, a typed table, an unlogged sequence, a column's storage, compression, statistics target or options, extended statistics, and the relations and types already listed as unsupported (foreign tables, range and multirange types), what a snapshot cannot hold of a partition (see Partitioned tables), and an aggregate with a moving-aggregate option, a sort operator or direct arguments (ordered-set and hypothetical-set aggregates). A link to a parent outside the product's schemas is a blocker when it is added or removed: its columns and checks are not in the snapshot. An unchanged one blocks nothing. What an extension installs belongs to the extension and is left out of the snapshot.

The rule is inverted from what you might expect: the planner cannot carry an unplanned statement, so a change to one is refused, not silently dropped. files.json records each install file's hash and the kinds of its unplanned statements. The command exits 65, naming the file and the kinds, when

• a changed file's unplanned statements changed (unless the file is replayed), or
• a file removed from the install list had any (removing it undoes none of them).

To proceed:

1. Write that part as your own migration file, with any name that sorts before the next generated one.
2. Rerun with --acknowledge-unplanned.

The planned part is written as usual, each acknowledged file is listed (and noted in the delta's header), and the state records the statements as migrated, so the next run reports no changes.

-- blueprint: replayable marks a hand-written file that is safe to run again. A changed marked file is replayed whole in every delta instead of being refused.

Owners

A single ALTER <kind> … OWNER TO <role> on a schema (other than public), table, view, materialized view, standalone sequence, function, procedure, aggregate, enum type, composite type or domain of the product's schemas is planned. The snapshot lists an owner (owners: key, target, role) only when it is not the role that captures: an object that role owns adds nothing, so a schema without other owners keeps the hash it had. The public schema owned by pg_database_owner, the sequence a serial or identity column owns (it follows its table, and PostgreSQL refuses to change its owner alone), and the members of an extension are never listed. The owner of a composite type is listed with its TYPE target; the row type of a table follows its table.

• Statement. ALTER … OWNER TO "role" for an owner the target has and the source lacks or has differently, and again for any object the plan touches: a view or function the plan drops and creates anew starts owned by the role that runs the migration. An owner the target no longer lists, on an object that stays, is OWNER TO CURRENT_USER. The statement is idempotent, so a stored snapshot that predates owner capture applies to a database that already has the owners.
• Order. Owners come after every object exists and before privileges. ALTER … OWNER rewrites the object's ACL, so a GRANT to the previous owner that ran first would be merged into that owner's entry and lost. A REVOKE from the role that owns the object in the target is left out: the snapshot never lists an owner's own privileges, so the revoke would strip the new owner of them. When an owner is reset to the current user, the revoke runs only for another role. A sequence a serial or identity column owns follows its table's owner, so the same holds for the revokes on it: the table's owner keeps its sequence privileges.
• Roles are recorded by name: a role the migration names must exist where it is applied.

Default privileges

ALTER DEFAULT PRIVILEGES [FOR ROLE r] IN SCHEMA s GRANT|REVOKE … ON TABLES | SEQUENCES | FUNCTIONS | TYPES is planned when every schema is one of the product's. Without IN SCHEMA (it changes the role's global defaults), for several roles, on SCHEMAS or ROUTINES, it stays unplanned.

The snapshot lists them (default_privileges: key <schema>:<role|*>:<objtype>:<grantee>:<privilege>, grantable) only when a schema has some, so a schema without any keeps the hash it had. PostgreSQL starts a schema-level entry empty, so an entry lists exactly what was granted; the role's own entry is left out, as an owner's own privileges are left out of a grant list. role is absent when it is the role that captures, so the snapshot does not depend on who captured it.

• Statement. GRANT for a new entry, REVOKE for a removed one, REVOKE GRANT OPTION FOR or … WITH GRANT OPTION for a grant option that changes. An entry both snapshots hold is not restated. A schema that leaves the product takes its default ACLs with it.
• Order. Defaults run right after the schemas are created, before any object: PostgreSQL applies a default when an object is created, so the objects the plan creates see the target's defaults (only those of the role that runs the plan; a default FOR ROLE another role does not reach them). The target may have been installed in either order, so each created table, view, sequence and function (a serial or identity sequence comes with its table) has what the defaults gave it settled before grants are restated: what the target does not list is revoked (the role that owns the object in the target is left alone), and what it lists is granted. An existing object is never touched by a default. Types are not tracked (their ACLs are not in the snapshot).
• Source captured earlier. A stored snapshot made before defaults were captured lists the schema as unsupported (default-privileges). It passes when the target states defaults for that schema (or the schema leaves the product); otherwise the plan is refused, naming the schema: the live default ACLs are unknown to the ledger, so state them in the target. When it passes, the plan restates every default (idempotent) and then checks that the database holds exactly the target's; another entry makes the migration fail with the entries named, rolling everything back.
• Roles are recorded by name; a role the migration names must exist where it is applied, or the migration fails as a whole.

Composite types

A standalone composite type (CREATE TYPE s.t AS (a int, b text)) is captured as composites: key schema.name and its attributes in declaration order (a row is built by position), each with its type as format_type writes it and its collation when it differs from the type's. The row type of a table or a view is not a standalone type and is never listed; neither is a composite an extension made. A schema without composite types has no composites key, so it keeps the hash it had. A composite's comment and the comment of an attribute (COMMENT ON TYPE, COMMENT ON COLUMN s.t.attr) and its owner are captured with it; privileges on types (GRANT USAGE ON TYPE) are not, so such a statement stays unplanned.

• Create. Composites are created after the enums and domains they are built over and before the tables, views and functions that use them, each after the composite it is built over. CREATE TYPE has no IF NOT EXISTS, so it is a DO block: it creates a missing type, leaves one with the planned attributes, and raises when a type of that name exists with other attributes (schema qualifiers are ignored when comparing). A stored snapshot from before composites were captured lists each as unsupported (type:c); that is not a reason to refuse the plan when the target has the same composite, and the guard then covers a type that is already live. A stored type:c the target does not have stays a blocker, naming the type.
• Alter in place. ADD ATTRIBUTE appends one; DROP ATTRIBUTE removes one and is destructive (its values go); ALTER ATTRIBUTE … TYPE retypes one. A rename is a drop and an add (destructive), as no intent names it. PostgreSQL can only append, so a reorder, or an attribute added before an existing one, is a blocker.
• Retyping an attribute is refused by PostgreSQL while a table column stores the composite, directly, in an array, inside another composite or through a domain: a blocker naming the type and the column (a column dropped in the same plan still counts; split the migration). It is also refused while a view, a policy, a trigger, a column default or a generated column, or a function with a SQL-standard body (BEGIN ATOMIC, RETURN) depends on the attribute. A snapshot records no dependencies, so the planner finds them by name: whatever mentions the composite, a composite or domain that holds it (however deep), or a function whose arguments or result are of one of those, is dropped before the change and created again after, the way a restructured table's dependents are (a default or a generated column is released and put back). The rule errs toward rebuilding. A function with a standard body cannot be rebuilt that way: the plan is refused, naming it and the composite (such views or functions must be dropped and recreated by hand).
• Dropping an attribute goes last by default: after the functions, defaults, generated columns and other readers the target changed or removed to stop reading it (a snapshot records no dependencies, and a reader may reach the attribute through a table column or a table's row type), but before the type the attribute is built over is dropped. PostgreSQL also checks a row constructor against the attributes the type has when the object is created, so when the target adds or changes a column default or a standard-body function that builds a value of the type (ROW(..)::t, or a record literal for a column of the type), the attribute goes first. When that happens in a plan where a view, policy, trigger, index, constraint, column default or standard-body function that mentions the attribute and the composite (a text rule) is also changed or removed, no order works and the plan is refused, naming the attribute and the object that builds the type: split it into two migrations, first changing what reads the attribute, then dropping it. (An object the target keeps that still reads the attribute is not checked here: PostgreSQL rejects it when the target is installed.)
• Drop. A removed composite is dropped (destructive) after the tables and after the functions that take or return it, and before the enum or domain it is built over. A composite that holds the row type of a table the plan creates or removes, and a domain over a composite the plan creates or removes, are blockers: the types cannot be ordered with the table or domain. Replacing an enum (a label mapping) an attribute is built over is a blocker.
• Decorations. The owner and comments follow the usual order (owners before grants, comments last). An attribute's comment goes with the attribute; its owner is the type's.

Procedures and aggregates

A stored procedure is captured as procedures: key schema.name(identity arguments) (the OUT and INOUT parameters that identify a procedure are part of it), schema, name, arguments and the definition pg_get_functiondef writes, a CREATE OR REPLACE PROCEDURE. An aggregate is captured as aggregates, with the same key and its options: sfunc, stype, sspace, finalfunc, finalfunc_extra, finalfunc_modify, combinefunc, serialfunc, deserialfunc, initcond and parallel. Every function an aggregate names is "schema"."name", quoted and qualified from the catalog, so a capture does not depend on the search path of its session. Only the product's schemas are read, and what an extension installed is left out. A schema without procedures or aggregates has neither key, so it keeps the hash it had. An aggregate with a moving-aggregate option, a sort operator or direct arguments (an ordered-set or hypothetical-set aggregate) is not listed: it is unsupported (aggregate-option) and a plan around it is refused, naming the aggregate.

Comments (COMMENT ON PROCEDURE, COMMENT ON AGGREGATE), owners (ALTER PROCEDURE|AGGREGATE … OWNER TO) and privileges are captured with them. GRANT … ON PROCEDURE is the clause for a procedure; PostgreSQL grants on an aggregate with ON FUNCTION. A zero-argument aggregate is named (*) in DROP, ALTER and COMMENT, and () in GRANT and REVOKE. A new procedure or aggregate is closed to PUBLIC like a new function, and the target's own privileges are stated after; a default privilege ON FUNCTIONS reaches procedures and aggregates too, so one the plan creates is settled to the privileges the target records for it.

• Procedure. A changed definition is CREATE OR REPLACE PROCEDURE; a changed argument list or parameter name is another key, so the old one is dropped (DROP PROCEDURE, destructive) and the new one created. A procedure that loses a parameter default is dropped and created again too, because OR REPLACE cannot remove one. A function that becomes a procedure or an aggregate of the same name (or the other way round) is dropped first: PostgreSQL will not change a routine's kind in place.
• Aggregate. A changed aggregate is DROP AGGREGATE (destructive) and CREATE OR REPLACE AGGREGATE. The create is OR REPLACE so that stating an aggregate the database already has (a stored snapshot from before aggregates were captured) is safe: identical options change nothing, a different INITCOND, PARALLEL or support function is updated in place, and a different state type or result type fails loudly (PostgreSQL refuses it), rolling the migration back.
• Order. Procedures and aggregates are dropped first, before the tables change and before the types and functions they use are altered or dropped (a procedure before an aggregate, which it may read). They are created last, after every function, table and type the plan makes, because an aggregate needs its support functions and a procedure may take a table's row type or a domain or composite type of the target.
• Rebuilt around a change. A support function the plan drops and creates again (its result type or parameter list changed) takes the aggregates that name it down and brings them back, although their own definition is the same. A view or a policy that calls an aggregate the plan drops is rebuilt around it; nothing else can call one. A procedure with a SQL-standard body (BEGIN ATOMIC) depends on what it reads, so one that mentions a table, a type or a function the plan rebuilds, or calls an aggregate it drops, is dropped first and created again after. A function with a standard body that calls an aggregate the plan drops or creates cannot be rebuilt that way: the plan is refused, naming the function and the aggregate (change the function in one migration and the aggregate in the next). A procedure with a string body records no dependency, and is replaced only when it changed.
• Enums. Replacing an enum type (a label mapping) that a procedure's or an aggregate's signature or state type uses is a blocker, as it is for a function.
• Decorations. Owners come before privileges and comments last, as for every object. A routine the plan drops and creates again is given its owner, privileges and comment again.

Rewrite rules

A rewrite rule (CREATE RULE name AS ON INSERT|UPDATE|DELETE TO table [WHERE …] DO [ALSO|INSTEAD] …) on a table or a view of the product's schemas is captured as rules: key schema.table.name, schema, table_name, name, the definition pg_get_ruledef writes, and enabled, PostgreSQL's ev_enabled: O (the default), D (disabled), R (replica sessions only) or A (always). The _RETURN rule PostgreSQL makes for every view is the view's definition, captured with the view, and never listed. A rule on a partitioned table, a partition or a foreign table is not captured (those are unsupported), and PostgreSQL has no rule on a materialized view; what an extension installed is left out. A schema without rules has no rules key, so it keeps the hash it had. A comment on a rule (COMMENT ON RULE r ON t) is captured with it.

• Statement. A rule is created, and a changed rule replaced in place, by CREATE OR REPLACE RULE (event and body can change; the comment and the enablement are kept). Because it is idempotent it also states a rule the database already has: a stored snapshot from before rules were captured, applied to a database whose rules were made by hand, leaves the rule as the target describes it.
• Enablement. ALTER TABLE t ENABLE|DISABLE|ENABLE REPLICA|ENABLE ALWAYS RULE r moves a rule whose state differs, once every rule exists; a state change alone never rebuilds the rule. A rule the plan creates, or drops and creates again, starts enabled. A source that has no rules key may be describing a database whose rules were made by hand in any state, so every rule the plan states from such a source is followed by its state, the default included (ENABLE RULE). PostgreSQL has no enablement for a rule on a view: they are always enabled and never given the statement.
• Dropping. DROP RULE r ON t (destructive) removes a rule the target no longer has. A rule goes with its table or view; the plan states nothing of its own for it.
• Order. A rule that has to go goes first, before the views are dropped and the tables change, and a rule is created or replaced last of the structure, after the tables, views, functions and types it reads, and after the views it may sit on. PostgreSQL refuses to retype or drop a column a rule reads, and to drop a table, view, function, type or sequence a rule uses, so a rule is dropped first and created again after when it sits on a table whose columns are restructured, or when its text mentions a table or view the plan removes or rebuilds, a function, aggregate, type, sequence or enum it removes or recreates, an attribute of a composite type it drops, or a name the plan rebuilds dependents around (the rule errs toward rebuilding). A rule that only changed is replaced in place. A rule on a view the plan rebuilds goes with the view and is created again after it; a rule on a table the plan renames by intent follows it, keeping its name and its state. A rule renamed in the target is a drop and a create.
• Decorations. A comment on a rule is set again when the rule was dropped and created again; a rule has no owner or privileges.
• Statements. CREATE [OR REPLACE] RULE for INSERT, UPDATE or DELETE, DROP RULE, ALTER RULE … RENAME and COMMENT ON RULE on an owned table or view are planned; the same on a table outside the product are reported. ALTER TABLE t ENABLE|DISABLE [REPLICA|ALWAYS] RULE r is planned for an owned table, as it is captured. A rule with several actions (DO ALSO (a; b)) holds a semicolon inside its parentheses. The splitter that classifies a migration file cuts at every semicolon outside quotes, so the later actions are reported as an unplanned data statement and the cut is refused until you pass --acknowledge-unplanned. Nothing is lost: the rule itself is captured whole and planned from the snapshot. To avoid the refusal, write the rule with one action.

Materialized views

A materialized view of the product's schemas is captured with the views, in views, with materialized: true on its record and on no other: key, schema, name, the definition pg_get_viewdef writes, its storage parameters as options (fillfactor …) and the dependencies it has on views and other materialized views. A schema without a materialized view has no such field, so its views, and its hash, are unchanged. Whether the view holds rows is runtime state and is not captured: a plan makes every materialized view WITH DATA and never refreshes one. Its indexes are captured with the other indexes; its owner, comments (the view and its columns) and privileges are captured like those of a table (GRANT … ON TABLE).

• Statement. CREATE MATERIALIZED VIEW s.n [WITH (…)] AS <query> WITH DATA. There is no CREATE OR REPLACE, so a changed query or storage parameter is DROP MATERIALIZED VIEW then CREATE, and so is a view that becomes materialized, or stops being (the drop is the one of what it was). REFRESH MATERIALIZED VIEW is not a plan's job; a REFRESH in a migration file is reported as an unplanned statement. Seeds run after the structure, so a materialized view over a table the same migration seeds is filled before those rows exist: refresh it where that matters.
• Destructive. A DROP MATERIALIZED VIEW is destructive (--allow-destructive), whether the view goes or only changes: it destroys the rows it holds, and the new one is filled from the tables as they are when the plan runs. A plain view's drop is not.
• Order. Materialized views are ordered with the views: one that reads a view or another materialized view is created after it and dropped before it, and is rebuilt when the view it reads is, when a table it reads is restructured (a column retyped, dropped or renamed; a view names the table, so any column dropped from it rebuilds the view, whether it reads that column or not) or when a function it calls is recreated. Its indexes go with it when it is dropped, with no DROP INDEX of their own, and are created again right after it. Owners follow, then privileges, comments last, as for every object.
• Defaults. A default privilege ON TABLES reaches a materialized view the plan creates, which is then settled to the privileges the target records for it, as a table is.
• First cut. An older release listed a materialized view as unsupported (relation:m), so no product has a migration cut with one; but a stored source made before may still carry the entry, and the database may already have the view (made by hand). The entry is retired for the source when the target holds that materialized view; else the plan is refused, naming it. The CREATE of such a view is a DO block that does nothing when a relation of the name exists and raises when it is not this materialized view: it compares the kind of relation and the query as pg_get_viewdef prints it (schema qualifiers dropped, white space folded). It does not compare storage parameters, whether the view holds rows, or its indexes and privileges. Every other materialized view is created plainly.
• Blockers. A materialized view's non-default access method, tablespace or column storage, compression, statistics target or options are unsupported state, as for a table.
• Statements. CREATE, DROP, COMMENT ON, OWNER TO, one RENAME (the view or a column; the view is dropped and created again under its new name, destructively), SET SCHEMA, and SET/RESET of its storage parameters, on an owned materialized view, are planned.

Partitioned tables

A partitioned table is a record of tables with partition_key, the text pg_get_partkeydef prints (RANGE (at), LIST (code), HASH (id)), valid after PARTITION BY. A partition is a record of tables with partition_of: {parent: 'schema.table', bound: '…'}, the bound as pg_get_expr(relpartbound) prints it (FOR VALUES FROM (…) TO (…), IN (…), WITH (modulus 4, remainder 0), or DEFAULT), valid after PARTITION OF parent. A partition that is partitioned itself has both. A table that is neither has neither field, so a schema without partitions keeps the tables, and the hash, it had.

A partition has the columns of its parent and no others, so its record holds none (columns is empty): what the parent does to a column reaches it. Its constraints are its own: the checks it gets from its parent, and the primary key, unique, foreign key constraints PostgreSQL clones from the parent (conparentid), follow the parent. The indexes and the row triggers a parent clones to its partitions are left out too, so an index on the parent is captured once, on the parent, without the ONLY that pg_get_indexdef prints for it (ON ONLY leaves an invalid index on a parent that has partitions). A partition has its own owner, privileges, comments, row security, storage options (WITH (…)), logging (UNLOGGED) and replica identity, captured like a table's.

• Statements. CREATE TABLE s.p (…) PARTITION BY <key> for a new partitioned table; CREATE TABLE s.c PARTITION OF s.p <bound> for a new partition, made after its parent (and … PARTITION BY too when it is partitioned). Both are created as createTable, so the default privileges of the schema are settled on them like on any table. A partition added to a table that stays is created after the columns the parent gets in the same plan, so it starts with them. The index of a partitioned table is created before the indexes of its partitions, so that an index a partition has of its own is not taken as the clone of the parent's. A foreign key of a partitioned table is added without NOT VALID, which PostgreSQL does not allow there.
• Columns, indexes, constraints and triggers are planned on the parent alone: ADD COLUMN, DROP COLUMN (never ONLY), ALTER COLUMN TYPE, defaults, NOT NULL, its indexes, primary key, unique and foreign keys, checks, row triggers and policies. A column restructured on the parent rebuilds what hangs on its partitions too (their views, their own indexes, checks, triggers and policies).
• Destructive. A partition that goes is DROP TABLE, which destroys its rows, so it needs --allow-destructive; so does each partition of a partitioned table that goes, which drops them first, leaf before parent. A dropped partition is no table of a definition, so it is never traced to a change: it is always asked for.
• Blockers. The planner does not move rows or make a table again: a changed partition key (or strategy), a changed bound, a partition that moves to another parent, a table that becomes a partition or stops being one (ATTACH PARTITION or DETACH PARTITION by hand, then re-baseline), a table that becomes partitioned or stops being, and a column of the partition key that is dropped or retyped (PostgreSQL refuses it) are refused by name. A key column renamed by the intent is the same key. To change a bound, drop the partition and add one with another name (a destructive plan), or detach and attach by hand.
• State a snapshot cannot hold is unsupported, so a plan that touches it is refused by name: a column of a partition that is not its parent's (a default or NOT NULL of its own, partition-column; a primary key of the partition alone makes its columns NOT NULL), a partition and its parent across the boundary of the product's schemas (partition-outside), an index made ON ONLY a partitioned table and not attached to every partition (partition-index-only), a rule on a partitioned table or a partition (partition-rule), a foreign table, a non-heap access method or a tablespace, as for a table.
• An identical index. An index of a partition equal to one of the parent's is the parent's clone to PostgreSQL, whichever was made first, and is not captured as the partition's own. Do not rely on a duplicate.
• Bounds and the session. A bound and a partition key are captured as PostgreSQL prints them, and a plan applies them verbatim. A session whose DateStyle output is not ISO (SQL, DMY prints 01/02/2026) is moved to ISO, MDY for the capture and put back after, so a captured bound reads the same in any session; an ISO session, in either field order, is left alone and keeps its hashes. A timestamp bound is printed in the session's TimeZone with its offset, so it names the same instant everywhere, but its text differs between sessions of different zones, as for any timestamp literal in a default. When the source and the target were captured in different zones, the plan reports the blocker "bound of partition … changes" (a changed bound is refused). The postgres driver pins the session to UTC, so this is rare.
• Foreign keys to the parent. A table with a foreign key to a partitioned table holds one to every partition, and PostgreSQL refuses to drop an attached partition under it. A partition that goes while its parent stays is therefore DETACH PARTITIONed first (its own non-destructive operation, noInheritTable; the DROP TABLE after it carries the destructive flag). Every partition detach of a plan precedes every partition drop, so the partitions below a dropped one are freed with it. DETACH PARTITION refuses, loudly and before anything is dropped, while a row of the partition is still referenced, and the plan's single transaction leaves nothing half applied. When the parent goes too nothing is detached: the keys are dropped, then each partition, then the parent.
• Supported versions. Identity columns on a partitioned table are verified on PostgreSQL 17 only. Before 17 the behaviour is unverified: an older server may accept an identity column on the parent while its partitions do not share it, so do not rely on one there.
• First cut. An older release listed a partitioned table as unsupported (relation:p) and a partition as relation:r (relation:p when it is partitioned itself), so no product has a migration cut with one; but a stored source made before may still carry them, and the database may already have the tables (made by hand). An entry is retired for the source when the target holds that table as partitioned (relation:p) or as a partition (relation:r); else the plan is refused, naming it. The CREATE of such a table is a DO block that does nothing when a relation of the name exists and raises when it is not the same: it compares the kind of table, the partition key or bound, and the parent (schema qualifiers dropped, white space folded), not its columns, options or privileges. The primary key, unique, foreign key and check constraints the plan adds to such a table are guarded the same way (skipped when a constraint of the name exists, raising when its definition differs). The source is read as a current capture holds the clones an older capture saw as the partitions' own indexes and triggers, and the index of a partitioned table without ONLY, so the database's are not dropped or made again. An index, trigger or policy the older capture did not record (it recorded those of a partitioned table) is created plainly: a hand-made one the stored source missed fails the plan loudly (already exists), and the way out is to re-baseline. A foreign key to a partitioned table in a stored source made by an older capture is the same case: the older capture also listed the clones PostgreSQL makes of it on the partitions, which a plan cannot drop (cannot drop inherited constraint), so re-baseline. An index a partition has of its own that the older capture read as a clone is left where it is.

Table inheritance

A table that exists is linked to a parent, and unlinked from one, in place with ALTER TABLE … INHERIT and NO INHERIT. It is never dropped and created again, so its rows stay. A snapshot lists a child's columns and checks whole and marks each one it holds by inheriting inherited; such an item follows its parent and is never added to or altered on the child.

• Linking. The child is first brought to what the parent gives it: the parent's columns it lacks are added (ADD COLUMN IF NOT EXISTS, so a database that already has them is left alone), a parent's default and NOT NULL are set on the child (PostgreSQL refuses the link otherwise), and the parent's checks are stated on the child with the same name, because INHERIT needs the child to have them. INHERIT runs last, once the columns and checks are final, and only if the link is missing, so a stored snapshot that predates inheritance capture and a database that already has the link apply cleanly. A required parent column the child lacks needs a default or a backfill, as any new required column does.
• Unlinking. PostgreSQL keeps what the child inherited as the child's own. A check the target also lists for the child stays, one it does not is dropped.
• A parent that changes. A column added to a parent reaches its children and is added only to the parent. A column dropped from a parent is dropped with ONLY, so a child that keeps it keeps its data; the child's own DROP COLUMN follows where the target drops it too. A check the parent drops or restates is cleared from the children that hold it, IF EXISTS, since a child that declared the same check itself would keep it. A default or NOT NULL the parent changes, and a column it renames, reach the children, which are brought back to a definition of their own.
• Capture. A column or check counts as inherited when a parent gives it and the child adds nothing: ALTER TABLE … INHERIT leaves the child's own columns local, so a table that was linked and a table that was created inheriting capture the same. A column with a default or stricter NOT NULL of its own stays the child's own.
• Not captured. The order of the parents is not part of a snapshot, so reordering them is not a difference.
• A column the child lacks is added to a shadow table <child>_blueprint_inherit, which the child inherits from for the moment, so that the child holds the column by inheriting it, as a table created with INHERITS does (a column added to the child itself would be its own for good). The shadow is unlinked and dropped once the child has its real parent, so a generated script contains a CREATE TABLE, NO INHERIT and DROP TABLE of that name; no table of yours is dropped.
• Stored snapshots. An earlier capture listed a column or check a child restates from its parent as the child's own; this one reads it as inherited. For a table that restates inherited columns, migration new --check may report a one-time structural difference, and migration new then refreshes the stored snapshot without any SQL.

Trigger enablement

A trigger's state (PostgreSQL's tgenabled: O enabled, D disabled, R enabled for replica sessions, A enabled always) is part of its record (enabled) but not of its definition. A changed state moves the trigger with ALTER TABLE t ENABLE|DISABLE|ENABLE REPLICA|ENABLE ALWAYS TRIGGER n and never drops and creates it; a definition that changes with its state is dropped and created, then moved. A trigger the plan creates, or creates again, starts enabled, so it is moved when the target wants another state. The moves run once every trigger exists, and a trigger of a declared dependent is moved like any other. A table renamed by intent keeps its triggers and their state.

Order of functions, domains and types

Functions stay ahead of the tables, as before, with two exceptions that follow from what a signature or a constraint names. A function that a domain's DEFAULT or CHECK calls and that does not exist yet is created before the domain; a function whose signature uses a type or a domain the plan creates is created after it. A removed domain is dropped after the functions that used it, and before the enum it is based on. A domain that calls a function whose signature uses a type or table created in the same plan cannot be ordered: the plan is refused and the change goes in two migrations. Names are matched as words in the text, so the rule errs toward ordering and refusing.

After an upgrade: the first cut

A stored snapshot cut by an earlier Blueprint does not hold what the planner carries now (domains, composite types, procedures, rules, partitions and so on), and the database may already have those objects, made by hand. The first cut after the upgrade is planned from that snapshot and must apply to such a database and leave it as the target describes:

• unsupported reasons an earlier capture recorded for these constructs are not a reason to refuse the plan when the target holds the same construct (see each kind above); one the target does not hold stays a blocker, naming it.
• What the plan creates for those kinds is idempotent: CREATE OR REPLACE, IF NOT EXISTS, or a guarded DO block that creates a missing object, leaves one of the planned shape and raises on an object of the same name and another shape, which rolls the migration back. A guard compares cheap facts (kind, definition text, key, bound), not rows, options or privileges.
• Known limitation: for an extension installed into an owned schema, a snapshot made before extension members were left out lists their functions, types and sequences as the product's. The first cut plans to drop them and fails loudly (2BP01, the extension requires them). Re-baseline.

files.json records the version of the classifier that decided which statements are unplanned (classifier, absent in files from before it was recorded, which read as 1; the version stays 2). A kind the planner listed as unplanned in version 1 and carries now (comments, extensions, table options, domains, composite types, owners, default privileges, procedures, aggregates, rewrite rules including CREATE OR REPLACE RULE, materialized views) is tolerated: a file whose stored digest lists only such kinds, and that has no unplanned statements today, is not a change, because the plan carries its statements. Any other kind, any unplanned statement left, or a digest of the current version is compared as before.

Checking the planner against pg-delta

For contributors: bun tools/pg-delta-oracle.ts checks the planner itself. For each of the before/after pairs in tools/pg-delta-oracle/corpus it applies the planner's delta to a clone of the "before" database and asks Supabase's pg-delta (pinned, @supabase/pg-delta@1.0.0-alpha.59, run through bunx) whether the clone still differs from "after". It needs Docker with postgres:17-alpine and starts one throwaway container on a random loopback port; it never touches a real database. Each pair's meta.json says whether it should converge, be refused, or is a known gap; a new divergence, or a known gap that starts to converge, exits 1. In CI the check is advisory (.github/workflows/pg-delta-oracle.yml).

Options and exit codes

Flag	Effect
--product P	The product (default: the profile's only migrated product)
--profile PATH	The development profile (default blueprint.dev.json)
--allow-destructive	Consent to every removal, and allow destructive operations on objects no definition derives
--decisions FILE	Answers (JSON or YAML, the shape decisions.json records for one migration) for changes that need one
--accept-renames[=old->new,...]	Accept every detected rename, or only the listed <old path>-><new path> pairs
--acknowledge-unplanned	Record refused unplanned statements as migrated by your own migration file
--check	Write nothing; exit 1 when a migration is needed (local only)
--force	Capture even while other schemas depend on the product
--database-url URI	The local database (wins over --database-env)
--database-env NAME	Local database URI variable (default BLUEPRINT_DATABASE_URL, else the local Supabase database)

Exit	Meaning
0	Written, no changes, or --check clean
1	--check found that a migration is needed
64	Usage, profile or configuration error, an unreadable --decisions file, decisions given with no stored definition, or product migrations without the state store
65	The delta needs review (destructive operations no change accounts for, planner blockers, changed hand files that are not replayable, unacknowledged unplanned statements, a missing or stale decision, a change the planner cannot carry out, a participant refusal), the product command emitted another Blueprint's definition, or the capture was refused or an install file failed (all rolled back)
69	The local database could not be reached
70	The product command failed, or wrote no definition or no migration steps
73	A file could not be read or written

The state store

supabase/blueprint/<product>/ holds snapshot.json (the captured schema snapshot), files.json (each install file's sha256 and its unplanned statement kinds), definition.json (the product's Blueprint definition the migration was cut from) and, once a migration needed an answer, decisions.json (the answers, by migration file). Commit it with the migration: the next delta is planned from it. The migration files and the state files are written together or not at all. If migrations exist without the folder, the command exits 64; restore the folder rather than cut a second baseline.

blueprint db migration squash

blueprint db migration squash --product plant --dry-run   # prove and report
blueprint db migration squash --product plant             # prove, then rewrite

Replaces a product's migrations with one baseline, <yyyyMMddHHmmss>_<product>_baseline.sql (pass a name to change baseline), cut from the install list exactly as a first migration is. Its stamp sorts after every migration file of every product, and the file is one transaction of its own. The product must be up to date first: migration new --check must report no change to its files.

The squash is proven before anything changes. On the local database (BLUEPRINT_DATABASE_URL, or the variable --database-env names, else the local Supabase database; remote hosts are refused), inside two transactions that are always rolled back, it drops the product's owned schemas and applies the old migrations in order, then drops them again and applies the new baseline. The two are compared object by object (the same structure snapshot migration new stores) and row by row in every table of the owned schemas, leaving out columns whose default is a clock or a random value. Any difference refuses the squash, names each object or table, and writes nothing. Rows the migrations write outside the owned schemas are not compared. Reset the local database from its migrations first, as for migration new; --force captures even while other schemas depend on the product.

--accept-drift schema.table (repeatable) reports row differences in that table instead of failing. Use it for a chain cut by an older Blueprint, whose deltas replayed contract rows without retiring the old ones, or whose hand-written deltas changed reference rows the seeds do not. Structure differences are never accepted.

When the proof holds, the squash, in one step:

• deletes the product's old files from supabase/migrations/;
• writes the baseline and resets supabase/blueprint/<product>/ (snapshot.json, files.json) to it;
• writes supabase/blueprint/<product>/squash-<stamp>.sql, the ledger repair for a database that applied the old migrations.

Run the repair once on each such database (psql -f), before the next blueprint db migration up. Until then up refuses that database, because its ledger names files the tree no longer has and the baseline must not be replayed over an installed product. The repair is one transaction and looks only at the product's rows of blueprint_migrations.product_migrations. It refuses, changing nothing, unless they record every old version (and not yet the baseline); otherwise it copies them to blueprint_migrations.product_migrations_backup_<UTC yyyyMMddHHmmss>, deletes the old versions and records the baseline. Run again, it does nothing. It runs no migration SQL: the proof showed the database already holds what the baseline installs. A local database needs no repair: blueprint db reset replays the baseline.

--dry-run proves the squash and reports what it would delete and write, without changing anything. Exit codes: 0 squashed, proven dry run, or a single migration (nothing to squash); 64 usage or configuration; 65 the proof failed, unmigrated changes, or a rehearsal failed; 69 the local database is unreachable; 73 a file could not be read or written.

After a squash, migration new --check, db emit --check, db reset and db reset work as before: reset replays the baseline alone.

Deploy: blueprint db migration up

blueprint db migration up --product plant --dry-run   # what is pending
blueprint db migration up --product plant             # the local database
BLUEPRINT_DATABASE_URL=... blueprint db migration up --product plant --allow-remote

up applies the product's migration files that the database's ledger does not record, in version order. Each file runs in one transaction with its ledger row, so a failing file is rolled back alone and the files before it stay applied and recorded; fix the file and run up again. On a database without the product it installs it from the baseline. It runs no provision SQL and no provisionCommands: provisioning a tenant is a separate step.

The database is the URI in BLUEPRINT_DATABASE_URL (or the variable --database-env names), else the local Supabase database. up connects by that URI; it needs only a supabase/config.toml that names the project and the profile, not a running Supabase stack. A host needs --allow-remote unless it is a loopback address (127.x.x.x or ::1), localhost or host.docker.internal. A single-label name such as db or prod-db is not local for up and adopt, though other blueprint db commands take it for a Docker service. The URI is never printed: output names the target as host:port/database. Use a direct or session-mode connection URL for up; a transaction-mode pooler does not keep one session for the run.

plant on db.example.com:5432/postgres: 2 recorded, 1 pending.
Applied migrations/20261010120000_plant_add_notes.sql
Applied 1 migration of plant on db.example.com:5432/postgres and recorded it in blueprint_migrations.product_migrations.

With nothing pending it prints plant is up to date with <newest file> on <target>. and exits 0.

Writers of product migrations run one at a time, across products: up, adopt, reset and the squash repair take one lock. up waits at most 10 seconds for it, and for any lock a file needs, then stops (exit 69) and names what was held; nothing of the file it was about to apply is kept. A file may run for at most 15 minutes as a whole: the bound is on the file, not on each of its statements.

Any other error of the database is printed as the database gave it, with how many pending files were applied before it (exit 1, or 69 when the connection failed). After a failed connection run up again: it applies only the files the ledger does not record.

up plans from what it reads before it takes the lock. Immediately before the first file it reads the ledger again and refuses when the product's rows changed since the plan: another run is deploying the product, so run up again. After the last file it reads the product's schemas again. If one is still missing, the ledger and the database disagree: up says so and exits 65. What it applied stays applied and recorded.

up refuses, changing nothing (exit 65), when the ledger and the database or the files disagree:

Refused when	Why	What to do
A file has BEGIN, COMMIT, ROLLBACK, SAVEPOINT or other transaction control, apart from one opening BEGIN and one closing COMMIT	The file must run in one transaction with its ledger row	Remove it; the output lists migrations/<file>:<line>
The product's install list creates no schema named <product> or <product>_*	up cannot tell whether the product is installed	Give the product a schema of its own, as migration new requires
The ledger has no row for the product and the Supabase CLI's history holds rows that are its own, whether or not its schemas exist	The Supabase CLI applied the files, so up would apply them again	blueprint db migration adopt
The product's schemas exist but the ledger has no row for it	up would replay the baseline over an installed product	The product was installed from its files, so reset a local database with blueprint db reset --allow-reset
The ledger has rows for the product but none of its schemas exists, or nothing is pending and one is missing	The ledger and the database disagree	Reset a local database; investigate a deployed one
The ledger records a version with no migration file	After a squash the baseline must not replay over the product	Run the squash repair that targets blueprint_migrations.product_migrations, or restore the files. A repair that targets supabase_migrations.schema_migrations does not change these rows
A pending file is older than the newest applied one	It would run after files written after it	Check the order is safe, then pass --allow-out-of-order
The product's ledger rows changed between the plan and the first file	Another run is deploying the product	Run up again
The product's structure on the database is not the one a pending file was planned from	The plan would run on a structure it was not written for: a change made outside Blueprint, or an earlier migration missing or applied by hand	Review the difference (both hashes are printed, and the committed file whose state the database is at), restore the state, or pass --allow-target-drift

Flag	Effect
--product P	The product (default: the profile's only migrated product)
--dry-run	List the pending files and apply nothing
--allow-remote	Allow a host that is not a loopback address
--allow-out-of-order	Apply pending files older than the newest applied one
--allow-target-drift	Apply a file although the database's structure is not the one the file was planned from
--profile PATH	The development profile (default blueprint.dev.json)
--database-env NAME	Database URI variable (default BLUEPRINT_DATABASE_URL, else the local Supabase database)

Exit	Meaning
0	Applied, up to date, or dry run
1	A file failed; it is named, with how many were applied before it. Or the database reported another error; its text is printed
64	Usage, profile or configuration error, no migrated product, or a host that is not a loopback address without --allow-remote
65	Refused; nothing changed. Or a schema is missing after the files were applied
69	The database could not be reached or its connection failed, or a lock up needs was held elsewhere for 10 seconds

Interruption and concurrency

A file and its ledger row are one transaction, so however up stops, a file is either applied and recorded or neither: never half of it, and never a file skipped without a row. Each situation below has a test that kills a runner, runs two at once or holds locks from another session.

Situation	Outcome	What to do
A file fails partway (exit 1)	All of it is rolled back, including the statements before the failing one. The files before it stay applied and recorded; the files after it are not run and not recorded. The message names the file and how many pending files were applied before it, then the database's own text	Fix the file and run up again; it resumes with that file
up is killed (kill -9, a crash, a lost connection) while a file runs, or after its body and before its commit	The server rolls the file back with its ledger row: none of its statements is visible and no row exists. Earlier files stay applied and recorded	Run up again; it applies the file whole
The server keeps running the statement of a killed up (a server does not look at a client's socket while a statement runs, and a long ALTER or pg_sleep is one)	Until it ends, that session holds the product migration lock and the locks the file took. A rerun waits 10 s and exits 69, naming the lock; reads of the tables the file altered wait behind it. Nothing is applied	Wait for the statement to end, or end the session (SELECT pg_terminate_backend(pid) for its row in pg_stat_activity), then run up again
The connection is lost as the commit is sent	The file is committed with its row, or rolled back with it; up cannot know which (exit 69)	Run up again: it applies only the files the ledger does not record
Two up on one database at once	They take turns per file under the product migration lock. Each file is applied once and has one ledger row; the database ends as after a single run. The other run prints Already applied <file> for each file it lost, and Applied none of the N pending migrations ... another run recorded each of them first when it lost them all. It exits 65 when the ledger changed while it was planning (This run changed nothing), or 69 when it waited 10 s for the lock while the other run applied a long file	Run up again; with every file recorded it prints that the product is up to date
Another session holds a lock on a table the file changes (an open transaction with a row lock, a SELECT ... FOR UPDATE)	up waits at most 10 s, then exits 69 naming a lock on an object the migration file changes and the file. The file is rolled back; earlier files stay applied and recorded	Let the other session finish, then run up again
Another session holds a lock on the ledger, or an exclusive lock on a table of the product that up reads to compare its structure	The same wait of 10 s, then exit 69 naming a lock on the product ledger or a lock on an object the structure of <product> is read from. A lock that kept only the ledger row from being written rolls the file back with it	Let the other session finish, then run up again
A file runs past 15 minutes as a whole	The server cancels it (canceling statement due to statement timeout); it is rolled back with its row and up exits 1, naming the file	Split the file, or take the long step out of it (a backfill in batches, an index built in a window)

The state a file was planned from, and its hazards

migration new writes two hashes into the header of each file, above its transaction: -- Planned from schema <hash>, the schemaHash of the structure the plan started from, and -- Produces schema <hash>, the structure it reaches. A baseline starts from nothing and has no planned-from line. A delta that adds enum labels is two files: the labels file starts from the stored structure and produces nothing yet, and the file after it starts from the stored structure with the new labels, the state the executor accepts.

Before each file that has a planned-from line, up captures the product's owned schemas on the database, as db status does, and compares. A mismatch refuses the file, and the files before it stay applied and recorded; the message prints both hashes and says where the database stands: already at the state the file produces (it was applied outside the ledger), the state after migrations/X (an earlier committed state), or matches no committed migration (changed outside Blueprint). --allow-target-drift applies the file anyway and says so. A dry run checks the first pending file only: the others start from what the files before them leave. A file without the line (a hand-written file) is applied unchecked.

The capture is not part of the file's transaction. The product migration lock keeps other Blueprint writers out, but a session that alters the product's tables between the check and the file is not caught.

The header also lists, under -- Hazards, the lock class and table-rewrite risk of each operation that locks an existing relation, in plan order:

-- Hazards (lock; effect: operation target (note)):
--   ACCESS EXCLUSIVE; rewrites the table: addColumn app.items.ref (a volatile default is evaluated for every existing row; ...)
--   SHARE UPDATE EXCLUSIVE; scans the table: validateConstraint app.items.ref:not-null (...)

migration new prints the operations that do more than a brief catalog change, and a dry run of up prints them under each pending file. Operations on a table the same migration creates are left out: the table is empty. The classes are those PostgreSQL takes for each statement alone; a test reads each back from pg_locks. The rendered file also locks every existing table the plan alters ACCESS EXCLUSIVE before its first operation and holds it until COMMIT, so on those tables the header says ACCESS EXCLUSIVE and readers and writers wait for the whole file, not for the statement. What the effects mean for a table in production:

Effect	Operations	What to do
rewrites the table	a column type change, a volatile ADD COLUMN default, a stored generated or identity column, SET LOGGED/UNLOGGED	Reads and writes wait for all of it. Plan a window, or split the change (add the column, backfill in batches, then set the default)
builds an index	an index, a UNIQUE or PRIMARY KEY constraint	Writes wait while the index builds (reads too, on a table the file locks up front). A migration runs in a transaction, so CREATE INDEX CONCURRENTLY is not available: plan a window for a large table
scans the table	a CHECK or foreign key not added NOT VALID, SET NOT NULL without a validated check, VALIDATE CONSTRAINT	On its own VALIDATE CONSTRAINT does not block reads or writes; inside a file that locks the table up front it does
updates rows	a backfill	Every matching row is written in one transaction; the table bloats
catalog only	everything else on an existing table	The statement is instant, but on a table the file locks up front the lock lasts to COMMIT, and it waits behind every running statement on the table and blocks those that arrive after it. up waits at most 10 s for it

Move from the Supabase history: blueprint db migration adopt

A database that applied a product's files through the Supabase CLI (supabase db push, supabase migration up, supabase db reset, or blueprint db reset of a Blueprint without the ledger) records them in supabase_migrations.schema_migrations. adopt moves those rows to the ledger, once per database:

blueprint db migration adopt --product plant --dry-run
blueprint db migration adopt --product plant

A row with a name is the product's when its name is (<product>_<name>; the longest product name wins, so plant_care_baseline belongs to plant_care, not plant), including the row of a file that no longer exists. Its version never decides: another repository's migration may carry the same stamp. A row without a name, in a history table that keeps versions alone, is the product's when its version is the stamp of one of the product's migration files.

adopt knows only the products of this checkout, so a row named for a longer product name that only another repository knows (plant_care_baseline where this checkout has plant alone) cannot be told from this product's: check the --dry-run list when two repositories deploy to one database.

In one transaction, each such row is inserted for the product with the statements the history kept for it (a version the ledger already records stays as recorded) and deleted from the Supabase history. Every other row stays. adopt runs no migration SQL, prints each moved version name and the count, and run again prints nothing to adopt. --dry-run lists the rows and changes nothing. The target, --allow-remote, --database-env, and --profile work as for up.

A row of the product with no migration file here, such as the row of a file a squash retired, is listed with (no local file), and adopt refuses, changing nothing (exit 65), unless you pass --include-retired. Once such rows are in the ledger, up refuses the product on that database until a squash repair that targets blueprint_migrations.product_migrations has replaced them with the baseline. Which repair you have decides the order:

The repair supabase/blueprint/<product>/squash-<stamp>.sql targets	Do
supabase_migrations.schema_migrations (a Blueprint without the ledger wrote it)	Run the repair first, then adopt, which then needs no flag. Once adopt has moved the rows, that repair cannot find them, and Blueprint cannot write a repair again for a product that is already squashed
blueprint_migrations.product_migrations	adopt --include-retired, then the repair, then up

Exit	Meaning
0	Adopted, nothing to adopt, or dry run
1	The database reported an error; its text is printed and nothing was adopted
64	Usage, profile or configuration error, no migrated product, or a host that is not a loopback address without --allow-remote
65	Refused: a row of the product has no migration file here
69	The database could not be reached or its connection failed, or a lock adopt needs was held elsewhere for 10 seconds, such as one the Supabase CLI holds on its history

Rollout for a deployed database

Do these in order, once per database:

1. Run any outstanding squash repair that a Blueprint without the ledger wrote. Such a repair targets the Supabase history (it names supabase_migrations.schema_migrations), so it must run while the rows are still there.
2. Stop the Supabase CLI from applying the product's files there. Remove them from the folder that supabase db push reads, or stop that push. If you skip this, the next supabase db push --include-all sees every adopted file as pending and replays the baseline over the product.
3. blueprint db migration adopt --product <product> --allow-remote.
4. From then on, deploy with blueprint db migration up --product <product> --allow-remote.

adopt refuses the rows of retired files unless --include-retired is passed, so a repair skipped in step 1 is caught at step 3, before the rows leave the history that repair reads.

Rehearsing a migration on a production-shaped copy

Before the files migration new wrote reach a production database, run them on a throwaway copy that holds data. bun tools/migration-rehearsal.ts does the whole path on one disposable Postgres container (Docker with postgres:17-alpine; it touches no other database): it cuts the product's baseline and applies it, loads rows into every table of the product's schemas (100,000 in each of a few big tables, a few hundred elsewhere), cuts the changes as migrations, applies the pending files with migration up, and checks:

• a the migrated structure equals a fresh install of the new state: the same schema hash, and an empty pg-delta diff between the two databases;
• b every table and column that exists in both states holds the same rows (count and an md5 of the ordered rows), with each rename, drop and conversion named;
• c the ledger holds exactly the committed files: versions, names and checksums;
• d running up again exits 0 with "up to date" and changes nothing (the catalog hash, the ledger and the row counts are the same);
• e migration new --check then finds nothing left to cut;
• what each change did to its data: a new required column holds its default, a new unique key refuses a duplicate, a governed entity's records become effective revision 1.

It also prints the wall time of each up file next to the lock and rewrite hazards that file's header lists, so you see which operations cost on that many rows. A change the planner refuses (a decision it needs, an unsupported change) is reported by name, and the rest of the changes go on.

bun tools/migration-rehearsal.ts                       # synthetic, fixed, batch; 100,000 rows
bun tools/migration-rehearsal.ts --product synthetic   # synthetic | fixed | batch | all
bun tools/migration-rehearsal.ts --product fixed --rows 5000
bun tools/migration-rehearsal.ts --cut once            # all changes in one migration, not one each
bun tools/migration-rehearsal.ts --keep                # leave the container and databases

Exit 0 when every step passed or is a named refusal or gap, 1 when one failed, 2 when the tool could not run. A product is a Blueprint built in Dart plus the changes to cut from it (tools/migration-rehearsal/products/); the rig that emits, cuts and applies it (product_rig.dart) runs the product's target in process, through db emit and migration new as the CLI runs them.

What it does not show: lock waits against live traffic, the hardware and volume of the real database, or data your tables hold that the generic loader does not produce. The loader leaves a table empty when it references the runtime contract (a row there would pin a retired revision, which a contract migration refuses to delete), and it loads with triggers off. Rehearse with a restore of production when you need those.

Reset

blueprint db reset --allow-reset treats a product as migrated when supabase/migrations/<stamp>_<product>_*.sql files exist. It runs on the target: the local Supabase database unless --database-url or BLUEPRINT_DATABASE_URL names another. On a database that does not have the product yet the drop removes nothing, so reset installs it. For a migrated product it:

1. drop the product's schemas (reset only, refused while other schemas depend on them; --force drops those too),
2. create the product ledger when it is absent and delete the product's rows from it (rows of retired files too),
3. apply each migration file in its own transaction and record it in blueprint_migrations.product_migrations,
4. run the provision list, then the provisionCommands.

Details:

• The drop shares the first migration's transaction, so a failing first migration changes nothing. A later failure leaves the earlier migrations applied and recorded, as blueprint db migration up would.
• A migration file that opens with BEGIN and ends with COMMIT, as db migration new writes it, runs as its body in that file's transaction; its ledger row records the file as written. Any other BEGIN, COMMIT, ROLLBACK, SAVEPOINT or transaction control is refused (exit 65) before the database is touched, listing migrations/<file>:<line>.
• The product migration lock waits at most 10 seconds for another reset, migration up or migration adopt, then fails (exit 69).
• It does not read or write the Supabase CLI's history, and does not need that table to exist.

--from-tree resets from the product's files (the install list, then provision) instead of its migrations. It deletes the product's ledger rows and records none. Use it to try a model change before cutting a migration. blueprint db migration up then refuses that database, because the product is installed and the ledger records nothing for it; run a plain db reset to return to the migrations.

Status warnings

blueprint db status adds two warnings for a migrated product, read from the local database:

• database differs from the committed migrations: the installed product is not supabase/blueprint/<product>/snapshot.json (installed from the tree, or behind or ahead of the migrations).
• state store missing: migrations exist without supabase/blueprint/<product>/.

--json adds differsFromMigrations and schemaStateMissing.

Example

The first baseline:

blueprint db emit --product plant           # write the generated SQL
blueprint db reset --allow-reset --from-tree    # install it locally
blueprint db migration new baseline --product plant
git add supabase/migrations supabase/blueprint supabase/schemas supabase/seed
git commit -m "feat(plant): baseline migration"

Then change the model (say, add a notes column), and cut a delta:

blueprint db emit --product plant
blueprint db reset --allow-reset                # local DB current: baseline replayed
blueprint db migration new --check --product plant   # exit 1: one is needed
blueprint db migration new add_notes --product plant
git add supabase/migrations supabase/blueprint supabase/schemas
git commit -m "feat(plant): add notes"
blueprint db reset --allow-reset                # replay baseline and delta, then provision
blueprint db status --product plant         # no "differs" warning

Deploy it. up applies only the delta to a database that has the baseline:

export BLUEPRINT_DATABASE_URL=...               # the deployed database
blueprint db migration up --product plant --allow-remote --dry-run
blueprint db migration up --product plant --allow-remote

If migration new exits 65 over unplanned statements, write your own migration file for them (named to sort before the next generated one) and rerun with --acknowledge-unplanned.