LPL File Sync Security Matching Inconsistency

Context

Discovered during investigation of ENGR-11630 (LPL - Missing Cash Holding).

LPL uses a hybrid sync architecture:

• API sync: Manual trigger (advisor clicks sync or visits client portal). Holdings processed through InvestmentAccountTrait::saveHoldings().
• File-based nightly sync: External CronJob triggers lpl:sync. Parses POS/SEC/CBS CSV files, matches against existing DB positions via isApiHoldingMatchedFileBasedHolding().

The Problem

API sync and file sync use fundamentally different security matching strategies:

API sync (InvestmentAccountTrait::saveHoldings()):

• Semantic classification: isCash() → force security_id = Security::ID_CASH (13)
• Multi-step matching: Morningstar sec_id → CUSIP → symbol → local security creation
• Consolidation: multiple holdings merged into one position via consolidateHoldingBy()

File sync (Integrator::syncHouseholdFromFiles()):

• Identifier-based matching: isApiHoldingMatchedFileBasedHolding() compares cusip (strict ===) or normalized_symbol
• No consolidation: matches file holdings 1:1 against existing DB positions

After API sync consolidates holdings (e.g., PJLXX + ICA → single cash position with security_id=13), the resulting position has:

• cusip = NULL (from security table, security 13 is generic)
• normalized_symbol = '!13'

File holdings retain their original identifiers (PJLXX: cusip='4812C0332', ICA: cusip=''). The strict comparison NULL === '' is false, so file sync can never match consolidated positions.

Impact

• Cash positions (most common consolidation target) are never updated by nightly file sync
• Any position created by merging multiple holdings with different identifiers is affected
• These positions rely entirely on API sync for updates
• Current mitigation: LPL triggers async API sync when advisor visits client portal, so data staleness is limited in practice

Options Considered

1. Targeted cash handling in file sync

Add special-case logic: if DB position is cash (security_id=13), consolidate all cash-like file holdings first, then match.

• Pros: minimal change, fixes the most visible case
• Cons: only covers cash, doesn’t address the architectural gap

2. Extract resolveSecurityId() from saveHoldings() for reuse

Extract the security matching pipeline (lines 149-169 of InvestmentAccountTrait) into a reusable method. File sync converts file holdings into HoldingInterface objects and calls resolveSecurityId() to get matched_security_id, then matches DB positions by security_id instead of cusip/symbol.

• Pros: covers all consolidation scenarios, keeps matching logic in one place, future matching rule changes automatically apply to both paths
• Cons: file holdings need to be representable as HoldingInterface; matchOrCreateLocalSecurity() should be skipped in file sync context

3. Unified sync pipeline

Both API and file sync feed holdings through the same saveHoldings() pipeline. File sync only parses files into holding arrays.

• Pros: eliminates the inconsistency entirely
• Cons: major refactor, must handle semantic differences (file sync = update only, API sync = create + update + zero-out)

Decision

Proposed: Option 2 — extract resolveSecurityId() from saveHoldings().

This provides the best balance of coverage and implementation cost. The core change is:

Current:
  API sync:  holding → inline matching → security_id → consolidate → save
  File sync: file holding → cusip/symbol guess → match DB position

Proposed:
  API sync:  holding → resolveSecurityId() → consolidate → save
  File sync: file holding → resolveSecurityId() → match DB position by security_id

Key considerations:

• resolveSecurityId() should accept HoldingInterface and return ?int
• File sync should skip matchOrCreateLocalSecurity() (no new security creation from file data)
• isApiHoldingMatchedFileBasedHolding() can be removed once migration is complete
• File sync should consolidate matched file holdings by security_id before updating positions

Priority: Low — current behavior is a design limitation, not a bug. The async API sync on portal visit provides adequate data freshness for most scenarios.