Number invoices in one unbroken sequence

How to do it

Article 226 of the EU VAT Directive requires a sequential number that identifies each invoice. Several countries also forbid gaps. A database sequence skips numbers when a transaction rolls back, so use a counter row updated inside the transaction. Separate series, such as one per year or per company, are allowed with a prefix. Never delete an issued invoice.

Example

UPDATE invoice_series SET last_number = last_number + 1
WHERE series = '2026-EB'
RETURNING last_number;

async function issueInvoice(db, draft) {
  return db.tx(async t => {
    const { last_number: number } = await t.one('UPDATE invoice_series SET last_number = last_number + 1 WHERE series = $1 RETURNING last_number', [draft.series])
    return t.one('INSERT INTO invoices (series, number, order_id, issued_at) VALUES ($1, $2, $3, now()) RETURNING *', [draft.series, number, draft.orderId])
  })
}

def issue_invoice(connection, draft):
    with connection.transaction():
        (number,) = connection.execute(
            "UPDATE invoice_series SET last_number = last_number + 1 WHERE series = %s RETURNING last_number",
            (draft.series,),
        ).fetchone()
        return connection.execute(
            "INSERT INTO invoices (series, number, order_id, issued_at) VALUES (%s, %s, %s, now()) RETURNING *",
            (draft.series, number, draft.order_id),
        ).fetchone()

How to check

A query over each series finds no gaps and no duplicates. A test that issues 50 invoices at the same moment gets 50 different numbers in a row.