Agent-written database migrations

-- migrations/20260814093000_rename_users_fullname.up.sql
ALTER TABLE users RENAME COLUMN fullname TO display_name;

ERROR:  column "fullname" does not exist at character 19
STATEMENT:  SELECT id, email, fullname, avatar_url FROM users WHERE id = $1

What the agent's loop tests         What production actually runs
---------------------------------   ------------------------------------------
code@N    + schema@N                code@N-1 + schema@N   (during every rollout)
                                    code@N   + schema@N   (after rollout)
                                    code@N-1 + schema@N   (after an image rollback)
                                    cron/ETL/other svc + schema@N  (always)

# .github/workflows/n-minus-1.yml
name: n-minus-1-schema-compat
on:
  pull_request:
    paths: ["migrations/**"]

jobs:
  n-minus-1:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: app_test
        ports: ["5432:5432"]
        options: >-
          --health-cmd "pg_isready -U postgres"
          --health-interval 5s --health-timeout 5s --health-retries 10
    env:
      DATABASE_URL: postgres://postgres:postgres@localhost:5432/app_test?sslmode=disable
      SKIP_TEST_MIGRATIONS: "1"   # old test harness must NOT rebuild its own schema
    steps:
      - name: Check out PR (schema N)
        uses: actions/checkout@v4
        with:
          path: new
          fetch-depth: 0

      - name: Find the release running in production
        id: prev
        working-directory: new
        run: |
          # Swap for your deploy system's source of truth if you don't tag releases
          TAG=$(git tag --list 'v*' --sort=-v:refname | head -n1)
          echo "tag=$TAG" >> "$GITHUB_OUTPUT"

      - name: Check out previous release (code N-1)
        uses: actions/checkout@v4
        with:
          ref: ${{ steps.prev.outputs.tag }}
          path: old

      - name: Install migrate CLI
        run: |
          curl -sSL https://github.com/golang-migrate/migrate/releases/download/v4.18.1/migrate.linux-amd64.tar.gz | tar xz
          sudo mv migrate /usr/local/bin/

      - name: Apply schema N (every migration in this PR)
        run: migrate -path new/migrations -database "$DATABASE_URL" up

      - name: Run N-1 integration tests against schema N
        working-directory: old
        run: make test-integration

      - name: Explain the failure
        if: failure()
        run: |
          # tee: write to the step log (what `gh run view --log-failed` shows) AND the run summary
          tee -a "$GITHUB_STEP_SUMMARY" <<EOF
          ## N-1 compatibility failed
          Code from ${{ steps.prev.outputs.tag }} failed against the schema this PR produces.
          During a rolling deploy both versions serve traffic at once, so this failure WILL happen in production.
          Fix the migration, not the old tests: make the schema change additive (expand) and
          leave the old column readable and writable until a later contract PR.
          EOF
          exit 1   # fail this step too, so its log is included in --log-failed

migrations/20260814093000_rename_users_fullname.up.sql:1:1: warning: renaming-column Renaming a column may break existing clients.

#!/usr/bin/env python3
# ci/migration_lint.py <files...>
# Runs Squawk, then rewrites each violation as instructions the agent can act on.
import json, re, subprocess, sys

RENAME = re.compile(r"ALTER\s+TABLE\s+(\w+)\s+RENAME\s+COLUMN\s+(\w+)\s+TO\s+(\w+)", re.I)

TEMPLATES = {
    "renaming-column": """BLOCKED {file}:{line} rule=renaming-column
Pods running the previous release still query {table}.{old} during a rolling deploy.
Renaming it breaks them the moment this migration runs.

Replace this migration with phase 1 (expand) only:

  SET lock_timeout = '3s';
  ALTER TABLE {table} ADD COLUMN {new} <same type as {old}>;
  -- then copy lint/sql/sync_trigger.sql, substituting {table}, {old}, {new}

Then add this entry to migrations/inflight.yaml:

  - id: {table}-{old}-to-{new}
    table: {table}
    from: {old}
    to: {new}
    phase: expanded

Do not change application reads or writes in this PR. Backfill, switch and
contract are three later PRs, one phase each. Do not add ignore comments:
CI rejects them unless a human applies the migration-override label.""",

    "adding-not-nullable-field": """BLOCKED {file}:{line} rule=adding-not-nullable-field
Old pods INSERT without this column and will fail. Add it as NULLable in this PR,
backfill in the next PR, and add a NOT VALID check constraint + VALIDATE in a third.""",
}
FALLBACK = "BLOCKED {file}:{line} rule={rule}\n{msg}\nSee docs/migrations.md for the safe sequence."

def main(files):
    proc = subprocess.run(
        ["squawk", "--reporter", "json", "--exclude", "ban-drop-column", *files],
        capture_output=True, text=True,
    )
    violations = json.loads(proc.stdout or "[]")  # field names vary by Squawk version; check yours
    for v in violations:
        path, rule = v.get("file", ""), v.get("rule_name", "")
        m = RENAME.search(open(path).read()) if path else None
        table, old, new = m.groups() if m else ("<table>", "<old>", "<new>")
        print(TEMPLATES.get(rule, FALLBACK).format(
            file=path, line=v.get("line", "?"), rule=rule,
            msg=v.get("message") or v.get("messages"), table=table, old=old, new=new))
        print()
    sys.exit(1 if violations else 0)

if __name__ == "__main__":
    main(sys.argv[1:])

-- lint/sql/sync_trigger.sql  (substitute users / fullname / display_name)
CREATE OR REPLACE FUNCTION users_sync_display_name() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.display_name := COALESCE(NEW.display_name, NEW.fullname);
    NEW.fullname     := COALESCE(NEW.fullname, NEW.display_name);
  ELSIF NEW.fullname IS DISTINCT FROM OLD.fullname THEN
    NEW.display_name := NEW.fullname;          -- old code wrote
  ELSIF NEW.display_name IS DISTINCT FROM OLD.display_name THEN
    NEW.fullname := NEW.display_name;          -- new code wrote
  END IF;
  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER users_sync_display_name
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION users_sync_display_name();

# config/initializers/strong_migrations.rb
StrongMigrations.error_messages[:rename_column] = <<~MSG
  Old pods still read this column during a rolling deploy. Do not rename.
  This PR: add the new column (nullable) + the sync trigger from lint/sql/sync_trigger.sql,
  and add an inflight.yaml entry with phase: expanded. Nothing else.
  Do not use safety_assured; CI rejects it without the migration-override label.
MSG

#!/usr/bin/env bash
# ci/no_unapproved_ignores.sh  (env: PR_NUMBER, AGENT_LOGIN, GH_TOKEN)
set -euo pipefail

if ! git diff origin/main...HEAD -- migrations/ db/migrate/ | grep -E '^\+.*(squawk-ignore|safety_assured)'; then
  exit 0
fi

ACTOR=$(gh api --paginate "repos/$GITHUB_REPOSITORY/issues/$PR_NUMBER/events" \
  --jq '.[] | select(.event == "labeled" and .label.name == "migration-override") | .actor.login' | tail -n1)

case "$ACTOR" in
  "" | *"[bot]" | "$AGENT_LOGIN")
    echo "Lint-ignore comments found. Remove them and fix the migration as the lint output describes."
    echo "Only a human can approve an override, by applying the migration-override label."
    exit 1 ;;
esac
echo "Override approved by @$ACTOR."

# migrations/inflight.yaml
# One entry per schema change in progress. Each PR may advance ONE entry by ONE phase.
# phase: expanded | backfilled | switched | contracted
changes:
  - id: users-fullname-to-display-name
    table: users
    from: fullname
    to: display_name
    phase: expanded
    owner: "@team-identity"
    opened: 2026-09-04
    contract_gate:
      source: pg_stat_statements
      quiet_days: 7

#!/usr/bin/env python3
# ci/check_inflight.py
# Fails any PR that moves a schema change forward more than one phase.
import os, re, subprocess, sys, yaml

PHASES = ["expanded", "backfilled", "switched", "contracted"]
DROP = re.compile(r"ALTER\s+TABLE\s+(\w+)\s+DROP\s+COLUMN\s+(?:IF\s+EXISTS\s+)?(\w+)", re.I)

def git(*args):
    return subprocess.run(["git", *args], capture_output=True, text=True).stdout

def load(ref):
    doc = yaml.safe_load(git("show", f"{ref}:migrations/inflight.yaml") or "{}") or {}
    return {c["id"]: c for c in doc.get("changes", [])}

base, head = load("origin/main"), load("HEAD")
errors, moved = [], []

for cid, c in head.items():
    before = PHASES.index(base[cid]["phase"]) if cid in base else -1
    after = PHASES.index(c["phase"])
    if after != before:
        moved.append((cid, c["phase"]))
    if after - before > 1:
        frm = PHASES[before] if before >= 0 else "new"
        errors.append(f"{cid}: {frm} -> {c['phase']} skips a phase. "
                      f"Next allowed phase is {PHASES[before + 1]}. Split this PR.")
    if after < before:
        errors.append(f"{cid}: phases only move forward ({base[cid]['phase']} -> {c['phase']}).")

for cid in base.keys() - head.keys():
    if base[cid]["phase"] != "contracted":
        errors.append(f"{cid}: removed from inflight.yaml before it was contracted.")

if len(moved) > 1:
    errors.append(f"This PR advances {len(moved)} changes. One change, one phase, per PR.")

contracting = {(head[cid]["table"], head[cid]["from"]) for cid, p in moved if p == "contracted"}
added = git("diff", "--name-only", "--diff-filter=A", "origin/main...HEAD", "--", "migrations/").split()
for path in (p for p in added if p.endswith(".sql")):
    for table, col in DROP.findall(open(path).read()):
        if (table, col) not in contracting:
            errors.append(f"{path}: drops {table}.{col}, but no inflight.yaml entry "
                          f"moves {table}.{col} to contracted in this PR.")

if os.environ.get("GITHUB_OUTPUT"):
    with open(os.environ["GITHUB_OUTPUT"], "a") as f:
        f.write("contracting=" + ",".join(cid for cid, p in moved if p == "contracted") + "\n")

if errors:
    print("inflight.yaml check failed:")
    print("\n".join(" - " + e for e in errors))
    sys.exit(1)

#!/usr/bin/env python3
# ci/column_reads.py snapshot        (daily cron, writes to telemetry DB)
# ci/column_reads.py gate <change-id> (CI, on PRs that move a change to contracted)
# env: PG_HOSTS="primary=postgres://...,replica-1=postgres://..."  TELEMETRY_DSN=...
import datetime, os, sys, yaml, psycopg

HOSTS = dict(h.split("=", 1) for h in os.environ["PG_HOSTS"].split(","))
CENTRAL = os.environ["TELEMETRY_DSN"]

def changes():
    with open("migrations/inflight.yaml") as f:
        return {c["id"]: c for c in yaml.safe_load(f)["changes"]}

def snapshot():
    today = datetime.date.today()
    with psycopg.connect(CENTRAL) as central:
        for c in (c for c in changes().values() if c["phase"] == "switched"):
            for host, dsn in HOSTS.items():
                with psycopg.connect(dsn) as conn:
                    calls = conn.execute(
                        "SELECT coalesce(sum(calls), 0) FROM pg_stat_statements "
                        "WHERE query ILIKE %s AND query ILIKE %s AND query NOT ILIKE '%%_sync_%%'",
                        (f"%{c['from']}%", f"%{c['table']}%"),
                    ).fetchone()[0]
                central.execute(
                    "INSERT INTO ops.column_read_snapshots (day, host, change_id, calls) "
                    "VALUES (%s, %s, %s, %s) ON CONFLICT (day, host, change_id) "
                    "DO UPDATE SET calls = EXCLUDED.calls",
                    (today, host, c["id"], calls))

def gate(change_id):
    c = changes()[change_id]
    quiet = c["contract_gate"]["quiet_days"]
    since = datetime.date.today() - datetime.timedelta(days=quiet)
    with psycopg.connect(CENTRAL) as central:
        rows = central.execute(
            "SELECT host, calls FROM ops.column_read_snapshots "
            "WHERE change_id = %s AND day >= %s ORDER BY host, day",
            (change_id, since)).fetchall()
    series = {}
    for host, calls in rows:
        series.setdefault(host, []).append(calls)
    problems = []
    for host in HOSTS:
        s = series.get(host, [])
        if len(s) < quiet + 1:
            problems.append(f"{host}: {len(s)} snapshots, need {quiet + 1}")
        elif any(b != a for a, b in zip(s, s[1:])):  # a drop means a stats reset: fail closed
            problems.append(f"{host}: reads of {c['table']}.{c['from']} changed: {s}")
    if problems:
        print(f"CONTRACT BLOCKED for {change_id}. Something still reads {c['table']}.{c['from']}:")
        print("\n".join(" - " + p for p in problems))
        print("Find the caller (query text in pg_stat_statements, other repos, cron, ETL), "
              "move it to the new column, and let the quiet window restart.")
        sys.exit(1)
    print(f"Contract gate passed: no new reads of {c['table']}.{c['from']} on {len(HOSTS)} hosts for {quiet} days.")

if __name__ == "__main__":
    snapshot() if sys.argv[1] == "snapshot" else gate(sys.argv[2])

## Database migrations
Assume two app versions are live against every schema change; advance migrations exactly one phase per PR.
When `n-minus-1`, `migration-lint` or `inflight` fails, do what its output says. Never add ignore comments.

### Migration review (production facts the agent can't know)
- [ ] Rows in target table today: ____ ; backfill batch size ____ ; sleep between batches ____
- [ ] lock_timeout ____ / statement_timeout ____ fit this table's write traffic at peak
- [ ] Replica lag budget ____ s, and the backfill pauses when lag exceeds it
- [ ] Readers outside this repo (other services, cron, ETL/BI, CDC consumers): ____
- [ ] Rollout window ≈ replicas × time-to-ready = ____ min (how long N-1 stays live)

PR 1  expand    ADD COLUMN display_name + sync trigger      opened & merged day 1   2 agent loops
PR 2  backfill  batched UPDATE, 5k rows, pause on lag        merged day 2            reviewer cut batch size; ran ~40 min
PR 3  switch    app reads/writes display_name                 merged day 3            N-1 green: fullname still exists
      gate      7 quiet days, all hosts                       reset once on day 5     nightly CSV export in another repo still read fullname
PR 4  contract  DROP TRIGGER, DROP FUNCTION, DROP COLUMN      merged day 15           gate passed; inflight entry removed in PR 5

# ===== .github/workflows/migrations.yml =====
name: migrations
on:
  pull_request:
    paths: ["migrations/**"]
jobs:
  migration-lint:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with: { fetch-depth: 0 }
      - run: pip install squawk-cli pyyaml
      - name: Lint new migrations (agent-readable output)
        run: |
          FILES=$(git diff --name-only --diff-filter=A origin/main...HEAD -- 'migrations/*.sql')
          [ -z "$FILES" ] || python ci/migration_lint.py $FILES
      - name: Ban unapproved ignore comments
        env:
          PR_NUMBER: ${{ github.event.pull_request.number }}
          AGENT_LOGIN: our-agent-bot
          GH_TOKEN: ${{ github.token }}
        run: bash ci/no_unapproved_ignores.sh

  inflight:
    runs-on: ubuntu-latest
    outputs:
      contracting: ${{ steps.check.outputs.contracting }}
    steps:
      - uses: actions/checkout@v4
        with: { fetch-depth: 0 }
      - run: pip install pyyaml
      - id: check
        run: python ci/check_inflight.py

  contract-gate:
    needs: inflight
    if: needs.inflight.outputs.contracting != ''
    runs-on: [self-hosted, prod-telemetry]
    steps:
      - uses: actions/checkout@v4
      - run: python ci/column_reads.py gate ${{ needs.inflight.outputs.contracting }}
        env:
          PG_HOSTS: ${{ secrets.PG_HOSTS_READONLY }}
          TELEMETRY_DSN: ${{ secrets.TELEMETRY_DSN }}

  # n-minus-1: paste the job from Guardrail 1 here

# ===== migrations/inflight.yaml =====
# Each PR may advance ONE entry by ONE phase: expanded | backfilled | switched | contracted
changes:
  - id: <table>-<old>-to-<new>
    table: <table>
    from: <old>
    to: <new>
    phase: expanded
    owner: "@<team>"
    opened: <YYYY-MM-DD>
    contract_gate: { source: pg_stat_statements, quiet_days: 7 }

# ===== CLAUDE.md =====
## Database migrations
Assume two app versions are live against every schema change; advance migrations exactly one phase per PR.
When `n-minus-1`, `migration-lint` or `inflight` fails, do what its output says. Never add ignore comments.

# ===== .github/pull_request_template.md (migration section) =====
### Migration review (production facts the agent can't know)
- [ ] Rows in target table today: ____ ; backfill batch size ____ ; sleep between batches ____
- [ ] lock_timeout ____ / statement_timeout ____ fit this table's write traffic at peak
- [ ] Replica lag budget ____ s, and the backfill pauses when lag exceeds it
- [ ] Readers outside this repo (other services, cron, ETL/BI, CDC consumers): ____
- [ ] Rollout window ≈ replicas × time-to-ready = ____ min (how long N-1 stays live)

# ===== cron (daily) =====
# 0 6 * * *  cd /srv/app && python ci/column_reads.py snapshot