v13.2 breaking change

Before upgrading, check whether your database already contains duplicate reference values:

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)

If this query returns no rows, you can proceed to upgrade.

This query finds duplicate references, keeps the oldest row in each group unchanged, and renames every later row by appending _dup_ plus its transaction_id.

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)

The earliest ref_123 row stays as-is. The two later rows get unique references so the upgrade can proceed. Re-run the audit query from step 1 until it returns no rows.

Deploy Blnk v0.13.2 or a later release. Migrations run automatically on startup and apply 1770611011.sql.

Confirm the unique index exists:

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

Optionally, post a transaction with a reference that already exists in your ledger. Blnk should reject the duplicate. The index is present and duplicate references are rejected.