Migrations

Prisma-managed migrations live in apps/api/prisma/migrations/. Every migration is a versioned SQL file. Prod uses prisma db push on container boot (idempotent against the current schema).

Dev workflow

# 1. Edit schema.prisma
# 2. Generate migration + client
cd apps/api
bunx prisma migrate dev --name <short-name>

# This creates apps/api/prisma/migrations/<timestamp>_<name>/migration.sql
# and regenerates the Prisma client.

Prod workflow

Dokploy runs start.sh in the API container on boot:

#!/bin/sh
# apps/api/start.sh (abbreviated)
bunx prisma db push
psql $DATABASE_URL -f /app/docker/init.sql
bun run src/index.ts

• prisma db push — applies schema changes without creating a migration file. Safe for additive changes. Not safe for enum changes that require data migration — handle those manually (see gotcha below).
• init.sql re-runs on every boot (CREATE ... IF NOT EXISTS everywhere). Keeps pg_proc functions and policies in sync even after container restarts.

Gotcha: Postgres enum changes in a single TX

Postgres rejects adding a new enum value and using it in the same transaction:

ERROR: unsafe use of new value of enum type

Split destructive enum migrations into two SQL files:

migration_part1_enums.sql — add values, commit first:

ALTER TYPE "PlatformRole" ADD VALUE IF NOT EXISTS 'superadmin';
ALTER TYPE "PlatformRole" ADD VALUE IF NOT EXISTS 'engineer';
-- let this commit fully

migration_part2_schema.sql — use new values in a separate run:

BEGIN;
ALTER TABLE users ALTER COLUMN platform_role SET DEFAULT 'none';
-- ... CREATE tables using new enum values ...
COMMIT;

Apply them manually to prod DB before deploying the schema:

ssh jaeger "docker exec -i lumen-postgres psql -U lumen -d lumen < /tmp/part1.sql"
ssh jaeger "docker exec -i lumen-postgres psql -U lumen -d lumen < /tmp/part2.sql"

Then merge the Prisma schema change to master; prisma db push in the API's boot script is a no-op because the schema already matches.

Post-migrate constraints

Partial unique indexes and CHECK constraints that Prisma doesn't model live in apps/api/prisma/migrations/_constraints.sql. Re-applied on every boot via init.sql:

-- Singleton: only one superadmin
CREATE UNIQUE INDEX IF NOT EXISTS single_superadmin
  ON users (platform_role)
  WHERE platform_role = 'superadmin';

-- Singleton: only one CEO
CREATE UNIQUE INDEX IF NOT EXISTS single_ceo
  ON users (org_position)
  WHERE org_position = 'ceo';

-- Polymorphic grant: exactly one target per row
ALTER TABLE project_grants
  ADD CONSTRAINT grant_one_target
  CHECK (
    (user_id IS NOT NULL)::int +
    (group_id IS NOT NULL)::int +
    (department_id IS NOT NULL)::int = 1
  );

Rollback

# If a migration went wrong and DB is in a weird state
ssh jaeger "docker exec lumen-postgres pg_dump -U lumen -d lumen > /tmp/bad-state.sql"
gunzip -c /tmp/lumen-backup-BEFORE.sql.gz | \
  docker exec -i lumen-postgres psql -U lumen -d lumen

Then revert the offending code commit:

git revert <sha>
git push origin master

Dokploy rebuilds with the old schema, prisma db push becomes a no-op, you're back to baseline.

Never do

• ❌ prisma migrate deploy in prod without reviewing the SQL first
• ❌ prisma db push --force-reset — nukes data
• ❌ Schema edit + deploy without taking a backup for anything involving enum changes
• ❌ Silently drop columns that production data might still reference