ToborLife — Schema & Developer Reference

Living document. Last reviewed: 2026-08-10.

This is the developer-facing reference for the multi-tenant Postgres schema under data/. It documents what each object is and how to use it. The SQL files are the source of truth; if this doc and the SQL disagree, the SQL wins — and please fix the doc (see Keeping this doc alive).

---

1. Map of the files

This are files that will be pushed to github and used as references

File	Contents	Apply phase
sql/schema.sql	Authoritative combined DDL: tables, constraints, FKs, indexes	1
sql/*.ddl / sql/*.sql	Per-table mirrors of schema.sql (one table each)	— (reference)
sql/seed_roles.sql	Seeds the global RBAC catalog (roles, permissions, mappings)	1b
queries/triggers.sql	Org-consistency guards for polymorphic columns	2
queries/rls.sql	RLS policies + app_current_org() + the tobor_app role	3
queries/versioning_triggers.sql	Automatic version capture + revert_entity + snapshot funcs	4
queries/admin_lifecycle.sql	delete_org(), anonymize_user()	5
queries/set_revisions.sql	mint_set_revision trigger + set_revision_manifest() / set_revision_as_of()	6
queries/group_revisions.sql	mint_group_revision trigger + group_revision_manifest() / group_revision_as_of() / group_revision_set_revisions()	7
queries/invites.sql	accept_member_invite() + create/revoke invite patterns	8
queries/membership.sql	Member add/remove/list/auth query patterns (reference)	—
queries/rls_smoke_test.sql	RLS isolation smoke tests (reference)	— (test)
reset_and_seed.sql	Wipe + reseed a full demo tenant	— (dev)

Apply order: schema.sql → seed_roles.sql → triggers.sql → rls.sql → versioning_triggers.sql → admin_lifecycle.sql → set_revisions.sql → group_revisions.sql → invites.sql. (rls.sql must come before versioning_triggers.sql/admin_lifecycle.sql/set_revisions.sql/group_revisions.sql/invites.sql — it creates the tobor_app role they grant to.)

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, non-owner role (tobor_app) or RLS is bypassed.

---

2. Conventions every developer must know

2.1 Primary keys are application-generated UUIDv7

Every business table declares id UUID PRIMARY KEY with no database default. That is deliberate: the application supplies the id on every insert. Adopt UUIDv7 for all of them.

Why v7 over v4 (gen_random_uuid()): - Time-ordered. v7 encodes a Unix-millisecond timestamp in its high bits, so newly generated ids sort roughly in creation order. Inserts hit the right edge of the primary-key B-tree instead of scattering across it — far less page fragmentation and write amplification than random v4, which matters for the high-insert tables (episodes, clips, junctions). - Still globally unique & uncoordinated — generate it client-side, no round trip, no central sequence. - Debuggable — ids are loosely chronological at a glance.

Rules: 1. Generate the id in the app and pass it explicitly: INSERT INTO episodes (id, …) VALUES ($1, …) where $1 is a v7. Libraries: Node uuidv7, Python uuid6/uuid_utils. 2. created_at is still the authoritative creation timestamp — never parse the time out of the id for business logic. The embedded time is for ordering/index locality only. 3. Privacy caveat: a v7 leaks creation time. These ids are internal PKs, so that's fine; if you ever expose an id in a public URL where creation time is sensitive, expose a separate opaque handle instead. 4. Enforced in the database. Every UUID id primary key carries CONSTRAINT check_uuidv7_version CHECK (uuid_extract_version(id) = 7), so a non-v7 id is rejected at write time — the convention above is not just guidance. uuid_extract_version() is native to PostgreSQL 18+ (this schema targets PG 18). The BIGSERIAL ids below are exempt (not UUIDs).

DB-side generated rows (created inside functions/seeds, not by the app) — entity_versions, snapshots, snapshot_members (in versioning_triggers.sql) and the seed/fixture rows — use the native uuidv7() generator, so they satisfy the same v7 CHECK as app-supplied ids. Both uuidv7() and uuid_extract_version() are native to PostgreSQL 18+, which this schema requires.

BIGSERIAL exceptions: upload_parts and audit_logs use BIGSERIAL (auto-increment bigint), not UUID — they're append-only, high-volume / internal, never referenced by composite (id, org_id) FKs, and benefit from the smaller, monotonic key. Do not put UUIDs on these.

2.2 Timestamps are TIMESTAMPTZ, stored UTC

Every time column is TIMESTAMPTZ. schema.sql runs SET timezone = 'UTC'; make it permanent with ALTER DATABASE <db> SET timezone = 'UTC'. created_at columns default to now() and are DB-generated — don't set them from the app.

2.3 Other conventions

• Soft delete for members (organization_members.removed_at); everything else hard-deletes — see §2.4.
• Append-only version history (entity_versions) — see §9.
• Composite same-org FKs — children reference (parent_id, org_id), not just parent_id (see §3).
• CHECK enum domains are commented out next to each status/type column — documentation of the intended values, to be enabled once lifecycles finalize.

2.4 Deletion model (and why it isn't "soft delete throughout")

The original requirement read "soft deletion throughout (no hard deletes that cause null FK cascades)." What's built deliberately splits those two clauses:

• The "no null-FK cascade" guarantee is met in full. No foreign key declares ON DELETE CASCADE or ON DELETE SET NULL anywhere — every FK is the default RESTRICT/NO ACTION. So a delete never silently nulls a child column or cascades into dependents; it is simply refused while anything still references the row. Bulk teardown (delete_org, restore_snapshot_hard) deletes children-before-parents so the order, 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 deletes nor get nulled.

• "Soft delete throughout" is intentionally not done — and this is the better state. Soft-delete is used only where a row must stay referenceable: organization_members (removed_at, so every "who did it" actor FK stays valid) and users (anonymize_user scrubs in place, keeping the id). Everything else hard-deletes, because the value soft-delete normally buys — recoverability and an audit trail — is already provided by the append-only entity_versions log (a 'delete' tombstone with the full row snapshot → time-travel + restore, §9) for the versioned entities, and by audit_logs for the who/when.

Why this beats a deleted_at on every table: - No "soft-delete tax": queries and RLS policies don't each need AND deleted_at IS NULL, uniques don't become partial, and indexes don't fill with tombstones. - High-volume tables (episodes, clips) don't accumulate dead rows — and their S3 bytes are actually freed, not just flagged. - Recoverability is centralized in one well-tested place (entity_versions) instead of duplicated as ad-hoc flags per table.

Honest caveat: non-versioned hard deletes (upload_parts, junctions, robots, sessions, subscriptions, refresh_tokens) have no tombstone and are unrecoverable. Only episodes/clips/sets/groups can be restored. invoices and subscription_plans are never deleted or edited in place at all — they are closed with is_valid=false and superseded (§4.2).

The episode trash (the per-table escape hatch, now taken). Deleting an episode's data offers two product paths: - Permanent delete now — the ordinary hard delete above (app removes child clips, then DELETEs the episode → entity_versions tombstone; app deletes the S3 bytes). No extra machinery. - Trash (recoverable, S3→Glacier, 30-day window) — a soft delete layered on top, using episodes.deleted_at/purge_after/deleted_by/storage_state and clips.deleted_at. trash_episode() soft-deletes the episode + cascade-trashes its clips, but hard-deletes upload_parts immediately — it is a ledger for bytes that already landed in S3 (and survive in Glacier), so keeping it hot for the window would defeat the cold-storage saving. episode_sets links ride along dormant. After the window, purge_trashed() hard-deletes children-first (the set links die; the sets themselves survive). See §11.1. This is exactly the "add a deleted_at to that table" escape hatch — taken for episodes/clips only, not soft-delete globally.

---

3. Multi-tenancy: the org_id boundary

The tenant key is org_id, on every tenant-scoped table. Isolation is defense-in-depth, four layers (full rationale in DATABASE.md §1–2):

1. NOT NULL on every org_id — no row escapes an org filter via NULL.
2. Composite (id, org_id) unique keys on parents + composite FKs on children — a child can't reference a parent in another org. (Postgres MATCH SIMPLE means the FK is skipped when the child column is NULL, so optional links still work.)
3. Membership FKs on actor columns — an actor (operator_user_id, created_by, edited_by, granted_by, …) must be a member of the org.
4. Row-Level Security (§8) — the database enforces "you only see/write your org's rows."

Polymorphic columns that can't be FKs (entity_versions.entity_id, resource_access_grants.resource_id) are guarded by triggers (§10).

---

4. Table reference

Legend: PK primary key · FK→ foreign key · 🔒 RLS under org RLS · 🌍 global not tenant-scoped · all id are app-supplied UUIDv7 unless noted.

4.1 Tenancy & identity

organizations — the tenant 🔒(by id)

The tenant root. RLS'd by id (a tenant sees only its own row).

Column	Type	Notes
id	UUID PK	the tenant key everything else carries as org_id
name	VARCHAR(255) NOT NULL
slug	VARCHAR(255) UNIQUE NOT NULL	URL handle
plan	VARCHAR(50)	deprecated denormalized cache; drop once frontend migrates to subscriptions
logo_url	TEXT	where the org's mark lives (S3/CDN URL); NULL = fall back to the default
timezone	VARCHAR(64) NOT NULL ('UTC')	IANA zone name used to render timestamps for this org
created_at	TIMESTAMPTZ NOT NULL
updated_at	TIMESTAMPTZ NOT NULL	stamped by organizations_touch_updated_at (§10)

timezone is a display preference, not a storage change. Every time column in the schema is TIMESTAMPTZ and the session is pinned to UTC, so nothing about how instants are stored depends on this value — it tells the app which zone to render them in. The CHECK is a shape guard only (^[A-Za-z0-9_+-]+(/[A-Za-z0-9_+-]+)*$): validating the name against pg_timezone_names would need a subquery (not allowed in a CHECK) or a non-IMMUTABLE lookup function (unsafe across dump/restore, since that catalog is build-dependent). The app must validate membership with SELECT 1 FROM pg_timezone_names WHERE name = $1.

users — global identity 🌍

A person; may belong to many orgs via organization_members. Never RLS'd. Holds no credential material — passwords are managed entirely by AWS Cognito, and this table only carries the cognito_id pointer to that identity.

Column	Type	Notes
id	UUID PK
email	VARCHAR(255) UNIQUE NOT NULL	CHECK position('@' in email) > 1
cognito_id	VARCHAR(255) UNIQUE	the AWS Cognito user-pool sub — this person's identity in the external IdP, and the only authentication material in this table. Nullable (invited-but-not-signed-up or seeded users) and UNIQUE, so one Cognito identity can never map to two rows. Global like the rest of this table: the identity is the person, not the tenant. Not a secret (an opaque subject id, not a token), so anonymize_user() deliberately leaves it alone — unlinking an IdP identity is an authn decision, not an erasure one.
first_name	VARCHAR(255)	'Deleted' after anonymize_user()
last_name	VARCHAR(255)	'User' after anonymize_user()
is_active	BOOLEAN NOT NULL (true)
created_at / updated_at	TIMESTAMPTZ NOT NULL	updated_at stamped by users_touch_updated_at (§10)

organization_members — the user↔org membership 🔒

The join that makes a user a tenant participant, and the target of every "who did it" actor FK. Soft-deleted (see §7).

Column	Type	Notes
id	UUID PK	children FK to (id, org_id)
org_id	UUID NOT NULL FK→organizations
user_id	UUID NOT NULL FK→users
role_id	UUID FK→roles	the member's RBAC role
invited_by	UUID	FK→organization_members(user_id, org_id)
joined_at	TIMESTAMPTZ NOT NULL	this row's creation stamp — there is deliberately no separate created_at: one fact, one column
removed_at	TIMESTAMPTZ	NULL = active; set = soft-removed
removed_by	UUID	FK→organization_members(user_id, org_id)
updated_at	TIMESTAMPTZ NOT NULL	stamped by organization_members_touch_updated_at (§10); moves on a role change, a removal, or the reactivation that clears removed_at

Keys: UNIQUE (id, org_id), UNIQUE (user_id, org_id) (a user joins an org at most once → enables reactivation). Index: (org_id) WHERE removed_at IS NULL (hot auth path). Ownership invariant (always ≥ 1 owner) is enforced by the enforce_min_one_owner trigger — see §7.2 / §10.

member_invites — pending invitations to join an org 🔒

The human counterpart of robot_invites: a not-yet-accepted invite for a person to become a member. Keyed by email because the invitee may not be a user yet. Accept flow + semantics in §7.1.

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
email	VARCHAR(255) NOT NULL	invitee; CHECK position('@'…) > 1
role_id	UUID FK→roles	role granted on accept
invite_token	TEXT UNIQUE NOT NULL	the secret emailed to the invitee
status	VARCHAR(50) NOT NULL ('pending')	pending/accepted/revoked/expired
invited_by	UUID	FK→organization_members(user_id, org_id)
accepted_by	UUID FK→users	set on accept
accepted_at / expires_at	TIMESTAMPTZ

Index: partial UNIQUE (org_id, lower(email)) WHERE status = 'pending' — one outstanding invite per email per org.

4.2 Billing

subscription_plans — plan catalog 🌍

Shared catalog of plans + metered rates. Not tenant-scoped. Never edited in place once anything is billed against it — repricing supersedes the row instead (see below).

Column	Type	Notes
id	UUID PK
name	VARCHAR(100) NOT NULL	not globally unique any more — unique among valid rows only, via partial UNIQUE (name) WHERE is_valid, so superseded rows keep the name they were sold under
description	TEXT
currency	VARCHAR(3)	CHECK ~ '^[A-Z]{3}$'
billing_interval	VARCHAR(20)	month/year (enum CHECK commented)
base_fee_cents	INTEGER	CHECK >= 0
included_storage_gb / included_streaming_gb	INTEGER	CHECK >= 0
included_gpu_hours	INTEGER	GPU-hour allowance before metering; CHECK >= 0
storage_rate_per_gb / cold_storage_rate_per_gb / streaming_rate_per_gb	NUMERIC(10,4)	metered rates
gpu_rate_per_hour	NUMERIC(10,4)	training/inference GPU $/GPU-hour beyond the allowance
retention_days	INTEGER	CHECK >= 0
is_active	BOOLEAN NOT NULL (true)	sellable: offer this to new customers
is_valid	BOOLEAN NOT NULL (true)	current: this row is still the truth for its name. Orthogonal to is_active — a plan can be valid but withdrawn from the price list
valid_from	TIMESTAMPTZ NOT NULL DEFAULT now()
valid_until	TIMESTAMPTZ	NULL while valid; CHECK (valid_until IS NULL OR valid_until >= valid_from) (>=, so a same-transaction correction is legal)
superseded_by_id	UUID FK→subscription_plans	the row that replaced this one
created_at	TIMESTAMPTZ NOT NULL

CHECK (is_valid OR valid_until IS NOT NULL) — an invalidated row must say when it stopped being valid. CHECK (superseded_by_id IS NULL OR NOT is_valid) and CHECK (superseded_by_id IS DISTINCT FROM id) — a superseded row can't be flipped back to valid, and can't point at itself. Indexes: partial UNIQUE (name) WHERE is_valid, (superseded_by_id).

Repricing, in order (the order is load-bearing): 1. UPDATE … SET is_valid=false, valid_until=now() on the old row — frees the name. 2. INSERT the new row (valid). 3. UPDATE … SET superseded_by_id=<new> on the old row — only possible once it exists.

Historical invoices.plan_id and subscriptions.plan_id keep pointing at the closed row, so they still resolve to the rate card actually in force.

subscriptions — a tenant's plan 🔒

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
plan_id	UUID FK→subscription_plans
status	VARCHAR(50)	trialing/active/past_due/canceled/paused (enum commented)
current_period_start / current_period_end	TIMESTAMPTZ	CHECK end > start
trial_start / trial_end	TIMESTAMPTZ	trial window as a permanent fact; both NULL = never trialed; CHECK end > start (skipped when NULL); trial_end anchors the first real charge and survives after status→active
canceled_at	TIMESTAMPTZ
created_at / updated_at	TIMESTAMPTZ NOT NULL	updated_at stamped by subscriptions_touch_updated_at (§10)

UNIQUE (id, org_id) so invoices can composite-FK back to the same org. trial_* are kept distinct from current_period_* so trial dates aren't destroyed when the subscription converts; status↔date consistency is app-enforced (a CHECK can't distinguish "now" from "then" once a trial has elapsed). A trial produces no invoices row (or a $0 draft); the first real invoice fires at trial_end. Indexes: (org_id), (plan_id), partial (trial_end) WHERE status='trialing' (expiring trials).

invoices — billing ledger 🔒

One row per period charged. Rates are snapshotted per invoice (the rate columns), so a later plan change doesn't rewrite history. An issued invoice is never rewritten: a correction issues a new invoice and closes the original (see below).

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
subscription_id	UUID	FK (subscription_id, org_id) → subscriptions (same-org)
plan_id	UUID FK→subscription_plans	plan in effect when issued
currency	VARCHAR(3)	CHECK ~ '^[A-Z]{3}$'
period_start / period_end	TIMESTAMPTZ	CHECK end > start
base_fee_cents	INTEGER	flat platform fee; CHECK >= 0
storage_gb / storage_rate_per_gb / storage_cents	NUMERIC / NUMERIC / INTEGER	hot storage metered (GB, rate, cents)
cold_storage_gb / cold_storage_rate_per_gb / cold_storage_cents	NUMERIC / NUMERIC / INTEGER	cold storage metered
streaming_gb / streaming_rate_per_gb / streaming_cents	NUMERIC / NUMERIC / INTEGER	egress metered
gpu_hours / gpu_rate_per_hour / gpu_cents	NUMERIC(14,4) / NUMERIC(10,4) / INTEGER	GPU compute metered (GPU-hours, $/GPU-hour snapshotted at issue time, resulting charge)
total_cents	INTEGER	base_fee + storage + cold_storage + streaming + gpu; CHECK >= 0 (all *_cents / *_gb / gpu_hours >= 0)
status	VARCHAR(50)	draft/open/paid/void/uncollectible — the billing lifecycle
issued_at / due_at / paid_at	TIMESTAMPTZ	paid_at NULL until paid
payment_reference	TEXT	external processor charge id
created_at	TIMESTAMPTZ NOT NULL
is_valid	BOOLEAN NOT NULL (true)	this row is still the current truth. Orthogonal to status — a paid invoice can be superseded by a corrected one, and both rows survive
voided_at	TIMESTAMPTZ	NULL while valid; set when voided/superseded
void_reason	TEXT
superseded_by_invoice_id	UUID	the corrected invoice; FK (superseded_by_invoice_id, org_id) → invoices — a correction chain never crosses tenants

UNIQUE (id, org_id) (target of that self-referential composite FK). CHECK (is_valid OR voided_at IS NOT NULL), CHECK (superseded_by_invoice_id IS NULL OR NOT is_valid), CHECK (superseded_by_invoice_id IS DISTINCT FROM id).

Correcting an invoice: issue the replacement, then close the original with is_valid=false, voided_at, void_reason, superseded_by_invoice_id. What the customer was originally shown survives verbatim.

Indexes: (org_id), (subscription_id), (org_id, paid_at), partial (org_id) WHERE is_valid (the live ledger), (superseded_by_invoice_id).

4.3 RBAC catalog & grants

roles 🌍 / permissions 🌍 / role_permissions 🌍

The global capability catalog (shared by all tenants), seeded by seed_roles.sql. See §5.

roles:

Column	Type	Notes
id	UUID PK
name	VARCHAR(100) UNIQUE NOT NULL	e.g. Owner / Manager / Engineer
description	TEXT
created_at	TIMESTAMPTZ NOT NULL

permissions:

Column	Type	Notes
id	UUID PK
key	VARCHAR(100) UNIQUE NOT NULL	e.g. data.extract
description	TEXT
created_at	TIMESTAMPTZ NOT NULL

role_permissions:

Column	Type	Notes
id	UUID PK
role_id	UUID FK→roles
permission_id	UUID FK→permissions	no UNIQUE on the pair; seed uses WHERE NOT EXISTS for idempotency
created_at	TIMESTAMPTZ NOT NULL

user_permissions — per-member permission override 🔒

Grants/denies a single permission to one member, on top of their role.

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations	carried directly so RLS + FKs are same-org
org_member_id	UUID	FK→organization_members(id, org_id)
permission_id	UUID FK→permissions
granted	BOOLEAN NOT NULL (true)	false = explicit deny
granted_by	UUID	FK→organization_members(user_id, org_id)

resource_access_grants — per-resource access 🔒

Grants one member access to one specific resource at a level (complements the org-wide role model — e.g. Engineer scoped to specific robots/episodes).

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
org_member_id	UUID	FK→organization_members(id, org_id)
resource_type	VARCHAR(50)	group|episode|clip|set|snapshot (enum commented)
resource_id	UUID	polymorphic, no FK — guarded by trigger (§10)
access_level	VARCHAR(50)	viewer|editor|owner (enum commented)
granted_by	UUID	FK→organization_members(user_id, org_id)
created_at	TIMESTAMPTZ NOT NULL

UNIQUE (org_member_id, resource_type, resource_id). Indexes: (org_id), (org_member_id).

role_change_logs — role-change audit 🔒

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
target_user_id	UUID	FK→organization_members(user_id, org_id)
old_role_id / new_role_id	UUID FK→roles
changed_by_user_id	UUID	FK→organization_members(user_id, org_id)
reason	TEXT
created_at	TIMESTAMPTZ NOT NULL

Index: (org_id).

4.4 Robots & sessions

robot_models — the hardware catalog 🌍

What kind of robot a robots row is, factored out of the unit itself. robots describes one physical machine (serial, api key, firmware, liveness); robot_models describes the product line it is an instance of, and the capability facts true of every unit of that model. Global shared catalog — no org_id, no RLS, like subscription_plans and the roles/permissions catalog.

Column	Type	Notes
id	UUID PK
manufacturer	VARCHAR(255)
model_name	VARCHAR(255) NOT NULL
version	VARCHAR(100)	hardware revision ('v2', 'Rev B') — part of the identity key, so two revisions coexist as separate rows
kind	VARCHAR(50)	arm/humanoid/mobile_base/gripper/quadruped (enum CHECK commented)
degrees_of_freedom	INTEGER	CHECK >= 0; NULL where not meaningful (a mobile base has no joint count)
default_fps	INTEGER	the model's native capture rate; CHECK > 0
sensor_spec	JSONB	payload varies wildly by model (cameras, depth, force-torque, tactile) and is read whole, never filtered on in SQL — hence JSONB, not columns
description	TEXT
is_active	BOOLEAN NOT NULL (true)	false = retired but still referenceable
created_at / updated_at	TIMESTAMPTZ NOT NULL	updated_at stamped by robot_models_touch_updated_at (§10)

UNIQUE NULLS NOT DISTINCT (manufacturer, model_name, version) — one row per product line + hardware revision. NULLS NOT DISTINCT (PG15+) is load-bearing: with the default NULLS DISTINCT, every unversioned model would count as a unique key and the same model could be entered any number of times. Indexes: (manufacturer, model_name), partial (model_name) WHERE is_active.

Retire, never delete. The robots.robot_model_id FK is plain (no cascade), so the database refuses to delete a model any robot still references — which is the desired behaviour: the units and the episodes they recorded must stay readable. Withdraw a model with is_active = false instead.

Trade-off of the global choice: a UR5e is stated once and shared by every tenant, so model facts can't drift between orgs — but only a platform admin can add a row, so 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 cost of duplicate rows per tenant.)

robots 🔒

A physical robot — one unit.

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
robot_uuid	UUID UNIQUE NOT NULL	device identity
robot_model_id	UUID FK→robot_models	which product line this unit is. Plain FK to a global catalog (like subscriptions.plan_id), not a composite one — robot_models has no org_id to match. Nullable: a robot can be provisioned before anyone records what it is, and pre-catalog fleet rows have none.
name	VARCHAR(255)
api_key_hash	TEXT
status	VARCHAR(50)	provisioning/online/offline/disabled (enum commented)
firmware_version	VARCHAR(100)	of this unit — distinct from the model's fixed capability facts
last_seen	TIMESTAMPTZ
created_at	TIMESTAMPTZ NOT NULL
updated_at	TIMESTAMPTZ NOT NULL	stamped by robots_touch_updated_at (§10)

UNIQUE (id, org_id). Indexes: (org_id), (robot_model_id) ("which units are this model?").

robot_invites 🔒

Pairing tokens for registering a robot.

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
invite_token	TEXT UNIQUE
expires_at	TIMESTAMPTZ
created_by	UUID	FK→organization_members(user_id, org_id)
created_at	TIMESTAMPTZ NOT NULL

Index: (org_id).

sessions 🔒

One operator's connection to the org — not bound to a single robot (the producing robot is recorded per-episode).

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
operator_user_id	UUID	FK→organization_members(user_id, org_id)
type	VARCHAR(50)	teleop/autonomous/maintenance (enum commented)
status	VARCHAR(50)	active/ended/aborted (enum commented)
started_at / ended_at	TIMESTAMPTZ	CHECK ended_at >= started_at
created_at	TIMESTAMPTZ NOT NULL	DB-generated row-creation stamp. Distinct from started_at, which is the app-set moment the operator's connection actually opened and may be NULL — created_at answers "when did this row appear", started_at answers "when did the session begin".
updated_at	TIMESTAMPTZ NOT NULL	stamped by sessions_touch_updated_at (§10)

UNIQUE (id, org_id). Indexes: (org_id), (operator_user_id).

4.5 Core robotics-data domain

episodes — a recorded run 🔒

The central entity. Always belongs to a session and a robot (both NOT NULL, same-org FKs). Versioned at the extraction boundary only — see §9.

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL
robot_id	UUID NOT NULL	FK→robots(id, org_id) — every upload comes from a robot
session_id	UUID NOT NULL	FK→sessions(id, org_id)
name	VARCHAR(255)	human-readable display label, mirroring sets.name / groups.name. Nullable (unlike those two): a robot creates the row mid-recording, before anyone has named it. Distinct from task below.
status	VARCHAR(50)	lifecycle: recording → uploading → uploaded → processing → processed, or failed
readiness_state	VARCHAR(50)	how far along this episode is toward being usable as training data. A curation gate, deliberately orthogonal to status above: status tracks the ingest pipeline (is the data here yet), readiness_state tracks human/QA judgement (is the data good enough to use). An episode can be 'processed' and still 'rejected'. NULL until anything has assessed it. Commented enum CHECK ready to enable: raw\|needs_review\|reviewed\|ready\|rejected.
task	TEXT	free-text task label (independent of linked sets)
origin	VARCHAR(50) NOT NULL DEFAULT 'original'	'original' = recorded directly; 'promoted' = created from a clip. Commented enum CHECK ready to enable.
promoted_from_clip_id	UUID	the source clip when origin = 'promoted'; bare UUID, no FK (like audit_logs.actor_id) so it survives the clip's cascade-delete
zstd_md5	VARCHAR(255)	integrity of the uploaded archive
mp4_s3_path / parquet_s3_path	TEXT	video + extracted per-timestep data in S3 (the parquet is the system of record for frame-level data; there is no frames table)
frame_count / duration_seconds / fps	INTEGER	CHECK >= 0 / >= 0 / > 0
aggregate_score	NUMERIC(5,2) NOT NULL DEFAULT 0	rolling quality score out of 100, CHECK BETWEEN 0 AND 100. Mutated in place each time scoring runs, so it always reads as the current score (not a history). Defaults to 0, not NULL, so an unscored episode sorts last without a COALESCE. Distinct from readiness_state, which is the human/QA gate.
recorded_at / uploaded_at / processed_at	TIMESTAMPTZ	lifecycle stamps
created_at	TIMESTAMPTZ NOT NULL
deleted_at	TIMESTAMPTZ	NULL = live; set = in the trash (§11.1)
deleted_by	UUID	who trashed it; FK→organization_members(user_id, org_id)
purge_after	TIMESTAMPTZ	deleted_at + retention; when purge_trashed() hard-deletes
storage_state	VARCHAR(50) NOT NULL ('standard')	standard\|glaciering\|glacier\|restoring\|purged (enum CHECK commented); where the S3 bytes are, moved async by the app
updated_at	TIMESTAMPTZ NOT NULL	stamped by episodes_touch_updated_at (§10)

UNIQUE (id, org_id). CHECK ((deleted_at IS NULL) = (purge_after IS NULL)) — a row is either fully live or fully trashed. Indexes: (org_id), (org_id, session_id), (org_id, robot_id), partial (org_id) WHERE deleted_at IS NULL (hot "list live" path), partial (org_id, readiness_state) WHERE deleted_at IS NULL (review queues / "what can I train on"), partial (purge_after) WHERE deleted_at IS NOT NULL (purge-job scan).

clips — a trimmed reference into an episode 🔒

A pointer to a trimmed span of one source episode's video (trim offsets + source S3 paths), no set linkage of its own. The episode_id FK is plain (no cascade): clip deletion is handled by the application, so an episode can't be deleted while clips still reference it — the app must remove or reassign them first.

Column	Type	Notes
episode_id	UUID NOT NULL	FK→episodes(id, org_id) (plain — no cascade; app deletes clips)
session_id	UUID	the session the clip was created in — may differ from the episode's session; FK→sessions(id, org_id)
start_time_ms / end_time_ms	BIGINT	CHECK end > start, start >= 0
source_mp4_s3_path / source_parquet_s3_path	TEXT	lets reads resolve video without joining episodes
name, status, description
deleted_at	TIMESTAMPTZ	NULL = live; set only by the episode-trash cascade (§11.1). A standalone clip delete is a plain hard delete, not a trash.
updated_at	TIMESTAMPTZ NOT NULL	stamped by clips_touch_updated_at (§10)

Indexes: (org_id), (org_id, episode_id), (org_id, session_id), partial (org_id, episode_id) WHERE deleted_at IS NULL (hot "list live clips" path). To keep a clip past its episode, promote it to its own episode app-side before deleting the episode; the resulting episode records origin = 'promoted' and promoted_from_clip_id = this clip's id (see episodes).

upload_parts — multipart-upload ledger 🔒 (BIGSERIAL)

Column	Type	Notes
id	BIGSERIAL PK	not a UUID
org_id	UUID NOT NULL FK→organizations
episode_id	UUID	FK→episodes(id, org_id)
part_number	INTEGER	CHECK > 0
etag	TEXT
uploaded_at	TIMESTAMPTZ	app-set: when the part actually landed in S3
created_at	TIMESTAMPTZ NOT NULL	DB-generated row-creation stamp

Index: (org_id, episode_id).

sets 🔒 / groups 🔒 — content taxonomy

sets:

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
name	VARCHAR(255) NOT NULL
description	TEXT
created_at	TIMESTAMPTZ NOT NULL (now())
updated_at	TIMESTAMPTZ NOT NULL (now())	stamped by the sets_touch_updated_at trigger (§10), never by the app — the trigger assigns NEW.updated_at in BEFORE UPDATE, so a caller that forgets it cannot leave a stale value and a caller that supplies a wrong one is overridden. Equals created_at until the row is first edited.

UNIQUE (id, org_id), index (org_id).

groups: identical shape — id, org_id, name, description, created_at, updated_at (via groups_touch_updated_at). UNIQUE (id, org_id), index (org_id).

updated_at tracks the ROW, not its membership. Linking or unlinking a member writes episode_sets / set_groups, not sets / groups, so membership changes do not move updated_at. That history is §4.5a/§4.5b (set_revisions.created_at / group_revisions.created_at). Three disjoint mechanisms, easily confused: updated_at = when the row last changed, entity_versions = what the row used to say, revisions = who was in it.

episode_sets 🔒 / set_groups 🔒 — many-to-many junctions

Both link columns are NOT NULL (a link needs both ends), and both ends carry composite FKs so a link can't cross orgs. An entity with no link simply has no junction row. Junctions are not versioned — but both content junctions' membership is historised, by a separate mechanism: episode_sets into set_revisions (§4.5a) and set_groups into group_revisions (§4.5b).

episode_sets:

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
episode_id	UUID NOT NULL	FK→episodes(id, org_id)
set_id	UUID NOT NULL	FK→sets(id, org_id)
created_at	TIMESTAMPTZ NOT NULL

UNIQUE (episode_id, set_id). Indexes: (org_id), (set_id).

set_groups:

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
set_id	UUID NOT NULL	FK→sets(id, org_id)
group_id	UUID NOT NULL	FK→groups(id, org_id)
created_at	TIMESTAMPTZ NOT NULL

UNIQUE (set_id, group_id). Indexes: (org_id), (group_id).

4.5a set_revisions 🔒 / set_revision_episodes 🔒 — citable dataset versions

The problem. Customers train models on a set, then edit the set. Months later they need to know exactly which episodes went into a given training run. episode_sets is mutable and unversioned, so on its own it cannot answer that: an unlinked row is simply gone.

The mechanism. episode_sets stays the live, editable membership. Every change to it mints an immutable, frozen manifest — one revision per set per transaction — via the deferred trigger in queries/set_revisions.sql (§9.7). The app pins the returned revision id against its training run; nothing in the existing read paths changes.

set_revisions:

Column	Type	Notes
id	UUID PK	the citation the app stores against a training run
org_id	UUID NOT NULL FK→organizations
set_id	UUID NOT NULL	bare UUID, no FK — a citation must outlive delete_set(); org-consistency via the set_revisions_org_guard trigger (§10)
revision_no	INTEGER NOT NULL	1,2,3… per set; CHECK > 0
set_name	VARCHAR(255)	the set's name at freeze time, so a revision of a deleted set still renders
episode_count	INTEGER NOT NULL	denormalized; CHECK >= 0
created_by	UUID	from app.current_user; FK→organization_members(user_id, org_id)
created_at	TIMESTAMPTZ NOT NULL	also what set_revision_as_of() searches

UNIQUE (set_id, revision_no), UNIQUE (id, org_id). Indexes: (org_id), (org_id, set_id, revision_no), (org_id, set_id, created_at).

set_revision_episodes — one row per episode per revision:

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
revision_id	UUID NOT NULL	FK→set_revisions(id, org_id)
episode_id	UUID NOT NULL	bare UUID, no FK — the manifest must survive purge_trashed() hard-deleting the episode, which is precisely when it matters; guard trigger in §10
name / task	VARCHAR(255) / TEXT	frozen at mint time
mp4_s3_path / parquet_s3_path	TEXT	frozen; locate the objects without the episodes row
frame_count / duration_seconds / fps	INTEGER	frozen
recorded_at	TIMESTAMPTZ	frozen
zstd_md5	VARCHAR(255)	frozen — proves the bytes behind a pinned revision are the bytes trained on
created_at	TIMESTAMPTZ NOT NULL

UNIQUE (revision_id, episode_id). Indexes: (org_id), (revision_id), and (org_id, episode_id) for the reverse lookup "this episode was purged — which revisions referenced it?".

Append-only. The set_revisions_immutable trigger (§10) rejects UPDATE/DELETE on both tables under tobor_app. A dataset version the customer can silently edit is worthless as a citation. Superuser is exempt, and delete_org passes via app.captured_links = 'skip'.

Availability, not deletion. Reads go through set_revision_manifest(revision_id), which LEFT JOINs episodes and labels each row 'live' / 'trashed' / 'purged'. A purged episode still appears, flagged — it is never dropped from the manifest.

4.5b group_revisions 🔒 / group_revision_sets 🔒 — citable collection versions

The same problem, one level up. §4.5a makes a set's episode membership citable. A group is a collection of sets, and customers train on "everything in group G" exactly as they train on a single set — so set_groups needs the same treatment. This is a structural mirror of §4.5a with the container and member types swapped:

	§4.5a	§4.5b
Live junction	episode_sets	set_groups
Container (subject)	set	group
Frozen members	episodes	sets
Revision table	set_revisions	group_revisions
Manifest table	set_revision_episodes	group_revision_sets
Availability states	live / trashed / purged	live / deleted

Everything in §4.5a's rationale carries over unchanged: automatic deferred minting, one revision per container per transaction, bare-UUID member ids so the citation outlives the row, append-only enforcement. Only the differences are called out below.

group_revisions:

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
group_id	UUID NOT NULL	bare UUID, no FK — a citation must outlive delete_group(); org-consistency via the group_revisions_org_guard trigger (§10)
revision_no	INTEGER NOT NULL	1,2,3… per group; CHECK > 0. Independent of any set's revision_no
group_name	VARCHAR(255)	the group's name at freeze time, so a revision of a since-deleted group still renders
set_count	INTEGER NOT NULL	denormalized; CHECK >= 0
created_by	UUID	from app.current_user, NULL if unset; FK→organization_members(user_id, org_id)
created_at	TIMESTAMPTZ NOT NULL	also what group_revision_as_of() searches

UNIQUE (group_id, revision_no), UNIQUE (id, org_id). Indexes: (org_id), (org_id, group_id, revision_no), (org_id, group_id, created_at).

group_revision_sets — one row per set per revision:

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
revision_id	UUID NOT NULL	FK→group_revisions(id, org_id)
set_id	UUID NOT NULL	bare UUID, no FK — the manifest must survive delete_set(); guard trigger in §10
name / description	VARCHAR(255) / TEXT	frozen at mint time
created_at	TIMESTAMPTZ NOT NULL

UNIQUE (revision_id, set_id). Indexes: (org_id), (revision_id), and (org_id, set_id) for the reverse lookup "this set was deleted — which group revisions referenced it?".

Two availability states, not three. Sets have no trash window (no deleted_at), so group_revision_manifest(revision_id) labels each row only 'live' or 'deleted'. That is the one shape difference from set_revision_manifest().

The manifest does NOT copy episode membership. A set's episodes already live in set_revisions; duplicating them here would create a second source of truth that could drift. To resolve a group revision all the way down to episodes, use group_revision_set_revisions(revision_id) (§9.8), which pairs each frozen set with the set revision current at the group revision's own created_at. The two histories compose rather than nest.

Append-only. The group_revisions_immutable trigger (§10) rejects UPDATE/DELETE on both tables under tobor_app, via the same shared revisions_immutable() function as the set side.

episode_metadata 🔒 — per-episode key/value attributes

EAV side table for arbitrary per-episode metadata (e.g. hand_model → Brainco Revo 2). An episode may carry many rows, and the same meta_key may repeat — there is deliberately no (episode_id, meta_key) uniqueness (multi-valued attributes are allowed). episode_id carries the composite FK so a row can't cross orgs. Not versioned.

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
episode_id	UUID NOT NULL	FK→episodes(id, org_id)
meta_key	VARCHAR(100) NOT NULL	machine key, e.g. hand_model
meta_title	VARCHAR(255)	human display label, e.g. Hand model
meta_value	TEXT	the value, e.g. Brainco Revo 2
status	VARCHAR(50) NOT NULL DEFAULT 'active'	commented CHECK enum (active/archived)
created_at	TIMESTAMPTZ NOT NULL
updated_at	TIMESTAMPTZ NOT NULL	stamped by episode_metadata_touch_updated_at (§10)

Indexes: (org_id), (org_id, episode_id) (list an episode's metadata).

4.6 Versioning & snapshots

Structure here; behavior (capture, revert, snapshot/restore) is in §9.

entity_versions — append-only version history 🔒

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
entity_type	VARCHAR(50)	episode|clip|set|group (enum commented)
entity_id	UUID	polymorphic, no FK — guarded by trigger (§10)
version_no	INTEGER
parent_version_id	UUID	FK→entity_versions(id, org_id)
operation	VARCHAR(20)	create|update|delete|rollback (enum commented)
data	JSONB	full row snapshot
changed_fields	JSONB
links	JSONB	'delete' tombstone only: the entity's junction links at delete time, replayed (survivors) on resurrect (§9.3a). NULL otherwise.
edited_by	UUID	FK→organization_members(user_id, org_id)
valid_from / valid_to	TIMESTAMPTZ	valid_to IS NULL = current
is_current	BOOLEAN (true)
created_at	TIMESTAMPTZ NOT NULL

Keys: UNIQUE (entity_type, entity_id, version_no), UNIQUE (id, org_id). Indexes: (org_id, entity_type, entity_id, version_no), (parent_version_id), and partial UNIQUE one_current_per_entity (entity_type, entity_id) WHERE is_current.

snapshots 🔒

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
name	VARCHAR(255)
description	TEXT
created_by	UUID	FK→organization_members(user_id, org_id)
created_at	TIMESTAMPTZ NOT NULL

UNIQUE (id, org_id). Index: (org_id).

snapshot_members 🔒

Freezes one entity version into a snapshot (many-to-many).

Column	Type	Notes
id	UUID PK
org_id	UUID NOT NULL FK→organizations
snapshot_id	UUID NOT NULL	FK→snapshots(id, org_id)
version_id	UUID NOT NULL	FK→entity_versions(id, org_id)
created_at	TIMESTAMPTZ NOT NULL

UNIQUE (snapshot_id, version_id). Indexes: (org_id), (snapshot_id), (version_id).

4.7 Audit & auth

audit_logs — business/security event log 🔒 (BIGSERIAL)

Application-written (same txn as the action) — logins, denials, exports, invites: events a trigger can't see. Contrast with entity_versions in §9.6.

Column	Type	Notes
id	BIGSERIAL PK	not a UUID
org_id	UUID NOT NULL FK→organizations
actor_type	VARCHAR(50)	user|robot|system (enum commented)
actor_id	UUID	bare UUID, no FK — must survive actor deletion
action	VARCHAR(255)
resource_type	VARCHAR(100)
resource_id	UUID
ip_address	INET
user_agent	TEXT
metadata	JSONB	request context only the app has
created_at	TIMESTAMPTZ NOT NULL

Index: (org_id, created_at).

refresh_tokens — session tokens 🌍

Tied to a user, not an org (a user's tokens span all their orgs), so not RLS'd.

Column	Type	Notes
id	UUID PK
user_id	UUID FK→users
token_hash	TEXT
expires_at	TIMESTAMPTZ
revoked_at	TIMESTAMPTZ	set by anonymize_user() / sign-out
created_at	TIMESTAMPTZ NOT NULL

Index: (user_id).

---

5. RBAC: roles, permissions & grants

Three tiers, evaluated together by the application:

1. Role — organization_members.role_id → roles → role_permissions → permissions. The member's baseline capabilities.
2. Per-member override — user_permissions grants (granted = true) or denies (granted = false) a single permission on top of the role.
3. Per-resource grant — resource_access_grants scopes a member to specific resources (e.g. an Engineer to particular robots/episodes) at viewer|editor|owner.

5.1 Seeded roles & the permission matrix (seed_roles.sql)

Permission key	Owner	Manager	Engineer
billing.manage	✅
org.delete	✅
members.manage	✅
members.invite	✅	✅
robots.register	✅	✅
robots.delete	✅
robots.configure	✅	✅
robots.view	✅	✅	✅
data.view_all	✅	✅
data.extract	✅	✅	✅
telemetry.view	✅	✅	✅

• Owner — org creator; absolute control incl. billing & deletion.
• Manager — team + fleet, all data; no billing/org.delete/members.manage/robots.delete. (data.extract is granted as an operational capability — drop that seed line if managers shouldn't extract.)
• Engineer/Operator — view + extract only; sees robot data + telemetry, not org-wide data.view_all. Narrow it to specific robots via resource_access_grants.

The catalog is global and seeded idempotently (roles/permissions upsert on their unique key; mappings use WHERE NOT EXISTS). Re-running seed_roles.sql is safe.

5.2 The authorization lookup

Roles only prove a user was a member. Gating an action must additionally require removed_at IS NULL (see membership.sql query #4):

SELECT m.id AS org_member_id, m.role_id
  FROM organization_members m
 WHERE m.user_id = $1 AND m.removed_at IS NULL;

---

6. The two GUCs (per-transaction context)

Everything tenant-aware keys off two SET LOCAL session variables. Set them at the start of each transaction (LOCAL = scoped to the txn, so they can't leak across a pooled connection):

GUC	Required?	Read by	Purpose
app.current_org	Yes (RLS)	app_current_org(), every RLS policy, create_snapshot, delete_org, purge_trashed	The active tenant. Unset → NULL → fails closed (matches no rows).
app.current_user	Optional	capture_version (edited_by), create_snapshot (created_by), trash_episode (deleted_by)	Who is acting. NULL if unset. If set, must be a member of the org.
app.version_operation	Internal	capture_version	Set to 'rollback' by revert_entity so reverts are tagged; you normally never set this yourself.

BEGIN;
SET LOCAL app.current_org    = '<org-uuid>';   -- required
SET LOCAL "app.current_user" = '<user-uuid>';  -- optional, for attribution
-- ... ordinary queries; no org_id filter needed ...
COMMIT;

⚠️ The quotes around "app.current_user" are mandatory. current_user is a reserved SQL keyword, so the unquoted SET LOCAL app.current_user = … is a syntax error, not a silent no-op. app.current_org needs no quoting. Inside PL/pgSQL use set_config('app.current_user', <uuid>, true) instead, which is what the trigger functions do.

6.1 Developer responsibilities & failure modes

The GUCs are how you hand tenant identity to the database — get them wrong and isolation is wrong. The rules:

1. Set app.current_org first, every transaction, before any tenant query. Forgetting it is safe: app_current_org() returns NULL and you see zero rows (fail-closed), so the bug surfaces as "no data," never as a leak.
2. Always SET LOCAL, never plain SET. LOCAL clears at COMMIT/ROLLBACK, so the value can't survive onto the next request that reuses a pooled connection. A plain SET persists on the connection — the classic cross-tenant leak. (SET LOCAL also only works inside a transaction; outside one it's a no-op.)
3. Connect as tobor_app (non-superuser, non-BYPASSRLS). RLS is bypassed for superusers, so a superuser connection sees every org even though policies are FORCEd. Verify the runtime role in CI / a health check.
4. Set app.current_user to a real member when edits must be attributed (edited_by, created_by); a non-member value makes version capture fail.

Don't rely on every developer remembering. Centralize it: wrap all tenant DB access in one helper that opens a transaction, SET LOCALs the GUCs, runs the work, and commits — so no individual query path can forget or mis-scope the context. As a backstop, have the connection pool reset session state on release (DISCARD ALL) or run in transaction-pooling mode, so a stray SET can never be inherited by the next request.

---

7. Membership lifecycle (membership.sql)

Members are never hard-deleted — that would orphan every actor FK and lose "who did it" history. Instead:

• Add / reactivate — INSERT … ON CONFLICT (user_id, org_id) DO UPDATE SET removed_at = NULL, …. A returning user reuses the same row.
• Remove (soft) — UPDATE … SET removed_at = now(), removed_by = $remover WHERE user_id = $1 AND removed_at IS NULL.
• List active — WHERE removed_at IS NULL (backed by the partial index).

7.1 Invitations (member_invites, invites.sql)

People join an org through a pending invitation, the human counterpart of robot_invites. member_invites is keyed by email (the invitee may not have a user account yet) and carries the role_id to grant, 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 (member with app.current_org set; RLS keeps them in-org; the invited_by FK forces the inviter to be a member).
• Accept is special: the invitee isn't a member yet, so it can't run inside the org's RLS scope. accept_member_invite(token, user_id) is SECURITY DEFINER (authorized by the token): it validates the token 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 (superuser in dev, a dedicated BYPASSRLS role in prod).

7.2 Ownership invariant: always ≥ 1 owner

Multiple owners are allowed, but every org must always keep at least one active Owner. The enforce_min_one_owner trigger (§10) blocks the change that would drop the active-owner count to zero — i.e. demoting (role off Owner) or soft-removing the last owner. Ownership transfer is therefore: promote another member to Owner, after which the previous owner may step down. ("Owner" is the seeded role identified by roles.name = 'Owner'.)

---

8. Row-Level Security (RLS)

queries/rls.sql ENABLEs and FORCEs RLS on every tenant table, each with one FOR ALL policy named org_isolation:

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

- USING gates reads + which rows an UPDATE/DELETE may touch; WITH CHECK gates rows a write may produce — so a tenant can neither read nor create another org's data. - app_current_org() reads app.current_org and fails closed (unset → NULL → matches nothing). - organizations uses a special policy keyed on id (not org_id). - FORCE makes the policy apply even to the table owner; the app must connect as tobor_app (or any non-superuser, non-BYPASSRLS role) which rls.sql grants DML.

Under RLS, queries omit org_id filters safely — each statement is implicitly constrained to app.current_org, so a cross-tenant id just returns nothing.

8.1 Which tables are NOT under org RLS (global data)

subscription_plans, robot_models, roles, permissions, role_permissions, users, refresh_tokens. These are global catalogs / cross-org identity. (organizations is RLS'd, by id.)

8.2 Operational invariants

Isolation holds only if the app (a) connects as a non-superuser/non-owner role and (b) sets app.current_org per transaction. (b) fails closed, so a miss breaks functionality loudly rather than leaking. Never run app traffic — or restore_snapshot_hard/delete_org — on a superuser/BYPASSRLS connection.

---

9. Time travel & snapshots

A product-level version history that lives in the database — distinct from Aurora PITR/console snapshots, which are whole-cluster disaster recovery (see §13). This layer answers per-entity, per-tenant, queryable questions and supports row-level revert.

9.1 entity_versions — append-only history 🔒

One row per version; edits and rollbacks append, never mutate. entity_id is deliberately not an FK so history outlives the live row (org-consistency is enforced by a trigger instead — §10).

Column	Type	Notes
id	UUID PK (v4 today)
org_id	UUID NOT NULL
entity_type	VARCHAR(50)	episode \| clip \| set \| group
entity_id	UUID	the live entity's id (no FK)
version_no	INTEGER	1,2,3… per entity; UNIQUE (entity_type, entity_id, version_no)
parent_version_id	UUID	previous version (linear chain, no branching); FK→entity_versions(id, org_id)
operation	VARCHAR(20)	create \| update \| delete \| rollback
data	JSONB	full snapshot of the row at this version
changed_fields	JSONB	{col: new_value} diff vs parent (UPDATEs)
links	JSONB	'delete' tombstone only: junction links at delete time (§9.3a)
edited_by	UUID	from app.current_user; FK→organization_members(user_id, org_id)
valid_from / valid_to	TIMESTAMPTZ	valid_to IS NULL = current
is_current	BOOLEAN	exactly one live head per entity
created_at	TIMESTAMPTZ NOT NULL

Indexes: (org_id, entity_type, entity_id, version_no), (parent_version_id), and partial unique one_current_per_entity ON (entity_type, entity_id) WHERE is_current.

9.2 Automatic capture (capture_version triggers)

A trigger appends a version with no app involvement. Capture scope differs by entity type:

Entity	Captured on
clips, sets, groups	every INSERT / UPDATE / DELETE
episodes	only at a terminal extraction outcome — status → 'processed' or 'failed', on DELETE (tombstone), and on an admin revert. Never on the transient recording/uploading/uploaded/processing states, nor on edits that don't move status into a terminal state.

The episode triggers are split per operation (capture_version_insert / _update / _delete) because a trigger WHEN clause can read OLD only on UPDATE/DELETE and NEW only on INSERT/UPDATE. The app.version_operation = 'rollback' arm keeps revert_entity's revert/resurrect capturing as before. Consequence: the first episode version is an 'update' (the extraction), and an episode that never reaches a terminal state has no version row.

9.3 Revert one entity — revert_entity(entity_type, id, version_no)

Admin/operator row-level recovery (not a user feature): - Live row → UPDATE it back to the snapshot (keeps id/org_id/created_at). - Deleted row → resurrect: re-INSERT the full snapshot under the original id (requires the snapshot's referenced parents — robot/session — to still exist).

It tags the result operation = 'rollback' (via app.version_operation). On a resurrect it also resets episode/clip trash columns to live (§11.1) and re-attaches junction links (§9.3a).

9.3a Junction-link preservation on delete & revert

Junctions (episode_sets/set_groups) aren't versioned (§9 / DATABASE.md §2.6), so deleting a content entity used to lose its associations. They're now preserved without versioning the junctions:

• Capture at delete. Because junction FKs are RESTRICT, the links are gone by the time the capture_version DELETE trigger fires. So the delete helpers (delete_episode/delete_set/delete_group, §11.2) snapshot the entity's current links into the app.captured_links GUC before removing the junctions; capture_version writes them onto the tombstone's entity_versions.links. Shapes: episode → {"sets":[…]}, group → {"sets":[…]}, set → {"episodes":[…],"groups":[…]} (the set is the hub; episode↔group is only indirect).
• Replay on resurrect. revert_entity re-inserts junction rows from links only for ends that still exist (re-linked episodes must be live, not trashed) — "re-join the survivors." Written only on 'delete' tombstones, so reverting a live entity doesn't touch its links, and a restore_snapshot resurrect (frozen from a live head, no links) restores fields but not associations.
• Never silent. The require_link_capture guard (§10) blocks a raw DELETE of episodes/sets/groups under the app role, forcing the helper path.

9.4 Snapshots — create_snapshot / restore_snapshot

• create_snapshot(name, description, entity_types[]) → freezes the current head of every matching entity into snapshots + snapshot_members. created_by from app.current_user; scoped to app.current_org. Returns the new snapshot id.
• restore_snapshot(snapshot_id) → revert_entity for every captured entity in dependency order (sets/groups → episodes → clips, so a resurrected child never precedes its parent). Additive: restores the captured set; does not delete entities created since.

snapshots: id, org_id, name, description, created_by, created_at, UNIQUE (id, org_id). snapshot_members: snapshot_id, version_id, UNIQUE (snapshot_id, version_id), composite FKs to snapshots and entity_versions.

9.5 restore_snapshot_hard(snapshot_id) — point-in-time reset ⚠️

Additive restore plus deletion of every versioned entity the snapshot didn't capture (and the non-versioned children/junctions of removed episodes), children-first. Destructive & not undoable for non-versioned children. RLS-scoped to the current tenant; guards that the snapshot exists in this org so a wrong id can't be read as "empty" and wipe the tenant. Never run on a superuser/BYPASSRLS connection. For whole-database point-in-time recovery, prefer Aurora PITR (§13).

9.6 entity_versions vs audit_logs

	entity_versions	audit_logs
Written by	DB triggers	Application
Captures	what a row looked like + who/when	intent + request context (ip, user_agent, denials, logins, exports)
Sees non-writes?	No (only row changes)	Yes
Both take the actor from app.current_user. Don't mirror data changes into
audit_logs (redundant); don't put a blanket audit trigger on high-volume tables.

9.7 Set revisions — mint_set_revision (set_revisions.sql)

The membership history behind §4.5a. Complementary to entity_versions, not part of it: entity_versions tracks a set's own columns (name, description); revisions track which episodes it contained.

Minting is automatic and deferred. mint_set_revision is a DEFERRABLE INITIALLY DEFERRED constraint trigger on episode_sets, so it fires at COMMIT and sees the transaction's final membership. A transaction-local marker (app.minted_set_revisions) makes it one revision per set per transaction — adding 200 episodes in one commit yields one revision, not 200.

Consequence for testing and for interactive use: a revision is not visible inside the transaction that caused it. tests/10_set_revisions.sql therefore uses explicit BEGIN/COMMIT blocks rather than one big DO block.

What does not mint a revision:

Case	Why
Net-zero change (add + remove the same episode in one transaction)	the manifest would duplicate the previous revision
delete_set()	the set is gone by COMMIT; its links are already on the 'delete' tombstone (§9.3a). Revisions minted earlier survive — set_id is not an FK
delete_org(), purge_trashed(), restore_snapshot_hard()	they set app.captured_links = 'skip'; a retention job should not author dataset versions attributed to nobody

delete_episode() does mint — removing an episode genuinely changes what the set contains. Note the purge_trashed() consequence: purging drops episode_sets links without minting, so the live set and its newest revision diverge until the next real edit. Every existing manifest still names the purged episode.

Reading: - set_revision_manifest(revision_id) → the frozen episode list plus a per-row availability of 'live' / 'trashed' / 'purged'. - set_revision_as_of(set_id, timestamptz) → the revision id current at that moment, or NULL if the set had no revision yet. This is the "what did I train on last Monday" path when the caller kept a timestamp instead of a revision id.

9.8 Group revisions — mint_group_revision (group_revisions.sql)

The membership history behind §4.5b, and a structural mirror of §9.7 with the container/member types swapped: mint_group_revision is a DEFERRABLE INITIALLY DEFERRED constraint trigger on set_groups, freezing which sets a group contained. Every rule in §9.7 applies unchanged — deferred minting, the not-visible-inside-its-own-transaction consequence (tests/11_group_revisions.sql uses explicit BEGIN/COMMIT for exactly this reason), net-zero suppression, and the 'skip' exemptions for delete_org() / purge_trashed() / restore_snapshot_hard().

The two histories are independent. Separate revision_no sequences, and a separate transaction marker (app.minted_group_revisions, not app.minted_set_revisions) — so one transaction can legitimately mint on both sides, and re-tagging a set into a different group never disturbs the dataset revisions a training run cites.

What does not mint, beyond the shared cases: delete_group() — the group is gone by COMMIT; its links are already on the 'delete' tombstone (§9.3a), and earlier revisions survive because group_id is not an FK.

delete_set() does mint, once for every group that set belonged to: removing a set genuinely changes what those groups contain. The set's own row is gone by COMMIT, so it is simply absent from the new revision — and still present, flagged 'deleted', in every older one.

Reading: - group_revision_manifest(revision_id) → the frozen set list plus a per-row availability of 'live' / 'deleted' (two states, not three — sets have no trash window). - group_revision_as_of(group_id, timestamptz) → the revision id current at that moment, or NULL if the group had no revision yet. - group_revision_set_revisions(revision_id) → the drill-down: one row per frozen set, paired with set_revision_as_of(set_id, <this revision's created_at>). This is how group → sets → episodes resolves without either history duplicating the other. set_revision_id is NULL when that set had no episode membership at freeze time; the row is still returned rather than dropped.

---

10. Triggers & org-guards (triggers.sql)

Guards for the two polymorphic columns that can't be FKs (run SECURITY INVOKER): - 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 (group/episode/clip/set/snapshot). - enforce_min_one_owner (AFTER UPDATE on organization_members) — blocks demoting or soft-removing the last active Owner, so every org keeps ≥ 1 owner. Fires on UPDATE only, so delete_org's whole-org hard delete is not affected. No-op if the Owner role isn't seeded. - require_link_capture (BEFORE DELETE on episodes/sets/groups) — rejects a raw DELETE under the application role, forcing it through delete_episode/_set/_group() so junction links are snapshotted (§9.3a). Superuser/owner (admin/migration/test) is exempt; bulk teardown sets app.captured_links = 'skip' to pass. - set_revisions_org_guard / set_revision_episodes_org_guard — same shape as entity_versions_org_guard, for the bare set_id / episode_id columns (§4.5a). A revision pointing at an already-deleted set or episode is allowed by design — that is the feature. - group_revisions_org_guard / group_revision_sets_org_guard — the same shape again, for the bare group_id / set_id columns (§4.5b). Pointing at an already-deleted group or set is likewise allowed by design. - set_revisions_immutable (on set_revisions/set_revision_episodes) and group_revisions_immutable (on group_revisions/group_revision_sets), both BEFORE UPDATE OR DELETE and both backed by the shared revisions_immutable() function — all four revision tables are append-only under tobor_app: a revision is a citation and must never change. Superuser exempt; delete_org passes via app.captured_links = 'skip'. - mint_set_revision (CONSTRAINT TRIGGER … DEFERRABLE INITIALLY DEFERRED on episode_sets, in set_revisions.sql) — freezes a new revision at COMMIT. See §9.7. - mint_group_revision (CONSTRAINT TRIGGER … DEFERRABLE INITIALLY DEFERRED on set_groups, in group_revisions.sql) — the group-side counterpart. See §9.8. - touch_updated_at (BEFORE UPDATE, one <table>_touch_updated_at trigger per table carrying the column) — assigns NEW.updated_at := now(). The database owns the column, not the app: an UPDATE that forgets it cannot leave a stale timestamp, and one that supplies a wrong value is overridden. Note now() is the transaction timestamp, so every row touched in one transaction shares it. Attached to: organizations, users, organization_members, subscriptions, robot_models, robots, sessions, episodes, clips, episode_metadata, sets, groups. Deliberately not attached to — and these tables have no updated_at at all: the append-only tables (audit_logs, role_change_logs, entity_versions, snapshots, snapshot_members, the four revision tables, upload_parts) and the junctions (episode_sets, set_groups, role_permissions), which are never updated; the seeded catalogs roles/permissions; and subscription_plans/invoices, which by design are never edited in place at all — their one legal transition is already recorded by valid_until/voided_at (§4.2).

---

11. Admin lifecycle (admin_lifecycle.sql)

• delete_org(org_id) — hard-deletes a whole tenant. Runs under RLS as tobor_app; refuses unless app.current_org = target org. Deletes children-first; removes entity_versions after the versioned tables (so teardown's own 'delete' tombstones are cleaned up). Doesn't touch refresh_tokens (user-global).
• anonymize_user(user_id) — GDPR erasure. Scrubs users PII in place (keeps id so references stay valid), revokes refresh_tokens. Global, so it erases the person across all orgs at once; soft-remove memberships per-org if access should also end.

11.1 Episode trash / restore / purge

The two-tier deletion model for episode data (see §2.4 for the rationale). All three run under RLS as tobor_app (SECURITY INVOKER, app.current_org must be set); the S3/Glacier byte movement is the app's job — these functions only manage DB state and return the S3 paths to act on.

• trash_episode(episode_id, retention_days DEFAULT NULL) → timestamptz — soft-delete an episode into the trash. Sets deleted_at/purge_after/deleted_by (deleted_by from app.current_user) and storage_state='glaciering'; cascade-trashes its clips (clips.deleted_at); hard-deletes its upload_parts. episode_sets links are left in place. Retention precedence: explicit arg → the org plan's retention_days → 30-day default. Returns the computed purge_after.
• restore_episode(episode_id) → TABLE(mp4_s3_path, parquet_s3_path) — pull it back out of the trash (in-window). Clears the trash columns, resurrects the cascade-trashed clips (every soft-deleted clip of the episode, since clips have no independent trash), and sets storage_state='restoring' as the readiness signal: the app thaws the Glacier objects, then flips storage_state='standard'. Returns the paths to thaw.
• purge_trashed() → TABLE(purged_episode_id, mp4_s3_path, parquet_s3_path) — the scheduled-job entry point. Hard-deletes every episode past its purge_after, children-first (upload_parts/episode_sets/clips, then the episode), firing the 'delete' tombstones for clips and episodes. The set links die; the sets themselves survive. Returns the purged episodes' S3 paths so the caller can delete the Glacier objects. RLS-scoped: the scheduler loops orgs, setting app.current_org per transaction — never run it on a superuser/BYPASSRLS connection.

An admin revert_entity() of a purged episode's tombstone resurrects it live (the resurrect path resets deleted_at/purge_after/storage_state), so a past-due trashed state can't bring it straight back for immediate re-purge.

11.2 Link-preserving deletes — delete_episode / delete_set / delete_group

The sanctioned hard-delete path for content entities (vs. trash_episode, the recoverable path). Each snapshots the entity's junction links onto the 'delete' tombstone (§9.3a) so a later revert_entity re-attaches the survivors, then deletes the entity. The require_link_capture guard (§10) blocks the raw DELETE they replace, so the app role cannot drop these links silently. All SECURITY INVOKER, run under RLS as tobor_app.

• delete_set(set_id) — captures {episodes, groups}, removes episode_sets + set_groups for the set, deletes the set.
• delete_group(group_id) — captures {sets}, removes set_groups, deletes the group.
• delete_episode(episode_id) — the permanent-delete-now path: captures {sets}, removes episode_sets + upload_parts, deletes the episode. Clips are content with their own tombstones — remove/reassign/promote them first or the clips FK refuses the delete. Refuses on a trashed episode (use restore_episode first, or let purge_trashed remove it).

Bulk teardown (delete_org, restore_snapshot_hard, purge_trashed) sets app.captured_links = 'skip' and does not preserve links — those entities are permanently discarded.

---

12. Developer recipes

Insert an episode (app-generated v7 id):

BEGIN;
SET LOCAL app.current_org    = :org;
SET LOCAL "app.current_user" = :user;   -- quotes required (reserved keyword)
INSERT INTO episodes (id, org_id, robot_id, session_id, status, recorded_at, created_at)
VALUES (:v7_id, :org, :robot, :session, 'recording', now(), now());
COMMIT;   -- no version captured yet (not terminal)

Mark extraction complete (captures version v1):

BEGIN;
SET LOCAL app.current_org    = :org;
SET LOCAL "app.current_user" = :user;   -- quotes required (reserved keyword)
UPDATE episodes
   SET status = 'processed', parquet_s3_path = :parquet, frame_count = :n,
       processed_at = now()
 WHERE id = :episode;     -- status transition into 'processed' fires capture
COMMIT;

Read an episode as of a past time T (time-travel):

SELECT data
  FROM entity_versions
 WHERE entity_type = 'episode' AND entity_id = :episode
   AND valid_from <= :T AND (valid_to IS NULL OR valid_to > :T);

Take and restore a snapshot:

SELECT create_snapshot('pre-cleanup', 'before bulk edit');   -- returns snapshot id
SELECT restore_snapshot(:snapshot_id);                       -- additive

Soft-remove a member: see membership.sql #2.

---

13. Backups: this vs Aurora PITR

They solve different problems — keep both, for different jobs:

	Aurora PITR / console snapshots	entity_versions / snapshots
Granularity	whole cluster, all tenants	one row / one entity / one org
Restore	new endpoint, minutes–hours	in-place, transactional, ms
Queryable?	no (restore op)	yes (SELECT)
Use	disaster recovery	per-entity undo, lineage, checkpoints

Rule of thumb: mass corruption / bad migration → Aurora PITR; undo/inspect a single episode or tenant checkpoint → in-DB versioning. restore_snapshot_hard overlaps Aurora — prefer Aurora for true point-in-time recovery.

---

14. Known limitations / residual risk

• entity_versions.entity_id cross-org dangling pointer. Under SECURITY INVOKER RLS, a cross-org entity is invisible, so the guard can't distinguish "deleted (allow)" from "another org's (reject)" and allows it — an inert dangling pointer, not a leak. Admin/superuser write paths (RLS off) do catch it.
• Isolation depends on operational discipline — non-superuser role + app.current_org per txn (fails closed).
• audit_logs.actor_id is a bare UUID (no FK) by design.
• organizations.plan is deprecated; don't build new logic on it.
• Enum CHECKs are disabled (commented) — invalid status/type strings are currently accepted until the lifecycles are finalized and the CHECKs enabled.

---

15. Keeping this doc alive

• Source of truth is the SQL. When you change schema.sql, a trigger, an RLS policy, or the role catalog, update the matching section here in the same change.
• Update the Last reviewed date at the top.
• Add a one-line entry to the changelog below.
• High-churn spots to watch: the permission matrix (§5.1), the episode capture scope (§9.2), the non-RLS table list (§8.1), and the UUIDv7 stance (§2.1).

Changelog

Date	Change
2026-06-25	Initial reference. Episode versioning scoped to terminal outcomes (processed/failed/delete/revert); UUIDv7 PK convention documented.
2026-06-25	Added episodes.origin ('original'/'promoted') + promoted_from_clip_id (bare UUID) to record clip→episode promotion.
2026-06-25	Enforced UUIDv7 in-DB: check_uuidv7_version CHECK (uuid_extract_version(id) = 7) on every UUID id PK (PG 18+). Swapped all DB-side gen_random_uuid() → uuidv7() (versioning triggers, seeds, tests) and rewrote test fixtures as genuine v7 (also fixed two malformed fixture UUIDs in tests 04/06).
2026-06-25	Requirements raised to PostgreSQL 18+. Removed clips → episodes ON DELETE CASCADE (clip deletion is now application-level). Validated the full pipeline + seed on PG 18.
2026-06-25	Added member_invites (email-keyed human invitations) + accept_member_invite() (invites.sql). Added the enforce_min_one_owner trigger: multiple owners allowed, but every org must keep ≥ 1 active owner. Covered by tests/07_invites_and_ownership.sql.
2026-06-25	Documented the deletion model (§2.4): selective soft-delete (members/users) + hard-delete-with-versioning elsewhere, and the all-RESTRICT (no cascade / no SET NULL) guarantee — the deliberate, better-than-literal answer to "soft deletion throughout, no null-FK cascades".
2026-06-27	Added the episode trash / restore / purge lifecycle (§2.4, §11.1). New columns episodes.deleted_at/deleted_by/purge_after/storage_state + clips.deleted_at (+ partial indexes); new functions trash_episode()/restore_episode()/purge_trashed(). Trash soft-deletes the episode + cascade-trashes clips, hard-deletes frames/upload_parts (re-projected from the parquet on restore), keeps episode_tasks links until purge. revert_entity() resurrect now resets the trash columns so an admin revert comes back live. Covered by tests/08_trash_lifecycle.sql (full pipeline + all tests green).
2026-07-02	Added explicit trial support to subscriptions: trial_start / trial_end TIMESTAMPTZ (both nullable; CHECK (trial_end > trial_start), skipped when NULL) recorded as a permanent fact distinct from current_period_* so trial dates survive conversion to active. New partial index (trial_end) WHERE status='trialing' for the expiring-trials ops query. status↔date consistency stays app-enforced; a trial produces no invoice (first charge at trial_end).
2026-07-06	Added episode_metadata (§4.5): EAV side table of per-episode key/value attributes (meta_key/meta_title/meta_value + status). Composite FK (episode_id, org_id)→episodes; no (episode_id, meta_key) uniqueness — the same key may repeat (multi-valued). Indexes (org_id), (org_id, episode_id). Under org RLS (standard org_isolation policy, rls.sql). Not versioned.
2026-06-28	Junction-link preservation on delete/revert (§9.3a, §11.2, DATABASE.md §2.6a). New entity_versions.links column; new delete_episode()/delete_task()/delete_group() helpers snapshot an entity's episode_tasks/task_groups links onto its 'delete' tombstone; revert_entity() re-attaches the surviving ends on resurrect. New require_link_capture BEFORE DELETE guard blocks raw deletes of episodes/tasks/groups under the app role (superuser exempt; delete_org/restore_snapshot_hard/purge_trashed set app.captured_links='skip'). Verified on native PostgreSQL 18.4. Covered by tests/09_link_preservation.sql; tests/03/05/08 updated for the guard.
2026-08-10	Set revisions — citable dataset versions (§4.5a, §9.7, §10). New append-only set_revisions + set_revision_episodes; every episode_sets change mints a frozen manifest via the DEFERRABLE INITIALLY DEFERRED mint_set_revision constraint trigger (queries/set_revisions.sql), one revision per set per transaction. set_id/episode_id are bare UUIDs (no FK) so a citation outlives delete_set() and a manifest outlives purge_trashed(); org-consistency via new guard triggers, immutability via set_revisions_immutable. New readers set_revision_manifest() (per-row live/trashed/purged) and set_revision_as_of(). episode_sets itself is unchanged — no existing read path is affected. No backfill: sets get revision 1 on their next membership change. Covered by tests/10_set_revisions.sql; tests/08/09 teardowns updated (revision tables clear after episode_sets, like entity_versions).
2026-08-10	Added episodes.name VARCHAR(255) (nullable — a robot creates the row before anyone names it), mirroring the existing sets.name/groups.name; frozen into the revision manifest. Split users.full_name → first_name / last_name (anonymize_user() now writes 'Deleted'/'User'); seeds and tests/01/06 updated.
2026-08-10	Doc fix (pre-existing bug): SET LOCAL app.current_user = … is a syntax error — current_user is a reserved keyword. Corrected to SET LOCAL "app.current_user" = … in §6, §12 and the versioning_triggers.sql header. app.current_org is unaffected.
2026-08-07	Renamed tasks → sets throughout, and renamed the free-text episodes.task_description / clips.task_description columns to task. Junctions episode_tasks → episode_sets and task_groups → set_groups (link column task_id → set_id); cardinality is unchanged (still many-to-many, §4.6). Versioning/grants vocabulary follows: entity_versions.entity_type and resource_access_grants.resource_type value 'task' → 'set', delete_task() → delete_set(), capture_version('task') → capture_version('set'), and the captured_links JSON key "tasks" → "sets". Applied to schema.sql + per-table DDL, rls.sql, triggers.sql, versioning_triggers.sql, admin_lifecycle.sql, reset_and_seed.sql and all tests. Earlier changelog rows keep the names in use at the time.
2026-08-11	Group revisions — citable collection versions (§4.5b, §9.8, §10). The group-side mirror of the 2026-08-10 set revisions: new append-only group_revisions + group_revision_sets; every set_groups change mints a frozen manifest of which sets a group contained, via the DEFERRABLE INITIALLY DEFERRED mint_group_revision constraint trigger (queries/group_revisions.sql), one revision per group per transaction (marker app.minted_group_revisions, independent of the set-side marker and of revision_no). group_id/set_id are bare UUIDs (no FK) so a citation outlives delete_group() and a manifest outlives delete_set(); org-consistency via new guard triggers. The immutability function was generalized set_revisions_immutable() → revisions_immutable(), now backing both the existing set_revisions_immutable triggers and the new group_revisions_immutable ones (trigger names unchanged). New readers group_revision_manifest() (per-row live/deleted — two states, not three, since sets have no trash window), group_revision_as_of(), and group_revision_set_revisions(), which resolves a group revision down to the set revision current at the same instant so the two histories compose instead of duplicating. set_groups itself is unchanged — no existing read path is affected. No backfill: groups get revision 1 on their next membership change. This reverses the "set_groups stays unhistorised" position recorded in DATABASE.md §2.6, which has been rewritten. Covered by tests/11_group_revisions.sql; tests/01/09 teardowns updated (group revision tables clear after set_groups, like the set side) and delete_org() + reset_and_seed.sql extended.
2026-08-11	Added sets.created_at + sets.updated_at and groups.updated_at, stamped by the new shared touch_updated_at() BEFORE UPDATE trigger (sets_touch_updated_at / groups_touch_updated_at, §10) rather than by the application — a caller that forgets it cannot leave a stale value, and one that supplies a wrong value is overridden. First updated_at columns in the schema. They track the row, not its membership: junction writes do not move them (§4.5 note). Added users.cognito_id VARCHAR(255) UNIQUE (nullable AWS Cognito sub; global, and deliberately left alone by anonymize_user() — it is an opaque subject id, not a secret). Added episodes.readiness_state VARCHAR(50) — a curation gate orthogonal to the pipeline status (an episode can be 'processed' and 'rejected'), NULL until assessed, commented enum raw\|needs_review\|reviewed\|ready\|rejected, with a partial index (org_id, readiness_state) WHERE deleted_at IS NULL. clips.status already existed and was left unchanged. Covered by new sections 5–7 in tests/01_schema_integrity.sql.
2026-08-13	clips: renamed task → description and dropped fps (with its CHECK (fps > 0)) — frame rate is a property of the source episode, and a clip is only a span into it. Updated schemas/clips.ddl, schemas/schema.sql, reset_and_seed.sql, and tests/01/08. episodes.task/episodes.fps are unchanged.
2026-08-25	Episode score, frames dropped, GPU metering, billing history. (1) New episodes.aggregate_score NUMERIC(5,2) NOT NULL DEFAULT 0 with CHECK BETWEEN 0 AND 100 — a rolling quality score mutated in place as scoring runs, replacing the commented-out TODO; defaults to 0 (not NULL) so unscored episodes sort last without a COALESCE, and stays distinct from the readiness_state curation gate. (2) Dropped the frames table and every connection to it: schemas/frames.ddl deleted, the CREATE TABLE/index removed from schema.sql, its RLS policy removed, the DELETE FROM frames steps removed from purge_org/trash_episode/purge_trashed/delete_episode/restore_snapshot_hard, and the seed + tests/08 fixtures/assertions updated. episodes.frame_count (a scalar) stays; the episode's parquet in S3 is the system of record for frame-level data. (3) GPU cost is metered on the payment tables: subscription_plans.gpu_rate_per_hour + included_gpu_hours, and invoices.gpu_hours / gpu_rate_per_hour / gpu_cents — the same GB-metered triplet shape as storage/streaming, rate snapshotted at issue time; total_cents now includes GPU. (4) History preservation on the billing tables (§4.2): is_valid plus complementary columns on subscription_plans (valid_from, valid_until, superseded_by_id) and invoices (voided_at, void_reason, superseded_by_invoice_id, self-referential composite FK). Repricing/correcting supersedes rather than edits, so historical plan_id references still resolve to the rate card actually charged. subscription_plans.name loses its plain UNIQUE in favour of partial UNIQUE (name) WHERE is_valid. Covered by the new tests/12_score_and_billing_history.sql; full pipeline + all 12 test files green on PostgreSQL 18 (61 PASS, exit 0).
2026-08-25	Robot models, org branding, and row timestamps. (1) New global catalog robot_models 🌍 (§4.4): manufacturer, model_name, version, kind, degrees_of_freedom, default_fps, sensor_spec JSONB, description, is_active, keyed UNIQUE NULLS NOT DISTINCT (manufacturer, model_name, version) so an unversioned model can't be entered twice. robots gains a nullable plain FK robot_model_id (the catalog has no org_id to composite-match) + index; the FK is RESTRICT, so a model in use is retired via is_active = false, never deleted. Not under RLS — added to the global-tables lists in rls.sql and §8.1. (2) organizations gains logo_url TEXT and timezone VARCHAR(64) NOT NULL DEFAULT 'UTC'; the timezone is a display preference only (all storage stays TIMESTAMPTZ/UTC) and its CHECK is a shape guard only — real IANA validation against pg_timezone_names is app-side, since a CHECK allows neither a subquery nor a safely-IMMUTABLE lookup. (3) created_at added to roles, permissions, role_permissions, sessions, robot_invites, snapshot_members, upload_parts (and on the new robot_models), all NOT NULL DEFAULT now(); organization_members deliberately keeps joined_at as its only creation stamp. Where a table already had a different-meaning stamp, both now exist and are documented as distinct (sessions.started_at, upload_parts.uploaded_at). (4) updated_at added to organizations, users, organization_members, subscriptions, robot_models, robots, sessions, episodes, clips, episode_metadata — joining the existing sets/groups — each with its own <table>_touch_updated_at BEFORE UPDATE trigger reusing the shared touch_updated_at() function. Deliberately not added to append-only tables/junctions, the seeded roles/permissions catalogs, or subscription_plans/invoices, which are never edited in place (their one transition is already recorded by valid_until/voided_at). See DATABASE.md §2.3a and §2.9a. Covered by the new tests/13_robot_models_and_timestamps.sql; full pipeline + all 13 test files green on PostgreSQL 18 (65 PASS, exit 0).