# Runbook — Database migrations

> Audience: any engineer touching `prisma/schema.prisma`, `dotnet/src/Domain/**`, or `database/migrations/**`.

## Mental model

Two writers, one schema.

- **Postgres** is canonical. Prisma owns the migrations.
- **SQL Server** is a hand-mirrored parallel deployment for environments where Postgres isn't possible. Updated only when a Postgres migration ships.
- **EF Core** on the .NET side mirrors only the tables it reads/writes — entity configs in `dotnet/src/Infrastructure/Persistence/Configurations/`. It does **not** own migrations.

## Day-to-day workflow

### Adding a column

```bash
# 1. Edit prisma/schema.prisma
# 2. Create the migration (interactive — Prisma prompts for a name)
pnpm db:migrate
#    → prisma/migrations/<timestamp>_<name>/migration.sql gets committed

# 3. Update the EF entity + EntityTypeConfiguration in dotnet/
#    Build to confirm
dotnet build dotnet

# 4. If the change touches an API DTO, update packages/api-contracts/src/zod/*
#    Run typecheck across the workspace
pnpm typecheck

# 5. Mirror the change in database/migrations/000N_<name>.sql for SQL Server
#    Use IF NOT EXISTS / IF COL_LENGTH guards

# 6. Run tests
pnpm test && dotnet test dotnet
```

### Removing a column (breaking change)

Always do it in **two deploys**:

1. **Stop reading** in code. Ship the deploy. Keep the column.
2. After confirming no caller reads the column (check application logs + the last week of metrics), ship a follow-up migration that drops it.

Never drop a column in the same deploy that stops reading it — rolling back the deploy needs the column to still be there.

### Renaming a column

Use the **expand-then-contract** pattern:

1. Add the new column, copy data from the old via the migration.
2. Update all readers to use the new column. Keep both columns populated by writers.
3. Once a release cycle has passed with no issues, drop the old column.

Prisma's `@map` makes most renames cosmetic at the TS layer (only the DB column needs to move) — exploit that.

### Multi-tenant changes

If you add a `tenantId`-scoped table:

- Add `tenantId` column + `@relation` to `Tenant`.
- Add `@@index([tenantId])` and, where queries are tenant-scoped, `@@index([tenantId, otherKey])`.
- Add a `tenantId` column to the SQL Server mirror with the same constraints.
- If `auth.multiTenant=true` in `project.config.json`, enforce tenant scoping at the EF query-filter layer (`ApplicationDbContext.OnModelCreating`).

## Production deploy

```bash
# CI pipeline (Phase 8 wires this — gitlab-ci.yml + .github/workflows/)
pnpm db:migrate:deploy
```

`prisma migrate deploy` only applies committed migrations and **does not prompt**. Safe to run unattended.

Set the connection string from your secret manager — never from a `.env` file in production.

## Rollback

Prisma does not auto-generate down migrations. To roll back:

1. **Schema-only rollback** (preferred): create a new migration that reverts the change with `prisma migrate dev --name revert_<name>`. Ship it as a normal deploy.
2. **Data-loss rollback** (last resort): restore from the most recent backup (see [`disaster-recovery.md`](disaster-recovery.md) — added Phase 8).

Never edit a migration file that has been applied to production.

## Seeding

```bash
pnpm db:seed                                  # idempotent — safe to re-run
ADMIN_PASSWORD_RESET=true pnpm db:seed        # reset the admin password (use sparingly)
```

For SQL Server-only deployments, run `database/seeds/*.sql` in order. Admin user creation lives in `prisma/seed.ts` (uses scrypt); to seed an admin on SQL Server only, create one via the .NET API after first deploy.

## When things break

| Symptom                                                | Action                                                                              |
| ------------------------------------------------------ | ----------------------------------------------------------------------------------- |
| `prisma migrate dev` says "drift detected"             | Someone applied SQL outside Prisma. `pnpm db:migrate:reset` in dev; never in prod.  |
| `dotnet ef` complains about a missing column           | Your EF entity is out of sync with the schema — mirror the Prisma change.           |
| Seed fails with `unique constraint`                    | Seed is not idempotent. Convert the insert to an `upsert` keyed by a stable column. |
| SQL Server migration runs twice and duplicates indexes | Wrap in `IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = '...')`.             |

## Conventions checklist

Before merging a schema change, confirm:

- [ ] Prisma migration generated and committed under `prisma/migrations/`.
- [ ] EF entity + EntityTypeConfiguration updated (or N/A) — `dotnet build` passes.
- [ ] SQL Server mirror migration created under `database/migrations/`.
- [ ] `packages/api-contracts/` updated if a DTO shape changed.
- [ ] Tests added or updated.
- [ ] If the change is breaking, the deprecation path is documented in the PR.
