Skip to content
RightCapital Knowledge Base

File-based Integrations

File-based integrations process data files that Vendors deliver via SFTP. These files are collected by the Collector service and stored in S3, then parsed by Retail API during sync.

Code Location: gitlab.rightcapital.io/php-libs/packages/-/tree/master/libs/integrations-file-based

Architecture

Section titled “Architecture”
flowchart LR
    subgraph Vendor
        VS[Vendor SFTP]
    end

    subgraph Collector
        C[Collector Service]
    end

    subgraph AWS
        S3[(S3 Bucket)]
    end

    subgraph RetailAPI[Retail API]
        Parser[File Parser]
        Sync[Sync Logic]
        DB[(Database)]
    end

    VS -->|1. SFTP| C
    C -->|2. Upload| S3
    S3 -->|3. Read LATEST| Parser
    Parser -->|4. Parse| Sync
    Sync -->|5. Save| DB

Data Flow

Section titled “Data Flow”
  1. Vendor generates data files (daily, typically overnight)
  2. Collector downloads files via SFTP, processes, and uploads to S3
  3. S3 stores files with date-based paths and LATEST symlinks
  4. Retail API reads LATEST files from S3
  5. Parser extracts structured data from CSV/XML files
  6. Sync matches and saves data to database

File Types

Section titled “File Types”

Common file types in file-based integrations:

File TypeContentUsage
AccountsAccount metadata (number, type, name, status)Create/update account records
PositionsHoldings (security, quantity, market value)Update portfolio positions
SecuritiesSecurity master data (CUSIP, ticker, name)Security matching
Tax LotsCost basis, acquisition dateTax lot tracking
TransactionsTrade historyTransaction records

File Structure on S3

Section titled “File Structure on S3”
s3://bucket/vendor-name/
├── rep_code_1/
│   ├── 2024-01-15/
│   │   ├── accounts.csv
│   │   ├── positions.csv
│   │   └── securities.csv
│   ├── LATEST_accounts.csv      # Points to latest file
│   ├── LATEST_positions.csv
│   └── LATEST_securities.csv
└── rep_code_2/
    └── ...

Rep Code

Section titled “Rep Code”

Rep Code is the advisor’s unique identifier in the Vendor’s system. It’s used to:

  • Organize files by advisor on S3
  • Link incoming data to the correct advisor in RightCapital

Note: Some vendors don’t segment by Rep Code - all data is in a single file, and we filter by rep code during parsing.

Creating a File-based Integration

Section titled “Creating a File-based Integration”

Support Workflow

Section titled “Support Workflow”
  1. Advisor contacts Support requesting integration
  2. Support guides advisor to contact Vendor
  3. Advisor requests Vendor to push data to RightCapital
  4. Vendor configures SFTP push to our server
  5. Support creates integration mapping in Admin Center with advisor’s Rep Code

Admin Center Setup

Section titled “Admin Center Setup”
  1. Go to Admin Center → Integrations
  2. Select the Vendor
  3. Enter the advisor’s Rep Code
  4. Save - this creates the integration_mappings record

Parsing Logic

Section titled “Parsing Logic”

The integrations-file-based library handles:

  • File format detection (CSV, fixed-width, XML)
  • Column mapping (Vendor fields → RightCapital fields)
  • Data transformation (date formats, number formats)
  • Validation (required fields, data types)

Vendor-specific Parsers

Section titled “Vendor-specific Parsers”

Each Vendor has custom parsing logic because:

  • File formats differ (CSV delimiters, headers, encodings)
  • Column names vary
  • Data representations differ (date formats, boolean values)
Section titled “Link Account Flow”
  1. Advisor opens Client Portal → Profile → Net Worth → Link Account
  2. Selects a file-based Vendor from their active integrations
  3. System reads LATEST files from S3 for advisor’s Rep Code
  4. Parses file to extract available accounts
  5. Displays account list to advisor
  6. Advisor selects accounts to import
  7. System saves selected accounts to household

Sync Process

Section titled “Sync Process”

During nightly sync:

  1. Read LATEST files for each active integration mapping
  2. Parse files to extract current data
  3. Compare with existing database records
  4. Update changed records, add new ones
  5. Handle deleted accounts (mark as inactive)

Troubleshooting

Section titled “Troubleshooting”

Missing Data

Section titled “Missing Data”
  1. Check if Collector ran successfully
  2. Verify LATEST files exist on S3
  3. Check file contains expected Rep Code
  4. Review parsing logs for errors

Stale Data

Section titled “Stale Data”
  1. Verify Vendor is sending updated files
  2. Check Collector download timestamp
  3. Confirm LATEST symlinks were updated

Parsing Errors

Section titled “Parsing Errors”
  1. Check if Vendor changed file format
  2. Compare current file with expected schema
  3. Look for encoding issues (UTF-8 vs Latin-1)
Section titled “Related”

Source

Section titled “Source”