bigtech-sucks/packages/core/db/schema.sql

-- bigtech.sucks — living database schema
-- The website is a view over this. Nothing in the site may hardcode a number
-- that lives here.
--
-- Design rule: an assessment is an ARTIFACT with a date, a version, a method and
-- evidence. A score without an assessment is a placeholder and is stored as NULL,
-- never as a guess.

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

-- ---------------------------------------------------------------- who owns it
CREATE TABLE IF NOT EXISTS vendor (
  id              TEXT PRIMARY KEY,
  name            TEXT NOT NULL,
  legal_entity    TEXT,
  entity_type     TEXT CHECK (entity_type IN ('nonprofit','private','public','state','unknown')),
  jurisdiction    TEXT,            -- ISO 3166-1 alpha-2
  business_model  TEXT CHECK (business_model IN ('advertising','subscription','hardware','license','mixed','none','unknown')),
  parent_id       TEXT REFERENCES vendor(id),
  founded         TEXT,
  notes           TEXT
);

-- ---------------------------------------------------------------- what it is
CREATE TABLE IF NOT EXISTS category (
  id          TEXT PRIMARY KEY,
  label       TEXT NOT NULL,
  sensitivity REAL NOT NULL CHECK (sensitivity BETWEEN 0 AND 1),
  nextcloud   TEXT
);

CREATE TABLE IF NOT EXISTS product (
  id            TEXT PRIMARY KEY,
  vendor_id     TEXT NOT NULL REFERENCES vendor(id),
  name          TEXT NOT NULL,
  category_id   TEXT REFERENCES category(id),
  -- platform is part of the identity: Signal iOS != Signal Desktop
  platform      TEXT NOT NULL,
  source_url    TEXT,
  license       TEXT,
  sovereign     INTEGER NOT NULL DEFAULT 0 CHECK (sovereign IN (0,1)),
  boss          INTEGER NOT NULL DEFAULT 0 CHECK (boss IN (0,1)),
  art_slot      TEXT,
  monthly_cost  REAL,
  -- priority > 0 puts a product on the board even before anything discovers it
  priority      INTEGER NOT NULL DEFAULT 0,
  notes         TEXT,
  UNIQUE(id, platform)
);

-- ---------------------------------------------------------------- the instrument
CREATE TABLE IF NOT EXISTS dimension (
  id          TEXT PRIMARY KEY,
  label       TEXT NOT NULL,
  weight      REAL NOT NULL CHECK (weight > 0),
  anchor_10   TEXT NOT NULL,
  anchor_0    TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS assessment (
  id                 TEXT PRIMARY KEY,
  product_id         TEXT NOT NULL REFERENCES product(id),
  version            TEXT NOT NULL,
  source_commit      TEXT,
  assessed_on        TEXT NOT NULL,
  method_version     TEXT NOT NULL,
  -- 0 = none, 1 = available but experimental/unverified, 2 = verified
  reproducible_build INTEGER NOT NULL DEFAULT 0 CHECK (reproducible_build IN (0,1,2)),
  -- 1 = we can read the code. 0 = closed. Closed source cannot exceed 5 on any
  -- dimension unless independent evidence (a capture or a court filing) says so.
  source_available   INTEGER NOT NULL DEFAULT 1 CHECK (source_available IN (0,1)),
  weighted_total     REAL NOT NULL CHECK (weighted_total BETWEEN 0 AND 100),
  grade              TEXT NOT NULL,
  weakest_dimension  TEXT NOT NULL REFERENCES dimension(id),
  headline           TEXT NOT NULL,   -- the caveat, in one sentence
  not_tested         TEXT,
  UNIQUE(product_id, version, assessed_on)
);

CREATE TABLE IF NOT EXISTS score (
  assessment_id TEXT NOT NULL REFERENCES assessment(id) ON DELETE CASCADE,
  dimension_id  TEXT NOT NULL REFERENCES dimension(id),
  value         REAL NOT NULL CHECK (value BETWEEN 0 AND 10),
  rationale     TEXT,
  PRIMARY KEY (assessment_id, dimension_id)
);

-- evidence is the only thing that makes a score legitimate
CREATE TABLE IF NOT EXISTS evidence (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  assessment_id TEXT NOT NULL REFERENCES assessment(id) ON DELETE CASCADE,
  dimension_id  TEXT REFERENCES dimension(id),
  claim         TEXT NOT NULL,
  tier          INTEGER NOT NULL CHECK (tier IN (1,2,3,4)),
  kind          TEXT CHECK (kind IN ('source','capture','record','filing','advisory','paper','claim')),
  artifact      TEXT,          -- path inside the vault, or URL
  sha256        TEXT,
  observed_at   TEXT
);

CREATE TABLE IF NOT EXISTS finding (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  assessment_id TEXT NOT NULL REFERENCES assessment(id) ON DELETE CASCADE,
  severity      TEXT NOT NULL CHECK (severity IN ('critical','high','medium','low','info')),
  title         TEXT NOT NULL,
  detail        TEXT,
  status        TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open','fixed','by_design','disputed','accepted')),
  disclosed_on  TEXT,
  cve           TEXT
);

CREATE TABLE IF NOT EXISTS offramp (
  product_id  TEXT NOT NULL REFERENCES product(id),
  replacement TEXT NOT NULL,
  nextcloud   TEXT,
  notes       TEXT,
  PRIMARY KEY (product_id, replacement)
);

-- ---------------------------------------------------------------- discovery
-- The database authors itself. Every product the ingest finds leaves a receipt
-- here, so a reader can see WHERE it came from and HOW MUCH it is used.
-- Discovery may create a product. Discovery may NEVER create a score.
CREATE TABLE IF NOT EXISTS discovery (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id   TEXT NOT NULL REFERENCES product(id),
  source       TEXT NOT NULL CHECK (source IN ('github','flathub','alternativeto','manual','seed')),
  source_url   TEXT,
  usage_signal INTEGER,
  usage_metric TEXT,
  observed_at  TEXT NOT NULL,
  raw          TEXT,
  UNIQUE(product_id, source, source_url)
);

CREATE INDEX IF NOT EXISTS idx_discovery_product ON discovery(product_id);

-- ---------------------------------------------------------------- attestation
-- Closed source can earn its way up, but only by producing evidence that expires.
--
-- The level is NOT claimed by the vendor and NOT assigned by us. It is COMPUTED
-- from which required artifacts actually exist. A vendor can claim level 4 all day;
-- if the capture report is missing, the validator grants level 1.
--
-- Every attestation is bound to one artifact hash on one version. Any new build
-- invalidates it. There is no such thing as a standing certificate here.
CREATE TABLE IF NOT EXISTS attestation (
  id                     INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id             TEXT NOT NULL REFERENCES product(id),
  version                TEXT NOT NULL,
  artifact_sha256        TEXT NOT NULL,
  level                  INTEGER NOT NULL CHECK (level BETWEEN 0 AND 4),
  -- L1 declared: the vendor tells the world what is in the box
  sbom_url               TEXT,          -- CycloneDX or SPDX
  signed_hash_url        TEXT,          -- signed release manifest
  destination_allowlist_url TEXT,       -- every host it may contact, and why
  -- L2 observed: we watched this exact binary
  capture_report         TEXT,          -- D1-D4 output, hash-bound
  -- L3 reproducible: the binary is provably the build they claim
  rebuild_recipe_url     TEXT,
  rebuild_matches        INTEGER CHECK (rebuild_matches IN (0,1)),
  -- L4 adversarial: someone tried to break it and published the attempt
  adversarial_report_url TEXT,
  differential_clean     INTEGER CHECK (differential_clean IN (0,1)),
  issued_on              TEXT NOT NULL,
  expires_on             TEXT NOT NULL,
  auditor                TEXT NOT NULL,
  -- Who ran the test. This is the load-bearing column.
  -- A vendor running our harness produces a DECLARATION, not an observation.
  -- Declarations are capped at level 1, because a self-run test is a claim.
  -- Levels 3 and 4 survive a self-run, because a public rebuild recipe and a
  -- published adversarial review can be re-checked by anyone.
  attestor               TEXT NOT NULL DEFAULT 'vendor'
                         CHECK (attestor IN ('vendor','binary-ranch','third-party','community')),
  UNIQUE(product_id, version, artifact_sha256)
);

CREATE INDEX IF NOT EXISTS idx_attestation_product ON attestation(product_id, version);

-- ---------------------------------------------------------------- suggestions
-- Anyone can ask for a product to be rated. The backlog is built from these.
-- No account, no email required. An optional contact so we can tell them it shipped.
CREATE TABLE IF NOT EXISTS suggestion (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  product_name  TEXT NOT NULL,
  category_hint TEXT,
  note          TEXT,
  contact       TEXT,
  suggested_at  TEXT NOT NULL,
  ip_hash       TEXT,          -- salted, never the raw address
  status        TEXT NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending','accepted','duplicate','declined'))
);

CREATE INDEX IF NOT EXISTS idx_suggestion_name ON suggestion(LOWER(product_name));

-- What the public is asking for, most-requested first.
CREATE VIEW IF NOT EXISTS v_requested AS
SELECT
  LOWER(TRIM(s.product_name)) AS key,
  TRIM(MIN(s.product_name))   AS product_name,
  COUNT(*)                    AS requests,
  MAX(s.suggested_at)         AS latest
FROM suggestion s
WHERE s.status = 'pending'
GROUP BY LOWER(TRIM(s.product_name))
ORDER BY requests DESC, latest DESC;

-- ---------------------------------------------------------------- derived views
-- The single number the game and the report both use.
CREATE VIEW IF NOT EXISTS v_product_score AS
SELECT
  p.id                AS product_id,
  p.name              AS product,
  p.platform,
  p.category_id,
  v.id                AS vendor_id,
  v.name              AS vendor,
  v.business_model,
  v.jurisdiction,
  a.id                AS assessment_id,
  a.assessed_on,
  a.version,
  a.weighted_total,
  a.grade,
  a.weakest_dimension,
  a.headline,
  -- policy axis: who holds your data, and what can they be made to hand over
  CAST(ROUND(10 * (
      0.40 * (SELECT value FROM score WHERE assessment_id = a.id AND dimension_id = 'server_retention')
    + 0.30 * (SELECT value FROM score WHERE assessment_id = a.id AND dimension_id = 'ownership_jurisdiction')
    + 0.30 * (SELECT value FROM score WHERE assessment_id = a.id AND dimension_id = 'user_control_exit')
  )) AS INTEGER) AS policy,
  -- infra axis: can you verify it, and what does it leave on your disk
  CAST(ROUND(10 * (
      0.50 * (SELECT value FROM score WHERE assessment_id = a.id AND dimension_id = 'verifiability')
    + 0.30 * (SELECT value FROM score WHERE assessment_id = a.id AND dimension_id = 'dependency_surface')
    + 0.20 * (SELECT value FROM score WHERE assessment_id = a.id AND dimension_id = 'local_data_exposure')
  )) AS INTEGER) AS infra
FROM product p
JOIN vendor v ON v.id = p.vendor_id
LEFT JOIN assessment a
  ON a.product_id = p.id
 AND a.assessed_on = (SELECT MAX(assessed_on) FROM assessment WHERE product_id = p.id);

-- A product with no assessment must surface as unassessed, not as a number.
CREATE VIEW IF NOT EXISTS v_coverage AS
SELECT
  p.id AS product_id,
  p.name,
  p.platform,
  CASE WHEN a.id IS NULL THEN 'unassessed' ELSE a.grade END AS status,
  a.weighted_total
FROM product p
LEFT JOIN assessment a ON a.product_id = p.id
 AND a.assessed_on = (SELECT MAX(assessed_on) FROM assessment WHERE product_id = p.id);

-- What to assess next. Anything a human put on the board outranks anything a
-- crawler found, and within each tier usage decides.
CREATE VIEW IF NOT EXISTS v_backlog AS
SELECT
  p.id            AS product_id,
  p.name,
  p.platform,
  p.category_id,
  p.priority      AS priority,
  COALESCE(MAX(d.usage_signal), 0) AS usage,
  GROUP_CONCAT(DISTINCT d.source) AS found_via
FROM product p
LEFT JOIN discovery d ON d.product_id = p.id
WHERE NOT EXISTS (SELECT 1 FROM assessment a WHERE a.product_id = p.id)
  AND (d.id IS NOT NULL OR p.priority > 0)
GROUP BY p.id
ORDER BY p.priority DESC, usage DESC;