Open-source AI Security Operations Center: alert fusion, LLM-agent triage, MITRE ATT&CK investigation, and a replayable decision ledger for every agent step. Self-hostable, runs with no API keys, MIT licensed. Ships an MCP server for Claude, Cursor and Continue.
Python agentic-aiai-securityalert-triageblue-teamcybersecuritydetection-engineeringincident-responselanggraphllm-agentsmcpmitre-attackmodel-context-protocolpurple-teamsecurity-operationsself-hostedsiemsoarsocthreat-detectionthreat-intelligence
-- 065: replay evaluation jobs and the decisions behind their reports.
--
-- Gap-closure Phase 1.4. An operator asks "how would this have done on my
-- alerts", the API reads their closed findings, replays them through the
-- production triage path and grades the result. This is where the answer
-- lives.
--
-- Note on the number: 064 is `064_sandbox_upload_policy.sql`, which landed
-- from a different phase while this one was in flight. The plan's deviation
-- log names 064 for this table because that was the next free number when it
-- was written. It is not any more.
--
-- Why two tables
-- --------------
-- `aisoc_replay_evaluations` answers "what did the report say". It is read on
-- every page load and every export, and it is small.
--
-- `aisoc_replay_decisions` answers "why did it say that". One row per
-- replayed finding, holding the analyst's label, the agent's verdict and the
-- evidence the agent was given. It is read when somebody disputes a number,
-- which is rare and heavy. Folding them into one table would put a few
-- megabytes of evidence behind every list query.
--
-- Why the report markdown is stored rather than re-rendered
-- ---------------------------------------------------------
-- The phase's acceptance bar is that a report reproduces byte for byte. A
-- report re-rendered later by a newer renderer is a different artefact from
-- the one the operator read, and the export would silently stop matching what
-- the page showed when it was produced. `report_markdown` is the artefact.
-- `score` is kept alongside it so the console can render structured views
-- without parsing prose back out.
--
-- Why a status column and not a queue
-- -----------------------------------
-- The job runs in-process in the API, which is where the vault and the tenant
-- session already are. `status` is how a caller polling `GET
-- /evaluations/replay/{id}` tells a run still going from one that failed, and
-- `error` is why. A run that crashed reads `failed` with a reason rather than
-- staying `running` forever, because a job that never terminates is
-- indistinguishable from a slow one.
CREATE TABLE IF NOT EXISTS aisoc_replay_evaluations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
-- 'queued' | 'running' | 'completed' | 'failed'
status TEXT NOT NULL DEFAULT 'queued',
error TEXT,
-- What was asked for.
connector_id TEXT NOT NULL,
vendor TEXT NOT NULL,
window_start TIMESTAMPTZ NOT NULL,
window_end TIMESTAMPTZ NOT NULL,
train_fraction DOUBLE PRECISION NOT NULL,
-- Both travel onto the row because both change the confidence intervals,
-- and a report whose seed is not recorded cannot be reproduced.
bootstrap_seed INTEGER NOT NULL,
bootstrap_resamples INTEGER NOT NULL,
-- Sample sizes, promoted out of the score JSON because the console shows
-- them beside the headline and a list query should not have to open a
-- JSONB document to say how much was graded.
findings_read INTEGER NOT NULL DEFAULT 0,
findings_labelled INTEGER NOT NULL DEFAULT 0,
decisions_recorded INTEGER NOT NULL DEFAULT 0,
graded INTEGER NOT NULL DEFAULT 0,
malicious_support INTEGER NOT NULL DEFAULT 0,
-- NULL when the corpus was too thin for a headline. Not 0: a zero in an
-- accuracy column says the agent got everything wrong, and "withheld" is
-- a different fact with a different remedy. `headline_withheld_reason`
-- carries the sentence the report prints.
headline_accuracy DOUBLE PRECISION,
headline_withheld_reason TEXT,
malicious_recall DOUBLE PRECISION,
-- The full `ReplayScore.as_dict()` and the method block the runner
-- produced, so the console can render every panel from one read.
score JSONB,
method JSONB,
-- The artefact the operator read, byte for byte.
report_markdown TEXT,
requested_by UUID REFERENCES users(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
started_at TIMESTAMPTZ,
completed_at TIMESTAMPTZ
);
COMMENT ON TABLE aisoc_replay_evaluations IS
'One replay evaluation: triage measured against a tenant''s own analysts on '
'their own closed findings. The stored report_markdown is the artefact the '
'operator read, not a re-render.';
COMMENT ON COLUMN aisoc_replay_evaluations.headline_accuracy IS
'NULL when withheld for a thin corpus. Never 0 to mean "not measured".';
COMMENT ON COLUMN aisoc_replay_evaluations.bootstrap_seed IS
'Seed the percentile bootstrap used. Recorded because a report that cannot '
'name its seed cannot be reproduced.';
CREATE TABLE IF NOT EXISTS aisoc_replay_decisions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
evaluation_id UUID NOT NULL REFERENCES aisoc_replay_evaluations(id) ON DELETE CASCADE,
-- Denormalised so the RLS policy on this table stands on its own rather
-- than on a join. A policy that has to reach another table to decide is a
-- policy that returns nothing when that table is itself filtered.
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
finding_id TEXT NOT NULL,
vendor TEXT NOT NULL,
rule_id TEXT,
closed_at TIMESTAMPTZ,
-- The analyst's label, canonical. 'unlabeled' means excluded from
-- accuracy, never guessed.
expected_disposition TEXT NOT NULL,
vendor_disposition TEXT NOT NULL DEFAULT '',
labelled BOOLEAN NOT NULL DEFAULT FALSE,
-- What triage said. `verdict_raw` is kept beside the canonical form
-- because a verdict the taxonomy does not recognise is a finding about
-- the product and must not be laundered into a canonical one.
verdict TEXT,
verdict_raw TEXT,
confidence DOUBLE PRECISION NOT NULL DEFAULT 0,
tier TEXT NOT NULL DEFAULT '',
-- Reasoning and the evidence it was produced from, so a disputed
-- hallucination score can be re-derived rather than re-argued.
decision JSONB NOT NULL DEFAULT '{}'::jsonb,
-- Set when triage refused the finding outright, which is neither a
-- verdict nor an abstention and is scored as neither.
error TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
COMMENT ON TABLE aisoc_replay_decisions IS
'One replayed finding: the analyst label, the agent verdict, and the evidence '
'the agent was given. Kept so a disputed score can be re-derived.';
CREATE INDEX IF NOT EXISTS idx_replay_evaluations_tenant_created
ON aisoc_replay_evaluations (tenant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_replay_evaluations_tenant_status
ON aisoc_replay_evaluations (tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_replay_decisions_evaluation
ON aisoc_replay_decisions (evaluation_id);
CREATE INDEX IF NOT EXISTS idx_replay_decisions_tenant
ON aisoc_replay_decisions (tenant_id, created_at DESC);
-- Row-level security. The `OR current_tenant_id() IS NULL` arm is what lets
-- the cross-tenant workers (retention purge, tenant deletion) reach these
-- tables on a connection that binds no tenant; dropping it makes those
-- silently see nothing. FORCE so the owner does not walk past, because the
-- owner is the role that runs migrations.
ALTER TABLE aisoc_replay_evaluations ENABLE ROW LEVEL SECURITY;
ALTER TABLE aisoc_replay_evaluations FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS aisoc_replay_evaluations_tenant ON aisoc_replay_evaluations;
CREATE POLICY aisoc_replay_evaluations_tenant ON aisoc_replay_evaluations
USING (tenant_id = current_tenant_id() OR current_tenant_id() IS NULL);
ALTER TABLE aisoc_replay_decisions ENABLE ROW LEVEL SECURITY;
ALTER TABLE aisoc_replay_decisions FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS aisoc_replay_decisions_tenant ON aisoc_replay_decisions;
CREATE POLICY aisoc_replay_decisions_tenant ON aisoc_replay_decisions
USING (tenant_id = current_tenant_id() OR current_tenant_id() IS NULL);
-- 061 set ALTER DEFAULT PRIVILEGES so new tables pick these up, but only for
-- tables created by the role that ran it. Granting explicitly means a chain
-- replayed by a different owner still leaves the runtime role able to work.
DO $$
BEGIN
IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'aisoc_app') THEN
GRANT SELECT, INSERT, UPDATE, DELETE ON aisoc_replay_evaluations TO aisoc_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON aisoc_replay_decisions TO aisoc_app;
END IF;
END
$$;