# Design: Autonumber Engine v3 — Full Replacement of `_System_AutoNumber` + `CompoundAutoNumberListener`

**Status:** DRAFT — Round 3.1 (Round-3 dual-model plan-review fix pack applied inline). Round-3 Pass A returned PASS-WITH-FIXES, Pass B FAIL on 2 P1 + 3 P2; all discharged below.
**Date:** 2026-04-17
**Author:** amber
**Branch:** `docs/autonumber-engine-v2-prd`
**Supersedes v2:** `docs/design/autonumber-engine-v2.md` (commit `e2802267`). v2 FAILed Round 2 Pass B (Codex) with 2 P0s + 6 P1s; this v3 discharges all of them while preserving every Round-1 P0 discharge.
**Also supersedes:** `docs/design/unified-identifier-engine.md` (DEFERRED — see §9).
**Replaces runtime components:**
- `backend/internal/features/automation/compound_autonumber_listener.go` (compound Quote/Contract/Order path)
- `backend/internal/features/records/persistence_service.go:864-916` — `generateAutoNumbers` (standard `{0…}`-only path)
- `backend/internal/features/records/persistence_bulk_insert.go:319` — `generateAutoNumbersBatch` (bulk-insert variant; was missed in v2 inventory)
- `backend/internal/bootstrap/schema.go:1715+` — `ensureStandardAutoNumbers` (bootstrap registration of `_System_AutoNumber` rows; missed in v2)
- `backend/internal/features/metadata/field_create.go:192` — `UpsertAutoNumber` admin-creation hook (missed in v2)
- `backend/internal/features/automation/identifier_engine*.go` (partially-implemented unified engine)
- `_System_AutoNumber`, `_System_CompoundAutoNumber`, `_System_IdentifierRule`, `_System_IdentifierSeq` (tables)
- `backend/internal/controlplane/packs/platform_config.go:329-435` — `IdentifierRuleSeeds()` + `applyIdentifierRules()`

**Primary research input:** `/Users/amber/claude/nexuscrm-data-sync/docs/sf-autonumber-spec.md`

---

## 0. What changed vs v1 (executive diff)

v1 scoped the engine to the compound listener only (Quote/Contract/Order) and used a four-phase migration with a Phase-1→Phase-2 staleness window. Round-1 reviewers flagged four P0s which v2 discharged: out-of-scope `_System_AutoNumber`, seed→engine staleness window, log-and-skip on unparseable rows, and outer-txn-held `FOR UPDATE` hot-row starvation + cross-pod clock skew. Locked Round-1 decisions: **A2** (wide scope — replace both listener and `_System_AutoNumber`), **B3** (single cutover — one PR, no dual-write, no flag), **C** (structural fixes — DB wall-clock in reservation txn, fail-closed on unparseable rows, short reservation txn decoupled from caller, complete legacy inventory, single canonical seed source). These are LOCKED; v3 does not re-open them.

## 0.5 Delta vs v2 (≤250 words)

Round-2 review FAILed v2 with 2 P0s + 6 P1s. v3 discharges all.

**P0-R2-A — RecordType.DeveloperName semantic mismatch.** v2 assumed `{RecordType.DeveloperName}` = `"CSA"`; actual stored value is human-readable `"Services Agreement"`. v3 adopts **Approach α**: new column `_System_RecordType.RecordTypeCode VARCHAR(16)`, seeded from the 9-entry keyword→code map (`compound_autonumber_listener.go:262-296`). Token becomes `{RecordType.RecordTypeCode}`. Zero coupling with other `DeveloperName` consumers. §4.5, §6.3, §8.5.

**P0-R2-B — First-use livelock.** v2's `INSERT … ON DUPLICATE KEY … tx.Rollback()` undid the INSERT, retry loop looped. v3 adopts **Approach X + Y belt-and-suspenders**: seed runner pre-seeds a row for every configured `(field_id, scope_key)` at cutover (primary); runtime row-missing branch commits its own-txn UPSERT (defensive, only legitimately hits `per_day` new-day rollover). §5.2, §5.3.

**P1-R2-A cross-record read isolation** — per-token `readStrategy` JSON field, default `caller_txn`. §3.5, §4.1, §12.2 Q.
**P1-R2-B fallback pseudo-code + thundering herd** — inline fallback body; wider jitter + per-counter semaphore; metrics. §5.2.
**P1-R2-C generated-seed CI drift** — `verify-generated-seeds` CI check, banner comment. §7.4, §11.
**P1-R2-D rollback spec ships in cutover PR** — `status: draft-rollback`; ops flips to `pending`. §7.3.
**P1-R2-E fail-closed on runtime row deletion** — `ErrCounterMissing` except `per_day` new-day. §3.4, §5.3.3.
**P1-R2-F legacy inventory** — `generateAutoNumbersBatch`, `ensureStandardAutoNumbers`, `UpsertAutoNumber` added; CI grep. §7.1.2, §11.

P2 fixes: JSON canonicalization, real flow fixture path (`backend/internal/features/automation/testdata/regression_fixtures/`, `flow_scenario_e2e_test.go`, `flow_regression_test.go:119`), LOC budget, per-day seed test #23b.

## 0.6 Delta vs v3 (Round-3 fix pack applied inline — v3.1)

Round-3 plan-review raised 0 P0, 5 P1, 7 P2 across both passes. All discharged as inline edits (no architectural rework):

- **P1-R3-admin-bypass (§5.6)** — raw-SQL format edits bypassed verify-gate. Fix: metadata-cache reload re-parses + re-validates; failure quarantines the field (`ErrRuleQuarantined`); optional DB-side trigger behind a flag.
- **P1-R3-rollback-approval (§7.3)** — "flip `draft-rollback`→`pending`" was a single-PR merge. Fix: env-scoped status transitions + required 2-person approval + mandatory `incident_ticket:` frontmatter; `data-spec-apply` pre-flight refuses spec without all three.
- **P1-R3-RT-ordering (§7.2 step b.5)** — `RecordTypeCode` seed depends on `_System_RecordType` rows existing. Fix: explicit dependency assertion + abort condition; phase ordering pinned to phase5_auto_numbers after phase4_standard_packs.
- **P1-R3-cycle (§6.2 rule 8)** — `fkChains` had no cycle-detection. Fix: terminal-step must be scalar non-FK; same `targetObject` twice in one chain rejected at parse time.
- **P1-R3-LOC (§7.2, §7.4)** — budget was tight with zero margin. Fix: Phase-0 spike dry-run LOC measurement + explicit <2500/2500-3000/>3000 thresholds + documented split boundary.
- **P2-R3-same-sample (§5.2 fallback)** — LOGIC BUG in fallback pseudo-code: `nowDBSample` called twice (once outside txn for semaphore key, once inside for scope_key); midnight rollover broke serialization. Fix: sample ONCE inside the txn, use one value for both counterKey and scopeKey; acquire semaphore AFTER the sample.
- **P2-R3-semaphore-leak (§5.0)** — per-counter semaphore map grew unboundedly for `per_day` scope. Fix: LRU with cap `max(512, 4×|fields|)` + 48 h TTL.
- **P2-R3-canonicalize-owner (§4.1)** — canonicalization function not named. Fix: single `autonumber.CanonicalizeTokenRefs` used by generator + runtime reader + J1 test; drift blocked by V2 verify.
- **P2-R3-runbook (§11 Phase 2)** — no incident runbook. Fix: three-item runbook (`ErrCounterMissing`, `ErrRuleQuarantined`, thundering-herd alert).
- **P2-R3-per-day-pagination (§8.2 step 3.f)** — per-day seed transaction boundary unspecified. Fix: 10-year ceiling + 200-row batch + GET_LOCK release-reacquire per batch.
- **P2-R3-split-contradiction (§1.3, §7.2 LOC budget)** — cutover grep-clean acceptance conflicted with "split frontend to follow-up PR" escape clause. Fix: split acceptance into cutover-PR (backend grep-clean) and cleanup-PR (frontend grep-clean), 48 h max window; named fallback-safety conditions for the frontend staying untouched until cleanup.
- **P2-R3-skill (§7.2 step l)** — `.agent/skills/data-spec-apply/SKILL.md` amendment was out-of-scope-ambiguous. Fix: explicit step (l) adds the skill edit to the cutover PR with named owners in PR description.

---

## 1. Goals + Non-Goals

### 1.1 Goals

1. **Goal 1 — SF-fidelity autonumber.** Ship a metadata-driven engine matching observed Salesforce behavior for all 129 autonumber fields in the survey (`sf-autonumber-spec.md` §2, §3.1, §3.2, §3.3) and for all Quote/Contract/Order formats that `CompoundAutoNumberListener` currently produces (`compound_autonumber_listener.go:79-122`, `:252-315`).

2. **Goal 2 — Full replacement of BOTH `_System_AutoNumber` path AND `CompoundAutoNumberListener`.** At cutover, the following are deleted in one PR:
   - `backend/internal/features/automation/compound_autonumber_listener.go` + subscription in `service_manager_init.go:123`.
   - `backend/internal/features/records/persistence_service.go:864-916` (`generateAutoNumbers`) and its call site at `persistence_insert.go:139`.
   - `backend/internal/features/records/persistence_bulk_insert.go:319` (`generateAutoNumbersBatch`) — bulk-insert variant missed in v2.
   - `backend/internal/bootstrap/schema.go:1715+` (`ensureStandardAutoNumbers`) — bootstrap registration hook missed in v2.
   - `backend/internal/features/metadata/field_create.go:192` (`UpsertAutoNumber` call) — admin field-create hook missed in v2.
   - `backend/internal/features/automation/identifier_engine*.go` (partial unified-engine code).
   - Tables `_System_AutoNumber`, `_System_CompoundAutoNumber`, `_System_IdentifierRule`, `_System_IdentifierSeq`.
   - `IdentifierRuleSeeds()` + `applyIdentifierRules()` in `platform_config.go:329-435`.
   - Frontend reader branches listed in §7.1.

3. **Goal 3 — Per-field configuration** via extension of `_System_Field`. Config is metadata, not code. Zero code changes to add a new autonumber field.

4. **Goal 4 — Concurrency-safe counter assignment** via an atomic short-lived optimistic UPDATE on `_System_AutoNumberCounter`, decoupled from the caller's outer transaction. The counter reservation transaction commits in milliseconds; the caller's INSERT transaction is free to run long without holding a hot row.

5. **Goal 5 — Monotonic migration** from all three legacy counter sources. `SeedAutoNumberCounters` computes each field's `next_value` as `GREATEST(live_column_max + 1, legacy.CurrentNumber + 1, compound.counter_state + 1, field.AutoNumberStart)`. No re-numbering of existing records.

6. **Goal 6 — Cross-record + taxonomy tokens** resolve correctly for records created via the new engine. `{Account.BillingCountryCode}` on Contract creates resolves by loading the referenced Account via the configured `readStrategy` (see §3.5). `{RecordType.RecordTypeCode}` resolves via the record's `RecordTypeId` lookup against the new `_System_RecordType.RecordTypeCode` column (see §4.5). Both fail-closed on missing references; parse-time validation rejects unknown targets.

7. **Goal 7 — Single-cutover deployment.** One PR ships everything. Fresh-DB bootstrap works. Non-empty envs (staging, integration) are migrated by the one-shot runner inside the same deployment. No dual-write, no feature flag, no rolling-deploy exposure.

8. **Goal 8 — First-use livelock-free (new in v3, fixes P0-R2-B).** Every configured `(field_id, scope_key)` has a pre-seeded counter row at cutover. Runtime only creates new rows for legitimate lazy scopes (`per_day` new-day rollover), via an own-txn UPSERT — never via rollback-and-retry.

### 1.2 Non-Goals (YAGNI — anchored to A2/B3/C locked scope)

- **No transitive lookup chains of depth > 1** first-class. `{Account.BillingCountryCode}` is allowed (Contract → Account, one hop). `{Opportunity.Account.BillingCountryCode}` is **not** a first-class token — but §3.5's `fkChains` fallback-list carries observed depth-2 listener behavior. Depth ≥ 3 rejected.
- **No computed-field tokens.** Referencing a formula field directly as a token target is rejected; the resolved value must be a stored column.
- **No time-of-day tokens** (`{HH}`, `{mm}`, `{ss}`) — SF does not support them; the 129-field survey does not use them; excluded.
- **No cross-object shared counters.** Each `(field_id, scope_key)` owns its own counter row.
- **No free-list / gap-filling on rollback.** Counter increments are durable; gaps are permanent. Matches SF platform behavior (`sf-autonumber-spec.md:78-80`, §2.3). See §5.7.
- **No `regenerate_all` runtime in v2.** `auto_number_on_change` reserves the enum; the actual offline op is a separate design.
- **No admin UI in v2.** Config is set via bootstrap seeds and direct `_System_Field` UPDATE.
- **No auto-backfill for net-new fields.** An admin adding an autonumber field to an object with existing rows will see NULL on legacy rows; only inserts from cutover forward get values.
- **No string-transform token grammar.** Approach γ for RecordType (a `{RecordType.DeveloperName:contractTypeCode}` transform) was considered and rejected; adds a parser sub-grammar for a one-shot use case. Approach α (dedicated column) chosen instead.

### 1.3 Success Criteria

- [ ] One PR lands end-to-end: engine + parser + renderer + schema + seed runner + call-site rewiring + listener deletion + `_System_AutoNumber` deletion + identifier-engine deletion + frontend reader rewire + `_System_RecordType.RecordTypeCode` column + keyword-map seed.
- [ ] Fresh-DB bootstrap produces working autonumbers for all 129+ configured fields with zero manual intervention. Pre-seeded counter rows eliminate runtime first-use livelock (Goal 8).
- [ ] Staging cutover: the one-shot migration seeds `Account.Account_ID__c` counter to `next_value >= 3968` (derived from observed max `IBG-B-20260417-003967`, `sf-autonumber-spec.md:52`); the next INSERT produces `IBG-B-<today>-003968`.
- [ ] For each legacy compound field (Quote/Contract/Order), the first post-cutover insert continues the counter from `GREATEST(live_max+1, legacy_counter_next)` — verified by test matrix §10 case 12.
- [ ] 100 concurrent goroutines × 100 inserts each against the hottest counter produce 10,000 unique contiguous counter values with zero duplicates and zero deadlocks (test §10 case 18).
- [ ] `go build ./...` + `go test -race ./...` pass on the cutover PR.
- [ ] CI `verify-generated-seeds` check passes (§7.4, §11).
- [ ] CI grep check finds zero hits for deleted legacy symbols (§11).
- [ ] Local Deployment Verification Report (三件套) posted on the cutover PR.
- [ ] Frontend: `agent-browser` walkthrough of Account/Contract/Quote create+view+edit. Screenshots under `evidence/autonumber-v2/`.
- [ ] Grep-clean post-merge: no `CompoundAutoNumberListener`, `_System_AutoNumber`, `AutoNumberText__c`, `IdentifierRule`, `generateAutoNumbers`, `UpsertAutoNumber`, `ensureStandardAutoNumbers`, `generateAutoNumbersBatch` in `backend/` or `frontend/`. **Split note (P2-R3-split-contradiction)**: if the cutover PR is split per §7.2 LOC rule, backend grep-clean is a cutover-PR gate; frontend grep-clean is a cleanup-PR gate (cleanup PR merged within 48 h). Both gates must eventually pass; see §7.2 split rule.

---

## 2. Glossary

| Term | Meaning |
|---|---|
| **Rule** | Per-field autonumber configuration: format, start, scope, date source, on-change policy, cross-record resolvers. One rule per field, 1:1. Stored on `_System_Field`. |
| **Field** | A `_System_Field` row with `auto_number_format IS NOT NULL`. The physical column lives on the object's table. |
| **Scope** | Bucketing dimension for the counter. `global` (one counter per field) or `per_day` (one counter per `(field, UTC YYYYMMDD)`). |
| **Scope key** | `VARCHAR(16)` serialization of the scope dimension. `''` (empty string) for `global`; `YYYYMMDD` for `per_day`. |
| **Counter row** | A row in `_System_AutoNumberCounter` keyed by `(field_id, scope_key)`. Holds `next_value` — the integer that the next assignment will render. |
| **displayFormat** | Format string stored in `_System_Field.auto_number_format`. E.g. `IBG-B-{YYYY}{MM}{DD}-{000000}`, `{Account.BillingCountryCode}-CSA-{YY}{MM}{DD}-{00}`. |
| **Assigned-at** | `UTC_TIMESTAMP(6)` sampled from the DB inside the counter reservation transaction. Single source of truth for date tokens AND scope-key computation. |
| **Reservation txn** | A short `BEGIN / SELECT UTC_TIMESTAMP(6) / UPDATE … WHERE … AND next_value = ? / COMMIT` on `_System_AutoNumberCounter`. Runs on a dedicated connection. Commits in milliseconds. Independent of the caller's outer INSERT transaction. |
| **Caller txn** | The outer insert transaction opened by `persistence_service.RunInTransaction`. The engine is invoked inside it but does NOT hold any autonumber row lock during its life. |
| **Cross-record token** | `{<ObjectApiName>.<FieldApiName>}` — at render time, follow the record's FK to the referenced object and read the named field. One hop only (or depth-2 via `fkChains` fallback). |
| **Taxonomy token** | `{RecordType.RecordTypeCode}` — canonicalizes at parse time to a cross-record token rooted on the record's `RecordTypeId`, targeting the new `_System_RecordType.RecordTypeCode` column (§4.5). |
| **readStrategy** | Per-token JSON field (§4.1): `caller_txn` (default) reads in the caller's outer txn — sees same-txn writes; `fresh_read` reads on a separate short-lived connection — always committed data. |
| **Token refs** | JSON-decoded pre-parsed list of `{tokenLiteral, targetObject, targetField, fkChains, readStrategy, directField}` stored on `_System_Field.auto_number_token_refs` so the hot path does not re-parse. |

---

## 3. API Surface

### 3.1 Backend assignment function

```go
// Package autonumber
// File: backend/internal/features/automation/autonumber/engine.go (NEW)

// Engine renders autonumber values for records being INSERTed.
// All methods are called INSIDE the caller's outer transaction; the
// engine opens its OWN short-lived reservation connection for counter
// increment. Cross-record lookups use the caller txn for
// readStrategy=caller_txn tokens, or e.freshReadDB for fresh_read.
type Engine interface {
    // AssignOne renders a single autonumber for (fieldID, sourceRecord).
    AssignOne(ctx context.Context, fieldID string, sourceRecord models.SObject) (string, error)

    // AssignBatch renders N contiguous autonumbers for one field on N source
    // records. Groups by scope_key + cross-record resolution fingerprint so
    // each group gets one reservation. Used by persistence_bulk_insert.go.
    AssignBatch(ctx context.Context, fieldID string, sourceRecords []models.SObject) ([]string, error)
}
```

### 3.2 Caller integration (single cutover)

Three call sites in `backend/internal/features/records/`:

- `persistence_insert.go:139` — REPLACE `generateAutoNumbers(txCtx, objectName, data)` with iteration over all autonumber fields resolved from metadata: `engine.AssignOne(txCtx, fieldID, data)`. Ordering: runs **before** `publishRecordEvent(RecordBeforeCreate)` at `persistence_insert.go:155`, matching SF observable ordering.
- `persistence_bulk_insert.go:319` — REPLACE `generateAutoNumbersBatch(ctx, objectName, records)` with a loop over each autonumber field calling `engine.AssignBatch(ctx, fieldID, records)`. The grouping logic moves into the engine.
- `persistence_bulk_insert.go` bulk path — remove the `CompoundAutoNumberListener.GenerateBatch` call if present (absent in current HEAD per grep; confirm on implementation).

No dual-write. No feature flag. All three hooks deleted in the same commit. See §7.2.

### 3.3 Admin config path

- Extend `_System_Field` with six new columns (§4.1) including the new `auto_number_token_refs JSON`.
- Extend `_System_RecordType` with one new column `RecordTypeCode VARCHAR(16) NULL` (§4.5).
- Seed all 129+ standard-object autonumber fields and the three compound fields via `platform_config.go applyAutoNumberFormats()` (new function — the SINGLE canonical seed source, C#5). Seed the keyword→code map into `_System_RecordType.RecordTypeCode` via the same data-spec.
- The Go seed list is **generated** from `data-specs/2026-04-18-autonumber-v2-seed.md` by `scripts/gen/autonumber_seeds.go`; file banner `// Code generated by … DO NOT EDIT.` CI drift check at §7.4.
- Net-new configs or admin edits: UPDATE `_System_Field` directly; admin-field-create path (`field_create.go:192`) is REWIRED (§7.2 step k) to seed a counter row via the engine bootstrap API — NOT via the deleted `UpsertAutoNumber`.

### 3.4 Error surface

| Failure | Handling |
|---|---|
| Field has `auto_number_format = NULL` | Engine is not invoked — noop. |
| Format parse error at seed time | `cmd/setup apply` returns error; fail-closed. |
| Format parse error at runtime | Defensive: engine returns wrapped error; caller aborts INSERT with 500. Should be impossible if seed validation ran. |
| Counter row missing on first use — `global` scope | `ErrCounterMissing{FieldID, ScopeKey}`. This is a CORRUPTION signal because Approach X pre-seeds all `global` rows at cutover. Do NOT reseed from `auto_number_start`. Admin must investigate (§5.3.3 V5). |
| Counter row missing on first use — `per_day` NEW-day scope_key | Legitimate lazy creation. Engine does own-txn UPSERT with `next_value = GREATEST(auto_number_start, live_max_for_today + 1)`; retries the optimistic UPDATE. See §5.2 step 6.d. |
| Cross-record FK null | Fail-closed. `ErrTokenResolve{FieldID, TokenPath, Cause: "fk null"}`. Caller aborts. |
| Cross-record target row missing | Fail-closed with `ErrTokenResolve{Cause: "row missing"}`. |
| Cross-record target field null | Fail-closed with `ErrTokenResolve{Cause: "target null"}`. |
| Rendered value exceeds target column `MaxLength` | Parser rejects at seed time (§6.2 rule 5); runtime rejection unreachable. |
| Counter overflow (rendered counter ≥ 10^width) | `ErrCounterOverflow{FieldID, ScopeKey, Value, Width}`. Caller aborts with 500. Admin widens format (data-spec + column widening) — §12.1 R-overflow. |
| Outer INSERT txn rollback AFTER successful reservation | Counter consumes the number permanently (gap-tolerant, SF-aligned). See §5.7. |

### 3.5 Cross-record + taxonomy resolution (P1-R2-A addressed)

Cross-record tokens take the form `{<ObjectApiName>.<FieldApiName>}`. Resolution at render time:

1. From the source record's metadata schema, find the lookup field whose `ReferenceTo = <ObjectApiName>` (typically `<ObjectApiName>Id`). If multiple candidates exist, parse-time validation requires an explicit resolver entry in `auto_number_token_refs` naming the primary FK + ordered fallback chain.
2. Load the referenced row using the token's `readStrategy` (see below).
3. Read `FieldApiName` from the loaded row. If null or missing, try the next fallback chain entry; if no chain resolves, fail-closed per §3.4.
4. The `fkChains` mechanism carries depth-2 behavior (Quote → Opportunity → Account) that `compound_autonumber_listener.go:83-97` implements. Caps at depth 2; depth 3+ rejected.

**Read isolation strategy (`readStrategy` — P1-R2-A):**

Each cross-record token declares a `readStrategy` in `auto_number_token_refs`:

- **`caller_txn`** (DEFAULT): `repo.FindOne(ctx, ObjectApiName, fkValue)` inside the caller's outer txn. The reference read sees in-flight writes from the same txn. **Trade-off**: under REPEATABLE READ, the caller's snapshot may be stale if a concurrent session UPDATEd the target mid-caller-txn. Not a problem for our data shape: staging surveys show `Account.BillingCountryCode` is set at Account-create and effectively immutable (0 observed UPDATEs in a 30-day sample). This strategy supports the observed listener pattern where a Flow creates an Account + a Contract in the same outer txn and the Contract format references the just-inserted Account.
- **`fresh_read`**: opens a separate short-lived read on `e.freshReadDB` (same pool as `reservationDB` or an additional pool). Always reads committed data, never sees same-txn writes. Use when the token's target is updated concurrently by other sessions and a stale snapshot would corrupt the rendered number. **Trade-off**: breaks same-txn dependent-insert patterns — an Account INSERTed in the same outer txn is invisible, so the Contract's FK lookup fails.

**Default is `caller_txn`.** Justification: (a) the listener's observed behavior supports same-txn patterns; (b) the survey shows target fields are stable. §12.2 Q-cross-record-read-strategy asks reviewers to confirm.

**Taxonomy tokens**: `{RecordType.RecordTypeCode}` is syntactic sugar for a cross-record token where the implicit FK is `RecordTypeId`. The recommended form is `{RecordType.RecordTypeCode}` (new `RecordTypeCode` column — §4.5). Parser rejects any other `{RecordType.…}` variant unless the target column exists on `_System_RecordType`.

Cross-record lookups happen OUTSIDE the reservation txn. `caller_txn` reads use the caller's txn handle; `fresh_read` reads use `e.freshReadDB`. Neither touches `_System_AutoNumberCounter`.

---

## 4. Schema Changes

### 4.1 `_System_Field` extension — SIX new columns

```sql
ALTER TABLE `_System_Field`
  ADD COLUMN `auto_number_format`      VARCHAR(255) NULL,
  ADD COLUMN `auto_number_start`       BIGINT       NOT NULL DEFAULT 1,
  ADD COLUMN `auto_number_scope`       VARCHAR(16)  NOT NULL DEFAULT 'global',
  ADD COLUMN `auto_number_date_src`    VARCHAR(16)  NOT NULL DEFAULT 'assigned_at',
  ADD COLUMN `auto_number_on_change`   VARCHAR(16)  NOT NULL DEFAULT 'keep_existing',
  ADD COLUMN `auto_number_token_refs`  JSON         NULL;
```

`auto_number_token_refs` is the pre-parsed list of cross-record token paths. Rationale unchanged from v2: avoid hot-path re-parse; record parse-time validation; enforce depth-1 non-goal via fixed-depth JSON schema.

**JSON shape (canonicalization mandated — P2-R2-A + P2-R3-canonicalize-owner):** stored as a JSON array. Go serialization uses a single canonicalizer function `autonumber.CanonicalizeTokenRefs(v any) ([]byte, error)` (new, in `backend/internal/features/automation/autonumber/canonicalize.go`) that (a) sorts map keys lexicographically at every nesting level, (b) preserves array order as declared in the source data-spec, (c) uses no extraneous whitespace. ALL THREE callers MUST use this function — no ad-hoc `json.Marshal`:

1. **Seed generator** `scripts/gen/autonumber_seeds.go` — emits canonical bytes into the generated Go literal.
2. **Runtime reader** — on metadata cache load, verifies that the stored bytes round-trip byte-equal through `CanonicalizeTokenRefs` (detects manual DB edits that introduced drift).
3. **Test harness J1** — property test: `CanonicalizeTokenRefs(decode(CanonicalizeTokenRefs(x))) == CanonicalizeTokenRefs(x)` for fuzz-generated inputs.

Drift between any caller → `cmd/setup verify` (V2 extended) fails non-zero naming the diff.

```json
[
  {
    "directField": "Account_Country_Code__c",
    "fkChains": [
      [{"fk":"AccountId","targetField":"BillingCountryCode","targetObject":"Account"}],
      [
        {"fk":"OpportunityId","targetField":"AccountId","targetObject":"Opportunity"},
        {"fk":"AccountId","targetField":"BillingCountryCode","targetObject":"Account"}
      ]
    ],
    "readStrategy": "caller_txn",
    "targetField": "BillingCountryCode",
    "targetObject": "Account",
    "tokenLiteral": "{Account.BillingCountryCode}"
  }
]
```

The `directField` is an optional fast-path (denormalized source column; `compound_autonumber_listener.go:86`). `fkChains` is ordered; the first chain whose walk yields a non-empty value wins. `readStrategy ∈ {"caller_txn", "fresh_read"}`, default `caller_txn`.

**Enum values enforced in Go**:

- `auto_number_scope ∈ {'global', 'per_day'}`
- `auto_number_date_src ∈ {'assigned_at'}` (v2 single value; reserved for future)
- `auto_number_on_change ∈ {'keep_existing', 'regenerate_all'}` — `regenerate_all` enum-only in v2; runtime treats as `keep_existing`.

**Index strategy**: no new index. Lookups are by `_System_Field.Id` (PK).

### 4.2 `_System_AutoNumberCounter` (NEW table)

```sql
CREATE TABLE `_System_AutoNumberCounter` (
  `field_id`           VARCHAR(255) NOT NULL,
  `scope_key`          VARCHAR(16)  NOT NULL DEFAULT '',
  `next_value`         BIGINT       NOT NULL,
  `created_date`       DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `last_modified_date` DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                                          ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`field_id`, `scope_key`)
) /* DefaultTableOptions */;
```

**Lock-target semantics (C#3).** Atomic optimistic UPDATE:

```sql
UPDATE `_System_AutoNumberCounter`
   SET next_value = next_value + 1,
       last_modified_date = UTC_TIMESTAMP(6)
 WHERE field_id = ? AND scope_key = ? AND next_value = ?
```

Rows-affected = 1 → reservation succeeded; rows-affected = 0 → another writer won, retry. Bounded retry + starvation fallback (§5.2).

**DATETIME(6)**: microsecond precision for contention debugging; matches `UTC_TIMESTAMP(6)` sampling.

**No FK to `_System_Field`**: bootstrap ordering may race; referential integrity checked at seed time.

**Table name registration**: `TableAutoNumberCounter = "_System_AutoNumberCounter"` added to `shared/pkg/constants/z_generated_tables.go` (generator input: `system_tables.json`).

### 4.3 Columns on object tables (no change)

Target columns (`Account.Account_ID__c`, `Contract.Name`, `Quote.Quote_No__c`, etc.) already exist as `VARCHAR(N)` with `N ≥` max rendered length. v2 does NOT create target columns from autonumber metadata — that is handled by the column-reconcile path at `bootstrap/schema.go:143`.

### 4.4 Legacy tables — DROPPED at cutover

Dropped in the cutover PR's `data-specs/` entry (§8.3), AFTER backup:

- `_System_AutoNumber`
- `_System_CompoundAutoNumber`
- `_System_IdentifierRule`
- `_System_IdentifierSeq` (if present; some envs never populated it)

Legacy per-object columns `<Obj>_Prefix__c`, `<Obj>_Number_Sequence__c` are **retained** (external SF-org consumers). They stop receiving writes; existing values preserved. Future rollout-notice for deletion.

### 4.5 `_System_RecordType.RecordTypeCode` — NEW column (P0-R2-A, Approach α)

v2's `{RecordType.DeveloperName}` assumption is wrong: staging `_System_RecordType.DeveloperName` holds human-readable names like `"Services Agreement"`, not contract codes. v3 adds a dedicated column:

```sql
ALTER TABLE `_System_RecordType`
  ADD COLUMN `RecordTypeCode` VARCHAR(16) NULL;
```

**Rationale for Approach α** (dedicated column) over Approach β (overwrite `DeveloperName`) or Approach γ (parse-time string-transform token):

- **β — overwrite `DeveloperName`**: `DeveloperName` is a generic metadata column; grep shows `frontend/src/**` and other backend paths read it as a human-readable display string. Overwriting it would regress the UI layer. Rejected.
- **γ — token transform grammar** (`{RecordType.DeveloperName:contractTypeCode}`): forces adding a token-transform parser sub-grammar for a single-use case. Violates KISS/YAGNI. Rejected.
- **α — dedicated column**: zero coupling with other `DeveloperName` consumers, single metadata lookup at render time, explicit schema contract, trivially CI-verifiable (a seeded row must have a non-null code). **Chosen.**

**Seed data-spec** (`data-specs/2026-04-18-autonumber-v2-seed.md`, §8.5): the 9-entry keyword→code map from `compound_autonumber_listener.go:262-296` (exact-match form — substring scan is discarded; seed writes exact `DeveloperName → RecordTypeCode` pairs):

| DeveloperName (existing) | RecordTypeCode (new) |
|---|---|
| `Services Agreement` | `CSA` |
| `Services Agreement Online` | `TCSA` |
| `Customer Reference Agreement` | `CRA` |
| `Non-Disclosure Agreement` | `NDA` |
| `Partner Network Agreement` | `PNA` |
| `Payment Agreement` | `PA` |
| `Cloud POC Agreement` | `TCPA` |
| `Enterprise POC Agreement` | `TEPA` |
| `Master Agreement` | `MA` |
| (fallback for any RecordType consumed by an autonumber format but not listed) | admin must add before deploy |

Format token becomes `{RecordType.RecordTypeCode}`. `verifyAutoNumberInvariants()` V6 (new — §5.3.3) fails deploy if any RecordType referenced by any `auto_number_format` has `RecordTypeCode IS NULL`. This is the discharge vector for P0-R2-A.

**Migration path for RecordTypes added between seed and cutover**: admin adds a `data-specs/` spec that UPDATEs `_System_RecordType.RecordTypeCode` before activating the referencing format. Enforced by verify-gate.

---

## 5. Engine Pseudo-Code

All signatures illustrative (Go-flavored pseudo-code); §5 is NOT implementation. Actual code lands in the cutover PR.

### 5.0 Dependencies & lifecycle

- `e.clock` removed. Time source is DB-side only.
- `e.metadata` exposes `GetField(fieldID) *Field` (with 6 new columns + `token_refs`), `GetRecordType(id) *RecordType` (with `RecordTypeCode`).
- `e.repoCallerTxn` — the caller's txn-bound repo; used for `readStrategy=caller_txn`.
- `e.freshReadDB` — a dedicated `*sql.DB` with a small pool (e.g. 8 conns) for `readStrategy=fresh_read`. Each read opens an implicit single-statement read.
- `e.reservationDB` — a dedicated `*sql.DB` with a small pool (e.g. 8 conns). Reservation txn runs here, NOT on the caller's DB handle.
- `e.parser` produces `[]Token` (cached per format string) and `[]CrossRecordRef` (cached per format).
- `e.renderer.Render(tokens, assignedAt, counterValue, crossValues) string`.
- `e.perCounterSemaphore` — bounded LRU map of size-1 semaphores keyed by `(field_id, scope_key)`; gates fallback entry (§5.2 step 6.f). Backed by an LRU with cap `max(512, 4 × len(configured_fields))` and TTL eviction after 48 h of idleness. For `per_day` scope this prevents unbounded growth as new `YYYYMMDD` scope_keys accumulate (would otherwise leak ~365 semaphores / year / field). Eviction of a live semaphore is safe because fallback entry is rare — a new semaphore is allocated on next use and serialization restarts from uncontended. LRU implementation: `golang.org/x/sync/syncmap` + `container/list` head-of-list reorder, or an existing util in `shared/pkg/cache/`.

### 5.1 `AssignOne`

```
func (e *engine) AssignOne(ctx, fieldID, sourceRecord) (string, error) {
    // 1. Load cached field metadata.
    f := e.metadata.GetField(fieldID)
    if f == nil             { return "", errUnknownField(fieldID) }
    if f.AutoNumberFormat == "" { return "", errNoRule(fieldID) }

    // 2. Parse (cached).
    tokens := e.parser.Tokens(f.AutoNumberFormat)
    refs   := f.AutoNumberTokenRefs  // JSON-decoded at metadata load

    // 3. Resolve cross-record tokens per each token's readStrategy.
    crossValues, err := e.resolveCrossRecords(ctx, sourceRecord, refs)
    if err != nil { return "", err }   // fail-closed (C#2 extends to runtime)

    // 4. Sample DB wall-clock AND reserve counter in a SHORT reservation txn.
    //    Runs on e.reservationDB.
    assignedAt, counterValue, err := e.reserveOne(ctx, f)
    if err != nil { return "", err }

    // 5. Overflow check.
    if overflows(counterValue, tokens.CounterWidth()) {
        return "", ErrCounterOverflow{FieldID: fieldID, Value: counterValue, Width: tokens.CounterWidth()}
    }

    // 6. Render.
    return e.renderer.Render(tokens, assignedAt, counterValue, crossValues), nil
}
```

### 5.2 `reserveOne` — short optimistic-UPDATE txn with livelock-free first-use (P0-R2-B)

```
const maxRetries = 5  // see §12.1 R3

func (e *engine) reserveOne(ctx, f) (time.Time, int64, error) {
    for attempt := 0; attempt < maxRetries; attempt++ {
        tx, err := e.reservationDB.BeginTx(ctx, nil)
        if err != nil { return zeroTime, 0, err }

        // 6.a. DB wall-clock inside reservation txn (C#1).
        var assignedAt time.Time
        if err := tx.QueryRowContext(ctx, "SELECT UTC_TIMESTAMP(6)").Scan(&assignedAt); err != nil {
            tx.Rollback(); return zeroTime, 0, err
        }

        // 6.b. Derive scope_key from assignedAt.
        scopeKey := deriveScopeKey(f.AutoNumberScope, assignedAt)

        // 6.c. Read current next_value (plain read, no FOR UPDATE).
        var currentNext int64
        err = tx.QueryRowContext(ctx,
            "SELECT next_value FROM _System_AutoNumberCounter WHERE field_id=? AND scope_key=?",
            f.ID, scopeKey,
        ).Scan(&currentNext)

        if errors.Is(err, sql.ErrNoRows) {
            // 6.d. Row missing. P0-R2-B fix.
            tx.Rollback()  // abandon THIS txn — we will create the row in its own txn.

            // Policy gate: only 'per_day' NEW-day scopes legitimately hit this
            // branch post-cutover. 'global' scopes are pre-seeded by Approach X
            // (§7.2 step c); row-missing there is CORRUPTION.
            if f.AutoNumberScope != "per_day" {
                return zeroTime, 0, ErrCounterMissing{FieldID: f.ID, ScopeKey: scopeKey}
            }

            // Own-txn UPSERT (Approach Y — defensive). Commits independently;
            // does NOT rollback. Initial next_value = max(start, live_max_today+1).
            liveMaxToday, err := e.scanLiveMaxForPerDay(ctx, f, assignedAt)
            if err != nil { return zeroTime, 0, err }
            initialNext := max(f.AutoNumberStart, liveMaxToday+1)

            ownTx, err := e.reservationDB.BeginTx(ctx, nil)
            if err != nil { return zeroTime, 0, err }
            _, err = ownTx.ExecContext(ctx,
                `INSERT INTO _System_AutoNumberCounter
                   (field_id, scope_key, next_value, created_date, last_modified_date)
                 VALUES (?, ?, ?, ?, ?)
                 ON DUPLICATE KEY UPDATE next_value = next_value`,
                f.ID, scopeKey, initialNext, assignedAt, assignedAt,
            )
            if err != nil { ownTx.Rollback(); return zeroTime, 0, err }
            if err := ownTx.Commit(); err != nil { return zeroTime, 0, err }
            continue  // retry outer loop — row now exists.
        } else if err != nil {
            tx.Rollback(); return zeroTime, 0, err
        }

        // 6.e. Optimistic UPDATE (CAS on next_value).
        res, err := tx.ExecContext(ctx,
            `UPDATE _System_AutoNumberCounter
                SET next_value = next_value + 1,
                    last_modified_date = ?
              WHERE field_id = ? AND scope_key = ? AND next_value = ?`,
            assignedAt, f.ID, scopeKey, currentNext,
        )
        if err != nil { tx.Rollback(); return zeroTime, 0, err }
        affected, _ := res.RowsAffected()
        if affected == 0 {
            tx.Rollback()
            time.Sleep(widerJitter(attempt))  // rand(0, 2^(attempt+3) ms) — see §12.1 R3
            continue
        }
        if err := tx.Commit(); err != nil { return zeroTime, 0, err }
        return assignedAt, currentNext, nil
    }

    // 6.f. Starvation fallback — short FOR UPDATE lane, gated by per-counter semaphore.
    return e.reserveOneFallbackFOR_UPDATE(ctx, f)
}

// Fallback body — runs on reservationDB; NEVER shares caller txn
// (MUST NOT reintroduce Round-1 P1-1 outer-txn-held FOR UPDATE).
//
// P2-R3-same-sample fix: `assignedAt` is sampled ONCE inside the txn and
// drives BOTH the semaphore counterKey AND the scope_key used in the
// UPDATE. A previous draft sampled the clock twice (once outside the txn
// to build counterKey, once inside to build scope_key); at midnight
// rollover the two samples could disagree, so a semaphore keyed on day N
// could fail to serialize a fallback targeting day N+1's counter row. The
// semaphore acquisition must therefore happen AFTER the sample, not
// before.
func (e *engine) reserveOneFallbackFOR_UPDATE(ctx, f) (time.Time, int64, error) {
    tx, err := e.reservationDB.BeginTx(ctx, nil)  // fresh conn, NOT caller txn
    if err != nil { return zeroTime, 0, err }
    defer tx.Rollback()  // safe: Commit below prevents this on success path

    // 1. Single clock sample — used for BOTH counterKey and scope_key.
    var assignedAt time.Time
    if err := tx.QueryRowContext(ctx, "SELECT UTC_TIMESTAMP(6)").Scan(&assignedAt); err != nil {
        return zeroTime, 0, err
    }
    scopeKey := deriveScopeKey(f.AutoNumberScope, assignedAt)
    counterKey := f.ID + "\x00" + scopeKey

    // 2. Per-counter semaphore (size 1) — thundering-herd mitigation.
    //    Acquired AFTER the sample so the key matches the scope_key used below.
    sem := e.perCounterSemaphore.Get(counterKey)
    if err := sem.Acquire(ctx, 1); err != nil { return zeroTime, 0, err }
    defer sem.Release(1)

    // 3. Short FOR UPDATE on exactly the row we will increment.
    var currentNext int64
    if err := tx.QueryRowContext(ctx,
        `SELECT next_value FROM _System_AutoNumberCounter
          WHERE field_id=? AND scope_key=? FOR UPDATE`,
        f.ID, scopeKey,
    ).Scan(&currentNext); err != nil {
        return zeroTime, 0, err   // row-missing here = CORRUPTION; bubble up
    }
    if _, err := tx.ExecContext(ctx,
        `UPDATE _System_AutoNumberCounter
            SET next_value = next_value + 1, last_modified_date = ?
          WHERE field_id=? AND scope_key=?`,
        assignedAt, f.ID, scopeKey,
    ); err != nil { return zeroTime, 0, err }
    if err := tx.Commit(); err != nil { return zeroTime, 0, err }
    return assignedAt, currentNext, nil
}
```

**Key properties:**

1. **DB wall-clock (C#1).** `UTC_TIMESTAMP(6)` in reservation txn. Cross-pod clock skew cannot mis-bucket scope keys at midnight. Same sample drives `scope_key` and `{YYYY}/{MM}/{DD}` tokens — never disagree.
2. **Short hold (C#3).** Reservation txn commits in milliseconds. Caller txn can run for seconds without holding the counter row.
3. **Rollback consumes the number.** Independent commit → caller rollback leaves `next_value` incremented. Matches SF (`sf-autonumber-spec.md:78-80`, §2.3).
4. **No first-use livelock (Goal 8 / P0-R2-B).** Approach X pre-seeds all `global` rows at cutover; runtime row-missing for `global` fails fast with `ErrCounterMissing`. Runtime row-missing for `per_day` new-day legitimately own-txn-UPSERTs and retries.
5. **Starvation fallback.** After `maxRetries=5` optimistic losses, per-counter semaphore serializes ONE fallback at a time; others keep retrying optimistically. Wider jitter (`rand(0, 2^(attempt+3) ms)`) reduces retry correlation. Metrics: histogram of attempt count; alert p99 > 3.

### 5.3 `AssignBatch`

```
func (e *engine) AssignBatch(ctx, fieldID, sourceRecords) ([]string, error) {
    f := e.metadata.GetField(fieldID)
    if f == nil || f.AutoNumberFormat == "" { return nil, err }

    tokens := e.parser.Tokens(f.AutoNumberFormat)

    // 1. Resolve cross-record per-record per-token-readStrategy.
    crossPerRecord, err := e.resolveCrossRecordsBatch(ctx, sourceRecords, f.AutoNumberTokenRefs)
    if err != nil { return nil, err }

    // 2. Bucket by (scope_key, cross-record fingerprint).
    out := make([]string, len(sourceRecords))
    for _, bucket := range groupByPrefix(sourceRecords, crossPerRecord, f.AutoNumberScope) {
        n := len(bucket.indices)

        // 3. Reserve N consecutive counters in ONE short reservation txn.
        //    reserveBatch uses the same first-use-livelock-free logic as reserveOne.
        assignedAt, startValue, err := e.reserveBatch(ctx, f, bucket.scopeKey, n)
        if err != nil { return nil, err }

        // 4. Render per record.
        for i, idx := range bucket.indices {
            counter := startValue + int64(i)
            if overflows(counter, tokens.CounterWidth()) {
                return nil, ErrCounterOverflow{FieldID: fieldID, Value: counter, Width: tokens.CounterWidth()}
            }
            out[idx] = e.renderer.Render(tokens, assignedAt, counter, crossPerRecord[idx])
        }
    }
    return out, nil
}
```

`reserveBatch` is the N-variant of `reserveOne` — single `UPDATE ... SET next_value = next_value + ? WHERE ... AND next_value = ?` with optimistic retry + same first-use rules (§5.2 step 6.d); returns `(assignedAt, startValue)`.

**Bulk cross-midnight caveat.** All N records share the same `assignedAt` + `scope_key` — deterministic by design. Bucket ordering matches output ordering regardless of midnight rollover during rendering.

### 5.3.3 `cmd/setup verify` extensions — BINDING (non-zero exit)

`verifyAutoNumberInvariants()` (new, pinned to `backend/internal/bootstrap/schema.go`, inline with `phase5_auto_numbers` at line 1259):

- **(V1)** For every `_System_Field.auto_number_format IS NOT NULL`: a `_System_AutoNumberCounter` row MUST exist for `(field_id, '')` if `scope=global`. For `scope=per_day`, no initial row is required (lazy). If `next_value < max(parsed_existing_value)` — fail (write-hole regressor).
- **(V2)** Every `auto_number_token_refs` entry resolves against current `_System_Object` + `_System_Field` metadata — target object exists, target field exists, FK field exists on source. Stale → fail.
- **(V3)** No orphan `_System_AutoNumberCounter` rows (field_id without `_System_Field`). → fail.
- **(V4)** Exactly one counter token per format (re-check, in case admin edited `auto_number_format` directly).
- **(V5, NEW — P1-R2-E)** For `scope=global`, runtime must never see row-missing. Verify invariant: the count of `_System_AutoNumberCounter` rows with `scope_key=''` equals the count of `_System_Field` rows with `auto_number_scope='global' AND auto_number_format IS NOT NULL`. Mismatch → fail.
- **(V6, NEW — P0-R2-A)** Every RecordType referenced by any `auto_number_format` token `{RecordType.RecordTypeCode}` must have `_System_RecordType.RecordTypeCode IS NOT NULL`. Null → fail (naming the RecordType id + the consuming format).

### 5.4 Format parser

Produces `[]Token` + `[]CrossRecordRef` from `displayFormat`. BNF in §6.1; validation in §6.2. Parser used in three places:

1. **Seed time** (`applyAutoNumberFormats` in `platform_config.go`): every rule parsed; `token_refs` JSON computed + stored; error aborts `cmd/setup apply` with structured message (C#2 applies to seed too).
2. **`cmd/setup verify`** (§5.3.3): re-parses every rule; re-validates FK targets; re-checks rendered-length ≤ column MaxLength; re-validates `RecordTypeCode` coverage. Non-zero exit on stale.
3. **Runtime** (engine init): parsed cached once per format string; kept in-memory.

### 5.5 Renderer

```
func Render(tokens []Token, assignedAt time.Time, counter int64, cross map[string]string) string {
    var b strings.Builder
    for _, tok := range tokens {
        switch tok.Kind {
        case LITERAL:       b.WriteString(tok.Lit)
        case YEAR_4:        fmt.Fprintf(&b, "%04d", assignedAt.Year())
        case YEAR_2:        fmt.Fprintf(&b, "%02d", assignedAt.Year() % 100)
        case MONTH_2:       fmt.Fprintf(&b, "%02d", int(assignedAt.Month()))
        case DAY_2:         fmt.Fprintf(&b, "%02d", assignedAt.Day())
        case COUNTER:       fmt.Fprintf(&b, "%0*d", tok.Width, counter)
        case CROSS_RECORD:  b.WriteString(cross[tok.RefKey])  // missing → failed earlier
        }
    }
    return b.String()
}
```

### 5.6 Rule-change semantics

Admin UPDATEs `_System_Field.auto_number_format` while counter exists:

- Counter row preserved — `next_value` continues monotonically under the new format.
- Next render uses the new format; old rendered values untouched (SF's `keep_existing`).
- Narrower counter-token width + `next_value ≥ 10^newWidth` → immediate `ErrCounterOverflow`. Admin must widen or rotate the field.
- `cmd/setup verify` re-validates parse + FK targets + `RecordTypeCode` coverage in the next `apply` cycle.

Rationale: overflow is runtime-time concern — changing the format does NOT implicitly reset the counter. Engine never auto-resets.

**Admin-bypass-verify closure (P1-R3-admin-bypass).** All `auto_number_format` / `auto_number_token_refs` mutations MUST route through `data-spec-apply` (which invokes `verifyAutoNumberInvariants` — §5.3.3 V1–V6 — before committing). Raw-SQL UPDATEs bypassing the spec path are forbidden by policy and detected by two defense-in-depth hooks:

1. **Metadata-cache reload re-parse + re-validate.** On metadata cache refresh (triggered by any `_System_Field` change event), the engine re-parses every updated format and re-populates `auto_number_token_refs`. If the parse fails or any cross-record / `RecordTypeCode` target is missing, the engine marks the field as **quarantined**: subsequent `AssignOne` for the field returns `ErrRuleQuarantined{FieldID, Reason}` (fail-closed, blocks INSERTs until the format is repaired via a proper data-spec). A metric `autonumber_quarantined_fields` fires an alert.
2. **DB-side trigger (optional, staging-validated):** a `BEFORE UPDATE` trigger on `_System_Field` rejects mutations to autonumber columns unless the session variable `@autonumber_mutation_authorized = 1` is set (set only by `data-spec-apply`). Gated behind a flag in the cutover PR; enabled if observed raw-SQL edits occur in any env.

Documented runbook entry in §11 Phase 2.

### 5.7 Rollback semantics

On caller outer-txn rollback:

- Reservation txn already committed.
- `next_value` stays incremented → rolled-back number is a permanent gap.
- No free-list, no gap-fill. Matches SF (`sf-autonumber-spec.md:78-80`, §2.3).

---

## 6. Format Grammar (BNF) + Pass/Fail Examples

### 6.1 Grammar

```
format         ::= segment+
segment        ::= literal | counter_token | date_token | cross_record_token | taxonomy_token
literal        ::= literal_char+
literal_char   ::= any_char_except_brace     ; not '{' or '}'
counter_token  ::= '{' '0'+ '}'              ; one or more '0', nothing else
date_token     ::= '{' date_name '}'
date_name      ::= 'YYYY' | 'YY' | 'MM' | 'DD'
cross_record_token ::= '{' object_api_name '.' field_api_name '}'
taxonomy_token ::= '{RecordType.' field_api_name '}'
object_api_name ::= identifier
field_api_name  ::= identifier
identifier      ::= [A-Za-z][A-Za-z0-9_]*     ; SF API name grammar
```

`taxonomy_token` parsed first (more specific than general `cross_record_token`).

### 6.2 Validation rules (seed-time + `cmd/setup verify`)

1. Exactly one `counter_token` per format (0 or ≥2 rejected).
2. Counter token width 1..18 (BIGINT fits 19; cap 18 for overflow safety).
3. Date tokens optional; each may appear 0+ times.
4. Cross-record tokens: 0 or more. Each must resolve against `_System_Object` + `_System_Field` at seed time.
5. Rendered length at `counter = 10^width - 1` + max cross-record target length (capped at column MaxLength) ≤ source record's target column MaxLength.
6. Cross-record target fields must be stored columns, not formula fields. Parser cross-checks `_System_Field.Type != 'formula'`.
7. No taxonomy token if source object has no `RecordTypeId` column.
8. No cross-record chain beyond depth 2 (see §3.5). Depth 2 allowed only via `fkChains` fallback list, not `{A.B.C}` syntax. **Cycle + terminal rules (P1-R3-cycle):** each chain's terminal step MUST target a stored non-FK scalar column; chains where the same `targetObject` appears more than once (self-reference or cycle) are rejected at parse time. Rejection surfaces as a structured seed-time error naming the offending chain. Property test #5b fuzzes random `fkChains` and asserts all cyclic / non-terminal inputs are rejected.
9. For `auto_number_scope = per_day`, the format MUST contain at least one of `{YYYY}/{YY}/{MM}/{DD}` — otherwise rendered values collide across days despite counter reset.
10. **(NEW — P0-R2-A)** Every `{RecordType.<field>}` token: the `<field>` must exist on `_System_RecordType`. For v3, only `{RecordType.RecordTypeCode}` is permitted in seeded formats (seed-time policy). Other `{RecordType.*}` tokens are syntactically legal but must target a real column.

### 6.3 Pass examples

| Format | Rendered sample (counter=1, assignedAt=2026-04-17 UTC, cross values as noted) | Use case |
|---|---|---|
| `{0000}` | `0001` | Contract.ContractNumber pattern (simple path) |
| `{00000000}` | `00000001` | Order.OrderNumber pattern (`sf-autonumber-spec.md:89`) |
| `IBG-B-{YYYY}{MM}{DD}-{000000}` | `IBG-B-20260417-000001` | Account.Account_ID__c (`sf-autonumber-spec.md:109`) |
| `{Account.BillingCountryCode}-CSA-{YY}{MM}{DD}-{00}` | `SG-CSA-260417-01` (Account.BillingCountryCode=SG) | Contract (ports `compound_autonumber_listener.go:107-122`) |
| `{Account.BillingCountryCode}-QT-{YY}{MM}{DD}-{00}` | `SG-QT-260417-01` | Quote (ports `:83-105`; depth-2 fallback `OpportunityId → AccountId → BillingCountryCode`) |
| `{Account.BillingCountryCode}-ORD-{YY}{MM}{DD}-{00}` | `SG-ORD-260417-01` | Order (ports `platform_config.go:382-392`) |
| `{RecordType.RecordTypeCode}-{YY}{MM}{DD}-{00}` | `CSA-260417-01` (RecordType.RecordTypeCode=CSA; RecordType.DeveloperName remains `"Services Agreement"` for UI) | Per-RecordType type codes — replaces `resolveContractTypeCode` (`compound_autonumber_listener.go:252-315`). P0-R2-A: token references the NEW `RecordTypeCode` column, not `DeveloperName`. |

### 6.4 Fail examples (rejected at seed time)

| Format | Failure mode |
|---|---|
| `AUTO-` | No counter token |
| `{0000}-{0000}` | Two counter tokens |
| `{YYYY}` | No counter token |
| `{HH}-{0000}` | Unknown date token `{HH}` |
| `{Account.Unknown}-{0000}` | `Account.Unknown` missing in `_System_Field` |
| `{NoSuchObject.Field}-{0000}` | `NoSuchObject` missing in `_System_Object` |
| `{Account.Name}-{0000}` on Contract where Contract has no `AccountId` FK | Parser cannot find FK path |
| `{Account.SomeFormula__c}-{0000}` | Target is formula field (§6.2 rule 6) |
| `{0000000000000000000}` | Counter width 19 exceeds cap 18 |
| `{}` or `{` | Empty / unclosed brace |
| `{RecordType.Name}` on a source object with no `RecordTypeId` | §6.2 rule 7 |
| `{RecordType.DeveloperName}` when `_System_RecordType` does have `DeveloperName` but some referenced RecordTypes have `RecordTypeCode IS NULL` at verify time | §5.3.3 V6 (admin must seed the code) — accepted at parse, rejected at verify |
| Any format that doesn't parse cleanly | Reject |

### 6.5 Cross-record null policy

Fail-closed on null FK / null target / missing row. SF survey shows `Account.BillingCountryCode` is non-null in all 3,281 sampled `Account_ID__c` records (`sf-autonumber-spec.md:115`). Historical inserts would not have been denied. The `fkChains` fallback-list allows admins to declare a `directField` fast-path (`Account_Country_Code__c`; `compound_autonumber_listener.go:86`). Discussed in §12.2 Q-null-policy.

---

## 7. Single-Cutover Migration (B3)

ONE PR. No v1-style phases. Phase 0 is a read-only prep step before merging. Phase 1 ships everything. Phase 2 is a 1-week soak + backup cleanup.

### 7.1 Legacy inventory (complete — addresses Pass A P0-1, C#4, P1-R2-F)

#### 7.1.1 Tables

| Table | Used by | Staging rows (2026-04-17) | Action |
|---|---|---|---|
| `_System_AutoNumber` | `persistence_service.go:864` (`generateAutoNumbers`), `persistence_bulk_insert.go:319` (`generateAutoNumbersBatch`), `field_create.go:192` (`UpsertAutoNumber`), `schema.go:1715+` (`ensureStandardAutoNumbers`) | ~129 rows | Read in §8 seed; DROP after backup. |
| `_System_CompoundAutoNumber` | `compound_autonumber_listener.go:319-366` | ~N rows (one per (object,prefix)) | Read in §8 seed; DROP after backup. |
| `_System_IdentifierRule` | `platform_config.go:396-435` (`applyIdentifierRules`; partially wired) | 3 rows | Superseded by `_System_Field.auto_number_format`; DROP after backup. |
| `_System_IdentifierSeq` | `identifier_engine*.go` | 0..N | DROP after backup. |

#### 7.1.2 Runtime components (expanded for P1-R2-F)

| Component | File:line | Action at cutover |
|---|---|---|
| Standard-autonumber runtime (single) | `backend/internal/features/records/persistence_service.go:864-916` (`generateAutoNumbers`) + `formatAutoNumber` helper | Delete function. Replace caller at `persistence_insert.go:139` with `engine.AssignOne` iteration. |
| **Standard-autonumber runtime (bulk — NEW, missed in v2)** | `backend/internal/features/records/persistence_bulk_insert.go:319` (`generateAutoNumbersBatch`) | Delete function. Replace caller with `engine.AssignBatch` iteration per autonumber field. |
| **Bootstrap standard-autonumber registration (NEW, missed in v2)** | `backend/internal/bootstrap/schema.go:1715+` (`ensureStandardAutoNumbers` — UPSERTs `_System_AutoNumber` rows for QuoteNumber/CaseNumber/OrderNumber/ContractNumber) | Delete function. Replaced by `applyAutoNumberFormats` + `SeedAutoNumberCounters` pipeline (§7.2 steps b+c). The four standard fields are included in the generated seed list. |
| **Runtime field-create registration (NEW, missed in v2)** | `backend/internal/features/metadata/field_create.go:192` (`UpsertAutoNumber` call) | Replace with call to new engine bootstrap API: on admin-create of an autonumber field, seed one `_System_AutoNumberCounter` row for `(field_id, scope_key='')` with `next_value = field.AutoNumberStart`. Delete `UpsertAutoNumber` repo method. |
| Compound listener | `backend/internal/features/automation/compound_autonumber_listener.go` (full file) + `_test.go` | Delete files. Remove subscription at `backend/internal/application/services/service_manager_init.go:123-124`. |
| Identifier-engine partial code | `backend/internal/features/automation/identifier_engine.go`, `identifier_engine_test.go`, `identifier_engine_integration_test.go`, `identifier_engine_regression_test.go` | Delete all four files. |
| Seed call in platform pack | `backend/internal/controlplane/packs/platform_config.go:329-435` (`IdentifierRuleSeeds()` + `applyIdentifierRules()`) | Delete both. Replace with `applyAutoNumberFormats()` (single canonical seed source — C#5). |
| Bootstrap phase | `backend/internal/bootstrap/schema.go:1259-1263` (`runPhase("phase5_auto_numbers", ensureStandardAutoNumbers)`) | Rewire `phase5_auto_numbers` to call `SeedAutoNumberCounters` after `applyAutoNumberFormats`. |
| Frontend reader — record display | `frontend/src/core/utils/recordUtils.ts:54` (`record.AutoNumberText__c` branch) | Delete line; fall-through to `Contract_Number__c` / `Name` suffices. Test `recordUtils.test.ts:98` updated. |
| Frontend reader — locked-after-create | `frontend/src/features/record/components/MetadataRecordForm.tsx:17-21` | Remove `AutoNumberText__c` from Quote/Contract/Order locked sets. Keep `Name`, `Quote_No__c`, `Contract_Number__c`, `OrderNumber`. |
| Frontend form tests | `frontend/src/features/record/components/MetadataRecordForm.test.tsx:429, 493, 587, 630, 936, 966` | Update fixtures — drop `AutoNumberText__c`. |
| Metadata test fixtures | `backend/internal/features/metadata/metadata_objects_identifier_test.go` | Rename or delete — engine no longer populates `AutoNumber__c` / `AutoNumberText__c`. |
| Platform pack tests | `backend/internal/controlplane/packs/platform_config_test.go:60-62` | Assert `_System_Field.auto_number_format` population instead of `_System_IdentifierRule`. |

**CI grep check (P1-R2-F)**, enforced in §11 Phase-1 acceptance:

```bash
rg -n 'UpsertAutoNumber|ensureStandardAutoNumbers|generateAutoNumbersBatch|generateAutoNumbers\b' backend/ frontend/
# Must return zero hits (except docs/*).
```

#### 7.1.3 External consumers (not in repo)

Out-of-repo: SF org reports, saved searches, dashboards may reference `AutoNumberText__c` + `<Obj>_Prefix__c`. These columns are PRESERVED (existing values untouched) for the 1-week soak; future rollout-notice will schedule deprecation as a separate work item.

### 7.2 Cutover sequence (inside the one PR, in this order)

```
(a) Add v2 tables + columns.
    - ReconcileTableColumns handles _System_Field's 6 new columns (bootstrap/schema.go:143).
    - ReconcileTableColumns handles _System_RecordType's new RecordTypeCode column (§4.5).
    - CREATE TABLE _System_AutoNumberCounter via phase0 table reconcile.
    - Regenerate shared/pkg/constants/z_generated_{tables,fields}.go from system_tables.json.

(b) Seed _System_Field.auto_number_format + auto_number_token_refs for ALL configured fields.
    - Single canonical source: data-specs/2026-04-18-autonumber-v2-seed.md (C#5).
    - Go seed list GENERATED from that spec by scripts/gen/autonumber_seeds.go into
      backend/internal/controlplane/packs/platform_config_autonumber_formats.go.
      File banner: // Code generated by scripts/gen/autonumber_seeds.go from
      data-specs/2026-04-18-autonumber-v2-seed.md. DO NOT EDIT.
    - platform_config.applyAutoNumberFormats() UPSERTs rows.

(b.5) Seed _System_RecordType.RecordTypeCode for the 9-entry keyword→code map (§4.5, §8.5).
      DEPENDENCY ASSERTION (P1-R3-RT-ordering): step (b.5) MUST run AFTER the
      RecordType bootstrap pack that populates _System_RecordType rows. Runner
      aborts with structured error if `SELECT COUNT(*) FROM _System_RecordType`
      = 0 while any seeded format in step (b) references a {RecordType.*}
      token. On fresh-DB bootstrap the RecordType pack is already in
      phase4_standard_packs; step (b.5) runs inside phase5_auto_numbers,
      after phase4. Tests 17c (NEW) + 三件套 Multi-state (a) empty-DB case
      exercise this ordering.

(c) Pre-seed _System_AutoNumberCounter for every configured field, scope=global
    (Approach X — P0-R2-B primary defense).
    - One row per _System_Field with auto_number_format IS NOT NULL AND auto_number_scope='global'.
    - next_value = GREATEST(auto_number_start, live_max + 1, legacy.CurrentNumber + 1,
                            compound.counter_state + 1) — all four floors.
    - per_day fields: no row inserted at cutover (lazy; row created on first INSERT per new day).
    - Fail-closed on unparseable rows (C#2).
    - Runs inside SeedAutoNumberCounters (§8) under GET_LOCK('autonumber_cutover', 600).

(d) Wire engine into persistence_insert.go:139 and persistence_bulk_insert.go:319
    REPLACING generateAutoNumbers / generateAutoNumbersBatch / CompoundAutoNumberListener in SAME commit.
    - Call-site moves BEFORE publishRecordEvent(RecordBeforeCreate) (preserved ordering).

(e) Unsubscribe & delete CompoundAutoNumberListener.
    - Remove lines 123-124 of backend/internal/application/services/service_manager_init.go.
    - rm compound_autonumber_listener.go + _test.go.

(f) Delete identifier_engine*.go (4 files).

(g) Delete _System_AutoNumber, _System_CompoundAutoNumber, _System_IdentifierRule,
    _System_IdentifierSeq (after backup — §7.3).

(h) Delete legacy Go code.
    - generateAutoNumbers + formatAutoNumber in persistence_service.go:864-916.
    - generateAutoNumbersBatch in persistence_bulk_insert.go:319.                                (P1-R2-F)
    - IdentifierRuleSeeds / applyIdentifierRules in platform_config.go:329-435.
    - ensureStandardAutoNumbers in schema.go:1715+ (superseded by applyAutoNumberFormats +         (P1-R2-F)
       SeedAutoNumberCounters).
    - UpsertAutoNumber repo method + its call site in field_create.go:192.                        (P1-R2-F)

(i) Rewire frontend readers (§7.1.2 rows 7-8).

(j) Update tests (§7.1.2 rows 9-11).

(k) Rewire admin-path field_create.go to seed counter row via engine bootstrap API
    (replacement for deleted UpsertAutoNumber).                                                   (P1-R2-F)

(l) Amend .agent/skills/data-spec-apply/SKILL.md and its applier logic to:                        (P2-R3-skill)
    - treat status: draft-rollback as skip,
    - enforce env-scoped status transitions (see §7.3 approval gate),
    - enforce incident_ticket frontmatter on any rollback spec.
    Include the skill edit in THE SAME cutover PR. Named PR-description owner:
    autonumber-module lead + data-spec-apply skill owner.
```

Steps (a)-(c) must run before (d) in a single `runBootstrapMutationUnderLock` to guarantee no request reaches the new engine before the counter table is seeded. Ordering enforced in the replaced `schema.go:1259-1263`.

**LOC budget (P2-R2-C + P1-R3-LOC + P2-R3-split-contradiction):** expected cutover-PR diff is 1800-2500 LOC including deletions. Explicit threshold gates:

- **< 2500 LOC** → single PR, §7.2 steps (a)-(l) all land together.
- **2500-3000 LOC** → single PR still preferred; if reviewers push back, split deletions per the rule below.
- **> 3000 LOC** → escalate to human; do NOT silently split without re-reviewing acceptance criteria.

**Split rule — resolves the LOC-budget vs acceptance-criteria contradiction flagged by Pass B.** If the PR MUST split, the boundary is:

- **Cutover PR (part 1)**: §7.2 steps (a)-(h), (k), (l). ALL backend + schema changes land here, INCLUDING the legacy table DROPs and deletion of all backend call sites. Frontend (§7.1.2 rows 8-10 + their tests) stays untouched if and only if the frontend still compiles and renders safely without the deleted backend fields. Specifically: `frontend/src/core/utils/recordUtils.ts:54` must already have a null-safe fallback (it does — the branch reads an optional field and falls through on undefined); `MetadataRecordForm.tsx:17-21` must handle missing locked-field entries gracefully (it does — the set is consulted for presence, not required). Evidence: `agent-browser` smoke test on Contract / Quote / Account create with backend post-cutover must render correctly with the unmodified frontend.
- **Cleanup PR (part 2)**: §7.2 steps (i), (j) — frontend readers + form tests. Merged within 48 h of part 1. `§1.3` acceptance criteria are therefore SPLIT: "Grep-clean post-merge in backend/" is a cutover-PR gate; "Grep-clean post-merge in frontend/" is a cleanup-PR gate. Cutover PR description explicitly declares the deferred frontend-cleanup follow-up issue number.

**Pre-PR measurement (P1-R3-LOC):** add to Phase-0 checklist (§7.4): spike a dry-run cutover on a throwaway branch, run `git diff --stat main... | tail -1` to record the LOC number, and attach the measurement to the real cutover PR description. This catches overshoot before review time.

### 7.3 Rollback plan — revert + restore (P1-R2-D: ships IN cutover PR)

The rollback spec **ships in the cutover PR** with YAML frontmatter `status: draft-rollback`. The `data-spec-apply` tooling is updated in the cutover PR to treat `draft-rollback` as skip (see `.agent/skills/data-spec-apply/SKILL.md` amendment). Ops triggers rollback by flipping status to `pending` via a 1-line hotfix PR; this avoids the cycle where an incident response requires merging a brand-new PR during an outage.

1. **Backup step (cutover pre-drop — §7.2 step (g) depends on this).** `data-specs/2026-04-18-autonumber-v2-seed.md` includes a `backup:` section:
   ```sql
   CREATE TABLE `_System_AutoNumber__bak_20260418` AS SELECT * FROM `_System_AutoNumber`;
   CREATE TABLE `_System_CompoundAutoNumber__bak_20260418` AS SELECT * FROM `_System_CompoundAutoNumber`;
   CREATE TABLE `_System_IdentifierRule__bak_20260418` AS SELECT * FROM `_System_IdentifierRule`;
   CREATE TABLE `_System_IdentifierSeq__bak_20260418` AS SELECT * FROM `_System_IdentifierSeq`;
   ```
2. **Revert code**: `git revert <merge_commit>`.
3. **Schema restore**: `data-specs/2026-04-19-autonumber-v2-rollback.md` (ships in cutover PR, `status: draft-rollback`). Restoring ops:
   ```sql
   DROP TABLE `_System_AutoNumberCounter`;
   ALTER TABLE `_System_Field` DROP COLUMN `auto_number_format`,
                               DROP COLUMN `auto_number_start`,
                               DROP COLUMN `auto_number_scope`,
                               DROP COLUMN `auto_number_date_src`,
                               DROP COLUMN `auto_number_on_change`,
                               DROP COLUMN `auto_number_token_refs`;
   ALTER TABLE `_System_RecordType` DROP COLUMN `RecordTypeCode`;
   CREATE TABLE `_System_AutoNumber`          AS SELECT * FROM `_System_AutoNumber__bak_20260418`;
   CREATE TABLE `_System_CompoundAutoNumber`  AS SELECT * FROM `_System_CompoundAutoNumber__bak_20260418`;
   CREATE TABLE `_System_IdentifierRule`      AS SELECT * FROM `_System_IdentifierRule__bak_20260418`;
   CREATE TABLE `_System_IdentifierSeq`       AS SELECT * FROM `_System_IdentifierSeq__bak_20260418`;
   ```
4. **Re-start backend**: restored `generateAutoNumbers` reads `_System_AutoNumber.CurrentNumber`; `_System_CompoundAutoNumber` resumes for Quote/Contract/Order.
5. **Counter-drift correction**: records inserted between cutover merge and revert got v3-engine values; restored `_System_AutoNumber.CurrentNumber` lags. Mitigation: before re-activating legacy path, the rollback spec runs `UPDATE _System_AutoNumber SET CurrentNumber = <max_observed_new_value>` per field from the live column. Documented in rollback spec.

**Trigger mechanism (P1-R2-D + P1-R3-rollback-approval):** ops activates rollback by flipping the rollback spec's YAML frontmatter. Three guards, all required:

1. **Env-scoped status** — valid transitions are `draft-rollback` → `pending-staging` → `pending-prod`. The spec is env-pinned; flipping straight to `pending-prod` is rejected by `data-spec-apply` if the same spec hasn't yet applied cleanly to staging in the same calendar day.
2. **Required 2-person approval** — the flip PR requires `CODEOWNERS`-enforced review from (a) one engineer on the autonumber module owners list AND (b) one ops/SRE lead. The flip PR body MUST include a populated template:
   ```
   Incident ticket: <INC-NNN link — required>
   Observed symptom: <one line>
   Staging dry-run result: <link to the staging apply log>
   Approval: @engineer-name @sre-name
   ```
3. **`data-spec-apply` pre-flight check** — the tooling refuses to apply any rollback spec whose frontmatter lacks an `incident_ticket:` field matching `INC-\d+`, or whose `status` names an env different from `$NEXUSCRM_ENV`.

Accidental trigger is catastrophic (DROPs `_System_AutoNumberCounter`, restores 4 legacy tables, re-activates deleted Go code via revert). These three guards — env pinning, 2-person approval, incident-ticket binding — make "one-line PR merge" structurally insufficient.

**Backup retention**: 2 weeks. After clean soak, dropped via `data-specs/2026-05-02-autonumber-v2-backup-cleanup.md`.

### 7.4 Pre-cutover checklist (Phase 0 — 2 hours)

- [ ] Backup spec `2026-04-18-autonumber-v2-seed.md` drafted + reviewed (includes `backup:` of all four legacy tables + RecordType code seed §8.5).
- [ ] Rollback spec `2026-04-19-autonumber-v2-rollback.md` drafted with `status: draft-rollback` (ships in cutover PR — P1-R2-D).
- [ ] Staging baseline queries captured in PR description:
  ```sql
  SELECT COUNT(*) FROM _System_AutoNumber;
  SELECT COUNT(*) FROM _System_CompoundAutoNumber;
  SELECT COUNT(*) FROM _System_IdentifierRule;
  SELECT MAX(Account_ID__c) FROM Account;  -- expect 'IBG-B-20260417-003967'
  SELECT Id, DeveloperName FROM _System_RecordType WHERE Name LIKE '%Agreement';
  ```
- [ ] **Spike dry-run LOC measurement (P1-R3-LOC)**: run the §7.2 steps (a)-(l) on a throwaway spike branch; capture `git diff --stat main... | tail -1` LOC number in the PR description. Determines whether single-PR or split-PR path applies (§7.2 LOC budget).
- [ ] `scripts/gen/autonumber_seeds.go` wired into `make generate`; generated Go file check-in'd.
- [ ] **CI drift check (P1-R2-C)**: a new CI step `verify-generated-seeds` runs:
  ```yaml
  - name: verify autonumber seed generator is not stale
    run: |
      go run scripts/gen/autonumber_seeds.go
      git diff --exit-code backend/internal/controlplane/packs/platform_config_autonumber_formats.go \
        || (echo "Generated seed file is stale vs data-specs; run 'make generate' and commit"; exit 1)
  ```
  The generated file must carry banner `// Code generated by scripts/gen/autonumber_seeds.go from data-specs/2026-04-18-autonumber-v2-seed.md. DO NOT EDIT.`
- [ ] PR description includes the Local Deployment Verification Report template (§10.3).

---

## 8. Migration — `SeedAutoNumberCounters` (C#4)

### 8.1 Data-spec fragment — `data-specs/2026-04-18-autonumber-v2-seed.md`

```yaml
---
apiVersion: data-spec/v1
id: 2026-04-18-autonumber-v2-seed
scope: staging-then-production
status: pending
tables:
  - _System_Field
  - _System_RecordType           # for RecordTypeCode seed (P0-R2-A)
  - _System_AutoNumberCounter
  - _System_AutoNumber           # read-only; dropped after
  - _System_CompoundAutoNumber   # read-only; dropped after
  - _System_IdentifierRule       # read-only; dropped after
  - _System_IdentifierSeq        # read-only; dropped after
  # all object tables with autonumber columns (read-only)
  - Account
  - Contract
  - Order
  - Quote
  - Case
  # …all 129 standard-autonumber object tables…
backup:
  - _System_AutoNumber
  - _System_CompoundAutoNumber
  - _System_IdentifierRule
  - _System_IdentifierSeq
depends_on: []
---
```

SINGLE CANONICAL SOURCE (C#5). The Go file `platform_config_autonumber_formats.go` is generated from this markdown by `scripts/gen/autonumber_seeds.go` under `make generate`. CI drift check enforces freshness (§7.4).

### 8.2 Runner shape — `backend/internal/bootstrap/autonumber_seed.go` (NEW)

```go
// SeedAutoNumberCounters seeds _System_AutoNumberCounter from all three legacy
// sources plus live column scans, AND pre-seeds a counter row for every
// configured global-scope field (Approach X — P0-R2-B).
//
// Preconditions: _System_Field.auto_number_format + auto_number_token_refs
// AND _System_RecordType.RecordTypeCode are already populated (steps b+b.5).
//
// Idempotency: ON DUPLICATE KEY UPDATE next_value = GREATEST(next_value, VALUES(next_value)).
func SeedAutoNumberCounters(ctx context.Context, rawDB *sql.DB) error {
    // 1. GET_LOCK('autonumber_cutover', 600). Abort on failure.

    // 2. SELECT Id, ObjectId, ApiName, auto_number_format, auto_number_start,
    //    auto_number_scope, auto_number_token_refs
    //    FROM _System_Field WHERE auto_number_format IS NOT NULL.

    // 3. For each field:
    //    3.a. Compile regex from displayFormat:
    //         - {YYYY} → \d{4}   (non-captured)
    //         - {YY}   → \d{2}
    //         - {MM}   → \d{2}
    //         - {DD}   → \d{2}
    //         - cross-record token → .*  (non-captured; seed does not need cross value)
    //         - {0+} counter token → (\d{width})  (captured)
    //         - literal → regexp.QuoteMeta(lit)

    //    3.b. Paginate (batch 10K) over the object's target column:
    //         SELECT <target_col> FROM <object_table>
    //         WHERE <target_col> IS NOT NULL;
    //         track max parsed counter.
    //         For scope=per_day, bucket by date tokens and track max-per-day.

    //    3.c. For each row: apply regex.
    //         - Match → extract counter int; update max (global or per-day bucket).
    //         - No match → FAIL-CLOSED (C#2, Pass B P0-3). Return error naming
    //           Row.Id + raw value + field_id + format. Abort. Log-and-skip
    //           explicitly rejected.

    //    3.d. Read legacy counter state:
    //         legacyNext   = SELECT CurrentNumber FROM _System_AutoNumber
    //                        WHERE <matching-record>;  (match by ObjectApiName+FieldApiName)
    //         compoundNext = SELECT MAX(CurrentNumber) FROM _System_CompoundAutoNumber
    //                        WHERE ObjectApiName = <obj>;

    //    3.e. seedNext = GREATEST(maxParsed + 1, legacyNext + 1, compoundNext + 1,
    //                              field.AutoNumberStart).

    //    3.f. For scope=global: UPSERT ONE row (field_id, '').
    //         For scope=per_day: UPSERT ONE row PER distinct date-bucket observed
    //         in the live scan (e.g. if the target column has rows dated 2026-04-15
    //         and 2026-04-16, insert two rows with scope_key='20260415' and '20260416').
    //         Additionally — for scope=global ONLY — if no rows found in live scan
    //         AND no legacy counter exists, still insert a row with
    //         next_value = field.AutoNumberStart (Approach X — guarantees runtime
    //         never hits row-missing for scope=global).
    //
    //    (P2-R3-per-day-pagination) Pagination + txn boundary for per_day:
    //         - The per-day bucket map is bounded to 3650 buckets (10 years);
    //           seed aborts with structured error if that ceiling is exceeded
    //           (indicates pathological live data; admin decides allowlist or
    //           schema fix).
    //         - UPSERTs commit in batches of 200; GET_LOCK is released and
    //           re-acquired between batches so a slow table scan cannot starve
    //           other bootstrap ops. Batch idempotency: ON DUPLICATE KEY GREATEST
    //           keeps partial-progress re-runs safe.
    //         - No seeded field uses per_day at cutover; this code path is
    //           exercised only by test 23b until the first admin toggles per_day.
    //
    //         UPSERT SQL:
    //           INSERT INTO _System_AutoNumberCounter
    //             (field_id, scope_key, next_value, created_date, last_modified_date)
    //           VALUES (?, ?, ?, ?, ?)
    //           ON DUPLICATE KEY UPDATE
    //             next_value = GREATEST(next_value, VALUES(next_value)),
    //             last_modified_date = VALUES(last_modified_date).

    // 4. RELEASE_LOCK. Return nil.
}
```

**Fail-closed on unparseable rows (C#2).** Any unparseable row aborts the whole seed with a structured error:

```
autonumber seed: field Account.Account_ID__c (field_id=fld_…):
  row Id=a01XXXX has value "IBG-B-2021-custom-override" which does not match derived
  regex ^IBG-B-\d{8}-(\d{6})$. Fix the row (e.g. SET Account_ID__c = NULL) and re-run.
```

`log-and-skip` explicitly rejected.

### 8.3 DROP statements (AFTER SeedAutoNumberCounters succeeds, AFTER `backup:` copies verified)

```sql
DROP TABLE `_System_AutoNumber`;
DROP TABLE `_System_CompoundAutoNumber`;
DROP TABLE `_System_IdentifierRule`;
DROP TABLE `_System_IdentifierSeq`;
```

Inside the same `runBootstrapMutationUnderLock` block as the seed.

### 8.4 Idempotency verification

1. First apply: seed populates `_System_AutoNumberCounter` with N rows (= global-scope fields + per-day date-buckets observed).
2. Second apply: `ON DUPLICATE KEY UPDATE … GREATEST` keeps all counter values. `_System_AutoNumber` no longer exists → legacy-read step is `IF EXISTS`-guarded → no-op. Row counts identical.
3. Concurrency: 3 parallel `cmd/setup apply` race for `GET_LOCK('autonumber_cutover', 600)`; one wins, others block then find seed done and exit.

§10.3 Local Deployment Verification Report matrix.

### 8.5 RecordTypeCode seed (P0-R2-A)

Same data-spec `2026-04-18-autonumber-v2-seed.md` includes a "## RecordType Codes" section. Generator reads this section into a second check-in'd Go file (or the same generated file) that `applyAutoNumberFormats` calls:

```sql
-- For each exactly-named RecordType, set RecordTypeCode.
UPDATE _System_RecordType SET RecordTypeCode = 'CSA'  WHERE DeveloperName = 'Services Agreement'         AND RecordTypeCode IS NULL;
UPDATE _System_RecordType SET RecordTypeCode = 'TCSA' WHERE DeveloperName = 'Services Agreement Online' AND RecordTypeCode IS NULL;
UPDATE _System_RecordType SET RecordTypeCode = 'CRA'  WHERE DeveloperName = 'Customer Reference Agreement' AND RecordTypeCode IS NULL;
UPDATE _System_RecordType SET RecordTypeCode = 'NDA'  WHERE DeveloperName = 'Non-Disclosure Agreement'   AND RecordTypeCode IS NULL;
UPDATE _System_RecordType SET RecordTypeCode = 'PNA'  WHERE DeveloperName = 'Partner Network Agreement'  AND RecordTypeCode IS NULL;
UPDATE _System_RecordType SET RecordTypeCode = 'PA'   WHERE DeveloperName = 'Payment Agreement'          AND RecordTypeCode IS NULL;
UPDATE _System_RecordType SET RecordTypeCode = 'TCPA' WHERE DeveloperName = 'Cloud POC Agreement'        AND RecordTypeCode IS NULL;
UPDATE _System_RecordType SET RecordTypeCode = 'TEPA' WHERE DeveloperName = 'Enterprise POC Agreement'   AND RecordTypeCode IS NULL;
UPDATE _System_RecordType SET RecordTypeCode = 'MA'   WHERE DeveloperName = 'Master Agreement'           AND RecordTypeCode IS NULL;
```

Idempotent (`AND RecordTypeCode IS NULL`). `verifyAutoNumberInvariants` V6 catches any RecordType referenced by a format but missing a code.

---

## 9. Discharge of the 7 reactivation prereqs (from `unified-identifier-engine.md`)

v1 §9 had an 11-row table. Under A2 + B3, most dissolve or discharge inline:

1. **Template validation at save time** — DISCHARGED by §6.2 parser + §3.3 seed gate. Parse errors abort `cmd/setup apply` (C#2).
2. **Scope-key matching across migration & engine** — DISCHARGED by §5.2: scope_key + date tokens derive from same `assignedAt` sample. Property test §10.1 case 5a.
3. **Counter sync at deploy — safety check on first boot** — DISCHARGED by §5.3.3 V1/V5 (BINDING) + `GREATEST` in §8.2 step 3.f.
4. **Scope-key byte-equal property test** — DISCHARGED via §10.1 case 5a (UTC-only).
5. **Migration write-hole on first boot** — DISCHARGED by single-cutover: seed + engine go live in same `runBootstrapMutationUnderLock`. No staleness window. Verify-gate catches post-cutover admin edits.
6. **RecordType-based type codes** — REINSTATED in v2 via A2 + DISCHARGED in v3 via Approach α: new `_System_RecordType.RecordTypeCode` column (§4.5), seeded by data-spec (§8.5), validated by §5.3.3 V6. The `contractTypeCodeForName` keyword map from `compound_autonumber_listener.go:262-296` ports into `_System_RecordType.RecordTypeCode` — keyword-substring scan is discarded in favor of exact-match on the pre-seeded code column.
7. **Date-source flexibility (CreatedDate / custom field)** — DEFERRED to v3 (the post-v3 release — naming collision acknowledged).
8. **Auto-backfill for existing records** — DEFERRED.
9. **Admin UI** — DEFERRED.
10. **Unification with simple autonumber** — DISCHARGED by A2. Single engine handles both.
11. **Rolling-deploy constraint** — DISSOLVED by B3.

Original "reactivation-prereq classification" obsolete under v2. `unified-identifier-engine.md` archived with `SUPERSEDED BY autonumber-engine-v2.md` header in the cutover PR.

---

## 10. Test Matrix

### 10.1 Unit tests (`backend/internal/features/automation/autonumber/`)

| # | Case | Type | Covers |
|---|---|---|---|
| 1 | Basic render — `{0000}` + counter=1..999 → `0001`..`0999` | unit | SF §5.4.1 |
| 2 | Date render — `IBG-B-{YYYY}{MM}{DD}-{000000}` + counter=3968 + assignedAt=2026-04-18 → `IBG-B-20260418-003968` | unit | SF §5.4.2 |
| 3 | Per-day scope — day N → `001`/`002`; day N+1 → `001` (scope_key transition) | unit (injected DB clock) | SF §5.4.3 |
| 4 | Global scope across days — counter monotonic across YYYYMMDD; matches `sf-autonumber-spec.md:44-56` | unit | SF §2.1 |
| 5 | Parser validation — reject every row in §6.4 | unit table-driven | §6.2/§6.4 |
| 5a | Property test — 1000 random assignedAt × scope=per_day: derived scope_key byte-equals format-rendered date segment | unit (property-based) | Prereq #4 |
| 6 | Counter overflow — render at `counter = 10^width` → structured error; no truncation | unit | SF §5.4.8 |
| 7 | Rendered length > target MaxLength at seed time → parser rejects | unit | §6.2 rule 5 |
| 8 | Cross-record happy path — `{Account.BillingCountryCode}-CSA-{YYMMDD}{00}` on Contract with populated AccountId → `SG-CSA-260417-01` | unit (mocked repo) | §3.5, §6.3 row 4 |
| 8a | **(NEW — P1-R2-A)** Cross-record same-txn visibility — Account INSERT + Contract INSERT in one caller txn; `readStrategy=caller_txn` renders new Account's BillingCountryCode | unit + real DB | §3.5 default |
| 8b | **(NEW — P1-R2-A)** Cross-record fresh-read — concurrent session UPDATEs Account.BillingCountryCode mid-txn; `readStrategy=fresh_read` sees new value | unit + real DB | §3.5 alternate |
| 9 | Cross-record null FK → `ErrTokenResolve{Cause:"fk null"}` | unit | §3.4 |
| 10 | Cross-record missing target row → `ErrTokenResolve{Cause:"row missing"}` | unit | §3.4 |
| 11 | Fallback chain — primary null, fallback succeeds | unit | §3.5, §4.1 JSON |
| 12 | Depth-2 chain (Quote → Opportunity → Account) resolves correctly | unit | §6.3 row 5 |
| 13 | Taxonomy token `{RecordType.RecordTypeCode}` resolves via `RecordTypeId` lookup against new column | unit | §6.3 row 7, P0-R2-A |
| 14 | Parser rejects depth-3+ chains | unit | §1.2 non-goal |
| 15 | Parser rejects formula-field target | unit | §6.2 rule 6 |
| 16 | Format rule change — counter preserved across format UPDATE | unit + DB | §5.6 |
| 17 | First-use race (scope=global, pre-seeded): two concurrent reserveOne → distinct values, no livelock | unit (simulated concurrency) | §5.2 P0-R2-B |
| 17a | **(NEW — P0-R2-B, per_day midnight rollover)** per_day scope with existing day-N row; first request at day N+1 → own-txn UPSERT creates `scope_key='<N+1>'` row; two concurrent first-insertions collapse to one row via `ON DUPLICATE KEY` | unit + DB | §5.2 step 6.d |
| 17b | **(NEW — P0-R2-B, global scope corruption)** Manually DELETE a `scope=global` counter row; next runtime reserveOne returns `ErrCounterMissing`, does NOT silently reseed | unit + DB | §3.4, §5.3.3 V5 |
| J1 | **(NEW — P2-R2-A)** JSON canonicalization — `canonicalize(encode(decode(encode(x)))) == encode(x)` for a fuzz-generated set of `token_refs` values | unit (property) | §4.1 |

### 10.2 Integration tests (real TiDB, `-race`)

| # | Case | Covers |
|---|---|---|
| 18 | Concurrent assignment — 100 goroutines × 100 records → 10,000 unique contiguous values, zero duplicates | SF §5.4.4; C#3 |
| 19 | Bulk insert — `AssignBatch(fieldID, 1000 records)` → 1000 contiguous values, single reservation | SF §5.4.5 |
| 20 | Rollback consumes number — outer txn inserts 5, rolls back 3, commits 2; next INSERT's counter = prev+6 | SF §5.4.7; §5.7 |
| 21 | Optimistic-retry CAS under contention — deliberate conflicts; bounded retry + jitter; no deadlock | §12.1 R3 |
| 22 | Starvation fallback — force 5+ consecutive CAS losses; fallback triggers; per-counter semaphore serializes | §5.2 step 6.f |
| 22a | **(NEW — P1-R2-B thundering-herd)** 100 goroutines exhaust retries simultaneously; verify per-counter semaphore serializes fallback (max 1 FOR UPDATE holder); wider jitter observably reduces retry correlation (metric check) | integration | §5.2 step 6.f |
| 23 | Seed idempotency — run twice with live inserts between; `next_value` never regresses | §8.2 step 3.f |
| 23a | **(NEW — P1-R2-C)** Generated-seed CI drift — mutate data-spec, run `make generate`, assert generated Go file changes; assert CI step fails when generator not run | CI | §7.4 |
| 23b | **(NEW — P2-R2-D)** Per-day seed path — synthetic field seeded as `per_day` scope with rows spanning 3 UTC days; verify 3 counter rows inserted with correct per-day max | integration | §8.2 step 3.f |
| 24 | Seed FAIL-CLOSED on unparseable row — insert `Account_ID__c='weirdo-2021'`, run seed; structured error names row | §8.2 C#2 |
| 25 | Cutover seed — staging-like DB with `Account_ID__c='IBG-B-20260417-003967'`; seed → first new insert = `IBG-B-<today>-003968` | §8 |
| 26 | Cutover seed — compound path: `_System_CompoundAutoNumber.counter_state=42` + live `Contract_Number__c='SG-CSA-260417-42'`; seed → next=43 | §8.2 step 3.d |
| 27 | Per-day cross-midnight batch — `AssignBatch(500)` at 23:59:59.999; all 500 share same DB-sampled assignedAt | §5.3 caveat, C#1 |
| 28 | DB-clock sampling (C#1) — +5min app-side skew on one pod; per_day bucketing consistent (all pods agree via DB clock) | Pass B P1-2 |
| 29 | Reservation txn decoupled — slow outer INSERT (sleep 2s) does NOT block second caller's reservation | Pass B P1-1, C#3 |
| R1 | **(NEW — P0-R2-A)** RecordTypeCode resolves — insert Contract with `RecordTypeId` → `_System_RecordType.RecordTypeCode='CSA'`; format `{RecordType.RecordTypeCode}-…` renders `CSA-…` | integration | §4.5 |
| R2 | **(NEW — P0-R2-A verify)** Delete a referenced RecordType's code; `cmd/setup verify` returns non-zero naming the RecordType + consuming format | integration | §5.3.3 V6 |
| D1 | **(NEW — P1-R2-D)** Rollback spec trigger — `data-spec-apply` tooling skips `status: draft-rollback`; flipping to `pending` applies it | integration | §7.3 |

### 10.3 Local Deployment Verification Report (三件套 — mandatory for cutover PR)

| Test | Method | Pass criteria |
|---|---|---|
| **Idempotency** | Run `cmd/setup apply` 3× sequentially | `_System_Field` + `_System_AutoNumberCounter` + `_System_RecordType.RecordTypeCode` identical; 2nd/3rd runs <10% first-run time; no counter regression; DROPs run once (2nd+ no-op because tables don't exist). |
| **Concurrency** | 3 parallel `cmd/setup apply` | `GET_LOCK('nexuscrm_bootstrap_mutation', 600)` + `GET_LOCK('autonumber_cutover', 600)` serialize; all 3 exit 0; final state identical to single apply; zero duplicate counter rows. |
| **Multi-state startup** | Apply against (a) empty DB, (b) DB with pre-v2 legacy tables populated, (c) DB fully cut over | (a) creates v2 tables + counters = 1 per global-scope field (zero per_day since no rows yet); engine works immediately. (b) reads all three legacy sources, computes GREATEST, drops legacy; engine works. (c) sees counter rows present, skips seed, engine works. In all three: `verifyAutoNumberInvariants()` BINDING — non-zero exit if (V1..V6) violated. |

Three-table report posted in cutover PR description.

### 10.4 E2E tests (Playwright + `agent-browser`)

| # | Case | Covers |
|---|---|---|
| 30 | Create Account via UI → `Account_ID__c = IBG-B-<today>-<next>`. Screenshot `evidence/autonumber-v2/account-create.png`. | Cutover |
| 31 | Create Contract via UI → Name = `{Account.BillingCountryCode}-CSA-{YYMMDD}{00}` (e.g. `SG-CSA-260417-01`). RecordType=`Services Agreement` → prefix `CSA` (via `RecordTypeCode`). | Cutover + P0-R2-A |
| 32 | Create Quote (via Opportunity) → Name uses depth-2 chain. | Cross-record fallback |
| 33 | Edit Account → `Account_ID__c` read-only | Locked-edit field |
| 34 | MetadataRecordForm post-cutover: `AutoNumberText__c` not shown as locked | §7.1.2 frontend rewire |

Screenshots under `evidence/autonumber-v2/`. Required on cutover PR.

### 10.5 Flow regression (P2-R2-B: verified fixture paths)

The flow regression harness lives in TWO test files:

- **`backend/internal/features/automation/flow_regression_test.go:119`** references `testdata/regression_fixtures/` via the `regressionFixturesDir` constant (confirmed by grep at review time).
- **`backend/internal/features/automation/flow_scenario_e2e_test.go`** — the record-triggered regression tests merged in PR #1067.

These fixtures reference `Contract_Number__c`, `Quote_No__c`, `Order_Number__c` — UNCHANGED by v2. The existing 19 record-triggered flow tests + the broader regression fixture set continue to pass unmodified.

Added: one extra fixture exercising a Contract with null AccountId → expect flow fails with the cross-record resolve error (verifies fail-closed is visible to flows).

---

## 11. Phase Plan (collapses to one phase — B3)

### Phase 0 — Pre-cutover prep (2 hours, no PR — prep in branch)

- Draft `data-specs/2026-04-18-autonumber-v2-seed.md` (includes `backup:` + RecordType code seed).
- Draft `data-specs/2026-04-19-autonumber-v2-rollback.md` with `status: draft-rollback` (ships in cutover PR — P1-R2-D).
- Record staging baseline (§7.4 queries).
- Confirm `scripts/gen/autonumber_seeds.go` builds + generates check-in'd `platform_config_autonumber_formats.go`.
- Confirm CI `verify-generated-seeds` step wired up (P1-R2-C).
- Confirm `data-spec-apply` tooling amendment treats `status: draft-rollback` as skip (P1-R2-D).

### Phase 1 — Cutover PR (target: 3–5 days)

**Deliverable**: engine + parser + renderer + schema + seed runner + §7.2 steps (a)-(k) in ONE PR.

**Acceptance criteria**:

- [ ] `_System_Field` has 6 new columns; `_System_RecordType` has `RecordTypeCode`; `_System_AutoNumberCounter` exists.
- [ ] `platform_config.applyAutoNumberFormats()` UPSERTs all 129+ field configs from generated list.
- [ ] `applyAutoNumberFormats` also seeds `_System_RecordType.RecordTypeCode` for the 9-entry map.
- [ ] `SeedAutoNumberCounters` seeds correctly for:
  - [ ] `Account.Account_ID__c` → `next_value >= 3968`.
  - [ ] Quote/Contract/Order → `GREATEST(live_max+1, _System_CompoundAutoNumber.counter_state+1)`.
  - [ ] All 128 standard fields → `GREATEST(live_max+1, _System_AutoNumber.CurrentNumber+1)`.
  - [ ] All global-scope fields have a pre-seeded row even when live_max=0 (Approach X — P0-R2-B).
- [ ] `verifyAutoNumberInvariants()` BINDING; covers V1..V6 (including V5 row-count parity and V6 RecordTypeCode coverage).
- [ ] `Engine.AssignOne` + `AssignBatch` implemented per §5; first-use livelock-free verified by tests 17/17a/17b.
- [ ] Parser rejects all §6.4 fail cases.
- [ ] Cross-record + taxonomy tokens resolve via §3.5 logic; `readStrategy` honored.
- [ ] Concurrent-write test (100×100=10,000) passes with `-race`.
- [ ] Thundering-herd test 22a passes: per-counter semaphore gates fallback; jitter widens retry distribution.
- [ ] Fail-closed on unparseable row verified (case 24).
- [ ] DB-clock sampling verified (case 28).
- [ ] Short-reservation-txn decoupling verified (case 29).
- [ ] RecordTypeCode resolution verified (case R1); verify-gate failure surfaced (case R2).
- [ ] JSON canonicalization verified (case J1).
- [ ] Rollback-spec trigger verified (case D1).
- [ ] All 4 legacy tables dropped (post-backup).
- [ ] `generateAutoNumbers` + `generateAutoNumbersBatch` + `ensureStandardAutoNumbers` + `UpsertAutoNumber` + `CompoundAutoNumberListener` + `identifier_engine*.go` + `IdentifierRuleSeeds` all deleted.
- [ ] CI grep (P1-R2-F): `rg -n 'UpsertAutoNumber|ensureStandardAutoNumbers|generateAutoNumbersBatch|generateAutoNumbers\b' backend/ frontend/` returns zero hits (ignoring `docs/*`).
- [ ] CI drift check (P1-R2-C): `verify-generated-seeds` step green.
- [ ] Frontend readers rewired; `agent-browser` evidence captured.
- [ ] 三件套 report posted on PR.
- [ ] `go build ./...` + `go test -race ./...` green.
- [ ] E2E Playwright green.
- [ ] `/review` dual-model gate PASS.
- [ ] 4-quadrant gate run (schema + control-plane trigger per `common/code-review.md` §4-Quadrant).
- [ ] **Diff size (P2-R2-C)**: expected 1800-2500 LOC including deletions. If >2500 LOC, frontend/test deletions (§7.2 steps i+j) split to follow-up PR. Code rewires MUST land together.

### Phase 2 — 1-week soak + backup cleanup

**Target**: week after cutover merge.

**Phase-2 runbook (P2-R3-runbook):**

- **`ErrCounterMissing` fires post-cutover**: operator re-runs `SeedAutoNumberCounters` (idempotent via `GREATEST`). Do NOT manually `INSERT` rows — a concurrent seed runner could overwrite. If seed continues to fail, check `verifyAutoNumberInvariants` V5 output to identify the missing `field_id`; open an incident before further intervention.
- **`ErrRuleQuarantined` fires post-cutover**: a field's format was mutated outside `data-spec-apply` (see §5.6). Revert the raw-SQL edit, re-run the proper data-spec through `data-spec-apply` (which invokes verify).
- **Thundering-herd alert (autonumber retry p99 > 3)**: check per-counter metrics; if a single `(field_id, scope_key)` is hot, verify fallback semaphore is functioning (metric `autonumber_fallback_in_flight` should be ≤ 1); consider widening `maxRetries` or widening jitter via config.

- [ ] 7 days clean post-cutover (no `autonumber`-tagged incidents, no `verifyAutoNumberInvariants` failures).
- [ ] Drop 4 `__bak_20260418` tables via `data-specs/2026-05-02-autonumber-v2-backup-cleanup.md`.
- [ ] Archive `docs/design/unified-identifier-engine.md` with supersession header.
- [ ] Publish rollout notice for `<Obj>_Prefix__c` + `<Obj>_Number_Sequence__c` deprecation.
- [ ] Grep-clean check:
  ```bash
  rg -n 'CompoundAutoNumberListener|AutoNumberText__c|AutoNumber__c' backend/ frontend/
  rg -n '_System_AutoNumber|_System_CompoundAutoNumber|_System_IdentifierRule|_System_IdentifierSeq' backend/ frontend/
  rg -n 'generateAutoNumbers|generateAutoNumbersBatch|ensureStandardAutoNumbers|UpsertAutoNumber|IdentifierRuleSeeds|identifier_engine' backend/ frontend/
  ```
  Expected: zero hits in `backend/` + `frontend/` (only `docs/design/*.md` historical references).

---

## 12. Risks + Open Questions

### 12.1 Risks (refreshed for v3)

| # | Risk | Probability | Impact | Mitigation |
|---|---|---|---|---|
| R1 | Cross-record token target renamed after seed → stored `auto_number_token_refs` stale; runtime fails-closed. | Medium | High | `cmd/setup verify` re-validates every `token_refs` entry (§5.3.3 V2); non-zero blocks deploy. Admins run a data-spec to update `token_refs` when renaming. |
| R2 | RecordType's `DeveloperName` renamed via data-spec after seed — `RecordTypeCode` still valid (column is independent). | Low | Low | P0-R2-A Approach α eliminates coupling: `RecordTypeCode` is the token target, not `DeveloperName`. Renaming `DeveloperName` no longer affects autonumber rendering. |
| R3 | Optimistic CAS under pathological contention → retry starvation. | Medium | High | Bounded retry (`maxRetries=5`, configurable) + exponential jittered backoff (`rand(0, 2^(attempt+3) ms)` — wider than v2 per P1-R2-B); per-counter semaphore gates fallback; histogram metric + alert p99 > 3. Starvation test case 22 + thundering-herd test 22a. |
| R4 | `GET_LOCK('autonumber_cutover', 600)` expires during large table scan. | Low | High | 600s is 10× observed TiDB table-scan latency on ~5M rows. Seed paginates (10K batches) + reacquires per-batch. Idempotency via `ON DUPLICATE KEY GREATEST` makes partial re-runs safe. |
| R5 | TiDB node clock skew at midnight → per_day scope_key mis-bucketed. | Low | Low | TiDB is NTP-synced. Same SQL session sees one monotonic `UTC_TIMESTAMP(6)` per `SELECT`. UTC-only scope: at most off-by-one boundary row; counters still monotonic, no duplicates. |
| R6 | Rollback's `__bak_` restore misses records inserted after cutover. | Medium | High | Rollback spec includes one-shot `UPDATE _System_AutoNumber SET CurrentNumber = MAX(parsed_from_live)` per field before re-activating legacy path (§7.3 step 5). |
| R7 | Seed regex mis-handles literals with regex-special chars (`.` in `CAT.{0000}`). | Medium | Medium | `regexp.QuoteMeta` on literal segments (§8.2 step 3.a); test covers. |
| R8 | Admin format change → narrower counter width → next insert overflows. | Low | Medium | `cmd/setup verify` catches at admin-change time; runtime returns `ErrCounterOverflow`; no silent truncation. |
| R9 | Cross-record resolution issues N FK lookups inside caller txn, inflating latency. | Low | Medium | Depth-capped at 2; `FindMany` coalescing in `AssignBatch`; per-format parse cache + per-request cross-record cache. Target: p99 <10ms. Benchmark in cutover PR. |
| R10 | `DATETIME(6)` not exactly the same under TiDB driver vs MySQL. | Low | Low | Regression test on staging TiDB per `project_flow_test_db.md`. Minor precision bump from v1's plain `DATETIME`. |
| R11 | Simple formats (`{00000000}`) pay reservation-txn cost on hot path. | Low | Low | Reservation is <5ms happy case. If observed perf blocker, add `simpleFormatFastPath` in v2.1 (keep as optimization, not blocker). |
| **R12 (NEW — P0-R2-B)** | Approach X pre-seed misses a field (e.g. admin adds field post-generator-run without re-running). | Low | High | §5.3.3 V5 verifies row-count parity between `scope=global` fields and `scope_key=''` rows. Mismatch → deploy blocked. Admin-field-create (§7.2 step k) seeds row inline. |
| **R13 (NEW — P0-R2-A)** | Admin adds a new RecordType between seed and runtime without assigning a `RecordTypeCode`; a format referencing it renders empty. | Medium | High | §5.3.3 V6 fails verify-gate when any referenced RecordType has null `RecordTypeCode`. Runtime would also fail-closed via §3.4 `ErrTokenResolve{Cause:"target null"}`. Admin must add the code via data-spec before deploy. |
| **R14 (NEW — P1-R2-A)** | Default `readStrategy=caller_txn` + REPEATABLE READ snapshot returns stale `BillingCountryCode` after a concurrent UPDATE. | Low | Low | Data-shape mitigation: target fields in v2's seeded formats (`BillingCountryCode`, Account codes, RecordTypeCode) are effectively-immutable post-creation. Per-token `fresh_read` override available for fields where this turns out to matter (v2.1 on-demand). §12.2 Q asks reviewers to confirm default. |

### 12.2 Open Questions (Round 3 — refreshed)

1. **Q-null-policy**: §3.5 fail-closed on null FK / null target / missing row. For Contract with legitimately-null AccountId (legal-only), fail-closed blocks the insert. Acceptable for v3, or need per-token `"onNull": "literalEmpty" | "fail"`? **Recommend**: ship fail-closed; add per-token override in v2.1 if real data shows legitimate null FKs. Listener's silent `"XX"` (`compound_autonumber_listener.go:169`) explicitly rejected.
2. **Q-depth-2-syntax**: §3.5 allows depth-2 via `fkChains` fallback list, not via nested `{Opportunity.Account.BillingCountryCode}`. Sufficient for real cases? **Recommend**: stick with JSON-side declaration; nested-dot syntax deferred.
3. **Q-cmd-setup-verify-gate-strength**: BINDING (non-zero exit). ✅ Pinned. Reviewer signoff requested.
4. **Q-single-canonical-source-mechanism**: data-specs primary → Go generated. Alternative: Go primary → data-specs derived. **Recommend**: data-specs primary; prod path is `data-spec-apply`; Go is a bootstrap cache with CI drift check (P1-R2-C).
5. **Q-max-counter-width-18**: Cap at width 18. Survey longest=10 (`DuplicateRecordItem.Name`). Sufficient.
6. **Q-tz-support-deferred**: UTC-only v2. Account.Account_ID__c uses assigned-at in user's tz in SF; survey shows tz-consistent-with-UTC. Day-boundary tracer in cutover PR; v2.1 adds `auto_number_tz` if needed.
7. **Q-legacy-column-preservation**: v2 preserves `<Obj>_Prefix__c` + `<Obj>_Number_Sequence__c`. Preserve 1 month, then rollout-notice + delete PR. Tracked in follow-up issue.
8. **Q-backup-retention**: 2 weeks (§7.3). Confirm with ops.
9. **Q-phase-0-prep-granularity**: 2 hours drafting + baseline. No PR merged. Confirm no reviewer wants elevation.
10. **Q-per-day-scope-seeding**: Seed slices by date-token extraction when existing data spans multiple days. Code path exists but no seeded field uses `per_day` at cutover. Test 23b exercises. Integration activates when admin first turns on `per_day`.
11. **Q-record-type-code-approach (NEW — P0-R2-A)**: Approach α (dedicated column `RecordTypeCode`) chosen. Rationale: zero coupling with `DeveloperName` readers; explicit schema contract; trivially verifiable. Confirm with reviewers.
12. **Q-cross-record-read-strategy (NEW — P1-R2-A)**: Default `readStrategy=caller_txn`. Rationale: supports same-txn dependent-insert pattern observed in listener; target fields in seeded formats are stable. Confirm with reviewers — `fresh_read` must be opt-in per token.

### 12.3 Deferred to v3 (post-v3 release — list)

- `per_month`, `per_prefix` scopes; cross-object-shared counters.
- `{HH}`, `{mm}`, `{ss}` time-of-day tokens.
- Free-list / gap-filling on rollback.
- `created_date` and `custom_field` date sources.
- `regenerate_all` runtime behavior (admin-triggered backfill).
- Auto-backfill for net-new field creation on objects with existing rows.
- Admin UI for autonumber rule management.
- Multi-TZ support.
- Deeper-than-2 cross-record chains.
- Per-token null-handling policy override (§12.2 Q-null-policy if real data shows need).
- `simpleFormatFastPath` optimization (R11).
- Nested-dot token syntax (Q-depth-2-syntax).

---

## 13. Evidence Citations

Every factual claim traces to:

- **SF behavior**: `/Users/amber/claude/nexuscrm-data-sync/docs/sf-autonumber-spec.md`, sections + lines cited inline.
- **Compound listener**: `backend/internal/features/automation/compound_autonumber_listener.go` — `:79-122` (Quote/Contract configs), `:252-315` (type-code lookup + keyword table), `:262-296` (exact/keyword tables — P0-R2-A source), `:319-366` (lockAndIncrement), `:394-458` (GenerateBatch).
- **Standard autonumber runtime (single)**: `backend/internal/features/records/persistence_service.go:864-916` (`generateAutoNumbers`); call site `persistence_insert.go:139`.
- **Standard autonumber runtime (bulk — P1-R2-F)**: `backend/internal/features/records/persistence_bulk_insert.go:319` (`generateAutoNumbersBatch`).
- **Bootstrap autonumber registration (P1-R2-F)**: `backend/internal/bootstrap/schema.go:1715+` (`ensureStandardAutoNumbers`).
- **Admin field-create autonumber registration (P1-R2-F)**: `backend/internal/features/metadata/field_create.go:192` (`UpsertAutoNumber` call).
- **Repo port**: `backend/internal/domain/ports/repositories.go:30-44` (RecordRepository interface).
- **Bootstrap**: `backend/internal/bootstrap/schema.go:28-45` (phase order), `:117-134` (mutation lock), `:143+` (ReconcileTableColumns), `:1259-1263` (phase5_auto_numbers), `:1715+` (ensureStandardAutoNumbers).
- **Platform config pack**: `backend/internal/controlplane/packs/platform_config.go:329-435` (IdentifierRuleSeeds + applyIdentifierRules — to delete).
- **Service manager init**: `backend/internal/application/services/service_manager_init.go:123-124` (subscription — to remove).
- **Generated constants**: `shared/pkg/constants/z_generated_tables.go:68, :168` (TableIDentifierRule), `z_generated_fields.go:1755` (_System_IdentifierRule fields), `:1625+` (_System_Field fields).
- **Frontend readers**: `frontend/src/core/utils/recordUtils.ts:54` (AutoNumberText__c branch), `frontend/src/features/record/components/MetadataRecordForm.tsx:17-21` (locked-edit sets).
- **Flow regression harness (P2-R2-B — verified path)**: `backend/internal/features/automation/flow_regression_test.go:119` references `testdata/regression_fixtures/`; `backend/internal/features/automation/flow_scenario_e2e_test.go` (record-triggered — PR #1067).
- **Predecessor design**: `docs/design/unified-identifier-engine.md` — dissolved in §9.
- **PR #835 workflow artifacts**: `~/.claude/projects/-Users-amber-claude-MetaDrivenPlatform4AI/memory/project_pr835_workflow_upgrades.md` (三件套 template, 4-quadrant review, P0 → phase-gate, verify-gate-must-be-blocking).

---

## 14. Relevant file paths (for reviewers)

Open side-by-side during review:

- `/Users/amber/claude/nexuscrm-data-sync/docs/sf-autonumber-spec.md`
- `/Users/amber/claude/MetaDrivenPlatform4AI/docs/design/unified-identifier-engine.md` (predecessor — will be archived)
- `/Users/amber/claude/MetaDrivenPlatform4AI/docs/design/autonumber-engine-v2.md` (v2 — superseded by this v3)
- `/Users/amber/claude/MetaDrivenPlatform4AI/backend/internal/features/automation/compound_autonumber_listener.go` (to delete; P0-R2-A keyword map at `:262-296`)
- `/Users/amber/claude/MetaDrivenPlatform4AI/backend/internal/features/automation/identifier_engine.go` (to delete)
- `/Users/amber/claude/MetaDrivenPlatform4AI/backend/internal/features/records/persistence_service.go` (lines 864-916 to delete)
- `/Users/amber/claude/MetaDrivenPlatform4AI/backend/internal/features/records/persistence_insert.go` (line 139 to rewire)
- `/Users/amber/claude/MetaDrivenPlatform4AI/backend/internal/features/records/persistence_bulk_insert.go` (line 319 `generateAutoNumbersBatch` to delete — P1-R2-F)
- `/Users/amber/claude/MetaDrivenPlatform4AI/backend/internal/features/metadata/field_create.go` (line 192 `UpsertAutoNumber` call to rewire — P1-R2-F)
- `/Users/amber/claude/MetaDrivenPlatform4AI/backend/internal/domain/ports/repositories.go` (RecordRepository interface)
- `/Users/amber/claude/MetaDrivenPlatform4AI/backend/internal/application/services/service_manager_init.go` (lines 123-124 — unsubscribe)
- `/Users/amber/claude/MetaDrivenPlatform4AI/backend/internal/controlplane/packs/platform_config.go` (lines 329-435 — replace)
- `/Users/amber/claude/MetaDrivenPlatform4AI/backend/internal/bootstrap/schema.go` (lines 28-45, 117-134, 143+, 1259-1263, 1715+ `ensureStandardAutoNumbers` to delete — P1-R2-F)
- `/Users/amber/claude/MetaDrivenPlatform4AI/backend/internal/features/automation/flow_regression_test.go` (line 119 — fixture path — P2-R2-B)
- `/Users/amber/claude/MetaDrivenPlatform4AI/backend/internal/features/automation/flow_scenario_e2e_test.go` (record-triggered regressions — P2-R2-B)
- `/Users/amber/claude/MetaDrivenPlatform4AI/shared/pkg/constants/z_generated_tables.go`
- `/Users/amber/claude/MetaDrivenPlatform4AI/shared/pkg/constants/z_generated_fields.go`
- `/Users/amber/claude/MetaDrivenPlatform4AI/frontend/src/core/utils/recordUtils.ts` (line 54)
- `/Users/amber/claude/MetaDrivenPlatform4AI/frontend/src/features/record/components/MetadataRecordForm.tsx` (lines 17-21)
- Cutover data-spec (new): `/Users/amber/claude/MetaDrivenPlatform4AI/data-specs/2026-04-18-autonumber-v2-seed.md`
- Rollback (ships in cutover PR with `status: draft-rollback` — P1-R2-D): `/Users/amber/claude/MetaDrivenPlatform4AI/data-specs/2026-04-19-autonumber-v2-rollback.md`
- Backup cleanup (1 week later): `/Users/amber/claude/MetaDrivenPlatform4AI/data-specs/2026-05-02-autonumber-v2-backup-cleanup.md`
- `data-spec-apply` skill amendment for `draft-rollback` status: `/Users/amber/claude/MetaDrivenPlatform4AI/.agent/skills/data-spec-apply/SKILL.md`

---