Newer
■
■
■
■
■
■
■
■
■
■
Older
56 lines
|
2.224kb
| 1 |
# Table: `vhosts` |
|
| 2 | ||
| 3 |
Stores virtual hosts separately from physical/logical hosts so a vhost can be moved between hosts. |
|
| 4 | ||
| 5 |
## Columns |
|
| 6 | ||
| 7 |
| Column | Type | Null | Default | Notes | |
|
| 8 |
|--------|------|------|---------|-------| |
|
| 9 |
| `vhost_fqdn` | `TEXT` | no | none | Vhost DNS name. Primary key. | |
|
| 10 |
| `host_fqdn` | `TEXT` | no | none | Current host serving the vhost. References `hosts(fqdn)`. | |
|
| 11 |
| `status` | `TEXT` | no | `'active'` | Vhost lifecycle state. | |
|
| 12 |
| `service_name` | `TEXT` | no | `''` | Service label, often inferred from first DNS label. | |
|
| 13 |
| `upstream_url` | `TEXT` | no | `''` | Optional upstream URL. | |
|
| 14 |
| `tls_mode` | `TEXT` | no | `'local-ca'` | TLS handling mode. | |
|
| 15 |
| `certificate_id` | `TEXT` | yes | `NULL` | Optional issued certificate. References `certificates(certificate_id)`. | |
|
| 16 |
| `notes` | `TEXT` | no | `''` | Operator notes. | |
|
| 17 |
| `created_at` | `TEXT` | no | none | ISO UTC creation timestamp. | |
|
| 18 |
| `updated_at` | `TEXT` | no | none | ISO UTC update timestamp. | |
|
| 19 | ||
| 20 |
## Keys And Indexes |
|
| 21 | ||
| 22 |
- Primary key: `vhost_fqdn` |
|
| 23 |
- Lookup index: `idx_vhosts_host_status` on `(host_fqdn, status)` |
|
| 24 | ||
| 25 |
## Relationships |
|
| 26 | ||
| 27 |
- `host_fqdn` references `hosts(fqdn)` with `ON UPDATE CASCADE ON DELETE RESTRICT` |
|
| 28 |
- `certificate_id` references `certificates(certificate_id)` with `ON UPDATE CASCADE ON DELETE SET NULL` |
|
| 29 | ||
| 30 |
## Rules |
|
| 31 | ||
| 32 |
- Moving a vhost means updating `host_fqdn`. |
|
| 33 |
- The `certificate_id` binding stays on the vhost row when the vhost moves to another host. |
|
| 34 |
- Retiring a vhost means setting `status = 'retired'`; the row remains. |
|
| 35 | ||
| 36 |
## Definition |
|
| 37 | ||
| 38 |
```sql |
|
| 39 |
CREATE TABLE IF NOT EXISTS vhosts ( |
|
| 40 |
vhost_fqdn TEXT PRIMARY KEY, |
|
| 41 |
host_fqdn TEXT NOT NULL, |
|
| 42 |
status TEXT NOT NULL DEFAULT 'active', |
|
| 43 |
service_name TEXT NOT NULL DEFAULT '', |
|
| 44 |
upstream_url TEXT NOT NULL DEFAULT '', |
|
| 45 |
tls_mode TEXT NOT NULL DEFAULT 'local-ca', |
|
| 46 |
certificate_id TEXT, |
|
| 47 |
notes TEXT NOT NULL DEFAULT '', |
|
| 48 |
created_at TEXT NOT NULL, |
|
| 49 |
updated_at TEXT NOT NULL, |
|
| 50 |
FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE RESTRICT, |
|
| 51 |
FOREIGN KEY (certificate_id) REFERENCES certificates(certificate_id) ON UPDATE CASCADE ON DELETE SET NULL |
|
| 52 |
); |
|
| 53 | ||
| 54 |
CREATE INDEX IF NOT EXISTS idx_vhosts_host_status |
|
| 55 |
ON vhosts(host_fqdn, status); |
|
| 56 |
``` |