​

Integration Specialist Agent

​

Primary Role & Responsibilities

• Configuring and managing API integrations with custodians (State Street, BNY Mellon, etc.)
• Ingesting administrator feeds via file uploads, SFTP, or email
• Mapping data fields between external systems and OpsHub schemas
• Transforming and validating data during ingestion
• Monitoring sync job health and retry logic
• Detecting and alerting on connectivity failures
• Orchestrating data refresh schedules and on-demand syncs

​

Key Capabilities

​

Technical Architecture

​

Integration Patterns Supported

• Direct REST API connections with authentication (OAuth 2.0, API keys, mTLS)
• Webhook listeners for push-based updates from custodians
• Polling-based synchronization with configurable intervals
• Connection pooling and retry logic with exponential backoff

• SFTP file retrieval and monitoring
• Email attachment extraction
• Cloud storage integration (S3, Azure Blob)
• Direct file upload via web UI
• Automatic format detection (CSV, Excel, JSON, XML)

• Field mapping with 1:1, 1:many, and many:1 relationships
• Unit conversion (currency, percentages, dates)
• Business logic transformations (fee calculations, look-through aggregation)
• Data enrichment from multiple sources
• Deduplication and conflict resolution

​

Database Integration

• integration.data_pipelines - Pipeline definitions and configurations
• integration.sync_jobs - Job history, timing, and status
• integration.field_mappings - Data transformation rules
• integration.data_sources - External system configurations
• integration.connectivity_logs - Connection events and diagnostics

• Ingestion counts and validation results
• Error logs and data quality issues
• Delta tracking (what changed since last sync)
• Data freshness indicators

• All data changes logged to audit.audit_log
• Data provenance (source system, timestamp, user who triggered)
• Change history with rollback capabilities

​

Common Use Cases

​

Daily Custodian Data Sync (Fund Accountant)

1. Integration Specialist triggers scheduled sync with State Street API
2. Authenticates using stored OAuth credentials
3. Retrieves positions, prices, and FX rates
4. Maps custodian field names to OpsHub investment schema
5. Validates no null prices or missing positions
6. Loads data into investment.holdings table
7. Notifies Fund Accountant Assistant that data is ready
8. Stores sync metadata (count of records, duration, any warnings)

​

Multi-Source Administrator Feed (Operations Manager)

1. Integration Specialist monitors email for administrator file uploads
2. Extracts CSV attachments from NAV, cash, and fee reports
3. Maps each source to appropriate schema:
   • NAV file → validation.nav_data
   • Cash file → investment.cash_positions
   • Fee file → performance.fee_detail
4. Validates data consistency (fund totals, reconciliation points)
5. Handles conflicts (if two sources have different values):
   • Flags for Data Quality Analyst review
   • Defaults to most recent timestamp
6. Loads confirmed data and marks as reconciled
7. Logs all administrator-sourced data for audit trail

​

Real-Time Market Data Integration (Portfolio Manager)

1. Integration Specialist configured webhook listener for market data provider
2. Receives price updates in real-time as markets trade
3. Maps provider codes to OpsHub security identifiers
4. Validates price reasonableness (no outliers)
5. Updates market_data.prices with timestamp and source
6. Triggers Portfolio Manager Copilot with price alerts
7. Maintains price history for attribution calculations
8. Provides freshness indicators (latest update timestamp)

​

Data Migration & System Replacement

1. Integration Specialist configures both custodian APIs simultaneously
2. Runs parallel sync jobs from old and new custodian
3. Maps potentially different field names between providers
4. Runs reconciliation:
   • Total asset values match (within tolerance)
   • Position counts align
   • Security identifiers resolve correctly
5. Data Quality Analyst reviews exceptions
6. Cutover happens at agreed date with data validation
7. Integration Specialist monitors both feeds for 30 days
8. Once confident, decommissions old connection

​

Regulatory Reporting Data Feed (Compliance Officer)

1. Integration Specialist creates export jobs from:
   • NAV calculations → validation.nav_reports
   • Holdings data → investment.holdings
   • Transactions → investment.transactions
   • Fee accruals → performance.fees
2. Transforms data to regulatory format (ASIC requirements, date formats)
3. Validates completeness and accuracy
4. Generates regulatory export file
5. Routes to Compliance Sentinel for final review
6. Archives export with metadata (date generated, who approved, data version)

​

Agent Tools

• Create/update data pipeline definitions
• Validate pipeline configurations
• List available pipelines and sync history
• Monitor pipeline health and performance

• Trigger on-demand sync jobs
• Schedule recurring syncs
• View sync job history and results
• Retry failed sync jobs

• Define field mappings between systems
• Test transformation logic
• Validate mapped data
• Version and audit mapping changes

• Configure external data sources
• Manage authentication credentials securely
• Test connectivity
• Monitor source availability

• Validate ingested data against rules
• Check for duplicates and conflicts
• Monitor data quality metrics
• Generate validation reports

• View connectivity logs and diagnostics
• Track data freshness and sync timing
• Monitor data quality trends
• Generate integration health reports

​

Best Practices

​

Security

• Store all API keys and passwords in Supabase vault
• Rotate credentials regularly (quarterly minimum)
• Never log sensitive authentication data
• Use OAuth 2.0 when available (safer than API keys)

• Use TLS/HTTPS for all external connections
• Verify SSL certificates
• Implement request signing where available
• Monitor for security certificates expiration

​

Data Quality

• Define expected data ranges (prices, quantities, dates)
• Check for required fields before loading
• Validate against business rules (e.g., no negative holdings)
• Flag unusual patterns (sudden price spikes, missing data)

• Retry failed syncs with exponential backoff
• Alert on connectivity failures immediately
• Skip invalid records but log details
• Never silently drop data without audit trail

​

Performance

• Schedule large syncs during off-peak hours
• Use delta sync (only changed records) when available
• Batch load data for efficiency
• Monitor sync duration and optimize queries

• Track sync job duration and trend
• Monitor network latency to external systems
• Alert if sync takes longer than expected
• Maintain sync statistics for capacity planning

​

Integration Specialist in Action

​

Example Request: “Set up daily custodian sync”

1. Gathers Context
   • Which custodian? (State Street, BNY Mellon, etc.)
   • What data? (positions, prices, cash, FX)
   • When? (time of day, frequency)
   • Authentication? (API key, OAuth, certificate)
2. Configures Connection
   • Tests API connectivity and credentials
   • Maps custodian fields to OpsHub schema
   • Sets up webhook or polling schedule
   • Stores credentials securely in vault
3. Creates Pipeline
   • Defines data transformation rules
   • Sets validation rules
   • Configures retry logic
   • Sets up alert thresholds
4. Tests & Validates
   • Runs trial sync
   • Validates data quality
   • Checks record counts
   • Confirms data in database
5. Drafts Proposal
   • Summarizes pipeline configuration
   • Shows sample data
   • Lists validation rules
   • Proposes schedule
6. Implements & Monitors
   • After your approval, enables scheduled sync
   • Monitors first week closely
   • Provides daily sync reports
   • Alerts on any failures

​

Example Request: “Reconcile data between two administrators”

1. Connects Both Sources
   • Retrieves data from Administrator A
   • Retrieves data from Administrator B
   • Maps fields between potentially different schemas
2. Compares Data
   • Total assets: A=$1.234B vs B=$1.234B ✓
   • Fund count: A=25 vs B=25 ✓
   • Holding count: A=4,512 vs B=4,498 (14 holdings differ)
3. Identifies Exceptions
   • Fund XYZ, security ABC: A=$500k vs B=$475k (difference: $25k)
   • Fund PQR, security XYZ: A=$1.5M vs B=$1.5M ✓
   • Documents all variances with details
4. Drafts Resolution Plan
   • Which administrator is likely correct? (based on timestamps, other sources)
   • Explains root causes (if identifiable)
   • Proposes next steps
5. Coordinates Investigation
   • Delegates to Data Quality Analyst for detailed investigation
   • Coordinates follow-up with administrators
   • Updates data once discrepancies resolved

​

Workflow Integration

• Ensures custodian data loaded before NAV validation
• Provides data freshness indicators
• Alerts on data quality issues that impact NAV

• Flags data quality issues detected during ingestion
• Receives reconciliation results and updates data accordingly
• Escalates exceptions for investigation

• Triggers workflows after successful data sync
• Monitors workflows that depend on fresh data
• Reschedules syncs based on workflow dependencies

• Provides audit trail of all data ingestions
• Ensures data provenance tracking
• Validates data meets compliance requirements

​

Configuration Example

Pipeline: Daily Custodian Sync (State Street)
├── Data Source
│   ├── Type: REST API
│   ├── Endpoint: https://api.statestreet.com/v1/holdings
│   ├── Authentication: OAuth 2.0
│   └── Schedule: Daily at 06:00 UTC
│
├── Field Mappings
│   ├── ISIN → security_id
│   ├── QUANTITY → quantity
│   ├── PRICE_LOCAL → price
│   ├── PRICE_USD → price_usd
│   └── VALUATION_DATE → as_of_date
│
├── Data Validation
│   ├── No null prices
│   ├── Quantities >= 0
│   ├── Prices > 0
│   └── as_of_date not in future
│
├── Transformation
│   ├── Convert prices to base currency
│   ├── Calculate market values
│   └── Aggregate by fund
│
└── Loading
    ├── Target: investment.holdings
    ├── Mode: Replace (full refresh)
    └── Audit: Full history tracking

​

Monitoring & Alerts

• Last sync timestamp and duration
• Success/failure status
• Records processed, loaded, skipped
• Data freshness indicators
• Alert history

• Connection failures (immediately)
• Sync duration exceeded threshold
• Data quality issues detected
• Unusual data patterns
• Credential expiration warnings

​

Limitations & Considerations

• Manages external system connectivity (not internal data transformations)
• Data Quality Analyst handles anomaly detection within OpsHub
• Workflow Director manages post-sync workflows
• Orchestrator handles overall orchestration

• Optimized for typical fund operations (thousands to tens of thousands of records per sync)
• Large data volumes may require custom optimization
• Contact support for enterprise-scale integrations

• Typical API sync: 1-5 minutes
• File upload processing: 1-2 minutes
• Real-time webhook: less than 1 second
• Scheduling varies by complexity and data volume

​

Getting Started

1. In Agent Console: Ask “Set up [custodian] integration” or “Show data pipeline status”
2. Natural Language: Describe your data connectivity needs naturally
3. Approval: Review and approve proposed configurations
4. Monitoring: Check dashboard for sync status and alerts

​

FAQ