The Architecture of a Schema Weaver PostgreSQL Migration Engine

Why We Built a Migration Engine

PostgreSQL schema migrations are deceptively hard. Adding a column is easy. Renaming a column that five views and two functions depend on is hard. Doing it without downtime on a 2TB production table is harder. Doing it when the production schema has drifted from what is in version control — because a DBA ran an emergency fix at 2am last Tuesday — is where most migration workflows fall apart.

We built the Schema Weaver Migration Engine to handle the full lifecycle of a PostgreSQL schema change: introspect the live database, diff it against a desired schema, generate DDL, classify every change by risk, apply only safe patterns by default, and recover gracefully if something fails mid-migration. The engine is the same one that powers schema operations inside Schema Weaver's SQL Editor and is shipped as a standalone npm package for teams that want to run it from CI or their own tooling. This article is the architecture behind it.

Our approach is state-based rather than version-script-based. You declare what the schema should look like, the engine reads what the database actually looks like right now, and it computes the safe delta between the two. Every change is tagged with a risk level, every reversible operation gets a generated rollback statement, and every step that cannot run inside a transaction is isolated and sequenced explicitly. We try to be honest about the parts PostgreSQL itself makes hard — DDL is not transactional for some operations, dependency cycles do exist, and risk classification is conservative by design — and we will point those out as we go.

System Overview

The engine is a strict pipeline. Data flows in one direction: a desired schema (JSON) and a live PostgreSQL connection enter on the left, and a fully-applied, recorded migration exits on the right. There are no cycles, no implicit state, and no hidden side effects. Each module owns exactly one responsibility, and every module is independently importable. You can call introspect() alone, print the resulting diff, and never execute a single statement.

graph TD
    SRC["Source DB
PostgreSQL 10-18"] --> INT
    DES["Desired Schema
JSON snapshot"] --> DIF

    subgraph PIPE["Migration Pipeline"]
        INT["1. Introspector
parallel catalog queries"]
        TRN["Translator
catalog rows to SchemaSnapshot"]
        DIF["2. Differ
ObjectMatcher + PropertyDiffer"]
        DDL["3. DDL Generator
CREATE / ALTER / DROP / RENAME"]
        PLN["4. Planner
dependency-ordered phases"]
        RSK["5. Risk Tagger
5-level classification"]
        EXE["6. Executor
transactional + non-tx"]
        STO["7. Storage
migration_history table"]
    end

    INT --> TRN
    TRN --> DIF
    DIF --> DDL
    DDL --> PLN
    PLN --> RSK
    RSK --> EXE
    EXE --> STO
    STO --> TGT["Target DB
schema applied"]

    style SRC fill:#00e676,color:#1a1d2e,stroke:none
    style DES fill:#6c63ff,color:#fff,stroke:none
    style INT fill:#00d4ff,color:#1a1d2e,stroke:none
    style TRN fill:#00d4ff,color:#1a1d2e,stroke:none
    style DIF fill:#ffd740,color:#1a1d2e,stroke:none
    style DDL fill:#ff9100,color:#fff,stroke:none
    style PLN fill:#ff9100,color:#fff,stroke:none
    style RSK fill:#ff4081,color:#fff,stroke:none
    style EXE fill:#6c63ff,color:#fff,stroke:none
    style STO fill:#00e676,color:#1a1d2e,stroke:none
    style TGT fill:#00e676,color:#1a1d2e,stroke:none

The Introspector reads the live database through parallel catalog queries and the Translator normalizes those raw pg_catalog rows into a SchemaSnapshot object. The Differ compares that snapshot against the desired schema and produces a list of changes. The DDL Generator turns each change into PostgreSQL DDL. The Planner sequences those statements by dependency. The Risk Tagger scores every change from none to critical. The Executor applies the plan inside a transaction, isolates non-transactional DDL into a separate queue, and detects drift before and after. Storage writes a complete audit record to migration_history and tracks every step through a state machine that supports crash recovery.

Each of these modules is substantial enough that the rest of this article walks through them one at a time. Before we get there, it is worth noting what the pipeline is not: it is not a SQL file runner, it is not a generator that emits files you commit and forget, and it is not a thin wrapper around pg_dump. It is a real execution engine that holds locks, opens transactions, takes savepoints, and decides at runtime whether each statement is safe to run.

The Introspector

The Introspector is the engine's source of truth. Everything downstream — the diff, the plan, the risk scores, the rollback statements — is computed against the snapshot it produces. If it reads the database incorrectly, every later stage inherits the error. We treat introspection as a zero-trust operation: the engine never assumes a schema matches what migration history claims; it always re-reads the live catalog.

To build a SchemaSnapshot, the Introspector issues more than thirty catalog queries against pg_catalog, information_schema, and a handful of PG-version-specific system views. These queries are dispatched in parallel via Promise.all so that introspecting a moderately complex database (say, 200 tables with indexes, constraints, and a dozen functions) completes in a single round-trip's worth of latency rather than thirty sequential ones. Each query module lives in its own file under introspection/queries/ — tables, columns, indexes, constraints, sequences, enums, domains, composites, ranges, multiranges, views, materialized views, functions, procedures, triggers, event triggers, policies, rules, casts, operators, operator classes and families, collations, conversions, statistics, aggregates, text-search objects, FDWs and foreign servers and user mappings, foreign tables, publications, subscriptions, extensions, schemas, tablespaces, roles, databases, ACLs, default privileges, procedural languages, access methods, and PG18+ virtual generated columns.

Raw catalog rows are not directly usable. A column's data_type from information_schema.columns comes back as USER-DEFINED for enums and composites, which is useless to the differ. The Translator layer underneath the Introspector resolves these to the underlying type name (enum, composite, domain, range) and reconstructs full type signatures including array dimensions, character lengths, numeric precision, and time-zone awareness. The version detector underneath queries current_setting('server_version_num') once at the start of introspection so every later module can branch on PG version — for example, PG15+ exposes NULLS NOT DISTINCT on unique constraints, PG16+ supports ALTER TYPE RENAME VALUE, and PG18+ adds virtual generated columns and NOT ENFORCED constraints.

The full coverage matrix is wide. The table below shows the object categories that matter most for production schemas.

In total, more than 50 PostgreSQL object types are introspected, with 190+ properties tracked across them. The full matrix lives in the Supported Objects wiki; the table above highlights the categories that show up in nearly every production schema we have seen.

The Differ

The Differ takes two SchemaSnapshot objects — the live database and the desired schema — and produces a flat list of changes. Each change carries enough metadata that downstream stages can act on it without re-reading either snapshot: the object type, the object key (schema-qualified name), the change type (CREATE, ALTER, DROP, or RENAME), the property path for property-level changes, the previous and next values, and any dependent changes the differ detected.

The interesting work happens in ObjectMatcher. Matching objects by key alone is naive — if you rename users.email to users.email_address, a key-only matcher sees a column drop and a column create, which means data loss. The matcher uses Levenshtein string similarity with a 0.60 threshold for most object types and a lower 0.50 threshold for columns. The lower bar for columns is deliberate: a column candidate already shares its parent table and is type-compatible (both checks run before the name check), so the name gate is only a coarse filter. Without the lower threshold, a real-world rename like old_col → new_col scores 0.571 and would silently degrade to a destructive drop-and-create. We err on the side of rename detection to preserve data.

When a rename is detected, the differ records a RENAME change instead of a paired drop-create, and the DDL Generator emits ALTER TABLE ... RENAME COLUMN instead of ALTER TABLE ... DROP COLUMN followed by ALTER TABLE ... ADD COLUMN. The result is that the column's data is preserved. The matcher also tracks confidence — high (above 0.80), medium (above 0.70), and low (above 0.60) — and confidence is surfaced in the plan output so a human reviewer can sanity-check borderline rename decisions before approving the migration.

The PropertyDiffer handles objects that exist in both snapshots but have changed properties. It does semantic comparison rather than string comparison: varchar(255) and character varying(255) are the same type, timestamp without time zone and timestamp are the same type, and so on. For data-type changes it consults type-compatibility.js to classify the change as a widening cast (safe), a narrowing cast (data may be lost), an unsafe cast (cast may fail at runtime), or an impossible cast (no path exists). That classification feeds directly into risk tagging, which is why a text → varchar change is rated none while text → integer is rated critical.

The DependencyResolver walks the change list and builds a directed acyclic graph. Tables depend on schemas; columns depend on tables; indexes depend on tables and columns; constraints depend on tables and columns; foreign keys depend on both the referencing and referenced tables; views depend on their underlying tables and other views; functions depend on their argument types; triggers depend on tables and the functions they call. The graph is what lets the Planner emit CREATE TABLE before CREATE INDEX, CREATE TYPE before CREATE TABLE that uses it, and CREATE FUNCTION before CREATE TRIGGER that fires it.

The DDL Generator

The DDL Generator takes a single change object and produces a SQL string. It is a pure function — no database access, no side effects — which makes it easy to test exhaustively and safe to call from a dry-run path. The dispatcher in statement-factory.js routes each change to the right generator based on a combination of change type (CREATE, ALTER, DROP, RENAME) and object type (table, column, index, constraint, sequence, type, view, function, and so on).

Underneath the dispatcher are dedicated generators: create-generator.js for CREATE statements, alter-generator.js for ALTER, drop-generator.js for DROP, rename-generator.js for ALTER ... RENAME, comment-generator.js for COMMENT ON, and grant-generator.js for GRANT/REVOKE. Each generator handles the quirks of its object type. Indexes need to know about access methods (btree, hash, gin, gist, brin, spgist), INCLUDE columns, partial-index predicates, and collation overrides. Constraints need to distinguish primary keys, unique constraints, foreign keys (with ON DELETE / ON UPDATE actions), check constraints, and exclusion constraints. Functions need to round-trip argument modes (IN, OUT, INOUT, VARIADIC), default values, and the function language.

Every identifier is quoted using PostgreSQL's double-quote escaping rules so that reserved words, mixed-case names, and identifiers containing dots or spaces survive the round trip. The generator is version-aware — it checks the detected PG version before emitting NULLS NOT DISTINCT (PG15+), ALTER TYPE RENAME VALUE (PG16+), SET EXPRESSION for generated columns (PG17+), or GENERATED ALWAYS AS ... VIRTUAL (PG18+) — and throws a VersionIncompatibilityError if a change requires a feature the target server does not have.

Here is an example of the DDL the generator produces for a column data-type change with a USING clause — the safe pattern for type changes on populated columns:

-- change: ALTER column orders.metadata from text to jsonb
ALTER TABLE public.orders ALTER COLUMN metadata TYPE jsonb
  USING metadata::jsonb;

The USING clause is non-negotiable for type changes on populated columns. Without it, PostgreSQL implicitly casts and may fail on rows that do not conform to the target type. With it, you control the cast explicitly — and the risk tagger can flag a USING that hides a narrowing cast (for example, jsonb → text → integer would truncate data).

The Planner

The Planner takes a list of changes and produces a MigrationPlan: a sequence of phases, each containing zero or more steps, with explicit dependency edges between steps. A step is the smallest unit of execution — one SQL statement, plus its risk level, its transactional flag, its phase, and the IDs of any steps it depends on. The plan is the contract between the analysis half of the engine and the execution half.

Phases are numbered and run in order. Drops run in reverse dependency order (drop triggers before functions before views before constraints before indexes before columns before tables before types), and creates run forward (create types before tables before columns before indexes before constraints before views before functions before triggers before policies before rules). The phase numbering is the topological sort made concrete — the planner does not re-sort at runtime, it walks the phases in order and within each phase it walks the steps in dependency order.

The SmartMigrator is a sub-component of the planner that decomposes dangerous operations into multi-step safe workflows. When the differ reports a column going from nullable to non-nullable, the SmartMigrator replaces the single dangerous ALTER TABLE ... ALTER COLUMN ... SET NOT NULL with three safer steps: add a CHECK constraint NOT VALID, validate the CHECK constraint (which scans the table but does not hold a long lock), and finally convert the validated CHECK to a real NOT NULL and drop the CHECK. The same decomposition happens for foreign-key additions (add NOT VALID, then validate), index creations on populated tables (CREATE INDEX CONCURRENTLY outside the transaction), and type changes on populated columns (USING clause derivation).

The planner also generates rollback information for every reversible step. A CREATE TABLE becomes a DROP TABLE on rollback; an ADD COLUMN becomes a DROP COLUMN; an ADD INDEX becomes a DROP INDEX. Some operations are not reversible — DROP COLUMN with data, TRUNCATE, DROP TABLE with data — and the planner marks them as such so the executor can refuse to run a rollback that would silently destroy data. The rollback information is persisted alongside the migration record so engine.rollback(pool, migrationId) can replay the reverse sequence without re-deriving it.

Finally, the planner exposes a dry-run mode that runs every step up to execution but stops short of sending SQL to the database. The dry-run output is identical to the live plan — same steps, same risk tags, same DDL — which means what you review in dry-run is exactly what will run in production. There is no separate "preview" code path that could drift from the real one.

The Risk Tagger

Every change that flows through the planner is tagged with a risk level before it is allowed to execute. The risk tagger is the engine's safety boundary — it is the component that decides whether a change is safe to run automatically, needs human approval, or should be blocked outright. A migration plan with any change tagged critical is blocked by default; the caller has to explicitly opt in (via allowRiskBelow: 'critical' or a similar override) to run it.

The five levels are none, low, medium, high, and critical. The tagger evaluates each change against a series of checks, and the highest applicable level wins. A change can be escalated by multiple checks — a column data-type change that is also a narrowing cast and also requires a table rewrite ends up critical even if each individual check would have settled on a lower level.

graph TD
    CHG["Incoming change"] --> NONE{"No-op or
pure additive?"}
    NONE -->|"Yes"| L0["none"]
    NONE -->|"No"| DDL{"DROP / TRUNCATE /
type-losing cast?"}
    DDL -->|"Yes"| L4["critical"]
    DDL -->|"No"| LOCK{"Lock hazard?
e.g. SET NOT NULL
on large table"}
    LOCK -->|"Yes, no safe pattern"| L3["high
requires downtime"]
    LOCK -->|"Yes, safe pattern
available"| L2["medium
rewrites to safe pattern"]
    LOCK -->|"No"| CAST{"Narrowing or
unsafe cast?"}
    CAST -->|"Yes"| L3b["high"]
    CAST -->|"No, widening"| L1["low"]
    CAST -->|"No change"| L0b["none"]

    style CHG fill:#6c63ff,color:#fff,stroke:none
    style NONE fill:#00d4ff,color:#1a1d2e,stroke:none
    style DDL fill:#ff4081,color:#fff,stroke:none
    style LOCK fill:#ff9100,color:#fff,stroke:none
    style CAST fill:#ffd740,color:#1a1d2e,stroke:none
    style L0 fill:#00e676,color:#1a1d2e,stroke:none
    style L0b fill:#00e676,color:#1a1d2e,stroke:none
    style L1 fill:#9d9db5,color:#1a1d2e,stroke:none
    style L2 fill:#ffd740,color:#1a1d2e,stroke:none
    style L3 fill:#ff9100,color:#fff,stroke:none
    style L3b fill:#ff9100,color:#fff,stroke:none
    style L4 fill:#ff4081,color:#fff,stroke:none

The decision tree above is a simplification — the actual risk tagger has more than ten categories including data_loss, irreversible, lock_hazard, unsafe_cast, impossible_cast, version_incompatible, and destructive_change — but the structure is the same: a series of checks, an escalation rule, and a final level. The tagger also records the categories that fired, the warnings to surface, whether a safe pattern is available, and an estimated execution duration for lock-hazard cases.

Here is what a risk-tagged plan output looks like for a migration that adds a foreign key and changes a column type:

migration_id: "a3f1c8e2-7b2d-4e9f-9c1a-5d8e3f2b1a04"
overall_risk: "high"
blocked: false  // high but below critical threshold

steps:
  - id: "step_001"
    description: "Add FK orders_user_id_fk NOT VALID"
    risk_level: "low"  // safe pattern: NOT VALID
    categories: []

  - id: "step_002"
    description: "Validate FK orders_user_id_fk"
    risk_level: "low"

  - id: "step_003"
    description: "Alter type orders.metadata text -> jsonb"
    risk_level: "high"
    categories: [ "unsafe_cast" ]
    warnings:
      - "Cast text -> jsonb may fail on rows that are not valid JSON"
      - "Recommend: preflight with SELECT count(*) FROM orders WHERE metadata::text !~ '^{.*}$'"
    safe_pattern_available: true  // USING clause derived

The plan above is not blocked because the highest risk is high, not critical. If the type change had been an impossible cast — say bytea → integer with no cast path — the level would have escalated to critical, the plan would have been blocked, and the caller would have received a PlanBlockedError with the block reason and the full risk assessment attached.

The Executor

The Executor takes a migration plan and applies it to the database. It is the largest module by responsibility: it acquires locks, opens transactions, takes savepoints, runs statements, classifies errors, retries transient failures, isolates non-transactional DDL, captures pre- and post-migration snapshots for drift detection, and persists the audit trail. Every other module in the engine is a pure function or a read operation; the executor is the one that writes.

The transaction strategy is layered. At the outermost layer, a session-level advisory lock (pg_advisory_lock) spans the entire migration to prevent two migrations from running concurrently against the same database. The lock key is derived from the connection ID via SHA-256 and clamped to a 64-bit integer so it fits in PostgreSQL's bigint advisory lock parameter space. Inside that session lock, each phase runs in its own transaction with a transaction-scoped advisory lock (pg_advisory_xact_lock) that auto-releases on COMMIT or ROLLBACK. Each step within a phase runs under a savepoint so that a single failing statement can be rolled back without aborting the entire phase.

PostgreSQL is honest about which DDL operations can run inside a transaction block and which cannot. CREATE INDEX CONCURRENTLY refuses to run inside a transaction. ALTER TYPE ... ADD VALUE (for ENUM types) could not run inside a transaction block before PG12 and still has caveats. VACUUM, CLUSTER, REINDEX on a system catalog, and CREATE DATABASE all refuse transactional wrapping. The executor handles this by routing transactional steps into the savepoint loop and routing non-transactional steps into a separate NonTransactionalQueue that runs only after every transactional phase has committed. Deferring non-transactional steps to after COMMIT guarantees they only run against a fully committed schema — a failed migration never leaves an orphaned CONCURRENTLY index pointing at a table that was rolled back.

graph TD
    START["Migration starts"] --> ADV["Acquire session
pg_advisory_lock"]
    ADV --> SNAP1["Pre-migration snapshot
drift baseline"]
    SNAP1 --> LOOP{"For each phase"}
    LOOP -->|"transactional phase"| BEGIN["BEGIN"]
    BEGIN --> XLOCK["pg_advisory_xact_lock"]
    XLOCK --> SPS["For each step:
SAVEPOINT"]
    SPS --> EXEC["Execute SQL"]
    EXEC -->|"ok"| COMMIT["COMMIT phase"]
    EXEC -->|"transient error"| RTRB["ROLLBACK TO SAVEPOINT
retry with backoff"]
    RTRB --> SPS
    EXEC -->|"permanent error"| FAIL["ROLLBACK phase
mark failed"]
    LOOP -->|"non-tx phase"| QUEUE["Enqueue to
NonTransactionalQueue"]
    LOOP -->|"phases done"| DRAIN["Drain non-tx queue
after final COMMIT"]
    DRAIN --> SNAP2["Post-migration snapshot"]
    SNAP2 --> DRIFT{"Drift detected?"}
    DRIFT -->|"No"| DONE["completed"]
    DRIFT -->|"Yes"| REVIEW["needs_review"]
    COMMIT --> LOOP
    FAIL --> RECOVER["Crash recovery"]

    style START fill:#6c63ff,color:#fff,stroke:none
    style ADV fill:#00d4ff,color:#1a1d2e,stroke:none
    style SNAP1 fill:#00d4ff,color:#1a1d2e,stroke:none
    style LOOP fill:#ffd740,color:#1a1d2e,stroke:none
    style BEGIN fill:#ff9100,color:#fff,stroke:none
    style XLOCK fill:#ff9100,color:#fff,stroke:none
    style SPS fill:#ff9100,color:#fff,stroke:none
    style EXEC fill:#ff9100,color:#fff,stroke:none
    style COMMIT fill:#00e676,color:#1a1d2e,stroke:none
    style RTRB fill:#ffd740,color:#1a1d2e,stroke:none
    style FAIL fill:#ff4081,color:#fff,stroke:none
    style QUEUE fill:#00d4ff,color:#1a1d2e,stroke:none
    style DRAIN fill:#00d4ff,color:#1a1d2e,stroke:none
    style SNAP2 fill:#00d4ff,color:#1a1d2e,stroke:none
    style DRIFT fill:#ffd740,color:#1a1d2e,stroke:none
    style DONE fill:#00e676,color:#1a1d2e,stroke:none
    style REVIEW fill:#ff9100,color:#fff,stroke:none
    style RECOVER fill:#ff4081,color:#fff,stroke:none

The advisory-lock acquisition itself is straightforward but worth showing, because it sits at the boundary of the engine and the database:

-- Set lock_timeout so a blocked acquire fails fast instead of hanging
SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '5s';

-- Transaction-scoped: auto-released on COMMIT or ROLLBACK
SELECT pg_advisory_xact_lock($1);
-- $1 is the SHA-256-derived bigint key for this connection_id
-- On 55P03 (lock_timeout) the engine throws LockAcquisitionError

Inside a phase, the savepoint loop wraps each step so a single failure does not poison the rest of the phase. The pattern looks like this:

BEGIN;
SELECT pg_advisory_xact_lock($lockKey);

FOR step IN phase.steps LOOP
  SAVEPOINT step_sp;
  BEGIN
    EXECUTE step.sql;
  EXCEPTION WHEN OTHERS THEN
    IF is_transient(sqlstate) AND attempts < 3 THEN
      ROLLBACK TO step_sp;
      PERFORM pg_sleep(backoff(attempts));
      -- retry step
    ELSE
      RAISE;
    END IF;
  END;
END LOOP;

COMMIT;

Transient SQLSTATE codes — 40P01 (deadlock detected), 55P03 (lock not available), 40001 (serialization failure), and the 08 connection class — are retried up to three times with exponential backoff (1s, 2s, 4s, capped at 10s). Permanent SQLSTATE codes — syntax errors, unique violations, foreign-key violations — are not retried; the phase fails immediately, the executor rolls back, and the migration record is marked failed with the failing step's SQL and error message attached.

Storage and Migration History

Storage persists the audit trail. Every migration — successful, failed, partially applied, rolled back, or marked for manual review — is recorded in a migration_history table that lives in the same database the engine migrates. The table is created on first run if it does not exist, and it carries more than sixty columns covering migration identity (id, version, name, connection_id), execution state (status, status_previous, status_changed_at, started_at, completed_at, applied_at, last_heartbeat_at), lock and process context (lock_key, lock_pid, executor_pid, executor_hostname), phase and step progress (current_phase, current_step_id, phase_count, step_count), the schema diff that was planned, the SQL statements that were generated, the execution results that were observed, the rollback SQL if reversible, and a JSONB metadata column for everything else.

An execution log table sits alongside migration_history and records one row per step attempt — step_id, attempt number, status, duration, row count, error message if any. This trace is what makes crash recovery possible: it lets the recovery system distinguish a step that committed from a step that failed without re-running every check against the live database. The trace augments introspection but never overrides it; introspection remains the source of truth.

erDiagram
    Migration ||--o{ MigrationStep : has
    Migration ||--|| RiskAssessment : has
    Migration ||--o{ Snapshot : captures
    Migration ||--o{ Warning : raises
    MigrationStep ||--o{ Warning : may_raise

    Migration {
        uuid id PK
        string version
        string name
        string connection_id
        string status
        timestamp started_at
        timestamp completed_at
        bigint lock_key
        int lock_pid
        timestamp last_heartbeat_at
        jsonb schema_diff
        jsonb sql_statements
        jsonb execution_results
        jsonb rollback_sql
    }
    MigrationStep {
        uuid migration_id FK
        string step_id PK
        int phase
        string description
        text sql
        string risk_level
        boolean is_transactional
        string status
        int duration_ms
        text error
    }
    RiskAssessment {
        uuid migration_id FK
        string overall_risk
        jsonb categories
        jsonb warnings
        boolean blocked
        string block_reason
    }
    Snapshot {
        uuid migration_id FK
        string kind
        jsonb schema_snapshot
        timestamp captured_at
    }
    Warning {
        uuid migration_id FK
        string step_id
        string category
        string severity
        text message
        jsonb recommendation
    }

The schema above is the conceptual model; the physical migration_history table denormalizes the one-to-one relationships (RiskAssessment, the pre and post Snapshots) into columns on the migration row, and keeps the one-to-many relationships (MigrationStep, Warning) in their own tables. The denormalization is intentional: a single-row read gives the executor everything it needs to make a recovery decision without joins, which matters when recovery is happening under a held advisory lock.

State Machine and Crash Recovery

The state machine is what makes the engine safe to kill. A migration that crashes mid-execution — process died, database connection dropped, host restarted — leaves behind a row in migration_history marked running or verifying or completing. Without a state machine, that row sits in limbo forever. The next migration sees the advisory lock held (or, worse, sees it released because the connection dropped) and either blocks indefinitely or runs concurrently with the orphaned migration.

The state machine defines twelve statuses and the valid transitions between them: pending, acquiring_lock, running, verifying, completing, completed, partially_applied, failed, stale, rolling_back, rolled_back, and needs_review. Every transition is validated — completed → running is rejected, failed → completed is rejected, only the paths in the transition table are allowed. The state machine also keeps status_previous, status_changed_at, started_at, completed_at, applied_at, and last_heartbeat_at accurate without the caller having to manage them.

stateDiagram-v2
    [*] --> pending
    pending --> acquiring_lock
    pending --> stale: heartbeat timeout
    pending --> failed: never started 5min

    acquiring_lock --> running
    acquiring_lock --> failed
    acquiring_lock --> stale

    running --> verifying
    running --> failed
    running --> stale
    running --> needs_review
    running --> completed

    verifying --> completing
    verifying --> failed
    verifying --> stale

    completing --> completed
    completing --> partially_applied
    completing --> failed
    completing --> stale

    stale --> needs_review: recovery partial
    stale --> completed: ghost migration detected
    stale --> failed: rollback detected
    stale --> pending: retry
    stale --> rolling_back

    needs_review --> pending
    needs_review --> completed
    needs_review --> failed
    needs_review --> rolling_back

    partially_applied --> pending
    partially_applied --> failed
    partially_applied --> rolling_back
    partially_applied --> rolled_back

    failed --> pending
    failed --> rolling_back

    rolling_back --> rolled_back
    rolling_back --> failed

    completed --> rolling_back
    rolled_back --> [*]

Heartbeats are how the engine tells live migrations from dead ones. While a migration is in an active status — acquiring_lock, running, verifying, or completing — the executor refreshes last_heartbeat_at on a configurable interval (default 30 seconds). The CrashRecovery component scans for active migrations whose heartbeat is older than three times the interval (so 90 seconds by default) and marks them stale. Legacy rows without a heartbeat fall back to a 30-minute threshold on created_at. Pending rows that never started for 5 minutes are marked failed.

Once a migration is marked stale, the recovery system takes over. It walks the migration's planned changes and asks the database, for each change, whether that change is actually present in the live schema. The check is per-object-type: information_schema.tables for tables, pg_indexes for indexes, information_schema.table_constraints for constraints, pg_policies for RLS policies, and so on. The execution-log trace is consulted first — steps logged as completed are trusted as applied without a database round-trip, and steps logged as failed are treated as not-applied. The trace augments introspection; it does not override it.

Three outcomes are possible. If every planned change is present in the live schema, the migration is a ghost migration — the engine crashed after the work committed but before it could update its own row. The state machine transitions stale → completed, and the migration is recorded as completed with a ghost_migration detection marker. If no changes are present, the transaction was rolled back by PostgreSQL, and the migration transitions stale → failed. If some but not all changes are present, the migration transitions stale → needs_review and the partial list is surfaced for human inspection — there is no fully-automatic recovery for partial state, because the safe next step depends on what specifically half-applied.

The Six Safe Patterns

Safe patterns are the engine's answer to operations that are dangerous in their naive form but have a well-known multi-step alternative that is safe. When the SmartMigrator recognizes one of these operations, it replaces the single dangerous step with the safe multi-step sequence. The risk tagger records the original risk level and the safe-pattern transformation so the audit trail shows both what was requested and what was actually run.

graph LR
    HAZ["Hazardous change"] --> P1["Expand-Contract"]
    HAZ --> P2["Add Column Nullable"]
    HAZ --> P3["CREATE INDEX CONCURRENTLY"]
    HAZ --> P4["ADD CONSTRAINT NOT VALID"]
    HAZ --> P5["Type change USING clause"]
    HAZ --> P6["Backfill before NOT NULL"]

    P1 --> R1["zero downtime"]
    P2 --> R2["no table rewrite"]
    P3 --> R3["no write blocking"]
    P4 --> R4["no table scan"]
    P5 --> R5["explicit cast control"]
    P6 --> R6["no constraint failure"]

    style HAZ fill:#ff4081,color:#fff,stroke:none
    style P1 fill:#ff9100,color:#fff,stroke:none
    style P2 fill:#ff9100,color:#fff,stroke:none
    style P3 fill:#ff9100,color:#fff,stroke:none
    style P4 fill:#ff9100,color:#fff,stroke:none
    style P5 fill:#ff9100,color:#fff,stroke:none
    style P6 fill:#ff9100,color:#fff,stroke:none
    style R1 fill:#00e676,color:#1a1d2e,stroke:none
    style R2 fill:#00e676,color:#1a1d2e,stroke:none
    style R3 fill:#00e676,color:#1a1d2e,stroke:none
    style R4 fill:#00e676,color:#1a1d2e,stroke:none
    style R5 fill:#00e676,color:#1a1d2e,stroke:none
    style R6 fill:#00e676,color:#1a1d2e,stroke:none

The six patterns the engine implements automatically are summarized in the table below. Each one targets a specific PostgreSQL hazard and replaces it with a sequence that avoids the hazard.

The NOT NULL pattern is the most elaborate. Direct ALTER TABLE ... ALTER COLUMN ... SET NOT NULL scans the entire table to verify no NULLs exist, holding an AccessExclusiveLock for the duration. On a 2TB table that is hours of downtime. The safe decomposition is three steps: add a CHECK constraint NOT VALID (no scan, no long lock), validate the CHECK constraint (scans the table but takes only ShareUpdateExclusiveLock, so writes proceed), and finally SET NOT NULL plus DROP the CHECK (no scan because the CHECK is already validated, fast metadata-only change). The end state is identical to the naive operation, but the table was never unavailable for writes.

The Five Risk Levels

Risk levels are how the engine decides what to run automatically and what to block. A migration's overall risk is the maximum of its steps' risks, and the caller configures an allowRiskBelow threshold (default: critical means anything below critical is allowed). The table below summarizes the five levels with concrete examples.

The escalation rules are important. A single change can be escalated by multiple checks: a column data-type change starts at medium, escalates to high if the cast is unsafe, escalates to critical if the cast is impossible (no path exists), and escalates further to critical if the change requires a PG version newer than the target server. The highest applicable level always wins, and the categories list on the risk object records every check that fired.

End-to-End Migration Flow

The end-to-end flow ties the modules together. When engine.migrate(pool, desired) is called, the engine runs the full pipeline from introspection through recording, with optional dry-run and approval gates between risk tagging and execution.

sequenceDiagram
    participant CLI as CLI / Caller
    participant ENG as SwMigrationEngine
    participant INT as Introspector
    participant DIF as Differ
    participant PLN as Planner
    participant RSK as Risk Tagger
    participant EXE as Executor
    participant STO as Storage
    participant DB as PostgreSQL

    CLI->>ENG: migrate(pool, desiredSchema)
    ENG->>INT: introspect(pool)
    INT->>DB: parallel catalog queries
    DB-->>INT: pg_catalog rows
    INT-->>ENG: SchemaSnapshot (current)
    ENG->>DIF: diff(desired, current)
    DIF-->>ENG: SchemaDiff (change list)
    ENG->>PLN: plan(diff)
    PLN-->>ENG: MigrationPlan (phased steps)
    ENG->>RSK: tagAll(plan.steps)
    RSK-->>ENG: tagged plan + overall risk
    ENG->>CLI: dry-run output (if requested)
    CLI->>ENG: approve (if risk >= threshold)
    ENG->>EXE: execute(pool, plan)
    EXE->>DB: pg_advisory_lock
    EXE->>DB: pre-migration snapshot
    EXE->>DB: BEGIN → steps → COMMIT (per phase)
    EXE->>DB: drain non-tx queue
    EXE->>DB: post-migration snapshot
    EXE->>STO: write migration_history row
    STO->>DB: INSERT migration_history
    EXE-->>ENG: MigrationResult
    ENG-->>CLI: result.migrationId

The dry-run gate is where most production deployments stop the first time a plan is generated. The dry-run output is the full plan with every step's SQL, risk level, and dependencies — identical to what would run in live mode — but no statement is sent to the database. A reviewer reads the dry-run, approves it (or edits the desired schema and regenerates), and only then does the executor take the advisory lock and start writing.

$ node migrate.js --dry-run --desired schema.json

== Migration plan (dry-run) ==
overall_risk: medium  // 1 high step, 4 low steps
blocked: false
estimated_duration: ~45s (dominated by VALIDATE CONSTRAINT)

phases:
  [4] types                1 step
  [6] tables               2 steps (add columns, nullable)
  [12] constraints         2 steps (FK NOT VALID, then VALIDATE)
  [23] concurrent indexes  1 step (non-tx)

-- Review the plan above. Re-run without --dry-run to apply.

Testing Strategy

The engine ships with 378 tests across five categories: unit tests for DDL generation, type definitions, and utility functions; audit tests for the risk engine, planner, and executor that verify cross-layer behavior; integration deep-dives that validate each object type end-to-end against a real PostgreSQL instance; a legacy integration suite that runs the full pipeline against representative schemas; and version-specific tests that exercise PG14, PG15, PG16, PG17, and PG18 compatibility in isolation. Behavioral tests — the most important category — assert what the engine does, not how it does it: given this desired schema and this live database, the engine produces this plan with these risk levels and this DDL. They survive internal refactors because they assert on observable outputs, not implementation details.

PostgreSQL Version Support

The engine supports PostgreSQL 10 through 18. The version detector queries current_setting('server_version_num') once at introspection time and exposes the major version to every downstream module. PG15 enabled NULLS NOT DISTINCT on unique constraints and security_invoker view options; PG16 added ALTER TYPE RENAME VALUE and ICU collation rules; PG17 added ALTER TABLE SET EXPRESSION for generated columns, the MAINTAIN privilege, and login event triggers; PG18 added virtual generated columns (GENERATED ALWAYS AS ... VIRTUAL) and NOT ENFORCED constraints. The test suite is run against PG 18.1 in CI to catch regressions on the latest stable release, and PG19 features (MERGE PARTITIONS, SPLIT PARTITION, unenforced partitioned constraints) are stubbed for future support.

License

The engine is published under the Business Source License 1.1 (BSL). The license permits free use for development, testing, evaluation, and non-production environments. Production use requires a commercial license — contact vivek@vivekmind.com. The BSL includes a change date of 2030-01-01, at which point the license automatically converts to Apache 2.0 for all users. The change date is a commitment: even if commercial priorities shift, the engine becomes fully open-source on that date regardless. We chose BSL over a more permissive license because we want to fund continued development through commercial use while keeping the source readable and the development path free for everyone.

Get Started

The engine runs anywhere Node.js does. Install it from npm:

$ npm install pg-migration-engine

Then run a one-shot migration in three lines:

import pg from 'pg';
import { SwMigrationEngine } from 'pg-migration-engine';

const pool = new pg.Pool({ ... });
const engine = new SwMigrationEngine();

const result = await engine.migrate(pool, desiredSchema);
console.log('Migrated:', result.migrationId);

The full source is on GitHub, the package is on npm, and the architecture wiki (including the supported-objects matrix, safe-patterns catalog, risk-assessment guide, and PG-version compatibility table) lives alongside the repo. The engine also powers the schema operations inside Schema Weaver — connect a database in the SQL Editor or browse data with the Data Explorer and the same introspector, differ, and risk tagger that run in your CI are running behind the UI. Full documentation is at docs.schemaweaver.vivekmind.com, and you can read more engineering breakdowns on the VivekMind blog.

Existing Migration Engines (Migration Tool Landscape)

The PostgreSQL migration ecosystem is crowded, and we want to be honest about where the Schema Weaver Migration Engine sits relative to the other tools teams already use. The table below lists the established migration engines, their primary model, and the language or runtime they target. None of these tools are bad — most of them are battle-tested in production at scale — but each one makes different trade-offs than we do, and we built our engine to fill a gap we kept hitting.

Our differentiation is not "we do migrations and they do not." Every tool above does migrations. Our differentiation is the combination of state-based introspection of 50+ PostgreSQL object types, automatic application of six safe patterns, five-level risk classification with configurable blocking, first-class non-transactional DDL handling, and a crash-recovery state machine that distinguishes ghost migrations from partial failures. If your current tool works for you, we are not trying to rip it out — but if you have ever lost a night to a CONCURRENTLY index that blocked writes, a NOT NULL constraint that locked a 2TB table, or a 2am DBA fix that drifted out of your migration history, those are the failures our architecture was designed to prevent.