Database Operations (Forge Backend)

Forge's backend-api uses Prisma with SQLite in development and PostgreSQL in production. All schema changes must go through Prisma migrations — never use prisma db push outside of development.

When to Use This Skill

• Reviewing a PR that modifies prisma/schema.prisma
• Reviewing a PR that adds files under prisma/migrations/
• Running migrations before a production deploy
• Auditing schema for unused tables or columns
• Responding to a migration-related incident

Migration Safety Checklist

Before approving or merging any PR that changes the schema, verify all of the following:

1. All new columns are nullable or have defaults

Zero-downtime deploys require that existing rows satisfy the new schema immediately.

Safe:

emailVerified Boolean  @default(false)
displayName   String?

Unsafe — will fail on existing rows in production:

requiredField String   // no default, not nullable

2. Index coverage

Every @relation field (foreign key) must have a @@index. Fields used in hot where clauses should also be indexed. Check prisma/schema.prisma for any @relation without a matching @@index.

3. Review the generated SQL before committing

Run prisma migrate dev --name <description> locally, then read the generated SQL:

cat prisma/migrations/*/migration.sql | tail -40

Red flags:

• DROP TABLE or DROP COLUMN on tables/columns still referenced in src/
• ALTER TABLE ... ADD COLUMN without DEFAULT or NOT NULL (with default)
• Missing IF NOT EXISTS on index creation

4. Deploy procedure (staging and production)

# 1. Snapshot the database before migrating — coordinate with ops
# 2. Deploy application binary first, then run migration
npx prisma migrate deploy

# 3. Confirm status
npx prisma migrate status
# Expected: "All migrations have been applied."

Never use prisma db push outside of dev — it can silently drop data.

5. Rollback

Prisma does not auto-rollback. If a migration causes an incident:

1. Identify the bad migration: npx prisma migrate status
2. Write a compensating migration that reverts the change
3. Apply it: npx prisma migrate deploy
4. File an incident report noting the migration hash and impact

Removing Deprecated Tables or Columns

Only remove a column or table after all three conditions are met:

1. No code in src/ references it (verify with grep -r "columnName" src/)
2. At least one full deploy cycle has passed with the code reference removed
3. A backup snapshot exists

# 1. Confirm no references
grep -r "oldColumn" src/

# 2. Remove from schema.prisma, then create migration
npx prisma migrate dev --name remove_deprecated_old_column

# 3. Inspect the SQL — should be a single DROP COLUMN line
cat prisma/migrations/$(ls -t prisma/migrations | head -1)/migration.sql

Credentials

• Dev: DATABASE_URL=file:./dev.db (.env)
• Test: DATABASE_URL=file:./test.db (set automatically in tests/setup.ts)
• Staging and production credentials are in the team vault under forge/db/