migrate-sqlite-to-pg.js
1 #!/usr/bin/env node 2 3 /** 4 * SQLite → PostgreSQL Data Migration Script 5 * 6 * Phase 2 of the SQLite → PostgreSQL migration. The PG schema already exists. 7 * This script reads all three SQLite databases and writes to the corresponding 8 * PG schemas (m333, ops, tel). 9 * 10 * Usage: 11 * node scripts/migrate-sqlite-to-pg.js # normal run 12 * node scripts/migrate-sqlite-to-pg.js --final # final sync (logged) 13 * 14 * Requirements: 15 * - PG_PASSWORD env var (or DATABASE_URL) 16 * - PG schema already applied (db/pg-schema.sql) 17 * - better-sqlite3 and pg both installed 18 */ 19 20 import '../src/utils/load-env.js'; 21 import path from 'path'; 22 import { fileURLToPath } from 'url'; 23 import Database from 'better-sqlite3'; 24 import pkg from 'pg'; 25 26 const { Pool } = pkg; 27 28 const __filename = fileURLToPath(import.meta.url); 29 const __dirname = path.dirname(__filename); 30 const ROOT = path.resolve(__dirname, '..'); 31 32 // --------------------------------------------------------------------------- 33 // CLI flags 34 // --------------------------------------------------------------------------- 35 36 const IS_FINAL = process.argv.includes('--final'); 37 38 // --------------------------------------------------------------------------- 39 // Logging 40 // --------------------------------------------------------------------------- 41 42 function ts() { 43 return new Date().toISOString().replace('T', ' ').slice(0, 23); 44 } 45 function log(msg) { 46 console.log(`[${ts()}] ${msg}`); 47 } 48 function warn(msg) { 49 console.warn(`[${ts()}] WARN ${msg}`); 50 } 51 function err(msg) { 52 console.error(`[${ts()}] ERROR ${msg}`); 53 } 54 55 // --------------------------------------------------------------------------- 56 // PostgreSQL connection 57 // --------------------------------------------------------------------------- 58 59 function buildPgPool() { 60 if (process.env.DATABASE_URL) { 61 return new Pool({ connectionString: process.env.DATABASE_URL }); 62 } 63 64 // Build from individual parts. PG_PASSWORD may contain special chars so 65 // we pass as object fields rather than embedding in a URL string. 66 const password = process.env.PG_PASSWORD; 67 if (!password) { 68 throw new Error( 69 'PG_PASSWORD env var is required (or set DATABASE_URL). ' + 70 'Ensure .env.secrets is loaded and contains PG_PASSWORD.' 71 ); 72 } 73 74 return new Pool({ 75 host: process.env.PGHOST || 'localhost', 76 port: parseInt(process.env.PGPORT || '5432', 10), 77 database: process.env.PGDATABASE || 'mmo', 78 user: process.env.PGUSER || 'jason', 79 password, 80 }); 81 } 82 83 // --------------------------------------------------------------------------- 84 // Boolean columns (SQLite INTEGER 0/1 → PG BOOLEAN) 85 // --------------------------------------------------------------------------- 86 87 /** Set of "schema.table.column" strings that need 0/1 → bool coercion. */ 88 const BOOL_COLUMNS = new Set([ 89 'm333.sites.gdpr_verified', 90 'm333.sites.resulted_in_sale', 91 'm333.messages.is_read', 92 'm333.countries.is_price_sensitive', 93 'm333.countries.is_premium_market', 94 'm333.countries.requires_gdpr_check', 95 'm333.countries.is_active', 96 'm333.countries.price_overridden', 97 'm333.countries.sms_enabled', 98 'm333.cron_jobs.enabled', // note: lives in ops schema 99 'm333.cron_jobs.pause_pipeline', 100 'm333.cron_jobs.critical', 101 'ops.cron_jobs.enabled', 102 'ops.cron_jobs.pause_pipeline', 103 'ops.cron_jobs.critical', 104 'ops.pipeline_control.paused', 105 'm333.openrouter_credit_log.is_free_tier', 106 'm333.prompt_feedback.resulted_in_sale', 107 'm333.error_fix_history.fix_succeeded', 108 ]); 109 110 // --------------------------------------------------------------------------- 111 // JSONB columns (TEXT → JSONB, with validation) 112 // --------------------------------------------------------------------------- 113 114 const JSONB_COLUMNS = new Set([ 115 // m333.sites 116 'm333.sites.evidence_pass1_json', 117 'm333.sites.evidence_pass2_json', 118 'm333.sites.evidence_json', 119 'm333.sites.company_proof', 120 'm333.sites.http_headers', 121 'm333.sites.locale_data', 122 'm333.sites.form_fill_data', 123 'm333.sites.key_pages_html', 124 'm333.sites.perf_json', 125 // m333.messages 126 'm333.messages.raw_payload', 127 // m333.countries 128 'm333.countries.company_types', 129 'm333.countries.company_keywords', 130 'm333.countries.key_page_names', 131 'm333.countries.common_cities', 132 // m333.openrouter_credit_log 133 'm333.openrouter_credit_log.rate_limit', 134 'm333.openrouter_credit_log.raw_response', 135 // m333.dashboard_cache 136 'm333.dashboard_cache.cache_value', 137 // m333.error_pattern_proposals 138 'm333.error_pattern_proposals.example_errors', 139 // m333.proposal_templates 140 'm333.proposal_templates.variables', 141 // tel.agent_tasks 142 'tel.agent_tasks.context_json', 143 'tel.agent_tasks.result_json', 144 'tel.agent_tasks.approval_json', 145 // tel.agent_state 146 'tel.agent_state.metrics_json', 147 // tel.agent_messages 148 'tel.agent_messages.metadata_json', 149 // tel.agent_logs 150 'tel.agent_logs.data_json', 151 // tel.agent_outcomes 152 'tel.agent_outcomes.context_json', 153 'tel.agent_outcomes.result_json', 154 ]); 155 156 // --------------------------------------------------------------------------- 157 // Value coercion 158 // --------------------------------------------------------------------------- 159 160 /** 161 * Coerce a raw SQLite value to something safe for PG. 162 * 163 * @param {string} schema e.g. 'm333' 164 * @param {string} table e.g. 'sites' 165 * @param {string} col column name 166 * @param {*} value raw SQLite value 167 * @param {number} rowIndex for logging 168 * @returns {*} coerced value 169 */ 170 function coerce(schema, table, col, value, rowIndex) { 171 const key = `${schema}.${table}.${col}`; 172 173 if (value === null || value === undefined) return null; 174 175 // Boolean coercion 176 if (BOOL_COLUMNS.has(key)) { 177 if (typeof value === 'number') return value !== 0; 178 if (typeof value === 'string') return value !== '0' && value !== 'false'; 179 return Boolean(value); 180 } 181 182 // JSONB coercion 183 if (JSONB_COLUMNS.has(key)) { 184 if (typeof value === 'object') { 185 // Already parsed (shouldn't happen with better-sqlite3 TEXT, but safe) 186 return value; 187 } 188 if (typeof value === 'string' && value.trim() !== '') { 189 try { 190 JSON.parse(value); // validate 191 return value; // pass raw string — pg driver handles TEXT→JSONB cast 192 } catch { 193 warn(` Invalid JSON in ${key} at row index ${rowIndex}, setting NULL`); 194 return null; 195 } 196 } 197 return null; 198 } 199 200 return value; 201 } 202 203 // --------------------------------------------------------------------------- 204 // Table migration definitions 205 // --------------------------------------------------------------------------- 206 207 /** 208 * @typedef {Object} TableSpec 209 * @property {string} sqliteDb path to SQLite db (relative to ROOT) 210 * @property {string} sqliteTable source table name 211 * @property {string} pgSchema 212 * @property {string} pgTable 213 * @property {string} [pkColumn] primary key column for sequence reset (default 'id') 214 * @property {boolean} [noPk] true if no integer PK / no sequence to reset 215 */ 216 217 /** @type {TableSpec[]} */ 218 const TABLE_SPECS = [ 219 // ------------------------------------------------------------------------- 220 // m333 schema (source: db/sites.db) 221 // ------------------------------------------------------------------------- 222 // No FK dependencies 223 { sqliteDb: 'db/sites.db', sqliteTable: 'pricing', pgSchema: 'm333', pgTable: 'pricing' }, 224 { sqliteDb: 'db/sites.db', sqliteTable: 'countries', pgSchema: 'm333', pgTable: 'countries', noPk: true }, 225 { sqliteDb: 'db/sites.db', sqliteTable: 'prompt_versions', pgSchema: 'm333', pgTable: 'prompt_versions' }, 226 { sqliteDb: 'db/sites.db', sqliteTable: 'config', pgSchema: 'm333', pgTable: 'config', noPk: true }, 227 // sites first (FK parent for most) 228 { sqliteDb: 'db/sites.db', sqliteTable: 'sites', pgSchema: 'm333', pgTable: 'sites' }, 229 { sqliteDb: 'db/sites.db', sqliteTable: 'site_status', pgSchema: 'm333', pgTable: 'site_status', 230 whereClause: 'WHERE site_id IN (SELECT id FROM sites)' }, 231 { sqliteDb: 'db/sites.db', sqliteTable: 'keywords', pgSchema: 'm333', pgTable: 'keywords' }, 232 // messages (FK: sites, pricing) 233 { sqliteDb: 'db/sites.db', sqliteTable: 'messages', pgSchema: 'm333', pgTable: 'messages', 234 whereClause: 'WHERE site_id IN (SELECT id FROM sites)' }, 235 // compliance 236 { sqliteDb: 'db/sites.db', sqliteTable: 'unsubscribed_emails', pgSchema: 'm333', pgTable: 'unsubscribed_emails' }, 237 { sqliteDb: 'db/sites.db', sqliteTable: 'opt_outs', pgSchema: 'm333', pgTable: 'opt_outs' }, 238 // proposals (FK: messages, sites) 239 { sqliteDb: 'db/sites.db', sqliteTable: 'proposal_templates', pgSchema: 'm333', pgTable: 'proposal_templates' }, 240 { sqliteDb: 'db/sites.db', sqliteTable: 'prompt_feedback', pgSchema: 'm333', pgTable: 'prompt_feedback' }, 241 { sqliteDb: 'db/sites.db', sqliteTable: 'export_sheets', pgSchema: 'm333', pgTable: 'export_sheets' }, 242 // purchases (FK: messages, sites) 243 { sqliteDb: 'db/sites.db', sqliteTable: 'purchases', pgSchema: 'm333', pgTable: 'purchases' }, 244 // LLM usage 245 { sqliteDb: 'db/sites.db', sqliteTable: 'llm_usage', pgSchema: 'm333', pgTable: 'llm_usage' }, 246 { sqliteDb: 'db/sites.db', sqliteTable: 'openrouter_credit_log', pgSchema: 'm333', pgTable: 'openrouter_credit_log' }, 247 // error tracking 248 { sqliteDb: 'db/sites.db', sqliteTable: 'error_fix_history', pgSchema: 'm333', pgTable: 'error_fix_history' }, 249 { sqliteDb: 'db/sites.db', sqliteTable: 'human_review_queue', pgSchema: 'm333', pgTable: 'human_review_queue' }, 250 { sqliteDb: 'db/sites.db', sqliteTable: 'screenshot_optimization_tests', pgSchema: 'm333', pgTable: 'screenshot_optimization_tests' }, 251 // config & cache 252 { sqliteDb: 'db/sites.db', sqliteTable: 'dashboard_cache', pgSchema: 'm333', pgTable: 'dashboard_cache', noPk: true }, 253 { sqliteDb: 'db/sites.db', sqliteTable: 'error_pattern_proposals', pgSchema: 'm333', pgTable: 'error_pattern_proposals' }, 254 255 // ------------------------------------------------------------------------- 256 // ops schema (source: db/ops.db) 257 // ------------------------------------------------------------------------- 258 { sqliteDb: 'db/ops.db', sqliteTable: 'cron_jobs', pgSchema: 'ops', pgTable: 'cron_jobs' }, 259 { sqliteDb: 'db/ops.db', sqliteTable: 'cron_job_logs', pgSchema: 'ops', pgTable: 'cron_job_logs' }, 260 { sqliteDb: 'db/ops.db', sqliteTable: 'cron_locks', pgSchema: 'ops', pgTable: 'cron_locks', noPk: true }, 261 { sqliteDb: 'db/ops.db', sqliteTable: 'pipeline_control', pgSchema: 'ops', pgTable: 'pipeline_control', noPk: true }, 262 { sqliteDb: 'db/ops.db', sqliteTable: 'settings', pgSchema: 'ops', pgTable: 'settings', noPk: true }, 263 { sqliteDb: 'db/ops.db', sqliteTable: 'migrations', pgSchema: 'ops', pgTable: 'migrations' }, 264 265 // ------------------------------------------------------------------------- 266 // tel schema (source: db/telemetry.db) 267 // ------------------------------------------------------------------------- 268 { sqliteDb: 'db/telemetry.db', sqliteTable: 'pipeline_metrics', pgSchema: 'tel', pgTable: 'pipeline_metrics' }, 269 { sqliteDb: 'db/telemetry.db', sqliteTable: 'system_metrics', pgSchema: 'tel', pgTable: 'system_metrics' }, 270 { sqliteDb: 'db/telemetry.db', sqliteTable: 'system_health', pgSchema: 'tel', pgTable: 'system_health' }, 271 // agent_tasks before agent_state (agent_state refs agent_tasks) 272 { sqliteDb: 'db/telemetry.db', sqliteTable: 'agent_tasks', pgSchema: 'tel', pgTable: 'agent_tasks' }, 273 { sqliteDb: 'db/telemetry.db', sqliteTable: 'agent_state', pgSchema: 'tel', pgTable: 'agent_state', noPk: true }, 274 { sqliteDb: 'db/telemetry.db', sqliteTable: 'agent_messages', pgSchema: 'tel', pgTable: 'agent_messages' }, 275 { sqliteDb: 'db/telemetry.db', sqliteTable: 'agent_logs', pgSchema: 'tel', pgTable: 'agent_logs' }, 276 { sqliteDb: 'db/telemetry.db', sqliteTable: 'agent_llm_usage', pgSchema: 'tel', pgTable: 'agent_llm_usage' }, 277 { sqliteDb: 'db/telemetry.db', sqliteTable: 'agent_outcomes', pgSchema: 'tel', pgTable: 'agent_outcomes' }, 278 ]; 279 280 // --------------------------------------------------------------------------- 281 // Orphan FK check 282 // --------------------------------------------------------------------------- 283 284 /** 285 * Before migrating, report any orphaned FKs in the SQLite data. 286 * We only check the most critical one (messages.site_id → sites.id). 287 */ 288 function checkOrphans(sitesDb, opsDb) { 289 log('Checking for orphaned foreign keys...'); 290 291 // messages.site_id → sites.id 292 try { 293 const orphanedMessages = sitesDb 294 .prepare( 295 `SELECT COUNT(*) as n FROM messages 296 WHERE site_id NOT IN (SELECT id FROM sites)` 297 ) 298 .get(); 299 if (orphanedMessages.n > 0) { 300 warn(` messages: ${orphanedMessages.n} rows with site_id not in sites (will be skipped by FK disable)`); 301 } else { 302 log(' messages.site_id → sites.id: OK'); 303 } 304 } catch (e) { 305 warn(` Could not check messages orphans: ${e.message}`); 306 } 307 308 // site_status.site_id → sites.id 309 try { 310 const orphanedStatus = sitesDb 311 .prepare( 312 `SELECT COUNT(*) as n FROM site_status 313 WHERE site_id NOT IN (SELECT id FROM sites)` 314 ) 315 .get(); 316 if (orphanedStatus.n > 0) { 317 warn(` site_status: ${orphanedStatus.n} rows with site_id not in sites`); 318 } else { 319 log(' site_status.site_id → sites.id: OK'); 320 } 321 } catch (e) { 322 warn(` Could not check site_status orphans: ${e.message}`); 323 } 324 325 // prompt_feedback: message_id and site_id 326 try { 327 const orphanedFeedback = sitesDb 328 .prepare( 329 `SELECT COUNT(*) as n FROM prompt_feedback 330 WHERE message_id IS NOT NULL 331 AND message_id NOT IN (SELECT id FROM messages)` 332 ) 333 .get(); 334 if (orphanedFeedback.n > 0) { 335 warn(` prompt_feedback: ${orphanedFeedback.n} rows with orphaned message_id`); 336 } else { 337 log(' prompt_feedback.message_id → messages.id: OK'); 338 } 339 } catch (e) { 340 warn(` Could not check prompt_feedback orphans: ${e.message}`); 341 } 342 343 // agent_tasks self-referential parent_task_id 344 try { 345 const orphanedTasks = opsDb 346 .prepare( 347 `SELECT COUNT(*) as n FROM agent_tasks 348 WHERE parent_task_id IS NOT NULL 349 AND parent_task_id NOT IN (SELECT id FROM agent_tasks)` 350 ) 351 .get(); 352 if (orphanedTasks.n > 0) { 353 warn(` agent_tasks: ${orphanedTasks.n} rows with orphaned parent_task_id`); 354 } else { 355 log(' agent_tasks.parent_task_id self-ref: OK'); 356 } 357 } catch (e) { 358 // table may be in a different db; silently skip 359 } 360 } 361 362 // --------------------------------------------------------------------------- 363 // Column introspection 364 // --------------------------------------------------------------------------- 365 366 /** 367 * Return column names for a SQLite table. 368 * @param {import('better-sqlite3').Database} db 369 * @param {string} tableName 370 * @returns {string[]} 371 */ 372 function getSqliteColumns(db, tableName) { 373 const info = db.prepare(`PRAGMA table_info("${tableName}")`).all(); 374 return info.map(r => r.name); 375 } 376 377 // --------------------------------------------------------------------------- 378 // Core table migrator 379 // --------------------------------------------------------------------------- 380 381 const BATCH_SIZE = 5000; 382 383 /** 384 * Migrate one table from SQLite → PostgreSQL. 385 * 386 * @param {import('better-sqlite3').Database} sqliteDb open source DB 387 * @param {import('pg').Pool} pgPool PG pool 388 * @param {TableSpec} spec 389 * @returns {Promise<{migrated: number, errors: number}>} 390 */ 391 async function migrateTable(sqliteDb, pgPool, spec) { 392 const { sqliteTable, pgSchema, pgTable, pkColumn = 'id', noPk = false, whereClause = '' } = spec; 393 const qualifiedPg = `${pgSchema}.${pgTable}`; 394 395 // --- Count source rows --- 396 let total = 0; 397 try { 398 const row = sqliteDb.prepare(`SELECT COUNT(*) as n FROM "${sqliteTable}" ${whereClause}`).get(); 399 total = row.n; 400 } catch (e) { 401 warn(` [${qualifiedPg}] Cannot count rows in SQLite (table may not exist): ${e.message}`); 402 return { migrated: 0, errors: 0 }; 403 } 404 405 log(`Migrating ${qualifiedPg} — ${total} source rows`); 406 407 if (total === 0) { 408 log(` [${qualifiedPg}] Nothing to migrate.`); 409 return { migrated: 0, errors: 0 }; 410 } 411 412 // --- Get column list --- 413 const columns = getSqliteColumns(sqliteDb, sqliteTable); 414 if (columns.length === 0) { 415 warn(` [${qualifiedPg}] No columns found.`); 416 return { migrated: 0, errors: 0 }; 417 } 418 419 // --- Truncate PG target first (idempotent) --- 420 const pgClient = await pgPool.connect(); 421 try { 422 await pgClient.query(`TRUNCATE ${qualifiedPg} CASCADE`); 423 } finally { 424 pgClient.release(); 425 } 426 427 // --- Stream batches --- 428 let offset = 0; 429 let totalMigrated = 0; 430 let totalErrors = 0; 431 432 const colList = columns.map(c => `"${c}"`).join(', '); 433 434 // PG protocol limits to 65535 parameters per query. 435 // Reduce batch size for wide tables to stay under the limit. 436 const maxParamsPerQuery = 65000; 437 const effectiveBatch = Math.min(BATCH_SIZE, Math.floor(maxParamsPerQuery / columns.length)); 438 439 while (offset < total) { 440 const rows = sqliteDb 441 .prepare(`SELECT ${colList} FROM "${sqliteTable}" ${whereClause} LIMIT ${effectiveBatch} OFFSET ${offset}`) 442 .all(); 443 444 if (rows.length === 0) break; 445 446 // Build parameterised multi-row INSERT 447 // $1, $2, ... numbered per value across all rows 448 const placeholders = []; 449 const values = []; 450 let paramIdx = 1; 451 452 for (let ri = 0; ri < rows.length; ri++) { 453 const row = rows[ri]; 454 const rowPlaceholders = []; 455 456 for (const col of columns) { 457 const raw = row[col] ?? null; 458 const coerced = coerce(pgSchema, pgTable, col, raw, offset + ri); 459 values.push(coerced); 460 rowPlaceholders.push(`$${paramIdx++}`); 461 } 462 463 placeholders.push(`(${rowPlaceholders.join(', ')})`); 464 } 465 466 const colNames = columns.map(c => `"${c}"`).join(', '); 467 const sql = `INSERT INTO ${qualifiedPg} (${colNames}) VALUES ${placeholders.join(', ')} ON CONFLICT DO NOTHING`; 468 469 const client = await pgPool.connect(); 470 try { 471 await client.query(sql, values); 472 totalMigrated += rows.length; 473 } catch (e) { 474 totalErrors += rows.length; 475 err(` [${qualifiedPg}] Batch insert failed (offset=${offset}): ${e.message}`); 476 // Try row-by-row to salvage what we can 477 const rowClient = await pgPool.connect(); 478 let rescued = 0; 479 let failed = 0; 480 try { 481 for (let ri = 0; ri < rows.length; ri++) { 482 const row = rows[ri]; 483 const singleValues = columns.map((col) => { 484 const raw = row[col] ?? null; 485 return coerce(pgSchema, pgTable, col, raw, offset + ri); 486 }); 487 const singlePlaceholders = singleValues.map((_, i) => `$${i + 1}`).join(', '); 488 const singleSql = `INSERT INTO ${qualifiedPg} (${colNames}) VALUES (${singlePlaceholders}) ON CONFLICT DO NOTHING`; 489 try { 490 await rowClient.query(singleSql, singleValues); 491 rescued++; 492 } catch (rowErr) { 493 failed++; 494 if (failed <= 5) { 495 warn(` Row ${offset + ri} failed: ${rowErr.message.slice(0, 120)}`); 496 } 497 } 498 } 499 totalMigrated += rescued; 500 totalErrors = totalErrors - rows.length + failed; 501 log(` [${qualifiedPg}] Row-by-row rescue: ${rescued} saved, ${failed} failed`); 502 } finally { 503 rowClient.release(); 504 } 505 } finally { 506 client.release(); 507 } 508 509 offset += rows.length; 510 if (total > BATCH_SIZE) { 511 log(` [${qualifiedPg}] Progress: ${Math.min(offset, total)} / ${total}`); 512 } 513 } 514 515 // --- Reset sequence (only for tables with a serial/bigserial PK) --- 516 if (!noPk) { 517 const seqClient = await pgPool.connect(); 518 try { 519 // Standard PG sequence name convention: {table}_{col}_seq 520 const seqName = `${pgSchema}.${pgTable}_${pkColumn}_seq`; 521 await seqClient.query( 522 `SELECT setval('${seqName}', COALESCE((SELECT MAX("${pkColumn}") FROM ${qualifiedPg}), 0))` 523 ); 524 } catch (e) { 525 // Sequence may not exist (e.g. text PK tables). Log but continue. 526 warn(` [${qualifiedPg}] Could not reset sequence: ${e.message}`); 527 } finally { 528 seqClient.release(); 529 } 530 } 531 532 // --- Row count verification --- 533 const verifyClient = await pgPool.connect(); 534 try { 535 const pgCount = await verifyClient.query(`SELECT COUNT(*) as n FROM ${qualifiedPg}`); 536 const pgRows = parseInt(pgCount.rows[0].n, 10); 537 if (pgRows !== total) { 538 warn(` [${qualifiedPg}] Count mismatch: SQLite=${total}, PG=${pgRows} (delta: ${total - pgRows})`); 539 } else { 540 log(` [${qualifiedPg}] Verified: ${pgRows} rows in PG.`); 541 } 542 } finally { 543 verifyClient.release(); 544 } 545 546 return { migrated: totalMigrated, errors: totalErrors }; 547 } 548 549 // --------------------------------------------------------------------------- 550 // Open SQLite connections (cached per path) 551 // --------------------------------------------------------------------------- 552 553 /** @type {Map<string, import('better-sqlite3').Database>} */ 554 const sqliteConnections = new Map(); 555 556 function getSqliteDb(relPath) { 557 const absPath = path.resolve(ROOT, relPath); 558 if (!sqliteConnections.has(absPath)) { 559 const db = new Database(absPath, { readonly: true }); 560 sqliteConnections.set(absPath, db); 561 } 562 return sqliteConnections.get(absPath); 563 } 564 565 function closeAllSqlite() { 566 for (const [, db] of sqliteConnections) { 567 try { db.close(); } catch { /* ignore close errors */ } 568 } 569 } 570 571 // --------------------------------------------------------------------------- 572 // Main 573 // --------------------------------------------------------------------------- 574 575 async function main() { 576 log(`SQLite → PostgreSQL migration starting${IS_FINAL ? ' [FINAL SYNC]' : ''}`); 577 log(`Batch size: ${BATCH_SIZE} rows`); 578 579 // Build PG pool 580 const pgPool = buildPgPool(); 581 582 // Test PG connection 583 const testClient = await pgPool.connect(); 584 try { 585 const result = await testClient.query('SELECT current_database(), current_user, version()'); 586 const row = result.rows[0]; 587 log(`Connected to PG: db=${row.current_database}, user=${row.current_user}`); 588 log(`PG version: ${row.version.split(' ').slice(0, 2).join(' ')}`); 589 } finally { 590 testClient.release(); 591 } 592 593 // FK checks: tables are loaded in dependency order (parents first) and 594 // TRUNCATE ... CASCADE clears children before re-loading parents. 595 // No need for session_replication_role (requires superuser). 596 log('Tables will be loaded in dependency order (parents first)'); 597 598 // Run orphan checks (informational only) 599 const sitesDbForCheck = getSqliteDb('db/sites.db'); 600 const opsDbForCheck = getSqliteDb('db/ops.db'); 601 checkOrphans(sitesDbForCheck, opsDbForCheck); 602 603 // Migrate each table 604 const results = []; 605 for (const spec of TABLE_SPECS) { 606 const sqliteDb = getSqliteDb(spec.sqliteDb); 607 try { 608 const result = await migrateTable(sqliteDb, pgPool, spec); 609 results.push({ table: `${spec.pgSchema}.${spec.pgTable}`, ...result, ok: true }); 610 } catch (e) { 611 err(`Failed to migrate ${spec.pgSchema}.${spec.pgTable}: ${e.message}`); 612 results.push({ table: `${spec.pgSchema}.${spec.pgTable}`, migrated: 0, errors: 0, ok: false, error: e.message }); 613 } 614 } 615 616 // Summary 617 log(''); 618 log('=== Migration Summary ==='); 619 let totalMigrated = 0; 620 let totalErrors = 0; 621 let failedTables = 0; 622 623 for (const r of results) { 624 const status = r.ok ? (r.errors > 0 ? 'PARTIAL' : 'OK') : 'FAILED'; 625 const detail = r.ok 626 ? `${r.migrated} rows migrated${r.errors > 0 ? `, ${r.errors} errors` : ''}` 627 : `ERROR: ${r.error}`; 628 log(` [${status.padEnd(7)}] ${r.table.padEnd(40)} ${detail}`); 629 totalMigrated += r.migrated ?? 0; 630 totalErrors += r.errors ?? 0; 631 if (!r.ok) failedTables++; 632 } 633 634 log(''); 635 log(`Total rows migrated : ${totalMigrated}`); 636 log(`Total row errors : ${totalErrors}`); 637 log(`Tables failed : ${failedTables}`); 638 if (IS_FINAL) log('Final sync: YES — this was the final migration run.'); 639 log('Migration complete.'); 640 641 // Cleanup 642 closeAllSqlite(); 643 await pgPool.end(); 644 645 process.exit(failedTables > 0 || totalErrors > 0 ? 1 : 0); 646 } 647 648 main().catch((e) => { 649 err(`Fatal: ${e.message}`); 650 err(e.stack); 651 process.exit(1); 652 });