OmexIT/db-migration

DB Migration: PostgreSQL Schema Evolution Skill

Generates safe, reversible PostgreSQL migrations for Liquibase or Flyway with naming, audit columns, rollback, and production-safety checks.

---

Before You Start — Superpowers Workflow

This skill generates SQL migrations. Migrations have no classical TDD but they have strict verification requirements.

1. superpowers:brainstorming — recommended for destructive operations (DROP, rename, column type change). Explore blast radius, rollback plan, estimated downtime, lock behavior under load, backfill strategy.
2. superpowers:writing-plans — mandatory for multi-phase migrations (add column → backfill → switch reads → drop old). Each phase is a separate changeset.
3. Write verification SQL first — assertions about the expected post-migration state (rows exist, constraints applied, index present, RLS enabled). This REPLACES superpowers:test-driven-development for the SQL class.
4. Invoke this skill (db-migration) to generate the migration changeset. Sequential execution only — migrations have strict ordering; never parallel.
5. superpowers:verification-before-completion — MANDATORY. Run the migration against a test database (Testcontainers or dedicated test DB). Paste output. Then run rollback. Then re-run the migration. Paste all output.
6. superpowers:requesting-code-review — MANDATORY for schema changes. Reviewer focuses on: rollback safety, lock behavior, index strategy (CONCURRENTLY on populated tables), audit column discipline.

Destructive operation gate: this skill must never generate DROP TABLE, DROP COLUMN, column renames, or type changes without explicit user confirmation shown as a prompt. If the user tries to bypass the prompt, the skill refuses.

---

0. Mode Detection

# Liquibase indicators
find src/main/resources/db/changelog -name "*.sql" 2>/dev/null | head -3
grep -l "liquibase formatted sql" src/main/resources/db/changelog/**/*.sql 2>/dev/null | head -3

# Flyway indicators
find src/main/resources/db/migration -name "V*__*.sql" 2>/dev/null | head -3
grep -rE "flyway-core|spring-boot-starter-flyway" build.gradle* pom.xml 2>/dev/null

# Report mode:
🗄️  MIGRATION MODE DETECTED
  Project:    <name>
  Mode:       <liquibase | flyway>
  Location:   <path to changelog/migration dir>
  Proceeding with <mode>-specific format.

If neither detected → ask which to use, default to Liquibase for projects that have a PayserFlow-like db/changelog/changes/sql/ structure.

---

1. Conventions (both modes)

PKs

id BIGINT PRIMARY KEY  -- application-generated TSID (time-sorted, 64-bit)

Never SERIAL. Never UUID. Never IDENTITY. IDs are generated by Hypersistence TSID in the application, passed as a BIGINT.

Audit columns (on every business table)

created_by        TEXT NOT NULL,
created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
last_modified_by  TEXT,
updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()

Money columns

amount_fiat   NUMERIC(20,2)   -- USD, EUR, NGN, KES, ETB
amount_crypto NUMERIC(20,8)   -- BTC, ETH, USDT

Never DECIMAL, never DOUBLE PRECISION, never REAL.

Timestamps

occurred_at TIMESTAMPTZ NOT NULL

Never TIMESTAMP (without time zone). Never DATE for event times.

Multi-tenancy

If the table is tenant-scoped, add tenant_id BIGINT NOT NULL and enable RLS:

ALTER TABLE <table> ENABLE ROW LEVEL SECURITY;
ALTER TABLE <table> FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON <table>
  USING (tenant_id = current_setting('app.tenant_id')::BIGINT);

Enums

Use TEXT with CHECK constraints, not native ENUM types (easier to evolve):

status TEXT NOT NULL CHECK (status IN ('PENDING','ACTIVE','SUSPENDED','CLOSED'))

---

2. Mode A: Liquibase (Kifiya/PayserFlow)

2.1 File naming

src/main/resources/db/changelog/changes/sql/<scope>-<NNN>-<description>.sql

Example: ledger-001-core-tables.sql, payment-link-002-add-expires-at.sql.

2.2 Required header format

--liquibase formatted sql
--changeset <author>:<scope>-<NNN>-<description>

Example:

--liquibase formatted sql
--changeset payserflow:payment-link-001-schema

CREATE TABLE payment_links (
    id                BIGINT PRIMARY KEY,
    tenant_id         BIGINT NOT NULL,
    amount            NUMERIC(20,2) NOT NULL CHECK (amount > 0),
    currency          TEXT NOT NULL,
    description       TEXT NOT NULL,
    expires_at        TIMESTAMPTZ NOT NULL,
    status            TEXT NOT NULL CHECK (status IN ('ACTIVE','EXPIRED','CANCELLED','USED')),
    metadata          JSONB NOT NULL DEFAULT '{}',
    created_by        TEXT NOT NULL,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_modified_by  TEXT,
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_payment_links_tenant_status ON payment_links(tenant_id, status);
CREATE INDEX idx_payment_links_expires_at    ON payment_links(expires_at)
  WHERE status = 'ACTIVE';

ALTER TABLE payment_links ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_links FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON payment_links
  USING (tenant_id = current_setting('app.tenant_id')::BIGINT);

--rollback DROP POLICY tenant_isolation ON payment_links;
--rollback ALTER TABLE payment_links DISABLE ROW LEVEL SECURITY;
--rollback DROP INDEX idx_payment_links_expires_at;
--rollback DROP INDEX idx_payment_links_tenant_status;
--rollback DROP TABLE payment_links;

2.3 Add-column pattern

--liquibase formatted sql
--changeset payserflow:payment-link-002-add-reference

ALTER TABLE payment_links ADD COLUMN reference TEXT;
CREATE UNIQUE INDEX idx_payment_links_reference ON payment_links(tenant_id, reference)
  WHERE reference IS NOT NULL;

--rollback DROP INDEX idx_payment_links_reference;
--rollback ALTER TABLE payment_links DROP COLUMN reference;

2.4 Backfill pattern (split into two changesets)

--liquibase formatted sql
--changeset payserflow:payment-link-003-backfill-status-batch
--runInTransaction:false

DO $$
DECLARE
    batch_size INT := 10000;
    rows_updated INT;
BEGIN
    LOOP
        UPDATE payment_links
           SET status = 'EXPIRED'
         WHERE id IN (
            SELECT id FROM payment_links
             WHERE status = 'ACTIVE' AND expires_at < now()
             LIMIT batch_size
         );
        GET DIAGNOSTICS rows_updated = ROW_COUNT;
        EXIT WHEN rows_updated = 0;
        PERFORM pg_sleep(0.05);  -- throttle
    END LOOP;
END $$;

--rollback SELECT 1; -- backfill is irreversible; document in ADR

---

3. Mode B: Flyway 10+

3.1 File naming

src/main/resources/db/migration/V20260415_100000__create_payment_links.sql

Format: V<yyyyMMddHHmmss>__<snake_case_description>.sql. Use the current timestamp (not sequential numbers) to avoid merge conflicts.

3.2 Standard migration

-- Create payment_links table (multi-tenant, RLS-protected)

CREATE TABLE payment_links (
    id                BIGINT PRIMARY KEY,
    tenant_id         BIGINT NOT NULL,
    amount            NUMERIC(20,2) NOT NULL CHECK (amount > 0),
    currency          TEXT NOT NULL,
    description       TEXT NOT NULL,
    expires_at        TIMESTAMPTZ NOT NULL,
    status            TEXT NOT NULL CHECK (status IN ('ACTIVE','EXPIRED','CANCELLED','USED')),
    metadata          JSONB NOT NULL DEFAULT '{}',
    created_by        TEXT NOT NULL,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    last_modified_by  TEXT,
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_payment_links_tenant_status ON payment_links(tenant_id, status);
CREATE INDEX idx_payment_links_expires_at    ON payment_links(expires_at)
  WHERE status = 'ACTIVE';

ALTER TABLE payment_links ENABLE ROW LEVEL SECURITY;
ALTER TABLE payment_links FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON payment_links
  USING (tenant_id = current_setting('app.tenant_id')::BIGINT);

3.3 Flyway undo (Teams edition)

src/main/resources/db/migration/U20260415_100000__create_payment_links.sql

DROP POLICY tenant_isolation ON payment_links;
ALTER TABLE payment_links DISABLE ROW LEVEL SECURITY;
DROP TABLE payment_links;

3.4 Index on populated table — use CONCURRENTLY

-- Flyway: mark this migration as transactional=false
-- Add ";-- FLYWAY TRANSACTIONAL=false" header or set spring.flyway.mixed=true

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_payment_links_created_at
  ON payment_links(created_at);

---

4. Safety Rules

1. Never DROP without explicit confirmation. If the user asks to drop a table/column, first show:
   • Row count: SELECT COUNT(*) FROM <table> (or ask user to run it)
   • Dependencies: SELECT * FROM pg_depend WHERE refobjid = '<table>'::regclass
   • Referencing FKs Then ask: "Confirm destructive operation? [yes/no]"
2. Never rename a column in a single step on a populated table. Two-phase:
   • Step 1 (non-breaking): add new column, dual-write from app, backfill
   • Step 2 (cleanup, after deploy verifies dual-write): drop old column
3. Indexes on large tables must use CONCURRENTLY. Never block writes on production.
4. Foreign keys on large tables must be added NOT VALID then VALIDATE CONSTRAINT.

   ALTER TABLE child ADD CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent(id) NOT VALID;
   ALTER TABLE child VALIDATE CONSTRAINT fk_parent;  -- runs without exclusive lock
   

5. Every changeset has a rollback. Even if it's SELECT 1; -- irreversible, see ADR.
6. Backfills are throttled (pg_sleep(0.05) between batches) and run in non-transactional mode.
7. Seed data uses ON CONFLICT DO NOTHING — seeds must be idempotent.

---

5. Verification Query (always generate alongside migration)

After creating a migration, generate a verification SQL file that asserts the expected state:

-- verify-payment-link-001.sql
SELECT assert_equal('payment_links table exists',
    (SELECT COUNT(*) FROM information_schema.tables
      WHERE table_name = 'payment_links'), 1);

SELECT assert_equal('tenant_id column exists',
    (SELECT COUNT(*) FROM information_schema.columns
      WHERE table_name = 'payment_links' AND column_name = 'tenant_id'), 1);

SELECT assert_equal('RLS enabled',
    (SELECT relrowsecurity FROM pg_class WHERE relname = 'payment_links'), TRUE);

SELECT assert_equal('check constraint on amount',
    (SELECT COUNT(*) FROM pg_constraint
      WHERE conname LIKE 'payment_links%amount%check'), 1);

---

6. Output Contract

produces:
  - type: "migration"
    format: "sql"
    paths:
      - "src/main/resources/db/changelog/changes/sql/<scope>-<NNN>-<description>.sql"  # Liquibase
      OR
      - "src/main/resources/db/migration/V<timestamp>__<description>.sql"              # Flyway
      - "src/main/resources/db/migration/U<timestamp>__<description>.sql"              # Flyway undo (optional)
  - type: "verification"
    format: "sql"
    paths: ["docs/migrations/verify-<description>.sql"]
  - type: "doc"
    format: "markdown"
    path: "docs/migrations/<description>.md"   # rollback plan, risks, expected downtime
  handoff: "Write claudedocs/handoff-db-migration-<timestamp>.yaml — suggest: verify-impl, api-first (if paired with endpoint)"

---

7. Anti-patterns (never generate)

• SERIAL / BIGSERIAL / IDENTITY / GENERATED ALWAYS AS IDENTITY PKs — use application-generated TSIDs
• Native CREATE TYPE ... AS ENUM — use TEXT + CHECK constraints
• TIMESTAMP without time zone — always TIMESTAMPTZ
• DECIMAL, DOUBLE PRECISION, REAL, MONEY — always NUMERIC(20,2) or NUMERIC(20,8)
• Renaming a column on a populated table in one step — two-phase
• DROP TABLE ... CASCADE without rollback script — irreversible
• Running index creation without CONCURRENTLY on populated tables
• Omitting --rollback section in Liquibase
• Adding FK without NOT VALID on large tables
• Missing audit columns on business tables
• Foreign keys without an index on the FK column
• Seeds without ON CONFLICT DO NOTHING
• Using Liquibase XML format — always --liquibase formatted sql
• Mixing multiple unrelated changes in one changeset — one concern per file