005_add_certificate_validations.sql RawHistoryFolder

1-- Add certificate validation storage for permanent TLS decisions
2-- Store user decisions for certificate errors to avoid repeated prompts
3
4CREATE TABLE IF NOT EXISTS certificate_validations (
5    id INTEGER PRIMARY KEY AUTOINCREMENT,
6    hostname TEXT NOT NULL,
7    certificate_hash TEXT NOT NULL,
8    user_decision TEXT NOT NULL CHECK (user_decision IN ('accepted', 'rejected')),
9    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
10    expires_at DATETIME, -- Optional expiration for temporary decisions
11    UNIQUE(hostname, certificate_hash)
12);
13
14CREATE INDEX idx_cert_validations_hostname ON certificate_validations(hostname);
15CREATE INDEX idx_cert_validations_hash ON certificate_validations(certificate_hash);
16CREATE INDEX idx_cert_validations_expires ON certificate_validations(expires_at);