Migration 0195 for the ActionMode video editor adds five tables. Before any of it touched a shared environment, a throwaway Postgres in CI ran the real migration, switched JWT claims to impersonate a second tenant, and confirmed that every cross-tenant read, update and insert failed in the expected way. The test lives alongside the migration and runs on every pull request.
The Five Tables and What They Hold
The migration creates:
- video_recordings: the top-level take, owned by a project.
- video_segments: individual clips cut from a recording.
- video_captions: transcript lines attached to a segment.
- video_thumbnails: candidate thumbnails for a segment.
- video_render_jobs: async export work queued by the worker.
Each table carries an owns_project RLS policy. That policy compares the row's project_id against the projects the authenticated user owns, using a claim from the JWT. A session with the wrong tenant's claims sees no rows at all.
Why RLS Alone Is Not Enough
RLS handles reads and updates well. Inserts are trickier.
If a child table only has a project_id column and an RLS insert policy, a determined caller could still attempt to insert a segment under a recording that belongs to a different project, as long as they supply a matching project_id. The policy might pass because the project_id they supplied is one they own, even though the recording is not.
Composite foreign keys close that gap. Every child table references both the parent row and its project_id together. The database checks that the combination exists in the parent. If a segment insert names a recording from project A but supplies project B's ID, the FK raises foreign_key_violation immediately, before any application logic runs.
This matters even for the service-role worker. Service role bypasses RLS, but it does not bypass FK constraints. The test asserts this explicitly: a cross-project segment insert as service_role must still fail.
Column Grants: Keeping the Browser in Its Lane
Not every column should be writable from the browser. The grant structure reflects what each caller actually needs:
- Browser (authenticated role): can flip
keptandtrim_enabledon segments, and write caption text and thumbnail selections. - Worker (service role): owns transcript timestamps, segment status transitions and all render job fields.
Column-level grants enforce this at the database layer. Even if application code had a bug that tried to write a transcript timestamp from the browser, Postgres would reject it. There is no relying on the API layer to filter the payload correctly.
What the SQL Test Actually Asserts
The test file is apps/web/tests/sql/video-editor-isolation.sql. It runs three classes of assertion against owner B trying to reach owner A's data:
Cross-tenant reads:
SELECTon A's recordings, segments, captions and thumbnails returns 0 rows when the session carries B's JWT claims.
Cross-tenant updates:
UPDATEstatements targeting A's rows touch 0 rows. The RLS policy filters them out silently, which is the correct behaviour.
Cross-tenant inserts:
- Inserting a segment under A's recording raises
insufficient_privilegeorforeign_key_violation, depending on which constraint fires first. - Inserting as service_role with a mismatched project raises
foreign_key_violationbecause the FK check runs regardless of RLS.
Asserting all three separately matters. A policy that blocks reads might still allow an insert if the insert policy is misconfigured. Checking each failure mode independently means a regression in one does not hide behind a passing check in another.
How the Runner Works
scripts/test-video-editor-sql.py does the following:
- Spins up a disposable Postgres (no shared state, no existing data).
- Applies migration 0195 in full, exactly as it would run in production.
- Seeds the minimum rows needed: two projects, two owners, one recording per project.
- Executes
video-editor-isolation.sqland collects results. - Fails the step if any assertion does not match the expected outcome.
The runner is wired into the Database and operations check in .github/workflows/safety-checks.yml. It runs on every pull request that touches the apps/web/tests/sql/ or supabase/migrations/ paths.
Using the real migration rather than a hand-crafted schema is the important part. If the migration has a typo in a policy name or a missing grant, the test catches it because it is running the same SQL that production would run.
Setting JWT Claims in a Test Session
Supabase RLS policies that read request.jwt.claims rely on a Postgres session variable. In a test, you set it directly:
SELECT set_config(
'request.jwt.claims',
'{"sub": "owner-b-uuid", "role": "authenticated"}',
true
);From that point in the session, any RLS policy that checks auth.uid() or reads claims from the JWT sees owner B's identity. The test switches between owner A and owner B by resetting this variable, so the same file can assert both directions of isolation.
This is not a Supabase-specific trick. It is standard Postgres session configuration. Any test runner that can execute SQL can do it.
Fitting Into the Broader PR Sequence
This migration is PR 2 of 6 for the video editor. The isolation proof being in CI from PR 2 means every subsequent PR that touches the schema has to keep the test green. If PR 4 adds a column and forgets to update the grant, the test fails. The isolation guarantee does not drift silently as the feature grows.
ActionMode is public on dil.dev if you want to see the broader context.
What I'd do differently
The test seeds the minimum rows to make assertions pass. That is fine for isolation checks, but it means the test does not catch policies that accidentally allow reads on empty tables (a policy that returns true when there are no rows to filter). I'd add an explicit assertion that a table with rows belonging to owner A returns exactly the right count when queried as owner A, so the test proves the policy is actually being evaluated rather than just not blocking an empty result set.
I'd also separate the service-role FK assertion into its own test step with a clearer label. Right now it lives in the same file as the JWT-based assertions, which makes the failure message slightly ambiguous when something goes wrong. Two clearly named steps would make a CI failure easier to diagnose at a glance.




