Postgres / Prisma Backend

Postgres is the recommended backend: your own database, standard SQL, no vendor lock-in, and a connection string that never leaves the server. The commerce layer reaches it through a server-side Prisma adapter that satisfies the same CommerceServices interface as every other backend — same lifecycle events, same read shapes. The whole store runs on it: catalog, carts, quote requests, orders, discounts, and the admin audit trail, on any Postgres you control (local, Supabase, Neon, RDS, a container).

Adapter: src/lib/commerce/server/prisma/index.ts → export const prismaCommerce
Schema: prisma/schema.prisma
CLI config: prisma.config.ts

Nothing here is needed to run the boilerplate — a fresh clone serves the bundled demo catalog with no database at all. These steps are for connecting real data. Sign-in is a separate choice you still have to make: Firebase Auth is the default and the RPC route verifies its ID tokens before touching Postgres, or AUTH_PROVIDER=authjs puts sessions in this same database and removes the Firebase project entirely. See authentication.

Prisma Postgres / Accelerate is NOT used

This project does not use Prisma Postgres or Prisma Accelerate — there is no Prisma account, login, or API key involved anywhere. DATABASE_URL must be a plain postgresql:// connection string, and it must be present in the environment before you run npm run prisma:generate.

Prisma 7's CLI has an onboarding flow for its own hosted database (Prisma Postgres + Accelerate). If DATABASE_URL is missing — or points at a prisma:// / prisma+postgres:// URL — the CLI falls back to that onboarding and prompts you to log in and issue an API key. With a real postgresql://... string set, prisma generate and prisma db push run non-interactively and never ask for an account or key.

Prisma 7 specifics

This project ships Prisma 7, which changes two things the adapter relies on:

No datasource.url in schema.prisma. Prisma 7 removed that property. The connection string the CLI needs lives in prisma.config.ts instead (datasource.url = env("DATABASE_URL")), and the runtime client gets it through a driver adapter.
The runtime client needs a driver adapter (not a bare URL). This adapter builds a @prisma/adapter-pg client over DATABASE_URL. The @prisma/client, @prisma/adapter-pg, and pg packages ship in the base dependencies — no extra install is needed; you only generate the client once a DATABASE_URL is set.

prisma.config.ts

import { defineConfig, env } from "prisma/config";

// COMMERCE_BACKEND also picks the schema file, because Prisma will not read
// the datasource provider from an env var. See /docs/backends/sqlite.
const isSqlite = process.env.COMMERCE_BACKEND === "sqlite";

export default defineConfig({
  schema: isSqlite ? "prisma/schema.sqlite.prisma" : "prisma/schema.prisma",
  datasource: {
    url: env("DATABASE_URL"),
  },
});

Setup

Set `DATABASE_URL`

Use a plain PostgreSQL connection string. Any Postgres works. Export it in your shell or put it in a .env file your process loads — it must be present both for the Prisma CLI (read via prisma.config.ts) and at runtime (the adapter reads process.env.DATABASE_URL on first use).

.env

# Local Postgres
DATABASE_URL="postgresql://user:password@localhost:5432/ecommerce"

# Supabase (Project Settings → Database). The pooled "Transaction"
# string works well for serverless:
DATABASE_URL="postgresql://postgres.<ref>:<password>@aws-0-<region>.pooler.supabase.com:6543/postgres"

Set this before the next step. prisma generate only needs the string to parse (it does not connect), but if it is missing the CLI drops into Prisma's hosted-database onboarding and asks for an API key.

Generate the client

Terminal

npm run prisma:generate   # == npx prisma generate

This produces the typed client that @prisma/client re-exports. Re-run it after every schema change (and after a fresh npm install on a Postgres deploy).

Create the tables

For an existing or shared database where you do not want migration history (e.g. prototyping, or applying onto a Supabase project), push the schema directly:

Terminal

npm run prisma:push   # == npx prisma db push

Both read the connection string from prisma.config.ts.

Grant admin access

admin.isAdmin(uid) checks row existence in the admins table, keyed by the auth uid. Insert a row per admin:

insert into admins (uid) values ('<auth-uid>')
on conflict (uid) do nothing;

Activate the backend

Backend selection is env-driven — you do not edit any source file. Set both variables so the browser and the server agree:

.env

NEXT_PUBLIC_COMMERCE_BACKEND=postgres   # browser: route commerce calls to the RPC layer
COMMERCE_BACKEND=postgres               # server: run the Prisma adapter

The browser never talks to Postgres directly. It calls a single RPC route (POST /api/commerce/<service>/<method>) that identifies the caller through the configured auth provider, enforces per-method access control (public / user-scoped / admin), and then runs prismaCommerce with the privileged DATABASE_URL. On the default provider that means verifying a Firebase ID token, so the FIREBASE_SERVICE_ACCOUNT* credentials are required too; AUTH_PROVIDER=authjs reads a session cookie instead and needs none of them. See environment variables.

Recording Stripe payments

orders.recordPaidSession is the Postgres implementation of what the Stripe webhook calls after verifying an event, so paid orders land in your database like every other write. It leans on two things:

orders.payment_session_id is UNIQUE. A retried delivery's INSERT violates it (Prisma P2002), the whole transaction rolls back, and the adapter answers with the order that already exists. That is stronger than a pre-check, which two concurrent deliveries could both pass under READ COMMITTED.
The products are locked first. Stock lives inside the product's data JSON, so decrementing is read-modify-write. The rows are taken with SELECT … FOR UPDATE (sorted by id, so concurrent sessions cannot deadlock) before anything is computed, which is what stops two payments from both spending the last unit.

Existing databases need one migration. payment_session_id and payment_status are new columns. Run npm run prisma:push (or a migration) before the first paid webhook arrives, or recording fails on an unknown column.

That FOR UPDATE query is the second Postgres-specific statement in the adapter (the first is the COLLATE "C" search) — worth knowing if you follow Using a different engine below.

Category pages

data.categories is promoted into a product_categories join table, so a category page is an indexed WHERE clause and products.countByCategory is a real count. The adapter rewrites a product's rows inside the same transaction as the product itself, on every write that touches its categories, so the two can never disagree.

A join table rather than an array column because the query is "which products are in this category": an array would need engine-specific containment operators, while this indexes identically on Postgres and SQLite.

An existing database needs a backfill. product_categories is new, and an empty one means every category page shows nothing. After npm run prisma:push, run this once — it is idempotent:

Postgres

insert into product_categories (product_id, category)
select p.id, value
from products p,
     lateral jsonb_array_elements_text(p.data -> 'categories') as value
where jsonb_typeof(p.data -> 'categories') = 'array'
on conflict do nothing;

SQLite

insert or ignore into product_categories (product_id, category)
select p.id, j.value
from products p, json_each(p.data, '$.categories') j;

Re-saving a product from the admin dashboard also rebuilds its rows.

Seeding works here too: npm run seed writes through this adapter like the app does, and populates the index as it goes.

Bundling: `serverExternalPackages`

The optional SQL-backend packages are kept out of the bundle and require()d at runtime inside the adapter. next.config.mjs lists them as external so a default (Firebase) build succeeds even without a generated client, and so the Postgres adapter can load the generated client at runtime:

next.config.mjs

serverExternalPackages: [
  "@prisma/client",
  "@prisma/adapter-pg",
  "pg",
  "@prisma/adapter-better-sqlite3",
  "better-sqlite3",
],

This pairs with the adapter's lazy client: @prisma/client and @prisma/adapter-pg are loaded via require inside getPrisma() (not a top-level import) so the file type-checks and builds with no generated client. The instance is cached on globalThis so hot reload does not open a new pool per edit, and the first actual operation — not import — throws if DATABASE_URL is unset.

Deploying on Postgres

A fresh CI environment has no generated client, so the Prisma client must be generated during the build. Set the build command (or add a postinstall) to run generation first:

Vercel Build Command

npm run prisma:generate && npm run build

The deploy environment must include:

Variable	Purpose
`DATABASE_URL`	Plain `postgresql://` string the adapter and CLI connect with.
`NEXT_PUBLIC_COMMERCE_BACKEND=postgres`	Browser routes commerce calls to the RPC layer.
`COMMERCE_BACKEND=postgres`	Server runs the Prisma adapter.
`FIREBASE_SERVICE_ACCOUNT_JSON`	Verifies caller ID tokens on the default identity provider. Not needed under `AUTH_PROVIDER=authjs`; still needed if you keep Firebase Auth.

Run npm run prisma:push (or your migration step) against the production database once, out of band, to create the tables.

Deploying on Firebase but the build still asks for a Prisma API key? Some platforms (notably Vercel) auto-detect prisma/schema.prisma + @prisma/client and inject prisma generate into the install step. With no DATABASE_URL present, that generate drops into Prisma's hosted-database onboarding and prompts for a key — even though your app never uses Postgres at runtime. Two fixes, either works:

Give the build a placeholder DATABASE_URL, e.g. postgresql://user:pass@localhost:5432/db. prisma generate only needs the string to parse, so generation runs non-interactively; the value is never used at runtime while COMMERCE_BACKEND is unset/firebase.
Or disable auto-generate on your platform (on Vercel, set the build command explicitly to npm run build) so Prisma is never invoked.

Data model

The schema (prisma/schema.prisma) mirrors the Firestore layout the Firebase adapter uses, with loose/schemaless documents stored in Json columns so unknown fields round-trip exactly like Firestore documents.

Model	Table	Key	Notes
`Product`	`products`	caller id (e.g. `AUD-001`)	Body in `data` Json; `productNameLower` and `brand` are promoted, indexed columns the adapter keeps in sync. Variants/options live inside `data`.
`ProductCategory`	`product_categories`	(`productId`, `category`)	Promoted index over `data.categories`; rewritten in the same transaction as the product.
`FeaturedProduct`	`featured_products`	`cuid()`	Full copy of a product body; `productId` promoted back-reference.
`Cart`	`carts`	`userId` (auth uid)	`items` is a JSON `CartItem[]`; `clear` deletes the row (missing ≠ empty).
`Quote`	`quotes`	`cuid()`	`items`/`customer`/`discount` are Json; `orderNumber` claimed atomically on conversion.
`Order`	`orders`	`cuid()`	Created by promoting quotes; `total` is a `Float`.
`Customer`	`customers`	`id` (auth uid)	Rigid: `firstName`, `lastName`, `phone`, `email` only.
`Address`	`addresses`	`cuid()`	Schemaless `data` Json + indexed `customerId` (no FK).
`Discount`	`discounts`	trimmed UPPERCASE `code`	Saves are single-row upserts; lookups case-insensitive by construction. `usedCount` is system-maintained (`quotes.submit` increments it, `save` never writes it) against a `maxUses` cap; `automatic` + `minSubtotal` drive codeless promotions.
`Admin`	`admins`	`uid`	Row existence grants admin.
`AuditLog`	`audit_log`	`cuid()`	The admin activity trail behind `commerce.audit`. Append-only — no update, no delete — with `details` as id-only Json and `source` recording whether the server or a client subscriber wrote it. Indexed `(created, id)` for the keyset page.
`NewsletterSignup`	`newsletter_signups`	normalized `email`	Written by `POST /api/newsletter`. Email-as-primary-key is what makes a repeat signup an upsert instead of a row.
`User` / `Account` / `Session` / `VerificationToken`	`users`, `accounts`, `sessions`, `verification_tokens`	see schema	Auth.js session store, used only under `AUTH_PROVIDER=authjs`. `User.id` is the same uid every commerce record is keyed by. Unused on Firebase Auth, where the tables stay empty.

Behavioral notes worth knowing:

Prefix search uses COLLATE "C". products.search pins its range bounds and ordering to byte-order collation so results match Firestore's code-point ordering regardless of the database's default collation.
Per-customer quote/order copies are flattened. listForCustomer is a WHERE customer_id = $uid query; the copy identity is synthesized on read (id === quoteNumber/orderNumber, mainQuoteId === <main record id> on quotes).
carts.listen polls (immediate fetch + 5s interval) — realtime push is Firestore-only for now. The callback contract is otherwise identical.
PageCursor is a base64url-encoded keyset token (JSON-serializable so it survives HTTP round-trips), not a Firestore snapshot.

Using a different engine

SQLite already ships. COMMERCE_BACKEND=sqlite runs this same adapter over a file, with its own schema file and three dialect branches — see SQLite. It is also how the adapter gets tested against a real engine.

For anything else (MySQL/MariaDB, say), the models port cleanly: the schema uses no Postgres-only features — no scalar lists, no enums, no @db.* native types. Adding one means copying prisma/schema.prisma with a new provider, teaching prisma.config.ts to select it, adding the driver-adapter branch in getPrisma(), and handling the three places isSqlite() already marks: the binary collation for products.search (MySQL: utf8mb4_bin), the row-lock statement in recordPaidSession, and whether raw reads of Json come back parsed. tests/prisma-schemas.test.ts will want extending to cover the third file.