Database

SafeCall uses SQLite for all data storage. Database initialization and migrations are handled by lib/src/db.ts.

Databases

The server runs two separate SQLite databases:

Main Database (safecall.db)

Stores all application data. Path is configured via server_db_path in config/main.config.json (legacy fallback: root main.config.json, default path value: db/safecall.db).

Sensor Database (db/sensor_data.db)

Stores time-series sensor data (temperature, humidity, battery) from beacon adv1/adv8 messages. Kept separate to avoid bloating the main database.

Migrations

Migrations live in server/migrations/ as TypeScript files, numbered sequentially (001-029). In production builds, migrations are embedded via server/src/migration_embeds.ts.

The migration system:

1. Creates a migrations table to track applied migrations
2. Runs pending migrations in order
3. Each migration receives the Database instance

Current Schema

Table	Migration	Purpose
users	001	User accounts (email, password hash, admin flag)
locations	002	Physical locations/buildings
beacons	003, 015, 021, 027	BLE beacons (MAC, name, type, sensor config)
beacon_events	004, 026	Panic/SOS events with zone ID
gateways	005, 007, 008, 011, 017	BLE gateways (MAC, IP, location, map)
logs	006	System logs
maps	009	Floor map images and metadata
zones	010, 012	Zones with rectangle coordinates
zone_connections	013, 018	Zone adjacency graph
tracking_logs	014	Beacon tracking intervals
nodes	020	Navigation graph nodes (x, y, floor)
edges	020	Navigation graph edges (weight)
floors	019	Floor metadata for navigation
device_gateways	022	Gateway device sync table
device_beacons	023	Beacon device sync table
beacon_gateway_sightings	024, 028	Beacon-gateway RSSI sightings
panic_event_gateways	025	Gateways involved in panic events
navigation_tokens	029	Read-only tokens for mobile navigation

Sensor Data Schema

Table	Columns	Purpose
sensor_data	id, dmac, type, value, timestamp	Time-series sensor readings

Column details:

• id — auto-increment primary key
• dmac — beacon MAC address (TEXT)
• type — sensor type: 1 = Humidity, 2 = Temperature, 3 = Battery (INTEGER)
• value — sensor reading (REAL)
• timestamp — milliseconds since epoch (INTEGER)

Indexes: (dmac, timestamp DESC) and (type).

WAL Mode

Both databases run in WAL (Write-Ahead Logging) mode for better concurrent read/write performance.

Query Result Types

All query results are validated at runtime using Valibot schemas defined in server/src/schemas.ts. The corresponding TypeScript types are in lib/src/types/query.d.ts.