# CMDB — Configuration Management Database

> Full-stack CMDB for corporate/homelab environments.
> **Stack:** FastAPI · PostgreSQL 16 · React 18 (MUI 5) · Docker · Kubernetes

---

## Table of Contents

- [Architecture](#architecture)
- [Features](#features)
- [Quick Start](#quick-start)
- [Local Development](#local-development)
- [Project Structure](#project-structure)
- [Database Schema](#database-schema)
- [API Reference](#api-reference)
- [Frontend](#frontend)
- [Testing](#testing)
- [Deployment](#deployment)
- [Security](#security)
- [Backup & Recovery](#backup--recovery)
- [Ansible Integration](#ansible-integration)
- [Roadmap](#roadmap)

---

## Architecture

```
                        ┌─────────────────────────────────────────┐
                        │              Nginx :80                   │
                        │         (reverse proxy)                 │
                        └──────────┬──────────────┬───────────────┘
                                   │              │
                           /api/*  │              │  /*
                                   ▼              ▼
                        ┌──────────────┐  ┌──────────────────┐
                        │   FastAPI    │  │   React (MUI)    │
                        │   :8000      │  │   :3000           │
                        │   async      │  │   Vite dev server │
                        └──────┬───────┘  └──────────────────┘
                               │
                        ┌──────▼───────┐
                        │  PostgreSQL   │
                        │  :5432        │
                        │  JSONB + INET │
                        └──────────────┘
```

## Features

| Layer | Highlights |
|-------|-----------|
| **Database** | 14 tables, JSONB for extensible attributes, INET for IPs, soft delete, audit triggers, versioning |
| **Backend** | JWT auth, RBAC (admin/editor/viewer), CRUD, search/filter, relationship graph (N-level), bulk import (JSON), CSV export, rate limiting |
| **Frontend** | Dark theme, server-side pagination, inline FK editing, force-directed graph, dashboard with stats, responsive layout |
| **DevOps** | Docker Compose, Kubernetes manifests, seed data (homelab), Ansible integration playbook |

---

## Quick Start

### Docker Compose (recommended)

```bash
git clone http://10.0.1.48:3010/smolkik_adm/----.git
cd ----

docker-compose up -d

# Migrations auto-apply via /docker-entrypoint-initdb.d/
# If not — apply manually:
docker exec -i cmdb-postgres psql -U cmdb -d cmdb < backend/migrations/001_initial_schema.sql
docker exec -i cmdb-postgres psql -U cmdb -d cmdb < backend/migrations/002_seed_data.sql
```

| Service | URL |
|---------|-----|
| Frontend | http://localhost |
| API Docs (Swagger) | http://localhost/api/docs |
| API Docs (ReDoc) | http://localhost/api/redoc |
| PostgreSQL | localhost:5432 |

**Default login:** `admin` / `admin123`

---

## Local Development

### Prerequisites

- Python 3.12+
- Node.js 20+
- PostgreSQL 16+

### Backend

```bash
cd backend

python -m venv .venv
source .venv/bin/activate        # Linux/macOS
# .venv\Scripts\activate         # Windows

pip install -r requirements.txt

# Start PostgreSQL (Docker one-liner)
docker run -d --name cmdb-pg -p 5432:5432 \
  -e POSTGRES_DB=cmdb -e POSTGRES_USER=cmdb -e POSTGRES_PASSWORD=cmdb_secret \
  postgres:16-alpine

# Apply migrations
psql -h localhost -U cmdb -d cmdb < migrations/001_initial_schema.sql
psql -h localhost -U cmdb -d cmdb < migrations/002_seed_data.sql

# Copy env and start
cp .env.example .env
uvicorn app.main:app --reload --port 8000
```

### Frontend

```bash
cd frontend
npm install
npm run dev
# → http://localhost:5173 (proxies /api → localhost:8000)
```

---

## Project Structure

```
cmdb-app/
├── backend/
│   ├── app/
│   │   ├── config.py               # Pydantic Settings (.env loading)
│   │   ├── database.py             # AsyncSession, engine, get_db
│   │   ├── main.py                 # FastAPI app, middleware, routers
│   │   ├── middleware/
│   │   │   ├── auth.py             # JWT encode/decode, RBAC dependencies
│   │   │   └── rate_limit.py       # Per-IP sliding window rate limiter
│   │   ├── models/
│   │   │   └── models.py           # SQLAlchemy ORM (14 tables)
│   │   ├── routes/
│   │   │   ├── auth.py             # POST /login, POST /users, GET /me
│   │   │   ├── ci.py               # CRUD, search, graph, bulk, export
│   │   │   ├── dashboard.py        # GET /stats
│   │   │   └── reference.py        # classes, types, locations
│   │   └── schemas/
│   │       └── schemas.py          # Pydantic v2 request/response models
│   ├── migrations/
│   │   ├── 001_initial_schema.sql  # DDL: tables, indexes, triggers
│   │   ├── 002_seed_data.sql       # 25+ homelab CIs
│   │   └── 003_audit_triggers.sql  # Fine-grained field audit (production)
│   ├── tests/
│   │   └── test_api.py             # 20 tests (pytest + httpx)
│   ├── Dockerfile
│   ├── requirements.txt
│   └── .env.example
├── frontend/
│   ├── src/
│   │   ├── services/api.ts         # Axios client, TypeScript interfaces
│   │   ├── components/
│   │   │   └── Layout.tsx          # Sidebar + AppBar shell
│   │   └── pages/
│   │       ├── LoginPage.tsx        # JWT authentication form
│   │       ├── DashboardPage.tsx    # Stats cards, status/class breakdown
│   │       ├── CIListPage.tsx       # Table + filters + pagination + CRUD
│   │       ├── CIDetailPage.tsx     # Tabs: details, IP, NIC, HW, SW, relations
│   │       └── GraphPage.tsx        # Canvas force-directed graph
│   ├── Dockerfile
│   ├── package.json
│   └── vite.config.ts
├── k8s/
│   ├── postgres.yaml                # Deployment + PVC + Service
│   ├── backend.yaml                 # Deployment + Service + Secret
│   └── frontend.yaml                # Deployment + Service + Ingress
├── docs/
│   ├── ansible-integration.md       # Auto-import from Ansible inventory
│   └── security.md                  # Hardening, backups, monitoring
├── docker-compose.yml
├── docker-compose.prod.yml
├── nginx.conf
└── README.md
```

---

## Database Schema

### Tables (14)

| Table | Description | Key Features |
|-------|-------------|--------------|
| `configuration_items` | Core entity | JSONB `attributes`, array `tags`, `version`, soft delete |
| `ci_classes` | Taxonomy root (Hardware, Network, Software, Storage) | Self-referencing `parent_id` |
| `ci_types` | Subtypes (PhysicalServer, VM, Switch, Router, Application, NAS) | FK → ci_classes |
| `ci_relationships` | Graph edges | 8 relationship types, JSONB metadata |
| `changelog` | Audit trail + versioning | Auto-populated by PostgreSQL triggers |
| `ip_addresses` | IP assignments | Native `INET` type, VLAN, gateway |
| `network_interfaces` | NIC details | `MACADDR` type, speed, bond/bridge/vlan |
| `hardware_details` | Physical specs | CPU, RAM, storage, JSONB `specs` |
| `software_instances` | Installed software | Version, port, protocol, JSONB `config` |
| `locations` | Physical hierarchy | Self-referencing (rack → room → building) |
| `users` | Accounts | RBAC enum: admin, editor, viewer |
| `owners` | CI ↔ User mapping | Many-to-many with role (owner, responsible) |

### ER Diagram

```
ci_classes ──1:N──▶ ci_types ──1:N──▶ configuration_items
                                              │
            ┌─────────────┬───────────┬───────┼────────┬──────────────┐
            ▼             ▼           ▼       ▼        ▼              ▼
      ip_addresses  network_iface  hw_detail  sw_inst  ci_relationships  owners
                                                           │              │
                                                      (self FK)      users (FK)

locations (self FK) ◀── configuration_items.location_id
changelog ◀── configuration_items.id (audit trail)
```

### Key Design Decisions

| Decision | Rationale |
|----------|-----------|
| **Soft delete** (`deleted_at`) | Never lose data; restore capability; audit compliance |
| **JSONB attributes** | Extensible per-class fields without schema changes |
| **UUID primary keys** | Safe for multi-instance, no sequential leak |
| **INET type** | Native PostgreSQL IP handling, subnet operations |
| **Audit triggers** | Automatic changelog on every UPDATE/DELETE |
| **Version column** | Optimistic concurrency control |

### Seed Data (Homelab)

| Category | Items |
|----------|-------|
| Physical hosts | proxmox-01, proxmox-02, esxi-01, backup-nas, hetzner-01 |
| Virtual machines | vm-plex, vm-nextcloud, vm-gitlab, vm-monitoring, vm-dns, vm-docker, vm-homeassistant, vm-gaming, vm-jenkins, vm-vaultwarden |
| Network devices | mikrotik-core (CCR1036), mikrotik-access (CRS328), mikrotik-ap (hAP ac²) |
| Storage | synology-ds920 (DS920+ NAS) |
| Services | svc-plex, svc-nextcloud, svc-gitlab, svc-prometheus, svc-grafana |

---

## API Reference

### Base URL

```
http://localhost:8000/api
```

### Authentication

```bash
# Login → get JWT
curl -X POST http://localhost:8000/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"username":"admin","password":"admin123"}'
# Response: {"access_token":"eyJhbGciOiJIUzI1NiJ9...","token_type":"bearer"}

# Use token in all subsequent requests
export TOKEN="eyJhbGciOiJIUzI1NiJ9..."

# Get current user
curl -H "Authorization: Bearer $TOKEN" http://localhost:8000/api/auth/me

# Create user (admin only)
curl -X POST http://localhost:8000/api/auth/users \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"username":"editor1","email":"editor@lab.local","role":"editor","password":"pass123"}'
```

### Configuration Items

```bash
# List (paginated + filtered)
curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:8000/api/ci?page=1&page_size=10&status=active&search=proxmox&sort_by=name&sort_order=asc"

# Get single CI (full details: IPs, NICs, HW, SW, relationships, owners)
curl -H "Authorization: Bearer $TOKEN" http://localhost:8000/api/ci/<UUID>

# Create
curl -X POST http://localhost:8000/api/ci \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "new-server",
    "ci_type_id": "<type_uuid>",
    "status": "active",
    "location_id": "<location_uuid>",
    "tags": ["production", "rack-1"],
    "attributes": {"os": "Ubuntu 22.04", "cpu_cores": 8, "ram_gb": 32}
  }'

# Update (partial)
curl -X PATCH http://localhost:8000/api/ci/<UUID> \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"status": "maintenance", "description": "Scheduled upgrade"}'

# Soft delete
curl -X DELETE -H "Authorization: Bearer $TOKEN" http://localhost:8000/api/ci/<UUID>

# Restore
curl -X POST -H "Authorization: Bearer $TOKEN" http://localhost:8000/api/ci/<UUID>/restore

# Export CSV
curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:8000/api/ci/export?format=csv" > cmdb_export.csv
```

### Query Parameters

| Parameter | Type | Description |
|-----------|------|-------------|
| `page` | int | Page number (default: 1) |
| `page_size` | int | Items per page (1–100, default: 20) |
| `search` | string | Full-text search on name, description, serial, asset tag |
| `status` | string | Filter: `active`, `inactive`, `maintenance`, `deprecated`, `planned` |
| `ci_type_id` | UUID | Filter by CI type |
| `location_id` | UUID | Filter by location |
| `tag` | string | Filter by tag (array contains) |
| `owner_id` | UUID | Filter by owner user |
| `sort_by` | string | Sort field (default: `name`) |
| `sort_order` | string | `asc` or `desc` (default: `asc`) |

### Relationships

```bash
# Add relationship
curl -X POST http://localhost:8000/api/ci/<CI_ID>/relationships \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "source_ci_id": "<CI_ID>",
    "target_ci_id": "<OTHER_CI_ID>",
    "relationship": "depends_on",
    "description": "Plex depends on vm-plex"
  }'

# Relationship types: depends_on, connected_to, hosted_on, runs_on,
#                     manages, contains, part_of, related_to

# Remove relationship
curl -X DELETE -H "Authorization: Bearer $TOKEN" \
  http://localhost:8000/api/ci/<CI_ID>/relationships/<REL_ID>
```

### Graph Visualization

```bash
# Get relationship graph (BFS up to N levels)
curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:8000/api/ci/graph/visualize?depth=2"

# With specific starting node
curl -H "Authorization: Bearer $TOKEN" \
  "http://localhost:8000/api/ci/graph/visualize?depth=3&ci_id=<UUID>"

# Response:
# {
#   "nodes": [{"id": "...", "label": "proxmox-01", "group": "PhysicalServer", "status": "active"}],
#   "edges": [{"source": "...", "target": "...", "label": "hosted_on"}]
# }
```

### Bulk Import

```bash
curl -X POST http://localhost:8000/api/ci/bulk/import \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "items": [
      {"name": "server-1", "ci_type_name": "PhysicalServer", "status": "active"},
      {"name": "vm-web", "ci_type_name": "VirtualMachine", "status": "active",
       "attributes": {"os": "Ubuntu 22.04"}, "tags": ["web"]},
      {"name": "switch-1", "ci_type_name": "Switch", "status": "active"}
    ]
  }'
# Response: {"created": 3, "errors": []}
```

### Dashboard

```bash
curl -H "Authorization: Bearer $TOKEN" http://localhost:8000/api/dashboard/stats
# Response:
# {
#   "total_cis": 30,
#   "by_status": {"active": 27, "inactive": 1, "maintenance": 1, "planned": 1},
#   "by_class": {"Hardware": 5, "NetworkDevice": 3, "Storage": 1, "Software": 5},
#   "total_relationships": 22,
#   "total_locations": 3
# }
```

### Reference Data

```bash
# CI Classes
curl -H "Authorization: Bearer $TOKEN" http://localhost:8000/api/classes

# CI Types (optionally filter by class)
curl -H "Authorization: Bearer $TOKEN" "http://localhost:8000/api/types?class_id=<UUID>"

# Locations
curl -H "Authorization: Bearer $TOKEN" http://localhost:8000/api/locations
```

---

## Frontend

### Pages

| Page | Route | Description |
|------|-------|-------------|
| Login | `/login` | JWT authentication |
| Dashboard | `/` | Stats cards, status/class breakdown |
| CI List | `/ci` | Table with server-side pagination, search, filters, create/delete dialogs |
| CI Detail | `/ci/:id` | Tabs: Details, IP Addresses, Network, Hardware, Software, Relationships |
| Graph | `/graph` | Force-directed canvas visualization, adjustable depth |

### UI Features

- **Dark theme** (GitHub-inspired palette)
- **Server-side pagination** (10/20/50 per page)
- **Inline FK editing** via dropdowns (type, location, status)
- **Force-directed graph** on HTML5 Canvas (no heavy libs)
- **Confirmation dialogs** for destructive actions
- **Snackbar notifications** for success/error feedback
- **Responsive layout** (MUI Grid + Drawer)

---

## Testing

```bash
cd backend

# Run all tests
pytest tests/ -v

# Run with coverage
pytest tests/ -v --tb=short

# Test scenarios covered:
# - Health check
# - Login (valid/invalid)
# - RBAC (viewer cannot create)
# - CRUD operations
# - Soft delete + restore
# - Search + pagination + filters
# - Dashboard stats
# - Graph traversal
# - Bulk import
# - Relationship management
# - 404 handling
```

---

## Deployment

### Docker Compose

```bash
# Production setup
export JWT_SECRET=$(openssl rand -hex 32)
export POSTGRES_PASSWORD=$(openssl rand -hex 32)

docker-compose -f docker-compose.yml up -d --build

# Verify
docker-compose ps
curl http://localhost:8000/api/health
```

### Kubernetes

```bash
# Namespace
kubectl create namespace cmdb

# Secrets (never commit these!)
kubectl -n cmdb create secret generic cmdb-secrets \
  --from-literal=db-user=cmdb \
  --from-literal=db-password=$(openssl rand -hex 16) \
  --from-literal=jwt-secret=$(openssl rand -hex 32)

# Deploy
kubectl apply -f k8s/postgres.yaml
kubectl apply -f k8s/backend.yaml
kubectl apply -f k8s/frontend.yaml

# Verify
kubectl -n cmdb get pods
kubectl -n cmdb get svc
```

### Environment Variables

| Variable | Default | Description |
|----------|---------|-------------|
| `DATABASE_URL` | `postgresql+asyncpg://cmdb:cmdb_secret@localhost:5432/cmdb` | PostgreSQL connection string |
| `JWT_SECRET` | `CHANGE-ME` | Secret for JWT signing |
| `JWT_ALGORITHM` | `HS256` | JWT algorithm |
| `JWT_EXPIRATION_MINUTES` | `60` | Token lifetime |
| `CORS_ORIGINS` | `["http://localhost:3000"]` | Allowed origins |
| `RATE_LIMIT_PER_MINUTE` | `120` | Per-IP rate limit |
| `DATABASE_POOL_SIZE` | `20` | Connection pool size |
| `DEBUG` | `false` | Enable SQL logging |

---

## Security

### Checklist

- [ ] Change `JWT_SECRET` to random 64-char hex
- [ ] Change PostgreSQL password from default
- [ ] Enable SSL/TLS for PostgreSQL (`sslmode=require`)
- [ ] Run backend as non-root in Docker
- [ ] Restrict CORS to production domain
- [ ] Configure `pg_hba.conf` for network access
- [ ] Enable rate limiting (default: 120 req/min)
- [ ] Set up daily `pg_dump` backups
- [ ] Review `changelog` table weekly
- [ ] Scan Docker images with Trivy

### RBAC Matrix

| Action | admin | editor | viewer |
|--------|-------|--------|--------|
| Read CIs | ✅ | ✅ | ✅ |
| Create/Update CIs | ✅ | ✅ | ❌ |
| Delete CIs | ✅ | ✅ | ❌ |
| Manage relationships | ✅ | ✅ | ❌ |
| Create users | ✅ | ❌ | ❌ |
| Bulk import | ✅ | ✅ | ❌ |
| Export data | ✅ | ✅ | ✅ |

---

## Backup & Recovery

```bash
# Daily backup (add to cron)
pg_dump -h localhost -U cmdb cmdb | gzip > /backups/cmdb_$(date +%Y%m%d).sql.gz

# Restore
dropdb cmdb && createdb cmdb
zcat /backups/cmdb_20240101.sql.gz | psql -U cmdb cmdb

# Point-in-time recovery (WAL archiving)
# See docs/security.md for pgBackRest configuration
```

---

## Ansible Integration

Auto-discover infrastructure and register as CIs:

```bash
# Set CMDB token
export CMDB_TOKEN=$(curl -s -X POST http://cmdb:8000/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"username":"admin","password":"admin123"}' | jq -r .access_token)

# Run playbook
ansible-playbook -i inventory playbooks/cmdb-sync.yml
```

See `docs/ansible-integration.md` for full playbook examples.

---

## Roadmap

| Phase | Feature | Priority |
|-------|---------|----------|
| v1.1 | Discovery integration (nmap, arp-scan, SNMP) | High |
| v1.1 | CMDB reconciliation (discovered vs. recorded) | High |
| v1.2 | Change management (RFC workflow, approval) | Medium |
| v1.2 | Dependency impact analysis | Medium |
| v1.3 | SSO/LDAP authentication | Medium |
| v1.3 | Webhook notifications (Slack/Teams) | Low |
| v2.0 | GraphQL API layer | Low |
| v2.0 | Terraform/Pulumi resource import | Low |
| v2.1 | RBAC per CI type | Low |
| v2.1 | API versioning (`/api/v2/`) | Low |

---

## License

MIT