bogdan / LocalAuthority
LocalAuthority / .doc / database.md
bogdan Use relational SQLite host database
4f8b59d 4 days ago History
1 contributor
439 lines | 12.86kb

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:

var/host-manager.sqlite

Locația runtime pe jumper:

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

Path-ul poate fi schimbat cu:

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.

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

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

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:

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

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:

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

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

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:

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

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:

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

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:

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

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:

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

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:

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:

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:

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

Restore

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

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
Gitprep Version v2.6.2 build on Mojolicious
DBViewer