Read the diagram: Orange box = parent or core transactional table. properties is the anchor — every site-linked table FKs to it. Voice agent confirmed-location pattern always resolves to property_id before answering hydrants / water / access / alarms / risks. incidents FKs to airs_codes + firefighters for AIRS close-out with airs_confirmed_by/at. dispatch_log denormalises apparatus_call_sign/type/station_name for history even after fleet changes. Spatial tables (geom) are nearest-neighbour queried via PostGIS ST_Distance.
🏢 gis.properties — parent registry
TABLE · 46 rowsproperty_idPKint · nextval
property_nametext
property_typetext
geomgeometry(Point,4326)
construction_type / occupancy_typetext
floors · floor_area_sqmint · numeric
sprinkler_coverage / alarm_systemtext
access_notes · hazards · aliasestext (pre-curated, no scraping)
Children: alarms · call_notes (CASCADE) · conversation_log · property_risks + spatial hydrants/water46
🔥 gis.incidents — core transaction
TABLE · 27 rowsincident_idPKint
incident_name / incident_type / statustext
reported_timetimestamp now()
geomgeometry(Point)
firefighter_idFK→ firefighters
airs_codeFK→ airs_codes.airs_code
airs_suggested_code / suggestion_reasontext (AI suggest)
airs_confirmed_byFK→ firefighters + airs_confirmed_at
commenttext
Children: incident_reports · dispatch_log · agency_notifications · ai.incident_assessment27
👨🚒 gis.firefighters
TABLE · 12firefighter_idPKint
call_signtext UNIQUE
full_name / role / phone / emailtext
station_idFK→ fire_stations
auth_idtext (voice: auth_el_<id>)
qualifications[] / current_shift / activetext[] · bool
↗ agent_state 1:1 · Referenced by incidents/call_notes/conversation_log12
🏛️ gis.fire_stations
TABLE · 6station_idPKint
station_nametext
tankers / personnelint
geomgeometry(Point)
Parent of firefighters + apparatus6 · 23 units
🚒 gis.apparatus
TABLE · 23apparatus_idPKint
call_sign / apparatus_type / statustext
station_idFK→ fire_stations
water_capacity_l / pump_capacity_lpmint
crew_min / crew_max / equipment[]int · text[]
Fleet inventory — station-drilldown in React23
🚨 gis.alarms
TABLE · 73alarm_idPKint
property_idFK→ properties
alarm_date / alarm_type / outcometimestamp · text
alarm_codetext → alarm_codes
geomgeometry(Point)
AS 1670.1/1670.2 + false/malicious analysis73
📝 gis.call_notes — site knowledge
TABLE · 23note_idPKint
property_idFK→ properties CASCADE
firefighter_idFK→ firefighters
note_texttext (crew → next crew)
created_attimestamp
CASCADE delete with property · Next-crew handover23
💬 gis.conversation_log — auto-log
TABLE · 488log_idPKint
firefighter_idFK→ firefighters
property_idFK→ properties (nullable)
question / answertext (voice Q&A)
created_attimestamp
Every voice turn logged · Powers AIRS suggest488
📋 gis.dispatch_log
TABLE · 87dispatch_idPKint
incident_idFK→ incidents
apparatus_id / call_sign / type / station_nameint · text (denormalised)
dispatched_at / arrived_at / cleared_attimestamp
firefighter_ids[] / role / notesint[] · text
History-safe — keeps names even if fleet renamed87
💧 gis.hydrants
TABLE · 34hydrant_idPKint
hydrant_numbertext
flow_rateint (L/min)
statustext
geomgeometry(Point) · ST_Distance
Spatial — nearest 3 via PostGIS (no FK, proximity)34
🚰 gis.water_sources
TABLE · 12water_source_idPKint
source_name / source_typetext
capacity_litresnumeric
geomgeometry(Point)
Spatial alternatives (dam / tank / river)12
⚠️ gis.property_risks
TABLE · 59risk_idPKint
property_idFK→ properties
risk_type / risk_leveltext (High/Critical)
Pre-incident risk — React high-risk assets59
📄 gis.airs_codes
TABLE · 111airs_codePKtext (111,131,412...)
airs_category / subcategory / descriptiontext
keywords[] / nfirs_equivalent / prioritytext[] · text · int
AIRS reference — incidents.airs_code FK111
🔔 gis.alarm_codes
TABLE · 27alarm_codePKtext
detection_method / description / prioritytext
as1670_category / zone_typetext (AS 1670)
AS 1670.1/1670.2 lookup for alarms27
📑 gis.incident_reports
TABLE · —report_idPKint
incident_idFK→ incidents
report_type / status / content_text / content_jsontext · jsonb
generated_byFK→ firefighters
Generated reports (AFIRS)—
📢 gis.agency_notifications
TABLEnotification_idPKint
incident_idFK→ incidents
agency / contact_method / reference_numbertext
requested_at / acknowledged_at / status / notestimestamp · text
Inter-agency coordination—
📖 gis.standard_operating_procedures
TABLEsop_idPKint
sop_number / title / version / effective_datetext · date
incident_types[] / keywords[] / contenttext[] · text
SOP library — keyword search—
🌦️ gis.weather_observations
TABLEobs_idPKint
district / observed_at / temp_c / humidity_pct / wind_kmh / wind_dirtext · numeric
fdr_rating / fdr_index / source (BOM)text · numeric
BOM fire danger rating—
🤖 ai.incident_assessment
AI SCHEMA · 8assessment_idPKint
incident_idFK→ gis.incidents (logical)
risk_level / exposed_assets / recommendationtext · int
assessment_timetimestamp
Future AI risk assessment8
🧭 gis.agent_state
TABLEfirefighter_idPK+FK→ firefighters 1:1
step / context (jsonb) / updated_attext · jsonb · timestamp
Guided confirm-driven state machine per firefighter—
🗺️ gis.assets + gis hydrants/water spatial
GEOM · 59 assetsassets: asset_id PK · asset_name/type/risk_category · geom(Point)59 rows
hydrants / water_sources / fire_stations / incidents / alarms / propertiesall geom(Point,4326) · PostGIS
Power BI views expose lat/lon: v_assets, v_hydrants, v_fire_stations...9 × v_*
Spatial index (GIST) on every geom · Nearest-neighbour via ST_Distance9 views
📊 Power BI views (read-only)
9 VIEWSv_incidents · v_assets · v_hydrants · v_fire_stations · v_water_sources→ lat/lon + joins
v_alarm_analysis · v_property_risks · v_call_notes · v_conversation_log→ enriched w/ names, AS1670, result_classification
powerbi_ro (SCRAM) on :5433 · Oracle + Hestia firewall openread-only
React /timeline/api/stats + Power BI share same PostGIS source9 views