Nightly Sync

Nightly Sync is the scheduled batch process that updates all integration data from Vendors. It runs daily after US market close to capture end-of-day positions and prices.

Schedule

• Timing: US evening (after market close)
• Frequency: Every US business day
• Duration: Varies by number of integrations (typically 2-4 hours)

Process Flow

sequenceDiagram
    participant S as Scheduler
    participant C as Command
    participant Q as Queue
    participant W as Worker
    participant V as Vendor/S3
    participant DB as Database

    S->>C: Trigger sync command
    C->>DB: Get all active integration mappings
    C->>Q: Create job per mapping
    loop For each mapping
        Q->>W: Dequeue job
        W->>V: Fetch data (API or S3)
        V-->>W: Return data
        W->>W: Parse and transform
        W->>DB: Update records
        W->>Q: Mark complete
    end

What Gets Synced

Data Type	Source	Action
Accounts	Vendor	Create/Update/Deactivate
Positions	Vendor	Replace with latest
Holdings	Vendor	Update quantities and values
Prices	Morningstar	Update security prices

Sync by Integration Type

File-based Sync

1. Read LATEST files from S3
2. Parse files for each Rep Code
3. Match accounts to existing records
4. Update positions and holdings
5. Handle deleted accounts

API-based Sync

1. Load OAuth credentials for mapping
2. Call Vendor API endpoints
3. Transform response to internal format
4. Update database records
5. Handle errors and retries

Job Processing

Queue Structure

Each integration mapping becomes a queue job:

Job {
    integration_mapping_id: 12345
    vendor: "schwab"
    advisor_id: 67890
    type: "api_based"
}

Retry Logic

• Failed jobs are retried (up to 3 attempts)
• Exponential backoff between retries
• Permanent failures logged for investigation

Monitoring

Key Metrics

Metric	Alert Threshold
Sync completion rate	< 95%
Average job duration	> 60s
Failed jobs	> 5%
Stale data (not updated)	> 24 hours

Logs

• Application logs: Job start/end, errors
• File logs: Raw API requests/responses
• Database: Sync history per mapping

Dashboards

• Sync completion status by Vendor
• Error rate trends
• Processing time distribution

Common Issues

Sync Not Running

• Check scheduler health
• Verify queue workers are running
• Check for infrastructure issues

High Failure Rate

• Vendor API may be down
• Rate limiting issues
• Credential expiration

Slow Sync

• Large data volumes
• Vendor API latency
• Database bottlenecks

Missing Updates

• Vendor didn’t send updated files
• Collector failed to download
• Mapping deactivated

Troubleshooting

1. Check job status - Look at queue monitoring
2. Review logs - Application logs for errors
3. Check file logs - Raw Vendor responses
4. Verify data - Compare S3 files with DB

Manual Sync

For debugging or recovery, sync can be triggered manually:

# Sync specific integration mapping
php artisan integration:sync --mapping-id=12345

# Sync all mappings for a vendor
php artisan integration:sync --vendor=schwab

Related

• Data Issues - Investigating sync problems
• Collector - File-based data source
• API Integrations - API-based sync

Source

• Notion: Integration 整体介绍 - Nightly Sync