f89e074dd1
Governed substrate for autonomous agents: scoped identity (passports), audited actions, MCP workspace. Infra IPs and secrets redacted for public release.
207 lines
8.6 KiB
JavaScript
207 lines
8.6 KiB
JavaScript
#!/usr/bin/env node
|
|
// DOWN migration for 2026-05-16 ADR-0029 Phase 1 — Skills Curation Pipeline.
|
|
//
|
|
// Restores table 3710 + 1790 to byte-equal pre-UP state by reading the
|
|
// pre-migration JSONL backups at:
|
|
// /root/backups/adr-0029-p1-pre-2026-05-16-rows.jsonl
|
|
// /root/backups/adr-0029-p1-pre-2026-05-16-cols.jsonl
|
|
//
|
|
// Strategy:
|
|
// 1. DELETE skill_adoptions universal_tables row + its table_columns.
|
|
// 2. DELETE skill_sources universal_tables row + its table_rows + table_columns.
|
|
// 3. DELETE all 7 new rows in 3710 (system-audit + 6 gitnexus).
|
|
// 4. Restore 1790 row 119821 from backup JSONL.
|
|
// 5. UPDATE 3 existing 3710 rows: revert data jsonb to pre-UP form (use backup).
|
|
// 6. DELETE 18 new columns from 3710 + revert status enum.
|
|
//
|
|
// All inside one transaction. Idempotent re-runs allowed.
|
|
|
|
import 'dotenv/config';
|
|
import pg from 'pg';
|
|
import fs from 'node:fs';
|
|
import readline from 'node:readline';
|
|
|
|
const { Client } = pg;
|
|
|
|
const HOST = process.env.POSTGRES_HOST || 'localhost';
|
|
const PORT = parseInt(process.env.POSTGRES_PORT || '5432', 10);
|
|
const DB = process.env.POSTGRES_DB || 'godcrm_prod';
|
|
const USER = process.env.POSTGRES_USER || 'godcrm';
|
|
const PASS = process.env.POSTGRES_PASSWORD;
|
|
|
|
if (!PASS) {
|
|
console.error('[rollback] POSTGRES_PASSWORD not set');
|
|
process.exit(1);
|
|
}
|
|
|
|
const SKILLS_TABLE_ID = 3710;
|
|
const TOOLS_TABLE_ID = 1790;
|
|
const KNOWLEDGE_PIPELINE_PROJECT = 244;
|
|
const SKILLS_PROMPTS_PROJECT = 245;
|
|
|
|
const ROWS_BACKUP = '/root/backups/adr-0029-p1-pre-2026-05-16-rows.jsonl';
|
|
const COLS_BACKUP = '/root/backups/adr-0029-p1-pre-2026-05-16-cols.jsonl';
|
|
|
|
const NEW_3710_COLUMN_NAMES = [
|
|
'source_id','source_slug','source_url','upstream_sha256','upstream_version',
|
|
'original_prompt','adapted','adapted_by','adapted_at','license','license_url',
|
|
'release_gate','attribution_text','tags','requires_connectors',
|
|
'last_imported_at','last_drift_check_at','drift_detected',
|
|
];
|
|
|
|
const NEW_3710_DATA_KEYS = [
|
|
'source_id','source_slug','source_url','upstream_sha256','upstream_version',
|
|
'original_prompt','adapted','adapted_by','adapted_at','license','license_url',
|
|
'release_gate','attribution_text','tags','requires_connectors',
|
|
'last_imported_at','last_drift_check_at','drift_detected',
|
|
];
|
|
|
|
function unescapePgCopy(line) {
|
|
let out = '';
|
|
for (let i = 0; i < line.length; i++) {
|
|
const ch = line[i];
|
|
if (ch === '\\' && i + 1 < line.length) {
|
|
const next = line[i + 1];
|
|
if (next === '\\') { out += '\\'; i++; continue; }
|
|
if (next === 'n') { out += '\n'; i++; continue; }
|
|
if (next === 't') { out += '\t'; i++; continue; }
|
|
if (next === 'r') { out += '\r'; i++; continue; }
|
|
if (next === 'b') { out += '\b'; i++; continue; }
|
|
if (next === 'f') { out += '\f'; i++; continue; }
|
|
if (next === 'v') { out += '\v'; i++; continue; }
|
|
}
|
|
out += ch;
|
|
}
|
|
return out;
|
|
}
|
|
|
|
async function readJsonl(filePath) {
|
|
const out = [];
|
|
const rl = readline.createInterface({ input: fs.createReadStream(filePath), crlfDelay: Infinity });
|
|
for await (const line of rl) {
|
|
if (!line.trim()) continue;
|
|
out.push(JSON.parse(unescapePgCopy(line)));
|
|
}
|
|
return out;
|
|
}
|
|
|
|
async function run() {
|
|
if (!fs.existsSync(ROWS_BACKUP) || !fs.existsSync(COLS_BACKUP)) {
|
|
console.error(`[rollback] backup files missing — refuse to run`);
|
|
console.error(` expected: ${ROWS_BACKUP}`);
|
|
console.error(` expected: ${COLS_BACKUP}`);
|
|
process.exit(1);
|
|
}
|
|
|
|
const backupRows = await readJsonl(ROWS_BACKUP);
|
|
const backupCols = await readJsonl(COLS_BACKUP);
|
|
console.log(`[rollback] loaded ${backupRows.length} backup rows, ${backupCols.length} backup cols`);
|
|
|
|
const client = new Client({ host: HOST, port: PORT, database: DB, user: USER, password: PASS });
|
|
await client.connect();
|
|
console.log(`[rollback] connected: ${USER}@${HOST}:${PORT}/${DB}`);
|
|
|
|
await client.query('BEGIN');
|
|
try {
|
|
// ─── 1. skill_adoptions table teardown ────────────────────────────────
|
|
const adoptions = await client.query(
|
|
'SELECT id FROM universal_tables WHERE project_id=$1 AND name=$2',
|
|
[SKILLS_PROMPTS_PROJECT, 'skill_adoptions']
|
|
);
|
|
if (adoptions.rowCount > 0) {
|
|
const id = adoptions.rows[0].id;
|
|
// Best-effort drop of optional partial index (if a DBA added it post-P1)
|
|
try { await client.query('DROP INDEX IF EXISTS skill_adoptions_unique_idx'); } catch (_) {}
|
|
const rowsDel = await client.query('DELETE FROM table_rows WHERE table_id=$1', [id]);
|
|
const colsDel = await client.query('DELETE FROM table_columns WHERE table_id=$1', [id]);
|
|
const utDel = await client.query('DELETE FROM universal_tables WHERE id=$1', [id]);
|
|
console.log(`[1] skill_adoptions: -${rowsDel.rowCount} rows, -${colsDel.rowCount} cols, -${utDel.rowCount} table`);
|
|
} else {
|
|
console.log('[1] skill_adoptions: already gone');
|
|
}
|
|
|
|
// ─── 2. skill_sources table teardown ──────────────────────────────────
|
|
const sources = await client.query(
|
|
'SELECT id FROM universal_tables WHERE project_id=$1 AND name=$2',
|
|
[KNOWLEDGE_PIPELINE_PROJECT, 'skill_sources']
|
|
);
|
|
if (sources.rowCount > 0) {
|
|
const id = sources.rows[0].id;
|
|
const rowsDel = await client.query('DELETE FROM table_rows WHERE table_id=$1', [id]);
|
|
const colsDel = await client.query('DELETE FROM table_columns WHERE table_id=$1', [id]);
|
|
const utDel = await client.query('DELETE FROM universal_tables WHERE id=$1', [id]);
|
|
console.log(`[2] skill_sources: -${rowsDel.rowCount} rows, -${colsDel.rowCount} cols, -${utDel.rowCount} table`);
|
|
} else {
|
|
console.log('[2] skill_sources: already gone');
|
|
}
|
|
|
|
// ─── 3. Delete 7 new rows in 3710 (NOT the original 3) ───────────────
|
|
const origIds = [115385, 115386, 115387];
|
|
const newDel = await client.query(
|
|
'DELETE FROM table_rows WHERE table_id=$1 AND id NOT IN (115385, 115386, 115387)',
|
|
[SKILLS_TABLE_ID]
|
|
);
|
|
console.log(`[3] 3710: -${newDel.rowCount} new rows (kept 3 originals)`);
|
|
|
|
// ─── 4. Restore 1790 row 119821 from backup ──────────────────────────
|
|
const oldStray = backupRows.find(r => r.id === 119821 && r.table_id === TOOLS_TABLE_ID);
|
|
if (!oldStray) {
|
|
console.log('[4] backup has no row 119821 — skipping');
|
|
} else {
|
|
const exists = await client.query('SELECT 1 FROM table_rows WHERE id=$1', [119821]);
|
|
if (exists.rowCount === 0) {
|
|
await client.query(
|
|
`INSERT INTO table_rows (id, table_id, base_id, data, created_by, created_at, updated_at)
|
|
VALUES ($1, $2, $3, $4::jsonb, $5, $6, $7)`,
|
|
[oldStray.id, oldStray.table_id, oldStray.base_id,
|
|
JSON.stringify(oldStray.data), oldStray.created_by,
|
|
oldStray.created_at, oldStray.updated_at]
|
|
);
|
|
console.log('[4] restored 1790 row 119821 from backup');
|
|
} else {
|
|
console.log('[4] 1790 row 119821 already present — no-op');
|
|
}
|
|
}
|
|
|
|
// ─── 5. Revert 3 existing rows to backup data ────────────────────────
|
|
let reverted = 0;
|
|
for (const origId of origIds) {
|
|
const orig = backupRows.find(r => r.id === origId);
|
|
if (!orig) continue;
|
|
await client.query(
|
|
'UPDATE table_rows SET data=$2::jsonb WHERE id=$1 AND table_id=$3',
|
|
[origId, JSON.stringify(orig.data), SKILLS_TABLE_ID]
|
|
);
|
|
reverted++;
|
|
}
|
|
console.log(`[5] reverted ${reverted} original Skills rows to backup state`);
|
|
|
|
// ─── 6. Drop 18 new columns + revert status enum ─────────────────────
|
|
const colDel = await client.query(
|
|
`DELETE FROM table_columns WHERE table_id=$1 AND column_name = ANY($2::text[])`,
|
|
[SKILLS_TABLE_ID, NEW_3710_COLUMN_NAMES]
|
|
);
|
|
console.log(`[6a] dropped ${colDel.rowCount} new columns from 3710`);
|
|
|
|
// Restore original status column config from backup
|
|
const statusBackup = backupCols.find(c => c.table_id === SKILLS_TABLE_ID && c.column_name === 'status');
|
|
if (statusBackup) {
|
|
await client.query(
|
|
'UPDATE table_columns SET config=$1 WHERE table_id=$2 AND column_name=$3',
|
|
[statusBackup.config, SKILLS_TABLE_ID, 'status']
|
|
);
|
|
console.log('[6b] restored status column config from backup');
|
|
}
|
|
|
|
await client.query('COMMIT');
|
|
console.log('\n[rollback] COMMIT — DOWN complete');
|
|
} catch (err) {
|
|
await client.query('ROLLBACK');
|
|
console.error('[rollback] ROLLBACK due to:', err);
|
|
process.exit(2);
|
|
} finally {
|
|
await client.end();
|
|
}
|
|
}
|
|
|
|
run();
|