Data Models

Database models, table structures, and design patterns for the Integration system. This document provides a database-centric view of the Integration architecture.

Core Tables Overview

The Integration system centers on two tables: integrations and integration_mappings, which connect to business entities (Household, Account, Insurance, etc.) via polymorphic relationships.

erDiagram
    advisors ||--o{ integrations : has
    integrations ||--o{ integration_mappings : contains
    integration_mappings ||--o{ integration_mappings : "parent-child"
    users ||--o{ integration_mappings : "deleted_by"

    integration_mappings }o--|| households : "mappable (polymorphic)"
    integration_mappings }o--|| accounts : "mappable (polymorphic)"
    integration_mappings }o--|| insurances : "mappable (polymorphic)"
    integration_mappings }o--|| persons : "mappable (polymorphic)"

    accounts ||--o{ positions : "contains (investment accounts only)"
    accounts ||--o| account_yodlees : "satellite (Yodlee only)"

    yodlee_providers ||--o{ yodlee_provider_accounts : has
    yodlee_provider_accounts ||--o{ account_yodlees : contains

    integrations {
        int id PK
        int advisor_id FK
        enum type "IntegrationType"
        varchar reference
        json credentials
        json settings
        datetime failed_since
        tinyint failed_biz_days
        datetime last_completed_at
        datetime created_at
        datetime updated_at
        datetime deleted_at
    }

    integration_mappings {
        int id PK
        int integration_id FK
        int parent_id FK "self-ref"
        enum mappable_type
        int mappable_id
        varchar reference
        enum sync_status
        datetime last_completed_at
        datetime refreshed_at
        int deleted_by_user_id FK
        datetime created_at
        datetime updated_at
        datetime deleted_at
    }

    households {
        int id PK
        enum source "HouseholdSource"
    }

    accounts {
        int id PK
        enum source "AccountSource"
        decimal cash_balance
        int partner_oauth_client_id
    }

    insurances {
        int id PK
        enum source "InsuranceSource"
        decimal premium
        decimal benefit
    }

    positions {
        int id PK
        int investment_account_id FK
        int security_id FK
        varchar reference
        decimal quantity
        decimal cost_basis
    }

    yodlee_providers {
        int reference PK
        varchar name
        varchar favicon
        varchar logo
    }

    yodlee_provider_accounts {
        int reference PK
        int provider_ref
        enum status
    }

    account_yodlees {
        int account_id PK
        int yodlee_account_ref
        int provider_account_ref
        int status_code
    }

integrations Table

Represents the connection between an Advisor and a Vendor.

Schema

Column	Type	Required	Description
id	int	Yes	Primary key
advisor_id	int	Yes	FK → advisors
type	enum	Yes	IntegrationType (47 vendors)
reference	varchar(255)	No	Vendor-specific identifier (e.g., Rep Code)
credentials	json	No	Encrypted auth credentials (OAuth tokens, API keys)
settings	json	No	Sync settings (sync_note, sync_task booleans)
failed_since	datetime	No	When integration started failing
failed_biz_days	tinyint	No	Consecutive business days failed (0-255)
last_completed_at	datetime	No	Last successful sync timestamp
created_at	datetime	Yes	Record creation time
updated_at	datetime	Yes	Record update time
deleted_at	datetime	No	Soft delete timestamp

Key Points

• One integration per advisor-vendor pair: An advisor can have at most one active integration per vendor type
• Credentials encryption: Stored as encrypted JSON, contains OAuth tokens or API keys
• Failure tracking: failed_since + failed_biz_days enable automatic disabling after threshold
• Soft deletes: Preserves history, allows restoration

IntegrationType Enum

47 supported vendors, split into two categories:

API-based (29 types):

ADDEPAR, ADVYZON, ALBRIDGE, ALLIANZ_API, ASSET_BOOK, ASSET_MARK,
BLACK_DIAMOND, BLUELEAF, BRIDGE_FT, CAPITECT, CIRCLE_BLACK, COMMONWEALTH,
DST, FIN_FOLIO, INVESTIGO, LPL, MAX_MY_INTEREST, MORNINGSTAR_ADVISOR_WORKSTATION,
MORNINGSTAR_OFFICE, NATIONWIDE, ORION, PANORAMIX, REDTAIL, RISKALYZE,
SCHWAB_API, SMART_OFFICE, TAMARAC, WEALTH_ACCESS, WEALTHBOX

File-based (19 types):

ALLIANZ, ALTRUIST, APEX, BETTERMENT, FIDELITY, FIRST_CLEARING,
FLOURISH, FOLIO_INVESTING, INTERACTIVE_BROKERS, JACKSON, LPL,
MY529, PACIFIC_LIFE, PERSHING, RAYMOND_JAMES, RBC, SCHWAB, SEI, TRUST_AMERICA

Note: LPL supports both API-based and File-based integration modes.

integration_mappings Table

Links external entities (vendor accounts) to internal objects (Household, Account, etc.).

Schema

Column	Type	Required	Description
id	int	Yes	Primary key
integration_id	int	Yes	FK → integrations
parent_id	int	No	FK → integration_mappings (self-ref, hierarchy)
mappable_type	enum	Yes	Target entity type (8 types)
mappable_id	int	Yes	Target entity ID
reference	varchar(255)	Yes	Vendor’s entity ID
sync_status	enum	No	pending / running / completed
last_completed_at	datetime	No	Last successful sync
refreshed_at	datetime	No	Last refresh operation
deleted_by_user_id	int	No	FK → users (manual deletion tracking)
created_at	datetime	Yes	Record creation time
updated_at	datetime	Yes	Record update time
deleted_at	datetime	No	Soft delete timestamp

Indexes

• (mappable_type, mappable_id) - Polymorphic lookup optimization

IntegrationMappingMappableType Enum

HOUSEHOLD           = 'household'      // Root level
PERSON              = 'person'         // Family members
CONTACT             = 'contact'        // CRM contacts
ACCOUNT             = 'account'        // Financial accounts (PRIMARY)
TARGET_CATEGORY_MIX = 'target_category_mix'
INSURANCE           = 'insurance'      // Insurance policies (PRIMARY)
NOTE                = 'note'           // CRM notes
TASK                = 'task'           // CRM tasks

Data Integration Types (sync actual financial data):

• ACCOUNT - Bank, investment, loan accounts with positions
• INSURANCE - Life, disability, LTC policies

Hierarchical Structure

Mappings form a tree via parent_id:

graph TD
    I[Integration<br/>e.g., Schwab API] --> R[IntegrationMapping<br/>household, parent_id=NULL]
    R --> A1[IntegrationMapping<br/>account]
    R --> A2[IntegrationMapping<br/>account]
    R --> INS[IntegrationMapping<br/>insurance]
    A1 --> P1[Positions synced<br/>with account]

    style R fill:#e1f5fe
    style A1 fill:#fff3e0
    style A2 fill:#fff3e0
    style INS fill:#f3e5f5

Key Rule: Only root mappings (parent_id = NULL) can be synchronized. Child mappings inherit sync status from parent.

Target Entity Tables

accounts Table (Polymorphic Target)

Column	Type	Description
source	AccountSource enum	Origin: YODLEE, SCHWAB, FIDELITY, TWEAK (manual), etc.
cash_balance	decimal	Synced from integration
partner_oauth_client_id	int	OAuth connection reference

AccountSource Enum: 45 sources tracking data origin (includes TWEAK for manual entry, YODLEE for aggregation, plus 43 vendor-specific sources).

insurances Table (Polymorphic Target)

Column	Type	Description
source	InsuranceSource enum	Origin (5 sources)
premium	decimal	Synced from integration
benefit	decimal	Coverage amount

InsuranceSource Enum:

COMMONWEALTH, ADVYZON, JACKSON, ALLIANZ_API, INVESTIGO

households Table (Polymorphic Target)

Column	Type	Description
source	HouseholdSource enum	CRM origin (7 sources)

HouseholdSource Enum:

REDTAIL, WEALTHBOX, ADVYZON, COMMONWEALTH, SMART_OFFICE, ASSET_MARK, LPL

positions Table (Child of Account)

Column	Type	Description
reference	varchar	Integration provider’s position ID
investment_account_id	int	FK → investment_accounts
security_id	int	FK → securities
quantity	decimal	Shares/units
cost_basis	decimal	Original purchase cost

Yodlee Integration (Special Architecture)

Yodlee uses a different integration architecture from the standard Advisor-Vendor pattern. It is a data aggregation service where end-users (clients) connect their financial institutions directly via OAuth/FastLink.

Key Differences

Aspect	Standard Integration	Yodlee
Initiator	Advisor configures connection	Client links their bank accounts
Connection Level	Advisor ↔ Vendor	Client ↔ Financial Institution
Storage	integrations table	Dedicated Yodlee tables
IntegrationType	Exists in enum	Not in enum (separate system)
AccountSource	Matches IntegrationType	YODLEE in AccountSource enum

Yodlee Table Structure

erDiagram
    yodlee_providers ||--o{ yodlee_provider_accounts : has
    yodlee_provider_accounts ||--o{ account_yodlees : contains
    account_yodlees ||--|| accounts : "satellite"

    yodlee_providers {
        int reference PK
        varchar name
        varchar favicon
        varchar logo
    }

    yodlee_provider_accounts {
        int reference PK
        int provider_ref
        enum status
    }

    account_yodlees {
        int account_id PK
        int yodlee_account_ref
        int provider_account_ref
        int status_code
    }

Note: Field names in diagrams are abbreviated. See table schemas below for full column names.

yodlee_providers Table

Represents financial institutions (banks, brokerages) available through Yodlee.

Column	Type	Description
reference	int	PK, Yodlee’s provider ID
name	varchar(255)	Financial institution name (e.g., “Chase”, “Fidelity”)
favicon	varchar(255)	Institution favicon URL
logo	varchar(255)	Institution logo URL
created_at	datetime	Record creation time
updated_at	datetime	Record update time

yodlee_provider_accounts Table

Represents a user’s connection to a financial institution via Yodlee.

Column	Type	Description
reference	int	PK, Yodlee’s provider account ID
yodlee_provider_reference	int	FK → yodlee_providers
status	enum	Connection status (see below)
created_at	datetime	Record creation time
updated_at	datetime	Record update time

YodleeProviderAccountStatus Enum:

LOGIN_IN_PROGRESS, USER_INPUT_REQUIRED, IN_PROGRESS,
PARTIAL_SUCCESS, SUCCESS, FAILED

account_yodlees Table (Satellite)

Yodlee-specific metadata for accounts (1:1 relationship with accounts):

Column	Type	Description
account_id	int	PK/FK → accounts
yodlee_account_reference	int	Yodlee’s account ID (nullable for manual accounts)
yodlee_provider_account_reference	int	FK → yodlee_provider_accounts
additional_status_code	int	Yodlee status code (maps to 30+ status types)
refreshed_at	datetime	Last successful Yodlee sync

Yodlee Data Flow

flowchart LR
    subgraph Client Actions
        FL[FastLink Widget]
        MR[Manual Refresh]
    end

    subgraph Yodlee API
        YA[Yodlee Aggregation]
    end

    subgraph RightCapital
        PA[YodleeProviderAccount]
        AY[AccountYodlee]
        ACC[Account]
        POS[Position]
    end

    FL -->|OAuth| YA
    MR -->|Refresh| YA
    YA -->|Webhook/Poll| PA
    PA -->|Status Update| AY
    AY -->|Sync Data| ACC
    ACC -->|Holdings| POS

Yodlee Additional Status Codes

The additional_status_code in account_yodlees maps to 30 different status types:

Code	Status	Action Required
1-3	LOGIN_IN_PROGRESS, DATA_RETRIEVAL_IN_PROGRESS, ACCT_SUMMARY_RECEIVED	Poll for refresh
4-5	AVAILABLE_DATA_RETRIEVED, PARTIAL_DATA_RETRIEVED	Done (success)
11-12, 26-28, 30	CREDENTIALS_UPDATE_NEEDED, INCORRECT_CREDENTIALS, CONSENT_*, INCORRECT_OAUTH_TOKEN	FastLink Edit flow
9, 14	ADDL_AUTHENTICATION_REQUIRED, INVALID_ADDL_INFO_PROVIDED	FastLink Refresh flow
Others	Various errors	Show error, no action

Action Categories:

• done - Data retrieved successfully
• get_refresh - Poll Yodlee for status update
• put - User must re-authenticate via FastLink Edit
• manual_refresh - User must provide MFA via FastLink Refresh
• fail - Unrecoverable error, show message to user

Data Flow Architecture

flowchart TD
    subgraph Trigger
        NS[Nightly Sync<br/>scheduled]
        MS[Manual Sync<br/>user action]
        WH[Webhook<br/>real-time]
    end

    subgraph Lookup
        IL[Integration.getSynchronizer]
    end

    subgraph Fetch
        API[API-based<br/>Connector.call → Vendor API]
        FILE[File-based<br/>S3 → Parser]
    end

    subgraph Transform
        VM[Vendor Models]
        IM[Internal Models<br/>Account, Position, Insurance]
    end

    subgraph Persist
        DB[(Database)]
        MAP[IntegrationMappings]
    end

    NS --> IL
    MS --> IL
    WH --> IL
    IL --> API
    IL --> FILE
    API --> VM
    FILE --> VM
    VM --> IM
    IM --> DB
    IM --> MAP
    MAP -->|sync_status=completed| DB

Design Principles

1. Polymorphic Mapping

Single integration_mappings table handles all entity types via mappable_type + mappable_id. This enables:

• Unified sync tracking
• Consistent audit trail
• Single query for all linked entities

2. Source Attribution

Business entities (accounts, insurances, households) have source enum columns tracking origin:

• Enables data quality analysis
• Supports vendor-specific business logic
• Audit trail for data provenance

3. Reference-based Linking

External entity IDs stored in reference columns:

• integration_mappings.reference - Vendor’s entity ID
• positions.reference - Vendor’s position ID
• Enables re-sync and deduplication

4. Hierarchical Sync

Tree structure via parent_id:

• Root mapping represents household-level link
• Child mappings track individual accounts/insurances
• Sync operates at root level, cascades to children

5. Soft Deletes

Both core tables use soft deletes (deleted_at):

• Preserve historical data
• Enable restoration of accidentally unlinked accounts
• Track who deleted (deleted_by_user_id)

6. Failure Tracking

Integration health monitored via:

• failed_since - Start of failure period
• failed_biz_days - Count of business days (max 255)
• Auto-disable after threshold exceeded

Model Relationships

// Integration Model (retail-api/app/Models/Integration.php)
class Integration
{
    // Eloquent Relationships
    public function advisor(): BelongsTo;           // → Advisor
    public function integrationMappings(): HasMany; // → IntegrationMapping[]

    // Factory Methods (return class-string or null)
    public function getIntegratorFqcn(): string|null;  // Integrator class name
    public function getConnectorFqcn(): string|null;   // Connector class name
    public function getImporterFqcn(): string|null;    // Importer class name
    public function getSyncFqcn(): string|null;        // Sync class name
    public function getConfigFqcn(): string|null;      // Config class name
    public function getSynchronizer(): Sync;           // Instantiated Sync handler
}

// IntegrationMapping Model (retail-api/app/Models/IntegrationMapping.php)
class IntegrationMapping
{
    // Eloquent Relationships
    public function integration(): BelongsTo;       // → Integration
    public function parent(): BelongsTo;            // → IntegrationMapping (self)
    public function children(): HasMany;            // → IntegrationMapping[] (self)
    public function mappable(): MorphTo;            // → Household|Account|Insurance|...
    public function deletedByUser(): BelongsTo;     // → User

    // Business Logic
    public function sync(): array;                  // Trigger sync (root mappings only)
    public function isDataIntegrationMapping(): bool; // Check if ACCOUNT or INSURANCE type
}

Code Locations

Core Integration Tables

Component	Path
Integration Schema	packages/libs/core-models/src/Schemas/Integration.php
IntegrationMapping Schema	packages/libs/core-models/src/Schemas/IntegrationMapping.php
IntegrationType Enum	packages/libs/core-models/src/Enums/Integration/IntegrationType.php
MappableType Enum	packages/libs/core-models/src/Enums/IntegrationMapping/IntegrationMappingMappableType.php
SyncStatus Enum	packages/libs/core-models/src/Enums/IntegrationMapping/IntegrationMappingSyncStatus.php
AccountSource Enum	packages/libs/core-models/src/Enums/Account/AccountSource.php
Retail API Models	retail-api/app/Models/Integration.php, IntegrationMapping.php
Admin API Models	admin-api/app/Models/Integration.php, IntegrationMapping.php

Yodlee Tables

Component	Path
YodleeProvider Schema	packages/libs/core-models/src/Schemas/YodleeProvider.php
YodleeProviderAccount Schema	packages/libs/core-models/src/Schemas/YodleeProviderAccount.php
AccountYodlee Schema	packages/libs/core-models/src/Schemas/AccountYodlee.php
YodleeProviderAccountStatus Enum	packages/libs/core-models/src/Enums/YodleeProviderAccount/YodleeProviderAccountStatus.php
Yodlee Integrator	retail-api/app/Integrations/Yodlee/Integrator.php
Yodlee Connector	retail-api/app/Integrations/Yodlee/Connector.php
Yodlee API Client	retail-api/app/Integrations/Yodlee/Api.php

Related Documentation

• Architecture - System architecture overview
• Code Locations - Repository and file locations
• API-based Patterns - Connector and Integrator patterns
• Sync Lifecycle - Job flow and error handling