---
apiVersion: data-spec/v1
id: lead-auto-create-org-update-account-20260430
description: "Add `Account__c = $Record.ConvertedAccountId` inputAssignment to the `Update_Org` element of the active `Lead_Auto_Create_Org` flow so the Convert reuse-Opp path populates Account__c on the existing Org__c row"
issue: "#1792"
created: 2026-04-30
scope: production-only
status: pending
tables: [Flow]
depends_on: []
---

# Lead_Auto_Create_Org — Update_Org Account__c assignment

## Summary

Issue #1792 Scenario 5: when `convertLead` reuses an existing Opportunity on a
Lead whose create-side `Org__c` row was retained, the AfterSave + CreateAndUpdate
flow `Lead_Auto_Create_Org` correctly fires and routes through `Update_Org`,
but the existing `Org__c.Account__c` is never written to
`$Record.ConvertedAccountId`.

Root cause is in the flow metadata, not the engine: the symmetric
`Create_Org_1` element (Convert-creates branch) has the
`Account__c = $Record.ConvertedAccountId` inputAssignment, but `Update_Org`
does not. The L0 flow engine correctly executes only what the metadata
declares; therefore the fix lives at L2 (org metadata).

The accompanying code PR fixes a separate, code-side defect surfaced by the
same issue (`CheckUniqueness` not excluding soft-deleted rows — Scenario 2).
This data-spec only addresses the metadata gap.

## Changes

### 1. Append `Account__c` inputAssignment to `Update_Org`

The patch:

- Locates the `Lead_Auto_Create_Org` flow definition by `DeveloperName`.
- Resolves its `ActiveVersionId` (the live flow row in `Flow`).
- Locates the `Update_Org` element inside `Metadata.recordUpdates[*]` by
  searching for `name='Update_Org'` rather than indexing positionally — the
  array order may differ across clusters / future re-published versions.
- Idempotency probe: skips the UPDATE if `Account__c` already appears in the
  element's `inputAssignments`.
- Appends the new assignment using `JSON_ARRAY_APPEND`, preserving the
  exact value-shape of the sibling assignments in the same array (the
  `value` object includes the full set of `apexValue`/`booleanValue`/...
  null fields so downstream consumers parsing strict-shape get a
  shape-identical record).

```sql
SET @flow_def_id := (
  SELECT Id FROM FlowDefinition
   WHERE DeveloperName = 'Lead_Auto_Create_Org' AND IsDeleted = 0
   LIMIT 1
);

SET @active_flow_id := (
  SELECT ActiveVersionId FROM FlowDefinition WHERE Id = @flow_def_id
);

-- Locate '$.recordUpdates[N].name' where name='Update_Org'.
SET @update_org_name_path := (
  SELECT JSON_UNQUOTE(JSON_SEARCH(
    Metadata, 'one', 'Update_Org', NULL,
    '$.recordUpdates[*].name'))
  FROM Flow WHERE Id = @active_flow_id
);

-- Strip the trailing '.name' to get the element path; append '.inputAssignments'.
SET @ia_path := REPLACE(@update_org_name_path, '.name', '.inputAssignments');

-- Idempotency: skip if Account__c is already in inputAssignments.
SET @existing_account_path := (
  SELECT JSON_UNQUOTE(JSON_SEARCH(
    JSON_EXTRACT(Metadata, @ia_path), 'one', 'Account__c'))
  FROM Flow WHERE Id = @active_flow_id
);

UPDATE Flow
SET Metadata = JSON_ARRAY_APPEND(
      Metadata,
      @ia_path,
      JSON_OBJECT(
        'field', 'Account__c',
        'processMetadataValues', JSON_ARRAY(),
        'value', JSON_OBJECT(
          'apexValue', NULL,
          'booleanValue', NULL,
          'complexValue', NULL,
          'complexValueType', NULL,
          'dateTimeValue', NULL,
          'dateValue', NULL,
          'elementReference', '$Record.ConvertedAccountId',
          'formulaDataType', NULL,
          'formulaExpression', NULL,
          'numberValue', NULL,
          'setupReference', NULL,
          'setupReferenceType', NULL,
          'sobjectValue', NULL,
          'stringValue', NULL,
          'transform', NULL,
          'transformValueReference', NULL
        )
      )
    ),
    LastModifiedDate = CURRENT_TIMESTAMP
WHERE Id = @active_flow_id
  AND @ia_path IS NOT NULL
  AND @existing_account_path IS NULL;
```

**Idempotency**: The `@existing_account_path IS NULL` guard ensures a re-run
on an already-patched cluster updates 0 rows. Verified locally:
`rerun_rows_updated = 0` after first apply.

**Cluster portability**: Local TiDB and the staging branch are byte-identical
(33,797 bytes pre-patch on both, with `recordUpdates[0].name = 'Update_Org'`
and 17 inputAssignments lacking `Account__c`). The path-locator pattern uses
`JSON_SEARCH` against `name` rather than a hard-coded array index, so it
remains correct even if a future flow republish reorders elements.

### 2. Verification

```sql
SELECT
  JSON_LENGTH(JSON_EXTRACT(f.Metadata,
    REPLACE(JSON_UNQUOTE(JSON_SEARCH(f.Metadata, 'one', 'Update_Org', NULL,
      '$.recordUpdates[*].name')), '.name', '.inputAssignments'))) AS ia_count,
  JSON_UNQUOTE(JSON_SEARCH(JSON_EXTRACT(f.Metadata,
    REPLACE(JSON_UNQUOTE(JSON_SEARCH(f.Metadata, 'one', 'Update_Org', NULL,
      '$.recordUpdates[*].name')), '.name', '.inputAssignments')),
    'one', 'Account__c')) AS account_at_path
FROM Flow f
JOIN FlowDefinition d ON f.DefinitionId = d.Id
WHERE d.DeveloperName = 'Lead_Auto_Create_Org'
  AND f.Id = d.ActiveVersionId;
```

Expected (post-apply): `ia_count = 18`, `account_at_path` non-null (e.g.
`"$[17]"`).

### 3. Cache & runtime visibility

The flow engine queries `FlowDefinition` and the active `Flow` row on every
record event (no long-lived in-memory parsed-metadata cache); confirmed via
backend log inspection during issue #1792 reproduction. No backend restart
is required after applying this spec — the next AfterSave dispatch picks up
the new `Account__c` assignment immediately.

## Rollback

```sql
SET @flow_def_id := (
  SELECT Id FROM FlowDefinition
   WHERE DeveloperName = 'Lead_Auto_Create_Org' AND IsDeleted = 0
   LIMIT 1
);
SET @active_flow_id := (
  SELECT ActiveVersionId FROM FlowDefinition WHERE Id = @flow_def_id
);
SET @update_org_name_path := (
  SELECT JSON_UNQUOTE(JSON_SEARCH(
    Metadata, 'one', 'Update_Org', NULL,
    '$.recordUpdates[*].name'))
  FROM Flow WHERE Id = @active_flow_id
);
SET @ia_path := REPLACE(@update_org_name_path, '.name', '.inputAssignments');
-- JSON_SEARCH on the inputAssignments array returns the path to the
-- matched STRING property (e.g. '$[17].field'), not to the array
-- element. We strip the trailing '.field' to get the element index, so
-- JSON_REMOVE removes the whole assignment object — not just its
-- `field` key (which would leave a malformed assignment behind).
SET @account_field_path := (
  SELECT JSON_UNQUOTE(JSON_SEARCH(
    JSON_EXTRACT(Metadata, @ia_path), 'one', 'Account__c'))
  FROM Flow WHERE Id = @active_flow_id
);
-- '$[17].field' → '[17]'
SET @account_idx_only := REPLACE(REPLACE(REPLACE(@account_field_path, '.field', ''), '"', ''), '$', '');
-- '$.recordUpdates[0].inputAssignments' + '[17]'
SET @full_account_path := CONCAT(@ia_path, @account_idx_only);

UPDATE Flow
SET Metadata = JSON_REMOVE(Metadata, @full_account_path),
    LastModifiedDate = CURRENT_TIMESTAMP
WHERE Id = @active_flow_id
  AND @account_field_path IS NOT NULL
  AND @ia_path IS NOT NULL;
```

## Apply order

1. **Local** — `mysql -h 127.0.0.1 -P 4000 -u root nexuscrm < apply.sql`.
   Verify per Verification SELECT.
2. **Staging** (`STAGING_DB_*` in `~/codex/tidbcloud/.env`) — apply identical
   SQL after the code PR's CheckUniqueness fix has shipped to staging
   (otherwise S2's reproduction harness still hits the soft-delete uniqueness
   defect; S5 alone is fine in any order).
3. **Prod** — manual application by a maintainer with explicit user approval
   per `~/.claude/projects/.../memory/feedback_no_modify_prod_db.md`.