Foundational knowledge

What is an ETF?

An ETF is a regulated fund that holds a portfolio (stocks, bonds, commodities, crypto, etc.) and issues shares listed on exchanges. Corgi is the issuer; investors hold fund shares, not direct ownership of every underlying stock.

Two markets — primary vs secondary

ETF activity splits into two markets. Secondary is investors buying and selling existing ETF shares on exchanges — this is the vast majority of volume. Primary is Authorized Participants (APs) creating or redeeming shares directly with the fund — this changes shares outstanding and AUM, but happens far less often.

Architecture — today vs target

Five fixed roles — only the company inside each box changes. Fund accounting always pulls market data and operational truth into a fund administrator, which produces NAV and GL for the issuer.

Roles

What changes: fund administrator and transfer agent move from U.S. Bank GFS to Corgi in-house. Custodian and market data vendors stay on existing contracts. Bloomberg / ICE feed the admin's pricing pipelines — not the custodian. Custodian data is a separate input (dataset 4) governed by the custody SLA, not the admin outsourcing agreement.

Data sourcing

Fund accounting requires six input datasets — five core plus product-specific add-ons. See architecture for how market data vendors, custodian, and administrator roles map to these inputs. Market data (datasets 1–3, 5–6) typically comes from one vendor contract (Bloomberg below); operational data (dataset 4) originates at the custodian and is consumed by the fund administrator. Each pipeline lists one primary, one backup, and one cross-check vendor.

Other product-specific add-ons: derivatives marks (2×/buffer funds), index/benchmark levels (buffer funds). Internal fund master and fee schedules are configuration — enriched on top of dataset 3, not a separate vendor pipeline.

Procurement paths

Redundancy rules (market-data pipelines)

Datapoints

Every line on the fund general ledger and trial balance maps to a datapoint below. Reference GL: EUV thematic equity fund (211161). Bond funds (e.g. 211265) add fixed-income lines. Groups reflect Corgi product type — not GL sub-category codes.

Click any row to expand the description and formula. One source of truth — no separate detail section.

Field prefixes: D1 pricing & FX · D2 corp actions & FI events · D3 security reference · D4 operational truth · CFG fund config (fee schedule, base currency)
Method: Pulled = maps 1:1 to a single normalized input field · Computed = combines two or more inputs via an explicit formula

Internal fields

Not everything comes from a vendor feed or a formula. Corgi must store and maintain fund-specific configuration and identifiers — the fund master. Without these, datapoint computes have nothing to key on.

Stored in CFG.* referenced throughout datapoint formulas. Changes are versioned and effective-dated — not overwritten silently.

Data pipelines

Receiving data is the easy part — a vendor CSV drop over SFTP/FTP. The hard part is when files arrive, what you store, how you validate before normalization, and how you prove it later. Everything below assumes we persist data; we do not compute NAV from live API calls at publish time.

Why end-of-day?

Fund accounting runs on an as-of date, not in real time. A trade executed today does not settle today — US equities are T+1 (next business day); EU markets are typically T+2. Until settlement, the position is in investment receivable/payable, not cash. Market data vendors publish official EOD marks only after the close. Running the batch intraday would mean marking on incomplete prices and booking unsettled trades as settled.

EOD batch logic: wait for (1) market close + official prices, (2) custodian day-end file, (3) corp-action confirmations — then compute, reconcile, sign off, publish.

Ingest & compute (AWS)

Feeds arrive as CSV exports pushed by vendors (and U.S. Bank GFS during calibration) over SFTP or FTP. AWS pattern: Transfer Family exposes the endpoint → files land in an S3 landing prefix → Lambda fires on ObjectCreated.

Lambda does two things on every file:

1. Archive — move CSV to immutable S3 hierarchy: archive/{vendor}/{feed}/{as_of_date}/{received_at}_{filename}. This is the raw layer — we do not need a separate JSONB extract table when the vendor artifact is already a file.
2. Normalize — parse validated rows into typed core.* tables. Idempotent per (as_of, feed, row_key) so reprocessing a file replaces the same logical rows, never silently duplicates.

Downstream EOD (ledger, reports, email) chains via EventBridge once required feeds for an as_of are validated + loaded. CloudFront Functions are edge HTTP only; batch work runs on Lambda in-region.

CSV validation — before normalized insert

Skipping validation and jumping straight to core.* is the main ingest failure mode. If our parser is wrong, we only find out when NAV breaks — too late. Validate before insert; on failure, quarantine the file, log to reporting.ingest_exceptions, alert ops, and block ledger for that feed/as_of.

Each feed ships with a CSV contract in repo config: expected columns, types, required/nullable, filename pattern, and schema version. Vendor changes the file → contract version bumps → parser update → replay from archive. Never mutate a archived CSV.

Cross-day sanity tests

After schema validation, compare today's file against T−1 loaded data. These are cheap catches for stale or broken drops — run on every feed before core.* insert.

Thresholds live in CFG.recon_tolerances per feed — not hardcoded. Log every failed test to reporting.ingest_exceptions with T and T−1 aggregates for ops review.

SFTP re-pull on stale or failed file

If cross-day or schema checks fail — file identical to yesterday, wrong as_of, truncated drop, etc. — do not load into core.*. Instead:

1. Quarantine the file and log the failure reason
2. Trigger an SFTP re-pull for that feed (Lambda polls vendor outbox or requests re-push per vendor SLA)
3. Re-run validation on the new file; cap retries (e.g. 3 attempts before escalating)
4. If still bad at EOD cutover → block ledger for that as_of and alert ops

Re-pull only applies when the CSV itself is suspect — not when our parser is wrong. Parser bugs are fixed in code and replayed from the S3 archive.

Ledger output checks (before GL insert)

Second failure point: bad calculations, not bad CSV. EventBridge invokes ledger Lambda after core.* is loaded. Before writing to ledger.*, sanity-check computed values against CFG.recon_tolerances — NAV delta vs T−1, GL line bounds, impossible negatives. Values outside the realm of possibility → block insert, log exception, do not publish.

Index and market-data providers (e.g. MSCI, Bloomberg) set the pattern fund admins mirror:

• Scheduled file delivery — daily packages over SFTP/FTP at published cutover windows; ASCII/delimited modules per dataset (index level, security level, constituents — we split D1–D5 the same way)
• Versioned file contracts — vendors announce schema changes before they ship; we version contracts and never silently accept unknown columns (MSCI explicitly reserves the right to change formats with advance notice)
• Immutable archive — every delivery retained as-received; replays and audits trace to the original file, not a re-export
• Validate → load — no direct insert into downstream tables; quarantine on failure; idempotent load per as_of
• Distribution cycles — EventBridge cutover only after required modules for the date are present and validated (mirrors MSCI's fixed daily delivery schedule)
• API optional, files canonical — providers offer APIs for flexibility; batch CSV/SFTP remains the audit trail fund accounting relies on

Our ingest methodology should be indistinguishable from how a Bloomberg or MSCI client would wire a fund admin — modular feeds, strict contracts, archived raw files, gated normalize.

Ingest metadata

Lightweight ingest.files table — not a second copy of the data:

Storage model

Four layers — raw is the CSV file on S3, not a parsed duplicate in Postgres.

Encryption: TLS in transit; AES-256 at rest (cloud KMS or on-prem HSM). Fund accounting data is not PCI, but it is fiduciary — treat as confidential. On-prem is viable for latency/control; cloud is viable for ops cost. Either works if access is role-scoped and audit-logged.

Validation — development vs production

Two phases. Do not conflate them.

Development (pre-launch calibration)

While building the in-house engine, the trusted reference is U.S. Bank GFS output — labeled ground truth from the system we are replacing. Each EOD:

1. Run our best-effort datapoint functions on the same inputs
2. Import U.S. Bank trial balance / NAV extract for the same as_of
3. Diff every GL line and datapoint — log to reporting.calibration_diffs
4. Where we disagree, backsolve: trace inputs → formula → find the break
5. Fix compute, rerun until output is congruent

After several days of this loop, calculations should match on all standard paths. Remaining gaps are edge cases — corp-action quirks, FI amortization corners — where domain review (operations team) closes the last mile before we hit them in production.

Production (post-launch vendor reconciliation)

Once live, U.S. Bank is no longer the reference — three market-data vendors per pipeline are. When primary (A) ≠ backup (B), cross-check (C) breaks the tie.

Every exception writes to reporting.vendor_exceptions: field, as_of, vendor flagged, values from A/B/C, resolution. Over time this becomes a vendor reliability scorecard. Three sources are sufficient for tiebreak — if A, B, and C all disagree, that is a block-NAV event.

Transform

core.* → ledger.* via datapoint formulas (detail cards) — owned by Carl. Transforms are deterministic, versioned, and replayable from archived CSV + normalized rows. No ledger run for an as_of until required feeds show status = loaded in ingest.files.

Retention

SEC Rule 204-2 requires investment advisers to retain books and records for at least five years (first two in primary office). NAV history, GL, reconciliation logs, and vendor CSV archives qualify. Design default: 7 years online on S3, then glacier — covers examination cycles with margin. Immutable CSV archive makes replay and audit defensible without a parallel JSONB store.

Fund administration workflows

Industry-standard daily and periodic processes the system must support — not exotic, but all mandatory for a regulated ETF administrator.

Major outputs

Established artifacts every fund administrator produces — regulatory, operational, and investor-facing. These are not optional nice-to-haves; they are what auditors, the SEC, APs, and the issuer expect.

Daily fund report

Corgi runs a Daily Volume Report every business day — all funds on one HTML page. Tracks AUM, primary-market flow (creates / redeems), secondary-market turnover by venue, and NAV deltas. This is an issuer-facing operations report, distinct from the official NAV publish.

Report structure

Matches the provided sample (daily_volume_report_anonymised_.html). Skeleton generator: daily_volume_report.py.

Flow columns = primary market (AP creates/redeems). Turnover columns = secondary market volume by listing venue (Dataset 5).

Generation pipeline (AWS)

Distribution groups stored in fund config — not hardcoded. Example: daily_volume_report → desk, issuer, distribution list. AWS SES identity and suppressions managed in-console; recipient lists live in our DB so ops can update without redeploying Lambda.

Sophisticated report packs (monthly performance, audit extracts) follow the same pattern: Lambda render → S3 archive → SES to a different distribution group. Detail deferred — same infrastructure.

The HTML report is a distribution output (email + archive) — not the main UI. Day-to-day work happens in the web app.

UI

The most critical feature is calculating reliable figures — eliminating dependence on U.S. Bank as fund administrator. A polished UI is secondary but matters for selling the system to other firms later.

Web first — faster iteration, easier demos to prospective clients. The daily volume HTML report is a distribution artifact we generate and send; the web app is where accountants work.

Specific screens and layout TBD — validate by shadowing the current U.S. Bank workflow once we have access. Desktop shell (Electron) only if a buyer requires on-prem deployment.

Implementation plan

• Get access to the current accounting system and backend — learn how it works, view the data
• Stand up SFTP → S3 → Lambda ingest with CSV + cross-day validation; load core.* tables
• Carl owns ledger from normalized data; engineering supports ingest and calibration diffs vs U.S. Bank
• Shadow U.S. Bank UI after access; define web app from discovered workflows — not before
• Once calibrated: EventBridge EOD chain, report render + SES, web app — keep diffing