migrate.ts
1 import type { Database as BetterSqliteDatabase } from 'better-sqlite3' 2 import { ensureAgentRuntimeStorageSchema } from './migrate-agent-runtime' 3 import { ensureAgentPluginSettingsSchema } from './migrate-agent-plugins' 4 import { ensureAgentAuthStorageSchema } from './migrate-agent-auth' 5 6 const STORAGE_SCHEMA_VERSION = 1 7 8 const LEGACY_APP_SETTINGS_COLUMNS = [ 9 'default_provider_preset', 10 'openai_provider_base_url', 11 'openai_provider_api_key', 12 'router_provider_base_url', 13 'router_provider_api_key', 14 'default_provider_base_url', 15 'default_provider_api_key', 16 'provider_models_json', 17 'provider_model_capabilities_json', 18 'custom_provider_label', 19 ] 20 21 const LEGACY_TABLE_MARKER = '_legacy_v1' 22 const FK_REPAIR_TABLE_MARKER = '_fk_repair_v1' 23 const FK_MARKERS = [LEGACY_TABLE_MARKER, FK_REPAIR_TABLE_MARKER] as const 24 const FK_REPAIR_LEGACY_TABLES = [ 25 'messages', 26 'memories', 27 'memory_embeddings', 28 'uploads', 29 'tool_execution_logs', 30 'agent_tasks', 31 'agent_task_events', 32 'agent_task_runs', 33 'workflow_runs', 34 'workflow_inputs', 35 'agent_health_checks', 36 ] as const 37 38 interface TableInfoRow { 39 name: string 40 } 41 42 interface LegacyProviderProfileRow { 43 id: string 44 name: string 45 type: string 46 is_builtin: number 47 api_host: string | null 48 api_path: string | null 49 api_key: string | null 50 endpoint_mode: string | null 51 models_json?: string | null 52 model_capabilities_json?: string | null 53 last_probe_at: string | null 54 created_at: string | null 55 updated_at: string | null 56 default_model_id?: string | null 57 } 58 59 interface LegacySessionRow { 60 id: string 61 created_at: string 62 updated_at: string 63 compacted_summary: string 64 chat_model_override?: string | null 65 chat_model_id_override?: string | null 66 thinking_level: string | null 67 reasoning_level: string | null 68 used_capabilities_json: string | null 69 } 70 71 interface LegacySessionProviderOverrideRow { 72 session_id: string 73 provider_id: string | null 74 base_url?: string | null 75 api_key: string | null 76 chat_endpoint_mode: string | null 77 created_at: string | null 78 updated_at: string | null 79 last_probe_at: string | null 80 } 81 82 interface LegacyAppSettingsRow { 83 default_chat_model?: string | null 84 default_chat_model_id?: string | null 85 default_embedding_model?: string | null 86 default_embedding_model_id?: string | null 87 default_transcription_model?: string | null 88 default_transcription_model_id?: string | null 89 default_embedding_provider_id?: string | null 90 default_transcription_provider_id?: string | null 91 default_provider_chat_models_json?: string | null 92 provider_models_json?: string | null 93 provider_model_capabilities_json?: string | null 94 default_provider_id?: string | null 95 default_provider_endpoint_mode?: string | null 96 default_thinking_level?: string | null 97 default_reasoning_level?: string | null 98 web_search_provider?: string | null 99 web_search_max_results?: number | null 100 web_fetch_max_bytes?: number | null 101 agent_backends_enabled_json?: string | null 102 tool_call_log_retention_days?: number | null 103 updated_at?: string | null 104 } 105 106 export function initializeStorageSchema(db: BetterSqliteDatabase): void { 107 ensureCoreTables(db) 108 ensureRuntimeStorageMigrations(db) 109 } 110 111 export function ensureRuntimeStorageMigrations(db: BetterSqliteDatabase): void { 112 ensureSchemaMetaTable(db) 113 114 const currentVersion = getSchemaVersion(db) 115 if (currentVersion < STORAGE_SCHEMA_VERSION) { 116 if (isAlreadySchemaV1(db)) { 117 setSchemaVersion(db, STORAGE_SCHEMA_VERSION) 118 } else { 119 migrateToSchemaV1(db) 120 } 121 } 122 123 ensureToolExecutionLogSchema(db) 124 ensureAgentPluginSettingsSchema(db) 125 ensureAgentAuthStorageSchema(db) 126 ensureAgentRuntimeStorageSchema(db) 127 repairLegacyForeignKeyTargets(db) 128 129 if (process.env.NODE_ENV !== 'production') { 130 assertLegacyColumnsRemoved(db) 131 } 132 } 133 134 function ensureCoreTables(db: BetterSqliteDatabase): void { 135 db.exec(` 136 CREATE TABLE IF NOT EXISTS sessions ( 137 id TEXT PRIMARY KEY, 138 created_at TEXT NOT NULL, 139 updated_at TEXT NOT NULL, 140 compacted_summary TEXT NOT NULL DEFAULT '', 141 chat_model_id_override TEXT, 142 thinking_level TEXT, 143 reasoning_level TEXT, 144 used_capabilities_json TEXT NOT NULL DEFAULT '[]' 145 ); 146 147 CREATE TABLE IF NOT EXISTS messages ( 148 id TEXT PRIMARY KEY, 149 session_id TEXT NOT NULL, 150 role TEXT NOT NULL, 151 text TEXT NOT NULL, 152 attachments_json TEXT NOT NULL DEFAULT '[]', 153 created_at TEXT NOT NULL, 154 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE 155 ); 156 CREATE INDEX IF NOT EXISTS idx_messages_session_created 157 ON messages(session_id, created_at); 158 159 CREATE TABLE IF NOT EXISTS memories ( 160 id TEXT PRIMARY KEY, 161 session_id TEXT NOT NULL, 162 memory_key TEXT NOT NULL, 163 value TEXT NOT NULL, 164 category TEXT NOT NULL, 165 confidence REAL NOT NULL, 166 created_at TEXT NOT NULL, 167 updated_at TEXT NOT NULL, 168 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE, 169 UNIQUE(session_id, memory_key) 170 ); 171 172 CREATE TABLE IF NOT EXISTS memory_embeddings ( 173 memory_id TEXT PRIMARY KEY, 174 session_id TEXT NOT NULL, 175 provider TEXT NOT NULL, 176 model TEXT, 177 dimensions INTEGER NOT NULL, 178 vector_json TEXT NOT NULL, 179 created_at TEXT NOT NULL, 180 updated_at TEXT NOT NULL, 181 FOREIGN KEY (memory_id) REFERENCES memories(id) ON DELETE CASCADE, 182 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE 183 ); 184 CREATE INDEX IF NOT EXISTS idx_memory_embeddings_session 185 ON memory_embeddings(session_id, updated_at); 186 187 CREATE TABLE IF NOT EXISTS api_call_logs ( 188 id TEXT PRIMARY KEY, 189 provider TEXT NOT NULL, 190 endpoint TEXT NOT NULL, 191 model TEXT, 192 status TEXT NOT NULL, 193 http_status INTEGER, 194 estimated_cost_usd REAL, 195 usage_json TEXT, 196 request_json TEXT NOT NULL, 197 response_json TEXT, 198 error TEXT, 199 started_at TEXT NOT NULL, 200 completed_at TEXT NOT NULL, 201 month_key TEXT NOT NULL 202 ); 203 CREATE INDEX IF NOT EXISTS idx_api_call_logs_completed 204 ON api_call_logs(completed_at DESC); 205 CREATE INDEX IF NOT EXISTS idx_api_call_logs_month 206 ON api_call_logs(month_key, completed_at DESC); 207 208 CREATE TABLE IF NOT EXISTS uploads ( 209 id TEXT PRIMARY KEY, 210 session_id TEXT NOT NULL, 211 kind TEXT NOT NULL, 212 file_name TEXT NOT NULL, 213 mime_type TEXT NOT NULL, 214 size INTEGER NOT NULL, 215 disk_path TEXT NOT NULL, 216 created_at TEXT NOT NULL, 217 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE 218 ); 219 CREATE INDEX IF NOT EXISTS idx_uploads_session_created 220 ON uploads(session_id, created_at); 221 222 CREATE TABLE IF NOT EXISTS app_settings ( 223 id INTEGER PRIMARY KEY CHECK (id = 1), 224 default_chat_model_id TEXT, 225 default_embedding_model_id TEXT, 226 default_transcription_model_id TEXT, 227 default_embedding_provider_id TEXT, 228 default_transcription_provider_id TEXT, 229 default_provider_id TEXT, 230 default_provider_endpoint_mode TEXT, 231 default_thinking_level TEXT, 232 default_reasoning_level TEXT, 233 web_search_provider TEXT, 234 web_search_max_results INTEGER, 235 web_fetch_max_bytes INTEGER, 236 agent_backends_enabled_json TEXT NOT NULL DEFAULT '{}', 237 tool_call_log_retention_days INTEGER, 238 updated_at TEXT NOT NULL 239 ); 240 241 CREATE TABLE IF NOT EXISTS provider_profiles ( 242 id TEXT PRIMARY KEY, 243 name TEXT NOT NULL, 244 type TEXT NOT NULL, 245 is_builtin INTEGER NOT NULL DEFAULT 0, 246 api_host TEXT, 247 api_path TEXT, 248 api_key TEXT, 249 endpoint_mode TEXT NOT NULL DEFAULT 'auto', 250 default_model_id TEXT, 251 last_probe_at TEXT, 252 created_at TEXT NOT NULL, 253 updated_at TEXT NOT NULL 254 ); 255 256 CREATE TABLE IF NOT EXISTS models ( 257 id TEXT PRIMARY KEY, 258 display_name TEXT NOT NULL, 259 kind TEXT NOT NULL, 260 created_at TEXT NOT NULL, 261 updated_at TEXT NOT NULL 262 ); 263 264 CREATE TABLE IF NOT EXISTS provider_models ( 265 provider_id TEXT NOT NULL, 266 model_id TEXT NOT NULL, 267 provider_model_ref TEXT NOT NULL, 268 capabilities_json TEXT NOT NULL DEFAULT '[]', 269 enabled INTEGER NOT NULL DEFAULT 1, 270 created_at TEXT NOT NULL, 271 updated_at TEXT NOT NULL, 272 PRIMARY KEY (provider_id, model_id), 273 UNIQUE (provider_id, provider_model_ref), 274 FOREIGN KEY (provider_id) REFERENCES provider_profiles(id) ON DELETE CASCADE, 275 FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE 276 ); 277 278 CREATE TABLE IF NOT EXISTS session_provider_overrides ( 279 session_id TEXT PRIMARY KEY, 280 provider_id TEXT NOT NULL, 281 api_key TEXT, 282 chat_endpoint_mode TEXT NOT NULL DEFAULT 'auto', 283 created_at TEXT NOT NULL, 284 updated_at TEXT NOT NULL, 285 last_probe_at TEXT, 286 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE, 287 FOREIGN KEY (provider_id) REFERENCES provider_profiles(id) ON DELETE CASCADE 288 ); 289 `) 290 } 291 292 function ensureSchemaMetaTable(db: BetterSqliteDatabase): void { 293 db.exec(` 294 CREATE TABLE IF NOT EXISTS schema_meta ( 295 id INTEGER PRIMARY KEY CHECK (id = 1), 296 schema_version INTEGER NOT NULL, 297 updated_at TEXT NOT NULL 298 ) 299 `) 300 db.prepare( 301 'INSERT OR IGNORE INTO schema_meta (id, schema_version, updated_at) VALUES (1, 0, ?)', 302 ).run(new Date().toISOString()) 303 } 304 305 function getSchemaVersion(db: BetterSqliteDatabase): number { 306 const row = db 307 .prepare('SELECT schema_version FROM schema_meta WHERE id = 1') 308 .get() as { schema_version: number } | undefined 309 return row?.schema_version ?? 0 310 } 311 312 function setSchemaVersion(db: BetterSqliteDatabase, version: number): void { 313 db.prepare('UPDATE schema_meta SET schema_version = ?, updated_at = ? WHERE id = 1').run( 314 version, 315 new Date().toISOString(), 316 ) 317 } 318 319 function isAlreadySchemaV1(db: BetterSqliteDatabase): boolean { 320 const appSettingsColumns = getColumnNames(db, 'app_settings') 321 const sessionOverrideColumns = getColumnNames(db, 'session_provider_overrides') 322 const sessionColumns = getColumnNames(db, 'sessions') 323 324 return ( 325 appSettingsColumns.has('default_chat_model_id') && 326 !appSettingsColumns.has('default_provider_preset') && 327 sessionOverrideColumns.has('provider_id') && 328 !sessionOverrideColumns.has('base_url') && 329 sessionColumns.has('chat_model_id_override') && 330 !sessionColumns.has('chat_model_override') 331 ) 332 } 333 334 function migrateToSchemaV1(db: BetterSqliteDatabase): void { 335 const now = new Date().toISOString() 336 337 db.exec('PRAGMA foreign_keys = OFF') 338 try { 339 const migrate = db.transaction(() => { 340 const legacyAppSettings = readLegacyAppSettings(db) 341 const legacyProviderDefaults = parseProviderModelDefaults( 342 legacyAppSettings?.default_provider_chat_models_json ?? null, 343 ) 344 const legacyProviderRows = readLegacyProviderProfiles(db) 345 const sourceModels = buildSourceModels(db, legacyProviderRows, legacyAppSettings) 346 347 rebuildProviderProfilesTable(db, legacyProviderRows, now) 348 rebuildModelsTables(db, sourceModels, legacyProviderDefaults, legacyAppSettings, now) 349 350 const mappedOverrides = mapLegacySessionOverrides(db, legacyProviderRows) 351 rebuildSessionsTable(db, mappedOverrides, legacyAppSettings, now) 352 rebuildSessionProviderOverridesTable(db, mappedOverrides, now) 353 rebuildAppSettingsTable(db, legacyAppSettings, now) 354 355 dropTableIfExists(db, 'provider_profiles_legacy_v1') 356 dropTableIfExists(db, 'sessions_legacy_v1') 357 dropTableIfExists(db, 'session_provider_overrides_legacy_v1') 358 dropTableIfExists(db, 'app_settings_legacy_v1') 359 360 setSchemaVersion(db, STORAGE_SCHEMA_VERSION) 361 }) 362 migrate() 363 } finally { 364 db.exec('PRAGMA foreign_keys = ON') 365 } 366 } 367 368 function rebuildProviderProfilesTable( 369 db: BetterSqliteDatabase, 370 legacyRows: LegacyProviderProfileRow[], 371 now: string, 372 ): void { 373 renameTableIfExists(db, 'provider_profiles', 'provider_profiles_legacy_v1') 374 375 db.exec(` 376 CREATE TABLE provider_profiles ( 377 id TEXT PRIMARY KEY, 378 name TEXT NOT NULL, 379 type TEXT NOT NULL, 380 is_builtin INTEGER NOT NULL DEFAULT 0, 381 api_host TEXT, 382 api_path TEXT, 383 api_key TEXT, 384 endpoint_mode TEXT NOT NULL DEFAULT 'auto', 385 default_model_id TEXT, 386 last_probe_at TEXT, 387 created_at TEXT NOT NULL, 388 updated_at TEXT NOT NULL 389 ) 390 `) 391 392 const insert = db.prepare( 393 `INSERT INTO provider_profiles ( 394 id, name, type, is_builtin, api_host, api_path, api_key, 395 endpoint_mode, default_model_id, last_probe_at, created_at, updated_at 396 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NULL, ?, ?, ?)`, 397 ) 398 399 for (const row of legacyRows) { 400 insert.run( 401 row.id, 402 row.name, 403 row.type, 404 row.is_builtin, 405 normalizeNullableString(row.api_host), 406 normalizeNullableString(row.api_path), 407 normalizeNullableString(row.api_key), 408 normalizeEndpointMode(row.endpoint_mode), 409 row.last_probe_at ?? null, 410 row.created_at ?? now, 411 row.updated_at ?? now, 412 ) 413 } 414 415 if (legacyRows.length === 0) { 416 insert.run( 417 'openai', 418 'OpenAI', 419 'openai', 420 1, 421 'https://api.openai.com/v1', 422 '/responses', 423 null, 424 'auto', 425 null, 426 now, 427 now, 428 ) 429 } 430 } 431 432 function rebuildModelsTables( 433 db: BetterSqliteDatabase, 434 sourceModels: Partial<Record<string, { refs: string[]; capabilitiesByRef: Record<string, string[]> }>>, 435 legacyDefaults: Partial<Record<string, string>>, 436 legacySettings: LegacyAppSettingsRow | null, 437 now: string, 438 ): void { 439 dropTableIfExists(db, 'provider_models') 440 dropTableIfExists(db, 'models') 441 442 db.exec(` 443 CREATE TABLE models ( 444 id TEXT PRIMARY KEY, 445 display_name TEXT NOT NULL, 446 kind TEXT NOT NULL, 447 created_at TEXT NOT NULL, 448 updated_at TEXT NOT NULL 449 ); 450 451 CREATE TABLE provider_models ( 452 provider_id TEXT NOT NULL, 453 model_id TEXT NOT NULL, 454 provider_model_ref TEXT NOT NULL, 455 capabilities_json TEXT NOT NULL DEFAULT '[]', 456 enabled INTEGER NOT NULL DEFAULT 1, 457 created_at TEXT NOT NULL, 458 updated_at TEXT NOT NULL, 459 PRIMARY KEY (provider_id, model_id), 460 UNIQUE (provider_id, provider_model_ref), 461 FOREIGN KEY (provider_id) REFERENCES provider_profiles(id) ON DELETE CASCADE, 462 FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE 463 ) 464 `) 465 466 const providers = db 467 .prepare('SELECT id FROM provider_profiles ORDER BY created_at ASC') 468 .all() as Array<{ id: string }> 469 470 const insertModel = db.prepare( 471 'INSERT OR IGNORE INTO models (id, display_name, kind, created_at, updated_at) VALUES (?, ?, ?, ?, ?)', 472 ) 473 const insertProviderModel = db.prepare( 474 `INSERT OR IGNORE INTO provider_models ( 475 provider_id, model_id, provider_model_ref, capabilities_json, enabled, created_at, updated_at 476 ) VALUES (?, ?, ?, ?, 1, ?, ?)`, 477 ) 478 const updateProviderDefault = db.prepare( 479 'UPDATE provider_profiles SET default_model_id = ? WHERE id = ?', 480 ) 481 482 const embeddingProviderId = normalizeNullableString(legacySettings?.default_embedding_provider_id) 483 const embeddingModelRef = 484 normalizeNullableString(legacySettings?.default_embedding_model_id) ?? 485 normalizeNullableString(legacySettings?.default_embedding_model) 486 const transcriptionProviderId = normalizeNullableString( 487 legacySettings?.default_transcription_provider_id, 488 ) 489 const transcriptionModelRef = 490 normalizeNullableString(legacySettings?.default_transcription_model_id) ?? 491 normalizeNullableString(legacySettings?.default_transcription_model) 492 493 for (const provider of providers) { 494 const source = sourceModels[provider.id] 495 const refs = source?.refs ?? [] 496 const capabilitiesByRef = source?.capabilitiesByRef ?? {} 497 498 let fallbackDefaultModelId: string | null = null 499 for (const ref of refs) { 500 const kind = inferModelKind({ 501 providerId: provider.id, 502 providerModelRef: ref, 503 embeddingProviderId, 504 embeddingModelRef, 505 transcriptionProviderId, 506 transcriptionModelRef, 507 }) 508 const modelId = buildNamespacedModelId(kind, provider.id, ref) 509 if (!fallbackDefaultModelId) { 510 fallbackDefaultModelId = modelId 511 } 512 513 insertModel.run(modelId, ref, kind, now, now) 514 insertProviderModel.run( 515 provider.id, 516 modelId, 517 ref, 518 JSON.stringify(capabilitiesByRef[ref] ?? []), 519 now, 520 now, 521 ) 522 } 523 524 const preferredRef = normalizeNullableString(legacyDefaults[provider.id] ?? null) 525 if (preferredRef) { 526 updateProviderDefault.run( 527 buildNamespacedModelId('chat', provider.id, preferredRef), 528 provider.id, 529 ) 530 } else if (fallbackDefaultModelId) { 531 updateProviderDefault.run(fallbackDefaultModelId, provider.id) 532 } 533 } 534 } 535 536 function rebuildSessionsTable( 537 db: BetterSqliteDatabase, 538 mappedOverrides: Map<string, string>, 539 legacySettings: LegacyAppSettingsRow | null, 540 now: string, 541 ): void { 542 const legacyRows = readLegacySessions(db) 543 renameTableIfExists(db, 'sessions', 'sessions_legacy_v1') 544 545 db.exec(` 546 CREATE TABLE sessions ( 547 id TEXT PRIMARY KEY, 548 created_at TEXT NOT NULL, 549 updated_at TEXT NOT NULL, 550 compacted_summary TEXT NOT NULL DEFAULT '', 551 chat_model_id_override TEXT, 552 thinking_level TEXT, 553 reasoning_level TEXT, 554 used_capabilities_json TEXT NOT NULL DEFAULT '[]' 555 ) 556 `) 557 558 const defaultProviderId = 559 normalizeNullableString(legacySettings?.default_provider_id) ?? 'openai' 560 561 const insert = db.prepare( 562 `INSERT INTO sessions ( 563 id, created_at, updated_at, compacted_summary, chat_model_id_override, 564 thinking_level, reasoning_level, used_capabilities_json 565 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`, 566 ) 567 568 for (const row of legacyRows) { 569 const rawModel = normalizeNullableString( 570 row.chat_model_id_override ?? row.chat_model_override ?? null, 571 ) 572 const modelId = rawModel 573 ? resolveLegacySessionModelId(db, { 574 rawModel, 575 providerId: mappedOverrides.get(row.id) ?? defaultProviderId, 576 }) 577 : null 578 579 insert.run( 580 row.id, 581 row.created_at, 582 row.updated_at, 583 row.compacted_summary, 584 modelId, 585 row.thinking_level, 586 row.reasoning_level, 587 row.used_capabilities_json ?? '[]', 588 ) 589 } 590 591 if (legacyRows.length === 0) { 592 const created = now 593 insert.run( 594 cryptoRandomId(), 595 created, 596 created, 597 '', 598 null, 599 null, 600 null, 601 '[]', 602 ) 603 db.exec('DELETE FROM sessions') 604 } 605 } 606 607 function rebuildSessionProviderOverridesTable( 608 db: BetterSqliteDatabase, 609 mappedOverrides: Map<string, string>, 610 now: string, 611 ): void { 612 const legacyRows = readLegacySessionProviderOverrides(db) 613 renameTableIfExists(db, 'session_provider_overrides', 'session_provider_overrides_legacy_v1') 614 615 db.exec(` 616 CREATE TABLE session_provider_overrides ( 617 session_id TEXT PRIMARY KEY, 618 provider_id TEXT NOT NULL, 619 api_key TEXT, 620 chat_endpoint_mode TEXT NOT NULL DEFAULT 'auto', 621 created_at TEXT NOT NULL, 622 updated_at TEXT NOT NULL, 623 last_probe_at TEXT, 624 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE, 625 FOREIGN KEY (provider_id) REFERENCES provider_profiles(id) ON DELETE CASCADE 626 ) 627 `) 628 629 const sessionIds = new Set( 630 (db.prepare('SELECT id FROM sessions').all() as Array<{ id: string }>).map((row) => row.id), 631 ) 632 const providerIds = new Set( 633 (db.prepare('SELECT id FROM provider_profiles').all() as Array<{ id: string }>).map((row) => row.id), 634 ) 635 636 const insert = db.prepare( 637 `INSERT OR REPLACE INTO session_provider_overrides ( 638 session_id, provider_id, api_key, chat_endpoint_mode, created_at, updated_at, last_probe_at 639 ) VALUES (?, ?, ?, ?, ?, ?, ?)`, 640 ) 641 642 for (const row of legacyRows) { 643 const providerId = mappedOverrides.get(row.session_id) ?? normalizeNullableString(row.provider_id) 644 if (!providerId || !providerIds.has(providerId) || !sessionIds.has(row.session_id)) { 645 continue 646 } 647 648 insert.run( 649 row.session_id, 650 providerId, 651 normalizeNullableString(row.api_key), 652 normalizeEndpointMode(row.chat_endpoint_mode), 653 row.created_at ?? now, 654 row.updated_at ?? now, 655 row.last_probe_at ?? null, 656 ) 657 } 658 } 659 660 function rebuildAppSettingsTable( 661 db: BetterSqliteDatabase, 662 legacy: LegacyAppSettingsRow | null, 663 now: string, 664 ): void { 665 renameTableIfExists(db, 'app_settings', 'app_settings_legacy_v1') 666 667 db.exec(` 668 CREATE TABLE app_settings ( 669 id INTEGER PRIMARY KEY CHECK (id = 1), 670 default_chat_model_id TEXT, 671 default_embedding_model_id TEXT, 672 default_transcription_model_id TEXT, 673 default_embedding_provider_id TEXT, 674 default_transcription_provider_id TEXT, 675 default_provider_id TEXT, 676 default_provider_endpoint_mode TEXT, 677 default_thinking_level TEXT, 678 default_reasoning_level TEXT, 679 web_search_provider TEXT, 680 web_search_max_results INTEGER, 681 web_fetch_max_bytes INTEGER, 682 agent_backends_enabled_json TEXT NOT NULL DEFAULT '{}', 683 tool_call_log_retention_days INTEGER, 684 updated_at TEXT NOT NULL 685 ) 686 `) 687 688 const providerIds = new Set( 689 (db.prepare('SELECT id FROM provider_profiles').all() as Array<{ id: string }>).map((row) => row.id), 690 ) 691 692 const defaultProviderIdCandidate = normalizeNullableString(legacy?.default_provider_id) 693 const defaultProviderId = 694 defaultProviderIdCandidate && providerIds.has(defaultProviderIdCandidate) 695 ? defaultProviderIdCandidate 696 : providerIds.has('openai') 697 ? 'openai' 698 : ((Array.from(providerIds)[0] as string | undefined) ?? 'openai') 699 700 const defaultChatModelRef = 701 normalizeNullableString(legacy?.default_chat_model_id) ?? 702 normalizeNullableString(legacy?.default_chat_model) 703 const defaultEmbeddingModelRef = 704 normalizeNullableString(legacy?.default_embedding_model_id) ?? 705 normalizeNullableString(legacy?.default_embedding_model) 706 const defaultTranscriptionModelRef = 707 normalizeNullableString(legacy?.default_transcription_model_id) ?? 708 normalizeNullableString(legacy?.default_transcription_model) 709 710 const defaultEmbeddingProviderId = 711 normalizeNullableString(legacy?.default_embedding_provider_id) ?? defaultProviderId 712 const defaultTranscriptionProviderId = 713 normalizeNullableString(legacy?.default_transcription_provider_id) ?? defaultProviderId 714 715 const insert = db.prepare( 716 `INSERT INTO app_settings ( 717 id, default_chat_model_id, default_embedding_model_id, default_transcription_model_id, 718 default_embedding_provider_id, default_transcription_provider_id, default_provider_id, 719 default_provider_endpoint_mode, default_thinking_level, default_reasoning_level, 720 web_search_provider, web_search_max_results, web_fetch_max_bytes, 721 agent_backends_enabled_json, tool_call_log_retention_days, updated_at 722 ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, 723 ) 724 725 insert.run( 726 1, 727 defaultChatModelRef 728 ? resolveLegacySessionModelId(db, { 729 rawModel: defaultChatModelRef, 730 providerId: defaultProviderId, 731 }) 732 : null, 733 defaultEmbeddingModelRef 734 ? resolveLegacySessionModelId(db, { 735 rawModel: defaultEmbeddingModelRef, 736 providerId: defaultEmbeddingProviderId, 737 }) 738 : null, 739 defaultTranscriptionModelRef 740 ? resolveLegacySessionModelId(db, { 741 rawModel: defaultTranscriptionModelRef, 742 providerId: defaultTranscriptionProviderId, 743 }) 744 : null, 745 defaultEmbeddingProviderId, 746 defaultTranscriptionProviderId, 747 defaultProviderId, 748 normalizeEndpointMode(legacy?.default_provider_endpoint_mode), 749 normalizeNullableString(legacy?.default_thinking_level), 750 normalizeNullableString(legacy?.default_reasoning_level), 751 normalizeNullableString(legacy?.web_search_provider), 752 legacy?.web_search_max_results ?? null, 753 legacy?.web_fetch_max_bytes ?? null, 754 normalizeAgentBackendsEnabledJson(legacy?.agent_backends_enabled_json), 755 legacy?.tool_call_log_retention_days ?? 30, 756 legacy?.updated_at ?? now, 757 ) 758 } 759 760 function readLegacyProviderProfiles(db: BetterSqliteDatabase): LegacyProviderProfileRow[] { 761 if (!tableExists(db, 'provider_profiles')) return [] 762 763 const columnNames = getColumnNames(db, 'provider_profiles') 764 const hasLegacyModelColumns = 765 columnNames.has('models_json') && columnNames.has('model_capabilities_json') 766 const hasDefaultModelId = columnNames.has('default_model_id') 767 768 const rows = db 769 .prepare( 770 `SELECT id, name, type, is_builtin, api_host, api_path, api_key, endpoint_mode, 771 ${hasLegacyModelColumns ? 'models_json, model_capabilities_json,' : ''} 772 ${hasDefaultModelId ? 'default_model_id,' : ''} 773 last_probe_at, created_at, updated_at 774 FROM provider_profiles 775 ORDER BY created_at ASC, id ASC`, 776 ) 777 .all() as LegacyProviderProfileRow[] 778 779 return rows 780 } 781 782 function readLegacySessions(db: BetterSqliteDatabase): LegacySessionRow[] { 783 if (!tableExists(db, 'sessions')) return [] 784 const columnNames = getColumnNames(db, 'sessions') 785 const modelColumn = columnNames.has('chat_model_id_override') 786 ? 'chat_model_id_override' 787 : columnNames.has('chat_model_override') 788 ? 'chat_model_override' 789 : 'NULL AS chat_model_override' 790 791 return db 792 .prepare( 793 `SELECT id, created_at, updated_at, compacted_summary, 794 ${modelColumn}, 795 thinking_level, reasoning_level, used_capabilities_json 796 FROM sessions 797 ORDER BY created_at ASC`, 798 ) 799 .all() as LegacySessionRow[] 800 } 801 802 function readLegacySessionProviderOverrides( 803 db: BetterSqliteDatabase, 804 ): LegacySessionProviderOverrideRow[] { 805 if (!tableExists(db, 'session_provider_overrides')) return [] 806 const columnNames = getColumnNames(db, 'session_provider_overrides') 807 808 return db 809 .prepare( 810 `SELECT session_id, 811 ${columnNames.has('provider_id') ? 'provider_id' : 'NULL AS provider_id'}, 812 ${columnNames.has('base_url') ? 'base_url' : 'NULL AS base_url'}, 813 api_key, 814 chat_endpoint_mode, 815 created_at, 816 updated_at, 817 last_probe_at 818 FROM session_provider_overrides`, 819 ) 820 .all() as LegacySessionProviderOverrideRow[] 821 } 822 823 function mapLegacySessionOverrides( 824 db: BetterSqliteDatabase, 825 legacyProviderRows: LegacyProviderProfileRow[], 826 ): Map<string, string> { 827 const overrides = readLegacySessionProviderOverrides(db) 828 if (overrides.length === 0) return new Map() 829 830 const providers = legacyProviderRows.length > 0 831 ? legacyProviderRows 832 : (db.prepare('SELECT id, api_host FROM provider_profiles').all() as Array<{ id: string; api_host: string | null }>) 833 834 const providerByHost = new Map<string, string>() 835 for (const provider of providers) { 836 const apiHost = normalizeNullableString(provider.api_host) 837 if (!apiHost) continue 838 providerByHost.set(normalizeBaseUrl(apiHost), provider.id) 839 } 840 841 const mapped = new Map<string, string>() 842 for (const row of overrides) { 843 const explicit = normalizeNullableString(row.provider_id) 844 if (explicit) { 845 mapped.set(row.session_id, explicit) 846 continue 847 } 848 849 const legacyBaseUrl = normalizeNullableString(row.base_url) 850 if (!legacyBaseUrl) continue 851 const matched = providerByHost.get(normalizeBaseUrl(legacyBaseUrl)) 852 if (matched) { 853 mapped.set(row.session_id, matched) 854 } 855 } 856 857 return mapped 858 } 859 860 function readLegacyAppSettings(db: BetterSqliteDatabase): LegacyAppSettingsRow | null { 861 if (!tableExists(db, 'app_settings')) return null 862 const columns = getColumnNames(db, 'app_settings') 863 864 const select = [ 865 columns.has('default_chat_model') 866 ? 'default_chat_model' 867 : "NULL AS default_chat_model", 868 columns.has('default_chat_model_id') 869 ? 'default_chat_model_id' 870 : "NULL AS default_chat_model_id", 871 columns.has('default_embedding_model') 872 ? 'default_embedding_model' 873 : "NULL AS default_embedding_model", 874 columns.has('default_embedding_model_id') 875 ? 'default_embedding_model_id' 876 : "NULL AS default_embedding_model_id", 877 columns.has('default_transcription_model') 878 ? 'default_transcription_model' 879 : "NULL AS default_transcription_model", 880 columns.has('default_transcription_model_id') 881 ? 'default_transcription_model_id' 882 : "NULL AS default_transcription_model_id", 883 columns.has('default_embedding_provider_id') 884 ? 'default_embedding_provider_id' 885 : "NULL AS default_embedding_provider_id", 886 columns.has('default_transcription_provider_id') 887 ? 'default_transcription_provider_id' 888 : "NULL AS default_transcription_provider_id", 889 columns.has('default_provider_chat_models_json') 890 ? 'default_provider_chat_models_json' 891 : "NULL AS default_provider_chat_models_json", 892 columns.has('provider_models_json') 893 ? 'provider_models_json' 894 : "NULL AS provider_models_json", 895 columns.has('provider_model_capabilities_json') 896 ? 'provider_model_capabilities_json' 897 : "NULL AS provider_model_capabilities_json", 898 columns.has('default_provider_id') 899 ? 'default_provider_id' 900 : "NULL AS default_provider_id", 901 columns.has('default_provider_endpoint_mode') 902 ? 'default_provider_endpoint_mode' 903 : "NULL AS default_provider_endpoint_mode", 904 columns.has('default_thinking_level') 905 ? 'default_thinking_level' 906 : "NULL AS default_thinking_level", 907 columns.has('default_reasoning_level') 908 ? 'default_reasoning_level' 909 : "NULL AS default_reasoning_level", 910 columns.has('web_search_provider') 911 ? 'web_search_provider' 912 : "NULL AS web_search_provider", 913 columns.has('web_search_max_results') 914 ? 'web_search_max_results' 915 : "NULL AS web_search_max_results", 916 columns.has('web_fetch_max_bytes') 917 ? 'web_fetch_max_bytes' 918 : "NULL AS web_fetch_max_bytes", 919 columns.has('agent_backends_enabled_json') 920 ? 'agent_backends_enabled_json' 921 : "NULL AS agent_backends_enabled_json", 922 columns.has('tool_call_log_retention_days') 923 ? 'tool_call_log_retention_days' 924 : "NULL AS tool_call_log_retention_days", 925 columns.has('updated_at') ? 'updated_at' : "NULL AS updated_at", 926 ] 927 928 return ( 929 (db 930 .prepare(`SELECT ${select.join(', ')} FROM app_settings WHERE id = 1`) 931 .get() as LegacyAppSettingsRow | undefined) ?? null 932 ) 933 } 934 935 function buildSourceModels( 936 db: BetterSqliteDatabase, 937 providerRows: LegacyProviderProfileRow[], 938 legacySettings: LegacyAppSettingsRow | null, 939 ): Partial<Record<string, { refs: string[]; capabilitiesByRef: Record<string, string[]> }>> { 940 const fromProfiles: Partial< 941 Record<string, { refs: string[]; capabilitiesByRef: Record<string, string[]> }> 942 > = {} 943 944 for (const provider of providerRows) { 945 const refs = parseStringArrayJson(provider.models_json ?? null) 946 const capabilitiesByRef = parseModelCapabilitiesJson(provider.model_capabilities_json ?? null) 947 fromProfiles[provider.id] = { refs, capabilitiesByRef } 948 } 949 950 const hasProfileModels = Object.values(fromProfiles).some( 951 (entry) => (entry?.refs.length ?? 0) > 0, 952 ) 953 if (hasProfileModels) { 954 return fromProfiles 955 } 956 957 const fallbackRefs = parseProviderModelsJson(legacySettings?.provider_models_json ?? null) 958 const fallbackCaps = parseProviderModelCapabilitiesJson( 959 legacySettings?.provider_model_capabilities_json ?? null, 960 ) 961 962 const providerIds = new Set( 963 providerRows.length > 0 964 ? providerRows.map((row) => row.id) 965 : (db.prepare('SELECT id FROM provider_profiles').all() as Array<{ id: string }>).map((row) => row.id), 966 ) 967 968 const merged: Partial< 969 Record<string, { refs: string[]; capabilitiesByRef: Record<string, string[]> }> 970 > = {} 971 for (const providerId of providerIds) { 972 merged[providerId] = { 973 refs: fallbackRefs[providerId] ?? fromProfiles[providerId]?.refs ?? [], 974 capabilitiesByRef: 975 fallbackCaps[providerId] ?? fromProfiles[providerId]?.capabilitiesByRef ?? {}, 976 } 977 } 978 979 return merged 980 } 981 982 function resolveLegacySessionModelId( 983 db: BetterSqliteDatabase, 984 input: { rawModel: string; providerId: string }, 985 ): string | null { 986 const rawModel = input.rawModel.trim() 987 if (!rawModel) return null 988 989 const existing = db 990 .prepare('SELECT id FROM models WHERE id = ?') 991 .get(rawModel) as { id: string } | undefined 992 if (existing) return existing.id 993 994 const mapped = db 995 .prepare( 996 `SELECT model_id 997 FROM provider_models 998 WHERE provider_id = ? AND provider_model_ref = ?`, 999 ) 1000 .get(input.providerId, rawModel) as { model_id: string } | undefined 1001 if (mapped?.model_id) return mapped.model_id 1002 1003 return null 1004 } 1005 1006 function buildNamespacedModelId( 1007 kind: 'chat' | 'embedding' | 'transcription' | 'rerank', 1008 providerId: string, 1009 providerModelRef: string, 1010 ): string { 1011 const normalizedProviderId = providerId.trim().toLowerCase() 1012 const normalizedRef = providerModelRef.trim() 1013 return `${kind}:${normalizedProviderId}:${normalizedRef}` 1014 } 1015 1016 function inferModelKind(input: { 1017 providerId: string 1018 providerModelRef: string 1019 embeddingProviderId: string | null 1020 embeddingModelRef: string | null 1021 transcriptionProviderId: string | null 1022 transcriptionModelRef: string | null 1023 }): 'chat' | 'embedding' | 'transcription' | 'rerank' { 1024 if ( 1025 input.embeddingProviderId === input.providerId && 1026 input.embeddingModelRef === input.providerModelRef 1027 ) { 1028 return 'embedding' 1029 } 1030 if ( 1031 input.transcriptionProviderId === input.providerId && 1032 input.transcriptionModelRef === input.providerModelRef 1033 ) { 1034 return 'transcription' 1035 } 1036 return 'chat' 1037 } 1038 1039 function assertLegacyColumnsRemoved(db: BetterSqliteDatabase): void { 1040 const appSettingsColumns = getColumnNames(db, 'app_settings') 1041 const sessionOverrideColumns = getColumnNames(db, 'session_provider_overrides') 1042 const sessionColumns = getColumnNames(db, 'sessions') 1043 1044 for (const column of LEGACY_APP_SETTINGS_COLUMNS) { 1045 if (appSettingsColumns.has(column)) { 1046 throw new Error( 1047 `Legacy app_settings column still exists after schema v1 migration: ${column}`, 1048 ) 1049 } 1050 } 1051 1052 if (sessionOverrideColumns.has('base_url')) { 1053 throw new Error('Legacy session_provider_overrides.base_url still exists after schema v1 migration.') 1054 } 1055 1056 if (sessionColumns.has('chat_model_override')) { 1057 throw new Error('Legacy sessions.chat_model_override still exists after schema v1 migration.') 1058 } 1059 1060 const lingeringLegacyTables = listTablesContainingMigrationMarkers(db) 1061 if (lingeringLegacyTables.length > 0) { 1062 throw new Error( 1063 `Legacy table markers still exist after schema v1 migration: ${lingeringLegacyTables.join(', ')}`, 1064 ) 1065 } 1066 } 1067 1068 function repairLegacyForeignKeyTargets(db: BetterSqliteDatabase): void { 1069 const needsRepair = FK_REPAIR_LEGACY_TABLES.some((tableName) => 1070 tableSqlContainsMigrationMarker(db, tableName), 1071 ) 1072 1073 if (!needsRepair) { 1074 return 1075 } 1076 1077 db.exec('PRAGMA foreign_keys = OFF') 1078 try { 1079 const repair = db.transaction(() => { 1080 rebuildMessagesTableForForeignKeyRepair(db) 1081 rebuildMemoriesTableForForeignKeyRepair(db) 1082 rebuildMemoryEmbeddingsTableForForeignKeyRepair(db) 1083 rebuildUploadsTableForForeignKeyRepair(db) 1084 rebuildToolExecutionLogsTableForForeignKeyRepair(db) 1085 rebuildAgentTasksTableForForeignKeyRepair(db) 1086 rebuildAgentTaskEventsTableForForeignKeyRepair(db) 1087 rebuildAgentTaskRunsTableForForeignKeyRepair(db) 1088 rebuildWorkflowRunsTableForForeignKeyRepair(db) 1089 rebuildWorkflowInputsTableForForeignKeyRepair(db) 1090 rebuildAgentHealthChecksTableForForeignKeyRepair(db) 1091 dropTableIfExists(db, 'sessions_legacy_v1') 1092 dropTableIfExists(db, 'provider_profiles_legacy_v1') 1093 dropTableIfExists(db, 'session_provider_overrides_legacy_v1') 1094 dropTableIfExists(db, 'app_settings_legacy_v1') 1095 }) 1096 repair() 1097 } finally { 1098 db.exec('PRAGMA foreign_keys = ON') 1099 } 1100 } 1101 1102 function listTablesContainingMigrationMarkers(db: BetterSqliteDatabase): string[] { 1103 const rows = db 1104 .prepare( 1105 `SELECT name 1106 FROM sqlite_master 1107 WHERE type = 'table' 1108 AND sql IS NOT NULL 1109 AND (${FK_MARKERS.map(() => 'sql LIKE ?').join(' OR ')})`, 1110 ) 1111 .all(...FK_MARKERS.map((marker) => `%${marker}%`)) as Array<{ name: string }> 1112 return rows.map((row) => row.name) 1113 } 1114 1115 function tableSqlContainsMigrationMarker( 1116 db: BetterSqliteDatabase, 1117 tableName: string, 1118 ): boolean { 1119 const row = db 1120 .prepare( 1121 `SELECT sql 1122 FROM sqlite_master 1123 WHERE type = 'table' AND name = ?`, 1124 ) 1125 .get(tableName) as { sql: string | null } | undefined 1126 1127 return FK_MARKERS.some((marker) => row?.sql?.includes(marker) ?? false) 1128 } 1129 1130 function rebuildMessagesTableForForeignKeyRepair( 1131 db: BetterSqliteDatabase, 1132 ): void { 1133 if (!tableExists(db, 'messages')) return 1134 renameTableIfExists(db, 'messages', 'messages_fk_repair_v1') 1135 db.exec(` 1136 CREATE TABLE messages ( 1137 id TEXT PRIMARY KEY, 1138 session_id TEXT NOT NULL, 1139 role TEXT NOT NULL, 1140 text TEXT NOT NULL, 1141 attachments_json TEXT NOT NULL DEFAULT '[]', 1142 created_at TEXT NOT NULL, 1143 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE 1144 ); 1145 CREATE INDEX IF NOT EXISTS idx_messages_session_created 1146 ON messages(session_id, created_at); 1147 `) 1148 db.exec(` 1149 INSERT INTO messages (id, session_id, role, text, attachments_json, created_at) 1150 SELECT id, session_id, role, text, attachments_json, created_at 1151 FROM messages_fk_repair_v1 1152 `) 1153 dropTableIfExists(db, 'messages_fk_repair_v1') 1154 } 1155 1156 function rebuildMemoriesTableForForeignKeyRepair( 1157 db: BetterSqliteDatabase, 1158 ): void { 1159 if (!tableExists(db, 'memories')) return 1160 renameTableIfExists(db, 'memories', 'memories_fk_repair_v1') 1161 db.exec(` 1162 CREATE TABLE memories ( 1163 id TEXT PRIMARY KEY, 1164 session_id TEXT NOT NULL, 1165 memory_key TEXT NOT NULL, 1166 value TEXT NOT NULL, 1167 category TEXT NOT NULL, 1168 confidence REAL NOT NULL, 1169 created_at TEXT NOT NULL, 1170 updated_at TEXT NOT NULL, 1171 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE, 1172 UNIQUE(session_id, memory_key) 1173 ); 1174 `) 1175 db.exec(` 1176 INSERT INTO memories ( 1177 id, session_id, memory_key, value, category, confidence, created_at, updated_at 1178 ) 1179 SELECT 1180 id, session_id, memory_key, value, category, confidence, created_at, updated_at 1181 FROM memories_fk_repair_v1 1182 `) 1183 dropTableIfExists(db, 'memories_fk_repair_v1') 1184 } 1185 1186 function rebuildMemoryEmbeddingsTableForForeignKeyRepair( 1187 db: BetterSqliteDatabase, 1188 ): void { 1189 if (!tableExists(db, 'memory_embeddings')) return 1190 renameTableIfExists(db, 'memory_embeddings', 'memory_embeddings_fk_repair_v1') 1191 db.exec(` 1192 CREATE TABLE memory_embeddings ( 1193 memory_id TEXT PRIMARY KEY, 1194 session_id TEXT NOT NULL, 1195 provider TEXT NOT NULL, 1196 model TEXT, 1197 dimensions INTEGER NOT NULL, 1198 vector_json TEXT NOT NULL, 1199 created_at TEXT NOT NULL, 1200 updated_at TEXT NOT NULL, 1201 FOREIGN KEY (memory_id) REFERENCES memories(id) ON DELETE CASCADE, 1202 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE 1203 ); 1204 CREATE INDEX IF NOT EXISTS idx_memory_embeddings_session 1205 ON memory_embeddings(session_id, updated_at); 1206 `) 1207 db.exec(` 1208 INSERT INTO memory_embeddings ( 1209 memory_id, session_id, provider, model, dimensions, vector_json, created_at, updated_at 1210 ) 1211 SELECT 1212 memory_id, session_id, provider, model, dimensions, vector_json, created_at, updated_at 1213 FROM memory_embeddings_fk_repair_v1 1214 `) 1215 dropTableIfExists(db, 'memory_embeddings_fk_repair_v1') 1216 } 1217 1218 function rebuildUploadsTableForForeignKeyRepair( 1219 db: BetterSqliteDatabase, 1220 ): void { 1221 if (!tableExists(db, 'uploads')) return 1222 renameTableIfExists(db, 'uploads', 'uploads_fk_repair_v1') 1223 db.exec(` 1224 CREATE TABLE uploads ( 1225 id TEXT PRIMARY KEY, 1226 session_id TEXT NOT NULL, 1227 kind TEXT NOT NULL, 1228 file_name TEXT NOT NULL, 1229 mime_type TEXT NOT NULL, 1230 size INTEGER NOT NULL, 1231 disk_path TEXT NOT NULL, 1232 created_at TEXT NOT NULL, 1233 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE 1234 ); 1235 CREATE INDEX IF NOT EXISTS idx_uploads_session_created 1236 ON uploads(session_id, created_at); 1237 `) 1238 db.exec(` 1239 INSERT INTO uploads ( 1240 id, session_id, kind, file_name, mime_type, size, disk_path, created_at 1241 ) 1242 SELECT 1243 id, session_id, kind, file_name, mime_type, size, disk_path, created_at 1244 FROM uploads_fk_repair_v1 1245 `) 1246 dropTableIfExists(db, 'uploads_fk_repair_v1') 1247 } 1248 1249 function rebuildToolExecutionLogsTableForForeignKeyRepair( 1250 db: BetterSqliteDatabase, 1251 ): void { 1252 if (!tableExists(db, 'tool_execution_logs')) return 1253 if (!tableSqlContainsMigrationMarker(db, 'tool_execution_logs')) return 1254 renameTableIfExists(db, 'tool_execution_logs', 'tool_execution_logs_fk_repair_v1') 1255 db.exec(` 1256 CREATE TABLE tool_execution_logs ( 1257 id TEXT PRIMARY KEY, 1258 session_id TEXT NOT NULL, 1259 provider TEXT NOT NULL, 1260 model TEXT, 1261 tool_name TEXT NOT NULL, 1262 tool_call_id TEXT, 1263 source TEXT NOT NULL, 1264 status TEXT NOT NULL, 1265 preview TEXT NOT NULL, 1266 arguments_json TEXT, 1267 output_json TEXT NOT NULL, 1268 started_at TEXT NOT NULL, 1269 completed_at TEXT NOT NULL, 1270 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE 1271 ); 1272 CREATE INDEX IF NOT EXISTS idx_tool_execution_logs_completed 1273 ON tool_execution_logs(completed_at DESC); 1274 CREATE INDEX IF NOT EXISTS idx_tool_execution_logs_session_completed 1275 ON tool_execution_logs(session_id, completed_at DESC); 1276 `) 1277 db.exec(` 1278 INSERT INTO tool_execution_logs ( 1279 id, session_id, provider, model, tool_name, tool_call_id, source, 1280 status, preview, arguments_json, output_json, started_at, completed_at 1281 ) 1282 SELECT 1283 id, session_id, provider, model, tool_name, tool_call_id, source, 1284 status, preview, arguments_json, output_json, started_at, completed_at 1285 FROM tool_execution_logs_fk_repair_v1 1286 `) 1287 dropTableIfExists(db, 'tool_execution_logs_fk_repair_v1') 1288 } 1289 1290 function rebuildAgentTasksTableForForeignKeyRepair( 1291 db: BetterSqliteDatabase, 1292 ): void { 1293 if (!tableExists(db, 'agent_tasks')) return 1294 if (!tableSqlContainsMigrationMarker(db, 'agent_tasks')) return 1295 renameTableIfExists(db, 'agent_tasks', 'agent_tasks_fk_repair_v1') 1296 db.exec(` 1297 CREATE TABLE agent_tasks ( 1298 id TEXT PRIMARY KEY, 1299 session_id TEXT NOT NULL, 1300 parent_task_id TEXT, 1301 run_id TEXT, 1302 backend_id TEXT, 1303 title TEXT NOT NULL, 1304 kind TEXT NOT NULL, 1305 status TEXT NOT NULL, 1306 progress_percent REAL, 1307 progress_label TEXT, 1308 attached_message_id TEXT, 1309 source TEXT NOT NULL, 1310 error TEXT, 1311 started_at TEXT NOT NULL, 1312 updated_at TEXT NOT NULL, 1313 completed_at TEXT, 1314 metadata_json TEXT, 1315 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE, 1316 FOREIGN KEY (parent_task_id) REFERENCES agent_tasks(id) ON DELETE SET NULL 1317 ); 1318 CREATE INDEX IF NOT EXISTS idx_agent_tasks_session_updated 1319 ON agent_tasks(session_id, updated_at DESC); 1320 CREATE INDEX IF NOT EXISTS idx_agent_tasks_status_updated 1321 ON agent_tasks(status, updated_at DESC); 1322 `) 1323 db.exec(` 1324 INSERT INTO agent_tasks ( 1325 id, session_id, parent_task_id, run_id, backend_id, title, kind, status, 1326 progress_percent, progress_label, attached_message_id, source, error, 1327 started_at, updated_at, completed_at, metadata_json 1328 ) 1329 SELECT 1330 id, session_id, parent_task_id, run_id, backend_id, title, kind, status, 1331 progress_percent, progress_label, attached_message_id, source, error, 1332 started_at, updated_at, completed_at, metadata_json 1333 FROM agent_tasks_fk_repair_v1 1334 `) 1335 dropTableIfExists(db, 'agent_tasks_fk_repair_v1') 1336 } 1337 1338 function rebuildAgentTaskEventsTableForForeignKeyRepair( 1339 db: BetterSqliteDatabase, 1340 ): void { 1341 if (!tableExists(db, 'agent_task_events')) return 1342 if (!tableSqlContainsMigrationMarker(db, 'agent_task_events')) return 1343 renameTableIfExists(db, 'agent_task_events', 'agent_task_events_fk_repair_v1') 1344 db.exec(` 1345 CREATE TABLE agent_task_events ( 1346 id TEXT PRIMARY KEY, 1347 task_id TEXT NOT NULL, 1348 at TEXT NOT NULL, 1349 level TEXT NOT NULL, 1350 event_type TEXT NOT NULL, 1351 text TEXT NOT NULL, 1352 dedupe_key TEXT, 1353 payload_json TEXT, 1354 FOREIGN KEY (task_id) REFERENCES agent_tasks(id) ON DELETE CASCADE 1355 ); 1356 CREATE INDEX IF NOT EXISTS idx_agent_task_events_task_at 1357 ON agent_task_events(task_id, at ASC); 1358 CREATE INDEX IF NOT EXISTS idx_agent_task_events_dedupe 1359 ON agent_task_events(task_id, dedupe_key); 1360 `) 1361 db.exec(` 1362 INSERT INTO agent_task_events ( 1363 id, task_id, at, level, event_type, text, dedupe_key, payload_json 1364 ) 1365 SELECT 1366 id, task_id, at, level, event_type, text, dedupe_key, payload_json 1367 FROM agent_task_events_fk_repair_v1 1368 `) 1369 dropTableIfExists(db, 'agent_task_events_fk_repair_v1') 1370 } 1371 1372 function rebuildAgentTaskRunsTableForForeignKeyRepair( 1373 db: BetterSqliteDatabase, 1374 ): void { 1375 if (!tableExists(db, 'agent_task_runs')) return 1376 if (!tableSqlContainsMigrationMarker(db, 'agent_task_runs')) return 1377 renameTableIfExists(db, 'agent_task_runs', 'agent_task_runs_fk_repair_v1') 1378 db.exec(` 1379 CREATE TABLE agent_task_runs ( 1380 id TEXT PRIMARY KEY, 1381 task_id TEXT NOT NULL, 1382 run_id TEXT, 1383 attempt INTEGER NOT NULL, 1384 status TEXT NOT NULL, 1385 started_at TEXT NOT NULL, 1386 updated_at TEXT NOT NULL, 1387 completed_at TEXT, 1388 exit_code INTEGER, 1389 signal TEXT, 1390 timed_out INTEGER NOT NULL DEFAULT 0, 1391 stdout_preview TEXT, 1392 stderr_preview TEXT, 1393 error TEXT, 1394 metadata_json TEXT, 1395 FOREIGN KEY (task_id) REFERENCES agent_tasks(id) ON DELETE CASCADE 1396 ); 1397 CREATE UNIQUE INDEX IF NOT EXISTS idx_agent_task_runs_task_attempt 1398 ON agent_task_runs(task_id, attempt); 1399 CREATE INDEX IF NOT EXISTS idx_agent_task_runs_task_updated 1400 ON agent_task_runs(task_id, updated_at DESC); 1401 `) 1402 db.exec(` 1403 INSERT INTO agent_task_runs ( 1404 id, task_id, run_id, attempt, status, started_at, updated_at, completed_at, 1405 exit_code, signal, timed_out, stdout_preview, stderr_preview, error, metadata_json 1406 ) 1407 SELECT 1408 id, task_id, run_id, attempt, status, started_at, updated_at, completed_at, 1409 exit_code, signal, timed_out, stdout_preview, stderr_preview, error, metadata_json 1410 FROM agent_task_runs_fk_repair_v1 1411 `) 1412 dropTableIfExists(db, 'agent_task_runs_fk_repair_v1') 1413 } 1414 1415 function rebuildWorkflowRunsTableForForeignKeyRepair( 1416 db: BetterSqliteDatabase, 1417 ): void { 1418 if (!tableExists(db, 'workflow_runs')) return 1419 if (!tableSqlContainsMigrationMarker(db, 'workflow_runs')) return 1420 renameTableIfExists(db, 'workflow_runs', 'workflow_runs_fk_repair_v1') 1421 db.exec(` 1422 CREATE TABLE workflow_runs ( 1423 id TEXT PRIMARY KEY, 1424 task_id TEXT NOT NULL UNIQUE, 1425 session_id TEXT NOT NULL, 1426 command_id TEXT NOT NULL, 1427 status TEXT NOT NULL, 1428 awaiting_input INTEGER NOT NULL DEFAULT 0, 1429 resume_token TEXT, 1430 state_json TEXT NOT NULL DEFAULT '{}', 1431 error TEXT, 1432 created_at TEXT NOT NULL, 1433 updated_at TEXT NOT NULL, 1434 completed_at TEXT, 1435 FOREIGN KEY (task_id) REFERENCES agent_tasks(id) ON DELETE CASCADE, 1436 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE 1437 ); 1438 CREATE INDEX IF NOT EXISTS idx_workflow_runs_session_updated 1439 ON workflow_runs(session_id, updated_at DESC); 1440 `) 1441 db.exec(` 1442 INSERT INTO workflow_runs ( 1443 id, task_id, session_id, command_id, status, awaiting_input, resume_token, 1444 state_json, error, created_at, updated_at, completed_at 1445 ) 1446 SELECT 1447 id, task_id, session_id, command_id, status, awaiting_input, resume_token, 1448 state_json, error, created_at, updated_at, completed_at 1449 FROM workflow_runs_fk_repair_v1 1450 `) 1451 dropTableIfExists(db, 'workflow_runs_fk_repair_v1') 1452 } 1453 1454 function rebuildWorkflowInputsTableForForeignKeyRepair( 1455 db: BetterSqliteDatabase, 1456 ): void { 1457 if (!tableExists(db, 'workflow_inputs')) return 1458 if (!tableSqlContainsMigrationMarker(db, 'workflow_inputs')) return 1459 renameTableIfExists(db, 'workflow_inputs', 'workflow_inputs_fk_repair_v1') 1460 db.exec(` 1461 CREATE TABLE workflow_inputs ( 1462 id TEXT PRIMARY KEY, 1463 workflow_id TEXT NOT NULL, 1464 resume_token TEXT NOT NULL, 1465 prompt TEXT NOT NULL, 1466 fields_json TEXT NOT NULL, 1467 choices_json TEXT, 1468 created_at TEXT NOT NULL, 1469 resolved_at TEXT, 1470 resolved_values_json TEXT, 1471 FOREIGN KEY (workflow_id) REFERENCES workflow_runs(id) ON DELETE CASCADE 1472 ); 1473 CREATE INDEX IF NOT EXISTS idx_workflow_inputs_workflow_created 1474 ON workflow_inputs(workflow_id, created_at DESC); 1475 CREATE INDEX IF NOT EXISTS idx_workflow_inputs_resume_token 1476 ON workflow_inputs(resume_token, resolved_at); 1477 `) 1478 db.exec(` 1479 INSERT INTO workflow_inputs ( 1480 id, workflow_id, resume_token, prompt, fields_json, choices_json, 1481 created_at, resolved_at, resolved_values_json 1482 ) 1483 SELECT 1484 id, workflow_id, resume_token, prompt, fields_json, choices_json, 1485 created_at, resolved_at, resolved_values_json 1486 FROM workflow_inputs_fk_repair_v1 1487 `) 1488 dropTableIfExists(db, 'workflow_inputs_fk_repair_v1') 1489 } 1490 1491 function rebuildAgentHealthChecksTableForForeignKeyRepair( 1492 db: BetterSqliteDatabase, 1493 ): void { 1494 if (!tableExists(db, 'agent_health_checks')) return 1495 if (!tableSqlContainsMigrationMarker(db, 'agent_health_checks')) return 1496 renameTableIfExists(db, 'agent_health_checks', 'agent_health_checks_fk_repair_v1') 1497 db.exec(` 1498 CREATE TABLE agent_health_checks ( 1499 id TEXT PRIMARY KEY, 1500 backend_id TEXT NOT NULL, 1501 session_id TEXT, 1502 status TEXT NOT NULL, 1503 category TEXT NOT NULL, 1504 message TEXT NOT NULL, 1505 details_json TEXT, 1506 observed_at TEXT NOT NULL, 1507 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE 1508 ); 1509 CREATE INDEX IF NOT EXISTS idx_agent_health_backend_observed 1510 ON agent_health_checks(backend_id, observed_at DESC); 1511 `) 1512 db.exec(` 1513 INSERT INTO agent_health_checks ( 1514 id, backend_id, session_id, status, category, message, details_json, observed_at 1515 ) 1516 SELECT 1517 id, backend_id, session_id, status, category, message, details_json, observed_at 1518 FROM agent_health_checks_fk_repair_v1 1519 `) 1520 dropTableIfExists(db, 'agent_health_checks_fk_repair_v1') 1521 } 1522 1523 function ensureToolExecutionLogSchema(db: BetterSqliteDatabase): void { 1524 db.exec(` 1525 CREATE TABLE IF NOT EXISTS tool_execution_logs ( 1526 id TEXT PRIMARY KEY, 1527 session_id TEXT NOT NULL, 1528 provider TEXT NOT NULL, 1529 model TEXT, 1530 tool_name TEXT NOT NULL, 1531 tool_call_id TEXT, 1532 source TEXT NOT NULL, 1533 status TEXT NOT NULL, 1534 preview TEXT NOT NULL, 1535 arguments_json TEXT, 1536 output_json TEXT NOT NULL, 1537 started_at TEXT NOT NULL, 1538 completed_at TEXT NOT NULL, 1539 FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE 1540 ); 1541 CREATE INDEX IF NOT EXISTS idx_tool_execution_logs_completed 1542 ON tool_execution_logs(completed_at DESC); 1543 CREATE INDEX IF NOT EXISTS idx_tool_execution_logs_session_completed 1544 ON tool_execution_logs(session_id, completed_at DESC); 1545 `) 1546 } 1547 1548 function getColumnNames(db: BetterSqliteDatabase, tableName: string): Set<string> { 1549 if (!tableExists(db, tableName)) return new Set() 1550 const rows = db 1551 .prepare(`PRAGMA table_info(${tableName})`) 1552 .all() as TableInfoRow[] 1553 return new Set(rows.map((row) => row.name)) 1554 } 1555 1556 function tableExists(db: BetterSqliteDatabase, tableName: string): boolean { 1557 const row = db 1558 .prepare( 1559 `SELECT name 1560 FROM sqlite_master 1561 WHERE type = 'table' AND name = ?`, 1562 ) 1563 .get(tableName) as { name: string } | undefined 1564 return Boolean(row?.name) 1565 } 1566 1567 function renameTableIfExists( 1568 db: BetterSqliteDatabase, 1569 fromTableName: string, 1570 toTableName: string, 1571 ): void { 1572 if (!tableExists(db, fromTableName)) return 1573 dropTableIfExists(db, toTableName) 1574 db.exec(`ALTER TABLE ${fromTableName} RENAME TO ${toTableName}`) 1575 } 1576 1577 function dropTableIfExists(db: BetterSqliteDatabase, tableName: string): void { 1578 db.exec(`DROP TABLE IF EXISTS ${tableName}`) 1579 } 1580 1581 function parseStringArrayJson(rawValue: string | null | undefined): string[] { 1582 if (!rawValue) return [] 1583 try { 1584 const parsed = JSON.parse(rawValue) as unknown 1585 if (!Array.isArray(parsed)) return [] 1586 return Array.from( 1587 new Set( 1588 parsed 1589 .filter((value): value is string => typeof value === 'string') 1590 .map((value) => value.trim()) 1591 .filter((value) => value.length > 0), 1592 ), 1593 ) 1594 } catch { 1595 return [] 1596 } 1597 } 1598 1599 function parseProviderModelDefaults( 1600 rawValue: string | null | undefined, 1601 ): Partial<Record<string, string>> { 1602 if (!rawValue) return {} 1603 try { 1604 const parsed = JSON.parse(rawValue) as unknown 1605 if (!parsed || typeof parsed !== 'object' || Array.isArray(parsed)) return {} 1606 const next: Partial<Record<string, string>> = {} 1607 for (const [providerId, modelRef] of Object.entries(parsed as Record<string, unknown>)) { 1608 if (typeof modelRef !== 'string') continue 1609 const normalizedProviderId = providerId.trim() 1610 const normalizedModelRef = modelRef.trim() 1611 if (!normalizedProviderId || !normalizedModelRef) continue 1612 next[normalizedProviderId] = normalizedModelRef 1613 } 1614 return next 1615 } catch { 1616 return {} 1617 } 1618 } 1619 1620 function parseProviderModelsJson( 1621 rawValue: string | null | undefined, 1622 ): Partial<Record<string, string[]>> { 1623 if (!rawValue) return {} 1624 try { 1625 const parsed = JSON.parse(rawValue) as unknown 1626 if (!parsed || typeof parsed !== 'object' || Array.isArray(parsed)) return {} 1627 const next: Partial<Record<string, string[]>> = {} 1628 for (const [providerId, models] of Object.entries(parsed as Record<string, unknown>)) { 1629 if (!Array.isArray(models)) continue 1630 const normalizedProviderId = providerId.trim() 1631 if (!normalizedProviderId) continue 1632 next[normalizedProviderId] = parseStringArrayJson(JSON.stringify(models)) 1633 } 1634 return next 1635 } catch { 1636 return {} 1637 } 1638 } 1639 1640 function parseModelCapabilitiesJson( 1641 rawValue: string | null | undefined, 1642 ): Record<string, string[]> { 1643 if (!rawValue) return {} 1644 try { 1645 const parsed = JSON.parse(rawValue) as unknown 1646 if (!parsed || typeof parsed !== 'object' || Array.isArray(parsed)) return {} 1647 const next: Record<string, string[]> = {} 1648 for (const [modelRef, capabilities] of Object.entries(parsed as Record<string, unknown>)) { 1649 if (!Array.isArray(capabilities)) continue 1650 const normalizedModelRef = modelRef.trim() 1651 if (!normalizedModelRef) continue 1652 const normalizedCapabilities = Array.from( 1653 new Set( 1654 capabilities 1655 .filter((value): value is string => typeof value === 'string') 1656 .map((value) => value.trim()) 1657 .filter((value) => value.length > 0), 1658 ), 1659 ) 1660 if (normalizedCapabilities.length === 0) continue 1661 next[normalizedModelRef] = normalizedCapabilities 1662 } 1663 return next 1664 } catch { 1665 return {} 1666 } 1667 } 1668 1669 function parseProviderModelCapabilitiesJson( 1670 rawValue: string | null | undefined, 1671 ): Partial<Record<string, Record<string, string[]>>> { 1672 if (!rawValue) return {} 1673 try { 1674 const parsed = JSON.parse(rawValue) as unknown 1675 if (!parsed || typeof parsed !== 'object' || Array.isArray(parsed)) return {} 1676 const next: Partial<Record<string, Record<string, string[]>>> = {} 1677 for (const [providerId, byModel] of Object.entries(parsed as Record<string, unknown>)) { 1678 const normalizedProviderId = providerId.trim() 1679 if (!normalizedProviderId || !byModel || typeof byModel !== 'object' || Array.isArray(byModel)) { 1680 continue 1681 } 1682 const normalizedByModel = parseModelCapabilitiesJson(JSON.stringify(byModel)) 1683 if (Object.keys(normalizedByModel).length === 0) continue 1684 next[normalizedProviderId] = normalizedByModel 1685 } 1686 return next 1687 } catch { 1688 return {} 1689 } 1690 } 1691 1692 function normalizeNullableString(value: string | null | undefined): string | null { 1693 const trimmed = value?.trim() 1694 return trimmed ? trimmed : null 1695 } 1696 1697 function normalizeEndpointMode(value: string | null | undefined): 'auto' | 'responses' | 'chat_completions' { 1698 if (value === 'responses' || value === 'chat_completions') return value 1699 return 'auto' 1700 } 1701 1702 function normalizeBaseUrl(value: string): string { 1703 return value.trim().replace(/\/+$/, '') 1704 } 1705 1706 function normalizeAgentBackendsEnabledJson(value: string | null | undefined): string { 1707 if (!value) return '{}' 1708 try { 1709 const parsed = JSON.parse(value) as unknown 1710 if (!parsed || typeof parsed !== 'object' || Array.isArray(parsed)) return '{}' 1711 const next = Object.fromEntries( 1712 Object.entries(parsed as Record<string, unknown>) 1713 .filter(([key, raw]) => key.trim().length > 0 && typeof raw === 'boolean') 1714 .map(([key, raw]) => [key.trim().toLowerCase(), raw]), 1715 ) 1716 return JSON.stringify(next) 1717 } catch { 1718 return '{}' 1719 } 1720 } 1721 1722 function cryptoRandomId(): string { 1723 return `migrate-${Math.random().toString(16).slice(2)}-${Date.now().toString(16)}` 1724 }