Database Schema

All PKs are UUID. All timestamps are timestamptz UTC. All agency-scoped tables are enforced via AgencyAwareRepository.

Auth & Users

Table	Key columns
agencies	id, name, slug, agency_type (psap/company), timezone, is_active
users	id, agency_id, role_id, email, password_hash, first_name, last_name, is_active, last_login_at
roles	id, name, slug, permissions (jsonb)
user_transport_preferences	id, user_id, transport_channel_id, priority_order, delivery_mode, config_override (jsonb, encrypted)
jwt_refresh_tokens	id, user_id, token_hash, expires_at, revoked_at
api_keys	id, agency_id, name, key_hash, key_prefix, scopes (jsonb), last_used_at, expires_at
command_connections	id, psap_agency_id, api_key_hash, ack_mode (poll/callback), public_url, last_seen_at

CFS

Table	Key columns
cfs_configurations	id, agency_id, inbox_path, poll_interval_seconds, file_pattern, parser_class, field_mappings (jsonb)
cfs_imports	id, cfs_configuration_id, filename, file_hash, record_count, status
cfs_incidents	id, cfs_configuration_id, external_cfs_id, caller_name, caller_phone, description, address, raw_xml*, linked_incident_id

*Encrypted at rest.

Incidents & Notifications

Table	Key columns
incidents	id, agency_id, priority (irod/critical/dangerous/routine_special), status (open/acknowledged/resolved/closed), title, description, address*
incident_agencies	incident_id, agent_agency_id — which companies are on this incident
notifications	id, incident_id, notified_user_id, ack_status (pending/acked), ack_source, escalation_due_at, escalation_cancelled_at
notification_deliveries	id, notification_id, transport_channel_id, transport_type_slug, send_status, attempt_count, external_message_id

*Encrypted at rest.

Transport & Rules

Table	Key columns
transport_types	id, name, slug, supports_inbound_ack, config_schema (jsonb), platform_config (jsonb, encrypted)
transport_channels	id, agency_id, transport_type_id, name, config (jsonb, encrypted)
notification_rules	id, agency_id, transport_channel_id, priority_filter, type_filter, time_start, time_end
escalation_rules	id, agency_id, priority_filter, ack_timeout_seconds, escalation_order (jsonb), notify_psap_user_ids (jsonb)

Audit & Communication Log

Table	Key columns
incident_communications	id, incident_id, notification_id, communication_type, direction, content*, payload_snapshot (jsonb, no credentials), sender/receiver context fields, created_at only — no updated_at, append-only
audit_logs	id, agency_id, user_id, action, entity_type, entity_id, before (jsonb), after (jsonb) — immutable

*Encrypted at rest.