Data model

The system uses 38 PostgreSQL tables (PostGIS-enabled). Each is defined in lib/db/src/schema/ and generated below directly from that schema.

admin_actions

Column	Type	Nullable	Key	References
id	uuid	no	PK
actor_user_id	uuid	yes	FK	→ users.id
actor_org_id	uuid	yes	FK	→ organizations.id
action	text	no
target_type	text	yes
target_id	text	yes
before	jsonb	yes
after	jsonb	yes
reason	text	yes
ip	text	yes
ts	timestamp with time zone	no

Indexes: admin_actions_ts_idx, admin_actions_action_idx, admin_actions_actor_idx, admin_actions_target_idx

alerts

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
asset_id	uuid	yes	FK	→ assets.id
user_id	uuid	yes	FK	→ users.id
hotspot_id	text	yes
cluster_id	uuid	yes	FK	→ fire_clusters.id
level	alert_level	no
title	text	no
body	text	no
source	alert_source	no
status	alert_status	no
issued_at	timestamp with time zone	no
expires_at	timestamp with time zone	yes
acked_at	timestamp with time zone	yes
metadata	jsonb	yes

Indexes: alerts_org_idx, alerts_asset_idx, alerts_user_idx, alerts_hotspot_idx, alerts_cluster_idx, alerts_status_idx, alerts_issued_at_idx, alerts_active_dedup_uniq, alerts_active_cluster_dedup_uniq, alerts_broadcast_dedup_uniq

assets

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
owner_user_id	uuid	no	FK	→ users.id
name	text	no
address	text	no
location	geometry(Point, 4326)	no
area	geometry(Geometry, 4326)	yes
property_type	property_type	no
structure	structure_kind	no
bal	bal_rating	no
status	risk_level	no
metadata	jsonb	yes
created_at	timestamp with time zone	no
updated_at	timestamp with time zone	no

Indexes: assets_org_idx, assets_owner_idx, assets_location_gist, assets_area_gist

audit_log

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	yes	FK	→ organizations.id
user_id	uuid	yes	FK	→ users.id
action	text	no
resource	text	yes
metadata	jsonb	yes
ts	timestamp with time zone	no

Indexes: audit_log_ts_idx, audit_log_action_idx, audit_log_user_idx

broadcasts

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
level	alert_level	no
title	text	no
body	text	no
location	geometry(Point, 4326)	yes
radius_m	integer	yes
area	geometry(Geometry, 4326)	yes
reason	text	no
recipient_count	integer	no
valid_from	timestamp with time zone	no
expires_at	timestamp with time zone	yes
revoked_at	timestamp with time zone	yes
revoked_by_user_id	uuid	yes	FK	→ users.id
created_by_user_id	uuid	yes	FK	→ users.id
created_at	timestamp with time zone	no

Indexes: broadcasts_org_idx, broadcasts_location_gist, broadcasts_area_gist, broadcasts_created_at_idx

checkin_sessions

Column	Type	Nullable	Key	References
id	uuid	no	PK
owner_user_id	uuid	no	FK	→ users.id
started_at	timestamp with time zone	no
closed_at	timestamp with time zone	yes

Indexes: checkin_sessions_owner_idx

checkin_statuses

Column	Type	Nullable	Key	References
id	uuid	no	PK
session_id	uuid	no	FK	→ checkin_sessions.id
contact_id	uuid	no	FK	→ family_contacts.id
status	checkin_status	no
responded_at	timestamp with time zone	yes

Indexes: checkin_statuses_session_idx, checkin_statuses_contact_idx

device_tokens

Column	Type	Nullable	Key	References
id	uuid	no	PK
user_id	uuid	no	FK	→ users.id
token	text	no
platform	text	no
app_version	text	yes
registered_at	timestamp with time zone	no
last_used_at	timestamp with time zone	yes
revoked_at	timestamp with time zone	yes

Indexes: device_tokens_user_idx, device_tokens_token_unique

email_verifications

Column	Type	Nullable	Key	References
id	uuid	no	PK
user_id	uuid	no	FK	→ users.id
token_hash	text	no
purpose	text	no
expires_at	timestamp with time zone	no
used_at	timestamp with time zone	yes
created_at	timestamp with time zone	no

Indexes: email_verifications_token_hash_unique, email_verifications_user_purpose_idx

escalation_events

Column	Type	Nullable	Key	References
id	uuid	no	PK
alert_id	uuid	yes	FK	→ alerts.id
level	text	no
reason	text	no
status	text	no
assigned_org_id	uuid	yes	FK	→ organizations.id
acknowledged_by_user_id	uuid	yes	FK	→ users.id
acknowledged_at	timestamp with time zone	yes
resolved_by_user_id	uuid	yes	FK	→ users.id
resolved_at	timestamp with time zone	yes
created_at	timestamp with time zone	no

Indexes: escalation_events_alert_uniq, escalation_events_status_idx, escalation_events_created_at_idx

evacuation_routes

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
asset_id	uuid	no	FK	→ assets.id
primary_name	text	no
backup_name	text	no
destination	text	no
distance_km	double precision	no
eta_minutes	integer	no
hazard_count	integer	no
water_stops	integer	no
refuges	integer	no
notes	jsonb	no
primary_path	jsonb	no
backup_path	jsonb	no
pois	jsonb	no
cached_at	timestamp with time zone	no

Indexes: evacuation_routes_org_idx, evacuation_routes_asset_idx

exclusion_zones

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
kind	text	no
location	geometry(Point, 4326)	yes
radius_m	integer	yes
area	geometry(Geometry, 4326)	yes
label	text	yes
reason	text	yes
valid_from	timestamp with time zone	no
expires_at	timestamp with time zone	yes
revoked_at	timestamp with time zone	yes
revoked_by_user_id	uuid	yes	FK	→ users.id
created_by_user_id	uuid	yes	FK	→ users.id
created_at	timestamp with time zone	no

Indexes: exclusion_zones_org_idx, exclusion_zones_location_gist, exclusion_zones_area_gist, exclusion_zones_active_idx

family_contacts

Column	Type	Nullable	Key	References
id	uuid	no	PK
owner_user_id	uuid	no	FK	→ users.id
name	text	no
role	text	no
mobile	text	no
initials	text	no
tint	text	no
created_at	timestamp with time zone	no

Indexes: family_contacts_owner_idx

fire_cluster_observations

Column	Type	Nullable	Key	References
id	uuid	no	PK
cluster_id	uuid	no	FK	→ fire_clusters.id
org_id	uuid	no	FK	→ organizations.id
observed_at	timestamp with time zone	no
centroid	geometry(Point, 4326)	no
footprint	geometry(Polygon, 4326)	yes
hotspot_count	integer	no
total_frp_mw	double precision	yes
intensity	fire_intensity	no
movement_bearing_deg	double precision	yes
movement_speed_kmh	double precision	yes
created_at	timestamp with time zone	no

Indexes: fire_cluster_obs_cluster_observed_idx, fire_cluster_obs_org_idx, fire_cluster_obs_observed_idx, fire_cluster_obs_centroid_gist

fire_clusters

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
status	text	no
source	text	no
centroid	geometry(Point, 4326)	no
footprint	geometry(Polygon, 4326)	yes
hotspot_count	integer	no
total_frp_mw	double precision	yes
max_confidence	text	yes
intensity	fire_intensity	no
prev_centroid	geometry(Point, 4326)	yes
movement_bearing_deg	double precision	yes
movement_speed_kmh	double precision	yes
merged_into_id	uuid	yes
parent_cluster_id	uuid	yes
incident_id	uuid	yes
first_observed_at	timestamp with time zone	no
last_observed_at	timestamp with time zone	no
last_evaluated_at	timestamp with time zone	no
resolved_at	timestamp with time zone	yes
lineage	jsonb	no
metadata	jsonb	yes
created_at	timestamp with time zone	no
updated_at	timestamp with time zone	no

Indexes: fire_clusters_org_idx, fire_clusters_centroid_gist, fire_clusters_footprint_gist, fire_clusters_status_idx, fire_clusters_last_observed_idx, fire_clusters_incident_idx

fire_incidents

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
source	text	no
external_id	text	yes
name	text	yes
category	text	yes
status	text	yes
alert_level	text	yes
location	geometry(Point, 4326)	no
perimeter	geometry(Geometry, 4326)	yes
size_ha	double precision	yes
council_area	text	yes
region	text	yes
agency_updated_at	timestamp with time zone	yes
observed_at	timestamp with time zone	no
ai_eligible	boolean	no
attribution	text	yes
raw	jsonb	yes
created_at	timestamp with time zone	no
updated_at	timestamp with time zone	no

Indexes: fire_incidents_org_idx, fire_incidents_location_gist, fire_incidents_perimeter_gist, fire_incidents_source_idx, fire_incidents_alert_level_idx, fire_incidents_observed_at_idx, fire_incidents_source_external_unique

fire_predictions

Column	Type	Nullable	Key	References
id	uuid	no	PK
cluster_id	uuid	no	FK	→ fire_clusters.id
org_id	uuid	no	FK	→ organizations.id
made_at	timestamp with time zone	no
horizon_minutes	integer	no
model_version	text	no
engine	text	no
predicted_geom	geometry(Polygon, 4326)	no
predicted_head_lat	double precision	yes
predicted_head_lng	double precision	yes
inputs	jsonb	no
created_at	timestamp with time zone	no

Indexes: fire_predictions_cluster_idx, fire_predictions_org_idx, fire_predictions_made_at_idx, fire_predictions_model_horizon_idx

fire_spread_snapshots

Column	Type	Nullable	Key	References
id	uuid	no	PK
fire_id	uuid	no	FK	→ fires.id
org_id	uuid	no	FK	→ organizations.id
source	text	no
wind_dir_deg	integer	yes
wind_kmh	double precision	no
ffdi	double precision	yes
valid_from	timestamp with time zone	no
valid_to	timestamp with time zone	no
payload	jsonb	no
computed_at	timestamp with time zone	no
expires_at	timestamp with time zone	no

Indexes: fire_spread_snapshots_fire_uq, fire_spread_snapshots_org_idx, fire_spread_snapshots_expires_idx

fire_static_masks

Column	Type	Nullable	Key	References
id	uuid	no	PK
location	geometry(Point, 4326)	no
radius_m	integer	no
label	text	yes
source	text	no
created_at	timestamp with time zone	no

Indexes: fire_static_masks_location_gist, fire_static_masks_source_idx

fire_threats

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
asset_id	uuid	no	FK	→ assets.id
owner_user_id	uuid	no	FK	→ users.id
fire_id	uuid	no	FK	→ fires.id
cluster_id	uuid	yes	FK	→ fire_clusters.id
hotspot_external_id	text	yes
status	text	no
current_severity	text	no
current_level	text	yes
eta_minutes	integer	yes
eta_minutes_low	integer	yes
eta_minutes_high	integer	yes
distance_km	double precision	no
bearing_deg	double precision	yes
ros_kmh	double precision	yes
prediction_source	text	no
predicted_worst	jsonb	yes
first_detected_at	timestamp with time zone	no
last_evaluated_at	timestamp with time zone	no
last_notified_severity	text	yes
last_notified_level	text	yes
last_notified_at	timestamp with time zone	yes
quiet_cycles	integer	no
resolved_at	timestamp with time zone	yes
trajectory	jsonb	no
metadata	jsonb	yes

Indexes: fire_threats_asset_fire_uq, fire_threats_org_idx, fire_threats_owner_idx, fire_threats_fire_idx, fire_threats_cluster_idx, fire_threats_status_idx, fire_threats_last_eval_idx

fires

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
source	text	no
external_id	text	yes
satellite_code	text	yes
sensor_version	text	yes
observed_at	timestamp with time zone	no
confidence	text	yes
frp_mw	double precision	yes
name	text	yes
region	text	yes
intensity	fire_intensity	no
location	geometry(Point, 4326)	no
cluster_id	uuid	yes	FK	→ fire_clusters.id
wind_kmh	integer	yes
wind_direction	text	yes
risk_score	integer	yes
distance_km	double precision	yes
bearing	text	yes
eta_minutes	integer	yes
detected_at	timestamp with time zone	no
updated_at	timestamp with time zone	no

Indexes: fires_org_idx, fires_location_gist, fires_intensity_idx, fires_observed_at_idx, fires_cluster_idx, fires_source_external_unique

forecast_points

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
location	geometry(Point, 4326)	yes
source	text	no
observed_at	timestamp with time zone	yes
hour_label	text	no
valid_at	timestamp with time zone	no
risk_score	integer	no
wind_kmh	integer	no
wind_direction_deg	double precision	yes
temp_c	double precision	no
relative_humidity	double precision	yes
ffdi	double precision	yes
danger_level	text	yes
note	text	yes
created_at	timestamp with time zone	no

Indexes: forecast_points_org_idx, forecast_points_location_gist, forecast_points_valid_at_idx, forecast_points_source_idx

ingest_cycles

Column	Type	Nullable	Key	References
id	uuid	no	PK
source	text	no
status	text	no
started_at	timestamp with time zone	no
finished_at	timestamp with time zone	no
duration_ms	integer	no
fetched	integer	yes
inserted	integer	yes
updated	integer	yes
deleted	integer	yes
metadata	jsonb	yes
error_text	text	yes

Indexes: ingest_cycles_source_idx, ingest_cycles_finished_at_idx

invitations

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
role_id	uuid	no	FK	→ roles.id
email	citext	no
token_hash	text	no
status	text	no
invited_by_user_id	uuid	yes	FK	→ users.id
expires_at	timestamp with time zone	no
accepted_at	timestamp with time zone	yes
accepted_by_user_id	uuid	yes	FK	→ users.id
created_at	timestamp with time zone	no

Indexes: invitations_token_hash_unique, invitations_org_idx, invitations_email_idx, invitations_status_idx

jurisdictions

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	yes	FK	→ organizations.id
parent_id	uuid	yes	FK	→ jurisdictions.id
level	text	no
name	text	no
boundary	geometry(Geometry, 4326)	yes
created_by_user_id	uuid	yes	FK	→ users.id
created_at	timestamp with time zone	no

Indexes: jurisdictions_org_idx, jurisdictions_parent_idx, jurisdictions_boundary_gist

membership_grants

Column	Type	Nullable	Key	References
id	uuid	no	PK
membership_id	uuid	no	FK	→ memberships.id
capability	text	no
max_recipients	integer	yes
max_km2	integer	yes
jurisdiction_id	uuid	yes	FK	→ jurisdictions.id
valid_from	timestamp with time zone	no
expires_at	timestamp with time zone	yes
revoked_at	timestamp with time zone	yes
revoked_by_user_id	uuid	yes	FK	→ users.id
granted_by_user_id	uuid	yes	FK	→ users.id
created_at	timestamp with time zone	no

Indexes: membership_grants_membership_idx, membership_grants_capability_idx, membership_grants_active_uniq

memberships

Column	Type	Nullable	Key	References
id	uuid	no	PK
user_id	uuid	no	FK	→ users.id
org_id	uuid	no	FK	→ organizations.id
role_id	uuid	no	FK	→ roles.id
created_at	timestamp with time zone	no
verified_at	timestamp with time zone	yes
verified_by_user_id	uuid	yes	FK	→ users.id
verification_note	text	yes
jurisdiction_id	uuid	yes	FK	→ jurisdictions.id

Indexes: memberships_user_org_role_unique, memberships_user_idx, memberships_org_idx

notification_deliveries

Column	Type	Nullable	Key	References
id	uuid	no	PK
alert_id	uuid	no	FK	→ alerts.id
user_id	uuid	no	FK	→ users.id
channel	text	no
status	text	no
reason	text	yes
device_count	integer	no
attempted_at	timestamp with time zone	no
delivered_at	timestamp with time zone	yes
metadata	jsonb	yes

Indexes: notification_deliveries_alert_idx, notification_deliveries_user_idx, notification_deliveries_attempted_idx, notification_deliveries_channel_status_idx

notification_preferences

Column	Type	Nullable	Key	References
user_id	uuid	no	PK	→ users.id
alerts_enabled	boolean	no
quiet_hours_start_utc	integer	yes
quiet_hours_end_utc	integer	yes
min_severity	text	no
web_push_enabled	boolean	no
updated_at	timestamp with time zone	no

organizations

Column	Type	Nullable	Key	References
id	uuid	no	PK
slug	text	no
name	text	no
kind	org_kind	no
created_at	timestamp with time zone	no

Indexes: organizations_slug_unique

places

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
kind	place_kind	no
name	text	no
location	geometry(Point, 4326)	no
status	text	no
open	place_open	no
capacity	text	yes
phone	text	yes
notes	text	yes
source	text	no
external_id	text	yes
distance_km	double precision	yes
bearing	bearing_dir	yes

Indexes: places_org_idx, places_kind_idx, places_location_gist, places_source_external_uniq

prediction_costs

Column	Type	Nullable	Key	References
id	uuid	no	PK
prediction_id	uuid	no	FK	→ predictions.id
org_id	uuid	no	FK	→ organizations.id
user_id	uuid	yes	FK	→ users.id
source	text	no
cpu_seconds	integer	no
s3_puts	integer	no
cost_aud	double precision	no
created_at	timestamp with time zone	no

Indexes: prediction_costs_prediction_idx, prediction_costs_org_idx, prediction_costs_user_idx, prediction_costs_created_idx

prediction_etas

Column	Type	Nullable	Key	References
id	uuid	no	PK
prediction_id	uuid	no	FK	→ predictions.id
asset_id	uuid	no	FK	→ assets.id
org_id	uuid	no	FK	→ organizations.id
distance_km	double precision	no
bearing_deg	integer	no
rate_of_spread_kmh	double precision	no
alignment_factor	double precision	no
slope_multiplier	double precision	no
eta_minutes	integer	no
eta_minutes_low	integer	no
eta_minutes_high	integer	no
severity	text	no
confidence	double precision	no
method	text	no
computed_at	timestamp with time zone	no

Indexes: prediction_etas_prediction_idx, prediction_etas_asset_idx, prediction_etas_org_idx, prediction_etas_prediction_asset_uq

prediction_scores

Column	Type	Nullable	Key	References
id	uuid	no	PK
prediction_id	uuid	no	FK	→ fire_predictions.id
cluster_id	uuid	no	FK	→ fire_clusters.id
org_id	uuid	no	FK	→ organizations.id
scored_at	timestamp with time zone	no
model_version	text	no
horizon_minutes	integer	no
containment	double precision	yes
iou	double precision	yes
position_error_km	double precision	yes
eta_error_min	double precision	yes
in_footprint	boolean	yes
observation_id	uuid	yes
observed_at	timestamp with time zone	yes
created_at	timestamp with time zone	no

Indexes: prediction_scores_prediction_uq, prediction_scores_cluster_idx, prediction_scores_org_idx, prediction_scores_model_horizon_idx, prediction_scores_scored_idx

predictions

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
owner_user_id	uuid	yes	FK	→ users.id
kind	prediction_kind	no
bbox	geometry(Polygon, 4326)	no
valid_from	timestamp with time zone	no
valid_to	timestamp with time zone	no
params_hash	text	no
status	prediction_status	no
output_uri	text	yes
metadata	jsonb	yes
expires_at	timestamp with time zone	no
created_at	timestamp with time zone	no

Indexes: predictions_kind_hash_unique, predictions_org_idx, predictions_owner_idx, predictions_bbox_gist, predictions_valid_idx, predictions_expires_idx

roles

Column	Type	Nullable	Key	References
id	uuid	no	PK
slug	role_slug	no
description	text	yes

Indexes: roles_slug_unique

simulation_jobs

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
prediction_id	uuid	yes	FK	→ predictions.id
owner_user_id	uuid	yes	FK	→ users.id
params	jsonb	no
params_hash	text	no
status	job_status	no
priority	integer	no
attempts	integer	no
error	text	yes
started_at	timestamp with time zone	yes
finished_at	timestamp with time zone	yes
created_at	timestamp with time zone	no

Indexes: simulation_jobs_org_idx, simulation_jobs_owner_idx, simulation_jobs_status_idx, simulation_jobs_params_hash_idx

users

Column	Type	Nullable	Key	References
id	uuid	no	PK
org_id	uuid	no	FK	→ organizations.id
email	citext	no
display_name	text	yes
external_id	text	yes
auth_provider	auth_provider	no
auth_provider_user_id	text	yes
is_service_account	boolean	no
password_hash	text	yes
email_verified_at	timestamp with time zone	yes
phone	text	yes
phone_verified_at	timestamp with time zone	yes
created_at	timestamp with time zone	no
last_login_at	timestamp with time zone	yes
token_revoked_before	timestamp with time zone	yes

Indexes: users_email_unique, users_phone_unique, users_provider_subject_unique, users_org_idx