What we measured ahead of the review

Measured on 6 October 2026 against the live database, as real logins where it matters (set local role authenticated plus a JWT claim, which is how PostgREST makes the call). Every query ran read-only. Statement statistics have been collecting since the project was created (stats_reset is empty), so they cover the whole build and every demo, not one clean day.

1. Server and settings

Setting	Value
shared_buffers / effective_cache_size	256 MB / 768 MB (the smallest Supabase compute size)
work_mem	3.5 MB
max_connections	60; PostgREST pools through authenticator
statement_timeout	anon 3 s, authenticated 20 s, authenticator 8 s, server 120 s
lock_timeout	authenticator 8 s, everyone else none
idle_in_transaction_session_timeout	none
log_min_duration_statement	off; log_lock_waits on; track_io_timing off
Cache hit ratio	heap 99.95 %, index 99.96 %
Deadlocks / temp files since creation	1 / 2,723 files, 13 GB

2. Where the time goes

Total execution time by function, signed-in callers only:

Function	Calls	Mean	Max	What it is
watch_assets	1,254	1.39 s	7.9 s	Vehicle watchlist
dash_sites	1,350	1.18 s	7.5 s	Site table on every manager dashboard
dash_leading	1,232	1.19 s	8.0 s	Leading-indicator chart
dash_exposure	1,621	0.64 s	15.1 s	Hours worked, for incident rates
cofi_invoice_columns	258	3.90 s	19.5 s	Receivables, sent whole to the browser
cofi_route_months	216	4.08 s	18.0 s	Route P&L, sent whole to the browser
watch_people	1,220	0.65 s	6.5 s	People watchlist
demo_reset	6	22.9 s	28.1 s	Demo reset (see section 4)

The same calls timed one at a time on a warm cache:

Call	VP (all 10 sites)	Supervisor (1 site)
watch_assets	686 ms	172 ms
dash_sites	209 ms	65 ms
dash_leading	176 ms	47 ms
cofi_invoice_columns, 24 months	821 ms, 7.7 MB of JSON
cofi_route_months, 24 months	567 ms, 1.5 MB of JSON

The large gap between the mean and the warm single call is consistent with a dashboard firing a dozen of these together on the smallest compute size. That is our reading, not a measurement.

Disk spills. Four functions write most of the 13 GB of temp files: dash_exposure (3.9 GB), dash_leading (2.9 GB), cofi_route_months (2.4 GB) and watch_assets (1.5 GB). Each one aggregates route_day or inspection over the whole period on every call. cofi_invoice_columns already sets its own work_mem to 64 MB; the others use the 3.5 MB default.

Full scans of inspection. inspection has had 2,926 sequential scans that read 636 million rows in total, about the whole table each time. Most of them come from one policy:

-- inspection_defect, SELECT
inspection_id IN (SELECT i.inspection_id FROM inspection i)

Any read of inspection_defect therefore evaluates inspection's own policy over all 233,246 rows. watch_assets does this twice per call. inspection_defect has no site_id of its own to filter on. Nine more policies use the same parent-subquery shape (incident_contributing_factor, incident_incident_type, incident_event, observation_item, role, investigation_request_event, training_observation_item, customer_site, customer_service); their parents are small enough that it does not show yet.

Every other policy wraps its helper calls as (SELECT app.site_scope()), so they run once per statement, not once per row. The helpers are STABLE SECURITY DEFINER with a fixed search_path.

3. Indexes

• Foreign keys with no index on the referencing side: 37 (36 in public, plus app.user_site.site_id). The largest are invoice_line.customer_service_id (209k rows), telematics_event.event_type, action.verifier_person_id and action.work_done_by_person_id (11k each), and observation.focus_area / observation_type. Most of the rest are on tables with no rows yet. The full list is in inventory.md.
• Never used since creation (non-unique): 18. The largest is inspection_mechanic_idx (1.6 MB). The rest are under 350 kB.
• Covered by a wider index: 6. incident_status_idx, observation_item_observation_idx, policy_clause_section_idx, investigation_request_event_request_idx, route_day_driver_date_idx and route_day_route_idx are each the leading columns of another index on the same table.
• No uniqueness where the app assumes it: one open asset_downtime row per truck, one live qualification per person and requirement, one open training assignment per person and course, one pre-trip per truck per day. All four are checked in code, or not at all, rather than by an index.

4. Two writes at once

Read committed throughout. What each write path relies on today:

Path	Protected by	What two callers at once can do
Human-readable numbers (IN-, ACT-, TSK-, INS-, OBS-, requests)	Advisory lock per date for incidents, sequences for the rest; unique key on each	Nothing. Sequences leave gaps on rollback
A capture retried by the offline outbox (capture_dvir, capture_incident, capture_observation)	A "does this id exist" check, then a plain insert; the primary key	If the retry arrives while the first call is still running, the retry fails with 23505. The client treats that as a rejection and parks a record that did save
A DVIR taking a truck out of service while a repair sign-off on the same truck returns it (raise_dvir_repair, release_hold_on_repair)	Nothing	The truck can read In Service with a new out-of-service defect open and no downtime clock running. Two repairs signed off together can leave a truck held for good
Opening a downtime row (raise_dvir_repair)	Existence check only	Two open rows for one truck; hours counted twice
Reassigning a repair (dispatch_action) against "work done" or "verify"	Status read into a variable, UPDATE by id only	The verifier can close an action the new assignee has not done
Verifying against rejecting a DQ document (verify_person_document, reject_person_document)	Status read into a variable, UPDATE by id only	A live qualification resting on a rejected document, or two live qualifications
Request-for-input events (log_request_event)	max(seq)+1, unique key	One of two simultaneous events fails with 23505
A request raised on an incident being closed	Nothing	A Closed incident with a live request
Two people saving the same investigation (save_investigation)	The incident row lock	The second save replaces the first in full, without a warning
demo_snapshot	Each table copied in its own statement	A baseline taken while someone is using the app can be inconsistent, and a later reset then fails on a foreign key
demo_reset, callable by a VP from the app	TRUNCATE of 23 tables in one statement, 120 s timeout	ACCESS EXCLUSIVE on inspection, incident, action, task, qualification and 18 more for 23 to 28 seconds. Every read of those tables waits. Triggers are disabled table by table first, in a fixed order, so a capture taking locks in another order can deadlock with it

complete_action, the asset odometer update, the incident and request audit logs, and the action, asset, downtime and task lock order were checked and hold. The function-level detail, with line numbers into schema.sql, is available on request.

5. Access controls

What holds:

• RLS is on for every table in public and app. Signed out, anon gets "permission denied" on incident and empty results from the person and request functions.
• All 77 SECURITY DEFINER functions pin search_path. The three views run security_invoker.
• demo_baseline and sales are not reachable by anon or authenticated at all. The four sales tables have RLS off and rely on that.
• The three Storage buckets are private, size-capped at 25 MB, with an allow-list of file types.

What a reviewer will see:

• Default grants. authenticated holds INSERT/UPDATE/DELETE/TRUNCATE on 51 tables and anon holds every privilege on 29 of them, from Supabase's project defaults. RLS stops the row-level ones. TRUNCATE is not subject to RLS; PostgREST cannot issue it, so it is reachable only by a role with a direct SQL connection. Revoking these is open (docs/todo.md, item 12).
• 55 SECURITY DEFINER functions are executable by anon. Those in app are unreachable because anon has no USAGE on the schema. Those in public check the caller, with one exception: next_incident_number(date) answers a signed-out caller, which tells them how many incidents were recorded on a given day.
• hub_question_answer accepts INSERT and UPDATE from anon with true as the check. It is the internal hub's questions board, not product.
• Raw-token inserts. The incident_insert and action_insert policies let a signed-in user choose their own incident_number / action_number, which bypasses the numbering above. A collision shows up as a unique-key error, not bad data.
• Already known: a driver's token can read the investigation columns of their own incident, and a GM's can read settled cost (docs/access-and-relationships.md).

Questions for the review

1. Which compute size, and which timeouts, should the next environment start from?
2. Should the dashboard aggregates keep reading the base tables on every call, or read from something refreshed on a schedule?
3. How should a child table's policy reach its parent's scope: by subquery as now, or by a copied site_id?
4. Which of the uniqueness rules in section 3 belong in the database?
5. What locking discipline should the state-changing functions follow: row locks, status in the WHERE, version columns, or a stricter isolation level?
6. Should anything that truncates live tables be callable from the app, and in what window?
7. Which grants should anon and authenticated hold once the defaults are revoked?