Migrations & Seeding

Migration workflow

cd packages/database

# 1. edit prisma/schema.prisma
# 2. create and apply the migration
pnpm db:migrate                    # prisma migrate dev — prompts for a name

# 3. rebuild so consuming services see the new types
cd ../.. && pnpm build --filter=@playpals/db

prisma migrate dev does three things: diffs the schema against the database, writes SQL into prisma/migrations/<timestamp>_<name>/migration.sql, applies it, and regenerates the client. Commit the generated migration directory.

The commands

Command	Runs	When
pnpm db:generate	prisma generate	After pulling a schema change without a new migration
pnpm db:migrate	prisma migrate dev	Local development — creates and applies
pnpm db:deploy	prisma migrate deploy	Production — applies existing migrations, never creates
pnpm db:push	prisma db push	Throwaway local experiments only — no migration file
pnpm db:seed	prisma/seed.ts	Populate demo data

Never run db:push against a shared database

It mutates the schema without recording a migration, so the migration history no longer describes reality and the next migrate deploy fails or silently diverges. It is fine on your own laptop for a quick experiment you intend to throw away.

Migration rules

Never edit an applied migration. Once a migration has run anywhere other than your laptop it is immutable — Prisma records a checksum, and changing the file makes migrate deploy fail. Fix mistakes with a new migration.

Plan destructive changes in two steps. Renaming a column or dropping a table in a single migration means downtime, because the old code is still running while the new schema is live. Use expand/contract:

1. Expand — add the new column, deploy code that writes both and reads the new one.
2. Contract — a later migration drops the old column, once nothing reads it.

Keep a down.sql for hand-written invariants. When a migration adds triggers or CHECK constraints by hand (as 20261021120000_coin_ledger does), put the SQL that reverses it in a down.sql beside migration.sql. Prisma never runs it; it is for an operator rolling back by hand.

Watch for locks. Adding a column with a non-null default rewrites the table on older Postgres. Adding an index locks writes unless you use CREATE INDEX CONCURRENTLY, which Prisma will not generate — write it by hand in the migration SQL when the table is large.

Rollback-safe migrations

A code rollback is one command; a schema rollback is a restore. Every migration must therefore keep the previous release working, so the code can be rolled back while the schema stays. The rules for PRs are in Developing & Committing → Migrations. Here is how the common changes look.

Add a column: nullable, or with a default. Safe in one release.

ALTER TABLE "Post" ADD COLUMN "pinnedAt" TIMESTAMP(3);

Rename a column (caption → body): four steps over at least two releases.

Release	Migration	Code
1 (expand)	ADD COLUMN "body" TEXT; then UPDATE "Post" SET "body" = "caption";	writes both columns, reads body with a fallback to caption
2	—	reads and writes body only
3 (contract, contract-migration label)	ALTER TABLE "Post" DROP COLUMN "caption";	unchanged

Rolling back release 2 lands on release 1, which still writes both columns. Rolling back release 3 doesn't need the column. No step is unsafe.

Make a column required: add it nullable, backfill, deploy code that always writes it, then a later SET NOT NULL.

Drop a table or column: first ship the code that stops using it, wait at least one release, then drop it in a PR labelled contract-migration. Take a backup before deploying a contract migration. It's the one kind that can't be undone by rolling back.

Big tables: use CREATE INDEX CONCURRENTLY (by hand, in the migration SQL), and batch backfills instead of one large UPDATE.

Applying migrations in production

The API doesn't migrate when it starts. Migrations run once, before the API rollout, in a Helm pre-upgrade hook Job on the playpalz-api release, with the image about to be deployed:

./scripts/build-api.sh "$TAG"
infra/helm/deploy.sh api "$TAG"     # pre-upgrade hook Job playpalz-api-migrate runs first
kubectl logs job/playpalz-api-migrate

The whole deploy procedure is in Building & Deploying.

Until October 2026, apps/api/docker-entrypoint.sh ran migrate deploy in every replica on every start, with a db:push fallback. That meant every API deploy also migrated, and a failed migration crash-looped every pod. The hook separates the two: a failed migration stops the deploy before any pod changes, and helm rollback never runs it.

Order matters. Apply an additive migration before deploying the code that needs it. Apply a destructive migration only after the code that stopped using the old shape is fully rolled out.

Check the latest backup first, and take a dump before a contract migration. See Backups.

When a migration fails

migrate deploy runs each migration in a transaction where Postgres allows it. A failure leaves the migration marked as failed in _prisma_migrations, and later runs refuse to continue until it's resolved.

# See what state it's in (from a pod in the cluster, or with the Job's image)
pnpm exec prisma migrate status

# After fixing the database by hand, or confirming the failed migration changed nothing:
pnpm exec prisma migrate resolve --rolled-back <migration_name>   # then ship a corrected migration
pnpm exec prisma migrate resolve --applied <migration_name>       # if you completed it by hand

Never edit the failed migration's file once it has run in production. Ship a new one.

Schema permissions on a new database

PostgreSQL 15 revoked CREATE on the public schema from the PUBLIC role. DigitalOcean creates application users with the normal role, so a freshly provisioned database gives playpalz_app enough access to connect but not to create tables. migrate deploy fails on the first run with:

Error: ERROR: permission denied for schema public

This shows up as the migrate Job failing on a freshly rebuilt database. The connection string is correct and the database is reachable, so it reads like a credentials problem when it isn't.

The database is owned by doadmin, so the grant has to be issued as that user. This is codified in infra/terraform/database-grants.tf and runs automatically on terraform apply:

GRANT ALL PRIVILEGES ON DATABASE "playpalz-prod" TO playpalz_app;
GRANT ALL ON SCHEMA public TO playpalz_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO playpalz_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO playpalz_app;

Why it is a null_resource and not the postgresql provider

digitalocean_database_firewall admits only the Kubernetes cluster and the LiveKit droplet, so Terraform cannot reach the database from an operator's laptop. Rather than open the database to a changing home or office IP, the SQL runs as a short-lived Job inside the cluster, which is already on the allow-list. The admin connection string is passed through a temporary Secret so it never appears in a pod spec, and both the Job and the Secret are removed afterwards.

The statements are idempotent. To re-run them by hand:

cd infra/terraform
terraform apply -target=null_resource.database_grants

To confirm the grant is in place:

SELECT has_schema_privilege('playpalz_app', 'public', 'CREATE');   -- expect t

Checking state

cd packages/database
pnpm exec prisma migrate status     # applied vs pending
pnpm exec prisma validate           # schema is valid
pnpm exec prisma format             # canonical formatting

Prisma configuration

packages/database/prisma.config.ts supplies the datasource URL and the seed command:

export default defineConfig({
  datasource: { url: env("DATABASE_URL") },
  migrations: {
    seed: "ts-node --project ./prisma/tsconfig.seed.json ./prisma/seed.ts",
  },
});

schema.prisma itself declares only provider = "postgresql" — no url. The connection comes from this config for CLI operations, and from the driver adapter in src/client.ts at runtime.

Seeding

cd packages/database
pnpm db:seed

The seed produces a full demo environment rather than a handful of rows — users of each persona, posts of every type, channels, DMs, sessions, subscriptions, shop items, wallets and ledger entries, notifications, trending, and sponsors. Enough that every screen in the app has something to render.

What it does with media

Avatars and post images are fetched from pravatar and picsum, processed with Sharp, and uploaded to Spaces. Clip videos from assets/ are uploaded to Mux. The upload helpers fall back to source URLs when credentials are missing, so seeding works without Spaces or Mux access — you get remote URLs instead of your own CDN.

Safety properties

• Additive. Seeded accounts live in a dedicated @playpalz.gg email space so they cannot collide with existing rows.
• Idempotent. Re-running aborts early if the seed data is already present.
• Games are not seeded. It queries the existing Game rows (populated by igdb-heartbeat) and links to them.

Seeded credentials

Every seeded account uses the password Password123!. Local demo data only — never reuse it anywhere real.

Related files

File	Purpose
prisma/seed.ts	The active seed
prisma/seed/env.ts	Env loading for the seed
prisma/seed/spaces.ts	Image processing and upload
prisma/seed/mux.ts	Video upload
prisma/seedStore.ts	Store products
prisma/seedOLD.ts	Superseded — do not use

Resetting locally

docker compose down -v            # deletes the volume and all local data
docker compose up -d db redis
cd packages/database
pnpm db:migrate
pnpm db:seed

Prisma also offers pnpm exec prisma migrate reset, which drops, re-migrates, and re-seeds in one step. Both are local-only operations.