Allocate invoice numbers at send, not at creation

If invoice numbers must be gapless — as Croatian law requires of the issued sequence — then numbering a draft is already a bug. A number given to something that might never be issued is a hole waiting to happen.

Learned the hard way in invoicing|the sikod invoicing app: 1/P1/1 was created, deleted, and left the two live drafts holding 2 and 3, with nothing able to ever be 1.

Renumbering on delete does not fix it

The obvious patch is to re-pack draft numbers when one is deleted. It fails on a case that takes ten seconds to hit:

Create two drafts, numbered 1 and 2.
Send the second one first. It is issued as 2 and its number is now immutable — the client has it.
Delete the first draft.

Nothing can ever be 1. The sequence starts at 2. Renumbering cannot touch an issued invoice, so the hole is permanent.

The model that works

Drafts hold no number at all. Every draft displays the number the next issued invoice will take — the same value for all of them, because whichever one is sent next is the one that gets it. The number is allocated in the transaction that marks it sent.

Consequences, all of them free because nothing is stored:

Deleting a draft changes nothing.
Sending one bumps every remaining draft automatically.
Drafts need no order, so a “reorder the queue” feature is meaningless.

A per-draft provisional number by queue position (1, 2, 3) is tempting and wrong: it promises a draft will be 2 when sending it first makes it 1.

`MAX(seq)+1` in a transaction is not enough

Under READ COMMITTED — Postgres’s default — two concurrent transactions both read the same MAX(seq). A unique index saves correctness but not the user: the loser gets a constraint violation instead of the next number.

Measured with 8 concurrent sends: 1 succeeded, 7 failed as duplicates.

Fix with a transaction-scoped advisory lock, released automatically on commit or rollback:

SELECT pg_advisory_xact_lock(<namespace>, <year>);
SELECT COALESCE(MAX(seq), 0) + 1 FROM invoices WHERE year = $1 AND ...;

With the lock, all 8 allocated 1..8 cleanly. Preferred over a DB trigger because the logic stays visible in the code rather than firing invisibly.

Order of operations when sending

The number and the payment reference only exist after allocation, so a PDF rendered first goes out with neither. Issue first, then render, then send — and compensate if the send fails, handing the number back so a failed send does not burn one.

That compensation is only safe while issuing is effectively serial. If another invoice could be issued in between, releasing the number leaves the hole it was meant to avoid.

The trap this creates

A draft that renders exactly like the real thing — real number, real reference, scannable barcode — is excellent for testing, and means a downloaded draft PDF is indistinguishable from an issued one. If one is ever sent by hand, the app still thinks that number is free and will hand it to the next invoice.