Summary

Migration v35 auto-RLS event trigger — every gbrain brain becomes secure by default on upgrade.

A production incident on 2026-05-04 found face_detections (a Baku-created table) sitting in the Supabase project without Row Level Security enabled. gbrain doctor caught it after the fact, but the gap window between create and next doctor run was the silent vector. v0.26.8 closes that gap from both sides.

The original PR #612 commit (86e7b7a) shipped a first-cut event trigger. This PR was then revised through /plan-eng-review (7 architectural decisions D1-D7) and /codex outside-voice consult (11 specific corrections), producing the final shape:

• Postgres DDL event trigger auto_rls_on_create_table fires on every ddl_command_end for WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS', 'SELECT INTO') and runs ALTER TABLE … ENABLE ROW LEVEL SECURITY for every new public.* table.
• One-time backfill of every existing public.* base table without RLS, honoring doctor's exact ^GBRAIN:RLS_EXEMPT\s+reason=\S.{3,} regex and quoting identifiers via format('%I.%I', schema, table).
• doctor rls_event_trigger check verifies the trigger is installed and enabled (evtenabled ∈ {O, A}).
• Deleted the dead checkRls() from src/core/supabase-admin.ts (zero callers, hardcoded 10-table list disagreed with doctor's posture).

Posture: ENABLE only (no FORCE), public-schema-only (Supabase manages auth/storage/realtime), no EXCEPTION wrap inside the trigger (event triggers fire inside the DDL transaction, so a failed ALTER aborts the offending CREATE TABLE — wrapping would have replaced loud rollback with silent permissive default).

Test Coverage

TEST COVERAGE — migration v35
[+] test/e2e/migration-v35-auto-rls.test.ts (Postgres-only, 12 cases, all pass)
  ├── [★★★ TESTED]  event trigger exists
  ├── [★★★ TESTED]  CREATE TABLE auto-enables RLS
  ├── [★★  TESTED]  auto_enable_rls function exists
  ├── [★★★ TESTED]  FORCE RLS NOT applied (D1 ENABLE only)
  ├── [★★★ TESTED]  CREATE TABLE AS triggers auto-RLS (D6)
  ├── [★★★ TESTED]  SELECT INTO triggers auto-RLS (D6)
  ├── [★★★ TESTED]  non-public schemas not touched (D2)
  ├── [★★★ TESTED]  replay idempotency (DROP IF EXISTS + CREATE round-trip)
  ├── [★★★ TESTED]  backfill enables RLS on pre-existing public.* tables
  ├── [★★★ TESTED]  backfill respects GBRAIN:RLS_EXEMPT (matches doctor regex)
  ├── [★★★ TESTED]  backfill quotes mixed-case identifiers safely (%I.%I)
  └── [★★★ TESTED]  regression guard: no EXCEPTION WHEN OTHERS in trigger body

[+] test/migrate.test.ts (9 structural guards, all pass)
  ├── v35 exists with expected name and SQL shape
  ├── PGLite no-op override (sqlFor.pglite === '')
  ├── No FORCE ROW LEVEL SECURITY (D1)
  ├── public-only schema scope (D2)
  ├── WHEN TAG covers all 3 syntaxes (D6)
  ├── No EXCEPTION WHEN OTHERS (D5 reversed)
  ├── %I.%I identifier quoting in backfill
  ├── Backfill exemption regex matches doctor.ts
  └── BYPASSRLS gate

[+] test/doctor.test.ts (1 new structural guard)
  └── rls_event_trigger check exists, scoped after schema_version, healthy on (O,A)

[-] test/e2e/mechanical.test.ts
  └── RLS regression test updated: simulates post-v35 escape route (operator drops trigger, creates RLS-off table) since CREATE-then-DISABLE is now closed by the trigger

COVERAGE: 22/22 paths tested (100%)
QUALITY: ★★★:21 ★★:1 ★:0  |  GAPS: 0

Tests: 3720 → 3748 unit (3748 pass after master merge), +12 v35 E2E cases. Master's #613 test isolation work fixed the EMBEDDING_MODEL flake.

Pre-Landing Review

/plan-eng-review found 6 architectural issues + 2 code-quality items + 6 test gaps + 5 silent-failure modes; 7 decisions resolved (D1-D7), 2 TODOs filed.

Adversarial Review

/codex outside-voice consult returned 11 specific findings, all material ones integrated into the plan. Two decisions reversed (D4 privilege pre-check dropped, D5 EXCEPTION wrap removed) and two new decisions resolved (D6 CTAS coverage, D7 dead-surface deletion). Codex caught 4 corrections eng review missed: D5 inversion, fake pg_create_event_trigger role, evtenabled='R' healthy classification, GBRAIN:RLS_EXEMPT regex drift.

Plan Completion

7/7 decisions resolved. All 11 implementation items DONE. Lake score: 7/7 chose the complete-coverage option after codex correction.

Verification Results

• bun run typecheck: clean
• bun run test: 3748 pass, 0 fail
• Postgres E2E (v35 + mechanical): 89 pass, 1 pre-existing failure (gbrain doctor exits 0 on healthy DB — verified to fail on master baseline, unrelated)
• Live smoke against test container: trigger installed, backfill flips RLS on all RLS-off public tables, comment-exemption respected, mixed-case names handled

TODOS

No items completed in this PR. Two TODOs filed in the plan for follow-up:

• Hoist BYPASSRLS gate (v24/v29/v32/v35 share the pattern)
• gbrain doctor --fix-rls for the long tail (post-v35 if operator drift surfaces)

Documentation

• docs/guides/rls-and-you.md — new "v0.26.8 — auto-RLS event trigger and one-time backfill" section explaining the trigger, breaking-change semantics, and cross-app implications.
• CLAUDE.md — extended src/core/migrate.ts annotation with v35 specifics; added rls_event_trigger check to src/commands/doctor.ts annotation.
• CHANGELOG.md — v0.26.8 release-summary block with required "To take advantage of v0.26.8" recovery steps and explicit breaking-change line for intentionally-RLS-off public tables.

Breaking change

Operators with intentionally-RLS-off public tables MUST add the GBRAIN:RLS_EXEMPT reason=… (≥4 chars after reason=) comment before running gbrain upgrade to v0.26.8. The migration's one-time backfill flips RLS on for any public table whose comment doesn't carry the exact contract. Recovery cost is one round-trip; no data is lost.

Test plan

• bun run typecheck clean
• bun run test 3748/3748 pass
• Postgres E2E: 12 v35 cases pass + mechanical RLS regression test pass
• Replay idempotency, backfill exemption, mixed-case identifier safety verified
• Cross-model review: /plan-eng-review (D1-D7 resolved) + /codex consult (11 findings integrated)

🤖 Generated with Claude Code