Number Sequence Management

File: /docs/concepts/shared/number-sequence-management.concept.md
Audience: System Configurators, Architects, Developers
Scope: Cross-Module (Shared Infrastructure)

Overview

This document explains the complete number sequence management system in the ERP, from initial configuration through number generation and lifecycle management. Number sequences are critical infrastructure that ensure unique, auditable identifiers for business documents across all modules.

Business Goal

Enable organizations to:

Generate consistent, gap-minimal sequential numbers for business documents
Maintain audit trails through traceable document numbering
Support regulatory compliance with continuous numbering requirements
Configure format patterns that match organizational standards
Prevent duplicate identifiers across concurrent operations

Key Concepts

Concept	Definition	Example
NumberSequence	Configuration that generates formatted sequential numbers	"Sales Invoice Numbers"
Format Pattern	Template with '#' placeholders defining number structure	"INV-2025-####"
ModuleFeature	Specific transaction type that uses a sequence	SalesInvoices, PurchaseOrders
Sequence Reference	Association between a sequence and ModuleFeature(s)	SalesInvoices → "Sales Invoice Numbers"
Current Value	Last generated number	42
Pessimistic Lock	Database row lock preventing concurrent access	FOR UPDATE in SQL

High-Level Architecture

Complete Workflow

Detailed Process Steps

Phase 1: Initial Configuration (Administrator)

Step 1.1: Create Number Sequence

Prerequisites: Administrator access to system configuration

Configuration Decisions:

Name (Unique Identifier)
- Purpose: Human-readable name for administration
- Example: "Sales Invoice Numbers", "Purchase Order Sequence"
- Must be unique across entire system

Format Pattern (Number Structure)

Uses '#' as digit placeholder
All other characters are literals

Examples:

"INV-####"          → INV-0001, INV-0002, ...
"PO-2025-######"    → PO-2025-000001, PO-2025-000002, ...
"V-####-A"          → V-0001-A, V-0002-A, ...

Starting Value (Optional)
- Default: 0 (first number will be 1)
- Can be set to resume from legacy system
- Example: Start at 1000 to continue from old system
ModuleFeature Associations
- Select which transaction types use this sequence
- Examples:
  - Sales Invoices → "Sales Invoice Numbers"
  - Sales Invoice Vouchers → "Sales Invoice Numbers"
  - Return Invoices → "Sales Invoice Numbers"

Real-World Example:

{
  "name": "Sales Invoice Master Sequence",
  "format": "SI-2025-#####",
  "startingValue": 0,
  "moduleFeatures": [
    "SalesInvoices",
    "SalesInvoiceVoucher",
    "ReturnInvoices"
  ]
}

Why Multiple Features Use Same Sequence:

Provides unified numbering across related documents
Simplifies audit trail (chronological order across document types)
Reduces administrative overhead

Step 1.2: Understanding ModuleFeatures

ModuleFeature Hierarchy:

Module: GeneralLedger (ID range: 1-100)
  - JournalBatchNumber (1)
  - TraceNumber (2)
  - GeneralJournalReversalVoucher (4)

Module: AccountsReceivable (ID range: 101-200)
  - Customers (101)
  - SalesInvoices (102)
  - SalesInvoiceVoucher (103)
  - CustomerPaymentJournal (104)
  - PrepaymentInvoice (105)
  - ReturnInvoices (108)

Module: AccountsPayable (ID range: 201-300)
  - Vendors (201)
  - PurchaseInvoices (202)
  - PurchaseInvoiceVoucher (203)
  - VendorPaymentJournal (204)
  - PrepaymentInvoiceVoucher (206)

Configuration Pattern:

// One sequence can serve multiple related features
var invoiceSequence = new NumberSequence("Invoice Numbers", "INV-####");
invoiceSequence.AddReference(AccountsReceivableFeature.SalesInvoices);
invoiceSequence.AddReference(AccountsReceivableFeature.SalesInvoiceVoucher);

// Or, separate sequences for complete isolation
var salesSequence = new NumberSequence("Sales Invoices", "SI-####");
var returnSequence = new NumberSequence("Return Invoices", "RI-####");

Step 1.3: Format Pattern Design Guidelines

Best Practices:

Include Year for Long-Running Systems

"INV-2025-####"  // Better than "INV-####" if system runs multiple years

Plan for Volume

"####"     // Supports 0-9999 (10K documents)
"#####"    // Supports 0-99999 (100K documents)
"######"   // Supports 0-999999 (1M documents)

Use Meaningful Prefixes

"SI-####"  // Sales Invoice
"PI-####"  // Purchase Invoice
"PAY-####" // Payment
"ADJ-####" // Adjustment

Consider Sorting Requirements

// Good: Alphabetically sorts chronologically
"INV-2025-####"

// Bad: Won't sort properly across years
"INV-####-2025"

Avoid Special Characters that Cause Issues

// Good
"INV-####"
"PO_####"

// Potentially problematic in some systems
"INV/####"  // Slash can be confused with URL paths
"INV#####"  // No separator between prefix and number

Phase 2: Number Generation (Runtime Operations)

Step 2.1: Request Flow

Trigger Points: A command handler needs to create a business document that requires a unique identifier.

Decision Point: How to identify the correct sequence?

Option 1: By ModuleFeature (Most Common)

// Handler knows the transaction type
string invoiceNumber = await _numberSequenceService.GetNextNumberAsync(
    AccountsReceivableFeature.SalesInvoices
);

Option 2: By NumberSequence ID (When Pre-Configured)

// Journal has pre-configured voucher sequence
string voucherNumber = await _numberSequenceService.GetNextNumberAsync(
    journal.VoucherSeriesId  // Guid of NumberSequence
);

Step 2.2: The Critical Locking Pattern

The Problem: Race Condition

Transaction A                    Transaction B
-----------                      -----------
Read CurrentValue: 42            Read CurrentValue: 42
Increment to: 43                 Increment to: 43
Save: CurrentValue = 43          Save: CurrentValue = 43
Generate: "INV-0043"             Generate: "INV-0043"

RESULT: DUPLICATE NUMBERS!

The Solution: Pessimistic Row Lock

-- Transaction A acquires lock first
SELECT * FROM number_sequences WHERE id = $1 FOR UPDATE;
-- Transaction B must wait here until A commits

-- Transaction A increments and commits
UPDATE number_sequences SET current_value = 43 WHERE id = $1;
COMMIT;
-- Lock released

-- Transaction B can now proceed
SELECT * FROM number_sequences WHERE id = $1 FOR UPDATE;
-- Gets CurrentValue = 43 (updated by A)
-- Increments to 44
-- No duplicate!

Implementation:

// Repository Layer (PostgreSQL-specific)
public async Task<NumberSequence?> GetByIdForUpdateAsync(Guid id)
{
    return await _context.NumberSequences
        .FromSqlRaw(
            "SELECT * FROM general_ledger.number_sequences WHERE id = {0} FOR UPDATE", 
            id
        )
        .FirstOrDefaultAsync();
}

// Service Layer
public async Task<string> GetNextNumberAsync(Guid numberSequenceId)
{
    // CRITICAL: Acquire lock
    var sequence = await _repository.GetByIdForUpdateAsync(numberSequenceId);
    
    // Safe to increment - we have exclusive access
    string number = sequence.GetNextNumber();
    
    // Mark for update
    _repository.Update(sequence);
    
    // Caller's transaction will commit and release lock
    return number;
}

Step 2.3: Transaction Coordination

Command Handler Pattern:

public class CreateSalesInvoiceCommandHandler : IRequestHandler<CreateSalesInvoiceCommand, Guid>
{
    private readonly INumberSequenceService _numberSequenceService;
    private readonly ISalesInvoiceRepository _invoiceRepository;
    private readonly IUnitOfWork _unitOfWork;
    
    public async Task<Guid> Handle(CreateSalesInvoiceCommand request, CancellationToken ct)
    {
        // IMPORTANT: Number generation happens INSIDE the transaction
        // that creates the invoice
        
        // Step 1: Generate invoice number (acquires lock)
        string invoiceNumber = await _numberSequenceService.GetNextNumberAsync(
            AccountsReceivableFeature.SalesInvoices
        );
        
        // Step 2: Create invoice with generated number
        var invoice = new SalesInvoice(
            invoiceNumber: invoiceNumber,
            customerId: request.CustomerId,
            invoiceDate: request.InvoiceDate,
            dueDate: request.DueDate
        );
        
        // Add lines, calculate totals, etc.
        foreach (var lineDto in request.Lines)
        {
            invoice.AddLine(lineDto.ItemId, lineDto.Quantity, lineDto.Price);
        }
        
        // Step 3: Persist invoice
        _invoiceRepository.Add(invoice);
        
        // Step 4: Commit transaction (saves both sequence and invoice)
        await _unitOfWork.SaveChangesAsync(ct);
        
        // Lock is released here
        // If any step failed, entire transaction rolls back
        // including the sequence increment
        
        return invoice.Id;
    }
}

Transaction Rollback Scenario:

BEGIN TRANSACTION
  1. Get next number: "INV-0042" (sequence locked, CurrentValue now 42)
  2. Create invoice with "INV-0042"
  3. Validate invoice business rules
  4. Validation FAILS
  ROLLBACK
  
Result: 
  - Invoice not created
  - Sequence CurrentValue rolled back to 41
  - Next invoice will get "INV-0042" (no gap)

Exception Scenario (Gap Created):

BEGIN TRANSACTION
  1. Get next number: "INV-0042" (CurrentValue: 42)
  2. Create invoice with "INV-0042"
  3. COMMIT (sequence saved)
NEXT TRANSACTION
  4. User cancels operation or network error
  
Result:
  - Sequence CurrentValue is 42 (committed)
  - No invoice exists with "INV-0042"
  - Next invoice will get "INV-0043" (gap at 0042)

Architectural Decision: Most systems accept occasional gaps for simplicity. True gap-free numbering requires complex two-phase commit patterns.

Phase 3: Maintenance and Lifecycle Management

Step 3.1: Deactivating a Sequence

Use Case: Temporarily disable number generation without deleting configuration.

Scenario: End of fiscal year, switching to new year-specific sequence.

// Deactivate old sequence
var old2024Sequence = await _repository.GetByNameAsync("Invoices-2024");
old2024Sequence.Deactivate();
_repository.Update(old2024Sequence);

// Activate new sequence
var new2025Sequence = await _repository.GetByNameAsync("Invoices-2025");
new2025Sequence.Activate();
_repository.Update(new2025Sequence);

// Reassign ModuleFeature
await UpdateModuleFeatureSequenceAsync(
    moduleFeature: AccountsReceivableFeature.SalesInvoices,
    newSequenceId: new2025Sequence.Id
);

await _unitOfWork.SaveChangesAsync();

User Impact:

Attempting to generate from deactivated sequence throws exception
Historical documents retain their numbers (immutable)
Audit trail preserved

Step 3.2: Changing Format

Use Case: Company rebranding or numbering standard change.

Important: Format changes only affect FUTURE numbers, not historical ones.

var sequence = await _repository.GetByIdAsync(sequenceId);

// Current: "INV-####", CurrentValue: 42, Last generated: "INV-0042"

sequence.Update(format: "INVOICE-2025-#####");

// Next number: "INVOICE-2025-00043"
// Historical "INV-0042" remains unchanged in database

Best Practice: When changing formats significantly, create a NEW sequence instead of updating existing one to avoid confusion.

Step 3.3: Reassigning ModuleFeatures

Use Case: Consolidating sequences or separating previously combined ones.

Scenario: Initially, all invoices used one sequence. Now, company wants separate sequences for domestic vs. export invoices.

// Step 1: Create new sequences
var domesticSeq = new NumberSequence("Domestic Invoices", "DOM-####");
var exportSeq = new NumberSequence("Export Invoices", "EXP-####");

_repository.Add(domesticSeq);
_repository.Add(exportSeq);
await _unitOfWork.SaveChangesAsync();

// Step 2: Get current unified sequence
var oldUnifiedSeq = await _repository.GetByDataTypeAsync(
    AccountsReceivableFeature.SalesInvoices
);

// Step 3: Remove reference from old sequence
oldUnifiedSeq.RemoveReference(AccountsReceivableFeature.SalesInvoices);
_repository.Update(oldUnifiedSeq);

// Step 4: Add references to new sequences
// (This requires application logic to determine which sequence to use)
// Typically done through configuration or document type field

await _unitOfWork.SaveChangesAsync();

Migration Considerations:

Historical documents keep their original numbers
New documents use new sequences
Application logic may need updates to select correct sequence
Report queries may need adjustments to handle multiple sequences

Step 3.4: Resetting or Advancing a Sequence

Use Case 1: Annual Reset

var sequence = await _repository.GetByIdAsync(sequenceId);

// Reset to 0 (next number will be 1)
sequence.Update(currentValue: 0);
_repository.Update(sequence);
await _unitOfWork.SaveChangesAsync();

Use Case 2: Migrating from Legacy System

var sequence = await _repository.GetByIdAsync(sequenceId);

// Legacy system last invoice was SI-2024-05432
// Set current value to continue from there
sequence.Update(currentValue: 5432);
_repository.Update(sequence);
await _unitOfWork.SaveChangesAsync();

// Next generated number: "SI-2025-05433"

Common Configuration Patterns

Scenario: All sales-related documents use unified numbering for chronological traceability.

var salesSequence = new NumberSequence("Sales Documents", "SD-2025-#####");

salesSequence.AddReference(AccountsReceivableFeature.SalesInvoices);
salesSequence.AddReference(AccountsReceivableFeature.ReturnInvoices);
salesSequence.AddReference(AccountsReceivableFeature.PrepaymentInvoice);

Result:

SD-2025-00001  - Sales Invoice
SD-2025-00002  - Sales Invoice
SD-2025-00003  - Return Invoice
SD-2025-00004  - Prepayment Invoice
SD-2025-00005  - Sales Invoice

Benefits:

Chronological order across document types
Single sequence to manage
Clear audit trail

Drawbacks:

Cannot distinguish document type from number alone
Single point of contention (all documents lock same sequence)

Pattern 2: Separate Sequences by Document Type

Scenario: Each document type has its own numbering for clear identification.

var salesInvSeq = new NumberSequence("Sales Invoices", "SI-2025-####");
salesInvSeq.AddReference(AccountsReceivableFeature.SalesInvoices);

var returnInvSeq = new NumberSequence("Return Invoices", "RI-2025-####");
returnInvSeq.AddReference(AccountsReceivableFeature.ReturnInvoices);

var prepaymentSeq = new NumberSequence("Prepayments", "PP-2025-####");
prepaymentSeq.AddReference(AccountsReceivableFeature.PrepaymentInvoice);

Result:

SI-2025-0001  - Sales Invoice
SI-2025-0002  - Sales Invoice
RI-2025-0001  - Return Invoice  (separate sequence)
PP-2025-0001  - Prepayment      (separate sequence)
SI-2025-0003  - Sales Invoice

Benefits:

Document type immediately visible in number
Reduced lock contention (separate sequences)
Easier filtering and reporting

Drawbacks:

More sequences to manage
Chronological order not immediately apparent across types

Pattern 3: Department-Specific Sequences

Scenario: Large organization with multiple departments issuing invoices independently.

var salesDeptSeq = new NumberSequence("Sales Dept Invoices", "SALES-####");
var serviceDeptSeq = new NumberSequence("Service Dept Invoices", "SVC-####");
var exportDeptSeq = new NumberSequence("Export Dept Invoices", "EXP-####");

// Application logic determines which sequence based on department
// This may require custom configuration beyond ModuleFeature

Benefits:

Departmental autonomy
Parallel processing (no cross-department contention)
Clear departmental identification

Drawbacks:

Requires additional application logic
More complex configuration

Pattern 4: Year-Specific Sequences

Scenario: Annual reset for regulatory or organizational standards.

// End of 2024
var seq2024 = new NumberSequence("Invoices 2024", "INV-2024-####");
seq2024.Deactivate();  // Stop using

// Start of 2025
var seq2025 = new NumberSequence("Invoices 2025", "INV-2025-####");
seq2025.AddReference(AccountsReceivableFeature.SalesInvoices);

Benefits:

Clean annual restart
Year embedded in document number
Historical sequences preserved

Drawbacks:

Annual maintenance required
Need to switch configuration at year boundary

Design Decisions & Rationale

Decision 1: Format Pattern with '#' Placeholders

Alternative Considered: Complex format DSL with variables like {YEAR}, {SEQUENCE}, {PREFIX}.

Decision: Use simple '#' placeholders.

Rationale:

Easy to understand for administrators
Covers 95% of real-world requirements
Less prone to configuration errors
Year/prefix are typically static per sequence

Trade-off: Dynamic values (like auto-updating year) require creating new sequences or application-level logic.

Decision 2: Pessimistic Locking (FOR UPDATE)

Alternative Considered: Optimistic locking with version checks.

Decision: Use pessimistic row-level locks.

Rationale:

Guarantees no duplicates
Simpler error handling (no retry logic)
Acceptable performance for typical ERP workloads
Standard pattern in established ERP systems

Trade-off: Lock contention under very high concurrency. Mitigated by using separate sequences.

Decision 3: Accept Gaps on Transaction Rollback

Alternative Considered: Two-phase commit to prevent all gaps.

Decision: Accept occasional gaps.

Rationale:

Vastly simpler implementation
Acceptable for most regulatory requirements
True gap-free numbering is extremely complex
Gaps are rare in practice (most transactions succeed)

Trade-off: Small risk of gaps, but benefits of simplicity outweigh.

Decision 4: ModuleFeature as Enumeration (Not Configuration)

Alternative Considered: Store module features as database table.

Decision: Use code-based Enumeration pattern.

Rationale:

ModuleFeatures are tightly coupled to code
Adding a feature requires code changes anyway (new aggregate, handlers, etc.)
Type-safe in application code
No risk of invalid references

Trade-off: Less flexible for runtime configuration, but appropriate for this use case.

Performance & Scalability

Throughput Expectations

Single Sequence Performance (PostgreSQL, typical hardware):

Sequential operations: 500-1000 numbers/second
Bottleneck: Database lock duration
Latency: 5-15ms per generation (including lock wait)

Scaling Strategies:

Horizontal Scaling via Sequence Separation

One global sequence: 500 numbers/sec max
Ten departmental sequences: 5000 numbers/sec aggregate

Connection Pooling
- Ensure adequate pool size (min: 20 connections)
- Monitor for pool exhaustion under load
Transaction Optimization
- Minimize time holding lock
- Generate number as late as possible in transaction
- Avoid external calls while holding lock

Monitoring & Alerting

Key Metrics:

Lock Wait Time

-- Monitor transactions waiting for sequence locks
SELECT * FROM pg_stat_activity 
WHERE wait_event = 'tuple' 
  AND query LIKE '%number_sequences%';

Generation Rate

-- Track numbers generated per sequence per hour
SELECT 
    ns.name,
    COUNT(*) as numbers_generated,
    MAX(current_value) as current_value
FROM number_sequences ns
GROUP BY ns.name;

Sequence Depletion Warnings

-- Alert when sequence approaching max capacity
SELECT 
    name,
    format,
    current_value,
    (10 ^ (LENGTH(format) - LENGTH(REPLACE(format, '#', '')))) as max_value,
    (current_value * 100.0 / (10 ^ (LENGTH(format) - LENGTH(REPLACE(format, '#', ''))))) as percent_used
FROM number_sequences
WHERE percent_used > 80;  -- Alert at 80% capacity

Troubleshooting

Issue 1: Slow Number Generation

Symptoms: CreateInvoice operations taking 500ms+ instead of <50ms.

Diagnosis:

-- Check for lock contention
SELECT 
    pid,
    wait_event,
    state,
    query
FROM pg_stat_activity
WHERE wait_event = 'tuple' 
  AND query LIKE '%number_sequences%';

Solutions:

Identify high-contention sequences
Consider splitting into multiple sequences
Review transaction sizes (are they unnecessarily long?)
Increase database connection pool

Issue 2: Unexpected Gaps in Numbering

Symptoms: Generated numbers jump (e.g., INV-0042, INV-0044, missing INV-0043).

Diagnosis:

-- Find gaps in invoice numbering
WITH invoice_numbers AS (
    SELECT 
        CAST(SUBSTRING(invoice_number FROM '\d+') AS INTEGER) as num
    FROM sales_invoices
    WHERE invoice_number LIKE 'INV-%'
)
SELECT 
    num + 1 as missing_number
FROM invoice_numbers
WHERE NOT EXISTS (
    SELECT 1 FROM invoice_numbers in2 
    WHERE in2.num = invoice_numbers.num + 1
)
ORDER BY num;

Root Causes:

Transaction rollback after number generation
Application error after commit
Manual deletion of invoices

Resolution: Typically acceptable. If critical, investigate specific missing numbers.

Issue 3: Format Doesn't Support Enough Numbers

Symptoms: current_value approaching maximum capacity.

Example: Format "INV-###" maxes at 999.

Solution:

// Update format to support more digits
var sequence = await _repository.GetByIdAsync(sequenceId);
sequence.Update(format: "INV-#####");  // Now supports 99,999
_repository.Update(sequence);
await _unitOfWork.SaveChangesAsync();

Domain Documentation

Conceptual Documentation

Journal Configuration and Setup

API Documentation

NumberSequence API

Last Updated: 2025-01-15 | Version: 1.0 | Status: Production

Overview​

Business Goal​

Key Concepts​

High-Level Architecture​

Complete Workflow​

Detailed Process Steps​

Phase 1: Initial Configuration (Administrator)​

Step 1.1: Create Number Sequence​

Step 1.2: Understanding ModuleFeatures​

Step 1.3: Format Pattern Design Guidelines​

Phase 2: Number Generation (Runtime Operations)​

Step 2.1: Request Flow​

Step 2.2: The Critical Locking Pattern​

Step 2.3: Transaction Coordination​

Phase 3: Maintenance and Lifecycle Management​

Step 3.1: Deactivating a Sequence​

Step 3.2: Changing Format​

Step 3.3: Reassigning ModuleFeatures​

Step 3.4: Resetting or Advancing a Sequence​

Common Configuration Patterns​

Pattern 1: Shared Sequence Across Related Documents​

Pattern 2: Separate Sequences by Document Type​

Pattern 3: Department-Specific Sequences​

Pattern 4: Year-Specific Sequences​

Design Decisions & Rationale​

Decision 1: Format Pattern with '#' Placeholders​

Decision 2: Pessimistic Locking (FOR UPDATE)​

Decision 3: Accept Gaps on Transaction Rollback​

Decision 4: ModuleFeature as Enumeration (Not Configuration)​

Performance & Scalability​

Throughput Expectations​

Monitoring & Alerting​

Troubleshooting​

Issue 1: Slow Number Generation​

Issue 2: Unexpected Gaps in Numbering​

Issue 3: Format Doesn't Support Enough Numbers​

Related Documentation​

Domain Documentation​

Conceptual Documentation​

API Documentation​

Overview

Business Goal

Key Concepts

High-Level Architecture

Complete Workflow

Detailed Process Steps

Phase 1: Initial Configuration (Administrator)

Step 1.1: Create Number Sequence

Step 1.2: Understanding ModuleFeatures

Step 1.3: Format Pattern Design Guidelines

Phase 2: Number Generation (Runtime Operations)

Step 2.1: Request Flow

Step 2.2: The Critical Locking Pattern

Step 2.3: Transaction Coordination

Phase 3: Maintenance and Lifecycle Management

Step 3.1: Deactivating a Sequence

Step 3.2: Changing Format

Step 3.3: Reassigning ModuleFeatures

Step 3.4: Resetting or Advancing a Sequence

Common Configuration Patterns

Pattern 1: Shared Sequence Across Related Documents

Pattern 2: Separate Sequences by Document Type

Pattern 3: Department-Specific Sequences

Pattern 4: Year-Specific Sequences

Design Decisions & Rationale

Decision 1: Format Pattern with '#' Placeholders

Decision 2: Pessimistic Locking (FOR UPDATE)

Decision 3: Accept Gaps on Transaction Rollback

Decision 4: ModuleFeature as Enumeration (Not Configuration)

Performance & Scalability

Throughput Expectations

Monitoring & Alerting

Troubleshooting

Issue 1: Slow Number Generation

Issue 2: Unexpected Gaps in Numbering

Issue 3: Format Doesn't Support Enough Numbers

Related Documentation

Domain Documentation

Conceptual Documentation

API Documentation