SCOPE schema, as built

Generated by scripts/db/gen_erd.py from the live database. Do not edit by hand; re-run after any migration. The hand-drawn SCOPE_ERD.png in the client context folder is the original brief and stays as it was.

Source of truth: 78 tables (72 in public, 2 in app, 4 in sales) and 3 views, across 110 applied migrations (latest 20261008120000).

Sections: one diagram per group; Definitions; Access; Views; Constraints; Foreign keys.

Reading the diagrams: ||--o{ is one-to-zero-or-many with a mandatory parent; |o--o{ means the foreign key is nullable. Edge labels are the child column. (deferred) marks the site/person cycle, which loads in one transaction. Every attribute is NOT NULL unless marked nullable. A column marked to <table> is a foreign key into another group; the whole set is in Foreign keys. For the schema as one picture, open docs/ERD.html.

Core platform

erDiagram
  site {
    text site_id PK
    text site_name
    text site_type FK
    text region
    text address_line1
    text city
    text state
    int headcount
    uuid site_manager_person_id FK
    text operating_days
    date opened_date
    text status
  }
  site_type {
    text site_type PK
    text walkthrough_label
  }
  person {
    uuid person_id PK
    text employee_number UK
    text first_name
    text last_name
    text full_name
    text employee_type
    text job_title
    text site_id FK
    uuid supervisor_person_id FK "nullable"
    date hire_date
    date termination_date "nullable"
    text status
    text email UK
    text phone
    text shift
    bool can_see_cost "nullable"
    text pay_group "nullable"
    numeric pay_rate_usd "nullable"
  }
  role {
    uuid role_id PK
    uuid person_id FK
    text role_name
    bool is_primary
    date start_date
    date end_date "nullable"
    text status
  }
  requirement {
    text requirement_id PK
    text requirement_type
    text name
    text description
    text recurrence
    int recurrence_months
    text regulatory_reference "nullable"
    bool is_mandatory
    text requirement_group "nullable"
    int group_rank "nullable"
  }
  requirement_job_title {
    text requirement_id PK, FK
    text job_title PK
  }
  qualification {
    uuid qualification_id PK
    uuid person_id FK
    text requirement_id FK
    text qual_type
    text qual_name
    text identifier "nullable"
    text issuing_authority
    date issued_date
    date expiry_date
    text status
    uuid verified_by_person_id FK
    date superseded_on "nullable"
    uuid source_training_id FK "nullable, to training"
  }
  asset {
    uuid asset_id PK
    text asset_type
    text asset_class
    text identifier UK
    text site_id FK
    text make
    text model
    int model_year
    text vin UK "nullable"
    text license_plate "nullable"
    text availability_status
    text maintenance_status
    int odometer_miles "nullable"
    int engine_hours "nullable"
    date in_service_date
    bool telematics_enabled
  }
  asset_type_requirement {
    text asset_type PK
    text requirement_id PK, FK
    text note "nullable"
  }
  site ||--o{ asset : "site_id"
  requirement ||--o{ asset_type_requirement : "requirement_id"
  site ||--o{ person : "site_id (deferred)"
  person |o--o{ person : "supervisor_person_id (deferred)"
  person ||--o{ qualification : "person_id"
  requirement ||--o{ qualification : "requirement_id"
  person ||--o{ qualification : "verified_by_person_id"
  requirement ||--o{ requirement_job_title : "requirement_id"
  person ||--o{ role : "person_id"
  person ||--o{ site : "site_manager_person_id (deferred)"
  site_type ||--o{ site : "site_type"

Referenced from other groups:

• site by action, app.user_site, ar_invoice, asset_downtime, customer, incident, inspection, invoice_line, labor_month, observation, revenue_budget_daily, route, route_day, route_pnl_month, route_stop_plan, task, telematics_event, training, training_session, truck_cost_month
• person by action, app.user_account, capture_draft, document_request, external_training, incident, incident_attachment, incident_event, inspection, inspection_defect, investigation_request, investigation_request_event, observation, operator_release, person_document, route_day, task, telematics_event, training, training_assignment_rule, training_course, training_material, training_session, training_session_attendee
• requirement by document_request, person_document, training, training_course
• qualification by document_request, person_document
• asset by asset_downtime, incident, inspection, operator_release, route_day, telematics_event, truck_cost_month

Event capture

erDiagram
  incident {
    uuid incident_id PK
    text incident_number UK
    date incident_date
    time incident_time
    text site_id FK "to site"
    text title
    text description
    uuid person_id FK "to person"
    text person_job_title
    numeric person_tenure_years
    uuid supervisor_person_id FK "to person"
    text primary_incident_type_id FK
    text severity
    text injury_classification "nullable"
    text injury_illness_type "nullable"
    text body_part "nullable"
    bool recordable
    bool dart_case
    int lost_days "nullable"
    int restricted_days "nullable"
    bool reportable_to_agency
    uuid asset_id FK "nullable, to asset"
    bool occurred_on_company_premises
    text specific_location
    text equipment_involved "nullable"
    text root_cause_category "nullable"
    text root_cause "nullable"
    text corrective_action_summary "nullable"
    uuid reported_by_person_id FK "to person"
    date date_reported
    uuid investigation_owner_person_id FK "nullable, to person"
    int estimated_cost_usd "nullable"
    text status
    bool is_lti
    bool treatment_sought "nullable"
    bool returned_to_work_same_shift "nullable"
    numeric actual_cost_usd "nullable"
    uuid signed_off_by_person_id FK "nullable, to person"
    date signed_off_on "nullable"
    bool preventable "nullable"
    text route_id FK "nullable, to route"
    text lob "nullable"
  }
  incident_type {
    text incident_type_id PK
    text incident_type_name
    text description
    text default_severity
    bool is_active
  }
  incident_incident_type {
    uuid incident_id PK, FK
    text incident_type_id PK, FK
    bool is_primary
  }
  contributing_factor {
    text contributing_factor_id PK
    text factor_name
    text factor_category
    bool is_active
  }
  incident_contributing_factor {
    uuid incident_id PK, FK
    text contributing_factor_id PK, FK
    int rank
  }
  incident_attachment {
    uuid attachment_id PK
    uuid incident_id FK "to investigation_request"
    text kind
    text file_name
    text content_type
    int byte_size
    text storage_path UK
    text caption "nullable"
    uuid uploaded_by_person_id FK "to person"
    timestamptz uploaded_at
    uuid request_id FK "nullable, to investigation_request"
  }
  observation {
    uuid observation_id PK
    text observation_number UK
    text site_id FK "to site"
    uuid employee_person_id FK "to person"
    uuid supervisor_person_id FK "to person"
    date observation_date
    time observation_time
    text observation_type FK "to observation_type"
    text focus_area FK "to focus_area"
    text result
    text notes
    int safe_behaviors_count
    int at_risk_behaviors_count
    bool corrective_action_required
    date follow_up_due_date "nullable"
    text status
  }
  observation_item {
    uuid observation_item_id PK
    uuid observation_id FK, UK
    text focus_area FK "to focus_area"
    text behavior UK
    text result
    bool required
    text risk_reason "nullable"
    text notes "nullable"
    text behavior_id FK "nullable, to observation_behavior"
    text corrective_action "nullable"
    bool is_other
  }
  telematics_event {
    uuid telematics_event_id PK
    text provider_event_id UK
    uuid driver_person_id FK "to person"
    uuid asset_id FK "to asset"
    text site_id FK "to site"
    timestamp event_datetime
    date event_date
    text event_type FK
    int severity_score
    text severity_band
    int speed_mph
    int posted_speed_mph
    numeric latitude
    numeric longitude
    text location
    bool video_available
    text review_status
    bool coached
    uuid coached_by_person_id FK "nullable, to person"
  }
  telematics_event_type {
    text event_type PK
    int sort_order
    bool active
  }
  incident_type ||--o{ incident : "primary_incident_type_id"
  incident ||--o{ incident_attachment : "incident_id"
  contributing_factor ||--o{ incident_contributing_factor : "contributing_factor_id"
  incident ||--o{ incident_contributing_factor : "incident_id"
  incident ||--o{ incident_incident_type : "incident_id"
  incident_type ||--o{ incident_incident_type : "incident_type_id"
  observation ||--o{ observation_item : "observation_id"
  telematics_event_type ||--o{ telematics_event : "event_type"

Referenced from other groups:

• incident by action, incident_event, investigation_request
• observation by action
• observation_item by training_observation_item
• telematics_event by action
• telematics_event_type by observation_risk_rule

Investigation and audit trail

erDiagram
  incident_event {
    uuid event_id PK
    uuid incident_id FK "to incident"
    bigint seq
    timestamptz occurred_at
    date occurred_on
    uuid actor_person_id FK "nullable, to person"
    text actor_name
    text event_type
    text summary
    jsonb detail "nullable"
    bool reconstructed
  }
  investigation_request {
    uuid request_id PK, UK
    text request_number UK
    uuid incident_id FK, UK "to incident"
    uuid requested_by_person_id FK "to person"
    uuid assigned_to_person_id FK "to person"
    text request_type_id FK
    text prompt
    date requested_on
    date due_date
    text status
    date closed_on "nullable"
  }
  investigation_request_event {
    uuid event_id PK
    uuid request_id FK, UK
    int seq UK
    text event_type
    uuid actor_person_id FK "to person"
    timestamptz occurred_at
    text body "nullable"
  }
  investigation_request_type {
    text request_type_id PK
    text name UK
    text prompt_hint
    int sort_order UK
  }
  investigation_request_type ||--o{ investigation_request : "request_type_id"
  investigation_request ||--o{ investigation_request_event : "request_id"

Referenced from other groups:

• investigation_request by incident_attachment

Follow-through

erDiagram
  inspection {
    uuid inspection_id PK
    text inspection_number UK
    uuid asset_id FK "to asset"
    text site_id FK "to site"
    uuid inspector_person_id FK "to person"
    text inspection_type
    date inspection_date
    time inspection_time
    text result
    int defect_count
    bool out_of_service
    int odometer_miles "nullable"
    int engine_hours "nullable"
    uuid mechanic_signoff_person_id FK "nullable, to person"
    bool driver_signoff
    text status
    date next_due_date
  }
  inspection_defect {
    uuid inspection_defect_id PK
    uuid inspection_id FK
    text component_category
    text component
    text defect_severity
    text remarks
    bool corrected
    uuid corrected_by_person_id FK "nullable, to person"
    date corrected_date "nullable"
    bool safe_to_operate
  }
  action {
    uuid action_id PK
    text action_number UK
    text action_title
    text source_type
    uuid incident_id FK "nullable, to incident"
    uuid observation_id FK "nullable, to observation"
    uuid inspection_id FK "nullable"
    uuid telematics_event_id FK "nullable, to telematics_event"
    text site_id FK "to site"
    uuid assignee_person_id FK "to person"
    text action_type
    text description
    text priority
    date created_date
    date due_date
    date completed_date "nullable"
    bool verification_required
    uuid verified_by_person_id FK "nullable, to person"
    text status
    date work_done_on "nullable"
    uuid work_done_by_person_id FK "nullable, to person"
    uuid verifier_person_id FK "nullable, to person"
  }
  task {
    uuid task_id PK
    text task_number UK
    uuid action_id FK
    text task_title
    uuid assignee_person_id FK "to person"
    text site_id FK "to site"
    text channel
    date created_date
    date due_date
    date completed_date "nullable"
    int reminder_count
    int escalation_level
    uuid escalated_to_person_id FK "nullable, to person"
    text status
  }
  training {
    uuid training_id PK
    uuid person_id FK "to person"
    text requirement_id FK "nullable, to requirement"
    text course_name
    text training_type
    uuid trainer_person_id FK "nullable, to person"
    text site_id FK "to site"
    date assigned_date
    date due_date
    date completion_date "nullable"
    date expiry_date "nullable"
    numeric duration_hours
    int score "nullable"
    text status
    uuid source_action_id FK "nullable"
    bool is_remedial
    text course_id FK "nullable, to training_course"
    date acknowledged_on "nullable"
    text completion_method "nullable"
    uuid session_id FK "nullable, to training_session"
    uuid external_training_id FK "nullable, to external_training"
    text result "nullable"
    uuid evaluator_person_id FK "nullable, to person"
    date evaluated_on "nullable"
    text assignment_source
    uuid rule_id FK "nullable, to training_assignment_rule"
    uuid renews_training_id FK "nullable"
    jsonb material_snapshot "nullable"
    timestamptz signed_at "nullable"
  }
  training_observation_item {
    uuid training_id PK, FK
    uuid observation_item_id PK, FK "to observation_item"
  }
  inspection |o--o{ action : "inspection_id"
  inspection ||--o{ inspection_defect : "inspection_id"
  action ||--o{ task : "action_id"
  training |o--o{ training : "renews_training_id"
  action |o--o{ training : "source_action_id"
  training ||--o{ training_observation_item : "training_id"

Referenced from other groups:

• inspection by asset_downtime
• training by person_document, qualification, training_session_attendee

Training catalog and DQ file

erDiagram
  training_course {
    text course_id PK
    text course_name UK
    text requirement_id FK "nullable, to requirement"
    text delivery
    text source
    int sort_order
    bool active
    numeric default_duration_hours
    bool requires_evaluation
    int renewal_window_days
    uuid created_by_person_id FK "nullable, to person"
    text created_by_name "nullable"
    timestamptz updated_at
    text retired_reason "nullable"
  }
  training_course_focus_area {
    text course_id PK, FK
    text focus_area PK, FK "to focus_area"
  }
  training_material {
    uuid material_id PK
    text course_id FK
    text kind
    text title
    text storage_path "nullable"
    text url "nullable"
    text content_type "nullable"
    int byte_size "nullable"
    int sort_order
    uuid added_by_person_id FK "nullable, to person"
    text added_by_name
    timestamptz added_at
    int version
    uuid superseded_by FK "nullable"
    date superseded_on "nullable"
    text language "nullable"
  }
  person_document {
    uuid document_id PK
    uuid person_id FK "to person"
    text requirement_id FK "to requirement"
    uuid qualification_id FK "nullable, to qualification"
    uuid training_id FK "nullable, to training"
    text status
    text claimed_identifier "nullable"
    text claimed_issuing_authority "nullable"
    date claimed_issued_date "nullable"
    date claimed_expiry_date "nullable"
    uuid submitted_by_person_id FK "to person"
    timestamptz submitted_at
    date submitted_on
    uuid verifier_person_id FK "nullable, to person"
    uuid verified_by_person_id FK "nullable, to person"
    date verified_on "nullable"
    text rejection_reason "nullable"
    date superseded_on "nullable"
    text file_name
    text content_type
    int byte_size
    text storage_path UK
    text verifier_name "nullable"
    text verified_by_name "nullable"
    text submitted_by_name "nullable"
    text person_name "nullable"
  }
  document_request {
    uuid request_id PK
    uuid person_id FK "to person"
    text requirement_id FK "to requirement"
    date due_date
    text reason
    text note "nullable"
    uuid requested_by_person_id FK "to person"
    date requested_on
    text status
    uuid fulfilled_by_document_id FK "nullable"
    date fulfilled_on "nullable"
    text cancelled_reason "nullable"
    date cancelled_on "nullable"
    text requested_by_name "nullable"
    uuid qualification_id FK "nullable, to qualification"
  }
  operator_release {
    uuid release_id PK
    uuid person_id FK, UK "to person"
    uuid asset_id FK, UK "to asset"
    date valid_on UK
    uuid released_by_person_id FK "to person"
    timestamptz released_at
    text reason
    ARRAY missing_requirements
    bool is_regulatory
    text released_by_name
  }
  person_document |o--o{ document_request : "fulfilled_by_document_id"
  training_course ||--o{ training_course_focus_area : "course_id"
  training_course ||--o{ training_material : "course_id"
  training_material |o--o{ training_material : "superseded_by"

Referenced from other groups:

• training_course by external_training, training, training_assignment_rule, training_session

Training sessions and rules

erDiagram
  training_session {
    uuid session_id PK
    text course_id FK "to training_course"
    text site_id FK "to site"
    date session_date
    time starts_at
    time ends_at
    text location "nullable"
    text language
    uuid trainer_person_id FK "to person"
    text trainer_name
    text trainer_job_title
    text external_instructor "nullable"
    text notes "nullable"
    text status
    uuid created_by_person_id FK "to person"
    timestamptz created_at
    timestamptz signed_at "nullable"
    uuid signed_by_person_id FK "nullable, to person"
    text signed_by_name "nullable"
    timestamptz cancelled_at "nullable"
    uuid cancelled_by_person_id FK "nullable, to person"
    text cancelled_reason "nullable"
    jsonb material_snapshot "nullable"
    text sign_in_sheet_path UK "nullable"
    text sign_in_sheet_content_type "nullable"
    int sign_in_sheet_byte_size "nullable"
    timestamptz sign_in_sheet_attached_at "nullable"
  }
  training_session_attendee {
    uuid session_id PK, FK
    uuid person_id PK, FK "to person"
    uuid training_id FK "nullable, to training"
    text attendance
    text result "nullable"
    bool walk_in
    uuid added_by_person_id FK "to person"
    timestamptz added_at
  }
  external_training {
    uuid external_training_id PK
    uuid person_id FK "to person"
    text course_id FK "nullable, to training_course"
    text title
    text provider
    text provider_address "nullable"
    date completed_on
    numeric hours "nullable"
    text certificate_path UK
    text certificate_file_name
    text certificate_content_type
    int certificate_byte_size
    uuid submitted_by_person_id FK "to person"
    text submitted_by_name
    timestamptz submitted_at
    text status
    uuid reviewer_person_id FK "nullable, to person"
    text reviewer_name "nullable"
    timestamptz reviewed_at "nullable"
    text reason "nullable"
  }
  training_assignment_rule {
    uuid rule_id PK
    text course_id FK "to training_course"
    text job_title
    ARRAY site_ids "nullable"
    int due_offset_days
    text anchor
    bool active
    uuid created_by_person_id FK "to person"
    text created_by_name
    date created_on
    timestamptz created_at
    timestamptz updated_at
    timestamptz last_applied_at "nullable"
  }
  training_event {
    uuid event_id PK
    bigint seq
    timestamptz occurred_at
    date occurred_on
    uuid actor_person_id "nullable"
    text actor_name
    text event_type
    uuid person_id "nullable"
    text site_id "nullable"
    text course_id "nullable"
    uuid training_id "nullable"
    uuid session_id "nullable"
    uuid external_training_id "nullable"
    text reason "nullable"
    jsonb detail "nullable"
  }
  training_session ||--o{ training_session_attendee : "session_id"

Referenced from other groups:

• training_session by training
• external_training by training
• training_assignment_rule by training

Observation catalog

erDiagram
  focus_area {
    text focus_area PK
    int sort_order
    bool active
  }
  observation_type {
    text observation_type PK
    int sort_order
    bool is_walkthrough
    bool active
  }
  observation_type_focus_area {
    text observation_type PK, FK
    text focus_area PK, FK
  }
  observation_behavior {
    text behavior_id PK
    text focus_area FK
    text behavior UK
    int sort_order
    bool active
  }
  observation_cadence_rule {
    text cadence_rule_id PK
    text basis
    numeric threshold
    int target_interval_days
    int sort_order
    bool active
  }
  observation_risk_rule {
    text risk_rule_id PK
    text event_type FK, UK "to telematics_event_type"
    text behavior_id FK, UK
    bool active
  }
  observation_setting {
    text setting_key PK
    numeric setting_value
    text description
  }
  focus_area ||--o{ observation_behavior : "focus_area"
  observation_behavior ||--o{ observation_risk_rule : "behavior_id"
  focus_area ||--o{ observation_type_focus_area : "focus_area"
  observation_type ||--o{ observation_type_focus_area : "observation_type"

Referenced from other groups:

• focus_area by observation, observation_item, training_course_focus_area
• observation_type by observation
• observation_behavior by observation_behavior_policy, observation_item

Policy library

erDiagram
  policy_document {
    text policy_document_id PK
    text title
    text version "nullable"
    text source_filename "nullable"
    bool active
  }
  policy_section {
    text section_id PK
    text policy_document_id FK, UK
    text section_number UK
    text parent_section_id FK "nullable"
    int depth
    text heading
    int sort_order
  }
  policy_clause {
    text clause_id PK, UK
    text section_id FK, UK
    int ordinal UK
    int depth
    text kind
    text text
  }
  observation_behavior_policy {
    text behavior_id PK, FK "to observation_behavior"
    text section_id PK, FK
    text clause_id FK "nullable"
    int rank
    text note
  }
  policy_clause ||--o{ observation_behavior_policy : "clause_id,section_id"
  policy_section ||--o{ observation_behavior_policy : "section_id"
  policy_section ||--o{ policy_clause : "section_id"
  policy_section |o--o{ policy_section : "parent_section_id"
  policy_document ||--o{ policy_section : "policy_document_id"

Route operations

erDiagram
  route {
    text route_id PK
    text site_id FK "to site"
    text route_name
    text service_type
    text scheduled_days
    int planned_stops
    text status
  }
  route_day {
    text route_id PK, FK
    text site_id FK "to site"
    date service_date PK
    uuid driver_person_id FK "to person"
    uuid asset_id FK "to asset"
    time start_time
    time end_time
    numeric on_duty_hours
    int planned_stops
    int stops_completed
    int lifts
    int miles
    int service_exceptions
    text status
    int exceptions_no_service
    int exceptions_lockout
    int exceptions_cancelled
    int exceptions_blocked
    int exceptions_other
  }
  route_stop_plan {
    text route_id PK, FK
    text customer_service_id PK, FK "to customer_service"
    text site_id FK "to site"
    int stop_sequence
  }
  asset_downtime {
    uuid downtime_id PK
    uuid asset_id FK "to asset"
    text site_id FK "to site"
    uuid inspection_id FK "to inspection"
    text work_order_type
    text reason
    date out_of_service_date
    time out_of_service_time
    date returned_to_service_date "nullable"
    numeric hours_out "nullable"
  }
  labor_month {
    text site_id PK, FK "to site"
    text lob PK
    date month PK
    int headcount
    numeric regular_hours
    numeric overtime_hours
    numeric total_hours
    numeric total_labor_cost
    numeric avg_hourly_rate
    numeric overtime_pct
  }
  route ||--o{ route_day : "route_id"
  route ||--o{ route_stop_plan : "route_id"

Referenced from other groups:

• route by incident, invoice_line, route_pnl_month

Financial layer

erDiagram
  customer {
    text customer_id PK
    text customer_name
    text customer_type
    text site_id FK "to site"
    text status
  }
  customer_site {
    text customer_site_id PK
    text customer_id FK
    text site_name
    text city "nullable"
    text state "nullable"
  }
  customer_service {
    text customer_service_id PK
    text customer_site_id FK
    text customer_id FK
    text lob
    text container_size "nullable"
    int quantity
    numeric monthly_rate_usd
    text status
  }
  invoice_line {
    text invoice_line_id PK
    text customer_service_id FK
    text customer_id FK
    text site_id FK "to site"
    text route_id FK "to route"
    date month
    date transaction_date
    text lob
    text charge_type
    text gl_description
    int quantity
    numeric amount_usd
  }
  ar_invoice {
    text ar_invoice_id PK
    text invoice_number UK
    text customer_id FK
    text site_id FK "to site"
    date month
    date invoice_date
    int payment_terms_days
    numeric invoiced_amount_usd
    date paid_date "nullable"
    numeric outstanding_balance_usd
  }
  aging_bucket {
    text bucket PK
    int min_days
    int max_days
    int sort_order
  }
  route_pnl_month {
    text route_id PK, FK "to route"
    text site_id FK "to site"
    text lob
    date month PK
    int route_days
    numeric gross_revenue_usd
    numeric disposal_tons
    numeric disposal_cost_usd
    numeric labor_cost_usd
    numeric driver_hours
    numeric fuel_cost_usd
    numeric maintenance_cost_usd
    numeric safety_claims_cost_usd
    numeric total_operating_cost_usd
    numeric net_revenue_usd
    numeric gross_profit_usd
  }
  truck_cost_month {
    uuid asset_id PK, FK "to asset"
    text site_id FK "to site"
    date month PK
    int route_days
    numeric route_hours
    int miles
    numeric fuel_cost_usd
    numeric maintenance_cost_usd
  }
  revenue_budget_daily {
    text site_id PK, FK "to site"
    text lob PK
    date date_day PK
    int month_days
    numeric monthly_budget_usd
    numeric daily_budget_usd
  }
  pnl_line {
    int line_no PK
    text line_item
    text section
  }
  customer ||--o{ ar_invoice : "customer_id"
  customer ||--o{ customer_service : "customer_id"
  customer_site ||--o{ customer_service : "customer_site_id"
  customer ||--o{ customer_site : "customer_id"
  customer ||--o{ invoice_line : "customer_id"
  customer_service ||--o{ invoice_line : "customer_service_id"

Referenced from other groups:

• customer_service by route_stop_plan

App support

erDiagram
  capture_draft {
    uuid draft_id PK
    uuid person_id FK "to person"
    text kind
    uuid subject_person_id FK "nullable, to person"
    text summary
    jsonb payload
    timestamptz started_at
    timestamptz updated_at
  }
  ask_log {
    uuid ask_id PK
    timestamptz asked_at
    uuid person_id "nullable"
    text access_level "nullable"
    text question_text
    text matched_question_id "nullable"
    jsonb slots
    numeric confidence "nullable"
    text outcome
    text reading "nullable"
    int row_count "nullable"
    int duration_ms "nullable"
    jsonb answer_digest "nullable"
  }
  benchmark {
    text metric PK
    text source PK
    int effective_year PK
    numeric low
    numeric high
    text note "nullable"
    bool is_active
    timestamptz updated_at
  }
  hub_question_answer {
    int question_number PK
    text answer
    bool resolved
    date resolved_on "nullable"
    timestamptz updated_at
  }
  hub_prospect {
    uuid prospect_id PK
    text client
    text website "nullable"
    text mark
    text ground
    text accent
    uuid added_by_person_id "nullable"
    timestamptz created_at
    jsonb personas
  }
  hub_demo_removed {
    text demo_key PK
    uuid removed_by_person_id "nullable"
    timestamptz removed_at
  }

Auth link (schema app)

erDiagram
  app_user_account {
    uuid auth_user_id PK, FK
    uuid person_id FK, UK "to person"
    text access_level
    timestamptz created_at
  }
  app_user_site {
    uuid auth_user_id PK, FK
    text site_id PK, FK "to site"
  }
  app_user_account ||--o{ app_user_site : "auth_user_id"

Sales (schema sales)

erDiagram
  sales_team_member {
    uuid user_id PK, FK
    timestamptz added_at
  }
  sales_lead {
    uuid lead_id PK
    text company
    text website "nullable"
    text contact_name "nullable"
    text contact_email "nullable"
    text contact_phone "nullable"
    text stage
    text owner "nullable"
    text notes "nullable"
    timestamptz created_at
    timestamptz updated_at
  }
  sales_lead_event {
    uuid event_id PK
    uuid lead_id FK
    date happened_on
    text kind
    text notes "nullable"
    timestamptz created_at
  }
  sales_checklist_answer {
    uuid lead_id PK, FK
    text row_key PK
    text have "nullable"
    text system "nullable"
    timestamptz updated_at
  }
  sales_lead ||--o{ sales_checklist_answer : "lead_id"
  sales_lead ||--o{ sales_lead_event : "lead_id"

Definitions

What one row of each table is. Hand-written in gen_erd.py; the run fails if a table has none.

Core platform

Table	One row is
site	One operating location: a hauling depot, the landfill or the recycling center. The main rollup dimension and the unit of row-level scope.
site_type	Hauling, Landfill, Recycling, Waste-to-Energy. Drives job mix, asset mix, incident profile and operating days.
person	One employee. Carries their site, supervisor, job title and pay; app.user_account links a login to exactly one person.
role	A role a person holds or held, primary or secondary, with dates. History, not the current title (that is person.job_title).
requirement	Something a job demands: a license class, a medical card, a certification. Grouped so a higher class satisfies a lower one.
requirement_job_title	Which job titles must hold which requirement.
qualification	A requirement a person actually holds, with its issue and expiry dates.
asset	One vehicle or piece of equipment. Belongs to a yard (site), never to a driver: trucks are a pool.
asset_type_requirement	The certifications needed to operate an asset type. No row means nothing gates that type.

Event capture

Table	One row is
incident	One safety event: injury, vehicle accident, property damage, spill, near miss. Person facts are snapshots taken at the time, not stale copies.
incident_type	Reference list of incident types.
incident_incident_type	Every type an incident counts as (an incident can be several).
contributing_factor	Reference list of contributing factors.
incident_contributing_factor	The contributing factors found for one incident, ranked.
incident_attachment	One photo or document on an incident. The bytes live in the private bucket incident-attachments; may cite the request for input it answered.
observation	One behavior-based safety observation of an employee by a supervisor.
observation_item	One checklist line on an observation: Safe or Unsafe, with the corrective action an Unsafe line requires.
telematics_event	One camera or telematics event (Lytx style) for a driver and vehicle, with a severity band.
telematics_event_type	Reference list of telematics event types.

Investigation and audit trail

Table	One row is
incident_event	Append-only audit trail of one incident's milestones. Trigger-written; loaded history is flagged reconstructed.
investigation_request	A piece of an investigation sent to somebody else: a statement, a photo, a document. The owner keeps the file.
investigation_request_event	Append-only paper trail of one request: raised, reminded, answered, reviewed, cancelled.
investigation_request_type	What an investigation owner can ask for.

Follow-through

Table	One row is
inspection	One vehicle inspection: a pre- or post-trip DVIR, PM, annual DOT or audit. The largest table.
inspection_defect	One defect found on an inspection, with whether it was corrected and whether the vehicle was safe to operate.
action	One corrective action, raised by exactly one source: an incident, observation, inspection or telematics event. May need a second person to verify it.
task	One reminder or escalation under an action, assigned to a person.
training	One training assignment for a person: a renewal of a requirement or a remedial course raised by an action.
training_observation_item	Which unsafe observation lines a remedial training answers.

Training catalog and DQ file

Table	One row is
training_course	The client's own course catalog. Seeded by app.seed_training_course(), so it survives a reload.
training_course_focus_area	Which focus areas a course answers, used to pick a remedial course.
training_material	What a course teaches: a link to the client's LMS or a file in the private bucket training-material. Replaced by a new version, never edited.
person_document	One file in a person's driver-qualification (DQ) file: what it claims, who submitted it, who verified or rejected it.
document_request	A dated request for a person to bring a document ("the new med card by this date"). Closed by a verified document.
operator_release	A supervisor letting one person file a DVIR for one vehicle on one day despite a missing cert. Records the decision; confers no authority.

Training sessions and rules

Table	One row is
training_session	One sitting of a course: date, times, site, trainer. Signed once by the trainer, then locked.
training_session_attendee	Who was booked on a session and what happened: Attended, No-show or Excused, and Passed / Not yet on an evaluated course.
external_training	A course taken somewhere else, with its certificate in training-evidence. Counts once a supervisor other than the learner approves it.
training_assignment_rule	A standing rule: everyone with this job title at these sites gets this course, due N days from hire or from the rule.
training_event	The training paper trail: assigned, attended, evaluated, signed, approved. Append-only and trigger-written.

Observation catalog

Table	One row is
focus_area	A safety focus area (backing, lifting, PPE...). Organizes behaviors and courses.
observation_type	A kind of observation, each with its own checklist.
observation_type_focus_area	Which focus areas an observation type covers.
observation_behavior	One line on the observation checklist.
observation_cadence_rule	How often an employee should be observed, by basis and threshold.
observation_risk_rule	Which telematics event types make which behaviors a priority to observe.
observation_setting	Key-value settings for the observation program.

Policy library

Table	One row is
policy_document	The client's rule book, one row per version.
policy_section	A chapter or section of the rule book, nested by parent.
policy_clause	One clause (paragraph) of a section, as extracted from the .docx.
observation_behavior_policy	Which rule-book section and clause each checklist line rests on, with the reason.

Route operations

Table	One row is
route	A serviced area worked from a hauling depot. Reference data with a readable code.
route_day	One route on one day: truck, driver, stops, lifts, miles and measured on-duty hours. One truck works one route a day.
route_stop_plan	The planned stop sequence of a route: which customer service it visits, in what order.
asset_downtime	The time a vehicle was out of service after an inspection pulled it. A NULL return date means still out.
labor_month	Labor hours and cost per site, line of business and month. Route hours are measured; the rest is assumed into Support.

Financial layer

Table	One row is
customer	One billed customer. A town is one customer for residential service.
customer_site	A service address belonging to a customer.
customer_service	One recurring service at a customer site: line of business, container, quantity and monthly rate.
invoice_line	One billed line. Route revenue is the sum of these, by construction.
ar_invoice	One receivable invoice with its terms, payment date and outstanding balance.
aging_bucket	The receivables aging bands (current, 30, 60, 90, 120+).
route_pnl_month	One route's profit and loss for one month. Gross revenue equals its invoice lines; disposal sits inside operating cost.
truck_cost_month	One truck's route days, hours, miles, fuel and maintenance cost for one month.
revenue_budget_daily	The revenue budget per site, line of business and day.
pnl_line	The fixed lines of the P&L statement and their order.

App support

Table	One row is
capture_draft	An unfinished observation or incident report, kept per person so it can be resumed. Dropped when the record is filed.
ask_log	Every question typed into /ask, answered or not. Append-only; the misses are the roadmap.
benchmark	An externally published comparison band (BLS and the like) that KPI tiles are colored against. Never a client target.
hub_question_answer	Answers to the client questions list, typed on /hub. Writable without signing in, by design.
hub_prospect	A prospect card added on /hub. VP only; excused from the demo reset.
hub_demo_removed	A built-in client demo card removed from /hub. VP only; excused from the demo reset.

Auth link (schema app)

Table	One row is
app.user_account	Maps a Supabase auth user to one person and an access level (driver, supervisor, gm, vp). Readable only by its own login.
app.user_site	The extra sites a GM login covers. Absent for every other level.

Sales (schema sales)

Table	One row is
sales.team_member	Auth users allowed into the sales schema. Demo personas are deliberately not listed.
sales.lead	A company we are talking to: contact, stage, owner, notes. Real data, real dates.
sales.lead_event	One dated touch on a lead: a call, a meeting, a demo.
sales.checklist_answer	A lead's answer to one line of the data-discovery checklist.

Access: who reads and writes each table

Read from pg_policies and from the function bodies. Row-level security is the lock; the app's canAccess() only decides which screens are offered.

• Helpers. app.site_scope() is the sites the login covers (own site; a GM's app.user_site rows; every site for a VP). app.current_person_id() is the login's person. app.is_driver(), app.access_level(), app.can_see_cost() and app.person_in_scope() read app.user_account. All are SECURITY DEFINER with a pinned search_path.
• Roles. authenticated is any signed-in login. anon is the publishable key with no session.
• Writers. Functions whose body inserts, updates or deletes the table. A definer function bypasses RLS, so its own checks are the lock. An invoker function runs under the caller's policies. A trigger fires on another write. Dynamic SQL (the demo reset) is not listed.
• No write policy and no writer means the table is loaded from CSV or seeded by a migration, and read-only to every login.

Schemas no login can enter at all (no USAGE for anon or authenticated): sales. RLS there is moot; only the SECURITY DEFINER functions listed as writers reach those tables, and each checks the caller itself.

Core platform

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
site	on	site_read	SELECT	authenticated	site_id IN app.site_scope()
site_type	on	site_type_read	SELECT	authenticated	true
person	on	person_read	SELECT	authenticated	(person_id = app.current_person_id()) OR ((NOT app.is_driver()) AND (site_id IN app.site_scope()))
		person_read_who_i_answer_to	SELECT	authenticated	person_id IN app.people_i_answer_to()
role	on	role_read	SELECT	authenticated	person_id IN ( SELECT p.person_id FROM person p)
requirement	on	requirement_read	SELECT	authenticated	true
requirement_job_title	on	requirement_job_title_read	SELECT	authenticated	true
qualification	on	qualification_read	SELECT	authenticated	(person_id = app.current_person_id()) OR ((NOT app.is_driver()) AND (person_id IN ( SELECT p.person_id FROM person p WHERE (p.site_id IN app.site_scope()))))
asset	on	asset_read	SELECT	authenticated	site_id IN app.site_scope()
asset_type_requirement	on	asset_type_requirement_read	SELECT	authenticated	true

Table	Written by
qualification	app.issue_qualification_from_training (definer), verify_person_document (definer)
asset	app.advance_asset_reading (definer), app.raise_dvir_repair (definer), app.release_hold_on_repair (trigger)

Event capture

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
incident	on	incident_read	SELECT	authenticated	(site_id IN app.site_scope()) AND ((NOT app.is_driver()) OR (person_id = app.current_person_id()) OR (reported_by_person_id = app.current_person_id()))
		incident_insert	INSERT	authenticated		(site_id IN app.site_scope()) AND (reported_by_person_id = app.current_person_id())
		incident_update	UPDATE	authenticated	(site_id IN app.site_scope()) AND (NOT app.is_driver())	(site_id IN app.site_scope()) AND (NOT app.is_driver()) AND ((signed_off_by_person_id IS NULL) OR ((signed_off_by_person_id = app.current_person_id()) AND (signed_off_on = app.today())))
incident_type	on	incident_type_read	SELECT	authenticated	true
incident_incident_type	on	iit_read	SELECT	authenticated	incident_id IN ( SELECT i.incident_id FROM incident i)
		iit_insert	INSERT	authenticated		(incident_id IN ( SELECT i.incident_id FROM incident i)) AND ((NOT app.is_driver()) OR (incident_id IN ( SELECT i.incident_id FROM incident i WHERE ((i.reported_by_person_id = app.current_person_id()) AND (i.status = 'New')))))
contributing_factor	on	contributing_factor_read	SELECT	authenticated	true
incident_contributing_factor	on	icf_read	SELECT	authenticated	incident_id IN ( SELECT i.incident_id FROM incident i)
		icf_insert	INSERT	authenticated		(incident_id IN ( SELECT i.incident_id FROM incident i)) AND ((NOT app.is_driver()) OR (incident_id IN ( SELECT i.incident_id FROM incident i WHERE ((i.reported_by_person_id = app.current_person_id()) AND (i.status = 'New')))))
		icf_delete	DELETE	authenticated	(NOT app.is_driver()) AND (incident_id IN ( SELECT i.incident_id FROM incident i))
incident_attachment	on	incident_attachment_read	SELECT	authenticated	((incident_id IN ( SELECT i.incident_id FROM incident i)) AND ((NOT app.is_driver()) OR (uploaded_by_person_id = app.current_person_id()))) OR ((incident_id IN app.request_incident_ids()) AND (uploaded_by_person_id = app.current_person_id()))
		incident_attachment_insert	INSERT	authenticated		(uploaded_by_person_id = app.current_person_id()) AND ((incident_id IN ( SELECT i.incident_id FROM incident i)) OR (incident_id IN app.request_incident_ids()))
		incident_attachment_update	UPDATE	authenticated	(uploaded_by_person_id = app.current_person_id()) OR ((NOT app.is_driver()) AND (incident_id IN ( SELECT i.incident_id FROM incident i)))	(uploaded_by_person_id = app.current_person_id()) OR ((NOT app.is_driver()) AND (incident_id IN ( SELECT i.incident_id FROM incident i)))
		incident_attachment_delete	DELETE	authenticated	(uploaded_by_person_id = app.current_person_id()) OR ((NOT app.is_driver()) AND (incident_id IN ( SELECT i.incident_id FROM incident i)))
observation	on	observation_read	SELECT	authenticated	(site_id IN app.site_scope()) AND ((NOT app.is_driver()) OR (employee_person_id = app.current_person_id()) OR (supervisor_person_id = app.current_person_id()))
		observation_insert	INSERT	authenticated		(site_id IN app.site_scope()) AND (supervisor_person_id = app.current_person_id()) AND (NOT app.is_driver())
observation_item	on	observation_item_read	SELECT	authenticated	observation_id IN ( SELECT o.observation_id FROM observation o)
		observation_item_insert	INSERT	authenticated		observation_id IN ( SELECT o.observation_id FROM observation o WHERE (o.supervisor_person_id = app.current_person_id()))
telematics_event	on	telematics_read	SELECT	authenticated	(site_id IN app.site_scope()) AND ((NOT app.is_driver()) OR (driver_person_id = app.current_person_id()))
telematics_event_type	on	telematics_event_type_read	SELECT	authenticated	true

Table	Written by
incident	capture_incident (invoker), save_investigation (invoker)
incident_incident_type	capture_incident (invoker)
incident_contributing_factor	capture_incident (invoker), save_investigation (invoker)
observation	capture_observation (invoker)
observation_item	capture_observation (invoker)

Investigation and audit trail

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
incident_event	on	incident_event_read	SELECT	authenticated	incident_id IN ( SELECT i.incident_id FROM incident i)
investigation_request	on	investigation_request_read	SELECT	authenticated	(assigned_to_person_id = app.current_person_id()) OR ((NOT app.is_driver()) AND (incident_id IN ( SELECT i.incident_id FROM incident i)))
investigation_request_event	on	investigation_request_event_read	SELECT	authenticated	request_id IN ( SELECT r.request_id FROM investigation_request r)
investigation_request_type	on	investigation_request_type_read	SELECT	authenticated	true

Table	Written by
incident_event	app.backfill_incident_events (invoker), app.log_incident_attachment_event (trigger), app.log_incident_event (trigger)
investigation_request	amend_investigation_input (definer), cancel_investigation_input (definer), request_investigation_input (definer), review_investigation_input (definer), submit_investigation_input (definer)
investigation_request_event	app.log_request_event (definer)

Follow-through

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
inspection	on	inspection_read	SELECT	authenticated	(site_id IN app.site_scope()) AND ((NOT app.is_driver()) OR (inspector_person_id = app.current_person_id()))
		inspection_insert	INSERT	authenticated		(site_id IN app.site_scope()) AND (inspector_person_id = app.current_person_id()) AND (app.person_could_operate_on(inspector_person_id, ( SELECT a.asset_type FROM asset a WHERE (a.asset_id = inspection.asset_id)), inspection_date) OR app.person_released_on(inspector_person_id, asset_id, inspection_date))
inspection_defect	on	defect_read	SELECT	authenticated	inspection_id IN ( SELECT i.inspection_id FROM inspection i)
		defect_insert	INSERT	authenticated		inspection_id IN ( SELECT i.inspection_id FROM inspection i WHERE ((i.inspector_person_id = app.current_person_id()) AND (i.xmin = (pg_current_xact_id())::xid)))
action	on	action_read	SELECT	authenticated	(site_id IN app.site_scope()) AND ((NOT app.is_driver()) OR (assignee_person_id = app.current_person_id()))
		action_read_to_verify	SELECT	authenticated	verifier_person_id = app.current_person_id()
		action_insert	INSERT	authenticated		(site_id IN app.site_scope()) AND (observation_id IN ( SELECT o.observation_id FROM observation o WHERE (o.supervisor_person_id = app.current_person_id()))) AND (status = 'Open') AND (completed_date IS NULL) AND (verified_by_person_id IS NULL) AND (verifier_person_id IS NULL) AND (work_done_on IS NULL) AND (work_done_by_person_id IS NULL)
		action_insert_incident	INSERT	authenticated		(site_id IN app.site_scope()) AND (NOT app.is_driver()) AND (incident_id IN ( SELECT i.incident_id FROM incident i)) AND (status = 'Open') AND (completed_date IS NULL) AND (verified_by_person_id IS NULL) AND (verifier_person_id IS NULL) AND (work_done_on IS NULL) AND (work_done_by_person_id IS NULL)
task	on	task_read	SELECT	authenticated	(site_id IN app.site_scope()) AND ((NOT app.is_driver()) OR (assignee_person_id = app.current_person_id()))
		task_insert	INSERT	authenticated		(site_id IN app.site_scope()) AND (NOT app.is_driver()) AND (action_id IN ( SELECT a.action_id FROM action a)) AND (status = 'Open') AND (completed_date IS NULL)
training	on	training_read	SELECT	authenticated	(person_id = app.current_person_id()) OR ((NOT app.is_driver()) AND (site_id IN app.site_scope()))
		training_insert	INSERT	authenticated		(site_id IN app.site_scope()) AND (NOT app.is_driver()) AND (source_action_id IN ( SELECT a.action_id FROM action a)) AND app.training_is_open(status) AND (assignment_source = 'remedial') AND (result IS NULL) AND (evaluator_person_id IS NULL) AND (session_id IS NULL) AND (external_training_id IS NULL) AND (rule_id IS NULL) AND (signed_at IS NULL)
training_observation_item	on	training_observation_item_read	SELECT	authenticated	training_id IN ( SELECT t.training_id FROM training t)
		training_observation_item_insert	INSERT	authenticated		observation_item_id IN ( SELECT oi.observation_item_id FROM (observation_item oi JOIN observation o ON ((o.observation_id = oi.observation_id))) WHERE (o.supervisor_person_id = app.current_person_id()))

Table	Written by
inspection	capture_dvir (invoker)
inspection_defect	capture_dvir (invoker)
action	app.raise_dvir_repair (definer), complete_action (definer), dispatch_action (definer), mark_action_work_done (definer), verify_action (definer), capture_observation (invoker), save_investigation (invoker), app.set_action_verifier (trigger)
task	app.raise_dvir_repair (definer), complete_action (definer), dispatch_action (definer), verify_action (definer), capture_observation (invoker), save_investigation (invoker)
training	acknowledge_training (definer), app.open_training_for (definer), review_external_training (definer), sign_training_session (definer), verify_training (definer), capture_observation (invoker)
training_observation_item	capture_observation (invoker)

Training catalog and DQ file

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
training_course	on	training_course_read	SELECT	authenticated	true
training_course_focus_area	on	training_course_focus_area_read	SELECT	authenticated	true
training_material	on	training_material_read	SELECT	authenticated	true
person_document	on	person_document_read	SELECT	authenticated	app.person_in_scope(person_id)
		person_document_read_to_verify	SELECT	authenticated	verifier_person_id = app.current_person_id()
document_request	on	document_request_read	SELECT	authenticated	app.person_in_scope(person_id) OR (requested_by_person_id = app.current_person_id())
operator_release	on	operator_release_read	SELECT	authenticated	(person_id = app.current_person_id()) OR ((NOT app.is_driver()) AND (person_id IN ( SELECT p.person_id FROM person p WHERE (p.site_id IN app.site_scope()))))

Table	Written by
training_course	retire_training_course (definer), upsert_training_course (definer), app.seed_training_course (invoker)
training_course_focus_area	upsert_training_course (definer), app.seed_training_course (invoker)
training_material	add_training_material (definer), remove_training_material (definer), replace_training_material (definer)
person_document	app.issue_qualification_from_training (definer), reject_person_document (definer), submit_person_document (definer), verify_person_document (definer)
document_request	app.issue_qualification_from_training (definer), cancel_document_request (definer), reject_person_document (definer), request_document (definer), verify_person_document (definer)
operator_release	release_operator (definer)

Training sessions and rules

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
training_session	on	training_session_read	SELECT	authenticated	app.can_read_session(session_id)
training_session_attendee	on	training_session_attendee_read	SELECT	authenticated	(person_id = app.current_person_id()) OR ((NOT app.is_driver()) AND app.can_read_session(session_id))
external_training	on	external_training_read	SELECT	authenticated	app.person_in_scope(person_id) OR (submitted_by_person_id = app.current_person_id())
training_assignment_rule	on	training_assignment_rule_read	SELECT	authenticated	(NOT app.is_driver()) AND ( SELECT app.is_supervisory(app.current_person_id()) AS is_supervisory)
training_event	on	training_event_read	SELECT	authenticated	(person_id = app.current_person_id()) OR ((NOT app.is_driver()) AND ( SELECT app.is_supervisory(app.current_person_id()) AS is_supervisory) AND (site_id IN app.site_scope()))

Table	Written by
training_session	attach_sign_in_sheet (definer), cancel_training_session (definer), schedule_training_session (definer), sign_training_session (definer)
training_session_attendee	set_session_roster (definer), sign_training_session (definer)
external_training	review_external_training (definer), submit_external_training (definer)
training_assignment_rule	app.apply_training_rule (definer), save_training_rule (definer)
training_event	app.log_training_event (definer)

Observation catalog

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
focus_area	on	focus_area_read	SELECT	authenticated	true
observation_type	on	observation_type_read	SELECT	authenticated	true
observation_type_focus_area	on	otfa_read	SELECT	authenticated	true
observation_behavior	on	observation_behavior_read	SELECT	authenticated	true
observation_cadence_rule	on	observation_cadence_rule_read	SELECT	authenticated	true
observation_risk_rule	on	observation_risk_rule_read	SELECT	authenticated	true
observation_setting	on	observation_setting_read	SELECT	authenticated	true

Policy library

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
policy_document	on	policy_document_read	SELECT	authenticated	true
policy_section	on	policy_section_read	SELECT	authenticated	true
policy_clause	on	policy_clause_read	SELECT	authenticated	true
observation_behavior_policy	on	observation_behavior_policy_read	SELECT	authenticated	true

Route operations

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
route	on	route_read	SELECT	authenticated	site_id IN app.site_scope()
route_day	on	route_day_read	SELECT	authenticated	(site_id IN app.site_scope()) AND ((NOT app.is_driver()) OR (driver_person_id = app.current_person_id()))
route_stop_plan	on	route_stop_plan_read	SELECT	authenticated	site_id IN app.site_scope()
asset_downtime	on	asset_downtime_read	SELECT	authenticated	site_id IN app.site_scope()
labor_month	on	labor_month_read	SELECT	authenticated	(site_id IN app.site_scope()) AND app.can_see_cost()

Table	Written by
asset_downtime	app.raise_dvir_repair (definer), app.release_hold_on_repair (trigger)

Financial layer

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
customer	on	customer_read	SELECT	authenticated	(site_id IN app.site_scope()) AND (app.access_level() = ANY (ARRAY['gm', 'vp'])) AND app.can_see_cost()
customer_site	on	customer_site_read	SELECT	authenticated	(customer_id IN ( SELECT c.customer_id FROM customer c WHERE (c.site_id IN app.site_scope()))) AND (app.access_level() = ANY (ARRAY['gm', 'vp'])) AND app.can_see_cost()
customer_service	on	customer_service_read	SELECT	authenticated	(customer_id IN ( SELECT c.customer_id FROM customer c WHERE (c.site_id IN app.site_scope()))) AND (app.access_level() = ANY (ARRAY['gm', 'vp'])) AND app.can_see_cost()
invoice_line	on	invoice_line_read	SELECT	authenticated	(site_id IN app.site_scope()) AND (app.access_level() = ANY (ARRAY['gm', 'vp'])) AND app.can_see_cost()
ar_invoice	on	ar_invoice_read	SELECT	authenticated	(site_id IN app.site_scope()) AND (app.access_level() = ANY (ARRAY['gm', 'vp'])) AND app.can_see_cost()
aging_bucket	on	aging_bucket_read	SELECT	authenticated	true
route_pnl_month	on	route_pnl_month_read	SELECT	authenticated	(site_id IN app.site_scope()) AND (app.access_level() = ANY (ARRAY['gm', 'vp'])) AND app.can_see_cost()
truck_cost_month	on	truck_cost_month_read	SELECT	authenticated	(site_id IN app.site_scope()) AND (app.access_level() = ANY (ARRAY['gm', 'vp'])) AND app.can_see_cost()
revenue_budget_daily	on	revenue_budget_daily_read	SELECT	authenticated	(site_id IN app.site_scope()) AND (app.access_level() = ANY (ARRAY['gm', 'vp'])) AND app.can_see_cost()
pnl_line	on	pnl_line_read	SELECT	authenticated	true

App support

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
capture_draft	on	capture_draft_own	ALL	authenticated	person_id = app.current_person_id()	person_id = app.current_person_id()
ask_log	on	ask_log_read	SELECT	authenticated	app.access_level() = 'vp'
		ask_log_insert	INSERT	authenticated		person_id = app.current_person_id()
benchmark	on	benchmark_read	SELECT	authenticated	true
		benchmark_write	INSERT	authenticated		app.access_level() = 'vp'
		benchmark_update	UPDATE	authenticated	app.access_level() = 'vp'	app.access_level() = 'vp'
		benchmark_delete	DELETE	authenticated	app.access_level() = 'vp'
hub_question_answer	on	hub_question_answer_read	SELECT	anon, authenticated	true
		hub_question_answer_insert	INSERT	anon, authenticated		true
		hub_question_answer_update	UPDATE	anon, authenticated	true	true
hub_prospect	on	hub_prospect_read	SELECT	authenticated	app.access_level() = 'vp'
		hub_prospect_insert	INSERT	authenticated		app.access_level() = 'vp'
		hub_prospect_update	UPDATE	authenticated	app.access_level() = 'vp'	app.access_level() = 'vp'
		hub_prospect_delete	DELETE	authenticated	app.access_level() = 'vp'
hub_demo_removed	on	hub_demo_removed_read	SELECT	authenticated	app.access_level() = 'vp'
		hub_demo_removed_insert	INSERT	authenticated		app.access_level() = 'vp'
		hub_demo_removed_delete	DELETE	authenticated	app.access_level() = 'vp'

Table	Written by
capture_draft	app.drop_filed_draft (trigger)

Auth link (schema app)

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
app.user_account	on	user_account_self	SELECT	authenticated	auth_user_id = auth.uid()
app.user_site	on	user_site_self	SELECT	authenticated	auth_user_id = auth.uid()

Sales (schema sales)

Table	RLS	Policy	Command	Roles	Row filter (USING)	New-row check (WITH CHECK)
sales.team_member	off	(none)
sales.lead	off	(none)
sales.lead_event	off	(none)
sales.checklist_answer	off	(none)

Table	Written by
sales.lead	sales_add_event (definer), sales_delete_lead (definer), sales_save_answer (definer), sales_save_lead (definer)
sales.lead_event	sales_add_event (definer), sales_delete_event (definer)
sales.checklist_answer	sales_save_answer (definer)

Views

Every view runs as the caller (security_invoker) unless marked otherwise, so it returns only rows the caller's own policies allow.

View	Runs as	Columns	Reads
cofi.fact_fleet	caller	12	asset, asset_downtime, site
incident_changelog	caller	9	incident_event, investigation_request, investigation_request_event, person
incident_cost	caller	8	incident

Constraints: value ranges and cross-column rules

Every CHECK in the database, one row each. Enumerations are listed in full; columns with no CHECK accept any value of their type.

Core platform

Table	Constraint	Rule
site	site_headcount_check	headcount >= 0
site	site_operating_days_check	operating_days in {Mon-Sat, Mon-Sun}
site	site_site_type_check	site_type in {Hauling, Waste-to-Energy, Landfill, Recycling}
person	person_employee_type_check	employee_type in {Employee, Contractor, Temp}
person	person_not_own_supervisor	(supervisor_person_id IS NULL) OR (supervisor_person_id <> person_id)
person	person_pay_group_known	(pay_group IS NULL) OR (pay_group = ANY (ARRAY['Hourly', 'Salaried']))
person	person_pay_rate_plausible	(pay_rate_usd IS NULL) OR ((pay_rate_usd >= (15)::numeric) AND (pay_rate_usd <= (200)::numeric))
person	person_shift_check	shift in {Day, Night, Swing}
person	person_status_check	status in {Active, On Leave, Terminated}
person	person_terminated_after_hire	(termination_date IS NULL) OR (termination_date >= hire_date)
role	role_ends_after_start	(end_date IS NULL) OR (end_date >= start_date)
requirement	requirement_group_rank_together	(requirement_group IS NULL) = (group_rank IS NULL)
qualification	qualification_expires_after_issue	expiry_date >= issued_date
qualification	qualification_status_check	status in {Active, Expiring Soon, Expired}
asset	asset_asset_class_check	asset_class in {On-Road, Off-Road}
asset	asset_class_fields	((asset_class = 'On-Road') AND (vin IS NOT NULL) AND (odometer_miles IS NOT NULL) AND (engine_hours IS NULL)) OR ((asset_class = 'Off-Road') AND (vin IS NULL) AND (odometer_miles IS NULL) AND (engine_hours IS NOT NULL))

Event capture

Table	Constraint	Rule
incident	incident_actual_cost_needs_closed	(actual_cost_usd IS NULL) OR (status = 'Closed')
incident	incident_actual_cost_positive	(actual_cost_usd IS NULL) OR (actual_cost_usd > (0)::numeric)
incident	incident_dart_matches	dart_case = (COALESCE(injury_classification, '') = ANY (ARRAY['Restricted Work', 'Lost Time']))
incident	incident_injury_classification_check	injury_classification in {No Treatment, First Aid, Medical Treatment, Restricted Work, Lost Time}
incident	incident_intake_facts_need_injury	((treatment_sought IS NULL) AND (returned_to_work_same_shift IS NULL)) OR (injury_classification IS NOT NULL) OR (body_part IS NOT NULL) OR (injury_illness_type IS NOT NULL)
incident	incident_investigation_progress	CASE status WHEN 'New' THEN true WHEN 'Open' THEN (investigation_owner_person_id IS NOT NULL) ELSE ((investigation_owner_person_id IS NOT NULL) AND (root_cause IS NOT NULL) AND (root_cause_category IS NOT NULL) AND (estimated_cost_usd IS NOT NULL) AND (signed_off_by_person_id IS NOT NULL) AND (signed_off_on IS NOT NULL)) END
incident	incident_lob_known	(lob IS NULL) OR (lob = ANY (ARRAY['Residential', 'Commercial', 'Roll-Off', 'Transfer', 'Support']))
incident	incident_lob_needs_a_route	(lob IS NULL) OR (lob = 'Support') OR (route_id IS NOT NULL)
incident	incident_lost_days_only_lost_time	(lost_days IS NULL) OR (injury_classification = 'Lost Time')
incident	incident_lti_matches	is_lti = (COALESCE(lost_days, 0) > 0)
incident	incident_recordable_matches	recordable = (COALESCE(injury_classification, '') = ANY (ARRAY['Medical Treatment', 'Restricted Work', 'Lost Time']))
incident	incident_reported_on_or_after	date_reported >= incident_date
incident	incident_restricted_days_only_restricted	(restricted_days IS NULL) OR (injury_classification = 'Restricted Work')
incident	incident_severity_check	severity in {Minor, Moderate, Serious, Severe}
incident	incident_signoff_after_report	(signed_off_on IS NULL) OR (signed_off_on >= date_reported)
incident	incident_signoff_needs_closed	(status = 'Closed') OR ((signed_off_by_person_id IS NULL) AND (signed_off_on IS NULL))
incident	incident_status_check	status in {New, Open, Closed}
incident_contributing_factor	incident_contributing_factor_rank_check	(rank >= 1) AND (rank <= 4)
incident_attachment	incident_attachment_caption_length	(caption IS NULL) OR (length(caption) <= 500)
incident_attachment	incident_attachment_file_name_length	(length(file_name) >= 1) AND (length(file_name) <= 255)
incident_attachment	incident_attachment_kind	kind in {Photo, Document}
incident_attachment	incident_attachment_kind_matches_type	(kind = 'Photo') = (content_type ~~ 'image/%')
incident_attachment	incident_attachment_path_under_incident	app.attachment_incident_id(storage_path) = incident_id
incident_attachment	incident_attachment_size	(byte_size > 0) AND (byte_size <= 26214400)
observation	observation_at_risk_behaviors_count_check	at_risk_behaviors_count >= 0
observation	observation_observer_not_subject	employee_person_id <> supervisor_person_id
observation	observation_result_check	result in {Safe, At-Risk, Mixed}
observation	observation_safe_behaviors_count_check	safe_behaviors_count >= 0
observation	observation_status_check	status in {Open, Closed, Overdue}
observation_item	observation_item_other_or_catalog	(behavior_id IS NULL) = is_other
observation_item	observation_item_reason_only_when_required	(risk_reason IS NULL) OR required
observation_item	observation_item_required_skip_has_reason	(NOT (required AND (result = 'Not Observed'))) OR ((notes IS NOT NULL) AND (length(btrim(notes)) >= 3))
observation_item	observation_item_result_check	result in {Safe, Coaching Needed, Unsafe, Not Observed}
observation_item	observation_item_unsafe_has_corrective_action	(result <> 'Unsafe') OR ((corrective_action IS NOT NULL) AND (length(btrim(corrective_action)) >= 3))
observation_item	observation_item_unsafe_has_notes	(result <> 'Unsafe') OR ((notes IS NOT NULL) AND (length(btrim(notes)) >= 3))
telematics_event	telematics_event_date_matches	event_date = (event_datetime)::date
telematics_event	telematics_event_review_status_check	review_status in {Pending Review, Reviewed, Dismissed}
telematics_event	telematics_event_severity_band_check	severity_band in {Low, Medium, High}
telematics_event	telematics_event_severity_score_check	(severity_score >= 1) AND (severity_score <= 10)

Investigation and audit trail

Table	Constraint	Rule
incident_event	incident_event_event_type_check	event_type in {Reported, Owner assigned, Owner changed, Status changed, Findings updated, Signed off, Reopened, Attachment added, Attachment removed}
investigation_request	investigation_request_closed_when_terminal	(status = ANY (ARRAY['Accepted', 'Cancelled'])) = (closed_on IS NOT NULL)
investigation_request	investigation_request_due_after_requested	due_date >= requested_on
investigation_request	investigation_request_not_self	assigned_to_person_id <> requested_by_person_id
investigation_request	investigation_request_prompt_length	(length(btrim(prompt)) >= 10) AND (length(btrim(prompt)) <= 2000)
investigation_request	investigation_request_status_check	status in {Requested, Submitted, Accepted, Cancelled}
investigation_request_event	investigation_request_event_event_type_check	event_type in {Requested, Reminded, Reassigned, Submitted, Returned, Accepted, Cancelled, Due date changed}
investigation_request_event	investigation_request_event_seq_check	seq >= 1

Follow-through

Table	Constraint	Rule
inspection	inspection_defect_count_check	defect_count >= 0
inspection	inspection_inspection_type_check	inspection_type in {DVIR - Pre-Trip, DVIR - Post-Trip, Preventive Maintenance Service, Annual DOT Inspection, Asset Compliance Audit}
inspection	inspection_result_check	result in {Satisfactory, Defects Found}
inspection	inspection_result_matches_defects	((result = 'Satisfactory') AND (defect_count = 0)) OR ((result = 'Defects Found') AND (defect_count > 0))
inspection	inspection_status_check	status in {Closed, Open Defect}
inspection_defect	inspection_defect_component_category_check	component_category in {Power Unit, Safety Equipment, Trailer}
inspection_defect	inspection_defect_correction_consistent	(corrected AND (corrected_by_person_id IS NOT NULL) AND (corrected_date IS NOT NULL)) OR ((NOT corrected) AND (corrected_by_person_id IS NULL) AND (corrected_date IS NULL))
inspection_defect	inspection_defect_defect_severity_check	defect_severity in {Minor, Major, Out of Service}
action	action_due_after_created	due_date >= created_date
action	action_exactly_one_source	(((((incident_id IS NOT NULL)) + ((observation_id IS NOT NULL))) + ((inspection_id IS NOT NULL))) + ((telematics_event_id IS NOT NULL))) = 1
action	action_priority_check	priority in {Low, Medium, High, Critical}
action	action_source_type_check	source_type in {Incident, Observation, Inspection, Telematics Event}
action	action_source_type_matches	((source_type = 'Incident') AND (incident_id IS NOT NULL)) OR ((source_type = 'Observation') AND (observation_id IS NOT NULL)) OR ((source_type = 'Inspection') AND (inspection_id IS NOT NULL)) OR ((source_type = 'Telematics Event') AND (telematics_event_id IS NOT NULL))
action	action_status_check	status in {Open, In Progress, Completed, Overdue, Cancelled}
action	action_verified_when_completed	NOT ((status = 'Completed') AND verification_required AND (verified_by_person_id IS NULL))
action	action_verifier_is_not_the_assignee	(verifier_person_id IS NULL) OR (verifier_person_id <> assignee_person_id)
action	action_work_done_names_a_verifier	(work_done_on IS NULL) OR (verifier_person_id IS NOT NULL)
task	task_escalation_level_check	escalation_level >= 0
task	task_reminder_count_check	reminder_count >= 0
task	task_status_check	status in {Open, In Progress, Completed, Overdue, Cancelled}
training	training_acknowledged_after_assigned	(acknowledged_on IS NULL) OR (acknowledged_on >= assigned_date)
training	training_assignment_source_known	assignment_source in {manual, renewal, remedial, rule, session, external, history}
training	training_attended_names_session	(completion_method = 'Attended') = (session_id IS NOT NULL)
training	training_completion_consistent	((status = 'Completed') AND (completion_date IS NOT NULL) AND ((expiry_date IS NOT NULL) OR (requirement_id IS NULL)) AND ((score IS NOT NULL) OR (completion_method = ANY (ARRAY['Acknowledged', 'Attended', 'External'])))) OR ((status <> 'Completed') AND (completion_date IS NULL) AND (expiry_date IS NULL) AND (score IS NULL))
training	training_completion_method_known	(completion_method IS NULL) OR (completion_method = ANY (ARRAY['Acknowledged', 'Delivered', 'Attended', 'External']))
training	training_external_names_record	(completion_method = 'External') = (external_training_id IS NOT NULL)
training	training_has_course_or_requirement	(requirement_id IS NOT NULL) OR (course_id IS NOT NULL)
training	training_not_own_evaluator	(evaluator_person_id IS NULL) OR (evaluator_person_id <> person_id)
training	training_not_own_trainer	(trainer_person_id IS NULL) OR (trainer_person_id <> person_id)
training	training_not_yet_is_open	NOT ((status = 'Completed') AND (result = 'Not yet'))
training	training_remedial_has_source	is_remedial = (source_action_id IS NOT NULL)
training	training_result_has_evaluator	(result IS NULL) OR ((evaluator_person_id IS NOT NULL) AND (evaluated_on IS NOT NULL))
training	training_result_known	(result IS NULL) OR (result = ANY (ARRAY['Passed', 'Not yet']))
training	training_score_check	(score >= 0) AND (score <= 100)
training	training_status_check	status in {Assigned, In Progress, Completed, Overdue, Cancelled}

Training catalog and DQ file

Table	Constraint	Rule
training_course	training_course_delivery_known	delivery in {Instructor-Led, eLearning (LMS), Toolbox Talk, On-the-Job (OJT)}
training_course	training_course_name_length	(length(btrim(course_name)) >= 1) AND (length(btrim(course_name)) <= 200)
training_course	training_course_renewal_window	(renewal_window_days >= 0) AND (renewal_window_days <= 365)
training_material	training_material_doc_has_meta	(storage_path IS NULL) OR ((content_type IS NOT NULL) AND (byte_size IS NOT NULL) AND (byte_size > 0))
training_material	training_material_kind_check	kind in {Document, Link}
training_material	training_material_link_is_http	(url IS NULL) OR (url ~* '^https?://')
training_material	training_material_one_source	(storage_path IS NULL) <> (url IS NULL)
training_material	training_material_replaced_is_dated	(superseded_by IS NULL) OR (superseded_on IS NOT NULL)
training_material	training_material_title_length	(length(btrim(title)) >= 1) AND (length(btrim(title)) <= 200)
training_material	training_material_version_positive	version >= 1
person_document	person_document_claimed_dates_ordered	(claimed_expiry_date IS NULL) OR (claimed_issued_date IS NULL) OR (claimed_expiry_date >= claimed_issued_date)
person_document	person_document_file_name_length	(length(file_name) >= 1) AND (length(file_name) <= 255)
person_document	person_document_one_subject	(qualification_id IS NULL) OR (training_id IS NULL)
person_document	person_document_path_under_person	app.document_person_id(storage_path) = person_id
person_document	person_document_rejected_has_reason	(status = 'Rejected') = (rejection_reason IS NOT NULL)
person_document	person_document_rejection_reason_length	(rejection_reason IS NULL) OR ((length(btrim(rejection_reason)) >= 1) AND (length(btrim(rejection_reason)) <= 500))
person_document	person_document_signer_is_not_subject	(verified_by_person_id IS NULL) OR (verified_by_person_id <> person_id)
person_document	person_document_size	(byte_size > 0) AND (byte_size <= 26214400)
person_document	person_document_status	status in {Pending, Verified, Rejected, Superseded}
person_document	person_document_superseded_dated	(status = 'Superseded') = (superseded_on IS NOT NULL)
person_document	person_document_verified_has_subject	(status <> ALL (ARRAY['Verified', 'Superseded'])) OR (qualification_id IS NOT NULL) OR (training_id IS NOT NULL)
person_document	person_document_verified_is_signed	(status = ANY (ARRAY['Verified', 'Superseded'])) = ((verified_by_person_id IS NOT NULL) AND (verified_on IS NOT NULL))
person_document	person_document_verifier_is_not_subject	(verifier_person_id IS NULL) OR (verifier_person_id <> person_id)
document_request	document_request_cancelled_has_reason	(status = 'Cancelled') = ((cancelled_reason IS NOT NULL) AND (cancelled_on IS NOT NULL))
document_request	document_request_cancelled_reason_length	(cancelled_reason IS NULL) OR ((length(btrim(cancelled_reason)) >= 1) AND (length(btrim(cancelled_reason)) <= 500))
document_request	document_request_fulfilled_names_document	(status = 'Fulfilled') = ((fulfilled_by_document_id IS NOT NULL) AND (fulfilled_on IS NOT NULL))
document_request	document_request_note_length	(note IS NULL) OR ((length(btrim(note)) >= 1) AND (length(btrim(note)) <= 500))
document_request	document_request_reason	reason in {Renewal, Rejected, Missing, Evaluation}
document_request	document_request_status	status in {Open, Fulfilled, Cancelled}
operator_release	operator_release_has_gap	cardinality(missing_requirements) > 0
operator_release	operator_release_not_self	released_by_person_id <> person_id
operator_release	operator_release_reason_substantive	length(btrim(reason)) >= 20

Training sessions and rules

Table	Constraint	Rule
training_session	training_session_cancelled	(status = 'Cancelled') = ((cancelled_at IS NOT NULL) AND (cancelled_reason IS NOT NULL))
training_session	training_session_cancelled_reason_length	(cancelled_reason IS NULL) OR ((length(btrim(cancelled_reason)) >= 1) AND (length(btrim(cancelled_reason)) <= 500))
training_session	training_session_sheet_size	(sign_in_sheet_byte_size IS NULL) OR ((sign_in_sheet_byte_size >= 1) AND (sign_in_sheet_byte_size <= 26214400))
training_session	training_session_sheet_together	((sign_in_sheet_path IS NULL) = (sign_in_sheet_content_type IS NULL)) AND ((sign_in_sheet_path IS NULL) = (sign_in_sheet_byte_size IS NULL)) AND ((sign_in_sheet_path IS NULL) = (sign_in_sheet_attached_at IS NULL))
training_session	training_session_sheet_under_session	`(sign_in_sheet_path IS NULL) OR (sign_in_sheet_path ~~ ((session_id)
training_session	training_session_signed	(status = 'Signed') = ((signed_at IS NOT NULL) AND (signed_by_person_id IS NOT NULL))
training_session	training_session_status	status in {Scheduled, Signed, Cancelled}
training_session	training_session_text_lengths	((location IS NULL) OR (length(location) <= 200)) AND ((external_instructor IS NULL) OR (length(external_instructor) <= 200)) AND ((notes IS NULL) OR (length(notes) <= 4000)) AND ((length(btrim(language)) >= 1) AND (length(btrim(language)) <= 50))
training_session	training_session_times	ends_at > starts_at
training_session_attendee	training_attendee_attendance	attendance in {Booked, Attended, No-show, Excused}
training_session_attendee	training_attendee_result	(result IS NULL) OR (result = ANY (ARRAY['Passed', 'Not yet']))
training_session_attendee	training_attendee_result_needs_attendance	(result IS NULL) OR (attendance = 'Attended')
external_training	external_training_hours	(hours IS NULL) OR (hours > (0)::numeric)
external_training	external_training_not_own_reviewer	(reviewer_person_id IS NULL) OR (reviewer_person_id <> person_id)
external_training	external_training_path_under_person	`certificate_path ~~ ((person_id)
external_training	external_training_rejected_has_reason	(status <> 'Rejected') OR (reason IS NOT NULL)
external_training	external_training_reviewed	(status <> 'Pending') = ((reviewer_person_id IS NOT NULL) AND (reviewed_at IS NOT NULL))
external_training	external_training_size	(certificate_byte_size >= 1) AND (certificate_byte_size <= 26214400)
external_training	external_training_status	status in {Pending, Approved, Rejected}
external_training	external_training_text_lengths	((length(btrim(title)) >= 1) AND (length(btrim(title)) <= 200)) AND ((length(btrim(provider)) >= 1) AND (length(btrim(provider)) <= 200)) AND ((provider_address IS NULL) OR (length(provider_address) <= 500)) AND ((reason IS NULL) OR ((length(btrim(reason)) >= 1) AND (length(btrim(reason)) <= 500))) AND ((length(certificate_file_name) >= 1) AND (length(certificate_file_name) <= 255))
training_assignment_rule	training_rule_anchor	anchor in {hire_date, rule_created}
training_assignment_rule	training_rule_job_title_length	(length(btrim(job_title)) >= 1) AND (length(btrim(job_title)) <= 100)
training_assignment_rule	training_rule_offset	(due_offset_days >= 0) AND (due_offset_days <= 365)
training_assignment_rule	training_rule_sites_not_empty	(site_ids IS NULL) OR (cardinality(site_ids) > 0)
training_event	training_event_has_subject	(training_id IS NOT NULL) OR (session_id IS NOT NULL) OR (external_training_id IS NOT NULL)
training_event	training_event_type	event_type in {scheduled, updated, booked, removed, assigned, acknowledged, attended, no-show, excused, evaluated, signed, sheet attached, submitted, approved, rejected, corrected, cancelled, completed}

Observation catalog

Table	Constraint	Rule
observation_cadence_rule	observation_cadence_rule_basis_check	basis in {Tenure, Risk Events}
observation_cadence_rule	observation_cadence_rule_target_interval_days_check	target_interval_days > 0

Policy library

Table	Constraint	Rule
policy_section	policy_section_depth_check	(depth >= 1) AND (depth <= 2)
policy_clause	policy_clause_kind_check	kind in {rule, lead, table}
observation_behavior_policy	observation_behavior_policy_rank_check	rank > 0

Route operations

Table	Constraint	Rule
route	route_planned_stops_positive	planned_stops > 0
route	route_service_type_known	service_type in {Residential, Commercial, Roll-Off, Transfer}
route	route_status_known	status in {Active, Suspended, Retired}
route_day	route_day_exceptions_not_negative	service_exceptions >= 0
route_day	route_day_exceptions_sum	((((exceptions_no_service + exceptions_lockout) + exceptions_cancelled) + exceptions_blocked) + exceptions_other) = service_exceptions
route_day	route_day_hours_within_window	(on_duty_hours > (0)::numeric) AND (on_duty_hours <= (14)::numeric)
route_day	route_day_lifts_not_negative	lifts >= 0
route_day	route_day_miles_not_negative	miles >= 0
route_day	route_day_status_known	status in {Completed, Partial}
route_day	route_day_status_matches_stops	(status = 'Completed') = (stops_completed >= planned_stops)
route_day	route_day_stops_within_plan	(stops_completed >= 0) AND (stops_completed <= planned_stops)
asset_downtime	asset_downtime_back_after_out	(returned_to_service_date IS NULL) OR (returned_to_service_date >= out_of_service_date)
asset_downtime	asset_downtime_hours_match_return	(returned_to_service_date IS NULL) = (hours_out IS NULL)
asset_downtime	asset_downtime_kind_known	work_order_type in {Planned PM, Unplanned Repair}
labor_month	labor_month_hours_have_cost	(total_hours <= (0)::numeric) OR (total_labor_cost > (0)::numeric)
labor_month	labor_month_lob_known	lob in {Residential, Commercial, Roll-Off, Transfer, Support}
labor_month	labor_month_total_is_the_sum	abs(((total_hours - regular_hours) - overtime_hours)) <= 0.2

Financial layer

Table	Constraint	Rule
customer	customer_type_known	customer_type in {Municipal, Commercial, Industrial, Construction}
customer_service	customer_service_lob_known	lob in {Residential, Commercial, Roll-Off, Transfer}
customer_service	customer_service_monthly_rate_usd_check	monthly_rate_usd >= (0)::numeric
customer_service	customer_service_quantity_check	quantity > 0
invoice_line	invoice_line_charge_known	charge_type in {Service, Disposal, Fee, Adjustment}
ar_invoice	ar_invoice_paid_after_issue	(paid_date IS NULL) OR (paid_date >= invoice_date)
ar_invoice	ar_invoice_paid_is_settled	(paid_date IS NULL) = (outstanding_balance_usd > (0)::numeric)
route_pnl_month	route_pnl_gross_basis	abs((gross_profit_usd - (gross_revenue_usd - total_operating_cost_usd))) <= 0.05
route_pnl_month	route_pnl_net_is_after_disposal	abs((net_revenue_usd - (gross_revenue_usd - disposal_cost_usd))) <= 0.05
route_pnl_month	route_pnl_operating_cost_is_the_sum	abs((total_operating_cost_usd - ((((disposal_cost_usd + labor_cost_usd) + fuel_cost_usd) + maintenance_cost_usd) + safety_claims_cost_usd))) <= 0.05

App support

Table	Constraint	Rule
capture_draft	capture_draft_kind_check	kind in {observation, incident}
capture_draft	capture_draft_observation_names_someone	(kind <> 'observation') OR (subject_person_id IS NOT NULL)
ask_log	ask_log_answered_has_match	(outcome <> ALL (ARRAY['answered', 'declined'])) OR ((matched_question_id IS NOT NULL) AND (reading IS NOT NULL))
ask_log	ask_log_miss_has_no_match	(outcome <> 'miss') OR (matched_question_id IS NULL)
ask_log	ask_log_outcome_check	outcome in {answered, declined, clarify, refused, miss, error}
ask_log	ask_log_question_text_check	length(btrim(question_text)) > 0
benchmark	benchmark_band_nonnegative	low >= (0)::numeric
benchmark	benchmark_band_ordered	high >= low
hub_question_answer	hub_question_answer_resolved_on_needs_resolved	(resolved_on IS NULL) OR resolved
hub_prospect	hub_prospect_accent_check	accent ~* '^#[0-9a-f]{6}$'
hub_prospect	hub_prospect_client_check	(length(btrim(client)) >= 1) AND (length(btrim(client)) <= 80)
hub_prospect	hub_prospect_ground_check	ground ~* '^#[0-9a-f]{6}$'
hub_prospect	hub_prospect_mark_check	(mark ~~ 'data:image/png;base64,%') AND (length(mark) < 400000)
hub_prospect	hub_prospect_personas_shape	(jsonb_typeof(personas) = 'object') AND (length((personas)) < 4000)
hub_prospect	hub_prospect_website_check	(website IS NULL) OR (website ~* '^https?://[^\s]+$')
hub_demo_removed	hub_demo_removed_demo_key_check	(length(demo_key) >= 1) AND (length(demo_key) <= 120)

Auth link (schema app)

Table	Constraint	Rule
app.user_account	user_account_access_level_check	access_level in {driver, supervisor, gm, vp}

Sales (schema sales)

Table	Constraint	Rule
sales.lead	lead_company_check	(length(btrim(company)) >= 1) AND (length(btrim(company)) <= 120)
sales.lead	lead_contact_email_check	(contact_email IS NULL) OR (length(contact_email) <= 200)
sales.lead	lead_contact_name_check	(contact_name IS NULL) OR (length(contact_name) <= 120)
sales.lead	lead_contact_phone_check	(contact_phone IS NULL) OR (length(contact_phone) <= 60)
sales.lead	lead_notes_check	(notes IS NULL) OR (length(notes) <= 8000)
sales.lead	lead_owner_check	(owner IS NULL) OR (length(owner) <= 60)
sales.lead	lead_stage_check	stage in {Lead, Discovery, Demo, Onboarding, Won, Lost}
sales.lead	lead_website_check	(website IS NULL) OR (website ~* '^https?://[^\s]+$')
sales.lead_event	lead_event_kind_check	kind in {Discovery call, Demo, Onboarding, Follow-up, Note}
sales.lead_event	lead_event_notes_check	(notes IS NULL) OR (length(notes) <= 8000)
sales.checklist_answer	checklist_answer_have_check	have in {yes, part, no}
sales.checklist_answer	checklist_answer_row_key_check	(length(row_key) >= 1) AND (length(row_key) <= 200)
sales.checklist_answer	checklist_answer_system_check	(system IS NULL) OR (length(system) <= 400)

Foreign keys

Child	Column(s)	Parent	Nullable	On delete	Deferrable
action	assignee_person_id	person	no	no action	no
action	incident_id	incident	yes	cascade	no
action	inspection_id	inspection	yes	cascade	no
action	observation_id	observation	yes	cascade	no
action	site_id	site	no	no action	no
action	telematics_event_id	telematics_event	yes	cascade	no
action	verified_by_person_id	person	yes	no action	no
action	verifier_person_id	person	yes	no action	no
action	work_done_by_person_id	person	yes	no action	no
app.user_account	auth_user_id	auth.users	no	cascade	no
app.user_account	person_id	person	no	no action	no
app.user_site	auth_user_id	app.user_account	no	cascade	no
app.user_site	site_id	site	no	cascade	no
ar_invoice	customer_id	customer	no	no action	no
ar_invoice	site_id	site	no	no action	no
asset	site_id	site	no	no action	no
asset_downtime	asset_id	asset	no	no action	no
asset_downtime	inspection_id	inspection	no	no action	no
asset_downtime	site_id	site	no	no action	no
asset_type_requirement	requirement_id	requirement	no	no action	no
capture_draft	person_id	person	no	cascade	no
capture_draft	subject_person_id	person	yes	cascade	no
customer	site_id	site	no	no action	no
customer_service	customer_id	customer	no	no action	no
customer_service	customer_site_id	customer_site	no	no action	no
customer_site	customer_id	customer	no	no action	no
document_request	fulfilled_by_document_id	person_document	yes	no action	no
document_request	person_id	person	no	no action	no
document_request	qualification_id	qualification	yes	set null	no
document_request	requested_by_person_id	person	no	no action	no
document_request	requirement_id	requirement	no	no action	no
external_training	course_id	training_course	yes	no action	no
external_training	person_id	person	no	no action	no
external_training	reviewer_person_id	person	yes	no action	no
external_training	submitted_by_person_id	person	no	no action	no
incident	asset_id	asset	yes	no action	no
incident	investigation_owner_person_id	person	yes	no action	no
incident	person_id	person	no	no action	no
incident	primary_incident_type_id	incident_type	no	no action	no
incident	reported_by_person_id	person	no	no action	no
incident	route_id	route	yes	no action	no
incident	signed_off_by_person_id	person	yes	no action	no
incident	site_id	site	no	no action	no
incident	supervisor_person_id	person	no	no action	no
incident_attachment	incident_id	incident	no	cascade	no
incident_attachment	request_id,incident_id	investigation_request	no	no action	no
incident_attachment	uploaded_by_person_id	person	no	no action	no
incident_contributing_factor	contributing_factor_id	contributing_factor	no	no action	no
incident_contributing_factor	incident_id	incident	no	cascade	no
incident_event	actor_person_id	person	yes	no action	no
incident_event	incident_id	incident	no	cascade	no
incident_incident_type	incident_id	incident	no	cascade	no
incident_incident_type	incident_type_id	incident_type	no	no action	no
inspection	asset_id	asset	no	no action	no
inspection	inspector_person_id	person	no	no action	no
inspection	mechanic_signoff_person_id	person	yes	no action	no
inspection	site_id	site	no	no action	no
inspection_defect	corrected_by_person_id	person	yes	no action	no
inspection_defect	inspection_id	inspection	no	cascade	no
investigation_request	assigned_to_person_id	person	no	no action	no
investigation_request	incident_id	incident	no	cascade	no
investigation_request	request_type_id	investigation_request_type	no	no action	no
investigation_request	requested_by_person_id	person	no	no action	no
investigation_request_event	actor_person_id	person	no	no action	no
investigation_request_event	request_id	investigation_request	no	cascade	no
invoice_line	customer_id	customer	no	no action	no
invoice_line	customer_service_id	customer_service	no	no action	no
invoice_line	route_id	route	no	no action	no
invoice_line	site_id	site	no	no action	no
labor_month	site_id	site	no	no action	no
observation	employee_person_id	person	no	no action	no
observation	focus_area	focus_area	no	no action	no
observation	observation_type	observation_type	no	no action	no
observation	site_id	site	no	no action	no
observation	supervisor_person_id	person	no	no action	no
observation_behavior	focus_area	focus_area	no	no action	no
observation_behavior_policy	behavior_id	observation_behavior	no	no action	no
observation_behavior_policy	clause_id,section_id	policy_clause	no	no action	no
observation_behavior_policy	section_id	policy_section	no	cascade	no
observation_item	behavior_id	observation_behavior	yes	no action	no
observation_item	focus_area	focus_area	no	no action	no
observation_item	observation_id	observation	no	cascade	no
observation_risk_rule	behavior_id	observation_behavior	no	no action	no
observation_risk_rule	event_type	telematics_event_type	no	no action	no
observation_type_focus_area	focus_area	focus_area	no	no action	no
observation_type_focus_area	observation_type	observation_type	no	no action	no
operator_release	asset_id	asset	no	no action	no
operator_release	person_id	person	no	no action	no
operator_release	released_by_person_id	person	no	no action	no
person	site_id	site	no	no action	yes
person	supervisor_person_id	person	yes	no action	yes
person_document	person_id	person	no	no action	no
person_document	qualification_id	qualification	yes	no action	no
person_document	requirement_id	requirement	no	no action	no
person_document	submitted_by_person_id	person	no	no action	no
person_document	training_id	training	yes	no action	no
person_document	verified_by_person_id	person	yes	no action	no
person_document	verifier_person_id	person	yes	no action	no
policy_clause	section_id	policy_section	no	cascade	no
policy_section	parent_section_id	policy_section	yes	no action	no
policy_section	policy_document_id	policy_document	no	no action	no
qualification	person_id	person	no	cascade	no
qualification	requirement_id	requirement	no	no action	no
qualification	source_training_id	training	yes	set null	no
qualification	verified_by_person_id	person	no	no action	no
requirement_job_title	requirement_id	requirement	no	cascade	no
revenue_budget_daily	site_id	site	no	no action	no
role	person_id	person	no	cascade	no
route	site_id	site	no	no action	no
route_day	asset_id	asset	no	no action	no
route_day	driver_person_id	person	no	no action	no
route_day	route_id	route	no	no action	no
route_day	site_id	site	no	no action	no
route_pnl_month	route_id	route	no	no action	no
route_pnl_month	site_id	site	no	no action	no
route_stop_plan	customer_service_id	customer_service	no	no action	no
route_stop_plan	route_id	route	no	no action	no
route_stop_plan	site_id	site	no	no action	no
sales.checklist_answer	lead_id	sales.lead	no	cascade	no
sales.lead_event	lead_id	sales.lead	no	cascade	no
sales.team_member	user_id	auth.users	no	cascade	no
site	site_manager_person_id	person	no	no action	yes
site	site_type	site_type	no	no action	no
task	action_id	action	no	cascade	no
task	assignee_person_id	person	no	no action	no
task	escalated_to_person_id	person	yes	no action	no
task	site_id	site	no	no action	no
telematics_event	asset_id	asset	no	no action	no
telematics_event	coached_by_person_id	person	yes	no action	no
telematics_event	driver_person_id	person	no	no action	no
telematics_event	event_type	telematics_event_type	no	no action	no
telematics_event	site_id	site	no	no action	no
training	course_id	training_course	yes	no action	no
training	evaluator_person_id	person	yes	no action	no
training	external_training_id	external_training	yes	no action	no
training	person_id	person	no	cascade	no
training	renews_training_id	training	yes	set null	no
training	requirement_id	requirement	yes	no action	no
training	rule_id	training_assignment_rule	yes	no action	no
training	session_id	training_session	yes	no action	no
training	site_id	site	no	no action	no
training	source_action_id	action	yes	no action	no
training	trainer_person_id	person	yes	no action	no
training_assignment_rule	course_id	training_course	no	no action	no
training_assignment_rule	created_by_person_id	person	no	no action	no
training_course	created_by_person_id	person	yes	no action	no
training_course	requirement_id	requirement	yes	no action	no
training_course_focus_area	course_id	training_course	no	cascade	no
training_course_focus_area	focus_area	focus_area	no	cascade	no
training_material	added_by_person_id	person	yes	no action	no
training_material	course_id	training_course	no	cascade	no
training_material	superseded_by	training_material	yes	set null	no
training_observation_item	observation_item_id	observation_item	no	cascade	no
training_observation_item	training_id	training	no	cascade	no
training_session	cancelled_by_person_id	person	yes	no action	no
training_session	course_id	training_course	no	no action	no
training_session	created_by_person_id	person	no	no action	no
training_session	signed_by_person_id	person	yes	no action	no
training_session	site_id	site	no	no action	no
training_session	trainer_person_id	person	no	no action	no
training_session_attendee	added_by_person_id	person	no	no action	no
training_session_attendee	person_id	person	no	no action	no
training_session_attendee	session_id	training_session	no	no action	no
training_session_attendee	training_id	training	yes	set null	no
truck_cost_month	asset_id	asset	no	no action	no
truck_cost_month	site_id	site	no	no action	no