Entity updates broken since es2022 target bump: nightly onchain sync aborts on dup-key, transaction_lightning silently accumulates duplicate rows

[Danswar]

Symptom

Since 2025-11-11, the nightly onchain sync (processSyncOnchainTransactions, 02:00 UTC) fails every night with:

duplicate key value violates unique constraint "UQ_8a233973c49afbbc14a01ce076b"

The count is suspiciously constant — exactly 55 duplicate-key errors per night on PRD, 3 on DEV, every single night. That constant is the tell: 55/3 is the entire row count of transaction_onchain in each environment. The job collides with every row it just loaded, Promise.all rejects, the @Lock() wrapper logs Error during processSyncOnchainTransactions, and the job aborts. There are zero syncOnchainTransactions: runtime= success lines in the logs — the job has effectively never completed since November.

Root cause

Commit e18f05a (PR #84, 2025-11-11) bumped tsconfig.json target from es2017 to es2022:

-    "target": "es2017",
+    "target": "es2022",

With target ≥ es2022, TypeScript's useDefineForClassFields default flips to true. Class-field declarations (like the entity base class's id) then become own properties initialized to undefined on every instance — including entities produced by repo.create({...}).

The update helpers merge a freshly-created entity into the loaded DB row with Object.assign:

api/src/subdomains/lightning/services/lightning-transaction.service.ts

Lines 308 to 322 in 67ce15f

	private async doUpdateOnchainTransaction(
	updateOnchainTransactionEntity: TransactionOnchainEntity,
	): Promise<TransactionOnchainEntity> {
	let dbOnchainTransactionEntity = await this.transactionOnchainRepo.findOneBy({
	transaction: Equal(updateOnchainTransactionEntity.transaction),
	});
	if (!dbOnchainTransactionEntity) {
	dbOnchainTransactionEntity = updateOnchainTransactionEntity;
	} else {
	Object.assign(dbOnchainTransactionEntity, updateOnchainTransactionEntity);
	}
	return this.transactionOnchainRepo.save(dbOnchainTransactionEntity);
	}

let dbOnchainTransactionEntity = await this.transactionOnchainRepo.findOneBy({
  transaction: Equal(updateOnchainTransactionEntity.transaction),
});

if (!dbOnchainTransactionEntity) {
  dbOnchainTransactionEntity = updateOnchainTransactionEntity;
} else {
  Object.assign(dbOnchainTransactionEntity, updateOnchainTransactionEntity);  // copies id: undefined!
}

return this.transactionOnchainRepo.save(dbOnchainTransactionEntity);

Pre-es2022, the fresh entity had no own id property, so Object.assign left the loaded row's id intact and save() issued an UPDATE. Post-es2022, the fresh entity carries id: undefined as an own property, Object.assign wipes the loaded row's id, and save() issues an INSERT — colliding with the very row the code just found.

The worse, silent half

transaction_onchain has UNIQUE ("transaction") (migration 1697006086810-Transactions.js), so its corruption is loud — the INSERT fails.

transaction_lightning has no unique constraint on (type, transaction) — only the PK. The identical pattern in doUpdateLightningTransaction:

api/src/subdomains/lightning/services/lightning-transaction.service.ts

Lines 409 to 424 in 67ce15f

	private async doUpdateLightningTransaction(
	updateTransactionLightningEntity: TransactionLightningEntity,
	): Promise<TransactionLightningEntity> {
	let dbTransactionLightningEntity = await this.transactionLightningRepo.findOneBy({
	type: Equal(updateTransactionLightningEntity.type),
	transaction: Equal(updateTransactionLightningEntity.transaction),
	});
	if (!dbTransactionLightningEntity) {
	dbTransactionLightningEntity = updateTransactionLightningEntity;
	} else {
	Object.assign(dbTransactionLightningEntity, updateTransactionLightningEntity);
	}
	return this.transactionLightningRepo.save(dbTransactionLightningEntity);
	}

…therefore succeeds in inserting duplicates. Consequences, ongoing since November 2025:

• The 03:00 UTC processSyncLightningTransactions re-inserts its trailing-window candidate set as new rows every night (its logged entries= count has been growing, e.g. 369 → 1250/night over one recent week).
• The websocket live-update path turns every state transition (e.g. invoice OPEN → SETTLED) into a new row instead of an update, so a single invoice exists multiple times in different states.

Same mechanism likely affects the other Object.assign-onto-loaded-entity sites: user-boltcard.service.ts:62 and lightning-wallet.service.ts:286 — worth auditing all of them in the fix.

Note user_transaction.lightningTransactionId has an FK onto transaction_lightning.id, so any dedupe cleanup has to remap references, not just delete rows.

Suggested fix

Two options, not mutually exclusive:

1. "useDefineForClassFields": false in tsconfig — one line, restores the pre-November semantics for the entire codebase and kills the whole bug class at once.
2. Replace the Object.assign(dbEntity, freshEntity) merge sites with an id-safe merge (repo.merge() with an explicit field list, repo.update(id, partial), or explicit per-field assignment).

Option 1 is the smallest safe change; option 2 is more future-proof if a later target bump is ever wanted. Either way a regression test pinning "update does not change the row's id / does not insert a second row" would prevent a silent re-flip.

Optional hardening while in there: add the missing UNIQUE (type, transaction) constraint on transaction_lightning after the cleanup below, so this failure mode can never again be silent.

Follow-up: data cleanup (separate task)

After the code fix, transaction_lightning needs a dedupe pass (keep the newest row per (type, transaction), remap user_transaction.lightningTransactionId FKs to the surviving row, delete the rest) and the onchain sync needs one clean full run to verify. Sizing the duplicate population first (SELECT type, transaction, COUNT(*) ... GROUP BY ... HAVING COUNT(*) > 1) would tell us how big that task is.

Verification after deploy

• 02:00 UTC job: Error during processSyncOnchainTransactions disappears; a syncOnchainTransactions: runtime= success line appears.
• Zero UQ_8a233973 duplicate-key lines in the DB logs.
• transaction_lightning row count stops growing at the nightly sync rate; invoice state transitions update rows in place.

[TaprootFreak]

Correction: the growing entries= count is not a symptom of this bug

The root cause and the fix in #206 are correct — this only concerns one supporting symptom in the Consequences section:

The 03:00 UTC processSyncLightningTransactions re-inserts its trailing-window candidate set as new rows every night (its logged entries= count has been growing, e.g. 369 → 1250/night over one recent week).

That number is the most visible one in this issue, so it is the first thing anyone reaches for when verifying the fix — but it is unrelated to the duplicate rows, and it will not move now that the fix is deployed.

entries= cannot be inflated by duplicate rows. It is entities.length from syncLightningTransactions (lightning-transaction.service.ts:117-120), and that array is built exclusively from source data:

• saveLightningTransactions assembles transactionEntities only from invoices / payments / routings (:211-215), fetched through getLightningTransactions from client.getInvoices/getPayments/getRoutings — plain LND REST calls, no repository access. The window filtering happens source-side in LND.
• createTransactionLightningEntities is a straight transactions.map(...) — one entity per source DTO, no expansion.
• doUpdateLightningTransaction returns Promise<TransactionLightningEntity> (singular), so Promise.all(batch.map(...)) is exactly batch.length and the .flat() at :239 sums back to transactionEntities.length.
• The only DB read in the path is the findOneBy at :412-415. Its result decides whether Object.assign runs — never how many elements are returned.
• getEntriesWithMaxCreationTimestamp (:171) sits in the if (!startDate) branch, which the cron never reaches, since :112 sets startDate = Util.daysBefore(7).

What the growth actually is: the LNbits funding health probe from #203 (merged 07-07). Each probe creates a real 1 sat invoice (memo: 'monitoring healthcheck'), throttled by PROBE_TTL_MS = 5 min. Those invoices are never settled — they expire, but LND's ListInvoices still returns them, so they stay inside the 7-day window.

The arithmetic fits exactly:

baseline 07-08	378
plateau 07-16	1647
1647 − 378 = 1269 over 7 days	181/night
378 + 7 × 181	1647

Nightly deltas from 07-09: +149, +174, +155, +180, +214, +200, +181, +16. Scattering around ~180, then a plateau exactly when a 7-day rolling window finishes filling — the signature of a constant machine-generated source rate, not of compounding duplicates.

Measured against the production logs to confirm: the health endpoint is polled every 300 s (288/day); simulating the 5 min throttle against those timestamps yields ~177 probes/day, matching the 181/day derived from the entries= curve.

The timing settles it independently of the code: the plateau (1631 → 1647, +16) happened at the 07-16 03:00 UTC run — roughly 17 hours before the fix reached production at ~20:00 UTC on 07-16. The number stabilised on its own, pre-fix. Duplicate-driven growth would never plateau.

Verification signals that do work

The three in Verification after deploy are right; two notes on how they pair:

1. Primary, positive, binary: the first syncOnchainTransactions: runtime= success line at the 02:00 UTC run. Baseline: zero such lines since November 2025.
2. Zero UQ_8a233973 dup-key errors — meaningful only paired with (1). On its own, zero is also exactly what a job that never ran produces. Baseline: exactly 55 per night, every night, uninterrupted 07-10 … 07-16.
3. Bonus: if the entries= in that first success line reads 55, it closes the 55 = the entire row count of transaction_onchain reading in this issue.
4. For the silent transaction_lightning half there is no log signal — it needs a DB query. Duplicate groups (GROUP BY type, transaction HAVING COUNT(*) > 1) must stay constant after the fix, not drop: the backlog only goes away with the cleanup follow-up.

Falsifiable: the probe invoices carry memo: 'monitoring healthcheck' — counting those in transaction_lightning should account for ~180/day of the window.

[TaprootFreak]

Verified in production — first nightly run with the fix (2026-07-17)

The fix reached production at ~20:00 UTC on 07-16. The first nightly processSyncOnchainTransactions (02:00 UTC) since the fix flipped all three verification signals exactly as expected:

Signal	Baseline (every night 07-10 … 07-16)	07-17 (with fix)
syncOnchainTransactions: runtime= success line	none since November 2025	present — runtime=0.139 sec., entries=55
UQ_8a233973 dup-key errors	exactly 55/night	0
Error during processSyncOnchainTransactions	1/night	0

entries=55 in that first success line confirms the 55 = the entire row count of transaction_onchain reading.

Note on the entries= correction above: the 03:00 UTC lightning sync ran the same night with entries=1645 — essentially unchanged from the pre-fix 1647, no drop. That is the expected behaviour and the final confirmation that entries= is source-driven and unaffected by the fix.

Still open (the Follow-up: data cleanup task): the transaction_lightning duplicate backlog accumulated since November is untouched — the fix stops new duplicates but removes none. Its duplicate-group count should now stay constant instead of growing; the dedupe + FK-remap + UNIQUE (type, transaction) remain to be done.