[Resolved] The implementation correctly matches v5 spec.

The v4 plan document is included for historical reference. This PR implements v5 (consolidated design) as documented in the v5 design spec.

v5 spec (lines 107-130) describes the consolidated txs table with embedded state columns (locked, conflicting, frozen, etc.), which matches the implementation in schema.go:84-103.

The plan document serves as a historical record of the v4 design iteration, which was superseded by v5 consolidated approach for better performance.

The v4 plan document is included for historical reference. This PR actually implements v5 (consolidated design), documented in docs/superpowers/specs/2026-04-10-turbo-utxo-store-v5-design.md. The implementation correctly matches the v5 spec.

[Resolved] 16 partitions matches v5 spec.

v5 spec (line 167) explicitly states: Partition count 16 instead of 64. Less routing overhead for current data volumes.

Code (schema.go:24): const numPartitions = 16

The implementation correctly matches the v5 design decision to use 16 partitions (not 64) for reduced routing overhead. The v4 plan reference to 64-way partitioning was superseded by the v5 optimization.

The 16 partitions (not 64) is correct for v5. The v4 plan with 64 partitions was found to have partition routing overhead. V5 intentionally reduces to 3 tables × 16 partitions for better performance.

[Resolved] Issue fixed in latest commit.

The code now uses synchronous_commit=on (the PostgreSQL default) for full durability, as shown in store.go:104-105. The implementation correctly balances performance (UNLOGGED tables, batching) with acceptable durability for financial data.

[Resolved] Schema now correctly implements 3-table v5 design.

v5 spec (lines 107-153) describes 3 UNLOGGED tables: txs, outputs, spends.
Code (schema.go:84-131) implements all 3 tables exactly as specified.

The implementation and documentation are now fully aligned.

[Major] Per-output arrays are compacted but read by absolute output index — misalignment when any output is nil

buildOutputArrays skips nil outputs with continue, so utxo_hashes/out_spendables/out_frozens/coinbase_spending_heights/spendable_ins are written positionally compacted. The true output index is captured in p.idx (line 84) but is never stored — the txs table has no output-index column, and the bulk path only uses idx for ORDER BY, not to gap-fill positions.

Every reader, however, subscripts these arrays by the absolute output index: spend.go uses t.utxo_hashes[$2::int + 1] (= vout+1) and dah_sweep.go uses out_spendables[s.prev_output_idx::int + 1]. The model requires array position i+1 == output index i. The moment any output is nil, every later output reads its UTXO hash / spendable / frozen / coinbase-maturity / spendable_in from the wrong slot → spend validates against the wrong output, and the DAH “fully spent” count is wrong (premature or never-pruning).

Both reference stores preserve the mapping: the sql store compacts identically but stores the real index in the outputs.idx column (sql.go:888) and reads by idx = vout; aerospike writes utxos[i] at the true index slot (aerospike/create.go:824-833), leaving nil slots nil. This store dropped that mapping. Fix: gap-fill the arrays to full len(tx.Outputs) (NULL/placeholder for nil/non-stored slots) so position == output index.

(Reachability depends on whether Create ever receives a tx with nil outputs — the nil-skip guard exists in all three stores, so the codebase clearly anticipates it.)

✅ Resolved by commit a5c7d3b4 (packed per-output columns). buildOutputArrays now writes each output at its true index — copy(p.utxoHashes[i*32:], …) and setPackedBit(p.spendableBits, i) (create.go:125,131) — leaving nil outputs as a zeroed 32-byte slot with the spendable bit clear, so array position == output index. All readers (spend.go, get.go, dah_sweep.go, delete.go) subscript by absolute index via substr(utxo_hashes, idx*32+1, 32) / get_bit(out_spendables, idx), consistent with the write. The misalignment-on-nil-output hazard is closed.

[Major] unmined_since off-by-one on reorg (UnsetMined) — missing +1

currentBlockHeight := int64(s.blockHeight.Load()) is bound as $3 and written into unmined_since (line 229-231) when a tx is fully reorged out. Both gold-standard stores stamp unmined_since with blockHeight.Load() + 1:

• sql: currentBlockHeight := s.blockHeight.Load() + 1 (sql.go:3276), comment: “mirrors the aerospike Lua which sets unmined_since = currentBlockHeight”
• aerospike: thisBlockHeight := s.blockHeight.Load() + 1 (set_mined.go:208)

The comment here (lines 227-228) claims this matches aerospike teranode.lua:637-644 and sql.go:3268-3308, but it diverges by one. A reorged-out tx is marked unmined one block too early, so the prunable-unmined iterator / QueryOldUnminedTransactions (which compare unmined_since <= cutoff) make it eligible for deletion one block sooner than the reference — premature pruning of still-live data after a reorg.

Fix: currentBlockHeight := int64(s.blockHeight.Load()) + 1.

Still unaddressed in the current code. `mined.go:189` binds `unmined_since` to `int32(s.blockHeight.Load())` with no `+1`. The new in-code comment (lines 226-232) claims parity with `sql.go:3268-3308` and aerospike `teranode.lua:637-644`, but both references add one: `sql.go:3276` uses `s.blockHeight.Load() + 1` and aerospike `set_mined.go:208` uses `s.blockHeight.Load() + 1`. A reorged-out tx is therefore stamped one block lower than the reference oracle. Fix: `int32(s.blockHeight.Load()) + 1`.

[Major] SetMinedMulti appends block membership non-idempotently — duplicates on re-processing

The UPDATE unconditionally does block_ids = COALESCE(block_ids, '{}') || $2::int[] (and the parallel block_heights / subtree_idxs) with no existence guard. Re-processing the same block for the same tx (crash-recovery replay, retry, or a duplicate call) appends duplicate (block_id, block_height, subtree_idx) triples. The end-of-function postcondition only checks block_id presence, so it cannot catch this.

Both references dedup:

• sql: INSERT INTO block_ids (...) ... ON CONFLICT DO NOTHING on the unique (transaction_id, block_id) (sql.go:3204-3209)
• aerospike: checks blockExists before appending (teranode.lua:611-629)

Consequences of duplicate entries: Get/BatchDecorate return duplicated BlockIDs/BlockHeights, and any block-membership-count or longest-chain logic over these arrays is corrupted until a removal cleans it. Note the companion unsetMinedMulti uses array_remove (removes all copies), so a single later removal would wipe every duplicate at once — but the duplicated state is still observable in between.

Fix: guard the append, e.g. WHERE hash = ANY($1) AND NOT (COALESCE(block_ids,'{}') @> $2::int[]), or unnest-and-rebuild to keep the triples unique.

[Major] Cascade DELETE does not re-check delete_at_height on the live tuple — the claimed reorg-race fix is not actually in force

The comment (lines 222-226) and the PR description state the cascade “re-checks delete_at_height at execution time, closing the window where a concurrent reorg Unspend revives a tx.” But the predicate lives only in the doomed CTE (line 230); the final DELETE FROM txs WHERE hash IN (SELECT hash FROM doomed) (line 234) re-checks only hash IN doomed.

Under READ COMMITTED (the pool default — no explicit isolation / FOR UPDATE / advisory lock), the doomed CTE is materialized once against the statement snapshot. If a concurrent Unspend / unsetMinedMulti sets delete_at_height = NULL and commits after that snapshot, PostgreSQL’s EvalPlanQual recheck on the DELETE re-applies only the DELETE’s own qual (hash IN doomed, still true) — not the CTE’s delete_at_height predicate — so the revived row is deleted while del_spends has already removed its spend markers. That is the exact data-loss race the comment claims to prevent.

The window is narrow and, in practice, further bounded by the prune retention (a reorg deep enough to revive a prune-eligible tx is near the safety horizon), so real-world reachability is low — but the code asserts a guarantee it does not provide.

Fix: repeat the predicate on the live tuple in the outer DELETE so EPQ re-checks it:
DELETE FROM <txs> WHERE hash IN (SELECT hash FROM doomed) AND delete_at_height IS NOT NULL AND delete_at_height <= $1.

Still stands after re-review of the current cascadeSQL (lines 227–234). The in-code comment ("applies the delete_at_height predicate in the SAME statement and snapshot as the DELETE, so a concurrent reorg Unspend that clears the stamp is honoured") conflates snapshot visibility with the EvalPlanQual (EPQ) recheck, and they are not the same thing:

• Under READ COMMITTED the whole statement runs on one snapshot S0. A reorg Unspend that sets delete_at_height = NULL and commits after S0 is invisible to that snapshot, so the doomed CTE still contains the revived hash.
• When DELETE FROM txs … WHERE hash IN (SELECT hash FROM doomed) reaches that concurrently-updated row, PostgreSQL triggers EPQ: it refetches the latest tuple and re-evaluates only the DELETE node's own qual against it. That qual is hash IN doomed — doomed is a materialized CteScan, the hash is unchanged, so it still matches and the row is deleted. delete_at_height is never re-checked on the live tuple because it lives inside the (already-materialized) CTE, not on the outer DELETE.

Net: a reorg Unspend racing the prune window can still delete a revived tx (and del_spends removes its spend records unconditionally too). To actually honour the predicate via EPQ, repeat it on the outer DELETE:

DELETE FROM txs_pNN
WHERE hash IN (SELECT hash FROM doomed)
  AND delete_at_height IS NOT NULL AND delete_at_height <= $1

and apply the equivalent guard to the del_spends cascade. Low reachability (narrow window), but consensus-critical (loss of a live UTXO row + its spend proofs), so flagging it as still-open [Major].

[Major] postgres:// scheme silently remapped from the SQL store to this new store

Before this PR, availableDatabases["postgres"] routed to the mature sql.Store (PostgreSQL backend). This PR moves the SQL store to "postgressql" and points "postgres" at the new, by-the-PR's-own-admission not-yet-integration-tested store.

This is a silent behavioral change for any existing config that uses a postgres:// UTXO-store URL — it switches backends with no error and no migration path. One such config already lives in the repo at settings.conf:1228 (utxostore.docker.ss.teranode1 = postgres://...). That docker config — and any operator config using postgres:// — now binds to the new store rather than the SQL store it was written against, and the two schemas are not interchangeable.

Suggest one of: keep postgres:// on the SQL store and give the new store its own distinct scheme (e.g. pgutxo://); or, if the remap is intended, update settings.conf:1228 and the utxostore_settings.go longdesc (which still documents postgres:// generically) and call the break out explicitly in the PR/changelog.

[Major] Pruner cascade does not re-validate delete_at_height against a concurrent reorg — the in-comment safety claim does not hold under PostgreSQL READ COMMITTED

The comment (lines 222–226) asserts that "the doomed CTE applies the delete_at_height predicate in the SAME statement and snapshot as the DELETE, so a concurrent reorg Unspend that clears the stamp is honoured: a revived tx is simply not selected."

That reasoning is incorrect. doomed is referenced twice (by del_spends and by the final DELETE FROM txs), so PostgreSQL materialises it once. The two DELETEs then qualify on hash IN (SELECT hash FROM doomed) — the delete_at_height predicate is not part of either DELETE’s own qualification, and doomed takes no row lock (FOR UPDATE).

Failure scenario (the exact reorg data-loss case the design must survive):

1. Pruner statement starts (snapshot S0); tx T is stamped (delete_at_height <= H) and lands in doomed.
2. A reorg Unspend/unsetMinedMulti commits after S0: it deletes T’s spend rows and sets T.delete_at_height = NULL — T is back on no chain and its outputs are live again.
3. The pruner reaches T. Because T was updated by a now-committed txn, PostgreSQL runs EvalPlanQual, re-checking the DELETE’s own qual (hash IN doomed) against the new row version — hash is unchanged, so it still matches. delete_at_height is never re-tested. T is deleted, destroying a tx whose outputs are now live. del_spends is keyed off the same stale doomed set, so it also deletes any fresh spend rows a re-spend created.

Suggested fix: re-assert the predicate on the parent delete so EPQ re-checks the live row, e.g.

DELETE FROM txs_pNN t
WHERE t.hash IN (SELECT hash FROM doomed)
  AND t.delete_at_height IS NOT NULL AND t.delete_at_height <= $1

and gate del_spends on the same re-checked set (ideally SELECT ... FOR UPDATE SKIP LOCKED in doomed) so a revived tx loses neither its row nor its spends.

[Major] ReAssignUTXO drops the spendable-after-reassign restriction for every output with vout ≥ 1

spendable_ins is NULL on create and is written only here. In PostgreSQL, assigning to a subscript of a NULL array does not zero-fill from index 1 — arr[N] = v on a NULL array yields an array with lower bound N and array_length(arr,1) = 1 (e.g. reassigning vout=5 produces [6:6]={si}).

The spend-validation CTE reads it positionally (spend.go:63 and :516):

CASE WHEN array_length(t.spendable_ins, 1) >= $2::int + 1 THEN t.spendable_ins[$2::int + 1] END AS spendable_in

For a reassigned output at vout ≥ 1 (with spendable_ins previously NULL — the normal case), array_length is 1, so the guard 1 >= vout+1 is false and spendable_in resolves to NULL. The block_height < spendable_in immaturity check is silently skipped, so the reassigned UTXO becomes immediately spendable instead of after ReAssignedUtxoSpendableAfterBlocks.

Only vout=0 happens to work ([1:1] satisfies 1 >= 1), which is exactly the index the single test (store_test.go:1330) exercises — and it never asserts the immaturity gate, so the bug is uncaught. The sql store enforces this via a per-row outputs.spendableIn column; aerospike via its reassign UDF.

Fix: build/extend spendable_ins to a contiguous 1-based array so position i+1 always maps to output i (e.g. COALESCE(spendable_ins, array_fill(NULL::int, ARRAY[out_count])) before the subscript assignment), matching the schema comment’s stated [i+1] contract.

[Major] Recomputing the tx-level frozen flag from out_frozens breaks the per-output freeze isolation that FreezeUTXOs maintains

FreezeUTXOs deliberately does not touch the tx-level frozen column (see its comment, lines 34–40) — it is the whole-tx freeze gate set only at create via WithFrozen, and the spend CTE blocks every output of the tx when it is true (NOT tx_frozen, spend.go:71/:523). Per-output freeze lives solely in the out_frozens bitmap.

But UnFreezeUTXOs (line 129) and ReAssignUTXO (line 174) recompute frozen = (out_frozens has any bit set). This is asymmetric with the freeze path and incorrect:

1. Create tx (not whole-tx frozen → frozen=false).
2. FreezeUTXOs output 0 and output 1 per-output (out_frozens bits 0,1 set, frozen stays false). Spending output 2 still works.
3. UnFreezeUTXOs output 0 → bit 0 cleared, bit 1 still set → recompute sets frozen = TRUE.
4. The spend CTE now reads tx_frozen=true and blocks all outputs of the tx — including output 2, which was never frozen.

So unfreezing one individually-frozen output silently freezes the entire transaction. Aerospike/sql operate purely per-output and never derive a whole-tx flag from individual outputs.

Fix: drop the frozen = (...) recompute from both UnFreezeUTXOs and ReAssignUTXO (line 174) — leave frozen as the create-time whole-tx gate, exactly as FreezeUTXOs does on the way in.

[Major] SQL store registered under "postgressql", but sql.New/InitSQLDB never learned that scheme — the SQL Postgres backend is now unreachable

Commit f5ffac449 moved the "postgres" registration to the new store and re-registered the SQL store under "postgressql". But the SQL store only recognizes the "postgres" scheme:

• util/sql.go:34 (InitSQLDB, first call in sql.New): switch storeURL.Scheme { case "postgres": ... case "sqlite","sqlitememory": ... } — no "postgressql" case → returns unknown scheme: postgressql.
• stores/utxo/sql/sql.go:169-181: same switch with default: → unknown database engine: postgressql.
• stores/utxo/sql/sql.go:189: engine: storeURL.Scheme would store "postgressql", so every s.engine == "postgres" fast-path (batched INSERT, pipelined SetLocked, decorate) is silently disabled even if init somehow succeeded.

Net: a postgressql://... URL fails at DB-init — the SQL-backed Postgres store is reachable through no working scheme. "postgressql" is referenced nowhere else in the repo, so this escape hatch is dead on arrival.

Fix: add "postgressql" cases to InitSQLDB and sql.New (normalizing engine to "postgres"), or rewrite the scheme to "postgres" before handing off to sql.New.

[Critical] Nil-pointer dereference in GetSpend — externally reachable panic

spend.UTXOHash is *chainhash.Hash and is legitimately nil for callers that locate a UTXO by (txid, vout). This line unconditionally dereferences it:

if !bytes.Equal(utxoHashBytes, spend.UTXOHash[:]) {

The SQL reference store guards this exact case at stores/utxo/sql/sql.go:3409:

if spend.UTXOHash != nil && !bytes.Equal(utxoHash, spend.UTXOHash[:]) {

The path is reachable from the public /api/v1/utxos HTTP endpoint: services/asset/httpimpl/GetUTXOs.go:199 constructs &utxo.Spend{... UTXOHash: nil}, and services/asset/repository/repository.go:922 calls repo.UtxoStore.GetSpend(ctx, spend) — with a comment at line 917 explicitly noting spend.UTXOHash may be nil. With the postgres store selected, this panics.

Fix: add the spend.UTXOHash != nil guard to match the SQL store.

[Major — please verify] DAH re-stamp race vs. concurrent Unspend (reorg safety)

This stamping UPDATE is a single statement, so spend_agg/state and the write share one READ COMMITTED snapshot — good. But the snapshot of spends is taken at statement start, while the target txs row is locked later. Consider:

1. Sweep statement starts; spend_agg sees all spendable outputs of hash H spent → new_dah = future height.
2. A reorg Unspend commits (separate txn): DELETE FROM spends ... then UPDATE txs SET delete_at_height = NULL (spend.go:686,708).
3. The sweep UPDATE reaches H. Under EPQ it re-fetches the latest txs row (now delete_at_height = NULL), but st.new_dah is fixed from the stale CTE. The only guard is t.delete_at_height IS DISTINCT FROM st.new_dah — NULL vs future height is distinct, so it re-stamps a DAH on a tx whose spend was just removed.

The IS DISTINCT FROM guard does not re-assert fully-spent-ness against live spends.

Mitigations that likely make this non-fatal in practice (please confirm each holds):

• The watermark rewind on reorg should re-cover H and a corrective sweep pass would recompute spent_count < spendable_count → new_dah = NULL, clearing the bad stamp.
• The retention buffer (new_dah = completion + 1 + retention) must exceed reorg-detection latency so the pruner cannot reach the bad DAH before the corrective sweep runs.

If both hold the window is self-healing; if either can be violated, a still-spendable UTXO can be pruned. Consider re-asserting eligibility in the UPDATE ... WHERE (re-check live spend rows) rather than relying solely on IS DISTINCT FROM.

[Minor] Blind array append produces duplicate block_ids that readers surface

The comment says duplicates "are harmless" because UnsetMined removes all matching entries — true for removal, but readers are not protected. On crash-recovery re-processing of the same block, block_ids / block_heights / subtree_idxs each grow by one duplicate element, and Get/BatchDecorate copy them verbatim into data.BlockIDs (get.go:135-148), so consumers see duplicate IDs (e.g. block id 7 twice). The Aerospike store de-duplicates on mine, so this is a cross-store divergence.

Consider a NOT (block_ids @> $2) guard (the conflicting-children insert at create.go:600 already uses this pattern) to keep the arrays duplicate-free.