smolkik_adm/----
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
----/backend/migrations/002_seed_data.sql
smolkik-code 9b15f8b09c feat: CMDB full-stack app - FastAPI + PostgreSQL + React 
- PostgreSQL schema: 14 tables, JSONB attributes, audit triggers, soft delete
- FastAPI backend: CRUD, search/filter, relationship graph, bulk import, JWT RBAC
- React frontend: CI table, detail card, force-graph, dashboard
- Seed data: homelab scenario (Proxmox, Mikrotik, VMs, services)
- Docker Compose + Kubernetes manifests
- 20 backend tests (pytest + httpx)
2026-06-25 13:01:40 +07:00

179 lines
18 KiB
PL/PgSQL
Raw Permalink Blame History

-- ============================================================
-- CMDB Seed Data — Homelab Scenario
-- 5 hosts, 10 VMs, 3 switches, 1 NAS, 5 services
-- ============================================================
BEGIN;
-- -----------------------------------------------------------
-- Users
-- -----------------------------------------------------------
INSERT INTO users (id, username, email, full_name, role, team, password_hash) VALUES
('a0000000-0000-0000-0000-000000000001', 'admin', 'admin@homelab.local', 'Admin User', 'admin', 'IT', crypt('admin123', gen_salt('bf'))),
('a0000000-0000-0000-0000-000000000002', 'operator','ops@homelab.local', 'Ops Engineer', 'editor', 'Operations', crypt('ops123', gen_salt('bf'))),
('a0000000-0000-0000-0000-000000000003', 'viewer', 'viewer@homelab.local', 'Read Only', 'viewer', 'External', crypt('view123', gen_salt('bf')));
-- -----------------------------------------------------------
-- Locations
-- -----------------------------------------------------------
INSERT INTO locations (id, name, description) VALUES
('b0000000-0000-0000-0000-000000000001', 'Homelab Rack 1', 'Main 42U rack in basement'),
('b0000000-0000-0000-0000-000000000002', 'Office Desk', 'Home office desk area'),
('b0000000-0000-0000-0000-000000000003', 'Cloud — Hetzner', 'Hetzner CX31 VPS');
-- -----------------------------------------------------------
-- CI Classes & Types
-- -----------------------------------------------------------
INSERT INTO ci_classes (id, name, description, icon) VALUES
('c0000000-0000-0000-0000-000000000001', 'Hardware', 'Physical hardware devices', 'server'),
('c0000000-0000-0000-0000-000000000002', 'NetworkDevice', 'Network infrastructure', 'router'),
('c0000000-0000-0000-0000-000000000003', 'Software', 'Software and services', 'software'),
('c0000000-0000-0000-0000-000000000004', 'Storage', 'Storage systems', 'storage');
INSERT INTO ci_types (id, class_id, name, description) VALUES
('d0000000-0000-0000-0000-000000000001', 'c0000000-0000-0000-0000-000000000001', 'PhysicalServer', 'Bare-metal server'),
('d0000000-0000-0000-0000-000000000002', 'c0000000-0000-0000-0000-000000000001', 'VirtualMachine', 'Virtual machine'),
('d0000000-0000-0000-0000-000000000003', 'c0000000-0000-0000-0000-000000000002', 'Switch', 'Network switch'),
('d0000000-0000-0000-0000-000000000004', 'c0000000-0000-0000-0000-000000000002', 'Router', 'Router / firewall'),
('d0000000-0000-0000-0000-000000000005', 'c0000000-0000-0000-0000-000000000003', 'Application', 'Application / service'),
('d0000000-0000-0000-0000-000000000006', 'c0000000-0000-0000-0000-000000000004', 'NAS', 'Network attached storage');
-- -----------------------------------------------------------
-- Physical Servers (5)
-- -----------------------------------------------------------
INSERT INTO configuration_items (id, ci_type_id, name, description, status, location_id, attributes, tags) VALUES
('e0000000-0000-0000-0000-000000000001', 'd0000000-0000-0000-0000-000000000001', 'proxmox-01', 'Primary Proxmox VE host', 'active', 'b0000000-0000-0000-0000-000000000001', '{"os": "Proxmox VE 8.1", "cpu": "Intel Xeon E-2278G", "ram_gb": 64, "storage_tb": 2.0}', ARRAY['proxmox','production']),
('e0000000-0000-0000-0000-000000000002', 'd0000000-0000-0000-0000-000000000001', 'proxmox-02', 'Secondary Proxmox VE host', 'active', 'b0000000-0000-0000-0000-000000000001', '{"os": "Proxmox VE 8.1", "cpu": "Intel Xeon E-2278G", "ram_gb": 64, "storage_tb": 4.0}', ARRAY['proxmox','production']),
('e0000000-0000-0000-0000-000000000003', 'd0000000-0000-0000-0000-000000000001', 'esxi-01', 'VMware ESXi host (legacy)', 'maintenance', 'b0000000-0000-0000-0000-000000000001', '{"os": "ESXi 7.0U3", "cpu": "AMD EPYC 7302P", "ram_gb": 128, "storage_tb": 8.0}', ARRAY['vmware','legacy']),
('e0000000-0000-0000-0000-000000000004', 'd0000000-0000-0000-0000-000000000001', 'backup-nas', 'Synology NAS for backups', 'active', 'b0000000-0000-0000-0000-000000000001', '{"os": "DSM 7.2", "cpu": "Intel Celeron J4125", "ram_gb": 8, "storage_tb": 16.0}', ARRAY['synology','backup']),
('e0000000-0000-0000-0000-000000000005', 'd0000000-0000-0000-0000-000000000001', 'hetzner-01', 'Hetzner Cloud CX31', 'active', 'b0000000-0000-0000-0000-000000000003', '{"os": "Ubuntu 22.04", "cpu": "AMD EPYC (shared 4 vCPU)", "ram_gb": 8, "storage_gb": 160}', ARRAY['cloud','hetzner']);
-- -----------------------------------------------------------
-- Virtual Machines (10)
-- -----------------------------------------------------------
INSERT INTO configuration_items (id, ci_type_id, name, description, status, location_id, attributes, tags) VALUES
('e0000000-0000-0000-0000-000000000010', 'd0000000-0000-0000-0000-000000000002', 'vm-plex', 'Plex Media Server', 'active', 'b0000000-0000-0000-0000-000000000001', '{"os": "Ubuntu 22.04", "cpu_cores": 4, "ram_gb": 16, "disk_gb": 500}', ARRAY['plex','media']),
('e0000000-0000-0000-0000-000000000011', 'd0000000-0000-0000-0000-000000000002', 'vm-nextcloud', 'Nextcloud file sync', 'active', 'b0000000-0000-0000-0000-000000000001', '{"os": "Ubuntu 22.04", "cpu_cores": 2, "ram_gb": 4, "disk_gb": 100}', ARRAY['nextcloud','files']),
('e0000000-0000-0000-0000-000000000012', 'd0000000-0000-0000-0000-000000000002', 'vm-gitlab', 'GitLab CE instance', 'active', 'b0000000-0000-0000-0000-000000000001', '{"os": "Ubuntu 22.04", "cpu_cores": 4, "ram_gb": 8, "disk_gb": 200}', ARRAY['gitlab','dev']),
('e0000000-0000-0000-0000-000000000013', 'd0000000-0000-0000-0000-000000000002', 'vm-monitoring', 'Prometheus + Grafana', 'active', 'b0000000-0000-0000-0000-000000000001', '{"os": "Ubuntu 22.04", "cpu_cores": 2, "ram_gb": 8, "disk_gb": 100}', ARRAY['monitoring','observability']),
('e0000000-0000-0000-0000-000000000014', 'd0000000-0000-0000-0000-000000000002', 'vm-dns', 'Pi-hole / AdGuard DNS', 'active', 'b0000000-0000-0000-0000-000000000001', '{"os": "Debian 12", "cpu_cores": 1, "ram_gb": 2, "disk_gb": 20}', ARRAY['dns','network']),
('e0000000-0000-0000-0000-000000000015', 'd0000000-0000-0000-0000-000000000002', 'vm-docker', 'Docker workload host', 'active', 'b0000000-0000-0000-0000-000000000001', '{"os": "Ubuntu 22.04", "cpu_cores": 4, "ram_gb": 16, "disk_gb": 250}', ARRAY['docker','containers']),
('e0000000-0000-0000-0000-000000000016', 'd0000000-0000-0000-0000-000000000002', 'vm-homeassistant','Home Assistant', 'active', 'b0000000-0000-0000-0000-000000000001', '{"os": "HAOS", "cpu_cores": 2, "ram_gb": 4, "disk_gb": 60}', ARRAY['homeassistant','iot']),
('e0000000-0000-0000-0000-000000000017', 'd0000000-0000-0000-0000-000000000002', 'vm-gaming', 'Windows gaming VM', 'inactive', 'b0000000-0000-0000-0000-000000000001', '{"os": "Windows 11", "cpu_cores": 8, "ram_gb": 32, "disk_gb": 500}', ARRAY['windows','gaming']),
('e0000000-0000-0000-0000-000000000018', 'd0000000-0000-0000-0000-000000000002', 'vm-jenkins', 'CI/CD Jenkins', 'active', 'b0000000-0000-0000-0000-000000000001', '{"os": "Ubuntu 22.04", "cpu_cores": 2, "ram_gb": 4, "disk_gb": 50}', ARRAY['jenkins','cicd']),
('e0000000-0000-0000-0000-000000000019', 'd0000000-0000-0000-0000-000000000002', 'vm-vaultwarden', 'Vaultwarden password mgr', 'active', 'b0000000-0000-0000-0000-000000000001', '{"os": "Debian 12", "cpu_cores": 1, "ram_gb": 2, "disk_gb": 10}', ARRAY['vaultwarden','security']);
-- -----------------------------------------------------------
-- Network Devices (3 — Mikrotik + 1 switch)
-- -----------------------------------------------------------
INSERT INTO configuration_items (id, ci_type_id, name, description, status, location_id, attributes, tags) VALUES
('e0000000-0000-0000-0000-000000000020', 'd0000000-0000-0000-0000-000000000004', 'mikrotik-core', 'MikroTik CCR1036 core router', 'active', 'b0000000-0000-0000-0000-000000000001', '{"firmware": "RouterOS 7.12", "ports": 36, "throughput_gbps": 36}', ARRAY['mikrotik','router']),
('e0000000-0000-0000-0000-000000000021', 'd0000000-0000-0000-0000-000000000003', 'mikrotik-access', 'MikroTik CRS328 access switch', 'active', 'b0000000-0000-0000-0000-000000000001', '{"firmware": "RouterOS 7.12", "ports": 28, "type": "managed"}', ARRAY['mikrotik','switch']),
('e0000000-0000-0000-0000-000000000022', 'd0000000-0000-0000-0000-000000000004', 'mikrotik-ap', 'MikroTik hAP ac² WiFi AP', 'active', 'b0000000-0000-0000-0000-000000000002', '{"firmware": "RouterOS 7.12", "wifi_standard": "802.11ac"}', ARRAY['mikrotik','wifi']);
-- -----------------------------------------------------------
-- NAS
-- -----------------------------------------------------------
INSERT INTO configuration_items (id, ci_type_id, name, description, status, location_id, attributes, tags) VALUES
('e0000000-0000-0000-0000-000000000030', 'd0000000-0000-0000-0000-000000000006', 'synology-ds920', 'Synology DS920+ NAS', 'active', 'b0000000-0000-0000-0000-000000000001', '{"model": "DS920+", "disks": 4, "total_tb": 16, "raid": "SHR-2"}', ARRAY['synology','nas']);
-- -----------------------------------------------------------
-- Software Instances (5 services)
-- -----------------------------------------------------------
INSERT INTO configuration_items (id, ci_type_id, name, description, status, attributes, tags) VALUES
('e0000000-0000-0000-0000-000000000040', 'd0000000-0000-0000-0000-000000000005', 'svc-plex', 'Plex Media Server', 'active', '{"port": 32400, "protocol": "tcp", "version": "1.40.0"}', ARRAY['plex','media']),
('e0000000-0000-0000-0000-000000000041', 'd0000000-0000-0000-0000-000000000005', 'svc-nextcloud', 'Nextcloud WebDAV', 'active', '{"port": 443, "protocol": "tcp", "version": "28.0"}', ARRAY['nextcloud','files']),
('e0000000-0000-0000-0000-000000000042', 'd0000000-0000-0000-0000-000000000005', 'svc-gitlab', 'GitLab CE', 'active', '{"port": 443, "protocol": "tcp", "version": "16.8"}', ARRAY['gitlab','dev']),
('e0000000-0000-0000-0000-000000000043', 'd0000000-0000-0000-0000-000000000005', 'svc-prometheus', 'Prometheus monitoring', 'active', '{"port": 9090, "protocol": "tcp", "version": "2.49"}', ARRAY['prometheus','monitoring']),
('e0000000-0000-0000-0000-000000000044', 'd0000000-0000-0000-0000-000000000005', 'svc-grafana', 'Grafana dashboards', 'active', '{"port": 3000, "protocol": "tcp", "version": "10.3"}', ARRAY['grafana','monitoring']);
-- -----------------------------------------------------------
-- IP Addresses
-- -----------------------------------------------------------
INSERT INTO ip_addresses (ci_id, ip_address, subnet_mask, gateway, is_primary, vlan_id) VALUES
('e0000000-0000-0000-0000-000000000001', '10.0.0.11/24', '255.255.255.0', '10.0.0.1', true, 10),
('e0000000-0000-0000-0000-000000000002', '10.0.0.12/24', '255.255.255.0', '10.0.0.1', true, 10),
('e0000000-0000-0000-0000-000000000003', '10.0.0.13/24', '255.255.255.0', '10.0.0.1', true, 10),
('e0000000-0000-0000-0000-000000000004', '10.0.0.14/24', '255.255.255.0', '10.0.0.1', true, 10),
('e0000000-0000-0000-0000-000000000005', '203.0.113.50/24','255.255.255.0', '203.0.113.1', true, NULL),
('e0000000-0000-0000-0000-000000000020', '10.0.0.1/24', '255.255.255.0', NULL, true, 10),
('e0000000-0000-0000-0000-000000000021', '10.0.0.2/24', '255.255.255.0', '10.0.0.1', true, 10),
('e0000000-0000-0000-0000-000000000022', '10.0.0.3/24', '255.255.255.0', '10.0.0.1', true, 20),
('e0000000-0000-0000-0000-000000000030', '10.0.0.15/24', '255.255.255.0', '10.0.0.1', true, 10);
-- VMs get DHCP from bridge
INSERT INTO ip_addresses (ci_id, ip_address, is_primary, vlan_id) VALUES
('e0000000-0000-0000-0000-000000000010', '10.0.0.101/24', true, 10),
('e0000000-0000-0000-0000-000000000011', '10.0.0.102/24', true, 10),
('e0000000-0000-0000-0000-000000000012', '10.0.0.103/24', true, 10),
('e0000000-0000-0000-0000-000000000013', '10.0.0.104/24', true, 10),
('e0000000-0000-0000-0000-000000000014', '10.0.0.105/24', true, 10),
('e0000000-0000-0000-0000-000000000015', '10.0.0.106/24', true, 10),
('e0000000-0000-0000-0000-000000000016', '10.0.0.107/24', true, 10),
('e0000000-0000-0000-0000-000000000017', '10.0.0.108/24', true, 10),
('e0000000-0000-0000-0000-000000000018', '10.0.0.109/24', true, 10),
('e0000000-0000-0000-0000-000000000019', '10.0.0.110/24', true, 10);
-- -----------------------------------------------------------
-- Hardware Details (physical servers + NAS)
-- -----------------------------------------------------------
INSERT INTO hardware_details (ci_id, manufacturer, model, cpu_model, cpu_cores, ram_gb, storage_gb, storage_type, form_factor, specs) VALUES
('e0000000-0000-0000-0000-000000000001', 'SuperMicro', 'X11SCL-IF', 'Intel Xeon E-2278G', 8, 64, 2000, 'NVMe', '1U', '{"ipmi": true, "gbe_ports": 2}'),
('e0000000-0000-0000-0000-000000000002', 'SuperMicro', 'X11SCL-IF', 'Intel Xeon E-2278G', 8, 64, 4000, 'NVMe', '1U', '{"ipmi": true, "gbe_ports": 2}'),
('e0000000-0000-0000-0000-000000000003', 'Dell', 'R740xd', 'AMD EPYC 7302P', 16, 128, 8000, 'SSD', '2U', '{"idrac": true, "gbe_ports": 4}'),
('e0000000-0000-0000-0000-000000000030', 'Synology', 'DS920+', 'Intel Celeron J4125', 4, 8, 16000, 'HDD', 'Desktop', '{"expandable": true, "nvme_cache": true}');
-- -----------------------------------------------------------
-- Network Interfaces (sample for hosts)
-- -----------------------------------------------------------
INSERT INTO network_interfaces (ci_id, name, mac_address, speed_mbps, interface_type, is_up) VALUES
('e0000000-0000-0000-0000-000000000001', 'enp1s0', 'AA:BB:CC:01:00:01', 1000, 'ethernet', true),
('e0000000-0000-0000-0000-000000000001', 'enp2s0', 'AA:BB:CC:01:00:02', 1000, 'ethernet', true),
('e0000000-0000-0000-0000-000000000002', 'enp1s0', 'AA:BB:CC:02:00:01', 1000, 'ethernet', true),
('e0000000-0000-0000-0000-000000000020', 'ether1', 'CC:DD:EE:01:00:01', 10000, 'ethernet', true),
('e0000000-0000-0000-0000-000000000020', 'bond1', 'CC:DD:EE:01:00:02', 20000, 'bond', true);
-- -----------------------------------------------------------
-- CI Relationships
-- -----------------------------------------------------------
-- VMs hosted on physical hosts
INSERT INTO ci_relationships (source_ci_id, target_ci_id, relationship, description) VALUES
('e0000000-0000-0000-0000-000000000010', 'e0000000-0000-0000-0000-000000000001', 'hosted_on', 'Plex runs on proxmox-01'),
('e0000000-0000-0000-0000-000000000011', 'e0000000-0000-0000-0000-000000000001', 'hosted_on', 'Nextcloud on proxmox-01'),
('e0000000-0000-0000-0000-000000000012', 'e0000000-0000-0000-0000-000000000001', 'hosted_on', 'GitLab on proxmox-01'),
('e0000000-0000-0000-0000-000000000013', 'e0000000-0000-0000-0000-000000000002', 'hosted_on', 'Monitoring on proxmox-02'),
('e0000000-0000-0000-0000-000000000014', 'e0000000-0000-0000-0000-000000000001', 'hosted_on', 'DNS on proxmox-01'),
('e0000000-0000-0000-0000-000000000015', 'e0000000-0000-0000-0000-000000000002', 'hosted_on', 'Docker host on proxmox-02'),
('e0000000-0000-0000-0000-000000000016', 'e0000000-0000-0000-0000-000000000001', 'hosted_on', 'HA on proxmox-01'),
('e0000000-0000-0000-0000-000000000017', 'e0000000-0000-0000-0000-000000000003', 'hosted_on', 'Gaming VM on esxi-01'),
('e0000000-0000-0000-0000-000000000018', 'e0000000-0000-0000-0000-000000000002', 'hosted_on', 'Jenkins on proxmox-02'),
('e0000000-0000-0000-0000-000000000019', 'e0000000-0000-0000-0000-000000000002', 'hosted_on', 'Vaultwarden on proxmox-02');
-- Services depend on VMs
INSERT INTO ci_relationships (source_ci_id, target_ci_id, relationship, description) VALUES
('e0000000-0000-0000-0000-000000000040', 'e0000000-0000-0000-0000-000000000010', 'runs_on', 'Plex service on vm-plex'),
('e0000000-0000-0000-0000-000000000041', 'e0000000-0000-0000-0000-000000000011', 'runs_on', 'Nextcloud on vm-nextcloud'),
('e0000000-0000-0000-0000-000000000042', 'e0000000-0000-0000-0000-000000000012', 'runs_on', 'GitLab on vm-gitlab'),
('e0000000-0000-0000-0000-000000000043', 'e0000000-0000-0000-0000-000000000013', 'runs_on', 'Prometheus on vm-monitoring'),
('e0000000-0000-0000-0000-000000000044', 'e0000000-0000-0000-0000-000000000013', 'runs_on', 'Grafana on vm-monitoring');
-- Network relationships
INSERT INTO ci_relationships (source_ci_id, target_ci_id, relationship, description) VALUES
('e0000000-0000-0000-0000-000000000001', 'e0000000-0000-0000-0000-000000000020', 'connected_to', 'proxmox-01 to core router'),
('e0000000-0000-0000-0000-000000000002', 'e0000000-0000-0000-0000-000000000020', 'connected_to', 'proxmox-02 to core router'),
('e0000000-0000-0000-0000-000000000020', 'e0000000-0000-0000-0000-000000000021', 'connected_to', 'Core router to access switch'),
('e0000000-0000-0000-0000-000000000021', 'e0000000-0000-0000-0000-000000000022', 'connected_to', 'Access switch to WiFi AP');
-- Backup dependencies
INSERT INTO ci_relationships (source_ci_id, target_ci_id, relationship, description) VALUES
('e0000000-0000-0000-0000-000000000004', 'e0000000-0000-0000-0000-000000000001', 'depends_on', 'NAS backs up proxmox-01'),
('e0000000-0000-0000-0000-000000000004', 'e0000000-0000-0000-0000-000000000002', 'depends_on', 'NAS backs up proxmox-02');
-- Owners
INSERT INTO owners (ci_id, user_id, role) VALUES
('e0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000001', 'owner'),
('e0000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000001', 'owner'),
('e0000000-0000-0000-0000-000000000043', 'a0000000-0000-0000-0000-000000000002', 'responsible'),
('e0000000-0000-0000-0000-000000000044', 'a0000000-0000-0000-0000-000000000002', 'responsible');
COMMIT;
Reference in New Issue View Git Blame Copy Permalink