DB Migration

DB Migration

Instructions

Create or update the database schema as goose SQL migrations based on docs/entity_model.md, and regenerate the sqlc Go code.

goose does not diff a schema — every migration is written by hand as SQL. That is intended: the migration file is the reviewed change. Each file has an -- +goose Up and an -- +goose Down section.

DO NOT

• Edit a migration that has already been applied anywhere (merged to main, or run against dev) — add a new migration instead
• Drop or rename existing tables or columns without explicit user confirmation (a DROP in the -- +goose Down section of a new migration is expected and fine)
• Edit anything under internal/db/ — it is generated by sqlc generate
• Skip relations defined in the entity model — every relation becomes a foreign key
• Use serial / bigserial IDs unless the entity model explicitly requires them (use uuid with DEFAULT gen_random_uuid())

Nexa Rules Gate

Read and follow ${CLAUDE_PLUGIN_ROOT}/shared/readiness/NEXA_RULES_GATE.md.

Worktree Gate

Read and follow ${CLAUDE_PLUGIN_ROOT}/shared/readiness/WORKTREE_GATE.md.

Locations

db/migrations/NNNNN_<name>.sql   # goose migrations (schema source for sqlc)
db/migrations/embed.go           # embeds the migrations, exposes Up(ctx, dsn)
db/queries/<entity>.sql          # sqlc queries
sqlc.yaml
internal/db/                     # generated — committed, never edited

Example Migration

-- +goose Up
CREATE TABLE room_type (
    id          uuid          PRIMARY KEY DEFAULT gen_random_uuid(),
    name        varchar(50)   NOT NULL UNIQUE,
    description varchar(500),
    capacity    integer       NOT NULL CHECK (capacity > 0),
    price       numeric(10,2) NOT NULL
);

CREATE TABLE room (
    id           uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    number       varchar(10) NOT NULL UNIQUE,
    room_type_id uuid        NOT NULL REFERENCES room_type (id)
);

CREATE INDEX room_room_type_id_idx ON room (room_type_id);

-- +goose Down
DROP TABLE room;
DROP TABLE room_type;

Example Queries

-- name: GetRoomType :one
SELECT * FROM room_type WHERE id = $1;

-- name: ListRoomTypes :many
SELECT * FROM room_type ORDER BY name;

Entity Model → SQL Mapping

Table and column names are snake_case, singular table names.

Workflow

1. Read docs/entity_model.md
2. Read the existing migrations in db/migrations/ (if any) to know the current schema
3. Bootstrap once, if missing:
   • sqlc.yaml:

     version: "2"
     sql:
       - engine: "postgresql"
         schema: "db/migrations"
         queries: "db/queries"
         gen:
           go:
             package: "db"
             out: "internal/db"
             sql_package: "pgx/v5"
             emit_interface: true
     

   • db/migrations/embed.go:

     package migrations
     
     import (
         "context"
         "database/sql"
         "embed"
     
         _ "github.com/jackc/pgx/v5/stdlib"
         "github.com/pressly/goose/v3"
     )
     
     //go:embed *.sql
     var FS embed.FS
     
     // Up applies every pending migration to the database at dsn.
     func Up(ctx context.Context, dsn string) error {
         db, err := sql.Open("pgx", dsn)
         if err != nil {
             return err
         }
         defer db.Close()
         p, err := goose.NewProvider(goose.DialectPostgres, db, FS)
         if err != nil {
             return err
         }
         _, err = p.Up(ctx)
         return err
     }
     

   • db/migrations/migrations_integration_test.go — the round-trip check (build tag integration): start a testcontainers-go Postgres container, run Up, then goose.NewProvider(...).DownTo(ctx, 0), then Up again; fail on any error. This proves every Down section reverses its Up.
   • Tools: go get -tool github.com/pressly/goose/v3/cmd/goose; verify sqlc version (install the binary per the sqlc docs if missing). Use context7 to confirm goose and sqlc configuration for the installed versions.
4. Create the migration file: go tool goose -dir db/migrations create <descriptive_name> sql
5. Write the Up section from the entity model (see the mapping table) and a Down section that reverses it exactly, in reverse dependency order
6. Ensure relations match the entity model (foreign keys, join tables, ON DELETE behaviour only when the model states it)
7. For each new table, add db/queries/<entity>.sql with a get-by-id and a list query if the file does not exist. Use-case-specific queries are added later by /implement
8. Run sqlc generate
9. Validate the migration:
   • go test -tags=integration ./db/migrations/ passes — the migrations apply to a fresh container and survive a full down/up round trip (Docker must be running)
   • go build ./... succeeds against the regenerated internal/db/
   • Every entity in the entity model has a table; every attribute has a column with the mapped type and constraints; every relation has a foreign key
   • Read the migration SQL once more for a destructive statement in Up — if there is one, confirm with the user

Verification

The skill is complete when:

• go test -tags=integration ./db/migrations/ exits 0
• sqlc generate exits 0 and go build ./... exits 0
• internal/db/ changes are staged with the migration (generated code is committed)
• No previously applied migration file was modified (git diff origin/main -- db/migrations/ shows only added files)