Counter Management

Overview

Counters generate sequential, formatted identifiers like PRD-2025-001, ORD-2025-12-19-0001. CoreWrite automatically manages counters via TB_COUNTER metadata table, eliminating manual sequence management.

Benefits:

✅ Human-readable identifiers
✅ Sequential numbering per dimension
✅ Flexible format masks (year, month, day, sequence)
✅ Concurrent-safe (database-level locking)
✅ Automatic reset (yearly, monthly, daily)
✅ No manual management needed

TB_COUNTER Table

Schema

CREATE TABLE TB_COUNTER (
    COUNTER_ID INT PRIMARY KEY AUTO_INCREMENT,
    COD_DIM VARCHAR(10) NOT NULL,      -- Dimension (PRD, ORD, etc.)
    COD_VAR VARCHAR(50) NOT NULL,      -- Field (XPRD03, XORD01, etc.)
    COUNTER_VALUE INT NOT NULL DEFAULT 0,  -- Current value
    FORMAT_MASK VARCHAR(100),          -- Format: PRD-{YYYY}-{NNN}
    RESET_PERIOD VARCHAR(10),          -- YEARLY, MONTHLY, DAILY, NEVER
    LAST_RESET_DATE VARCHAR(8),        -- YYYYMMDD
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY (COD_DIM, COD_VAR)
);

Configuration Example

-- Product code counter: PRD-2025-001, PRD-2025-002, ...
INSERT INTO TB_COUNTER (
    COD_DIM,
    COD_VAR,
    COUNTER_VALUE,
    FORMAT_MASK,
    RESET_PERIOD,
    LAST_RESET_DATE
) VALUES (
    'PRD',                    -- Product dimension
    'XPRD03',                 -- Counter field
    0,                        -- Start at 0
    'PRD-{YYYY}-{NNN}',       -- Format mask
    'YEARLY',                 -- Reset each year
    '20250101'                -- Last reset date
);

-- Order number counter: ORD-2025-12-19-0001, ORD-2025-12-19-0002, ...
INSERT INTO TB_COUNTER (
    COD_DIM,
    COD_VAR,
    COUNTER_VALUE,
    FORMAT_MASK,
    RESET_PERIOD
) VALUES (
    'ORD',
    'XORD01',
    0,
    'ORD-{YYYY}-{MM}-{DD}-{NNNN}',
    'DAILY'
);

Format Masks

Available Tokens

Token	Description	Example
`{YYYY}`	4-digit year	2025
`{YY}`	2-digit year	25
`{MM}`	2-digit month	12
`{M}`	Month no padding	1, 12
`{DD}`	2-digit day	19
`{D}`	Day no padding	1, 19
`{NNN}`	Sequential number (3 digits)	001, 042, 999
`{NNNN}`	Sequential number (4 digits)	0001, 0042, 9999
`{N}`	Sequential number (no padding)	1, 42, 999

Format Examples

Simple counter:

Format: PRD-{YYYY}-{NNN}
Result: PRD-2025-001, PRD-2025-002, ...

Daily counter:

Format: ORD-{YYYY}{MM}{DD}-{NNNN}
Result: ORD-20251219-0001, ORD-20251219-0002, ...

Fiscal year counter:

Format: INV-FY{YY}-{NNNN}
Result: INV-FY25-0001, INV-FY25-0002, ...

Short format:

Format: {YY}{MM}-{NNN}
Result: 2512-001, 2512-002, ...

Counter Generation

Automatic Generation on POST

TB_COST.VALUE_TEMP triggers counter generation:

-- Configure field for counter generation
INSERT INTO TB_COST (
    COD_DIM,
    NUM_COST,
    COD_VAR,
    VALUE_TEMP,
    COD_ON_OFF,
    DESCRIZIONE_COST
) VALUES (
    'PRD',
    3,
    'XPRD03',
    '{counter:PRD-{YYYY}-{NNN}}',  -- References counter config
    'LDR',                          -- Read-only (no N or M flag)
    'Product code'
);

Request:

POST /api/v4/core/PRD
{
  "data": {
    "XPRD01": "Widget Pro",
    "XPRD02": 99.99
    // XPRD03 auto-generated
  }
}

CoreWrite Logic:

// CoreWrite pre-insert processing
public function processPreInsertFunctions(string $dimension, array &$data): void {
    // Get field configurations
    $costs = $this->getFieldConfigurations($dimension);

    foreach ($costs as $cost) {
        if (strpos($cost['VALUE_TEMP'], '{counter:') === 0) {
            // Extract format mask from VALUE_TEMP
            $formatMask = $this->extractFormatMask($cost['VALUE_TEMP']);

            // Generate counter value
            $counterValue = $this->generateCounter($dimension, $cost['COD_VAR'], $formatMask);

            // Set field value
            $data[$cost['COD_VAR']] = $counterValue;
        }
    }
}

public function generateCounter(string $dimension, string $field, string $formatMask): string {
    // 1. Lock counter row for update (prevents race conditions)
    $counter = $this->pdo->query(
        "SELECT * FROM TB_COUNTER
         WHERE COD_DIM = :dim AND COD_VAR = :field
         FOR UPDATE",
        ['dim' => $dimension, 'field' => $field]
    )->fetch();

    // 2. Check if reset needed
    $currentDate = date('Ymd');
    if ($this->needsReset($counter, $currentDate)) {
        $counter['COUNTER_VALUE'] = 0;
        $counter['LAST_RESET_DATE'] = $currentDate;
    }

    // 3. Increment counter
    $counter['COUNTER_VALUE']++;

    // 4. Format with mask
    $formattedValue = $this->applyFormatMask($formatMask, $counter['COUNTER_VALUE']);

    // 5. Update counter in database
    $this->pdo->execute(
        "UPDATE TB_COUNTER
         SET COUNTER_VALUE = :value,
             LAST_RESET_DATE = :reset_date,
             UPDATED_AT = NOW()
         WHERE COD_DIM = :dim AND COD_VAR = :field",
        [
            'value' => $counter['COUNTER_VALUE'],
            'reset_date' => $counter['LAST_RESET_DATE'],
            'dim' => $dimension,
            'field' => $field
        ]
    );

    return $formattedValue;
}

private function applyFormatMask(string $mask, int $counterValue): string {
    $replacements = [
        '{YYYY}' => date('Y'),
        '{YY}' => date('y'),
        '{MM}' => date('m'),
        '{M}' => date('n'),
        '{DD}' => date('d'),
        '{D}' => date('j')
    ];

    // Replace date tokens
    $formatted = str_replace(array_keys($replacements), array_values($replacements), $mask);

    // Replace counter tokens
    if (preg_match('/{N+}/', $formatted, $matches)) {
        $padding = strlen($matches[0]) - 2;  // {NNN} = 3 padding
        $formatted = preg_replace('/{N+}/', str_pad($counterValue, $padding, '0', STR_PAD_LEFT), $formatted);
    }

    return $formatted;
}

Response:

{
  "status": "success",
  "data": {
    "PRD_ID": "uuid-...",
    "XPRD01": "Widget Pro",
    "XPRD02": 99.99,
    "XPRD03": "PRD-2025-001",  // Auto-generated
    "TREC": "N"
  }
}

Reset Periods

RESET_PERIOD Values

YEARLY: Resets on January 1st

INSERT INTO TB_COUNTER (COD_DIM, COD_VAR, FORMAT_MASK, RESET_PERIOD)
VALUES ('PRD', 'XPRD03', 'PRD-{YYYY}-{NNN}', 'YEARLY');

2024-12-31: PRD-2024-999
2025-01-01: PRD-2025-001  ← Reset to 1
2025-01-02: PRD-2025-002

MONTHLY: Resets on 1st of each month

INSERT INTO TB_COUNTER (COD_DIM, COD_VAR, FORMAT_MASK, RESET_PERIOD)
VALUES ('INV', 'XINV01', 'INV-{YYYY}{MM}-{NNN}', 'MONTHLY');

2025-11-30: INV-202511-999
2025-12-01: INV-202512-001  ← Reset to 1
2025-12-02: INV-202512-002

DAILY: Resets every day

INSERT INTO TB_COUNTER (COD_DIM, COD_VAR, FORMAT_MASK, RESET_PERIOD)
VALUES ('ORD', 'XORD01', 'ORD-{YYYY}{MM}{DD}-{NNNN}', 'DAILY');

2025-12-18: ORD-20251218-9999
2025-12-19: ORD-20251219-0001  ← Reset to 1
2025-12-19: ORD-20251219-0002

NEVER: Never resets (continuous sequence)

INSERT INTO TB_COUNTER (COD_DIM, COD_VAR, FORMAT_MASK, RESET_PERIOD)
VALUES ('USR', 'XUSR03', 'USR-{NNNNNN}', 'NEVER');

USR-000001, USR-000002, ..., USR-999999, USR-1000000

Concurrent Safety

Row-Level Locking

SELECT FOR UPDATE prevents race conditions:

Transaction A              Transaction B
    |                          |
BEGIN                      BEGIN
    |                          |
SELECT ... FOR UPDATE          |
(locks counter row)            |
    |                          |
    |                     SELECT ... FOR UPDATE
    |                     [BLOCKED - waiting for A]
    |                          |
COUNTER_VALUE++                |
    |                          |
UPDATE TB_COUNTER              |
    |                          |
COMMIT                         |
(releases lock)                |
    |                          |
    |                     [UNBLOCKED]
    |                     COUNTER_VALUE++
    |                     UPDATE TB_COUNTER
    |                     COMMIT

Result: No duplicate counter values, even under high concurrency.

Multiple Counters Per Dimension

Different counters for different purposes:

-- Product code counter (SKU)
INSERT INTO TB_COUNTER (COD_DIM, COD_VAR, FORMAT_MASK, RESET_PERIOD)
VALUES ('PRD', 'XPRD03', 'PRD-{YYYY}-{NNN}', 'YEARLY');

-- Product barcode counter (EAN)
INSERT INTO TB_COUNTER (COD_DIM, COD_VAR, FORMAT_MASK, RESET_PERIOD)
VALUES ('PRD', 'XPRD_BARCODE', '{NNNNNNNNNNNN}', 'NEVER');

Best Practices

✅ DO:

Use counters for user-facing codes:

-- ✅ Good - readable codes
FORMAT_MASK = 'PRD-{YYYY}-{NNN}'
Result: PRD-2025-001

Include year in format for yearly reset:

-- ✅ Good - year visible
FORMAT_MASK = 'INV-{YYYY}-{NNNN}'
Result: INV-2025-0001

Use appropriate padding:

-- ✅ Good - 4 digits for ~10k/year
FORMAT_MASK = 'ORD-{YYYY}-{NNNN}'
Result: ORD-2025-0001 to ORD-2025-9999

Let CoreWrite manage counters:

// ✅ Good - automatic
{
  "data": {
    "XPRD01": "Product Name"
    // XPRD03 auto-generated
  }
}

❌ DON'T:

Don't try to set counter value manually:

// ❌ Bad - will be rejected (COD_ON_OFF='LDR')
{
  "data": {
    "XPRD01": "Product",
    "XPRD03": "PRD-2025-999"  // Read-only!
  }
}

Don't use short padding for high volume:

-- ❌ Bad - only 999 per year
FORMAT_MASK = 'PRD-{YYYY}-{NN}'
Result: PRD-2025-99 (max!)

Don't forget year for yearly reset:

-- ❌ Bad - ambiguous dates
FORMAT_MASK = 'INV-{NNN}'
Result: INV-001 (which year?)

-- ✅ Good
FORMAT_MASK = 'INV-{YYYY}-{NNN}'
Result: INV-2025-001

Troubleshooting

Duplicate Counter Values

Symptom: Duplicate codes generated

Cause: Missing FOR UPDATE lock

Solution: Ensure CoreWrite uses row locking:

// ✅ Correct - with lock
SELECT * FROM TB_COUNTER WHERE ... FOR UPDATE

// ❌ Wrong - no lock
SELECT * FROM TB_COUNTER WHERE ...

Counter Not Resetting

Symptom: Counter continues incrementing past reset date

Cause: LAST_RESET_DATE not updated

Fix:

-- Manual reset
UPDATE TB_COUNTER
SET COUNTER_VALUE = 0,
    LAST_RESET_DATE = CURDATE()
WHERE COD_DIM = 'PRD' AND COD_VAR = 'XPRD03';

Counter Value Too High

Symptom: PRD-2025-10000 (expected max 9999)

Cause: Insufficient padding in format mask

Fix:

-- Increase padding
UPDATE TB_COUNTER
SET FORMAT_MASK = 'PRD-{YYYY}-{NNNNN}'  -- 5 digits instead of 4
WHERE COD_DIM = 'PRD' AND COD_VAR = 'XPRD03';

Summary

✅ TB_COUNTER manages sequential identifiers
✅ Format masks: {YYYY}, {MM}, {DD}, {NNN}
✅ Reset periods: YEARLY, MONTHLY, DAILY, NEVER
✅ Row-level locking prevents duplicates
✅ Automatic generation via TB_COST.VALUE_TEMP
✅ Read-only fields (COD_ON_OFF without N/M flags)

Key Takeaways:

Counters generate human-readable codes automatically
Format mask defines structure: PRD-{YYYY}-{NNN}
Reset period controls when counter resets
SELECT FOR UPDATE ensures concurrent safety
COD_ON_OFF='LDR' makes counter read-only
No manual management needed

Counter Configuration:

Component	Purpose	Example
COD_DIM	Dimension	PRD
COD_VAR	Field	XPRD03
FORMAT_MASK	Format	`PRD-{YYYY}-{NNN}`
RESET_PERIOD	When to reset	YEARLY
COUNTER_VALUE	Current value	42

Generated: PRD-2025-042

Next: Error Handling →

Create Operations - Counter generation on POST
Field Visibility - COD_ON_OFF read-only
MAP Framework - TB_COUNTER metadata

Counter Management

Overview​

TB_COUNTER Table​

Schema​

Configuration Example​

Format Masks​

Available Tokens​

Format Examples​

Counter Generation​

Automatic Generation on POST​

Reset Periods​

RESET_PERIOD Values​

Concurrent Safety​

Row-Level Locking​

Multiple Counters Per Dimension​

Best Practices​

✅ DO:​

❌ DON'T:​

Troubleshooting​

Duplicate Counter Values​

Counter Not Resetting​

Counter Value Too High​

Summary​

Related Concepts​