1 contributor
Table: certificates
Stores metadata for certificates issued by the local CA.
Certificate rows are synchronized when the app reads ca_manager.sh list-json.
Columns
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
certificate_id |
TEXT |
no | none | Certificate identifier, currently derived from issued cert filename. Primary key. |
host_fqdn |
TEXT |
yes | NULL |
Matched host if known. References hosts(fqdn). |
common_name |
TEXT |
no | '' |
Common name or primary DNS name. |
subject |
TEXT |
no | '' |
Certificate subject. |
issuer |
TEXT |
no | '' |
Certificate issuer. |
serial |
TEXT |
yes | NULL |
Certificate serial. Unique when present. |
status |
TEXT |
no | 'issued' |
Certificate lifecycle state. |
not_before |
TEXT |
no | '' |
Certificate validity start. |
not_after |
TEXT |
no | '' |
Certificate validity end. |
fingerprint_sha256 |
TEXT |
yes | NULL |
SHA256 fingerprint. Unique when present. |
cert_path |
TEXT |
no | '' |
Certificate file path. |
csr_path |
TEXT |
no | '' |
CSR file path. |
created_at |
TEXT |
no | none | ISO UTC creation timestamp. |
updated_at |
TEXT |
no | none | ISO UTC update timestamp. |
notes |
TEXT |
no | '' |
Operator notes. |
Keys And Indexes
- Primary key:
certificate_id - Unique:
serial - Unique:
fingerprint_sha256
Relationships
host_fqdnreferenceshosts(fqdn)withON UPDATE CASCADE ON DELETE SET NULL- Referenced by
certificate_dns_names.certificate_id - Referenced by
vhosts.certificate_id
Definition
CREATE TABLE IF NOT EXISTS certificates (
certificate_id TEXT PRIMARY KEY,
host_fqdn TEXT,
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 '',
FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE SET NULL
);