fix: index subscription filters by digest so large in lists fit - #2097

Closes #1670.

The problem

realtime.subscription's unique index covers filters by value:

CREATE UNIQUE INDEX ... ON realtime.subscription
  (subscription_id, entity, filters, action_filter, coalesce(selected_columns, '{}'));

so a subscription's whole filter array has to fit btree's 2704-byte tuple limit. An id=in.(...) list of uuids passes that quickly, and the subscription fails to register with 54000 index row size ... exceeds btree version 4 maximum 2704.

Measured on supabase/postgres:17.6.1.127 by inserting in filters of growing length:

largest in-list that inserts: 69 uuids
first failure at 70 uuids: 54000 index row size 2712 exceeds btree version 4 maximum 2704

69. Meanwhile realtime.subscription_check_filters raises too many values for 'in' filter. Maximum 100, and the docs describe the same 100. So the limit the code enforces has never been reachable on a uuid column, and because the real constraint is total byte length rather than value count, the same list works on an int column and fails on a uuid one — which is what makes it look arbitrary from the client side.

The change

Index a digest of the array instead of the array:

create function realtime.filters_hash(filters realtime.user_defined_filter[]) returns bytea
  language sql immutable strict parallel safe
as $$ select pg_catalog.sha256(pg_catalog.convert_to(filters::text, 'UTF8')) $$;

and point the upsert's conflict target at the same expression. The index row is now fixed-width, uniqueness is unchanged, and the 100-value guard becomes the thing that actually stops you.

sha256 rather than the md5 the Postgres hint suggests, since the value is user-controlled and the digest is what decides whether two subscriptions are the same row; a collision would silently fold one subscription into another. grant execute follows the pattern of 20260706120000_grant_check_equality_op_5_arg, since the blanket grant in 20231204144023 only covered functions that existed then.

Why the tenant dumps are in this PR

Realtime.Tenants.Migrations.load_db_dump/1 provisions a brand-new tenant from priv/repo/tenant_db_dump_<major>.sql and returns Enum.count(migrations()) — it records every migration as applied without replaying any of them. A new tenant would therefore keep the old value index while being marked fully migrated, and the new on conflict target would have nothing to match, so every postgres_changes subscription on that tenant would fail, not just the large ones.

update-tenant-db-snapshots.yml is gated to same-repo PRs (if: github.event.pull_request.head.repo.full_name == github.repository), so it will not run here. I updated the three snapshot files by hand — the function, its ACL block, the index, the schema_migrations row, and the tenant_schema entries — and verified the result rather than trusting the edit: a fresh database loaded from the edited tenant_db_dump_17.sql reports 82 migrations (matching migrations.ex), has subscription_subscription_id_entity_filters_hash_key and realtime.filters_hash, and accepts a 100-uuid filter through the exact upsert statement from subscriptions.ex. Please regenerate them properly before merge if you would rather not carry a hand-edit.

Verification

Tests added to subscriptions_test.exs: a 100-value in filter is accepted, and resubscribing the same large filter updates the existing row instead of duplicating it (that one guards the conflict-target change).

I could not run mix test locally — Windows box, no Elixir toolchain, and the suite provisions tenant databases through its own Docker backend. I verified the SQL directly instead, against a real tenant schema on supabase/postgres:17.6.1.127:

• Before: 69 uuids maximum, 70 fails with 54000.
• After: 100 uuids insert; 101 fails with P0001 too many values for 'in' filter. Maximum 100 — the intended guard, reached for the first time.
• Upsert identity preserved: resubscribing the same 100-value filter leaves one row with updated claims; a genuinely different 100-value filter on the same channel is still its own row; the new index is the one enforcing both.
• End to end: with two subscriptions carrying different 100-value lists, an insert whose id is in the first list is delivered only to that subscription.