Supabase Platform Posture

Written 2026-07-12 after a full platform audit + remediation pass (applied directly to the live project jrsgosnnyjonxaesqtln via MCP apply_migration; migration names below). This doc records what the system uses, what it deliberately does not, and the operator follow-ups. Since the 2026-07-26 ownership cutover, this repository owns the replayable migration history, all deployable edge-function source, the function allowlist, and Hermes service source. The React repository is a read-only rollback reference and has no production deployment authority.

Surfaces in use

Surface	State
Database	~300 tables in public/internal/private; curated api schema (~250 security-invoker views + 67 RPCs). Exposed schemas: api, public, graphql_public. Pre-request hook public.check_request (anon write rate-limit).
Auth	Phone-OTP only (auth-sms-hook). Anonymous sign-ins ENABLED on purpose — lib/core/platform/public_visitor_service.dart seeds guest sessions for public routes. Custom WebAuthn passkeys via webauthn-register/authenticate edge fns. MFA off (product decision).
Storage	31 buckets, all private except avatars and promo-assets; signed-URL pattern only.
Edge Functions	156 active. Flutter invokes 37 (manifest test: test/core/navigation/edge_function_inventory_test.dart).
Realtime	postgres_changes publication on 12 tables (Flutter uses this exclusively); broadcast/presence policies on realtime.messages serve the web app.
Cron	45 pg_cron jobs (incl. 2 new retention jobs below).
Vault	3 secrets (GOOGLE_AI_API_KEY, cron_secret, supabase_anon_key) — cron jobs read the anon key from Vault.
Queues (pgmq)	NEW 2026-07-12 — extension installed, pilot_jobs queue verified (send/read/archive). Standard for FUTURE queues; the 4 existing hand-rolled queue tables stay (proven, audited, realtime-subscribed).
Arabic FTS (pgroonga)	NEW 2026-07-12 — pilot index on unified_persons names + public.search_parties_fts(q) (SECURITY INVOKER, RLS applies). Compare vs pg_trgm/ILIKE before swapping app call sites; next candidate: paci_residents.full_name.

2026-07-12 remediation (migration names)

Security: enable_rls_defense_in_depth_internal_private (8 tables), revoke_internal_definer_fn_exec (4 cron-only SECURITY DEFINER fns), pin_function_search_path (18 routines), fix_election_districts_policies (always-true UPDATE removed; staff read), drop_noop_deny_policies_and_anon_guards (no-op PERMISSIVE deny policies dropped; paci_name_corrections INSERT + paci_street_coords read hardened vs anonymous sessions), admin_read_policies_for_kept_audit_tables.

Performance/hygiene: drop_duplicate_indexes (25), fix_realtime_messages_rls_initplan (7 hot policies), fix_public_rls_initplan, drop_redundant_and_noop_policies, split_for_all_policies_to_writes (network_*, whatsapp incident/replay, inspectors, party_roles, paci_block_coords, public_holidays), merge_duplicate_select_update_policies, consolidate_clients_policies (hot CRM table: 2 policies/action → 1), drop_backup_scratch_tables (14 tables), drop_unused_expensive_gin_trgm_indexes (6, verified idx_scan=0 over the DB's whole lifetime).

Capabilities: enable_pgmq_with_pilot_queue, enable_pgroonga_arabic_fts_pilot, add_log_retention_cron_jobs (internal.entity_audit_logs 24-month window, public.whatsapp_webhook_events 90-day window).

Deliberately NOT adopted (with reasons)

• GraphQL (pg_graphql) — no consumer in either app; graphql_public stays exposed but the extension stays uninstalled (v1.6 also disables introspection by default).
• Foreign Data Wrappers — wrappers extension installed but no external-DB use case; zero foreign servers configured.
• Analytics / vector buckets — public alpha; hermes_embeddings (pgvector) already serves embeddings.
• Branching — paid add-on; single-env workflow works. Revisit for risky migrations.
• Native Auth passkeys (beta) — custom WebAuthn flow works; migrate when the native feature is GA (would retire webauthn-register/webauthn-authenticate + custom tables).
• pg_partman — retention crons chosen instead for the large append-only logs; revisit if parcel_identity_history (1.1 GB) keeps growing.
• MFA — phone-OTP-only product decision.

2026-07-13 follow-up round (all four deferred items executed)

1. registry_voters RETIRED. It was NOT redundant — all 770k rows carried civil_id (the system's only name→civil-ID source; neither rebuild table nor paci_residents has it). Business data preserved in slim internal.voter_civil_registry (best-effort name, precomputed normalize_ar_name, civil_id, address; 187 MB vs 1.18 GB). Both matchers (public.trigger_voter_match, internal.match_voters_to_persons) repointed and smoke-tested (trigger enrichment verified end-to-end in a rollback txn); registry_voters_clean + the fat table dropped. ~1 GB reclaimed.
2. Auth pool switched to percentage (12% of max connections) via the dashboard — advisor auth_db_connections_absolute resolved.
3. Recurring log errors root-caused and fixed:
   • deals.area 400s = dead never-consumed query in the web repo's MarketAnomalyDetection page (was mis-dismissed as an audit false positive). Removed in aldilaijanre PR #1046.
   • records_processed = one-off exploratory SQL from another agent session (self-corrected; no system fix needed).
   • anon permission denied trio = missing anon GRANTs (property_attachments, api.dropdown_options view) + missing anon EXECUTE on role-helper fns (is_staff etc.), triggered by anonymous web-SPA visits to /catalog and /p/:id. Fixed via fix_anon_grant_gaps (silent RLS-filtered empties instead of 42501s) + a REAL bug fix in PR #1046: the public /p/:id gallery read property_attachments directly (missed by the 2026-04-26 anon-RPC wave) — new catalog-gated get_public_property_images RPC + anon branch in usePropertyMedia.
4. Migration history fully reconciled (it was worse than drift: remote history had been squashed ~2026-07-10 to a baseline with ZERO overlap vs the web repo's 1,370 files, so the CI migration workflow was warning + deploying nothing). All 1,365 local versions repaired --status applied (+2 Flutter-owned), 55 placeholder files added, 2 timestamp-collision files renamed to their true remote versions. Verified supabase db push --dry-run → "Remote database is up to date." See aldilaijanre PR #1046 + docs/MIGRATION_DRIFT_RECONCILIATION.md there.

Go-forward rule: every Supabase-MCP apply_migration must be followed by a matching placeholder (or real) migration file in the next aldilaijanre PR, or CI migration deploys silently stop again.

2026-07-13 second round: pgroonga decision, public dropdown labels, pgmq verdict

1. pgroonga pilot resolved with real evidence, not a guess. Tested against production data:
   • Hamza/diacritic variants (searching اوصاف for stored أوصاف): pgroonga on the raw column scored the SAME as plain ilike — 0 hits either way. That gap needs normalize_ar_name() matching (already used elsewhere, e.g. paci_residents.search_text), not pgroonga.
   • Word-order-independent search (typing "last name first"): plain ilike requires the literal substring in order → 0 hits; pgroonga's tokenized match found it. This is a real, common failure mode of the party registry's ilike .or() search that ilike literally cannot fix.
   • Decision: no blanket swap. Added search_parties_fts_fallback (superseding the unscoped search_parties_fts pilot), wired into Flutter's PartyRegistryRepository.searchParties() as a narrow fallback — triggers only on a plain, unfiltered, first-page, zero-result name search, so it can never override an explicit filter or change a result the primary search already found. See aldilaijanre PR #1053 + aldilaijankhobara-app branch feat/party-search-pgroonga-fallback.
2. Public dropdown labels fixed. The 2026-07-13 anon-grant lockdown correctly restricted dropdown_options to authenticated staff, but left no substitute for the three PUBLIC pages that render dropdown filters/forms (/catalog, /request, the rent-estimator) — anonymous visitors got silent empty option lists. Added get_public_dropdown_options RPC (active options only) + an anon branch in useAllDropdownOptions. aldilaijanre PR #1053.
3. pgmq rewiring of paci_sync_queue — investigated and INTENTIONALLY NOT DONE. Full consumer map: paci-sync-area edge fn claims via RPC but writes done/error/checkpoint state via THREE direct UPDATEs on the table; paci-resume-import.mjs mirrors it; the admin dashboard reads the api.paci_sync_queue view; the Flutter contract tests pin that view's exact 9-column shape. Verdict: this queue is a fixed-row per-area state machine (permanent checkpoint/generation/ error/staleness metadata the dashboard renders), not an append-consume message stream — pgmq would still need a companion state table, and the existing SKIP LOCKED claim already provides the queueing guarantee pgmq would add. Swapping it would need either a cross-repo edge-fn PR (adding mark-complete/mark-error/advance-checkpoint RPCs) or a compatibility view + INSTEAD OF trigger facade, for no real benefit. Not worth it — pgmq remains the standard for genuinely NEW queues only.

⚠ Concurrent-modification hazard (confirmed twice in one day)

Another actor (teammate or a separate agent session) is actively applying migrations to this SAME production project independently of any one session's work — confirmed twice on 2026-07-13: mid-afternoon (transaction_rollup_rpcs, revoke_stale_internal_grants appearing mid-session) and again after PR #1046 merged, when the just-reconciled supabase_migrations.schema_migrations history (1,367 rows) was found reset to 14 rows within the hour — two of the 14 remaining rows (fix_webhook_trigger_null_url, backfill_priced_as_land_available_vacant) were legitimate, well-documented fixes from that other actor, not corruption. The live SCHEMA was verified intact both times (RLS/grants/indexes/policies all still correct) — only the bookkeeping table churns. Net effect: CI's supabase-migrations.yml failed again right after PR #1046 merged (tried to re-push ~44 pre-baseline files against a database that already has that schema). Before running any further supabase migration repair/db push --include-all reconciliation, re-check supabase_migrations.schema_migrations row count first — redoing the 1,367-row repair blind risks colliding with whoever else is concurrently touching this project. This needs the team to identify who/what else has MCP or CLI access to jrsgosnnyjonxaesqtln and coordinate.

2026-07-24 history reconciliation + db pull post-processing (required)

The history drifted again after the 2026-07-13 reconciliation: supabase_migrations held 1,463 rows vs 18 local snapshot files. Repaired by marking the 1,445 remote-only versions reverted (all 18 local versions were already recorded applied — nothing was falsely registered), then pulled the live schema as 20260724190057_remote_schema.sql and registered it applied. Local and remote histories now match (19 = 19). The concurrent-actor caveat above still applies — re-check the row count before any future repair.

Every supabase db pull regenerates *_remote_schema.sql without the manual comment-outs these snapshots need to replay on the CLI's shadow database. Without them the pull dies at "failed to provision the shadow database" on three statements: the plain drop function of has_any_role / has_role (RLS policies on storage.objects depend on them) and alter column search_text set default on paci_residents (generated column). All three are schema-neutral to skip — the same file recreates the functions with CREATE OR REPLACE, and the generated expression is declared at CREATE TABLE.

Workflow after every supabase db pull:

python tool/fix_snapshot_replay.py          # comment the 3 statements, idempotent
python tool/fix_snapshot_replay.py --check  # CI mode: exit 1 if any snapshot is unpatched

Two operational notes for Windows machines: db pull needs Docker Desktop's resources/bin on PATH for the diff step, and if a pull is killed mid-run its shadow container leaks and squats on port 54320 — remove it (docker rm -f $(docker ps -aq --filter publish=54320)) before retrying.

Migration filenames are replay order, not just labels. If a pull's clock-generated snapshot version sorts before an already-applied migration (for example, because an additive migration was intentionally pre-numbered later that day), do not comment out the resulting forward references. Re-version the new snapshot after the latest local migration, repair only the old and new snapshot history entries, run the replay fixer, and prove the complete ordering with supabase db reset --local --no-seed plus the database test suite.

2026-09-25 view security_invoker regression class + three-legged guard

Views in exposed schemas (public, api) that lack security_invoker = true execute as their owner, so their queries bypass the RLS of every table they read — the 2026-09-02 incident class (135 anon-readable views effectively running as postgres). This regression is not a one-off: it is mechanically reintroduced by our own snapshot workflow.

Root cause: supabase db pull's *_remote_schema.sql dump carries no view reloptions — 20260919103802_remote_schema.sql contains 281 CREATE VIEW statements and zero security_invoker settings. Pushing a snapshot re-creates every view it lists and silently strips the reloption from all of them. The 23 ensure_security_invoker_views repair migrations (2026-09-14 → 2026-09-19, each written minutes after a snapshot dump) were the recurring cleanup, not the cure.

The guard (three legs):

1. Hosted audit — tool/audit_view_security.mjs + .github/workflows/view-security.yml (nightly + change-gated on PRs; registered in .github/ci-budget.json under scheduled and job_gated). Queries the live catalog through the Management API (SUPABASE_ACCESS_TOKEN only) and fails on any exposed view without the reloption that is not in tool/view_security_baseline.json. The baseline ships initialized: false: until someone runs node tool/audit_view_security.mjs --write-baseline with a token, the audit is report-only (loud ::warning:: per finding, exit 0) and the nightly nags with a ci-drift issue. Deliberate owner-execution views get a line in the baseline's notes map. Promote the check to required only after it has run clean for a cycle.
2. PR-time lint — tool/check_view_security_in_migrations.mjs fails any changed migration that creates a view without setting security_invoker in the same file (escape hatch: -- view-security: definer-ok <reason>). Runs in view-security.yml on PRs and as a diagnostic step in supabase-migrations.yml on pushes to main. Snapshots are exempt — the dump format carries no reloptions, so flagging them would fail forever.
3. Paired repair per pull — python tool/fix_snapshot_replay.py now also emits idempotent ALTER VIEW ... SET (security_invoker = true) statements for every view in the newest snapshot into tool/generated/restore_security_invoker_<ts>.sql (gitignored). Review it, re-timestamp it under a fresh 14-digit timestamp (check open PRs for collisions), and commit it together with the snapshot. Nothing is written into supabase/migrations/ automatically.

Updated db pull workflow: pull → python tool/fix_snapshot_replay.py → commit the snapshot and the re-timestamped generated repair → PR. Never restore invoker settings out of band; the audit's baseline and the migration ledger both need the repair to exist as a migration.

Remaining known items

• ~270 remaining unused_index INFO lints — btree indexes left alone deliberately.
• Reminder from canonical supabase/config.toml: remote email-signup must stay disabled in the dashboard (the committed local config already has it off).