Prisma 5 → 6 Upgrade — Design Spec¶

Date: 2026-04-21 Status: Draft — pending review Scope: Monorepo-wide coordinated upgrade of prisma and @prisma/client from ^5.20.x–^5.22.x to ^6.5.0 (or current latest 6.x). Prisma 6 → 7 is a separate follow-up spec.

1. Scope, Goals, Non-Goals¶

Goal¶

Move the entire SA Platform monorepo from Prisma 5.x to the latest Prisma 6.x in a single coordinated PR. No functional change; the bump exists to stay current, pick up 6.x performance improvements, and reduce future migration debt.

In scope¶

Bump prisma and @prisma/client pins to ^6.5.0 in all 7 package.jsons:
5 services: auth, clinical-api, consent, notifications, user-management
2 shared packages: @sa-platform/common, @sa-platform/auth-client
Regenerate all 5 Prisma clients (each service has its own schema + custom or default generator output path).
Update pnpm-lock.yaml.
Add a minimal CREATE, DROP ON *.* grant in scripts/init-databases.sql so prisma migrate dev works out of the box on fresh MySQL containers.
Verify: monorepo typecheck, full unit-test suite, full integration-test suite — all green.
Smoke-test prisma migrate dev locally for each service after the bump.

Out of scope (explicitly deferred)¶

Move to the new prisma-client generator (introduced in 6, becomes default in 7). Defer to the 6 → 7 PR.
Adopt TypedSQL, Client Extensions, omit config, or any other 6.x features.
Any opportunistic schema changes, refactors, or cleanup unrelated to the upgrade.
Prisma 7.x upgrade itself.

Success criteria¶

All CI jobs green on the upgrade PR.
One smoke migration on any service succeeds end-to-end under 6.x with the new init-SQL grant in place.
No production behaviour change — the full test suite (unit + integration) encodes current behaviour and must remain passing.

2. Audit: What We Verified Is NOT Affected¶

A codebase-wide audit was run before writing this spec. The following Prisma 6 breaking-change vectors do not touch us:

Breaking change in 6.x	Our exposure
`Buffer` → `Uint8Array` for `Bytes` field values	None. No `Bytes` columns in any of the 5 schemas.
`$use` middleware — soft deprecation in 6, removal in 7	None. No `$use` calls anywhere in the repo. The project deliberately avoids middleware.
`rejectOnNotFound` option removal	None. Not used anywhere.
Node.js minimum bumped to 18.18	None. Monorepo runs on Node 20+.
Query-compiler default (only applies to the new `prisma-client` generator)	None. We stay on `prisma-client-js` for this PR.
`findUniqueOrThrow` / `findFirstOrThrow` error-class changes	Low. Used in ~10 places; throw-on-not-found behaviour is preserved; error constructor shape is compatible.
`P2025` / `P2002` / `P2003` error-code strings	None. These codes are stable across 5 → 6.
Removal of deprecated `distinct` with aggregations	None. Not used.

What we do expect to encounter¶

Type regeneration. Every @prisma/client import produces refreshed .d.ts files. Any place that uses internal Prisma helper types (e.g. Prisma.NotificationCreateInput) may show minor type drift — likely innocuous.
Enum imports. Our schemas export Channel, Category, NotificationStatus, RecipientType, TemplateSource. Import paths stay the same for prisma-client-js; no action required.
Integration-test runtime. Prisma 6's migration engine is re-engineered; migrate deploy is faster but emits slightly different log formatting. Not a behaviour change, just noise to ignore.
Lockfile churn. pnpm-lock.yaml will see a substantial diff as Prisma transitive deps update. Expected.

3. The Actual Changes¶

This is the complete list of files the PR will touch.

Package manifests (7 files)¶

File	Change
`services/auth/package.json`	`prisma` + `@prisma/client`: `^5.20.0` → `^6.5.0`
`services/clinical-api/package.json`	`prisma` + `@prisma/client`: `^5.20.0` → `^6.5.0`
`services/consent/package.json`	`prisma` + `@prisma/client`: `^5.20.0` → `^6.5.0`
`services/notifications/package.json`	`prisma` + `@prisma/client`: `^5.22.0` → `^6.5.0`
`services/user-management/package.json`	`prisma` + `@prisma/client`: `^5.20.0` → `^6.5.0`
`packages/common/package.json`	`prisma` + `@prisma/client`: `^5.20.0` → `^6.5.0`
`packages/auth-client/package.json`	`prisma` + `@prisma/client`: `^5.20.0` → `^6.5.0`

The exact 6.x minor is resolved at install time; we use caret pinning to stay consistent with existing convention. If a specific 6.x bug is material, we pin narrower at that point.

Lockfile¶

pnpm-lock.yaml — regenerated via pnpm install.

Regenerated Prisma clients (not checked into git; CI and local installs re-emit)¶

node_modules/.prisma/auth-client/
node_modules/.prisma/consent-client/
node_modules/.prisma/user-management-client/
node_modules/.prisma/notifications-client/
node_modules/@prisma/client/ — clinical-api uses the default path

Shadow-DB grant fix¶

scripts/init-databases.sql — add one block after the existing per-database GRANT ALL PRIVILEGES statements:

-- Needed by `prisma migrate dev` to create/drop a transient shadow database.
-- CI uses `prisma migrate deploy` which does not require this.
GRANT CREATE, DROP ON *.* TO 'sa_platform'@'%';
FLUSH PRIVILEGES;

This is strictly narrower than the one-off ALL PRIVILEGES ON *.* that was applied manually to the running container during the notifications service upgrade (2026-04-21). With this grant baked into the init script, every fresh MySQL container works with migrate dev out of the box.

Schemas — no changes expected¶

services/*/prisma/schema.prisma files use only portable syntax. We run prisma validate per service to confirm each schema parses cleanly under the 6.x CLI; no edits anticipated.

Migrations — no changes expected¶

The existing migration SQL applies under 6.x's migration engine without modification. Prisma guarantees forward compatibility of the migration history across minor and major versions within prisma migrate.

Code — no changes expected¶

The audit showed zero breaking-API usage. If regeneration surfaces unexpected type regressions, we fix them case-by-case within this PR but do NOT add opportunistic cleanup.

CI workflow — no changes expected¶

.github/workflows/ci.yml invokes the CLI via pnpm --filter ... prisma:generate and pnpm --filter ... test:integration, which pick up the workspace-pinned 6.x automatically. One risk item (Section 4) flags that CI's MySQL initialisation path must actually re-run scripts/init-databases.sql for the new grant to take effect — to be verified during implementation.

4. Verification Plan & Risks¶

Local verification sequence¶

All steps must pass before pushing.

From repo root: pnpm install — clean.
pnpm --filter "./services/*" prisma:generate — all 5 clients regenerate without error.
pnpm turbo run typecheck — all 9 workspace packages clean.
pnpm turbo run test — full unit-test suite green across all services.
Bring up Docker infra: docker compose down -v && docker compose up -d mysql redis. Confirm scripts/init-databases.sql applies the new grant by running SHOW GRANTS FOR 'sa_platform'@'%' against the fresh container.
For each of the 5 services in turn, run DATABASE_URL=... npx prisma migrate dev --create-only --name smoke_upgrade_test to generate a diff, then prisma migrate dev to apply. Expected: each service reports no diff (schemas didn't change) but exercises the 6.x migration engine against the existing migration history.
pnpm --filter @sa-platform/notifications test:integration and pnpm --filter @sa-platform/consent test:integration — at least two services' integration suites green against the freshly-migrated 6.x state. Remaining integration suites run via CI.
Smoke-run the notifications service locally: pnpm --filter @sa-platform/notifications start:dev, hit GET /health/ready, expect 200.

CI verification¶

Lint / typecheck / unit-tests job across all packages — green.
Integration-tests job for each service — green.

Risks & mitigations¶

Risk	Likelihood	Mitigation
Type regeneration surfaces breaking changes in generated types	Low	Audit is clean; if it happens, fix inline. If non-trivial, split those fixes out of this PR.
6.x migration engine rejects an existing 5.x migration	Very low	Prisma guarantees migration-history forward compatibility within `prisma migrate`. Smoke step 6 confirms per-service.
Integration tests become flaky due to subtle Prisma behaviour drift	Low	Existing integration tests exercise realistic paths (transactions, enum filters, unique violations). If anything drifts, it'll surface here before merge.
Lockfile conflict with concurrent PRs on main	Medium	Rebase-before-merge. This PR touches only version pins + lockfile, so conflicts should be resolvable.
Fresh MySQL container in CI doesn't re-run `init-databases.sql`	Medium	CI uses `docker run` with MySQL init. Verify the init script is actually applied on fresh start. If CI uses a different init flow, patch the CI step in this PR.
`CREATE, DROP ON .` grant is too broad for security-conscious reviewers	Low	Strictly narrower than the one-off `ALL PRIVILEGES ON .` applied manually during the notifications upgrade. Documented inline with a why-comment.

Rollback plan¶

If any CI job fails unrecoverably, revert the single PR. The version bump is mechanical; no data-layer or schema changes are being persisted, so there is no migration state to unwind.

5. Out of scope — followups captured for the 6 → 7 spec¶

Migrate all 5 services from prisma-client-js to the new prisma-client TypeScript-native generator.
Consider adopting omit config for PHI-sensitive field exclusion (aligns with the notifications service's deferred PHI-at-rest work).
Re-evaluate shadow-DB approach — a dedicated prisma_shadow database via SHADOW_DATABASE_URL becomes more attractive if we add more services.
$use middleware is removed in 7 (we don't use it, so no work required — just confirmation during the 6 → 7 upgrade).