This route translates the canonical wide incident model into UI-facing language. The schema
comes from docs/architecture/03-data-model.md; the examples mirror the mock DetailedIncident data rendered by the incident modal today.
Stable identifiers and source metadata explain where a row came from and how it was keyed across NFIRS eras.
incident_uid STRINGb2f83e7d9c4a10abStable hash of state, FDID, incident date, incident number, exposure number, year, and source.
incident_uid_version STRINGblake2b16-5part-v1Names the key algorithm so a future UID migration is explicit and auditable.
raw_incident_key_json STRING{"state":"PA","fdid":"51021","inc_date":"2024-04-18"}Pre-hash key payload retained for debug, audits, and data lineage.
source_system STRINGNFIRSThe upstream system that supplied the record. NFIRS is constant during the mock phase.
source_schema_vintage STRING2019plusVintage marker for schema/code-set differences such as pre-2019, 2019+, or dBASE records.
source_release_scope STRINGall_incidentsPer-row coverage band used to gate call-composition charts honestly across fire-only and all-incident years.
Department fields connect each incident to the current MVP identity seam: state plus FDID, decorated with a display name.
dept_fdid STRING51021Fire Department Identifier. For MVP, state plus FDID is the working department identity.
state STRINGPATwo-letter state code and a serving partition key.
dept_name STRINGLancaster City Bureau of FireDisplay name decorated from department header data during ETL.
Dates and event times power trends, response-time metrics, and the timeline shown in the incident modal.
incident_date DATE2024-04-18ISO-8601 normalized incident date. Normalization happens before UID hashing.
year INT2024Partition key and the field used to place records into release-scope bands.
alarm_ts TIMESTAMP2024-04-18T14:03:00ZDispatch/alarm time. In mock incidents this is represented as dispatchTimeline.alarm.
arrival_ts TIMESTAMP2024-04-18T14:09:00ZOn-scene time. Used with alarm_ts to derive responseMin in the mock UI.
cleared_ts TIMESTAMP2024-04-18T15:01:00ZClear/in-service time. Used to derive totalIncidentMin in the mock UI.
Classification keeps incident kind separate from severity: a fatal building fire is still a fire, with severity in outcome columns.
incident_type_code STRING111Raw NFIRS incident type code.
incident_type_label STRINGBuilding fireVintage-aware decoded label. The mock modal combines code and label as description.
incident_category STRINGfireCanonical kind such as fire, ems, hazmat, service, goodIntent, falseAlarm, severeWeather, or other.
rollup_version STRINGnfirs-kind-v1Version for the code-to-kind rollup dimension stamped onto each incident row.
Location fields are split between display geography and coordinate provenance so maps can avoid mixing points and centroids silently.
prop_use_code / prop_use_label STRING419 / 1 or 2 family dwellingProperty-use code and decoded label for context and filtering.
city / zip / census_tract STRINGLancaster / 17602 / 42071001000Geographic descriptors used for tables, filters, and aggregated maps.
lat / lon DOUBLE40.0379 / -76.3055Coordinates used by mock map pins. Production rows only expose pin-safe coordinates when provenance allows it.
coordinate_source STRINGgpkgProvenance such as gpkg, geocoded_address, tract_centroid, zip_centroid, or missing.
coordinate_precision STRINGpointMap rule switch: point coordinates can be pins; tract/zip centroids are aggregate-only.
Severity is an overlay made from casualties, loss, and acreage. It never changes the incident kind.
civilian_fatalities / civilian_injuries INT0 / 2Civilian casualty counts. Mock data surfaces these as casualties.civFatalities and casualties.civilianInjuries.
ff_fatalities / ff_injuries INT0 / 1Firefighter casualty counts. Mock incidents currently display firefighter injuries.
property_loss_usd BIGINT185000Estimated property loss in dollars, displayed in the incident modal when present.
acres_burned DOUBLE3.5Wildland acreage impact when applicable.
Boolean module flags tell the UI whether a row has fire, hazardous-material, or EMS-specific detail available.
has_fire_module BOOLEANtrueIndicates linked fire-incident module data exists for drilldown.
has_hazmat_module BOOLEANfalseIndicates linked hazardous-material module data exists for drilldown.
has_ems_module BOOLEANtrueIndicates linked EMS module data exists for drilldown where the release scope includes EMS.
Low-cardinality child rows live in nested arrays for incident drilldown and in satellites/pre-aggregates for analytics.
casualties ARRAY<STRUCT<...>>[{ kind: "civilian", severity_label: "Injury", age: 42 }]Nested casualty details for a selected incident. Not enough by itself for corpus-wide casualty analytics.
incident_casualty.record_uid STRINGcas_8fd2b2a01c77Child-record identifier that references the parent incident_uid rather than replacing it.
agg_casualty_dept_year_kind AGGREGATEPA / 51021 / 2024 / civilian / injury_count = 12Pre-aggregate for casualty analytics because R2 SQL cannot explode nested arrays.
The mock modal uses friendly fields such as id, description, dispatchTimeline, responseMin, and casualties. Those
are representative projections of the canonical columns above, not a separate production
schema. When DuckDB/R2 data lands, the modal can keep the same user-facing layout while
reading from canonical field names.