@danielcok17/prisma-db

Shared Prisma setup with dual Prisma clients: one for your app tables (app schema), one for existing law tables (public schema).

Structure

• prisma/app.prisma - your models, migrate here (uses schema app)
• prisma/law.prisma - existing public.law_* and public.version_paragraphs tables (introspection only)
• src/utils.ts - exports appPrisma and lawPrisma

Scripts

• npm run db:generate - generate both Prisma clients
• npm run db:migrate - run migrations for app.prisma only
• npm run db:migrate:prod - deploy migrations (app)
• npm run db:pull:law - introspect existing public law tables
• npm run db:studio - open Prisma Studio for app

Import

import { appPrisma, lawPrisma } from '@danielcok17/prisma-db';
// or direct clients if you need advanced typing control
import { AppPrismaClient, LawPrismaClient } from '@danielcok17/prisma-db';

Environment

Set the following env vars in your runtime/build (names match the .prisma files):

POSTGRES_PRISMA_URL=postgres://USER:PASS@HOST:PORT/DB?schema=app
POSTGRES_URL_NON_POOLING=postgres://USER:PASS@HOST:PORT/DB?schema=app
POSTGRES_PRISMA_URL_LAW=postgres://READONLY_USER:PASS@HOST:PORT/DB
POSTGRES_URL_NON_POOLING_LAW=postgres://READONLY_USER:PASS@HOST:PORT/DB

Notes:

• Use a read-only role for POSTGRES_PRISMA_URL_LAW.
• Ensure the DB has CREATE SCHEMA IF NOT EXISTS app; executed once.

Schema paths (for tooling / CI)

This package publishes the prisma directory. You can get absolute paths to the schemas programmatically:

import { appPrismaSchemaPath, lawPrismaSchemaPath, prismaMigrationsPath } from '@danielcok17/prisma-db';

Examples:

# Use the packaged app schema with Prisma CLI
npx prisma migrate deploy --schema "$(node -e "console.log(require('@danielcok17/prisma-db').appPrismaSchemaPath)")"

# Or resolve directly to the file shipped in the package
APP_SCHEMA=$(node -e "console.log(require.resolve('@danielcok17/prisma-db/prisma/app.prisma'))")
npx prisma generate --schema "$APP_SCHEMA"

Installation

npm install --save-exact @danielcok17/prisma-db@<x.y.z>
# or
yarn add @danielcok17/prisma-db

The package runs Prisma generate on postinstall. If your environment blocks lifecycle scripts, run generate manually using appPrismaSchemaPath as shown below.

Quick start

1. Set env vars (match the names used in .prisma files):

POSTGRES_PRISMA_URL=postgres://USER:PASS@HOST:PORT/DB?schema=app
POSTGRES_URL_NON_POOLING=postgres://USER:PASS@HOST:PORT/DB?schema=app
POSTGRES_PRISMA_URL_LAW=postgres://READONLY_USER:PASS@HOST:PORT/DB
POSTGRES_URL_NON_POOLING_LAW=postgres://READONLY_USER:PASS@HOST:PORT/DB

2. Apply migrations for the app schema:

APP_SCHEMA=$(node -e "console.log(require('@danielcok17/prisma-db').appPrismaSchemaPath)")
npx prisma migrate deploy --schema "$APP_SCHEMA"

3. Use the clients:

import { appPrisma, lawPrisma } from '@danielcok17/prisma-db';

const users = await appPrisma.user.findMany();
const laws = await lawPrisma.lawVersion.findMany({ take: 5 });

Who migrates which database (IP-011 Q5)

This repository owns the app schema and its migration history. Only this repository applies migrations to DEV and PROD, through .github/workflows/migrate.yml. chatbot-ui and rag-backend never run prisma migrate deploy on deploy; their Compose service db_schema_check runs prisma migrate status and stops the application from starting when the database lacks a migration the image ships.

Rules:

• Consumers pin one exact version ("@danielcok17/prisma-db": "1.20.37", no ^), the same in both repositories. Never latest.
• Never edit a migration that has been applied anywhere. Fix forward with a new one.
• No DDL in the applications (no runtime CREATE TABLE, no bootstrap scripts).
• law.prisma is introspection only (npm run db:pull:law); it is never migrated.
• Review every generated SQL file. prisma migrate dev currently also emits DROP TABLE "BackgroundJob" and DROP TABLE "legal_opinion" (tables created by migrations without a model in app.prisma); never commit those drops.

Expand-contract

Every release must work with the previous application images, because the database is migrated before the applications are deployed and a rollback redeploys an older image on the newer schema.

1. Expand (any release): new tables, new nullable columns or columns with a default, new indexes. Backfills go in a separate step or release.
2. Contract (a later release, after every deployed consumer stopped using the old shape): drop or rename a column or table, narrow a type, add NOT NULL without a default, add a unique constraint on existing data.

A rename is always expand (add new) -> deploy code that writes both and reads new -> contract (drop old) in a later release.

Deploy order

If step 2 is skipped, db_schema_check fails and the new application container does not start (on PROD chatbot-ui stops the old container first, so this means downtime). Always migrate before the release that bumps the version.

Validation before publishing

publish-npm.yml runs on every PR into main and before every publish, on a clean PostgreSQL 15 (PROD) and 18 (DEV) service container:

• prisma validate for app.prisma and law.prisma,
• prisma migrate deploy of the full history, then prisma migrate status,
• scripts/check-migration-drift.sh: the replayed database must equal app.prisma (only the documented known drift is allowed), and every database function in prisma/functions.lock must match its md5(pg_get_functiondef) (functions are versioned by name, e.g. _v1, and never changed after a publish),
• npm run build and scripts/check-pack.mjs: the tarball contains both schemas, every migration.sql, migration_lock.toml and dist.

The version bump happens only after validation, so a failing check publishes nothing.

Running migrate.yml

# DEV (normally automatic after a publish)
gh workflow run migrate.yml --ref main -f version=1.20.37 -f environment=development
# PROD (manual only, from main, before the BE/FE release)
gh workflow run migrate.yml --ref main -f version=1.20.37 -f environment=production

The job validates the exact version (x.y.z, must be published), takes the exact Prisma CLI version from package-lock.json, and sends deploy/migrate-on-host.sh over SSH. On the host the script takes the shared deploy lock (waits up to 1200 s, since a deploy holds it through the drain of the old version, IP-012), then runs a throwaway node container (image pinned by digest) on the backend network that downloads the package with npm pack, installs the pinned Prisma CLI, runs prisma migrate deploy and prisma migrate status. Database URLs travel on stdin into a 0600 env file and never appear in a command line or log. Runs are serialized per environment.

Required GitHub Environments in this repository (names only, values from the same Environment of rag-backend; the database URLs must be identical to the ones the applications use, including any ?schema= parameter):

The DEV entry point accepts migrate db-dev <x.y.z> only from the version of deploy/ci/ssh-entrypoint.sh that ships with the IP-011 Q5 PRs in chatbot-ui and rag-backend; install it on smartlex before the first DEV run (see rag-backend deploy/ci/README.md, section "One-time SSH entry point installation").

A failed migration

prisma migrate deploy stops at the first failing migration and records it as failed; db_schema_check then keeps new application containers from starting, the running ones keep running. Never edit the failed migration in place once it ran anywhere. Fix the cause, then on the host mark it rolled back (prisma migrate resolve --rolled-back <name> in the same throwaway container) or ship a corrected follow-up migration, and run migrate.yml again.

Tables adopted into the history

20261015120000_adopt_security_rate_limit_and_document_draft_session adopts SecurityRateLimit (chatbot-ui scripts/bootstrap-rate-limit-table.mjs) and DocumentDraftSession (rag-backend migrateDocumentDraftSession). Both already exist on DEV and PROD; the migration uses CREATE ... IF NOT EXISTS and then checks the column types, nullability and primary keys, so it is a no-op on an existing, matching table, creates the tables on a clean database, and fails loudly on a table with a different shape.

Local development

Local development databases only. Shared DEV and PROD are migrated by migrate.yml (see above), never from a consumer.

npm install --save-exact @danielcok17/prisma-db@<x.y.z>
APP_SCHEMA=$(node -e "console.log(require('@danielcok17/prisma-db').appPrismaSchemaPath)")
npx prisma generate --schema "$APP_SCHEMA"
npx prisma migrate deploy --schema "$APP_SCHEMA"   # local database only
npx prisma migrate status --schema "$APP_SCHEMA"   # what the consumers' db_schema_check runs

Do not run prisma migrate dev in consumer apps. Schema changes are made in this repository and released as versions.

Prisma Studio (optional)

APP_SCHEMA=$(node -e "console.log(require('@danielcok17/prisma-db').appPrismaSchemaPath)")
npx prisma studio --schema "$APP_SCHEMA"

Troubleshooting

• Missing client types/runtime: run npx prisma generate --schema "$(node -e "console.log(require('@danielcok17/prisma-db').appPrismaSchemaPath)")".
• Migrate deploy fails: verify env vars and DB connectivity; ensure the app schema exists.
• Read-only access to law tables: use a user/role with SELECT-only permissions for POSTGRES_PRISMA_URL_LAW.