Privacy Score — SQL source

-- privacy-score — scoring rubric + overall letter computation.
-- Source:   CosmoDev polyglot showcase port of the Privacy Score tool,
--           ported from src/lib/privacy-score.ts (canonical TypeScript).
-- License:  display source — part of CosmoDev's polyglot tool pages.
--
-- Four category checks, each scored out of 25; the overall percent is
-- renormalized over the checks that actually ran (skipping a check never
-- lowers your score). Letter bands: >=85 A, >=70 B, >=50 C, else D.

-- The rubric: how each category converts raw check output into points.
CREATE TABLE privacy_rubric (
  category   TEXT PRIMARY KEY,  -- fingerprint | password | headers | breach
  rule       TEXT NOT NULL,     -- point conversion, straight from the TS lib
  floor_bad  INTEGER NOT NULL,  -- points < floor_bad  -> 'bad'  (act now)
  floor_warn INTEGER NOT NULL   -- ... < floor_warn    -> 'warn'
);
INSERT INTO privacy_rubric (category, rule, floor_bad, floor_warn) VALUES
  ('fingerprint', '25 - high_risk*4 - medium_risk*1.5 - max(0, signals - 12)*0.5', 10, 20),
  ('password',    'clamp(score,0,4)/4 * 25, then *0.32 when breached',              10, 20),
  ('headers',     'any F -> 0; any C -> 10; any B -> 18; else 25 (0 when none)',    10, 20),
  ('breach',      'pwned -> 0, clean -> 25',                                         10, 20);

-- One row per check that ran, already converted to points by the rubric.
CREATE TABLE privacy_checks (
  run_id    INTEGER NOT NULL,
  category  TEXT NOT NULL REFERENCES privacy_rubric (category),
  points    INTEGER NOT NULL CHECK (points BETWEEN 0 AND 25)
);
INSERT INTO privacy_checks (run_id, category, points) VALUES
  (1, 'fingerprint', 8),   -- 18 signals, 2 high-risk, 4 medium
  (1, 'password',    8),   -- score 4 but breached -> 25 * 0.32 floor
  (1, 'headers',     18),  -- grades A A B
  (1, 'breach',      25);  -- not pwned

-- Per-check status against the rubric floors (ok >= 20, warn >= 10).
SELECT c.category,
       c.points,
       CASE WHEN c.points >= r.floor_warn THEN 'ok'
            WHEN c.points >= r.floor_bad  THEN 'warn'
            ELSE 'bad' END AS status
FROM privacy_checks AS c
JOIN privacy_rubric AS r ON r.category = c.category
WHERE c.run_id = 1
ORDER BY c.rowid;

-- Overall: renormalize over the checks that ran and grade the letter.
SELECT sum(points)                    AS total,
       count(*) * 25                  AS max,
       round(sum(points) * 100.0 / (count(*) * 25)) AS percent,
       CASE
         WHEN count(*) = 0 THEN '—'
         WHEN round(sum(points) * 100.0 / (count(*) * 25)) >= 85 THEN 'A'
         WHEN round(sum(points) * 100.0 / (count(*) * 25)) >= 70 THEN 'B'
         WHEN round(sum(points) * 100.0 / (count(*) * 25)) >= 50 THEN 'C'
         ELSE 'D'
       END AS letter
FROM privacy_checks
WHERE run_id = 1;