File-based Integration Patterns

This document covers the patterns and code organization for file-based integrations that process user-uploaded files (CSV, Excel) instead of connecting to external APIs.

Core Differences from API-based

Aspect	API-based	File-based
Data Source	Real-time API calls	User-uploaded files
Authentication	OAuth/API Key/JWT	None (file validation)
Sync Trigger	Manual + Scheduled + Webhook	Manual upload only
State Management	Complex (token refresh, retries)	Simple (parse or fail)
Error Recovery	Automatic retries	User re-uploads
Data Freshness	Real-time or near real-time	As fresh as uploaded file

Shared Package

Location: /packages/libs/integrations-file-based/

All 19 file-based integrations share this package for common functionality:

• File upload handling
• Format validation
• Parsing utilities
• Error handling

Class Structure Pattern

app/Integrations/[Vendor]/
├── Integrator.php       # Extends file-based base, main entry point
├── Parser.php           # File format parsing (CSV/Excel)
├── Mapper.php           # Map parsed data to internal models
├── Validator.php        # File and data validation
├── Models/              # Vendor-specific data structures
│   ├── Account.php
│   ├── Holding.php
│   └── Position.php
└── Exceptions/          # Vendor-specific errors

Processing Flow

┌─────────────────────────────────────────────────────────────────────────────┐
│                           User Uploads File                                  │
│                    (via UI or API endpoint)                                  │
└─────────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                          File Storage (S3)                                   │
│                                                                              │
│   Path: integrations/{integration_id}/{reference}/file.csv                   │
│   Reference from: $integration->file_reference                               │
└─────────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                         1. File Validation                                   │
│                                                                              │
│   - Check file exists                                                        │
│   - Validate file size (not too large)                                       │
│   - Check file format (CSV/Excel)                                           │
│   - Validate encoding (UTF-8, Latin-1, etc.)                                │
│   - Check file freshness (not outdated)                                     │
└─────────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                         2. Parse File                                        │
│                                                                              │
│   Parser::parse($filePath)                                                   │
│   - Read file contents                                                       │
│   - Parse CSV rows or Excel sheets                                          │
│   - Extract headers and data rows                                            │
│   - Handle format-specific quirks                                           │
└─────────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                         3. Map to Vendor Models                              │
│                                                                              │
│   Mapper::map($parsedData)                                                   │
│   - Map columns to vendor model fields                                       │
│   - Transform data types (strings to numbers, dates)                        │
│   - Handle null/empty values                                                │
│   - Create vendor-specific Account/Holding objects                          │
└─────────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                         4. Map to Internal Models                            │
│                                                                              │
│   Integrator::sync($mapping)                                                 │
│   - Match vendor accounts to existing IntegrationMappings                   │
│   - Create/Update Account, Insurance, Holding records                        │
│   - Update IntegrationMapping.last_completed_at                             │
└─────────────────────────────────────────────────────────────────────────────┘
                                    │
                                    ▼
┌─────────────────────────────────────────────────────────────────────────────┐
│                         5. Return Results                                    │
│                                                                              │
│   - Imported households                                                      │
│   - Created/updated accounts                                                │
│   - Error messages for failed rows                                          │
└─────────────────────────────────────────────────────────────────────────────┘

File Storage

S3 Path Structure

// Integration model method
public function getFileReferenceAttribute(): string
{
    return $this->type->getFileReference($this->reference);
}

// IntegrationType enum method
public function getFileReference(string $reference): string
{
    // Returns S3 path prefix for this integration's files
    return "integrations/{$this->value}/{$reference}/";
}

Hosted Types

File-based integrations have two hosting types:

// IntegrationType enum
public function getFileBasedHostType(): string
{
    return match($this) {
        self::FIDELITY, self::PERSHING => 'vendor-hosted',  // Vendor sends files
        self::SCHWAB, self::LPL => 'self-hosted',           // User uploads files
        // ...
    };
}

Host Type	Description	Flow
self-hosted	User downloads from vendor, uploads to RC	User → RC
vendor-hosted	Vendor sends files directly to RC	Vendor → RC

Parser Design

Base Parser Interface

interface Parser
{
    /**
     * Parse file and return structured data
     *
     * @param string $filePath Path to uploaded file
     * @return array<int, array<string, mixed>> Parsed rows
     * @throws FileNotFoundException
     * @throws InvalidFormatException
     */
    public function parse(string $filePath): array;
}

CSV Parser Example

class CsvParser implements Parser
{
    public function __construct(
        private string $delimiter = ',',
        private string $enclosure = '"',
        private string $encoding = 'UTF-8'
    ) {}

    public function parse(string $filePath): array
    {
        $content = file_get_contents($filePath);

        // Handle encoding
        if ($this->encoding !== 'UTF-8') {
            $content = mb_convert_encoding($content, 'UTF-8', $this->encoding);
        }

        $lines = str_getcsv($content, "\n");
        $headers = str_getcsv(array_shift($lines), $this->delimiter, $this->enclosure);

        $rows = [];
        foreach ($lines as $line) {
            if (empty(trim($line))) continue;

            $values = str_getcsv($line, $this->delimiter, $this->enclosure);
            $rows[] = array_combine($headers, $values);
        }

        return $rows;
    }
}

Excel Parser Example

class ExcelParser implements Parser
{
    public function __construct(
        private ?string $sheetName = null,
        private int $headerRow = 1
    ) {}

    public function parse(string $filePath): array
    {
        $spreadsheet = IOFactory::load($filePath);

        $sheet = $this->sheetName
            ? $spreadsheet->getSheetByName($this->sheetName)
            : $spreadsheet->getActiveSheet();

        $data = $sheet->toArray();

        // Extract headers from specified row
        $headers = $data[$this->headerRow - 1];

        // Map remaining rows
        $rows = [];
        for ($i = $this->headerRow; $i < count($data); $i++) {
            if (empty(array_filter($data[$i]))) continue;
            $rows[] = array_combine($headers, $data[$i]);
        }

        return $rows;
    }
}

Mapper Design

Base Mapper Interface

interface Mapper
{
    /**
     * Map parsed row to vendor model
     *
     * @param array<string, mixed> $row Parsed row data
     * @return Account|Holding|null Mapped model or null if invalid
     */
    public function map(array $row): Account|Holding|null;

    /**
     * Get column mapping configuration
     *
     * @return array<string, string> Vendor column => model field
     */
    public function getColumnMapping(): array;
}

Mapper Example

class FidelityAccountMapper implements Mapper
{
    public function getColumnMapping(): array
    {
        return [
            'Account Number' => 'account_number',
            'Account Name' => 'name',
            'Account Type' => 'type',
            'Total Value' => 'balance',
            'As of Date' => 'as_of_date',
        ];
    }

    public function map(array $row): ?Account
    {
        $accountNumber = $row['Account Number'] ?? null;
        if (empty($accountNumber)) {
            return null;
        }

        return new Account(
            reference: $accountNumber,
            name: $row['Account Name'] ?? 'Unknown',
            type: $this->mapAccountType($row['Account Type'] ?? ''),
            balance: $this->parseAmount($row['Total Value'] ?? '0'),
            asOfDate: $this->parseDate($row['As of Date'] ?? ''),
        );
    }

    private function parseAmount(string $value): float
    {
        // Remove currency symbols, commas, handle negatives
        $cleaned = preg_replace('/[^0-9.\-]/', '', $value);
        return (float) $cleaned;
    }

    private function parseDate(string $value): ?Carbon
    {
        // Handle MM/DD/YYYY format
        try {
            return Carbon::createFromFormat('m/d/Y', $value);
        } catch (Exception) {
            return null;
        }
    }

    private function mapAccountType(string $vendorType): string
    {
        return match(strtoupper($vendorType)) {
            'IRA', 'ROTH IRA', 'TRADITIONAL IRA' => 'retirement',
            'BROKERAGE', 'INDIVIDUAL' => 'investment',
            'JOINT', 'JTWROS' => 'investment',
            '401K', '403B' => 'retirement',
            default => 'investment',
        };
    }
}

Integrator Design

File-based Integrator Base

abstract class FileBasedIntegrator extends Integrator
{
    abstract protected function getParser(): Parser;
    abstract protected function getMapper(): Mapper;

    public static function sync(IntegrationMapping $mapping): ?array
    {
        $integrator = new static($mapping->integration);

        // Get file path from S3
        $filePath = $integrator->downloadFile($mapping);

        // Validate file freshness
        if ($integrator->isFileOutdated($filePath)) {
            throw new FileOutdatedException(
                $mapping->integration,
                AffectedLevel::IntegrationMapping
            );
        }

        // Parse file
        $parsedRows = $integrator->getParser()->parse($filePath);

        // Map and import
        $results = [];
        foreach ($parsedRows as $row) {
            $entity = $integrator->getMapper()->map($row);
            if ($entity) {
                $results[] = $integrator->importEntity($entity, $mapping);
            }
        }

        $mapping->last_completed_at = now();
        $mapping->save();

        return $results;
    }

    protected function isFileOutdated(string $filePath): bool
    {
        $maxAge = config('integrations.file_max_age_days', 30);
        $fileDate = Carbon::createFromTimestamp(filemtime($filePath));

        return $fileDate->diffInDays(now()) > $maxAge;
    }
}

Vendor Integrator Example

class FidelityIntegrator extends FileBasedIntegrator
{
    protected function getParser(): Parser
    {
        return new CsvParser(delimiter: ',', encoding: 'UTF-8');
    }

    protected function getMapper(): Mapper
    {
        return new FidelityAccountMapper();
    }

    protected static function getVendor(): IntegrationType
    {
        return IntegrationType::FIDELITY;
    }
}

Validation

File Validation

class FileValidator
{
    public function validate(string $filePath): void
    {
        // Check file exists
        if (!file_exists($filePath)) {
            throw new FileNotFoundException("File not found: {$filePath}");
        }

        // Check file size
        $maxSize = config('integrations.max_file_size', 50 * 1024 * 1024); // 50MB
        if (filesize($filePath) > $maxSize) {
            throw new FileTooLargeException("File exceeds {$maxSize} bytes");
        }

        // Validate format
        $extension = pathinfo($filePath, PATHINFO_EXTENSION);
        if (!in_array(strtolower($extension), ['csv', 'xlsx', 'xls'])) {
            throw new InvalidFormatException("Unsupported format: {$extension}");
        }
    }
}

Data Validation

class DataValidator
{
    public function validateRow(array $row, array $requiredFields): array
    {
        $errors = [];

        foreach ($requiredFields as $field) {
            if (empty($row[$field])) {
                $errors[] = "Missing required field: {$field}";
            }
        }

        return $errors;
    }
}

Error Handling

File-specific Exceptions

// File not found in S3
class FileNotFoundException extends ExternalServiceException
{
    protected function getDefaultHttpResponseMessage(): string
    {
        return 'The data file was not found. Please upload a new file.';
    }
}

// File is too old
class FileOutdatedException extends ExternalServiceException
{
    protected function getDefaultHttpResponseMessage(): string
    {
        return 'The data file is outdated. Please upload a recent file.';
    }
}

// Invalid file format
class InvalidFormatException extends ExternalServiceException
{
    protected function getDefaultHttpResponseMessage(): string
    {
        return 'The file format is not supported. Please upload a CSV or Excel file.';
    }
}

Row-level Error Handling

public static function sync(IntegrationMapping $mapping): ?array
{
    $results = [];
    $errors = [];

    foreach ($parsedRows as $index => $row) {
        try {
            $entity = $mapper->map($row);
            if ($entity) {
                $results[] = $this->importEntity($entity, $mapping);
            }
        } catch (Exception $e) {
            $errors[] = [
                'row' => $index + 2, // +2 for header row and 0-indexing
                'error' => $e->getMessage(),
                'data' => $row,
            ];
        }
    }

    if (!empty($errors)) {
        Log::warning('File import had errors', [
            'integration_id' => $mapping->integration_id,
            'error_count' => count($errors),
            'errors' => array_slice($errors, 0, 10), // Log first 10
        ]);
    }

    return ['imported' => $results, 'errors' => $errors];
}

Related Documentation

• Vendor Specifics - Format details for each vendor
• Architecture Overview - System overview
• API-based Patterns - Compare with API-based approach