Keeping each firm's data apart

How a row decides who may see it

Every row knows which firm, and usually which project, it belongs to, so the check is one quick lookup. Every table carries org_id, and the project-scoped ones also carry project_id, denormalized so a policy is one indexed check. The policies call a small set of functions — lt_me(), lt_my_staff_orgs(), lt_my_projects(), lt_has_org_role(org, roles…), lt_is_platform_admin() — which run as security definer so they can read the membership tables the caller cannot.

Staff (employees and AI assistants) see their organization's projects. Outside parties — clients, subcontractors, vendors, partners — see only projects they were explicitly granted, never the staff directory or the firm's finances. Saved queries (views) are declared security_invoker, so a view can't read around the policy on its base tables.

Files follow the same rule

A file is visible to exactly the people who can see the record it belongs to. A storage object has no org_id, so the storage policy asks the database which record references the path — lt_evidence_ref(name) returns the project and organization that own it — and applies the same visibility as that record. An upload nothing references is visible to nobody but the uploader and platform staff.

A child cannot leave its project

A guard on nine child tables refuses a row whose org_id differs from its project's org_id, so moving a project moves its record and nothing is stranded.

Privilege does not escalate

There is no client write path to platform_admins. An org admin cannot grant themselves a role on another organization's project.

Proven, not described

The tests build the real database in memory and query it as each kind of person, with the security rules switched on (the real migrations, an in-process Postgres, RLS active). No network, no mocks.