-- PloitMalper stateful intelligence schema.
--
-- The database holds only metadata and short fields; bulk content (full
-- reports, long finding evidence) is stored as files in the content store
-- and referenced by path.
--
-- The database holds the current state of the Malper ecosystem plus full
-- history. JSON scan artifacts remain immutable; this schema stores only the
-- evolving intelligence derived from them.
-- ---------------------------------------------------------------------------
-- Assets: scannable targets (IP, hostname, domain) with lifecycle state.
-- ---------------------------------------------------------------------------
CREATE TABLE Assets (
stable_id TEXT REQUIRED UNIQUE,
asset_type SELECT('ip', 'hostname', 'domain', 'url'),
name TEXT REQUIRED,
ip TEXT,
fqdn TEXT,
reverse_dns TEXT,
first_seen TEXT,
last_seen TEXT,
status TEXT,
metadata JSON
);
-- ---------------------------------------------------------------------------
-- Services: open network services discovered on an asset.
-- ---------------------------------------------------------------------------
CREATE TABLE Services (
stable_id TEXT REQUIRED UNIQUE,
asset_id TEXT REQUIRED,
port NUMBER,
protocol TEXT,
service_name TEXT,
product TEXT,
version TEXT,
version_str TEXT,
banner TEXT,
technologies JSON,
cpes JSON,
first_seen TEXT,
last_seen TEXT,
status TEXT,
metadata JSON
);
-- ---------------------------------------------------------------------------
-- Findings: vulnerabilities / security findings on an asset. Full detail is
-- stored as a file; `detail_path` points into the content store.
-- ---------------------------------------------------------------------------
CREATE TABLE Findings (
stable_id TEXT REQUIRED UNIQUE,
asset_id TEXT REQUIRED,
service_id TEXT,
title TEXT REQUIRED,
severity TEXT,
tool TEXT,
target_url TEXT,
detail TEXT,
detail_path TEXT,
reference TEXT,
cves JSON,
endpoints JSON,
technologies JSON,
first_seen TEXT,
last_seen TEXT,
status TEXT,
exploitability TEXT,
metadata JSON
);
-- ---------------------------------------------------------------------------
-- Observations: append-only history of how intelligence changed across runs.
-- ---------------------------------------------------------------------------
CREATE TABLE Observations (
stable_id TEXT REQUIRED UNIQUE,
run_id TEXT REQUIRED,
subject_type TEXT REQUIRED,
subject_id TEXT REQUIRED,
kind TEXT REQUIRED,
before JSON,
after JSON,
detail TEXT,
observed_at TEXT
);
-- ---------------------------------------------------------------------------
-- ScanRuns: one logical scan over a target (may cover several artifacts).
-- Report content is stored as files; ScanRuns.artifacts records each source
-- artifact including a `content_path` for report documents.
-- ---------------------------------------------------------------------------
CREATE TABLE ScanRuns (
stable_id TEXT REQUIRED UNIQUE,
target TEXT REQUIRED,
folder TEXT,
started_at TEXT,
finished_at TEXT,
tools JSON,
artifacts JSON,
content_hash TEXT,
imported_at TEXT,
stats JSON
);
-- ---------------------------------------------------------------------------
-- ExploitExecutions: append-only history of module executions planned against
-- a run. Every attempt (dry run, skipped, failed, completed, still running) is
-- recorded so exploitation activity is fully auditable alongside the findings
-- it was derived from. Loot and session references are stored as ids into the
-- framework database.
-- ---------------------------------------------------------------------------
CREATE TABLE ExploitExecutions (
execution_id TEXT REQUIRED UNIQUE,
run_id TEXT REQUIRED,
asset_id TEXT REQUIRED,
vulnerability_id TEXT REQUIRED,
module_type TEXT,
module TEXT REQUIRED,
host TEXT,
payload TEXT,
status TEXT,
start_time TEXT,
finish_time TEXT,
job_id TEXT,
session_id TEXT,
error TEXT,
loot JSON,
options JSON,
selected BOOLEAN,
created_at TEXT
);