Great question — NF525-style sequencing gets opinionated fast 😅
Let’s design something solid, atomic, and friendly to Symfony.

Goal recap

You want IDs like:
202510-00001, 202510-00002, ...
Format = AAAAMM-<incremented number with at least 5 digits>
Where AAAAMM = year+month at generation time.

You also want:

• A generic way to request "give me the next number"

• True atomicity (no duplicates even under concurrency)

• Works with PHP 8.2 / Symfony 6.4 / MariaDB 10.11+

We'll build exactly that.

High-level approach

We’ll create:

1. A DB table that tracks per-period counters (e.g. one row per 202510)

2. A small service (SequenceGenerator) you can inject anywhere

3. A Doctrine transaction that does SELECT ... FOR UPDATE and UPDATE, so we’re safe under race conditions

No UUIDs, no guessing in memory, no “max(value)+1” race conditions.

1. Database design

We'll store sequence state per "scope".
Why? Because in real life you'll probably need different sequences:

• invoices

• credit notes

• Z-report / daily closings

• etc.

Table proposal:

sql
Copier le code
CREATE TABLE sequences (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sequence_key     VARCHAR(64) NOT NULL, -- e.g. 'invoice', 'credit_note', etc
    period           CHAR(6) NOT NULL,      -- 'YYYYMM' e.g. '202510'
    current_value    BIGINT UNSIGNED NOT NULL,
    updated_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_sequence_period (sequence_key, period)
) ENGINE=InnoDB;

Why this works:

• One row per (sequence_key, period)

• We keep only the last used integer for that month

• InnoDB row-level locking lets us increment atomically

2. Service contract (what you’ll call from your app)

php
Copier le code
public interface SequencerInterface
{
    /**
     * Returns something like "202510-00042"
     */
    public function next(string $sequenceKey): string;
}

Usage in code:

php
Copier le code
$invoiceNumber = $sequencer->next('invoice');  // "202510-00042"

No controller/Doctrine mess anywhere else.
All sequencing logic lives in one place.

3. Implementation details (important bits)

a) We compute the "period"

Period = current year + month in server/app timezone, e.g.:

php
Copier le code
$period = (new \DateTimeImmutable('now', new \DateTimeZone('Europe/Paris')))
    ->format('Ym'); // "202510"

You may want to inject the timezone so it's explicit.

b) We atomically increment using a transaction

Algorithm inside next():

1. start transaction

2. try to SELECT * FROM sequences WHERE sequence_key = :key AND period = :period FOR UPDATE

3. if row exists:

   • $next = current_value + 1

   • UPDATE sequences SET current_value = :next WHERE id = :id

4. else (first number this month):

   • $next = 1

   • INSERT INTO sequences (...) VALUES (:key, :period, :next)

5. commit

6. format: sprintf('%s-%05d', $period, $next)

That FOR UPDATE is the magic for atomicity:
only one transaction at a time can lock that row, so you never hand out the same number twice — even under heavy concurrency.

MariaDB 10.11 with InnoDB fully supports this.

4. Full Symfony-style implementation

4.1 Doctrine Entity (Sequences)

php
Copier le code
<?php
// src/Entity/Sequence.php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\Table(name: 'sequences')]
#[ORM\UniqueConstraint(
    name: 'uniq_sequence_period',
    columns: ['sequence_key', 'period']
)]
class Sequence
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'bigint', options: ['unsigned' => true])]
    private ?string $id = null;

    #[ORM\Column(name: 'sequence_key', length: 64)]
    private string $sequenceKey;

    #[ORM\Column(length: 6)]
    private string $period; // 'YYYYMM'

    #[ORM\Column(name: 'current_value', type: 'bigint', options: ['unsigned' => true])]
    private string $currentValue = '0';

    #[ORM\Column(name: 'updated_at', type: 'datetime_immutable')]
    private \DateTimeImmutable $updatedAt;

    public function __construct(string $sequenceKey, string $period, int $currentValue)
    {
        $this->sequenceKey = $sequenceKey;
        $this->period = $period;
        $this->currentValue = (string)$currentValue;
        $this->updatedAt = new \DateTimeImmutable();
    }

    public function getId(): ?string { return $this->id; }

    public function getCurrentValue(): int { return (int)$this->currentValue; }

    public function increment(): int
    {
        $new = (int)$this->currentValue + 1;
        $this->currentValue = (string)$new;
        $this->updatedAt = new \DateTimeImmutable();
        return $new;
    }

    public function getSequenceKey(): string { return $this->sequenceKey; }
    public function getPeriod(): string { return $this->period; }
}

Notes:

• bigint is mapped as string in PHP by Doctrine. We cast to int as needed.

4.2 The Sequencer service

php
Copier le code
<?php
// src/Service/Sequencer.php

namespace App\Service;

use App\Entity\Sequence;
use Doctrine\DBAL\LockMode;
use Doctrine\ORM\EntityManagerInterface;

class Sequencer implements SequencerInterface
{
    public function __construct(
        private readonly EntityManagerInterface $em,
        private readonly \DateTimeZone $tz = new \DateTimeZone('Europe/Paris'),
    ) {}

    public function next(string $sequenceKey): string
    {
        $period = (new \DateTimeImmutable('now', $this->tz))->format('Ym'); // e.g. 202510

        $this->em->beginTransaction();

        try {
            // Step 1: lock row if exists
            $repo = $this->em->getRepository(Sequence::class);

            /** @var Sequence|null $sequence */
            $sequence = $repo->createQueryBuilder('s')
                ->andWhere('s.sequenceKey = :key')
                ->andWhere('s.period = :period')
                ->setParameter('key', $sequenceKey)
                ->setParameter('period', $period)
                ->getQuery()
                ->setLockMode(LockMode::PESSIMISTIC_WRITE) // translates to SELECT ... FOR UPDATE
                ->getOneOrNullResult();

            if ($sequence) {
                $nextInt = $sequence->increment();
            } else {
                // First value of this period for this key
                $sequence = new Sequence($sequenceKey, $period, 1);
                $this->em->persist($sequence);
                $nextInt = 1;
            }

            $this->em->flush();
            $this->em->commit();

            // Format final code: AAAAMM-00001
            return sprintf('%s-%05d', $period, $nextInt);

        } catch (\Throwable $e) {
            $this->em->rollback();
            throw $e;
        }
    }
}

Why PESSIMISTIC_WRITE instead of raw SQL?

• Doctrine will emit SELECT ... FOR UPDATE under the hood on InnoDB.

This guarantees atomic increments even with many parallel requests.

If you prefer DBAL and manual SQL instead of ORM for this part (which some auditors like because it's simpler to prove), I'll show that next.

5. (Alt) Low-level DBAL version (even stricter / auditor-friendly)

Some NF525 auditors dislike “ORM magic” and want to literally see: lock row → increment → commit.
You can provide that too.

php
Copier le code
<?php
// src/Service/SequencerDbal.php

namespace App\Service;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Exception;

class SequencerDbal implements SequencerInterface
{
    public function __construct(
        private readonly Connection $conn,
        private readonly \DateTimeZone $tz = new \DateTimeZone('Europe/Paris'),
    ) {}

    /**
     * @throws Exception
     */
    public function next(string $sequenceKey): string
    {
        $period = (new \DateTimeImmutable('now', $this->tz))->format('Ym'); // e.g. 202510

        $this->conn->beginTransaction();
        try {
            // 1. Lock row if exists
            $row = $this->conn->fetchAssociative(
                'SELECT id, current_value 
                 FROM sequences 
                 WHERE sequence_key = :key AND period = :period
                 FOR UPDATE',
                [
                    'key'    => $sequenceKey,
                    'period' => $period,
                ]
            );

            if ($row) {
                $nextInt = (int)$row['current_value'] + 1;
                $this->conn->update(
                    'sequences',
                    [
                        'current_value' => $nextInt,
                        'updated_at'    => (new \DateTimeImmutable())->format('Y-m-d H:i:s'),
                    ],
                    ['id' => $row['id']]
                );
            } else {
                $nextInt = 1;
                $this->conn->insert('sequences', [
                    'sequence_key'  => $sequenceKey,
                    'period'        => $period,
                    'current_value' => $nextInt,
                    'updated_at'    => (new \DateTimeImmutable())->format('Y-m-d H:i:s'),
                ]);
            }

            $this->conn->commit();

            return sprintf('%s-%05d', $period, $nextInt);

        } catch (\Throwable $e) {
            $this->conn->rollBack();
            throw $e;
        }
    }
}

This is extremely explicit and will make most accountants smile.

6. Wiring it in Symfony

In config/services.yaml:

yaml
Copier le code
services:
    App\Service\SequencerInterface:
        alias: App\Service\SequencerDbal
    App\Service\SequencerDbal:
        arguments:
            $tz: '@app.sequence_timezone'

    app.sequence_timezone:
        class: DateTimeZone
        factory: ['DateTimeZone', 'new']
        arguments: ['Europe/Paris']

Now you can autowire SequencerInterface anywhere and call next('invoice').

7. Where do you call it?

Typical places:

• Before persisting an Invoice entity (in an application service or domain service, not an entity lifecycle callback if you can avoid it — callbacks can get messy in tests and retries).

• When creating a fiscal log line.

Example in an InvoiceCreator service:

php
Copier le code
$invoiceNumber = $sequencer->next('invoice');

$invoice = new Invoice(
    number: $invoiceNumber,
    // ...
);

$em->persist($invoice);
$em->flush();

Important: call next() inside the same HTTP request/command that will persist the invoice, but not lazily later, so you never “burn” numbers without saving the document.

8. NF525-ish notes (not legal advice, just patterns auditors look for in FR 🇫🇷)

NF525 compliance around invoicing / cash register requires:

• Immutable numbering (no gaps / no reuse).

• Chronological traceability.

• Integrity: cannot silently modify past records.

This sequencer:

• Gives you strictly increasing numbers per month.

• Prevents duplicates.

• Prevents going backwards.

You still need:

• Audit log / signature of issued invoices linking each invoice to the previous one (hash chain). That part is out of scope here but keep it in mind — auditors will ask.

9. TL;DR copy/paste recipe

You can ship today with:

1. sequences table (SQL above)

2. SequencerDbal service (DBAL version above)

3. Call $sequencer->next('invoice') when creating the document

4. Store that string forever on the invoice row

That gives you:

• Generic: different sequence_key

• Atomic: row lock + transaction

• Required format: AAAAMM-00001

This is a solid core to build the rest of your NF525 story on ✅

Want next step from me: I can draft the migration file or show how to unit test concurrency.

Love it — we're getting into ergonomics now 😎

Where we are

• We have a sequences table that hands out atomic monthly numbers.

• We want entities (like Invoice, Quote, etc.) to “know” how to get their next sequence.

• Sequences are per user (tenant/customer/account), not global.

Example dream usage:

php
Copier le code
$invoice->nextSeq($sequencer, $user);
// and now $invoice->getNumber() === "202510-00042"

We'll get you very close to that, safely.

1. Update the sequences table for multi-tenant

We need to isolate numbering per business customer.

Add a user_id (or company_id, whatever your tenant is). That becomes part of the uniqueness.

sql
Copier le code
ALTER TABLE sequences
    ADD COLUMN user_id BIGINT UNSIGNED NOT NULL AFTER id;

ALTER TABLE sequences
    DROP INDEX uniq_sequence_period,
    ADD UNIQUE KEY uniq_sequence_user_period (
        user_id,
        sequence_key,
        period
    );

Now the “counter row” is scoped by:

• which user,

• which logical sequence (invoice, credit_note, ...),

• which period (YYYYMM)

DBAL service update (important part only)

php
Copier le code
public function next(string $sequenceKey, int $userId): string
{
    $period = (new \DateTimeImmutable('now', $this->tz))->format('Ym');

    $this->conn->beginTransaction();
    try {
        $row = $this->conn->fetchAssociative(
            'SELECT id, current_value
             FROM sequences
             WHERE user_id = :user
               AND sequence_key = :key
               AND period = :period
             FOR UPDATE',
            [
                'user'   => $userId,
                'key'    => $sequenceKey,
                'period' => $period,
            ]
        );

        if ($row) {
            $nextInt = (int)$row['current_value'] + 1;
            $this->conn->update(
                'sequences',
                [
                    'current_value' => $nextInt,
                    'updated_at'    => (new \DateTimeImmutable())->format('Y-m-d H:i:s'),
                ],
                ['id' => $row['id']]
            );
        } else {
            $nextInt = 1;
            $this->conn->insert('sequences', [
                'user_id'       => $userId,
                'sequence_key'  => $sequenceKey,
                'period'        => $period,
                'current_value' => $nextInt,
                'updated_at'    => (new \DateTimeImmutable())->format('Y-m-d H:i:s'),
            ]);
        }

        $this->conn->commit();

        return sprintf('%s-%05d', $period, $nextInt);

    } catch (\Throwable $e) {
        $this->conn->rollBack();
        throw $e;
    }
}

We just added $userId everywhere.
This is now tenant-safe.

2. How do we “link” the entity to a sequence?

We do not want heavy business logic inside Doctrine entities (audit, testing, hydrators, etc.), but we can give them:

• a standard property (like $sequenceNumber)

• a standard getter

• a helper that can be called to assign it

That smells like a trait ✅

The trait

php
Copier le code
<?php
// src/Entity/SequencedDocumentTrait.php

namespace App\Entity;

use App\Service\SequencerInterface;
use App\Entity\User;

trait SequencedDocumentTrait
{
    #[\Doctrine\ORM\Mapping\Column(name: 'sequence_number', length: 32, nullable: false)]
    private ?string $sequenceNumber = null;

    public function getSequenceNumber(): ?string
    {
        return $this->sequenceNumber;
    }

    /**
     * Assign a new sequence number if not already set.
     *
     * IMPORTANT:
     * - You pass the tenant user so we scope the counter.
     * - You pass the service so we're still testable / DI-friendly.
     */
    public function nextSeq(
        SequencerInterface $sequencer,
        User $user
    ): void {
        if (null !== $this->sequenceNumber) {
            // already numbered, don't renumber
            return;
        }

        $this->sequenceNumber = $sequencer->next(
            $this->getSequenceKeyForSequence(),
            $user->getId()
        );
    }
}

What this trait gives you:

• a column called sequence_number

• a nextSeq() method that uses the shared SequencerInterface

But you’ll notice we’re calling:

php
Copier le code
$this->getSequenceKeyForSequence()

This is on purpose 👇

3. Each entity defines its own sequence key

An invoice might want "invoice", a quote "quote", etc.
We'll force the entity to implement a tiny interface that exposes that key.

php
Copier le code
<?php
// src/Entity/SequencedDocumentInterface.php

namespace App\Entity;

interface SequencedDocumentInterface
{
    /**
     * Must return the logical sequence name,
     * e.g. 'invoice', 'quote', 'credit_note'
     */
    public function getSequenceKeyForSequence(): string;
}

Now your entity does:

php
Copier le code
<?php
// src/Entity/Invoice.php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class Invoice implements SequencedDocumentInterface
{
    use SequencedDocumentTrait;

    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'bigint')]
    private ?string $id = null;

    #[ORM\ManyToOne(targetEntity: User::class)]
    #[ORM\JoinColumn(nullable: false)]
    private User $owner; // tenant

    // ... other fields like totals, etc.

    public function __construct(User $owner)
    {
        $this->owner = $owner;
    }

    public function getOwner(): User
    {
        return $this->owner;
    }

    public function getSequenceKeyForSequence(): string
    {
        return 'invoice';
    }
}

Now Invoice:

• has sequence_number thanks to the trait

• knows who owns it ($owner)

• knows what key to use (invoice)

4. How you actually use it when creating the entity

In your application/service layer (controller, command handler, etc.):

php
Copier le code
public function createInvoice(
    User $user,
    SequencerInterface $sequencer,
    EntityManagerInterface $em
): Invoice {
    $invoice = new Invoice($user);

    // assign sequence number ONCE, atomically
    $invoice->nextSeq($sequencer, $user);

    $em->persist($invoice);
    $em->flush();

    return $invoice;
}

Boom:

• $invoice->getSequenceNumber() now returns something like 202510-00001

• That number is guaranteed unique per (user, month, sequence_key)

Also nice: if you try to call nextSeq() again later, nothing happens (so you can't accidentally renumber an already-issued invoice).

5. Can we make it even cleaner, like $invoice->nextSeq() with no args?

Short answer: you can, but it’s a trap in Symfony land.

Why it’s a trap:

• Entities shouldn’t know about services in their constructor (Doctrine gets grumpy when hydrating from DB).

• Autowiring into entities directly is not reliable.

• You can’t inject the current User cleanly there either (security context inside entity is a code smell).

So, the clean + auditable way is:

• Pass the sequencer + owner from the service that’s doing the creation.

• Keep the entity dumb-ish.

That keeps you on good terms with:

• unit tests

• Doctrine proxies

• auditors reading your code for tamperability

If you really want $entity->nextSeq() with zero args:

• you’d have to use a Doctrine lifecycle listener/subscriber that listens on prePersist, looks at the entity, checks if it instanceof SequencedDocumentInterface, injects Sequencer, injects Security to get current User, and sets the number automatically.

• That works, but it actually hides complexity and makes some fiscal auditors nervous because “magic side effects on persist” = harder to prove determinism.

I'll show you anyway because you might want it for ergonomics:

Doctrine Event Subscriber approach (automatic numbering on persist)

php
Copier le code
<?php
// src/Doctrine/SequenceAssignSubscriber.php

namespace App\Doctrine;

use App\Entity\SequencedDocumentInterface;
use App\Service\SequencerInterface;
use Doctrine\Common\EventSubscriber;
use Doctrine\ORM\Event\PrePersistEventArgs;
use Doctrine\ORM\Events;
use Symfony\Component\Security\Core\Security;

class SequenceAssignSubscriber implements EventSubscriber
{
    public function __construct(
        private readonly SequencerInterface $sequencer,
        private readonly Security $security, // to get current User/tenant
    ) {}

    public function getSubscribedEvents(): array
    {
        return [Events::prePersist];
    }

    public function prePersist(PrePersistEventArgs $args): void
    {
        $entity = $args->getObject();

        if (!$entity instanceof SequencedDocumentInterface) {
            return;
        }

        // entity uses the trait from before
        if (method_exists($entity, 'getSequenceNumber') && $entity->getSequenceNumber()) {
            // already assigned
            return;
        }

        $user = $this->security->getUser();
        if (!$user) {
            // up to you: throw? skip? depends on your model.
            throw new \RuntimeException('No user in context for sequence generation');
        }

        // do what nextSeq() did
        $sequenceNumber = $this->sequencer->next(
            $entity->getSequenceKeyForSequence(),
            $user->getId()
        );

        // directly set the private prop via the trait’s setter-ish behavior
        $refProperty = (new \ReflectionObject($entity))->getProperty('sequenceNumber');
        $refProperty->setAccessible(true);
        $refProperty->setValue($entity, $sequenceNumber);
    }
}

Register that subscriber as a service tagged with doctrine.event_subscriber.

Result:

• You just persist($invoice) and it auto-gets a number.

• But you’ve now coupled persistence order with number allocation, which some people actually dislike for auditability.

My recommendation for NF-style audit trails:
stay explicit and call $invoice->nextSeq($sequencer, $user) yourself in your app service.

6. Summary / what you can drop in today

1. Add user_id to sequences and make (user_id, sequence_key, period) unique.

2. Change SequencerInterface::next() to take $userId.

3. Add a trait that:

   • gives sequenceNumber column

   • provides nextSeq($sequencer, $user) to assign it once

4. Make each entity that needs numbering:

   • use SequencedDocumentTrait

   • implements SequencedDocumentInterface

   • define getSequenceKeyForSequence(): string

   • store the User/tenant

Then in your create flow:

php
Copier le code
$invoice = new Invoice($user);
$invoice->nextSeq($sequencer, $user);
$em->persist($invoice);
$em->flush();

That’s clean, explicit, tenant-scoped, atomic, NF-friendly, and reusable across all entities that need fiscal numbering. ✅

Haha — no worries at all 😄
You’re absolutely not stupid — that question shows you’re thinking in the right direction.

Let’s unpack it for a second, because this is actually one of those subtle “Doctrine mindset” things that everyone trips on at first.

💡 Why the trait holds $sequenceNumber

Exactly as you realized:
it’s not just for logic, it’s the actual persisted field that stores the issued sequence string in the database — e.g. "202510-00042".

That value becomes:

• immutable (you never reassign it once set)

• visible in your entity / repository queries

• what gets printed on the invoice / quote / whatever document

So the trait is basically giving you both:

1. the storage column (sequence_number)

2. a little bit of logic to fill it safely

🧩 Why put it in a trait instead of each entity

Two main reasons:

1. Avoid repetition
   Every entity that needs a sequence would otherwise need the same property, mapping annotation, getter/setter, etc.
   The trait keeps that DRY and consistent.

2. Allow generic logic
   You can later write helpers, Doctrine subscribers, or admin tools that work on any entity “that has a sequence number,” without knowing whether it’s an invoice or a quote — because they all share the same trait/interface contract.

⚙️ What happens at runtime

When you do:

php
Copier le code
$invoice->nextSeq($sequencer, $user);

That trait’s method:

1. Asks the Sequencer service for the next atomic number.

2. Sets $this->sequenceNumber = '202510-00042';

3. Doctrine sees that property and persists it to the sequence_number column.

And that’s it — from that point on it’s part of the entity’s state, saved to DB, and later retrievable via $invoice->getSequenceNumber().

🔒 NF525 perspective

From a fiscal-compliance point of view, having the number stored directly on the document record is important:

• It becomes immutable proof that the document existed with that number.

• You can hash/sign it for audit trails.

• You don’t rely on a separate table join just to find the numbering sequence afterward.

So your instinct (“wait, why store it there?”) is actually the kind of healthy skepticism that auditors like — you’re thinking about persistence boundaries 👏

TL;DR:
You’re 100% correct now — the trait carries $sequenceNumber because that’s the document’s own serial number, and we want to persist it once assigned.
Nothing stupid about asking — it’s the kind of detail that distinguishes a framework user from a framework master.