ToborLife — Relational Database Design

Design and justification for the multi-tenant Postgres schema under tests/data/.

• sql/ — table structure only: CREATE TABLE, constraints, foreign keys, CHECKs, indexes. schema.sql is the combined, authoritative copy; the per-table *.ddl / *.sql files mirror it one table at a time.
• queries/ — everything behavioral: RLS policies, triggers, and reference query patterns. Nothing here changes table structure.

Apply order: sql/schema.sql → queries/triggers.sql → queries/rls.sql → queries/versioning_triggers.sql → queries/admin_lifecycle.sql → queries/set_revisions.sql → queries/group_revisions.sql → queries/invites.sql. rls.sql creates the tobor_app role that versioning_triggers.sql / admin_lifecycle.sql / set_revisions.sql / group_revisions.sql / invites.sql grant to, so it must come before them.

Requirements: PostgreSQL 18+ — the schema uses the native uuidv7() generator and a uuid_extract_version() CHECK on every UUID primary key. The application must connect as a non-superuser role (see RLS).

Timestamps: every time column is TIMESTAMPTZ, and database-generated values are UTC — schema.sql runs SET timezone = 'UTC' (make it permanent with ALTER DATABASE <db> SET timezone = 'UTC').

---

1. The tenant boundary

Every tenant is an organizations row. The tenant key is org_id, carried on every tenant-scoped table. Isolation is enforced in four layers, so a single mistake in one layer does not become a breach:

#	Layer	Guarantees
1	NOT NULL on every org_id	No row can hide from an org filter via NULL
2	Composite (id, org_id) unique keys on parents + composite FKs on children	A child can never reference a parent in another org
3	Membership FKs on actor columns	An actor (operator, creator, editor, grantor…) must belong to the org
4	Row-Level Security (queries/rls.sql)	The database — not the app — enforces "you only see/write your org's rows"

Polymorphic columns that cannot be foreign keys are guarded by triggers (queries/triggers.sql).

---

2. Key design decisions & justification

2.1 org_id is NOT NULL everywhere

A primary key on organizations.id only constrains that column. On child tables org_id is a foreign-key column, which is nullable by default, and Postgres FK validation skips NULLs — so org_id = NULL would be accepted and then slip past every WHERE org_id = … filter and every RLS policy. Explicit NOT NULL closes that hole.

2.2 Same-org foreign keys (composite FKs)

A plain FK(parent_id) → parent(id) lets a child in org A point at a parent in org B. Instead, parents carry UNIQUE (id, org_id) and children reference FOREIGN KEY (parent_id, org_id) → parent(id, org_id). The shared org_id column makes a cross-org reference structurally impossible.

Because Postgres uses MATCH SIMPLE, a composite FK is not enforced when the child column is NULL — so optional relationships still work: a clip with no recorded creation session, a version with no parent, an unattributed actor column, etc. The FK only says "if linked, same org."

2.3 Sessions belong to the org, not to a robot

A session is one operator's connection to the org. An org has many robots and one connection may touch several (or none), so pinning a single robot_id on the session would be lossy. The producing robot is recorded per episode instead. Sessions keep only org_id + operator_user_id. A session need not generate any episodes; an episode, however, always belongs to exactly one session (episodes.session_id NOT NULL).

2.3a A robot is a unit; its model is a shared catalog entry

robots describes one physical machine — its serial, api key, firmware version, liveness. What kind of machine it is lives in robot_models: manufacturer, model name, hardware revision, form factor, degrees of freedom, native frame rate, sensor payload. Those facts are true of every unit of a model, so storing them per robot would mean re-entering (and eventually contradicting) them on each row.

robot_models is a global catalog (§2.9) — no org_id, no RLS, like subscription_plans. A UR5e is a UR5e in every tenant, so the model is stated once and every org's units point at the same row; robots.robot_model_id is therefore a plain FK, not the composite same-org FK used between tenant tables (there is no org_id on the catalog side to match).

Three consequences worth stating: - Nullable link. A robot can be provisioned before anyone records what it is, and fleet rows predating the catalog have no model. robot_model_id is nullable by design; readers must handle NULL rather than assuming a join succeeds. - Retire, don't delete. The FK is plain RESTRICT, so the database refuses to drop a model any robot still references — correct, since those units and the episodes they recorded must stay readable. Withdraw a model with is_active = false. - Only a platform admin can add a model. That is the cost of the global choice: registering prototype or custom hardware is a catalog change, not a self-serve tenant action. The alternative (an org_id + composite FK) would let each org self-serve at the price of a duplicate row per tenant for every common model.

2.4 Episodes ↔ sets and sets ↔ groups are many-to-many

An episode can serve multiple sets, so a scalar episodes.set_id cannot represent it (one column = at most one set). Resolved with junction tables:

• episode_sets (episode_id, set_id) — episode ↔ set
• set_groups (set_id, group_id) — set ↔ group

Both link columns are NOT NULL (a link needs both ends) and both carry org_id with composite FKs into each side, so a link can never cross orgs. An entity with no link simply has no junction row, which is how "an episode/set/clip need not belong to a set/group" is represented.

Both junctions hold the membership as it is now. Their history — the exact list as it stood at any past point, citable by id so a training run can reference it — lives in a parallel pair of revision tables per side: set_revisions / set_revision_episodes for a set's episodes, and group_revisions / group_revision_sets for a group's sets (§2.6b).

2.5 Clips are references, not entities

A clip is a pointer to a trimmed span of its single source episode's video (trim offsets + the source S3 paths) and owns no set linkage of its own — set context comes from that episode via episode_sets. A clip must belong to an episode (episode_id NOT NULL).

• Creation session may differ from the episode's session. clips.session_id records the session the clip was created in, which can be a different, later session than the one that recorded the episode (record one day, clip the next). The episode is the same; only the session differs. session_id is optional and same-org.
• Deletion. The episode_id FK is plain — no ON DELETE CASCADE. Clip deletion is handled by the application, so an episode cannot be deleted while clips still reference it (the delete is blocked until they are removed or reassigned). Keeping a clip past its episode is done by promoting it into its own episode application-side, before the episode is deleted; the promoted episode records origin = 'promoted' and promoted_from_clip_id.
• Trash cascade. A standalone clip delete is a plain hard delete. But when an episode is trashed (§2.11), its clips are cascade-trashed via clips.deleted_at (clips are metadata only) and resurrected when the episode is restored. deleted_at on a clip is therefore only ever set by that cascade — which is why no per-clip "trashed-with" marker is needed.

The stored source paths let the common read path resolve the video without joining episodes.

2.6 Append-only version history

entity_versions stores one row per version; edits and rollbacks append rather than mutate. entity_id is deliberately not a foreign key so history outlives the live row. Consequences:

• org_id consistency can't be an FK, so the entity_versions_org_guard trigger checks it at write time (see triggers).
• A partial unique index one_current_per_entity guarantees exactly one live head (is_current) per entity.
• CHECKs constrain entity_type and operation to their allowed values.
• Capture is automatic (database-side). A capture_version trigger (in queries/versioning_triggers.sql) appends a version with no help from the application. clips, sets, and groups are captured on every INSERT/UPDATE/DELETE; episodes are captured only at a terminal extraction outcome — when status becomes 'processed' or 'failed', on delete, and on an admin revert — and never on the transient 'recording'/'uploading'/ 'uploaded'/'processing' states (so intermediate progress doesn't amplify writes). edited_by is read from the app.current_user GUC (set it alongside app.current_org; NULL if unset).
• Create a snapshot with create_snapshot(name, description, entity_types[]): freezes the current head of each matching entity into snapshots / snapshot_members (created_by from app.current_user). The only manual step is now thin; restore is below.
• Revert with revert_entity(entity_type, id, version_no). If the entity is live it UPDATEs it back to the snapshot (preserving id/org_id/created_at); if it was deleted it resurrects it by re-inserting the full snapshot under the original id (requires the snapshot's referenced parents to still exist). Either way the capture trigger records the result as operation = 'rollback'. Resurrect comes back live: a tombstone captured at purge holds the row's trashed state, so the resurrect path resets the trash columns (episodes.deleted_at/deleted_by/purge_after/storage_state, clips.deleted_at) — otherwise a past-due trashed row would be re-deleted on the next purge_trashed() run. (Trash is undone by restore_episode, not by revert.)
• Restore a whole snapshot with restore_snapshot(snapshot_id): it applies revert_entity to every captured entity in dependency order (sets/groups → episodes → clips, since the only versioned dependency is clip→episode), so a resurrected child never precedes its parent. It restores the captured set to its frozen state but does not delete entities created since the snapshot.
• Scope — only episode, clip, set, group are versioned (enforced by the entity_type CHECK + the four capture_version triggers). Versioning earns its write-amplification cost only where humans edit content and undo/rollback is a feature; the rest is excluded on purpose:
• Already logs/ledgers (audit_logs, role_change_logs, upload_parts, entity_versions itself) — versioning a log is meaningless.
• Identity / tenancy / billing (organizations, members, users, subscriptions, robots, sessions) — operational config, not content; where history matters it's handled by role_change_logs, member soft-delete, and audit_logs. Generic rollback of billing/membership would be unsafe.

• Junctions (episode_sets, set_groups, role_permissions) — state is implied by the entities they link; a link has no editable fields to roll back. (The content junctions' associations are still preserved across a delete/revert — not by versioning them, but by snapshotting them onto the entity's tombstone; see §2.6a.)

  Exception — the content junctions' membership is historised (§2.6b): episode_sets into set_revisions, and set_groups into group_revisions. Not by versioning the junctions, but by freezing the whole membership list into a separate immutable revision whenever it changes. The distinction is the point: what a customer needs is not "roll back one link", it is "the exact list I trained on, citable by id" — which a per-link version log cannot give you without replaying every row. role_permissions stays unhistorised; it is capability config, not a dataset, and role_change_logs already records who changed what. - Secrets (refresh_tokens, *_api_key_hash) — keeping a history of old credential hashes is a security anti-pattern.

Adding a fifth content table later is a two-line change (extend the CHECK + add a capture_version trigger). - Hard restore (point-in-time reset) with restore_snapshot_hard(snapshot_id): additive restore plus deletion of every versioned entity the snapshot did not capture (and the non-versioned children/junctions of removed episodes), children-first to satisfy NO ACTION FKs. Destructive and RLS-scoped to the current tenant; it guards that the snapshot exists in this org so a wrong id can't be read as "empty" and wipe the tenant. Never run it on a superuser/BYPASSRLS connection.

2.6a Preserving junction links across delete & revert

The junction tables (episode_sets, set_groups) are deliberately not versioned (§2.6) — a link is just a pair of ids with no editable state. But that created a gap: when you delete a content entity and later revert it, its associations were lost. This section is how that's closed without versioning the junctions.

Why a tombstone snapshot, not a junction version log. A content entity can only be deleted after its junction rows are gone (the junction FKs are RESTRICT, §2.2), so the app removes the links first, then the entity. By the time the capture_version trigger fires AFTER DELETE on the entity, the links have already vanished — a trigger literally cannot see them. So the capture has to happen before the junctions are removed, which means it belongs in the delete path, not a trigger.

The mechanism — delete_episode / delete_set / delete_group (admin_lifecycle.sql). Each helper, in one transaction: reads the entity's current links into a JSONB, publishes it on the app.captured_links GUC, deletes the junction rows, then deletes the entity. The capture_version DELETE path reads that GUC and writes the snapshot onto the tombstone's new entity_versions.links column. Each entity captures only the links that exist for it (the set is the hub: episodes link to sets and groups link to sets, but episodes and groups never link to each other — see the ER map in §7):

Deleted	links shape	Re-links on resurrect to surviving…
episode	{"sets":[…]}	sets
group	{"sets":[…]}	sets
set	{"episodes":[…],"groups":[…]}	episodes + groups

Replay on resurrect. revert_entity resurrecting a deleted entity re-inserts the junction rows from links, but only for ends that still exist (re-linked episodes must also be live, not in the trash) — the same "re-join the survivors" rule the episode trash uses. If the other end was deleted in the meantime, that link is simply not restored. links is only written on 'delete' tombstones, so reverting a live entity to an old version leaves its current links untouched; restoring associations is specifically the undelete path (revert to the delete tombstone). A snapshot-based resurrect (restore_snapshot) freezes a live head, which carries no links, so it restores fields but not associations — an accepted limitation.

No silent loss — the require_link_capture guard (triggers.sql). A helper that can capture links isn't enough; a raw DELETE FROM sets … would still drop them unrecorded. So a BEFORE DELETE trigger on episodes/sets/groups rejects any delete that isn't running with a capture context set, forcing the helper path. It is scoped to the application role (same trust model as RLS, §3): a superuser/owner — admin, migration, test teardown — is exempt, and bulk teardown run by the app role (delete_org, restore_snapshot_hard, purge_trashed) sets app.captured_links = 'skip' to pass without per-entity capture (those entities are being permanently discarded, so preservation is moot). Net effect: under the app role you cannot delete a content entity in a way that silently loses its links — you either go through a helper (which records them) or it's an explicit, flagged bulk teardown.

2.6b Membership revisions — citable dataset versions

The junctions answer "what is in this set / group now". Customers who train models need something else: "what was in it when I trained, months ago, after I have since edited it". §2.6a does not cover this — a tombstone only preserves links across a delete, and an unlink leaves no trace at all.

The shape. The junction remains the live, mutable membership; every change to it freezes an immutable manifest into a pair of revision tables. The customer pins the revision id against their training run and can retrieve that exact list forever after. The same mechanism is applied once per junction side, with the container as the subject and its members frozen:

Live junction	Container	Frozen members	Revision tables
episode_sets	set	episodes	set_revisions + set_revision_episodes
set_groups	group	sets	group_revisions + group_revision_sets

The two histories are independent — separate revision_no sequences, separate transaction markers — so re-tagging a set into a different group never disturbs the dataset revisions a training run cites, and vice versa. They compose rather than duplicate: a group revision freezes which sets, and group_revision_set_revisions() resolves each of those to the set revision current at the same instant, giving the full group → sets → episodes picture without either table storing the other's data.

Design points worth knowing (full detail in SCHEMA_REFERENCE.md §4.5a / §9.7):

• Automatic and deferred. A DEFERRABLE INITIALLY DEFERRED constraint trigger on each junction fires at COMMIT, so it sees the transaction's final state and mints one revision per container per transaction — bulk-adding 200 episodes to a set gives one revision, not 200; linking 50 sets to a group likewise. A no-op transaction (add then remove the same member) mints nothing.
• A revision is not visible inside the transaction that caused it. It exists only after COMMIT. This surprises people writing tests.
• No FKs on the container or member ids. Same reasoning as entity_versions.entity_id: a citation has to outlive delete_set() / delete_group(), and a manifest has to outlive purge_trashed() hard-deleting the episode or delete_set() removing the set — which is exactly the moment the customer most needs the record. Departed members are reported as 'trashed' / 'purged' (episodes) or 'deleted' (sets), never dropped from the manifest. Sets have no trash window, which is why the set-side manifest has three availability states and the group-side has two.
• Append-only, enforced in the DB. UPDATE/DELETE are rejected under the app role. A dataset version anyone can quietly rewrite is not a citation.
• Purely additive. No existing query, endpoint, or read path changes — this is why it was preferred over making the junctions themselves temporal, where every forgotten WHERE valid_to IS NULL would silently leak removed members into a live training job.
• A revision records membership, not the row. Editing a set's name writes sets, not episode_sets, so it does not mint — that edit is captured by entity_versions (§2.6) and stamped on sets.updated_at. The three mechanisms are deliberately disjoint: updated_at = when the row last changed, entity_versions = what the row used to say, revisions = who was in it.

2.7 Actor columns enforce org membership

Columns recording who did something (sessions.operator_user_id, *.created_by, entity_versions.edited_by, resource_access_grants.granted_by, role_change_logs.target_user_id/changed_by_user_id, organization_members.invited_by/removed_by, user_permissions.granted_by) do not reference the global users table directly. They reference organization_members(user_id, org_id) via composite FK, which requires the actor to be a member of that org. This also added UNIQUE (user_id, org_id) to organization_members (a user joins a given org at most once) and gave user_permissions its own org_id so it uses the same direct FKs and RLS policy.

2.8 Members are soft-deleted

Routing actor columns through organization_members means a member can't be hard-deleted while any session/log/version/grant references them. Rather than lose that history, memberships are soft-deleted: removed_at IS NULL means active. Re-adding a removed member reactivates the same row (the UNIQUE (user_id, org_id) keeps it one row per user per org). Authorization checks add removed_at IS NULL; a partial index supports that hot path. See queries/membership.sql.

2.9 Global (non-tenant) tables

Intentionally have no org_id and are not under org RLS:

Table	Why
subscription_plans	Shared plan catalog
robot_models	Shared hardware catalog — a UR5e is a UR5e in every tenant
roles, permissions, role_permissions	Shared capability catalog
users	Global identity; a user may belong to many orgs
refresh_tokens	Keyed to a user, not an org

organizations itself is RLS'd by id (a tenant sees only its own org row).

2.9a Row timestamps: created_at everywhere, updated_at where rows change

Two different questions, two different columns, both database-generated:

• created_at — NOT NULL DEFAULT now() on every table. It is the row's birth certificate and never moves. Where a table already had a domain-specific creation stamp, that column is the created_at and no second one was added: organization_members.joined_at. Where a table has a stamp that means something else, both exist and are distinct — sessions.started_at is when the operator's connection opened (app-set, nullable), sessions.created_at is when the row appeared; likewise upload_parts.uploaded_at vs. created_at.
• updated_at — only on tables whose rows are actually edited, and always stamped by the shared touch_updated_at() BEFORE UPDATE trigger (§4), never by the app. An UPDATE that forgets the column can't leave a stale timestamp, and one that supplies a wrong value is overridden. It carries: organizations, users, organization_members, subscriptions, robot_models, robots, sessions, episodes, clips, episode_metadata, sets, groups.

Where updated_at is deliberately absent, because the column would be dead weight or an outright lie: - Append-only tables and junctions (audit_logs, role_change_logs, entity_versions, snapshots, snapshot_members, upload_parts, the four revision tables, episode_sets, set_groups, role_permissions) — nothing ever updates them; several are protected by immutability triggers that make an UPDATE an error. - subscription_plans and invoices — never edited in place by design (§2.11b). Their one legal transition, being closed and superseded, is already recorded with far more precision by valid_until / voided_at + superseded_by_*. An updated_at here would imply a mutability the model specifically forbids. - roles / permissions — seeded catalogs, changed by migration, not by users.

updated_at tracks the ROW, not its relationships. Linking an episode into a set writes episode_sets, so neither the episode's nor the set's updated_at moves; that history is set_revisions / group_revisions (§2.6b). Three mechanisms, easily confused: updated_at = when the row last changed, entity_versions = what it looked like before, revisions = what it contained.

2.10a Data-integrity constraints

Beyond keys and FKs, the schema enforces:

• NOT NULL + DEFAULT now() on every created_at/joined_at/updated_at, so these timestamps are database-generated (UTC) rather than app-supplied; and NOT NULL on business keys (organizations.name/slug, users.email, roles.name, permissions.key, subscription_plans.name, robots.robot_uuid, robot_models.model_name). Every table now carries a creation stamp — except organization_members, where joined_at is that stamp and a second column would be the same fact under two names. See §2.9a for which tables also carry updated_at.
• Sanity CHECKs (active): non-negative counts/fees, fps > 0, episodes.aggregate_score BETWEEN 0 AND 100, end_time_ms > start_time_ms, current_period_end > current_period_start, ended_at >= started_at, an '@' in users.email, and a 3-letter currency. (A CHECK passes when the column is NULL, so nullable columns stay valid.)
• episodes.aggregate_score is NOT NULL DEFAULT 0, not nullable. It is a rolling 0–100 quality score, mutated in place each time scoring runs, so it always reads as the current score rather than a history (the per-run history, if it is ever needed, belongs in episode_metadata or a scores table — not here). Defaulting to 0 rather than NULL means an unscored episode sorts last in a "best data first" list view without a COALESCE in every query, and it keeps the column distinct from readiness_state, which is the human/QA gate.
• Enum domains are commented out, on purpose. The status/type value-lists (episodes.status, sessions.type/status, clips.status, robots.status, subscriptions.status, audit_logs.actor_type, billing_interval, and the entity_versions/resource_access_grants type+level lists) are kept as commented CHECK (... IN (...)) next to each column — documentation of the intended domain, to be enabled once the lifecycles are finalized.

2.10 Change tracking: entity_versions (DB-side) vs audit_logs (app-side)

Two logs, split by purpose rather than convenience:

• entity_versions is database-side (triggers) because it is the data-change ledger — what a row looked like and who changed it. Living in the DB makes it unbypassable and atomic with the change it records; that is exactly the guarantee a data trail needs.
• audit_logs is application-side (written in the same transaction as the action) because it is the business / security event log — logins, permission-denied, exports, invites, intent — plus request context (ip_address, user_agent, metadata) that only exists in the app.

Why not put audit_logs in triggers too:

• A trigger sees that a row changed, not the intent behind it (a promotion vs. an automated sync look identical at the row level).
• Triggers structurally cannot see non-writes — failed logins, denials, and reads/exports never change a row, yet are often the most important security events.
• A blanket audit trigger would be brutal on high-volume tables (e.g. episodes during ingest) and pushes business logic into SQL.

Mirroring data changes into audit_logs via triggers would be redundant with entity_versions and still miss what audit_logs is for. Both take the actor from the app.current_user GUC. If bypass-proof logging is ever required on a non-versioned sensitive table (e.g. organization_members, resource_access_grants), add a narrow trigger there — never a blanket one.

2.11 Deletion model: soft vs hard (and the no-cascade guarantee)

Deletion is decided per table by one rule: soft-delete a row when it is referenced, recoverable, and low-volume; hard-delete it when its history is already captured elsewhere, or when it is high-volume / throwaway — and never use ON DELETE CASCADE or SET NULL. Three buckets follow from that:

1. Soft delete — only where a row must stay referenceable. organization_members (removed_at) and users (anonymize_user); see §2.8 / §8. These rows are the target of dozens of actor FKs ("who operated this session, edited this version, granted this access"). Hard-deleting one would either be blocked by those inbound FKs, or — with a cascade — destroy the operational history that points at it; SET NULL would keep the history but erase who did it. Soft delete keeps the row (every reference stays valid and attributable), revokes access via removed_at IS NULL, records when / by whom (removed_at / removed_by), and lets a returning member reactivate the same row.

2. Hard delete with versioning — the four content entities (episodes, clips, sets, groups). These hard-delete from the live table, but the capture_version trigger first appends a 'delete' tombstone (full snapshot) to entity_versions (§2.6) — so the row leaves the hot table yet stays fully recoverable via revert_entity. Soft delete here would be strictly worse: a deleted_at flag is a one-bit, last-state-only history that duplicates what entity_versions already does richly, while forcing every query and RLS policy to carry AND deleted_at IS NULL and leaving dead rows in the live table forever. Keeping the live tables corpse-free and the history in a dedicated log is the same principle as a Git working tree vs. its commit history.

The episode trash is a deliberate, scoped exception to this. episodes and clips also carry a deleted_at — but only to support a user-facing, retention-windowed trash (recoverable for N days, S3 bytes parked in Glacier; §2.11a / admin_lifecycle.sql), which entity_versions (admin recovery, no clock, resurrect needs live parents) can't provide. It is added on top of, not instead of, hard-delete-with-versioning: "permanent delete now" still takes the bucket-2 hard-delete path. We pay the soft-delete tax (AND deleted_at IS NULL) on exactly two tables, not globally — the per-table escape hatch §2.4 anticipated.

3. Hard delete, not recoverable — throwaway / config tables (upload_parts, the junctions, and operational config such as robots, sessions, subscriptions, refresh_tokens). These are not versioned, so a deleted row cannot be recovered from the database — deliberately: - upload_parts is the clearest case. It is a multipart-upload ledger for bytes that have already landed in S3: once the episode's mp4_s3_path resolves, the part list has served its purpose. Versioning or soft-deleting it would add writes and never free the storage for zero benefit. This is also why trashing an episode hard-deletes its upload_parts immediately rather than soft-deleting them for the window — keeping them hot would defeat the cold-storage saving the trash exists to capture. - The junctions and config tables either reconstruct from their parents or are not business content whose past states anyone needs to roll back. - invoices and subscription_plans are the exception inside this bucket — they are financial records, so they are never rewritten or discarded in place. See §2.11b.

The "no null-FK cascade" guarantee. Every foreign key is the default RESTRICT / NO ACTION — there is no ON DELETE CASCADE and no SET NULL anywhere. So a delete can never silently null a child column or cascade into dependents; at worst it is refused while something still references the row (the safe failure). Bulk teardown (delete_org, restore_snapshot_hard) deletes children-before-parents, so ordering — not a cascade — does the work. Columns that must outlive their target (entity_versions.entity_id, audit_logs.actor_id, resource_access_grants.resource_id, episodes.promoted_from_clip_id) are bare UUIDs, not FKs, so they neither block a delete nor get nulled by one.

2.11a Episode trash: a recoverable, retention-windowed delete

Deleting an episode's data is a two-tier user choice:

1. Permanent delete now — the bucket-2 path above: the app removes child clips, then hard-deletes the episode ('delete' tombstone), and deletes the S3 bytes.
2. Trash — trash_episode() soft-deletes the episode (deleted_at / purge_after / deleted_by, storage_state = 'glaciering') and cascade-trashes its clips, while the app moves the mp4/parquet S3 objects to Glacier. It is recoverable until purge_after; then purge_trashed() (a scheduled, per-tenant job) hard-deletes it children-first and the app deletes the Glacier objects.

Three design points worth keeping straight: - upload_parts is purged at trash time, not soft-deleted — it is a ledger for bytes that already landed in S3 (kept in Glacier through the window; §2.11 bucket 3). So it is handled identically in both tiers; the only thing the trash-vs-permanent choice changes is the fate of the S3 bytes. - episode_sets links ride along dormant. The episode row stays during the window, so the junction FK holds; if a linked set is deleted meanwhile, normal FK-ordered set deletion prunes the link. At purge the links are removed children-first — the sets themselves survive (shared taxonomy). Restoring re-uses whatever links remain, so "re-join surviving sets, drop deleted ones" needs no rebuild step. - Retention comes from the org plan's subscription_plans.retention_days (default 30); Glaciered bytes meter against the existing invoices.cold_storage_* columns. Note the plan lookup follows subscriptions.plan_id to the exact plan row it was sold under — which may be a superseded one (§2.11b) — not "the current Pro plan".

See SCHEMA_REFERENCE.md §11.1 for the function signatures and tests/08_trash_lifecycle.sql.

2.11b Billing history: is_valid instead of in-place edits

subscription_plans and invoices sit in the hard-delete bucket above but are governed by an extra rule: a row that anything has ever been billed against is never rewritten or deleted. Editing a plan's rate in place would silently restate every historical invoice that points at it; editing an issued invoice would destroy the record of what the customer was actually shown. Both tables therefore carry an is_valid flag plus the columns needed to read the closed row as history:

Table	Flag	Complementary columns
subscription_plans	is_valid	valid_from, valid_until, superseded_by_id
invoices	is_valid	voided_at, void_reason, superseded_by_invoice_id

• Repricing a plan = close the old row (is_valid=false, valid_until=now()), insert the replacement, then point the old row at it via superseded_by_id. That order is load-bearing: a partial unique index (name) WHERE is_valid permits only one valid row per plan name, and superseded_by_id can only be set once the new row exists. Old invoices keep resolving to the exact rate card they were billed under, because their plan_id still points at the closed row.
• Correcting an invoice = issue a new one, then close the original (is_valid=false, voided_at, void_reason, superseded_by_invoice_id). The self-referential FK is composite (superseded_by_invoice_id, org_id), so a correction chain can never cross tenants.
• is_valid is not is_active, and not status. On plans, is_active means "sell this to new customers" (a plan can be valid but withdrawn from the price list); is_valid means "this row is still the current truth for its name". On invoices, status is the billing lifecycle (draft/open/paid/…); is_valid is orthogonal — a paid invoice can be superseded by a corrected one, and both rows survive with only one of them valid.
• Two CHECKs keep the states honest: a row that is not valid must record when it stopped being valid (valid_until / voided_at), and a row that has been superseded cannot be flipped back to valid. Live-ledger reads go through the partial indexes (invoices (org_id) WHERE is_valid).

GPU compute is metered like storage and streaming. The rate card carries gpu_rate_per_hour + included_gpu_hours, and each invoice carries the metered triplet gpu_hours / gpu_rate_per_hour / gpu_cents, with the rate snapshotted at issue time exactly as the storage and egress rates are — which is what makes a superseded plan row readable rather than merely retained. total_cents is base_fee + storage + cold_storage + streaming + gpu.

Covered by tests/12_score_and_billing_history.sql.

2.12 Joining an org: invitations & the owner invariant

Invitations (member_invites). People join an org through a pending invitation — the human counterpart of robot_invites. It is keyed by email (not user_id) because the invitee may not have an account yet, so it can't be modeled as an organization_members row up front; it carries the role_id to grant on accept, an invite_token, a status (pending/accepted/revoked/ expired), and an expiry. A partial unique index allows one outstanding (pending) invite per email per org.

• Create / revoke are ordinary tenant operations (a member with app.current_org set; RLS keeps them in-org; the invited_by composite FK forces the inviter to be a member).
• Accept is the one step that crosses the tenant boundary — the invitee isn't a member yet, so there's no org context to rely on. accept_member_invite(token, user_id) (queries/invites.sql) is SECURITY DEFINER, authorized by the token: it validates the invite is pending + unexpired, then adds/reactivates the organization_members row with the invited role and marks the invite accepted. Its owner must be able to bypass RLS (the tables are FORCEd).

Owner invariant — multiple owners, always ≥ 1. "Owner" is the seeded role (roles.name = 'Owner'); an org may have several owners, and a user may own several orgs (multi-membership). But every org must always keep at least one active owner: the enforce_min_one_owner trigger (§4) blocks the demote or soft-remove that would drop the active-owner count to zero. Ownership transfer therefore needs no special machinery — promote another member to Owner, after which the previous owner may step down. delete_org is unaffected (it hard-deletes the whole org, and the guard fires on UPDATE only).

2.13 Polymorphic columns (references that can't be FKs)

A few columns are polymorphic: one id column that can reference rows in several tables, disambiguated by a companion *_type column. SQL has no "foreign key to one of several tables," so these columns are deliberately not FKs. There are exactly three:

Column (+ type)	Points at	Why polymorphic
entity_versions.(entity_type, entity_id)	episode/clip/set/group	one history log for all four content types; tombstones must reference rows that have been deleted
resource_access_grants.(resource_type, resource_id)	group/episode/clip/set/snapshot	one grants table for access to any resource type
audit_logs.(actor_type, actor_id)	user/robot/system	the actor may be a non-table (system), and audit rows must survive actor deletion

Why not separate tables / FKs. A real FK targets one table, so the alternative is N near-identical tables (episode_versions, clip_versions, …) with N triggers and N-way UNION reads, and snapshot_members would need an FK per type. One polymorphic table collapses that to a single uniform log; adding a type is a two-line change. Just as important, a real FK would forbid the references these tables exist to keep: an entity_versions 'delete' tombstone points at an entity_id no longer in the live table, and audit_logs.actor_id must outlive the actor — an FK would block the delete or cascade the history away. "Not an FK" is a requirement here, not a shortcut.

The cost, and how it's paid back. A polymorphic column forfeits everything the database gives a foreign key: referential integrity, ON DELETE behavior, the composite-(id, org_id) org isolation (§2.2), and planner knowledge. The schema restores what it can by hand: - a *_type domain (the commented CHECK (… IN (…)) lists + the guard trigger rejecting unknown types); - guard triggers that do the FK's org check (§4) — entity_versions_org_guard and resource_access_grants_org_guard, both SECURITY INVOKER; - indexes on the (type, id) pair, so lookups don't lose the FK's implicit index.

One mismatch between the two guards is deliberate and explains the residual gap in §6: resource_access_grants_org_guard requires the resource to exist in this org (a grant to a missing/cross-org resource is rejected), whereas entity_versions_org_guard must tolerate a not-found entity — that's how tombstones for deleted entities are allowed. That leniency is exactly what lets a forged cross-org entity_id slip past under RLS (a hidden cross-org row and a deleted row both read as "not found"). See §6 for why it's inert.

---

3. Row-Level Security

queries/rls.sql enables and forces RLS on every tenant table with a single FOR ALL policy per table:

USING (org_id = app_current_org()) WITH CHECK (org_id = app_current_org())

• USING scopes reads and the rows an UPDATE/DELETE may touch; WITH CHECK scopes the rows a write may produce — so a tenant can neither read nor create another org's data.
• app_current_org() reads the app.current_org GUC and fails closed: unset → NULL → matches no rows (rather than leaking).
• The app sets the tenant once per transaction: BEGIN; SET LOCAL app.current_org = '<uuid>'; … COMMIT;
• RLS is bypassed by superusers/BYPASSRLS roles and (without FORCE) the table owner. We FORCE it, and the app must connect as the non-superuser tobor_app role that rls.sql grants DML to.

Effect on queries/versioning_examples.sql: those queries omit an org_id filter, which is safe under RLS — each statement is implicitly constrained to app.current_org, so a cross-tenant id simply returns nothing.

---

4. Triggers

queries/triggers.sql holds the org guards for the two polymorphic columns that can't be foreign keys, plus the ownership invariant:

• entity_versions_org_guard — a version's org_id must match the live entity's org_id (references to already-deleted entities are allowed, by design).
• resource_access_grants_org_guard — the granted resource_id must exist in this org for the given resource_type.
• enforce_min_one_owner (AFTER UPDATE on organization_members) — blocks demoting or soft-removing the last active Owner, so every org keeps ≥ 1 owner. Multiple owners are allowed; fires on UPDATE only, so delete_org's whole-org hard delete is unaffected (see §2.12).

Both run as SECURITY INVOKER: under RLS the referenced row is same-org and visible (check is effective); on RLS-exempt admin/migration paths the function sees all orgs and the check still holds.

queries/versioning_triggers.sql adds automatic version capture: a capture_version trigger appends to entity_versions — on every change for clips/sets/groups, but for episodes only at a terminal outcome (status → 'processed'/'failed', on delete, or on revert), never on the transient states — plus the revert_entity() function. See §2.6. Apply order is schema.sql → triggers.sql → rls.sql → versioning_triggers.sql (then admin_lifecycle.sql → set_revisions.sql → group_revisions.sql → invites.sql) — rls.sql first, since it creates the tobor_app role the later files grant to.

triggers.sql also owns touch_updated_at, a BEFORE UPDATE trigger attached to every table carrying updated_at (§2.9a) — it stamps the column from the database rather than trusting the application to remember, and overrides any value the app supplies, so it cannot go stale or be back-dated. It reflects edits to the row; membership changes write the junction tables and are recorded by the revisions in §2.6b instead.

---

5. Indexes

Postgres auto-indexes PRIMARY KEY/UNIQUE columns but not foreign-key columns, and the composite UNIQUE (id, org_id) keys lead with id, so they do not serve WHERE org_id = …. Since every tenant query and every RLS policy filters on org_id, each tenant table gets a standalone org_id index, and high-traffic tables (episodes, clips, entity_versions, audit_logs) get composite (org_id, <fk>) indexes to cover the common joins.

---

6. Known limitations / residual risk

• entity_versions.entity_id → other-org reference under RLS (inert). entity_id isn't an FK, so entity_versions_org_guard enforces org-consistency by looking the entity up and comparing its org_id. That lookup runs SECURITY INVOKER, so RLS scopes it to the current org — which makes a "not found" result ambiguous between two cases the guard can't tell apart:
• (a) the entity was deleted → must be allowed (tombstones legitimately reference rows that no longer exist), versus
• (b) the entity belongs to another org and RLS is hiding it → should be rejected.

The guard assumes the innocent case (a) and allows it. So a hand-crafted insert in org A can store a version row whose entity_id actually names an org B row. It is inert, not a leak: org A still can't read that row (dereferencing it is RLS-blocked) and nothing of org B's is copied or changed — it's just a mislabeled pointer inside org A's own history. The normal write path never hits it (the capture_version trigger copies the entity's real id+org_id together), and admin/superuser writes (RLS off) see all orgs and do reject the mismatch. To close it, make this one guard SECURITY DEFINER owned by a BYPASSRLS role so its lookup can see across orgs. - Operational dependencies. Isolation only holds if the app connects as a non-superuser role and sets app.current_org per transaction. The latter fails closed, so a miss breaks functionality loudly rather than leaking. - audit_logs.actor_id is a bare UUID (no FK): actor_type may be user/robot/system, and audit rows must survive actor deletion.

---

7. Entity relationships

Cardinality of the core robotics-data domain (every table below is also }o--|| to organizations via org_id, omitted from the diagram for clarity):

Relationship	Cardinality
session → episodes	0..* (a session need not generate any episode)
episode → session	exactly 1 (mandatory)
episode → clips	0..* (an episode need not have clips)
clip → episode	exactly 1 (mandatory; plain FK — app deletes clips, no cascade)
clip → session (created in)	0..1 (may differ from the episode's session)
episode → robot	exactly 1 (every upload comes from a robot)
robot → robot_model	0..1 (nullable: a unit may be unclassified)
robot_model → robots	0.. (across tenants* — the catalog is global)
episode ↔ set	many-to-many via episode_sets (history: set_revisions)
set ↔ group	many-to-many via set_groups (history: group_revisions)
episode → upload_parts	0..*
snapshot ↔ entity_version	many-to-many via snapshot_members

erDiagram
  organizations        ||--o{ organization_members : "has"
  organizations        ||--o{ robots               : "owns"
  robot_models         |o--o{ robots               : "classifies (global catalog)"
  organizations        ||--o{ sessions             : "scopes"
  organizations        ||--o{ sets                 : "scopes"
  organizations        ||--o{ groups               : "scopes"
  users                ||--o{ organization_members : "is"

  sessions             ||--o{ episodes      : "generates 0..*"
  robots               ||--o{ episodes      : "produces"
  episodes             ||--o{ clips         : "trimmed into 0..*"
  sessions             |o--o{ clips         : "clip created in (0..1)"
  episodes             ||--o{ upload_parts  : "uploaded in"

  episodes             ||--o{ episode_sets : ""
  sets                 ||--o{ episode_sets : ""
  sets                 ||--o{ set_groups   : ""
  groups               ||--o{ set_groups   : ""

  %% Frozen membership history, one pair per junction side. Dotted: the member
  %% ids are bare UUIDs, not FKs, so a revision outlives its container and a
  %% manifest outlives the members it names.
  sets                 ||..o{ set_revisions         : "historised as"
  set_revisions        ||--o{ set_revision_episodes : "freezes"
  episodes             ||..o{ set_revision_episodes : "named in"

  groups               ||..o{ group_revisions       : "historised as"
  group_revisions      ||--o{ group_revision_sets   : "freezes"
  sets                 ||..o{ group_revision_sets   : "named in"

  organizations        ||--o{ entity_versions      : "scopes"
  entity_versions      ||--o{ snapshot_members     : ""
  snapshots            ||--o{ snapshot_members     : ""
  organization_members ||--o{ resource_access_grants : "granted"

Reading the crow's-foot notation: the symbol nearest an entity is its count in the relationship. sessions ||--o{ episodes = each episode has exactly one session (||), each session has zero-or-many episodes (o{). sessions |o--o{ clips = a clip's creation session is zero-or-one (|o).

---

8. Tenant offboarding & PII erasure

Destructive admin operations live in queries/admin_lifecycle.sql (applied after rls.sql, which creates the tobor_app role they grant to):

• delete_org(org_id) — hard-deletes an entire tenant. Runs under RLS as the app role, so it only touches the current tenant, and it refuses unless app.current_org equals the target org (can't be aimed at the wrong tenant). Deletes children-first to satisfy the NO ACTION FKs, and removes entity_versions after the versioned tables so the teardown's own 'delete' tombstones are cleaned up. It does not touch refresh_tokens (those belong to a user, who may be in other orgs).
• anonymize_user(user_id) — GDPR-style erasure. Scrubs PII in users (email/name) in place and revokes the user's refresh_tokens, keeping the id so every membership/actor reference stays valid. Because users/refresh_tokens are global (not RLS-scoped), it erases the person everywhere at once; memberships are left pointing at the anonymized user and can be soft-removed per-org if access should also be revoked. Credentials are not in scope: passwords live in AWS Cognito, so disabling or deleting the Cognito user is a separate application-owned step alongside this call.
• trash_episode / restore_episode / purge_trashed — the episode trash lifecycle (§2.11a). Same RLS model as delete_org (per-tenant, app.current_org required); purge_trashed() is the scheduled job that hard-deletes episodes past their retention window, children-first.

These cover the "you can't currently delete an org or a user" gaps: an org can't be hard-deleted directly because everything FKs to it (delete_org does the ordered teardown), and a user can't be hard-deleted because of inbound FKs (anonymize_user scrubs instead of deletes).