-- Workstream 9: security features (secret scanning, advisories, code scanning, dependency graph, signatures).
CREATE TABLE repo_security_settings (
repo_id TEXT PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE,
secret_scanning INTEGER NOT NULL DEFAULT 1,
push_protection INTEGER NOT NULL DEFAULT 0,
private_reporting INTEGER NOT NULL DEFAULT 0,
dependency_graph INTEGER NOT NULL DEFAULT 1,
updated_at INTEGER NOT NULL
);
-- Secrets are never stored: only a SHA-256 fingerprint and a redacted preview.
CREATE TABLE secret_alerts (
id TEXT PRIMARY KEY,
repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
number INTEGER NOT NULL,
detector TEXT NOT NULL,
fingerprint TEXT NOT NULL,
preview TEXT NOT NULL,
path TEXT,
line INTEGER,
commit_sha TEXT,
state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'resolved')),
resolution TEXT CHECK (resolution IN ('revoked', 'false_positive', 'used_in_tests', 'wont_fix', 'bypassed')),
resolved_by TEXT REFERENCES accounts(id) ON DELETE SET NULL,
resolved_at INTEGER,
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
UNIQUE (repo_id, fingerprint),
UNIQUE (repo_id, number)
);
CREATE INDEX secret_alerts_state ON secret_alerts(repo_id, state, created_at DESC);
-- Pushes blocked by push protection, so the pusher can review and bypass with a reason.
CREATE TABLE secret_push_blocks (
id TEXT PRIMARY KEY,
repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
user_id TEXT REFERENCES accounts(id) ON DELETE CASCADE,
detector TEXT NOT NULL,
fingerprint TEXT NOT NULL,