# SQLite Database

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

```

- 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`

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

    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,

);

```

- UNIQUE: `hosts(legacy_id)`

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)

);

```

CREATE UNIQUE INDEX idx_host_aliases_active_name

ON host_aliases(alias_name)

WHERE status = 'active';

ON host_aliases(host_fqdn, status);

```

- 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'`

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

);

```

CREATE INDEX idx_vhosts_host_status

ON vhosts(host_fqdn, status);

```

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)

);

    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)

);

    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)

);

    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)

);

```

```sql

    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

);

```sql

ON data_workers(worker_type, status);

- `dhcp-router`, type `dhcp`

- `mdns-listener`, type `mdns`

### `dhcp_leases`

```sql

    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 ''

```

```sql

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);

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 ''

);

```

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);

```

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 ''

);

    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)

);

CREATE INDEX idx_certificate_dns_names_dns_name

ON certificate_dns_names(dns_name);

```

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

);

    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)

);

    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)

);

```

2. `config/hosts.yaml`, dacă documentul legacy lipsește

3. document gol valid, dacă lipsește și seed-ul

- 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

## Inspecție

Pe jumper:

```bash

cd /usr/local/xdev-host-manager

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

```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'"

```

var/host-manager.sqlite

var/host-manager.sqlite-wal

var/host-manager.sqlite-shm

## Restore

```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

```