v13.2 breaking change

​

Breaking changes summary

• From: Non-unique index on reference; uniqueness enforced only in application logic (with gaps on some queued and bulk paths).
• To: Unique index idx_transactions_reference_unique on blnk.transactions(reference).
• Impact: The database migration fails at startup if duplicate references already exist. After upgrade, duplicate references are rejected at the database layer, not only by application checks.

​

What changed

1. Unique reference constraint: Every transaction reference must be unique in the database. If two rows share the same reference, the upgrade fails until you fix them.
2. Queue recovery index: The same release also improves recovery of transactions stuck in QUEUED. This does not change how you post transactions. See Queue recovery.

​

Migration steps

SELECT reference, COUNT(*) AS duplicate_count
FROM blnk.transactions
WHERE reference IS NOT NULL AND reference <> ''
GROUP BY reference
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC, reference;

 reference | duplicate_count
-----------+---------------
 ref_123   |               3
 ref_abc   |               2
(2 rows)

UPDATE blnk.transactions t
SET reference = t.reference || '_dup_' || t.transaction_id
FROM (
  SELECT transaction_id,
         ROW_NUMBER() OVER (
           PARTITION BY reference
           ORDER BY created_at ASC
         ) AS row_num
  FROM blnk.transactions
  WHERE reference IS NOT NULL AND reference <> ''
) ranked
WHERE t.transaction_id = ranked.transaction_id
  AND ranked.row_num > 1;

transaction_id     | reference
-------------------+---------------------------------
txn_f482a1b3-...   | ref_123
txn_c5d9e2a1-...   | ref_123_dup_txn_c5d9e2a1-...
txn_a8b3c1d2-...   | ref_123_dup_txn_a8b3c1d2-...
(3 rows)

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'blnk'
  AND tablename = 'transactions'
  AND indexname = 'idx_transactions_reference_unique';

​

Technical details

• Migration file: 1770611011.sql
• Index name: idx_transactions_reference_unique
• Legacy index: Before 0.13.2, reference had a non-unique index (idx_transactions_reference from the initial schema).

​

Need help?