Seven sources, seven domains: designing ingest

6 minutes ago

19 min read

Mapping every upstream feed to a data domain, a flow and a local store, plus the cases where ingest and publish collapse into one step, and the parser bugs that only showed up once we looked.

The easiest way to lose track of a data platform is to organize ingest by source: "the FMP

jobs", "the SEC jobs". Sources change. Plans get upgraded or downgraded, endpoints get renamed, a vendor turns out not to carry the one market you need. FMP alone feeds five of our domains through two different client libraries. So we draw ingest by domain instead, and sources become inputs to a domain rather than its identity.

This article walks through all seven domains: what each one pulls, when, why at that time,

where it lands, and the specific ways each one has bitten us. The theme that keeps recurring is that ingest bugs are quiet. A broken publish blanks a screen and someone notices within a day. A broken parser files a loan as an equity, or stamps a holding with next year's date, and the pipeline stays green for months.

Sources → domains → stores

A few patterns stand out once it's drawn this way:

PostgreSQL is the system of record for anything tabular and historical: prices,
statements, estimates, filings, fund holdings, sentiment, muni scores.
Redis is the hot layer. Macro series, news by category and intraday state go straight
there, and a `pg_view_agent` mirrors key Postgres views into Redis so the publish scripts
and compute jobs don't hammer Postgres.
Couchbase holds documents (SEC RSS filings) and Neo4j holds the fund-holdings graph. Neither is fed directly by a scheduled flow. The graph is rebuilt from Postgres by hand, which matters later when the N-PORT data underneath it had to be thrown away.

Schedules are in New York time throughout. Every flow named below is a Prefect deployment in `orchestration/deployments.py`; [Article 2](043-cron-jobs-one-process) covers how that single process runs all of them.

Two ways to talk to FMP

Financial Modeling Prep is the main source. It supplies prices, statements, estimates, ETF

holdings, news, ratings and shares float. We reach it two ways: some flows call its REST

`stable/*` endpoints directly, others go through an MCP client in `util/fmp_mcp_client.py`.

The MCP client was meant to be the friendly path: one JSON-RPC interface, tool discovery, a single place to handle auth. In practice it taught us two things.

The server doesn't expose the tools you'd expect. It serves about 28 coarse category tools (`quote`, `statements`, `analyst`, `news`, `etfAndMutualFunds` and so on), not one tool per endpoint. Our client originally mapped calls to per-endpoint names like `key-metrics`,

`income-statement` or `etf-holdings`, and every one of those answered "Tool not found". Each failed call cost two round trips and a log line before the REST fallback did the real work, on every symbol. The client now keeps the advertised tool set (refreshed from `tools/list` at handshake, so a new server-side tool is picked up without a code change) and sends anything outside it straight to REST.

It stalls under concurrency. Measured over 12 symbols, two requests in flight took 4.8

seconds in total with nothing stalled. Three in flight took 30.4 seconds. Four took 31.0. A

single fetch on its own is 0.2 to 2 seconds, so the ceiling is the client, not the vendor: once

more than two requests are outstanding, a caller waits a flat ~30 seconds. Any job that uses

the MCP path now holds a two-wide semaphore around it, and outer concurrency has to stay off that path or it brings the timeouts back.

The rule we ended up with: MCP for low-volume convenience calls (news categories, analyst

estimates), REST for anything that runs across thousands of symbols or uses bulk endpoints.

The pg_view → Redis mirror

A lot of local compute wants the same few Postgres views: company profiles, ratios, key

metrics, analyst estimates, sector summaries. Rather than let every consumer query Postgres,

`data_sync/pg_view_to_redis` (run by the `pg_view_agent` and by some flows directly, such as the morning pricing update) copies each view into a Redis key named

`view_cache:{schema}.{view}:data`, alongside a content hash so unchanged views aren't

rewritten. Keys carry a seven-day TTL as a safety net.

The mirror has a row cap, `SYNC_MAX_ROWS`, defaulting to 100,000. That cap caused one of our quieter bugs. The world P/E producers were reading the cache of the **raw** ratios table, which holds every period for every symbol. Truncated at 100,000 rows, it held 57,589 of the 69,912 trailing-twelve-month rows, and whole listings (Royal Bank of Canada, TD, Shopify, Santander's Madrid line) were simply absent. Nothing errored. The fix was to read `ratios_view`, which is filtered to the latest row per symbol and fits comfortably, and to give `key_metrics` its own 300,000-row cap plus a filter that drops superseded TTM snapshots.

There's a second cost. Consumers read these keys with a single `GET` and decode the whole

payload before filtering to the symbol they want. `key_metrics` alone is 100,000 rows. That's

fine once per job and ruinous once per symbol, which is why the scanner's enrichment pass

([Article 4](04-anatomy-of-a-nightly-scan.md)) loads each view once and indexes it by symbol

before doing any per-symbol work.

Prices and Quotes

The cadence follows the market, not a round-number schedule. The pricing update runs at 10:00 (half an hour after the open, once opening auctions have settled) and 16:30 (after the close). Index quotes come in at 16:20 and ETF quotes at 16:30, weekdays only. Commodities and crypto run at 11:00 and 16:15. The list of actively traded symbols is refreshed at 23:00, after everything that depends on today's list has finished. Quotes for indexes, ETFs, commodities and crypto pass through `pricing/technical_analysis_service.py`, which computes indicators on each quote before persisting it. The pricing update also refreshes the Redis copy of the latest-prices view, so the scan at 21:00 reads warm data.

One surprising detail: daily price history for the charts isn't stored locally first. The

publish scripts (`aiim-mobile/scripts/sync_scanner_prices` twice a weekday, and

`orchestration/portfolio_universe_flow.py` at 18:40) fetch FMP end-of-day history and push it

straight to Cloudflare. There's no reason to keep two years of daily bars for a thousand symbols in a local table that nothing else reads. The edge is the system of record for chart data.

Two FMP details matter here. The full end-of-day endpoint returns about 1,254 bars (five years) newest first, and anything that seeds moving averages from the start of the series must reverse it. And our own `fetch_combined_prices` helper only honours its `limit` argument on the Redis-cache path; on a cache miss it returns the provider's whole series. Any caller that cares about depth trims the frame itself.

Fundamentals change slowly, so the cadence is coarse. Statements come in twice a month, on the 1st and the 15th at 03:00, which catches a quarterly reporting season without re-fetching unchanged filings every night. Key metrics run on Sunday at 22:00 and the bulk

trailing-twelve-month refresh follows at 23:00. Analyst estimates change daily, so they run

nightly at 22:00 and are mirrored into Redis for the screens that show consensus.

IBKR fundamentals run daily at 13:00, and they're the one ingest that runs as a subprocess. `orchestration/ibkr_etl_flow` shells out to the ETL with `--limit 15000 --concurrency 5` (both overridable from the environment), because the IBKR client wants its own asyncio event loop and its own connection lifecycle, and running it inside the Prefect worker's loop caused more trouble than it was worth. The limit is an entitlement guard: it caps the symbols processed per run at what the account is permitted to request. A subprocess also gives us a clean failure boundary. If the IBKR session drops, the child exits non-zero and the flow reports it, instead of a stuck coroutine holding a worker slot.

The TTM loader that never existed

For most of 2026 the "current" P/E for every company was a January snapshot, and nobody had noticed. When we went looking, the trailing-twelve-month ratios table held 69,912 rows inserted in nine seconds on 6 January, and the key-metrics TTM rows had landed six minutes earlier, in five seconds. That's a one-off manual bulk import. No loader for it existed anywhere in the repo. The weekly key-metrics flow only fetched quarterly rows for about 4,600 active US symbols and deliberately left the "latest" flag to "the TTM loader", which was a script nobody had ever written.

FMP's bulk endpoints made the fix cheap: `ratios-ttm-bulk` returns 71,400 rows (69 MB) in about four seconds, and with `key-metrics-ttm-bulk` and the paged `profile-bulk` a full refresh is about five calls. The new `orchestration/ttm_bulk_refresh_flow.py` appends a dated TTM snapshot (so history is kept) and moves the `is_latest` flag in the same transaction. Company profiles are updated in place instead, because the profiles view is the whole table, and appending would give several rows per symbol.

It found three traps on its first runs. Asking `profile-bulk` for a page past the last one

returns HTTP 400, not an empty page, so the loop has to treat 400 as "done" on that endpoint only. `company_profiles.average_volume` was a 32-bit integer and overflowed on the most liquid names. And the first failed run logged the API key, because FMP takes the key as a query parameter and the error included the request URL. Error URLs are now redacted in code. That last one generalises: any vendor that authenticates by query string will eventually put its key in your logs.

The weekly `fundamentals_pg_flow` (Saturday 07:20) is the exception to "everything lands in

Postgres". It pulls FMP fundamentals and sends them through the Worker straight to PlanetScale, bypassing the local database. That's a pattern we keep coming back to. When local storage adds nothing, skip it. [Article 6](06-push-never-pull.md) covers that route.

Bulk endpoints and their rate limits

FMP's bulk endpoints change the economics of backfills. `stable/eod-bulk?date=` returns every symbol's end-of-day row for one date, about 53,500 symbols, compared with one call per symbol for the per-ticker endpoints. `income-statement-bulk?year=&period=` covers about 40,000 symbols across most major exchanges. Backfills that would take hours of rate-limited requests become a few hundred calls.

They come with their own limits, and those limits are sharper than the per-ticker ones. In our

experience the bulk endpoints answer 429 after about three calls in ten minutes, and a sustained backfill runs at about one call a minute. The 429 body reads "Limit Reach" and warns that the key may be restricted, which is not a warning you want to test.

The realized-outcomes pipeline ([Article 5](05-measuring-models-honestly.md)) is the heaviest regular user. To label a scanner prediction it needs a close for every scanned symbol on every day since; `eod-bulk` gives 875 of the 942 scanner symbols in one call, where our own `equity_prices` table covers only 60 to 110 of them. So it caches each date's file locally, only fetches after 22:00 so the day's file is final, and paces requests 20 seconds apart.

Two further details from that work. On weekends and market holidays `eod-bulk` returns a

crypto-only file, so "SPY is absent" is how we detect a closed market. And a cached file can't

reflect a split that happens later, and we haven't verified whether FMP adjusts the bulk file's

adjusted close after the fact, so any labelling window that spans a split is masked using the

splits calendar.

Bulk coverage also has holes you only find by measuring. `eod-bulk` contains no Shanghai or

Shenzhen symbols at all. For mainland China the country P/E backfill had to fall back to the

per-symbol light history endpoint: 4,357 profiles at about 0.3 seconds each, full history per

call.

World P/E: many inputs, one number

The World P/E screen shows a cap-weighted P/E per country, its history, and a forward estimate. It's worth singling out because it's the domain where ingest quality and the final number are most directly coupled. Its inputs are TTM ratios and company profiles from the bulk refresh above, FX rates and latest prices from the pricing flows, index quotes, consensus estimates for the forward figure, and inflation forecasts from the IMF to turn nominal growth into real growth. Each input had at least one trap:

Input	Trap	Effect
UK and South African listings	FMP quotes price in pence or cents but market cap in pounds or rand	Caps were divided by 100 along with prices
Tel Aviv listings	The agorot currency code wasn't recognised (only the mixed-case GBp/ZAc forms were handled)	All 460 Israeli profiles dropped; Israel's P/E read 16.5 instead of 25.2
Indian listings	`company_profiles.symbol` was `varchar(10)`	About 707 `.BO` symbols truncated; India's constituent count went from 1,702 to 3,095 after the fix
Index quotes for Brazil, Malaysia, Poland, South Africa	The fallback quote source 404s for those index symbols	`ytd_change` arrived as the string `"N/A"`, which crashed the panel
Forward estimates	FMP FY2026 consensus is GAAP and includes one-offs	Implied US earnings growth read +37.5% before switching to a next-twelve-months blend
Quarterly ratios	FMP's quarterly P/E is a single quarter annualised, not TTM	One-off charges produce absurd P/Es; we compute TTM from four quarters' EPS ourselves

The general lesson: currency units and identifier widths are ingest concerns, not modelling

concerns. By the time a mis-scaled market cap reaches a weighted median, nothing downstream can tell it apart from a real one.

SEC filings and fund holdings

The SEC domain has three ingest shapes, and each one exists because the others can't do its job.

Bulk archives** (Friday 22:00, `stock_financials/sec_filing_manager.py`) give us filings,
their sections and XBRL financial facts in Postgres. Weekly is enough because the bulk files are themselves published in batches.
The Atom RSS feed is read continuously by an agent and lands raw filing documents in
Couchbase. It's the low-latency path: a 10-K is searchable by the assistant's document tools shortly after it's filed, without waiting for the weekly bulk run.
N-PORT (Sunday 02:00) is the monthly portfolio report every registered fund files. It's
what lets us say which funds hold which issuers, and it feeds the fund-network panels.

N-PORT: the most complex parser we have

A single N-PORT filing can contain equity positions, debt, derivatives, repo agreements,

securities lending, short-term investment vehicles and fund-level risk metrics, each with its own table. In October 2026 we went to load it into the graph and found it couldn't be trusted. The audit turned up three bugs first, then several more.

Dates. The persister set each holding's `report_date` from `repPdEnd`. In N-PORT that's the fund's fiscal year end; the as-of date of the holdings is `repPdDate`. On a table of 21.2 million rows, about 30% (6.45 million) had a report date in the future, every one of them filed before its supposed period end. Worse, the unique key included that date with

`ON CONFLICT ... DO UPDATE`, so each quarterly filing within a fiscal year overwrote the previous quarter's positions. Those overwritten quarters weren't recoverable from the table; they needed a re-ingest from EDGAR.

Convertibles. The parser looked for a `<convertible>` element that N-PORT doesn't have. The real signals are a pair of Y/N flags for mandatory and contingent convertibility, a conversion ratio on the currency block, and a reference to the underlying equity. Result: zero of 10,032,042 debt rows were marked convertible.

Paging. The fetcher read only the "recent" block of each registrant's submissions, and the backfill capped at 50 filings per registrant. But a trust files one N-PORT per series per

quarter. One sampled trust had 50 filings in the table, covering three months of 2026; it has

1,025 since 2021. The backfill now filters on the submission's report date and pages properly.

Then we did a proper audit. We diffed every element and attribute in 202 cached filings (61,835 holdings) against what the parser reads, and compared per-field fill rates of the parsed objects with presence in the XML. That found the bugs no reading of the code would have:

The persister routed detail inserts by lists of asset categories, and the lists were missing
loans and FX derivatives. 4.7 million loans never got debt rows, and 621,000 FX derivatives never got derivative rows. A loan was silently just a holding with a value.
Futures were matched on a misspelled tag, so their expiry was never read.
Derivative contract rows were keyed so that only the first derivative of each type per filing survived; the rest hit `DO NOTHING`.
Monthly return data grabbed the first asset-class block and labelled it as the fund total.
Holding tickers, "other" identifiers (20% of holdings, mostly loans and foreign securities) and the repo central-counterparty flag were never read, and fund risk metrics (interest-rate and credit-spread sensitivities by tenor) weren't parsed at all.
Routing is now driven by what was actually parsed, not by a category list. All the new SQL was validated against the live schema on those 202 filings through a recording cursor that captures statements without executing them, which is a technique worth stealing: you get a real integration test against production schema with zero writes.

Then all twelve N-PORT tables were reset (the first attempt truncated only the `nport_*` tables and missed five fund-level ones; spell out the full list when you hand over a reset) and the re-ingest began. It broke once more, at 48.3 million rows, when the main unique constraint and its index disappeared mid-load. The resulting foreign-key errors were a cascade from a separate bug: the persister rolled back the whole connection when one holding failed, which discarded the fund-level row its caller hadn't committed yet. It now uses a savepoint per holding and raises if every holding in a filing fails. Six network panels in the app wait on that backfill. A parser bug in a domain like this doesn't fail loudly. It quietly files a loan as an equity.

ETF holdings and index constituents

ETF holdings come from FMP twice over. A weekly flow (Sunday 05:45) loads holdings, fund info and the ETF list into Postgres for local analytics. Separately, `aiim-mobile/scripts/sync_fund_holdings.py` runs at 11:30 and 18:30 and publishes per-fund holdings, sector and country breakdowns straight to KV for the app's fund screens.

Index constituents are harder than they look. The Global Markets map shows a native index for 28 markets, and users wanted to tap one and see the whole index with weights. FMP has no constituent lists for most non-US indices. What it does have is holdings for ETFs, and a

full-replication tracker fund holds its index at index weights. So the Worker maps each index to a local tracker and asks FMP for that fund's holdings: SPY for the S&P 500, local trackers for Canada, Japan, the UK, Germany, Australia, France, Hong Kong, Singapore, Switzerland, the Netherlands, Italy, Spain, Poland and South Africa, a Euro Stoxx 50 tracker, and a KOSPI 200 tracker for Korea. That covers 17 of the 28 markets.

For the other 11 (including Brazil, India, Mexico, Sweden, mainland China, Malaysia, Indonesia, Saudi Arabia and Turkey) FMP carries no tracker with holdings. Those fall back to the MSCI country ETF, and the payload says so with `basis: "country_etf"` and a plain note in the UI. Probing is the only way to know: many local ETFs that obviously exist return zero holdings rows. Constituents are cached in PlanetScale and refetched by the Worker when they're about a week old.

Macro and Regional Economy: ingest and publish in one step

The **G20 forecasts** (`macro/flows/prefect_g20_forecasts.py`) pull CPI, unemployment and rate series from FRED nightly at 21:00, rerun at 10:00 and 16:00 by the macro recalculation flow, and write forecast paths to Redis. Two ingest problems shaped it. The OECD source used for some series allows about 60 requests an hour, which a per-country loop blew through; fetches are now batched. And several countries' series simply stop: Russia's latest vintage was April 2013, China's June 2025. A six-month staleness rule now drops a country rather than forecasting from a decade-old observation.

The county economy script (`scripts/sync_us_county_economy_kv.py`) is the clearest case of collapsing a pipeline. It fetches county GDP and unemployment, computes a nowcast, and POSTs the result to KV. There's no local table. Getting the inputs was most of the work:

BEA's regional bulk zips need no key. As of October 2026 the latest county GDP year is 2024.
The BLS bulk download site returns 403 to scripts that don't send a registered contact in the User-Agent. GeoFRED, using our existing FRED key, returns every county's unemployment rate for 13 months in one call, so county unemployment comes from there instead.
October 2025 unemployment is null for every county because of the federal shutdown. We keep it null. Interpolating would invent a data point the statistical agency chose not to publish.
Counties are keyed by FIPS code, never by name. Virginia has Fairfax, Franklin, Richmond and Roanoke as both a county and an independent city. BEA reports some Virginia combinations and Maui with Kalawao as one area, so those values are written to every member county and flagged. Alaska's Valdez-Cordova was split in 2019 but the map geometry still has the old shape, so the two new areas are summed. Connecticut switched to planning regions in 2022 and FRED has no Connecticut counties, so it's shown at state level with a note.
FRED takes its key as a query parameter, and `httpx` logs request URLs at INFO. The script silences that logger and raises errors without the URL.

The nowcast itself estimates 2025 county GDP before BEA publishes it. The first version scaled each county by its wage growth relative to its state, and it barely beat the naive baseline of "every county grows at its state's rate" (median error 2.39 percentage points against 2.53, and worse on the mean). Resource counties move with output and prices, not payroll. What fixed it was shift-share: grow each county's industry mix at its state's industry growth rates, then adjust by relative wage growth raised to a power of 0.5, rake to the state totals, and deflate. Backtested over ten one-year windows from 2014 to 2023, it beat the baseline in all ten, with a median error of 2.44 points against 3.21. One caution from the backtest: the 2023-to-2024 window was the easiest of the decade, so calibrating confidence tiers on that window alone overstated accuracy by about half a point. Calibrate on the pooled range.

Because BEA publishes real county GDP with a long lag, the nowcast is thrown away once the real figure arrives. Persisting it locally would only create a second copy to keep consistent.

News and Sentiment

News comes from FMP's category feeds into Redis every other hour from 09:00 to 19:00, which is when headlines move. Per-symbol sentiment is scored by `scripts/sync_sentiment_daily`, which the market-data sync runs at 10:00 and 17:00 for every current scanner symbol.

That script exists because the sentiment table was created as a skeleton and never populated, so every sentiment surface in the apps was hardcoded. FMP's own sentiment endpoints, social and news, return 403 on our plan. So we derive news sentiment ourselves from ticker-tagged articles, scored with VADER. Stock VADER scores "beats earnings estimates, raises guidance" as exactly 0.000, because its lexicon is tuned for social media, not markets; we extend it with about 90 finance-specific terms. The `social` and `call` columns stay NULL because there's no source for them, and the composite score therefore equals the news score. Any UI that shows it labels it as news-derived. The diagram records the absence of a social source so nobody goes looking for it.

Municipal Bonds

Munis have the thinnest public data of any domain. The features come from official statements and continuing disclosures published on EMMA, parsed into Postgres and processed by a local agent every four hours. Ratings, which the risk model needs as labels, come from the historical rating-action files the rating agencies publish as a regulatory disclosure (with roughly a 12-month lag) plus S&P's public listing of recent rating actions, about 2,100 US public-finance actions a week. A weekly refresh on Sunday at 04:00 loads them, and each load replaces that agency's rows rather than appending, so a corrected file corrects the table.

The hard part is joining a rating action to a bond we know about. Issuer names extracted from statements are often junk (the extractor picked up a sentence of statement text instead of a name), so they're filtered, and matches require agreement on name and state. An earlier, looser matcher paired issuers across states; it was replaced once we checked. Scoring all of this is compute, not ingest, so it's covered in [Article 5](05-measuring-models-honestly.md).

Intraday: the domain that never leaves the LAN

The intraday stream detects patterns (opening-range breaks, VWAP reclaims and the like) on each one-minute bar close. Choosing the feed took longer than building the detectors, so here's what we tried:

Feed	Result
FMP websockets (Nasdaq, IEX, OTC, UK)	Every subscribe returns 401. These are exchange-licensed add-ons the key lacks. The crypto socket works with the same key, so it's an entitlement gap, not a protocol bug
Alpaca, basic plan	Real-time IEX only. IEX is 2.3–6.3% of consolidated volume, so relative volume survives (it's a same-venue ratio) but session VWAP doesn't: a VWAP detector would assert crosses of a level nobody else watches
IBKR Mobile	Can't serve the TWS API at all. And IBKR allows one session per username, so opening the mobile app kicks the desktop session out
IBKR TWS real-time bars	Works and is consolidated, but needs a logged-in desktop session, force-restarts daily and caps at about 90 market-data lines
FMP 1-minute REST, polled every 60 sec	Default No desktop app, no exchange entitlement, and volume measured at 98.5–101.2% of the consolidated tape for the same minutes, so session VWAP is meaningful

Since detectors only evaluate on bar close, polling loses nothing but sub-minute latency. Both feeds expose the same interface, so the detectors, the API and the outcome logger don't know which one is running. The status payload carries a `vwap_trustworthy` flag and the agent suppresses the VWAP detector when it's false.

FMP's one-minute endpoint has four traps, all now commented in place:

1. Passing a date range returns less data, not more. With no `from`/`to`, it returns 1,170

bars across three sessions; with any range, even a month, it returns 390 bars from one. The

warm start sends neither.

2. Timestamps are naive US/Eastern, not UTC. Read as UTC, a 15:59 bar becomes 11:59, and every session-scoped measure (VWAP reset, opening range, regular-hours filter) runs over the wrong window.

3. A poll re-delivers older sessions' bars. Resetting session state on their timestamps wiped the current session's VWAP. Reset now happens only when a bar is appended.

4. Steady-state polls must fold only the newest bars. Re-folding all 1,170 every cycle sent

bars older than the retained window down the out-of-order path, forcing 390 redundant session rebuilds per symbol per minute.

There's no multi-symbol batching on that endpoint, so symbols are fetched individually; at a

concurrency of eight, about 90 symbols fit in the 60-second cycle.

Market Hours Gating

The stream window lives in `util/market_window.py`: 09:15 to 16:05, weekdays, NYSE holidays

skipped. The 15 minutes before the open give the backfill time to settle so the opening-range detector has bars at the bell; the five minutes after the close let the final bar arrive and be evaluated. The holiday check fails open: if the calendar can't be loaded, the stream runs on the nine or so holidays a year rather than going silent on the 250 trading days.

The gate used to live in the governor, which killed the agent at 16:05 because an ungated poller costs a request a minute all night. But the LAN API that serves intraday data to the apps runs inside the agent process, so the kill also took away the only intraday endpoint at exactly the moment a finished session became worth reviewing. Every evening and all weekend the panel showed a connection error. Now the agent runs 24/7 and the gate is enforced in the poll loop. Outside the window the poller sleeps and makes zero FMP requests, while the API keeps serving the retained session.

Three details keep that working: one seeding pass is allowed through the gate on a cold start (bars live only in memory, so without it every bars request 404s); the seeded state is cleared at the open so the first fetch is a full one; and after the close the signals endpoint reads the day's full list rather than the live set, which is pruned to an hour. Measured on one

day, 430 live signals at 16:45 had become 120 by 17:00 while the day list held 2,720.

Intraday data is never copied to Cloudflare. IBKR market data is licensed to the account holder, not for redistribution, and the value of intraday data is in being current, which propagation lag would undermine. Apps reach it over the LAN or not at all, and treat "unreachable" as a normal state rather than an error.

Debugging an ingest domain

When a screen looks wrong, the domain diagrams give a fixed order of questions:

1. Did the flow run? Check the Prefect run for that deployment. If nothing ran at all, the

serve process is the suspect, not the flow ([Article 2](43-cron-jobs-one-process)).

2. Did it land? Query the local store directly: row counts and the newest timestamp. The TTM

problem was visible in one query once we thought to look at insert times.

3. Is it complete? Compare counts across stages. A capped Redis mirror or a missing category in a routing list shows up as a count that's suspiciously round or suspiciously lower than the source.

4. Is it right? Check invariants a human would catch: no holding dated in the future, no

convertible-bond count of zero, market caps in the right currency unit.

The fourth question is the one that found the N-PORT bugs, and it's the one no scheduler will ask for you.

Takeaways

Organise ingest diagrams by domain, not by vendor. Vendors change; domains don't.
Skip the local store when it adds nothing. Chart history, PlanetScale fundamentals and the county nowcast all go straight to the edge.
Measure a client's concurrency ceiling before you size a job around it. The FMP MCP client's limit was two in flight, not the vendor's.
Row caps and type widths are silent truncation. Check counts at each hop, and check the widest identifier you'll ever store.
Audit parsers against the source format, field by field, with fill rates. That found 4.7 million loans with no debt rows, which reading the code never did.
Draw the gaps too. "No social sentiment: 403 on this plan" on a diagram saves someone an afternoon.
Bulk endpoints change the economics of backfills, and so do their rate limits. Cache, pace, and never let a key reach a log line.