Newer
■
■
■
■
■
■
■
■
■
■
Older
60 lines
|
2.337kb
| 1 |
# Table: `certificates` |
|
| 2 | ||
| 3 |
Stores metadata for certificates issued by the local CA. |
|
| 4 | ||
| 5 |
Certificate rows are synchronized when the app reads `ca_manager.sh list-json`. |
|
| 6 | ||
| 7 |
## Columns |
|
| 8 | ||
| 9 |
| Column | Type | Null | Default | Notes | |
|
| 10 |
|--------|------|------|---------|-------| |
|
| 11 |
| `certificate_id` | `TEXT` | no | none | Certificate identifier, currently derived from issued cert filename. Primary key. | |
|
| 12 |
| `host_fqdn` | `TEXT` | yes | `NULL` | Matched host if known. References `hosts(fqdn)`. | |
|
| 13 |
| `common_name` | `TEXT` | no | `''` | Common name or primary DNS name. | |
|
| 14 |
| `subject` | `TEXT` | no | `''` | Certificate subject. | |
|
| 15 |
| `issuer` | `TEXT` | no | `''` | Certificate issuer. | |
|
| 16 |
| `serial` | `TEXT` | yes | `NULL` | Certificate serial. Unique when present. | |
|
| 17 |
| `status` | `TEXT` | no | `'issued'` | Certificate lifecycle state. | |
|
| 18 |
| `not_before` | `TEXT` | no | `''` | Certificate validity start. | |
|
| 19 |
| `not_after` | `TEXT` | no | `''` | Certificate validity end. | |
|
| 20 |
| `fingerprint_sha256` | `TEXT` | yes | `NULL` | SHA256 fingerprint. Unique when present. | |
|
| 21 |
| `cert_path` | `TEXT` | no | `''` | Certificate file path. | |
|
| 22 |
| `csr_path` | `TEXT` | no | `''` | CSR file path. | |
|
| 23 |
| `created_at` | `TEXT` | no | none | ISO UTC creation timestamp. | |
|
| 24 |
| `updated_at` | `TEXT` | no | none | ISO UTC update timestamp. | |
|
| 25 |
| `notes` | `TEXT` | no | `''` | Operator notes. | |
|
| 26 | ||
| 27 |
## Keys And Indexes |
|
| 28 | ||
| 29 |
- Primary key: `certificate_id` |
|
| 30 |
- Unique: `serial` |
|
| 31 |
- Unique: `fingerprint_sha256` |
|
| 32 | ||
| 33 |
## Relationships |
|
| 34 | ||
| 35 |
- `host_fqdn` references `hosts(fqdn)` with `ON UPDATE CASCADE ON DELETE SET NULL` |
|
| 36 |
- Referenced by `certificate_dns_names.certificate_id` |
|
| 37 |
- Referenced by `vhosts.certificate_id` |
|
| 38 | ||
| 39 |
## Definition |
|
| 40 | ||
| 41 |
```sql |
|
| 42 |
CREATE TABLE IF NOT EXISTS certificates ( |
|
| 43 |
certificate_id TEXT PRIMARY KEY, |
|
| 44 |
host_fqdn TEXT, |
|
| 45 |
common_name TEXT NOT NULL DEFAULT '', |
|
| 46 |
subject TEXT NOT NULL DEFAULT '', |
|
| 47 |
issuer TEXT NOT NULL DEFAULT '', |
|
| 48 |
serial TEXT UNIQUE, |
|
| 49 |
status TEXT NOT NULL DEFAULT 'issued', |
|
| 50 |
not_before TEXT NOT NULL DEFAULT '', |
|
| 51 |
not_after TEXT NOT NULL DEFAULT '', |
|
| 52 |
fingerprint_sha256 TEXT UNIQUE, |
|
| 53 |
cert_path TEXT NOT NULL DEFAULT '', |
|
| 54 |
csr_path TEXT NOT NULL DEFAULT '', |
|
| 55 |
created_at TEXT NOT NULL, |
|
| 56 |
updated_at TEXT NOT NULL, |
|
| 57 |
notes TEXT NOT NULL DEFAULT '', |
|
| 58 |
FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE SET NULL |
|
| 59 |
); |
|
| 60 |
``` |