66 lines
2.2 KiB
SQL
66 lines
2.2 KiB
SQL
CREATE TABLE IF NOT EXISTS projects (
|
|
id TEXT PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
path TEXT NOT NULL,
|
|
cloned_from TEXT,
|
|
base_branch TEXT NOT NULL DEFAULT 'main',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS tuleap_credentials (
|
|
id TEXT PRIMARY KEY,
|
|
tuleap_url TEXT NOT NULL,
|
|
username TEXT NOT NULL,
|
|
password_encrypted TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS watched_trackers (
|
|
id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
tracker_id INTEGER NOT NULL,
|
|
tracker_label TEXT NOT NULL,
|
|
polling_interval INTEGER NOT NULL DEFAULT 10,
|
|
agent_config_json TEXT NOT NULL,
|
|
filters_json TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS processed_tickets (
|
|
id TEXT PRIMARY KEY,
|
|
tracker_id TEXT NOT NULL REFERENCES watched_trackers(id) ON DELETE CASCADE,
|
|
artifact_id INTEGER NOT NULL,
|
|
artifact_title TEXT NOT NULL,
|
|
artifact_data TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'Pending',
|
|
analyst_report TEXT,
|
|
developer_report TEXT,
|
|
worktree_path TEXT,
|
|
branch_name TEXT,
|
|
detected_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
processed_at TEXT
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_processed_tickets_tracker_artifact_unique
|
|
ON processed_tickets(tracker_id, artifact_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS worktrees (
|
|
id TEXT PRIMARY KEY,
|
|
ticket_id TEXT NOT NULL REFERENCES processed_tickets(id),
|
|
path TEXT NOT NULL,
|
|
branch_name TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'Active',
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
|
|
merged_at TEXT,
|
|
merged_into TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS notifications (
|
|
id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
ticket_id TEXT REFERENCES processed_tickets(id),
|
|
type TEXT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
message TEXT NOT NULL,
|
|
read INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
|
|
);
|