How one company's data is kept from another's

Summary

Three layers, all in the database, so a mistake in application code cannot leak a company's data. Each is covered by a test in packages/db/src.

Why it matters

Babul holds copies of its customers' products. The worst bug it can have is one company seeing another's.

Implications

• Every row a company owns carries clientId.
• A child's foreign key includes clientId, and productId where it has one. A page cannot belong to one company and point at another's product. A journey link cannot join pages of two products.
• Row-level security hides other companies' rows. The app sets the company once per transaction with withClient(db, clientId, work). With nothing set, a query returns no rows.
• The app connects as a role that owns nothing, so the rules apply to it: the web app as babul_app, the worker as babul_worker. Migrations and system jobs connect as the owner. See .context/ops/setup.md.
• A role's test password is never readable by the web app. It is sealed (AES-256-GCM, key in the environment) before it is written, and the web app's database role has no SELECT on the column that holds it. Only the worker's role reads it, and the worker opens it only in memory. A preview visitor sees no sign-in at all.
• Four tables can be read before a company is known: Client, Membership, Invite and PreviewLink. Anyone may read them; only the company may write them.
• Optional links are checked by a trigger. A foreign key check ignores row-level security, so a trigger proves the linked row is in the same company.
• Prisma drops a foreign key it did not write. So every foreign key is declared in schema.prisma. Checks, partial unique indexes, triggers and policies are hand-written at the bottom of the migration, and Prisma leaves them alone.
• Not enforced by the database: that a company always has an owner. The code that removes a member must check it.
• AI actions and invoices outlive what they point at. Deleting a product keeps its AI actions, so the meter cannot be reset. A company with invoices cannot be hard-deleted by accident.

Related

• 2026-10-12-stack
• access-levels · plans

Sources

• packages/db/prisma/schema.prisma
• packages/db/prisma/migrations
• packages/db/src/tenant.ts
• packages/db/src/tenant.test.ts
• packages/db/src/constraints.test.ts
• packages/db/src/roles.test.ts
• packages/db/src/role-secret.ts

Products inside a company

A second rule sits on top of the company rule: 2026-10-13-product-access. A product is open to everyone in the company, or only to its chosen people and the owner. It is enforced the same way, by policies, on Product and on every table with a productId.

• withMember(db, clientId, userId, work) is what the web app uses. It sets who is asking. A test fails if the web app ever uses withClient.
• withClient(db, clientId, work) sets no person and sees every product of the company. It is for the capture worker.
• The policies call two functions that run as the owner of the tables, app_can_open and app_open_products. They read only who is in the company and each product's chosen people (ProductMember); the product's access is passed in, because a product being made is not yet visible to a query.
• Every product has one Open role, made by the database with the product and never deleted. A capture runs as one of the product's roles.