# 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`, `proton0`, etc.), role (`overlay`, `upstream`), private/public keys, optional listen port (`NULL` for dynamic kernel allocation), 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")`:
  - `0001_initial_schema.sql` — Initial relational schema.
  - `0002_wiregui_schema.sql` — WireGUI capabilities and profile structures.
  - `0003_server_endpoint_settings.sql` — Persistent server endpoint settings.
  - `0004_interface_roles.sql` — Interface roles (`overlay` and `upstream`).
  - `0005_optional_listen_port.sql` — Nullable `listen_port` for ephemeral kernel port selection.
- Migrations are executed automatically via `store.migrate().await?`.
- Migrations are tracked in the `_sqlx_migrations` table for idempotency.

## Usage in Code

```rust
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:

```bash
cargo test -p nx9-wg-db
```