Migration Convention

This page mirrors docs/src/app/docs/database-schema/migration-convention.md. Keep both files aligned to avoid conflicting guidance.

Purpose

Defines mandatory rules for creating, reviewing, and deploying Prisma migrations in StudyBoost shared environments.

Core Principles

  1. Forward-fix only on shared environments. Never rewrite migration history after merge.
  2. Safety must be enforceable, not implicit. Destructive changes require explicit checklist + approval.
  3. Intent must be documented in the migration itself. Reviewers should understand why/impact without searching old PRs.
  4. Parallel work is allowed, unsafe ordering is not. Coordinate and resolve ordering conflicts without manually editing _prisma_migrations.

Required Migration Header (Mandatory)

Every migration.sql must start with this intent block:

-- ============================================================
-- StudyBoost — <Short Title>
-- Migration: YYYYMMDDHHmm_short_description
-- Date: YYYY-MM-DD HH:mm
-- PURPOSE: <why this migration exists>
-- IMPACT: <tables/columns/indexes and runtime impact>
-- DATA RISK: <none|low|medium|high + brief justification>
-- ROLLBACK STRATEGY: <forward-fix plan, not migrate down>
-- Safe to re-run: uses IF NOT EXISTS / IF EXISTS guards
-- ============================================================

Dangerous Migration Classification (Mandatory)

A migration is dangerous if it does any of the following:

  • DROP TABLE, DROP COLUMN, or destructive ALTER on critical columns.
  • Large backfills/data rewrites that can lock tables or alter many rows.
  • Constraint tightening that may fail on existing data.

For dangerous migrations:

  • Include -- Type: Dangerous migration in header.
  • Include a dedicated section in SQL comments: -- DANGEROUS CHANGE REVIEW.
  • PR description must include checklist evidence (backup, row counts, approvals).

Destructive Migration Checklist (Required for Prod/Staging)

Before running pnpm run migrate:prod for any dangerous migration, all must be confirmed:

  • Backup/snapshot confirmed (timestamp + owner recorded in PR).
  • Pre-migration row counts captured for affected tables.
  • Expected data impact documented (what will be removed/changed).
  • Explicit approver sign-off recorded in PR (backend owner or designated reviewer).
  • Post-migration verification queries prepared and run.

No checklist evidence = no deploy.

Parallel Migration Workflow (Scalable)

  1. Coordinate with teammates if migrations touch overlapping tables.
  2. Multiple migration PRs may be open in parallel.
  3. Before merge, rebase on latest dev and regenerate migration if ordering drift occurs.
  4. If conflict occurs, never manually edit _prisma_migrations. Instead:
    • Recreate migration from clean state in correct order.
    • Re-run pnpm run migrate:status.
    • Document resolution in PR.

Migration Changelog Requirement (Human-Readable)

Each migration PR must include a short rationale block:

  • Problem solved
  • Why this approach was chosen
  • Data-impact summary
  • Operational notes (runtime/locking expectations)

This is required for explainability and onboarding.

Quick Reference

Current active Neon branch (v2)

  • Shared v2 migration work is currently targeting Neon branch br-blue-glade-a4t2qrmy: https://console.neon.tech/app/projects/frosty-haze-02246987/branches/br-blue-glade-a4t2qrmy/tables?database=StudyboostDatabase
  • When running shared migration commands, confirm backend/.env DATABASE_URL points to this v2 branch.

Commands (Shared DB Development)

  • Shared DB status check: pnpm run migrate:status
  • Shared DB apply pending migrations: pnpm run migrate:prod
  • Optional alias (non-destructive): pnpm run migrate (mapped to migrate:status)
  • Do not use migrate:dev on the shared Neon branch.

When we finally switch to production (Runbook)

  • Final production apply command: pnpm -C backend run migrate:prod
  • Recommended safety sequence at release time:
    1. pnpm -C backend run migrate:status
    2. pnpm -C backend run migrate:prod
    3. pnpm -C backend run migrate:status

If target DB is a non-empty branch copy (P3005)

  • Symptom: migrate:prod fails with P3005 (The database schema is not empty).
  • Cause: branch has existing tables/data but no Prisma migration history for that branch yet.
  • One-time baseline flow before deploy:
    1. pnpm -C backend run migrate:status
    2. pnpm -C backend prisma migrate resolve --applied 000000000000_baseline
    3. pnpm -C backend run migrate:status
    4. pnpm -C backend run migrate:prod
    5. pnpm -C backend run migrate:status
  • Note: migrate resolve --applied updates migration history only; it does not delete table rows.

What developers should run in dev (shared branch model)

  • For day-to-day development on the shared Neon DB:
    1. pnpm -C backend run migrate:status
    2. pnpm -C backend run migrate:prod
    3. pnpm -C backend run migrate:status
  • Team rule: treat the shared dev DB like a deploy target; use migrate:prod for applying migration files.
  • Use migrate:dev only on disposable local databases.

Command Safety Note (Required Team Habit)

  • Always run explicit scripts with a colon (migrate:status, migrate:prod, migrate:dev).
  • Do not use pnpm run migrate status expecting migrate:status; this runs the migrate script with an extra argument.
  • Use pnpm run migrate:status for status checks and pnpm run migrate:prod only when you intentionally want to apply migrations.

Rollback / Recovery Policy

  • Prisma does not provide a standard migrate down workflow in normal team usage.
  • If a migration is wrong on shared dev/staging/prod, create a new forward corrective migration (do not rewrite history).
  • prisma migrate reset is destructive (data loss) and is last resort only for disposable local environments.
  • For shared environments, never use reset; run pnpm run migrate:status to verify state before and after corrective migrations.

Example Header

-- ============================================================
-- StudyBoost — Add User Preferences Table
-- Migration: 202604211430_add_user_preferences
-- Date: 2026-04-21 14:30
-- PURPOSE: Persist per-user UI preferences for dashboard experience.
-- IMPACT: Adds new `user_preferences` table and PK index; no destructive operations.
-- DATA RISK: low (additive schema only).
-- ROLLBACK STRATEGY: forward-fix by follow-up migration if schema adjustments are needed.
-- Safe to re-run: uses IF NOT EXISTS guards
-- ============================================================

CREATE TABLE IF NOT EXISTS user_preferences (
    user_id UUID PRIMARY KEY,
    theme   VARCHAR(50) NOT NULL DEFAULT 'light'
);