Database schema

Migrations live in backend/migrations/ and are embedded in the core crate with sqlx::migrate!. They run automatically on startup, and they are the single source of truth for the schema, including in tests.

Tables

Table	Description	Key columns
stacks	Metadata and health status for Compose projects	id, name, current_version, update_available, outdated_images, env_pending
stack_versions	Historical snapshots of docker-compose.yml and their environment	stack_id, version, content (YAML), env_snapshot (JSON)
stack_env	Current environment variables of a stack, including changes not yet applied	stack_id, key, value, secret
containers	Real-time mirror of container state on the host	id, name, stack, state, image, ports
container_metrics	Time-series history for CPU, RAM and network I/O	container_id, cpu_pct, memory_mb, network_rx_bps, timestamp
events	Audit log of system, container and stack lifecycle events	id, kind, entity_id, message, level, timestamp
cluster	One row: this server’s own identity, the join token it hands out, and the cluster it has itself joined	id (this server’s identity), join_token, control_plane_url, node_name, joined_at
nodes	The Kaio servers that have joined this one, with the result of each one’s last probe	id (the joined server’s own identity), name, url, status, version, last_seen, last_error
image_scans	Latest Trivy scan per image, with severity counts and the full CVE list	id (image ID), image_name, status, critical_count…total_count, vulnerabilities (JSON)

Indexes

Index	Why
container_metrics(timestamp)	Retention cleanup and time-window queries for the sparklines
stack_versions(stack_id)	Version history lookups
image_scans(image_name)	Backs the vulnerability join hit on every dashboard poll
nodes(name)	Nodes are addressed by name on every route and command
containers(image)	The other side of that join (image_scans.image_name ⨝ containers.image), also used by the rescan cleanup delete

Retention

Events are deleted after KAIO_EVENTS_RETENTION_DAYS days (default 30). Metrics accumulate in container_metrics; the sparklines read a 12-hour window.

Adding a migration

# in backend/migrations/, create <timestamp>_<name>.sql
make verify     # tests run the migrations from scratch

Never edit an applied migration; add a new one. The test suite builds its database from the same files, so a migration that breaks is caught before it reaches a deployment.

Related

• Persistence
• Data retention