mirror of
https://github.com/fsolla/teqo.git
synced 2026-11-13 15:13:07 +00:00
495 lines
19 KiB
JavaScript
495 lines
19 KiB
JavaScript
/**
|
|
* OPS79 — read-only reconciliation of the campaign vertical between the old
|
|
* platform (Neon, frozen by OPS80) and the new platform (`teqo_1313`).
|
|
*
|
|
* Purpose: prove "nada órfão, nada duplicado" for the campaign vertical after
|
|
* the OPS51 dump/restore. Compares counts AND id-sets of the 13 campaign
|
|
* collections, plus the semantic content of the relationship join tables
|
|
* (municipality_rels / leadership_rels / activity_rels / organization_rels /
|
|
* state_deputy_rels / campaign_demand_rels) by (parent, path, child).
|
|
*
|
|
* OPS84 extension (--values): compares ALL content columns per row (not just
|
|
* ids). Columns are classified into buckets:
|
|
* - content: divergence fails the run (the acceptance criterion)
|
|
* - derived: hook-stamped or Payload-managed; divergence is informational only
|
|
* - sensitive: PII; values are never printed, only counted
|
|
*
|
|
* Safety model:
|
|
* - READ-ONLY: every connection runs `default_transaction_read_only = on`
|
|
* and a statement timeout. No writes are possible from this script.
|
|
* - NEVER prints PII: only ids, counts and relation keys are reported.
|
|
* In --values mode, sensitive column values are never printed.
|
|
* - Both URLs come from env; the operator supplies them on the homeserver
|
|
* (NEON_DATABASE_URL from ~/stack/.env; DATABASE_URL from
|
|
* ~/stack/teqo-1313.env, rewritten to the socat proxy 127.0.0.1:5433 —
|
|
* see the OPS79 runbook in docs/ops/teqo-1313-deploy.md).
|
|
*
|
|
* Usage:
|
|
* NEON_DATABASE_URL=postgres://... DATABASE_URL=postgres://... \
|
|
* pnpm ops79:reconcile # id-mode (default, OPS79)
|
|
* NEON_DATABASE_URL=postgres://... DATABASE_URL=postgres://... \
|
|
* pnpm ops84:reconcile-values # values-mode (OPS84)
|
|
* NEON_DATABASE_URL=postgres://... DATABASE_URL=postgres://... \
|
|
* pnpm ops84:reconcile-values --table vote_pledge --report report.md
|
|
*/
|
|
import { Client } from 'pg'
|
|
|
|
import { dieWithLabel, loadCliEnv } from './lib/cli.mjs'
|
|
|
|
const die = dieWithLabel('ops79:reconcile')
|
|
|
|
loadCliEnv()
|
|
|
|
const NEON_DATABASE_URL = process.env.NEON_DATABASE_URL
|
|
const TARGET_DATABASE_URL = process.env.DATABASE_URL
|
|
|
|
if (!NEON_DATABASE_URL) die('NEON_DATABASE_URL is not set (fonte: ~/stack/.env no homeserver).')
|
|
if (!TARGET_DATABASE_URL) die('DATABASE_URL is not set (target teqo_1313).')
|
|
|
|
// CLI flags: --values (OPS84), --table <name>, --report <path>
|
|
const VALUES_MODE = process.argv.includes('--values')
|
|
const TABLE_FILTER = (() => {
|
|
const idx = process.argv.indexOf('--table')
|
|
return idx !== -1 && idx + 1 < process.argv.length ? process.argv[idx + 1] : null
|
|
})()
|
|
const REPORT_PATH = (() => {
|
|
const idx = process.argv.indexOf('--report')
|
|
return idx !== -1 && idx + 1 < process.argv.length ? process.argv[idx + 1] : null
|
|
})()
|
|
if (VALUES_MODE) {
|
|
console.log(
|
|
`[ops84:reconcile-values] modo VALUES ativado${TABLE_FILTER ? ` (tabela: ${TABLE_FILTER})` : ''}`,
|
|
)
|
|
if (REPORT_PATH) console.log(`[ops84:reconcile-values] relatório: ${REPORT_PATH}`)
|
|
}
|
|
|
|
// Campaign vertical collections (id-bearing, content tables).
|
|
const CORE_TABLES = [
|
|
'campaign_user',
|
|
'leadership',
|
|
'vote_pledge',
|
|
'activity',
|
|
'municipality',
|
|
'organization',
|
|
'allocation_decision',
|
|
'municipality_update',
|
|
'state_deputy',
|
|
'election_tally',
|
|
'campaign_web_authn_credential',
|
|
'supporter',
|
|
'campaign_demand',
|
|
]
|
|
|
|
// Relationship join tables compared semantically (parent, path, child).
|
|
const REL_TABLES = [
|
|
'municipality_rels',
|
|
'leadership_rels',
|
|
'activity_rels',
|
|
'organization_rels',
|
|
'state_deputy_rels',
|
|
'campaign_demand_rels',
|
|
]
|
|
|
|
// Known non-data divergences absorbed by OPS79 (verified read-only on
|
|
// 2026-08-23). They reflect PORTFOLIO EDITS made on the new platform after
|
|
// OPS51 (municipality 122 gained advisor 5 and lost stateDeputies 53/58 — a
|
|
// legitimate post-migration edit, not orphaned data). Anything outside this
|
|
// explicit set still fails the reconcile.
|
|
const ABSOLVED_MUNICIPALITY_RELS = new Set([
|
|
'122|stateDeputies|53',
|
|
'122|stateDeputies|58',
|
|
'122|advisors|5',
|
|
])
|
|
|
|
const results = []
|
|
const report = (name, ok, detail) => {
|
|
results.push({ name, ok, detail })
|
|
console.log(`${ok ? ' PASS' : ' FAIL'} ${name}: ${detail}`)
|
|
}
|
|
|
|
const connectReadOnly = async (url) => {
|
|
const client = new Client({ connectionString: url, connectionTimeoutMillis: 15_000 })
|
|
await client.connect()
|
|
await client.query('SET default_transaction_read_only = on')
|
|
await client.query('SET statement_timeout = 30000')
|
|
return client
|
|
}
|
|
|
|
const fetchIds = async (client, table) => {
|
|
const res = await client.query(`SELECT id FROM "${table}" ORDER BY id`)
|
|
return res.rows.map((r) => String(r.id))
|
|
}
|
|
|
|
// --- OPS84: derived/sensitive column maps per table ---
|
|
// Derived = stamped by Payload hooks or managed fields; divergence is
|
|
// informational (expected after legitimate post-OPS51 edits in target).
|
|
// Sensitive = PII; values are NEVER printed, only counted.
|
|
// Column names use the DB convention (e.g. relationship fields get _id suffix,
|
|
// group fields are flattened to field_subfield).
|
|
|
|
const DERIVED_COLUMNS = {
|
|
vote_pledge: [
|
|
'declared_at',
|
|
'declared_by_id',
|
|
'estimated_at',
|
|
'estimated_by_id',
|
|
'created_at',
|
|
'updated_at',
|
|
],
|
|
campaign_user: ['created_at', 'updated_at'],
|
|
leadership: ['created_at', 'updated_at'],
|
|
activity: ['created_at', 'updated_at'],
|
|
municipality: ['created_at', 'updated_at'],
|
|
organization: ['created_at', 'updated_at'],
|
|
allocation_decision: ['created_at', 'updated_at'],
|
|
municipality_update: ['created_at', 'updated_at'],
|
|
state_deputy: ['created_at', 'updated_at'],
|
|
election_tally: ['created_at', 'updated_at'],
|
|
campaign_web_authn_credential: ['created_at', 'updated_at'],
|
|
supporter: ['created_at', 'updated_at'],
|
|
campaign_demand: ['created_at', 'updated_at'],
|
|
}
|
|
|
|
const SENSITIVE_COLUMNS = {
|
|
campaign_user: ['email', 'name'],
|
|
supporter: ['email', 'name'],
|
|
leadership: ['name', 'email', 'phone'],
|
|
campaign_web_authn_credential: ['public_key'],
|
|
}
|
|
|
|
// Payload-managed columns that are always "derived" (not compared as content).
|
|
const PAYLOAD_MANAGED = new Set(['id', 'created_at', 'updated_at', '_status'])
|
|
|
|
// Schema introspection: list columns and their types for a table.
|
|
async function introspectColumns(client, table) {
|
|
const { rows } = await client.query(
|
|
`SELECT column_name, data_type FROM information_schema.columns
|
|
WHERE table_schema = 'public' AND table_name = $1
|
|
ORDER BY ordinal_position`,
|
|
[table],
|
|
)
|
|
return rows.map((r) => ({ name: r.column_name, type: r.data_type }))
|
|
}
|
|
|
|
// Classify a column into a bucket: 'content' | 'derived' | 'ensitive' | 'internal'.
|
|
function classifyColumn(table, colName) {
|
|
if (PAYLOAD_MANAGED.has(colName)) return 'managed'
|
|
if ((DERIVED_COLUMNS[table] || []).includes(colName)) return 'derived'
|
|
if ((SENSITIVE_COLUMNS[table] || []).includes(colName)) return 'sensitive'
|
|
return 'content'
|
|
}
|
|
|
|
// Build a SELECT expression that returns jsonb columns as ::text (preserving
|
|
// exact representation — '1' vs '1.0' diverges, SQL NULL vs JSON null differs)
|
|
// and other columns as ::text with a NULL sentinel.
|
|
function buildSelectExpression(columns) {
|
|
const parts = columns.map((col) => {
|
|
if (col.type === 'jsonb') {
|
|
return `"${col.name}"::text AS "${col.name}"`
|
|
}
|
|
return `COALESCE("${col.name}"::text, '§NULL§') AS "${col.name}"`
|
|
})
|
|
return parts.join(', ')
|
|
}
|
|
|
|
// Strict JS comparator: two row values are equal iff their string representations
|
|
// match exactly. No trim, no round, no coalesce.
|
|
function compareRows(neonRow, targetRow, columns) {
|
|
const diffs = []
|
|
for (const col of columns) {
|
|
const nv = neonRow[col.name]
|
|
const tv = targetRow[col.name]
|
|
if (nv !== tv) {
|
|
diffs.push({ column: col.name, type: col.type, neon: nv, target: tv })
|
|
}
|
|
}
|
|
return diffs
|
|
}
|
|
|
|
// Fetch all rows for a table, keyed by id, with selected columns.
|
|
async function fetchRowsByKey(client, table, columns, keyCol = 'id') {
|
|
const selectExpr = buildSelectExpression(columns)
|
|
const res = await client.query(`SELECT ${selectExpr} FROM "${table}" ORDER BY "${keyCol}"`)
|
|
const map = new Map()
|
|
for (const row of res.rows) {
|
|
map.set(String(row[keyCol]), row)
|
|
}
|
|
return map
|
|
}
|
|
|
|
// Truncate a value for the report artifact (PII-safe, max 120 chars).
|
|
function truncateForReport(val, maxLen = 120) {
|
|
if (val === null || val === undefined) return 'NULL'
|
|
const s = String(val)
|
|
return s.length > maxLen ? s.slice(0, maxLen) + '…' : s
|
|
}
|
|
|
|
// Render a single table's value comparison to console + optional report lines.
|
|
function renderTableValues(table, diffs, neonCount, targetCount, commonIds, reportLines) {
|
|
const contentDiffs = diffs.filter((d) => d.bucket === 'content')
|
|
const derivedDiffs = diffs.filter((d) => d.bucket === 'derived')
|
|
const sensitiveDiffs = diffs.filter((d) => d.bucket === 'sensitive')
|
|
|
|
const ok = contentDiffs.length === 0
|
|
const detail =
|
|
`rows ${neonCount}=${targetCount}; common=${commonIds.length}; ` +
|
|
`content_diffs=${contentDiffs.length}; derived_diffs=${derivedDiffs.length}; ` +
|
|
`sensitive_diffs=${sensitiveDiffs.length}`
|
|
|
|
report(table, ok, detail)
|
|
|
|
if (contentDiffs.length > 0) {
|
|
console.log(` content divergences:`)
|
|
for (const d of contentDiffs) {
|
|
console.log(
|
|
` id=${d.id} .${d.column}: neon=${truncateForReport(d.neon)} target=${truncateForReport(d.target)}`,
|
|
)
|
|
}
|
|
}
|
|
if (derivedDiffs.length > 0) {
|
|
console.log(` derived (informational): ${derivedDiffs.length} divergences`)
|
|
}
|
|
if (sensitiveDiffs.length > 0) {
|
|
console.log(` sensitive (PII, not printed): ${sensitiveDiffs.length} divergences`)
|
|
}
|
|
|
|
// Report artifact lines
|
|
if (reportLines) {
|
|
reportLines.push(`## ${table}`)
|
|
reportLines.push(``)
|
|
reportLines.push(`- rows: neon=${neonCount}, target=${targetCount}, common=${commonIds.length}`)
|
|
reportLines.push(`- content diffs: ${contentDiffs.length}`)
|
|
reportLines.push(`- derived diffs (informational): ${derivedDiffs.length}`)
|
|
reportLines.push(`- sensitive diffs (PII, not printed): ${sensitiveDiffs.length}`)
|
|
reportLines.push(``)
|
|
if (contentDiffs.length > 0) {
|
|
reportLines.push(`### Content divergences`)
|
|
reportLines.push(``)
|
|
reportLines.push(`| id | column | neon | target |`)
|
|
reportLines.push(`|---|--------|------|--------|`)
|
|
for (const d of contentDiffs) {
|
|
reportLines.push(
|
|
`| ${d.id} | ${d.column} | ${truncateForReport(d.neon)} | ${truncateForReport(d.target)} |`,
|
|
)
|
|
}
|
|
reportLines.push(``)
|
|
}
|
|
if (derivedDiffs.length > 0) {
|
|
reportLines.push(`### Derived divergences (informational)`)
|
|
reportLines.push(``)
|
|
for (const d of derivedDiffs) {
|
|
reportLines.push(
|
|
`- id=${d.id} .${d.column}: neon=${truncateForReport(d.neon)} target=${truncateForReport(d.target)}`,
|
|
)
|
|
}
|
|
reportLines.push(``)
|
|
}
|
|
if (sensitiveDiffs.length > 0) {
|
|
reportLines.push(`### Sensitive divergences (PII, not printed)`)
|
|
reportLines.push(``)
|
|
reportLines.push(
|
|
`- ${sensitiveDiffs.length} rows with PII divergence (column names: ${[...new Set(sensitiveDiffs.map((d) => d.column))].join(', ')})`,
|
|
)
|
|
reportLines.push(``)
|
|
}
|
|
}
|
|
}
|
|
|
|
// Run values-mode comparison for one table.
|
|
async function reconcileValuesTable(neon, target, table) {
|
|
const neonCols = await introspectColumns(neon, table)
|
|
const targetCols = await introspectColumns(target, table)
|
|
|
|
// Use intersection of columns (ignore columns present in only one side).
|
|
const neonColNames = new Set(neonCols.map((c) => c.name))
|
|
const commonColDefs = targetCols.filter((c) => neonColNames.has(c.name))
|
|
|
|
// Classify all columns.
|
|
const classified = commonColDefs.map((col) => ({
|
|
...col,
|
|
bucket: classifyColumn(table, col.name),
|
|
}))
|
|
|
|
const contentCols = classified.filter((c) => c.bucket === 'content')
|
|
const derivedCols = classified.filter((c) => c.bucket === 'derived')
|
|
const sensitiveCols = classified.filter((c) => c.bucket === 'sensitive')
|
|
const managedCols = classified.filter((c) => c.bucket === 'managed')
|
|
|
|
console.log(
|
|
` [${table}] columns: ${classified.length} total; ` +
|
|
`content=${contentCols.length} derived=${derivedCols.length} ` +
|
|
`sensitive=${sensitiveCols.length} managed=${managedCols.length}`,
|
|
)
|
|
|
|
// If no content columns, nothing to fail on.
|
|
if (contentCols.length === 0) {
|
|
report(table, true, 'no content columns to compare')
|
|
return []
|
|
}
|
|
|
|
// Fetch rows (content + derived + sensitive; managed excluded from comparison).
|
|
const fetchCols = [...contentCols, ...derivedCols, ...sensitiveCols]
|
|
const neonRows = await fetchRowsByKey(neon, table, fetchCols)
|
|
const targetRows = await fetchRowsByKey(target, table, fetchCols)
|
|
|
|
const commonIds = [...neonRows.keys()].filter((id) => targetRows.has(id))
|
|
const neonOnly = [...neonRows.keys()].filter((id) => !targetRows.has(id))
|
|
const targetOnly = [...targetRows.keys()].filter((id) => !neonRows.has(id))
|
|
|
|
if (neonOnly.length > 0 || targetOnly.length > 0) {
|
|
console.log(
|
|
` id-only: neon=${neonOnly.length} target=${targetOnly.length} (use id-mode for details)`,
|
|
)
|
|
}
|
|
|
|
// Compare rows present in both.
|
|
const allDiffs = []
|
|
for (const id of commonIds) {
|
|
const diffs = compareRows(neonRows.get(id), targetRows.get(id), fetchCols)
|
|
for (const d of diffs) {
|
|
allDiffs.push({ id, ...d })
|
|
}
|
|
}
|
|
|
|
return allDiffs
|
|
}
|
|
// polymorphic FK; we normalise to (parent, path, child) so semantically equal
|
|
// relationships are comparable even when Payload churn changes row ids. The
|
|
// child id lives in whichever `<relation>_id` column is set for the path — the
|
|
// column set is introspected per table (they differ: municipality_rels has
|
|
// campaign_user_id/state_deputy_id, leadership_rels has municipalities_id, …).
|
|
async function fetchRels(client, table) {
|
|
const { rows: cols } = await client.query(
|
|
`SELECT column_name FROM information_schema.columns
|
|
WHERE table_schema = 'public' AND table_name = $1 AND column_name LIKE '%\\_id'`,
|
|
[table],
|
|
)
|
|
const childCandidates = cols
|
|
.map((c) => c.column_name)
|
|
.filter((c) => !['parent_id'].includes(c))
|
|
.map((c) => `"${c}"::text`)
|
|
if (childCandidates.length === 0) throw new Error(`sem colunas *_id em ${table}`)
|
|
const childExpr = `COALESCE(${childCandidates.join(', ')}, '')`
|
|
const res = await client.query(
|
|
`SELECT parent_id::text AS parent, path, ${childExpr} AS child
|
|
FROM "${table}"`,
|
|
)
|
|
return res.rows.map((r) => `${r.parent}|${r.path}|${r.child}`)
|
|
}
|
|
|
|
async function runIdMode(neon, target) {
|
|
for (const table of CORE_TABLES) {
|
|
const neonIds = await fetchIds(neon, table)
|
|
const targetIds = await fetchIds(target, table)
|
|
const neonSet = new Set(neonIds)
|
|
const targetSet = new Set(targetIds)
|
|
const neonOnly = neonIds.filter((id) => !targetSet.has(id))
|
|
const targetOnly = targetIds.filter((id) => !neonSet.has(id))
|
|
report(
|
|
table,
|
|
neonOnly.length === 0 && targetOnly.length === 0,
|
|
`counts ${neonIds.length}=${targetIds.length}; neon_only=${neonOnly.length} target_only=${targetOnly.length}`,
|
|
)
|
|
}
|
|
|
|
for (const table of REL_TABLES) {
|
|
const neonRels = await fetchRels(neon, table)
|
|
const targetRels = await fetchRels(target, table)
|
|
const neonSet = new Set(neonRels)
|
|
const targetSet = new Set(targetRels)
|
|
const absolved = table === 'municipality_rels' ? ABSOLVED_MUNICIPALITY_RELS : new Set()
|
|
const neonOnly = neonRels.filter((r) => !targetSet.has(r) && !absolved.has(r))
|
|
const targetOnly = targetRels.filter((r) => !neonSet.has(r) && !absolved.has(r))
|
|
const absolvedHits = [...absolved].filter((r) => neonSet.has(r) || targetSet.has(r)).length
|
|
const ok = neonOnly.length === 0 && targetOnly.length === 0
|
|
const detail = `rels ${neonRels.length}=${targetRels.length}; neon_only=${neonOnly.length} target_only=${targetOnly.length}`
|
|
report(
|
|
table,
|
|
ok,
|
|
absolvedHits > 0 ? `${detail} (+${absolvedHits} absorvida pós-OPS51)` : detail,
|
|
)
|
|
}
|
|
}
|
|
|
|
async function runValuesMode(neon, target) {
|
|
const tables = TABLE_FILTER ? [TABLE_FILTER] : CORE_TABLES
|
|
const reportLines = []
|
|
let totalContentDiffs = 0
|
|
|
|
for (const table of tables) {
|
|
if (!CORE_TABLES.includes(table)) {
|
|
die(`tabela "${table}" não é uma das 13 tabelas da vertical: ${CORE_TABLES.join(', ')}`)
|
|
}
|
|
|
|
const diffs = await reconcileValuesTable(neon, target, table)
|
|
const contentDiffs = diffs.filter((d) => d.bucket === 'content')
|
|
totalContentDiffs += contentDiffs.length
|
|
|
|
const neonRows = await fetchRowsByKey(neon, table, [{ name: 'id', type: 'integer' }])
|
|
const targetRows = await fetchRowsByKey(target, table, [{ name: 'id', type: 'integer' }])
|
|
const commonIds = [...neonRows.keys()].filter((id) => targetRows.has(id))
|
|
|
|
renderTableValues(table, diffs, neonRows.size, targetRows.size, commonIds, reportLines)
|
|
}
|
|
|
|
// Write report artifact if requested.
|
|
if (REPORT_PATH) {
|
|
const fs = await import('node:fs')
|
|
const header = [
|
|
`# Relatório de verificação de valores — OPS84`,
|
|
``,
|
|
`- Data: ${new Date().toISOString()}`,
|
|
`- Neon: ${new URL(NEON_DATABASE_URL).host}`,
|
|
`- Target: ${new URL(TARGET_DATABASE_URL).host}`,
|
|
`- Modo: values${TABLE_FILTER ? ` (tabela: ${TABLE_FILTER})` : ' (13 tabelas)'}`,
|
|
`- Total content diffs: ${totalContentDiffs}`,
|
|
``,
|
|
]
|
|
fs.writeFileSync(REPORT_PATH, [...header, ...reportLines].join('\n'))
|
|
console.log(`\n[ops84:reconcile-values] relatório escrito em ${REPORT_PATH}`)
|
|
}
|
|
|
|
return totalContentDiffs
|
|
}
|
|
|
|
async function main() {
|
|
console.log(`[ops79:reconcile] Neon : ${new URL(NEON_DATABASE_URL).host}`)
|
|
console.log(`[ops79:reconcile] Target: ${new URL(TARGET_DATABASE_URL).host}`)
|
|
|
|
const neon = await connectReadOnly(NEON_DATABASE_URL)
|
|
const target = await connectReadOnly(TARGET_DATABASE_URL)
|
|
if (VALUES_MODE) {
|
|
await neon.query('SET statement_timeout = 60000')
|
|
await target.query('SET statement_timeout = 60000')
|
|
}
|
|
console.log(`[ops79:reconcile] conexões read-only OK`)
|
|
|
|
if (VALUES_MODE) {
|
|
const contentDiffs = await runValuesMode(neon, target)
|
|
await neon.end()
|
|
await target.end()
|
|
|
|
const failures = results.filter((r) => !r.ok)
|
|
console.log(
|
|
`\n[ops84:reconcile-values] ${results.length - failures.length}/${results.length} PASS` +
|
|
(failures.length > 0 ? `; ${failures.length} FAIL` : ''),
|
|
)
|
|
// Exit 1 if any content divergence (the acceptance criterion).
|
|
process.exit(contentDiffs > 0 || failures.length > 0 ? 1 : 0)
|
|
} else {
|
|
await runIdMode(neon, target)
|
|
await neon.end()
|
|
await target.end()
|
|
|
|
const failures = results.filter((r) => !r.ok)
|
|
console.log(
|
|
`\n[ops79:reconcile] ${results.length - failures.length}/${results.length} PASS` +
|
|
(failures.length > 0 ? `; ${failures.length} FAIL` : ''),
|
|
)
|
|
process.exit(failures.length > 0 ? 1 : 0)
|
|
}
|
|
}
|
|
|
|
main().catch((err) => {
|
|
die(err?.message || String(err))
|
|
})
|