thakaresandCopilot
2ac6c81dfe
- Prevent 'nx9-wg version' from creating data directories by avoiding database initialization. - Create parent directories when an explicit --database path is provided. - Redact printed generated administrator passwords; announce file path or redact instead. Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
220 lines
9.4 KiB
SQL
220 lines
9.4 KiB
SQL
------------------------------------------------------------------------
|
|
-- nx9-wg SQLite Initial Migration (0001_initial.sql)
|
|
------------------------------------------------------------------------
|
|
|
|
------------------------------------------------------------------------
|
|
-- 1. Admin (Exactly one row, id=1 enforced by CHECK)
|
|
------------------------------------------------------------------------
|
|
CREATE TABLE admin (
|
|
id INTEGER PRIMARY KEY CHECK (id = 1),
|
|
username TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
totp_secret TEXT,
|
|
totp_enabled INTEGER NOT NULL DEFAULT 0,
|
|
last_login_at TEXT,
|
|
last_login_ip TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE INDEX idx_admin_username ON admin(username);
|
|
|
|
------------------------------------------------------------------------
|
|
-- 2. Sessions
|
|
------------------------------------------------------------------------
|
|
CREATE TABLE sessions (
|
|
id TEXT PRIMARY KEY,
|
|
admin_id INTEGER NOT NULL DEFAULT 1 REFERENCES admin(id) ON DELETE CASCADE,
|
|
ip_address TEXT,
|
|
user_agent TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
expires_at TEXT NOT NULL,
|
|
last_seen_at TEXT
|
|
);
|
|
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);
|
|
CREATE INDEX idx_sessions_admin_id ON sessions(admin_id);
|
|
|
|
------------------------------------------------------------------------
|
|
-- 3. Login Attempts (Brute force protection)
|
|
------------------------------------------------------------------------
|
|
CREATE TABLE login_attempts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
ip_address TEXT NOT NULL,
|
|
attempted_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
success INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE INDEX idx_login_attempts_ip ON login_attempts(ip_address, attempted_at);
|
|
|
|
------------------------------------------------------------------------
|
|
-- 4. API Tokens
|
|
------------------------------------------------------------------------
|
|
CREATE TABLE api_tokens (
|
|
id TEXT PRIMARY KEY,
|
|
admin_id INTEGER NOT NULL DEFAULT 1 REFERENCES admin(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
token_hash TEXT NOT NULL UNIQUE,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
expires_at TEXT,
|
|
last_used_at TEXT,
|
|
revoked_at TEXT
|
|
);
|
|
CREATE INDEX idx_api_tokens_token_hash ON api_tokens(token_hash);
|
|
CREATE INDEX idx_api_tokens_expires_at ON api_tokens(expires_at);
|
|
|
|
------------------------------------------------------------------------
|
|
-- 5. WireGuard Interfaces
|
|
------------------------------------------------------------------------
|
|
CREATE TABLE interfaces (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
private_key TEXT NOT NULL,
|
|
public_key TEXT NOT NULL,
|
|
listen_port INTEGER NOT NULL DEFAULT 51820,
|
|
ipv4_cidr TEXT NOT NULL,
|
|
ipv6_cidr TEXT,
|
|
mtu INTEGER,
|
|
dns TEXT,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
pre_up TEXT,
|
|
post_up TEXT,
|
|
pre_down TEXT,
|
|
post_down TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE INDEX idx_interfaces_name ON interfaces(name);
|
|
|
|
------------------------------------------------------------------------
|
|
-- 6. Peers / Clients
|
|
------------------------------------------------------------------------
|
|
CREATE TABLE peers (
|
|
id TEXT PRIMARY KEY,
|
|
interface_id TEXT NOT NULL REFERENCES interfaces(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
peer_type TEXT NOT NULL DEFAULT 'road_warrior'
|
|
CHECK (peer_type IN ('road_warrior', 'site_gateway', 'server', 'relay')),
|
|
state TEXT NOT NULL DEFAULT 'active'
|
|
CHECK (state IN ('active', 'disabled', 'revoked', 'expired')),
|
|
profile TEXT NOT NULL DEFAULT 'full_tunnel'
|
|
CHECK (profile IN ('full_tunnel', 'split_tunnel', 'custom')),
|
|
public_key TEXT NOT NULL,
|
|
private_key TEXT,
|
|
preshared_key TEXT,
|
|
endpoint TEXT,
|
|
allowed_ips TEXT NOT NULL,
|
|
server_allowed_ips TEXT,
|
|
address_ipv4 TEXT,
|
|
address_ipv6 TEXT,
|
|
dns TEXT,
|
|
mtu INTEGER,
|
|
persistent_keepalive INTEGER,
|
|
expires_at TEXT,
|
|
last_handshake_at TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
UNIQUE(interface_id, name),
|
|
UNIQUE(interface_id, public_key)
|
|
);
|
|
CREATE INDEX idx_peers_interface_id ON peers(interface_id);
|
|
CREATE INDEX idx_peers_name ON peers(interface_id, name);
|
|
CREATE INDEX idx_peers_state ON peers(state);
|
|
|
|
------------------------------------------------------------------------
|
|
-- 7. Networks
|
|
------------------------------------------------------------------------
|
|
CREATE TABLE networks (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
cidr TEXT NOT NULL,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
description TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE INDEX idx_networks_name ON networks(name);
|
|
|
|
------------------------------------------------------------------------
|
|
-- 8. Routes
|
|
------------------------------------------------------------------------
|
|
CREATE TABLE routes (
|
|
id TEXT PRIMARY KEY,
|
|
network_id TEXT REFERENCES networks(id) ON DELETE SET NULL,
|
|
interface_id TEXT REFERENCES interfaces(id) ON DELETE SET NULL,
|
|
destination TEXT NOT NULL,
|
|
gateway TEXT,
|
|
metric INTEGER,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
description TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE INDEX idx_routes_network_id ON routes(network_id);
|
|
CREATE INDEX idx_routes_interface_id ON routes(interface_id);
|
|
|
|
------------------------------------------------------------------------
|
|
-- 9. Firewall Rules
|
|
------------------------------------------------------------------------
|
|
CREATE TABLE firewall_rules (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
interface_id TEXT REFERENCES interfaces(id) ON DELETE SET NULL,
|
|
direction TEXT NOT NULL DEFAULT 'in'
|
|
CHECK (direction IN ('in', 'out', 'forward')),
|
|
action TEXT NOT NULL DEFAULT 'accept'
|
|
CHECK (action IN ('accept', 'drop', 'reject')),
|
|
protocol TEXT NOT NULL DEFAULT 'any'
|
|
CHECK (protocol IN ('tcp', 'udp', 'tcp_udp', 'icmp', 'any')),
|
|
source TEXT,
|
|
destination TEXT,
|
|
source_port INTEGER,
|
|
destination_port INTEGER,
|
|
priority INTEGER NOT NULL DEFAULT 100,
|
|
enabled INTEGER NOT NULL DEFAULT 1,
|
|
description TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE INDEX idx_firewall_interface_priority ON firewall_rules(interface_id, priority);
|
|
|
|
------------------------------------------------------------------------
|
|
-- 10. Settings (Key-Value)
|
|
------------------------------------------------------------------------
|
|
CREATE TABLE settings (
|
|
key TEXT PRIMARY KEY NOT NULL,
|
|
value TEXT NOT NULL,
|
|
is_secret INTEGER NOT NULL DEFAULT 0,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
------------------------------------------------------------------------
|
|
-- 11. Audit Events (Append-only)
|
|
------------------------------------------------------------------------
|
|
CREATE TABLE audit_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
event_type TEXT NOT NULL,
|
|
actor TEXT NOT NULL DEFAULT 'admin',
|
|
resource_type TEXT,
|
|
resource_id TEXT,
|
|
message TEXT,
|
|
metadata TEXT,
|
|
ip_address TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE INDEX idx_audit_created_at ON audit_events(created_at);
|
|
CREATE INDEX idx_audit_event_type ON audit_events(event_type);
|
|
CREATE INDEX idx_audit_resource ON audit_events(resource_type, resource_id);
|
|
|
|
------------------------------------------------------------------------
|
|
-- 12. Backups (Metadata)
|
|
------------------------------------------------------------------------
|
|
CREATE TABLE backups (
|
|
id TEXT PRIMARY KEY,
|
|
filename TEXT NOT NULL,
|
|
size INTEGER NOT NULL,
|
|
checksum TEXT NOT NULL,
|
|
encrypted INTEGER NOT NULL DEFAULT 0,
|
|
schema_version TEXT NOT NULL,
|
|
description TEXT,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
CREATE INDEX idx_backups_created_at ON backups(created_at);
|