schema_sqlite.sql

CREATE TABLE IF NOT EXISTS samples (
	id            INTEGER PRIMARY KEY AUTOINCREMENT,
	sha256        TEXT UNIQUE NOT NULL,
	source        TEXT NOT NULL DEFAULT '',
	feed          TEXT NOT NULL DEFAULT '',
	ecosystem     TEXT NOT NULL DEFAULT '',
	url           TEXT NOT NULL DEFAULT '',
	-- domain is the registered domain (eTLD+1), populated by the Go writer
	-- via golang.org/x/net/publicsuffix.
	domain        TEXT NOT NULL DEFAULT '',
	-- package is the software package this file belongs to (parsed from
	-- the download filename via pkgparse.ParseFilename).
	package       TEXT NOT NULL DEFAULT '',
	version       TEXT NOT NULL DEFAULT '',
	-- purl_base is the version-less canonical PURL (e.g. "pkg:npm/lodash"); the
	-- package identity across versions. See schema.sql for rationale.
	purl_base     TEXT NOT NULL DEFAULT '',
	filename      TEXT NOT NULL DEFAULT '',
	-- file_type, score, formula, litmus_score are GENERATED from the JSONB
	-- source columns. Writing to them is an error; readers see the same
	-- derived values they always did.
	file_type     TEXT GENERATED ALWAYS AS
	                   (COALESCE(json_extract(cleave_result, '$.files[0].type'), json_extract(cleave_result, '$.fs[0].type'), ''))
	                   STORED,
	size_bytes    INTEGER NOT NULL DEFAULT 0,
	-- label: 'bad' > 'good' > 'sighted' > 'unknown' (see labelRank in hopper.go).
	label         TEXT NOT NULL DEFAULT 'unknown',
	label_source  TEXT NOT NULL DEFAULT '',
	cleave_result TEXT,
	litmus_result TEXT,
	litmus_score  REAL GENERATED ALWAYS AS
	                   (COALESCE(json_extract(litmus_result, '$.prob'), 0))
	                   STORED,
	-- See schema.sql: collector provenance sidecar JSON + artifact fetch time.
	provenance    TEXT,
	fetched_at    TIMESTAMPTZ,
	path  TEXT NOT NULL DEFAULT '',
	status        TEXT NOT NULL DEFAULT '',
	note          TEXT NOT NULL DEFAULT '',
	canonical_sha256 TEXT NOT NULL DEFAULT '',
	parent        TEXT NOT NULL DEFAULT '',
	skip          TEXT NOT NULL DEFAULT '',
	formula       TEXT GENERATED ALWAYS AS
	                   (COALESCE(json_extract(cleave_result, '$.files[0].mol'), json_extract(cleave_result, '$.fs[0].f'), ''))
	                   STORED,
	elements      TEXT NOT NULL DEFAULT '',
	score         INTEGER GENERATED ALWAYS AS
	                   (COALESCE(json_extract(cleave_result, '$.files[0].risk'), json_extract(cleave_result, '$.fs[0].x'), 0))
	                   STORED,
	max_crit      INTEGER NOT NULL DEFAULT 0,
	suspicious_count INTEGER NOT NULL DEFAULT 0,
	-- corroborated: 1 when an external threat feed has cited this sample's sha256
	-- or purl_base (see the sightings table). SQLite mirror of the PG boolean.
	corroborated  INTEGER NOT NULL DEFAULT 0,
	created_at    DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f', 'now')),
	updated_at    DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%f', 'now')),

	analyzed_at   DATETIME,
	first_analyzed_at DATETIME,
	-- See schema.sql: set once when a worker is first handed this sample.
	claimed_first_at DATETIME,
	last_error_at DATETIME,
	mtime         DATETIME,
	marker_mtime  DATETIME,
	claimed_by    TEXT NOT NULL DEFAULT '',
	claimed_at    DATETIME,
	traits_version TEXT NOT NULL DEFAULT ''
);

CREATE INDEX IF NOT EXISTS idx_samples_label ON samples(label);
CREATE INDEX IF NOT EXISTS idx_samples_file_type ON samples(file_type);
CREATE INDEX IF NOT EXISTS idx_samples_unanalyzed ON samples(sha256) WHERE cleave_result IS NULL;
CREATE INDEX IF NOT EXISTS idx_samples_status ON samples(status, updated_at);
CREATE INDEX IF NOT EXISTS idx_samples_path ON samples(path);
CREATE INDEX IF NOT EXISTS idx_samples_parent ON samples(parent) WHERE parent != '';

CREATE TABLE IF NOT EXISTS reports (
	id          INTEGER PRIMARY KEY AUTOINCREMENT,
	sha256      TEXT NOT NULL REFERENCES samples(sha256),
	report_type TEXT NOT NULL,
	content     TEXT NOT NULL,
	provider    TEXT NOT NULL DEFAULT '',
	duration_ms INTEGER NOT NULL DEFAULT 0,
	created_at  DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

CREATE INDEX IF NOT EXISTS idx_reports_sha256_type ON reports(sha256, report_type);
CREATE INDEX IF NOT EXISTS idx_reports_sha256_type_created ON reports(sha256, report_type, created_at DESC);

-- sightings: external-corroboration ledger. SQLite mirror of schema.sql's table,
-- where the columns are explained. subject is a sha256 or a PURL; the key
-- includes affected so one source can make two claims about one package.
CREATE TABLE IF NOT EXISTS sightings (
	source       TEXT NOT NULL,
	subject      TEXT NOT NULL,
	url          TEXT NOT NULL DEFAULT '',
	note         TEXT NOT NULL DEFAULT '',
	operator     TEXT NOT NULL DEFAULT '',
	affected     TEXT NOT NULL DEFAULT '',
	claim        TEXT NOT NULL DEFAULT 'malicious',
	filename     TEXT NOT NULL DEFAULT '',
	-- Opaque provider retrieval identifier. A hint, never artifact identity.
	handle       TEXT NOT NULL DEFAULT '',
	-- basis is how the source arrived at the claim: 'predicted' (a detector
	-- or model fired and nobody adjudicated it), 'hosted' (the source holds
	-- the artifact as malware) or 'reviewed' (a person adjudicated the report
	-- before publication). Stamped by the producer from its parallax source
	-- definition, for the same reason operator is: the judgement belongs
	-- beside the definition it is about, and that lives in a module hopper
	-- must not depend on. A copy of the list here would be a second opinion
	-- that drifts, which is exactly what TrustedBadSources was.
	--
	-- It names a FACT, not a policy: "enough on its own" is enough for what,
	-- and gauntlet, promoter and /v1/lookup each mean a different bar. Each
	-- consumer applies its own threshold to this; see hopper.Assess.
	--
	-- 'predicted' is the fail-safe default, so rows written before the column
	-- existed under-count confidence until their feed re-pushes.
	basis        TEXT NOT NULL DEFAULT 'predicted',
	published_at DATETIME,
	first_seen   DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
	PRIMARY KEY (source, subject, affected)
);

CREATE INDEX IF NOT EXISTS idx_sightings_subject ON sightings(subject);

CREATE INDEX IF NOT EXISTS idx_sightings_acquisition_recent
	ON sightings(first_seen DESC) WHERE claim IN ('malicious', 'suspicious');

CREATE TABLE IF NOT EXISTS sighting_acquisitions (
	target       TEXT PRIMARY KEY,
	attempts     INTEGER NOT NULL DEFAULT 0,
	acquired     INTEGER NOT NULL DEFAULT 0,
	last_attempt DATETIME,
	next_attempt DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
	last_error   TEXT NOT NULL DEFAULT '',
	-- Set when an attempt reported an outcome. Claimed-but-never-finished is
	-- the only way a terminal target silently misses its recovery; see
	-- schema.sql.
	finished_at  DATETIME
);

CREATE INDEX IF NOT EXISTS idx_sighting_acquisitions_due
	ON sighting_acquisitions(next_attempt) WHERE acquired = 0;

-- The finished_at index is created in migrateSQLite, after the ADD COLUMN, for
-- the same reason schema.sql does not carry it: this file runs first, and on an
-- existing database the CREATE TABLE above is a no-op.

-- SQLite mirror of schema.sql's sightings_corroborate triggers: no operation on
-- the ledger can leave samples.corroborated behind. SQLite has no TG_OP, so the
-- one PG function becomes three bodies. Kept in step with
-- liteSightingCorroborationTriggers in sqlite.go, which creates the same three
-- on an already-migrated database.
CREATE TRIGGER IF NOT EXISTS sightings_corroborate_trg
AFTER INSERT ON sightings
FOR EACH ROW
BEGIN
	UPDATE samples SET corroborated = 1
	 WHERE corroborated = 0 AND sha256 = NEW.subject;
	UPDATE samples SET corroborated = 1
	-- Narrowed to the releases the claim actually names; see the Postgres
	-- trigger in schema.sql for why. SQLite has no regex, so "names exact
	-- releases" is a GLOB, and list membership a LIKE over a comma-delimited
	-- copy of the scope.
	 WHERE purl_base = NEW.subject AND purl_base != '' AND corroborated = 0
	   AND (
	     NOT (NEW.affected GLOB '[0-9]*')
	     OR ',' || replace(NEW.affected, ' ', '') || ',' LIKE '%,' || version || ',%'
	   );
END;

-- Only once the LAST citation is gone: two sources naming one package is the
-- normal case, and dropping one must not uncorroborate the sample.
CREATE TRIGGER IF NOT EXISTS sightings_uncorroborate_trg
AFTER DELETE ON sightings
FOR EACH ROW
WHEN NOT EXISTS (SELECT 1 FROM sightings WHERE subject = OLD.subject)
BEGIN
	UPDATE samples SET corroborated = 0
	 WHERE corroborated = 1 AND sha256 = OLD.subject;
	UPDATE samples SET corroborated = 0
	 WHERE purl_base = OLD.subject AND purl_base != '' AND corroborated = 1;
END;

CREATE TRIGGER IF NOT EXISTS sightings_resubject_trg
AFTER UPDATE OF subject ON sightings
FOR EACH ROW
WHEN OLD.subject IS NOT NEW.subject
BEGIN
	UPDATE samples SET corroborated = 0
	 WHERE corroborated = 1 AND sha256 = OLD.subject
	   AND NOT EXISTS (SELECT 1 FROM sightings WHERE subject = OLD.subject);
	UPDATE samples SET corroborated = 0
	 WHERE purl_base = OLD.subject AND purl_base != '' AND corroborated = 1
	   AND NOT EXISTS (SELECT 1 FROM sightings WHERE subject = OLD.subject);
	UPDATE samples SET corroborated = 1
	 WHERE corroborated = 0 AND sha256 = NEW.subject;
	UPDATE samples SET corroborated = 1
	 WHERE purl_base = NEW.subject AND purl_base != '' AND corroborated = 0
	   AND (
	     NOT (NEW.affected GLOB '[0-9]*')
	     OR ',' || replace(NEW.affected, ' ', '') || ',' LIKE '%,' || version || ',%'
	   );
END;

CREATE TABLE IF NOT EXISTS workers (
	name      TEXT PRIMARY KEY,
	last_seen DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
	slots     INTEGER NOT NULL DEFAULT 1,
	version   TEXT NOT NULL DEFAULT '',
	traits    TEXT NOT NULL DEFAULT '',
	analyzed  INTEGER NOT NULL DEFAULT 0,
	errors    INTEGER NOT NULL DEFAULT 0
);

-- hopper_kv stores internal key/value state that needs to survive process
-- restart but doesn't belong in a domain table.
CREATE TABLE IF NOT EXISTS hopper_kv (
	key        TEXT PRIMARY KEY,
	value      TEXT NOT NULL,
	updated_at DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);