schema.sql

-- FIPS 140 certificate data, as served to MCP clients.
--
-- Scalar columns are the ones worth filtering on; everything else rides in
-- `detail` as JSON, because it is only ever returned whole. That keeps the
-- row count low enough that a LIKE scan over 5,504 rows stays inside the
-- Workers free plan's 10 ms of CPU.

DROP TABLE IF EXISTS certs;
CREATE TABLE certs (
  number              INTEGER PRIMARY KEY,
  standard            TEXT NOT NULL,   -- 140-1 | 140-2 | 140-3
  status              TEXT NOT NULL,   -- active | historical | revoked
  module_name         TEXT NOT NULL,
  vendor_name         TEXT NOT NULL,   -- as NIST filed it
  vendor_key          TEXT,            -- normalised grouping key, precomputed upstream
  vendor_display      TEXT,            -- spelling NIST used most in that group
  module_type         TEXT,
  embodiment          TEXT,
  overall_level       INTEGER,
  sunset_date         TEXT,            -- active certificates only
  first_seen          TEXT,            -- first observed by this tracker
  last_change         TEXT,
  caveat              TEXT,
  description         TEXT,
  historical_reason   TEXT,
  revoked_reason      TEXT,
  nist_url            TEXT,
  security_policy_url TEXT,
  detail              TEXT             -- JSON: validation history, algorithms, versions, CVEs
);

CREATE INDEX idx_certs_vendor   ON certs(vendor_name);
CREATE INDEX idx_certs_vkey     ON certs(vendor_key);
CREATE INDEX idx_certs_status   ON certs(status);
CREATE INDEX idx_certs_standard ON certs(standard);
CREATE INDEX idx_certs_sunset   ON certs(sunset_date);
CREATE INDEX idx_certs_level    ON certs(overall_level);

-- Modules in process: NIST's queue of submissions awaiting validation.
-- This is the half nobody else exposes programmatically.
DROP TABLE IF EXISTS mip;
CREATE TABLE mip (
  key           TEXT PRIMARY KEY,
  submission_id INTEGER,
  module_name   TEXT NOT NULL,
  vendor_name   TEXT NOT NULL,
  standard      TEXT,
  phase         TEXT,
  phase_date    TEXT
);

CREATE INDEX idx_mip_vendor ON mip(vendor_name);
CREATE INDEX idx_mip_phase  ON mip(phase);

-- CVEs associated with a module's product family. This is a name-based match
-- against the vendor/product CPE, NOT a statement about the validated
-- boundary: a CVE listed here may sit in code the certificate never covered.
-- Every tool that returns these says so, because an unqualified CVE list is
-- the easiest thing here to repeat as fact.
DROP TABLE IF EXISTS cves;
CREATE TABLE cves (
  cert_number INTEGER NOT NULL,
  id          TEXT NOT NULL,
  cvss        REAL,
  severity    TEXT,
  matched_via TEXT,
  confidence  TEXT,
  PRIMARY KEY (cert_number, id)
);

CREATE INDEX idx_cves_id       ON cves(id);
CREATE INDEX idx_cves_severity ON cves(severity);

-- One row of provenance, so a caller can tell how stale the data is.
DROP TABLE IF EXISTS meta;
CREATE TABLE meta (
  key   TEXT PRIMARY KEY,
  value TEXT NOT NULL
);