Read the source and its declared rule
The original historical source has no category evidence for A. Adding category DEFAULT New makes that value appear on the existing row. This migration instead adds a nullable column and fills only supplied historical evidence; New has not been established for the old record.
| Record | Input JSON |
|---|---|
| Base input | {"records":[{"id":1,"name":"A"}],"evidence":[]} |
Compare the actual paths
Correct A retains category null. The difference comes from the actual ALTER statement and read-back, rather than a new category decision by the importer.
| Path | Output JSON |
|---|---|
| Misleading path | [{"id":1,"name":"A","category":"New"}] |
| Corrected path | [{"id":1,"name":"A","category":null}] |
import {DatabaseSync} from 'node:sqlite';
import {TextDecoder} from 'node:util';
import {inflateSync} from 'node:zlib';
import {posix} from 'node:path';
import {createHash} from 'node:crypto';
const rows=(db,sql)=>db.prepare(sql).all().map(r=>({...r}));
const failed=(action)=>{try{action();return null;}catch(e){return {code:e.errcode??e.code??e.name};}};
import assert from 'node:assert/strict';
const run=function run(i,corrected=true){
const db=new DatabaseSync(':memory:');try{db.exec('CREATE TABLE target(id INTEGER PRIMARY KEY,name TEXT);');
for(const r of i.records)db.prepare('INSERT INTO target VALUES(?,?)').run(r.id,r.name);
db.exec(corrected?'ALTER TABLE target ADD COLUMN category TEXT':"ALTER TABLE target ADD COLUMN category TEXT DEFAULT 'New'");
if(corrected)for(const e of i.evidence)db.prepare('UPDATE target SET category=? WHERE id=?').run(e.category,e.id);
return rows(db,'SELECT * FROM target ORDER BY id');
}finally{db.close();}
};
const cases=[{"label":"misleading base","input":{"records":[{"id":1,"name":"A"}],"evidence":[]},"expected":[{"id":1,"name":"A","category":"New"}],"corrected":false},{"label":"corrected base","input":{"records":[{"id":1,"name":"A"}],"evidence":[]},"expected":[{"id":1,"name":"A","category":null}],"corrected":true},{"label":"evidenced historical category","input":{"records":[{"id":1,"name":"A"}],"evidence":[{"id":1,"category":"Old"}]},"expected":[{"id":1,"name":"A","category":"Old"}],"corrected":true},{"label":"no historical rows","input":{"records":[],"evidence":[]},"expected":[],"corrected":true}];
for(const test of cases){
const actual=run(test.input,test.corrected);
assert.deepEqual(actual,test.expected,test.label);
console.log(test.label,actual);
}
Check boundaries before the next load
Evidence explicitly stating Old fills Old. An empty historical table stays empty.
| Case | Input JSON | Expected output JSON |
|---|---|---|
| Explicit historical evidence | {"records":[{"id":1,"name":"A"}],"evidence":[{"id":1,"category":"Old"}]} | [{"id":1,"name":"A","category":"Old"}] |
| No historical records | {"records":[],"evidence":[]} | [] |
May an added default establish the old record’s historical category?
Show the worked answer
No. A remains unknown here. Fill Old only when the corresponding supplied historical evidence exists.