Redwood · Fidelity

Data Source

Property	Value
Source Type	File-based custodian data feed
Delivery Method	Azure Blob Storage — hot tier
File Format	Fixed-width files (COBOL-spec layout)
Authentication	`redwood-hot-storage-config` (Azure Key Vault)
Blob Container	`fidelity`
Sync Mode	incremental — per (filename, customer_code)
Processing Package	`datafeed-parser-fidelity-sftp` (internal Python wheel)
DAG	`redwood_fidelity_sync`
DAG File	`redwood_fidelity_sync.py`

Fidelity delivers fixed-width files per object type keyed by customer code and branch. The sync DAG groups files by customer code, processes each file type through the Fidelity parser, and bulk-inserts records into PostgreSQL. Previously processed files are tracked by (filename, customer_code) composite key in fidelity_sync_log.

Objects Being Synced

fidelity_account_balance

Account-level balances — net worth, margin, buying power, cash by account type (180 columns)

fidelity_activity

Bookkeeping activity — trades, dividends, fees, commissions (120 columns)

fidelity_positions

Position detail — quantities, prices, settlement, income, sector (110 columns)

fidelity_rmd

Required minimum distributions — raw COBOL output

fidelity_rmd_clean

Required minimum distributions — normalized with typed fields

fidelity_scheduled_events

Scheduled dividend/interest/income events (220+ columns)

fidelity_iwm_tas_close_lot

Closed lot cost basis data

fidelity_tas_close_delta

TAS close lot delta records (84 columns)

fidelity_tas_open_delta

TAS open lot delta records (77 columns)

fidelity_trade_revenue

Revenue metrics per trade (225 columns)

fidelity_ibxr

Interest-bearing security transaction records

III

Config, Logs, Monitoring & Schedule

Configuration Table — `fidelity_sync_config`

Column	Type	Description
`record_id`	UUID	Primary key
`object`	TEXT	File type (positions, activity, account_balance, etc.)
`destination_table`	TEXT	Target PostgreSQL table name
`enabled`	BOOLEAN	Active flag
`customer_codes`	VARCHAR(40)	Comma-separated Fidelity customer codes
`sync_mode`	TEXT	Sync mode
`load_type`	VARCHAR(20)	Load strategy
`schema`	VARCHAR(63)	Target schema name
`archival_policy`	TEXT	Archival tier reference
`last_sync_date`	TIMESTAMP	Timestamp of last successful sync
`created_at` / `updated_at`	TIMESTAMP	Audit timestamps

Log Table — `fidelity_sync_log`

Column	Type	Description
`file_name`	VARCHAR(255)	Source filename (deduplication key)
`customer_code`	VARCHAR(40)	Fidelity customer code (deduplication key)
`status`	VARCHAR(20)	completed processing failed
`processed_at`	TIMESTAMP	Processing timestamp
`records_added`	INTEGER	Rows inserted
`error_message`	TEXT	Error detail if failed

Monitoring & Alerting

Property	Value
Notifier	`airflow_email_notifier` — `send_dag_notification`
Subject prefix	`[Redwood-Fidelity]`
On success / failure	Yes / Yes
Retries	0
Execution timeout	6 hours
In-pipeline validation	Per-file status tracking · batch error isolation · processed-file deduplication by (filename, customer_code)

Email recipients: prashant.surana@collation.ai · monitor@collation.ai · mschwartz@sequoia-financial.com · handerson@sequoia-financial.com · vzivich@sequoia-financial.com · hpatel@sequoia-financial.com · karishni.mehta@collation.ai

DAG Schedule

redwood_fidelity_sync

10:00 AM UTC

0 10 * * * · Daily

redwood_fidelity_archiver

3:00 AM UTC

0 3 * * 0 · Weekly Sunday

Max active runs: 1 · Catchup: disabled · Start date: 2026-04-02

Bronze Layer — Database Schema

Schema: All tables reside in the public schema. Every table includes record_id UUID, filename TEXT, created_on TIMESTAMP, and updated_on TIMESTAMP.

fidelity_account_balance — Account Balances (180 columns)

CREATE TABLE public.fidelity_account_balance (
    record_id                     UUID NOT NULL,
    record                        VARCHAR(1),
    branch                        VARCHAR(3),
    accountnumber                 VARCHAR(6),
    lastupdatedate                DATE,
    -- Net worth
    networthfieldsign             VARCHAR(1),
    networth                      NUMERIC(17,2),
    networthmarketvalue           NUMERIC(17,2),
    customer_facingnetworth       NUMERIC(17,2),
    -- Cash & collected balances
    cashcollectedbalance          NUMERIC(17,2),
    collectedbalance              NUMERIC(17,2),
    nettradedatebalance           NUMERIC(17,2),
    settledcash                   NUMERIC(17,2),
    unsettledcashcredit           NUMERIC(17,2),
    unsettledcashdebit            NUMERIC(17,2),
    cashavailabletowithdraw       NUMERIC(17,2),
    availabletopay                NUMERIC(17,2),
    coresweep_fundamount          NUMERIC(17,2),
    -- Buying power
    availabletopurchasemargin     NUMERIC(17,2),
    availabletopurchase_cash      NUMERIC(17,2),
    buyingpowercorpbonds          NUMERIC(17,2),
    buyingpowermunibonds          NUMERIC(17,2),
    buyingpowergovtbonds          NUMERIC(17,2),
    -- Market values (options)
    cashmoneymarkets              NUMERIC(17,2),
    optionmarketvalue             NUMERIC(17,2),
    optioninmoneyamt              NUMERIC(17,2),
    -- Margin
    marginequity                  NUMERIC(17,2),
    marginliquidatingequity       NUMERIC(17,2),
    marginequitypercentage        INTEGER,
    housesurpluscall              NUMERIC(17,2),
    sma_fedcall                   NUMERIC(17,2),
    minimumequitycall             NUMERIC(17,2),
    fedcallreduction              NUMERIC(17,2),
    housecallreduction            NUMERIC(17,2),
    availabletoborrow             NUMERIC(17,2),
    -- Account type segments: accounttype1..10
    -- Each segment has: accounttype, marketvalue, tradedatebalance, settlementdatebalance
    accounttype                   NUMERIC(3,0),
    superbranch                   VARCHAR(3),
    -- Indicator flags (portfoliomarginindicator, msla_indicator, etc.)
    filename    TEXT,
    created_on  TIMESTAMP NOT NULL,
    updated_on  TIMESTAMP NOT NULL
);

fidelity_activity — Bookkeeping Activity

CREATE TABLE public.fidelity_activity (
    record_id                         UUID,
    branch                            VARCHAR(3),
    accountnumber                     VARCHAR(6),
    accounttype                       VARCHAR(1),
    cusip                             VARCHAR(9),
    transactiontype_mnemonic_         VARCHAR(3),
    bkpgreferencenumber               VARCHAR(6),
    rundate                           DATE,
    entrydate                         DATE,
    tradedate                         DATE,
    -- Amounts
    bookkeepingquantity               NUMERIC(13,5),
    bookkeepingamount                 NUMERIC(11,2),
    bookkeepingmarketvalue            NUMERIC(11,2),
    principal                         NUMERIC(15,2),
    price                             NUMERIC(18,9),
    accruedinterest                   NUMERIC(9,2),
    commission                        NUMERIC(7,2),
    concession                        NUMERIC(10,2),
    statetax                          NUMERIC(8,2),
    ticketcharge                      NUMERIC(8,2),
    optionsregulatoryfee              NUMERIC(8,2),
    secfee                            NUMERIC(8,2),
    servicechargemiscfee              NUMERIC(10,2),
    additionalfeeamount               NUMERIC(12,2),
    proratedcommission                NUMERIC(10,2),
    -- Trade classification
    buysellcode                       VARCHAR(1),
    marketcode                        VARCHAR(1),
    tradetype                         VARCHAR(1),
    securitytype                      VARCHAR(1),
    ordertype                         VARCHAR(1),
    -- Multi-currency
    isin                              VARCHAR(15),
    sedol                             VARCHAR(15),
    currencycode                      VARCHAR(3),
    localcurrencyprice                NUMERIC(16,8),
    localcurrencyfees                 NUMERIC(18,4),
    reportingcurrencyconversionrate   NUMERIC(16,8),
    -- Expanded precision fields (same as above at 15-17 digit precision)
    -- Description lines: bkpgdescriptionline1..9
    -- Broker codes: minorexecutingbroker, majorclearingbroker, etc.
    filename    TEXT,
    created_on  TIMESTAMP,
    updated_on  TIMESTAMP
);

fidelity_positions — Position Detail (110 columns)

CREATE TABLE public.fidelity_positions (
    record_id               UUID NOT NULL,
    branch                  VARCHAR(3),
    accountnumber           VARCHAR(6),
    accounttype             VARCHAR(1),
    cusip                   VARCHAR(9),
    isin                    VARCHAR(15),
    sedol                   VARCHAR(15),
    -- Quantities by settlement type
    quantitysettledlong     NUMERIC,
    quantitysettledshort    NUMERIC,
    quantityunsettledlong   NUMERIC,
    quantityunsettledshort  NUMERIC,
    -- Pricing
    closingprice            NUMERIC,
    closingpricefactor      NUMERIC,
    -- Market values
    marketvaluesettledlong  NUMERIC,
    marketvaluesettledshort NUMERIC,
    -- Cost basis
    totalcostbasisamount    NUMERIC,
    averagecostpershare     NUMERIC,
    -- Income
    ytddividend             NUMERIC,
    ytdinterest             NUMERIC,
    accruedinterest         NUMERIC,
    -- Classification
    securitytype            VARCHAR(1),
    assettype               VARCHAR(2),
    sectorcode              VARCHAR(4),
    -- Tax lot fields, option fields, additional pricing...
    filename    TEXT,
    created_on  TIMESTAMP NOT NULL,
    updated_on  TIMESTAMP NOT NULL
);

fidelity_rmd / fidelity_rmd_clean — Required Minimum Distributions

CREATE TABLE public.fidelity_rmd (
    record_id                                  UUID NOT NULL,
    branch                                     TEXT,
    accountnumber                              TEXT,
    registrationtype                           TEXT,
    establishdate                              TEXT,
    customerdateofbirth                        TEXT,
    prioryear_endbalance                       NUMERIC,
    year_to_datedistributionamount             NUMERIC,
    lifeexpectancy_le_factor                   NUMERIC,
    lifeexpectancytypecode                     TEXT,
    periodicplanbalanceadjustmentamount        NUMERIC,
    periodicplanexpectedpayoutamount           NUMERIC,
    estimatedrequiredminimumdistributionamount NUMERIC,
    registeredrepowningrep_rr_                 TEXT,
    registeredrepexecrep_rr2_                  TEXT,
    filler                                     TEXT,
    filename    TEXT,
    created_on  TIMESTAMP NOT NULL,
    updated_on  TIMESTAMP NOT NULL
);

fidelity_iwm_tas_close_lot — Closed Lot Cost Basis

CREATE TABLE public.fidelity_iwm_tas_close_lot (
    record_id                       UUID NOT NULL,
    branch                          VARCHAR(3),
    accountnumber                   VARCHAR(6),
    accounttype                     VARCHAR(1),
    symbol_cusip                    VARCHAR(9),
    tradedatequantity               NUMERIC(16,5),
    costbasissourcecode             VARCHAR(1),
    totalcostbasisamount            NUMERIC,
    totalcostbasisproceedsamount    NUMERIC,
    costbasismethodcode             VARCHAR(1),
    shortname                       VARCHAR(10),
    retirementindicator             VARCHAR(1),
    adjustedcostbasissourcecode     VARCHAR(1),
    totaladjustedcostbasisamount    NUMERIC,
    ytdacquisitionpremium           NUMERIC,
    ytdamortizedpremium             NUMERIC,
    unrealizedmarketdiscountincome  NUMERIC,
    filename    TEXT,
    created_on  TIMESTAMP NOT NULL,
    updated_on  TIMESTAMP NOT NULL
);

fidelity_tas_close_delta / fidelity_tas_open_delta

fidelity_tas_close_delta (84 columns) and fidelity_tas_open_delta (77 columns) — lot-level cost basis and quantity change records from Fidelity's Tax Accounting System delta files. Include fields for acquisition date, cost basis amounts, lot quantity changes, wash sale indicators, and amortization adjustments.

Other Tables

Table	Columns	Key Fields
`fidelity_scheduled_events`	220+	event type, ex-date, pay date, amount, tax rate, record date
`fidelity_trade_revenue`	225	revenue per trade, fee breakdown, commission allocation
`fidelity_ibxr`	6	record_id, portaluserid, branch, accountnumber, filename

Archiving Policy

Setting	Value
DAG	`redwood_fidelity_archiver`
DAG File	`redwood_fidelity_archiver.py`
Schedule	`0 3 * * 0` — Weekly Sunday 3:00 AM UTC
Retention threshold	7 days (Airflow Variable: `fidelity_archive_older_than_days`)
Grouping	By date (YYYYMMDD pattern extracted from filename)
ZIP prefix	`fidelity` (Airflow Variable: `fidelity_archive_zip_prefix`)
Cold storage path	`{cold_container}/{customer_code}/{YYYYMMDD}/{prefix}_{date}.zip`
Temp workspace	`/tmp/fidelity_archive` (Airflow Variable: `fidelity_temp_dir`)
Hot storage secret	`redwood-hot-storage-config`
Cold storage secret	`redwood-cold-storage-config`

Archive Process

load_creds — Fetches hot/cold storage credentials from Azure Key Vault
archive_{code} (dynamic, one per customer code) — Identifies files older than threshold → groups by date → creates dated ZIP → verifies ZIP integrity (testzip) → uploads to cold storage → verifies remote copy → deletes originals
Conflict handling: if destination blob already exists, existing blob is renamed with a timestamp suffix before overwrite

Fidelity