Numbering: Invoice and Order Numbers, Gapless Sequences and Yearly Resets

Version note: The C# code in this post was tested with .NET 10 and EF Core 10, and the SQL examples with SQL Server 2025.

Guess First: Where Does a Rolled-Back Row's Number Go?

In the first version, the table's identity column was used as the invoice number. One invoice is inserted, a second one is inserted inside a transaction that's rolled back because of a signing error, and then a third one is inserted:

CREATE TABLE dbo.OrdersDemo (Id int IDENTITY(1,1) PRIMARY KEY, Note nvarchar(50) NOT NULL);

INSERT dbo.OrdersDemo (Note) VALUES (N'first');
BEGIN TRANSACTION;
    INSERT dbo.OrdersDemo (Note) VALUES (N'rolled back');
ROLLBACK;
INSERT dbo.OrdersDemo (Note) VALUES (N'third');
SELECT Id, Note FROM dbo.OrdersDemo;

The table will have two rows. Is the third row's Id 2 or 3? If everything the rolled-back transaction did was undone, 2 sounds reasonable. And consider this too: would the answer change if we asked the same question about a SEQUENCE?

Context and Problem

The HR Portal's billing module issues a subscription invoice to each of its 120 tenants every month, and the purchasing module generates order numbers within each tenant. An invoice number isn't a technical id; it's part of a legal document. According to the Turkish Revenue Administration's (GİB) e-Document announcement of 31.12.2020, e-Document applications use a 16-character "Document Number" instead of a series and sequence number: a 3-character unit code, a 4-digit year code and a 9-digit consecutive number. The e-Archive Technical Guide dated November 2026 (version 1.20) gives an invoice number example in the same format: CDE2013000000001.

Three requirements follow from this format. The number starts at 1 per series (unit code) and year, which means it resets every year. The sequence number is consecutive; in an audit, a series that jumps from 141 to 143 needs an explanation. And the same number can't be given to two documents; even a cancelled invoice keeps carrying its number. The system adds its own requirement: at month end, hundreds of invoices are approved in the same minute, and numbering must handle that.

Symptoms

You know numbering is set up wrong when accounting asks "why is there 1045 after 1043, where is 1044?" and the answer is "there was an error and it was rolled back". After a server restart the numbers suddenly jump by 1000. The first invoice of the year comes out as number 18,422 instead of 1. Two invoices were printed with the same number, one found during a load test and one at month end. Because drafts get a number, deleted drafts leave holes in the series. An invoice dated the last day of December gets a number from the new year's series when it's approved on January 2.

The Naive Solution and Why It Breaks

The answer to the opening question:

Id Note
-- ----
1 first
3 third

The identity value is consumed outside the transaction; the rollback undoes the row but not the counter. SQL Server can also skip larger ranges when the server restarts, because of the identity cache. Identity's job is to identify a row uniquely, not to be consecutive.

The second naive attempt is MAX + 1: read the largest number in the table and write one more. It works flawlessly with a single user. To simulate month end we sent 50 concurrent requests; the column has a unique constraint:

await db.Database.ExecuteSqlRawAsync(
    "DECLARE @next int = (SELECT ISNULL(MAX(Seq), 0) + 1 FROM NaiveInvoices); INSERT NaiveInvoices (Seq) VALUES (@next);");

SqlException 2627: Violation of UNIQUE KEY constraint 'UQ_NaiveInvoices_Seq'. Cannot insert duplicate key in object 'dbo.NaiveInvoices'. The duplicate key value is (4).
MAX+1: 12 inserted, 38 duplicate key errors

38 of the 50 requests read the same MAX and collided. Without the unique constraint, 38 invoices would have been printed with duplicate numbers. Under the default READ COMMITTED isolation, the window between the read and the write isn't protected.

Options

A) An Identity Column

Unique, fast, free. Not consecutive, can't be reset per year, and knows nothing about series. Not suitable as an invoice number; keep it as the technical primary key.

B) SEQUENCE

A counter independent of tables; you get values with NEXT VALUE FOR, and you can create a separate sequence per series and year or reset it with ALTER SEQUENCE ... RESTART. Very fast, because it holds no locks and caches values in memory. But Microsoft's CREATE SEQUENCE documentation says it plainly: sequence numbers are generated outside the scope of the current transaction and are consumed whether the transaction commits or rolls back; with CACHE, numbers in the cache can be lost on an unexpected shutdown. The same rollback test:

CREATE SEQUENCE dbo.InvoiceSeqDemo AS int START WITH 1 INCREMENT BY 1 CACHE 50;

DECLARE @a int = NEXT VALUE FOR dbo.InvoiceSeqDemo;
BEGIN TRANSACTION;
    DECLARE @b int = NEXT VALUE FOR dbo.InvoiceSeqDemo;
ROLLBACK;
DECLARE @c int = NEXT VALUE FOR dbo.InvoiceSeqDemo;
SELECT @a AS First, @b AS RolledBack, @c AS Next;

First RolledBack Next
----- ---------- ----
1 2 3

If gaps are acceptable (order numbers, request numbers, support tickets), a sequence is the right choice.

C) Gapless Numbers with a Counter Table

The last issued number is kept in a table keyed by (Series, Year), and the number is taken with an UPDATE inside the transaction that approves the invoice. Because the row lock is held until commit, two transactions can't get the same number; if the transaction rolls back, the counter rolls back too. No gaps. The cost is concurrency: transactions taking a number for the same series and year line up.

D) When You Assign the Number

This isn't a storage option but a timing decision, and it's at least as important as the others. If you assign a number when a draft is created, every deleted draft leaves a hole in the series. The number should be assigned when the document becomes legal: inside the approval or signing transaction. A draft has its own technical id, and the invoice number stays NULL until approval.

For HR Portal invoices we choose C and D together; for purchase order numbers, B is enough.

Implementation: The Counter Table and the Approval Transaction

The invoice number is a nullable column; empty on a draft, filled on approval. EF Core's SQL Server provider adds the IS NOT NULL filter to a unique index on a nullable column by itself; we turned that convention off in the settings post, but here it's exactly what we want: unnumbered drafts don't block each other, and numbered documents are unique.

public class Invoice
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public DateOnly InvoiceDate { get; set; }
    public decimal Total { get; set; }
    public InvoiceStatus Status { get; set; }
    public string? Number { get; set; }
}

public class InvoiceNumberCounter
{
    public string Series { get; set; } = "";
    public short Year { get; set; }
    public int LastNumber { get; set; }
}

modelBuilder.Entity<Invoice>(b =>
{
    b.Property(i => i.Total).HasPrecision(19, 4);
    b.Property(i => i.Number).HasMaxLength(16).IsUnicode(false).IsFixedLength();
    b.HasIndex(i => i.Number).IsUnique();
});
modelBuilder.Entity<InvoiceNumberCounter>(b =>
{
    b.HasKey(c => new { c.Series, c.Year });
    b.Property(c => c.Series).HasMaxLength(3).IsUnicode(false).IsFixedLength();
});

CREATE UNIQUE INDEX [IX_Invoices_Number] ON [Invoices] ([Number]) WHERE [Number] IS NOT NULL;

The code that takes a number lives in a single class. For the first number of the year the counter row doesn't exist yet; so that two transactions don't both see "no row" and both try to INSERT, the check is done with UPDLOCK, HOLDLOCK. HOLDLOCK also locks the key range of the missing row:

public sealed class InvoiceNumberAllocator(HrPortalDbContext db)
{
    // Must run inside the caller's transaction; the row lock is held until commit
    public async Task<int> NextAsync(string series, short year, CancellationToken ct)
    {
        if (db.Database.CurrentTransaction is null)
            throw new InvalidOperationException("Invoice numbers must be allocated inside a transaction.");

        List<int> result = await db.Database.SqlQuery<int>($"""
            IF NOT EXISTS (SELECT 1 FROM dbo.InvoiceNumberCounters WITH (UPDLOCK, HOLDLOCK)
                           WHERE Series = {series} AND [Year] = {year})
                INSERT dbo.InvoiceNumberCounters (Series, [Year], LastNumber) VALUES ({series}, {year}, 0);

            UPDATE dbo.InvoiceNumberCounters
            SET LastNumber = LastNumber + 1
            OUTPUT inserted.LastNumber AS Value
            WHERE Series = {series} AND [Year] = {year};
            """).ToListAsync(ct);
        return result.Single();
    }
}

The transaction check isn't decoration. Taking the number outside the transaction and then saving the invoice in a separate transaction brings back the sequence's gap problem: the number is taken, the save fails, the number is lost. That's why the allocator refuses to run without a transaction:

Invoice numbers must be allocated inside a transaction.

Formatting is a separate, testable function: a 3-character series, a 4-digit year, a 9-digit sequence.

public static partial class InvoiceNumber
{
    public static string Format(string series, int year, int sequence)
    {
        if (!SeriesPattern().IsMatch(series))
            throw new ArgumentException($"Series must be 3 characters [A-Z0-9]: '{series}'.", nameof(series));
        if (sequence is < 1 or > 999_999_999)
            throw new ArgumentOutOfRangeException(nameof(sequence));
        return $"{series}{year:D4}{sequence:D9}";
    }

    [GeneratedRegex("^[A-Z0-9]{3}$")]
    private static partial Regex SeriesPattern();
}

IKP2026000000042 (16 chars)

The approval service does everything in one transaction: it reads the invoice, checks its status, takes the number, saves and commits. The details of transactions are in the EF Core series post "Transactions in EF Core: SaveChanges, BeginTransaction and Savepoints".

public async Task<string> ApproveAsync(int invoiceId, Func<Task>? beforeCommit = null, Func<Task>? beforeNumber = null, CancellationToken ct = default)
{
    await using IDbContextTransaction tx = await db.Database.BeginTransactionAsync(ct);

    Invoice invoice = await db.Invoices.SingleAsync(i => i.Id == invoiceId, ct);
    if (invoice.Status != InvoiceStatus.Draft)
        throw new InvalidOperationException($"Invoice {invoiceId} is {invoice.Status}.");

    if (beforeNumber is not null) await beforeNumber();

    // The year comes from the invoice date, not from the approval time
    int seq = await numbers.NextAsync(Series, (short)invoice.InvoiceDate.Year, ct);
    invoice.Number = InvoiceNumber.Format(Series, invoice.InvoiceDate.Year, seq);
    invoice.Status = InvoiceStatus.Approved;
    await db.SaveChangesAsync(ct);

    if (beforeCommit is not null) await beforeCommit();
    await tx.CommitAsync(ct);
    return invoice.Number;
}

beforeNumber and beforeCommit won't be in real code; they let the experiment inject work or a failure into the transaction. Now let's simulate month end: 50 draft invoices are approved at the same time, and every tenth one fails at the signing step and is rolled back. We ran the same scenario with a sequence too:

counter: approved 45, min 1, max 45, distinct 45, gaps 0
first numbers: IKP2026000000001, IKP2026000000002, IKP2026000000003
sequence: committed 45, max 50, missing 4,14,24,34,44

The counter table gave the 45 approved invoices numbers 1 to 45 without gaps; the numbers taken by the five rolled-back transactions were rolled back together with the counter and handed to the next transaction. The sequence lost five numbers.

Implementation: Throughput, Yearly Reset and Cancellation

The price of gapless numbers is lock duration. Because the counter row's lock is held until commit, any work done inside the transaction after taking the number makes the whole queue wait. Assuming a 20-millisecond task during approval (like generating the e-invoice XML), we measured two orderings:

number first, 20 ms work inside lock  : 50 approvals in 1244 ms
20 ms work first, number last         : 50 approvals in 212 ms

Same work, same transaction, only the order differs: when you take the number last, the lock lasts milliseconds and the 50 approvals finish in a sixth of the time. The rule: take the number right before commit, and don't make network calls, generate files or wait for user interaction after taking it. If the number has to go into the XML, prepare the XML without the number and insert the number last. Because there's a separate counter row per series and year, approvals in different series don't wait for each other; if you need to go beyond dozens of approvals per second in a single series, increasing the number of series (for example a unit code per branch) is a real scaling tool.

The year comes from the invoice date, not the approval time. The same GİB announcement explains this with an example: an e-Document issued on January 3 but dated December 31 is numbered with the previous year's year code. In our test, two drafts dated the last day of December and the second day of January were approved:

dated 2026-12-31 -> IKP2026000000146
dated 2027-01-02 -> IKP2027000000001

The 2026 series continued from 146 with the counter left over from the earlier tests, and the 2027 series started at 1. Yearly reset needs no cron job, no RESTART and no New Year's Eve shift; the first approval of the new year creates the counter row itself. Which time zone determines the date is a separate question: an invoice issued in Istanbul at 23:30 on December 31 is still December 31 in UTC, but one issued at 01:30 on January 1 is 22:30 on December 31 in UTC. Store the invoice date as a DateOnly in the company's local calendar; the series post "Time and Time Zones: UTC Instants, Local Business Dates, DateOnly and TimeProvider" covers this distinction in detail.

A cancelled invoice doesn't lose its number. The e-Archive Technical Guide shows that cancelled invoices are written into the report in the faturaIptal element together with their numbers; so that number belongs to a cancelled document in GİB's records. The unique index stops code that tries to give the number to a new document:

Cannot insert duplicate key row in object 'dbo.Invoices' with unique index 'IX_Invoices_Number'. The duplicate key value is (IKP2026000000001).

Cancellation is a status change (Status = Cancelled), not a deletion. Any flow that frees the number through a hard delete or a soft delete is an audit finding.

If you use PostgreSQL, the same counter can be written as a single statement; INSERT ... ON CONFLICT ... DO UPDATE creates the row if it doesn't exist, increments it if it does, and RETURNING returns the new value. The row lock is still held until the end of the transaction:

INSERT INTO invoice_number_counters (series, year, last_number)
VALUES (@series, @year, 1)
ON CONFLICT (series, year)
DO UPDATE SET last_number = invoice_number_counters.last_number + 1
RETURNING last_number;

A final warning: if you send e-invoices through a private integrator, make clear who assigns the number. If the integrator generates numbers, your counter should only be an internal reference; if two systems generate numbers for the same series, collisions are inevitable.

Trade-off Table

When Not to Use It

Use gapless numbers only for documents that legally or contractually need to be consecutive: invoices, delivery notes, receipts. For numbers shown to users that don't need to be consecutive, such as order numbers, support tickets and request numbers, a sequence is faster and simpler; you don't have to explain a gap. Don't use the number as a primary key or foreign key; relationships between tables should go through the technical id, with the number as just a property. In a system where hundreds of documents per second take numbers in a single series, a gapless counter becomes a bottleneck; there you need to split the series or assign the number in a separate step where the document is finalized. Don't generate the number on the client or in a cache; the single authority is the counter in the database.

Checklist

• The invoice number isn't an identity or MAX + 1; a sequence for numbers that needn't be consecutive, a counter table for documents that must be.
• The number is assigned inside the approval transaction, not on the draft; the allocator won't run without a transaction.
• The number is taken right before commit; no slow work happens after taking it.
• The counter key is (Series, Year); the year comes from the invoice date in the company's local calendar.
• The number format lives in one function: a 3-character series, a 4-digit year, a 9-digit sequence; 16 characters.
• The number column is unique; a cancelled document keeps its number, and numbers aren't reused.
• There are tests for 50 concurrent approvals, a scenario with rollbacks, and the year boundary.
• If a private integrator is used, it's clear that a single system generates the number.

Related Patterns and Sources

This post is directly connected to the series post "When the Status Column Is Updated from Everywhere: Transition Tables, State Machines and Status History": the number is a side effect of the Draft → Approved transition and should be assigned only in that transition. The split between a technical id and a number shown to users, from "Exposing Ids: int Identity, GUID v7 and the Public Id Split", applies here as well. For transaction and locking mechanics see the EF Core series posts "Transactions in EF Core: SaveChanges, BeginTransaction and Savepoints" and "Concurrency in EF Core: Tokens, rowversion and DbUpdateConcurrencyException", and for how concurrency bugs affect business logic, the Web Security series post "OWASP A06: Insecure Design — Business Logic Flaws, Race Conditions and Rate Limiting". Sources: the Turkish Revenue Administration's (GİB) announcement of 31.12.2020 to taxpayers using e-Document applications through the GİB Portal (the 16-character document number, the year code determined by the document date); the GİB e-Archive Technical Guide version 1.20 (November 2026; the faturaNo example, unit code rules, faturaIptal); on Microsoft Learn, the "CREATE SEQUENCE" (cache and consumption outside the transaction), "Table hints" (UPDLOCK, HOLDLOCK) and "OUTPUT clause" docs; INSERT ... ON CONFLICT in the PostgreSQL docs. For legal details (cancellation periods, unit code assignment), verify with your accountant and your integrator's documentation.

A Small Experiment

★☆☆ Write tests for InvoiceNumber.Format: an invalid series ("ik1", "IKPX"), a sequence of zero and of 1,000,000,000 should fail; the result for ("IKP", 2026, 1) should be IKP2026000000001.

★★☆ Write numbering for purchase orders that uses a sequence per tenant and per year (PO-2026-000123). Write the code that creates the sequences on first need, and show with a test that a rollback leaves a gap.

★★★ Write a version that keeps the counter keyed only by Series instead of (Series, Year) and handles the year boundary with a CASE inside the UPDATE. Set up a test for concurrent approvals between 23:59 on December 31 and 00:00 on January 1, and note which design leaves less room for bugs.

The invoice now carries the right number. But is the amount on it right? In the next post we'll talk about money: decimal, a Money value object, the 1-cent difference created by rounding VAT per line versus on the total, and the cent that disappears when splitting 100 TL into three installments.