PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS hypotheses (
id INTEGER PRIMARY KEY AUTOINCREMENT,
hash TEXT NOT NULL UNIQUE,
content TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
);
CREATE TABLE IF NOT EXISTS models (
id INTEGER PRIMARY KEY AUTOINCREMENT,
hypothesis_id INTEGER NOT NULL,
hash TEXT NOT NULL,
model_json TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
FOREIGN KEY (hypothesis_id) REFERENCES hypotheses(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS workflow_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
hypothesis_id INTEGER NOT NULL,
model_id INTEGER,
step TEXT NOT NULL,
status TEXT NOT NULL,
message TEXT,
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
FOREIGN KEY (hypothesis_id) REFERENCES hypotheses(id) ON DELETE CASCADE,
FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_hypotheses_hash ON hypotheses(hash);
CREATE INDEX IF NOT EXISTS idx_hypotheses_created_at ON hypotheses(created_at);
CREATE INDEX IF NOT EXISTS idx_models_hypothesis_id ON models(hypothesis_id);
CREATE INDEX IF NOT EXISTS idx_models_hash ON models(hash);
CREATE INDEX IF NOT EXISTS idx_models_created_at ON models(created_at);
CREATE INDEX IF NOT EXISTS idx_workflow_logs_hypothesis_id ON workflow_logs(hypothesis_id);
CREATE INDEX IF NOT EXISTS idx_workflow_logs_model_id ON workflow_logs(model_id);
CREATE INDEX IF NOT EXISTS idx_workflow_logs_created_at ON workflow_logs(created_at);
