Skip to content

Architecture

Jump to bottom
Vivek edited this page Jul 14, 2026 · 2 revisions

Architecture

The migration engine processes schema changes through 8 distinct modules (7 layers + Behavioral module):

SQL ──► Introspector ──► Translator ──► Differ ──► DDL Generator ──► Planner ──► Executor
                                │                      │                         │
                                ▼                      ▼                         ▼
                           Risk Tagger          Safe Patterns              Storage
                                                         │
                                                         ▼
                                                   Behavioral
                                                  (Track 2)

Module Overview

Module Layer # Purpose
Introspector 1 Capture live database schema
Translator 2 Normalize catalog rows to canonical model
Differ 3 Detect changes between snapshots
DDL Generator 4 Generate PostgreSQL DDL statements
Planner 5 Order changes by dependencies
Executor 6 Apply migration safely
Storage 7 Persist migration history
Behavioral — Handle functions, views, triggers, policies

Layer 1: Introspector

Purpose: Capture live database schema from PostgreSQL catalogs.

What it does:

  • Queries pg_catalog and information_schema for all 50+ object types
  • Detects PostgreSQL version (10-18) and adapts queries accordingly
  • Handles version-specific features (e.g., multiranges in PG14+, temporal tables in PG18)

Output: SchemaSnapshot — complete database state

const snapshot = await engine.introspect(pool);
// snapshot.tables, snapshot.indexes, snapshot.constraints,
// snapshot.views, snapshot.functions, snapshot.triggers, ...

Layer 2: Translator

Purpose: Normalize raw PostgreSQL catalog rows into canonical schema model.

What it does:

  • Converts snake_case catalog names to camelCase
  • Resolves OID references to qualified names
  • Handles inheritance, partitions, dependencies
  • Merges data from multiple catalog tables

Input: Raw pg_catalog query results
Output: Canonical SchemaSnapshot object

Layer 3: Differ

Purpose: Detect changes between two schema snapshots.

What it does:

  • Classifies changes: CREATE, ALTER, DROP, RENAME
  • Property-level diff for ALTER changes
  • Smart rename detection (avoids DROP+CREATE when object was renamed)
  • Detects implicit changes (e.g., dropping a table drops its indexes)

Input: desired schema + current schema
Output: SchemaDiff with list of Change[]

const diff = engine.diff(desired, current);
// diff.changes = [
//   { changeType: 'CREATE', objectType: 'table', objectKey: 'public.users' },
//   { changeType: 'ALTER', objectType: 'column', objectKey: 'public.users.email', properties: [...] }
// ]

Layer 4: DDL Generator

Purpose: Generate valid PostgreSQL DDL statements.

What it does:

  • Generates CREATE, ALTER, DROP, COMMENT statements
  • Applies safe patterns automatically (6+ safe patterns)
  • Handles quoting, escaping, dollar-quoted strings
  • Version-aware syntax (e.g., NULLS NOT DISTINCT for PG15+)

Safe Patterns Applied:

Pattern Steps
NOT NULL column 1) ADD DEFAULT → 2) SET NOT NULL → 3) DROP DEFAULT
FK constraint 1) ADD NOT VALID → 2) VALIDATE separately
CREATE INDEX Use CONCURRENTLY for zero-downtime
Type change USING clause for safe cast
UNIQUE constraint Create unique index, then convert to constraint
CHECK constraint NOT VALID first, then validate
ENUM ADD VALUE Pre-transaction (PG < 14 requires outside transaction) 
const ddl = engine.generateDDL(diff);
// CREATE TABLE "public"."users" (...);
// ALTER TABLE "public"."users" ALTER COLUMN "email" SET NOT NULL;

Layer 5: Planner

Purpose: Order changes respecting dependencies.

What it does:

  • Topological sort based on object dependencies
  • Phase-based execution (Track 1 before Track 2)
  • Detects circular dependencies
  • Groups changes for optimal execution

Phase Order:

Phase Object Types
0-4 Schemas, Extensions
5-9 Types, Domains, Sequences
10-14 Tables, Columns
15-19 Indexes, Constraints
20-24 Functions, Triggers
25-29 Views, Materialized Views
30+ Policies, RLS

Track Separation:

  • Track 1: Structural objects (tables, indexes, constraints)
  • Track 2: Behavioral objects (functions, views, triggers, policies)
const plan = engine.plan(diff);
// plan.steps are ordered by phase
// plan.blocked = true if any change exceeds risk threshold

Layer 6: Executor

Purpose: Apply migration safely.

What it does:

  • Acquires advisory lock to prevent concurrent migrations
  • Creates savepoints for partial rollback
  • Drift detection (fails if schema changed since introspection)
  • Progress callbacks for UI integration
  • Handles non-transactional operations (e.g., CREATE INDEX CONCURRENTLY)

Safety Mechanisms:

Mechanism Purpose
Advisory Lock Prevents concurrent migrations (key = CRC32 of connectionId)
Drift Detection Fails if database changed since introspection
Savepoints Partial rollback on failure
Fingerprints SHA-256 of schema state for drift detection 
const result = await engine.execute(pool, plan);
// result.migrationId, result.applied, result.failed

Layer 7: Storage

Purpose: Persist migration history.

Backends:

Backend Use Case
PostgreSQL Production — migration_history table
Memory Testing — in-memory store
File Local development — JSON files
GitHub CI/CD — commit-based history

Schema:

CREATE TABLE migration_history (
  id UUID PRIMARY KEY,
  applied_at TIMESTAMPTZ DEFAULT NOW(),
  connection_id UUID NOT NULL,
  snapshot_before JSONB,
  snapshot_after JSONB,
  changes JSONB,
  ddl TEXT[],
  duration_ms INTEGER,
  status TEXT
);

Module 8: Behavioral

Purpose: Handle behavioral objects separately from structural DDL.

What it does:

  • Processes functions, views, triggers, policies, rules
  • Handles RLS (Row Level Security) policies
  • Manages event triggers and aggregate functions
  • Processes AFTER structural DDL completes

Object Types:

  • Views (regular and materialized)
  • Functions (scalar, aggregate, window)
  • Procedures
  • Triggers (table and event)
  • Policies (RLS)
  • Rules
// Behavioral objects processed after structural
const result = await engine.execute(pool, plan, {
  behavioral: true  // Include Track 2 objects
});

Data Flow

1. Introspect current DB
   │
   ▼
2. Compare with desired schema
   │
   ▼
3. Generate DDL statements
   │
   ▼
4. Plan execution order
   │
   ▼
5. Risk assessment
   │
   ├── BLOCKED? → Stop, show warnings
   │
   └── Continue
       │
       ▼
6. Execute with locks
   │
   ▼
7. Apply behavioral DDL
   │
   ▼
8. Store history

Error Handling

Error Type Recovery
IntrospectionError Fails fast, returns partial results
DiffError Returns changes + warnings
DDLGenerationError Skips unsupported, continues
PlanningError Circular deps → report and fail
ExecutionError Rollback to savepoint, report position
DriftDetectedError Fail, require re-introspection

Performance

Operation Typical Time
Introspect (100 tables) 500ms - 2s
Diff < 100ms
DDL Generation < 50ms
Plan < 50ms
Execute (varies by DDL) Depends on PostgreSQL

See Also

Clone this wiki locally