# Prisma Migrations — Operator Runbook

> **Sprint origin:** AUDIT-2 #3 (2026-05-23). Third-party audit caught that `docker-entrypoint.sh` runs `npx prisma migrate deploy`, but the repo had zero proper Prisma migrations — every schema change lived in `prisma/legacy-manual-sql/*.sql` and was applied by hand. So `migrate deploy` always reported "No pending migrations to apply" and exited 0, while the app booted expecting columns that didn't exist (P2022 on first write). The "AUDIT-10 migration deploy" guard from PR #47 was theatre.

This runbook is the one-time operator action required after PR #60 lands. After that, schema changes follow the standard Prisma workflow described at the bottom.

---

## Background: what changed in PR #60

- All 14 SQL files under `prisma/legacy-manual-sql/` were re-encoded as proper Prisma migration directories:
  ```
  prisma/migrations/
    20260101000001_b_add_heartbeat_errorclass/migration.sql
    20260101000002_c_add_maintenance_window/migration.sql
    20260201000001_p1_10_add_report_preference_lastsentat/migration.sql
    ...
    20260523000002_audit_8_add_apitoken_noc_columns/migration.sql
    migration_lock.toml
    manual/                # original SQL kept for reference
  ```
- `migration_lock.toml` declares MySQL as the provider so Prisma's diff/format tooling works.
- `docker-entrypoint.sh` already runs `npx prisma migrate deploy` (no change needed).
- The `manual/` directory is retained — same SQL, kept as the historical record + the format `scripts/maint/_run-manual-migration.ts` reads.

## One-time operator action on the live DB (post-merge)

Each live deployment that has already applied these 14 manual migrations needs to tell Prisma "yes, this migration is already in the schema — don't re-run it." That's `prisma migrate resolve --applied`.

Do this **once per environment** (Evidence Action dev DB, prod, any staging mirror):

```bash
cd /path/to/uptime-sentinel

# Sanity check that DATABASE_URL points at the right environment.
echo "Resolving against: $DATABASE_URL"

npx prisma migrate resolve --applied 20260101000001_b_add_heartbeat_errorclass
npx prisma migrate resolve --applied 20260101000002_c_add_maintenance_window
npx prisma migrate resolve --applied 20260201000001_p1_10_add_report_preference_lastsentat
npx prisma migrate resolve --applied 20260301000001_pr19_add_status_page_subscriber
npx prisma migrate resolve --applied 20260301000002_prb_2_add_monitor_requiredups
npx prisma migrate resolve --applied 20260301000003_prb_3_add_monitor_dedupwindowseconds
npx prisma migrate resolve --applied 20260301000004_prc_1_drop_monitor_auth_columns
npx prisma migrate resolve --applied 20260301000005_pr26_add_pending_escalation
npx prisma migrate resolve --applied 20260501000001_ea_1_audit_log_hash_chain
npx prisma migrate resolve --applied 20260501000002_ea_3_monitor_display_name
npx prisma migrate resolve --applied 20260501000003_ea_4_drop_monitor_jsonpath
npx prisma migrate resolve --applied 20260501000004_ea_6_monitor_retries_default
npx prisma migrate resolve --applied 20260523000001_audit_6_add_monitor_heartbeattokenhash
npx prisma migrate resolve --applied 20260523000002_audit_8_add_apitoken_noc_columns
# AUDIT-2 #17e (2026-05-24): destructive DROP TABLE — see notes below.
# Run AFTER the audit2-17ef PR merges + the schema/client diff is
# already reflected in this checkout. Pre-merge environments can
# skip; the migration is idempotent (DROP TABLE IF EXISTS).
npx prisma migrate resolve --applied 20260524000001_audit2_17e_drop_synthetic_test
# AUDIT-2 #18 (2026-05-24): destructive DROP TABLE — SLATarget + SLAReport.
# Operator-requested removal of the SLA Tracking feature (0 rows in
# live DB). Drops leaves-first: SLAReport before SLATarget.
npx prisma migrate resolve --applied 20260524000002_audit2_18_drop_sla
# AUDIT-2 #6 (2026-05-24): replaces AuditLog BEFORE INSERT trigger with
# length-prefixed canonicalisation (v2). Existing rows keep v1 hashes —
# verifyChain accepts either, so chain continuity is preserved across
# the migration boundary.
npx prisma migrate resolve --applied 20260524000003_audit2_6_audit_chain_v2
# AUDIT-3 (2026-05-28): non-destructive — adds composite index
# (monitorId, status) on Heartbeat so monitor-engine's recentDowns
# count is served from the index alone.
npx prisma migrate resolve --applied 20260528000001_audit3_heartbeat_monitorid_status_index
# audit3-followup (2026-05-29): non-destructive — creates
# BruteForceCounter table. Replaces the per-process in-memory Maps in
# src/lib/auth/{login-attempts,twofa-attempts}.ts so brute-force
# counters survive restarts. Additive change; no rows to backfill.
npx prisma migrate resolve --applied 20260529000001_audit3_brute_force_counter
# audit3-followup (2026-05-29): non-destructive — adds nullable
# User.passwordChangedAt for session-rotation cutoff. NULL value
# preserves pre-PR behaviour for users who haven't changed their
# password since this column landed.
npx prisma migrate resolve --applied 20260529000002_audit3_user_password_changed_at
# heartbeat-partition (2026-05-31): ⚠️ DESTRUCTIVE / STRUCTURAL — rewrites
# the Heartbeat table to RANGE-partition it on createdAt, changes the PK to
# composite (id, createdAt), and DROPS the Monitor FK (MySQL forbids FKs on
# partitioned tables). Do NOT `--applied` this one blindly: unlike the rows
# above, its SQL must actually RUN against the table. Only resolve-as-applied
# on a DB where the partitioning is already physically present. See
# "Heartbeat partitioning" below for the maintenance-window apply procedure.
npx prisma migrate resolve --applied 20260531000001_heartbeat_partition
# vendor-sla (2026-06-02): non-destructive — creates the Vendor table and
# adds nullable Monitor.vendorId (+ FK + index) for vendor-attributed SLA
# tracking. Additive; existing monitors get vendorId = NULL. Safe to apply
# ahead of / behind other in-flight branches.
npx prisma migrate resolve --applied 20260602000001_vendor_sla

# Verify clean state — should print "Database schema is up to date!"
npx prisma migrate status
```

After that, `npx prisma migrate deploy` is a true no-op on this DB. The docker entrypoint becomes silent on subsequent boots.

### What `--applied` does

`prisma migrate resolve --applied <name>` writes a row into the `_prisma_migrations` table marking the migration as already executed. It does **not** run the migration's SQL. This is purely additive metadata — safe to run before merge if you want to test the runbook, with no schema impact.

If you accidentally run it on a DB that does **not** have the migration applied, Prisma's next `migrate deploy` will not catch up because Prisma thinks the migration is already there. Recover with `prisma migrate resolve --rolled-back <name>` to unmark, then either run the SQL by hand or let `migrate deploy` apply it.

## Fresh deployments

For a brand-new deployment (no prior schema), the entrypoint's `npx prisma migrate deploy` now does the right thing automatically:

1. `docker-compose pull && up -d`
2. Container boots, entrypoint runs `prisma migrate deploy`
3. Each of the 14 migrations applies in order against the empty DB
4. App boots with the expected schema

No operator action required.

## cPanel / Passenger deploys — use `deploy.sh`

The Docker entrypoint migrates on boot, but the **live cPanel host boots `server.js` via Passenger directly and runs no migrations**. A bare `git pull` there ships code against the old schema — which took live login down on 2026-05-30 (deployed code `SELECT`ed `User.passwordChangedAt` before its migration applied → NextAuth `AccessDenied`).

**Always deploy on cPanel with the repo's `deploy.sh`** — one command, from the app root:

```bash
./deploy.sh
```

`deploy.sh` runs **`git pull --ff-only origin main`** → `npm ci` → `prisma generate` → **`prisma migrate deploy`** → `npm run build` → restart, with `set -euo pipefail` so it **fails closed**: a failed pull/migration aborts the deploy with the old (working) code still serving, instead of restarting into a broken state. The pull is `--ff-only` so a diverged server checkout refuses to deploy rather than silently merging. The `audit3-migrate-deploy-guard` smoke enforces that the pull precedes the build and `migrate deploy` precedes the restart.

## Baselining a legacy production DB (P3005)

A DB first built via the old `manual/*.sql` process has **no Prisma migration history**, so `prisma migrate deploy` fails with **P3005 "database schema is not empty."** One-time fix — mark every already-applied migration as applied (writes history only, runs no SQL):

```bash
# Run once per legacy environment. List comes from `npx prisma migrate status`.
for m in <each already-applied migration directory name>; do
  npx prisma migrate resolve --applied "$m"
done
npx prisma migrate deploy   # now applies only genuinely-new migrations
npx prisma migrate status   # → "Database schema is up to date!"
```

> If a migration's DDL was **not** yet applied to that DB, do NOT resolve it — let `migrate deploy` run it. Resolve only the ones whose schema is physically present.

The legacy reference SQL lives in [`prisma/legacy-manual-sql/`](../../prisma/legacy-manual-sql/) (moved out of `prisma/migrations/` on 2026-05-30 — a folder there without a `migration.sql` makes `migrate deploy` fail P3015).

## Going forward: adding new migrations

After this PR lands, the workflow for schema changes is the standard Prisma flow — **not** the old `manual/*.sql` pattern.

1. Edit `prisma/schema.prisma`.
2. Generate the migration:
   ```bash
   npx prisma migrate dev --name <slug>
   ```
   This creates `prisma/migrations/<timestamp>_<slug>/migration.sql` with the auto-generated DDL **and** applies it to the local dev DB.
3. Review the generated SQL. If it's correct, commit both the schema change and the new migration directory.
4. Ship the PR.
5. On merge + deploy, `docker-entrypoint.sh`'s `prisma migrate deploy` applies the migration automatically against each live DB.

### Destructive migrations

The pre-existing ground rule still holds: **destructive DDL** (`DROP COLUMN`, `DROP TABLE`, `RENAME`, `CHANGE TYPE`) must not be applied to the live dev DB until the PR is merged. With proper Prisma migrations this is enforced naturally — the migration only runs on `migrate deploy` after the container picks up the merged image. During PR development, only run `prisma migrate dev` against your own local DB.

For purely additive migrations (`ADD COLUMN <nullable>`, `ADD INDEX`, `CREATE TABLE`), you can run `prisma migrate dev` against the shared dev DB if you want to validate the schema before merge — but tag in the team channel first so no one is mid-test.

### What about `prisma db push`?

Don't use it for schema changes any more. `db push` synchronises the schema without recording a migration, which means:
- Your local DB has the change.
- The repo doesn't.
- The next time someone runs `migrate dev`, Prisma sees the drift and generates a corrective migration with a confusing name.
- Production never gets the change.

`db push` is fine for spike branches you'll throw away. For anything that ships, use `migrate dev`.

## Heartbeat partitioning

> **Migration:** `20260531000001_heartbeat_partition` — PRD §7 deferred item #14. ⚠️ **Destructive / structural and NOT auto-applied on cPanel.**

### Why

At the Evidence Action fleet size `Heartbeat` accrues ~21M rows/month. `RollupService.runRollup` reclaims space with a per-monitor `DELETE FROM Heartbeat WHERE monitorId = ? AND createdAt < ?`. At tens of millions of rows that DELETE holds a long row-by-row lock and InnoDB never returns the freed pages to the filesystem (the tablespace high-water mark stays up). RANGE partitioning on `createdAt` replaces that with `ALTER TABLE Heartbeat DROP PARTITION pYYYYMM` — an instant metadata operation that frees the disk immediately.

### What the migration changes (read before applying)

1. **Drops the `Heartbeat → Monitor` foreign key.** MySQL cannot carry a FK on a partitioned table. Safe here because monitor deletion is **soft-delete only** (`MonitorService.deleteMonitors` sets `deletedAt`; there is no hard `prisma.monitor.delete` in app code), so the `ON DELETE CASCADE` was never exercised in production. Integrity now rests on app logic. The Prisma relation is retained (Prisma joins on the field map, not the DB FK) so `include: { heartbeats }` keeps working.
   - **Caveat for tests:** the e2e teardown in `src/e2e/e2e/monitor-lifecycle.spec.ts` hard-`deleteMany`s test monitors and previously relied on the cascade to clear their heartbeats. Against a partitioned DB that leaves orphan heartbeat rows. Harmless for the assertion (test monitors are unique-named) but worth knowing.
2. **Changes the PK to composite `(id, createdAt)`** — every key on a partitioned table must contain the partition column. `id` stays the leading column so it remains `AUTO_INCREMENT` and globally unique (the `/api/dashboard/kpi` `h.id = (SELECT id … LIMIT 1)` correlated subquery still resolves a single row).

### Applying it (maintenance window, on a restored-backup test first)

The cPanel host does **not** run `prisma migrate deploy`, so this never applies itself there. Apply by hand:

```bash
# 0. On a RESTORED-BACKUP copy first — time it; the ALTER ... PARTITION BY
#    rewrites the entire table, so it scales with row count and needs free
#    disk ≈ the current Heartbeat size.
mysql "$DB" < prisma/migrations/20260531000001_heartbeat_partition/migration.sql

# 1. Confirm the layout took.
mysql "$DB" -e "SELECT PARTITION_NAME, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_NAME='Heartbeat';"

# 2. Tell Prisma it's applied (writes history only, runs no SQL).
npx prisma migrate resolve --applied 20260531000001_heartbeat_partition
```

### Monthly maintenance routine (this replaces the slow retention DELETE)

Partition naming: `pYYYYMM` holds `createdAt < first day of the NEXT month` (e.g. `p202605` = all of May 2026). `pmax` is the `MAXVALUE` catch-all.

**Add next month's partition** — split it off `pmax` so future inserts have a home (run a few days before month end):

```sql
-- Carve 2026-10 out of the catch-all, keeping pmax for everything beyond.
ALTER TABLE Heartbeat REORGANIZE PARTITION pmax INTO (
    PARTITION p202609 VALUES LESS THAN (TO_DAYS('2026-10-01')),
    PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);
```

> `REORGANIZE PARTITION pmax` only rewrites rows currently in `pmax`. If `pmax` is empty (the normal case when you stay ahead of it) this is near-instant. Equivalent shorthand: `ALTER TABLE Heartbeat ADD PARTITION (...)` only works when there is no `MAXVALUE` partition — because we keep one, always `REORGANIZE pmax`.

**Drop an aged-out partition** — instant, frees disk, replaces `DELETE FROM Heartbeat WHERE createdAt < cutoff`:

```sql
-- Raw heartbeats older than the rollup window are already aggregated into
-- HeartbeatHourly, so dropping the month is lossless for analytics.
ALTER TABLE Heartbeat DROP PARTITION p202603;
```

### How this could later replace the rollup DELETE

`RollupService.processMonitorRollup` still issues `DELETE FROM Heartbeat WHERE monitorId = ? AND createdAt < ?` after aggregating into `HeartbeatHourly`. That DELETE is **per-monitor + createdAt-scoped**, so it cannot be swapped for a single `DROP PARTITION` (a partition spans all monitors). The intended follow-up is to flip the retention model to **whole-month granularity**: once every monitor's data for month *M* has been rolled into `HeartbeatHourly`, drop `pM` in one statement instead of N per-monitor DELETEs. That change is out of scope for this migration (it touches the rollup scheduling contract) and should land as its own PR once partitioning is live and validated in prod.

## The legacy `manual/*.sql` directory

The 14 files under `prisma/legacy-manual-sql/` are kept for:

1. **Historical reference** — the `-- UP / -- DOWN` markers + commentary capture the intent of each migration in a richer form than auto-generated Prisma SQL.
2. **Rollback record** — Prisma's `migrate deploy` doesn't run `down` migrations. If you need to roll back a specific migration manually, the `-- DOWN` block in the corresponding `manual/*.sql` is the documented reverse.
3. **`scripts/maint/_run-manual-migration.ts`** — local one-shot helper that knows the `manual/` format. Useful for applying out-of-band SQL during incident response without going through Prisma.

The `manual/` directory is **not** read by `prisma migrate deploy` — it has no effect on production. Treat it as documentation.

## Troubleshooting

### "Database schema is not in sync with the migration history"

Run `prisma migrate status` to see which migrations Prisma thinks haven't been applied. Compare against actual schema (`DESCRIBE <table>`). If the schema has the change but Prisma's metadata is missing, `prisma migrate resolve --applied <name>` will fix it.

### "P2022: column X does not exist"

The app expects a column the schema doesn't have. Check:
1. `prisma migrate status` — is the migration that adds X listed as pending?
2. If pending: run `prisma migrate deploy` (or restart the container — entrypoint runs it).
3. If listed as applied but the column isn't there: the operator ran `--applied` against the wrong DB or the migration was rolled back. Run `prisma migrate resolve --rolled-back <name>` then `prisma migrate deploy`.

### "ENOENT: prisma/migrations/migration_lock.toml"

You're on a branch before this PR landed. Pull main.

## Related

- `docker-entrypoint.sh` — runs `prisma migrate deploy` on container boot (gated by `SKIP_DB_MIGRATIONS`).
- `scripts/maint/_run-manual-migration.ts` — local one-shot SQL runner against `manual/*.sql` (kept for incident-response use).
- `docs/audit-2-followups.md` — see item 11 for the long-term Prisma improvements (CI `migrate diff` check, strict `migrate dev` enforcement).