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

URGENT: 202608081500_guard_analytics_rpc_callers.sql is merged to main and NOT applied to production

DoneOwner · Vivt_b6fccf27

No dependencies in this wave — nothing gates it, and nothing waits on it.

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

**URGENT: 202608081500_guard_analytics_rpc_callers.sql is merged to main and NOT applied to production** · done

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

## The brief on file

[2026-10-06 · Viv] DONE — APPLIED TO PRODUCTION 2026-10-06 with Matthew's approval; verified before and after with read-only SELECTs. analytics_assert_caller now exists, the six reporting RPCs call it, and all eight functions are service_role-only. Smoke test after the apply: the service_role path returns data (roster 12 rows / 2,387 scans) and a non-owner is refused with 42501. The migration file 202608081500_guard_analytics_rpc_callers.sql was already on main (#289), so no repo change was needed to apply it. Repo follow-up still owed (this task's own step 4; it does not block the close): tests/fixtures/rls/policy-snapshot.json on origin/main and in draft PR #724 (generated 2026-10-06T18:03:48Z) both still show has_caller_check=false for the six analytics_* functions, so the fixture needs a refresh (has_caller_check flips true) and any matching KNOWN_* / db-grants-allow.json entries need deleting, in one commit.

[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 the dump + restore-test preconditions below no longer gate this task; the other preconditions, e.g. the Worker being on its final key, are not changed by this decision); each prod migration still needs Matthew's named yes. Status and owner unchanged.

[2026-10-05 · Viv (verify pass)]
DECISION: Apply 202608081500_guard_analytics_rpc_callers.sql to prod (TacTile DB xtzqmvfhtremtmbzykeq) now, or defer it to the end of the post-dump DB sitting (after the legacy-key migration), and keep it on the launch-day DB list?
RECOMMEND: Defer (B), then apply it LAST, after (1) a dump + restore-test exist, (2) the API Worker is on its final Supabase key, (3) the dashboard has been smoke-tested on that key, and before ~1 Dec. Nothing is exposed today, so "URGENT" in the title is stale (titles cannot be edited via task_update; treat as P3). It is the only one of the four batch migrations that changes function bodies, and its one pass-branch (`auth.role() = 'service_role'`) is exactly the thing the key migration changes.
COST IF YES (apply now): ~5 min via Supabase MCP apply_migration + ~10 min repo follow-up (pnpm verify:rls-snapshot --write; delete matching KNOWN_*/db-grants-allow.json entries in the SAME commit). Risk: prod DDL on a no-backup DB (~/TacTile-backups/db is still EMPTY as of 5 Oct; t_a2ccb527 already says "do not apply queued DDL until a dump exists"), and if Worker calls ever arrive as anything but service_role the analytics dashboard errors for everyone (outage, not data loss; one-statement undo below). The key migration (74 Bearer call sites, rotation-night PR #657) changes how the Worker authenticates, so applying the guard first makes any post-migration dashboard failure ambiguous.
COST IF NO (defer): no exposure today (re-verified live today). You only lack defence in depth: a future `drop function` + `create function` on any of the six resets the ACL to default PUBLIC EXECUTE and reopens an unauthenticated cross-tenant analytics read, with nothing in the body to stop it. Risk is it falling off the launch-day DB list, so keep it there.
FLAGS: irreversible (prod DDL on a database with pitr_enabled=false and zero backups; the migration itself is reversible, see rollback)
DRAFT (NOT executed; no PR needed, the file is on origin/main unchanged since PR #289, commit 75a59c65): supabase/migrations/202608081500_guard_analytics_rpc_callers.sql (511 lines).
  0. Preconditions: scripts/db/backup.sh + restore-test.sh have run; Worker on its final key.
  1. Drift check, read-only, re-run immediately before applying:
     select p.proname, md5(p.prosrc) from pg_proc p where p.pronamespace='public'::regnamespace and p.proname in ('analytics_destination_source_attribution','analytics_group_distribution','analytics_group_member_health','analytics_group_ranking','analytics_group_roster','analytics_trigger_cadence') order by 1;
     Expect exactly: destination_source_attribution 830bd7a04f1b50434b4ca681cd99aedf · group_distribution b7a09935c787889bd2cb7fccabf27d8e · group_member_health 392075179c681de1373d550440aa6ba1 · group_ranking 9a4dbe745b93f82a441c73ce1b1ebed8 · group_roster b34f606ce844fda0b8d8d4f70f533177 · trigger_cadence 9c98c4ca242ab35ede6f507392b0501c. Any difference = someone hand-edited a body; stop, because the migration would overwrite it.
  2. Apply: Supabase MCP apply_migration with the file body (not the CLI).
  3. Verify: the ACL query in the migration header (anon/auth false, svc true on all eight; analytics_assert_caller now exists); then load one real analytics dashboard request as a signed-in user. DATA CAVEAT: the newest analytics_events row in prod is 2026-07-05 (18,246 rows total), so use a date range that includes data. An empty chart on a recent range looks identical to an outage.
  4. Follow-up in ONE commit: pnpm verify:rls-snapshot --write (has_caller_check flips true for the six), delete the matching KNOWN_*/db-grants-allow.json entries.
  FAST UNDO (guard off, bodies and ACLs untouched, one statement; tested):
     create or replace function public.analytics_assert_caller(p_owner uuid) returns boolean language plpgsql stable security invoker set search_path to 'public' as $$ begin return true; end $$;
  FULL UNDO: restore the six pre-guard bodies (the migration's bodies minus the guard line). A generated file is at /private/tmp/claude-503/-Users-matthewkvamme-Command-tactile/c619278e-5ecc-46a1-ac4b-5d8e23403818/scratchpad/work/b6fccf27/rollback_unguard.sql (scratch, may be cleaned; regenerate by deleting the single `select|perform public.analytics_assert_caller(p_owner);` line, plus the blank line after the plpgsql one, from each of the six bodies). After running it the six md5 values must equal the list in step 1.

VERIFIED TODAY 2026-10-05 (all read-only; nothing applied or written anywhere remote):
- Live catalog (Supabase MCP execute_sql, project xtzqmvfhtremtmbzykeq): analytics_assert_caller does not exist; none of the six bodies calls it; the other eight functions all read acl {postgres=X/postgres,service_role=X/postgres}, anon_exec=false, authed_exec=false, svc_exec=true. Identical to the 29 Sep result. The 9/30 stop-note was accurate; nothing in it was stale except the title.
- No PR open or closed references this migration or t_b6fccf27; the only commit touching the file on origin/main is #289.
- The rest of the runbook batch is applied by effect: log_analytics_event anon_exec=false, p_destinations_public_select qual is (visibility = 'public' AND deleted_at IS NULL), cleanup_expired_oauth_codes revoked. This is the last of the four, and the only one that changes function bodies.
- Bodies match: md5(prosrc) of all six LIVE functions equals the md5 of the migration's six bodies with the guard line removed (byte-identical, including the 2026-07-15 family_triggers rewire of analytics_destination_source_attribution). Applying cannot clobber a hand edit as of today.
- Local scratch test (ephemeral postgres:16-alpine docker container, since removed; no remote DB): ran the whole migration file with zero errors (check_function_bodies=off, because the real bodies need prod tables) and all nine ACLs ended anon/auth=false, svc=true. Then with the REAL analytics_assert_caller, in both the LANGUAGE sql statement-list form and the plpgsql PERFORM form: service_role -> rows; authenticated asking about itself -> rows; authenticated asking about another account -> 42501; anon -> 42501; no JWT -> 42501; control (same function WITHOUT the guard, as anon) returned the other account's rows. Full undo restored all six md5s to the live values above; the fast undo reopens the guard. Limit: Postgres 16 with stubbed auth.role()/auth.uid(), not Supabase's auth schema on the live engine.
- Worker path unchanged on origin/main: apps/api/tactile-api/src/utils/analytics/db.ts rpcCall sends apikey AND Authorization: Bearer <service role key>; all six RPCs are called only through it (destinations.ts, groups.ts x4, triggers.ts). Runbook §3 cannot be proven from prod telemetry today: Supabase edge logs hold 172 rows/24h with no rpc paths, pg track_functions=none, and the newest analytics_event is 2026-07-05. Hence the smoke test in step 3 is the real check.

[previous prompt, unchanged]
Found 2026-08-08 by the backend session while closing t_6a588183. Verified against the live catalog, not inferred.

THE STATE, right now:
  · public.analytics_assert_caller(uuid) DOES NOT EXIST in production (pg_proc count = 0).
  · None of the six analytics RPC bodies contains the guard (prosrc ilike '%analytics_assert_caller%' = false for all six).
  · supabase/migrations/202608081500_guard_analytics_rpc_callers.sql IS on main, merged via PR #289.

So the repo says the guard shipped and the database says it did not. That is exactly the drift the Command DB's own "Migration NOT applied" notes exist to warn about, and it is one `create or replace` away from being real.

WHAT *IS* LIVE, so nobody panics: the six RPCs plus recompute_analytics_rollups are REVOKED from public/anon/authenticated — applied 2026-08-08 by 202608081200_revoke_public_analytics_rpcs.sql (PR #291). Verified 7 of 7 anon-unexecutable. The cross-tenant leak is CLOSED. The guard is defence-in-depth against a future `drop function` + `create function` resetting the ACL, which is a real vector because create-or-replace preserves an ACL and drop+create does not.

WHY I DID NOT APPLY IT MYSELF: it re-creates six function bodies on a database with pitr_enabled:false and zero stored backups, it is another session's design, and that session may be mid-flight. Applying another lane's unapplied production DDL unilaterally is how two sessions collide on a database with no undo.

TO APPLY: use the Supabase MCP apply_migration (the CLI silently skips most of this directory). The migration's own header carries its rollback. Afterwards:
  1. Re-run the six as service_role and confirm rows still come back — the guard's first branch is `auth.role() = 'service_role'`, and every dashboard call arrives that way via rpcCall's baseHeaders.
  2. Regenerate tests/fixtures/rls/policy-snapshot.json — has_caller_check flips to true for the six, which will make KNOWN_UNCHECKED_DEFINER's staleness test fire again if any entry survives.
  3. Run scripts/verify/rls-snapshot-fresh.mjs (added by #289) — it deep-diffs the fixture against the live catalog and is the standing defence against this exact drift.

NOTE ON THE GUARD'S DESIGN, which is good and worth not second-guessing: five of the six RPCs are LANGUAGE sql, so the plpgsql `if … then raise … end if;` form the task originally specified is syntactically impossible for them. #289 solves it by exploiting that a LANGUAGE sql body may be a STATEMENT LIST — `select public.analytics_assert_caller(p_owner);` runs as its own statement and cannot be optimised away. I had independently tested the join-based alternatives and found that `CROSS JOIN LATERAL` is silently elided by the planner when the inner query returns no rows (verified: returned [] instead of raising), while `WITH … AS MATERIALIZED` does fire. #289's statement-list approach is better than either and needs no fence.

[overnight 29→30 Sep] NEEDS MATTHEW — apply 202608081500_guard_analytics_rpc_callers.sql to production, or leave it deferred? It is NOT applied — but the leak it was written against is closed, so "URGENT" no longer holds.

WHAT I DID: one read-only catalog SELECT (Supabase MCP execute_sql, project xtzqmvfhtremtmbzykeq, 2026-09-29 ~22:00 PT). Nothing applied or written. The query left-joins the nine function names against pg_proc (schema public) and returns exists, (prosrc ilike '%analytics_assert_caller%'), proacl and has_function_privilege for anon/authenticated/service_role:
select v.fn, p.oid::regprocedure::text as signature, (p.oid is not null) as exists_in_prod, (p.prosrc ilike '%analytics_assert_caller%') as body_calls_guard, p.proacl::text as acl, has_function_privilege('anon', p.oid, 'execute') as anon_exec, has_function_privilege('authenticated', p.oid, 'execute') as authed_exec, has_function_privilege('service_role', p.oid, 'execute') as svc_exec from (values ('analytics_assert_caller'),('analytics_destination_source_attribution'),('analytics_group_distribution'),('analytics_group_member_health'),('analytics_group_ranking'),('analytics_group_roster'),('analytics_trigger_cadence'),('recompute_analytics_rollups'),('cleanup_expired_oauth_codes')) as v(fn) left join pg_proc p on p.proname = v.fn and p.pronamespace = 'public'::regnamespace order by v.fn
RESULT: analytics_assert_caller exists_in_prod=false (no pg_proc row). The six reporting RPCs exist and body_calls_guard=false on every one. All eight other functions read acl {postgres=X/postgres,service_role=X/postgres}, anon_exec=false, authed_exec=false, svc_exec=true. So the guard function and the six rewritten bodies are NOT live; the REVOKE half of the migration IS live (by effect — 202608081200 and 202608091200). The RLS fixture regenerated 2026-09-23 agrees (has_caller_check=false, anon/public_execute=false for all six).

WHY IT NEEDS YOU: it re-creates six function bodies on a database with pitr_enabled:false and zero stored backups; only you apply DDL here. Runbook docs/runbooks/apply-unapplied-migrations.md §2: "Recommendation: apply 2, 3, 4 first — they close everything actually exposed — and treat 1 as a separate, later decision. It is the only one that changes function *bodies*, and nothing is open that it alone closes." (1 = this migration.) §3: verify service_role BEFORE applying — every dashboard analytics call must arrive as service_role or the whole analytics dashboard returns empty for everyone (an outage, not data loss).
Consequence of NOT applying: no exposure today. What you lose is defence in depth — a future `drop function` + `create function` on any of the six resets the ACL to default PUBLIC EXECUTE and reopens an unauthenticated cross-tenant analytics read, and nothing in the body would stop it.

IF YOU SAY A (apply): Supabase MCP apply_migration (not the CLI). Do runbook §3 first, then apply; rollback is in the migration header (lines 91-96). Afterwards re-run the six as service_role and confirm rows come back, then §5 in ONE commit: pnpm verify:rls-snapshot --write and delete the matching KNOWN_*/db-grants-allow.json entries.
IF YOU SAY B (defer): nothing to do. Suggest re-titling this task (not urgent) and folding it into the launch-day DB sequence.
MY READ: B now, A before launch — the runbook itself says the same, and it is the only migration of the batch that changes function bodies.
SAFE TO ASSUME: the six analytics RPCs (and the pg_cron pair) are not callable by anon or authenticated today, verified 2026-09-29.

## 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

URGENT: 202608081500_guard_analytics_rpc_callers.sql is merged to main and NOT applied to production