sql/attack_chains_schema.sql - master - cyberknowledge/attack_chains

sql/attack_chains_schema.sql
108 строк4 KB
Codex Agent
Initialize attack_chains repository with schema, docs, and samples
09 июл 2026, 13:17
09 июл 2026, 13:17e0c9468
-- attack_chains PostgreSQL schema
-- Goal: preserve full source payload while minimizing redundancy.

CREATE SCHEMA IF NOT EXISTS attack_chains;

CREATE TABLE IF NOT EXISTS attack_chains.sources (
    source_id TEXT PRIMARY KEY,
    name TEXT,
    priority TEXT,
    status_mode TEXT,
    url TEXT,
    loader TEXT,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS attack_chains.techniques (
    technique_id TEXT PRIMARY KEY,
    tactic TEXT,
    subtechnique_name TEXT,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS attack_chains.chains (
    chain_id TEXT PRIMARY KEY,
    source_id TEXT NOT NULL REFERENCES attack_chains.sources(source_id) ON UPDATE CASCADE,
    external_id TEXT,
    title TEXT NOT NULL,
    description TEXT,
    campaign TEXT,
    actor_name TEXT,
    actor_mitre_intrusion_set TEXT,
    provenance_url TEXT,
    provenance_retrieved_at TIMESTAMPTZ,
    provenance_license TEXT,
    provenance_lang TEXT,
    sft_instruction TEXT,
    sft_input TEXT,
    sft_output TEXT,
    sft_reasoning_cot TEXT,
    raw_payload JSONB NOT NULL,
    loaded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_chain_source_external UNIQUE (source_id, external_id)
);

CREATE INDEX IF NOT EXISTS idx_chains_source ON attack_chains.chains(source_id);
CREATE INDEX IF NOT EXISTS idx_chains_external_id ON attack_chains.chains(external_id);
CREATE INDEX IF NOT EXISTS idx_chains_loaded_at ON attack_chains.chains(loaded_at DESC);
CREATE INDEX IF NOT EXISTS idx_chains_title_tsv ON attack_chains.chains USING GIN (to_tsvector('simple', COALESCE(title, '') || ' ' || COALESCE(description, '')));
CREATE INDEX IF NOT EXISTS idx_chains_raw_payload_gin ON attack_chains.chains USING GIN (raw_payload);

CREATE TABLE IF NOT EXISTS attack_chains.chain_actor_aliases (
    chain_id TEXT NOT NULL REFERENCES attack_chains.chains(chain_id) ON DELETE CASCADE,
    alias TEXT NOT NULL,
    PRIMARY KEY (chain_id, alias)
);

CREATE TABLE IF NOT EXISTS attack_chains.chain_sectors (
    chain_id TEXT NOT NULL REFERENCES attack_chains.chains(chain_id) ON DELETE CASCADE,
    sector TEXT NOT NULL,
    PRIMARY KEY (chain_id, sector)
);

CREATE TABLE IF NOT EXISTS attack_chains.chain_steps (
    step_id BIGSERIAL PRIMARY KEY,
    chain_id TEXT NOT NULL REFERENCES attack_chains.chains(chain_id) ON DELETE CASCADE,
    step_order INTEGER NOT NULL CHECK (step_order > 0),
    label TEXT NOT NULL,
    narrative TEXT,
    ioc JSONB,
    timestamp_hint TEXT,
    UNIQUE (chain_id, step_order)
);

CREATE INDEX IF NOT EXISTS idx_chain_steps_chain_id ON attack_chains.chain_steps(chain_id);
CREATE INDEX IF NOT EXISTS idx_chain_steps_narrative_tsv ON attack_chains.chain_steps USING GIN (to_tsvector('simple', COALESCE(label, '') || ' ' || COALESCE(narrative, '')));

CREATE TABLE IF NOT EXISTS attack_chains.step_techniques (
    step_id BIGINT NOT NULL REFERENCES attack_chains.chain_steps(step_id) ON DELETE CASCADE,
    technique_id TEXT NOT NULL REFERENCES attack_chains.techniques(technique_id) ON UPDATE CASCADE,
    tactic TEXT,
    subtechnique_name TEXT,
    PRIMARY KEY (step_id, technique_id)
);

CREATE INDEX IF NOT EXISTS idx_step_techniques_tid ON attack_chains.step_techniques(technique_id);

CREATE TABLE IF NOT EXISTS attack_chains.step_evidence_refs (
    step_id BIGINT NOT NULL REFERENCES attack_chains.chain_steps(step_id) ON DELETE CASCADE,
    project TEXT NOT NULL,
    ref TEXT NOT NULL,
    PRIMARY KEY (step_id, project, ref)
);

CREATE INDEX IF NOT EXISTS idx_step_evidence_project ON attack_chains.step_evidence_refs(project);

CREATE TABLE IF NOT EXISTS attack_chains.chain_edges (
    chain_id TEXT NOT NULL REFERENCES attack_chains.chains(chain_id) ON DELETE CASCADE,
    from_step_order INTEGER NOT NULL,
    to_step_order INTEGER NOT NULL,
    condition TEXT,
    PRIMARY KEY (chain_id, from_step_order, to_step_order),
    CONSTRAINT fk_chain_edges_from_step FOREIGN KEY (chain_id, from_step_order)
        REFERENCES attack_chains.chain_steps(chain_id, step_order) ON DELETE CASCADE,
    CONSTRAINT fk_chain_edges_to_step FOREIGN KEY (chain_id, to_step_order)
        REFERENCES attack_chains.chain_steps(chain_id, step_order) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_chain_edges_chain_id ON attack_chains.chain_edges(chain_id);