api/src/services/database.tsblame
View source
3e3af551import Database from "better-sqlite3";
3e3af552import { mkdirSync } from "fs";
3e3af553import { dirname } from "path";
3e3af554
3e3af555export function initDatabase(dbPath: string): Database.Database {
3e3af556 mkdirSync(dirname(dbPath), { recursive: true });
3e3af557 const db = new Database(dbPath);
3e3af558
3e3af559 // Enable WAL mode for better concurrent access
3e3af5510 db.pragma("journal_mode = WAL");
3e3af5511 db.pragma("foreign_keys = ON");
3e3af5512
3e3af5513 // Run migrations
3e3af5514 migrate(db);
3e3af5515
3e3af5516 return db;
3e3af5517}
3e3af5518
3e3af5519function migrate(db: Database.Database) {
3e3af5520 db.exec(`
3e3af5521 CREATE TABLE IF NOT EXISTS users (
3c994d322 id INTEGER PRIMARY KEY,
3e3af5523 username TEXT UNIQUE NOT NULL,
3e3af5524 display_name TEXT,
3e3af5525 created_at TEXT DEFAULT (datetime('now')),
3e3af5526 updated_at TEXT DEFAULT (datetime('now'))
3e3af5527 );
3e3af5528
3e3af5529 CREATE TABLE IF NOT EXISTS repos (
3e3af5530 id INTEGER PRIMARY KEY AUTOINCREMENT,
3e3af5531 owner_id INTEGER NOT NULL REFERENCES users(id),
3e3af5532 name TEXT NOT NULL,
3e3af5533 description TEXT,
3e3af5534 default_branch TEXT DEFAULT 'main',
3e3af5535 is_private INTEGER DEFAULT 0,
3e3af5536 mononoke_repo_id INTEGER,
3e3af5537 created_at TEXT DEFAULT (datetime('now')),
3e3af5538 updated_at TEXT DEFAULT (datetime('now')),
3e3af5539 UNIQUE(owner_id, name)
3e3af5540 );
3e3af5541
d12933e42 CREATE TABLE IF NOT EXISTS diffs (
3e3af5543 id INTEGER PRIMARY KEY AUTOINCREMENT,
3e3af5544 repo_id INTEGER NOT NULL REFERENCES repos(id),
3e3af5545 number INTEGER NOT NULL,
3e3af5546 title TEXT NOT NULL,
3e3af5547 description TEXT,
3e3af5548 author_id INTEGER NOT NULL REFERENCES users(id),
2ec686849 head_commit TEXT NOT NULL DEFAULT '',
2ec686850 base_commit TEXT,
2ec686851 status TEXT DEFAULT 'open' CHECK(status IN ('open', 'landed', 'closed')),
3e3af5552 created_at TEXT DEFAULT (datetime('now')),
3e3af5553 updated_at TEXT DEFAULT (datetime('now')),
2ec686854 landed_at TEXT,
2ec686855 landed_by INTEGER REFERENCES users(id),
3e3af5556 UNIQUE(repo_id, number)
3e3af5557 );
3e3af5558
3e3af5559 CREATE TABLE IF NOT EXISTS reviews (
3e3af5560 id INTEGER PRIMARY KEY AUTOINCREMENT,
d12933e61 diff_id INTEGER NOT NULL REFERENCES diffs(id),
3e3af5562 reviewer_id INTEGER NOT NULL REFERENCES users(id),
3e3af5563 status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'changes_requested')),
3e3af5564 body TEXT,
3e3af5565 created_at TEXT DEFAULT (datetime('now')),
3e3af5566 updated_at TEXT DEFAULT (datetime('now'))
3e3af5567 );
3e3af5568
3e3af5569 CREATE TABLE IF NOT EXISTS comments (
3e3af5570 id INTEGER PRIMARY KEY AUTOINCREMENT,
d12933e71 diff_id INTEGER NOT NULL REFERENCES diffs(id),
3e3af5572 author_id INTEGER NOT NULL REFERENCES users(id),
3e3af5573 body TEXT NOT NULL,
3e3af5574 file_path TEXT,
3e3af5575 line_number INTEGER,
3e3af5576 side TEXT CHECK(side IN ('left', 'right')),
3e3af5577 commit_sha TEXT,
3e3af5578 parent_id INTEGER REFERENCES comments(id),
3e3af5579 created_at TEXT DEFAULT (datetime('now')),
3e3af5580 updated_at TEXT DEFAULT (datetime('now'))
3e3af5581 );
3e3af5582
0101b6083 CREATE INDEX IF NOT EXISTS idx_repos_owner ON repos(owner_type, owner_id);
d12933e84 CREATE INDEX IF NOT EXISTS idx_diffs_repo ON diffs(repo_id);
d12933e85 CREATE INDEX IF NOT EXISTS idx_diffs_author ON diffs(author_id);
80fafdf86
80fafdf87 -- Canopy CI/CD
80fafdf88
37f693889 CREATE TABLE IF NOT EXISTS pipelines (
37f693890 id INTEGER PRIMARY KEY AUTOINCREMENT,
37f693891 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
37f693892 name TEXT NOT NULL,
37f693893 file TEXT NOT NULL,
37f693894 created_at TEXT DEFAULT (datetime('now')),
37f693895 UNIQUE(repo_id, name)
37f693896 );
37f693897
80fafdf98 CREATE TABLE IF NOT EXISTS pipeline_runs (
80fafdf99 id INTEGER PRIMARY KEY AUTOINCREMENT,
80fafdf100 repo_id INTEGER NOT NULL REFERENCES repos(id),
80fafdf101 pipeline_name TEXT NOT NULL,
80fafdf102 pipeline_file TEXT NOT NULL,
80fafdf103 trigger_type TEXT NOT NULL DEFAULT 'push'
d12933e104 CHECK(trigger_type IN ('push', 'diff', 'manual', 'schedule')),
80fafdf105 trigger_ref TEXT,
80fafdf106 commit_id TEXT,
818dc90107 commit_message TEXT,
80fafdf108 status TEXT NOT NULL DEFAULT 'pending'
80fafdf109 CHECK(status IN ('pending', 'running', 'passed', 'failed', 'cancelled')),
80fafdf110 started_at TEXT,
80fafdf111 finished_at TEXT,
80fafdf112 duration_ms INTEGER,
80fafdf113 created_at TEXT DEFAULT (datetime('now'))
80fafdf114 );
80fafdf115
80fafdf116 CREATE TABLE IF NOT EXISTS pipeline_steps (
80fafdf117 id INTEGER PRIMARY KEY AUTOINCREMENT,
80fafdf118 run_id INTEGER NOT NULL REFERENCES pipeline_runs(id) ON DELETE CASCADE,
80fafdf119 step_index INTEGER NOT NULL,
80fafdf120 name TEXT NOT NULL,
80fafdf121 image TEXT NOT NULL,
80fafdf122 status TEXT NOT NULL DEFAULT 'pending'
80fafdf123 CHECK(status IN ('pending', 'running', 'passed', 'failed', 'skipped')),
80fafdf124 exit_code INTEGER,
80fafdf125 started_at TEXT,
80fafdf126 finished_at TEXT,
80fafdf127 duration_ms INTEGER
80fafdf128 );
80fafdf129
80fafdf130 CREATE TABLE IF NOT EXISTS step_logs (
80fafdf131 id INTEGER PRIMARY KEY AUTOINCREMENT,
80fafdf132 step_id INTEGER NOT NULL REFERENCES pipeline_steps(id) ON DELETE CASCADE,
80fafdf133 stream TEXT NOT NULL DEFAULT 'stdout' CHECK(stream IN ('stdout', 'stderr')),
80fafdf134 content TEXT NOT NULL,
80fafdf135 created_at TEXT DEFAULT (datetime('now'))
80fafdf136 );
80fafdf137
80fafdf138 CREATE TABLE IF NOT EXISTS canopy_secrets (
80fafdf139 id INTEGER PRIMARY KEY AUTOINCREMENT,
80fafdf140 repo_id INTEGER NOT NULL REFERENCES repos(id),
80fafdf141 name TEXT NOT NULL,
80fafdf142 encrypted_value TEXT NOT NULL,
80fafdf143 created_at TEXT DEFAULT (datetime('now')),
80fafdf144 updated_at TEXT DEFAULT (datetime('now')),
80fafdf145 UNIQUE(repo_id, name)
80fafdf146 );
80fafdf147
80fafdf148 CREATE TABLE IF NOT EXISTS bookmark_state (
80fafdf149 repo_name TEXT NOT NULL,
80fafdf150 bookmark_name TEXT NOT NULL,
80fafdf151 commit_id TEXT NOT NULL,
80fafdf152 updated_at TEXT DEFAULT (datetime('now')),
80fafdf153 PRIMARY KEY (repo_name, bookmark_name)
80fafdf154 );
80fafdf155
79efd41156 CREATE TABLE IF NOT EXISTS orgs (
79efd41157 id INTEGER PRIMARY KEY,
79efd41158 name TEXT UNIQUE NOT NULL,
79efd41159 display_name TEXT,
79efd41160 created_at TEXT DEFAULT (datetime('now')),
79efd41161 updated_at TEXT DEFAULT (datetime('now'))
79efd41162 );
79efd41163
37f6938164 CREATE INDEX IF NOT EXISTS idx_pipelines_repo ON pipelines(repo_id);
80fafdf165 CREATE INDEX IF NOT EXISTS idx_pipeline_runs_repo ON pipeline_runs(repo_id);
80fafdf166 CREATE INDEX IF NOT EXISTS idx_pipeline_runs_status ON pipeline_runs(status);
80fafdf167 CREATE INDEX IF NOT EXISTS idx_pipeline_steps_run ON pipeline_steps(run_id);
80fafdf168 CREATE INDEX IF NOT EXISTS idx_step_logs_step ON step_logs(step_id);
80fafdf169 CREATE INDEX IF NOT EXISTS idx_canopy_secrets_repo ON canopy_secrets(repo_id);
3e3af55170 `);
79efd41171
79efd41172 // Add owner_type to repos
79efd41173 const repoCols = db.prepare("PRAGMA table_info(repos)").all() as { name: string }[];
79efd41174 if (!repoCols.map((c) => c.name).includes("owner_type")) {
79efd41175 db.exec("ALTER TABLE repos ADD COLUMN owner_type TEXT NOT NULL DEFAULT 'user'");
79efd41176 }
79efd41177
0101b60178 // Rebuild legacy repos schema:
0101b60179 // - remove stale FK repos.owner_id -> users(id) so org-owned repos can be created
0101b60180 // - make uniqueness owner_type-aware
0101b60181 const reposTable = db
0101b60182 .prepare("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'repos'")
0101b60183 .get() as { sql?: string } | undefined;
0101b60184 const reposSql = reposTable?.sql ?? "";
0101b60185 const needsReposRebuild =
0101b60186 /REFERENCES\s+users\s*\(\s*id\s*\)/i.test(reposSql) ||
0101b60187 /UNIQUE\s*\(\s*owner_id\s*,\s*name\s*\)/i.test(reposSql);
0101b60188
0101b60189 if (needsReposRebuild) {
0101b60190 db.exec("PRAGMA foreign_keys = OFF");
0101b60191 db.exec("BEGIN TRANSACTION");
0101b60192 try {
0101b60193 db.exec("DROP VIEW IF EXISTS repos_with_owner");
0101b60194 db.exec("ALTER TABLE repos RENAME TO repos_legacy");
0101b60195 db.exec(`
0101b60196 CREATE TABLE repos (
0101b60197 id INTEGER PRIMARY KEY AUTOINCREMENT,
0101b60198 owner_id INTEGER NOT NULL,
0101b60199 owner_type TEXT NOT NULL DEFAULT 'user' CHECK(owner_type IN ('user', 'org')),
0101b60200 name TEXT NOT NULL,
0101b60201 description TEXT,
0101b60202 default_branch TEXT DEFAULT 'main',
0101b60203 is_private INTEGER DEFAULT 0,
0101b60204 mononoke_repo_id INTEGER,
0101b60205 created_at TEXT DEFAULT (datetime('now')),
0101b60206 updated_at TEXT DEFAULT (datetime('now')),
0101b60207 UNIQUE(owner_type, owner_id, name)
0101b60208 )
0101b60209 `);
0101b60210 db.exec(`
0101b60211 INSERT INTO repos (
0101b60212 id,
0101b60213 owner_id,
0101b60214 owner_type,
0101b60215 name,
0101b60216 description,
0101b60217 default_branch,
0101b60218 is_private,
0101b60219 mononoke_repo_id,
0101b60220 created_at,
0101b60221 updated_at
0101b60222 )
0101b60223 SELECT
0101b60224 id,
0101b60225 owner_id,
0101b60226 COALESCE(owner_type, 'user'),
0101b60227 name,
0101b60228 description,
0101b60229 default_branch,
0101b60230 is_private,
0101b60231 mononoke_repo_id,
0101b60232 created_at,
0101b60233 updated_at
0101b60234 FROM repos_legacy
0101b60235 `);
0101b60236 db.exec("DROP TABLE repos_legacy");
0101b60237 db.exec("CREATE INDEX IF NOT EXISTS idx_repos_owner ON repos(owner_type, owner_id)");
0101b60238 db.exec("COMMIT");
0101b60239 } catch (err) {
0101b60240 db.exec("ROLLBACK");
0101b60241 throw err;
0101b60242 } finally {
0101b60243 db.exec("PRAGMA foreign_keys = ON");
0101b60244 }
0101b60245 }
0101b60246
db59b65247 // Repair bad FK targets introduced when repos was renamed during rebuild.
db59b65248 // Some SQLite versions rewrote dependent FK clauses to repos_legacy.
db59b65249 repairLegacyRepoForeignKeys(db);
db59b65250
3bf6e95251 // Migrate merge_requests -> diffs, merge_request_id -> diff_id
3bf6e95252 migrateMergeRequestsToDiffs(db);
3bf6e95253
2ec6868254 // Migrate branch-based diffs to commit-based
2ec6868255 migrateDiffsToCommitBased(db);
2ec6868256
6f8b526257 // Ensure diff-related indexes exist (safe after migration or on fresh db)
6f8b526258 db.exec("CREATE INDEX IF NOT EXISTS idx_comments_diff ON comments(diff_id)");
6f8b526259 db.exec("CREATE INDEX IF NOT EXISTS idx_reviews_diff ON reviews(diff_id)");
2ec6868260 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_head_commit ON diffs(head_commit)");
6f8b526261
23ab721262 // Add require_diffs setting to repos
23ab721263 const repoColsForDiffs = db.prepare("PRAGMA table_info(repos)").all() as { name: string }[];
23ab721264 if (!repoColsForDiffs.map((c) => c.name).includes("require_diffs")) {
23ab721265 db.exec("ALTER TABLE repos ADD COLUMN require_diffs INTEGER DEFAULT 0");
23ab721266 }
23ab721267
e5b523e268 // Add pages settings to repos
e5b523e269 const repoColsForPages = db.prepare("PRAGMA table_info(repos)").all() as { name: string }[];
e5b523e270 if (!repoColsForPages.map((c) => c.name).includes("pages_enabled")) {
e5b523e271 db.exec("ALTER TABLE repos ADD COLUMN pages_enabled INTEGER DEFAULT 0");
e5b523e272 }
e5b523e273 if (!repoColsForPages.map((c) => c.name).includes("pages_domain")) {
e5b523e274 db.exec("ALTER TABLE repos ADD COLUMN pages_domain TEXT");
e5b523e275 }
e5b523e276 db.exec("CREATE UNIQUE INDEX IF NOT EXISTS idx_repos_pages_domain ON repos(pages_domain) WHERE pages_domain IS NOT NULL");
e5b523e277
818dc90278 // Add commit_message to pipeline_runs
818dc90279 const runCols = db.prepare("PRAGMA table_info(pipeline_runs)").all() as { name: string }[];
818dc90280 if (!runCols.map((c) => c.name).includes("commit_message")) {
818dc90281 db.exec("ALTER TABLE pipeline_runs ADD COLUMN commit_message TEXT");
818dc90282 }
818dc90283
37f6938284 // Add pipeline_id FK to pipeline_runs, backfill from existing data
37f6938285 if (!runCols.map((c) => c.name).includes("pipeline_id")) {
37f6938286 db.exec(`
37f6938287 ALTER TABLE pipeline_runs ADD COLUMN pipeline_id INTEGER REFERENCES pipelines(id) ON DELETE CASCADE;
37f6938288
37f6938289 INSERT OR IGNORE INTO pipelines (repo_id, name, file)
37f6938290 SELECT DISTINCT repo_id, pipeline_name, pipeline_file
37f6938291 FROM pipeline_runs
37f6938292 WHERE pipeline_name IS NOT NULL;
37f6938293
37f6938294 UPDATE pipeline_runs
37f6938295 SET pipeline_id = (
37f6938296 SELECT p.id FROM pipelines p
37f6938297 WHERE p.repo_id = pipeline_runs.repo_id
37f6938298 AND p.name = pipeline_runs.pipeline_name
37f6938299 )
37f6938300 WHERE pipeline_id IS NULL;
37f6938301
37f6938302 CREATE INDEX IF NOT EXISTS idx_pipeline_runs_pipeline ON pipeline_runs(pipeline_id);
37f6938303 `);
37f6938304 }
37f6938305
79efd41306 // View for resolving repo owner names across users and orgs
433bcf1307 // DROP + recreate so the view picks up any new columns added above
433bcf1308 db.exec("DROP VIEW IF EXISTS repos_with_owner");
79efd41309 db.exec(`
433bcf1310 CREATE VIEW repos_with_owner AS
79efd41311 SELECT r.*,
79efd41312 CASE r.owner_type
79efd41313 WHEN 'user' THEN u.username
79efd41314 WHEN 'org' THEN o.name
79efd41315 END as owner_name
79efd41316 FROM repos r
79efd41317 LEFT JOIN users u ON r.owner_type = 'user' AND r.owner_id = u.id
79efd41318 LEFT JOIN orgs o ON r.owner_type = 'org' AND r.owner_id = o.id
79efd41319 `);
3e3af55320}
db59b65321
db59b65322function tableReferencesLegacyRepos(db: Database.Database, tableName: string): boolean {
db59b65323 const row = db
db59b65324 .prepare("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?")
db59b65325 .get(tableName) as { sql?: string } | undefined;
db59b65326 const sql = row?.sql ?? "";
db59b65327 return /REFERENCES\s+"?repos_legacy"?\s*\(\s*id\s*\)/i.test(sql);
db59b65328}
db59b65329
db59b65330function repairLegacyRepoForeignKeys(db: Database.Database): void {
db59b65331 const needsRepair =
d12933e332 tableReferencesLegacyRepos(db, "diffs") ||
db59b65333 tableReferencesLegacyRepos(db, "pipelines") ||
db59b65334 tableReferencesLegacyRepos(db, "pipeline_runs") ||
db59b65335 tableReferencesLegacyRepos(db, "canopy_secrets");
db59b65336 if (!needsRepair) return;
db59b65337
db59b65338 const runCols = db.prepare("PRAGMA table_info(pipeline_runs)").all() as { name: string }[];
db59b65339 const hasCommitMessage = runCols.some((c) => c.name === "commit_message");
db59b65340 const hasPipelineId = runCols.some((c) => c.name === "pipeline_id");
db59b65341
db59b65342 db.exec("PRAGMA foreign_keys = OFF");
db59b65343 db.exec("BEGIN TRANSACTION");
db59b65344 try {
db59b65345 db.exec("DROP VIEW IF EXISTS repos_with_owner");
db59b65346
db59b65347 if (tableReferencesLegacyRepos(db, "pipelines")) {
db59b65348 db.exec(`
db59b65349 CREATE TABLE pipelines_new (
db59b65350 id INTEGER PRIMARY KEY AUTOINCREMENT,
db59b65351 repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
db59b65352 name TEXT NOT NULL,
db59b65353 file TEXT NOT NULL,
db59b65354 created_at TEXT DEFAULT (datetime('now')),
db59b65355 UNIQUE(repo_id, name)
db59b65356 );
db59b65357 INSERT INTO pipelines_new (id, repo_id, name, file, created_at)
db59b65358 SELECT id, repo_id, name, file, created_at FROM pipelines;
db59b65359 DROP TABLE pipelines;
db59b65360 ALTER TABLE pipelines_new RENAME TO pipelines;
db59b65361 `);
db59b65362 db.exec("CREATE INDEX IF NOT EXISTS idx_pipelines_repo ON pipelines(repo_id)");
db59b65363 }
db59b65364
db59b65365 if (tableReferencesLegacyRepos(db, "pipeline_runs")) {
db59b65366 const commitMessageExpr = hasCommitMessage ? "commit_message" : "NULL";
db59b65367 const pipelineIdExpr = hasPipelineId ? "pipeline_id" : "NULL";
db59b65368 db.exec(`
db59b65369 CREATE TABLE pipeline_runs_new (
db59b65370 id INTEGER PRIMARY KEY AUTOINCREMENT,
db59b65371 repo_id INTEGER NOT NULL REFERENCES repos(id),
db59b65372 pipeline_name TEXT NOT NULL,
db59b65373 pipeline_file TEXT NOT NULL,
db59b65374 trigger_type TEXT NOT NULL DEFAULT 'push'
d12933e375 CHECK(trigger_type IN ('push', 'diff', 'manual', 'schedule')),
db59b65376 trigger_ref TEXT,
db59b65377 commit_id TEXT,
db59b65378 commit_message TEXT,
db59b65379 status TEXT NOT NULL DEFAULT 'pending'
db59b65380 CHECK(status IN ('pending', 'running', 'passed', 'failed', 'cancelled')),
db59b65381 started_at TEXT,
db59b65382 finished_at TEXT,
db59b65383 duration_ms INTEGER,
db59b65384 created_at TEXT DEFAULT (datetime('now')),
db59b65385 pipeline_id INTEGER REFERENCES pipelines(id) ON DELETE CASCADE
db59b65386 );
db59b65387 INSERT INTO pipeline_runs_new (
db59b65388 id,
db59b65389 repo_id,
db59b65390 pipeline_name,
db59b65391 pipeline_file,
db59b65392 trigger_type,
db59b65393 trigger_ref,
db59b65394 commit_id,
db59b65395 commit_message,
db59b65396 status,
db59b65397 started_at,
db59b65398 finished_at,
db59b65399 duration_ms,
db59b65400 created_at,
db59b65401 pipeline_id
db59b65402 )
db59b65403 SELECT
db59b65404 id,
db59b65405 repo_id,
db59b65406 pipeline_name,
db59b65407 pipeline_file,
db59b65408 trigger_type,
db59b65409 trigger_ref,
db59b65410 commit_id,
db59b65411 ${commitMessageExpr},
db59b65412 status,
db59b65413 started_at,
db59b65414 finished_at,
db59b65415 duration_ms,
db59b65416 created_at,
db59b65417 ${pipelineIdExpr}
db59b65418 FROM pipeline_runs;
db59b65419 DROP TABLE pipeline_runs;
db59b65420 ALTER TABLE pipeline_runs_new RENAME TO pipeline_runs;
db59b65421 `);
db59b65422 db.exec("CREATE INDEX IF NOT EXISTS idx_pipeline_runs_repo ON pipeline_runs(repo_id)");
db59b65423 db.exec("CREATE INDEX IF NOT EXISTS idx_pipeline_runs_status ON pipeline_runs(status)");
db59b65424 db.exec("CREATE INDEX IF NOT EXISTS idx_pipeline_runs_pipeline ON pipeline_runs(pipeline_id)");
db59b65425 }
db59b65426
d12933e427 if (tableReferencesLegacyRepos(db, "diffs")) {
2ec6868428 // Detect whether we have old branch-based or new commit-based schema
2ec6868429 const diffCols = db.prepare("PRAGMA table_info(diffs)").all() as { name: string }[];
2ec6868430 const hasBranches = diffCols.some((c) => c.name === "source_branch");
2ec6868431 if (hasBranches) {
2ec6868432 db.exec(`
2ec6868433 CREATE TABLE diffs_new (
2ec6868434 id INTEGER PRIMARY KEY AUTOINCREMENT,
2ec6868435 repo_id INTEGER NOT NULL REFERENCES repos(id),
2ec6868436 number INTEGER NOT NULL,
2ec6868437 title TEXT NOT NULL,
2ec6868438 description TEXT,
2ec6868439 author_id INTEGER NOT NULL REFERENCES users(id),
2ec6868440 head_commit TEXT NOT NULL DEFAULT '',
2ec6868441 base_commit TEXT,
2ec6868442 status TEXT DEFAULT 'open' CHECK(status IN ('open', 'landed', 'closed')),
2ec6868443 created_at TEXT DEFAULT (datetime('now')),
2ec6868444 updated_at TEXT DEFAULT (datetime('now')),
2ec6868445 landed_at TEXT,
2ec6868446 landed_by INTEGER REFERENCES users(id),
2ec6868447 UNIQUE(repo_id, number)
2ec6868448 );
2ec6868449 INSERT INTO diffs_new (id, repo_id, number, title, description, author_id, head_commit, status, created_at, updated_at, landed_at, landed_by)
2ec6868450 SELECT id, repo_id, number, title, description, author_id, COALESCE(source_branch, ''),
2ec6868451 CASE status WHEN 'merged' THEN 'landed' ELSE status END,
2ec6868452 created_at, updated_at, merged_at, merged_by
2ec6868453 FROM diffs;
2ec6868454 DROP TABLE diffs;
2ec6868455 ALTER TABLE diffs_new RENAME TO diffs;
2ec6868456 `);
2ec6868457 } else {
2ec6868458 db.exec(`
2ec6868459 CREATE TABLE diffs_new (
2ec6868460 id INTEGER PRIMARY KEY AUTOINCREMENT,
2ec6868461 repo_id INTEGER NOT NULL REFERENCES repos(id),
2ec6868462 number INTEGER NOT NULL,
2ec6868463 title TEXT NOT NULL,
2ec6868464 description TEXT,
2ec6868465 author_id INTEGER NOT NULL REFERENCES users(id),
2ec6868466 head_commit TEXT NOT NULL DEFAULT '',
2ec6868467 base_commit TEXT,
2ec6868468 status TEXT DEFAULT 'open' CHECK(status IN ('open', 'landed', 'closed')),
2ec6868469 created_at TEXT DEFAULT (datetime('now')),
2ec6868470 updated_at TEXT DEFAULT (datetime('now')),
2ec6868471 landed_at TEXT,
2ec6868472 landed_by INTEGER REFERENCES users(id),
2ec6868473 UNIQUE(repo_id, number)
2ec6868474 );
2ec6868475 INSERT INTO diffs_new (id, repo_id, number, title, description, author_id, head_commit, base_commit, status, created_at, updated_at, landed_at, landed_by)
2ec6868476 SELECT id, repo_id, number, title, description, author_id, head_commit, base_commit, status, created_at, updated_at, landed_at, landed_by
2ec6868477 FROM diffs;
2ec6868478 DROP TABLE diffs;
2ec6868479 ALTER TABLE diffs_new RENAME TO diffs;
2ec6868480 `);
2ec6868481 }
d12933e482 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_repo ON diffs(repo_id)");
d12933e483 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_author ON diffs(author_id)");
2ec6868484 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_head_commit ON diffs(head_commit)");
db59b65485 }
db59b65486
db59b65487 if (tableReferencesLegacyRepos(db, "canopy_secrets")) {
db59b65488 db.exec(`
db59b65489 CREATE TABLE canopy_secrets_new (
db59b65490 id INTEGER PRIMARY KEY AUTOINCREMENT,
db59b65491 repo_id INTEGER NOT NULL REFERENCES repos(id),
db59b65492 name TEXT NOT NULL,
db59b65493 encrypted_value TEXT NOT NULL,
db59b65494 created_at TEXT DEFAULT (datetime('now')),
db59b65495 updated_at TEXT DEFAULT (datetime('now')),
db59b65496 UNIQUE(repo_id, name)
db59b65497 );
db59b65498 INSERT INTO canopy_secrets_new (
db59b65499 id,
db59b65500 repo_id,
db59b65501 name,
db59b65502 encrypted_value,
db59b65503 created_at,
db59b65504 updated_at
db59b65505 )
db59b65506 SELECT
db59b65507 id,
db59b65508 repo_id,
db59b65509 name,
db59b65510 encrypted_value,
db59b65511 created_at,
db59b65512 updated_at
db59b65513 FROM canopy_secrets;
db59b65514 DROP TABLE canopy_secrets;
db59b65515 ALTER TABLE canopy_secrets_new RENAME TO canopy_secrets;
db59b65516 `);
db59b65517 db.exec("CREATE INDEX IF NOT EXISTS idx_canopy_secrets_repo ON canopy_secrets(repo_id)");
db59b65518 }
db59b65519
db59b65520 db.exec("COMMIT");
db59b65521 } catch (err) {
db59b65522 db.exec("ROLLBACK");
db59b65523 throw err;
db59b65524 } finally {
db59b65525 db.exec("PRAGMA foreign_keys = ON");
db59b65526 }
db59b65527}
3bf6e95528
3bf6e95529function migrateMergeRequestsToDiffs(db: Database.Database): void {
b11ada8530 // Check if old merge_requests table exists, or if reviews/comments still have old column
3bf6e95531 const oldTable = db
3bf6e95532 .prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'merge_requests'")
3bf6e95533 .get() as { name: string } | undefined;
b11ada8534 const reviewCols = db.prepare("PRAGMA table_info(reviews)").all() as { name: string }[];
b11ada8535 const needsColumnMigration = reviewCols.some((c) => c.name === "merge_request_id");
b11ada8536 if (!oldTable && !needsColumnMigration) return;
3bf6e95537
3bf6e95538 db.exec("PRAGMA foreign_keys = OFF");
3bf6e95539 db.exec("BEGIN TRANSACTION");
3bf6e95540 try {
b11ada8541 // Rename merge_requests -> diffs if old table still exists
b11ada8542 if (oldTable) {
b11ada8543 // Drop the empty diffs table created by CREATE TABLE IF NOT EXISTS,
b11ada8544 // then rename the real merge_requests table (which has the actual data)
b11ada8545 const emptyDiffs = db
b11ada8546 .prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'diffs'")
b11ada8547 .get();
b11ada8548 if (emptyDiffs) {
b11ada8549 db.exec("DROP TABLE diffs");
b11ada8550 }
b11ada8551 db.exec("ALTER TABLE merge_requests RENAME TO diffs");
b11ada8552 }
3bf6e95553
3bf6e95554 // Rebuild reviews with diff_id instead of merge_request_id
b11ada8555 if (needsColumnMigration) {
3bf6e95556 db.exec(`
3bf6e95557 CREATE TABLE reviews_new (
3bf6e95558 id INTEGER PRIMARY KEY AUTOINCREMENT,
3bf6e95559 diff_id INTEGER NOT NULL REFERENCES diffs(id),
3bf6e95560 reviewer_id INTEGER NOT NULL REFERENCES users(id),
3bf6e95561 status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'approved', 'changes_requested')),
3bf6e95562 body TEXT,
3bf6e95563 created_at TEXT DEFAULT (datetime('now')),
3bf6e95564 updated_at TEXT DEFAULT (datetime('now'))
3bf6e95565 );
3bf6e95566 INSERT INTO reviews_new (id, diff_id, reviewer_id, status, body, created_at, updated_at)
3bf6e95567 SELECT id, merge_request_id, reviewer_id, status, body, created_at, updated_at
3bf6e95568 FROM reviews;
3bf6e95569 DROP TABLE reviews;
3bf6e95570 ALTER TABLE reviews_new RENAME TO reviews;
3bf6e95571 `);
3bf6e95572 }
3bf6e95573
3bf6e95574 // Rebuild comments with diff_id instead of merge_request_id
3bf6e95575 const commentCols = db.prepare("PRAGMA table_info(comments)").all() as { name: string }[];
3bf6e95576 if (commentCols.some((c) => c.name === "merge_request_id")) {
3bf6e95577 db.exec(`
3bf6e95578 CREATE TABLE comments_new (
3bf6e95579 id INTEGER PRIMARY KEY AUTOINCREMENT,
3bf6e95580 diff_id INTEGER NOT NULL REFERENCES diffs(id),
3bf6e95581 author_id INTEGER NOT NULL REFERENCES users(id),
3bf6e95582 body TEXT NOT NULL,
3bf6e95583 file_path TEXT,
3bf6e95584 line_number INTEGER,
3bf6e95585 side TEXT CHECK(side IN ('left', 'right')),
3bf6e95586 commit_sha TEXT,
3bf6e95587 parent_id INTEGER REFERENCES comments_new(id),
3bf6e95588 created_at TEXT DEFAULT (datetime('now')),
3bf6e95589 updated_at TEXT DEFAULT (datetime('now'))
3bf6e95590 );
3bf6e95591 INSERT INTO comments_new (id, diff_id, author_id, body, file_path, line_number, side, commit_sha, parent_id, created_at, updated_at)
3bf6e95592 SELECT id, merge_request_id, author_id, body, file_path, line_number, side, commit_sha, parent_id, created_at, updated_at
3bf6e95593 FROM comments;
3bf6e95594 DROP TABLE comments;
3bf6e95595 ALTER TABLE comments_new RENAME TO comments;
3bf6e95596 `);
3bf6e95597 }
3bf6e95598
3bf6e95599 // Drop old indexes and create new ones
3bf6e95600 db.exec("DROP INDEX IF EXISTS idx_merge_requests_repo");
3bf6e95601 db.exec("DROP INDEX IF EXISTS idx_merge_requests_author");
3bf6e95602 db.exec("DROP INDEX IF EXISTS idx_comments_mr");
3bf6e95603 db.exec("DROP INDEX IF EXISTS idx_reviews_mr");
3bf6e95604 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_repo ON diffs(repo_id)");
3bf6e95605 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_author ON diffs(author_id)");
3bf6e95606 db.exec("CREATE INDEX IF NOT EXISTS idx_comments_diff ON comments(diff_id)");
3bf6e95607 db.exec("CREATE INDEX IF NOT EXISTS idx_reviews_diff ON reviews(diff_id)");
3bf6e95608
3bf6e95609 db.exec("COMMIT");
3bf6e95610 } catch (err) {
3bf6e95611 db.exec("ROLLBACK");
3bf6e95612 throw err;
3bf6e95613 } finally {
3bf6e95614 db.exec("PRAGMA foreign_keys = ON");
3bf6e95615 }
3bf6e95616}
2ec6868617
2ec6868618function migrateDiffsToCommitBased(db: Database.Database): void {
2ec6868619 // Check if diffs table still has branch-based columns
2ec6868620 const diffCols = db.prepare("PRAGMA table_info(diffs)").all() as { name: string }[];
2ec6868621 if (!diffCols.some((c) => c.name === "source_branch")) return;
2ec6868622
2ec6868623 db.exec("PRAGMA foreign_keys = OFF");
2ec6868624 db.exec("BEGIN TRANSACTION");
2ec6868625 try {
2ec6868626 db.exec(`
2ec6868627 CREATE TABLE diffs_new (
2ec6868628 id INTEGER PRIMARY KEY AUTOINCREMENT,
2ec6868629 repo_id INTEGER NOT NULL REFERENCES repos(id),
2ec6868630 number INTEGER NOT NULL,
2ec6868631 title TEXT NOT NULL,
2ec6868632 description TEXT,
2ec6868633 author_id INTEGER NOT NULL REFERENCES users(id),
2ec6868634 head_commit TEXT NOT NULL DEFAULT '',
2ec6868635 base_commit TEXT,
2ec6868636 status TEXT DEFAULT 'open' CHECK(status IN ('open', 'landed', 'closed')),
2ec6868637 created_at TEXT DEFAULT (datetime('now')),
2ec6868638 updated_at TEXT DEFAULT (datetime('now')),
2ec6868639 landed_at TEXT,
2ec6868640 landed_by INTEGER REFERENCES users(id),
2ec6868641 UNIQUE(repo_id, number)
2ec6868642 );
2ec6868643 INSERT INTO diffs_new (id, repo_id, number, title, description, author_id, head_commit, status, created_at, updated_at, landed_at, landed_by)
2ec6868644 SELECT id, repo_id, number, title, description, author_id, COALESCE(source_branch, ''),
2ec6868645 CASE status WHEN 'merged' THEN 'landed' ELSE status END,
2ec6868646 created_at, updated_at, merged_at, merged_by
2ec6868647 FROM diffs;
2ec6868648 DROP TABLE diffs;
2ec6868649 ALTER TABLE diffs_new RENAME TO diffs;
2ec6868650 `);
2ec6868651 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_repo ON diffs(repo_id)");
2ec6868652 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_author ON diffs(author_id)");
2ec6868653 db.exec("CREATE INDEX IF NOT EXISTS idx_diffs_head_commit ON diffs(head_commit)");
2ec6868654
2ec6868655 db.exec("COMMIT");
2ec6868656 } catch (err) {
2ec6868657 db.exec("ROLLBACK");
2ec6868658 throw err;
2ec6868659 } finally {
2ec6868660 db.exec("PRAGMA foreign_keys = ON");
2ec6868661 }
2ec6868662}