Database
The SQLite schema.
Database
This document describes the SQLite database. The database is at data/nvr.db. It stores the Vigil metadata.
The database is pure Go through modernc.org/sqlite. The backend opens it with WAL mode, foreign keys, and a busy timeout. It allows one writer at a time.
The schema is defined by the migration files in server/migrations/. The migrations are embedded and applied at startup.
The tables
The database has seven tables:
userssettingssessionscamerasstream_profilesrecordingsevents
users
The users table stores the accounts.
| Column | Purpose |
|---|---|
id |
The user ID. |
username |
The unique username. |
password_hash |
The Argon2id password hash. |
role |
The role: admin, operator, or viewer. |
| timestamps | The created and updated times. |
settings
The settings table stores key-value settings.
| Column | Purpose |
|---|---|
key |
The setting key. |
value |
The setting value. |
The backend stores the recordings directory, the recording enabled flag, and the retention period here. It also stores the Google Drive OAuth data here.
sessions
The sessions table stores the session tokens. The backend stores only the SHA-256 hash of a token.
| Column | Purpose |
|---|---|
id |
The session ID. |
user_id |
The user, with a foreign key. |
token_hash |
The unique token hash. |
expires_at |
The expiry time. |
| timestamps | The created and updated times. |
Deleting a user deletes their sessions. There are indexes on the user and the expiry time.
cameras
The cameras table stores the camera accounts.
| Column | Purpose |
|---|---|
id |
The camera ID. |
driver |
The camera driver. |
host |
The camera host. |
user |
The camera username. |
password_enc |
The encrypted camera password. |
enabled |
If the camera is enabled. |
status |
The camera status. |
| timestamps | The created and updated times. |
stream_profiles
The stream profiles table stores the camera stream profiles.
| Column | Purpose |
|---|---|
id |
The profile ID. |
camera_id |
The camera, with a foreign key. |
role |
The role: live or record. |
rtsp_url |
The RTSP URL. |
codec |
The optional codec. |
width |
The optional width. |
height |
The optional height. |
There is a unique constraint on the camera and role. Deleting a camera deletes its profiles.
recordings
The recordings table stores the segment index.
| Column | Purpose |
|---|---|
id |
The recording ID. |
camera_id |
The camera, with a foreign key. |
started_at |
The start time. |
duration |
The duration in seconds. |
size |
The file size. |
path |
The file path. |
codec |
The codec. |
thumbnail_path |
The optional thumbnail path. |
archived_at |
The archive time. |
archive_location |
The archive location. |
There is an index on the camera and start time. There are partial indexes for the archive state.
events
The events table stores the domain events.
| Column | Purpose |
|---|---|
id |
The event ID. |
camera_id |
The optional camera. |
type |
The event type. |
severity |
The severity: info, warning, or critical. |
title |
The event title. |
message |
The event message. |
started_at |
The start time. |
ended_at |
The optional end time. |
metadata |
The JSON metadata. |
thumbnail |
The optional thumbnail. |
acknowledged |
If the event is acknowledged. |
There are indexes on the start time, the camera, and the unacknowledged state.
Migrations
The migrations are in server/migrations/. Each migration has an up file and a down file. The files are embedded by server/migrations/embed.go.
The migrations are:
| Migration | Change |
|---|---|
000001_init |
Creates users and settings. |
000002_sessions |
Creates sessions. |
000003_cameras |
Creates cameras and stream_profiles. |
000004_recordings |
Creates recordings. |
000005_events |
Creates events. |
000006_recording_archive_indexes |
Adds the archive indexes. |
The backend applies the migrations with golang-migrate. It embeds the files in the binary.
The query layer
The queries are generated by sqlc. The query files are in server/internal/store/queries/. The generated code is in server/internal/store/.
The query groups are:
- Users: create, get, list, delete, and count.
- Sessions: create, get, delete.
- Settings: get, upsert, delete.
- Cameras: list, get, create, update, delete, and status.
- Stream profiles: list, upsert, delete.
- Recordings: list by range, get, insert, delete, and archive queries.
- Events: insert, get, list, and acknowledge.
The sqlc configuration is in server/sqlc.yaml. It reads the query files and the migrations schema. It generates Go code with JSON tags.
Disaster recovery
The recordings files are the backup truth. The filesystem layout mirrors the index. You can rebuild the index by scanning the disk.
The recordings layout is:
recordings/<camera_id>/<YYYY-MM-DD>/<HH-MM-SS-microseconds>.mp4
The database is on its own path. It is not on the recordings volume.