Database Migrations

Read this before touching the database.

This database is Neon Postgres, and all app tables live in a non-default schema called web (see datasource db { schemas = ["web"] } in schema.prisma, with previewFeatures = ["multiSchema"]). Prisma's own _prisma_migrations table also lives in web. The default search_path does not include web, which breaks every Prisma command that assumes the public schema.

There is a single DATABASE_URL in backend/.env, pointing at the shared Neon neondb database. There is no separate dev branch — a migration applied here hits the database everyone uses. Treat every migrate deploy as production.

What does not work

• npx prisma migrate dev — fails.

• npx prisma migrate dev --create-only — also fails, despite claiming not to touch the database. It spins up a shadow database to diff the schema, and the shadow DB has no web schema, so you get:

  Error: P3006
  Migration `…` failed to apply cleanly to the shadow database.
  ERROR: schema "web" does not exist

Do not retry these hoping for a different result. The shadow database is the blocker.

The working workflow

1. Edit the schema

Edit prisma/schema.prisma as usual. Every model needs @@schema("web").

2. Hand-write the migration SQL

Prisma cannot generate it, so create the file manually:

prisma/migrations/<YYYYMMDDHHMMSS>_<name>/migration.sql

• The timestamp must be lexically greater than the latest existing migration folder (they sort as strings). Use the current UTC time in YYYYMMDDHHMMSS format.
• Write schema-qualified SQL, mirroring the newest real migration (20260615130000_add_email_verification or 20260616120000_add_user_onboarding):
  • tables: "web"."my_table"
  • user foreign key: user_id is UUID, REFERENCES "web"."users"("id") ON UPDATE CASCADE ON DELETE CASCADE

warning

Do not copy pre-UUID migrations such as 20260514120000_add_user_profile_settings — they use unqualified names and TEXT ids from before the UUID conversion.

Example (user_onboarding):

-- CreateTable
CREATE TABLE "web"."user_onboarding" (
    "user_id" UUID NOT NULL,
    "role" VARCHAR(50),
    "usage" VARCHAR(50),
    "goal" VARCHAR(50),
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT "user_onboarding_pkey" PRIMARY KEY ("user_id")
);

-- AddForeignKey
ALTER TABLE "web"."user_onboarding" ADD CONSTRAINT "user_onboarding_user_id_fkey"
  FOREIGN KEY ("user_id") REFERENCES "web"."users"("id") ON UPDATE CASCADE ON DELETE CASCADE;

note

Prisma's own generator emits @updatedAt columns as NOT NULL with no default. We deliberately add DEFAULT CURRENT_TIMESTAMP to protect against raw inserts. The Prisma client always sets updatedAt itself, so this is harmless.

3. Apply with migrate deploy and a search_path-augmented URL

The key trick: append &options=-csearch_path%3Dweb to DATABASE_URL for the command. That is the URL encoding of the libpq option -c search_path=web.

cd backend
BASE='postgresql://USER:PASS@HOST/neondb?sslmode=require&channel_binding=require'
DATABASE_URL="$BASE&options=-csearch_path%3Dweb" npx prisma migrate deploy

Use the real DATABASE_URL from backend/.env as the base.

warning

Neon pooled vs unpooled. If DATABASE_URL points at the pooled endpoint (the host contains -pooler), migrate deploy fails with ERROR: unsupported startup parameter in options: search_path — the pooler rejects the search_path startup option. Run the migration against the unpooled (direct) endpoint by stripping -pooler from the host:

BASE_DIRECT="${BASE/-pooler/}"
DATABASE_URL="$BASE_DIRECT&options=-csearch_path%3Dweb" npx prisma migrate deploy

The running application keeps using the pooled URL — only the migration needs the direct one.

4. Regenerate the client

npx prisma generate

5. Restart the backend

npm run dev does not hot-reload (it is plain node src/index.js, with no nodemon or --watch), so a running server keeps the old code and the old .env it read at boot. After schema, .env, or code changes you must kill and restart the process, or the changes will appear "not to work".

Verifying

Use the same URL prefix:

DATABASE_URL="$BASE&options=-csearch_path%3Dweb" npx prisma migrate status

It should report Database schema is up to date!. To inspect a table:

DATABASE_URL="$BASE&options=-csearch_path%3Dweb" node -e "
  import('@prisma/client').then(async ({PrismaClient}) => {
    const p = new PrismaClient();
    console.table(await p.\$queryRawUnsafe(\"SELECT column_name,data_type,is_nullable,column_default FROM information_schema.columns WHERE table_schema='web' AND table_name='YOUR_TABLE' ORDER BY ordinal_position\"));
    await p.\$disconnect();
  });
"

Any @prisma/client script that needs to see web tables must run with the same options=-csearch_path%3Dweb URL prefix.

Raw SQL must be schema-qualified

The runtime DATABASE_URL carries no search_path=web, so prisma.$queryRaw / $executeRaw with unqualified table names (for example "password_reset_codes") fail with relation "..." does not exist. Normal Prisma model calls are fine — they are schema-aware through @@schema("web").

Prefer Prisma model methods over raw SQL. If you must use raw SQL, schema-qualify every table: "web"."password_reset_codes".

This was the root cause of password reset being broken; it was fixed on 2026-06-17 by switching the forgot/reset controllers to prisma.passwordResetCode.*.

Summary

1. Never run migrate dev or --create-only — the shadow DB has no web schema.
2. Hand-write migration.sql, schema-qualified, with UUID foreign keys to "web"."users".
3. Apply with DATABASE_URL="…&options=-csearch_path%3Dweb" npx prisma migrate deploy.
4. Run npx prisma generate, then restart the non-watching backend.
5. One shared Neon DB, no dev branch — every deploy is production.