172 lines
6.4 KiB
SQL
172 lines
6.4 KiB
SQL
CREATE TABLE projects (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
forge TEXT NOT NULL,
|
|
owner TEXT NOT NULL,
|
|
repo TEXT NOT NULL,
|
|
UNIQUE(forge, owner, repo)
|
|
);
|
|
|
|
CREATE TABLE project_memory_entries (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
project_id INTEGER NOT NULL,
|
|
key TEXT NOT NULL,
|
|
value_json TEXT NOT NULL,
|
|
source TEXT NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
UNIQUE(project_id, key)
|
|
);
|
|
|
|
CREATE TABLE project_memory_summaries (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
project_id INTEGER NOT NULL,
|
|
summary_type TEXT NOT NULL,
|
|
content TEXT NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE,
|
|
UNIQUE(project_id, summary_type)
|
|
);
|
|
|
|
CREATE TABLE review_threads (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
project_id INTEGER NOT NULL,
|
|
pull_request_id BIGINT NOT NULL,
|
|
file TEXT NOT NULL,
|
|
line INTEGER NOT NULL,
|
|
initial_comment TEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE review_thread_messages (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
thread_id INTEGER NOT NULL,
|
|
author TEXT NOT NULL,
|
|
body TEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY(thread_id) REFERENCES review_threads(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE prompt_audit_sessions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
project_id INTEGER NOT NULL,
|
|
pull_request_id BIGINT NOT NULL,
|
|
entrypoint TEXT NOT NULL,
|
|
started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
ended_at TIMESTAMP,
|
|
status TEXT NOT NULL,
|
|
error_message TEXT,
|
|
FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE prompt_hook_on_completion_call_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
session_id INTEGER NOT NULL,
|
|
event_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
sequence_no INTEGER NOT NULL,
|
|
prompt_json TEXT NOT NULL,
|
|
history_json TEXT NOT NULL,
|
|
FOREIGN KEY(session_id) REFERENCES prompt_audit_sessions(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE prompt_hook_on_completion_response_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
session_id INTEGER NOT NULL,
|
|
event_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
sequence_no INTEGER NOT NULL,
|
|
prompt_json TEXT NOT NULL,
|
|
assistant_choice_json TEXT NOT NULL,
|
|
usage_input_tokens BIGINT NOT NULL,
|
|
usage_output_tokens BIGINT NOT NULL,
|
|
usage_total_tokens BIGINT NOT NULL,
|
|
FOREIGN KEY(session_id) REFERENCES prompt_audit_sessions(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE prompt_hook_on_tool_call_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
session_id INTEGER NOT NULL,
|
|
event_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
sequence_no INTEGER NOT NULL,
|
|
tool_name TEXT NOT NULL,
|
|
tool_call_id TEXT,
|
|
internal_call_id TEXT NOT NULL,
|
|
args_json TEXT NOT NULL,
|
|
action TEXT NOT NULL,
|
|
reason TEXT,
|
|
FOREIGN KEY(session_id) REFERENCES prompt_audit_sessions(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE prompt_hook_on_tool_result_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
session_id INTEGER NOT NULL,
|
|
event_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
sequence_no INTEGER NOT NULL,
|
|
tool_name TEXT NOT NULL,
|
|
tool_call_id TEXT,
|
|
internal_call_id TEXT NOT NULL,
|
|
args_json TEXT NOT NULL,
|
|
result_json TEXT NOT NULL,
|
|
FOREIGN KEY(session_id) REFERENCES prompt_audit_sessions(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE prompt_hook_on_text_delta_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
session_id INTEGER NOT NULL,
|
|
event_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
sequence_no INTEGER NOT NULL,
|
|
text_delta TEXT NOT NULL,
|
|
aggregated_text TEXT NOT NULL,
|
|
FOREIGN KEY(session_id) REFERENCES prompt_audit_sessions(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE prompt_hook_on_tool_call_delta_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
session_id INTEGER NOT NULL,
|
|
event_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
sequence_no INTEGER NOT NULL,
|
|
tool_call_id TEXT NOT NULL,
|
|
internal_call_id TEXT NOT NULL,
|
|
tool_name TEXT,
|
|
tool_call_delta TEXT NOT NULL,
|
|
FOREIGN KEY(session_id) REFERENCES prompt_audit_sessions(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE prompt_hook_on_stream_completion_response_finish_events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
session_id INTEGER NOT NULL,
|
|
event_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
sequence_no INTEGER NOT NULL,
|
|
prompt_json TEXT NOT NULL,
|
|
response_summary_json TEXT NOT NULL,
|
|
FOREIGN KEY(session_id) REFERENCES prompt_audit_sessions(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX idx_project_memory_entries_project_id
|
|
ON project_memory_entries(project_id);
|
|
CREATE INDEX idx_project_memory_summaries_project_id
|
|
ON project_memory_summaries(project_id);
|
|
CREATE INDEX idx_review_threads_project_id
|
|
ON review_threads(project_id);
|
|
CREATE INDEX idx_review_threads_project_pr
|
|
ON review_threads(project_id, pull_request_id);
|
|
CREATE INDEX idx_review_thread_messages_thread_id
|
|
ON review_thread_messages(thread_id);
|
|
CREATE INDEX idx_prompt_audit_sessions_project_pr
|
|
ON prompt_audit_sessions(project_id, pull_request_id);
|
|
CREATE INDEX idx_prompt_audit_sessions_entrypoint_started
|
|
ON prompt_audit_sessions(entrypoint, started_at);
|
|
CREATE INDEX idx_prompt_hook_completion_call_session
|
|
ON prompt_hook_on_completion_call_events(session_id, sequence_no);
|
|
CREATE INDEX idx_prompt_hook_completion_response_session
|
|
ON prompt_hook_on_completion_response_events(session_id, sequence_no);
|
|
CREATE INDEX idx_prompt_hook_tool_call_session
|
|
ON prompt_hook_on_tool_call_events(session_id, sequence_no);
|
|
CREATE INDEX idx_prompt_hook_tool_result_session
|
|
ON prompt_hook_on_tool_result_events(session_id, sequence_no);
|
|
CREATE INDEX idx_prompt_hook_text_delta_session
|
|
ON prompt_hook_on_text_delta_events(session_id, sequence_no);
|
|
CREATE INDEX idx_prompt_hook_tool_delta_session
|
|
ON prompt_hook_on_tool_call_delta_events(session_id, sequence_no);
|
|
CREATE INDEX idx_prompt_hook_stream_finish_session
|
|
ON prompt_hook_on_stream_completion_response_finish_events(session_id, sequence_no);
|