verify-conservation.mjs
2.83 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
#!/usr/bin/env node
/**
* 对账（SDD「积分清算」）：① 已关闭托管的发放明细之和 = 托管金额；② held 托管都对应未结束的任务，
* 未结束的任务都有 held 托管；③ 每笔 voko 流水的 ref 都能找到业务记录。
* 任一不满足即失败并打印数据 id。可单独运行，也在每个场景脚本末尾运行。
*/
import { fileURLToPath } from 'node:url'
import { createDb } from '@drama/db'
import { databaseUrlFromArgs } from '../../../src/db/env-file.js'
const TERMINAL = ['settled', 'cancelled', 'refunded']
/**
* @param {import('knex').Knex} db
* @returns {Promise<Array<{ rule: string, id: string, detail: string }>>}
*/
export async function verifyConservation(db) {
const problems = []
const closed = await db('vk_escrows as e')
.leftJoin('vk_escrow_payouts as p', 'p.escrow_id', 'e.id')
.whereNot('e.status', 'held')
.groupBy('e.id', 'e.amount')
.havingRaw('COALESCE(SUM(p.amount), 0) <> e.amount')
.select('e.id', 'e.amount', db.raw('COALESCE(SUM(p.amount), 0) as paid'))
for (const row of closed) problems.push({ rule: 'payouts_sum', id: row.id, detail: `amount=${row.amount}, payouts=${row.paid}` })
const heldOnEnded = await db('vk_escrows as e').join('vk_tasks as t', 't.id', 'e.task_id').where('e.status', 'held').whereIn('t.status', TERMINAL).select('e.id', 't.status')
for (const row of heldOnEnded) problems.push({ rule: 'held_on_ended_task', id: row.id, detail: `task status=${row.status}` })
const openWithoutHeld = await db('vk_tasks as t')
.leftJoin('vk_escrows as e', (join) => join.on('e.task_id', 't.id').andOnVal('e.status', 'held'))
.whereNotIn('t.status', TERMINAL)
.whereNull('e.id')
.select('t.id', 't.status')
for (const row of openWithoutHeld) problems.push({ rule: 'active_task_without_held_escrow', id: row.id, detail: `task status=${row.status}` })
const orphanTaskRefs = await db('billing_ledger as l').leftJoin('vk_tasks as t', 't.id', 'l.ref_id').where({ 'l.source': 'voko', 'l.ref_type': 'vk_task' }).whereNull('t.id').select('l.id', 'l.ref_id')
const orphanAgentRefs = await db('billing_ledger as l').leftJoin('vk_agents as a', 'a.id', 'l.ref_id').where({ 'l.source': 'voko', 'l.ref_type': 'vk_agent' }).whereNull('a.id').select('l.id', 'l.ref_id')
for (const row of [...orphanTaskRefs, ...orphanAgentRefs]) problems.push({ rule: 'ledger_ref_missing', id: row.id, detail: `ref_id=${row.ref_id}` })
return problems
}
if (process.argv[1] === fileURLToPath(import.meta.url)) {
const db = createDb({ url: databaseUrlFromArgs(process.argv.slice(2)), pool: { min: 1, max: 1 } })
try {
const problems = await verifyConservation(db)
console.log(JSON.stringify({ status: problems.length === 0 ? 'PASS' : 'FAIL', problems }, null, 2))
if (problems.length > 0) process.exitCode = 1
} finally {
await db.destroy()
}
}