OneLinersCommand workbench
AI
Back to skills
SKILL.md

SQL migration review skill

Reviews schema changes for locking, compatibility, backfill, and rollback risk.

Revision
2
Verified
2026-10-20
Save or explore
Save to collectionCreate a collection in the sidebar first.

Compatibility and paths

Codexskills/sql-reviewer/SKILL.md
Claude Code.claude/skills/sql-reviewer/SKILL.md
VS Code.github/skills/sql-reviewer/SKILL.md

Trust and provenance

Curated record reviewed 2026-10-20. Results still depend on the supplied context and target environment.

Generated assetReady to copy or download
---
name: sql-reviewer
description: Reviews schema compatibility across existing rows and still-running writers. Use before a production migration; distinguish an expand/backfill/contract plan from a DDL statement that merely succeeds on an empty table.
license: CC-BY-4.0
compatibility: Owned SQL lab requires Node.js 22.13+ and @electric-sql/pglite 0.5.8. It uses only an in-memory database; real database access, locking tests and migrations require separate authorization.
metadata:
  author: oneliners
  version: "2.0.0"
---

# SQL migration review skill

## Begin with the engine and overlapping applications
Record exact database/engine version, table size/distribution, current schema, supported application/job/consumer versions, constraints, write patterns and permitted maintenance window. Classify metadata change, validation scan, rewrite/backfill and irreversible data effects separately using the actual engine documentation. An empty-table test and a green new-client test miss existing rows and old writers.

## Owned PostgreSQL compatibility exercise
Use a new empty local exercise directory, not an application checkout or production database. Node.js 22.13+ is required. Install the pinned library only if this exercise is approved:

    npm init -y
    npm install --ignore-scripts --save-exact @electric-sql/pglite@0.5.8

Package installation uses the npm registry; the program itself opens only a new in-memory database, with no server address or persisted data directory. The [PGlite documentation](https://pglite.dev/docs/) describes this mode. Save the complete code block below as the named .mjs file in that directory; all schema/data are included, so no missing SQL/data file is required.
Save this block as migration-fixture.mjs and run node migration-fixture.mjs. The intentionally failing DDL/write are confined to its new in-memory training_item table.

```js
import assert from "node:assert/strict";
import { PGlite } from "@electric-sql/pglite";
async function runMigrationFixture(PGlite) {
  const db = new PGlite(); // In-memory only; no server/connection string/data directory.
  try {
    const version = (await db.query("SHOW server_version")).rows[0].server_version;
    await db.exec("CREATE TABLE training_item(id integer PRIMARY KEY, label text NOT NULL); INSERT INTO training_item VALUES(1,'alpha'),(2,'beta');");
    let unsafeAddCode = null;
    try { await db.exec("ALTER TABLE training_item ADD COLUMN slug text NOT NULL"); }
    catch (error) { unsafeAddCode = error.code; if (error.code !== "23502") throw error; }
    const columnAfterFailure = (await db.query("SELECT count(*)::int AS n FROM information_schema.columns WHERE table_schema='public' AND table_name='training_item' AND column_name='slug'")).rows[0].n;
    await db.exec("ALTER TABLE training_item ADD COLUMN slug text; INSERT INTO training_item(id,label) VALUES(3,'gamma');");
    const nullsBeforeBackfill = (await db.query("SELECT count(*)::int AS n FROM training_item WHERE slug IS NULL")).rows[0].n;
    await db.exec("UPDATE training_item SET slug='item-' || id::text WHERE slug IS NULL; ALTER TABLE training_item ALTER COLUMN slug SET NOT NULL;");
    let prematureContractCode = null;
    try { await db.exec("INSERT INTO training_item(id,label) VALUES(4,'delta')"); }
    catch (error) { prematureContractCode = error.code; if (error.code !== "23502") throw error; }
    // Restore overlap in this owned lab, not a recommended production rollback.
    await db.exec("ALTER TABLE training_item ALTER COLUMN slug DROP NOT NULL; INSERT INTO training_item(id,label) VALUES(4,'delta');");
    const remainingNulls = (await db.query("SELECT count(*)::int AS n FROM training_item WHERE slug IS NULL")).rows[0].n;
    const oldReader = (await db.query("SELECT id,label FROM training_item ORDER BY id")).rows;
    return { version, unsafeAddCode, columnAfterFailure, nullsBeforeBackfill, prematureContractCode, remainingNulls, oldReader };
  } finally { await db.close(); }
}
const result = await runMigrationFixture(PGlite);
assert.equal(result.unsafeAddCode, "23502");
assert.equal(result.columnAfterFailure, 0);
assert.equal(result.nullsBeforeBackfill, 3);
assert.equal(result.prematureContractCode, "23502");
assert.equal(result.remainingNulls, 1);
assert.equal(result.oldReader.length, 4);
console.log(JSON.stringify(result, null, 2));
```

## Observed failures and rollout review
The real embedded PostgreSQL exercise rejects ADD COLUMN slug text NOT NULL on the two populated rows with SQLSTATE 23502. The failed statement leaves no slug column. Expanding with a nullable column allows an old writer to insert row 3; all three rows then need backfill.

After the lab backfills existing rows and sets NOT NULL, an old writer omitting slug fails with 23502. Restoring nullable overlap in the owned lab allows row 4 and leaves one new NULL. Therefore a zero-NULL scan at one instant is not proof that old writers have been retired or that future writes satisfy the contract. The old reader still sees its four id/label rows; that one read path is a positive control, not proof every old/new consumer works.

Recommended sequence: add a compatible nullable field; deploy/read-test code that tolerates overlap and writes the new value; backfill in approved bounded batches; verify all writers/jobs/retries supported during overlap; recheck invalid/missing values; only then separately review tightening the constraint. A default is a business/data decision, not a universal fix for the failing DDL.

The program actually creates and alters only an in-memory PGlite database, prints its backend version and closes it. It does not measure production lock duration, concurrent writer races, WAL/replica lag, table rewrites, data loss or restore readiness. Dropping NOT NULL in the lab demonstrates compatibility, not an automatically safe production rollback. Keep real schema/data rollback and irreversible effects as separate owner-approved gates.

## Locking and recovery are not answered by this fixture
[PostgreSQL ALTER TABLE](https://www.postgresql.org/docs/current/sql-altertable.html) describes operation-specific lock/validation behavior. Identify the exact supported version/subcommand and test contention in an approved representative environment with timeouts and stop conditions; do not infer milliseconds of downtime from this single-client WASM exercise. Plan batches/checkpoints, idempotent backfill, failed/retried jobs and writer overlap. Do not run a production backfill or acquire a test lock merely to complete this review.

## Handoff and acceptance
Return before/after schema, old/new read/write matrix, existing-row checks, engine-specific lock/rewrite evidence, batch/retry plan, validation gate, data-compatible rollback and owner. Verify actual application paths and restored backups separately. Stop a migration recommendation when writer inventory, data semantics, contention bounds or recovery evidence are missing. A reversible code commit or transactional DDL does not automatically reverse externally visible work or restore deleted data. This fixture did not change OneLiners D1 or any remote database.

Real example

Input

Review the complete owned PostgreSQL example in SKILL.md: two populated rows, a proposed NOT NULL addition, nullable expansion/backfill and a still-running old writer. Run only the included in-memory lab if approved; do not connect to any remote database.

Expected result

The real embedded PostgreSQL exercise rejects ADD COLUMN slug text NOT NULL on the two populated rows with SQLSTATE 23502. The failed statement leaves no slug column. Expanding with a nullable column allows an old writer to insert row 3; all three rows then need backfill.

After the lab backfills existing rows and sets NOT NULL, an old writer omitting slug fails with 23502. Restoring nullable overlap in the owned lab allows row 4 and leaves one new NULL. Therefore a zero-NULL scan at one instant is not proof that old writers have been retired or that future writes satisfy the contract. The old reader still sees its four id/label rows; that one read path is a positive control, not proof every old/new consumer works.

Recommended sequence: add a compatible nullable field; deploy/read-test code that tolerates overlap and writes the new value; backfill in approved bounded batches; verify all writers/jobs/retries supported during overlap; recheck invalid/missing values; only then separately review tightening the constraint. A default is a business/data decision, not a universal fix for the failing DDL.

The program actually creates and alters only an in-memory PGlite database, prints its backend version and closes it. It does not measure production lock duration, concurrent writer races, WAL/replica lag, table rewrites, data loss or restore readiness. Dropping NOT NULL in the lab demonstrates compatibility, not an automatically safe production rollback. Keep real schema/data rollback and irreversible effects as separate owner-approved gates.

Source evidence

Agent Skills specificationofficialPostgreSQL ALTER TABLE — locking and constraintsofficialPGlite in-memory PostgreSQL modeofficial