# SQLite Database

Madagascar Local Authority folosește SQLite ca sursă de adevăr runtime pentru hosturi, aliasuri, vhosturi, Work Orders, workeri de date și certificate.

Locația implicită în checkout:

```text
var/host-manager.sqlite
```

Locația runtime pe jumper:

```text
/usr/local/xdev-host-manager/var/host-manager.sqlite
```

Path-ul poate fi schimbat cu:

```text
HOST_MANAGER_DB=/path/to/host-manager.sqlite
```

## Principii

Hosturile sunt identificate prin FQDN complet, nu prin short name. Exemplu: `gw.local` și `gw.remote` sunt identități diferite. Coloana compatibilă `legacy_id` păstrează ID-ul scurt folosit de UI-ul curent, dar cheia reală este `hosts.fqdn`.

Schema evită să transforme `hosts` într-un tabel cu prea multe coloane. Datele specializate sunt în tabele separate:

- aliasuri: `host_aliases`
- roluri: `host_roles`
- surse: `host_sources`
- flaguri: `host_flags`
- SSH: `host_ssh`
- vhosturi mutabile: `vhosts`
- certificate: `certificates`, `certificate_dns_names`
- workeri și observații: `data_workers`, `dhcp_leases`, `mdns_observations`

`documents` rămâne doar tabel legacy pentru migrarea din modelul vechi document-store. Aplicația nu îl mai folosește ca sursă de adevăr.

## Schema Version

Schema curentă este versiunea `2`.

```sql
schema_meta('schema_version') = '2'
```

`schema_meta` păstrează și metadate runtime precum `registry_updated_at`.

## Catalog

| Tabel | Rol |
|-------|-----|
| `schema_meta` | metadate de schemă/runtime |
| `documents` | document-store legacy pentru migrare |
| `hosts` | hosturi canonice, identificate prin FQDN |
| `host_aliases` | aliasuri păstrate inclusiv după retragere |
| `host_roles` | roluri active/retrase per host |
| `host_sources` | surse active/retrase per host |
| `host_flags` | flaguri extensibile per host |
| `host_ssh` | profile SSH per host |
| `vhosts` | vhosturi mutabile între hosturi |
| `data_workers` | workeri/surse care colectează date |
| `dhcp_leases` | date observate din DHCP lease/reservation |
| `mdns_observations` | date observate din mDNS |
| `certificates` | certificate emise de CA locală |
| `certificate_dns_names` | SAN DNS names pentru certificate |
| `work_orders` | Work Orders |
| `work_order_checklist` | checklist items pentru Work Orders |
| `work_order_actions` | acțiuni confirmabile pentru Work Orders |

## Tabele

### `hosts`

```sql
CREATE TABLE hosts (
    fqdn TEXT PRIMARY KEY,
    legacy_id TEXT NOT NULL UNIQUE,
    status TEXT NOT NULL DEFAULT 'active',
    hosts_ip TEXT NOT NULL DEFAULT '',
    dns_ip TEXT NOT NULL DEFAULT '',
    monitoring TEXT NOT NULL DEFAULT 'pending',
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
```

Chei și indexuri:

- PK: `hosts(fqdn)`
- UNIQUE: `hosts(legacy_id)`

### `host_aliases`

```sql
CREATE TABLE host_aliases (
    alias_name TEXT NOT NULL,
    host_fqdn TEXT NOT NULL REFERENCES hosts(fqdn)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    alias_kind TEXT NOT NULL DEFAULT 'declared',
    status TEXT NOT NULL DEFAULT 'active',
    is_dns_published INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    retired_at TEXT,
    notes TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (alias_name, host_fqdn)
);
```

Indexuri:

```sql
CREATE UNIQUE INDEX idx_host_aliases_active_name
ON host_aliases(alias_name)
WHERE status = 'active';

CREATE INDEX idx_host_aliases_host_status
ON host_aliases(host_fqdn, status);
```

Reguli:

- aliasurile retrase nu se șterg; se setează `status = 'retired'`
- un alias activ poate aparține unui singur host
- aliasurile scurte derivate, cum ar fi `baobab`, sunt păstrate cu `alias_kind = 'derived'`
- short aliases derivate din vhosturi, cum ar fi `pmx.baobab`, sunt păstrate cu `alias_kind = 'derived-vhost'`

### `vhosts`

```sql
CREATE TABLE vhosts (
    vhost_fqdn TEXT PRIMARY KEY,
    host_fqdn TEXT NOT NULL REFERENCES hosts(fqdn)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    status TEXT NOT NULL DEFAULT 'active',
    service_name TEXT NOT NULL DEFAULT '',
    upstream_url TEXT NOT NULL DEFAULT '',
    tls_mode TEXT NOT NULL DEFAULT 'local-ca',
    certificate_id TEXT REFERENCES certificates(certificate_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
```

Indexuri:

```sql
CREATE INDEX idx_vhosts_host_status
ON vhosts(host_fqdn, status);
```

Un vhost se mută de pe un host pe altul prin update pe `vhosts.host_fqdn`. Vhosturile retrase rămân în tabel cu `status = 'retired'`.

### `host_roles`, `host_sources`, `host_flags`, `host_ssh`

```sql
CREATE TABLE host_roles (
    host_fqdn TEXT NOT NULL REFERENCES hosts(fqdn)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    role TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    created_at TEXT NOT NULL,
    retired_at TEXT,
    PRIMARY KEY (host_fqdn, role)
);

CREATE TABLE host_sources (
    host_fqdn TEXT NOT NULL REFERENCES hosts(fqdn)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    source TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    created_at TEXT NOT NULL,
    retired_at TEXT,
    PRIMARY KEY (host_fqdn, source)
);

CREATE TABLE host_flags (
    host_fqdn TEXT NOT NULL REFERENCES hosts(fqdn)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    flag TEXT NOT NULL,
    value TEXT NOT NULL DEFAULT '1',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    PRIMARY KEY (host_fqdn, flag)
);

CREATE TABLE host_ssh (
    host_fqdn TEXT NOT NULL REFERENCES hosts(fqdn)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    profile_name TEXT NOT NULL DEFAULT 'default',
    username TEXT NOT NULL DEFAULT '',
    port INTEGER NOT NULL DEFAULT 22,
    identity_file TEXT NOT NULL DEFAULT '',
    address TEXT NOT NULL DEFAULT '',
    local_forward_host TEXT NOT NULL DEFAULT '',
    local_forward_port INTEGER,
    remote_forward_host TEXT NOT NULL DEFAULT '',
    remote_forward_port INTEGER,
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    PRIMARY KEY (host_fqdn, profile_name)
);
```

### `data_workers`

```sql
CREATE TABLE data_workers (
    worker_id TEXT PRIMARY KEY,
    worker_type TEXT NOT NULL,
    name TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'active',
    source TEXT NOT NULL DEFAULT '',
    last_run_at TEXT,
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
);
```

Indexuri:

```sql
CREATE INDEX idx_data_workers_type_status
ON data_workers(worker_type, status);
```

Seed implicit:

- `dhcp-router`, type `dhcp`
- `mdns-listener`, type `mdns`

### `dhcp_leases`

```sql
CREATE TABLE dhcp_leases (
    lease_key TEXT PRIMARY KEY,
    worker_id TEXT NOT NULL REFERENCES data_workers(worker_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    host_fqdn TEXT REFERENCES hosts(fqdn)
        ON UPDATE CASCADE ON DELETE SET NULL,
    observed_name TEXT NOT NULL DEFAULT '',
    ip_address TEXT NOT NULL,
    mac_address TEXT NOT NULL DEFAULT '',
    lease_state TEXT NOT NULL DEFAULT '',
    first_seen TEXT NOT NULL,
    last_seen TEXT NOT NULL,
    raw TEXT NOT NULL DEFAULT ''
);
```

Indexuri:

```sql
CREATE INDEX idx_dhcp_leases_ip ON dhcp_leases(ip_address);
CREATE INDEX idx_dhcp_leases_mac ON dhcp_leases(mac_address);
CREATE INDEX idx_dhcp_leases_worker_last_seen
ON dhcp_leases(worker_id, last_seen);
```

### `mdns_observations`

```sql
CREATE TABLE mdns_observations (
    observation_key TEXT PRIMARY KEY,
    worker_id TEXT NOT NULL REFERENCES data_workers(worker_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    host_fqdn TEXT REFERENCES hosts(fqdn)
        ON UPDATE CASCADE ON DELETE SET NULL,
    observed_name TEXT NOT NULL,
    ip_address TEXT NOT NULL,
    rr_type TEXT NOT NULL DEFAULT 'A',
    ttl INTEGER NOT NULL DEFAULT 0,
    first_seen TEXT NOT NULL,
    last_seen TEXT NOT NULL,
    seen_count INTEGER NOT NULL DEFAULT 1,
    last_peer TEXT NOT NULL DEFAULT '',
    raw TEXT NOT NULL DEFAULT ''
);
```

Indexuri:

```sql
CREATE INDEX idx_mdns_observations_name ON mdns_observations(observed_name);
CREATE INDEX idx_mdns_observations_ip ON mdns_observations(ip_address);
CREATE INDEX idx_mdns_observations_worker_last_seen
ON mdns_observations(worker_id, last_seen);
```

### `certificates` și `certificate_dns_names`

```sql
CREATE TABLE certificates (
    certificate_id TEXT PRIMARY KEY,
    host_fqdn TEXT REFERENCES hosts(fqdn)
        ON UPDATE CASCADE ON DELETE SET NULL,
    common_name TEXT NOT NULL DEFAULT '',
    subject TEXT NOT NULL DEFAULT '',
    issuer TEXT NOT NULL DEFAULT '',
    serial TEXT UNIQUE,
    status TEXT NOT NULL DEFAULT 'issued',
    not_before TEXT NOT NULL DEFAULT '',
    not_after TEXT NOT NULL DEFAULT '',
    fingerprint_sha256 TEXT UNIQUE,
    cert_path TEXT NOT NULL DEFAULT '',
    csr_path TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    notes TEXT NOT NULL DEFAULT ''
);

CREATE TABLE certificate_dns_names (
    certificate_id TEXT NOT NULL REFERENCES certificates(certificate_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    dns_name TEXT NOT NULL,
    PRIMARY KEY (certificate_id, dns_name)
);
```

Indexuri:

```sql
CREATE INDEX idx_certificate_dns_names_dns_name
ON certificate_dns_names(dns_name);
```

Certificatele emise sunt sincronizate când aplicația citește lista CA prin `ca_manager.sh list-json`.

### Work Orders

```sql
CREATE TABLE work_orders (
    id TEXT PRIMARY KEY,
    status TEXT NOT NULL DEFAULT 'pending',
    title TEXT NOT NULL DEFAULT '',
    reason TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    confirmed_at TEXT NOT NULL DEFAULT '',
    result TEXT NOT NULL DEFAULT '',
    updated_at TEXT NOT NULL
);

CREATE TABLE work_order_checklist (
    work_order_id TEXT NOT NULL REFERENCES work_orders(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    item_id TEXT NOT NULL,
    text TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'pending',
    owner TEXT NOT NULL DEFAULT '',
    notes TEXT NOT NULL DEFAULT '',
    updated_at TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (work_order_id, item_id)
);

CREATE TABLE work_order_actions (
    work_order_id TEXT NOT NULL REFERENCES work_orders(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    position INTEGER NOT NULL,
    type TEXT NOT NULL,
    host_fqdn TEXT REFERENCES hosts(fqdn)
        ON UPDATE CASCADE ON DELETE SET NULL,
    host_legacy_id TEXT NOT NULL DEFAULT '',
    name TEXT NOT NULL DEFAULT '',
    payload TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (work_order_id, position)
);
```

## Migrare și Seed

La prima inițializare a unei baze fără rânduri în `hosts`, aplicația seed-uiește din:

1. `documents.name = 'hosts_yaml'`, dacă există din vechiul model
2. `config/hosts.yaml`, dacă documentul legacy lipsește
3. document gol valid, dacă lipsește și seed-ul

Work Orders se seed-uiesc similar din `documents.name = 'work_orders_yaml'` sau `config/work-orders.yaml`.

Seed-ul curent produce:

- 11 hosturi cunoscute
- aliasuri scurte derivate pentru hosturi și vhosturi
- vhosturile `hosts.*`, `pmx.*` și `pbs.*`
- workerii `dhcp-router` și `mdns-listener`
- Work Order-ul existent pentru retragerea numelor legacy

`config/local-hosts.tsv` rămâne manifest generat explicit din tabelele runtime.

## Inspecție

Pe jumper:

```bash
cd /usr/local/xdev-host-manager
sqlite3 var/host-manager.sqlite '.tables'
sqlite3 var/host-manager.sqlite '.schema hosts'
sqlite3 var/host-manager.sqlite '.schema host_aliases'
sqlite3 var/host-manager.sqlite '.schema vhosts'
sqlite3 var/host-manager.sqlite 'pragma foreign_key_list(vhosts);'
sqlite3 var/host-manager.sqlite 'pragma index_list(host_aliases);'
sqlite3 var/host-manager.sqlite 'select fqdn, legacy_id, status, dns_ip from hosts order by legacy_id;'
sqlite3 var/host-manager.sqlite 'select alias_name, host_fqdn, alias_kind, status from host_aliases order by alias_name;'
sqlite3 var/host-manager.sqlite 'select vhost_fqdn, host_fqdn, status from vhosts order by vhost_fqdn;'
```

## Backup

Backup recomandat:

```bash
cd /usr/local/xdev-host-manager
sqlite3 var/host-manager.sqlite ".backup 'backups/host-manager/host-manager.sqlite.$(date +%Y%m%d_%H%M%S).bak'"
```

Cu WAL activ, o copie brută trebuie să trateze fișierele ca set coerent:

```text
var/host-manager.sqlite
var/host-manager.sqlite-wal
var/host-manager.sqlite-shm
```

## Restore

Restore-ul înlocuiește sursa de adevăr runtime:

```bash
sudo systemctl stop host-manager
sudo cp backups/host-manager/host-manager.sqlite.YYYYMMDD_HHMMSS.bak var/host-manager.sqlite
sudo chown host-manager:host-manager var/host-manager.sqlite
sudo systemctl start host-manager
curl -fsS http://127.0.0.1:8088/healthz >/dev/null
```