Payroll migration deploy runbook

Status: Live — covers Payroll-E1 → P3 (every payroll migration on apps/api/prisma/migrations/ as of 2026-05-29).

This is the operator-side counterpart to the engineering work. It answers: given a freshly merged payroll PR, what does the on-call operator run, and how do they confirm the deploy did what it was supposed to?

---

When this runbook applies

You're applying a payroll-domain migration whose folder starts with apps/api/prisma/migrations/2026* and whose name contains any of:

• payroll_run
• payroll_tax / payroll_pension
• payroll_protection_policy
• payroll_one_time_earning
• payroll_overtime
• payroll_deduction_carry_forward
• payroll_salary_*
• payroll_employee_compensation

For any other migration, follow the generic Prisma deploy in docs/runbooks/drift-detected.md.

Migrations in scope as of 2026-05-30

Migration	Phase	What lands
…600000_payroll_protection_policy	P2-b	Minimum-net floor
…700000_payroll_one_time_earning	P2-d	Bonus / retro
…800000_payroll_overtime	P2-e	Overtime engine
…900000_payroll_deduction_carry_forward	P3	Persisted carry-forward
20260530000000_payroll_deduction_mandate	P3	Generic deduction engine
20260530100000_drop_legacy_payroll	cleanup	Drops legacy Payroll / PayrollEntry lineage
20260530200000_payroll_run_immutability	P4-a	DB triggers
20260530300000_payroll_adjustment	P4-b	Post-approval corrections
20260530400000_payroll_deduction_consumption	P5-a	Cross-domain dispatch tracking
20260530500000_payroll_deduction_event_state	P5-b	Consumer event-state (NO RLS)
20260530600000_payroll_run_locked	P6	LOCKED terminal + extended trigger
20260530700000_payroll_run_entry_pensionable_base	P5-d.b	Frozen bases on run entries

---

Prerequisites

• Migration files merged to main.
• DATABASE_URL points at the target environment (staging or production).
• The DB role used by Prisma must NOT bypass RLS — use the app_user role. The app_admin role bypasses RLS and the verify script will report false positives.
• A psql client is available on the box running the deploy.

---

Deploy procedure

Staging

# 1. Take a logical backup of every payroll-affected table.
#    Lightweight per-table dumps — easier to restore one table than
#    the whole database. The post-P5/P6 set:
pg_dump "$DATABASE_URL" \
  -t payroll_run -t payroll_run_entry \
  -t payroll_tax_rule -t payroll_pension_rule \
  -t payroll_salary_component -t payroll_salary_structure \
  -t payroll_employee_compensation \
  -t payroll_protection_policy \
  -t payroll_one_time_earning \
  -t payroll_overtime_policy -t payroll_overtime_entry \
  -t payroll_deduction_carry_forward \
  -t payroll_deduction_mandate \
  -t payroll_adjustment \
  -t payroll_deduction_consumption \
  -t payroll_deduction_event_state \
  > /var/backups/payroll-pre-$(date -u +%Y%m%dT%H%M%S).sql

# 2. Confirm the migration state. NOTE: do NOT pass --create-db.
(cd apps/api && DATABASE_URL="$DATABASE_URL" npx prisma migrate status)

# 3. Apply pending migrations.
(cd apps/api && DATABASE_URL="$DATABASE_URL" npx prisma migrate deploy)

# 4. Verify the schema state matches what the application expects.
./scripts/verify-payroll-migrations.sh "$DATABASE_URL"

# 5. Run the cross-tenant poller integration spec against staging.
RUN_INTEGRATION=1 DATABASE_URL="$DATABASE_URL" \
  node node_modules/jest/bin/jest.js \
  apps/api/src/payroll/consumers/payroll-deductions-poller.integration.spec.ts \
  --runInBand

# 6. Smoke-test the API. The /healthz endpoint hits the connection
#    pool; /readyz also checks Prisma can resolve the schema.
curl -fsS "$API_BASE/healthz" >/dev/null
curl -fsS "$API_BASE/readyz" >/dev/null

Production via the dedicated migrations container (preferred)

Production environments should NOT bundle npx prisma migrate deploy into the application image — running migrations from the request path is an availability risk. Use the dedicated migrations container instead:

# 1. Build the migrations image. Reproducible per-commit.
docker build \
  -f infra/migrations/Dockerfile \
  -t demozpay/migrations:$(git rev-parse --short HEAD) \
  .

# 2. Apply against the target DB via a one-shot container. The
#    container runs prisma migrate deploy + the verify script and
#    exits.
docker run --rm \
  -e DATABASE_URL='postgresql://migrator:…@db.host:5432/demozpay' \
  demozpay/migrations:$(git rev-parse --short HEAD)

In Kubernetes this maps cleanly onto a Job resource:

apiVersion: batch/v1
kind: Job
metadata:
  name: payroll-migrations-2026-05-30
spec:
  backoffLimit: 0
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: migrate
          image: demozpay/migrations:<commit-sha>
          envFrom:
            - secretRef:
                name: payroll-migrator-db-url

The migrator DB role should own the payroll tables but NOT bypass RLS — the verify step inside the container is RLS-aware. Provide a separate app_user role for the API; the migrator role's credentials must never reach the API pods.

Production via embedded CLI (legacy path)

Same procedure as staging, with three production-only caveats:

1. Take a full pg_dump of the whole database — the per-table backup is a fast-recovery convenience; for production keep a complete snapshot too.
2. Enable the pollers on ONE api instance first.

   PAYROLL_DEDUCTIONS_POLLER_ENABLED=true \
   PAYROLL_AUTO_LOCK_ENABLED=true \
   OUTBOX_DATABASE_URL=postgresql://outbox_publisher:…@host/db \
     systemctl restart demozpay-api@1

   Watch payroll_deduction_event_state and payroll_run (status counts by tenant) for an hour before fanning out.
3. The auto-lock cron horizon defaults to 30 days. Override via PAYROLL_AUTO_LOCK_DAYS_AFTER_COMPLETED if your reconciliation window is shorter — but never set it below 7 days without sign-off from finance.

If verify-payroll-migrations.sh exits non-zero, roll back the deploy (see the rollback section) and open an incident.

---

What the verify script checks

scripts/verify-payroll-migrations.sh confirms the 8 invariants the codebase enforces in tests:

1. All required tables exist — every payroll-domain table the API queries at boot.
2. RLS is ENABLED + FORCED on every tenant-scoped table.
3. tenant_isolation policy exists on every tenant-scoped table.
4. Idempotency uniques present on (tenantId, employeeId, periodId, reference) for one-time earnings and on (tenantId, employeeId, periodId) for overtime entries.
5. Santim columns are NUMERIC(20,0) — float drift is forbidden (ADR-005).
6. Carry-forward enums present with the expected value sets (PENDING/APPLIED/EXPIRED × EWA_REPAYMENT/LOAN_REPAYMENT/OTHER).
7. Immutability triggers — payroll_run_immutability_update, payroll_run_immutability_delete, plus the two payroll_run_entry counterparts. These BEFORE-row triggers refuse out-of-band mutation on any payroll row whose status is post-approval (Payroll-P4-a).
8. Legacy lineage is gone — Payroll / PayrollEntry tables and PayrollStatus enum dropped in 20260530100000_drop_legacy_payroll. Two co-existing payroll models was a documented confusion risk.
9. Inverse-RLS invariant — payroll_deduction_event_state must NOT have RLS enabled. It's a cross-tenant drain table read by the P5-b poller under a BYPASSRLS role. If a future migration accidentally enables RLS, the poller silently breaks (claims zero rows). The verify script FAILS loudly in that case.
10. P4-b integrity constraints — payroll_adj_amount_non_zero, payroll_adj_description_present, payroll_adj_reversal_has_parent, payroll_adj_linetype_consistent. Defense-in-depth alongside the aggregate guards.

Each check prints [ OK ] or [FAIL]; the script exits 1 if any failure was logged.

---

Rollback

The P2 + P3 migrations are additive — they add new tables and columns but do not drop or rename existing ones. Reverting in the code keeps the application healthy against the post-migration schema (no errors, but the new aggregates' use cases are unreachable).

If a migration must be undone in the database:

-- Last-ditch hand-rollback. Run inside a transaction. Replace the
-- target migration name as needed.
BEGIN;

-- Example: roll back the carry-forward migration.
DROP TABLE IF EXISTS payroll_deduction_carry_forward;
DROP TYPE  IF EXISTS "PayrollDeductionCarryForwardStatus";
DROP TYPE  IF EXISTS "PayrollDeductionCarryForwardType";

-- Remove the migration record so Prisma stops reporting it as applied.
DELETE FROM _prisma_migrations
 WHERE migration_name = '20260529900000_payroll_deduction_carry_forward';

COMMIT;

Hand-rollback is a last resort. The preferred path is to ship a forward migration that walks the schema back, so the migrations history stays linear.

---

Common failure modes

Symptom	Cause	Fix
permission denied for table payroll_*	Deploy ran under a role that lacks BYPASSRLS AND no app.tenant_id is set in the session. Verify scripts run without a tenant set on purpose.	Run the verify script as a role that owns the tables but does not have BYPASSRLS. The verify is RLS-aware.
payroll_run %s is APPROVED; ...immutable raised on UPDATE	A data-fix script tried to mutate a financial column on an approved payroll. The Payroll-P4-a triggers block this on purpose.	Don't bypass the triggers. Land corrections through a future PayrollAdjustment aggregate (P4-b) — or, in a true emergency, drop the trigger inside a single transaction, fix the row with a written justification, and re-create the trigger. Log the event.
payroll_run %s is %s; DELETE refused	Cascade-delete or hand-DELETE on an approved payroll. The trigger refuses.	Approved payroll is append-only. Do not delete. If the underlying record is wrong, post a correction adjustment.
[FAIL] RLS not enabled+forced on payroll_*	The migration's ALTER TABLE ... FORCE ROW LEVEL SECURITY was skipped because the role lacked privilege.	Re-run the migration under the table owner role.
[FAIL] payroll_*.* precision=10 scale=2	Someone created the column as DECIMAL(10,2) directly via psql before the Prisma migration ran.	Drop + recreate the column from the canonical migration.
API boots but payrollDeductionCarryForward queries fail	prisma generate was not re-run before deploy — the client doesn't know the new table.	Re-run pnpm prisma:generate and redeploy the API image.

---

Post-deploy

1. Watch the API for 10 minutes — /metrics should report no spike in prisma_query_total{model="payrollRun"} status="error".
2. Make sure the next scheduled payroll run computes without error. This validates the new tables are reachable end-to-end.
3. Close the change ticket and link the verify script output.

---

Provenance

• Migrations: see apps/api/prisma/migrations/2026052*.
• Engineering rules: docs/adr/ADR-005-money-santim.md, docs/adr/ADR-006-ledger-truth.md, docs/adr/ADR-013-tenant-isolation.md.
• Per-migration RLS guards: each migration ends with a DO $$ ... $$ block that raises an exception if RLS state drifts.