Concurrency-safe invoice numbering that avoids duplicates and gaps

What goes wrong when two people create records at once

Picture a busy office at 4:55 PM. Two people finish an invoice and hit Save within a second of each other. Both screens briefly show “Invoice #1042”. One record wins, the other fails, or worse, both get stored with the same number. That is the most common real-world symptom: duplicate numbers that appear only under load.

Tickets behave the same way. Two agents create a new ticket for the same customer at the same time, and your system tries to “pick the next number” by looking at the latest record. If both requests read the same “latest” value before either one writes, they can both choose the same next number.

The second symptom is more subtle: skipped numbers. You might see #1042, then #1044, with #1043 missing. This often happens after an error or retry. One request reserves a number, then the save fails because of a validation error, a timeout, or a user closing the tab. Or a background job retries after a network hiccup and grabs a new number even though the first attempt already consumed one.

For invoices, this matters because numbering is part of your audit trail. Accountants expect each invoice to be uniquely identified, and customers may reference invoice numbers in payments or support emails. For tickets, the number is the handle everyone uses in conversations, reports, and exports. Duplicates create confusion. Missing numbers can raise questions during reviews, even if nothing dishonest happened.

Here’s the key expectation to set early: not every numbering method can be both concurrency-safe and gapless. Concurrency-safe invoice numbering (no duplicates, even with many users) is achievable and should be non-negotiable. Gapless numbering is possible too, but it requires extra rules and often changes how you handle drafts, failures, and cancellations.

A good way to frame the problem is to ask what you need your numbers to guarantee:

• Must never repeat (unique, always)
• Should be mostly increasing (nice to have)
• Must never skip (only if you design for it)

Once you pick the rule, the technical solution becomes much easier to choose.

Why duplicates and gaps happen

Most apps follow a simple pattern: a user clicks Save, the app asks for the next invoice or ticket number, then it inserts the new record with that number. It feels safe because it works perfectly when only one person is doing it.

The trouble starts when two saves happen at almost the same time. Both requests can reach the “get next number” step before either one finishes the insert. If both reads see the same “next” value, they both try to write the same number. That is a race condition: the result depends on timing, not logic.

A typical timeline looks like this:

• Request A reads next number: 1042
• Request B reads next number: 1042
• Request A inserts invoice 1042
• Request B inserts invoice 1042 (or fails if a unique rule blocks it)

Duplicates happen when nothing in the database stops the second insert. If you only check “is this number taken?” in the app code, you can still lose the race between the check and the insert.

Gaps are a different problem. They happen when your system “reserves” a number, but the record never becomes a real, committed invoice or ticket. Common causes are failed payments, validation errors found late, timeouts, or a user closing the tab after a number is assigned. Even if the insert fails and nothing is saved, the number may already be consumed.

Hidden concurrency makes this worse because it is rarely just “two humans clicking Save.” You might also have:

• API clients creating records in parallel
• Imports that run in batches
• Background jobs generating invoices overnight
• Retries from mobile apps with spotty connections

So the root causes are: (1) timing conflicts when multiple requests read the same counter value, and (2) numbers being allocated before you are sure the transaction will succeed. Any plan for concurrency-safe invoice numbering has to decide which outcome you can tolerate: no duplicates, no gaps, or both, and under exactly which events (drafts, retries, cancellations).

Decide your numbering rule before you pick a solution

Before you design concurrency-safe invoice numbering, write down what the number must mean in your business. The most common mistake is choosing a technical method first, then discovering the accounting or legal rules expect something different.

Start by separating two goals that often get mixed up:

• Unique: no two invoices or tickets ever share the same number.
• Gapless: numbers are unique and also strictly consecutive (no missing numbers).

Many real systems aim for unique-only and accept gaps. Gaps can happen for normal reasons: a user opens a draft and abandons it, a payment fails after the number is reserved, or a record is created and then voided. For helpdesk tickets, gaps usually do not matter at all. Even for invoices, many teams accept gaps if they can explain them with an audit trail (voided, canceled, test, etc.). Gapless numbering is possible, but it forces extra rules and often adds friction.

Next, decide the scope of the counter. Small wording differences change the design a lot:

• One global sequence for everything, or separate sequences per company/tenant?
• Reset every year (2026-000123) or never reset?
• Different series for invoices vs credit notes vs tickets?
• Do you need a human-friendly format (prefixes, separators), or just an internal number?

A concrete example: a SaaS product with multiple client companies might require invoice numbers that are unique per company and reset per calendar year, while tickets are unique globally and never reset. Those are two different counters with different rules, even if the UI looks similar.

If you truly need gapless, be explicit about what events are allowed after a number is assigned. For example, can an invoice be deleted, or only canceled? Can users save drafts without a number and assign the number only on final approval? These choices often matter more than the database technique.

Write the rule down in one short spec before building:

• What record types use the sequence?
• What makes a number “used” (draft, sent, paid)?
• What is the scope (global, per company, per year, per series)?
• How do you handle voids and corrections?

In AppMaster, this kind of rule belongs next to your data model and business process flow, so the team implements the same behavior everywhere (API, web UI, and mobile) without surprises.

Common approaches and what each one guarantees

When people talk about “invoice numbering,” they often mix two different goals: (1) never generate the same number twice, and (2) never have gaps. Most systems can easily guarantee the first. The second is much harder, because gaps can appear any time a transaction fails, a draft is abandoned, or a record is voided.

Approach 1: Database sequence (fast uniqueness)

A PostgreSQL sequence is the simplest way to get unique, increasing numbers under load. It scales well because the database is built to hand out sequence values quickly, even with many users creating records at once.

What you get: uniqueness and ordering (mostly increasing). What you do not get: gapless numbers. If an insert fails after a number is assigned, that number is “burned,” and you’ll see a gap.

Approach 2: Unique constraint plus retry (let the database decide)

Here you generate a candidate number (from your app logic), save it, and rely on a UNIQUE constraint to reject duplicates. If you hit a conflict, you retry with a new number.

This can work, but it tends to become noisy under high concurrency. You can end up with more retries, more failed transactions, and harder-to-debug spikes. It also does not guarantee gapless numbering unless you combine it with strict reservation rules, which adds complexity.

Approach 3: Counter row with locking (aim for gapless)

If you truly need gapless invoice numbers, the usual pattern is a dedicated counter table (one row per numbering scope, like per year or per company). You lock that row in a transaction, increment it, and use the new value.

This is the closest you get to gapless in normal database design, but it has a cost: it creates a single “hot spot” that all writers must wait on. It also raises the stakes for operational mistakes (long transactions, timeouts, and deadlocks).

Approach 4: Separate reservation service (only for special cases)

A standalone “numbering service” can centralize rules across multiple apps or databases. It’s usually only worth it when you have several systems issuing numbers and you cannot consolidate writes.

The tradeoff is operational risk: you’ve added another service that must be correct, highly available, and consistent.

Here’s a practical way to think about guarantees for concurrency-safe invoice numbering:

• Sequence: unique, fast, accepts gaps
• Unique + retry: unique, simple at low load, can thrash at high load
• Locked counter row: can be gapless, slower under heavy concurrency
• Separate service: flexible across systems, highest complexity and failure modes

If you’re building this in a no-code tool like AppMaster, the same choices still apply: the database is where correctness lives. App logic can help with retries and clear error messages, but the final guarantee should come from constraints and transactions.

Step by step: prevent duplicates with sequences and unique constraints

If your main goal is to prevent duplicates (not to guarantee no gaps), the simplest reliable pattern is: let the database generate an internal ID, and enforce uniqueness on the customer-facing number.

Start by separating the two concepts. Use a database-generated value (identity/sequence) as the primary key for joins, edits, and exports. Keep the invoice_no or ticket_no as a separate column that is shown to people.

A practical setup in PostgreSQL

Here’s a common PostgreSQL approach that keeps the “next number” logic inside the database, where concurrency is handled correctly.

-- Internal, never-shown primary key
create table invoices (
  id bigint generated always as identity primary key,
  invoice_no text not null,
  created_at timestamptz not null default now()
);

-- Business-facing uniqueness guarantee
create unique index invoices_invoice_no_uniq on invoices (invoice_no);

-- Sequence for the visible number
create sequence invoice_no_seq;

Now generate the display number at insert time (not by doing "select max(invoice_no) + 1"). One simple pattern is to format a sequence value inside the INSERT:

insert into invoices (invoice_no)
values (
  'INV-' || lpad(nextval('invoice_no_seq')::text, 8, '0')
)
returning id, invoice_no;

Even if 50 users click “Create invoice” at the same time, each insert gets a different sequence value, and the unique index blocks any accidental duplicates.

What to do when there is a collision

With a plain sequence, collisions are rare. They usually happen when you add extra rules like “reset per year,” “per tenant,” or user-editable numbers. That’s why the unique constraint is still important.

At the application level, handle a unique-violation error with a small retry loop. Keep it boring and bounded:

• Try the insert
• If you get a unique constraint error on invoice_no, try again
• Stop after a small number of attempts and show a clear error

This works well because retries are only triggered when something unusual happens, like two different code paths producing the same formatted number.

Keep the race window small

Don’t compute the number in the UI, and don’t “reserve” numbers by reading first and inserting later. Generate it as close to the database write as possible.

If you use AppMaster with PostgreSQL, you can model the id as an identity primary key in the Data Designer, add a unique constraint for invoice_no, and generate invoice_no during the create flow so it happens together with the insert. That way the database stays the source of truth, and concurrency issues stay contained where PostgreSQL is strongest.

Step by step: build a gapless counter with row locking

If you truly need gapless numbers (no missing invoice or ticket numbers), you can use a transactional counter table and row locking. The idea is simple: only one transaction at a time can take the next number for a given scope, so numbers are handed out in order.

First, decide your scope. Many teams need separate sequences per company, per year, or per series (like INV vs CRN). The counter table stores the last used number for each scope.

Here’s a practical pattern for concurrency-safe invoice numbering using PostgreSQL row locks:

1. Create a table, for example number_counters, with columns like company_id, year, series, last_number, and a unique key on (company_id, year, series).
2. Start a database transaction.
3. Lock the counter row for your scope using SELECT last_number FROM number_counters WHERE ... FOR UPDATE.
4. Compute next_number = last_number + 1, update the counter row to last_number = next_number.
5. Insert the invoice or ticket row using next_number, then commit.

The key is FOR UPDATE. Under load, you do not get duplicates. You also do not get gaps from “two users got the same number”, because the second transaction cannot read and increment the same counter row until the first one commits (or rolls back). Instead, the second user’s request waits briefly. That wait is the price of being gapless.

Initializing a new scope

You also need a plan for the first time a scope appears (new company, new year, new series). Two common options:

• Pre-create counter rows ahead of time (for example, create next year’s rows in December).
• Create on demand: try to insert the counter row with last_number = 0, and if it already exists, fall back to the normal lock-and-increment flow.

If you build this in a no-code tool like AppMaster, keep the whole “lock, increment, insert” sequence inside one transaction in your business logic, so it either all happens or none of it happens.

Edge cases: drafts, failed saves, cancellations, and edits

Most numbering bugs show up in the messy parts: drafts that never get posted, saves that fail, invoices that get voided, and records that get edited after someone has already seen the number. If you want concurrency-safe invoice numbering, you need a clear rule for when the number becomes “real.”

The biggest decision is timing. If you assign a number the moment someone clicks “New invoice,” you will get gaps from abandoned drafts. If you assign only when an invoice is finalized (posted, issued, sent, or whatever “final” means in your business), you can keep numbers tighter and easier to explain.

Failed saves and rollbacks are where expectations often clash with database behavior. With a typical sequence, once a number is taken it is taken, even if the transaction later fails. That is normal and safe, but it can create gaps. If your policy requires gapless numbers, the number must be assigned only at the final step and only if the transaction commits. That usually means locking a single counter row, writing the final number, and committing as one unit. If any step fails, nothing gets assigned.

Cancellations and voids should almost never “reuse” a number. Keep the number and change the status. Auditors and customers expect that the history stays consistent, even when a document is corrected.

Edits are simpler: once a number is visible outside the system, treat it as permanent. Never renumber an invoice or ticket after it has been shared, exported, or printed. If you need a correction, create a new document and reference the old one (for example, a credit note or a replacement ticket), but do not rewrite history.

A practical rule set many teams adopt:

• Drafts have no final number (use an internal ID or “DRAFT”).
• Assign the number only on “Post/Issue,” inside the same transaction as the status change.
• Voids and cancellations keep the number, but get a clear status and reason.
• Printed/emailed numbers never change.
• Imports preserve original numbers and update the counter to the next safe value.

Migrations and imports deserve special care. If you move from another system, bring over the existing invoice numbers as-is, then set your counter to start after the maximum imported value. Also decide what to do with conflicting formats (like different prefixes per year). It is usually better to store the “display number” exactly as it was, and keep a separate internal primary key.

Example: a helpdesk creates tickets quickly, but many are drafts. Assign the ticket number only when the agent clicks “Send to customer.” That avoids wasting numbers on abandoned drafts, and it keeps the visible sequence aligned with real customer communication. In a no-code tool like AppMaster, the same idea applies: keep drafts as records without a public number, then generate the final number during the “submit” business process step that commits successfully.

Common mistakes that cause duplicates or surprise gaps

Most numbering problems come from one simple idea: treating a number like a display value instead of shared state. When several people save at once, the system needs one clear place to decide the next number, and one clear rule for what happens on failure.

A classic mistake is using SELECT MAX(number) + 1 in application code. It looks fine in single-user testing, but two requests can read the same MAX before either one commits. Both generate the same next value, and you get a duplicate. Even if you add a “check then retry,” you can still create extra load and weird spikes under peak traffic.

Another common source of duplicates is generating the number on the client side (browser or mobile) before saving. The client does not know what other users are doing, and it cannot safely reserve a number if the save fails. Client-generated numbers are fine for temporary labels like “Draft 12,” but not for official invoice or ticket IDs.

Gaps surprise teams who assume sequences are gapless. In PostgreSQL, sequences are designed for uniqueness, not perfect continuity. Numbers can be skipped when a transaction rolls back, when you prefetch IDs, or when the database restarts. That is normal behavior. If your real requirement is “no duplicates,” a sequence plus a unique constraint is usually the right answer. If your requirement is truly “gapless invoice numbers,” you need a different pattern (usually row locking) and you need to accept some trade-offs in throughput.

Locking can also backfire when it is too broad. A single global lock for all numbering forces every create action into a line, even if you could separate counters by company, location, or document type. That can slow the whole system and make users feel like saving is “randomly” stuck.

Here are the mistakes worth checking for when implementing concurrency-safe invoice numbering:

• Using MAX + 1 (or “find last number”) without a database-level unique constraint.
• Generating final numbers on the client, then trying to “fix conflicts later.”
• Expecting PostgreSQL sequences to be gapless, then treating gaps as errors.
• Locking one shared counter for everything, instead of partitioning counters where it makes sense.
• Only testing with one user, so race conditions never show up until launch.

Practical test tip: run a simple concurrency test that creates 100 to 1,000 records in parallel and then checks for duplicates and unexpected gaps. If you build in a no-code tool like AppMaster, the same rule applies: make sure the final number is assigned inside a single server-side transaction, not in the UI flow.

Quick checks before you ship

Before you roll out invoice or ticket numbering, do a quick pass on the parts that usually fail under real traffic. The goal is simple: every record gets exactly one business number, and your rules stay true even when 50 people click "Create" at once.

Here’s a practical pre-ship checklist for concurrency-safe invoice numbering:

• Confirm the business number field has a unique constraint in the database (not just a UI check). This is your last line of defense if two requests collide.
• Make sure the number is assigned inside the same database transaction that saves the record. If number assignment and save are split across requests, you will eventually see duplicates.
• If you require gapless numbers, only assign the number when the record is finalized (for example, when an invoice is issued, not when a draft is created). Drafts, abandoned forms, and failed payments are the most common source of gaps.
• Add a retry strategy for rare conflicts. Even with row locking or sequences, you can hit a serialization error, a deadlock, or a unique violation in edge timing cases. A simple retry with a short backoff is often enough.
• Stress test with 20 to 100 simultaneous creates across all entry points: UI, public API, and bulk imports. Test realistic mixes like bursts, slow networks, and double submits.

A quick way to validate your setup is to simulate a busy helpdesk moment: two agents open the "New ticket" form, one submits from the web app while an import job inserts tickets from an email inbox at the same time. After the run, check that all numbers are unique, in the right format, and that failures do not leave half-saved records.

If you build the workflow in AppMaster, the same principles apply: keep number assignment in the database transaction, rely on PostgreSQL constraints, and test both UI actions and API endpoints that create the same entity. This is where many teams feel safe in manual testing but get surprised the first day real users pile in.

Example: busy helpdesk tickets and what to do next

Picture a support desk where agents create tickets all day in the web app, while an integration also creates tickets from a chat tool and email. Everyone expects ticket numbers like T-2026-000123, and they expect each number to point to exactly one ticket.

A naive approach is: read “last ticket number”, add 1, save the new ticket. Under load, two requests can read the same “last number” before either saves. Both calculate the same next number, and you get duplicates. If you try to “fix” it by retrying after a failure, you often create gaps without meaning to.

The database can stop duplicates even if your app code is naive. Add a unique constraint on the ticket_number column. Then, when two requests attempt the same number, one insert fails and you can retry cleanly. This is the core of concurrency-safe invoice numbering too: let the database enforce uniqueness, not your UI.

Gapless numbering changes the workflow. If you require no gaps, you usually cannot assign the final number when the ticket is first created (draft). Instead, create the ticket with a status like Draft and without a final ticket_number. Assign the number only when the ticket is finalized, so failed saves and abandoned drafts do not “burn” numbers.

A simple table design looks like this:

• tickets: id, created_at, status (Draft, Open, Closed), ticket_number (nullable), finalized_at
• ticket_counters: key (for example "tickets_2026"), next_number

In AppMaster, you can model this in the Data Designer with PostgreSQL types, and then build the logic in the Business Process Editor:

• Create Ticket: insert ticket with status=Draft and no ticket_number
• Finalize Ticket: start a transaction, lock the counter row, set ticket_number, increment next_number, commit
• Test: run two “Finalize” actions at the same time and confirm you never get duplicates

What to do next: start with your rule (unique only vs truly gapless). If you can accept gaps, a database sequence plus a unique constraint is usually enough and keeps the flow simple. If you must be gapless, move numbering to the finalization step and treat “draft” as a first-class state. Then load-test with multiple agents clicking at once and with the API integration firing bursts, so you see the behavior before real users do.