Database Schema

BLACKOUT uses PostgreSQL (via Supabase) for persistent storage.

Core Tables

sites

Primary entity for monitored web properties.

CREATE TABLE sites (
  id BIGSERIAL PRIMARY KEY,
  hostname TEXT UNIQUE NOT NULL,
  name TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

findings

Deduplicated vendor detections across scans.

CREATE TABLE findings (
  id BIGSERIAL PRIMARY KEY,
  site_id BIGINT REFERENCES sites(id) NOT NULL,
  vendor_id TEXT NOT NULL,
  finding_key TEXT UNIQUE NOT NULL,
 
  -- Lifecycle
  first_seen TIMESTAMPTZ NOT NULL,
  last_seen TIMESTAMPTZ NOT NULL,
  baseline_status TEXT DEFAULT 'new',
 
  -- Classification
  bti_categories TEXT[] NOT NULL DEFAULT '{}',
  btss_score NUMERIC(4,1),
  confidence INTEGER,
  page_type TEXT NOT NULL DEFAULT 'unknown',
 
  -- Decision
  recommendation TEXT,
  remediation_playbook_id BIGINT,
 
  -- Evidence
  evidence_refs JSONB DEFAULT '[]',
 
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Finding Key Formula:

finding_key = hash(vendor_id + behavior_signature + endpoint_pattern + page_type)

baselines

Snapshot of known-good state for drift comparison.

CREATE TABLE baselines (
  id BIGSERIAL PRIMARY KEY,
  site_id BIGINT REFERENCES sites(id) NOT NULL,
  scan_id TEXT NOT NULL,
 
  vendor_set JSONB NOT NULL,
  script_hashes JSONB NOT NULL,
  domains_contacted JSONB NOT NULL,
  consent_timing JSONB NOT NULL,
  storage_patterns JSONB NOT NULL,
 
  created_at TIMESTAMPTZ DEFAULT NOW(),
  is_active BOOLEAN DEFAULT true,
  superseded_by BIGINT REFERENCES baselines(id)
);

drift_events

Individual drift detections.

CREATE TABLE drift_events (
  id BIGSERIAL PRIMARY KEY,
  site_id BIGINT REFERENCES sites(id) NOT NULL,
  scan_id TEXT NOT NULL,
  baseline_id BIGINT REFERENCES baselines(id) NOT NULL,
  finding_id BIGINT REFERENCES findings(id) NOT NULL,
 
  drift_type TEXT NOT NULL,
  severity TEXT NOT NULL,
  subject_type TEXT NOT NULL,
  subject_id TEXT,
  diff_summary JSONB NOT NULL,
  evidence_refs JSONB,
 
  created_at TIMESTAMPTZ DEFAULT NOW(),
  acknowledged_at TIMESTAMPTZ,
  acknowledged_by TEXT
);

Drift Types:

vendor_added
vendor_removed
vendor_behavior_changed
script_added
script_hash_changed
endpoint_added
consent_regression
chain_depth_increased

Scan Tables

intel_scans

Scan execution records.

CREATE TABLE intel_scans (
  id TEXT PRIMARY KEY,
  hostname TEXT NOT NULL,
  url TEXT NOT NULL,
  status TEXT DEFAULT 'pending',
  vendor_count INTEGER,
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  error TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

intel_detections

Individual vendor detections from scans.

CREATE TABLE intel_detections (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  scan_id TEXT REFERENCES intel_scans(id),
  vendor_id TEXT NOT NULL,
  vendor_name TEXT,
  bti_category TEXT,
  confidence INTEGER,
  raw_evidence JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Remediation Tables

remediation_actions

Tracked remediation work items.

CREATE TABLE remediation_actions (
  id BIGSERIAL PRIMARY KEY,
  finding_id BIGINT REFERENCES findings(id) NOT NULL,
  site_id BIGINT REFERENCES sites(id) NOT NULL,
 
  recommendation TEXT NOT NULL,
  action_type TEXT,
  action_details JSONB,
 
  owner TEXT,
  owner_team TEXT,
  status TEXT DEFAULT 'pending',
 
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
 
  evidence_pack_url TEXT,
  jira_ticket_id TEXT,
  notes TEXT
);

playbooks

Remediation playbook templates.

CREATE TABLE playbooks (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  vendor_id TEXT,
  bti_category TEXT,
  description TEXT,
  steps JSONB NOT NULL,
  containment_options JSONB,
  escalation_criteria JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Intelligence Tables

vendors

Vendor profile database.

CREATE TABLE vendors (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  domain TEXT,
  category TEXT,
  description TEXT,
  privacy_policy_url TEXT,
  btss_score NUMERIC(4,1),
  risk_factors JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

domain_posture

Aggregated site health metrics.

CREATE TABLE domain_posture (
  hostname TEXT PRIMARY KEY,
  total_vendors INTEGER DEFAULT 0,
  risk_score NUMERIC(4,1),
  btss_score NUMERIC(4,1),
  last_scan_at TIMESTAMPTZ,
  first_scan_at TIMESTAMPTZ,
  vendor_summary JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

Indexes

Key indexes for performance:

-- Findings
CREATE INDEX idx_findings_site ON findings(site_id);
CREATE INDEX idx_findings_vendor ON findings(vendor_id);
CREATE INDEX idx_findings_status ON findings(baseline_status);
CREATE INDEX idx_findings_key ON findings(finding_key);
 
-- Drift
CREATE INDEX idx_drift_site ON drift_events(site_id);
CREATE INDEX idx_drift_finding ON drift_events(finding_id);
CREATE INDEX idx_drift_unacked ON drift_events(site_id) WHERE acknowledged_at IS NULL;
 
-- Scans
CREATE INDEX idx_scans_hostname ON intel_scans(hostname);
CREATE INDEX idx_detections_scan ON intel_detections(scan_id);

Migrations

Migrations are stored in supabase/migrations/.

Run migrations:

npx supabase db push

Check migration status:

npx supabase db diff

Control Library