db-migration

---
name: db-migration
description: >
  Creates goose SQL migrations and sqlc queries from the entity model, then regenerates the
  type-safe Go database code. Use when the user asks to "create a migration", "update the
  schema", "set up database tables", "write a goose migration", "add sqlc queries", or mentions
  schema migration, DB migration, database versioning, goose, or sqlc.
---

# 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

```sql
-- +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

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

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

## Entity Model → SQL Mapping

| Entity model | PostgreSQL |
|---|---|
| ID | `uuid PRIMARY KEY DEFAULT gen_random_uuid()` |
| String with max length | `varchar(N)` |
| Text without limit | `text` |
| Integer / Long | `integer` / `bigint` |
| Decimal / money | `numeric(P,S)` — never `float` for money |
| Boolean | `boolean NOT NULL DEFAULT false` (or the model's default) |
| Date / DateTime | `date` / `timestamptz` |
| Enum | `text` + `CHECK (col IN (...))` |
| Required | `NOT NULL` |
| Unique | `UNIQUE` |
| Min / max value | `CHECK (...)` |
| Many-to-one | `<entity>_id uuid NOT NULL REFERENCES <entity> (id)` + index |
| Many-to-many | join table with composite primary key of both foreign keys |

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`:
     ```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`:
     ```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)