Set Up Database

Rules and per-track seam code: database-patterns.md. Migration workflow, safety checklist, per-tool recipes: migration-patterns.md. Layer paths: service-layout.md. Config: config.md. Versions: stack-versions.md.

Postgres only. Schema changes are versioned SQL files in git, reviewed in the PR, applied forward-only to shared environments.

1. Audit (change nothing)

cat .claude/stack-profile.md ~/.claude/stack-profile.md 2>/dev/null   # backend.track, database.{engine,orm,host}, task_runner
ls compose.yaml docker-compose.yml drizzle.config.ts drizzle sqlc.yaml migrations alembic alembic.ini db/seed.sql 2>/dev/null
ls src/platform/db.ts internal/platform/db src/*/platform/db.py 2>/dev/null
grep -rnE "drizzle-kit push|db push|create_all\(|AutoMigrate|synchronize: *true" . --include=* -I 2>/dev/null | grep -v node_modules | head
grep -rnE "DATABASE_URL" .env.example src internal 2>/dev/null | head -5
git ls-files | grep -E "\.env($|\.)" | grep -v example           # committed env file = finding

Findings to record: a push/create_all/auto-migrate call anywhere that can reach a shared environment, a connection created outside the seam, SQL strings in services, no .env.example key for DATABASE_URL, migrations edited after merge.

2. Decide

• backend.track: supabase or database.host: supabase: the Supabase CLI owns migrations. Run set-up-supabase and use this skill only for the ORM seam and the migration-safety checklist.
• backend.track: nextjs: the seam path is the app's src/db/; follow build-nextjs-backend, apply the rules here.
• No database seam, no migrations: full setup (steps 3 to 7).
• Seam exists, migrations are tool-generated and reviewed: add only the gaps (pooling, transaction helper, seed, safety gate).
• A push or auto-migrate call reaches a shared environment: replace it with the migration command (step 6) before anything else.
• All present and step 7 passes: report "already in place" and stop.

3. Detect track

orm: none or unset: ask one question only if the track does not decide it. Defaults are the table's row for the track.

4. Install only what is missing

# hono
pnpm add drizzle-orm pg && pnpm add -D drizzle-kit @types/pg
# go
go get github.com/jackc/pgx/v5
go get -tool github.com/sqlc-dev/sqlc/cmd/sqlc@latest
go get -modfile=tools.mod -tool github.com/pressly/goose/v3/cmd/goose@latest   # keeps goose's dependencies out of go.mod
# fastapi
uv add "sqlalchemy[asyncio]" "psycopg[binary,pool]" alembic

Pin drizzle-orm and drizzle-kit to the stable latest line (0.45 / 0.31 today); 1.0 is rc, see stack-versions.md. Use the [asyncio] extra: without greenlet, importing sqlalchemy.ext.asyncio fails.

5. Generate the seams

1. compose.yaml: Postgres 18, health check, named volume at /var/lib/postgresql (Postgres 18 images keep the data in a versioned subfolder; the old /var/lib/postgresql/data mount is wrong). File in database-patterns.md.
2. .env.example: DATABASE_URL=postgres://app:app@127.0.0.1:5432/app. Add the key to the config schema (config.md); no default.
3. DB seam, one per track (code in database-patterns.md):
   • TS src/platform/db.ts: pool, transaction, ping, close; schema in src/repository/schema.ts.
   • Go internal/platform/db/db.go: pgxpool, InTx, ping; sqlc output in internal/repository/db/.
   • Python src/<pkg>/platform/db.py: async engine, get_session.
4. Repositories take a connection or transaction handle; services open the transaction (service-layout.md). One repository per aggregate.
5. Migration tool config: drizzle.config.ts + drizzle/, or sqlc.yaml + migrations/, or alembic/ (template pyproject_async).
6. db/seed.sql: idempotent development data (INSERT ... ON CONFLICT DO NOTHING). Reference data the app needs in production goes into a migration, not the seed.
7. Readiness: the service's /readyz calls ping (observability.md).

6. Wire

Four commands per project, named in the profile's task_runner (just, mise, package scripts or make):

Rules for the commands:

• db:migrate runs as a separate deploy step before the new app version starts, never inside app startup (two replicas race; a failed migration should stop the deploy, not crash-loop the app).
• No push, no create_all, no auto-migrate in any command that accepts a shared DATABASE_URL. Local throwaway databases may reset and re-migrate.
• The migration role differs from the runtime role: runtime has DML only; the migration role has DDL and lock_timeout/statement_timeout set on the role (migration-patterns.md).
• CI: start Postgres (service container), db:migrate on an empty database, db:check, then the tests against it. Test against real Postgres, not a mock.

7. Verify

docker compose up -d --wait db && <db:migrate>      # applies on an empty database
<db:check>                                          # TS: "Everything's fine"; Python: exit 0; Go: no diff
psql "$DATABASE_URL" -c '\dt'                       # expect your tables
<db:migrate>                                        # second run: nothing to apply, exit 0

Then the safety gate: for each new migration file, walk the checklist in migration-patterns.md and write the lock level of every statement in the PR description. A transaction test: force an error after an insert inside a service call and confirm zero rows persist.

References

• database-patterns.md: compose, pooling, transactions, seed, per-track seam code.
• migration-patterns.md: forward-only, expand and contract, safety checklist, tool pitfalls.
• ../../core/_shared/security-baseline.md: least-privilege roles, secrets.