Database Migrations

Approach

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

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

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)