-- dev_seed.sql (SQLite) — development test data -- -- Running this script is idempotent: existing rows are left untouched -- and missing rows are inserted. Safe to run multiple times. -- -- Load with: cargo run --features ssr --bin seed -- ── Networks ────────────────────────────────────────────────────────────────── INSERT OR IGNORE INTO networks (name, cidr) VALUES ('LAN', '192.168.1.0/24'); INSERT OR IGNORE INTO networks (name, cidr) VALUES ('DMZ', '192.168.10.0/24'); INSERT OR IGNORE INTO networks (name, cidr) VALUES ('Corporate', '10.0.0.0/8'); INSERT OR IGNORE INTO networks (name, cidr) VALUES ('VPN', '172.16.0.0/16'); -- ── Hosts ───────────────────────────────────────────────────────────────────── -- Hosts have no UNIQUE constraint, so we guard each insert with WHERE NOT EXISTS. -- Network IDs are resolved by subquery on cidr for portability. -- LAN — 192.168.1.0/24 INSERT INTO hosts (name, ip, network_id) SELECT 'gateway', '192.168.1.1', id FROM networks WHERE cidr = '192.168.1.0/24' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'gateway' AND ip = '192.168.1.1'); INSERT INTO hosts (name, ip, network_id) SELECT 'workstation-01', '192.168.1.10', id FROM networks WHERE cidr = '192.168.1.0/24' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'workstation-01' AND ip = '192.168.1.10'); INSERT INTO hosts (name, ip, network_id) SELECT 'workstation-02', '192.168.1.11', id FROM networks WHERE cidr = '192.168.1.0/24' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'workstation-02' AND ip = '192.168.1.11'); INSERT INTO hosts (name, ip, network_id) SELECT 'workstation-03', '192.168.1.12', id FROM networks WHERE cidr = '192.168.1.0/24' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'workstation-03' AND ip = '192.168.1.12'); INSERT INTO hosts (name, ip, network_id) SELECT 'nas-01', '192.168.1.20', id FROM networks WHERE cidr = '192.168.1.0/24' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'nas-01' AND ip = '192.168.1.20'); INSERT INTO hosts (name, ip, network_id) SELECT 'printer-01', '192.168.1.50', id FROM networks WHERE cidr = '192.168.1.0/24' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'printer-01' AND ip = '192.168.1.50'); -- DMZ — 192.168.10.0/24 INSERT INTO hosts (name, ip, network_id) SELECT 'web-server-01', '192.168.10.10', id FROM networks WHERE cidr = '192.168.10.0/24' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'web-server-01' AND ip = '192.168.10.10'); INSERT INTO hosts (name, ip, network_id) SELECT 'web-server-02', '192.168.10.11', id FROM networks WHERE cidr = '192.168.10.0/24' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'web-server-02' AND ip = '192.168.10.11'); INSERT INTO hosts (name, ip, network_id) SELECT 'db-server-01', '192.168.10.20', id FROM networks WHERE cidr = '192.168.10.0/24' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'db-server-01' AND ip = '192.168.10.20'); INSERT INTO hosts (name, ip, network_id) SELECT 'mail-server-01', '192.168.10.30', id FROM networks WHERE cidr = '192.168.10.0/24' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30'); -- Corporate backbone — 10.0.0.0/8 INSERT INTO hosts (name, ip, network_id) SELECT 'core-switch-01', '10.0.0.1', id FROM networks WHERE cidr = '10.0.0.0/8' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'core-switch-01' AND ip = '10.0.0.1'); INSERT INTO hosts (name, ip, network_id) SELECT 'monitoring-01', '10.0.1.10', id FROM networks WHERE cidr = '10.0.0.0/8' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'monitoring-01' AND ip = '10.0.1.10'); INSERT INTO hosts (name, ip, network_id) SELECT 'backup-server-01', '10.0.1.20', id FROM networks WHERE cidr = '10.0.0.0/8' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'backup-server-01' AND ip = '10.0.1.20'); INSERT INTO hosts (name, ip, network_id) SELECT 'log-server-01', '10.0.1.30', id FROM networks WHERE cidr = '10.0.0.0/8' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'log-server-01' AND ip = '10.0.1.30'); -- VPN — 172.16.0.0/16 INSERT INTO hosts (name, ip, network_id) SELECT 'vpn-gateway-01', '172.16.0.1', id FROM networks WHERE cidr = '172.16.0.0/16' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'vpn-gateway-01' AND ip = '172.16.0.1'); INSERT INTO hosts (name, ip, network_id) SELECT 'vpn-client-01', '172.16.1.10', id FROM networks WHERE cidr = '172.16.0.0/16' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'vpn-client-01' AND ip = '172.16.1.10'); INSERT INTO hosts (name, ip, network_id) SELECT 'vpn-client-02', '172.16.1.11', id FROM networks WHERE cidr = '172.16.0.0/16' AND NOT EXISTS (SELECT 1 FROM hosts WHERE name = 'vpn-client-02' AND ip = '172.16.1.11'); -- ── Ports catalog ───────────────────────────────────────────────────────────── INSERT OR IGNORE INTO ports (number, description) VALUES (22, 'SSH'), (25, 'SMTP'), (53, 'DNS'), (80, 'HTTP'), (143, 'IMAP'), (161, 'SNMP'), (443, 'HTTPS'), (445, 'SMB'), (465, 'SMTPS'), (500, 'IKE / IPSec'), (514, 'Syslog'), (587, 'SMTP Submission'), (873, 'rsync'), (993, 'IMAPS'), (1194, 'OpenVPN'), (2049, 'NFS'), (3000, 'Grafana / Gitea'), (3306, 'MariaDB / MySQL'), (3389, 'RDP'), (4500, 'IPSec NAT-T'), (5044, 'Logstash Beats'), (5432, 'PostgreSQL'), (5601, 'Kibana'), (6379, 'Redis'), (8096, 'Jellyfin'), (8123, 'Home Assistant'), (8384, 'Syncthing UI'), (8920, 'Jellyfin HTTPS'), (9000, 'Portainer'), (9090, 'Prometheus'), (9100, 'node_exporter / JetDirect'), (9200, 'Elasticsearch'), (9443, 'Portainer HTTPS'), (22000, 'Syncthing'), (51820, 'WireGuard'); -- ── Host ports ──────────────────────────────────────────────────────────────── -- INSERT OR IGNORE is safe: host_ports has a composite PRIMARY KEY (host_id, port_number). -- Host IDs are resolved by subquery on (name, ip) to stay independent of auto-increment values. -- gateway: SSH, DNS, HTTP (admin UI), HTTPS (admin UI) INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'gateway' AND ip = '192.168.1.1'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 53 FROM hosts WHERE name = 'gateway' AND ip = '192.168.1.1'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 80 FROM hosts WHERE name = 'gateway' AND ip = '192.168.1.1'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 443 FROM hosts WHERE name = 'gateway' AND ip = '192.168.1.1'; -- workstations: SSH, RDP INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'workstation-01' AND ip = '192.168.1.10'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 3389 FROM hosts WHERE name = 'workstation-01' AND ip = '192.168.1.10'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'workstation-02' AND ip = '192.168.1.11'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 3389 FROM hosts WHERE name = 'workstation-02' AND ip = '192.168.1.11'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'workstation-03' AND ip = '192.168.1.12'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 3389 FROM hosts WHERE name = 'workstation-03' AND ip = '192.168.1.12'; -- nas-01: SSH, HTTP (web UI), HTTPS, SMB, NFS INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'nas-01' AND ip = '192.168.1.20'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 80 FROM hosts WHERE name = 'nas-01' AND ip = '192.168.1.20'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 443 FROM hosts WHERE name = 'nas-01' AND ip = '192.168.1.20'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 445 FROM hosts WHERE name = 'nas-01' AND ip = '192.168.1.20'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 2049 FROM hosts WHERE name = 'nas-01' AND ip = '192.168.1.20'; -- printer-01: HTTP (web UI), HTTPS, JetDirect INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 80 FROM hosts WHERE name = 'printer-01' AND ip = '192.168.1.50'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 443 FROM hosts WHERE name = 'printer-01' AND ip = '192.168.1.50'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 9100 FROM hosts WHERE name = 'printer-01' AND ip = '192.168.1.50'; -- web servers: SSH, HTTP, HTTPS INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'web-server-01' AND ip = '192.168.10.10'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 80 FROM hosts WHERE name = 'web-server-01' AND ip = '192.168.10.10'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 443 FROM hosts WHERE name = 'web-server-01' AND ip = '192.168.10.10'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'web-server-02' AND ip = '192.168.10.11'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 80 FROM hosts WHERE name = 'web-server-02' AND ip = '192.168.10.11'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 443 FROM hosts WHERE name = 'web-server-02' AND ip = '192.168.10.11'; -- db-server-01: SSH, PostgreSQL INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'db-server-01' AND ip = '192.168.10.20'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 5432 FROM hosts WHERE name = 'db-server-01' AND ip = '192.168.10.20'; -- mail-server-01: SSH, SMTP, IMAP, SMTPS, SMTP Submission, IMAPS INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 25 FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 143 FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 465 FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 587 FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 993 FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30'; -- core-switch-01: SSH, SNMP INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'core-switch-01' AND ip = '10.0.0.1'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 161 FROM hosts WHERE name = 'core-switch-01' AND ip = '10.0.0.1'; -- monitoring-01: SSH, HTTP, HTTPS, Grafana, Prometheus INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'monitoring-01' AND ip = '10.0.1.10'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 80 FROM hosts WHERE name = 'monitoring-01' AND ip = '10.0.1.10'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 443 FROM hosts WHERE name = 'monitoring-01' AND ip = '10.0.1.10'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 3000 FROM hosts WHERE name = 'monitoring-01' AND ip = '10.0.1.10'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 9090 FROM hosts WHERE name = 'monitoring-01' AND ip = '10.0.1.10'; -- backup-server-01: SSH, SMB, rsync INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'backup-server-01' AND ip = '10.0.1.20'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 445 FROM hosts WHERE name = 'backup-server-01' AND ip = '10.0.1.20'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 873 FROM hosts WHERE name = 'backup-server-01' AND ip = '10.0.1.20'; -- log-server-01: SSH, Syslog, Logstash Beats, Elasticsearch, Kibana INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'log-server-01' AND ip = '10.0.1.30'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 514 FROM hosts WHERE name = 'log-server-01' AND ip = '10.0.1.30'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 5044 FROM hosts WHERE name = 'log-server-01' AND ip = '10.0.1.30'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 9200 FROM hosts WHERE name = 'log-server-01' AND ip = '10.0.1.30'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 5601 FROM hosts WHERE name = 'log-server-01' AND ip = '10.0.1.30'; -- vpn-gateway-01: SSH, IKE, IPSec NAT-T, OpenVPN INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'vpn-gateway-01' AND ip = '172.16.0.1'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 500 FROM hosts WHERE name = 'vpn-gateway-01' AND ip = '172.16.0.1'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 1194 FROM hosts WHERE name = 'vpn-gateway-01' AND ip = '172.16.0.1'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 4500 FROM hosts WHERE name = 'vpn-gateway-01' AND ip = '172.16.0.1'; -- vpn clients: SSH only INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'vpn-client-01' AND ip = '172.16.1.10'; INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'vpn-client-02' AND ip = '172.16.1.11'; -- ── Applications ────────────────────────────────────────────────────────────── -- applications has no UNIQUE constraint on name, so we use WHERE NOT EXISTS. INSERT INTO applications (name) SELECT 'Nginx' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'Nginx'); INSERT INTO applications (name) SELECT 'Pi-hole' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'Pi-hole'); INSERT INTO applications (name) SELECT 'WireGuard' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'WireGuard'); INSERT INTO applications (name) SELECT 'OpenVPN' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'OpenVPN'); INSERT INTO applications (name) SELECT 'PostgreSQL' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'PostgreSQL'); INSERT INTO applications (name) SELECT 'MariaDB' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'MariaDB'); INSERT INTO applications (name) SELECT 'Redis' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'Redis'); INSERT INTO applications (name) SELECT 'Grafana' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'Grafana'); INSERT INTO applications (name) SELECT 'Prometheus' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'Prometheus'); INSERT INTO applications (name) SELECT 'Elasticsearch' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'Elasticsearch'); INSERT INTO applications (name) SELECT 'Kibana' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'Kibana'); INSERT INTO applications (name) SELECT 'Portainer' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'Portainer'); INSERT INTO applications (name) SELECT 'Jellyfin' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'Jellyfin'); INSERT INTO applications (name) SELECT 'Home Assistant' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'Home Assistant'); INSERT INTO applications (name) SELECT 'Syncthing' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'Syncthing'); INSERT INTO applications (name) SELECT 'Vaultwarden' WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = 'Vaultwarden'); -- ── Application ports ───────────────────────────────────────────────────────── -- application_ports has a composite PRIMARY KEY, so INSERT OR IGNORE is safe. -- Nginx: HTTP, HTTPS INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 80 FROM applications WHERE name = 'Nginx'; INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 443 FROM applications WHERE name = 'Nginx'; -- Pi-hole: DNS, HTTP (admin UI), HTTPS INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 53 FROM applications WHERE name = 'Pi-hole'; INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 80 FROM applications WHERE name = 'Pi-hole'; INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 443 FROM applications WHERE name = 'Pi-hole'; -- WireGuard INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 51820 FROM applications WHERE name = 'WireGuard'; -- OpenVPN INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 1194 FROM applications WHERE name = 'OpenVPN'; -- PostgreSQL INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 5432 FROM applications WHERE name = 'PostgreSQL'; -- MariaDB INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 3306 FROM applications WHERE name = 'MariaDB'; -- Redis INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 6379 FROM applications WHERE name = 'Redis'; -- Grafana INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 3000 FROM applications WHERE name = 'Grafana'; -- Prometheus INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 9090 FROM applications WHERE name = 'Prometheus'; -- Elasticsearch INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 9200 FROM applications WHERE name = 'Elasticsearch'; -- Kibana INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 5601 FROM applications WHERE name = 'Kibana'; -- Portainer: HTTP, HTTPS INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 9000 FROM applications WHERE name = 'Portainer'; INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 9443 FROM applications WHERE name = 'Portainer'; -- Jellyfin: HTTP, HTTPS INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 8096 FROM applications WHERE name = 'Jellyfin'; INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 8920 FROM applications WHERE name = 'Jellyfin'; -- Home Assistant INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 8123 FROM applications WHERE name = 'Home Assistant'; -- Syncthing: UI, data sync INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 8384 FROM applications WHERE name = 'Syncthing'; INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 22000 FROM applications WHERE name = 'Syncthing'; -- Vaultwarden: HTTP, HTTPS INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 80 FROM applications WHERE name = 'Vaultwarden'; INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 443 FROM applications WHERE name = 'Vaultwarden';