# 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_fqdn` references `hosts(fqdn)` with `ON UPDATE CASCADE ON DELETE SET NULL`
- Referenced by `certificate_dns_names.certificate_id`
- Referenced by `vhosts.certificate_id`

## Definition

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