File size: 2,768 Bytes
d2d1903 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 | CREATE TABLE IF NOT EXISTS migrations (
id TEXT PRIMARY KEY,
description TEXT,
applied_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS tenants (
tenant_id TEXT PRIMARY KEY,
name TEXT NOT NULL,
industry TEXT DEFAULT 'logistics',
deployment_mode TEXT DEFAULT 'hosted',
status TEXT DEFAULT 'active',
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS jobs (
job_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
job_type TEXT NOT NULL,
state TEXT NOT NULL,
payload_json TEXT,
result_json TEXT,
error_json TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
started_at TEXT,
finished_at TEXT
);
CREATE TABLE IF NOT EXISTS runs (
run_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
job_id TEXT,
vertical TEXT DEFAULT 'logistics',
state TEXT NOT NULL,
output_dir TEXT,
manifest_json TEXT,
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL,
started_at TEXT,
finished_at TEXT
);
CREATE TABLE IF NOT EXISTS findings (
finding_id TEXT PRIMARY KEY,
tenant_id TEXT NOT NULL,
run_id TEXT NOT NULL,
title TEXT,
entity_id TEXT,
entity_type TEXT,
archetype TEXT,
severity TEXT,
confidence REAL,
drift_score REAL,
finding_json TEXT,
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS evidence (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tenant_id TEXT NOT NULL,
run_id TEXT NOT NULL,
finding_id TEXT NOT NULL,
metric TEXT,
source_file TEXT,
baseline_value REAL,
current_value REAL,
change_pct REAL,
confidence REAL,
evidence_json TEXT,
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS reports (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tenant_id TEXT NOT NULL,
run_id TEXT NOT NULL,
report_type TEXT NOT NULL,
path TEXT NOT NULL,
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS feedback (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tenant_id TEXT NOT NULL,
run_id TEXT,
finding_id TEXT,
user_id TEXT,
feedback_type TEXT,
notes TEXT,
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS audit_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
timestamp TEXT NOT NULL,
tenant_id TEXT NOT NULL,
user_id TEXT,
action TEXT NOT NULL,
resource_type TEXT,
resource_id TEXT,
outcome TEXT,
metadata_json TEXT
);
CREATE INDEX IF NOT EXISTS idx_jobs_tenant_state ON jobs(tenant_id, state);
CREATE INDEX IF NOT EXISTS idx_runs_tenant_state ON runs(tenant_id, state);
CREATE INDEX IF NOT EXISTS idx_findings_tenant_run ON findings(tenant_id, run_id);
CREATE INDEX IF NOT EXISTS idx_audit_logs_tenant_time ON audit_logs(tenant_id, timestamp);
|