SCOPE database: review pack

For the DBA reviewing SCOPE ahead of moving it off Supabase. It describes the database as it stands. Where it goes next is not decided here.

In this folder

File	What it is
inventory.md	Generated. Sizes and row counts, schemas, every table, foreign keys with no index, functions, triggers, grants, and every piece that is Supabase rather than PostgreSQL
schema.sql	Generated. pg_dump --schema-only of the four schemas the app owns (public, app, cofi, sales): tables, constraints, indexes, functions, triggers, policies and grants, with no data
prerequisites.sql	Hand-written. What a plain PostgreSQL needs before schema.sql will load
review-findings.md	Hand-written, measured 6 October 2026. Performance, indexes, concurrent writes and access controls as they stand, and the questions they raise

On the hub, Data and technical > Database review downloads each SQL file on its own, or the whole folder as scope-dba-pack.zip, which also carries docs/ERD.html as schema-diagram.html.

Regenerate both before handing them over, since row counts move whenever the demo is used:

python3 scripts/db/gen_dba_pack.py

Elsewhere in the repository

File	What it is
docs/ERD.html, docs/ERD.md	The schema as a diagram, generated from the live database: one definition per table, its keys, its row-level policies and the functions that write it
data/README.md	The sample data set: what each CSV holds and how the tables fit together
docs/access-and-relationships.md	The access model in plain English, and the three places only the app enforces a rule
supabase/migrations/	All 105 migrations, applied in filename order
scripts/db/verify_*.sql	Tests of the access rules run as real logins, each in a transaction that rolls back

The facts in brief

• PostgreSQL 17.6, hosted by Supabase on AWS us-east-1. 431 MB in all; the largest tables are inspection (233k rows) and invoice_line (209k).
• The data is synthetic. Every row is generated by scripts/generate_sample_data.py and loaded by scripts/db/load.sh. It holds no real people and can be rebuilt from nothing.
• Security lives in the database. Row-level security is on for all 67 tables in public. The app reads through Supabase's web API (PostgREST) as the signed-in user, and writes through SECURITY DEFINER functions that check the caller. All 77 of those functions pin their search_path.
• Entity tables use UUID keys minted on the device, because capture works offline. Reference tables use readable codes (SITE-RUT).
• Not everything is product. demo_baseline (the demo's reset copy), sales (an internal lead tracker) and four support tables are marked in inventory.md.
• "Today" is fixed. app.today() returns 2026-09-28, the demo date, not the clock.

Loading schema.sql somewhere other than Supabase

Run prerequisites.sql first. It creates stand-ins for the parts Supabase provides:

• the roles anon, authenticated and service_role, which the grants and policies name
• auth.users with an id uuid key (two foreign keys point at it), and the function auth.uid()
• storage.objects, which one function reads

psql -d scratch -f docs/dba/prerequisites.sql
psql -d scratch -f docs/dba/schema.sql

Tested on 2 October 2026 on a plain PostgreSQL 17.11:

• Every object arrived, with the same counts as the live database: 73 tables, 123 functions, 100 policies, 215 indexes, 11 triggers, 424 constraints and 3 views.
• All 123 function bodies re-validated against the loaded schema.
• 13 statements fail, and both causes are expected. One is schema "public" already exists. The other 12 are Supabase's project-wide default-privilege settings, which run as its own admin role supabase_admin. That is the setting behind the over-wide grants in inventory.md, so it is right that it does not carry over.

No extension is required: the only generated keys use gen_random_uuid(), which is built in.

Not covered here

Facts the files cannot show:

• Backups and point-in-time recovery are whatever the Supabase project's plan provides. They are not set up or recorded in the repository.
• There is one environment. Migrations are applied straight to the live database, with no separate development or test database.
• There is no monitoring or alerting beyond what Supabase's dashboard shows.

Known open items: a driver's login can read the investigation columns of their own incident, and a GM's login can read settled cost (docs/access-and-relationships.md). Default write grants on 27 tables are still to be revoked (docs/todo.md, item 12).