入职自测题答案

本文是 Onboarding Guide 自测题的参考答案。完成每个阶段后，可以用本文核对理解是否正确。

阶段一：基础知识

Integration 对 Advisor 的业务价值是什么？

Financial advisor 的客户（Household）通常在多个金融机构持有资产，包括银行存款、brokerage accounts 和 insurance policies。没有 integration 时，advisor 必须手动把所有客户数据录入 RightCapital，既耗时又容易出错。

Integration 提供三项核心价值：

1. 快速接入：Advisor 可以连接 vendor accounts，在几分钟内导入客户数据，而不是耗费数小时。
2. 自动同步：Nightly Sync 每天更新 holdings，无需人工操作。
3. 数据准确：数据直接来自金融机构，减少手工录入错误。

RightCapital currently integrates with 46+ vendors，覆盖 custodians、portfolio management platforms、CRMs、data aggregators 和 insurance providers。file-based vendor 正在从 legacy parser 迁移到统一 ELT；具体上线状态以 retail-api 的 IntegrationType::isMigratedToElt() 为准，不要继续使用固定的“27 API + 19 file-based”作为架构判断。

Integration 的主要类型有哪些（Data、SSO、CRM）？

类型	用途	示例
Data Integration	同步 financial data：accounts、positions/holdings、insurance policies	Schwab API、Fidelity (file)、Orion、Addepar
SSO (Single Sign-On)	让 advisor 无需重新认证即可从 vendor portal 进入 RightCapital	Schwab SSO、Fidelity SSO via SAML 2.0
CRM Integration	从 CRM 系统导入 household/contact 信息	Wealthbox、Redtail、Smart Office

此外，团队还负责 Morningstar Security Master，即证券价格和基本面数据 pipeline。它不完全属于上述分类，但仍是 Integration Team 的职责范围。

Data Integration 是核心工作，占据了大部分开发和运维工作量。

File-based 和 API-based integration 有什么区别？

维度	File-based	API-based
Data source	Vendor 通过 SFTP 推送文件（CSV/Excel）	RightCapital 调用 vendor REST/SOAP API
Data flow	已迁移：Vendor SFTP → Collector → raw S3 → Lambda → normalized S3 → ELT/Snowflake → Retail API → DB；legacy：S3 → Retail API parser → DB	Retail API → Vendor API → DB
Authentication	无（文件通过 SFTP 传输）	OAuth 2.0 / JWT / API Key / SOAP / mTLS
Advisor setup	被动配置：Support 在 Admin Center 配置 Rep Code	主动授权：Advisor 自助完成 OAuth authorization
Data freshness	取决于 vendor 推送计划（通常每天一次）	通过按需调用或 webhook 接近实时
Complexity	中等（解析、字段 mapping）	较高（状态机、错误恢复、token refresh）
状态	12 个 vendor 已完成 Retail API ELT cutover，其余按 legacy 或迁移中处理	仍由 Retail API 直接调用 Vendor API
Examples	Fidelity, Pershing, Schwab (file), SEI	Orion, Schwab API, Addepar, Wealthbox

部分 vendor 同时支持两种模式（例如 Schwab、LPL、Betterment）。

阶段二：Collector

Collector 的作用是什么？

Collector 是独立服务（仓库：integrations/collector），从 vendor SFTP 下载或接收数据文件，完成文件级处理，并将 raw 文件上传到 S3。ELT vendor 的后续消费由 file-based-integration-lambda 和 integrations/elt 完成；只有 legacy vendor 由 Retail API 直接消费 S3 文件。

Collector 是所有 file-based integration 的文件采集入口。如果没有 Collector，Retail API 就需要分别连接每个 vendor 的 SFTP。

数据流：ELT vendor 为 Vendor SFTP → Collector → raw S3 → Lambda → normalized S3 → integrations/elt → Snowflake；legacy vendor 为 Vendor SFTP → Collector → S3 latest → Retail API。

Collector 如何与 Vendor 通信（SFTP 等）？

Collector 根据每个 vendor 的配置连接其 SFTP 服务：

• 连接：使用 vendor-specific host、port 和 credentials（password 或 SSH key）的 SFTP protocol。
• 文件选择：通过 pattern matching rules 识别目标文件。
• 下载策略：按日期或 timestamp 过滤，只下载新文件。

注意：部分 vendor（例如 Pershing）会把文件推送到 RightCapital 自建的 SFTP，而不是提供自己的 SFTP。Collector 同时支持拉取和接收这两种方向。

Collector 的处理阶段是什么？

阶段	职责	输入	输出
1. Downloader	连接 vendor SFTP，将文件下载到本地临时目录	Vendor SFTP 上的文件	本地临时目录中的 raw 文件
2. Processor	解压（ZIP/GZ）、校验格式并按类型整理	临时目录中的压缩文件	清理并校验后的文件
3. Uploader	按整理后的目录结构将 raw 文件上传到 S3	已处理文件	S3 中的 raw objects
4. Finalizer	为仍需要该兼容输出的 vendor 创建/更新 LATEST 文件	S3 上的 dated files	LATEST_*；是否由 Retail API 使用取决于 isMigratedToElt() 和 vendor 配置

处理链路：Vendor SFTP → [Downloader/Receive] → temp/ → [Processor] → [Uploader] → raw S3 → (Lambda + ELT 或 legacy Finalizer)

ELT vendor 的最终输出是什么？

对已迁移 vendor，Collector 的输出是 raw S3 object；Lambda 生成 normalized CSV，ELT 生成 Snowflake staging/target tables。部分 vendor 仍可能生成 LATEST 供兼容或其他下游使用，但 Retail API 是否消费它必须以 isMigratedToElt() 和 vendor 配置为准。

对仍由 legacy parser 消费文件的 vendor，S3 上可能有 LATEST-prefixed files：

s3://bucket/vendor-name/
├── rep_code_1/
│   ├── 2024-01-15/
│   │   ├── accounts.csv
│   │   ├── positions.csv
│   │   └── securities.csv
│   ├── 2024-01-16/
│   │   └── ...
│   ├── LATEST_accounts.csv      ← legacy Retail API parser 可能读取
│   ├── LATEST_positions.csv
│   └── LATEST_securities.csv
└── timestamp.txt

legacy Retail API parser 可能读取 LATEST 文件；ELT vendor 则通过 LoadRequest.as_of_date 和 Snowflake target contract 解耦文件到达时间与业务 sync 时间。不能只凭 LATEST 是否存在判断最终链路。

阶段三：API-based Integration

如何与 Vendor 建立连接（OAuth、API key 等）？

Supported authentication methods:

Auth Method	Description	Vendors
OAuth 2.0	Most common. Advisor authorizes in browser → access_token + refresh_token stored in integrations.credentials	Schwab API, Orion, Addepar, LPL
OAuth 2.0 + PKCE	Enhanced security OAuth	Schwab API
JWT (RS512)	RSA private key signs JWT tokens	Yodlee (separate system)
API Key	Static key assigned by vendor	Wealthbox, Redtail
mTLS	Mutual TLS certificate authentication	Commonwealth
WS-Security/SOAP	XML-based SOAP auth	Tamarac
HMAC	Hash-based message signing	Wealth Access
RSA	RSA encrypted authentication	DST
Certificate-based	Client certificate	Albridge

Typical OAuth 2.0 flow (implemented in OauthConnector):

1. Advisor clicks “Connect” → RC redirects to vendor’s authorize URL
2. Advisor authenticates and grants permission on vendor’s page
3. Vendor redirects back to RC with authorization code
4. RC exchanges code for access_token + refresh_token
5. Tokens encrypted and stored in integrations.credentials

Token refresh is handled by ThreadSafeRefreshAccessToken plugin for concurrent-safe token renewal.

Connector/Integrator/Request 架构是什么？

Three-layer architecture built on the Saloon PHP library:

Component	Responsibility	Analogy
Connector	Transport layer — manages vendor API connection: base URL, auth headers, rate limiting, logging	HTTP Client
Integrator	Orchestration layer — implements sync logic: calls Connector, transforms data, persists to database	Service / Use Case
Request	Endpoint definition — each specific API call: HTTP method, path, query params, error handling	API Endpoint Definition

Per-vendor code structure:

app/Integrations/[Vendor]/
├── Connector.php     ← Transport + authentication
├── Integrator.php    ← Sync orchestration
├── Requests/         ← Individual API calls
│   ├── GetAccountsRequest.php
│   └── GetPositionsRequest.php
├── Models/           ← Vendor-specific data models
└── Importer.php      ← Household import logic

Call chain: Controller → Integrator → Connector → Request → Vendor API

API integration 如何获取 Vendor 数据？

Using Nightly Sync as example:

1. Scheduler triggers sync command (after US market close)
2. Command queries all active integration mappings, dispatches a queue job per mapping
3. Worker processes each job:
   • Loads OAuth credentials from integrations table
   • Gets vendor’s Sync handler via Integration::getSynchronizer()
   • Sync handler instantiates Connector (with access_token)
   • Sends Requests to vendor API endpoints
   • Receives account/position/insurance data
4. Integrator transforms vendor response models into RC internal models
5. Persist creates/updates accounts, positions in database

手动同步时，Advisor 点击“Link Account”，系统会按需执行相同的 Integrator 逻辑。

通常获取哪些数据（households、accounts、positions）？

Data Type	Description	mappable_type
Households	Client family unit	HOUSEHOLD
Accounts	Financial accounts (investment, bank, loan)	ACCOUNT
Positions/Holdings	Securities held (ticker, quantity, market value)	Stored in positions table, linked to Account
Insurance	Policies (life, disability, LTC)	INSURANCE
Persons	Family members	PERSON
Contacts	CRM contacts	CONTACT
Notes/Tasks	CRM notes and tasks	NOTE / TASK

Account and Insurance are core data integration types — IntegrationMapping::isDataIntegrationMapping() returns true only for these two.

Vendor 数据如何关联到 RightCapital household？

Through the integration_mappings table, which forms a hierarchical tree:

Integration (Advisor ↔ Vendor connection)
└── IntegrationMapping (parent_id=NULL, mappable_type=HOUSEHOLD)  ← root
    ├── IntegrationMapping (mappable_type=ACCOUNT)  ← child
    ├── IntegrationMapping (mappable_type=ACCOUNT)
    └── IntegrationMapping (mappable_type=INSURANCE)

Link Account flow:

1. Advisor opens Client Portal → Profile → Net Worth → Link Account
2. Selects a connected vendor
3. System calls vendor API via Integrator, displays available households/accounts
4. Advisor selects accounts to import
5. System creates integration_mappings records:
   • Root mapping: mappable_type=HOUSEHOLD, parent_id=NULL, linked to RC Household
   • Child mappings: mappable_type=ACCOUNT/INSURANCE, parent_id → root mapping
   • Each record’s reference field stores the vendor’s entity ID

Key rule: Only root mappings (parent_id=NULL) can trigger synchronization. Child mappings inherit sync from their parent.

IntegrationConfigController 做什么？

Manages integration lifecycle operations:

• Create new integration (CREATE_INTEGRATION scenario) — OAuth flow initiation
• Configure vendor connection settings
• Disconnect integration (soft delete)

API endpoints:

• POST /advisors/{advisor}/integrations — Create integration
• DELETE /advisors/{advisor}/integrations/{integration} — Delete integration

IntegrationImportController 做什么？

Handles bulk import of household and client data, primarily for CRM integrations:

• IMPORT_HOUSEHOLD — Batch import vendor households into RC
• IMPORT_CLIENTS — Import client/contact data
• LIST_TAGS — Retrieve CRM tags/categories for filtering

这与 Data Integration（account/position sync）不同。Import 主要负责从 Wealthbox、Redtail、Smart Office 等 vendor 导入 CRM 数据。

阶段四：File-based Integration

Vendor 数据从哪里来？如何判断 vendor 走 ELT 还是 legacy？

File-based data 从 Collector 上传到 S3 的 raw 文件开始。已迁移 vendor 继续经过 Lambda normalize 和 ELT；尚未完成 cutover 的 legacy vendor 才由 Retail API 直接读取 S3 的 LATEST 文件。注意，部分已完成 ELT cutover 的 vendor 仍可能由 Lambda 生成 LATEST 供兼容或其他用途，不能只凭文件是否存在判断最终消费者。

ELT vendor 的 chain:

Vendor → SFTP push → Collector → raw S3 → Lambda normalize → normalized S3 → ELT/Snowflake
                                                                            ↓
                                             completion SNS → Retail API → Save to database

Legacy chain 才是 S3 → LATEST → Retail API parser → Database。integrations-file-based package 主要承载该 legacy parser；已迁移 vendor 的文件解析和 mapping 在 Lambda/ELT 中完成。

文件中通常有哪些数据（accounts、positions、securities）？

文件类型	内容	常见格式	用途
Accounts	Account metadata（number、type、name、status）	CSV	创建或更新 account records
Positions	Holdings（security、quantity、market value）	CSV	更新 portfolio positions
Securities	Security master data（CUSIP、ticker、name）	CSV	Security matching
Tax Lots	Cost basis、purchase date	CSV	Tax lot tracking
Transactions	Trade history	CSV	Transaction records

不同 vendor 的格式可能使用不同 delimiter（逗号、tab、pipe）、列名、编码（UTF-8 或 Latin-1）和日期格式。legacy parser 或 Lambda 中的 vendor-specific 逻辑负责处理这些差异。

文件数据如何关联到 RightCapital household？

通常通过 Rep Code 关联——它是 Advisor 在 vendor 系统中的唯一标识。

关联流程：

1. Support 在 Admin Center 创建 integrations record，并将 advisor 在 vendor 侧的 Rep Code 写入 reference 字段。
2. ELT vendor：LoadRequest 携带 vendor、as_of_date 和 tenant，ELT 将 normalized data 写入 Snowflake target tables；legacy vendor：Retail API 从匹配 Rep Code 的 S3 目录读取 LATEST 文件
3. ELT vendor 从 target tables 获取 accounts；legacy vendor 由 parser 从文件中提取 accounts。
4. Advisor 在 Client Portal 使用 Link Account 选择要导入的 accounts。
5. 系统创建 integration_mappings 子记录，并关联到对应 Household。

与 API-based 的关键区别：File-based mapping 需要 Support 手动配置 Rep Code；API-based mapping 则由 advisor 通过 OAuth 自助完成。

阶段五：Sync 流程

用一句话说明 Sync 是什么？

Nightly Sync 是一个定时 batch job，通常每天在 US market close 后运行，获取已连接 vendor 的最新数据并更新 RightCapital database。具体数据可能来自 Vendor API、Snowflake target tables 或 legacy parser，取决于 vendor 的当前 contract。

哪些数据会被同步？

数据类型	来源	操作
Accounts	Vendor 或 Snowflake target tables	Create / Update / Mark stale
Positions	Vendor 或 Snowflake target tables	用最新 holdings 全量替换
Holdings	Vendor 或 Snowflake target tables	更新 quantity 和 market value
Prices	Morningstar	更新 security prices
Insurance	Vendor（如果支持）	更新 premium 和 benefit amounts

Holding 是什么？

A Holding（存储在 positions table 中）表示一个 account 内的特定 securities position。

每条 holding record 包含：

• security_id — Which security (matched via Morningstar Security Master)
• quantity — Number of shares/units held
• cost_basis — Original purchase cost
• reference — Vendor’s position ID
• investment_account_id — The investment account that holds this position

例如：客户在 Schwab account 中持有 100 股 AAPL，cost basis 为 $15,000，这就是一条 Holding。

Holdings 如何同步？

API-based sync：

1. Load OAuth credentials for the mapping’s integration
2. Call vendor API to get current positions list
3. Compare with existing positions in database:
   • New positions → INSERT
   • Changed quantity/value → UPDATE
   • No longer returned by vendor → Mark stale or delete
4. Update integration_mappings.last_completed_at

File-based sync：

1. 对 legacy vendor，从 S3 读取 LATEST positions file；对 ELT vendor，从 Snowflake target tables 读取 positions。
2. legacy 路径解析 CSV 并按 Rep Code 过滤；ELT 路径使用已标准化的 contract。
3. Match securities (via CUSIP/ticker → Morningstar Security Master)
4. Same INSERT/UPDATE/DELETE logic
5. Update sync status

Holdings are typically full-replaced (not incrementally updated) to ensure data consistency.

Holdings 有哪些类型？

按 account type：

Account Type	Holding 特征
Investment Account	Securities positions (stocks, bonds, mutual funds, ETFs)
Retirement Account (401k, IRA)	Retirement investment positions
Bank Account	Usually only cash_balance, no securities positions
Loan Account	Liability, no holdings concept

按数据来源：AccountSource enum（45 个值）记录来源，包括 YODLEE、SCHWAB、FIDELITY、TWEAK（manual entry）以及其他 41 个 vendor-specific source。

阶段六：数据库 Schema

integrations 中的一行代表什么？

A row in integrations 表示 一个 advisor 与一个 vendor 的连接。

Key fields:

• advisor_id — FK → advisors table
• type — IntegrationType enum (47 vendor types)
• reference — Vendor-side identifier (e.g., Rep Code for file-based)
• credentials — Encrypted JSON containing OAuth tokens or API keys
• failed_since / failed_biz_days — Failure tracking; auto-disables after threshold

Constraint: Each advisor can have at most one active integration per vendor type.

integrations 中的 reference 字段是什么？

The reference field stores the advisor’s identifier in the vendor’s system. Its meaning varies by integration type:

• File-based: Rep Code — used to locate the correct files on S3 (s3://bucket/vendor/{rep_code}/)
• API-based: May be empty (identity established via OAuth token) or store a vendor-assigned ID

credentials 和 reference 有什么区别？

Field	Purpose	Content
reference	Identity — who this advisor is at the vendor	Rep Code, vendor-assigned ID
credentials	Authentication — how to access vendor API	OAuth tokens, API keys, JWT secrets

Analogy: reference is your employee badge number; credentials is your login password.

credentials is encrypted JSON. Typical content:

{
  "access_token": "eyJ...",
  "refresh_token": "abc123...",
  "expires_at": "2024-01-15T10:00:00Z"
}

integration_mappings 代表什么？

The integration_mappings table maps vendor external entities to RightCapital internal objects.

integration_mappings 中的 reference 字段是什么？

The reference field stores the vendor’s entity ID — the unique identifier for this specific entity (account, household, etc.) in the vendor’s system. Used for deduplication and re-sync.

parent_id 为 null 和非 null 分别表示什么？

• parent_id = NULL → Root mapping: Represents a household-level link. Only root mappings can trigger synchronization.
• parent_id ≠ NULL → Child mapping: Represents an account, insurance, or other entity nested under a household. Inherits sync behavior from its parent.

Tree structure example:

Integration (Schwab API, advisor_id=123)
└── Mapping #1 (HOUSEHOLD, parent_id=NULL, reference="vendor_hh_001")
    ├── Mapping #2 (ACCOUNT, parent_id=1, reference="vendor_acct_001")
    ├── Mapping #3 (ACCOUNT, parent_id=1, reference="vendor_acct_002")
    └── Mapping #4 (INSURANCE, parent_id=1, reference="vendor_ins_001")

mappable_type 有哪些值，分别表示什么？

mappable_type	Meaning	Notes
HOUSEHOLD	Client family unit	Root node, typically parent_id=NULL
ACCOUNT	Financial account	Bank, investment, loan (core data type)
INSURANCE	Insurance policy	Life, disability, LTC (core data type)
PERSON	Family member	Links to persons table
CONTACT	CRM contact	From CRM integrations
TARGET_CATEGORY_MIX	Target asset allocation	Investment portfolio target
NOTE	CRM note	From CRM integrations
TASK	CRM task	From CRM integrations

ACCOUNT and INSURANCE are the two data integration types — IntegrationMapping::isDataIntegrationMapping() returns true only for these.

阶段七：SSO

Service Provider（SP）是什么？

The SP (Service Provider) is the system that receives authentication assertions and provides the service. In Integration Team’s SSO implementations, RightCapital is the SP — we receive SAML assertions from vendors, validate them, and create user sessions.

Identity Provider（IdP）是什么？

The IdP (Identity Provider) is the system that authenticates users and issues identity information. In IdP-initiated SSO, the vendor is the IdP (e.g., Schwab, Fidelity) — they have already verified the advisor’s identity and transmit it to RC via SAML assertion.

什么是 IdP-initiated SSO flow？

The most common SSO pattern. Advisor starts from the vendor’s system and lands in RightCapital:

1. Advisor logs into vendor portal
2. Clicks “Open in RightCapital” link
3. Vendor generates a SAML Assertion (signed XML document containing user identity)
4. Vendor POSTs the SAML Assertion to RightCapital’s SSO endpoint
5. RightCapital validates signature and assertion
6. Looks up the corresponding Advisor via identifier in the assertion (email/advisor ID)
7. Creates session, redirects to application

什么是 SP-initiated SSO flow？

Advisor starts from RightCapital and authenticates through the vendor:

1. Advisor clicks “Login with [Vendor]” on RightCapital
2. RC redirects to vendor’s login page
3. Advisor authenticates at vendor
4. Vendor POSTs SAML Assertion back to RC
5. RC validates and creates session

Less commonly used than IdP-initiated.

如何校验 SAML Response？校验哪些字段？

Validation	What is Checked	Common Failure Cause
Signature	XML digital signature verified using vendor’s public X.509 certificate	Certificate expired or rotated without notification
Issuer	Must match expected vendor identifier	Configuration mismatch
Audience	Must match RC’s SP Entity ID	Configuration mismatch
NotBefore / NotOnOrAfter	Assertion must be within valid time window	Server clock skew
Subject	Contains user identifier (email or advisor ID)	User not found in RC
Conditions	Additional business rule checks	Vendor-specific constraints

Common issues:

• User Not Found: Identifier in SAML does not match any RC advisor record
• Invalid Signature: Vendor rotated their certificate without notifying RC
• Expired Assertion: Clock drift between vendor and RC servers exceeds tolerance

Code location: api/app/Http/Controllers/Sso/ — each vendor has its own SSO controller.

附录：Yodlee 特殊架构

Yodlee is the most complex integration and operates as a completely separate system from the standard Integration architecture:

Aspect	Standard Integration	Yodlee
Initiator	Advisor configures connection	End-user (client) links their bank accounts
Connection level	Advisor ↔ Vendor	Client ↔ Financial Institution
Storage	integrations + integration_mappings	Dedicated yodlee_providers + yodlee_provider_accounts + account_yodlees tables
IntegrationType	Listed in enum	Not in enum (separate system)
Authentication	OAuth 2.0 (Saloon)	JWT RS512 (direct Guzzle)
Sync mode	Nightly Sync auto-trigger	Passive refresh (user-triggered)

Yodlee uses 30+ status codes (additional_status_code in account_yodlees):

Action Category	Meaning	Status Codes
done	Data retrieved successfully	4, 5
get_refresh	Poll Yodlee for status update	1, 2, 3
put	User must re-authenticate via FastLink Edit	11, 12, 26-28, 30
manual_refresh	User must provide MFA via FastLink Refresh	9, 14
fail	Unrecoverable error	Various others

See Yodlee vendor documentation for full details including test account credentials and FastLink widget modes.

相关文档

• Onboarding Guide — Learning path with tasks and resources
• Architecture — System architecture overview
• Data Models — Database schema details
• API-based Patterns — Connector/Integrator patterns
• File-based Patterns — Parser/Mapper design
• Vendor Catalog — All 46+ vendor integrations