Database inventory, for the DBA review

Generated 2026-10-08 by scripts/db/gen_dba_pack.py from the live database. Row counts and sizes are a reading on that date and move whenever the demo is used; structure moves only when a migration lands. Do not edit by hand.

Server

Fact	Value
Engine	PostgreSQL 17.6 on x86_64-pc-linux-gnu
Database size	333 MB
Migrations applied	109 (latest `20261007120000`)
Extensions	`pg_stat_statements` 1.11, `pgcrypto` 1.3, `supabase_vault` 0.3.1, `uuid-ossp` 1.1

pgcrypto, uuid-ossp and pg_stat_statements are standard on every managed PostgreSQL service. supabase_vault is Supabase's own; it holds 0 secrets and nothing in the migrations uses it.

The hard-coded date

app.today() returns 2026-09-28, a constant, not the clock. The data set is synthetic and anchored to that date, so every overdue, expiring and status flag in SQL is computed against it. Three more copies of the same date live outside the database (scripts/window.py, packages/core/src/config.ts, packages/core/src/cofi/facts.ts). Any real deployment has to change all four together.

Schemas

Schema	Tables	Views	Functions	Table size	Signed-out role can enter	Signed-in role can enter	What it is
`app`	2	0	71	80 kB	no	yes	Helper functions every policy calls (who is signed in, which sites they see), and the login-to-person link.
`cofi`	0	1	3	0 bytes	no	yes	One view and three functions behind the in-app copy of the client's Power BI report.
`demo_baseline`	28	0	0	61 MB	no	no	Not product: a frozen copy of the demo's writable tables, for resetting the demo. Left out of schema.sql.
`public`	72	2	83	252 MB	yes	yes	The product's tables, and the functions the app calls.
`sales`	4	0	0	136 kB	no	no	Not product: the internal sales lead tracker.

Tables

Every table the app owns, in the ERD's groups. Rows are exact counts. A table marked not product is in the database for the demo or for internal use; the definitions are the same ones docs/ERD.md carries.

Table	Group	Rows	Size	Indexes	RLS	Policies	Note
`asset`	Core platform	396	232 kB	5	on	1
`asset_type_requirement`	Core platform	19	32 kB	1	on	1
`person`	Core platform	601	352 kB	6	on	2
`qualification`	Core platform	2,006	888 kB	7	on	1
`requirement`	Core platform	29	32 kB	1	on	1
`requirement_job_title`	Core platform	67	32 kB	1	on	1
`role`	Core platform	728	248 kB	2	on	1
`site`	Core platform	10	48 kB	2	on	1
`site_type`	Core platform	4	32 kB	1	on	1
`contributing_factor`	Event capture	24	32 kB	1	on	1
`incident`	Event capture	1,490	1888 kB	16	on	3
`incident_attachment`	Event capture	0	40 kB	4	on	4
`incident_contributing_factor`	Event capture	3,309	480 kB	2	on	3
`incident_incident_type`	Event capture	1,714	264 kB	2	on	2
`incident_type`	Event capture	12	32 kB	1	on	1
`observation`	Event capture	10,810	4608 kB	6	on	2
`observation_item`	Event capture	0	104 kB	6	on	2
`telematics_event`	Event capture	13,267	5584 kB	7	on	1
`telematics_event_type`	Event capture	10	32 kB	1	on	1
`incident_event`	Investigation and audit trail	4,393	1528 kB	2	on	1
`investigation_request`	Investigation and audit trail	4	112 kB	6	on	1
`investigation_request_event`	Investigation and audit trail	10	64 kB	3	on	1
`investigation_request_type`	Investigation and audit trail	8	64 kB	3	on	1
`action`	Follow-through	11,421	5648 kB	10	on	4
`inspection`	Follow-through	233,246	79 MB	9	on	2
`inspection_defect`	Follow-through	41,031	11 MB	4	on	2
`task`	Follow-through	16,258	4976 kB	6	on	2
`training`	Follow-through	3,111	2680 kB	12	on	2
`training_observation_item`	Follow-through	0	40 kB	2	on	2
`document_request`	Training catalog and DQ file	1	64 kB	3	on	1
`operator_release`	Training catalog and DQ file	0	80 kB	4	on	1
`person_document`	Training catalog and DQ file	2	104 kB	6	on	2
`training_course`	Training catalog and DQ file	95	88 kB	2	on	1
`training_course_focus_area`	Training catalog and DQ file	119	48 kB	2	on	1
`training_material`	Training catalog and DQ file	0	56 kB	3	on	1
`external_training`	Training sessions and rules	0	40 kB	4	on	1
`training_assignment_rule`	Training sessions and rules	0	24 kB	2	on	1
`training_event`	Training sessions and rules	0	48 kB	5	on	1
`training_session`	Training sessions and rules	0	48 kB	5	on	1
`training_session_attendee`	Training sessions and rules	0	32 kB	3	on	1
`focus_area`	Observation catalog	14	32 kB	1	on	1
`observation_behavior`	Observation catalog	74	96 kB	3	on	1
`observation_cadence_rule`	Observation catalog	5	32 kB	1	on	1
`observation_risk_rule`	Observation catalog	13	48 kB	2	on	1
`observation_setting`	Observation catalog	2	32 kB	1	on	1
`observation_type`	Observation catalog	5	32 kB	1	on	1
`observation_type_focus_area`	Observation catalog	20	32 kB	1	on	1
`observation_behavior_policy`	Policy library	152	152 kB	3	on	1
`policy_clause`	Policy library	2,288	2040 kB	4	on	1
`policy_document`	Policy library	1	32 kB	1	on	1
`policy_section`	Policy library	95	104 kB	3	on	1
`asset_downtime`	Route operations	2,426	784 kB	3	on	1
`labor_month`	Route operations	1,008	288 kB	1	on	1
`route`	Route operations	241	88 kB	2	on	1
`route_day`	Route operations	131,641	55 MB	6	on	1
`route_stop_plan`	Route operations	4,357	680 kB	2	on	1
`aging_bucket`	Financial layer	5	32 kB	1	on	1
`ar_invoice`	Financial layer	102,970	20 MB	4	on	1
`customer`	Financial layer	4,296	568 kB	1	on	1
`customer_service`	Financial layer	4,357	768 kB	2	on	1
`customer_site`	Financial layer	4,296	576 kB	1	on	1
`invoice_line`	Financial layer	208,868	46 MB	4	on	1
`pnl_line`	Financial layer	9	32 kB	1	on	1
`revenue_budget_daily`	Financial layer	23,296	2808 kB	1	on	1
`route_pnl_month`	Financial layer	5,776	1208 kB	2	on	1
`truck_cost_month`	Financial layer	7,307	1112 kB	1	on	1
`ask_log`	App support	0	32 kB	3	on	2	not product: Ask, a question box that is built but switched off
`benchmark`	App support	2	64 kB	3	on	4
`capture_draft`	App support	1	56 kB	3	on	1
`hub_demo_removed`	App support	0	16 kB	1	on	3	not product: the internal hub's demo cards
`hub_prospect`	App support	5	144 kB	1	on	4	not product: the internal hub's demo cards
`hub_question_answer`	App support	12	64 kB	1	on	3	not product: the internal hub's questions board
`app.user_account`	Auth link (schema app)	6	48 kB	2	on	1
`app.user_site`	Auth link (schema app)	6	32 kB	1	on	1
`sales.checklist_answer`	Sales (schema sales)	2	32 kB	1	off	0	not product: the internal sales lead tracker
`sales.lead`	Sales (schema sales)	1	32 kB	1	off	0	not product: the internal sales lead tracker
`sales.lead_event`	Sales (schema sales)	0	48 kB	2	off	0	not product: the internal sales lead tracker
`sales.team_member`	Sales (schema sales)	1	24 kB	1	off	0	not product: the internal sales lead tracker

Foreign keys with no index on their leading column

49 of them. Rows is the referencing table's count, which is what decides whether a missing index matters: a delete or key change on the referenced row scans the referencing table.

Table	Rows	Column(s)	References
`invoice_line`	208,868	customer_service_id	`customer_service`
`telematics_event`	13,267	event_type	`telematics_event_type`
`action`	11,421	verifier_person_id	`person`
`action`	11,421	work_done_by_person_id	`person`
`observation`	10,810	focus_area	`focus_area`
`observation`	10,810	observation_type	`observation_type`
`truck_cost_month`	7,307	site_id	`site`
`incident_event`	4,393	actor_person_id	`person`
`customer_service`	4,357	customer_site_id	`customer_site`
`route_stop_plan`	4,357	site_id	`site`
`customer`	4,296	site_id	`site`
`customer_site`	4,296	customer_id	`customer`
`training`	3,111	evaluator_person_id	`person`
`training`	3,111	renews_training_id	`training`
`asset_downtime`	2,426	inspection_id	`inspection`
`incident`	1,490	signed_off_by_person_id	`person`
`observation_behavior_policy`	152	clause_id, section_id	`policy_clause`
`policy_section`	95	parent_section_id	`policy_section`
`training_course`	95	created_by_person_id	`person`
`training_course`	95	requirement_id	`requirement`
`observation_type_focus_area`	20	focus_area	`focus_area`
`asset_type_requirement`	19	requirement_id	`requirement`
`observation_risk_rule`	13	behavior_id	`observation_behavior`
`investigation_request_event`	10	actor_person_id	`person`
`site`	10	site_type	`site_type`
`app.user_site`	6	site_id	`site`
`investigation_request`	4	request_type_id	`investigation_request_type`
`investigation_request`	4	requested_by_person_id	`person`
`person_document`	2	requirement_id	`requirement`
`person_document`	2	submitted_by_person_id	`person`
`person_document`	2	verified_by_person_id	`person`
`capture_draft`	1	subject_person_id	`person`
`document_request`	1	fulfilled_by_document_id	`person_document`
`document_request`	1	qualification_id	`qualification`
`document_request`	1	requested_by_person_id	`person`
`document_request`	1	requirement_id	`requirement`
`external_training`	0	course_id	`training_course`
`external_training`	0	reviewer_person_id	`person`
`external_training`	0	submitted_by_person_id	`person`
`incident_attachment`	0	uploaded_by_person_id	`person`
`operator_release`	0	asset_id	`asset`
`operator_release`	0	released_by_person_id	`person`
`training_assignment_rule`	0	created_by_person_id	`person`
`training_material`	0	added_by_person_id	`person`
`training_material`	0	superseded_by	`training_material`
`training_session`	0	cancelled_by_person_id	`person`
`training_session`	0	created_by_person_id	`person`
`training_session`	0	signed_by_person_id	`person`
`training_session_attendee`	0	added_by_person_id	`person`

Functions

Schema	Functions	SECURITY DEFINER	Trigger functions
app	71	50	19
cofi	3	1	0
public	83	52	0

SECURITY DEFINER functions are the write paths: the app calls them and they check the caller themselves. docs/ERD.md lists which functions write each table.

Triggers (20)

Table	Trigger	Function
`action`	action_release_hold	`app.release_hold_on_repair`
`action`	action_set_verifier	`app.set_action_verifier`
`ask_log`	ask_log_append_only	`app.refuse_ask_log_change`
`external_training`	external_training_log_event	`app.log_external_row`
`incident`	incident_drops_its_draft	`app.drop_filed_draft`
`incident`	incident_log_event	`app.log_incident_event`
`incident`	incident_no_live_requests	`app.assert_no_live_requests`
`incident`	incident_owner_is_supervisory	`app.assert_investigation_owner_supervisory`
`incident_attachment`	incident_attachment_log_event	`app.log_incident_attachment_event`
`incident_event`	incident_event_no_change	`app.refuse_incident_event_change`
`investigation_request_event`	investigation_request_event_append_only	`app.refuse_request_event_change`
`observation`	observation_drops_its_draft	`app.drop_filed_draft`
`training`	training_evaluation_gate	`app.training_evaluation_gate`
`training`	training_fill_source	`app.training_fill_source`
`training`	training_log_event	`app.log_training_row`
`training_event`	training_event_no_change	`app.refuse_training_event_change`
`training_session`	training_session_lock	`app.lock_training_session`
`training_session`	training_session_log_event	`app.log_session_row`
`training_session_attendee`	training_attendee_lock	`app.lock_training_attendee`
`training_session_attendee`	training_attendee_log_event	`app.log_attendee_row`

What is Supabase rather than PostgreSQL

Everything in this section has to be replaced or re-pointed on any other PostgreSQL host. Everything not in it is plain PostgreSQL.

Login (Supabase Auth). app.user_account has a foreign key to auth.users; sales.team_member has a foreign key to auth.users. 8 functions read the signed-in user from the login token directly: app.access_level, app.can_see_actual_cost, app.can_see_cost, app.current_person_id, app.demo_snapshot, app.is_sales_team, app.site_scope, public.current_person. 2 policies outside Storage do the same (app.user_account user_account_self, app.user_site user_site_self); every other policy calls the app.* helpers, so changing where identity comes from is a change to those few functions, not to every policy.

Files (Supabase Storage). 4 buckets, all private: incident-attachments, person-documents, training-evidence, training-material. 11 policies on storage.objects decide who may read and write a file by asking the database, and public.demo_attachment_paths refers to Storage from SQL.

Roles and grants. The web API (PostgREST) connects as anon before sign-in and authenticated after. service_role bypasses row-level security; nothing in the repository uses it (our scripts connect with the database URL instead). Supabase grants all three every privilege on new tables by default, so on most tables row-level security, not the grant, is what stops a write.

Role	Public tables with any grant (of 72)	With a write grant
anon	27	27
authenticated	72	49
service_role	72	72

105 policies on our own tables. By the role they apply to (a policy can name more than one): authenticated 105, anon 3.

Migration history. Kept by the Supabase CLI in supabase_migrations.schema_migrations; the files are supabase/migrations/*.sql.