Database

PostgreSQL. nexus and authz use the same database (one DSN per environment: NEXUS_DATABASE_DSN and AUTHZ_DATABASE_DSN point at the same database on the environment's Scaleway RDB instance, provisioned by infra/terraform/modules/database). Each service owns its own tables and there are no foreign keys between them. nexus uses Bun (github.com/uptrace/bun).

Migrations

internal/store/pgstore/migrations/NNN_name.{up,down}.sql, embedded in the binary (//go:embed) and applied by Bun's migrator at startup (DB.Migrate). They run in numeric order, 001 to 032. Add a migration as the next number with both files; never edit one that has shipped.

authz migrates the same way from its own embedded files (timestamp-prefixed). Neither service sets a table name, so both record applied migrations in Bun's default bun_migrations table in the shared database; the two naming schemes do not collide.

Tables owned by nexus

The column lists below are the result of all migrations. NOT NULL and defaults are shown where they matter.

devices

Column	Type	Notes
id	VARCHAR(64) PK	gw-<8 hex>
hostname	VARCHAR(255) NOT NULL
display_name	VARCHAR(255) NOT NULL DEFAULT ''
model, board, architecture		Reported or set by an admin
agent_version	VARCHAR(64)	Reported by the agent
desired_agent_version	VARCHAR(64)	OTA pin; wins over the channel
agent_channel	VARCHAR(32) NOT NULL DEFAULT 'stable'	Release channel tracked
config_version	BIGINT NOT NULL DEFAULT 0	Last version the agent applied
desired_config_version	BIGINT NOT NULL DEFAULT 0	Version the platform wants
config_hash	VARCHAR(64)
api_token	VARCHAR(256)	SHA-256 of the device token
mac_address	VARCHAR(17)	Informational, not identity
device_type	VARCHAR(32) NOT NULL DEFAULT 'gateway'
status	VARCHAR(16) NOT NULL DEFAULT 'holding'	holding, assigned, prepared, retired
org_id	UUID	NULL = holding zone
enrolled_via	UUID	enrollment_codes.id used to enrol
last_seen_at, enrolled_at, created_at, updated_at	TIMESTAMPTZ

enrollment_codes

Enrollment codes (MAC-bound, single-use, Monsieur Wifi APs), transfer codes, and bootstrap tokens for gateways (type='bootstrap': named, multi-use, not MAC-locked, revocable). Lookups go by code_hash; bootstrap tokens keep no plaintext (code is NULL).

Column	Notes
id UUID PK
code VARCHAR(64)	Nullable
code_hash VARCHAR(64) NOT NULL UNIQUE	SHA-256
type VARCHAR(16)	enrollment | transfer | bootstrap
name	Label (batch, installer)
mac_address, device_type, serial_number	MAC-bound codes
max_uses INTEGER, uses INTEGER NOT NULL DEFAULT 0	NULL/0 = unlimited
revoked_at	Set = refused
created_by, claimed_by, claimed_at, expires_at, created_at, updated_at

organizations

Column	Notes
id UUID PK
name VARCHAR(255) NOT NULL
slug VARCHAR(255) NOT NULL UNIQUE	Immutable
owner_id VARCHAR(256) NOT NULL	Zitadel sub, or system
plan VARCHAR(64) NOT NULL DEFAULT 'free'
created_at, updated_at

Migration 003 seeds the Digilan org (00000000-0000-0000-0000-000000000001, owner_id='system'). Orgs made with CreateOrganization are also owned by system; access to any org comes from ACL grants in authz, not from owner_id.

Configuration and state

Table	Shape	Notes
gateway_configs	id BIGSERIAL, device_id → devices (cascade), version BIGINT, config_data JSONB, config_hash, created_by, created_at, status, applied_at, comment TEXT NOT NULL DEFAULT '', error TEXT NOT NULL DEFAULT ''; UNIQUE(device_id, version)	Status: draft, pending, awaiting_confirm, applied, failed, rolled_back. See Config Lifecycle.
gateway_states	device_id PK → devices, state_data JSONB, reported_at, updated_at	One row per device, replaced on each state report
gateway_logs	id, device_id, source, line, logged_at, received_at	Indexed by device/source/time, plus a pg_trgm GIN index on line for search. pf rows kept 48 h, others 7 days.
gateway_metrics	id, device_id, cpu, memory, temperature, recorded_at	From heartbeats; kept 24 h
gateway_block_samples	id, device_id, logging_packets_raw, silent_packets_raw, logging_packets, silent_packets, was_reset, recorded_at	Block counters per state report, with reload-corrected increments; feeds GetBlockTrend
template_overrides	PK (org_id, template_id), defaults JSONB, updated_at, updated_by	Per-org service-profile defaults

Agent updates and releases

Table	Shape	Notes
agent_releases	id UUID, version, arch (amd64|arm64), channel (default stable), sha256, signature (ed25519), object_key, yanked, timestamps; UNIQUE(version, arch, channel)	object_key is a private bucket key; nexus presigns a short-lived GET at read time
agent_update_status	device_id PK, phase, target_version, from_version, error, updated_at	Last agent_update phase; read by GetAgentUpdateStatus

Audit and sessions

Table	Shape	Notes
viewer_tickets	id, token_hash UNIQUE, device_id → devices, user_id, org_id, expires_at, used_at, created_at	30 s, single-use, hash only
shell_tickets	same shape as viewer_tickets	Separate table so the two can never cross
shell_sessions	id UUID, device_id, user_id, org_id, started_at, ended_at, end_reason	Root-console audit; backs ListShellSessions
device_events	id UUID, device_id, event_type, actor, old_value, new_value, created_at	Identity/lifecycle changes; backs ListDeviceEvents
portal_sessions	id UUID, user_id, org_id → organizations, ip, user_agent, started_at, last_seen_at	Portal visits, coalesced from Me calls; backs ListPortalSessions
user_preferences	PK (user_id, key), value JSONB, updated_at	Per-user portal preferences
device_command_replies	command_id PK, device_id, exit_code, stdout, stderr, created_at, expires_at	Private command replies (access codes) awaiting collection; 90 s TTL

Relays

Table	Shape	Notes
relay_bootstrap_tokens	token_hash PK, environment, created_at, spent_at, relay_id	Registered via RegisterRelayToken, spent by Enroll
relays	id PK (rl-<8 hex>), hostname, public_ipv4, public_ipv6, region, version, api_token_hash UNIQUE, max_rendezvous, max_forward, rendezvous_sessions, forward_sessions, healthy, draining, wireguard_public_key, wireguard_port, tunnel_url, last_seen_at, enrolled_at, updated_at	See Relay fleet
relay_sessions	gateway_id PK, relay_id → relays (cascade), role, attached_at, wireguard_public_key, tunnel_address (unique when non-empty)	One assignment per gateway

Site-to-site links

site_links (two devices, networks, presented ranges, tunnel subnet, transport direct|relay, state draft→awaiting_keys→configuring→ established, plus drifted/failed) and site_link_ranges (allocated CIDRs per org). Used by PortalLinkService, which is not mounted.

Tables owned by authz

Same database; written only through AuthzService. See Authz → Model.

Table	Contents
roles	id, name, alias, description
role_actions	(role_id, target, action)
acl	(subject_id, role_id, target, target_id)
service_keys	bcrypt-hashed API keys

Why JSONB for config

• One row is one complete config snapshot, so apply, confirm and rollback are a version number plus a status.
• Adding a field to the config model needs no migration; the agent and portal agree on the shape.
• The agent consumes the document as stored.