Table: vhosts

Stores virtual hosts separately from physical/logical hosts so a vhost can be moved between hosts.

Columns

Keys And Indexes

• Primary key: vhost_fqdn
• Lookup index: idx_vhosts_host_status on (host_fqdn, status)

Relationships

• host_fqdn references hosts(fqdn) with ON UPDATE CASCADE ON DELETE RESTRICT
• certificate_id references certificates(certificate_id) with ON UPDATE CASCADE ON DELETE SET NULL

Rules

• Moving a vhost means updating host_fqdn.
• The certificate_id binding stays on the vhost row when the vhost moves to another host.
• Retiring a vhost means setting status = 'retired'; the row remains.

Definition

CREATE TABLE IF NOT EXISTS vhosts (
    vhost_fqdn TEXT PRIMARY KEY,
    host_fqdn TEXT NOT NULL,
    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,
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (certificate_id) REFERENCES certificates(certificate_id) ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_vhosts_host_status
ON vhosts(host_fqdn, status);