xiaoshu-app/scripts/tests/payroll-incremental-reads.test.mjs

import {test} from 'node:test';import assert from 'node:assert/strict';import fs from 'node:fs';import {PGlite} from '@electric-sql/pglite';
const sql=fs.readFileSync(new URL('../sql/payroll-incremental-reads.sql',import.meta.url),'utf8');
test('incremental cutoff hashes equal full rows after writes, rollback, moves, tombstones, truncate and schema changes',async()=>{
 const db=new PGlite();try{
 await db.exec(`SET TimeZone='Etc/GMT-8';SET DateStyle='ISO, MDY';SET extra_float_digits=1;CREATE TABLE "CommonModel"("objectId" text PRIMARY KEY,company text,"modelId" numeric,"updatedAt" timestamptz,details jsonb);`);
 for(const t of ['CourseAppointment','LessonRecord','MemoryPracticeRecord'])await db.exec(`CREATE TABLE "${t}"("objectId" text PRIMARY KEY,company text,"updatedAt" timestamptz,amount numeric,details jsonb)`);
 await db.exec(`INSERT INTO "CommonModel" VALUES('a','a',54,'2026-11-10T12:00:00+08','{"nested":[1,2]}'),('b','a',99,now(),'{}');INSERT INTO "LessonRecord" VALUES('l','a',now(),1.25,'{"large":"original"}');`);
 await db.exec(sql);
 const one=async(q,args=[])=>(await db.query(q,args)).rows[0];
 const raw=`SELECT md5(COALESCE(string_agg(hash,'' ORDER BY kind COLLATE "C",hash COLLATE "C"),'')) AS hash FROM (SELECT 'common' kind,md5(c::text) hash FROM "CommonModel" c WHERE company=$1 AND "modelId" IN (54,59,60) UNION ALL SELECT 'appointment',md5(c::text) FROM "CourseAppointment" c WHERE company=$1 UNION ALL SELECT 'lesson',md5(c::text) FROM "LessonRecord" c WHERE company=$1 UNION ALL SELECT 'review',md5(c::text) FROM "MemoryPracticeRecord" c WHERE company=$1) s`;
 async function equal(){for(const co of ['a','b','empty'])assert.equal((await one('SELECT xs_payroll_data_version($1) AS hash',[co])).hash,(await one(raw,[co])).hash);}
 assert.equal((await one('SELECT xs_payroll_digest_ready() ready')).ready,true);await equal();
 for(const change of [`UPDATE "LessonRecord" SET amount=2.5,details='{"large":"changed"}'`,`UPDATE "LessonRecord" SET company='b',"objectId"='moved'`,`UPDATE "CommonModel" SET "modelId"=59 WHERE "objectId"='b'`,`UPDATE "CommonModel" SET "modelId"=99 WHERE "objectId"='a'`,`DELETE FROM "LessonRecord"`,`INSERT INTO "MemoryPracticeRecord" VALUES('r','a',now(),0,'{"deleted":true}')`,`TRUNCATE "MemoryPracticeRecord"`]){await db.exec(change);await equal();}
 await db.exec(`BEGIN;UPDATE "CommonModel" SET details='{"inside":true}';`);await equal();await db.exec('ROLLBACK');await equal();
 await db.exec(`ALTER TABLE "CommonModel" DISABLE TRIGGER xs_payroll_digest_row;UPDATE "CommonModel" SET details='{"missed":true}';ALTER TABLE "CommonModel" ENABLE ALWAYS TRIGGER xs_payroll_digest_row;`);assert.equal((await one('SELECT xs_payroll_digest_ready() ready')).ready,false);await equal();
 await db.exec(sql);assert.equal((await one('SELECT xs_payroll_digest_ready() ready')).ready,true);await equal();
 await db.exec('ALTER TABLE "LessonRecord" ADD COLUMN extra text DEFAULT \'new column\'');assert.equal((await one('SELECT xs_payroll_digest_ready() ready')).ready,false);await equal();
 await db.exec("SET TimeZone='UTC'");await equal();
 }finally{await db.close();}
});
test('verified preview cache invalidates every dependency, code/schema changes and failed publish races',async()=>{
 const db=new PGlite();try{
 await db.exec(`SET TimeZone='Etc/GMT-8';SET DateStyle='ISO, MDY';SET extra_float_digits=1;`);
 const tables=['CommonModel','CourseAppointment','LessonRecord','MemoryPracticeRecord','LessonRecordFact','ReviewReward','ReviewRewardCorrection','TeachingCompensationRule','CourseTeachingProfile','CoachPayRate','TeachingCourseCategory','PayrollLine','PayrollBatch','LegacyDirectRun','LegacyDirectEvidence','LegacySyncConflict','PayrollCutoffDecision','PayrollCutoffApproval','_User','Node','CourseBinding'];
 for(const tab of tables){await db.exec(`CREATE TABLE "${tab}"("objectId" text PRIMARY KEY,company text,value text,"modelId" numeric DEFAULT 54);INSERT INTO "${tab}"("objectId",company,value) VALUES('a','a','old'),('b','b','other')`);}
 await db.exec(`CREATE TABLE "Function"(path text,code text);INSERT INTO "Function" VALUES('xiaoshu/ops/gateway-v3','original code');CREATE VIEW "ReviewRewardIncome" AS SELECT * FROM "ReviewReward";`);
 await db.exec(sql);await db.exec(fs.readFileSync(new URL('../sql/payroll-verified-preview.sql',import.meta.url),'utf8'));
 const one=async(q,args=[])=>(await db.query(q,args)).rows[0];
 const read=async(co='a',key='month:11')=>(await one('SELECT xs_payroll_preview_cache_read($1,$2) r',[co,key])).r;
 let buildSnapshot;
 const write=async(stamp)=>(await one('SELECT xs_payroll_preview_cache_write($1,$2,$3,$4::jsonb) saved',['a','month:11',stamp,JSON.stringify({snapshot:buildSnapshot,result:{amount:99,settlementReady:true}})])).saved;
 for(const tab of tables){const before=await read();buildSnapshot=before.snapshot;assert.equal(await write(before.stamp),true);assert.equal((await read()).payload.amount,99);await db.exec(`UPDATE "${tab}" SET value=value||'changed' WHERE company='a'`);assert.equal((await read()).payload,null,tab);assert.equal(await write(before.stamp),false,tab+' race');}
 let before=await read();buildSnapshot=before.snapshot;await write(before.stamp);assert.equal((await read('b')).payload,null);assert.equal((await read('a','month:12')).payload,null);
 await db.exec(`UPDATE "LessonRecord" SET value='other tenant' WHERE company='b'`);assert.equal((await read()).payload.amount,99);
 await db.exec(`BEGIN;UPDATE "ReviewReward" SET value='uncommitted' WHERE company='a'`);assert.equal((await read()).payload,null);await db.exec('ROLLBACK');assert.equal((await read()).payload.amount,99);
 const aba=await read();await db.exec(`UPDATE "LessonRecord" SET value='temporary' WHERE company='a';UPDATE "LessonRecord" SET value='oldchanged' WHERE company='a';`);assert.equal((await read()).payload,null);assert.equal(await write(aba.stamp),false);
 await db.exec(`UPDATE "Function" SET code='new deployment'`);assert.equal((await read()).payload,null);before=await read();buildSnapshot=before.snapshot;await write(before.stamp);
 before=await read();buildSnapshot=before.snapshot;await write(before.stamp);await db.exec(`ALTER TABLE "LessonRecord" DISABLE TRIGGER xs_payroll_digest_row;UPDATE "LessonRecord" SET value='missed during outage' WHERE company='a';ALTER TABLE "LessonRecord" ENABLE ALWAYS TRIGGER xs_payroll_digest_row;`);assert.equal((await read()).payload,null);await db.exec(sql);assert.equal((await read()).payload,null,'rebuild must invalidate pre-outage caches');
 await db.exec(`ALTER TABLE "ReviewReward" ADD COLUMN new_column text DEFAULT 'new';`);assert.equal((await read()).payload,null);
 }finally{await db.close();}
});