Split out of #598 scope 2 during round-4 dispatch. Blocked-by: #625.
Why it is separate
lead_conversion.flow.ts dedupes accounts by exact-string company name, so "Acme Corp" and "ACME Corp" produce two accounts (the flow's own comment concedes this). Fixing it turns out to need a schema change, not a flow tweak — and that schema change collides with #625.
Measured constraints from the #598 investigation (17.0.0-rc.1, all verified against the shipped packages):
- The flow cannot normalize.
@objectstack/service-automation's resolveToken recognises exactly one function form — /^(NOW|TODAY)\s*\(\s*\)\s*(?:([+\-])\s*(\S+))?$/. There is no LOWER / TRIM / REPLACE, so "normalize then compare" is not expressible in a flow template.
- A formula field cannot be the match key.
driver-sql's fieldHasColumn returns (field?.type ?? 'string') !== 'formula', so a computed name_normalized has no physical column to filter on.
$regex is not an answer. It is the only case-insensitive operator ObjectQL offers, it is unindexed (full scan of crm_account on every conversion), it cannot collapse internal whitespace, and it would interpolate user-controlled text into a regex.
So the only sound shape is a stored, hook-maintained name_normalized column on crm_account, plus a one-time backfill — because the column starts NULL on existing accounts, and without the backfill the conversion flow matches nothing and creates more duplicate accounts than today.
Why it is blocked by #625
#625 is about the same constraint on the same object: crm_account declares { fields: ['name'], unique: true } as a table index, which driver-sql keeps verbatim with no tenant scoping, producing a platform-wide UNIQUE (name). Fixing that and adding a normalized match column are two changes to crm_account's uniqueness story that want one maintenance window and one runbook entry. Landing them separately puts deployments through an intermediate state neither change anticipated.
Scope (once #625 lands)
- Add a stored
name_normalized field on crm_account, maintained by a beforeInsert / beforeUpdate hook (lowercase, trim, collapse internal whitespace).
- Backfill existing accounts, with the operator step documented.
- Point
lead_conversion.flow.ts at the normalized column. Normalize-then-exact only — fuzzy matching stays out of scope, as the flow comment already states.
- Decide whether the normalized column carries its own unique index. Note the measured hazard:
create_index fails on any deployment that already holds Acme Corp and ACME Corp separately (driver: "Creating the UNIQUE index can fail on existing duplicates"), so a unique index cannot be the upgrade step without a merge pass first.
Acceptance
- Converting a lead whose company is
"ACME Corp" reuses the account created from "Acme Corp".
- A fresh install and a backfilled install behave identically.
Split out of #598 scope 2 during round-4 dispatch. Blocked-by: #625.
Why it is separate
lead_conversion.flow.tsdedupes accounts by exact-string company name, so"Acme Corp"and"ACME Corp"produce two accounts (the flow's own comment concedes this). Fixing it turns out to need a schema change, not a flow tweak — and that schema change collides with #625.Measured constraints from the #598 investigation (17.0.0-rc.1, all verified against the shipped packages):
@objectstack/service-automation'sresolveTokenrecognises exactly one function form —/^(NOW|TODAY)\s*\(\s*\)\s*(?:([+\-])\s*(\S+))?$/. There is noLOWER/TRIM/REPLACE, so "normalize then compare" is not expressible in a flow template.driver-sql'sfieldHasColumnreturns(field?.type ?? 'string') !== 'formula', so a computedname_normalizedhas no physical column to filter on.$regexis not an answer. It is the only case-insensitive operator ObjectQL offers, it is unindexed (full scan ofcrm_accounton every conversion), it cannot collapse internal whitespace, and it would interpolate user-controlled text into a regex.So the only sound shape is a stored, hook-maintained
name_normalizedcolumn oncrm_account, plus a one-time backfill — because the column starts NULL on existing accounts, and without the backfill the conversion flow matches nothing and creates more duplicate accounts than today.Why it is blocked by #625
#625 is about the same constraint on the same object:
crm_accountdeclares{ fields: ['name'], unique: true }as a table index, whichdriver-sqlkeeps verbatim with no tenant scoping, producing a platform-wideUNIQUE (name). Fixing that and adding a normalized match column are two changes tocrm_account's uniqueness story that want one maintenance window and one runbook entry. Landing them separately puts deployments through an intermediate state neither change anticipated.Scope (once #625 lands)
name_normalizedfield oncrm_account, maintained by abeforeInsert/beforeUpdatehook (lowercase, trim, collapse internal whitespace).lead_conversion.flow.tsat the normalized column. Normalize-then-exact only — fuzzy matching stays out of scope, as the flow comment already states.create_indexfails on any deployment that already holdsAcme CorpandACME Corpseparately (driver: "Creating the UNIQUE index can fail on existing duplicates"), so a unique index cannot be the upgrade step without a merge pass first.Acceptance
"ACME Corp"reuses the account created from"Acme Corp".