50 lines
1.6 KiB
SQL
50 lines
1.6 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS scan_events (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
score INTEGER NOT NULL CHECK (score >= 0 AND score <= 100),
|
|
band TEXT NOT NULL CHECK (band IN ('low', 'medium', 'high')),
|
|
locale TEXT,
|
|
timezone TEXT,
|
|
timezone_offset_minutes INTEGER,
|
|
user_agent_family TEXT,
|
|
signals_json TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_scan_events_created_at
|
|
ON scan_events(created_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_scan_events_band
|
|
ON scan_events(band);
|
|
|
|
CREATE TABLE IF NOT EXISTS signal_hits (
|
|
event_id TEXT NOT NULL,
|
|
signal_id TEXT NOT NULL,
|
|
contribution INTEGER NOT NULL CHECK (contribution >= 0 AND contribution <= 100),
|
|
verdict TEXT NOT NULL CHECK (verdict IN ('low', 'medium', 'high')),
|
|
raw TEXT,
|
|
PRIMARY KEY (event_id, signal_id),
|
|
FOREIGN KEY (event_id) REFERENCES scan_events(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_signal_hits_signal_id
|
|
ON signal_hits(signal_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS fingerprint_visits (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
scan_event_id TEXT NOT NULL,
|
|
created_at TEXT NOT NULL,
|
|
visitor_key TEXT NOT NULL,
|
|
fingerprint_hash TEXT NOT NULL,
|
|
component_hashes_json TEXT NOT NULL,
|
|
component_count INTEGER NOT NULL CHECK (component_count >= 0 AND component_count <= 96),
|
|
FOREIGN KEY (scan_event_id) REFERENCES scan_events(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_fingerprint_visits_visitor_key
|
|
ON fingerprint_visits(visitor_key, created_at);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_fingerprint_visits_fingerprint_hash
|
|
ON fingerprint_visits(fingerprint_hash, created_at);
|