Diff and drift detection

forge diff and db.$diff() compare your schema (the source of truth) against what is actually in the database, then emit a structured drift report. forge diff apply and the runtime db.$migrate() browser path use the same machinery to apply the safe subset automatically.

This file is the deep reference for both the CLI and the runtime path. The README's Creating tables and migrations chapter is the surface tour and MIGRATIONS.md covers the full migration lifecycle (push → diff → apply → rollback) — read those first if you want context for the workflow. This document is about the differ itself: the IR-vs-introspect comparator, the DriftItem taxonomy, what counts as drift on each dialect, what diff apply is willing to emit unattended, and what the 2.5.1 runtime drift-apply pass returns.

Source of truth: src/scripts/diff-core.ts (the comparator), src/scripts/diff.ts (CLI entry), src/scripts/diff-apply.ts (apply entry), src/scripts/migrate-gen.ts (SQL generator), src/wasm/drift-apply.ts (runtime drift-apply), src/wasm/migrate.ts (runtime entry).

Contents

• What diff does, end-to-end
• forge diff CLI — flags, exit codes, output shapes
• forge diff apply — the reconciliation generator
• Runtime equivalents — db.$diff() and db.$migrate()
• The DriftItem taxonomy
• Safe-apply vs pending — what runs unattended
• Per-dialect quirks
• Ignore patterns
• CI integration
• RuntimeApplyReport — the 2.5.1 shape
• Common scenarios
• Related

---

What diff does, end-to-end

The differ is pure — no IO of its own. It takes two inputs and returns a DriftReport:

   schema.ts                live DB
       │                       │
       ▼                       ▼
expectedFromSchema()    adapter.introspect()
       │                       │
       └──────────┬────────────┘
                  ▼
        diffIntrospection()
                  │
                  ▼
            DriftReport

• expectedFromSchema(schema) — walks every model(...) in the schema map, derives the tables, columns, indexes, FK edges, and views forge would emit at forge push time. Output is an ExpectedTable map keyed by collection name plus a separate views array.
• adapter.introspect() — reads the live DB through the same introspect path each adapter uses for compile.<op>(). Returns a DbIntrospection snapshot — tables with columns, indexes (full 2.2+ shape: method, where, include, expression, partialFilterExpression, collation, wildcardProjection, keySpec), FKs, views.
• diffIntrospection(schema, actual, ignore) — produces the DriftReport. Items, an inSync boolean (true only when items is empty), and an ignored list of names that matched an ignore pattern.

The split matters: expectedFromSchema is dialect-agnostic — same logic for every adapter. adapter.introspect() is per-adapter (the queries against information_schema, pg_catalog, sqlite_master, listIndexes). The differ never knows what dialect it's looking at past the comparator's "should I check column types?" branch (SQLite + Mongo are dynamic / schemaless, so type comparison is skipped on those).

The CLI and the runtime call the same diffIntrospection. The CLI prints the report; the runtime returns it. The 2.5.1 drift-apply pass takes the report and runs the safe subset.

---

forge diff CLI — flags, exit codes, output shapes

npx forge diff                              # human-readable report
npx forge diff --json                       # machine-readable
npx forge diff --check                      # exit 3 if drift found (CI gate)
npx forge diff --ignore=logs,/^_atlas_/i    # skip noisy meta-tables
npx forge diff --schema=./src/schema.ts     # override the schema cascade

diff is read-only — it opens a connection, runs the introspect path, prints the report, disconnects. It never writes DDL, never touches _forge_migrations, never modifies a column.

Flags

Flag	Effect
--json	Print DriftReport as JSON instead of the bullet-list. CI-friendly; pipe through jq to filter by kind / direction.
--check	Exit 3 (not 0) when inSync: false. Use this in CI to fail the job on drift.
--ignore=<list>	Comma-separated names or /regex/flags. Stacks on top of FORGE_DIFF_IGNORE env var (additive — neither overwrites the other).
--schema=<path>	Override the schema-resolution cascade. FORGE_SCHEMA_PATH env var works the same way.

Output shape (--json)

{
  "dialect": "postgres",
  "items": [
    { "kind": "column", "direction": "missing", "table": "users",
      "detail": "column 'email_verified_at'" },
    { "kind": "index",  "direction": "extra",
      "table": "users", "detail": "unique index u:legacy_email in DB but not in schema" },
    { "kind": "index",  "direction": "mismatch",
      "table": "users", "detail": "index 'idx_email_active' method: schema=gin db=btree" }
  ],
  "inSync": false,
  "ignored": ["hdb_catalog", "_atlas_meta"]
}

• dialect — postgres / mysql / sqlite / duckdb / mssql / mongo.
• items[].kind — table / column / index / foreignKey / view / columnType.
• items[].direction — missing (in schema, not in DB), extra (in DB, not in schema), mismatch (deep-field drift, e.g. wrong index method).
• items[].table — the table or collection name. For view drift this is the view name.
• items[].detail — a human-readable string. Stable enough to grep on, not stable enough to parse — let kind/direction/table drive any tooling.
• inSync — true only when items is empty. ignored doesn't affect this.
• ignored — names that matched an ignore pattern. Always surfaced so silent filtering can't hide real drift.

Human-readable output

✗ drift detected on postgres (3 issues):
  − [column] users: column 'email_verified_at'
  + [index] users: unique index u:legacy_email in DB but not in schema
  ≠ [index] users: index 'idx_email_active' method: schema=gin db=btree
  (ignored 2 tables: hdb_catalog, _atlas_meta)

The sigils — − missing, + extra, ≠ mismatch — match the JSON direction field. The (ignored …) line at the bottom only appears when patterns matched something; absence of the line means the ignore list didn't do anything.

The in-sync form:

✓ no drift — live postgres schema matches forge schema
  (ignored 3 tables: hdb_catalog, _atlas_meta, system.profile)

Exit codes

Exit	Meaning
0	No drift. inSync: true.
3	Drift detected. Only emitted when --check is set; without it, drift exits 0 (the report is the output).
1	Something went wrong before the diff ran — no DATABASE_URL, unreachable DB, schema didn't load, adapter has no introspect implementation.

The 3-vs-1 split lets a CI job distinguish "actual drift" from "couldn't even reach the DB". Pin a job's failure semantics on exit_code == 3 to catch the former without false-positive on the latter.

---

forge diff apply — the reconciliation generator

npx forge diff apply              # generate reconciliation migration + apply
npx forge diff apply --dry        # print the SQL only; don't apply, don't write the file

diff apply is the read-write sibling of forge diff. It generates a SQL migration file with up (bring DB forward) and down (inverse) sections, applies it statement-by-statement, and records the row in _forge_migrations. Files land in ./migrations/<timestamp>_drift.sql.

This is the path you take when you want the SQL on disk before running it, or when you need a per-deploy artefact recording "the change applied on this date". For pure additive drift (new columns, new indexes), forge push is simpler — it does the same thing without writing a file. For destructive drift (dropping a column, dropping a table), diff apply is the only path — push is additive-only by design.

Mongo is unsupported

[forge:diff:apply] Mongo uses forge:push for index management, not SQL migrations.

Mongo has no DDL — index management is a createIndex call against the live database, idempotent on the server. There's no "apply a migration file" path that makes sense. Re-run forge push against Mongo; that's the equivalent.

The change preview

Before applying, diff apply prints the change list:

[forge:diff:apply] schema: /Users/me/app/src/schema.ts
[forge:diff:apply] 3 change(s):
  • add users.email_verified_at
  • drop sessions.legacy_token
  • drop table audit_old

If anything in that list isn't what you want, Ctrl-C. There is no --accept-data-loss flag. The way to preview without applying is --dry, which prints the full SQL plus does not write the file:

--- migration (dry run) ---
-- forge migration: 20260624T143052_drift
-- generated: 2026-06-24T14:30:52.413Z

-- up
ALTER TABLE "users" ADD COLUMN "email_verified_at" TIMESTAMPTZ;
ALTER TABLE "sessions" DROP COLUMN "legacy_token";
DROP TABLE IF EXISTS "audit_old";

-- down
DROP TABLE IF EXISTS "audit_old"; -- cannot auto-restore dropped table
ALTER TABLE "sessions" ADD COLUMN "legacy_token" text;
ALTER TABLE "users" DROP COLUMN "email_verified_at";

Notice the down for "extra table" is a comment, not real SQL. diff apply never invents a recreate plan for a dropped table because the data is gone — the down side is best-effort, not lossless. The same caveat applies to DROP COLUMN: the down ADDs the column back with the type the introspect saw at apply time, but not the data.

What diff apply generates per drift item

The full source is src/scripts/migrate-gen.ts. Summary table:

Drift item	What diff apply emits	Notes
table missing	Comment ("create table 'x' via forge:push (full DDL)")	The full CREATE TABLE goes through forge push because table creation pulls in column DDL, defaults, FK constraints, and FTS shadows — too much to inline in a one-table-per-statement reconciliation. Run forge push after.
table extra	DROP TABLE IF EXISTS …	down is a comment ("cannot auto-restore").
column missing	ALTER TABLE … ADD COLUMN …	Nullable unless the schema field has a default; never NOT NULL without a constant default. Generated columns and id columns are skipped (they're CREATE-TABLE-only concerns).
column extra	ALTER TABLE … DROP COLUMN …	down is ADD COLUMN with the type introspect read back; data is gone.
columnType mismatch	Not emitted	A type change needs human judgment — narrowing risks truncation, widening can require an INDEX REBUILD on some dialects. diff apply surfaces it in the report; you write the DDL by hand.
index missing	CREATE INDEX with the full 2.2+ shape	Method, partial WHERE, INCLUDE columns, expression body — all translated per-dialect. Unique flag preserved.
index extra (unique)	Not emitted	The differ reports it (someone hand-added a unique constraint). To match it back, fold the unique into the schema. To drop it, write the DROP INDEX by hand.
index mismatch	Not emitted	An index that differs in method / WHERE / INCLUDE / expression needs DROP INDEX then CREATE INDEX — which forge push does via its idempotent recreate path. diff apply doesn't try; recreate at push time.
foreignKey missing	ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY	SQLite emits a comment ("SQLite cannot ALTER ADD FK") because the dialect doesn't support adding FKs after table create. MySQL and PG emit the full DDL.
view missing	Not emitted (handled by forge push)	Views are CREATE-time, like tables.

The migration file format

-- forge migration: 20260624T143052_drift
-- generated: 2026-06-24T14:30:52.413Z

-- up
ALTER TABLE "users" ADD COLUMN "email_verified_at" TIMESTAMPTZ;
DROP TABLE IF EXISTS "audit_old";

-- down
DROP TABLE IF EXISTS "audit_old"; -- cannot auto-restore dropped table
ALTER TABLE "users" DROP COLUMN "email_verified_at";

The down section reverses the up order — last-up-first-down — so dependent operations stay in lock-step. forge rollback reads this file back, runs the down block, and removes the row from _forge_migrations. Per-dialect rollback fidelity is in MIGRATIONS.md.

Idempotency on re-run

diff apply checks _forge_migrations before running statements. If the timestamp slug is already there (you re-ran the script), it exits cleanly without re-applying. Same name means same state — a re-run is a no-op, not a double-apply.

Failure handling

If a statement fails mid-batch, diff apply prints the failing SQL and the driver's error, then aborts:

[forge:diff:apply] 3 change(s):
  • add users.email_verified_at
  • drop sessions.legacy_token
  • drop table audit_old
  ✗ failed: ALTER TABLE "sessions" DROP COLUMN "legacy_token"
    column "legacy_token" does not exist

The prior statements remain applied (PG wraps DDL in a transaction, but diff apply runs each statement on its own connection; mid-batch failure leaves the partial state). The migration row is not recorded — re-running will retry, skipping the already-applied parts via dialect-level idempotency (ADD COLUMN IF NOT EXISTS doesn't exist on PG/MySQL, so the retry will fail differently; fix the underlying cause first).

For all-or-nothing semantics on a strict-tx dialect (PG / DuckDB / MSSQL), run the migration file by hand inside a BEGIN; … COMMIT; wrapper. The diff apply flow trades the all-or-nothing property for the ability to record applied state per-statement on dialects where DDL implicitly commits anyway (MySQL / SQLite older than 3.35).

---

Runtime equivalents — db.$diff() and db.$migrate()

The browser path doesn't have a CLI. The equivalents live on the db handle:

db.$diff(opts?) — drift detection

const report = await db.$diff();
// {
//   dialect: 'sqlite',
//   items: [
//     { kind: 'column', direction: 'missing', table: 'users',
//       detail: "column 'email_verified_at'" },
//   ],
//   inSync: false,
// }

Same DriftReport shape as forge diff --json. Works on every adapter that implements introspect — currently all six (PG, MySQL, SQLite, DuckDB, MSSQL, Mongo). The ignore option takes the same IgnoreSpec the CLI accepts via --ignore:

const report = await db.$diff({
  ignore: ['logs', /^_atlas_/i, 'external_events'],
});

$diff never writes. Use it server-side as a programmatic alternative to shelling out to forge diff --json, or in the browser to inspect what $migrate would skip.

db.$migrate(opts?) — runtime DDL apply + drift-apply

const report = await db.$migrate();
// {
//   applied:        ['items', 'forge_items_unique_name'],
//   skipped:        [],
//   failures:       [],
//   alteredColumns: ['items.email_verified_at'],
//   pending:        [],
// }

$migrate is sqlite-only today (the wasm browser path). On other adapters it throws — those go through the CLI. It does, in order:

1. Build full schema DDL from the active schema map (buildSchemaDDL).
2. Apply it via the idempotent CREATE … IF NOT EXISTS pass — every missing table and index lands; existing ones are skipped.
3. (Since 2.5.1) Run a drift-apply pass: introspectSqlite → diffIntrospection → ALTER TABLE … ADD COLUMN for every missing-column drift item that's safe to add. Destructive drift goes to pending.

The drift-apply pass is on by default. Pass { alter: false } to skip it — useful if you're shipping your own migration runner and want $migrate to behave like 2.4 / 2.5.0 (strict create-or-skip).

// 2.5.0 behaviour: don't touch existing tables' shape.
const report = await db.$migrate({ alter: false });
// report.alteredColumns is always [], report.pending is always [].

The full RuntimeApplyReport shape is documented under RuntimeApplyReport — the 2.5.1 shape.

applyDrift — the drift-apply primitive

import { applyDrift } from 'forge-orm/wasm';

const driver = (await createDb({ url: 'opfs:/app.sqlite', schema })).adapter.db;
const driftReport = await applyDrift(driver, { schema });
// {
//   alteredColumns: ['items.email_verified_at'],
//   pending:        [{ kind: 'column', direction: 'extra', ... }],
//   failures:       [],
// }

Exposed for callers who want to run the drift pass on its own — e.g. after a manual $executeRaw schema patch, or against a pre-migrated DB. $migrate calls this internally. Source: src/wasm/drift-apply.ts.

Which path on which adapter

Environment	Use
Production server / CI	forge diff (read), forge diff apply (write)
Local dev (Node)	forge diff
Browser / Electron / Tauri (sqlite-wasm)	db.$diff() (read), db.$migrate() (read + write)
Programmatic on any adapter	db.$diff() works server-side too — same comparator, just no CLI wrapper

The two paths share diffIntrospection (the pure comparator in src/scripts/diff-core.ts) and the per-adapter introspect() calls. What changes is the apply step: the CLI generates a SQL file and runs it via the driver pool; the runtime generates SQL strings and runs them inside the worker's prepared-statement API.

---

The DriftItem taxonomy

Every entry the differ emits matches the same shape:

export interface DriftItem {
  kind: 'table' | 'column' | 'index' | 'foreignKey' | 'view' | 'columnType';
  direction: 'missing' | 'extra' | 'mismatch';
  table: string;
  detail: string;
}

The full classification:

table

Direction	Trigger	Example	Per-dialect
missing	Schema declares model('orders', …) but no table orders exists.	{ kind: 'table', direction: 'missing', table: 'orders', detail: "table 'orders' declared in schema but not in DB" }	Same across SQL. Mongo: same trigger ("collection declared, none in DB"); collections are implicit on first insert so this usually means the collection has truly never been touched.
extra	Table in DB whose name isn't any model's collection.	{ kind: 'table', direction: 'extra', table: 'audit_old', detail: "table 'audit_old' in DB but not in schema" }	Filtered: _forge_migrations, anything matching /_fts/i, matview-backing tables for a declared view.

column

Direction	Trigger	Example	Per-dialect
missing	Field in schema but no column with that name on the matching table.	{ kind: 'column', direction: 'missing', table: 'users', detail: "column 'email_verified_at'" }	Skipped on Mongo (schemaless — no column drift concept).
extra	Column in DB but no field with that name on the matching model.	{ kind: 'column', direction: 'extra', table: 'users', detail: "column 'legacy_token' in DB but not in schema" }	Skipped on Mongo.

columnType

Direction	Trigger	Example
mismatch	Field's category differs from the DB column's category.	{ kind: 'columnType', direction: 'mismatch', table: 'orders', detail: "column 'total': schema=decimal db=text" }

Skipped on SQLite (dynamic typing) and Mongo (schemaless). The category mapping is intentional — it tolerates dialect-specific spellings without false-positiving:

Schema kind	Category	DB types that match
id, objectId, string, text, uuid, enum	string	text, varchar*, char*, uuid, citext
int	int	smallint, integer, int, int4, int2, mediumint
bigint	bigint	bigint, int8
float	float	real, double*, float*
decimal	decimal	numeric*, decimal*
bool	bool	bool*, tinyint(1)
dateTime	datetime	timestamp*, datetime*, date, time*
json, embed, embedMany	json	json, jsonb
arrays, custom types	—	(skipped)

PG text vs varchar(255) doesn't drift. MySQL tinyint(1) vs bool doesn't drift. MSSQL nvarchar(MAX) vs text doesn't drift. The cost is that genuine narrowings — bigint → int say — won't be caught if the field falls outside the table. The trade-off is "don't cry wolf" — false negatives are easier to live with than false positives that erode trust in the report.

index

Direction	Trigger	Example
missing	Index column-set + uniqueness signature in schema but not in DB.	{ kind: 'index', direction: 'missing', table: 'users', detail: 'index u:email,tenant_id' }
extra	Unique-only — extra non-unique indexes don't drift (engines create their own constraint-backing indexes). Extra unique indexes do.	{ kind: 'index', direction: 'extra', table: 'users', detail: 'unique index u:legacy_email in DB but not in schema' }
mismatch	Named index exists on both sides but method, where, include, expression, partialFilterExpression, collation, wildcardProjection, or per-key direction tokens differ.	{ kind: 'index', direction: 'mismatch', table: 'users', detail: "index 'idx_email_active' method: schema=gin db=btree" }

Index mismatch only fires when both sides have an index with the same explicit name. Unnamed indexes participate in the column-set signature check only — the differ won't try to align a generated name across runs (the names embed the column list, so a column change shifts the name and they look like "missing + extra" rather than "mismatch").

Each deep-field mismatch is its own entry, scoped by index name. An index that drifted in both method and where produces two DriftItems, not one merged "something changed":

≠ [index] users: index 'idx_email_active' method: schema=gin db=btree
≠ [index] users: index 'idx_email_active' where: schema=active = true db=∅

foreignKey

Direction	Trigger	Example
missing	Relation declared in schema with a non-id on column, but no FK with matching column → refTable.refColumn in DB.	{ kind: 'foreignKey', direction: 'missing', table: 'invoices', detail: 'user_id->users.id' }

extra and mismatch are not generated for foreign keys today. The differ only knows about FKs the schema declares; an extra FK in the DB stays invisible. Skipped on Mongo (no FKs).

view

Direction	Trigger	Example
missing	model('active_users', { … }, { view: { … } }) in schema but neither a view nor a table with that name in DB.	{ kind: 'view', direction: 'missing', table: 'active_users', detail: "view 'active_users'" }

The "either view or table" check is for PG matviews and table-backed views — a matview surfaced via pg_matviews may not appear in the regular pg_views list, so the differ falls back to "is there anything with this name?" before flagging. View extra and mismatch are not generated.

What is NOT flagged as drift

Non-drift	Why
Column ordering	Schemas declare order in the IR for DDL emit, but introspect just reads back the names. Reordering a model doesn't generate drift.
Default expressions	Each dialect echoes defaults differently — PG normalises gen_random_uuid(), MySQL re-spells CURRENT_TIMESTAMP. Comparing default-expression strings is high-noise / low-signal; the differ skips it.
Nullable / NOT NULL drift on existing columns	The introspect adapters read nullable, but the comparator doesn't compare it. Adding nullability via forge push is safe; removing it (NOT NULL on a nullable column with NULLs) would fail at apply time. The user-driven path is diff apply.
_forge_migrations	The migration ledger — always filtered.
*_fts shadow tables	Engine-managed by FTS5 / GIN tsvector / Mongo text. Always filtered.
Matview backing tables	A PG matview surfaced as a table is matched against the schema view list before being flagged extra.
Non-unique extra indexes	DBs add their own (constraint-backing, auto-stat). Only extra unique indexes drift.
Type comparison on SQLite + Mongo	SQLite types are dynamic; Mongo has no DDL. Column-existence and index drift still apply.

---

Safe-apply vs pending — what runs unattended

The 2.5.1 runtime drift-apply pass (applyDrift in src/wasm/drift-apply.ts) splits every DriftItem into three buckets:

• Safe — emit DDL inside the transaction. Currently: missing columns that meet the "safe to ADD" criteria below.
• Pending — surface in report.pending for the caller to handle. Destructive or otherwise unsafe.
• Failures — emitted but the DB rejected the statement (e.g. constraint conflict at apply time).

The safe slice

Currently only:

• { kind: 'column', direction: 'missing' } where the field passes isSafeAddColumn:
  • Field is not kind: 'id' (primary key columns can't be added with ADD COLUMN — they're CREATE-TABLE-only).
  • Field is not dbGenerated (generated columns are CREATE-TIME concerns on SQLite; ALTER ADD GENERATED doesn't exist).
  • Field is either optional (nullable, the safest add) or has a default set (constant default — literal, now, or autoId). SQLite refuses NOT NULL ADD COLUMN without a constant default; the differ obeys that rule.

Everything else lands in pending.

What stays pending

Drift item	Why it stays pending
{ kind: 'column', direction: 'extra' }	Drop column. Destructive — data loss. The runtime won't do this unsupervised.
{ kind: 'column', direction: 'missing' } with a NOT NULL field and no default	Adding NOT NULL without a default fails on a non-empty table. Surface so the caller can backfill first, then re-run.
{ kind: 'column', direction: 'missing' } with a generated column	ALTER ADD GENERATED isn't supported on SQLite. Pending.
{ kind: 'column', direction: 'missing' } with an id field	Adding a PK column after CREATE TABLE isn't a supported operation. Almost always means the table is missing — which the CREATE pass already handled.
{ kind: 'columnType', direction: 'mismatch' }	Type changes need ALTER COLUMN, which SQLite doesn't support — the only safe path is a full table rebuild (CREATE NEW, INSERT FROM OLD, DROP OLD, RENAME). Out of scope for a drift fix.
{ kind: 'table', direction: 'extra' }	Drop table. Destructive — data loss.
{ kind: 'table', direction: 'missing' }	Should never reach drift-apply — the CREATE pass already handles missing tables. If one does (introspect/diff race), it surfaces in pending.
{ kind: 'index', direction: 'extra' } (unique)	Dropping an unfamiliar unique index could be deliberate (someone hand-added it because the schema is wrong) — surface, don't drop.
{ kind: 'index', direction: 'mismatch' }	An index that drifted in method / where / include / expression. The fix is DROP + CREATE; the runtime won't reach for that on its own.
{ kind: 'foreignKey', direction: 'missing' }	SQLite can't ALTER ADD FK — needs table rebuild. Pending.
{ kind: 'view', direction: 'missing' }	Views are CREATE-time. Pending if introspect missed it on the create pass.

Pending policy on the app side

const report = await db.$migrate();
if (report.pending.length > 0) {
  // Three common policies:
  //
  //  (a) Soft-fail: log + carry on. The app probably still works against
  //      the existing shape; drift is a hint, not an error.
  console.warn('Schema drift not auto-applied:', report.pending);
  //
  //  (b) Hard-reset: wipe the on-device DB. Common pattern in offline-first
  //      apps where the source of truth is the server.
  await dropDatabase();
  await db.$migrate();   // fresh shape from current schema
  //
  //  (c) Prompt the user: export-then-reset. Last-ditch when the data on
  //      device matters and you don't want to silently nuke it.
  await promptUserToExport();
}

The runtime doesn't choose for you. The differ is just the report; what to do with the destructive items is product code.

---

Per-dialect quirks

The differ is dialect-aware in three places: which check it runs, which fields the introspect adapter populated, and how migrate-gen translates a missing index into DDL.

Postgres

• Type comparison runs (PG types are static).
• Introspect reads from pg_class, pg_attribute, pg_index, pg_constraint. Index method is populated (btree, gin, gist, brin, hash). include is the tail of pg_index.indkey after indnatts. where comes from pg_get_expr(indpred).
• Method drift detection — a method: 'gin' schema-side declaration vs btree in DB produces an index mismatch entry. Default btree is assumed if the schema doesn't specify.
• diff apply translates a missing index to CREATE [UNIQUE] INDEX IF NOT EXISTS … [USING <method>] (…) [INCLUDE (…)] [WHERE …].
• Matview backing tables — when a matview surfaces as both a pg_matviews row and a pg_class table, the differ matches the schema view list before flagging as extra.

// Schema declares a GIN index with a partial filter
indexes: [{
  name: 'idx_email_active',
  keys: { email: 1 },
  method: 'gin',
  where: 'active = true',
}],

// DB has a BTREE index without the WHERE clause
// → two drift items:
//   ≠ [index] users: index 'idx_email_active' method: schema=gin db=btree
//   ≠ [index] users: index 'idx_email_active' where: schema=active = true db=∅

MySQL

• Type comparison runs.
• Introspect reads information_schema.tables/columns/statistics/key_column_usage. Index method is normalised to BTREE / FULLTEXT / SPATIAL. Pre-8.0 versions can't read expression-index body — the introspect adapter leaves expression: undefined, and the differ skips the deep check for that field.
• No INCLUDE — covering columns aren't a MySQL concept. A schema-side include: [...] is silently ignored at push time; the differ won't false-positive because actual.indexes[*].include will be undefined.
• Partial-filter indexes translate to CASE WHEN … THEN col END expression indexes when unique: true. The differ deep-compares as expression, not where.
• diff apply doesn't emit USING for SPATIAL / FULLTEXT — those are statement-prefix keywords (CREATE SPATIAL INDEX …).

SQLite

• Type comparison is skipped — SQLite has dynamic typing. Schemas can declare f.string() and the underlying column will accept any value; comparing storage class to declared kind is high-noise.
• Introspect reads sqlite_master + PRAGMA table_info + PRAGMA index_list + PRAGMA index_info. where is parsed out of the CREATE INDEX SQL text in sqlite_master.sql.
• No FK ALTER — SQLite can't ALTER TABLE … ADD CONSTRAINT FOREIGN KEY. A missing FK in the diff produces a comment-only up: -- SQLite cannot ALTER ADD FOREIGN KEY …; recreate the table to add it.
• No ALTER COLUMN — SQLite has no ALTER COLUMN at all. A columnType mismatch can't be fixed by diff apply; the fix is a full table rebuild.
• DROP COLUMN needs SQLite 3.35+ (released 2021-03). diff apply emits the bare ALTER TABLE … DROP COLUMN; on older versions the statement fails and you do the rebuild dance manually (see MIGRATIONS.md § SQLite < 3.35).
• The runtime drift-apply pass is sqlite-only — every pending decision in applyDrift is shaped around SQLite's ALTER restrictions.

DuckDB

• Type comparison runs.
• Introspect reads duckdb_tables / duckdb_columns / duckdb_indexes. DuckDB's index introspection is minimal — method may be undefined, partial where may be undefined. The differ only flags drift on what it can read.
• Spatial extension auto-loads on connect; no --enable-extensions needed.
• diff apply emits the same shape as SQLite — CREATE INDEX IF NOT EXISTS, plain WHERE, no INCLUDE (DuckDB has no covering-index syntax).

MSSQL

• Type comparison runs.
• Introspect reads sys.tables / sys.columns / sys.indexes. INCLUDE is supported and populated.
• GEOGRAPHY and VECTOR(N) are built-in — no extension to install. Geo and vector drift detection works the same as on PG.
• diff apply support — MSSQL is supported by migrate-gen.ts for the additive cases but FK drops use DROP CONSTRAINT IF EXISTS (not DROP FOREIGN KEY).

Mongo

• Type comparison is skipped — schemaless.
• Column drift is skipped — there are no columns. The differ checks table (collection) presence and indexes only.
• Introspect reads db.command({ listCollections }) and collection.listIndexes(). Mongo populates the richest introspect surface: partialFilterExpression, collation, wildcardProjection, keySpec (per-key direction tokens — 1, -1, 'text', '2dsphere', '2d', 'hashed').
• diff apply isn't supported. forge diff apply against a Mongo URL exits with [forge:diff:apply] Mongo uses forge:push for index management, not SQL migrations. Re-run forge push instead — createIndex is idempotent on the server, and Mongo error 85/86 (index spec drift) triggers a drop-and-recreate at push time.
• Partial filter / collation / wildcardProjection comparison uses stable-key JSON so { a: 1, b: 2 } doesn't false-positive against { b: 2, a: 1 }. Collation is projected to user-declared keys before compare so the DB's echoed defaults (numericOrdering: false, strength: 3, …) don't flood the report.

Summary table

Feature	Postgres	MySQL	SQLite	DuckDB	MSSQL	Mongo
Column existence drift	✓	✓	✓	✓	✓	—
Column type drift	✓	✓	— (dynamic)	✓	✓	— (schemaless)
Index existence drift	✓	✓	✓	✓	✓	✓
Index method drift	✓	✓	—	partial	✓	n/a
Index where drift	✓	as expression	✓	partial	✓	—
Index INCLUDE drift	✓	n/a	n/a	n/a	✓	n/a
Index expression drift	✓	✓ (8.0+)	✓	✓	n/a	n/a
Index collation drift	n/a	n/a	n/a	n/a	n/a	✓
Index partialFilterExpression drift	n/a	n/a	n/a	n/a	n/a	✓
wildcardProjection drift	n/a	n/a	n/a	n/a	n/a	✓
keySpec drift	n/a	n/a	n/a	n/a	n/a	✓
FK drift	✓	✓	✓ (report only — no apply)	✓	✓	n/a
View drift	✓	✓	✓	✓	✓	n/a
Extra-table drift	✓	✓	✓	✓	✓	✓ (collection)
diff apply	✓	✓	✓ (3.35+ for drops)	✓	✓	not supported

---

Ignore patterns

npx forge diff --ignore=logs,sessions,/^_atlas_/i,events
export FORGE_DIFF_IGNORE='/^_/i,external_events'
npx forge diff   # CLI flag and env var stack

// Programmatic
const report = await db.$diff({
  ignore: ['logs', /^_atlas_/i, 'events'],
});

Two forms:

• Exact-match string — logs matches the table logs and nothing else.
• /regex/flags — anything wrapped in slashes is parsed as a regex. /^_atlas_/i matches any table starting with _atlas_ case-insensitively.

Items are comma-separated. The CLI flag and FORGE_DIFF_IGNORE env var both feed the same list — the CLI flag stacks on top of the env var (additive), so a fleet-wide default can be extended for a single run without overwriting it.

parseIgnoreList — direct use

import { parseIgnoreList } from 'forge-orm/scripts/diff-core';

parseIgnoreList('logs,/^_atlas_/i,events');
// → ['logs', /^_atlas_/i, 'events']

parseIgnoreList(undefined);
// → []

Import this directly when building tooling around the differ — a custom CI step that calls diffIntrospection programmatically can take the same FORGE_DIFF_IGNORE env var as the CLI by piping it through parseIgnoreList.

Always-ignored

Regardless of flags:

• _forge_migrations — the ledger.
• Any name matching /_fts/i — FTS5 shadow tables and triggers.
• PG matview backing tables that match a declared view name.

These are engine-managed; surfacing them as drift would be permanent noise.

Common ignore patterns by environment

Environment	Pattern	Why
MongoDB Atlas	/^_atlas_/i,/^system\./i	Atlas inserts metadata collections (_atlas_system_query_planner_caches, system.profile).
PostgREST	pgrst_watch,/^pgrst_/i	PostgREST creates trigger-backing tables.
Hasura	hdb_catalog,/^hdb_/i	Hasura keeps its metadata in a sibling schema, but the catalog leaks through.
Supabase	/^auth\./i,/^storage\./i,/^realtime\./i	Supabase's managed schemas — your service doesn't own them.
TimescaleDB	/^_timescaledb_/i,/_hyper_/i	Chunk metadata + hypertable shards.
Litestream replica	/^_litestream_/i	Replica state.
Sibling-service tables	users,plans,user_sessions	Pattern B monorepos — see MIGRATIONS.md § shared DB.

The right answer for any of these is to bake the pattern into FORGE_DIFF_IGNORE in the CI env so every diff job picks it up automatically.

Ignored names are reported

✓ no drift — live postgres schema matches forge schema
  (ignored 3 tables: hdb_catalog, _atlas_meta, system.profile)

Silent filtering would let a real-drift table slip through if someone accidentally wrote a too-broad regex. The trailing summary lets you verify the ignore list is doing what you meant.

---

CI integration

The standard shape: a job that fails the PR when drift is introduced. Two flavours.

(a) Drift gate — fail PRs that introduce drift

The shape: shape a staging DB from origin/main, then diff the PR's schema against it. Drift means "the PR adds DDL that needs to land before app code goes live". MIGRATIONS.md has the full GitHub Actions YAML; the heart of it is:

- run: git worktree add /tmp/main origin/main
- run: ( cd /tmp/main && npm ci --omit=dev && npx forge push )
- id: diff
  run: npx forge diff --check --json | tee diff.json
  continue-on-error: true
- if: steps.diff.outcome == 'failure'
  run: exit 1

--check makes the diff exit 3 on drift; pipe the JSON through actions/github-script to comment it on the PR. The exit code split (3 = drift, 1 = couldn't reach DB) means an unreachable DB doesn't false-positive as drift.

(b) Safe-apply on staging

After the gate passes, a follow-up job auto-applies the safe slice to staging. The jq gate is the trust boundary — forge push is additive-only by design, so this is belt-and-suspenders. Destructive items stop the job before any data is at risk; a human runs forge diff apply with the preview in front of them.

DESTRUCTIVE=$(npx forge diff --json | jq '[.items[] | select(.direction == "extra")] | length')
[ "$DESTRUCTIVE" -gt 0 ] && { echo "manual review required"; exit 2; }
npx forge push

(c) Triage script — auto-apply additive, escalate destructive

// scripts/drift-triage.ts
import { execSync } from 'child_process';

const report = JSON.parse(execSync('npx forge diff --json').toString());
const safe   = report.items.filter((i: any) => i.direction === 'missing');
const unsafe = report.items.filter((i: any) => i.direction !== 'missing');

if (safe.length > 0) {
  console.log(`Auto-applying ${safe.length} additive drift item(s) via forge push`);
  execSync('npx forge push', { stdio: 'inherit' });
}

if (unsafe.length > 0) {
  console.error(`Manual review needed for ${unsafe.length} item(s):`);
  console.error(JSON.stringify(unsafe, null, 2));
  process.exit(2);
}

Run as a CD post-deploy hook. Exit 2 trips an alert; exit 0 means the deploy converged. For Datadog / Prometheus / CloudWatch wiring, see MIGRATIONS.md § post-deploy verify.

---

RuntimeApplyReport — the 2.5.1 shape

db.$migrate() returns this:

export interface RuntimeApplyReport extends ApplyReport {
  applied:        string[];      // names of objects the CREATE pass created (tables, indexes)
  skipped:        string[];      // names the CREATE pass found already in place
  failures:       { name: string; error: string }[];   // CREATE pass + drift-apply failures merged
  alteredColumns: string[];      // 'table.column' for each ADD COLUMN run by drift-apply
  pending:        DriftItem[];   // destructive / unsafe drift left untouched
}

applied and skipped come from the create pass (buildSchemaDDL → applyMigration). failures aggregates both passes — a CREATE failure for forge_users_unique_email and an ALTER failure for users.email_verified_at both land here. The two new fields in 2.5.1 are:

• alteredColumns — entries are <table>.<column> strings, one per ALTER TABLE … ADD COLUMN the drift-apply pass ran. Empty when nothing drifted or when { alter: false } was passed. The whole batch runs inside one transaction; mid-batch failures roll back and surface in failures, not alteredColumns.

• pending — every DriftItem the drift-apply pass declined to fix. The runtime never auto-applies destructive drift; you decide what to do with pending. See Safe-apply vs pending.

Worked examples

Clean boot, then a no-op subsequent boot

On first boot the database file is empty. The CREATE pass lays everything down; the drift-apply pass has nothing to do — applied lists the table + indexes, every other field is empty.

On the next boot everything is already there. IF NOT EXISTS short-circuits the CREATEs (they land in skipped); the drift-apply pass introspects, finds no drift, returns empty.

New nullable column added to the schema since last boot

// schema.ts
const Items = model('items', {
  id:    f.id(),
  sku:   f.string().unique(),
  name:  f.string(),
  notes: f.string().optional(),   // ← added in this release
});

const report = await db.$migrate();
// {
//   applied:        [],
//   skipped:        ['items', 'forge_items_pk', 'forge_items_unique_sku'],
//   failures:       [],
//   alteredColumns: ['items.notes'],
//   pending:        [],
// }

The CREATE pass skipped the existing table. The drift-apply pass diffed and found items.notes missing; f.string().optional() is safe to ADD, so it ran ALTER TABLE "items" ADD COLUMN "notes" TEXT inside a transaction.

Column removed from the schema since last boot

// schema.ts
const Items = model('items', {
  id:   f.id(),
  sku:  f.string().unique(),
  // name was removed
});

const report = await db.$migrate();
// {
//   applied:        [],
//   skipped:        ['items', 'forge_items_pk', 'forge_items_unique_sku'],
//   failures:       [],
//   alteredColumns: [],
//   pending:        [
//     { kind: 'column', direction: 'extra', table: 'items',
//       detail: "column 'name' in DB but not in schema" },
//   ],
// }

pending surfaces the extra column. The runtime doesn't drop it. Your app code can console.warn, prompt the user, or do nothing — drift on extra columns is harmless for reads (the queryer just ignores them).

NOT NULL column added without a default

const Items = model('items', {
  id:    f.id(),
  sku:   f.string().unique(),
  count: f.int(),    // NOT NULL, no default — can't ADD safely
});

const report = await db.$migrate();
// {
//   alteredColumns: [],
//   pending:        [
//     { kind: 'column', direction: 'missing', table: 'items',
//       detail: "column 'count'" },
//   ],
//   ...
// }

The drift-apply pass refused — SQLite rejects ALTER TABLE … ADD COLUMN … NOT NULL without a constant DEFAULT on a non-empty table. Surface, don't try, don't fail. The fix is either to mark the field optional (then backfill via $executeRaw) or to add a default so the ADD becomes safe.

Opt-out: strict 2.5.0 behaviour

const report = await db.$migrate({ alter: false });
// alteredColumns and pending are always [] when alter is false.

{ alter: false } skips the drift-apply pass entirely. Use this if you're shipping your own migration runner and want $migrate to behave like 2.4 / 2.5.0 — strict create-or-skip, no ALTER touches.

---

Common scenarios

(a) Adding a column — the happy path

You added users.email_verified_at to the schema. Three steps:

# Preview the change
DATABASE_URL=$PROD_URL npx forge diff
# ✗ drift detected on postgres (1 issue):
#   − [column] users: column 'email_verified_at'

# Apply
DATABASE_URL=$PROD_URL npx forge push
# applied 1, skipped 47, failures 0

# Verify
DATABASE_URL=$PROD_URL npx forge diff --check
# exit 0

If you put forge push in the app's start command, the apply step is automatic — push is additive-only and idempotent, so it's safe to run on every container start.

(b) Type widening — int → bigint

The differ reports the mismatch:

≠ [columnType] events: column 'event_id': schema=bigint db=int

diff apply doesn't emit a type-change ALTER. The fix is per-dialect:

-- Postgres
ALTER TABLE events ALTER COLUMN event_id TYPE BIGINT;

-- MySQL
ALTER TABLE events MODIFY COLUMN event_id BIGINT NOT NULL;

-- SQLite — no ALTER COLUMN. Full rebuild:
BEGIN;
CREATE TABLE _events_new (event_id BIGINT, …);
INSERT INTO _events_new SELECT * FROM events;
DROP TABLE events;
ALTER TABLE _events_new RENAME TO events;
COMMIT;

-- MSSQL
ALTER TABLE events ALTER COLUMN event_id BIGINT NOT NULL;

Wrap the statement in a db.$executeRaw migration file (see MIGRATIONS.md § data migration for the idempotent ledger-tracked pattern) and run it before re-running forge diff --check.

(c) Type narrowing — bigint → int

The differ reports the same mismatch as widening. The fix is the same DDL pattern but riskier — values larger than INT_MAX will fail the ALTER. The safe order: query for oversized values first, then ALTER, then re-diff to confirm.

const oversized = await db.$queryRaw<{ event_id: string }>(
  raw`SELECT event_id FROM events WHERE event_id > 2147483647`,
);
if (oversized.length > 0) throw new Error(`${oversized.length} rows exceed INT range`);
await db.$executeRaw(raw`ALTER TABLE events ALTER COLUMN event_id TYPE INTEGER`);

Narrowing isn't generated by diff apply for the same reason it isn't generated by forge push — the safe path requires data inspection, not just DDL emit.

(d) Renaming a column — the four-step play

The differ has no rename heuristic. A schema change from email → email_address produces:

✗ drift detected on postgres (2 issues):
  − [column] users: column 'email_address'
  + [column] users: column 'email' in DB but not in schema

diff apply would emit ADD COLUMN email_address + DROP COLUMN email, which silently loses every row's email. The safe pattern is four deploys — see MIGRATIONS.md § blue/green schema rollouts for the full walk-through. Compressed:

Deploy	Schema	DDL emitted	App reads	App writes
1	both, email_address nullable	ADD COLUMN email_address	email	email
2 (data)	—	UPDATE users SET email_address = email via $executeRaw	email	email
3	both, email_address unique	ADD UNIQUE on email_address	email_address	email + email_address
4	only email_address	(via diff apply) DROP COLUMN email	email_address	email_address

The rule: never let diff apply emit both an ADD and a DROP for what's semantically a rename. Stage the change.

(e) Engine-managed tables show as extra

✗ drift detected on postgres (3 issues):
  + [table] hdb_catalog: table 'hdb_catalog' in DB but not in schema
  + [table] hdb_pro_catalog: table 'hdb_pro_catalog' in DB but not in schema
  + [table] auth.users: table 'auth.users' in DB but not in schema

These are Hasura + Supabase managed. Fold them into FORGE_DIFF_IGNORE:

export FORGE_DIFF_IGNORE='/^hdb_/i,/^auth\./i'
npx forge diff
# ✓ no drift — live postgres schema matches forge schema
#   (ignored 3 tables: hdb_catalog, hdb_pro_catalog, auth.users)

Bake the env var into the CI workflow so every diff job picks it up automatically.

(f) Drift after an out-of-band fix

Ops added a unique index to handle an incident:

✗ drift detected on postgres (1 issue):
  + [index] events: unique index u:idempotency_key in DB but not in schema

Two options:

• Fold it in — the unique was a real fix; update the schema so it matches reality and the next forge push is a no-op:

  const Events = model('events', { … }, {
    uniques: [['idempotency_key']],
  });

• Drop it — the unique was a quick patch and shouldn't be there. Hand-write the DROP INDEX since diff apply doesn't emit drops for extra uniques (safer to surface than auto-drop):

  psql "$DATABASE_URL" -c 'DROP INDEX events_idempotency_key_unique;'

Either way, re-run forge diff --check to confirm the report's clean.

(g) Browser app where the device DB drifted

The user shipped on an old app version that created items with three columns. The latest release adds two new nullable columns. On boot:

const report = await db.$migrate();
if (report.alteredColumns.length > 0) {
  console.log(`Schema updated: ${report.alteredColumns.join(', ')}`);
}
if (report.pending.length > 0) {
  // The device's DB has columns the schema doesn't anymore (the old release
  // had them, the new one removed them). Decide policy:
  if (await confirmExport()) await exportDb();
  await dropDb();
  await db.$migrate();
}

The drift-apply pass handles the additive case automatically. The destructive case is product-policy — wipe, prompt, or live with the extra columns (they're harmless for reads).

---

Related

• MIGRATIONS.md — the full migration lifecycle (push / diff / diff apply / rollback), CI workflows, blue/green rollouts, monorepo patterns.
• BROWSER.md — db.$migrate() semantics in the browser, OPFS, multi-tab safety, Safari ITP eviction.
• INDEXES.md — the full IndexDef shape that the deep-field index-drift pass compares against.
• DRIVERS.md — the introspect path each adapter takes.
• README: Creating tables and migrations — surface tour of the CLI commands.
• Source: src/scripts/diff-core.ts (the comparator), src/scripts/diff.ts + src/scripts/diff-apply.ts (CLI), src/scripts/migrate-gen.ts (SQL emit), src/wasm/drift-apply.ts + src/wasm/migrate.ts (runtime).