Skip to content

armgit / AiSOC

Public Imported from GitHub
0

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.

6.9 KB
Raw Blame
"""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")