RLS guards org boundaries; the table classification decides deletion

In OwlAI SaaS, many organizations share one PostgreSQL database, and one person can belong to two of them. If each query is left to separate organizations, this setup breaks in two places. One query missing its organization condition carries a fact learned in organization A's conversations into organization B's. And when an organization is deleted, a table added later can be left off the cleanup list, so copies of the deleted organization's conversations stay in the database.

Both failures have the same cause: someone has to remember which tables belong to an organization. So that list lives in one classification table in the code. The database applies row-level security (RLS) to every table the classification marks as organization-owned, the cleanup plan for deleting an organization is built from the same set of tables, and tests check that the RLS targets and the cleanup plan never drift from the classification. This post covers how we hand the organization boundary to the database, and the cleanup that keeps deleted memory out of every table.

#Personal memory belongs to the organization, not the person

The classification puts every table in the metastore into one of four groups.

Personal memory was the call that could have gone either way. It is memory about a person, so tying it to the account would spare someone in two organizations from saying the same thing twice. But what goes into personal memory is facts learned in that organization's conversations. This person's account ID in organization A's issue tracker is a fact about organization A, and account-level memory would load it into organization B's conversations too. So personal memory is one row per (person, organization) pair and classified as organization-owned. The same person has separate memory in each organization.

#The database applies the organization condition, not each query

The common approach is for the application to add WHERE org_id = ? to every query and wrap the repository functions so the condition is added automatically. It is simple and does not depend on database features. But a single SQL statement that bypasses the wrapper, or a join that forgets the condition on one side, returns another organization's rows. Chat, playbooks, the admin console and background jobs all read the memory tables, so every new path is another chance to miss it.

So the database enforces the boundary on organization-owned tables.

• The application's database connections run as the organization role from the moment they connect. There is no window in which a connection holds the table owner's privileges, and this role cannot bypass RLS.
• Each transaction sets the current organization on the session. The organization travels as an explicit function argument, bundled with the connection details, and never through a context variable. Workers have no request context, and a path that falls back to a default organization when the context is empty is exactly the accident RLS exists to prevent.
• On writes, the database fills the organization column with the current organization. An INSERT from a path that never set the organization fails because the column is empty. Rows never pile up silently under another organization or none at all.
• On reads, the RLS policy filters rows, so another organization's rows are invisible whatever the query says. A session that never set its organization gets an error, not zero rows, when it reads an organization-owned table. Zero rows would let the code that forgot the organization pass as "no results"; an error exposes it on the spot.
• RLS is enforced on the table owner too. If an owner connection skipped the policies, any code running on it would sit outside the boundary.

PostgreSQL's foreign-key checks ignore RLS. If a foreign key points only at the parent's ID, a row in organization A can point at a parent row in organization B. So foreign keys on organization-owned tables cover two columns together: (parent ID, organization). Child tables also keep their own organization column instead of borrowing it from the parent, because RLS policies are evaluated per table and cannot check the organization through the parent. On mixed tables, an organization session can only read the rows the platform seeded.

#Cross-organization jobs read only a work list and write as the row's organization

Some jobs look after the whole install and cannot be tied to one organization, such as the retention job that deletes old conversations and the job that runs scheduled playbooks. These jobs get a separate platform access that opens every organization's rows, with these rules.

1. Platform access runs one query that picks the work and returns only ID, organization, status and time columns.
2. Writes to the picked rows reopen a session as the organization that owns each row.
3. Conversation text, meeting transcripts and document text are never read through platform access.

Opening platform access requires one of a fixed set of reasons. For each reason, a list records the places in the code allowed to use it, and a test checks that this list matches the actual call sites in the source. Opening access from a new place means editing that list first, so a reviewer only has to read changes to the list to see where the boundary was newly opened.

#A new table does not merge until it is classified

Even with the database guarding the boundary, one organization table without RLS sits outside it. So three tests reconcile the classification with the real database.

The per-table isolation check tests five things on each table. In organization B's session, A's rows show up as zero rows. In B's session, an INSERT that names A's organization is rejected. A session with no organization set gets an error on read. Platform access sees both organizations' rows. In B's session, a child INSERT that points at A's parent row fails with a foreign-key violation. The targets come straight from the classification, and that set must equal the set of tables with RLS enabled in the database. A new memory table joins the check without any separate registration, and forgetting to classify it or to add RLS fails the pre-merge gate.

#Deleted conversations leave search first and lose their copies after a grace period

When a user deletes a conversation, it is hidden first, and the retention job deletes the row after a 30-day grace period. Conversations with no activity for a year go through the same path. Throughout the grace period, past conversation search never returns chunks from hidden conversations (“Load personal memory on every turn, and rewrite it whole”).

The thing easiest to miss in a hard delete is the past conversation chunks. A chunk is a copy of a turn's question and answer kept for search, and it has no foreign key to its conversation, so deleting the conversation does not cascade to it. Left behind, it would keep surfacing the deleted conversation's content through memory search. So every place that hard-deletes conversations (conversation retention, playbook run cleanup, playbook deletion) calls the same hard-delete function, and that function deletes the chunks too. If only one place knew to "delete the chunks along with the conversation", copies would survive in the others.

Files attached to a conversation (outputs, attachments) are deleted after the row deletion commits. If the files went first and the transaction rolled back, a live conversation would lose its files. A conversation that became the source of a playbook follows that playbook's lifetime, so the retention job does not delete it, but it stays out of search while hidden.

Deleting an account cascades to its personal memory and past conversation chunks. Table notes are the organization's knowledge about its data, so they stay, with only the author field cleared (“Corrected column meanings, kept as table notes for the next user”).

#The organization deletion plan uses the same set of tables as the classification

A closed organization is not deleted right away. It gets a retention period (organization.closingRetentionDays, 30 days by default and at minimum), during which members can only read, and resubscribing brings everything back. When the period ends, the worker's deletion job follows the cleanup plan, which assigns one treatment to each organization-owned or mixed table.

Everything memory-related is deleted. The audit log is append-only and the application's database roles cannot delete from it, so it stays until the retention job removes it after its retention period.

The plan's set of tables must equal the classification's set of organization-owned and mixed tables, and the test checks set equality, not subset. A subset check only confirms that the tables in the plan exist, so it still passes when a new organization table is missing from the plan. With set equality, adding a table to the classification without a deletion treatment fails. Two more checks work the same way: every object storage purpose (documents, meeting audio, sandboxes and so on) must have an organization-deletion handler, and no retained table may point at a deleted table through a foreign key.

The deletion order follows from where each target is found.

1. Cancel the organization's queued jobs.
2. Delete objects uploaded to the external speech-to-text service first. Once the database record is gone, there is no way to find the remote object.
3. Delete files in object storage. Their keys live in database rows, so files go before rows.
4. Delete or anonymize database rows in reverse foreign-key order, in fixed-size batches per table. If the job stops partway, the next run picks up from the remaining rows.

Rows are deleted in a session opened as that organization. Because of RLS, the deletion job cannot reach other organizations' rows, and it needs no platform access. How many rows were deleted from each table goes into the platform audit log; the deleted content does not.

Accounts that belonged only to the deleted organization are kept, and only their personal provider keys are deleted. Closing an organization is not leaving the service, so the same account can accept invitations from other organizations. Personal memory is organization-owned and already gone with the organization, so nothing from the old organization follows the account.

#Design choices

A separate schema or database per organization gives stronger isolation, and deleting an organization becomes dropping one schema. In exchange, migrations run once per organization, and connection pools and background jobs split by organization. We chose shared tables separated by RLS. The cost is that every organization table needs its own organization column and foreign keys that include the organization, and deleting an organization needs a plan with a treatment for each table. To keep that cost from depending on anyone's memory, the classification, RLS and the deletion plan are tied together by set-equality tests.

Platform access cannot go away, because jobs like retention have to walk every organization. So the places that open it are bound to a list with reasons, and those places read only a work list.

The cost of making personal memory organization-owned is that someone in several organizations has to tell each one about themselves again.

If several organizations share one database in your system, keep the list of organization-owned tables in one place in the code, build the RLS targets and the deletion plan from it, and have tests compare them by set equality. A new memory table that slips past the boundary or the deletion is then caught before it merges.