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
-- AiSOC Responder PWA migration (Phase 4B)
-- Adds the operational tables the mobile responder PWA needs:
--
-- passkey_credentials — WebAuthn passkeys per user (passwordless auth)
-- passkey_challenges — short-lived register/auth challenges
-- oncall_status — current on-call snapshot per user (available|busy|offline)
-- agent_approvals — agent-blocking approval requests with one-tap
-- approve/deny actions from the PWA
--
-- Plus a `snoozed_until` column on `alerts` so the queue view can hide
-- alerts the responder asked to defer for an hour without losing them.
--
-- All tables enforce tenant isolation via Row-Level Security so the
-- responder PWA respects the same boundaries as alerts/cases.
-- ============================================================
-- 0. Alerts: snooze support for the mobile triage queue
-- ============================================================
ALTER TABLE alerts
ADD COLUMN IF NOT EXISTS snoozed_until TIMESTAMPTZ;
ALTER TABLE alerts
ADD COLUMN IF NOT EXISTS snoozed_by_id UUID REFERENCES users(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_alerts_tenant_snoozed
ON alerts(tenant_id, snoozed_until)
WHERE snoozed_until IS NOT NULL;
-- ============================================================
-- 1. passkey_credentials (WebAuthn / FIDO2)
-- ============================================================
CREATE TABLE IF NOT EXISTS passkey_credentials (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
-- Raw WebAuthn credential ID (base64url-encoded for portability).
credential_id TEXT NOT NULL UNIQUE,
-- COSE public key, base64url-encoded.
public_key TEXT NOT NULL,
-- Anti-clone counter from the authenticator.
sign_count BIGINT NOT NULL DEFAULT 0,
-- Authenticator transports (usb, ble, nfc, internal, hybrid).
transports JSONB NOT NULL DEFAULT '[]'::jsonb,
-- Friendly name like "iPhone 15 Pro" or "YubiKey 5C NFC".
device_name VARCHAR(120) NOT NULL,
-- Optional AAGUID for telemetry / device-class display.
aaguid UUID,
-- True if the authenticator says it's a discoverable credential.
is_discoverable BOOLEAN NOT NULL DEFAULT TRUE,
last_used_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
revoked_at TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_passkeys_user
ON passkey_credentials(user_id)
WHERE revoked_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_passkeys_tenant
ON passkey_credentials(tenant_id);
ALTER TABLE passkey_credentials ENABLE ROW LEVEL SECURITY;
ALTER TABLE passkey_credentials FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS passkeys_tenant ON passkey_credentials;
CREATE POLICY passkeys_tenant ON passkey_credentials
USING (tenant_id = current_tenant_id() OR current_tenant_id() IS NULL);
-- ============================================================
-- 2. passkey_challenges (short-lived registration & auth challenges)
-- ============================================================
CREATE TABLE IF NOT EXISTS passkey_challenges (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
-- Challenge type: 'register' or 'authenticate'.
purpose VARCHAR(20) NOT NULL,
-- The pre-shared random challenge bytes, base64url-encoded.
challenge TEXT NOT NULL UNIQUE,
-- For registration we know the user; for authentication we may not
-- (discoverable creds), in which case both fields are NULL.
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
-- For passwordless conditional UI flows we record the email hint.
email_hint VARCHAR(255),
-- The hostname/origin that asked for the challenge (RP ID).
rp_id VARCHAR(255) NOT NULL,
expires_at TIMESTAMPTZ NOT NULL,
consumed_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_passkey_challenges_expiry
ON passkey_challenges(expires_at);
CREATE INDEX IF NOT EXISTS idx_passkey_challenges_user
ON passkey_challenges(user_id);
-- Challenges deliberately do NOT enforce RLS: the registration/auth start
-- endpoints insert rows on behalf of unauthenticated users (auth flow) or
-- in the tenant context of the requesting user (registration flow). The
-- challenge value itself is the secret.
-- ============================================================
-- 3. oncall_status (current on-call snapshot per user)
-- ============================================================
CREATE TABLE IF NOT EXISTS oncall_status (
user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
-- 'available' | 'busy' | 'offline'.
status VARCHAR(20) NOT NULL DEFAULT 'offline',
-- ISO-8601 schedule reference (PagerDuty/OpsGenie/manual/etc).
schedule_ref VARCHAR(200),
-- Optional rotation handle ("primary-soc", "ir-secondary", …).
rotation VARCHAR(80),
note TEXT,
until TIMESTAMPTZ,
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_oncall_tenant_status
ON oncall_status(tenant_id, status);
ALTER TABLE oncall_status ENABLE ROW LEVEL SECURITY;
ALTER TABLE oncall_status FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS oncall_tenant ON oncall_status;
CREATE POLICY oncall_tenant ON oncall_status
USING (tenant_id = current_tenant_id() OR current_tenant_id() IS NULL);
-- ============================================================
-- 4. agent_approvals (one-tap approval requests blocking agent runs)
-- ============================================================
CREATE TABLE IF NOT EXISTS agent_approvals (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
-- Optional pointers back to the originating run/case/alert.
run_id UUID REFERENCES investigation_runs(id) ON DELETE SET NULL,
case_id VARCHAR(200),
alert_id UUID,
-- Who created the approval (always the agents service).
requested_by VARCHAR(120) NOT NULL DEFAULT 'agent',
-- Who must approve. Either a specific user or a rotation/topic.
required_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
required_topic VARCHAR(80),
-- Human-readable summary surfaced in the PWA card.
title VARCHAR(200) NOT NULL,
summary TEXT NOT NULL,
risk_level VARCHAR(20) NOT NULL DEFAULT 'medium',
-- Structured action the agent intends to take if approved
-- ({ kind: "isolate_host", host: "WIN-…" }, etc).
action JSONB NOT NULL,
-- Approval lifecycle.
status VARCHAR(20) NOT NULL DEFAULT 'pending',
-- pending | approved | denied | expired | cancelled
decided_by_id UUID REFERENCES users(id) ON DELETE SET NULL,
decided_at TIMESTAMPTZ,
decision_comment TEXT,
expires_at TIMESTAMPTZ,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_agent_approvals_tenant_status
ON agent_approvals(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_agent_approvals_user
ON agent_approvals(required_user_id)
WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS idx_agent_approvals_run
ON agent_approvals(run_id);
ALTER TABLE agent_approvals ENABLE ROW LEVEL SECURITY;
ALTER TABLE agent_approvals FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS agent_approvals_tenant ON agent_approvals;
CREATE POLICY agent_approvals_tenant ON agent_approvals
USING (tenant_id = current_tenant_id() OR current_tenant_id() IS NULL);
-- ============================================================
-- 5. House-keeping: keep updated_at fresh on agent_approvals
-- ============================================================
CREATE OR REPLACE FUNCTION agent_approvals_touch_updated()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trg_agent_approvals_touch ON agent_approvals;
CREATE TRIGGER trg_agent_approvals_touch
BEFORE UPDATE ON agent_approvals
FOR EACH ROW EXECUTE FUNCTION agent_approvals_touch_updated();