Database Migrations

Approach

D1 migrations are managed as numbered SQL files in db/migrations/. The canonical schema is maintained in db/schema.sql.

The db/migrations/ directory is forward-only. wrangler d1 migrations apply runs every .sql file in that directory in alphabetical order, so any non-forward script (rollback, hotfix, one-off) MUST live elsewhere. Manual rollbacks live in db/rollbacks/ (see Rollbacks below).

Creating a Migration

Use the /db-migrate Claude Code command:

/db-migrate

Or manually:

1. Create db/migrations/NNNN_description.sql with the SQL changes
2. Apply locally: wrangler d1 execute nucleus-db --local --file=db/migrations/NNNN_description.sql
3. Update db/schema.sql to reflect the current state
4. Test locally
5. Apply to production before deploy: wrangler d1 execute nucleus-db --remote --file=db/migrations/NNNN_description.sql

Existing Migrations

File	Description
db/migrations/001-squads.sql	Squads, squad members, permissions, user fields
db/migrations/002-templates.sql	Template system: onboarding/scorecard templates, migrate from job_role to job_title
db/migrations/003-common-template.sql	Convert common onboarding items (NULL template_id) to locked Common template
db/migrations/004-template-permissions.sql	Add configurable permissions for the templates section
db/migrations/005-objectives.sql	Objectives hierarchy (objectives, priorities, kpi_definitions), scorecard comments + KPI linking
db/migrations/006-scorecard-types.sql	Add type (weekly/monthly) and period_key columns to scorecards, unique constraint per user/type/period

Rollbacks

Manual rollbacks live in db/rollbacks/<migration-id>.down.sql. Never put a *.down.sql (or any other non-forward script) inside db/migrations/ — wrangler will auto-apply it as a forward migration on the very next migrations apply and silently undo the change above it.

Run a rollback manually against an explicit target:

cd apps/app
pnpm wrangler d1 execute nucleus-db --remote \
  --file=db/rollbacks/<migration-id>.down.sql

Rollbacks should be idempotent (DROP TABLE IF EXISTS …, DROP INDEX IF EXISTS …) but treat them as destructive — confirm the target DB and intent before running.

Best Practices

• Use CREATE TABLE IF NOT EXISTS and CREATE INDEX IF NOT EXISTS for idempotent schema
• Use INSERT OR REPLACE for seed data that may already exist
• Use the SQLite copy-rename pattern for column changes (D1 has limited ALTER TABLE support)
• Always update db/schema.sql to match the current state after migrations
• Test migrations locally before applying to production
• Back up production data before destructive migrations (D1 supports point-in-time recovery)
• Keep db/migrations/ forward-only; put manual rollbacks in db/rollbacks/