Schema-per-tenant setup

select set_config('search_path', 'tenant_acme', true);

# TenancyJS Integration Agent - Express 5 + Drizzle, schema-per-tenant (PostgreSQL)

You are an integration agent. Wire fail-closed, schema-per-tenant multi-tenancy into THIS Express +
Drizzle app on PostgreSQL with TenancyJS - one schema per tenant in a shared database, integrating with
whatever already exists - do NOT assume a greenfield app. Execute steps in order; after each, verify it
applied before continuing. If a step does not apply to this repo, say so and adapt. Do NOT invent API
names - fetch the linked doc if unsure.

Source of truth:
- https://tenancyjs.pages.dev/docs/adapters/drizzle
- https://tenancyjs.pages.dev/docs/strategies/schema-per-tenant
- https://tenancyjs.pages.dev/docs/guides/provisioning/drizzle
- https://tenancyjs.pages.dev/docs/integrations/express
- https://tenancyjs.pages.dev/docs/guides/testing-isolation

## Step 0 - Assess the app (change nothing yet)
- Report the Node version (abort if < 24), the `express` and `drizzle-orm` versions, and confirm
  PostgreSQL and the `pg` driver are available (schema-per-tenant is PostgreSQL-only). If this is not an
  Express + Drizzle + Postgres app, stop.
- Inventory the Drizzle tables: list every table and mark each as tenant-owned or global/central.
- Find how the current user is determined per request (`req.user`, session, JWT) - the `authorize` hook
  will need it.
- Detect any EXISTING tenant concept (a `tenantId`/`orgId` column, a workspaces/orgs table, or an
  existing per-tenant schema naming scheme). If one exists, REUSE it - do not add a parallel one.
- Summarize what you found + the integration plan, and confirm the ISOLATION STRATEGY with the user:
  schema-per-tenant (this page) vs row-level vs database-per-tenant. If schema-per-tenant does not fit,
  point them to https://tenancyjs.pages.dev/docs/strategies/choosing and stop.

## Step 1 - Install
`npm install tenancyjs-core tenancyjs-adapter-drizzle tenancyjs-integration-express tenancyjs-identifiers drizzle-orm pg`
Verify the packages resolve before continuing.

## Step 2 - Placement on the tenant record
Each tenant row must carry a `schemaName` (e.g. `tenant_acme`). The store's `find(identifier)` returns
`[{ tenant: { id, schemaName }, status }]`. The schema name comes from the resolved tenant, never from
raw request input. Verify the store returns `schemaName` before continuing.

## Step 3 - tenancy.ts
- One exported `TenancyManager<Tenant>`.
- Define tenant tables as UNQUALIFIED `pgTable` (no schema prefix) - the adapter's transaction-local
  `search_path` resolves them to the active tenant's schema.
- The adapter in schema-per-tenant mode:
  ```
  const tenancy = createDrizzleTenancy({
    manager,
    database: createPostgresDrizzleBinding(drizzle({ client: pool })),
    strategy: "schemaPerTenant",
    schema: (t) => t.schemaName,
    centralSchema: "public",
    tenantTables: [{ table: posts }],
    // role: (t) => t.role, // optional: database-enforced sibling-schema denial
  });
  ```
  List EVERY EXISTING tenant-owned table in `tenantTables`; only add a new example table if the app has
  no domain tables yet.
- Keep tenant tables OUT of `centralSchema` (`public`) - a `public.posts` shadows the tenant tables and
  fails validation.
- A `TenantResolutionChain` (HeaderTenantResolver + the store above).
- Call `await tenancy.validate()` at boot; it refuses to run until the schemas and grants exist.
Verify `tenancy.validate()` passes before continuing.

## Step 4 - Express wiring
`app.use(createExpressTenancyMiddleware({ manager, resolver }))`; add an error handler for
`ExpressTenancyResolutionError` (400/404/500). Inside a request,
`tenancy.run((db) => db.table(posts).findMany())` returns only the current tenant's rows.
Verify a scoped request works before continuing.

## Step 5 - Provision each tenant's schema
Implement the provisioner hooks (see the Drizzle provisioning recipe at
https://tenancyjs.pages.dev/docs/guides/provisioning/drizzle): `provision` runs `CREATE SCHEMA
tenant_acme` and grants `USAGE` to the runtime role on a privileged admin connection (NOT the runtime
role); `migrate` runs your Drizzle migrations against that schema; `deprovision` drops it. Then:
`npx tenancyjs-cli tenant create acme --set schemaName=tenant_acme` ->
`npx tenancyjs-cli tenant provision acme` -> `npx tenancyjs-cli tenant migrate acme`.
Verify the schema and its tables exist before continuing. If the app already has existing tenants,
provision and migrate a schema for EACH of them before cutover - do not leave an existing tenant without
a schema.

## Step 6 - Central work
Cross-tenant/admin work runs against the central schema explicitly via
`manager.runInCentralContext(...)`. Never run tenant-aware queries with no scope.
Verify central work does not touch tenant schemas before continuing.

## Step 7 - Verify isolation (an isolated test - it does NOT touch app data)
Two TEST tenants in two TEST schemas (never real app tenants), same primary key: write a row under each,
assert querying as A never returns B's row, and that a tenant query with no scope THROWS. Follow the
testing-isolation guide. Verify the leak test passes before continuing.

## Step 8 - Roll out + verify
- `npm run build`, boot the app, run the two-tenant test. Report every file changed and any issue.
- Existing app: provision + migrate a schema for every existing tenant before cutover, then roll out
  incrementally - scope routes behind the middleware, keep admin/cross-tenant work in
  `runInCentralContext`, and treat any thrown unscoped-access error as a route you still need to scope.