nx9-wg/crates/nx9-wg-db/README.md

nx9-db — SQLite Persistence Layer

nx9-db provides the authoritative SQLite persistence layer for the nx9-wg native Rust WireGuard management system.

Architectural Boundaries

• Authoritative State: SQLite is the authoritative persistent store for nx9-wg desired state. It stores what the system intends the network, interfaces, peers, routes, firewall rules, administrator credentials, sessions, tokens, and settings to be.
• Separation of Concerns: SQLite records desired configuration only. Live kernel state (WireGuard interface status, handshake counters, packet counters, live nftables rules, live kernel routes) is queried directly from Linux kernel subsystems in later phases.
• SQL Encapsulation: All SQL queries, SQLite connection lifecycle, migrations, and row conversions are strictly encapsulated inside nx9-db. Neither nx9-core, nx9-api, nx9-ui, nx9-wireguard, nor nx9-network issue SQL directly.

SQLite Configuration

Every connection opened by Store enforces:

• PRAGMA journal_mode = WAL — Write-Ahead Logging for high-concurrency read/write operations.
• PRAGMA foreign_keys = ON — Strict relational integrity across all tables.
• PRAGMA busy_timeout = 5000 — 5-second busy timeout to avoid contention errors.
• PRAGMA synchronous = NORMAL — Optimal reliability and performance in WAL mode.

Database Schema (12 Tables)

1. admin — Single administrator identity (CHECK (id = 1)), Argon2id password hash, TOTP secrets, and login timestamp.
2. sessions — Admin web sessions (ON DELETE CASCADE).
3. login_attempts — IP-based login attempt tracking for brute-force rate limiting.
4. api_tokens — Hashed API tokens for automation (ON DELETE CASCADE).
5. interfaces — Desired WireGuard interfaces (wg0, wg1, etc.), private/public keys, listen port, IPv4/IPv6 CIDRs, MTU, DNS.
6. peers — Desired WireGuard peer definitions, classifications (road_warrior, site_gateway, server, relay), states (active, disabled, revoked, expired), profiles (full_tunnel, split_tunnel, custom), public/private/preshared keys, AllowedIPs, endpoints, and persistent keepalives (ON DELETE CASCADE).
7. networks — Named network CIDRs for routing and organization.
8. routes — Desired kernel routing rules (ON DELETE SET NULL).
9. firewall_rules — Desired firewall policy rules with priorities and directions (in, out, forward).
10. settings — Key-value system settings with secret redaction support.
11. audit_events — Append-only operational audit log with event filtering and pagination.
12. backups — Backup metadata and manifest checksum records.

Migration Strategy

• Migrations are defined in crates/nx9-db/migrations/ and embedded at compile time via sqlx::migrate!("./migrations").
• Migrations are executed automatically via store.migrate().await?.
• Migrations are tracked in the _sqlx_migrations table for idempotency.

Usage in Code

use nx9_db::Store;
use std::path::Path;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    // Connect and auto-migrate
    let store = Store::connect_path(Path::new("/var/lib/nx9-wg/nx9-wg.db")).await?;
    store.migrate().await?;

    // Create single admin if not initialized
    if !store.admin_exists().await? {
        store.create_admin("admin", "$argon2id$...").await?;
    }

    Ok(())
}

Running Tests

Tests use isolated in-memory or temporary file SQLite instances:

cargo test -p nx9-db