# Design: Unified Identifier Engine — Metadata-Driven Auto-Numbering for All Objects

**Status:** DEFERRED — design implementation paused after architectural review (see Status Note below). Do not treat sections below as ratified or actionable without re-review.
**Date:** 2026-04-16 (original) · 2026-04-17 (status update)
**Related Issue:** ywang-official/MetaDrivenPlatform4AI#555 (this issue lives in the upstream fork; OSS contributors should consult #1078 in this repo for tracked context)

## Status Note (2026-04-17)

The implementation of this design was scoped down after post-merge audits (see PR #1029 comments by @IANTHEREAL and 4 Copilot review comments) surfaced both factual issues with the design and architectural risks for the migration:

- **Stated motivation falsified** — #555's root cause is a `filterToLogicalFields` schema gap, not a missing compound config (Contract is wired). Verified by code grep; real fix shipped in PR #1079 (16 lines of packspec field additions + 91 lines of consistency test = 107 LOC total).
- **Migration plan vs. production reality** — design assumed single-instance swap; actual deploy is ECS Fargate rolling (`infra/ecs.tf:157-158`, `deployment_maximum_percent=200`) with ~60s overlap window producing duplicate identifiers under the proposed cutover.
- **Pack boundary** — seeding PingCAP-specific Contract type taxonomy (CSA / TCSA / CON / NDA / PNA / CRA / PA / TCPA / TEPA) into `PlatformConfigPack` deepens an existing OSS taxonomy leak.
- **Engine-template gap** — seeded Contract template was hardcoded `CSA`, losing the listener's dynamic `RecordType.Name → code` mapping for non-CSA contracts (#1068).

What landed (Tasks 1, 2, 3, 5 of the original split: PRs #1039, #1053, #1060, #1040) is **dead infrastructure** — the engine is not wired into the persistence path. PR #1073 (wiring + listener delete) and PR #1076 (P1 hardening for #1068, #1071, #1072) are closed.

**Reactivation prerequisites** (per @IANTHEREAL recommendations 4-7 in PR #1029):

1. Pack split: move PingCAP Contract taxonomy and `Quote_No__c` / `Contract_Number__c` / `Order_Number__c` rule seeds out of `PlatformConfigPack` into a tenant pack.
2. `TokenResolver` hook so the engine itself stays taxonomy-free (engine never names CSA / Contract_Number__c).
3. Cutover via feature flag, not subscription swap, to handle ECS rolling deploy overlap.
4. Scope-key byte-equal property test (≥1000 fuzzed inputs vs. listener output).
5. Resolve open P1 issues #1068 (Contract RecordType mapping), #1071 (FieldLookups validation + tri-state), #1072 (composite Id collision).
6. Migration write-hole fix: the first-boot counter sync must read the **entire** old `_System_CompoundAutoNumber` table — not just the rows the data-spec copied at T0 — to capture scope keys created during the overlap window between data-spec apply and code redeploy.
7. Seeded-rule upsert policy: decide before any admin UI ships whether bootstrap may stomp admin-edited rows (e.g., upsert only when `CreatedBy='system' AND LastModifiedBy='system'`, otherwise INSERT IGNORE — matches Salesforce managed-package semantics).

**Tracking issues**: #1077 (dead infra cleanup decision), #1078 (#555 reproduce + real root cause), #1068 / #1071 / #1072 (P1 hardening if engine reactivates).

## Problem Statement

NexusCRM has two disconnected auto-numbering systems that don't compose:

1. **Simple sequential** (`_System_AutoNumber`): format string like `CON-{00000000}`, one global counter per (object, field). Lives in `persistence_service.go:887`.
2. **Compound naming** (`CompoundAutoNumberListener`): generates business-meaningful codes like `GB-CSA-250114` using cross-record field resolution + daily scoped sequences. Lives in `compound_autonumber_listener.go`. **Only supports Quote** — Contract and Order were marked "extensible" but never wired.

Issue #555 is the symptom: new Contracts get no `AutoNumberText` because no compound config exists for Contract. This will repeat for Order and any future object.

Users treat these formatted numbers as **primary identifiers** — referenced in emails, Slack, legal documents. This is identity infrastructure, not just formatting.

## Demand Evidence

- Issue #555 is P1: every new Contract is missing its formatted identifier
- Migrated Salesforce contracts show correct values (e.g., `JP-CSA-250217`, `GB-MA-241218`) proving the pattern was operational in production Salesforce
- UAT testers flagged this as a blocker — users cannot reference contracts without human-readable IDs
- The same gap exists for Order (not yet reported because Order creation hasn't been UAT-tested)

## Status Quo

Two code paths, one working (Quote), one incomplete:

| System | Table | Code Location | Objects | Status |
|--------|-------|---------------|---------|--------|
| Simple AutoNumber | `_System_AutoNumber` | `persistence_service.go:887` | All objects with autonumber fields | Working |
| Compound Naming | `_System_CompoundAutoNumber` (state only) | `compound_autonumber_listener.go` | Quote only | Partial |

The compound system's **rules are hardcoded in Go** (`l.configs["quote"]`), while the counter state lives in the DB. Adding a new object requires code changes, recompilation, and redeployment.

## Target User & Narrowest Wedge

**Target:** Internal PingCAP CRM admins and operations team who need to configure naming patterns for standard and custom objects.

**Narrowest wedge:** Ship the identifier engine with seeded rules for Quote, Contract, and Order. No admin UI yet — rules are seeded via bootstrap. Proves the metadata-driven design by eliminating all hardcoded configs.

## Constraints

- Must run within the insert transaction (no gaps, no duplicates under concurrent writes)
- Must support TiDB's `SELECT ... FOR UPDATE` locking semantics
- Must not break existing Quote auto-numbering during migration
- Must resolve cross-record field references (e.g., Account.BillingCountryCode via Opportunity)
- Frontend is the API specification — backend changes must preserve all existing field contracts

## Premises

1. Unify the two systems into one metadata-driven engine
2. Template engine resolves cross-record field references at record creation time
3. Per-prefix sequence scoping (derived from fully-rendered template before SEQ token)
4. Transactional guarantees — no gaps, no duplicates
5. **Revised (per Codex challenge):** Separate rule metadata (`_System_IdentifierRule`) from counter state (`_System_IdentifierSeq` — new table, NOT a rename of `_System_CompoundAutoNumber`)
6. Must fix now — P1 blocking

## Approaches Considered

### Approach A: Unified Identifier Engine (CHOSEN)

Template strings like `{Account.BillingCountryCode}-CSA-{YYMMDD}{SEQ:2}` stored in `_System_IdentifierRule`. One `generateIdentifiers()` function replaces both `generateAutoNumbers()` and `CompoundAutoNumberListener`.

- Effort: M (3-5 days)
- Risk: Low
- Pros: one code path, human-readable templates, zero code changes for new objects
- Cons: multi-hop field resolution needs careful design, must migrate Quote without breaking

### Approach B: Config-Driven Listener

Keep `CompoundAutoNumberListener` architecture, load configs from DB as JSON instead of hardcoding. Same Go structs, just stored differently.

- Effort: S (1-2 days)
- Risk: Low
- Pros: fastest to ship, existing code is battle-tested
- Cons: two code paths remain, JSON config less readable, not truly extensible

### Approach C: Expression-Based DSL

Full mini expression language with functions (UPPER, LEFT, FORMAT_DATE) and conditional logic.

- Effort: L (1-2 weeks)
- Risk: Medium
- Pros: maximum flexibility
- Cons: YAGNI, parser complexity, harder to validate

## Recommended Approach

**Approach A: Unified Identifier Engine.** Best balance of clean design and shipping speed. One code path, human-readable templates, fixes #555 immediately.

### Data Model

#### `_System_IdentifierRule` (NEW table)

| Column | Type | Description |
|--------|------|-------------|
| Id | VARCHAR(255) PK | UUID |
| ObjectApiName | VARCHAR(255) NOT NULL | Target object (e.g., "Contract") |
| RuleName | VARCHAR(255) NOT NULL | Human label (e.g., "Contract Business ID") |
| Template | VARCHAR(1024) NOT NULL | Format string: `{Account.BillingCountryCode}-CSA-{YYMMDD}{SEQ:2}` |
| TargetFields | JSON NOT NULL | Fixed-key map (see TargetFields Specification below) |
| FieldLookups | JSON NOT NULL | Traversal definitions for cross-record refs (see below) |
| IsActive | BOOLEAN DEFAULT true | Enable/disable without deleting |
| CreatedDate | DATETIME | |
| LastModifiedDate | DATETIME | |

**Uniqueness enforcement:** TiDB does not support partial/filtered unique indexes. Enforce "at most one active rule per object" in Go code: before activating a rule, query for existing active rules on the same ObjectApiName and deactivate them. Add a regular unique index on `(ObjectApiName, RuleName)` to prevent duplicate rule names.

#### `_System_IdentifierSeq` (NEW table — does NOT rename `_System_CompoundAutoNumber`)

| Column | Type | Description |
|--------|------|-------------|
| Id | VARCHAR(255) PK | Composite string: `{RuleId}:{ScopeKey}` (matches existing `_System_CompoundAutoNumber` pattern) |
| RuleId | VARCHAR(255) NOT NULL | FK to _System_IdentifierRule |
| ScopeKey | VARCHAR(512) NOT NULL | Fully rendered prefix (e.g., `GB-CSA-260416`) |
| CurrentNumber | INT DEFAULT 0 | Counter for this scope |
| CreatedDate | DATETIME | |
| LastModifiedDate | DATETIME | |

**Unique constraint:** (RuleId, ScopeKey) — secondary index for lookups.

**Note (per eng review):** `Id` is a composite string `{RuleId}:{ScopeKey}`, NOT a UUID. This matches the existing `_System_CompoundAutoNumber` pattern (`ObjectAPIName:Prefix`) and works directly with `repo.GetLock(table, id)` — no repo layer changes needed. Practical scope keys like `GB-CSA-260416` are ~15 characters, so the composite stays well under VARCHAR(255).

### TargetFields Specification

`TargetFields` uses a **fixed set of keys** with field API names as values. The engine matches keys by name in Go code:

| Key | Description | Example Value |
|-----|-------------|---------------|
| `prefix` | Stores the scope key (fully rendered template with `{SEQ:n}` stripped) | `"AutoNumberText__c"` |
| `seq` | Stores the raw sequence number as integer | `"AutoNumber__c"` |
| `code` | Stores the full assembled identifier string | `"Contract_Number__c"` |
| `name` | Stores the full identifier into the object's Name field | `"Name"` |

All keys are optional. If a key is absent, that target field is not set. The Go struct:

```go
type IdentifierTargetFields struct {
    Prefix string `json:"prefix,omitempty"` // scope key field
    Seq    string `json:"seq,omitempty"`    // raw sequence number field
    Code   string `json:"code,omitempty"`   // full assembled code field
    Name   string `json:"name,omitempty"`   // record Name field
}
```

### Template Token Specification

| Token | Example | Resolution |
|-------|---------|------------|
| Literal text | `-CSA-` | Passed through as-is |
| `{FieldName}` | `{BillingCountryCode}` | Direct field on the record being created |
| `{Parent.Field}` | `{Account.BillingCountryCode}` | One-hop lookup: read FK, query parent, read field |
| `{Parent.Parent.Field}` | `{Opportunity.Account.BillingCountryCode}` | Multi-hop: chain FK traversals |
| `{YYMMDD}` | `260416` | Date component from record's CreatedDate (or NOW() if not yet set) |
| `{SEQ:n}` | `{SEQ:2}` | Zero-padded scoped sequence number, n = padding width |
| `{SEQNUM:n}` | `{SEQNUM:8}` | Simple global sequence (for Salesforce-compat `{00000000}` patterns) |

**Scope key derivation:** Fully render all field and date tokens, then strip the `{SEQ:n}` token. The resulting string IS the scope key.

Example: template `{Account.BillingCountryCode}-CSA-{YYMMDD}{SEQ:2}` with country=GB, date=260416 produces scope key `GB-CSA-260416`. The sequence counter row is locked by `(RuleId, "GB-CSA-260416")`.

**`{SEQNUM:n}` scope key:** When the template uses `{SEQNUM:n}` (global sequential), scope key is `{ObjectApiName}:{FieldApiName}` to match existing `_System_AutoNumber` semantics where each (object, field) pair has its own counter.

### Token Resolution Failures

Since identifiers are business-critical primary keys, token resolution failures must be handled predictably:

| Token Type | Failure Scenario | Behavior |
|------------|-----------------|----------|
| `{Field}` / `{Parent.Field}` | FK is null or target record not found | Use fallback value `"XX"` (matches existing CompoundAutoNumberListener behavior) |
| `{Parent.Parent.Field}` | Any hop in the chain returns null | Try next chain in FieldLookups. If all chains fail, use `"XX"` |
| `{YYMMDD}` | Record has no date field set | Use `time.Now()` in UTC |
| `{SEQ:n}` / `{SEQNUM:n}` | Counter row doesn't exist | Create it with CurrentNumber=0, then increment (upsert pattern) |

Log a warning when fallback values are used. Never abort the insert — a record with `XX-CSA-260416` is better than a failed save.

### Template Validation (per eng review — V1 scope)

Validate templates at rule save time (bootstrap seed or future admin UI). The parser should reject:
- Unclosed `{` brackets
- Unknown token types (not `{Field}`, `{Parent.Field}`, `{YYMMDD}`, `{SEQ:n}`, `{SEQNUM:n}`)
- Field tokens that have no corresponding entry in FieldLookups JSON
- `{SEQ:n}` or `{SEQNUM:n}` with non-numeric or zero padding width

Validation runs the template through the parser in "dry-run" mode (no DB access, no counter increment). Returns a list of validation errors. Invalid templates are rejected before saving to `_System_IdentifierRule`.

### FieldLookups JSON Structure

For cross-record field resolution, the rule stores traversal chains:

```json
{
  "Account.BillingCountryCode": {
    "chains": [
      [
        {"fkField": "OpportunityId", "targetTable": "Opportunity", "targetField": "AccountId"},
        {"fkField": "AccountId", "targetTable": "Account", "targetField": "BillingCountryCode"}
      ],
      [
        {"fkField": "AccountId", "targetTable": "Account", "targetField": "BillingCountryCode"}
      ]
    ]
  }
}
```

Multiple chains = fallback strategy (try primary path first, fall back to secondary).

### Template ↔ FieldLookups Mapping (per eng review)

Template tokens like `{Account.BillingCountryCode}` do NOT encode the full resolution strategy. The FieldLookups JSON is the authority for HOW to resolve a field reference. The mapping works as follows:

1. Parser extracts token `{Account.BillingCountryCode}` from template
2. Engine looks up `FieldLookups["Account.BillingCountryCode"]` to find the resolution chains
3. Engine also checks for a direct field matching the token (e.g., `Account_Country_Code__c` — a formula field)
4. Resolution order: **direct field first → chains in order → "XX" fallback**

This means the Quote rule's FieldLookups must include a `directField` key:

```json
{
  "Account.BillingCountryCode": {
    "directField": "Account_Country_Code__c",
    "chains": [
      [{"fkField": "OpportunityId", "targetTable": "Opportunity", "targetField": "AccountId"},
       {"fkField": "AccountId", "targetTable": "Account", "targetField": "BillingCountryCode"}],
      [{"fkField": "AccountId", "targetTable": "Account", "targetField": "BillingCountryCode"}]
    ]
  }
}
```

The `directField` is optional. If present, it is checked first (may be a formula field already populated on the record). If absent or null, resolution starts at the first chain.

**Regression requirement:** The resolution behavior MUST exactly match the existing `resolveCountryCode()` + `walkChain()` in `compound_autonumber_listener.go`. The existing `lookupStep` struct should be reused (with json tags added) rather than creating a new struct.

### Integration Point (per eng review)

The new engine uses **direct function calls** from `persistence_service.go` and `persistence_bulk_insert.go`, NOT the EventBus. This replaces the current event-driven pattern where `CompoundAutoNumberListener` subscribes to `RecordBeforeCreate`.

**Execution ordering:** `generateIdentifiers()` MUST be called BEFORE `publishRecordEvent(RecordBeforeCreate)` so that before-create flows can access the generated Name field. This matches existing Salesforce behavior where auto-numbers are populated before triggers fire.

### Rule Metadata Caching (per eng review)

`_System_IdentifierRule` is metadata (template definitions), NOT counter state. It changes only when an admin modifies rules. Rule metadata is cached in `MetadataService` alongside existing `AutoNumber` metadata — consistent caching strategy.

Counter state (`_System_IdentifierSeq.CurrentNumber`) is ALWAYS read from DB within the transaction. Never cached.

### Engine Flow

#### Single Record Insert

```
1. Record INSERT request arrives
2. Look up active _System_IdentifierRule for this object
   (via MetadataService cache — same as existing AutoNumber metadata)
3. If no active rule → skip (object has no identifier rule)
4. If rule uses {SEQNUM:n} only → simple sequential mode:
   a. Lock _System_IdentifierSeq row for (RuleId, ObjectApiName:FieldApiName)
   b. Increment and format
5. If rule uses {SEQ:n} or field refs → compound mode:
   a. Resolve all {Field} and {Parent.Field} tokens via FieldLookups chains
      (apply fallback "XX" on resolution failure)
   b. Resolve all date tokens from record data or time.Now()
   c. Render scope key = fully resolved template with {SEQ:n} stripped
   d. Acquire counter row: compose Id = `{RuleId}:{ScopeKey}`, then use existing
      `repo.GetLock(table, id)` pattern (same as current CompoundAutoNumber).
      If row doesn't exist, insert with CurrentNumber=0, then re-lock.
   e. Increment CurrentNumber, format with zero-padding
   f. Assemble full identifier string
6. Write to TargetFields:
   - prefix field ← scope key
   - seq field ← raw sequence number (int)
   - code field ← full identifier string
   - name field ← full identifier string
7. Continue with INSERT (within same transaction)
```

#### Bulk Insert

The engine exposes a `GenerateIdentifiersBatch(ctx, rule, records []map[string]interface{})` function for the bulk insert path in `persistence_bulk_insert.go`. It:

1. Resolves all field/date tokens for each record
2. Groups records by scope key
3. For each scope key group, within the same insert transaction: upsert counter row (same `INSERT ... ON DUPLICATE KEY UPDATE` pattern), then `SELECT ... FOR UPDATE`, then `UPDATE CurrentNumber = CurrentNumber + N` to allocate a contiguous block of N numbers in one statement
4. Assigns sequential numbers from the allocated block to each record in the group
5. This matches the existing `generateAutoNumbersBatch()` optimization — one lock per scope key, not per record

**Bulk FK resolution cache (per eng review):** When resolving `{Parent.Field}` tokens for a batch, use an in-transaction cache keyed by `(targetTable, fkValue)`. If 100 Contracts all reference the same Account, the Account is queried once, not 100 times. Cache lives only for the duration of the batch call — no cross-transaction staleness risk.

### Seeded Rules (Bootstrap)

#### Quote (compound)
```
Template: {Account.BillingCountryCode}-QT-{YYMMDD}{SEQ:2}
TargetFields: {"prefix": "AutoNumberText__c", "seq": "AutoNumber__c", "code": "Quote_No__c", "name": "Name"}
FieldLookups: {
  "Account.BillingCountryCode": {
    "directField": "Account_Country_Code__c",
    "chains": [
      [{"fkField": "OpportunityId", "targetTable": "Opportunity", "targetField": "AccountId"},
       {"fkField": "AccountId", "targetTable": "Account", "targetField": "BillingCountryCode"}],
      [{"fkField": "AccountId", "targetTable": "Account", "targetField": "BillingCountryCode"}]
    ]
  }
}
```

#### Contract (compound)
```
Template: {Account.BillingCountryCode}-CSA-{YYMMDD}{SEQ:2}
TargetFields: {"prefix": "AutoNumberText__c", "seq": "AutoNumber__c", "code": "Contract_Number__c", "name": "Name"}
FieldLookups: {
  "Account.BillingCountryCode": {
    "chains": [
      [{"fkField": "AccountId", "targetTable": "Account", "targetField": "BillingCountryCode"}]
    ]
  }
}
```

#### Order (compound)
```
Template: {Account.BillingCountryCode}-ORD-{YYMMDD}{SEQ:2}
TargetFields: {"prefix": "AutoNumberText__c", "seq": "AutoNumber__c", "code": "Order_Number__c", "name": "Name"}
FieldLookups: {
  "Account.BillingCountryCode": {
    "chains": [
      [{"fkField": "AccountId", "targetTable": "Account", "targetField": "BillingCountryCode"}]
    ]
  }
}
```

**Note on simple auto-number objects:** In V1, objects that use only simple sequential auto-numbers (e.g., Case.CaseNumber with `{00000000}`) continue using the existing `generateAutoNumbers()` code path and `_System_AutoNumber` table. These are NOT migrated to `_System_IdentifierRule` yet. The unification happens in V2 after the compound engine is proven. This avoids a risky big-bang migration and keeps the blast radius small.

### Migration Strategy

1. Add `_System_IdentifierRule` and `_System_IdentifierSeq` as NEW tables in `system_tables.json`
2. Seed Quote/Contract/Order identifier rules in bootstrap (platform_config.go)
3. Write a data-spec (`data-specs/migrate-compound-autonumber-state.md`) to copy existing `_System_CompoundAutoNumber` rows into `_System_IdentifierSeq` with translated IDs:
   - Old ID: `Quote:SG-QT-260330` → New: UUID, RuleId=quote-rule-id, ScopeKey=`SG-QT-260330`
   - **Critical:** scope keys must match exactly so counters don't reset
4. Deploy with `CompoundAutoNumberListener` disabled (remove subscription in `service_manager_init.go`)
5. New `generateIdentifiers()` runs for Quote/Contract/Order within `persistence_insert.go`
6. Existing `generateAutoNumbers()` continues running for all other objects (no change)
7. Validate in staging: create Quote, verify identical behavior to old listener
8. **Rollback:** If staging validation fails, re-enable the `CompoundAutoNumberListener` subscription and investigate. The old `_System_CompoundAutoNumber` data is untouched (we copied, not moved), so rollback is safe.
9. After validation: delete `CompoundAutoNumberListener` code and old `_System_CompoundAutoNumber` table in follow-up PR

**Cutover is a feature flag, not parallel execution.** The old listener is removed before the new engine goes live. There is no window where both run simultaneously.

**Deployment constraint (per eng review):** This migration requires **single-instance deployment** (Docker container swap), NOT rolling deployment. If old and new instances overlap, they write to different counter tables and may produce duplicate identifiers. NexusCRM's current deployment is single-instance swap, which satisfies this constraint.

**Counter sync at deploy time (per eng review):** The migration data-spec copies counter state from `_System_CompoundAutoNumber` to `_System_IdentifierSeq`. If any records are created between the data-spec execution and the new code deployment, the copied counters may be stale. The new engine's startup path should re-read `MAX(CurrentNumber)` from the old table for each migrated scope key and update `_System_IdentifierSeq` if the old table has a higher value. This is a one-time safety check that runs on first boot after migration.

## Open Questions

1. **RecordType-based type codes:** The current Contract pattern uses "CSA" for TiDB Cloud Services Agreement. Should the type code segment come from RecordType metadata (e.g., a `TypeCode__c` field on RecordType) or stay hardcoded in the template? If metadata-driven, we need a `{RecordType.TypeCode__c}` reference.
2. **Date source:** Should `{YYMMDD}` always use `CreatedDate`/`time.Now()`, or should the rule allow specifying a source field (e.g., `ContractStartDate` for Contracts)? V1 uses `time.Now()`. Can add a `DateSourceField` column to `_System_IdentifierRule` in V2.
3. **Backfill:** Should existing records with empty AutoNumberText be backfilled? If so, what sequence numbers do they get?
4. **Admin UI timeline:** When does the React admin screen for managing identifier rules need to ship?
5. **V2 unification timeline:** When should simple auto-number objects (Case, etc.) be migrated from `_System_AutoNumber` to `_System_IdentifierRule`?

## Success Criteria

- [ ] Issue #555 resolved: new Contracts get `AutoNumberText` like `GB-CSA-260416` with sequence `01` → full code `GB-CSA-26041601`
- [ ] Existing Quote auto-numbering continues working identically (same scope keys, same counter values)
- [ ] Order auto-numbering works (new seeded rule)
- [ ] Zero code changes required to add compound auto-numbering to a new object
- [ ] Concurrent insert test: 50 parallel inserts, no duplicates, no gaps
- [ ] Bulk insert test: 100 records in one batch, correct sequential numbering per scope key
- [ ] `go build ./...` and `go test ./...` pass
- [ ] `CompoundAutoNumberListener` code deleted (compound path unified)
- [ ] Data-spec for counter state migration passes dry-run validation

## Distribution Plan

Backend-only change. Deploys via existing CI/CD pipeline (Docker image build + AWS deployment). No new artifacts to distribute.

## Dependencies

- TiDB `SELECT ... FOR UPDATE` row locking (already used by existing system)
- Bootstrap system (`cmd/setup apply`) for seeding rules and new tables
- Data-spec for migrating `_System_CompoundAutoNumber` state into `_System_IdentifierSeq`

## Pre-Implementation Checklist

Before building, validate the migration contract by checking existing counter state in staging:

```sql
SELECT * FROM `_System_CompoundAutoNumber` ORDER BY ObjectApiName, Prefix;
```

The new system must produce **identical scope keys** to existing `Prefix` values so counters don't reset. Mismatched scope keys will cause duplicate identifiers.

## Eng Review Summary (2026-04-17)

Review of this design doc via `/plan-eng-review` with Codex outside voice.

### Decisions Made
1. **Integration:** Direct call from persistence layer, not EventBus (explicit ordering)
2. **PK strategy:** `_System_IdentifierSeq.Id` = composite string `{RuleId}:{ScopeKey}` (not UUID)
3. **Rule caching:** Via MetadataService (consistent with existing AutoNumber metadata)
4. **{SEQNUM:n}:** Implemented in V1 (pre-pave for V2 unification)
5. **Template ↔ FieldLookups:** Added `directField` key and explicit mapping rules
6. **Deployment:** Single-instance swap required (no rolling deploy)
7. **Flow ordering:** generateIdentifiers() before BeforeCreate flows
8. **Template validation:** V1 scope (not deferred)
9. **Batch FK cache:** V1 scope (in-transaction cache for repeated lookups)

### Codex Outside Voice Findings (resolved)
- PK strategy clarified (composite string, not real composite PK)
- DSL expressiveness gap fixed (added directField + explicit mapping)
- Rolling deploy constraint documented
- Deadlock retry unnecessary (TransactionManager already handles it)
- Flow ordering explicitly documented

### Critical Gaps to Address in Implementation
1. Template validation — reject invalid templates at save time
2. Scope key matching — migration must produce identical scope keys to existing Prefix values
3. Counter sync at deploy — safety check on first boot after migration