Ship the three migrations that prod's history lacks #313

Key: SPR-02 · Type: HITL · Part of: #327 · Blocked by: none · Checked against: 3bb26de

Problem

Prod's migration history ends at 20260921120000, but the repo has three newer migrations. Their objects are already live on prod (read-only check, 2026-09-28 ~15:30Z). Someone applied them by hand, so only the history rows are missing. #314 adds its own migration after these three, so the history must be in sync first. Otherwise db push refuses or re-orders.

All files are in packages/supabase/migrations/. All three commits are on origin/main. The maintainer pushed main at 204f8ef62 on 2026-09-28, about 14:42Z. The git side is done; only the SQL push is left.

Context

How prod got here. On 2026-09-28 the maintainer ran read-only check "0g" in the SQL editor. It showed that prod history ends at 20260921120000. The three versions above are missing. The emergency grant and RLS fix that day ran as direct SQL, not through db push. A push then would also have run these three files, while two were still uncommitted. Prod therefore also differs from the repo in grants. #314 ports those grants into the repo.

Why pushing these three does not undo the emergency fix. None of the three files drops or re-creates a function that the emergency fix changed. record_email_bounce was not in that fix. create or replace keeps a function's existing grants. The two new functions are security invoker, so the linter's SECURITY DEFINER checks (lints 0028 and 0029) do not count them.

20260922120000. It is one create or replace function public.record_email_bounce(text, text, text, text) (~9-83). Only the action_url literal changes (~75). The paired script is packages/supabase/scripts/07-5-email-notifications-pgmq.sql (record_email_bounce at ~208; the new link at ~274). Its grants come from 20260519200000_scripts_functions_triggers_parity.sql (~2135-2136): service_role only. It is idempotent. It may already be live on prod if someone ran it by hand. Step 3 checks this.

20260928120000. Paired with scripts/31-connected-app-token-gate.sql. The two bodies are identical after the header comment (checked with diff). It does four things:

1. Creates public.refuse_connected_app_request() (~10-27), security invoker. It raises a PostgREST 403 with code connected_app when auth.jwt() ->> 'client_id' is not null.
2. Grants EXECUTE to anon, authenticated, service_role (~32-33). Then it runs alter role authenticator set pgrst.db_pre_request = 'public.refuse_connected_app_request' and notify pgrst, 'reload config' (~35-36).
3. Adds a restrictive policy "Connected apps use only the MCP server" on storage.objects and realtime.messages (~39-57).
4. Loops over every public table in publication supabase_realtime and adds the same restrictive SELECT policy to each (~62-83). A table added to that publication later needs this block run again (see scripts/17-realtime-replica.sql ~12).

The file header says: "check authenticator has no other pgrst.db_pre_request" (~3). The alter role would silently replace an existing hook.

Risk in 20260928120000. PostgREST calls the hook on every request as the caller's role. If anon or authenticated cannot execute it, every Data API request fails. This is why it is security invoker: scripts/29-lint-hardening.sql §5 revokes EXECUTE from all security definer functions in public. Step 6 checks the grants right after the push. Step 8 is the rollback.

20260928130000. Paired with scripts/32-password-sign-in-hook.sql. The two bodies are identical after the header comment. It adds a Custom Access Token hook function, public.hook_block_password_tokens(jsonb) (~9-22). It grants EXECUTE to supabase_auth_admin only and revokes it from authenticated, anon, public (~27-29). Switching the hook on is tracked separately. packages/supabase/config.toml (~262-267) keeps it commented out locally.

Why prod lacks the connected-app gate today. Supabase applies RLS to OAuth tokens as if they were the user's session. The commit message of e4113fd9d says a connected app's token therefore reached tables, RPCs, Storage and Realtime. The backend already refuses such tokens in code: isConnectedAppToken in apps/hocuspocus.server/src/lib/jwtClaims.ts (~23) guards REST, admin and WebSocket auth. PostgREST, Storage and Realtime do not refuse them until this migration runs.

House push procedure (packages/supabase/CLAUDE.md ~29-30):

• [db.migrations] enabled in packages/supabase/config.toml (~53-58) stays false. With false, db push skips migrations. Set it to true only while pushing. Never commit it as true.
• Run bunx supabase migration list first. It is the ground truth (Local and Remote columns; an empty Remote means not applied).
• A "remote versions not found" or "does not match local files" refusal is normal drift, not corruption.
• migration repair --status applied <v> only edits the history table. It does not run the SQL. Never use it on a new version: db push would then skip that file forever.
• All repo migrations are idempotent, so a push over objects that already exist is safe.

Deploy note. 204f8ef62 is an empty (build): front back commit. Its push made .github/workflows/prod.docs.plus.yml deploy the webapp and backend. That workflow never runs db push, so the SQL still waits for step 5. No app code calls the two new SQL functions. A grep of apps/ and packages/ finds only the generated types, the seed and docs. So the deployed app does not depend on this SQL.

Measured vs predicted. The missing-history fact is measured (check 0g, 2026-09-28). Whether record_email_bounce already has the new body on prod is not known. The prod list of tables in supabase_realtime is not known to the repo; step 3 reads it. On the local stack (2026-09-28) it is five public tables: channel_members, channel_message_counts, channels, messages and users. Locally the policy check in Verification returns those five plus realtime.messages and storage.objects.

What to do

1. [agent] Confirm the files and pairing at the current tree. Run git log --oneline -1 -- <file> for the three migrations and for scripts/31-connected-app-token-gate.sql and scripts/32-password-sign-in-hook.sql. Expect f6ed0a22c, e4113fd9d and 3ef541d8a. Confirm each migration body matches its script after the header comment.
2. [agent] Prove idempotency on the local stack, without db reset (it wipes shared local state). From the repo root, apply each migration twice, in version order. Expect no error:

   for f in 20260922120000_system_alert_settings_hash_link 20260928120000_refuse_connected_app_tokens 20260928130000_reject_password_sign_in_hook; do
     docker exec -i supabase_db_docsplus_supabase psql -v ON_ERROR_STOP=1 -U postgres -d postgres < packages/supabase/migrations/$f.sql
     docker exec -i supabase_db_docsplus_supabase psql -v ON_ERROR_STOP=1 -U postgres -d postgres < packages/supabase/migrations/$f.sql
   done

   Report the output in the issue. If the local stack is not running, say so and skip.
3. [maintainer] Run these read-only prechecks on prod in the SQL editor. Record the results in the issue.

   -- (a) History: expect 20260921120000 as the newest row.
   select version from supabase_migrations.schema_migrations
   order by version desc limit 5;
   
   -- (b) Is 20260922120000 already live? true = applied by hand.
   select position('/#settings?tab=notifications' in p.prosrc) > 0 as new_link
   from pg_proc p
   where p.oid = 'public.record_email_bounce(text,text,text,text)'::regprocedure;
   
   -- (c) The pre-request hook. On 2026-09-28 prod already showed
   --     pgrst.db_pre_request=public.refuse_connected_app_request (applied by hand).
   select rolconfig from pg_roles where rolname = 'authenticator';
   
   -- (d) Tables that step 4 of 20260928120000 will gate (public schema only).
   select schemaname, tablename from pg_publication_tables
   where pubname = 'supabase_realtime' and schemaname = 'public'
   order by 1, 2;
   
   -- (e) The two new functions. On 2026-09-28 prod already had both (2 rows).
   select p.oid::regprocedure from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname = 'public'
     and p.proname in ('refuse_connected_app_request', 'hook_block_password_tokens');

   Stop rules: if (c) shows a pgrst.db_pre_request value other than public.refuse_connected_app_request, stop and ask. Do not overwrite it. Expected on 2026-09-28: (b) true, (c) that exact value, (e) 2 rows, and 9 "Connected apps use only the MCP server" policies. All three files are idempotent, so the push re-runs them harmlessly and records history. Prefer the push over repair --status applied, so the history row matches SQL that really ran.
4. [agent] Confirm the three commits are on origin/main. Run git fetch origin, then git merge-base --is-ancestor 3ef541d8a origin/main && echo on-origin. Expect on-origin. This was true on 2026-09-28. If not, stop: the git push is the maintainer's call.
5. [maintainer] Push the SQL. Work from packages/supabase (the linked project ref is in packages/supabase/.temp/project-ref; link first if it is missing).
   1. Set enabled = true under [db.migrations] in packages/supabase/config.toml (~58).
   2. Run bunx supabase migration list. Expect exactly three rows with an empty Remote column: the three versions above. If any other row differs, stop. Follow packages/supabase/CLAUDE.md ~30 before any repair.
   3. Run bunx supabase db push --dry-run. Expect the same three files, in version order.
   4. Run bunx supabase db push.
   5. Set enabled = false again at once. Run git diff packages/supabase/config.toml and expect no change.
6. [maintainer] Run the Verification SQL below within five minutes of the push. If anon or authenticated cannot execute refuse_connected_app_request, run step 8 at once.
7. [maintainer] Smoke-test the product. As a guest, open a public pad and read its chat. Signed in, send a chat message and upload an avatar. Open the admin email bounces page. Each must work as before. Re-run the Supabase security linter: expect 0 ERROR and no new lint 0028 rows (47 before the push).
8. [maintainer] Rollback, only if the Data API fails after the push. It removes the hook and keeps the rest:

   alter role authenticator reset pgrst.db_pre_request;
   notify pgrst, 'reload config';

   Then open a follow-up issue with the error text. Do not delete the history row.
9. [agent] Post the precheck results, the migration list output before and after, and the Verification results as a comment. Then tick the Acceptance criteria.

Out of scope

• Switching the Custom Access Token hook on: tracked separately.
• Porting the 2026-09-28 emergency grant and RLS fix into scripts and a migration: Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314. Its migration must have a version after 20260928130000.
• Allowlisting refuse_connected_app_request for anon in the grant guard: Add a read-only grant guard to run after each db push #317.

Acceptance criteria

• Prechecks (a) to (e) ran on prod before the push, and their results are in a comment.
• bunx supabase migration list shows 20260922120000, 20260928120000 and 20260928130000 with a Remote value.
• None of the three was marked applied with migration repair; all three ran through db push.
• packages/supabase/config.toml has enabled = false under [db.migrations], and git diff shows it unchanged.
• record_email_bounce on prod contains /#settings?tab=notifications, and anon cannot execute it.
• authenticator has pgrst.db_pre_request=public.refuse_connected_app_request, and anon and authenticated can execute that function.
• The restrictive policy exists on storage.objects, on realtime.messages, and on every public table from precheck (d).
• Only supabase_auth_admin (and the owner) can execute hook_block_password_tokens.
• The step 7 smoke test passed, and the linter shows 0 ERROR and no new lint 0028 rows.

Verification

Run on prod after the push. Expected results are in the comments.

-- 1. History. Expect the four newest rows: 20260928130000, 20260928120000,
--    20260922120000, 20260921120000.
select version from supabase_migrations.schema_migrations
order by version desc limit 4;

-- 2. record_email_bounce: new_link = true, anon_exec = false.
select position('/#settings?tab=notifications' in p.prosrc) > 0 as new_link,
       has_function_privilege('anon', p.oid, 'EXECUTE') as anon_exec
from pg_proc p
where p.oid = 'public.record_email_bounce(text,text,text,text)'::regprocedure;

-- 3. Pre-request hook. Expect rolconfig to contain
--    pgrst.db_pre_request=public.refuse_connected_app_request, and both flags true.
select rolconfig from pg_roles where rolname = 'authenticator';
select has_function_privilege('anon', 'public.refuse_connected_app_request()', 'EXECUTE') as anon_exec,
       has_function_privilege('authenticated', 'public.refuse_connected_app_request()', 'EXECUTE') as auth_exec;

-- 4. Restrictive policies. Expect storage.objects, realtime.messages, and one
--    row per table from precheck (d); every row RESTRICTIVE.
select schemaname, tablename, permissive
from pg_policies
where policyname = 'Connected apps use only the MCP server'
order by 1, 2;

-- 5. Custom Access Token hook grants. Expect true, false, false.
select has_function_privilege('supabase_auth_admin', 'public.hook_block_password_tokens(jsonb)', 'EXECUTE') as auth_admin,
       has_function_privilege('anon', 'public.hook_block_password_tokens(jsonb)', 'EXECUTE') as anon_exec,
       has_function_privilege('authenticated', 'public.hook_block_password_tokens(jsonb)', 'EXECUTE') as auth_exec;

Behaviour check (maintainer, own test account): a Data API request with your own connected-app token gets HTTP 403 with code connected_app. A normal signed-in session still works.

House rules

• Migrations and scripts/*.sql are paired. These three already have their scripts, seed and types in the same commits. Do not edit an applied migration; a fix is a new migration.
• Remote push: [db.migrations] enabled = true only while pushing, never committed. Run bunx supabase migration list first. Never repair --status applied a new version (packages/supabase/CLAUDE.md ~29-30).
• Never hand-edit packages/supabase/seed.sql or apps/webapp/src/types/supabase.ts.
• No git commit or git push by an agent. Those are the maintainer's steps.
• Bun only: bunx supabase ..., never npx.