Remove prod-only drift: four functions and the PushSentLog table #320

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

Problem

Prod holds five objects that no file at HEAD creates: four public functions and the table public."PushSentLog". Nobody has read their prod bodies.

The Supabase security linter found them on 2026-09-28. Before the emergency fix at about 13:38Z, anon could execute all four functions. Anon could also read, write and delete PushSentLog.

That fix closed browser access, but the objects are still there. The four functions are unreviewed SECURITY DEFINER code. A fresh db reset reproduces none of the five objects. No repo file records them, so no reviewed code controls their grants or bodies.

This issue asks the maintainer to read the prod bodies and rule "drop" or "keep" per object. An agent then ships the ruling as a scripts edit plus one paired migration.

Context

The five objects

The security review says git log -S finds none of the four. That holds only for reset_workspace_unread_counts. Git history has legacy bodies for the other three, shown above. The prod bodies may still differ from them.

Commit a8b6ea9d9 (2026-05-13) removed ALTER FUNCTION lines for three of these functions. Its message says get_push_subscriptions and get_push_notification_stats are "never defined". It says cleanup_push_subscriptions lives in internal. Both claims hold for a fresh local reset, but not for prod.

Why PushSentLog on Supabase is an orphan

• The real PushSentLog lives in the Prisma database, not in Supabase. See AGENTS.md ~377: the app's DATABASE_URL is the DigitalOcean Postgres.
• The Prisma model has a different shape: id, idempotencyKey, userId, subscriptionCount, sentAt, expiresAt. See apps/hocuspocus.server/prisma/schema.prisma ~123 (model PushSentLog) and apps/hocuspocus.server/prisma/migrations/20260201083215_schema_improvements/migration.sql ~35-44.
• No committed Prisma file ever held notificationId or subscriptionId. git log --all -S notificationId -- apps/hocuspocus.server/prisma returns nothing.
• The push worker reads and writes the Prisma table by idempotencyKey: apps/hocuspocus.server/src/lib/push/queue.ts ~122 (prisma.pushSentLog.findUnique) and ~145 (prisma.pushSentLog.create). apps/hocuspocus.server/src/lib/retention.ts ~45 deletes expired rows there.
• So dropping the Supabase table cannot break the push worker.
• Measured: RLS was off on it before the fix. Inferred, not measured: it was made by hand before the prod-only ensure_rls event trigger existed. That trigger's origin is unverified (Make new document_views partitions start closed #315). The review dates its install between 2026-02-01 and 2026-03-01.

The cron job that must keep working

• The live job is cleanup-push-subscriptions (hyphens), every 5 minutes. It calls internal.cleanup_push_subscriptions(). See packages/supabase/scripts/16-cron-jobs.sql ~20-24 and packages/supabase/migrations/20260806100000_cron_schedule_upsert_preserves_jobid.sql ~22.
• The internal body is at packages/supabase/scripts/07-4-push-notifications-pgmq.sql ~643 (create or replace function internal.cleanup_push_subscriptions()).
• The old job cleanup_push_subscriptions (underscores) called the public copy. Migration 20260717015500_cron_health_single_pass_and_schedule_fixes.sql ~12-13 unscheduled it.
• Any drop must touch schema public only. Dropping internal.cleanup_push_subscriptions() would stop the cron job.

What was measured on prod on 2026-09-28 (read-only)

• All four functions are owned by postgres. None returns trigger or event_trigger.
• PushSentLog is owned by postgres. RLS was off before the fix.
• pg_stat_statements shows anon ran full-column selects on PushSentLog 7 times. They match no repo code and look like a scanner. The table had 0 rows, so they returned nothing. One more anon count was the review's own grant check.
• Prod bodies were NOT read. Every body claim above comes from git history.

What the emergency fix did (already applied to prod)

For functions, it looped over every overload of the four names in schema public. For each, it ran grant execute ... to service_role, then revoke all ... from public, anon, authenticated. It asserted the result.

For the table, it ran this:

alter table if exists public."PushSentLog" enable row level security;
do $$ begin
  if to_regclass('public."PushSentLog"') is not null then
    revoke all on public."PushSentLog" from public, anon, authenticated;
  end if;
end $$;

#314 ports those grants into scripts/*.sql and a paired migration. This issue removes or records the objects themselves.

Recommendation

Drop all five, unless Step 1 finds a caller. The maintainer's local security report advises the same: read the bodies, then drop. It leaves the final call to the maintainer. No repo path, cron job or migration uses any of them.

What to do

1. [maintainer] Read the prod facts. Run these read-only queries in the Supabase SQL editor. Record the results in a comment on this issue.

   Before you paste a function body into this public issue, check that it holds no key, token, URL with a key, or personal data. If in doubt, summarize the body instead.

   -- 1a. Signature, return type, language, owner, ACL and body of each function.
   select p.oid::regprocedure as sig, pg_get_function_result(p.oid) as returns,
          l.lanname, p.prosecdef, pg_get_userbyid(p.proowner) as owner, p.proacl,
          pg_get_functiondef(p.oid) as body
   from pg_proc p
   join pg_namespace n on n.oid = p.pronamespace
   join pg_language l on l.oid = p.prolang
   where n.nspname = 'public'
     and p.proname in ('reset_workspace_unread_counts','get_push_notification_stats',
                       'cleanup_push_subscriptions','get_push_subscriptions')
   order by 1;
   
   -- 1b. Objects that depend on those functions (triggers, views, defaults).
   select p.oid::regprocedure as fn, d.deptype,
          pg_describe_object(d.classid, d.objid, d.objsubid) as dependent
   from pg_proc p
   join pg_namespace n on n.oid = p.pronamespace
   join pg_depend d on d.refclassid = 'pg_proc'::regclass and d.refobjid = p.oid
   where n.nspname = 'public'
     and p.proname in ('reset_workspace_unread_counts','get_push_notification_stats',
                       'cleanup_push_subscriptions','get_push_subscriptions');
   
   -- 1c. Text references in cron jobs, other function bodies and policies.
   select 'cron' as kind, jobname::text as name, command as text
   from cron.job
   where command ~* '(reset_workspace_unread_counts|get_push_notification_stats|cleanup_push_subscriptions|get_push_subscriptions|pushsentlog)'
   union all
   select 'function', p.oid::regprocedure::text, left(p.prosrc, 200)
   from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname not in ('pg_catalog','information_schema')
     and p.proname not in ('reset_workspace_unread_counts','get_push_notification_stats',
                           'cleanup_push_subscriptions','get_push_subscriptions')
     and p.prosrc ~* '(reset_workspace_unread_counts|get_push_notification_stats|cleanup_push_subscriptions|get_push_subscriptions|pushsentlog)'
   union all
   select 'policy', schemaname || '.' || tablename || '.' || policyname,
          coalesce(qual, '') || ' ' || coalesce(with_check, '')
   from pg_policies
   where coalesce(qual, '') || ' ' || coalesce(with_check, '')
         ~* '(reset_workspace_unread_counts|get_push_notification_stats|cleanup_push_subscriptions|get_push_subscriptions|pushsentlog)';
   
   -- 1d. Who has ever called them (normalized query text, no values).
   select r.rolname, s.calls,
          left(regexp_replace(s.query, '\s+', ' ', 'g'), 160) as query
   from pg_stat_statements s
   join pg_roles r on r.oid = s.userid
   where s.query ~* '(reset_workspace_unread_counts|get_push_notification_stats|cleanup_push_subscriptions|get_push_subscriptions|pushsentlog)'
   order by s.calls desc;
   
   -- 1e. The table: owner, RLS, ACL, live rows, write counters, dependents, foreign keys.
   select c.oid::regclass as tbl, pg_get_userbyid(c.relowner) as owner,
          c.relrowsecurity, c.relacl, c.reltuples,
          (select count(*) from public."PushSentLog") as live_rows
   from pg_class c where c.oid = to_regclass('public."PushSentLog"');
   select n_tup_ins, n_tup_upd, n_tup_del, seq_scan, idx_scan
   from pg_stat_user_tables where relid = to_regclass('public."PushSentLog"');
   select pg_describe_object(d.classid, d.objid, d.objsubid) as dependent, d.deptype
   from pg_depend d
   where d.refclassid = 'pg_class'::regclass
     and d.refobjid = to_regclass('public."PushSentLog"')
     and d.deptype = 'n';
   select conname, conrelid::regclass from pg_constraint
   where confrelid = to_regclass('public."PushSentLog"');

   How to read the results:

   • 1c: expect exactly one cron row, cleanup-push-subscriptions, whose command names internal.cleanup_push_subscriptions(). That is not a caller of the public copy. Any other row is a caller.
   • 1d: rows that name internal.cleanup_push_subscriptions come from that cron job. The review's own checks on 2026-09-28 also appear, and so do the Step 1 queries themselves. pg_stat_statements has no timestamps. So get_push_notification_stats calls may be the old admin dashboard, which stopped on 2026-02-04. Any other role or shape is a caller to explain.
   • 1b and 1e: any row with deptype = 'n', or any foreign key, blocks a plain drop.

2. [maintainer] Rule per object. Post a comment with one line per object: drop or keep, and the reason. Use this rule:

   Step 1 shows	Ruling
   No dependent, no text reference, no unexplained caller, and (for the table) 0 rows	drop
   A dependent object or a real caller	keep. The body moves into scripts/ (step 4).
   A body that writes other users' rows and has a caller	keep, and open a follow-up to review its logic

3. [agent] Write the drop in the script first. In packages/supabase/scripts/07-4-push-notifications-pgmq.sql, find section 9 (~672, header -- 9. Drop Deprecated Functions (from pg_net architecture)). It already drops v1 functions at ~676-678. After those lines, add one block for every object ruled drop. List only the ruled names.

   -- Prod-only leftovers that no repo file creates (#320). Schema public only:
   -- internal.cleanup_push_subscriptions() runs the live cron job and must stay.
   -- No CASCADE: a dependent object must stop this, not vanish with it.
   do $$
   declare
     f record;
   begin
     for f in
       select p.oid::regprocedure as sig
       from pg_proc p join pg_namespace n on n.oid = p.pronamespace
       where n.nspname = 'public'
         and p.proname = any (array['cleanup_push_subscriptions',
           'get_push_notification_stats','get_push_subscriptions',
           'reset_workspace_unread_counts'])
     loop
       execute format('drop function %s', f.sig);
     end loop;
   end $$;
   
   drop table if exists public."PushSentLog";

   The loop drops every overload, so the unknown signature of reset_workspace_unread_counts does not matter. It is a no-op on a fresh local reset, because local never had these objects.

4. [agent] Only for an object ruled keep. Copy its prod body from the Step 1 comment into a script. Push functions go in 07-4-push-notifications-pgmq.sql. reset_workspace_unread_counts goes at the end of packages/supabase/scripts/09-message_counter.sql (~283). Use create or replace, never drop and create. A new function gets the open default grants on prod, so anon could run it again. Follow the body with explicit grants:

   revoke all on function public.<name>(<args>) from public, anon, authenticated;
   grant execute on function public.<name>(<args>) to service_role;

   A kept table stays as it is on prod. Do not add it to scripts/. Its RLS and revoke come from Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314.

5. [agent] Write one paired migration. Create packages/supabase/migrations/<YYYYMMDDHHMMSS>_drop_prod_only_drift.sql. Its timestamp must sort after every file in that folder, including Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration. Put the same statements as steps 3 and 4 in it. Start it with a comment that names the paired script files and says "Idempotent." End it with this assert, trimmed to the dropped names:

   do $$
   begin
     if exists (select 1 from pg_proc p join pg_namespace n on n.oid = p.pronamespace
                where n.nspname = 'public'
                  and p.proname = any (array['cleanup_push_subscriptions',
                    'get_push_notification_stats','get_push_subscriptions',
                    'reset_workspace_unread_counts'])) then
       raise exception '#320: a dropped function still exists';
     end if;
     if to_regclass('public."PushSentLog"') is not null then
       raise exception '#320: public."PushSentLog" still exists';
     end if;
     if to_regprocedure('internal.cleanup_push_subscriptions()') is null then
       raise exception '#320: internal.cleanup_push_subscriptions() is missing';
     end if;
   end $$;

6. [agent] Regenerate the seed and types. From the repo root:

   bun run --filter @docs.plus/supabase_back seed
   bun run --filter @docs.plus/supabase_back types

   The seed diff should hold only the drop block, plus any kept bodies and their grants. Expect no change to apps/webapp/src/types/supabase.ts, because local never had these objects. If it does change, include the regenerated file.

7. [agent] Prove the migration locally. Local has none of these objects, so a plain run proves nothing. Build a scratch file outside the repo that creates stand-ins, runs the migration, checks, and rolls back:

   begin;
   create function public.reset_workspace_unread_counts(p uuid) returns void
     language sql as $$ select $$;
   create function public.get_push_subscriptions() returns int
     language sql as $$ select 1 $$;
   create table public."PushSentLog" (id text primary key);
   -- paste the migration body here
   select count(*) as left_over from pg_proc p join pg_namespace n on n.oid = p.pronamespace
   where n.nspname = 'public'
     and p.proname in ('reset_workspace_unread_counts','get_push_subscriptions');
   select to_regclass('public."PushSentLog"') as table_left,
          to_regprocedure('internal.cleanup_push_subscriptions()') as internal_kept;
   rollback;

   Run it with docker exec -i supabase_db_docsplus_supabase psql -v ON_ERROR_STOP=1 -U postgres -d postgres < <scratch file>. Then apply the real migration twice with the same command. Both runs must succeed.

   Do not run bun run --filter @docs.plus/supabase_back reset without asking the maintainer. It wipes shared local state.

8. [maintainer] Commit and push. Review the diff and commit through /commit-review. Push the migration to prod with the house procedure that Ship the three migrations that prod's history lacks #313 describes:

   1. Set enabled = true under [db.migrations] in packages/supabase/config.toml (~53-58) only while pushing. Never commit it as true.
   2. Run bunx supabase migration list. Expect only the new file with an empty Remote column. If any other row differs, stop and follow packages/supabase/CLAUDE.md ~30.
   3. Run bunx supabase db push --dry-run, then bunx supabase db push.
   4. Set enabled back to false. Never repair-apply a new version.

9. [maintainer] Check prod after the push. Run the queries under Verification. Wait for the next 5-minute run of cleanup-push-subscriptions.

Out of scope

• Porting the 2026-09-28 grants and RLS into scripts: Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314. Its migration must land first.
• Shipping the migrations that prod's history lacks: Ship the three migrations that prod's history lacks #313.
• Partition RLS, and the prod-only ensure_rls event trigger and rls_auto_enable function: Make new document_views partitions start closed #315.
• Default privileges for new tables and functions: Stop new tables and functions from opening by default #316.
• The read-only grant guard: Add a read-only grant guard to run after each db push #317. After the 2026-09-28 fix, none of its checks should match these five objects.
• The other lint 0028 warnings on functions: Close the remaining 47 linter warnings on functions #318.
• Other objects from the deleted v1 push script, such as internal.app_config, internal.get_config and internal.set_config. HEAD defines none of them, and prod was not checked. Open a follow-up if wanted.
• A guard that stops Prisma scripts from running against a Supabase host. The security review suggested it for PushSentLog. It is not in this issue; open a follow-up if wanted.
• Do not run the search_path sweep (29-lint-hardening.sql §1) on these functions. It can break drift code that calls extensions without a schema prefix.

Acceptance criteria

• A comment on this issue records the Step 1 results and one drop or keep ruling per object, five in all.
• packages/supabase/scripts/07-4-push-notifications-pgmq.sql section 9 holds a drop block that names only the objects ruled drop.
• Every function ruled keep has its body in a script, created with create or replace. A revoke from public, anon, authenticated and a grant to service_role follow it.
• Exactly one new file exists under packages/supabase/migrations/. Its timestamp sorts after Port the 2026-09-28 prod grant and RLS fix into scripts and a paired migration #314's migration, and it holds the same statements plus the assert block.
• No drop uses CASCADE. No statement changes an object in schema internal.
• git status --short packages/supabase/migrations lists only the new file, as untracked (??). No applied migration changed.
• packages/supabase/seed.sql was regenerated with the seed script, not edited by hand.
• The types script ran. apps/webapp/src/types/supabase.ts is unchanged, or its regenerated diff is included.
• The Step 7 scratch run shows left_over = 0, table_left null and internal_kept not null.
• The real migration applied twice to local Supabase with no error.
• After the prod push, the Verification queries return the expected results.

Verification

Local, by the agent:

git status --short packages/supabase/migrations       # only '?? <new file>'
git diff --stat apps/webapp/src/types/supabase.ts      # expect empty
docker exec -i supabase_db_docsplus_supabase psql -v ON_ERROR_STOP=1 -U postgres -d postgres \
  < packages/supabase/migrations/<new file>.sql        # run twice; both exit 0

Prod, read-only, by the maintainer after the push:

-- Expect 0 rows for every name ruled "drop".
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 ('reset_workspace_unread_counts','get_push_notification_stats',
                    'cleanup_push_subscriptions','get_push_subscriptions');

-- Expect null if the table was ruled "drop".
select to_regclass('public."PushSentLog"');

-- Expect not null: the cron job's function survives.
select to_regprocedure('internal.cleanup_push_subscriptions()');

-- Expect the newest runs to say 'succeeded'.
select d.status, d.start_time
from cron.job_run_details d join cron.job j on j.jobid = d.jobid
where j.jobname = 'cleanup-push-subscriptions'
order by d.start_time desc limit 3;

-- Expect the new version in the history.
select version from supabase_migrations.schema_migrations order by version desc limit 3;

Then re-run the security linter (Dashboard, Advisors, Security Advisor). No row may name any dropped object.

House rules

• Scripts first, then one paired migration. Regenerate seed.sql with the seed script, and run bun run --filter @docs.plus/supabase_back types. See packages/supabase/CLAUDE.md.
• Never edit an applied migration. Never hand-edit packages/supabase/seed.sql or apps/webapp/src/types/supabase.ts.
• No commits, pushes or branches by an agent. The maintainer commits through /commit-review.
• Bun only: no npm, yarn, pnpm or npx.
• Tests are opt-in. The Step 7 scratch run is a proof, not a committed test.