Failure mode: FM18 (no indexes on foreign keys, RLS subqueries evaluated per row) · Measured: 2026-11-22T16:05:41.112Z
Impact: with 72,390 invoices on the platform, every query a single agency makes scans the whole table. Each Seq Scan reads every agency's rows and throws away more than 99% of them, and the dashboard repeats one of these scans for every invoice it shows.
Setup
pnpm tsx before/exploits/run-all.tsboots localbase (Postgres in WASM via PGlite) with the before schema andbulkSeed(): the two demo agencies plus 60 generated ones.- Row counts: organizations 62, clients 1,809, projects 2,736, invoices 72,390, invoice_items 181,213, messages 9,572, payments 53,640.
- Each query is the exact SQL that localbase's PostgREST layer compiles from the URL the app sends, run inside a transaction as role
authenticatedwith Maya's JWT claims (as PostgREST does), so RLS policies are in the plan. EXPLAIN (ANALYZE, BUFFERS)6 times per query; the first run warms the buffer cache and is discarded; the median of the remaining 5 execution times is reported.- Absolute timings come from a WASM Postgres on a laptop and will differ from hosted Supabase. The plan shapes (Seq Scan, "Rows Removed by Filter") are the point, and they are the same on any Postgres without these indexes.
Summary
| Query | Rows returned | Median execution (5 runs) | Runs (ms) | Key plan node |
|---|---|---|---|---|
| Invoice list by org, ordered by due date | 35 | 5.628 ms | 4.83, 4.69, 5.70, 6.17, 5.63 | Seq Scan on invoices t0 |
| Line items for one invoice (runs once per invoice: the N+1) | 2 | 12.699 ms | 12.57, 12.87, 12.70, 12.72, 12.63 | Seq Scan on invoice_items t0 |
| Messages for one project (RLS policy with membership subquery) | 5 | 0.954 ms | 1.07, 0.95, 0.91, 0.93, 0.97 | Seq Scan on messages t0 |
| Payments for one invoice (invoice detail page) | 1 | 3.402 ms | 3.48, 3.40, 3.51, 3.40, 3.37 | Seq Scan on payments t0 |
| Revenue rpc get_org_revenue (dashboard) | 7 | 6.482 ms | 6.49, 6.34, 6.48, 6.23, 6.54 | Function Scan on get_org_revenue r (body: Seq Scan on invoices i) |
Invoice list by org, ordered by due date
App request: GET /rest/v1/invoices?select=*&org_id=eq.<uuid>&order=due_on.desc
Aggregate (cost=2282.02..2282.03 rows=1 width=32) (actual time=5.597..5.600 rows=1.00 loops=1)
Buffers: shared hit=1376
-> Subquery Scan on r (cost=2281.58..2281.94 rows=29 width=170) (actual time=5.399..5.459 rows=35.00 loops=1)
Buffers: shared hit=1376
-> Sort (cost=2281.58..2281.65 rows=29 width=146) (actual time=5.391..5.407 rows=35.00 loops=1)
Sort Key: t0.due_on DESC
Sort Method: quicksort Memory: 21kB
Buffers: shared hit=1376
-> Seq Scan on invoices t0 (cost=0.00..2280.88 rows=29 width=146) (actual time=0.010..5.363 rows=35.00 loops=1)
Filter: (org_id = 'd10e897e-3fad-4fe7-8ce6-ea6c6b804e9c'::uuid)
Rows Removed by Filter: 72355
Buffers: shared hit=1376
Planning Time: 0.089 ms
Execution Time: 5.628 msLine items for one invoice (runs once per invoice: the N+1)
App request: GET /rest/v1/invoice_items?select=*&invoice_id=eq.<uuid>
Aggregate (cost=4946.17..4946.18 rows=1 width=32) (actual time=12.665..12.668 rows=1.00 loops=1)
Buffers: shared hit=2681
-> Seq Scan on invoice_items t0 (cost=0.00..4946.16 rows=3 width=82) (actual time=0.009..12.638 rows=2.00 loops=1)
Filter: (invoice_id = 'c25b4d20-448c-4f77-b548-798f9e888d23'::uuid)
Rows Removed by Filter: 181211
Buffers: shared hit=2681
Planning Time: 0.118 ms
Execution Time: 12.699 msMessages for one project (RLS policy with membership subquery)
App request: GET /rest/v1/messages?select=*&project_id=eq.<uuid>&order=created_at.asc
Aggregate (cost=32153.52..32153.53 rows=1 width=32) (actual time=0.924..0.928 rows=1.00 loops=1)
Buffers: shared hit=184
-> Subquery Scan on r (cost=32153.49..32153.51 rows=2 width=146) (actual time=0.899..0.908 rows=5.00 loops=1)
Buffers: shared hit=184
-> Sort (cost=32153.49..32153.49 rows=2 width=122) (actual time=0.895..0.899 rows=5.00 loops=1)
Sort Key: t0.created_at
Sort Method: quicksort Memory: 17kB
Buffers: shared hit=184
-> Seq Scan on messages t0 (cost=0.00..32153.48 rows=2 width=122) (actual time=0.181..0.889 rows=5.00 loops=1)
Filter: ((project_id = '677bc368-9659-4982-9234-2b9351468ffd'::uuid) AND (((((COALESCE(NULLIF(current_setting('request.jwt.claims'::text, true), ''::text), '{}'::text))::jsonb -> 'user_metadata'::text) ->> 'role'::text) = 'admin'::text) OR (ANY (org_id = (hashed SubPlan 2).col1))))
Rows Removed by Filter: 9567
Buffers: shared hit=184
SubPlan 2
-> Seq Scan on memberships m (cost=0.00..3.15 rows=1 width=16) (actual time=0.007..0.163 rows=1.00 loops=1)
Filter: (user_id = (NULLIF(((COALESCE(NULLIF(current_setting('request.jwt.claims'::text, true), ''::text), '{}'::text))::jsonb ->> 'sub'::text), ''::text))::uuid)
Rows Removed by Filter: 65
Buffers: shared hit=1
Planning Time: 0.213 ms
Execution Time: 0.954 msPayments for one invoice (invoice detail page)
App request: GET /rest/v1/payments?select=*&invoice_id=eq.<uuid>
Aggregate (cost=1333.51..1333.52 rows=1 width=32) (actual time=3.384..3.386 rows=1.00 loops=1)
Buffers: shared hit=663
-> Seq Scan on payments t0 (cost=0.00..1333.50 rows=1 width=65) (actual time=0.006..3.374 rows=1.00 loops=1)
Filter: (invoice_id = 'c25b4d20-448c-4f77-b548-798f9e888d23'::uuid)
Rows Removed by Filter: 53639
Buffers: shared hit=663
Planning Time: 0.043 ms
Execution Time: 3.402 msRevenue rpc get_org_revenue (dashboard)
App request: GET /rest/v1/rpc/get_org_revenue
Aggregate (cost=12.75..12.76 rows=1 width=32) (actual time=6.464..6.465 rows=1.00 loops=1)
Buffers: shared hit=1376
-> Function Scan on get_org_revenue r (cost=0.25..10.25 rows=1000 width=24) (actual time=6.435..6.441 rows=7.00 loops=1)
Buffers: shared hit=1376
Planning Time: 0.023 ms
Execution Time: 6.482 msFunction body, explained directly:
GroupAggregate (cost=2462.42..2462.93 rows=20 width=48) (actual time=6.136..6.158 rows=7.00 loops=1)
Group Key: (to_char((issued_on)::timestamp with time zone, 'YYYY-MM'::text))
Buffers: shared hit=1376
-> Sort (cost=2462.42..2462.47 rows=21 width=40) (actual time=6.122..6.131 rows=26.00 loops=1)
Sort Key: (to_char((issued_on)::timestamp with time zone, 'YYYY-MM'::text))
Sort Method: quicksort Memory: 17kB
Buffers: shared hit=1376
-> Seq Scan on invoices i (cost=0.00..2461.95 rows=21 width=40) (actual time=0.012..6.094 rows=26.00 loops=1)
Filter: ((org_id = 'd10e897e-3fad-4fe7-8ce6-ea6c6b804e9c'::uuid) AND (status = 'paid'::text))
Rows Removed by Filter: 72364
Buffers: shared hit=1376
Planning Time: 0.071 ms
Execution Time: 6.180 msWhat fixes it
schema.sql has no indexes beyond primary keys (advisor lint 0001_unindexed_foreign_keys). create index on invoices (org_id, due_on desc), create index on invoice_items (invoice_id), create index on messages (project_id, created_at), create index on payments (invoice_id) and create index on memberships (user_id, org_id) turn each of these into an index scan. The after schema does this (after/supabase/migrations).