verify-migrations

name	verify-migrations
description	Apply all goose migrations against a disposable TimescaleDB container to validate SQL correctness. Spins up timescale/timescaledb:latest-pg18, runs `goose up`, reports the first failure (if any) with the exact PostgreSQL error and the migration filename. Idempotent — re-runs reset the DB and apply from scratch. Use after editing any migration or skill that emits migrations.
allowed-tools	Read, Bash

/verify-migrations

End-to-end validation of schema/migrations/. The single source of truth for "does our SQL actually work."

Why this exists

Skill output (from /generate-migration-from-diff and friends) is syntactically sane but PostgreSQL + TimescaleDB have their own rules: reserved words must be quoted, hypertable PKs must contain the partition column, constraint names truncate at 63 bytes, etc. Catching these only at production-deploy time is brutal. This skill catches them in <30 seconds.

Inputs

Optional flags:

• --dir <path> — migrations directory (default: schema/migrations)
• --keep-db — leave the container running after success (default: stops and removes)
• --target <version> — apply up to a specific migration version (default: all)

Steps

1. Ensure docker is available

docker --version

If absent: abort with instructions.

2. Bring up (or reuse) the TimescaleDB container

Container name: ps-verify-db. Image: timescale/timescaledb:latest-pg18. Port: 15432 (host) → 5432 (container). Password: verify.

If the container already exists, drop its schema; otherwise launch.

3. Wait for readiness

for i in $(seq 1 15); do
  docker exec ps-verify-db pg_isready -U postgres && break
  sleep 1
done

4. Reset the public schema

DROP SCHEMA IF EXISTS public CASCADE;
DROP SCHEMA IF EXISTS _timescaledb_internal CASCADE;
CREATE SCHEMA public;
DROP TABLE IF EXISTS goose_db_version;

This guarantees we apply migrations from scratch.

5. Run goose up

go run github.com/pressly/goose/v3/cmd/goose@latest \
    -dir <MIGRATIONS_DIR> \
    postgres "host=localhost port=15432 user=postgres password=verify dbname=postgres sslmode=disable" \
    up

Capture stdout + stderr.

6. Report

On success:

✅ All N migrations applied OK.
   Tables created: <count>
   Hypertables:    <count>
   Schema bytes:   <pg_database_size>

On failure: print the first failing migration, the PG error code (SQLSTATE), and the relevant snippet of the migration that triggered it (heuristic: grep for known reserved words, long identifiers, hypertable PK mismatches).

7. Cleanup

By default: stop and remove the container. With --keep-db: leave it running, print the connection string for manual inspection.

Common errors recognized

SQLSTATE	Likely cause	Suggested fix
42601 syntax error at or near <word>	Reserved keyword used as identifier	Quote in script's PG_RESERVED set
42710 constraint already exists	Identifier truncated to 63 bytes, collision with sibling	Shorten table or constraint name
42703 column does not exist	CREATE TABLE was IF NOT EXISTS no-op (table pre-existed with different shape)	Drop legacy table or rename new one
TS103 cannot create unique index without partition column	Hypertable PK missing block_time	Include partition column in PK
42P07 relation already exists	Two migrations creating same table	Audit migration ordering

Out of scope

• Data validation (this skill only proves SQL applies; not that rows roundtrip).
• Performance benchmarks.
• Down-migration testing — separate concern.

References

• ADR-016 — pgx + sqlc + goose (toolchain).
• ADR-028 — schema versioning strategy.
• .claude/skills/generate-migration-from-diff/ — generates the migrations we verify here.