SCOPE Safety POC: Sample Data Set

Synthetic sample data for the SCOPE safety / risk management platform, modeled on waste-services operations (eight hauling depots, a landfill and a recycling center). 32 CSVs in data/csv/: the 12 entities in SCOPE_ERD.png, a SITE entity, two lookups, three junction tables, the route-operations feed and a financial layer.

Window: 2024-10-01 → 2026-09-28 ("today" = 2026-09-28), set in scripts/window.py
Scale: 10 sites · 596 people · 396 assets · 1,488 incidents · 840,034 rows · 131 MB
Names: 100% synthetic. No real employee data is reproduced.
Regenerate: python3 scripts/generate_sample_data.py, deterministic (seed derived from the anchor date)
Verify: audit.py, verify.py, coherence.py, three independent suites, 0 failures

Files

Core platform (navy in the ERD)

File	Rows	Notes
`site.csv`	10	New, not in the ERD. See Deltas.
`person.csv`	596	Employees, drivers, supervisors. Self-referencing `supervisor_person_id`.
`role.csv`	723	Primary role per person + optional secondary (safety committee, ERT, trainer).
`requirement.csv`	20	CDL, DOT med cert, LOTO, confined space, fall protection, HAZWOPER, etc.
`requirement_job_title.csv`	58	Junction: which job titles each requirement applies to (1NF).
`qualification.csv`	1,316	Licenses/certs/medicals held, with issue and expiry dates.
`asset.csv`	396	Trucks and off-road equipment. `telematics_enabled` gates telematics events.

Event capture (teal in the ERD, "from POC sheet")

File	Rows	Notes
`incident.csv`	1,488	The spine; see Deltas.
`incident_type.csv`	12	Lookup, matching the "What" checkboxes on the VelocityEHS form.
`incident_incident_type.csv`	1,712	Junction: 30 incidents carry more than one type.
`contributing_factor.csv`	24	Lookup, categorized (Human Factors, Procedure/Process, …).
`incident_contributing_factor.csv`	3,305	Junction, 1–4 ranked factors per incident.
`observation.csv`	10,792	Behavior-based safety observations. Safe / At-Risk / Mixed.
`telematics_event.csv`	13,263	Lytx/DriveCam-style: harsh braking, following distance, distraction, speeding.

Follow-through (orange in the ERD)

File	Rows	Notes
`inspection.csv`	233,234	Largest file (51 MB). Daily DVIRs + PM service + annual DOT + audits.
`inspection_defect.csv`	41,030	New: the DVIR checklist line items. See Deltas.
`action.csv`	11,416	Corrective actions. Polymorphic source: incident / observation / inspection / telematics.
`task.csv`	16,253	Alerts, reminders, escalations generated by actions.
`training.csv`	3,100	Assigned + completed training, incl. 284 remedial records traced to an action.

Route operations (the first non-safety feed)

File	Rows	Notes
`route.csv`	241	A serviced area worked from a hauling depot. Readable code, like `site`.
`route_day.csv`	132,190	Second largest (21 MB). One route on one day: truck, driver, stops, lifts, miles and measured on-duty hours.

Why it is here. Every rate this platform publishes (TRIR, DART, AFR) divides by headcount × 2,000 hours prorated to the window, which is an assumption, not a measurement. route_day.on_duty_hours is measured: 393,983 hours against 766,948 assumed company-wide over the same window. It replaces the assumption for drivers on routes and for nobody else (mechanics, supervisors and plant crews are not in it) so wiring exposure to this table alone would halve every rate and make them wrong in a new way. The honest use is a blend.

A route is not a truck. The route is geographic and permanent; the truck serving it is whatever the depot has spare that morning, allocated fresh each day. Measured over this feed a truck is driven by a median of 37 different people and a driver uses 40 different trucks, which matches the 30/34 the inspection data already gives, because a per-truck route would have contradicted 64,916 rows already in the set.

The two feeds describe the same morning. Where a pre-trip DVIR exists for that truck on that day, the route-day takes its driver and its start time. audit.py asserts it.

No UUID on route_day. The uuid convention exists for offline capture; a route-day is never captured in the field, it arrives as a nightly file. Its key is natural, (route_id, service_date), with a unique index on (asset_id, service_date) so one truck cannot be on two routes in one day.

Routes exist only at hauling depots. On-road assets elsewhere (Water Trucks at the landfill, for example) service a site rather than run a route.

Financial and reporting layer

Read-only from the app; GM and VP only under RLS. Feeds /cofi.

File	Rows	Notes
`ar_invoice.csv`	102,949	One invoice per customer per month, with its payment date (or none).
`asset_downtime.csv`	2,426	Hours a vehicle was out of service after an out-of-service inspection. A null return date means it is still out.
`customer.csv`	4,296	Customers. A town served by residential routes is one customer; every business name is used once.
`customer_service.csv`	4,357	What is serviced there, on which route and at what price.
`customer_site.csv`	4,296	Where a customer is serviced.
`invoice_line.csv`	208,826	Third largest. One billed line. Route revenue is the sum of these lines by construction.
`labor_month.csv`	1,007	Labor hours and cost by site, line of business and month.
`revenue_budget_daily.csv`	23,266	The revenue plan, by day.
`route_pnl_month.csv`	5,769	Route profit and loss by month, gross basis.
`route_stop_plan.csv`	4,357	The planned stops on each route.
`truck_cost_month.csv`	7,306	Operating cost per truck per month.

The closed loop

INCIDENT ─┐
OBSERVATION ─┼─> ACTION ──> TASK ──> (escalation)
INSPECTION ─┤      │
TELEMATICS ─┘      └──> TRAINING ──> QUALIFICATION ──> satisfies REQUIREMENT

89% of incidents have ≥1 corrective action; severe incidents have 2–4.
100% of actions have ≥1 task; actions requiring verification get a second task assigned to the site manager.
action.action_type = 'Retraining' produces a training row with is_remedial = 'Yes' and source_action_id pointing back. Its status follows the action's: Completed, Overdue and Cancelled carry across, Open and In Progress become Assigned. training.status is Assigned / In Progress / Completed / Overdue / Cancelled; a Cancelled row is neither open nor complete (a withdrawn retraining takes its course with it, 2026-10-08).
Only Major and Out of Service inspection defects raise a tracked action; a minor DVIR defect is fixed by the mechanic on the spot.

Verification

The figures in this section and the ones below it were measured on the first, 8-month version of the data set and record how it was calibrated. The suites themselves are current; their latest output is data/AUDIT.txt, VERIFY.txt and COHERENCE.txt.

scripts/audit.py runs on every regeneration. Current result: 0 failures, 0 warnings.

Check	Result
Foreign-key relationships	48 checked, 0 broken
Primary-key uniqueness	16 tables, all unique
Composite keys on junctions	unique; every incident has exactly 1 primary type
Junction vs denormalized `primary_incident_type_id`	agree on 100% of rows
Logical / temporal consistency	70 checks, 70 clean

The 70 logical checks include: no event before a person's hire date or after their termination; no observation where the observer is the subject; no trainer who is the trainee; telematics only on telematics-enabled assets driven by people with driving job titles; DVIRs only on on-road assets; odometer readings that increase monotonically with inspection date and never exceed the asset's current reading; defect_count equal to the child row count; out_of_service consistent with defect severity; recordable/DART/LTI flags consistent with OSHA classification; every action created on or after its source event with exactly one source FK set; no action still Open or In Progress past its due date; task status consistent with parent action.

Realism benchmarks

Distributions were tuned against the real May-2026 incident export and published industry rates for NAICS 5621 (waste collection).

Metric	This data set	Reference
TRIR (recordables × 200k / hours)	4.5	industry 4.5–5.5
DART rate	3.3	industry 2.5–3.5
Injuries as % of all incidents	17.3%	real sheet 18.9%
Property Damage share	54.5%	real sheet 54.5%
Motor Vehicle Accident share	12.2%	real sheet 12.1%
Fire share	10.8%	real sheet 9.8%
Driver share of people involved	54.3%	real sheet 64.4%
Observations per employee per month	0.76	BBS programs 0.5–2.0
Telematics events per driver per month	1.7	Lytx/DriveCam 1–6
On-road assets per Driver	1.02	1 truck per 1.0–1.3 drivers
DVIR defect rate	11.8%	fleet programs 10–25%
Pre-trip DVIR completion vs obligation	89%	the compliance gap is the metric

Third pass: narrative coherence

The first two suites check that relationships resolve and that aggregates match. Neither asks what a human asks when they open one record in the app: does this story hold together? It did not. Title, location, equipment, root cause, contributing factors and follow-up training were each drawn from independent pools, so every foreign key resolved while the record read as fiction: a landfill fire logged at a fuel island with a rear-load truck attached and Bloodborne Pathogens retraining.

Incidents are now generated from scripts/scenarios.py, a catalog of 48 scenarios. Each bundles the site types it can occur at, the jobs it happens to, plausible locations and equipment, the investigator's conclusions, the contributing factors that can apply, and the retraining that follows. scripts/coherence.py validates every incident against its scenario and prints complete records for a human to read.

Coherence measure	Before	After
Driving incidents carrying plant-floor factors (LOTO, guarding)	52%	0%
Incident names a vehicle that isn't the linked asset	82%	0%
Remedial training unrelated to the cause	71%	0%
Observation note doesn't discuss its own focus area	92%	0%
Road-only telematics events on off-road plant	43%	0%
Night incidents involving day-shift staff	75%	0%
Corrective action type ill-suited to the incident	11%	0%
Actions owned by a role that can't perform them	254	0
Distinct incident titles	49	145
Most repeated title	7.1%	3.4%

Titles now follow the real export's mixed convention: bare descriptions alongside a site prefix ("Castleton: Backing incident in customer lot"), matching its repetition profile.

Second verification pass

scripts/verify.py is an independent suite written without reusing audit.py's checks. It covers CSV/file hygiene (encoding, BOM, line endings, column counts, padded values, dead and single-valued columns), controlled-vocabulary drift, uniqueness beyond primary keys (VIN, plate, email, one pre-trip per asset per day), org-structure rules (primary role, supervisory titles, site staffing minima, workflow ownership by employed staff), temporal and dimensional coverage gaps, statistical outliers, and a dry-run of eleven dashboard queries. It also asserts byte-identical output across regeneration.

Current result: 0 failures, 0 warnings. Full output in data/VERIFY.txt. Coherence output is in data/COHERENCE.txt.

Known deviations (deliberate, not defects)

Incident volume is ~57% of the real observed rate. The real May-2026 sheet shows 77 incidents/month across these 10 sites; this set averages 44. Volume was held at the requested ~350 so that headcount, TRIR and DART stay in their industry bands simultaneously. To match the real rate, raise N_INCIDENTS to ~620 and headcount proportionally.
Restricted Work is 7% of injuries vs ~20% in the real sheet. The seven scripted lost-time narratives consume the severe end of the distribution. Raising it further pushes DART above the industry band.
Near Miss is only 2% of events. The real source system captures almost none. A mature program reports far more near misses than recordables; growing that ratio is a target outcome for the platform, so the low starting point is intentional.
DVIR is one pre-trip per asset per operating day, plus a post-trip 25% of the time. Separate pre- and post-trip records for every asset-day would roughly double the largest file for little demo value.
TRIR sits at 4.3, just under the 4.5–5.5 band, while DART sits at 3.5, at the top of its band. The seven scripted lost-time narratives are a fixed floor under DART for a population this size. Tuning the classification mix to lift TRIR into the band pushes DART to 3.8, outside it. This configuration was chosen as the better balance of the two.

Deltas from `SCOPE_ERD.png`

Everything below is an addition, not a change. No ERD field was dropped.

1. New SITE entity. The ERD models site as a text field on PERSON, but Site is the primary rollup dimension in the real incident data and in the exec Safety Summary. site.csv adds site_type (Hauling / Waste-to-Energy / Landfill / Recycling), region, address, headcount, and site manager. Every event table carries site_id.

2. INCIDENT extended from 5 columns to 34. The ERD has incident_id, employee_id, incident_date, incident_type, severity. Added, sourced from the real spreadsheet, the VelocityEHS form, and the LTI report:

Added column	Source
`incident_number`	Real spreadsheet (`IN-YYYYMMDD-NNN`)
`incident_time`, `title`, `description`	Spreadsheet + form
`site_id`, `specific_location`, `occurred_on_company_premises`	Form ("Where")
`person_job_title`, `supervisor_person_id`	Spreadsheet
`person_tenure_years`	LTI report ("Tenure"), derived from `person.hire_date`
`injury_classification`, `injury_illness_type`, `body_part`	Spreadsheet
`recordable`, `dart_case`, `lost_days`, `restricted_days`, `reportable_to_agency`, `is_lti`	Spreadsheet + OSHA logic
`lost_time_start_date`, `lost_time_end_date`	Asked for 2026-10-21: which days a Lost Time case was away, not only how many
`asset_id`, `equipment_involved`	Form ("Equipment Involved")
`root_cause_category`, `root_cause`, `corrective_action_summary`	LTI report
`reported_by_person_id`, `date_reported`, `investigation_owner_person_id`	Form ("Internal Reporting" / "Follow Up Responsibilities")
`estimated_cost_usd`, `status`	New, needed for cost rollups and workflow state

3. Multi-valued incident type. Real records like "Motor Vehicle Accident; Property Damage; Equipment Failure" are modeled as a junction (incident_incident_type) with an is_primary flag, matching the checkbox UI on the form.

4. Structured contributing factors. The LTI report's "Root Cause & Significant Contributing Factors" is split into a narrative (incident.root_cause), a controlled category (root_cause_category), and a ranked junction to a 24-value lookup, so leading indicators are queryable rather than trapped in prose.

5. INSPECTION_DEFECT child table. The DVIR is a ~50-item checklist across Power Unit, Safety Equipment, and Trailer. Each defective component is captured with severity and the driver → mechanic → driver correction sign-off chain from the paper form.

6. PERSON.hire_date. Required to compute tenure, which the exec report reports on and which correlates with incident rate.

Normalization

1NF. No column holds a delimited list. requirement.applies_to_job_titles was replaced by the requirement_job_title junction. (Free-text narrative columns, description, root_cause, notes, remarks, contain semicolons as ordinary punctuation.)

2NF / 3NF. Every non-key column depends on its table's key. Lookups (incident_type, contributing_factor, requirement) are separate entities rather than repeated strings.

Deliberate denormalization, kept for query convenience and documented so it is a choice rather than an oversight:

Column	Rationale
`incident.person_job_title`, `person_tenure_years`	Point-in-time snapshots. Job title and tenure at the moment of the incident. Not an update anomaly: recomputing from `person` today would be wrong after a promotion.
`incident.primary_incident_type_id`	Duplicates the junction's `is_primary` row. Kept so simple charts don't need a join; audited for agreement.
`incident.corrective_action_summary`	Aggregate of child `action.action_type`. Read-model convenience.
`incident.equipment_involved`	The form's own free-text field, alongside the structured `asset_id`.
`site_id` on `telematics_event`, `task`, `training`	Transitively derivable, but present so every fact table can be filtered by site without a multi-hop join.
`inspection.defect_count`, `telematics_event.severity_band`, `incident.is_lti`, `site.headcount`	Cheap derived values; all audited for agreement with their source.
`inspection.odometer_miles`	A genuine point-in-time reading, back-computed from the asset's current odometer. Increases monotonically with date.

Removed after the audit: qualification.days_to_expiry and action.days_overdue. Both were computed relative to 2026-08-31 and would have been silently wrong the day after the data was loaded. Compute them at query time from expiry_date / due_date.

Polymorphic FK. action carries four nullable source FKs plus a source_type discriminator. Exactly one is set on every row (audited). In Postgres, enforce with a CHECK constraint, or split into four link tables if you prefer strict FKs.

Data quality conventions

The taxonomy is clean and every field that logically applies is populated. Fields that are structurally N/A are left empty:

injury_classification / injury_illness_type / body_part on Injury and Occupational Illness incidents only.
lost_days only when classification is Lost Time; restricted_days only when Restricted Work.
lost_time_start_date / lost_time_end_date only on Lost Time: the first and the last day away from work, inclusive. The start is the day after the incident (OSHA counts from then), lost_days = end - start + 1, and a case still running has no end.
vin, license_plate, odometer_miles on on-road assets; engine_hours on off-road.
completed_date empty unless status is Completed.

Injury type and body part are derived from the incident title, so a coolant splash is an eye exposure and never a head laceration. Severity is derived from the OSHA classification, so the two can never disagree.

Note what this deliberately differs from in the source: the real spreadsheet has duplicate categories (Bruise vs Contusion/Bruise, Cut vs Laceration), a blank Reportable on 58% of rows, and # Lost Days / Who is your Supervisor? 100% empty. If you want to demo data-quality detection and migration cleanup, ask for a "dirty" variant.

Derived logic

recordable = Yes when classification is Medical Treatment, Restricted Work, or Lost Time.
dart_case = Yes for Restricted Work or Lost Time.
is_lti = Yes when lost_days > 0.

Planted storylines

#	Storyline	How to see it
1	Castleton & Fair Haven is degrading. Incidents climb 3→9/month Jan→Aug; observation coverage falls; 28% of corrective actions overdue; DVIR completion 82%.	`incident` grouped by `site_id`, month
2	Wallingford is best-in-class. Incidents fall 5→1/month; 755 observations (2.5× Castleton); 4% overdue actions; DVIR completion 99%.	Same query, contrast the two
3	Five repeat-offender drivers. 39–74 telematics events each vs a 3–18 baseline, capped at 10 incidents apiece.	Count `telematics_event` by `driver_person_id`
4	Telematics predicts incidents. 49% of MVA/Property-Damage incidents have ≥3 severity-6+ telematics events in the prior 30 days.	Join telematics to incident on person, 30-day window
5	Overdue training precedes incidents. 62% of incidents involve a person with an overdue training record.	Join `training.status='Overdue'` to `incident.person_id`
6	Expiring qualifications. 13 people with a CDL / med cert / certification expiring within 60 days; 2 already expired.	`qualification` where `expiry_date <= '2026-10-30'`
7	Overdue corrective actions & escalation. 518 overdue actions, each carrying an extra escalation task routed to the site manager.	`action.status='Overdue'` → `task.escalation_level > 0`
8	DVIR compliance gap. 89% of legally required pre-trips were recorded, and 8,548 DVIRs were filed without a driver signature. Both gaps concentrate at Castleton.	Expected asset-days vs `inspection` rows; `driver_signoff='No'`
9	The LTI YTD Summary reproduces. 9 lost-time cases, 7 adapted from the real exec report (foot pinch during OJT, ice slip/wrist fracture, torch burns, struck by motorist, ash burns, LOTO failure, ergonomic elbow injury) each matched to a person whose tenure fits the narrative (0.4 yr for the trainee, 11.8 yr for the veteran mechanic).	`incident` where `is_lti='Yes'`

Next step

Done: the CSVs are loaded into a Supabase Postgres by scripts/db/load.sh, alongside the tables the migrations own (67 in public in all). Entity tables moved from text keys to UUIDs for offline capture; see Primary keys in CLAUDE.md. For the database as built, see docs/ERD.md and the review pack in docs/dba/.