Command

PePersonal JEJust Electrolyte RTRelease Timer TtTacTile CSClearSource AtAthletic

TacTile›Launch TacTile›Foundations›Database & Supabase·15 in this wave

Verify point-in-time recovery is enabledTo do · blocker Review anon-executable SECURITY DEFINER functionsDone · blocker Approve: Database & SupabaseTo do · blocker MATTHEW: profiles.visibility is nullable with no default, so any profile created without it reads as publicBlocked · blocker The RLS fixture expires 2026-09-30 and freezes main — only Matthew can refresh it (Touch ID)Done · blocker Fix v_account_tier security-definer viewDone Fix allow-all RLS policy on checklists tableDone URGENT: 202608081500_guard_analytics_rpc_callers.sql is merged to main and NOT applied to productionDone db_url and db_password now EXIST in 1Password — verify:rls-snapshot can run for the first timeTo do RLS snapshot fixture turns pnpm verify red for every branch on 5 Nov (30-day age limit)To do Hardware-ready data modelDone Document Supabase backup policyBlocked Document RLS test planDone Soft-deleted public destinations stay readableDone Confirm scan-event logging is rate-limited per destinationDone

Wave

Database & Supabase

Schema is already built (81 tables, 114 migrations, RLS on every table). This wave is the readiness pass plus three real gaps a live audit surfaced.

TacTile › Launch TacTile › Foundations › Database & Supabase

9/15 done6 open3 open blockers

Selected taskclear

Fix allow-all RLS policy on checklists table

DoneOwner · Vivt_204d475a

Policy is USING/WITH CHECK always-true, so RLS is on but enforcing nothing. Found by live audit.

Unlocks 2Document RLS test plan Approve: Database & Supabase

Show the prompt

You are Viv, the agent for the TacTile lane of Command.

Open the lane and work there:

```bash
cd ~/Command/tactile && claude
```

Read that lane's CLAUDE.md and lane.json before you touch anything — they
carry your identity, your scope, and the hazards specific to this lane.
Read state from the hub (https://internal.tactile.to) or ask. Never guess at it.

## The task

**Fix allow-all RLS policy on checklists table** · done

Policy is USING/WITH CHECK always-true, so RLS is on but enforcing nothing. Found by live audit.

Where it sits: TacTile › Launch TacTile › Foundations › Database & Supabase

## Unlocks 2

- Document RLS test plan
- Approve: Database & Supabase

## The brief on file

[2026-10-06 · Viv] DONE — verified live (applied 2026-10-06 with Matthew's yes, see the APPLIED note below): the allow-all policy on public.checklists is dropped and anon/authenticated INSERT/UPDATE/DELETE are revoked (0 policies; anon SELECT kept and returns 0 rows with no error; the 4 rows intact). Repo record pending in draft PR #724 (the migration with its applied marker, the regenerated RLS fixture, the KNOWN_OPEN_TABLES entry removed, docs corrected, the sync.sh service-role fallback); the live fix does not depend on it, and the earlier "NOT done until #724 merges" line below is superseded by this close.

[2026-10-06 · Viv] APPLIED TO PRODUCTION 2026-10-06 with Matthew's yes; repo record in PR #724.
Applied via the Supabase MCP apply_migration ("lock_down_checklists_rls"), the SQL of 202609300100_lock_down_checklists_rls.sql minus begin/commit. Verified after: 0 policies on public.checklists; anon and authenticated INSERT/UPDATE/DELETE revoked; anon SELECT kept (returns 0 rows, no error); 4 rows intact. A same-day read of the live catalogue (scripts/verify/rls-snapshot.sql, generated_at 2026-10-06T18:03:48Z) agrees: checklists has RLS on and no policy.
Repo record: draft PR #724 (branch sec/revoke-anon-resolve-trigger-destination, head ffcc791f) carries the migration with the applied marker (taken from #655 / 275630c2), the regenerated RLS fixture, the checklists entry deleted from KNOWN_OPEN_TABLES (table added to DENY_ALL_NO_POLICY), db-grants-allow.json updated, the docs corrected (RLS.md, ADVISORS.md, OVERVIEW.md, db-audit-followups.md §9, runbooks) and the sync.sh SUPABASE_SERVICE_ROLE_KEY fallback. NOT done until #724 merges; the orchestrator closes this task then. Heads-up: #655's own copy of this migration is now an add/add conflict with #724 — drop 275630c2 from #655 when rebasing (its sync.sh part already matches).
Consequence now live: tactile-checklist.pages.dev, internal.tactile.to/checklist and anon-key sync.sh show no data / cannot write; sync.sh needs SUPABASE_SERVICE_ROLE_KEY (apps/checklist/AGENTS.md says so).

[2026-10-06 · Matthew decided] Backups: no paid database backup pre-launch; production data is treated as replaceable (GitHub is the code backup). A verified dump is NO LONGER a precondition for applying a migration (so any "backup / dump first" step below no longer gates this task); each prod migration still needs Matthew's named yes. Status and owner unchanged.

[2026-10-05 · Viv (verify pass)]
DECISION: Apply supabase/migrations/202609300100_lock_down_checklists_rls.sql to prod (TacTile DB xtzqmvfhtremtmbzykeq) to drop the allow-all policy and revoke anon/authenticated writes on public.checklists? (Option B = same SQL, and also retire the checklist tool.)
RECOMMEND: Yes, apply A now, B later. The hole is still open as of today's live check; the SQL deletes no rows; its rollback restores the exact measured state in three lines; the tools it switches off hold five-month-stale data.
COST IF YES: ~2 min in the Supabase SQL editor, then ~10 min repo follow-up (pnpm verify:rls-snapshot --write; delete the 'checklists' entry from KNOWN_OPEN_TABLES in tests/unit/rls-cross-tenant.test.ts; correct supabase/docs/db/RLS.md, ADVISORS.md, db-audit-followups.md §9). Switches OFF tactile-checklist.pages.dev, internal.tactile.to/checklist and anon-key sync.sh (set SUPABASE_SERVICE_ROLE_KEY and sync.sh keeps working). The DB has no backups; no row is touched, but if you want a belt-and-braces copy first, `select * from public.checklists` is 4 rows (launch-readiness is ~185 KB).
COST IF NO: Anyone holding the public anon key (it ships in every web and mobile bundle), or any signed-in customer, can read, overwrite or delete all 4 rows, and can plant text that agents pull via sync.sh and act on (aiNotes / context). Nothing is leaking credentials today (pattern scan found none), but the launch plan is readable and the agent-instruction path is writable.
FLAGS: irreversible (prod DDL on a database with pitr_enabled=false and zero backups; the migration itself is reversible by the rollback block, but any anon writes made in the meantime are not recoverable)
DRAFT: NOT executed. File: supabase/migrations/202609300100_lock_down_checklists_rls.sql on draft PR #655 (branch r27/ops-sweep, commit 275630c2). You do not need to merge #655 to apply it, and you should not wait for it: #655 bundles unrelated work (abuse-report link, secrets doc, pricing note), is 86 commits behind main, and its static-firewall check is red from the report-link part ("No static logic inside the dynamic destination routes"); its only red verify step is the sanctioned Secrets rotation audit. Exact SQL (the header comment in the file has the rest):

  begin;
  drop policy if exists "Allow all access to checklists" on public.checklists;
  revoke insert, update, delete, truncate, references, trigger
    on table public.checklists from public, anon, authenticated;
  comment on table public.checklists is 'AI-human handoff checklists (apps/checklist). service_role only: RLS is on with no policy; anon/authenticated keep SELECT solely so the anon-key health probes get a 200 rather than a 401, and read zero rows. Was allow-all until 202609300100 (t_204d475a).';
  commit;

  Rollback (only if the checklist tool must work again tonight):
  begin;
  create policy "Allow all access to checklists" on public.checklists as permissive for all to public using (true) with check (true);
  grant insert, update, delete on public.checklists to anon, authenticated;
  commit;

  Post-apply check: select count(*) from pg_policies where tablename='checklists' -> 0; select has_table_privilege('anon','public.checklists','INSERT') -> false. Anon SELECT is deliberately kept so the two health probes (apps/api/tactile-api/src/utils/health.ts:35, apps/web/app/api/health/deep/route.ts:21, both HEAD checklists?id=eq.__health__ with the anon key) stay 200 instead of 401.

VERIFIED TODAY (read-only SELECTs against prod via Supabase MCP; nothing written):
- pg_policies on public.checklists: one policy "Allow all access to checklists", roles {public}, cmd ALL, USING true, WITH CHECK true. RLS enabled, not forced.
- Grants: anon and authenticated both hold DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE.
- A session with `set local role anon`: sees 4 checklists rows; has_table_privilege INSERT / UPDATE / DELETE all true. 4 rows total, newest updated_at 2026-05-08 23:38 UTC.
- origin/main has no checklists lockdown migration; the only copy is on #655. main's tests/unit/rls-cross-tenant.test.ts still carries the KNOWN_OPEN_TABLES entry for checklists (so the post-apply step above is still pending).
- Blocker status: REAL, needs Matthew (prod DDL on a no-backup DB; it also switches off two tools). The earlier stop-note below is accurate; nothing was stale.
- Same class, same decision-maker, still open: an anon-role session reads 152 rows of public.destinations (the stop-note said 151). The fix file supabase/migrations/202608272335_drop_anon_public_destinations_select.sql is on main but is not applied. Treat as a separate yes/no.

[previous prompt, unchanged]
Viv — fix the allow-all RLS policy on the checklists table (USING/WITH CHECK always-true, so RLS is on but enforcing nothing); surface anything needing Matthew's sign-off.

[overnight 29→30 Sep] NEEDS MATTHEW — say "apply" (or "retire the tool") and I finish it; the fix is drafted, not applied.

WHAT I DID (read-only against prod):
- public.checklists lives in the app DB (TacTile DB xtzqmvfhtremtmbzykeq), not pretender-cloud.
- Live: one policy "Allow all access to checklists", roles={public}, ALL, USING true / WITH CHECK true. RLS on. anon AND authenticated hold SELECT/INSERT/UPDATE/DELETE. 4 rows (launch-readiness, ai-agent, analytics-ui, white-label-urls), newest write 2026-05-08.
- From outside, with only the published anon key: GET /rest/v1/checklists?select=id returns 200 and all four ids. So it is still open.
- Worse than "dev-tool table": agents pull these rows into data/checklists/*.json and act on aiNotes/context, so an anonymous write can put instructions in front of an agent session. No credential values are in the rows (pattern scan for JWTs, sk_/rk_/whsec_, PEM, sbp_).
- Draft on draft PR #655, commit 275630c2: supabase/migrations/202609300100_lock_down_checklists_rls.sql — NOT applied. Drops the policy, revokes INSERT/UPDATE/DELETE from PUBLIC/anon/authenticated, KEEPS anon SELECT (RLS with no policy returns zero rows, so the two anon-key DB health probes stay 200 rather than 401). Header has the measurements, WHAT BREAKS, post-apply steps, rollback. apps/checklist/scripts/sync.sh now uses SUPABASE_SERVICE_ROLE_KEY when set, and can no longer overwrite the local JSON with an error body (found by testing: a bad key wrote null over it).
- Checks on the branch: verify:migration-grants, verify:shell-parse, verify:no-harness-artifacts green (full unit run pending, see PR).

WHY BLOCKED: applying switches off tactile-checklist.pages.dev and internal.tactile.to/checklist (browser + anon key) and anon-key sync.sh. The DB has no backups and prod DDL is yours to apply.
IF YOU SAY A (apply) → run the file in the Supabase SQL editor (or tell me to apply via MCP at your desk). Then `pnpm verify:rls-snapshot --write` and delete the 'checklists' entry from KNOWN_OPEN_TABLES in tests/unit/rls-cross-tenant.test.ts — the header lists both steps and the three docs to correct.
IF YOU SAY B (retire the tool) → same migration, plus `wrangler pages project delete tactile-checklist` (apps/internal-hub/scripts/setup-cf-access.sh already calls the site redundant); I remove the /checklist proxy from the hub.
MY READ: A now, B later. The data is five months stale, only those two tools touch it, and the rollback is three lines in the header.
ALSO SEEN, same class and same decision-maker: `destinations` is still anon-readable — HEAD with Prefer: count=exact and the anon key returns 151 rows. The F-1 fix (202608272335_drop_anon_public_destinations_select.sql) is still a draft.
SAFE TO ASSUME: nothing applied; the live table and its data are untouched.

## How to work this

- Do the work, don't just plan it. If the fix is obvious and safe, make it.
- Surface anything needing Matthew's sign-off instead of deciding it yourself.
- Short replies. Lead with the answer, then stop.
- Finish by saying what changed, what you verified, and what you'd do next.

To doIn progressDoneBlockedPick a wave on the left · tap a task to expand it

Lanes

Database & Supabase

Fix allow-all RLS policy on checklists table