ENGR-11638: LPL Missing Trust Account Investigation

Summary

Issue: Advisor reports that a Trust account ($335,000 balance) is not showing in the Link Account > LPL screen.

Affected Resources:

• Advisor ID: 450656
• Household ID: 1266043
• Rep Code: 4EEE
• Account Type: Q - Personal Trust
• LPL Account ID: 68795520
• Current Balance: $601,360.43 (per API response)

Status: Under Investigation

Reporter: Tyler Bernier Assignee: Yan Hu Created: 2026-03-12

Evidence

account.json (LPL API Response)

{
  "data": {
    "acctVal": 601360.43,
    "repId": "4EEE",
    "clntGrp": "Amy Rose Beiner",
    "acctName": "Beiner Amy",
    "acctClass": "SAM - Non-Retirement - Clt",
    "accountValue": 601360.43,
    "nwAum": 0,
    "totalAccountValue": 601360.43,
    "invstObj": "C - Growth with Income",
    "family": null,
    "product": null,
    "advFee": 1.15,
    "openedDt": "2021-12-17T00:00:00",
    "closedDt": null,
    "clientFeePercent": 1.15,
    "accountId": 68795520,
    "clientId": 19805474,
    "accountStatus": "Open",
    "acctType": "Q - Personal Trust",
    "accountLocationId": 1
  },
  "time": "2026-03-13T01:48:56-04:00",
  "user": {
    "id": 450656,
    "email": "jane@scarbroughfinancial.com",
    "type": "advisor"
  },
  "advisorId": 450656,
  "householdId": 1266043
}

Key Observations:

• ✅ Account status is "Open" (not closed)
• ✅ Account type is "Q - Personal Trust" (matches reported issue)
• ⚠️ Field name: accountLocationId (camelCase with lowercase ‘d’)
• ⚠️ Balance discrepancy: Ticket says $335K, API returns $601K (likely balance changed)

Technical Investigation

API Flow

Frontend: Link Account > LPL
  ↓
GET /advisors/{advisor_id}/integrations/{integration_id}/entities
  ↓
EntityController::index() → LplIntegrator::listEntities()
  ↓
Returns households list for selection
  ↓
User selects household → saveHousehold()
  ↓
getChildrenData() → getAccounts() → filters accounts
  ↓
Returns valid accounts for linking

Account Filtering Logic

Location: retail-api/app/Integrations/Lpl/Integrator.php:579-595

foreach ($accounts as $account) {
    self::logToFile(strval($account['accountID']), 'account', $account);

    // Filter out Closed lpl accounts
    if ($account['accountStatus'] === InvestmentAccount::ACCOUNT_STATUS_CLOSED) {
        continue;
    }

    // Filter out invalid sub accounts by accountLocationID
    if (isset($account['accountLocationID']) &&
        in_array($account['accountLocationID'], InvestmentAccount::IGNORED_ACCOUNT_LOCATION_IDS, true)) {
        continue;
    }

    $valid_accounts[] = $account;
}

Ignored Account Location IDs

Location: retail-api/app/Integrations/Lpl/Models/InvestmentAccount.php:27-32

public const array IGNORED_ACCOUNT_LOCATION_IDS = [
    128,
    256,
    512,
    1_024,
];

The Trust account has accountLocationId: 1, which is NOT in the ignored list.

Findings

1. Field Name Case Mismatch (Potential Non-Issue)

Expected by Code: accountLocationID (ID in uppercase) Returned by API: accountLocationId (camelCase)

Impact: The isset($account['accountLocationID']) check returns false, causing the filter to be skipped. Since the Trust account’s location ID (1) is not in the ignored list anyway, this mismatch doesn’t prevent the account from appearing.

Conclusion: This is not the root cause of the missing account issue.

2. Account Should Pass All Filters

Based on the API response data:

• ✅ accountStatus: "Open" → Passes “Closed” filter
• ✅ accountLocationId: 1 → Not in IGNORED_ACCOUNT_LOCATION_IDS (128, 256, 512, 1024)
• ✅ Account type "Q - Personal Trust" → Maps to InvestmentAccountSubtype::TRUST

The account should be included in the $valid_accounts list.

Next Steps

Investigation Tasks

1. Check if account is already linked:

   SELECT
     a.id,
     a.name,
     a.source,
     im.reference AS lpl_account_reference
   FROM accounts a
   JOIN integration_mappings im ON im.mappable_id = a.id AND im.mappable_type = 'account'
   JOIN integrations i ON i.id = im.integration_id
   WHERE a.household_id = 1266043
     AND a.source = 'lpl'
     AND a.deleted_at IS NULL
     AND im.deleted_at IS NULL

2. Verify integration setup:

   SELECT id, advisor_id, type, reference, credentials, settings
   FROM integrations
   WHERE advisor_id = 450656
     AND type = 'lpl'
     AND deleted_at IS NULL

3. Check integration_mappings for household:

   SELECT
     im.id,
     im.reference,
     im.mappable_type,
     im.mappable_id,
     im.sync_status
   FROM integration_mappings im
   JOIN integrations i ON i.id = im.integration_id
   WHERE i.advisor_id = 450656
     AND i.type = 'lpl'
     AND im.mappable_type = 'household'
     AND im.deleted_at IS NULL

4. Test API call directly:

   • Call /oauth/accountinfoservice/api/search with clientID 19805474
   • Verify account 68795520 is in the response
   • Check actual field names in response

5. Check frontend filtering:

   • Review frontend/app/src/application/client/plan/profile/net-worth/link-integration-account/helpers.tsx
   • Look for client-side account filtering logic

6. Review logs:

   • Check storage/logs/lpl/ for account sync logs
   • Look for any errors or warnings related to account 68795520

Potential Root Causes

1. Account already linked: The Trust account may already be linked to this or another household
2. Client ID mismatch: The account’s clientId (19805474) may not match the household reference being queried
3. Frontend filtering: Additional filtering logic in the frontend may be hiding the account
4. Stale data: The Link Account modal may be caching an old account list
5. Permission/scope issue: The API credentials may not have access to Trust accounts

Related Files

Backend:

• retail-api/app/Integrations/Lpl/Integrator.php - Main integration logic
• retail-api/app/Integrations/Lpl/Models/InvestmentAccount.php - Account model
• retail-api/app/Http/Controllers/Advisors/Integrations/EntityController.php - Entity list API
• retail-api/app/Integrations/Lpl/Connector.php - LPL API connector

Frontend:

• frontend/app/src/application/client/plan/profile/net-worth/link-integration-account/integration-mapping.service.ts - Link account service
• frontend/app/src/application/client/plan/profile/net-worth/link-integration-account/helpers.tsx - Helper functions

Root Cause Analysis

ACT File Data (s3://rightcapital-integration-prd/latest/lpl/jsca/ACT.csv.CD.gz)

column00: 2025-11-10
column01: BETA
column02: 48645995              // Account Number
column04: AP1                   // Account Type
column05: 4EEE                  // Rep ID
column07-10: BEINER FAMILY TRUST DTD 12/18/2014 AMY ROSE BEINER & RANDY BEINER TTEES
column11-16: 740 APOLLO LN, FOSTER CITY CA 94404-2733
column27: 71217526              // API Based Reference

API Response (GET /entities?search=Beiner+Family+Trust)

{
  "reference": "23142941",                    // LPL Client ID (NOT account ID)
  "name": "Beiner Family Trust",
  "address": "740 APOLLO LN, FOSTER CITY, CA",
  "email": "ABEINER@YAHOO.COM",
  "household_id": "eTYIQSwLCnTbQDYCFqu5NA",  // Already linked to another household
  "integration_mapping_id": "jz9lL9wyA0W4rxtFOvkyaQ"
}

Key Findings

This is NOT a bug - this is the expected behavior based on LPL’s data structure:

1. “Beiner Family Trust” is a separate LPL Client (23142941)

   • LPL treats trusts as independent clients, not just accounts
   • Account 48645995 belongs to this Trust client

2. Trust Client is already linked to a different RC Household

   • Current mapping: Client 23142941 → RC Household eTYIQSwLCnTbQDYCFqu5NA
   • Target household: 1266043 (Amy/Randy Beiner personal)

3. Household 1266043 is linked to a different LPL Client

   • Likely linked to Client 19805474 (Amy/Randy personal accounts)
   • This client includes accounts: 2088-3683, 3634-7738, 1852-6812, etc.

LPL Integration Architecture

LPL Side:
  Client 19805474 "Amy Beiner"
    ├─ Account 2088-3683 (Beiner Amy)
    ├─ Account 1852-6812 (Beiner Amy)
    └─ ... other personal accounts

  Client 23142941 "Beiner Family Trust"  ← Separate entity!
    └─ Account 48645995 (Beiner Family) $335K

RC Side:
  Household 1266043 (target)
    ← Linked to Client 19805474

  Household eTYIQSwLCnTbQDYCFqu5NA (unknown)
    ← Linked to Client 23142941  ← This is why the Trust account appears elsewhere

Resolution Options

Option 1: Re-link Trust Client to Household 1266043 (Recommended)

Steps:

1. Find RC Household eTYIQSwLCnTbQDYCFqu5NA that currently has the Trust linked
2. Unlink “Beiner Family Trust” (Client 23142941) from that household
3. In Household 1266043, click “Link Account > LPL”
4. Search for “Beiner Family Trust”
5. Link Client 23142941 to Household 1266043
6. Account 48645995 will automatically import

Impact: The previous household will lose access to the Trust account.

Query to find current household:

-- Decrypt household_id: eTYIQSwLCnTbQDYCFqu5NA
SELECT
  h.id AS household_id,
  h.advisor_id,
  CONCAT(p.first_name, ' ', p.last_name) AS household_name,
  im.reference AS lpl_client_id,
  im.created_at AS linked_at
FROM integration_mappings im
JOIN integrations i ON i.id = im.integration_id
JOIN households h ON h.id = im.mappable_id
LEFT JOIN persons p ON p.household_id = h.id
WHERE i.type = 'lpl'
  AND im.reference = '23142941'
  AND im.mappable_type = 'household'
  AND im.deleted_at IS NULL

Option 2: Coordinate with Other Household Owner

If the Trust should legitimately be shared across multiple households, coordinate with the owner of the other household to determine the proper data structure.

Option 3: Manual Account (Not Recommended)

Create a manual account in Household 1266043 with Trust information, but it won’t sync automatically.

Questions for Advisor

1. Should the Trust account belong to Household 1266043 or the other household?

   • Who is the primary owner/beneficiary?
   • Are Amy and Randy the trustees?

2. What is the other household (eTYIQSwLCnTbQDYCFqu5NA)?

   • Is it a duplicate household?
   • Is it for the Trust itself (separate entity)?

3. LPL Structure Clarification

   • Why did LPL create separate Clients for the Trust vs personal accounts?
   • Should they be consolidated at the LPL level?

Next Steps

1. Identify the conflicting household:

   • Decrypt eTYIQSwLCnTbQDYCFqu5NA to find household ID
   • Contact advisor to understand the relationship

2. Determine correct ownership:

   • Should Trust stay with current household or move to 1266043?

3. Execute appropriate solution:

   • Re-link if needed
   • Document the decision

References

• Jira: ENGR-11638
• ACT File: s3://rightcapital-integration-prd/latest/lpl/jsca/ACT.csv.CD.gz
• LPL Client ID (Trust): 23142941
• LPL Client ID (Personal): 19805474
• Missing Account: 48645995 ($335,665.86)