# Next.js and Prisma


A Next.js app (App Router) on Prisma 7, whose migrations are Prisma Migrate's. Route handlers, server actions and
pages check nothing: each transaction signs in as the request's user, the database filters what it reads and
refuses what it may not write, and the SDK answers a refusal with 403 and a hidden row with 404. Signed-in
reads never land in a cache another user could be served from.

Everything here comes from [`integrations/nextjs`](https://github.com/rowstile/rowstile/tree/v0.1.0-beta.5/integrations/nextjs), the conformance suite: a small app and the checks every
supported stack passes ([`integrations/nextjs/test.sh`](https://github.com/rowstile/rowstile/blob/v0.1.0-beta.5/integrations/nextjs/test.sh), against the production build). Drizzle, pg and
postgres.js: [Node apps](/stacks/node.md).
Neon's serverless driver, the edge runtime, a BullMQ queue and `after()` are a preview, each in its section.

To start from an app that runs instead of from this page: the
[Next.js and Prisma starter](https://github.com/rowstile/starter-nextjs-prisma), a template repository.
`docker compose up`, and documents are shared with people and teams, with the policy, its tests and the review
in CI already there.

## Install

```sh
# Prisma 7 and its Postgres adapter, if the app doesn't have them yet
npm install @prisma/client@7 @prisma/adapter-pg@7 pg && npm install --save-dev prisma@7 dotenv
# @next on each: the newest pre-release (until 0.1.0 a plain install gets it too)
npm install @rowstile/client@next @rowstile/prisma@next @rowstile/pg@next @rowstile/next@next @rowstile/react@next
npm install --save-dev rowstile@next @rowstile/vitest@next
```

The `rowstile` package is the command, with its own Python: nothing else to install.

Two roles connect. The owner of the tables runs the migrations (`DATABASE_URL`), and the app connects
as the role the policy names, which row-level security applies to (`ROWSTILE_APP_URL`). Prisma migrates, so
its own configuration takes the owner's URL. Prisma 7 doesn't read `.env` itself: `dotenv/config` loads it.

```ts
// prisma.config.ts
import "dotenv/config";
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: { path: "prisma/migrations" },
  datasource: { url: env("DATABASE_URL") },
});
```

The app role and what it may do are a migration like the others, written by hand. A role belongs to the
whole server, and Prisma's shadow database runs every migration again: the role is made only where it is
missing. Grant it what the app does: reading, and writing the tables the app writes.

```sql
DO $$ BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'conf_app') THEN
    CREATE ROLE conf_app LOGIN NOSUPERUSER NOBYPASSRLS PASSWORD 'app';
  END IF;
END $$;
GRANT USAGE ON SCHEMA "app" TO conf_app;
GRANT SELECT ON ALL TABLES IN SCHEMA "app" TO conf_app;
GRANT INSERT, UPDATE, DELETE ON "app"."notes", "app"."inbox" TO conf_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA "app" TO conf_app;
```

With Prisma's default schema, `public`, name the tables in the first grant: `ALL TABLES` there includes
Prisma's own `_prisma_migrations`. The owner must also be able to switch to the app role, since the command
looks at the data as the app does when it runs the policy's tests: an owner that isn't a superuser gives
itself the role once, `GRANT conf_app TO conf_owner` (the command says so when it is missing, AZ618).

## A first policy

`init` reads your tables, so it comes once they are there: after `npx prisma migrate dev` has run the app's
migrations, the app role's among them.

```sh
npx rowstile init        # a first policy from your tables, a test file, rowstile.toml
```

It connects as the tables' owner, with `DATABASE_URL` (or `--db`). The command reads its variables from the
environment, then from `.env.local` and `.env` in the project's folder, where Next.js and Prisma keep theirs.
The policy's `app role` is the role the tables are granted to, `conf_app` here; where several roles are, or
none yet, it is `app_user`, and `init --role` names another. It finds Next.js and Prisma, and writes
`rowstile.toml` for them, the owner's URL read from the variable Prisma reads. The conformance app's:

```toml
policy   = "db/policy.authz"
tests    = ["db/tests/*.authz"]
database = "env:DATABASE_URL"
[clients]
ts = "src/authz.gen.ts"
[migrations]
tool = "prisma"
dir  = "prisma/migrations"
```

## The database client

`signedIn` wraps Prisma's driver adapter: every transaction Prisma begins signs in as whoever `user` returns
for the request (`null`: nobody, who sees only what `anyone` may). `authz()` turns Prisma's "record not found"
into 404 or 403 with the reason, and adds `db.$authz`. Use the client `$extends(authz())` returns, and only
that one: the client it was made from is refused whenever it is used, inside your own Prisma extensions too
(add them to the client `authz()` returns: they are placed before it, and each hook runs once for a call).
Importing `@rowstile/next` keeps signed-in reads out of Next.js's caches, and `authz.gen.ts` (written by
`rowstile client`) gives the SDK the policy's names, so a misspelled permission doesn't type-check. A route with
`export const dynamic = "force-static"` is outside this: Next.js renders it once, at build time, and `connection()`
lets it be. rowstile's reads must never be in one: a read there signed in as someone the code names (a `job`,
`actingAs(42, ...)`) would be served to everyone.
The imports below end in `.ts` and the app's own start with `@/`: `allowImportingTsExtensions`, and
`"paths": { "@/*": ["./src/*"] }`, in `tsconfig.json`.

`auth()` is your sign-in's: Auth.js's, or whatever reads your session cookie or token. The conformance app
has no sign-in, so its `src/auth.ts` stands in for one: it takes the user from a header its checks set, which a
real app must never believe. That is why this page doesn't show it.

```ts
import { PrismaPg } from "@prisma/adapter-pg";
import pg from "pg";
import { authz, signedIn } from "@rowstile/prisma";
import "@rowstile/next";                              // signed-in reads never land in a cache
import { auth } from "./auth.ts";                     // the session: Auth.js's auth(), or your own
import { PrismaClient } from "./generated/prisma/client.ts";
import "./authz.gen.ts";                              // the policy's names, for the SDK's types

/** Who the request is: the session's user (none: nobody, who sees only what `anyone` may). */
export async function requestUser(): Promise<string | null> {
  return (await auth())?.user?.id ?? null;
}

const url = process.env.ROWSTILE_APP_URL;
export const pool = new pg.Pool({ connectionString: url, max: Number(process.env.PG_POOL_MAX ?? 5) });
export const db = new PrismaClient({ adapter: signedIn(new PrismaPg(pool), { user: requestUser }) }).$extends(authz());
```

At start-up, the server stops if its connection skips row-level security (a superuser, BYPASSRLS, the tables'
owner), where the database would filter nothing:

```ts
// instrumentation.ts
import { checkAtStart } from "@rowstile/next";

export async function register() {
  if (process.env.NEXT_RUNTIME === "nodejs") await checkAtStart((await import("./src/db.ts")).db.$authz);
}
```

Next.js looks for that file beside `app/`: in an app made with `--src-dir`, where `app/` is inside `src/`, it
is `src/instrumentation.ts`, and it imports `./db.ts`.

## Pages, route handlers and server actions

A page reads as the request's user. With Cache Components (`cacheComponents: true`, which `create-next-app`
writes), what reads the request goes inside `<Suspense>`; without it, `next dev` works and `next build` stops
with "uncached or runtime data during prerendering":

```tsx
import { Suspense } from "react";
import { db } from "@/db";

async function Notes() {
  const notes = await db.note.findMany({ select: { id: true, body: true }, orderBy: { id: "asc" } });
```

```tsx
export default function Page() {
  return <Suspense><Notes /></Suspense>;
}
```

`route()` answers a refusal with a problem body: 404 for a row the user can't see, 403 with the rule and the
reason for one they may not change (its fields: <https://rowstile.dev/problems/refused>). Its `table` is the
table as the policy names it, with its schema (`public.Note` for a model `Note`), whichever rule refused:
`tableName("Note")` on `db.$authz` gives that name, for code that compares. A call the database
says names something that isn't there (a share with someone who doesn't exist) is 404 too, one it says
lacks an argument or has a wrong one (a negative page size) 400, one that needs someone signed in, from a
visitor who isn't, 401, and a folder moved into one of its own subfolders 409, each with the database's words.
Prisma's driver adapter keeps no hint for SQLSTATE 28000, so through Prisma a login the database refuses
(`authz.login_key`, AZ703) has no code and stays Prisma's error, a 500; through `pg` it answers 401.

```ts
import { route } from "@rowstile/next";
import { db } from "@/db";

type Params = { params: Promise<{ id: string }> };

// an update the rules may refuse: a hidden note is 404, one the user may not edit 403 with the reason
export const PATCH = route(async (req: Request, { params }: Params) => {
  const id = Number((await params).id);
  const { body } = (await req.json()) as { body: string };
  await db.note.update({ where: { id }, data: { body } });
  return Response.json({ id });
});
```

Next.js hides a server action's thrown error from the browser, so `action()` returns the problem instead,
`{ ok: false, problem }`:

```ts
"use server";
import { action } from "@rowstile/next";

export const renameNote = action(async (id: number, body: string) => {
  await db.note.update({ where: { id }, data: { body } });
  return id;
});
```

Prisma's `create` reads the new row back, so an insert also needs the select rule; if the user may insert but
not read the row, the 403 names the select rule.

Two Prisma habits are handled: each `findUnique` runs in a transaction of its own (Prisma answers the calls of
one tick together, from the first caller's context), and array transactions, `$transaction([...])`, are
refused for the same reason. Use `$transaction(async (tx) => ...)`; inside it, why a write was refused is asked
through the transaction, and so is what `db.$authz` (or `tx.$authz`) asks for its principal: it sees what the
transaction wrote, and needs no second connection. The `findUnique` transactions follow Prisma's
`transactionOptions`: they wait for a connection as long as `maxWait` says, 2 seconds by default, and last at most
`timeout`, 5 seconds by default. With the database far away and many `findUnique` calls at once, give the client
a longer `maxWait` (or a bigger pool), or they fail with "Unable to start a transaction in the given time". A
`findUnique` slower than `timeout` (a large `include`) fails with "Transaction already closed": give a longer
`timeout`.

A transaction is one principal's: a sign-in lasts the database transaction. Inside `$transaction(async (tx) =>
...)`, a query through `db` (not `tx`) for anyone else, after `actingAs` or in a job, throws an `Error` before a
connection is asked for, and so does a `db.$transaction` inside it for anyone else; the transaction goes on as its
own principal. Run that work after it, or in a transaction of its own on another connection. The same holds for
`@rowstile/pg`, `/postgres` and `/drizzle` ([Node apps](/stacks/node.md#who-a-transaction-acts-for)). A `db.$transaction`
inside it for the same principal is Prisma's: another transaction, on another connection, which doesn't see what
the first one wrote. Prisma's own `tx.$transaction(async (t) => ...)` is a savepoint of it, as a transaction inside
another for the same principal is in those three.

## Buttons, sharing and live updates (React)

`authzRoutes()` is what the React kit calls: permissions for a list, shares, access requests, and live updates
from the change feed. A request that names no type, or a body that isn't a JSON object, is 400.

```ts
// app/api/authz/[...authz]/route.ts
import { authzRoutes } from "@rowstile/next";
import { db, feed } from "@/db";

export const { GET, POST } = authzRoutes({ calls: db.$authz, changes: feed });
```

```ts
import { changes } from "@rowstile/pg";

// live updates for @rowstile/react: one connection that LISTENs, beside the pool, straight to Postgres
// (through a pooler in transaction mode LISTEN hears nothing: ROWSTILE_FEED_URL is the direct URL then)
export const feed = changes(new pg.Pool({ connectionString: process.env.ROWSTILE_FEED_URL ?? url, max: 1 }));
```

The layout wraps every page in `<AuthzProvider>`. The kit is a client module (`"use client"`), so the layout,
a server component, renders it as it is:

```tsx
// app/layout.tsx
import { AuthzProvider } from "@rowstile/react";

export default function Layout({ children }: { children: React.ReactNode }) {
  return (
    <html lang="en">
      <body>
        <AuthzProvider>{children}</AuthzProvider>
      </body>
    </html>
  );
}
```

`usePerms` answers a list's buttons in one call, and `<Can>` shows what the user may do:

```tsx
function Buttons() {
  const perms = usePerms("project", [1, 2, 3]);
  if (perms.loading) return <p>loading</p>;
  return <ul>{[1, 2, 3].map((id) => <li key={id}>{id}:{perms(id).perms.join(",")}{perms(id).can("edit") && " [rename]"}</li>)}</ul>;
}
      <Can type="project" id={1} perm="edit" fallback={<span>no edit on 1</span>}><span>edit on 1</span></Can>
```

`useShares` and a headless `<ShareDialog>` share and unshare as the database allows; `useAccessRequest` asks
for access. They stay current over server-sent events. A request without a reason answers 400 with the
database's words, "say why you need it" (its fields: <https://rowstile.dev/problems/bad-argument>), one from
someone not signed in 401, "sign in first" (<https://rowstile.dev/problems/not-signed-in>), and a share with
someone who doesn't exist 404, "there is no user 99".

Your own code shares with `db.$authz.share`: the object, the relation, then who it is shared with (a type and
an id; for a team's members, `"team", 7, "member"`). Who may share is the policy's (`viewer : user shared by
edit`), so the handler checks nothing: anyone else gets a 403.

```ts
export const POST = route(async (req: Request, { params }: Params) => {
  const { user } = (await req.json()) as { user: number };
  await db.$authz.share("project", Number((await params).id), "viewer", "user", user);
  return new Response(null, { status: 204 });
});
```

## Next.js with pg, without Prisma

An app on plain `pg` (node-postgres) takes `@rowstile/pg` where this page has `@rowstile/prisma`; the rest
holds: `route()`, `action()`, `checkAtStart`, the React kit. But a refusal that Postgres raises itself (an insert
read back without the select rule, an upsert, a MERGE) names its table without the schema there, `inbox` and
not `app.inbox`: Postgres's words don't give it, and `@rowstile/prisma` adds it from the models. Leave Prisma and `@rowstile/prisma@next` out
of the install: `@rowstile/pg@next` is there already, for the live updates. The pool connects as the app role, with a URL of its own, and `authz(pool, { user })`
signs each transaction in as the request's user (`requestUser`: your session's user id, as above):

```ts
// src/db.ts
import pg from "pg";

const url = process.env.ROWSTILE_APP_URL;
export const pool = new pg.Pool({ connectionString: url, max: Number(process.env.PG_POOL_MAX ?? 5) });

// src/pg.ts
import { authz } from "@rowstile/pg";
import "@rowstile/next";                              // signed-in reads never land in a cache
import { pool, requestUser } from "./db.ts";

export const sql = authz(pool, { user: requestUser });
```

A plain query reads what the user may see. `sql.transaction(async (client) => ...)` runs several statements
signed in once, and `sql.expect(...)` says why an UPDATE or DELETE changed nothing ([Node apps](/stacks/node.md#pg)):

```ts
import { route } from "@rowstile/next";
import { sql } from "@/pg";

export const GET = route(async () => {
  const { rows } = await sql.query("SELECT id FROM app.notes ORDER BY id");
  return Response.json(rows.map((r) => r.id));
});
```

A sharing dialog lists the shares, makes them and takes them back; the database decides who may (the
relation's `shared by`), and `route()` answers its refusal with 403 ("you cannot share project 1"). A share's
columns are in [the reference](/reference/app-code.md):

```ts
type Params = { params: Promise<{ id: string }> };

export const GET = route(async (_req: Request, { params }: Params) => {
  const shares = await sql.listShares("project", Number((await params).id));
  return Response.json(shares.map((s) => ({ relation: s.relation, user: s.subject_id })));
});

export const POST = route(async (req: Request, { params }: Params) => {
  const { user } = (await req.json()) as { user: number };
  await sql.share("project", Number((await params).id), "viewer", "user", user);
  return new Response(null, { status: 204 });
});
```

`sql.unshare(...)` takes the same arguments. At start-up, `checkAtStart` in `instrumentation.ts` stops the
server if the pool's connection skips row-level security (a superuser, BYPASSRLS, the tables' owner); calling
`sql.check()` there yourself only throws, which Next.js logs before it goes on serving:

```ts
export async function register() {
  if (process.env.NEXT_RUNTIME === "nodejs") await checkAtStart((await import("./src/pg.ts")).sql);
}
```

## The Neon serverless driver and the edge runtime (preview)

*Preview: this part may change in any release, and is not part of the 1.0 freeze.*

Neon's serverless driver, `@neondatabase/serverless`, talks to Postgres over WebSockets, which the edge runtime
has where it has no TCP. Its `Pool` works as pg's does, and `@rowstile/pg` takes it: `authz(pool, { user })`, its
transactions, `expect` and the runtime's calls, typed with Neon's own client. Install the driver beside
`@rowstile/pg@next`: `npm install @neondatabase/serverless`.

On Neon the driver needs no settings. In front of your own Postgres (in development, and in the conformance
suite) it needs a WebSocket proxy, Neon's `wsproxy`, and three settings:

```ts
import { Pool, neonConfig } from "@neondatabase/serverless";

const proxy = process.env.ROWSTILE_WSPROXY;           // host:port of a local wsproxy; unset on Neon
if (proxy) {
  neonConfig.wsProxy = (host, port) => `${proxy}/v1?address=${host}:${port}`;
  neonConfig.useSecureWebSocket = false;              // ws://, to localhost
  neonConfig.pipelineConnect = false;                 // the startup pipelining only Neon's own proxy takes
}
```

On the Node.js runtime, one pool serves the server, as with pg:

```ts
const neonUrl = process.env.ROWSTILE_NEON_URL;
export const neonPool = new Pool({ connectionString: neonUrl, max: Number(process.env.PG_POOL_MAX ?? 5) });
export const neon = authz(neonPool, { user: requestUser });
```

On the edge runtime a WebSocket can't outlive the request that opened it. Each request makes a pool of its own,
and ends it once the response is sent:

```ts
export function edgeSql() {
  const pool = new Pool({ connectionString: neonUrl });
  after(() => pool.end());
  return authz(pool, { user: requestUser });
}
```

A route on the edge reads and writes as the request's user, and `route()` answers a refusal there as anywhere:

```ts
export const runtime = "edge";

export const GET = route(async () => {
  const { rows } = await edgeSql().query("SELECT id FROM app.notes ORDER BY id");
  return Response.json(rows.map((r) => r.id));
});
```

Neon's pooled URL (the host with `-pooler`) is PgBouncer in transaction mode. That is safe: every transaction
signs in, and nothing uses a session-level `SET`.

What the edge runtime can't do, and what happens:

- **An app with `"use cache"` has no edge routes.** Next.js refuses a route's `runtime` when `cacheComponents`
  (or `experimental.useCache`) is on: `Route segment config "runtime" is not compatible with
  nextConfig.cacheComponents`. The conformance suite's edge routes are an app of their own for that reason
  ([`integrations/nextjs/edge`](https://github.com/rowstile/rowstile/tree/v0.1.0-beta.5/integrations/nextjs/edge)).
- **`checkAtStart` throws there.** The edge runtime has no server to stop, and no `process.exit`. Keep it in
  `register()` where `NEXT_RUNTIME` is `"nodejs"`: the edge routes connect as the same role.
- **pg, `@prisma/adapter-pg` and postgres.js connect over TCP**, which the edge runtime doesn't have. Use Neon's
  driver there.
- **The change feed needs a connection that outlives a request.** Keep `authzRoutes()` (its `/events`) and
  `changes()` on the Node.js runtime.

Next.js 16 says the edge runtime is deprecated, and suggests the Node.js runtime. Neon's driver works on both.

## Background jobs

A job acts for a principal of its own (`type service = app.services principal` in the policy), whatever
started it: `after()`, a queue worker, a cron route.

```ts
import { current, job } from "@rowstile/client";

export const digest = job(["service", 1], async () => ({ count: await db.project.count(), who: current() }));
```

Anywhere else, `actingAs("3", () => ...)` signs in what runs inside it.

### A queue: BullMQ (preview)

*Preview: this part may change in any release, and is not part of the 1.0 freeze.*

BullMQ 6 on Redis, with ioredis (`npm install bullmq ioredis`). A worker runs the job as `job()` says, whoever
queued it: the request's user queues the digest, and the worker signs in as service 1. The worker's own code
outside the job (its `completed` handler, say) acts for nobody.

```ts
import { Queue, Worker } from "bullmq";
import { digest } from "./jobs.ts";

const connection = { url: process.env.REDIS_URL, maxRetriesPerRequest: null };

// made on first use: next build loads the routes, with no Redis to reach
let queue: Queue | undefined;
export const digests = () => (queue ??= new Queue("digest", { connection }));

// the worker runs the job as job() says: signed in as service 1
export const digestWorker = () => new Worker("digest", digest, { connection });
```

```ts
export const POST = route(async () => Response.json({ id: (await digests().add("digest", {})).id }, { status: 202 }));
```

Start the worker in a process of its own, at its top level, not inside a request.

### Work after the response: after() (preview)

*Preview: this part may change in any release, and is not part of the 1.0 freeze.*

Next's `after()` runs once the response is sent. In a route handler, a transaction there signs in as the
request's user, as every other one does: Next lets `after()` read the request's headers there, so `user()` still
finds the session. Wrap the work in `job()` to act for a service instead. Neither carries over to the next
request, on the same connection or not.

```ts
import { after } from "next/server";
import { job } from "@rowstile/client";
```

```ts
  after(async () => {
    const notes = (await db.note.findMany({ select: { id: true }, orderBy: { id: "asc" } })).map((n) => n.id);
```

```ts
  after(job(["service", 1], async () => {
    const projects = (await db.project.findMany({ select: { id: true }, orderBy: { id: "asc" } })).map((p) => p.id);
```

In a page, Next lets `after()` read nothing of the request: the work fails, in Next's words "used `headers()`
inside `after()` while rendering. This is not supported." Read the user while the page renders, and act for
them in the work with `actingAs` (the page inside `<Suspense>`, as above):

```tsx
async function Later() {
  const reader = await requestUser();
  after(() => actingAs(reader, () =>
    db.$executeRaw`INSERT INTO app.inbox (sender_id, recipient_id, body) VALUES (${Number(reader ?? 0)}, 2, 'later')`));
```

## Migrations

The policy ships as Prisma migrations. `rowstile migrate` writes the next one (only what changed since
`db/policy.lock`), and Prisma applies it with the others; Prisma's own diff then shows no change.

```sh
npx rowstile migrate --name note_comment_view   # after changing the policy: <time>_authz_note_comment_view
npx prisma migrate deploy                       # a new database, CI, production
npx rowstile migrate --check                    # in CI: exit 1 if a policy change has no migration

# the development database, which `rowstile dev` pushed that change to: there, for each migration after the
# first, in place of deploy (the first, the whole policy, deploys over what dev pushed)
npx prisma migrate resolve --applied 20261006215008_authz_note_comment_view   # the migration's folder
```

Without `--name` the folder is `<time>_authz_policy`.

While you edit, `npx rowstile dev` checks, pushes to the development database, runs the tests and rewrites
`src/authz.gen.ts` on every save (and writes the migration once you stop editing).

The development database then holds what `dev` (or `rowstile push`) pushed, not what its migrations left.
The first migration, the whole policy, applies over that, as anywhere: it needs no `resolve` (on one Prisma has
applied, `resolve --applied` answers P3008, "already recorded as applied"). A later one stops there with AZ607,
"this database already holds what this migration brings", which Prisma keeps as a failed migration:
`resolve --applied` is what the message asks for, and it clears that too. Or rebuild the database from the
migrations. Production and CI only ever take migrations.

## Tests

The policy's own tests run with `rowstile test`. In Vitest, the matchers read the refusal:

```ts
import { expect } from "vitest";
import { matchers } from "@rowstile/vitest";

expect.extend(matchers);
```

```ts
  await expect(actingAs("3", () => db.$transaction((tx) => tx.note.update({ where: { id: 1 }, data: { body: "x" } }))))
    .rejects.toBeRefused("update", "note.edit");
  await expect(actingAs("2", () => db.note.delete({ where: { id: 1 } }))).rejects.toBeNotFound();
```

`databasePerWorker(ownerUrl)` copies the migrated test database once for each Vitest worker, so tests that
write don't meet each other. The copy needs nobody connected to the original; where the service keeps one
(Neon does, for minutes after a connection), it says so and what to use instead ([Managed
Postgres](/managed-postgres.md)).

## Users and groups from an identity provider (SCIM)

An identity provider (Okta, Microsoft Entra ID) can write its directory's users and groups to the app. It sends
SCIM requests, and the database answers them
([Identity](/reference/identity.md#users-and-groups-from-an-identity-provider-scim) has all of it). The app's
part is a catch-all route:

```ts
// app/scim/v2/[...scim]/route.ts
import { scimRoutes } from "@rowstile/next";
import { db } from "@/db";

export const { GET, POST, PUT, PATCH, DELETE } = scimRoutes({ calls: db.$authz });
```

The handlers pass each request on and answer what the database says. The bearer token alone says who asks:
`user()` isn't asked about these requests, and their transaction signs nobody in, whatever session a request also
carries. So keep the route out of what a `proxy.ts` of yours asks a session for: a provider has none. An error
(settings that no longer hold, your function failing) is answered 500 in SCIM's words and logged with
`console.error`. The address of what a request made (`Location`) is the route's own, on the host the provider
asked: `x-forwarded-host` and `x-forwarded-proto`, which Next.js sets from the request when no proxy did. The
handlers read a request's whole body before the database looks at the key, as any route that reads JSON does: how
large a body may be is the server in front's to cap.

Users are rows of your table, with columns rowstile doesn't know, so a function of yours makes and changes them.
It is a migration, written by hand: the database calls it with the owner's rights, so it must be the owner's
own. Here the users' table gets three columns for what a provider says: an email, whether the user is active, and
the user as the provider wrote it, which the endpoint answers when the provider reads the user back. The
function never gets a password. A request may say less of a user than the app has (a PUT without an email, a
PATCH of a user the provider never wrote), so it keeps what the request says nothing of:

```sql
-- prisma/migrations/20261116090000_scim_user/migration.sql
ALTER TABLE "app"."users" ADD COLUMN "email" TEXT,
  ADD COLUMN "active" BOOLEAN NOT NULL DEFAULT true,
  ADD COLUMN "scim" JSONB;

-- the keys of the users it makes
CREATE SEQUENCE "app"."provisioned_user_ids" AS integer START 1000;

-- p_id NULL: a user to make; p_resource NULL: one to delete; else one to change. It answers the user's key.
-- rowstile calls it as the tables' owner, whose own it is: the app role has no right to write app.users
CREATE FUNCTION "app"."scim_user"(p_id text, p_resource jsonb) RETURNS text LANGUAGE plpgsql AS $f$
DECLARE v_id text;
BEGIN
  IF p_resource IS NULL THEN
    DELETE FROM "app"."users" WHERE id = p_id::integer;
    RETURN p_id;
  ELSIF p_id IS NULL THEN
    INSERT INTO "app"."users" (id, name, email, active, scim)
    VALUES (nextval('app.provisioned_user_ids'), p_resource->>'userName', p_resource#>>'{emails,0,value}',
            coalesce((p_resource->>'active')::boolean, true), p_resource)
    RETURNING id::text INTO v_id;
  ELSE
    -- a user changed: what the provider says nothing of stays as the app had it
    UPDATE "app"."users" SET name = p_resource->>'userName', scim = p_resource,
      email = coalesce(p_resource#>>'{emails,0,value}', email),
      active = coalesce((p_resource->>'active')::boolean, active)
    WHERE id = p_id::integer RETURNING id::text INTO v_id;
  END IF;
  RETURN v_id;
END $f$;
```

The model in `schema.prisma` gets the same three fields (`email String?`, `active Boolean @default(true)`,
`scim Json?`), so Prisma's own diff shows no change. This app's users have no default for their key, hence the
sequence.

Then the owner says what the endpoint reads, once, in psql or in a migration after the policy's own
(`authz.set_scim` comes with the policy). The call replaces the stored settings, and each is checked as it is
set:

```sql
SELECT authz.set_scim('{
  "principal": "user:9",
  "group": "project.member", "group_name": "name",
  "user_name": "name", "user_active": "active", "user_resource": "scim",
  "user_function": "app.scim_user"
}');
```

- `principal` is whose API key signs the provider in. Here it is user 9, a user the app holds for the provider
  and nobody signs in as; a row of a type that signs in (this app's `service`) does as well. The row is there
  before the settings name it.
- `group` is what the provider's groups are in the policy: here a project's members, so a group's PATCH writes
  `app.members` as `authz.sync_members` would, with its line in the audit trail, and the policy follows at once.
  With no `group_function` the provider finds the projects the app has by their name, and can't make one.
- The `user_` settings name the users' columns and the function above.

The provider's bearer token is an API key of that principal, made once:

```sql
BEGIN;
SELECT authz.act_as('user', '9');
SELECT authz.create_api_key('the identity provider');   -- shown this once: the provider's bearer token
COMMIT;
```

Give the provider the route's address (`https://app.example/scim/v2`) and that key. A user the provider
deactivates stays, with `active` false: what such a user may do is the policy's to say, and
`type user = app.users where {active}` makes one nobody. `rowstile lint` says when a setting no longer holds (a
column renamed, the function gone): a request that meets it is an error, the 500 above.

On another framework, `scim(method, path, query, body, token)` of the driver's calls (`db.$authz`,
`authz(pool)`) asks the database, and the route is yours: pass on the method, the path below the route, the
query string and the body as they were sent and the bearer token, and answer the `status`, the `body` and the
`location` that come back.
