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.

8.3 KB
Raw Blame
-- Consolidate `cases` into `aisoc_cases`.
--
-- Gap-closure wave 1.
--
-- The product has shipped with two case tables that never synchronised.
-- The console writes `aisoc_cases` — six-state machine, `sla_due_at`,
-- `evidence_chain`, `observable_graph`. Every metric reads `cases`:
-- `resolution_time` (which owns the one shared MTTR definition),
-- `metrics`, `insights`, `executive_digest`, `mssp_portfolio` and the
-- GraphQL query layer.
--
-- So **every case an analyst creates is invisible to every case metric**.
-- MTTR, `cases_opened_7d` and `cases_closed_7d` are computed over rows
-- written only by KEV-exposure sweeps, the scheduled-hunt worker and the
-- demo seed. A tenant could close fifty cases in the console and watch
-- MTTR stay null.
--
-- `aisoc_cases` survives because it holds the real user data and the
-- richer lifecycle. This migration widens it to carry everything the ORM
-- model needs, moves the rows across, and renames the old table out of
-- the way rather than dropping it — a consolidation that loses a row
-- nobody noticed is worse than two tables.

-- ── Columns the ORM `Case` model has and `aisoc_cases` lacked ───────────────
--
-- All nullable or defaulted, so existing rows stay valid.

ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS priority         TEXT;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS case_type        TEXT;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS mitre_tactics    JSONB NOT NULL DEFAULT '[]'::jsonb;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS assigned_to_id   UUID;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS assigned_at      TIMESTAMPTZ;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS created_by_id    UUID;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS sla_deadline     TIMESTAMPTZ;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS sla_breached     BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS ioc_ids          JSONB NOT NULL DEFAULT '[]'::jsonb;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS artifact_ids     JSONB NOT NULL DEFAULT '[]'::jsonb;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS ticket_refs      JSONB NOT NULL DEFAULT '[]'::jsonb;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS summary          TEXT;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS resolution       TEXT;
ALTER TABLE aisoc_cases ADD COLUMN IF NOT EXISTS lessons_learned  TEXT;

-- `sla_deadline` and `sla_due_at` are the same fact under two names. Keep
-- both columns so neither side's readers break, and backfill the new one
-- from whichever the row already had.
UPDATE aisoc_cases SET sla_deadline = sla_due_at WHERE sla_deadline IS NULL AND sla_due_at IS NOT NULL;

-- ── Move the rows ──────────────────────────────────────────────────────────
--
-- `cases.status` uses a different vocabulary ('open'/'resolved'/...) from
-- `aisoc_cases` ('new'/'triaged'/'investigating'/'contained'/'resolved'/
-- 'closed'). Mapped rather than copied: a status outside the target
-- machine would make the case unmovable in the console.
--
-- Guarded on `to_regclass` so a fresh deployment, where `cases` may not
-- exist yet, is a no-op rather than an error.

DO $$
DECLARE
    r RECORD;
BEGIN
    -- Only when `cases` is still a base TABLE.
    --
    -- `to_regclass` alone is not enough and the idempotency check caught
    -- why: the compatibility view at the bottom of this file is also
    -- called `cases`, so a second run found it non-null and tried to
    -- migrate from the view into the table it reads — failing on
    -- `COALESCE types uuid[] and jsonb cannot be matched`, because the
    -- view already exposes the converted column.
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.tables
         WHERE table_schema = 'public' AND table_name = 'cases' AND table_type = 'BASE TABLE'
    ) THEN
        RETURN;
    END IF;

    INSERT INTO aisoc_cases (
        id, tenant_id, case_number, title, description, severity, status,
        mitre_techniques, mitre_tactics, alert_ids, ioc_ids, artifact_ids,
        ticket_refs, tags, summary, resolution, lessons_learned,
        priority, case_type, assigned_to_id, assigned_at, created_by_id,
        sla_deadline, sla_due_at, sla_breached,
        opened_at, closed_at, created_at, updated_at
    )
    SELECT
        c.id, c.tenant_id, c.case_number, c.title, c.description,
        COALESCE(c.severity, 'medium'),
        CASE c.status
            WHEN 'open'          THEN 'new'
            WHEN 'in_progress'   THEN 'investigating'
            WHEN 'investigating' THEN 'investigating'
            WHEN 'triaged'       THEN 'triaged'
            WHEN 'contained'     THEN 'contained'
            WHEN 'resolved'      THEN 'resolved'
            WHEN 'closed'        THEN 'closed'
            ELSE 'new'
        END,
        COALESCE(c.mitre_techniques, '[]'::jsonb),
        COALESCE(c.mitre_tactics, '[]'::jsonb),
        -- `cases.alert_ids` is JSONB, `aisoc_cases.alert_ids` is uuid[].
        -- Unpacked element by element rather than cast, because a cast
        -- would fail the whole migration on one malformed entry while
        -- this drops only that entry.
        COALESCE(
            (SELECT array_agg(e::uuid)
               FROM jsonb_array_elements_text(COALESCE(c.alert_ids, '[]'::jsonb)) AS e
              WHERE e ~ '^[0-9a-fA-F-]{36}$'),
            ARRAY[]::uuid[]
        ),
        COALESCE(c.ioc_ids, '[]'::jsonb),
        COALESCE(c.artifact_ids, '[]'::jsonb),
        COALESCE(c.ticket_refs, '[]'::jsonb),
        COALESCE(c.tags, '[]'::jsonb),
        c.summary, c.resolution, c.lessons_learned,
        c.priority, c.case_type, c.assigned_to_id, c.assigned_at, c.created_by_id,
        c.sla_deadline, c.sla_deadline, COALESCE(c.sla_breached, FALSE),
        c.created_at, c.closed_at, c.created_at, c.updated_at
    FROM cases c
    -- An id already present means the row was migrated by an earlier run.
    WHERE NOT EXISTS (SELECT 1 FROM aisoc_cases a WHERE a.id = c.id);

    -- Child tables point at the surviving parent. Done before the rename
    -- so the constraint names are still predictable.
    --
    -- `case_tasks` and `case_timeline_events` both declared
    -- `REFERENCES cases(id)`, which is why repointing the ORM alone left
    -- SQLAlchemy unable to resolve `Case.tasks` at mapper-configuration
    -- time and took down every request that touched any model.
    FOR r IN
        SELECT tc.table_name, tc.constraint_name, kcu.column_name
          FROM information_schema.table_constraints tc
          JOIN information_schema.key_column_usage kcu
            ON tc.constraint_name = kcu.constraint_name
          JOIN information_schema.constraint_column_usage ccu
            ON tc.constraint_name = ccu.constraint_name
         WHERE tc.constraint_type = 'FOREIGN KEY' AND ccu.table_name = 'cases'
    LOOP
        EXECUTE format('ALTER TABLE %I DROP CONSTRAINT %I', r.table_name, r.constraint_name);
        EXECUTE format(
            'ALTER TABLE %I ADD CONSTRAINT %I FOREIGN KEY (%I) REFERENCES aisoc_cases(id) ON DELETE CASCADE',
            r.table_name, r.constraint_name, r.column_name
        );
    END LOOP;

    -- Renamed, not dropped. If this consolidation lost a row, the
    -- evidence of what was lost has to still exist.
    --
    -- Idempotency needs care here, and the fresh-apply-then-re-run check
    -- is what showed why. On a second pass `001_init.sql` recreates
    -- `cases` empty via CREATE TABLE IF NOT EXISTS, so this block runs
    -- again with nothing to move and an archive that already exists.
    -- Renaming then collides. The empty recreation is dropped instead,
    -- which keeps the original archive intact.
    --
    -- A compatibility VIEW named `cases` was tried first and is the
    -- wrong answer: every historical migration that does ALTER TABLE or
    -- CREATE INDEX on `cases` then fails on re-run against a view, and
    -- guarding each of them scatters this decision through the history.
    IF to_regclass('public.cases_pre_consolidation') IS NULL THEN
        ALTER TABLE cases RENAME TO cases_pre_consolidation;
    ELSE
        DROP TABLE cases;
    END IF;
END
$$;

COMMENT ON COLUMN aisoc_cases.sla_deadline IS
    'Same fact as sla_due_at, kept under both names so readers of either '
    'side of the pre-consolidation split keep working.';