# Plan v2: Speed Up Autonumber Seed via SQL-Side Filtering (Issue #1660)

**Scenario**: Bugfix (performance, no behavior change)
**Issue**: pingcap-inc/MetaDrivenPlatform4AI#1660
**Supersedes**: Plan v1 (watermark + skip) — rejected by Pass B adversarial review on 2 CRITICALs (silent drift after skip; seed-autonumbers --force violates documented quiesce-writers contract).

## Why v2

v1 proposed deleting the full-scan from the hot path. Pass B proved this trades 23s boot for silent production corruption (direct-DB writes bypass runtime UPSERT GREATEST; `verify --deep` is not a replacement — it's operator-triggered). Drift detector semantics must be preserved.

v2's core insight: **the scan is slow not because there's too much data to check, but because we fetch all rows to Go and iterate**. Push the regex filter into SQL. DB returns only the rows that actually matter (10s–100s instead of 4.47M). Same semantics, near-instant runtime.

## Design

### Current state (`scanLiveMaxCounter`, `autonumber_seed.go:556`)

```sql
SELECT `Id`, `<field>` FROM `<object>` WHERE `<field>` IS NOT NULL AND `<field>` != ''
```

Returns all 4.47M rows of Interface_Log__c. Go-side iterates, regex-matches, builds max, warn-or-fail on legacy rows.

### Proposed

Replace the single scan-all query with **three targeted queries**, each cheap:

1. **Max-of-matching query**:
   ```sql
   SELECT `Id`, `<field>` FROM `<object>`
   WHERE `<field>` IS NOT NULL AND `<field>` != ''
     AND `<field>` REGEXP '<sql_regex>'
   ```
   Returns only post-cutover rows (10s–100s). Go-side iterates them to build max. TiDB pushes REGEXP into the storage layer, so DB-side work is also bounded.

2. **Legacy-count query** (tolerant mode only):
   ```sql
   SELECT COUNT(*) FROM `<object>`
   WHERE `<field>` IS NOT NULL AND `<field>` != ''
     AND `<field>` NOT REGEXP '<sql_regex>'
   ```
   Returns a single integer — the skipped-rows count for the summary line. DB-side aggregate.

3. **Legacy-samples query** (tolerant mode only, only if count > 0):
   ```sql
   SELECT `Id`, `<field>` FROM `<object>`
   WHERE `<field>` IS NOT NULL AND `<field>` != ''
     AND `<field>` NOT REGEXP '<sql_regex>'
   LIMIT 5
   ```
   Returns up to 5 sample rows for the per-row WARN samples (same format the hotfix PR #1659 established).

4. **Legacy-abort query** (strict mode only):
   ```sql
   SELECT `Id`, `<field>` FROM `<object>`
   WHERE `<field>` IS NOT NULL AND `<field>` != ''
     AND `<field>` NOT REGEXP '<sql_regex>'
   LIMIT 1
   ```
   Returns 0 or 1 rows. If 1, fail-closed with that row's details (same error shape as today).

Same behavior, same semantics, same error messages. Only the query shape changes.

### `scanLivePerPrefixCounters` — parallel change

Same pattern. Query 1 becomes:
```sql
SELECT `Id`, `<field>` FROM `<object>`
WHERE `<field>` IS NOT NULL AND `<field>` != ''
  AND `<field>` REGEXP '<sql_regex>'
```

Go-side iterates matching rows only, builds the prefix→max map via the existing regex capture-group logic. Queries 2/3/4 are identical in shape.

### Go regex → SQL regex translation

`deriveCounterRegex` (line 458) emits Go regex. TiDB's REGEXP uses ICU (PCRE-ish) — compatible for our subset. Only one translation needed: `\d` → `[0-9]`. All other shapes (`^`, `$`, `.`, `*`, `+`, `{N,}`, `[...]`, literals) are identical.

Implementation: single helper `sqlRegexFromGoRegex(re *regexp.Regexp) string` that calls `re.String()` and does a single `strings.ReplaceAll(s, "\\d", "[0-9]")`. Done.

## Review-driven hardening (Round 1 findings)

Round 1 dual-model plan review surfaced 2 CRITICAL + 3 HIGH + 3 MEDIUM. Architecture stands; implementation must carry these fixes.

### CRITICAL-1 — case-sensitivity divergence (Pass B)

TiDB REGEXP uses the column's collation (typically `utf8mb4_unicode_ci`, case-INSENSITIVE). Go `regexp` is case-SENSITIVE. A row `con-00000001` that Go would reject as legacy would be counted as matching by naive TiDB REGEXP → silent under-count of legacy tail AND strict mode would silently pass.

**Fix**: every REGEXP predicate forces binary collation:
```sql
WHERE `<field>` COLLATE utf8mb4_bin REGEXP ?
```

**Regression test**: `TestCaseSensitivityParityWithGoRegex` — seed `CON-00000001` + `con-00000001`, Go regex `^CON-(\d{8,})$`. Both the Go-side match (`derivedRegex.FindStringSubmatch`) and the SQL predicate must reject the lowercase variant; assert COUNT of matching rows = 1 not 2.

### CRITICAL-2 — SQL literal escaping (Pass B)

The regex string contains `\`, `.`, `|`, etc. `fmt.Sprintf`-ing it into the WHERE clause risks double-escape bugs and is a latent injection hazard.

**Fix**: parameter binding via `?`. `sqlRegex` passed as a `QueryContext` argument, never string-concatenated.

### HIGH-1 — perf claim was hand-wavy (Pass A)

Plan originally asserted `<100ms` / `<2s`. No empirical basis. Per Pass A: TiDB REGEXP on un-indexed VARCHAR is still O(n) coprocessor work; expect seconds, not milliseconds.

**Fix**: replace perf table with acceptance criterion: **staging seed phase duration ≤ 50% of today's 23s (i.e. <12s)**. Measured via CloudWatch log timestamps. If >12s, revert.

### HIGH-2 — per_prefix match-set growth (Pass A M3)

Post-cutover match set grows with time. Today Interface_Log__c has ~42 matching rows (counter at 42). One year out: O(few thousand). Bounded for now, but unaddressed breaks the same way in 18 months.

**Fix**: Query 1 guards on result size. If matching-rows > 10,000, log a WARN and fall back to the pre-v2 iterate-all path for THIS field. Makes the optimization opt-in on growth — if we hit the ceiling we'll know + have time to design a GROUP BY pushdown before it bites.

**Regression test**: `TestScanLiveMaxCounter_FallsBackWhenMatchingExceedsCeiling`.

### HIGH-3 — `.*` greedy semantics (Pass B H3)

Go uses RE2 (no backtracking); TiDB uses ICU (PCRE-ish, backtracking). In principle `.*` + trailing `\d{N,}` with `$` anchor could partition differently. In practice, `deriveCounterRegex` only emits `.*` in a context immediately followed by literal hyphens + `\d{N,}$`, which both engines match identically (greedy `.*` yields to the anchored digit tail).

**Fix**: `TestRegexPartitionParity_GoVsTiDB` — feed the 132 seeded format specs through both `deriveCounterRegex` AND an in-memory TiDB REGEXP test (or a dedicated parity test that constructs sample values and asserts both engines extract the same counter). Fails loud if any format diverges.

### MEDIUM-1 — count + samples merged (Pass A M1)

Tolerant mode originally issued 2 queries (COUNT + LIMIT 5) against the legacy tail. Each is a full-tail scan.

**Fix**: single `SELECT Id, <field> FROM ... NOT REGEXP ... ORDER BY Id LIMIT N+1` where N = legacySampleLimit. Derive count (cap at N, report as `≥N` if ceiling hit) + samples in Go. Halves legacy-tail scan cost.

### MEDIUM-2 — strict-mode row determinism (Pass A M2)

TiDB un-ORDER-BY ordering is implementation-defined. Same deploy can fail on different rows across runs.

**Fix**: add `ORDER BY Id LIMIT 1` to the strict-mode legacy-abort query. Deterministic error messages for ops runbooks.

### MEDIUM-3 — test migration cost (Pass B M6)

Existing sqlmock tests expect 1 query per scan; v2 issues 3–4. Mock expectations need rewiring.

**Fix**: explicit in the test list below; treat as planned work, not incidental.

## Round 2 hardening (Pass B delta findings)

Round 2 Pass B agreed the CRITICAL fixes are complete but raised 3 implementation-level gaps. All addressed inline:

### HIGH-N1 — bound-param REGEXP may defeat TiDB's literal folding

`REGEXP '<literal>'` lets the planner see the pattern at compile time; `REGEXP ?` is opaque to the planner. Pushdown may still happen, but the query plan can differ.

**Fix**: perf acceptance step adds **EXPLAIN verification on staging before landing merge**. Capture `EXPLAIN SELECT ... WHERE field COLLATE utf8mb4_bin REGEXP ?` output for Interface_Log__c + one per_prefix field; confirm coprocessor pushdown still occurs (operator: `Selection_N` with `regexp_like(...)` in pushed-down predicates). If not, switch to literal-folded form with manually-escaped string (retaining `?` for any dynamic context) and re-verify. The `COLLATE utf8mb4_bin` clause is orthogonal to param binding and stays either way.

### HIGH-N2 — 10K ceiling hardcoded; silent regression in ~2.5 years

Interface_Log__c at ~4K/yr matching rows hits the hardcoded 10K ceiling around 2028, at which point fallback silently triggers and boot time regresses toward 23s without any deployment change.

**Fix**:
- Constant becomes env-configurable: `AUTONUMBER_SEED_MATCH_CEILING` with default `10000`.
- Fallback path emits a single WARN log per field: `"autonumber seed: field X has N matching rows (> ceiling M) — falling back to iterate-all scan; consider raising AUTONUMBER_SEED_MATCH_CEILING or optimizing this field's format"`.
- If a Prometheus metrics registry is wired (check at code time; if not, defer metric — WARN log is sufficient for ops).

### MEDIUM-N1 — tolerant sample query non-deterministic

The merged `NOT REGEXP ... LIMIT N+1` query has no `ORDER BY`. Samples across boots may show different legacy rows.

**Fix**: add `ORDER BY Id` to the merged NOT REGEXP query. Trivial, matches the strict-mode determinism fix.

### Residual risk acknowledgments

Two Round 2 findings are "PARTIAL — acceptable":

**HIGH-3 (`.*` PCRE vs RE2 parity)** — `TestRegexPartitionParity_GoVsTiDB` uses Go-side PCRE as a proxy (sqlmock can't execute real REGEXP). True parity verified on staging via: deploy → compare `NextValue` in `_System_AutoNumberCounter` before and after v2; equal values = partition parity across engines. Documented in staging verification plan.

**HIGH-4 (3-query race)** — Race window between query 1 (max-of-matching) and merged query 3 (NOT REGEXP samples/abort). Impact bounded because seed contract requires quiesced writers (`autonumber_seed.go:140-156`). Not fully closed but acceptable under the existing contract; worst case = one warn-log sample line cites a row that was just inserted as matching (cosmetic, not corruption).

## Why this is safe

1. **Seed remains the drift detector.** Every boot still verifies live max ≤ NextValue for every field. Direct-DB writes, backup restores, data-spec applies — all still caught on next boot, same as today.
2. **Semantics preserved** (after CRITICAL-1 + CRITICAL-2 + HIGH-3 fixes). Strict mode still fail-closes on the first legacy row (now ORDER BY Id LIMIT 1 — deterministic). Tolerant mode still samples + summarizes. Max still equals max-of-matching-rows. Case-sensitive REGEXP parity with Go.
3. **No schema change.** No `_System_AutoNumberCounter` migration.
4. **No new CLI command.** `cmd/setup apply` still does the right thing.
5. **No concurrency-contract violation.** Quiesce-writers prerequisite still holds; we don't introduce new operator paths.
6. **Cheap to revert.** One-file change; if TiDB REGEXP pushdown turns out to be slower than expected OR the match-set ceiling is hit early, revert restores the status quo.
7. **Guarded against future growth.** HIGH-2's 10K-match-count fallback means the optimization is self-disabling for any field that outgrows it.

## Acceptance criteria (perf)

Not pre-declaring absolute numbers — TiDB REGEXP on un-indexed columns is O(n) coprocessor work, and Round 1 Pass A correctly flagged that "<100ms" was a hand-wave.

**Acceptance** (two-step, both required):

1. **EXPLAIN verification (pre-merge)**: SSH into staging (or run via CloudShell with staging DB creds) and capture `EXPLAIN SELECT ... WHERE field COLLATE utf8mb4_bin REGEXP ?` for Interface_Log__c + one per_prefix field. Confirm `regexp_like(...)` appears in the pushed-down predicate list (coprocessor pushdown). If not: re-work queries to use literal-folded regex form before merging (HIGH-N1).

2. **Seed-phase duration (post-merge)**: staging seed duration (log boundary `Running control-plane apply...` → `═══ RunRepair completed`) **≤ 12s** (50% of today's 23s). Measured via CloudWatch log timestamps after the deploy of v2 lands. If >12s, revert.

Rationale for the 50% target: 4.47M → 5-digit rows returned is ~5 orders of magnitude less data transfer. Even if TiDB's coprocessor REGEXP is itself O(n), cutting network + Go-side iteration gets us well under 50%. If the DB-side scan itself dominates, we'll know (and the 10K-match-count fallback prevents pathological cases).

## Diff surface (single PR, small)

1. `backend/internal/bootstrap/autonumber_seed.go`:
   - New helper `sqlRegexFromGoRegex(re *regexp.Regexp) (string, error)` — translates `\d → [0-9]`; rejects any other backslash escape (defensive, fails loud on future format shape).
   - `scanLiveMaxCounter` rewritten with the 3-query pattern described above + `COLLATE utf8mb4_bin` + `?` param binding + 10K-match ceiling + fallback.
   - `scanLivePerPrefixCounters` same pattern.
   - Constants: `defaultMatchCountCeiling = 10000` near `legacySampleLimit`; runtime ceiling read from env `AUTONUMBER_SEED_MATCH_CEILING` (HIGH-N2).
   - All `NOT REGEXP` queries include `ORDER BY Id` (MEDIUM-N1 + MEDIUM-2).
   - ~80 lines changed total, no new external API.
2. `backend/internal/bootstrap/autonumber_seed_test.go`:
   - `TestSqlRegexFromGoRegex` — 5 real deriveCounterRegex outputs translate correctly; unknown escapes (`\s`, `\w`, `\b`) fail loud.
   - `TestCaseSensitivityParityWithGoRegex` — **CRITICAL-1 regression**: lowercase legacy variants excluded by both Go and SQL.
   - `TestRegexPartitionParity_GoVsTiDB` — **HIGH-3 regression**: 132 format specs extract same counter via Go regex and sqlmock-simulated TiDB REGEXP (engines partition identically).
   - `TestScanLiveMaxCounter_UsesParameterBinding` — **CRITICAL-2 regression**: sqlmock `WithArgs(regex)` passes, `WithoutArgs` fails.
   - `TestScanLiveMaxCounter_AppliesBinaryCollate` — asserts `COLLATE utf8mb4_bin` substring in query.
   - `TestScanLiveMaxCounter_PushesRegexToSQL` — asserts `REGEXP ?` predicate in query shape.
   - `TestScanLiveMaxCounter_ReturnsMaxOfMatchingOnly` — 3 matching rows + 5 legacy → max from matching only.
   - `TestScanLiveMaxCounter_StrictFailsClosedOnLegacy_DeterministicRow` — **MEDIUM-2 regression**: sqlmock returns 3 legacy rows in Id order `b`/`a`/`c`; strict mode must fail on `a` (lowest Id via `ORDER BY Id LIMIT 1`).
   - `TestScanLiveMaxCounter_TolerantCountsAndSamplesLegacy` — 10 legacy rows → 5 samples (reuses hotfix sampler contract), summary cites `skipped 10 legacy rows`. Single merged query (MEDIUM-1).
   - `TestScanLiveMaxCounter_FallsBackWhenMatchingExceedsCeiling` — **HIGH-2 regression**: 10001 matching rows → fallback path logged + uses legacy iterate-all query; assert error-free max correctly computed.
   - `TestScanLivePerPrefixCounters_*` — mirrors of all the above for the per_prefix scan path.
   - All existing tolerant / strict / empty-column / missing-table tests rewired for the 3-query shape (MEDIUM-3) and still green.

No other file touched. No schema change, no CLI change, no controlplane change.

## Blast radius

**Low.** One-file semantic-preserving optimization. Worst cases:
- TiDB REGEXP slower than expected → staging seed time regresses but stays ≤ today's 23s → we revert or add an env flag.
- Regex translation misses a shape we didn't think of → fails-closed at boot (by design) with a clear error; operator reverts.

No data corruption path. No cross-PR coordination. No rolling-deploy complication.

## Review level

**Dual-model** (Pass A + Pass B). No 4-quadrant — this is a perf bugfix, not a control-plane schema / concurrency / migration change.

## Triple-test?

Per `.agent/skills/e2e-test-policy/SKILL.md`, this is **exempt** — no observable change to SObject CRUD behavior. It's an internal perf change to the existing seed path. However the plan WILL include staging verification (same method as PR #1659):

- Deploy → measure boot-time seed phase duration via CloudWatch log timestamps.
- Compare: today's 23s vs post-v2's expected <2s.
- Sanity-check `_System_AutoNumberCounter` rows: same NextValues as today (semantic parity).

## What this plan does NOT do

- **Does NOT skip the scan on subsequent boots.** Plan v1 did; Plan v2 deliberately doesn't, for the drift-detector reason Pass B surfaced.
- **Does NOT add a watermark column.** No schema change.
- **Does NOT add `seed-autonumbers` CLI.** No new operator surface.
- **Does NOT fix the auto-revert workflow** (branch protection rejects direct push). That's a separate follow-up (noted in #1660 body).

## Open questions (to be closed before coding)

None at planning time. Plan review gate is the checkpoint.

## Parallel dispatch plan

Single turn Agent fan-out for v2 plan review (2 lanes):
- Pass A (planner / structured): feasibility, edge cases in the regex translation, test coverage adequacy
- Pass B (adversarial): find how TiDB REGEXP breaks vs Go regex; find a field whose derived regex has a shape that `\d → [0-9]` alone won't translate correctly; find a performance-regression case where REGEXP is slower than the current iterate-all path

Gate: both must PASS before PR.