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

nx9-wg-db — SQLite Persistence Layer

nx9-wg-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, client profiles, 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.
• SQL Encapsulation: All SQL queries, SQLite connection lifecycle, migrations, and row conversions are strictly encapsulated inside nx9-wg-db. Neither nx9-wg-core, nx9-wg-api, nx9-wg-ui, nx9-wireguard, nor nx9-wg-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 (13 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 and structured WireGuard server endpoint keys.
11. audit_events — Append-only operational audit log with event filtering and pagination.
12. backups — Backup metadata and manifest checksum records.
13. client_profiles — Device, connection, and MTU transport profile specifications with built-in protections.

Migration Strategy

• Migrations are defined in crates/nx9-wg-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_wg_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-wg-db