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
"""SQLAlchemy ORM models for the persistent agent decision ledger (Phase 1A).
Three tables back the ledger:
* ``investigation_runs`` - one row per investigation kicked off by an agent
* ``investigation_events`` - append-only sequence of agent steps (LLM calls,
tool calls, decisions, reasons, errors)
* ``investigation_artifacts`` - large blobs (full prompts, responses, reports)
The schema is also defined in ``services/api/migrations/008_investigation_ledger.sql``
which is the canonical version for production: it adds Row-Level Security and an
immutability trigger that this ORM definition cannot express. Dev environments
auto-create the tables via ``Base.metadata.create_all``; the SQL migration
applies the security guardrails on top.
"""
from __future__ import annotations
import uuid
from datetime import datetime
from sqlalchemy import (
DateTime,
ForeignKey,
Integer,
Numeric,
String,
Text,
UniqueConstraint,
func,
)
from sqlalchemy.dialects.postgresql import JSONB, UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.db.database import Base
class InvestigationRun(Base):
"""One row per investigation. Status transitions: running -> completed|failed."""
__tablename__ = "investigation_runs"
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
tenant_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
ForeignKey("tenants.id", ondelete="CASCADE"),
nullable=False,
index=True,
)
case_id: Mapped[str] = mapped_column(String(200), nullable=False, index=True)
alert_summary: Mapped[str | None] = mapped_column(Text)
raw_alert: Mapped[dict | None] = mapped_column(JSONB)
model_used: Mapped[str | None] = mapped_column(String(100))
status: Mapped[str] = mapped_column(String(20), nullable=False, default="running")
error: Mapped[str | None] = mapped_column(Text)
total_tokens: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
#: Measured (gateway-reported) cost only — see migration 063. Trustworthy
#: only when ``measured_call_count > 0``; a row with a zero count holds
#: either a pre-063 list-price guess or nothing at all, and both must read
#: as "not measured" rather than as a free run.
total_cost_usd: Mapped[float] = mapped_column(Numeric(10, 4), nullable=False, default=0)
measured_call_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
#: List-price estimate for calls the gateway did not price. Always
#: presented as an estimate, never merged into ``total_cost_usd``.
estimated_cost_usd: Mapped[float] = mapped_column(Numeric(10, 4), nullable=False, default=0)
estimated_call_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
unpriced_call_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
iterations: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=func.now())
completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=func.now())
events: Mapped[list[InvestigationEvent]] = relationship(
"InvestigationEvent",
back_populates="run",
cascade="all, delete-orphan",
order_by="InvestigationEvent.seq",
)
artifacts: Mapped[list[InvestigationArtifact]] = relationship(
"InvestigationArtifact",
back_populates="run",
cascade="all, delete-orphan",
)
class InvestigationEvent(Base):
"""One row per agent step. Append-only - the SQL migration enforces immutability.
``kind`` mirrors the values of ``StepKind`` in
``services/agents/app/investigator/state.py``.
"""
__tablename__ = "investigation_events"
__table_args__ = (UniqueConstraint("run_id", "seq", name="uq_inv_events_run_seq"),)
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
run_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
ForeignKey("investigation_runs.id", ondelete="CASCADE"),
nullable=False,
index=True,
)
tenant_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
ForeignKey("tenants.id", ondelete="CASCADE"),
nullable=False,
index=True,
)
seq: Mapped[int] = mapped_column(Integer, nullable=False)
ts: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=func.now())
kind: Mapped[str] = mapped_column(String(40), nullable=False, index=True)
agent: Mapped[str] = mapped_column(String(80), nullable=False)
summary: Mapped[str] = mapped_column(Text, nullable=False)
payload: Mapped[dict | None] = mapped_column(JSONB)
input_hash: Mapped[str | None] = mapped_column(String(64))
output_hash: Mapped[str | None] = mapped_column(String(64))
duration_ms: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=func.now())
run: Mapped[InvestigationRun] = relationship("InvestigationRun", back_populates="events")
artifacts: Mapped[list[InvestigationArtifact]] = relationship(
"InvestigationArtifact",
back_populates="event",
)
class InvestigationArtifact(Base):
"""Large blobs attached to a run or event: prompts, responses, full reports."""
__tablename__ = "investigation_artifacts"
id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
run_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
ForeignKey("investigation_runs.id", ondelete="CASCADE"),
nullable=False,
index=True,
)
event_id: Mapped[uuid.UUID | None] = mapped_column(
UUID(as_uuid=True),
ForeignKey("investigation_events.id", ondelete="CASCADE"),
index=True,
)
tenant_id: Mapped[uuid.UUID] = mapped_column(
UUID(as_uuid=True),
ForeignKey("tenants.id", ondelete="CASCADE"),
nullable=False,
index=True,
)
kind: Mapped[str] = mapped_column(String(40), nullable=False, index=True)
content: Mapped[str | None] = mapped_column(Text)
blob_ref: Mapped[str | None] = mapped_column(Text)
sha256: Mapped[str] = mapped_column(String(64), nullable=False)
size_bytes: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=func.now())
run: Mapped[InvestigationRun] = relationship("InvestigationRun", back_populates="artifacts")
event: Mapped[InvestigationEvent | None] = relationship("InvestigationEvent", back_populates="artifacts")