--- apiVersion: data-spec/v1 id: migrate-compound-autonumber-state-20260417 description: "Copy _System_CompoundAutoNumber counter state into _System_IdentifierSeq for the unified identifier engine (Quote/Contract/Order)" issue: "#1029" created: 2026-04-17 scope: production-only status: pending tables: - _System_IdentifierRule - _System_IdentifierSeq - _System_CompoundAutoNumber depends_on: [] --- # Migrate `_System_CompoundAutoNumber` Counter State → `_System_IdentifierSeq` ## Summary Task 5 of the Unified Identifier Engine rollout (design: `docs/design/unified-identifier-engine.md`, PR #1029; schema: PR #1039). The new engine reads counter state from `_System_IdentifierSeq` keyed by `(RuleId, ScopeKey)`. The old listener (`backend/internal/features/automation/compound_autonumber_listener.go`) writes counter state to `_System_CompoundAutoNumber` keyed by `(ObjectApiName, Prefix)` with `Id = "{ObjectApiName}:{Prefix}"`. This spec **copies** every existing counter row from `_System_CompoundAutoNumber` into `_System_IdentifierSeq`, mapping: | Old (`_System_CompoundAutoNumber`) | New (`_System_IdentifierSeq`) | |---|---| | `Prefix` (e.g. `GB-CSA-260416`) | `ScopeKey` (verbatim — same string) | | `CurrentNumber` | `CurrentNumber` | | `ObjectApiName` (`Quote`/`Contract`/`Order`) | `RuleId` (lookup via `_System_IdentifierRule.ObjectApiName`) | | `Id = "{ObjectApiName}:{Prefix}"` | `Id = CONCAT(RuleId, ':', ScopeKey)` | **Why scope keys are byte-identical:** the old listener builds `Prefix` as `countryCode + "-" + typeCode + "-" + dateStr` where `dateStr = fmt.Sprintf("%02d%02d%02d", now.Year()%100, now.Month(), now.Day())` (UTC, zero-padded YYMMDD). See `compound_autonumber_listener.go:213-216`. The new engine renders `{Account.BillingCountryCode}-{TypeCode}-{YYMMDD}{SEQ:n}` and strips the `{SEQ:n}` token to derive the scope key — produces the exact same string. So the migration is a **literal copy of `Prefix` → `ScopeKey`** with no transformation. **Why this is a copy, not a move:** the old `_System_CompoundAutoNumber` table stays in place after this spec runs. Task 4 disables the listener subscription in `service_manager_init.go` but leaves the table data untouched so a rollback to the old engine restores the original behavior with zero data loss. A later follow-up PR (after staging + production validation of the new engine) drops the old table — that drop is **not** part of this spec. **Out of scope:** `Contract` records may carry many `TypeCode`s (CSA, MA, NDA, PNA, TCSA, TCPA, TEPA, CRA, PA, CON) because the old listener resolves `TypeCodeLookup` from `RecordType.Name`. The migration carries every `(ObjectApiName, Prefix)` row regardless of the embedded `TypeCode` — the mapping happens at the `Prefix` level, not the `TypeCode` level, so all variants are preserved. ## Pre-Checks Run these BEFORE executing the migration. Each query must return the expected result; if any fails, **STOP** and investigate. ### P1. Schema PR is deployed (new tables exist) ```sql SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('_System_IdentifierRule', '_System_IdentifierSeq'); ``` Expected: 2 rows. If 0 or 1 row, the schema PR (#1039) has not been deployed to this environment yet — abort. ### P2. Seeded rules are present ```sql SELECT Id, ObjectApiName, RuleName, IsActive FROM _System_IdentifierRule WHERE Id IN ('idr_Quote_BusinessId', 'idr_Contract_BusinessId', 'idr_Order_BusinessId') ORDER BY ObjectApiName; ``` Expected: 3 rows, one per object, all `IsActive = 1`. If any are missing, re-run `cmd/setup apply` so `applyIdentifierRules` seeds them (`backend/internal/controlplane/packs/platform_config.go`). ### P3. Old table has data to migrate ```sql SELECT ObjectApiName, COUNT(*) AS row_count, MIN(Prefix) AS sample_prefix, SUM(CurrentNumber) AS total_counters FROM _System_CompoundAutoNumber WHERE ObjectApiName IN ('Quote', 'Contract', 'Order') GROUP BY ObjectApiName ORDER BY ObjectApiName; ``` Expected: 1-3 rows depending on environment activity. **Capture this output into the apply log** — Post-Check V1 compares against it. If 0 rows the migration is a no-op (nothing to copy) and that is fine; the new table will start empty and the engine will create rows on first insert. ### P4. New table is empty for these RuleIds (or contains a subset of what we are about to insert) ```sql SELECT RuleId, COUNT(*) AS existing_rows FROM _System_IdentifierSeq WHERE RuleId IN ('idr_Quote_BusinessId', 'idr_Contract_BusinessId', 'idr_Order_BusinessId') GROUP BY RuleId ORDER BY RuleId; ``` Expected: 0 rows (first apply) or `existing_rows <= corresponding row_count from P3` (re-apply). If `existing_rows > P3 row_count`, someone has already inserted data via the new engine — investigate before re-applying. ## Changes ### 1. Copy counter state into `_System_IdentifierSeq` (idempotent UPSERT) Single INSERT...SELECT joining the old counter table to the seed rules. The JOIN on `ObjectApiName` is the bridge between the two ID schemes. ```sql INSERT INTO _System_IdentifierSeq (Id, RuleId, ScopeKey, CurrentNumber, CreatedDate, LastModifiedDate) SELECT CONCAT(r.Id, ':', c.Prefix) AS Id, r.Id AS RuleId, c.Prefix AS ScopeKey, c.CurrentNumber AS CurrentNumber, COALESCE(c.CreatedDate, NOW()) AS CreatedDate, NOW() AS LastModifiedDate FROM _System_CompoundAutoNumber c JOIN _System_IdentifierRule r ON r.ObjectApiName = c.ObjectApiName WHERE r.Id IN ('idr_Quote_BusinessId', 'idr_Contract_BusinessId', 'idr_Order_BusinessId') ON DUPLICATE KEY UPDATE -- Re-applies are safe: take MAX of old and new so a higher counter -- already written by the new engine (post-cutover) is never decremented. -- This is the same "counter sync at deploy time" safety property the -- design doc calls out (line 356). CurrentNumber = GREATEST(_System_IdentifierSeq.CurrentNumber, VALUES(CurrentNumber)), LastModifiedDate = NOW(); ``` **Idempotency:** the primary key of `_System_IdentifierSeq` is the composite string `{RuleId}:{ScopeKey}`. Re-running this INSERT against the same `_System_CompoundAutoNumber` snapshot hits `ON DUPLICATE KEY UPDATE` for every row and applies `GREATEST` — no row is overwritten with a lower counter, no duplicates are created. The unique index on `(RuleId, ScopeKey)` provides the same guarantee through a second path. **Why `GREATEST` and not blind overwrite:** if the cutover (Task 4 — disable listener + enable new engine) happens between two runs of this spec, the new engine may have already advanced some counter rows. Blind overwrite would roll those back and cause duplicate identifiers on the next insert. `GREATEST` preserves any forward progress made by the new engine. **Safety:** the WHERE clause restricts the JOIN to the three known seeded RuleIds. If `_System_CompoundAutoNumber` ever contained rows for an `ObjectApiName` not in the seed list (it does not today — listener only configures Quote/Contract), those rows are silently ignored, not migrated to a phantom RuleId. **Salesforce parity:** scope keys are preserved verbatim, so the next identifier the new engine generates for `GB-CSA-260416` continues from the old counter. No customer-visible identifier resets. ## Post-Checks Run these AFTER applying. All must pass. ### V1. Row counts match ```sql SELECT 'old' AS source, ObjectApiName, COUNT(*) AS row_count FROM _System_CompoundAutoNumber WHERE ObjectApiName IN ('Quote', 'Contract', 'Order') GROUP BY ObjectApiName UNION ALL SELECT 'new' AS source, r.ObjectApiName, COUNT(*) FROM _System_IdentifierSeq s JOIN _System_IdentifierRule r ON r.Id = s.RuleId WHERE r.Id IN ('idr_Quote_BusinessId', 'idr_Contract_BusinessId', 'idr_Order_BusinessId') GROUP BY r.ObjectApiName ORDER BY ObjectApiName, source; ``` Expected: for each `ObjectApiName`, the `old` and `new` row counts are equal. A `new > old` count indicates the new engine has already created additional scope keys after cutover (acceptable). A `new < old` count indicates rows were not migrated — investigate. ### V2. Sample row comparison (Quote/Contract/Order, 1 row each) ```sql SELECT c.ObjectApiName, c.Prefix AS old_prefix, c.CurrentNumber AS old_counter, s.ScopeKey AS new_scope_key, s.CurrentNumber AS new_counter, s.RuleId AS new_rule_id, s.Id AS new_id, CONCAT(s.RuleId, ':', s.ScopeKey) AS expected_new_id FROM _System_CompoundAutoNumber c JOIN _System_IdentifierRule r ON r.ObjectApiName = c.ObjectApiName JOIN _System_IdentifierSeq s ON s.RuleId = r.Id AND s.ScopeKey = c.Prefix WHERE r.Id IN ('idr_Quote_BusinessId', 'idr_Contract_BusinessId', 'idr_Order_BusinessId') ORDER BY c.ObjectApiName, c.Prefix LIMIT 30; ``` Expected for every row: - `old_prefix = new_scope_key` (literal byte equality) - `new_counter >= old_counter` (equal on first apply; possibly greater after the new engine has advanced the row — `GREATEST` semantics) - `new_id = expected_new_id = CONCAT(new_rule_id, ':', new_scope_key)` Inspect at least one Quote, one Contract, and one Order row by eye. (Order rows may be absent if the old listener was never wired for Order — that is expected; the design doc explicitly calls out that Order auto-numbering is unfinished today.) ### V3. No orphan rows under the migrated RuleIds ```sql SELECT s.Id, s.RuleId, s.ScopeKey, s.CurrentNumber FROM _System_IdentifierSeq s LEFT JOIN _System_CompoundAutoNumber c ON c.ObjectApiName = ( SELECT r.ObjectApiName FROM _System_IdentifierRule r WHERE r.Id = s.RuleId ) AND c.Prefix = s.ScopeKey WHERE s.RuleId IN ('idr_Quote_BusinessId', 'idr_Contract_BusinessId', 'idr_Order_BusinessId') AND c.Id IS NULL; ``` Expected on first apply: 0 rows. After cutover (Task 4 disables the listener and the new engine starts populating fresh scope keys), this query may legitimately return rows — those are scope keys created by the new engine that never existed in the old table. That is expected and not an error after cutover; on first apply (before cutover) it must be empty. ## Idempotency Verification Running this spec twice in a row produces the same final state as running it once. Concretely: 1. First apply — inserts N rows (where N = pre-check P3 row_count). 2. Capture `_System_IdentifierSeq` state: ```sql SELECT Id, CurrentNumber FROM _System_IdentifierSeq WHERE RuleId IN ('idr_Quote_BusinessId','idr_Contract_BusinessId','idr_Order_BusinessId') ORDER BY Id; ``` 3. Second apply — every row hits `ON DUPLICATE KEY UPDATE`; `GREATEST` keeps the same `CurrentNumber` (because the source value is identical). The only delta is `LastModifiedDate = NOW()`. 4. Re-run the snapshot query — `Id` and `CurrentNumber` columns are byte-identical to step 2. `LastModifiedDate` movement is acceptable and expected; it is not a counter state column. ## Rollback The migration is purely additive — `_System_CompoundAutoNumber` is untouched. Rollback removes only the rows this spec created. ```sql DELETE FROM _System_IdentifierSeq WHERE RuleId IN ('idr_Quote_BusinessId', 'idr_Contract_BusinessId', 'idr_Order_BusinessId'); ``` After rollback, the old listener can be re-enabled (revert Task 4) and reads its state from the still-intact `_System_CompoundAutoNumber` table. Identifier generation resumes exactly where it left off. **Rollback safety:** if the new engine has already been live and populated fresh scope keys (post-cutover), the DELETE above also removes those rows. Re-applying this spec restores the migrated rows but loses the post-cutover deltas. Therefore: do NOT roll back this spec after cutover unless you are also reverting Task 4 in the same window. ## Operational Notes - **Run order:** apply this spec **before** Task 4 (the code change that disables the old listener and turns on the new engine). Doing so guarantees the new engine boots with fully migrated counter state. - **Counter sync at deploy time:** the design doc (line 356) calls for a one-time safety check on first boot of the new engine that re-reads `MAX(CurrentNumber)` from the old table per migrated scope key. Task 4 implements that startup hook; this spec is the bulk seed it complements. - **No DDL:** this spec only writes to `_System_IdentifierSeq`. The new tables themselves are created by the schema PR (#1039) running through `cmd/setup apply` against `backend/internal/bootstrap/system_tables.json`. - **Scope:** `production-only` — the bootstrap path already seeds an empty `_System_IdentifierSeq` and the seed rules; this spec is needed only for environments that have accumulated state in `_System_CompoundAutoNumber` (staging, production).