Data model

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

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
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_status_idx, alerts_issued_at_idx, alerts_active_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

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

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

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_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_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
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_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
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_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

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

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
distance_km	double precision	yes
bearing	bearing_dir	yes

Indexes: places_org_idx, places_kind_idx, places_location_gist

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

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