Most teams should use Prisma (or Drizzle). Neither of ours could. Both products have multi-tenant authz pinned to Row-Level Security, a second service writing to the same Postgres, and schemas heavy with triggers, RPCs, and partial indexes that an ORM’s DSL can’t model — and both times the LLM’s first scaffold was Prisma anyway. Both went SQL-first instead: timestamped migration files, a safe-push wrapper that blocks destructive SQL from reaching prod by accident, generated TypeScript types from live schema introspection, and a VERSION file at the repo root. This article is the decision tree for picking the right side, the prompt that does the setup, the four gotchas that bit us, and the criteria for overriding the LLM’s default.
The Decision Every Team Hits Around the Authz Layer
Pick an ORM, the discourse says. Use Prisma. Use Drizzle. Use Kysely if you want the types but not the runtime. The trade-offs people argue about are syntax (chained vs SQL-shaped), bundle size, edge-runtime compatibility, and Active Record vs Data Mapper.
None of those are the decision that matters.
The decision that matters is one level deeper: where does your authorization live?
If it lives in your app code — middleware checks the JWT, application layer enforces “user X can only see account Y’s data”, queries are written assuming the caller already authorized them — then any ORM is fine. Prisma is the right default. You can stop reading.
If it lives in the database — Postgres Row-Level Security policies decide which rows the current session can see and mutate, and your app code makes the typed call and trusts the database to filter — then the picture changes. Prisma and RLS are compatible at the database level: RLS is a Postgres feature, any client can use it. But Prisma is not RLS-native. The policies only fire if the connection runs as a non-bypass role (not postgres, not anything with BYPASSRLS) AND carries per-request session context (request.jwt.claims, app.user_id, whatever your policies read). Without careful transaction-scoped context via prisma.$transaction() with SET LOCAL, and without a non-bypass role on the connection, it’s easy to think RLS is protecting you when Prisma is silently bypassing it or supplying no policy context at all — the policies exist on the tables, nothing throws, every query passes through unfiltered. Supabase JS forwards the user’s JWT to PostgREST, which sets the context and the role per request by default.
It’s easy to think RLS is protecting you when Prisma is silently bypassing it or supplying no policy context at all. The policies exist, nothing throws, every query passes through unfiltered.
We’ve made this call twice. Both products had multi-tenant authz pinned to RLS and at least one writer outside the Next.js app. Both times the LLM’s first scaffold was Prisma. That’s not the LLM being wrong. Prisma is the highest-frequency answer in training data for “Next.js + Postgres”, and the right default for most teams. But the model can’t see your authz architecture until you tell it, doesn’t notice the second writer, and doesn’t know that two of the next ten migrations will be triggers, RPCs, or RLS policies. Both times we overrode the default and went SQL-first. PlanB V2 — multi-tenant backup product, Lambda fleet writing to the same Postgres, 132 timestamped SQL migrations, supabase-js, a Node safe-push wrapper — is the worked example below.
Three questions decide which side you’re on:
- Is the database the single authority for “who can read what”, or is the app?
- Does anything besides this app write to the same tables?
- When someone runs the migration tool against prod, what stops an accidental
DROP TABLEfrom landing?
Most teams don’t answer these before picking the ORM, because the ORM choice gets made before the authz architecture does.
What good looks like
Five questions partition every team into one of two database-management patterns. The cleaner you can answer each one, the lower the risk of picking the wrong pattern and discovering the mismatch six months in.
1. Authorization location. App-side authz means middleware checks the JWT, queries are written with explicit WHERE account_id = … clauses, the database trusts the app. Database-side authz means RLS policies on every tenant-scoped table, the app makes the typed call, the database returns only rows the session is allowed to see. The first is conventional. The second pushes more correctness into Postgres — fewer places to forget the check — but requires the data-access layer to supply the policy context per request (role and JWT) without leaking it across pooled connections. As covered in the intro, this is the work that distinguishes RLS-compatible from RLS-native clients.
2. Number of writers. Single-writer means this app’s server is the only thing inserting / updating / deleting rows. Multi-writer means a Lambda, a cron worker, an ETL job, or a sibling app also writes. With one writer, the ORM’s schema file can be the single source of truth — every change goes through the ORM, the DB shape never diverges. With multiple writers, the database itself has to be the source of truth, because no single codebase owns it.
3. Postgres-feature surface area. Count what the app actually uses that Prisma’s DSL can’t represent: triggers, RPCs called from the app, partial indexes, covering indexes with INCLUDE, GIN / GiST indexes, generated columns, custom types, STATEMENT_TIMEOUT per-function, RLS policies. If the count is “two or three things we could live without”, Prisma is fine and the rest goes in raw prisma migrate SQL. If the count is “most of the migrations contain something Prisma can’t model”, the ORM has stopped paying for itself.
4. Migration safety posture. What happens if someone runs the migration tool against prod from their laptop? With Prisma, prisma migrate deploy is explicit and reviewable. With Supabase, supabase db push defaults to --linked — a bare push talks to the linked project (often prod) with no confirmation prompt and no branching. --dry-run and --local both exist as opt-in flags; the failure mode is forgetting to pass them, not their absence. The safety story isn’t optional in either case; it just looks different.
5. Type-generation direction. Does your TypeScript types file come from the schema file (Prisma generates types from schema.prisma) or from the live database (Supabase introspects the running Postgres)? The first is faster and offline; the second is the only path that catches things Prisma’s DSL can’t model. If your schema includes RLS-policy-aware view types, generated columns, or RPC return shapes that you want typed at the call site, introspection is the only way to keep types honest.
A team that answers “app, one, few, prisma migrate, schema-file-first” should use Prisma. A team that answers “database, more than one, many, hand-written and gated, introspection” should use the SQL-first pattern. Borderline teams should pick the more conservative side — adding RLS later is harder than removing it, and adding a sibling writer later is much harder than designing for it from day one.
The prompt
The decision is small enough to make over coffee. The setup is mechanical enough that an agent should do it. Paste this into Claude Code (or Codex). Phase 0 forces the agent to read your codebase and answer the five questions before recommending anything; Phase 1 applies the decision tree; Phase 2 implements whichever pattern came out of it.
Decide and set up the database management pattern for this app.
═══ PHASE 0 — DISCOVER FIRST ═══
Before recommending or installing anything, inspect this codebase and
write your findings to `docs/db-management.md`. Cover:
1. STACK
- Framework (Next.js App Router? Remix? SvelteKit? Express? FastAPI?)
- Database (Postgres? MySQL? Managed: Supabase, Neon, RDS, Aiven?
Self-hosted?)
- Existing ORM or query layer, if any
- Existing migration tool, if any
- Whether a TS types file is checked in (e.g. database.types.ts,
prisma/client output)
2. AUTHORIZATION SHAPE
- Where multi-tenant boundaries are enforced today (app code? RLS?
neither — single-tenant?)
- Whether the app has B2B-style tenants (accounts owning users
owning data) or single-tenant
- Whether a second writer exists (Lambda, cron, ETL, analytics
warehouse, sibling app)
- Whether non-application services need to write to the same DB
3. POSTGRES-SPECIFIC FEATURE SURFACE
- Triggers (count today, expected use)
- Stored functions / RPCs invoked from the app
- Partial / covering / GIN / GiST indexes
- Custom types, enums
- Generated columns
- Per-function `statement_timeout` settings
- Row Level Security policies (count, hand-written vs generated)
- Realtime / logical replication subscriptions
4. MIGRATION SAFETY POSTURE
- Is the migration CLI linked to a prod database by default?
- Is there a preflight check for destructive SQL?
- How does a migration get from a dev branch into prod —
reviewed PR? auto-applied via CI? manual operator run?
5. SCHEMA GROWTH SHAPE
- Is the schema small and stable (CRUD app, a few dozen tables)?
- Or does it grow weekly with new triggers / RPCs / indexes /
event tables?
- Are most queries simple CRUD, or are some materially complex
(recursive CTEs, DISTINCT ON skip-scan, window functions,
multi-table aggregations)?
Then write the decision and the implementation plan in the same doc.
Stop. I'll confirm before you implement.
═══ PHASE 1 — DECIDE ═══
Apply this decision tree to the findings from Phase 0.
PICK PRISMA (or Drizzle, or any schema-file-first ORM) WHEN:
- Authorization is enforced in app code, not in the database
- The database has exactly one writer (this app's server)
- The schema is small-to-medium and grows slowly
- Most queries are CRUD-shaped
- Postgres-specific features beyond basic indexes are rare
- Type-safety at the call site is the highest priority and the
schema file as source of truth is acceptable
PICK SQL-FIRST (supabase-js / pg / postgres.js + timestamped SQL
migrations) WHEN ANY OF THE FOLLOWING ARE TRUE:
- Authorization lives in the database via Row Level Security
- There is more than one writer to the database (Lambda, ETL,
sibling app)
- The app uses material amounts of triggers, RPCs, partial /
covering / GIN indexes, generated columns, or other features
the ORM's schema DSL cannot model
- The DB schema is the contract between two or more codebases
- You're already on Supabase / Neon / managed Postgres where the
platform ships an introspection-based type generator and the
SQL-first path is well-supported
REJECT BOTH AND ESCALATE WHEN:
- This is a brand-new project with no Postgres choice locked in
and a non-Postgres database genuinely fits better (Mongo for
document-shape data, ClickHouse for analytics) — note and stop.
Write the decision with a one-paragraph rationale citing concrete
findings from Phase 0. If borderline, state the trade-off explicitly
and pick the more conservative side (RLS-friendly is more
conservative — easier to remove than to add).
═══ PHASE 2 — IMPLEMENT (after decision approval) ═══
IF THE DECISION WAS PRISMA (or a schema-file ORM):
1. Install Prisma. Generate the schema from the existing DB:
`npx prisma db pull` if a schema already exists, or design
the `schema.prisma` from scratch if greenfield.
2. `prisma/migrations/` directory. Local dev workflow uses
`prisma migrate dev`; CI uses `prisma migrate deploy`.
3. Connection wrapper as a module-level singleton (Next.js:
`global.prisma` pattern to survive HMR; serverless: Prisma
Accelerate or careful per-invocation client management).
4. Per-request session config (e.g. `SET app.user_id = …`)
if you want any DB-side checks at all. This is the one place
Prisma users brush against the RLS world.
5. CI: `prisma migrate diff --from-schema-datasource --to-schema-
datamodel` to catch drift between the live DB and the
committed schema.
6. CONTRIBUTOR DOC: every schema change goes through `.prisma`,
not raw SQL. Otherwise the diff drifts and `prisma migrate`
stops generating correctly.
IF THE DECISION WAS SQL-FIRST:
1. TIMESTAMPED MIGRATIONS — the patch system.
- Directory: `supabase/migrations/` (or `db/migrations/` for
non-Supabase tools).
- Filename pattern: `YYYYMMDDhhmmss_short_description.sql`.
Lexicographic order = application order; never rename a
file after it's shipped to any environment.
- One semantic change per file. Easier to review, easier to
rollback by writing a forward-fix migration.
- Idempotent where reasonable (`CREATE TABLE IF NOT EXISTS`,
`DROP POLICY IF EXISTS … CREATE POLICY …`).
2. SAFE-PUSH WRAPPER — `scripts/db-push-safe.mjs`.
A Node script invoked by `npm run db:push` that runs BEFORE
the migration CLI's apply command. Required checks:
a) Connect to the target DB. Refuse to proceed if any
session is holding a blocking lock on the
schema_migrations table — fail fast with the conflicting
PID, not after a 30-second lock timeout. (The original
trigger for this was a scheduled pg_dump backup colliding
with a migration push; any concurrent locker has the same
effect.)
b) Read every unapplied migration file. Strip SQL comments
first so docstrings don't trigger. Scan for destructive
patterns: `DROP TABLE`, `DROP COLUMN`, `TRUNCATE`,
`DROP SCHEMA`, `DROP FUNCTION`, `DROP POLICY`,
`ALTER TYPE … DROP`, `ALTER TABLE … DROP CONSTRAINT`.
c) On a destructive hit, print the migration filename, the
matching line, and require an explicit confirmation flag
(e.g. `--i-know-this-is-destructive`) before proceeding.
d) Hand off to the real CLI command
(`supabase db push --linked` or your tool's equivalent)
once preflight passes.
e) Optional CI mode: auto-terminate Supavisor sessions older
than 30 minutes that are blocking the lock. The 30-minute
floor is a guardrail so a human running pg_dump doesn't
get murdered mid-job.
3. GENERATED TYPES from live schema introspection.
- `supabase gen types typescript --local > src/lib/database.types.ts`
(or `pg-to-ts`, `kanel`, etc. for non-Supabase Postgres).
- CI step that regenerates and diffs against the committed
file — fails the build on drift.
4. RLS POLICIES — every multi-tenant table gets all of:
a) `ENABLE ROW LEVEL SECURITY`
b) `FORCE ROW LEVEL SECURITY` (so the table owner isn't
exempt — easy to forget; the table appears protected
until a service-role query proves otherwise)
c) Policies scoped per-role (authenticated / anon /
service_role / postgres). NEVER write blanket policies
unscoped to a role — name the role every time.
d) Helper functions for tenancy checks
(`get_current_account_id() RETURNS uuid`,
`is_member_of(account_id uuid) RETURNS boolean`)
marked `SECURITY DEFINER` and `STABLE`.
5. AUDIT THE SILENT-RLS-NO-OP PATTERN.
Routes that do
`await supabase.from('x').update(…).eq('id', y)`
return 200 OK even when the row exists and RLS prevents the
update — PostgREST treats zero rows affected as success.
For every mutation route, either:
- explicit ownership pre-check, OR
- assert that the returned row count > 0, OR
- `.select()` after the mutation and confirm the expected
row came back.
This is the most common silent cross-tenant failure mode in
RLS-based apps. We shipped at least one of these.
6. RPCs (stored functions) for read paths that aggregate or join
more than two tables.
- Marked `STABLE` or `IMMUTABLE` where applicable.
- Pinned `statement_timeout` via
`ALTER FUNCTION foo() SET statement_timeout = '20s'`.
IMPORTANT GOTCHA: `CREATE OR REPLACE FUNCTION` resets the
per-function `statement_timeout` config. Every migration that
redefines a timed function must RE-APPLY the `ALTER FUNCTION
SET statement_timeout` in the same migration immediately after
the `CREATE OR REPLACE`. Otherwise the timeout silently
disappears the first time the function is redefined.
7. VERSION FILE — `VERSION` at repo root, semver, bumped on
every release. Docker image tag aligns with it. An internal
`/version` endpoint surfaces it for incident triage. If you
have an admin app or a separate worker, give each its own
`VERSION` so the deployed surfaces are independently
identifiable.
8. HARD CONTRIBUTOR RULE in CLAUDE.md / AGENTS.md / README:
NEVER run the migration CLI's apply command directly.
ALWAYS go through the safe-push wrapper. The CLI is linked
to a real database; a five-character typo is enough to
apply every unapplied local migration to prod.
═══ PHASE 3 — VERIFY ═══
- The decision rationale in `docs/db-management.md` cites
concrete findings from Phase 0, not abstract "best practice"
claims.
- IF PRISMA:
- `npx prisma migrate status` is clean.
- `prisma migrate diff` against the live DB returns empty.
- One CI run proves the diff check fails on a synthetic
drift (an out-of-band SQL edit).
- IF SQL-FIRST:
- At least one mutation route has been audited for the
silent-RLS-no-op pattern; the fix is in place; a
regression test covers it.
- The safe-push wrapper has been smoke-tested with a
synthetic destructive migration; it refused to proceed
without the confirmation flag.
- `gen types` output matches the committed types file
(CI check is green).
- One RLS policy has a regression test: authenticated
user A cannot read account B's data through the typed
client.
- A redefined timed function has its `ALTER FUNCTION SET
statement_timeout` re-applied in the same migration
(grep the migrations for `CREATE OR REPLACE FUNCTION`
and confirm).
- The decision and any explicit trade-offs are documented in
`docs/db-management.md` so the next contributor doesn't
relitigate the choice.
Adapt the specifics to your stack (swap Supabase for Neon or vanilla pg, replace our Next.js App Router reference for whatever you use). The structure of the prompt is what does the work.
What it does
- Decision before code. Phase 0 and Phase 1 are pure analysis. Most teams skip this and end up with a stack that doesn’t match their requirements; the prompt makes the decision explicit and reviewable as a doc before any package is installed.
- Stack discovery, not stack assumption. Phase 0 hands the agent a checklist of dimensions to discover — framework, database, ORM, authz shape, Postgres-feature surface — so the recommendation grounds in real findings, not pattern-matching against the most popular blog post in training data.
- One implementation branch executes. Phase 2 has two parallel sub-blueprints. The agent runs exactly one of them, picked by the decision. The other becomes dead text — that’s correct; you want the prompt to be portable across teams.
- Every named callout is a real-world finding. The silent-RLS-no-op pattern, the
CREATE OR REPLACE FUNCTIONtimeout reset, theFORCE ROW LEVEL SECURITYowner-exempt trap, the schema_migrations lock collision, the--dry-runopt-in trap — none are in the standard docs. The prompt encodes the fix at the place the agent might otherwise skip it. - Verification, not vibes. Phase 3 is concrete checks, not “looks good to me”. The synthetic destructive-migration test is the most valuable single line — without it the safe-push wrapper ships untested.
What goes wrong
Gotcha 1: PostgREST returns 200 OK on an RLS no-op update. A user authenticated as account A sends PUT /api/apps/[appId]/settings with an appId that belongs to account B. RLS silently filters the UPDATE to zero rows. The typed Supabase call returns no error and the route returns 200 OK — no row changed, no row leaked, nothing thrown. (The exact body depends on .select() and the return-mode preference; what matters is that error is null.) The client thinks the update succeeded. We shipped this. It surfaces under audit only because someone reads the diff and asks “what does this return if RLS blocks it?” — not because anything alarms. Lesson: when RLS gates a mutation, “no error” does not mean “the change happened”. Every mutation route needs explicit ownership pre-check, assertion that rows-affected > 0, or .select() after mutation to confirm. An ORM that hides the connection model has no native concept of this failure mode; you check it in app code or you ship the bug.
Gotcha 2: Prisma’s schema file can’t see most of what your migrations do. PlanB V2 has 132 timestamped SQL migration files. A representative recent week: a trigger-and-event-table migration, four RPCs marked STABLE with pinned statement_timeout, two RLS policy rewrites, a unique-index-plus-backfill dedup migration, an ALTER TYPE enum extension, and a CHECK constraint added to an existing column. Prisma’s schema DSL doesn’t model most of these — partial indexes recently shipped as a Prisma 7 preview feature, but triggers, RPCs, RLS policies, per-function statement_timeout, covering indexes with INCLUDE, and most ALTER TYPE paths are still raw-SQL escape hatches. A team that chose Prisma would write the same migrations as raw SQL — and Prisma’s diff checker would not know they exist. The drift accumulates silently. The cost is not “Prisma is wrong”; the cost is “you have two sources of truth for the same schema and neither of them is complete”. Lesson: an ORM is a contract that the schema fits inside its DSL. Audit your last twenty migrations for things the DSL can’t represent. If most of them have at least one, the contract is broken and you’re paying the ORM tax with no return.
An ORM is a contract that the schema fits inside its DSL. Audit your last twenty migrations for things the DSL can’t represent. If most have at least one, the contract is broken.
Gotcha 3: supabase db push defaults to talking to the linked project. After the first supabase link, the linked project ref lives in supabase/.temp/project-ref. A bare supabase db push defaults to --linked and applies every unapplied local migration to that project — typically prod — with no confirmation prompt. --dry-run and --local exist as opt-in flags; the failure mode isn’t their absence, it’s forgetting to pass them. A developer who copies the bare command from the docs hits prod by default. Opt-in safety flags don’t survive the first time someone hurries. The db-push-safe.mjs wrapper exists for exactly this reason: connect to the DB, scan the pending migration files for destructive patterns, demand explicit acknowledgment on hits, and only then hand off to the real CLI. Lesson: managed-database tooling defaults to “talk to the linked project” because the CLI was built for an operator running it from their laptop. A safety wrapper is not a nice-to-have; it is the only thing standing between someone forgetting --dry-run and a prod incident.
Gotcha 4: CREATE OR REPLACE FUNCTION resets the per-function statement_timeout. You define a long-running aggregation as an RPC, pin its timeout to 20 seconds via ALTER FUNCTION foo() SET statement_timeout = ‘20s’, and ship it. Six weeks later a new migration redefines the function with CREATE OR REPLACE FUNCTION: different return shape, same name. The config setting is dropped silently as part of the redefinition. The function now runs unbounded. Three months later it takes the DB down during a busy period because no one realised the guardrail was gone. Lesson: per-function GUCs do not survive a redefinition. Every CREATE OR REPLACE FUNCTION in a migration must be followed by the ALTER FUNCTION SET statement_timeout in the same migration. Grep for CREATE OR REPLACE FUNCTION across your migration directory; every hit should be paired with the re-applied config or there’s a latent regression.
A few more went into the prompt as inline callouts rather than article-body paragraphs: introspected types don’t fully cover RPC argument shapes, FORCE ROW LEVEL SECURITY has to ride alongside ENABLE ROW LEVEL SECURITY because table owners are exempt by default, and the safe-push wrapper’s auto-terminate CI mode should never be on by default.
What it costs
An hour for the decision and the documented rationale. Half a day to scaffold whichever pattern came out of it — Prisma is faster to stand up; SQL-first takes longer because the safe-push wrapper and the RLS regression test are real engineering. Both fit the shape an agent handles well.
The real cost is the avoidance cost. Picking Prisma for an app that should be SQL-first means you live with raw-SQL escape hatches multiplying every sprint, a Prisma schema that’s no longer authoritative, and an authz story split between two layers. Picking SQL-first for an app that should be Prisma means you write fifty timestamped migrations to express what one schema.prisma would have. Both mistakes are expensive to undo. The decision is cheaper than the repair.
Why this matters
The ORM-vs-SQL debate is usually argued at the wrong altitude. People compare syntax and bundle size; the decision is upstream of both. The actual question is where the schema and the authz live — in a file your app owns, or in the database your app talks to. Get that right and the ORM choice is downstream; get it wrong and the ORM fights you for the lifetime of the codebase. The model will hand you Prisma on prompt #1 — that’s a defensible default for most teams. The override, when you need it, is yours to make deliberately, with the criteria above.
The model will hand you Prisma on prompt #1. That’s a defensible default for most teams. The override, when you need it, is yours to make deliberately.
Built on PlanB. Stack: Next.js 16 App Router + Supabase Postgres 15 + supabase-js + 132 timestamped SQL migrations + a Node safe-push wrapper. The decision tree was applied to two separate Supabase + Next.js products and both went SQL-first for the reasons in this article. Work captured by Claude Code (Opus 4.7) — about three hours of human review on top, plus a fact-check pass with Codex CLI before publication.