Skip to content

database.md

Latest commit

 

History

History
312 lines (245 loc) · 7.98 KB

database.md

File metadata and controls

312 lines (245 loc) · 7.98 KB
Raw

Aether Database Schema

PostgreSQL 16+. Migrations live in /migrations/ (sqlx, single source of truth).

Conventions

  • Primary keys: UUID (gen_random_uuid())
  • Multi-tenant: organization_id on all business tables
  • Soft delete: deleted_at TIMESTAMPTZ on servers, monitors, agents, users
  • Timestamps: created_at, updated_at with trigger set_updated_at()
  • No RLS in MVP — application filtering + integration tests

Core Tables

organizations

Column Type Notes
id UUID PK
name TEXT NOT NULL
slug TEXT UNIQUE URL-safe
status TEXT active, suspended
settings JSONB org flags e.g. allow_private_targets
created_at, updated_at, deleted_at TIMESTAMPTZ

users

Column Type Notes
id UUID PK
email TEXT UNIQUE
password_hash TEXT Argon2id
status TEXT active, disabled
created_at, updated_at, deleted_at TIMESTAMPTZ

organization_members

Column Type Notes
id UUID PK
organization_id UUID FK
user_id UUID FK
role TEXT owner, admin, operator, viewer
UNIQUE(organization_id, user_id)

user_sessions

Column Type Notes
id UUID PK
user_id UUID FK
refresh_token_hash TEXT SHA-256
expires_at TIMESTAMPTZ
revoked_at TIMESTAMPTZ
ip, user_agent TEXT
created_at TIMESTAMPTZ

servers

Column Type Notes
id UUID PK
organization_id UUID FK
name, hostname TEXT
status TEXT pending, active, decommissioned
created_at, updated_at, deleted_at TIMESTAMPTZ

agents

Column Type Notes
id UUID PK
server_id UUID FK
organization_id UUID FK
status TEXT enrolled, online, degraded, offline, revoked
last_seen_at TIMESTAMPTZ
config_version BIGINT
created_at, updated_at, deleted_at TIMESTAMPTZ

agent_credentials

Column Type Notes
id UUID PK
agent_id UUID FK
public_key TEXT base64url Ed25519
key_id TEXT
status TEXT active, revoked
created_at, revoked_at TIMESTAMPTZ

agent_enrollment_tokens

Column Type Notes
id UUID PK
server_id UUID FK
organization_id UUID FK
token_hash TEXT Argon2
expires_at TIMESTAMPTZ 15 min default
used_at TIMESTAMPTZ
attempts INT brute-force counter
created_at TIMESTAMPTZ

agent_configurations

Column Type Notes
id UUID PK
agent_id UUID FK
organization_id UUID FK
version BIGINT monotonic per agent
schema_version INT
content JSONB AgentConfig
checksum TEXT SHA-256 base64url
signature TEXT Ed25519
signing_key_id TEXT
status TEXT issued, active, superseded
issued_at, not_before, expires_at TIMESTAMPTZ
activated_at TIMESTAMPTZ

UNIQUE(agent_id, version)

agent_configuration_acknowledgements

Column Type Notes
id UUID PK
agent_id, configuration_id UUID FK
version BIGINT
status TEXT applied, failed
applied_at TIMESTAMPTZ
error TEXT

Anti-Replay

agent_request_nonces (
  agent_id UUID,
  nonce TEXT,
  request_id UUID,
  received_at TIMESTAMPTZ,
  PRIMARY KEY (agent_id, nonce)
)
-- INDEX (received_at) for purge

agent_processed_batches (
  agent_id UUID,
  batch_id UUID,
  payload_type TEXT,
  processed_at TIMESTAMPTZ,
  PRIMARY KEY (agent_id, batch_id, payload_type)
)

monitors

Column Type Notes
id UUID PK
organization_id UUID FK
name TEXT
type TEXT http, https, tcp, dns, tls
target TEXT URL or host:port
interval_seconds INT
timeout_seconds INT
enabled BOOLEAN
failure_threshold INT default 3
recovery_threshold INT default 2
expected_status INT HTTP only
expected_body TEXT optional substring
headers_encrypted BYTEA optional
state TEXT state machine value
created_at, updated_at, deleted_at TIMESTAMPTZ

monitor_checks

Partitioned monthly by finished_at.

Column Type Notes
id UUID PK
monitor_id, organization_id UUID
started_at, finished_at TIMESTAMPTZ
success BOOLEAN
latency_ms INT
status_code INT
resolved_ip INET
error_type, error_message TEXT
tls_expiry TIMESTAMPTZ
response_size BIGINT

monitor_check_leases

Column Type Notes
monitor_id UUID PK
leased_by TEXT instance_id
leased_until TIMESTAMPTZ
heartbeat_at TIMESTAMPTZ

heartbeats

Partitioned monthly by received_at. Columns: id, agent_id, organization_id, observed_at, received_at, payload JSONB.

incidents

Column Type Notes
id UUID PK
organization_id UUID FK
monitor_id, server_id UUID nullable
status TEXT open, resolved
opened_at, resolved_at TIMESTAMPTZ
title, severity TEXT

incident_events

id, incident_id, organization_id, event_type, payload JSONB, created_at.

Metric Tables (partitioned monthly)

Parent tables with PARTITION BY RANGE (received_at):

  • system_metrics: cpu_pct, mem_total/used/available, load_1/5/15, swap_*
  • disk_metrics: device, mountpoint, fs_type, bytes_total/used/available, inodes_*
  • network_metrics: interface, bytes/packets rx/tx, errors, drops, rates
  • container_metrics: container_id, name, image, state, cpu_pct, mem_*
  • service_status_events: unit_name, active_state, sub_state, failed
  • local_check_results: check_id, check_type, success, latency_ms

metric_aggregates_1m

Non-partitioned (lower volume):

Column Type
entity_type TEXT
entity_id UUID
organization_id UUID
bucket_start TIMESTAMPTZ
metric_name TEXT
min, max, avg DOUBLE
count BIGINT

UNIQUE(entity_type, entity_id, bucket_start, metric_name)

server_signing_keys

Column Type
key_id TEXT PK
public_key TEXT
private_key_encrypted BYTEA
purpose TEXT
status TEXT
created_at, retired_at TIMESTAMPTZ

audit_logs

id, organization_id, actor_type, actor_id, action, resource_type, resource_id, request_id, metadata JSONB, created_at.

Indexes

  • (organization_id, …) on all tenant tables
  • (agent_id, received_at DESC) on heartbeats
  • (server_id, observed_at DESC) on system_metrics
  • (monitor_id, finished_at DESC) on monitor_checks
  • (organization_id, created_at DESC) on audit_logs

Retention (defaults)

Data Retention
heartbeats 30 days
monitor_checks 30 days
raw metrics 7 days (DROP partition)
metric_aggregates_1m 30 days
audit_logs 1 year
incidents permanent
agent_request_nonces 24 hours

Partition Management

Script scripts/create_partitions.sql + worker:

  1. Create partitions 2 months ahead: system_metrics_2026_08
  2. Drop partitions past retention window
  3. Run daily via background worker

Migration Workflow

# Install sqlx-cli
cargo install sqlx-cli --no-default-features --features postgres

# Create migration
sqlx migrate add create_organizations

# Run migrations
DATABASE_URL=postgres://… sqlx migrate run

# Offline mode for CI
cargo sqlx prepare --workspace

Future: TimescaleDB

Feature timescale on aether-database:

  • Convert metric parents to hypertables
  • Enable compression policies
  • MetricStore trait unchanged for callers