safe-migrate v0.3.2

A PostgreSQL migration linter that executes a bi-directional state machine simulation over your SQL, combining static typed AST analysis with live database statistics to prevent blocking locks before they reach production.

The Problem: ALTER TABLE users ADD COLUMN status TEXT is safe on 500 rows. On 50M rows, it acquires an ACCESS EXCLUSIVE lock that takes down your app. Standard linters only look at the SQL. safe-migrate looks at the SQL AND the size of the tables it affects.

What's New in v0.3.2

v0.3.2 is a complete internal rewrite. Earlier versions parsed migrations with regex and substring matching, which broke on quoted identifiers, schemas, and anything non-trivial. safe-migrate now walks a typed PostgreSQL AST and runs a full state machine simulation of the migration — including transaction rollbacks, cascading drops, and partition hierarchies — before evaluating any rule.

Highlights:

Typed AST parsing via squawk_syntax (no more string matching)
Transactions are simulated correctly: BEGIN ... ROLLBACK fully restores prior state, including generation counters and pending validations
DROP TABLE ... CASCADE is evaluated against the actual dependency graph (foreign keys, views, partitions), not just the table named in the statement
Table renames are tracked so foreign key and index references stay correct
Search path resolution checks that a schema actually exists before using it
79 passing tests covering the simulator, rule engine, and CLI

✅ Live Database Statistics Integration

The sync command reads from PostgreSQL's catalog:

pg_class.reltuples — estimated row counts
pg_class.relpages — page estimates for TOAST threshold crossing
pg_stat_user_tables.last_analyze — staleness detection
pg_attribute.avg_width — column width for compression decisions
Foreign key graph, index mappings, partition hierarchies

No application credentials needed — sync only requires SELECT on catalog tables.

Installation

From Binary (Recommended)

curl -fsSL https://raw.githubusercontent.com/dsecurity49/safe-migrate/main/install.sh | bash

Supports:

Linux (x86_64, ARM64, musl)
macOS (Intel, Apple Silicon)
Windows (x86_64, ARM64)

From Cargo

cargo install safe-migrate

Quick Start

Step 1: Sync Database Statistics

export DATABASE_URL="postgres://user:password@localhost:5432/mydb"
safe-migrate sync

Creates .safe-migrate-stats.json with table sizes, column info, constraints, and indexes. Safe to commit to source control — contains no secrets, only statistics.

Cache freshness: Warnings if older than 7 days (configurable). Stale stats are flagged in the report.

Step 2: Lint Your Migration

safe-migrate lint --file migration.sql

Output:

Analyzing migration: migration.sql

--------------------------------------------------------------------------------
[FAIL] [TIER 1 - DANGER ] Synchronous FOREIGN KEY constraint addition locks 
                          public.orders and public.auth_users
                          Rule:   blocking-constraint
                          Recipe: Adding a valid CHECK or FOREIGN KEY constraint 
                                  takes an ACCESS EXCLUSIVE lock. Add it as NOT VALID 
                                  first, then VALIDATE it in a separate transaction.
--------------------------------------------------------------------------------
[WARN] [TIER 2 - WARNING] Synchronous index creation on public.orders
                          Rule:   require-concurrent-index
                          Recipe: Index operations block writes. Add the CONCURRENTLY 
                                  keyword.
--------------------------------------------------------------------------------

==================================================
Analysis Complete.
Analysis Confidence : Exact
Tier 1 (Halt Build) : 1
Tier 2 (Warning)    : 1
Tier 3 (Info)       : 0
==================================================

Error: Migration halted: Tier 1 lock detected.

Exit code: 1 (Tier 1 violation) → CI build fails

The Trust Model

Confidence Levels

Level	Meaning	When It Happens
Exact	Analysis is mathematically sound	Pure DDL, no opaque SQL
Tainted	Some DDL is hidden in opaque statements	`DO` blocks, `EXECUTE` statements, dynamic SQL

When confidence is Tainted, the engine:

Still evaluates all visible DDL
Warns that hidden mutations may exist
Does not suppress violations (conservative)

Version-Gating

safe-migrate detects your PostgreSQL version from the cache and applies version-specific rules:

Example: Constant DEFAULT on ADD COLUMN

ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

PG 11+: Metadata-only, no rewrite → ✅ Safe (Tier 3)
PG <11: Table rewrite → ⚠️ Warning (Tier 2 for small tables, Tier 1 for large)

The rule reads pg_version_num from the cache and applies the correct threshold.

Cache Staleness

Tables without recent ANALYZE:

Flagged as [WARNING: Based on stale statistics]
Treated conservatively (assume Tier 2+ severity)
Still evaluated (not suppressed)

Example:

[WARN] [TIER 2 - WARNING] Table statistics are stale. Lock evaluations may be 
                          inaccurate.
                          Rule:   blocking-constraint
                          Recipe: Run ANALYZE to ensure accurate row estimates.

Rules Reference

All 12 rules with examples:

1. blocking-constraint (Tier 1)

Adding a valid CHECK or FOREIGN KEY constraint scans the entire table with an ACCESS EXCLUSIVE lock.

ALTER TABLE orders ADD CONSTRAINT fk_user 
  FOREIGN KEY (user_id) REFERENCES users(id);

Safe alternative:

ALTER TABLE orders ADD CONSTRAINT fk_user 
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;
-- Later, in a separate migration:
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

2. size-aware-add-column (Tier 1)

Adding a column with a volatile DEFAULT requires a table rewrite, even on PG11+.

ALTER TABLE orders ADD COLUMN created_at TIMESTAMP DEFAULT NOW();  -- REWRITE
ALTER TABLE orders ADD COLUMN id UUID DEFAULT gen_random_uuid();   -- REWRITE

Safe alternative (PG11+):

ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';  -- METADATA ONLY

3. type-change-rewrite (Tier 1)

Changing a column type usually requires a full table rewrite with ACCESS EXCLUSIVE lock.

ALTER TABLE users ALTER COLUMN id TYPE BIGINT;  -- REWRITE

Safe alternatives:

Widen varchar(10) → varchar(100) (no rewrite)
Widen numeric(10,2) → numeric(20,2) on PG12+ (no rewrite)

4. concurrent-index (Tier 2)

Synchronous index creation blocks writes. Use CONCURRENTLY.

CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
DROP INDEX CONCURRENTLY idx_users_email;

5. concurrent-in-transaction (Tier 1)

PostgreSQL does not allow CREATE/DROP INDEX CONCURRENTLY inside an explicit transaction block.

BEGIN;
CREATE INDEX CONCURRENTLY idx ON users(id);  -- ❌ ERROR
COMMIT;

6. cascading-drop (Tier 1)

DROP TABLE ... CASCADE silently destroys views, indexes, constraints without warning.

DROP TABLE users CASCADE;  -- ❌ May drop dependent views

Safe alternative:

DROP VIEW dependent_view;
DROP TABLE users;

7. blocking-mat-view-refresh (Tier 2)

REFRESH MATERIALIZED VIEW (without CONCURRENTLY) blocks all reads during refresh.

REFRESH MATERIALIZED VIEW mv_order_totals;

Safe alternative:

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_order_totals;

8. partition-lock (Tier 1/2)

Partition operations that affect large parent tables.

9. opaque-dynamic-sql (Tier 2)

DO blocks and EXECUTE statements hide mutations. Analysis confidence degrades.

DO $$
BEGIN
  EXECUTE 'ALTER TABLE ' || table_name || ' ADD COLUMN id int';
END $$;

Recommendation: Avoid dynamic DDL in migrations. Use explicit SQL.

10. volatile-default (Tier 3)

Using volatile functions like random() or now() as defaults can cause unexpected behavior in logical replication.

11. vacuum-full (Tier 1)

VACUUM FULL requires an ACCESS EXCLUSIVE lock and rewrites the entire table. Never in migrations.

12. idempotency (Tier 3, disabled by default)

Recommend CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS for safer re-runs.

Configuration

Create safe-migrate.toml in your repo root to customize rule behavior and thresholds. All settings are optional — safe-migrate ships with sensible defaults.

Global Settings

# Row count threshold for Tier 1 (default: 100,000)
# Tables with >= this many rows trigger Tier 1 for dangerous operations
tier1_threshold_rows = 100000

# Row count threshold for Tier 2 (default: 10,000)
# Tables with >= this many rows trigger Tier 2 for dangerous operations
tier2_threshold_rows = 10000

# PostgreSQL version to assume when database is offline (default: 100000)
# Format: XXYYZZ (e.g., 100000 = PG 10.0, 110000 = PG 11.0, 170010 = PG 17.0.10)
# Used for version-gated rules like constant DEFAULT on ADD COLUMN (safe on PG11+)
assume_pg_version = 100000

# TOAST column width threshold in bytes (default: 2048)
# Columns wider than this are flagged for TOAST overflow risk
toast_width_threshold_bytes = 2048

# Default row count for unanalyzed tables (default: 10,000)
# Tables with unknown size are treated as having this many rows
default_rows = 10000

# Cache freshness threshold in days (default: 7)
# Warns if .safe-migrate-stats.json is older than this
stale_stats_days = 7

Per-Rule Configuration

Override any rule's tier or thresholds:

[rules.blocking-constraint]
# Stricter thresholds for foreign key constraints specifically
tier1_threshold_rows = 5000
tier2_threshold_rows = 1000

[rules.size-aware-add-column]
# Escalate all table rewrites to Tier 1 regardless of size
tier1_threshold_rows = 0

[rules.missing-idempotency]
# Disable the idempotency rule (don't warn about missing IF NOT EXISTS)
disabled = true

Complete Example

# Global defaults for the whole team
tier1_threshold_rows = 100000
tier2_threshold_rows = 10000
assume_pg_version = 170000   # Assume PG 17 for new staging envs
toast_width_threshold_bytes = 2048
default_rows = 10000
stale_stats_days = 7

# Stricter rules for high-traffic tables
[rules.blocking-constraint]
tier1_threshold_rows = 1000    # Flag FKs on tables >1K rows
tier2_threshold_rows = 100

[rules.concurrent-index]
tier1_threshold_rows = 50000   # Flag non-concurrent indexes on tables >50K rows

# Relax some rules for safer operations
[rules.blocking-mat-view-refresh]
tier1_threshold_rows = 500000  # Only flag materialized view refresh on huge tables

# Disable rules that don't apply to your workflow
[rules.vacuum-full]
disabled = true

Rule Reference

Rule ID	What It Does	Default Tier
`destructive-cascade`	Flags DROP TABLE ... CASCADE operations that affect baseline schema	Tier 1
`size-aware-add-column`	Flags table rewrites for ADD COLUMN with volatile defaults or PG<11 constant defaults	Tier 1
`type-change-rewrite`	Flags type changes that force ACCESS EXCLUSIVE table rewrites	Tier 1
`blocking-constraint`	Flags synchronous CHECK or FOREIGN KEY constraint additions	Tier 1
`blocking-index-constraint`	Flags synchronous PRIMARY KEY or UNIQUE constraint additions via index	Tier 1
`require-concurrent-index`	Flags synchronous index creation	Tier 2
`require-concurrent-drop-index`	Flags synchronous index dropping	Tier 2
`blocking-mat-view-refresh`	Flags synchronous REFRESH MATERIALIZED VIEW (without CONCURRENTLY)	Tier 2
`partition-lock`	Flags partition attach/detach operations on large tables	Tier 1/2
`concurrent-in-transaction`	Blocks CONCURRENTLY index operations inside explicit transaction blocks	Tier 1
`vacuum-full`	Flags VACUUM FULL usage (requires ACCESS EXCLUSIVE lock)	Tier 1
`opaque-dynamic-sql`	Detects dynamic SQL (DO blocks, EXECUTE) that hides mutations	Tier 2
`volatile-default`	Notes volatile functions like `NOW()` or `gen_random_uuid()` in defaults	Tier 3
`missing-idempotency`	Recommends IF NOT EXISTS on CREATE statements (disabled by default)	Tier 3
`table-rewrite-storage`	Flags table rewrites caused by column storage parameter changes	Tier 1
`table-rewrite-access-method`	Flags table rewrites caused by access method changes	Tier 1

Version-Gating Examples

The engine reads assume_pg_version and applies version-specific rules:

PG 11+: Constant defaults are safe

-- With assume_pg_version >= 110000, this is metadata-only (safe):
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

PG <11: Constant defaults require rewrite

-- With assume_pg_version < 110000, same SQL flags as Tier 1:
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

Cache Behavior

If safe-migrate sync hasn't been run or the cache is missing:

Uses assume_pg_version for version-gated rules
Uses default_rows for all unanalyzed tables
Sets confidence to Tainted (since actual row counts are unknown)

If the cache exists and is fresh:

Uses actual pg_version_num from PostgreSQL
Uses actual table row counts from pg_class.reltuples
Sets confidence to Exact (unless dynamic SQL is detected)

CLI Reference

`safe-migrate lint`

safe-migrate lint \
  --file migration.sql \
  --config safe-migrate.toml \
  --cache .safe-migrate-stats.json \
  --no-cache

Flag	Default	Description
`-f, --file`	required	SQL migration file
`--config`	`safe-migrate.toml`	Config overrides
`--cache`	`.safe-migrate-stats.json`	Stats cache
`--no-cache`	false	Use worst-case assumptions (offline mode)

`safe-migrate sync`

export DATABASE_URL="postgres://user:pass@localhost/db"
safe-migrate sync --out prod-stats.json

Flag	Default	Description
`--out`	`.safe-migrate-stats.json`	Cache output path

Requires DATABASE_URL environment variable.

CI/CD Integration

GitHub Actions

name: Safe Migrate

on:
  pull_request:
    branches: [main]

jobs:
  lint-migrations:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0

      - name: Install safe-migrate
        run: |
          curl -fsSL https://raw.githubusercontent.com/dsecurity49/safe-migrate/main/install.sh | bash

      - name: Sync database stats
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: safe-migrate sync --out prod-cache.json

      - name: Lint changed migrations
        run: |
          FILES=$(git diff --name-only origin/main...HEAD -- '*.sql')
          
          if [ -z "$FILES" ]; then
            echo "No migrations changed."
            exit 0
          fi
          
          for f in $FILES; do
            echo "Linting $f..."
            safe-migrate lint --file "$f" --cache prod-cache.json
          done

GitLab CI

lint-migrations:
  image: ubuntu:latest
  script:
    - curl -fsSL https://raw.githubusercontent.com/dsecurity49/safe-migrate/main/install.sh | bash
    - safe-migrate sync --out prod-cache.json
    - |
      git diff --name-only origin/main...HEAD -- '*.sql' | while read f; do
        safe-migrate lint --file "$f" --cache prod-cache.json
      done
  only:
    - merge_requests

Architecture

safe-migrate parses your migration into a typed AST, then simulates it statement-by-statement against an in-memory model of your schema (tables, columns, indexes, foreign keys, views, partitions). That model starts from your synced database statistics and is updated as each statement is applied — so by the time a rule runs, it's checking against the schema as it would actually look at that point in the migration, not just the raw SQL text.

This is what allows things like:

Correctly evaluating a DROP TABLE ... CASCADE against everything that actually depends on it
Knowing a table was renamed earlier in the same file when checking a later ALTER TABLE
Treating BEGIN ... ROLLBACK as a no-op on the schema, rather than analyzing the in-transaction state as if it persisted

DML statements (INSERT, UPDATE, DELETE, SELECT) are ignored. Dynamic SQL (DO blocks, EXECUTE) is detected and flagged, since it can hide schema changes the simulator can't see.

Why This Matters

PostgreSQL lock behavior is invisible in the SQL itself. The same ALTER TABLE statement is a no-op on one table and an outage on another, depending entirely on size, version, and what else depends on it. safe-migrate makes that visible before you deploy, not after.

License

Dual-licensed under MIT or Apache 2.0.

Changelog

See CHANGELOG.md for release history.

safe-migrate 0.3.2

safe-migrate v0.3.2

What's New in v0.3.2

✅ Live Database Statistics Integration

Installation

From Binary (Recommended)

From Cargo

Quick Start

Step 1: Sync Database Statistics

Step 2: Lint Your Migration

The Trust Model

Confidence Levels

Version-Gating

Cache Staleness

Rules Reference

1. blocking-constraint (Tier 1)

2. size-aware-add-column (Tier 1)

3. type-change-rewrite (Tier 1)

4. concurrent-index (Tier 2)

5. concurrent-in-transaction (Tier 1)

6. cascading-drop (Tier 1)

7. blocking-mat-view-refresh (Tier 2)

8. partition-lock (Tier 1/2)

9. opaque-dynamic-sql (Tier 2)

10. volatile-default (Tier 3)

11. vacuum-full (Tier 1)

12. idempotency (Tier 3, disabled by default)

Configuration

Global Settings

Per-Rule Configuration

Complete Example

Rule Reference

Version-Gating Examples

Cache Behavior

CLI Reference

safe-migrate lint

safe-migrate sync

CI/CD Integration

GitHub Actions

GitLab CI

Architecture

Why This Matters

License

Changelog

`safe-migrate lint`

`safe-migrate sync`