37 lines
1.3 KiB
SQL
37 lines
1.3 KiB
SQL
-- Smart Meter Gateways under PKI management.
|
|
CREATE TABLE IF NOT EXISTS gateways (
|
|
id TEXT PRIMARY KEY,
|
|
serial_number TEXT NOT NULL,
|
|
admin_key_label TEXT NOT NULL,
|
|
created_at TEXT NOT NULL
|
|
);
|
|
|
|
-- Issued end-entity certificates per gateway and usage.
|
|
-- not_before/not_after stored as RFC3339 UTC text (sorts lexicographically).
|
|
CREATE TABLE IF NOT EXISTS certificates (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
gateway_id TEXT NOT NULL REFERENCES gateways(id) ON DELETE CASCADE,
|
|
serial TEXT NOT NULL,
|
|
usage TEXT NOT NULL CHECK (usage IN ('tls', 'signature', 'encryption')),
|
|
pem TEXT NOT NULL,
|
|
not_before TEXT NOT NULL,
|
|
not_after TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL,
|
|
UNIQUE (gateway_id, usage)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_certificates_not_after
|
|
ON certificates (not_after);
|
|
|
|
-- TR-03129-4 messageID -> gateway_id lookup for asynchronous CA callbacks.
|
|
CREATE TABLE IF NOT EXISTS pending_requests (
|
|
message_id TEXT PRIMARY KEY,
|
|
gateway_id TEXT NOT NULL REFERENCES gateways(id) ON DELETE CASCADE,
|
|
created_at TEXT NOT NULL,
|
|
resolved_at TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_pending_unresolved
|
|
ON pending_requests (resolved_at)
|
|
WHERE resolved_at IS NULL;
|