# Table: `vhosts`

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

## Columns

| Column | Type | Null | Default | Notes |
|--------|------|------|---------|-------|
| `vhost_fqdn` | `TEXT` | no | none | Vhost DNS name. Primary key. |
| `host_fqdn` | `TEXT` | no | none | Current host serving the vhost. References `hosts(fqdn)`. |
| `status` | `TEXT` | no | `'active'` | Vhost lifecycle state. |
| `service_name` | `TEXT` | no | `''` | Service label, often inferred from first DNS label. |
| `upstream_url` | `TEXT` | no | `''` | Optional upstream URL. |
| `tls_mode` | `TEXT` | no | `'local-ca'` | TLS handling mode. |
| `certificate_id` | `TEXT` | yes | `NULL` | Optional issued certificate. References `certificates(certificate_id)`. |
| `notes` | `TEXT` | no | `''` | Operator notes. |
| `created_at` | `TEXT` | no | none | ISO UTC creation timestamp. |
| `updated_at` | `TEXT` | no | none | ISO UTC update timestamp. |

## 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`.
- Retiring a vhost means setting `status = 'retired'`; the row remains.

## Definition

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