Tenant isolation and the read-only role

Business view

Dentolize is one shared database serving many clinics. This feature adds a brand-new way to reach into that database — from Claude, on Anthropic's servers, at machine speed rather than a person clicking through a browser — which raises the stakes on a rule that has always mattered: one clinic must never see another clinic's data.

Rather than relying on every future engineer to remember to write the correct filter in every query forever, this PR pushes both guarantees for the MCP server down into the database itself:

• It can't write. The database login the MCP connects as physically has

no permission to insert, update, or delete anything, in any table. This isn't a setting that application code checks — Postgres refuses the statement before Dentolize's code is even involved.

• It can't see across clinics. Every inventory table now has a database

policy that only lets this login see rows belonging to the one clinic currently in scope. If something is ever misconfigured and that "current clinic" is never set, the result isn't an error and it isn't every clinic's data — it's zero rows. The failure mode is silence, not a leak.

Technical view

Two separate database logins, doing two separate jobs

Design decision A8 in OAUTH-DESIGN.md splits authentication from data access into two logins:

• mcp_auth — created by packages/claude-mcp/sql/002_split_auth_role.sql.

Can read only the three OAuth tables. Used exclusively by src/auth/oauth.ts to validate a bearer token.

• mcp_readonly — created by sql/001_create_readonly_role.sql. Can

SELECT on every business table, and is explicitly denied the OAuth tables. Used exclusively by the tools (db/pool.ts).

The two pools are never swapped or shared (index.ts:29-33). The reasoning in OAUTH-DESIGN.md is blunt about why this matters: Claude itself can never run arbitrary SQL — it can only call tools and read what they return — so the actual risk this split defends against is our own future tool code accidentally doing select * from "OAuthToken". With one combined role, the database would simply allow that. Split, a careless tool fails on a permissions error instead of on a reviewer's vigilance.

What mcp_readonly physically cannot do

sql/001_create_readonly_role.sql:76-78 — deliberately not granted: INSERT, UPDATE, DELETE, TRUNCATE, sequence usage, or EXECUTE on any function. The role also gets, at the Postgres role level:

• default_transaction_read_only = on — belt-and-braces, not the real

protection (a session can override its own default).

• statement_timeout = '10s' — this login points at the same primary

database that's taking clinics' payments; a badly-shaped report must not be able to sit on it.

• idle_in_transaction_session_timeout = '30s' — kills a connection that

opens a transaction and goes quiet, which would otherwise hold locks and pin old row versions indefinitely.

A subtlety worth knowing: ALTER DEFAULT PRIVILEGES ... GRANT SELECT ON TABLES TO mcp_readonly (line 73-74) is what stops this role from silently losing visibility into tables a future Prisma migration adds. Without it, a new table would need this script re-run by hand or the MCP would simply not see it.

Row-level security: fails closed, not open

packages/claude-mcp/sql/003_rls_inventory.sql enables RLS on eight tables that carry companyId directly (InventoryItem, InventoryTransaction, InventoryOrder, Supplier, and others), plus two tables that carry no tenant column of their own — InventoryProcedureUsage and PurchaseOrderItem — whose policies walk up to a parent table (InventoryItem or Expense → Branch) to find the clinic. The file's own comment calls these two out specifically as "exactly where a leak hides: a query joining straight to them looks harmless and is not filtered by anything."

Every policy has the same shape:

CREATE POLICY mcp_tenant_isolation ON "InventoryItem" FOR SELECT TO :"tool_role"
  USING ("companyId" = current_setting('app.company_id', true));

FORCE ROW LEVEL SECURITY is deliberately not used — it would also restrict the table owner, breaking seeds and migrations, which run as the owner. This means RLS only ever applies to mcp_readonly; the existing GraphQL API, which connects as the owner, is completely unaffected by any of this.

app.company_id: the one setting everything depends on

packages/claude-mcp/src/db/tenant.ts is called out in its own top comment as "the most safety-critical file in the package." Its withTenant() function is the only sanctioned way any tool touches the database, and it does four things every time, in order:

1. Validates the companyId looks like a UUID before it goes near SQL

(InvalidTenantError) — belt-and-braces on top of parameterized queries.

1. Checks the connection didn't arrive with app.company_id already set

(TenantLeakError, tenant.ts:99-107) — connections are recycled between requests from different clinics by the pool, so a value left behind by a previous request would be a live cross-tenant leak. This check turns that scenario into a loud, immediate error instead of a silent one.

1. Opens the transaction explicitly begin read only, and sets

app.company_id with set_config($1, $2, true) — the third argument, true, is what makes this transaction-scoped (SET LOCAL, effectively). Plain SET would be session-scoped and would leak into the connection pool exactly the way step 2 checks for.

1. Runs the caller's query, then commits — releasing the setting — or rolls

back on any error, without letting a failed rollback mask the original error (tenant.ts:140-143).

"Zero rows" means "unscoped," not "empty" — a real bug this caused

BUGS.md §5 documents exactly this trap being hit during development: a connection to mcp_readonly that hadn't set app.company_id returned zero rows from a table that genuinely held 60 of them — indistinguishable, from the caller's side, from an empty table. current_setting(..., true) returns NULL when unset, and comparing anything to NULL in SQL yields no rows — which is the correct, intended failure mode for this design, but only if everyone who reads a "zero results" answer from this system knows to ask "is the tenant actually scoped?" before assuming the data isn't there.

Two more traps this design specifically catches

• Enum arrays arriving as raw strings. tools/permissions.ts:36-44:

node-postgres has no parser for Postgres enum arrays, so a plain select permissions from "Group" hands back the literal string "{VIEW_INVENTORY_USAGE,VIEW_PATIENT}". .includes() on a string does substring matching — so permissions.includes('VIEW_INVENTORY') was true for anyone holding VIEW_INVENTORY_USAGE, because one name is a textual prefix of the other (BUGS.md §2, found by a person clicking through as a nurse and seeing data they shouldn't). The fix casts explicitly — g.permissions::text[] — and asserts the result really is an array before ever calling .includes() on it.

• UTC timestamps read back as local time. Every timestamp column in

this schema (no timezone) is written as UTC by Prisma but read back as local server time by node-postgres. BUGS.md §1 documents two bugs from this single root cause — every OAuth token looking already-expired on a server east of UTC, and fixture rows landing outside their own query window. The fix, applied everywhere in this PR: never compare these columns in JavaScript — always ask Postgres, which knows what it actually stored.