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
-- Migration 040: Saved natural-language hunts (Track 3, T3.4 — `/hunt` NL surface).
--
-- The `/hunt` page lets analysts ask plain-English questions ("Did we get
-- any new attacks from Iran?", "Show GitHub auth from a new device this
-- week", etc.), see the parsed query in ES|QL/SPL/KQL, and save the prompt
-- so the next shift can re-run it in one click — optionally on a cron.
--
-- This is intentionally a separate table from `aisoc_hunts` (the
-- hypothesis-driven hunt workbench from migration 014). The two surfaces
-- serve different jobs:
--
-- * `aisoc_hunts` — heavyweight, detection-engineer authored.
-- Carries hypothesis, MITRE mapping, status,
-- findings rollup, multi-platform queries.
-- Surfaced on the Hunt Workbench page.
-- * `aisoc_saved_hunts` — lightweight, tier-1 analyst authored.
-- Stores the NL question + translator output.
-- Surfaced as pills on the `/hunt` page.
--
-- Schema:
-- id - opaque uuid, surfaced in URLs (`?hunt=<id>`).
-- tenant_id - owner tenant; isolation enforced by RLS below.
-- created_by - author user; nullable so demo/system seeds can
-- omit it. Used for the "saved by" badge in the UI.
-- name - human-readable label, unique per tenant.
-- nl_query - the original plain-English question, kept verbatim
-- so we can re-translate when the translator
-- improves and re-show the prompt to the analyst.
-- translated_query - opaque JSONB { esql, kql, spl, explanation, intents }
-- — the translator owns the schema; the API never
-- inspects the contents.
-- language - preferred dialect at save time (esql|kql|spl); the
-- translator emits all three but the UI re-opens in
-- the dialect the analyst was looking at.
-- schedule - optional cron string ("0 */6 * * *"). NULL → manual
-- run only. The hunt scheduler worker picks up rows
-- where this is non-null and fires the hunt on cadence.
-- last_run_at - timestamp of the most recent execution (manual or
-- scheduled); used by the scheduler to gate cadence.
-- created_at - mint timestamp.
-- updated_at - bumped on every mutation.
--
-- Indexes:
-- - tenant_id index for the hot list path.
-- - partial index on `schedule IS NOT NULL` so the scheduler's "any
-- scheduled hunts due?" sweep stays cheap as the table grows.
-- - unique (tenant_id, name) keeps the saved-hunts list searchable; we
-- deliberately don't scope this to `created_by` so a teammate can find
-- "Iran inbound" without guessing who saved it.
BEGIN;
CREATE TABLE IF NOT EXISTS aisoc_saved_hunts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
created_by UUID REFERENCES users(id) ON DELETE SET NULL,
name VARCHAR(160) NOT NULL,
nl_query TEXT NOT NULL,
translated_query JSONB NOT NULL DEFAULT '{}'::jsonb,
language VARCHAR(16) NOT NULL DEFAULT 'esql',
schedule VARCHAR(120),
last_run_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT aisoc_saved_hunts_unique_name
UNIQUE (tenant_id, name),
CONSTRAINT aisoc_saved_hunts_language_check
CHECK (language IN ('esql', 'kql', 'spl'))
);
CREATE INDEX IF NOT EXISTS aisoc_saved_hunts_tenant_idx
ON aisoc_saved_hunts (tenant_id);
-- Partial index — the scheduler's hot read path is "any rows in this tenant
-- with a cron set?". Indexing only the scheduled subset keeps the index
-- tiny while still serving the sweep query in microseconds.
CREATE INDEX IF NOT EXISTS aisoc_saved_hunts_scheduled_idx
ON aisoc_saved_hunts (tenant_id, schedule)
WHERE schedule IS NOT NULL;
-- ============================================================
-- RLS: tenant isolation. Saved hunts are tenant-shared (every
-- analyst in the tenant can see every saved hunt) so we enforce
-- only the tenant boundary at the DB layer; user-level
-- permissions (who can delete) live in the API layer.
-- ============================================================
ALTER TABLE aisoc_saved_hunts ENABLE ROW LEVEL SECURITY;
ALTER TABLE aisoc_saved_hunts FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS aisoc_saved_hunts_tenant_isolation ON aisoc_saved_hunts;
CREATE POLICY aisoc_saved_hunts_tenant_isolation ON aisoc_saved_hunts
USING (tenant_id = current_tenant_id() OR current_tenant_id() IS NULL);
COMMIT;