Yearly invoice numbering sequence
Gap-free invoice numbers such as INV-2026-000042 using one locked counter row per year and prefix, safe under concurrent requests.
LaravelDatabaseinvoicesequencetransactionslocking
Code
<?php
namespace App\Billing;
use Illuminate\Support\Facades\DB;
final class DocumentNumber
{
/**
* Call inside the same transaction that saves the invoice. If that
* transaction rolls back, the counter rolls back too: no gaps.
*/
public static function next(string $prefix = 'INV', ?int $year = null): string
{
$year ??= (int) now()->format('Y');
return DB::transaction(function () use ($prefix, $year) {
// Make sure the row exists, then lock it so concurrent requests queue up.
DB::table('number_sequences')->insertOrIgnore(['prefix' => $prefix, 'year' => $year, 'last' => 0]);
$last = DB::table('number_sequences')
->where(['prefix' => $prefix, 'year' => $year])
->lockForUpdate()
->value('last');
$next = (int) $last + 1;
DB::table('number_sequences')
->where(['prefix' => $prefix, 'year' => $year])
->update(['last' => $next]);
return sprintf('%s-%d-%06d', $prefix, $year, $next);
});
}
}
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up(): void
{
Schema::create('number_sequences', function (Blueprint $t) {
$t->string('prefix', 12);
$t->unsignedSmallInteger('year');
$t->unsignedBigInteger('last')->default(0);
// A new year simply creates a new row, which restarts the count at 1.
$t->primary(['prefix', 'year']);
});
}
};
How it works
What it solves
Many countries expect invoice numbers to be sequential and free of gaps within a fiscal year. Using the auto-increment id leaks volume and never restarts, while max(number) + 1 produces duplicates when two invoices are created at the same moment.
How it works
A small number_sequences table has one row per prefix and year, with a composite primary key. next() inserts that row if it does not exist (insertOrIgnore is safe if two requests race to create it), then selects the counter with lockForUpdate(). The second concurrent request waits on that row lock until the first transaction commits, then reads the updated value. The counter is incremented and the formatted string is returned, for example INV-2026-000042.
Because the function opens a transaction, Laravel nests it as a savepoint when you call it inside the transaction that creates the invoice. If saving the invoice fails and the outer transaction rolls back, the counter increment rolls back as well, which is what keeps the sequence gap-free.
Different prefixes (INV, CN for credit notes) count independently, and a new year starts a new row at 1 automatically.
Adapting it
- Add a tenant or branch column to the primary key for per-company sequences.
- Change
%06dfor a different width. - Seed the row manually when migrating from an old system so numbering continues.
Pitfalls
- Never call it outside the invoice's transaction, or a failure leaves a gap.
lockForUpdate()needs InnoDB or PostgreSQL; SQLite ignores it, so test concurrency on the real engine.
How to use it
- Copy the migration and run
php artisan migrate. - Put
DocumentNumber.phpinapp/Billing/. - Use it inside your invoice transaction:
DB::transaction(fn () => Invoice::create(['number' => DocumentNumber::next('INV'), ...])); - For credit notes use another prefix:
DocumentNumber::next('CN'). - Add a unique index on
invoices.numberas a safety net.
Licence: DEV3 Snippet Licence. Free to use, change and ship in personal and commercial projects. Attribution is appreciated, not required. Please do not redistribute snippets as a collection.