Pi Migration Guard

PostgreSQL migration safety analyzer for Pi. It helps Pi and humans catch risky production migration patterns before they are applied or committed.

The goal is simple: when Pi writes or reviews a migration, it should not miss obvious database hazards like dropping tables, creating blocking indexes, rewriting large tables, adding unsafe constraints, or deleting every row by accident.

Why It Is Useful

Database migrations fail differently from normal application code. A migration can pass tests and still lock a production table, rewrite hundreds of gigabytes, block writes, or permanently remove data.

Migration Guard gives Pi a deterministic safety check before it recommends, commits, or applies PostgreSQL migration SQL. Instead of asking an LLM to guess whether SQL "looks dangerous", this package returns stable rule findings:

{
  "rule": "PG004",
  "severity": "high",
  "title": "Index is created non-concurrently",
  "statement": "CREATE INDEX users_email_idx ON users(email)",
  "recommendation": "For a production table, consider CREATE INDEX CONCURRENTLY to avoid blocking normal writes."
}

That structured result is useful because Pi can reason from evidence. It can explain the risk, propose a safer migration, and rerun the guard to verify the updated SQL.

Example Workflow

Pi or a developer writes:

ALTER TABLE users
    ADD COLUMN organization_id UUID NOT NULL;

CREATE INDEX users_email_idx
    ON users(email);

Migration Guard reports:

• PG008 high: adding a required column without a backfill path can fail or block on existing rows
• PG004 high: creating a normal index can block writes while PostgreSQL builds the index

Pi can then propose safer SQL:

ALTER TABLE users
    ADD COLUMN organization_id UUID;

-- Backfill existing rows in controlled batches.
UPDATE users
SET organization_id = ...
WHERE organization_id IS NULL;

ALTER TABLE users
    ALTER COLUMN organization_id SET NOT NULL;

CREATE INDEX CONCURRENTLY users_email_idx
    ON users(email);

The important part is that detection stays deterministic, while Pi handles the explanation and repair.

What It Catches

• PG001 critical: DROP TABLE
• PG002 critical: ALTER TABLE ... DROP COLUMN
• PG003 critical: TRUNCATE
• PG004 high: CREATE INDEX without CONCURRENTLY
• PG005 high: CREATE INDEX CONCURRENTLY inside an explicit transaction
• PG006 high: ALTER COLUMN ... TYPE
• PG007 high: ALTER COLUMN ... SET NOT NULL
• PG008 high: ADD COLUMN ... NOT NULL without a default
• PG009 warning: foreign key created without NOT VALID
• PG010 critical: DELETE FROM without WHERE

Pi Tool

Install the published package from npm:

npm install pi-migration-guard

After the package is installed and available to Pi, ask Pi to review a migration:

Check examples/bad-migration.sql with migration guard.

Pi can call:

migration_guard({
  path: "examples/bad-migration.sql",
});

The tool also accepts raw SQL:

migration_guard({
  sql: "CREATE INDEX users_email_idx ON users(email);",
});

The tool returns both user-readable text and structured details, so Pi can use the exact rule IDs, severities, SQL evidence, and recommendations in its next step.

Human Command

The package registers a direct command too:

/migration-guard migrations/0042_users.sql

The command reports pass/fail counts in the Pi UI. It is useful when a developer wants a quick manual check without asking Pi to inspect the file conversationally.

Fix Suggestions

Some rules include deterministic suggested SQL. For example, PG004 suggests replacing:

CREATE INDEX users_email_idx
    ON users(email);

with:

CREATE INDEX CONCURRENTLY users_email_idx
    ON users(email);

It also reminds Pi that CREATE INDEX CONCURRENTLY must run outside a transaction block.

For ADD COLUMN ... NOT NULL, the analyzer suggests a staged expand/backfill/enforce pattern:

ALTER TABLE users
ADD COLUMN organization_id UUID;

UPDATE users
SET organization_id = ...
WHERE organization_id IS NULL;

ALTER TABLE users
ALTER COLUMN organization_id SET NOT NULL;

These suggestions are not meant to replace engineering judgment. They give Pi a safer starting point and make the review loop faster.

Design Principles

• Deterministic detection: rules run locally over SQL and return stable IDs like PG004.
• Agent-assisted repair: Pi can use findings to explain and improve migrations.
• PostgreSQL first: v0.1 focuses on raw PostgreSQL SQL instead of spreading across many databases and frameworks too early.
• CI-ready shape: stable rule IDs make future config, suppressions, severity overrides, and CLI exit codes straightforward.

Development

pnpm install
pnpm test
pnpm typecheck
pnpm build

The current analyzer is intentionally regex/lexer based for v0.1. A future v0.2 should replace rule matching with a PostgreSQL AST parser such as pgsql-parser.

Publishing

Before publishing, verify that the GitHub repository URLs in package.json match the final repository, then run:

pnpm install
pnpm test
pnpm typecheck
pnpm build
npm publish

The npm package ships the compiled dist directory, the example migrations, this README, and the MIT license.