xinmi/scripts/verify_final_migration.cjs

const fs = require('node:fs')
const path = require('node:path')
const { execFileSync } = require('node:child_process')

const directory = path.resolve(__dirname, '../../acceptance')
const checks = []
// Explicitly reviewed post-migration deletion, not a general count exemption.
const reviewedDeletions = [{
  tenant_id: 10059,
  source_id: 2,
  agent_id: '21feb78a-d1ea-5d16-a030-7b1b24f3306a',
  digital_human_id: '58e3e84f-13cc-a2eb-4505-c944af7c46ce',
  audit_id: '1f72b3ad-0c5d-4377-aaff-5244d703d70d',
  owner_id: 'bad2d456-be74-458e-9037-661a371285b8',
  owner_name: 'admin',
  agent_deleted_at: '2026-09-19T08:31:48.443645+00:00',
  human_deleted_at: '2026-09-19T08:31:48.448769+00:00',
  audited_at: '2026-09-19T08:31:48.456139+00:00',
}]
const reviewedSampleDeletion = {
  tenant_id: 10059,
  resource_id: '1c257a32-1fbe-4d82-ba91-1bdee3427da9',
  audit_id: '53942b08-075e-4aad-a6dc-da329f7fd994',
  owner_id: 'bad2d456-be74-458e-9037-661a371285b8',
  owner_name: 'admin',
  audited_at: '2026-09-18T06:07:01.709356+00:00',
  revision_id: '53d54109-e8bc-4ec8-8f37-701611c09371',
}
const sql = statement => execFileSync('docker', ['--context', 'colima-yawan', 'exec', 'WeKnora-postgres', 'psql', '-U', 'postgres', '-d', 'yawan_native_acceptance_20260915', '-v', 'ON_ERROR_STOP=1', '-Atc', `BEGIN READ ONLY; SET LOCAL statement_timeout='30s'; ${statement}; COMMIT;`], { encoding: 'utf8', stdio: ['ignore', 'pipe', 'pipe'] }).trim().split('\n').filter(line => line.startsWith('{')).map(JSON.parse)[0]
const literal = value => "'" + String(value).replaceAll("'", "''") + "'"
const check = (name, actual, expected) => { checks.push({ name, actual, expected, passed: actual === expected }) }

function assessLegacyAgent(row, reviews = reviewedDeletions) {
  const issues = []
  for (const key of ['target_present', 'role_preserved', 'content_tags_preserved']) {
    if (row[key] !== true) issues.push(key)
  }
  const review = reviews.find(item => item.agent_id === row.target_id && item.tenant_id === row.tenant_id)
  if (row.deleted_at || review) {
    if (!review) issues.push('unreviewed_deletion')
    else {
      const audit = row.deletion_audit || {}
      const exact = {
        source_id: row.source_id === review.source_id,
        agent_tombstone: row.deleted_at === review.agent_deleted_at,
        human_id: row.digital_human_id === review.digital_human_id,
        primary_agent: row.primary_agent_id === review.agent_id,
        human_tombstone: row.human_deleted_at === review.human_deleted_at,
        audit_id: audit.id === review.audit_id,
        audit_tenant: audit.tenant_id === review.tenant_id,
        audit_resource: audit.resource_id === review.digital_human_id,
        audit_actor: audit.owner_id === review.owner_id && audit.owner_name === review.owner_name,
        audit_time: audit.created_at === review.audited_at && Date.parse(row.migrated_at) < Date.parse(audit.created_at),
        successful_delete: audit.kind === 'audit' && audit.action === '删除' && audit.status === 200 && audit.metadata?.method === 'DELETE' && audit.metadata?.route === '/api/v1/digital-humans/:id',
      }
      for (const [key, valid] of Object.entries(exact)) if (!valid) issues.push(key)
    }
  }
  return { source_id: row.source_id, target_id: row.target_id, name: row.name,
    status: issues.length ? 'unresolved' : row.deleted_at ? 'reviewed_user_deletion' : 'active', issues,
    ...(row.deleted_at ? { deleted_at: row.deleted_at, digital_human_id: row.digital_human_id, deletion_audit: row.deletion_audit } : {}) }
}

function assessLegacyPersona(row, agents) {
  const issues = []
  const agent = agents.find(item => item.target_id === row.agent_id)
  if (!agent || agent.status === 'unresolved') issues.push('agent_not_accounted_for')
  for (const key of ['source_scope_matches', 'source_hash_matches', 'source_persona_matches']) {
    if (row[key] !== true) issues.push(key)
  }
  if (agent?.status === 'reviewed_user_deletion') {
    if (row.digital_human_id !== agent.digital_human_id) issues.push('deleted_human_mismatch')
    if (row.version_exists || row.binding_exists) issues.push('deleted_persona_cascade_incomplete')
  } else if (row.native_persona_preserved !== true) issues.push('native_persona_not_preserved')
  return { source_id: row.source_id, agent_id: row.agent_id, digital_human_id: row.digital_human_id,
    status: issues.length ? 'unresolved' : agent.status === 'reviewed_user_deletion' ? 'reviewed_user_deletion' : 'preserved', issues }
}

function assessSample(data, review = reviewedSampleDeletion) {
  if (data.sample_present === 1) return { resource_id: review.resource_id, status: 'active', issues: [] }
  const issues = []
  const audit = data.sample_delete_audit || {}, revision = data.sample_revision || {}
  const exact = {
    sample_absent: data.sample_present === 0,
    audit_id: audit.id === review.audit_id,
    audit_tenant: audit.tenant_id === review.tenant_id,
    audit_resource: audit.resource_id === review.resource_id,
    audit_actor: audit.owner_id === review.owner_id && audit.owner_name === review.owner_name,
    audit_time: audit.created_at === review.audited_at,
    successful_delete: audit.kind === 'audit' && audit.action === '删除' && audit.status === 200 && audit.metadata?.method === 'DELETE' && audit.metadata?.route === '/api/v1/workbench/resources/:kind/:id',
    original_revision: revision.id === review.revision_id && revision.tenant_id === review.tenant_id && revision.resource_id === review.resource_id && revision.version === 24,
    manuscript_preserved: data.sample_manuscript_preserved === 1 && revision.composed_chars > 0 && Date.parse(revision.created_at) < Date.parse(audit.created_at),
  }
  for (const [key, valid] of Object.entries(exact)) if (!valid) issues.push(key)
  return { resource_id: review.resource_id, status: issues.length ? 'unresolved' : 'reviewed_user_deletion', issues, deletion_audit: data.sample_delete_audit, retained_revision: data.sample_revision }
}

async function checkHTTP(name, url) {
  try {
    const response = await fetch(url, { signal: AbortSignal.timeout(10000) })
    check(name, response.status, 200)
  } catch {
    check(name, 'unreachable', 200)
  }
}

async function main() {
  const state = JSON.parse(fs.readFileSync(path.join(directory, 'legacy-migration-state.json'), 'utf8'))
  const ids = Object.values(state.knowledge_bases).map(row => literal(row.id)).join(',')
  const auditIDs = reviewedDeletions.map(row => literal(row.audit_id)).join(',')
  const data = sql(`SELECT json_build_object(
    'schema_version',(SELECT version FROM schema_migrations),
    'schema_dirty',(SELECT dirty FROM schema_migrations),
    'business_bases',(SELECT count(*) FROM knowledge_bases WHERE id IN (${ids}) AND tenant_id=10059 AND deleted_at IS NULL),
    'business_documents',(SELECT count(*) FROM knowledges WHERE knowledge_base_id IN (${ids}) AND deleted_at IS NULL),
    'business_chunks',(SELECT count(*) FROM chunks WHERE knowledge_base_id IN (${ids}) AND deleted_at IS NULL),
    'unparsed_documents',(SELECT count(*) FROM knowledges WHERE knowledge_base_id IN (${ids}) AND deleted_at IS NULL AND (parse_status<>'completed' OR pending_subtasks_count>0)),
    'business_categories',(SELECT count(*) FROM knowledge_tags WHERE knowledge_base_id IN (${ids})),
    'agents',(SELECT count(*) FROM yawan_legacy_asset_mappings m JOIN custom_agents a ON a.id=m.target_id AND a.tenant_id=m.target_tenant_id WHERE m.source_kind='agent' AND m.target_tenant_id=10059 AND a.deleted_at IS NULL),
    'agent_roles_preserved',(SELECT count(*) FROM yawan_legacy_asset_mappings m JOIN custom_agents a ON a.id=m.target_id AND a.tenant_id=m.target_tenant_id WHERE m.source_kind='agent' AND m.target_tenant_id=10059 AND a.config->>'role'=m.source_snapshot->>'role'),
    'agent_content_tags_preserved',(SELECT count(*) FROM yawan_legacy_asset_mappings m JOIN custom_agents a ON a.id=m.target_id AND a.tenant_id=m.target_tenant_id WHERE m.source_kind='agent' AND m.target_tenant_id=10059 AND coalesce(a.config->'content_tags','[]'::jsonb)=coalesce(m.source_snapshot->'content_tags','[]'::jsonb)),
    'legacy_agent_rows',(SELECT coalesce(json_agg(row_to_json(x)), '[]') FROM (
      SELECT m.source_id,m.target_id,m.target_tenant_id AS tenant_id,m.created_at AS migrated_at,
        a.name,a.id IS NOT NULL AS target_present,a.deleted_at,
        a.config->>'role'=m.source_snapshot->>'role' AS role_preserved,
        coalesce(a.config->'content_tags','[]'::jsonb)=coalesce(m.source_snapshot->'content_tags','[]'::jsonb) AS content_tags_preserved,
        h.id AS digital_human_id,h.primary_agent_id,h.deleted_at AS human_deleted_at,
        (SELECT row_to_json(l) FROM workbench_operation_logs l WHERE l.id IN (${auditIDs})
          AND l.tenant_id=m.target_tenant_id AND l.resource_id=h.id LIMIT 1) AS deletion_audit
      FROM yawan_legacy_asset_mappings m
      LEFT JOIN custom_agents a ON a.id=m.target_id AND a.tenant_id=m.target_tenant_id
      LEFT JOIN yawan_legacy_agent_persona_mappings p ON p.agent_id=m.target_id AND p.tenant_id=m.target_tenant_id
      LEFT JOIN digital_humans h ON h.id=p.digital_human_id AND h.tenant_id=m.target_tenant_id
      WHERE m.source_kind='agent' AND m.target_tenant_id=10059 ORDER BY m.source_id
    ) x),
    'legacy_persona_preserved',(SELECT count(*) FROM yawan_legacy_agent_persona_mappings p JOIN digital_human_versions v ON v.id=p.version_id AND v.tenant_id=p.tenant_id JOIN digital_human_bindings b ON b.id=p.binding_id AND b.tenant_id=p.tenant_id WHERE p.tenant_id=10059 AND v.persona=p.source_persona AND v.status='imported' AND v.reviewed_at IS NULL AND b.applied_version=v.number),
    'legacy_persona_rows',(SELECT coalesce(json_agg(row_to_json(x)), '[]') FROM (
      SELECT p.source_id,p.agent_id,p.digital_human_id,
        p.source_system=m.source_system AND p.source_organization_id=m.source_organization_id AND p.source_id=m.source_id AS source_scope_matches,
        p.source_snapshot_sha256=m.source_snapshot_sha256 AS source_hash_matches,
        p.source_persona=m.source_snapshot->'persona_json' AS source_persona_matches,
        v.id IS NOT NULL AS version_exists,b.id IS NOT NULL AS binding_exists,
        v.persona=p.source_persona AND v.status='imported' AND v.reviewed_at IS NULL AND b.applied_version=v.number
          AND v.digital_human_id=p.digital_human_id AND b.digital_human_id=p.digital_human_id AND b.agent_id=p.agent_id AS native_persona_preserved
      FROM yawan_legacy_agent_persona_mappings p
      LEFT JOIN yawan_legacy_asset_mappings m ON m.target_id=p.agent_id AND m.target_tenant_id=p.tenant_id AND m.source_kind='agent'
      LEFT JOIN digital_human_versions v ON v.id=p.version_id AND v.tenant_id=p.tenant_id
      LEFT JOIN digital_human_bindings b ON b.id=p.binding_id AND b.tenant_id=p.tenant_id
      WHERE p.tenant_id=10059 ORDER BY p.source_id
    ) x),
    'skills',(SELECT count(*) FROM yawan_legacy_asset_mappings WHERE source_kind='skill' AND target_tenant_id=10059),
    'legacy_business_records',(SELECT json_object_agg(source_kind,n) FROM (SELECT source_kind,count(*) n FROM yawan_legacy_business_mappings WHERE target_tenant_id=10059 GROUP BY source_kind) b),
    'operations_history',(SELECT count(*) FROM yawan_legacy_operations_mappings WHERE target_tenant_id=10059),
    'active_workbench_jobs',(SELECT count(*) FROM workbench_jobs WHERE status IN ('queued','running')),
    'active_digital_jobs',(SELECT count(*) FROM digital_human_jobs WHERE status IN ('queued','running')),
    'active_version_jobs',(SELECT count(*) FROM workbench_knowledge_version_jobs WHERE status IN ('queued','running')),
    'active_classification_jobs',(SELECT count(*) FROM knowledge_classification_jobs WHERE status='running'),
    'active_uploads',(SELECT count(*) FROM resumable_uploads WHERE state NOT IN ('completed','cancelled','failed','expired')),
    'active_balance_holds',(SELECT count(*) FROM workbench_balance_holds WHERE status='reserved'),
    'sample_present',(SELECT count(*) FROM workbench_resources WHERE id='1c257a32-1fbe-4d82-ba91-1bdee3427da9' AND tenant_id=10059),
    'sample_status',(SELECT status FROM workbench_resources WHERE id='1c257a32-1fbe-4d82-ba91-1bdee3427da9' AND tenant_id=10059),
    'sample_manuscript_preserved',(SELECT count(*) FROM workbench_revisions WHERE resource_id='1c257a32-1fbe-4d82-ba91-1bdee3427da9' AND tenant_id=10059 AND version=24 AND length(data->>'composed')>0),
    'sample_delete_audit',(SELECT row_to_json(l) FROM workbench_operation_logs l WHERE id=${literal(reviewedSampleDeletion.audit_id)}),
    'sample_revision',(SELECT json_build_object('id',id,'tenant_id',tenant_id,'resource_id',resource_id,'version',version,'created_at',created_at,'composed_chars',length(data->>'composed'))
      FROM workbench_revisions WHERE id=${literal(reviewedSampleDeletion.revision_id)})
  )`)
  check('migration_clean', data.schema_dirty, false)
  check('migration_version_supported', data.schema_version >= 210, true)
  check('six_business_bases', data.business_bases, 6)
  check('all_business_documents_preserved', data.business_documents, 63)
  check('all_business_chunks_preserved', data.business_chunks, 310666)
  check('business_indexing_completed', data.unparsed_documents, 0)
  check('business_categories_preserved', data.business_categories, 63)
  data.agent_integrity = data.legacy_agent_rows.map(row => assessLegacyAgent(row))
  check('legacy_agent_mappings_preserved', data.agent_integrity.length, 15)
  check('legacy_agent_targets_unique', new Set(data.agent_integrity.map(row => row.target_id)).size, 15)
  check('legacy_agent_historical_rows_preserved', data.legacy_agent_rows.filter(row => row.target_present).length, 15)
  check('legacy_agents_active_or_reviewed_deleted', data.agent_integrity.filter(row => row.status !== 'unresolved').length, 15)
  check('reviewed_agent_deletions_present', data.agent_integrity.filter(row => row.status === 'reviewed_user_deletion').length, reviewedDeletions.length)
  for (const row of data.agent_integrity) check(`legacy_agent_${row.source_id}_integrity`, row.issues.length, 0)
  check('legacy_agent_roles_preserved', data.agent_roles_preserved, 15)
  check('legacy_agent_content_tags_preserved', data.agent_content_tags_preserved, 15)
  data.persona_integrity = data.legacy_persona_rows.map(row => assessLegacyPersona(row, data.agent_integrity))
  check('legacy_persona_provenance_preserved', data.persona_integrity.length, 1)
  check('legacy_persona_preserved_or_reviewed_deleted', data.persona_integrity.filter(row => row.status !== 'unresolved').length, 1)
  for (const row of data.persona_integrity) check(`legacy_persona_${row.source_id}_integrity`, row.issues.length, 0)
  check('legacy_skills_preserved', data.skills, 9)
  for (const [kind, expected] of Object.entries({ agentsession: 71, campaign: 2, campaignrun: 2, canvas: 3, llmcall: 123, qcreport: 100, runevent: 82, segment: 62, taskqueueitem: 2, trainingmap: 8 })) {
    check(`legacy_business_${kind}`, data.legacy_business_records?.[kind] ?? 0, expected)
  }
  check('operations_provenance_preserved', data.operations_history, 40353)
  for (const key of ['active_workbench_jobs', 'active_digital_jobs', 'active_version_jobs', 'active_classification_jobs', 'active_uploads', 'active_balance_holds']) check(key, data[key], 0)
  data.sample_integrity = assessSample(data)
  check('real_model_task_preserved_or_reviewed_deleted', data.sample_integrity.issues.length, 0)
  check('real_model_manuscript_revision_preserved', data.sample_manuscript_preserved, 1)
  await checkHTTP('api_health', 'http://127.0.0.1:18080/health')
  await checkHTTP('frontend_available', 'http://127.0.0.1:18088/login')
  return data
}

function saveReport(data, failure) {
  const differences = checks.filter(row => !row.passed)
  const reviewedChanges = [...(data?.agent_integrity || []), ...(data?.persona_integrity || []), ...(data?.sample_integrity ? [data.sample_integrity] : [])].filter(row => row.status === 'reviewed_user_deletion')
  const report = { checked_at: new Date().toISOString(), passed: !failure && !differences.length,
    checks, differences, reviewed_changes: reviewedChanges, data, database_read_only: true,
    ...(failure ? { failure } : {}) }
  const reportPath = path.join(directory, 'final-migration-integrity.json')
  fs.writeFileSync(reportPath, JSON.stringify(report, null, 2), { mode: 0o600 })
  console.log(JSON.stringify({ checked_at: report.checked_at, passed: report.passed, checks: checks.length, differences, reviewed_changes: reviewedChanges.length, report: reportPath, ...(failure ? { failure } : {}) }, null, 2))
  if (!report.passed) process.exitCode = 1
}

module.exports = { assessLegacyAgent, assessLegacyPersona, assessSample, reviewedDeletions, reviewedSampleDeletion }
if (require.main === module) main().then(data => saveReport(data)).catch(() => {
  saveReport(undefined, 'Final verification failed; inspect the database schema or local runtime. Raw database errors are withheld from the report.')
})