--- apiVersion: data-spec/v1 id: lead-validation-flag-20260422 description: "Add Lead.LeadValidation__c boolean field referenced by Lead_Convert_Validation_rules flow and analytics CRT goldens" issue: "#1694" created: 2026-04-22 scope: production-only status: applied applied_envs: [sandbox-new-prod-essential] tables: [lead, _System_Field] depends_on: [] --- # Add `Lead.LeadValidation__c` boolean flag ## Summary Issue #1694 identified that the `Lead_Convert_Validation_rules` record-triggered flow (ActiveVersionId `301RC00000Ze8hIYAR`, RecordBeforeSave / Update) contains a `yanzheng` assignment that writes `$Record.LeadValidation__c = true` whenever the converted lead's Portal_ID differs from its converted Account's Portal_ID. The column does not exist on the sandbox DB (`new-prod-essential` / TiDB Cloud branch `4Qbn3F3nyaU9DLk.root`): - No `_System_Field` row with `ApiName='LeadValidation__c'` on Lead. - No physical column matching the name on table `lead`. Analytics CRT golden files (`backend/internal/features/analytics/testdata/crt_goldens/leads_with_matching_accounts/`) already reference `Lead.LeadValidation__c` as a real column mapping, confirming the field is expected by the backend. This spec registers it so the flow's assignment can land and so CRT query generation keeps matching the golden. Semantics: boolean flag set by the flow to surface data inconsistency between a converted Lead and its resulting Account's Portal_ID. Default `false`; only the flow (or an explicit PATCH) writes `true`. ## Changes ### 1. Add the physical `LeadValidation__c` column to `lead` ```sql ALTER TABLE `lead` ADD COLUMN IF NOT EXISTS LeadValidation__c TINYINT(1) NULL DEFAULT 0; ``` **Idempotency**: `ADD COLUMN IF NOT EXISTS` is a no-op when the column already exists. `TINYINT(1)` + `DEFAULT 0` matches the existing pattern used by `Lead.Auto_Converted__c`, `Lead.IsConverted`, `Lead.AI_Processed__c`, etc. (verified via `INFORMATION_SCHEMA.COLUMNS` against table `lead`, all are `tinyint(1) NULL`). **Note**: `lead` MUST be backtick-quoted — TiDB / MySQL reserves `LEAD` as a keyword for the `LEAD()` window function, so unquoted `ALTER TABLE lead ...` fails with a syntax error. ### 2. Register `Lead.LeadValidation__c` in `_System_Field` ```sql UPDATE _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId SET f.Label = 'Lead Validation', f.PhysicalName = 'LeadValidation__c', f.Type = 'boolean', f.Required = 0, f.IsUnique = 0, f.IsSystem = 0, f.IsNameField = 0, f.DefaultValue = 'false', f.Createable = 1, f.Updateable = 1, f.LastModifiedDate = CURRENT_TIMESTAMP WHERE o.ApiName = 'Lead' AND f.ApiName = 'LeadValidation__c'; INSERT IGNORE INTO _System_Field ( Id, ObjectId, ApiName, Label, PhysicalName, Type, Required, IsUnique, IsSystem, IsNameField, DefaultValue, Createable, Updateable, CreatedDate, LastModifiedDate ) SELECT 'fld_lead_leadvalidation__c', o.Id, 'LeadValidation__c', 'Lead Validation', 'LeadValidation__c', 'boolean', 0, 0, 0, 0, 'false', 1, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP FROM _System_Object o WHERE o.ApiName = 'Lead'; ``` **Idempotency**: the `UPDATE` normalizes any existing row to the desired final state (no-op on fresh DB — zero rows affected). `INSERT IGNORE` then adds the row; on re-run or concurrent run it silently no-ops when a conflicting row already exists via **either** the primary key `Id='fld_lead_leadvalidation__c'` **or** the `UNIQUE(ObjectId, ApiName)` constraint on `_System_Field` (see `backend/internal/bootstrap/system_tables.json:497-508` — the `_System_Field` definition declares `{columns: [ObjectId, ApiName], unique: true}`). Choosing `INSERT IGNORE` over `INSERT ... SELECT ... WHERE NOT EXISTS` closes the race window two concurrent appliers would otherwise open (both observing NOT EXISTS, both INSERTing, one aborting on duplicate-key). UPDATE-first-then-INSERT ordering matches the template in `data-specs/2026-04-01-task-updated-by-field-and-layout.md`. Row Id `fld_lead_leadvalidation__c` follows the snake_case convention of `fld_lead_auto_converted__c`. **Partial-apply recovery**: TiDB, like MySQL, commits DDL atomically but does not wrap a DDL + DML statement pair in a single transaction — `ALTER TABLE` auto-commits before the metadata statements run. If step 1 succeeds and step 2 fails (network blip, transient error), the database is left with a live physical column and no `_System_Field` row. Re-running this spec recovers cleanly: `ADD COLUMN IF NOT EXISTS` is a no-op on the second run, and `UPDATE` + `INSERT IGNORE` then write the missing metadata row. The applier can safely re-run on any error without special recovery logic. ### 3. Post-apply assertion (MANDATORY) ```sql SELECT CASE WHEN COUNT(*) = 1 THEN 'OK' ELSE CONCAT('FAIL: expected 1 row with Id=fld_lead_leadvalidation__c AND ApiName=LeadValidation__c AND ObjectId=disc_lead; got ', COUNT(*)) END AS apply_result FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE o.ApiName = 'Lead' AND f.ApiName = 'LeadValidation__c' AND f.Id = 'fld_lead_leadvalidation__c'; ``` **Why**: `INSERT IGNORE` silences both PK conflicts and UNIQUE(ObjectId, ApiName) conflicts. In the pathological case where some unrelated row already owns the PK `Id='fld_lead_leadvalidation__c'` (e.g. leftover from a manual ops intervention), the INSERT silently skips AND the UPDATE matches nothing, leaving the Lead field unregistered. This assertion MUST return `apply_result = 'OK'` after step 2 — if not, the apply has silently failed and requires manual investigation before declaring the spec applied. **Operator contract**: since this spec is shipped with `status: applied`, it already skips in automated apply pipelines — the assertion is primarily a safeguard for the one-shot human-applier path used when this spec was originally landed. That one-shot apply was performed manually by the author and returned `OK` (see evidence log referenced in the Target DB version section above). Any future operator re-running this block MUST: 1. Execute steps 1 → 2 → 3 in order. 2. Read the result of step 3's `apply_result` column. 3. Treat any value other than the exact string `'OK'` as a **hard failure** requiring investigation before considering the apply successful. The `data-spec-apply` skill currently does not enforce SELECT-assertion evaluation automatically (`.agent/skills/data-spec-apply/SKILL.md` lacks a machine rule for `apply_result = 'OK'`); a project-level follow-up will extend the protocol to make this check automatic for specs that include a `SELECT ... AS apply_result` step. **Physical-shape drift (known limitation)**: `ADD COLUMN IF NOT EXISTS` only creates the column if absent — it does not normalize an existing column's type, nullability, or default. If an operator later manually changed the column to e.g. `NOT NULL DEFAULT 1`, re-applying this spec would preserve the drifted physical shape while the metadata continues to describe the original `boolean` / default `false`. This matches the data-spec protocol's refusal to issue destructive `ALTER ... MODIFY COLUMN` on a column that may already contain data; correcting drift is an explicit operator task, not an implicit re-apply. ## Rollback ```sql -- Remove the metadata registration DELETE f FROM _System_Field f JOIN _System_Object o ON o.Id = f.ObjectId WHERE o.ApiName = 'Lead' AND f.ApiName = 'LeadValidation__c'; ``` Rollback note: this spec intentionally does not drop the physical `lead.LeadValidation__c` column. Column drops are destructive and disallowed by the data-spec protocol. After rollback the column becomes inert because the field metadata is removed; the column can be safely dropped in a separate manual operation if ever required. ## Target DB version evidence Applied and triple-idempotency-verified against the sandbox TiDB Cloud branch `new-prod-essential`. `SELECT tidb_version()` at apply time returned: ``` Release Version: v8.5.3-serverless Edition: Community Git Commit Hash: 20260317-08575a5655e941f9ccd9db6e6e7c1af7d2034904 ``` `ADD COLUMN IF NOT EXISTS` and `INSERT IGNORE` are both supported on this version (`v8.5.3-serverless`, released 2026-03-17). No syntax errors observed across 6 consecutive applies on this exact branch. The project's CI TiDB image pin is in `.github/workflows/ci.yml`; the sandbox branch is newer. ## Scope & apply gating (mechanically enforced) `scope: production-only` in the frontmatter is the correct data-spec classification (Lead is a discovered production object, not a bootstrap object). **The `status: applied` value is the mechanical gate** against accidental auto-apply to staging or prod: - Per `.agent/skills/data-spec-apply/SKILL.md` §1.1 status model, `applied` means "Already applied in target env — Skip (non-error)". Any automated applier iterating over `data-specs/*.md` will therefore skip this file on every subsequent run, regardless of target DSN. - `applied_envs: [sandbox-new-prod-essential]` records the only environment where this has been applied. The triple-idempotency-verified apply on that sandbox branch completed before this PR opened (see `evidence/runtime/verify.log` in the companion runtime verify). - To later apply this change to staging or prod, the owning engineer MUST create a new spec (copy with a new `id` and `status: pending-staging` or `pending-prod`) — not edit this one. Rationale: the owning team has not yet decided whether the field should be mirrored to staging/prod or whether the flow's `yanzheng` assignment should be deprecated instead. Either path may still win; applying to prod now would pre-empt that decision. - **Follow-up**: a separate issue will track the staging + production decision once the sandbox runtime verify closes the loop on flow behavior. ## Related - Issue: #1694 - Discovery report: worktree `.worktrees/verify-lead-convert-flows/evidence/flow-feasibility-lead-convert/FINAL_VERDICT.md` - Flow metadata dump: same evidence dir, `flows_raw.tsv` - Analytics golden expecting the column: `backend/internal/features/analytics/testdata/crt_goldens/leads_with_matching_accounts/expected.json:3169,4111` - Follow-up: staging + production application — decide after sandbox runtime verify closes the loop.