Vol. I — MMXXVI Berlin EN · DE
The Good

Python Writes, TypeScript Reads: One SQLite File, Zero Drift

Python for ingestion, TypeScript for the app, one read-only SQLite file as the seam — and one JSON Schema generating matching Zod and Pydantic types.

I built a quant equity screener for myself: ~500 tickers, ~50 computed KPIs, a web UI I check daily. It’s a Python/TypeScript monorepo with a deliberately odd integration seam — a single SQLite file — and after a year of daily use, the architecture decisions that looked over-engineered for a personal tool turned out to be the ones that mattered most. Here’s the reasoning, including the part that still depends on discipline.

Why one SQLite file instead of an API between Python and TypeScript?

Python owns the database schema and is the only writer. The TypeScript side opens the same file read-only — it never migrates, never writes, never disputes ownership.

yfinance → Python ingestion → SQLite ←(read-only)— tRPC API → React web app
           validate · compute KPIs

One language would have meant compromising one end or the other. Python’s data ecosystem — pandas at the vendor boundary, SQLAlchemy Core for the schema, pydantic (Python’s runtime data-validation library) — is what you want for fetch-validate-compute. On the other side, tRPC (a TypeScript RPC library that infers API types on the client, no separate API schema to maintain) gives me type safety from database row to React component. Splitting the stack costs you one thing: the two sides’ types drifting apart. That specific failure is what the codegen section below kills.

As for the database: a personal tool runs on one machine, and SQLite is a file. Backup is cp. A feature branch works on a snapshot of the real data. There is no server to run, patch, or secure, and the schema code is dialect-agnostic (SQLAlchemy Core doesn’t care which SQL engine sits underneath), so a future move to Postgres is a one-line URL change I haven’t needed yet. The boring database was the right database.

The medallion layers: because vendors rewrite history

Ingestion follows the medallion pattern (a data-engineering convention: raw → cleaned → aggregated layers):

  • Bronze (raw_fetch): every vendor payload, verbatim, append-only.
  • Silver: validated prices and fundamentals derived from bronze. Anything that fails validation lands in a quarantine table with the reason — bad data gets isolated, never silently coerced.
  • Gold (kpi_snapshot): computed KPIs, derived from silver.

For a hobby project this sounds like enterprise cosplay. The payoff is one property: KPIs are computed from stored raw inputs, so a formula change never needs a re-scrape. When I switched the RSI calculation to Wilder smoothing, the whole universe recomputed offline in seconds. When I added price-to-free-cash-flow months after cash-flow statements first started landing in bronze, the back-fill was a single offline command.

Two rules ride along with this and both exist because financial data punishes sloppiness:

Missing data is null, never zero. A screener that treats a missing P/E as 0 will cheerfully rank a company with no earnings as the cheapest stock in the universe. In mine, a stock with no earnings has no P/E, and it drops out of “P/E < 15” screens instead of topping them. This was a founding rule, not a lesson learned — some bugs you only get to prevent once.

Point-in-time everything. Every fundamental row carries the date I observed it, and observations are never overwritten. Data vendors restate numbers retroactively; if your database silently updates, your history becomes fiction and any backtest on it inherits the lie. Append-only observation dates are the defense — and, compounded over years, they become a point-in-time archive that’s impractical for an individual to reconstruct after the fact.

Generating matching Zod and Pydantic types from one JSON Schema

Shared data shapes are defined once, as JSON Schema. Here’s a real field from the contract:

// security.schema.json
"short_ratio": { "type": ["number", "null"] }

Two generators run from it — pnpm generate on the TypeScript side, a Python script on the other — and produce both validators:

// generated Zod (TypeScript)
short_ratio: z.union([z.number(), z.null()]).optional()
# generated pydantic (Python)
short_ratio: float | None = None

A CONTRACT_VERSION is asserted at startup on both sides, and CI fails if generated output drifts from the schema. Across three contract versions and roughly twenty new fields, the two stacks have not disagreed about a shape — which is less an achievement than a structural property: generated code can’t drift from its source. That’s exactly why I generate it.

Honesty requires the flip side. The contract types can’t drift, but the TypeScript mirror of the raw database columns (the ORM table definitions) is hand-maintained against the Python DDL. That seam is protected by convention and review, not by a generator — it’s the one border where discipline still substitutes for structure, and it has the failure mode you’d expect: nothing stops a column from being mirrored wrong except a reviewer noticing.

What I’d repeat, and what I’m watching

  • Repeat: SQLite as the seam (a year of zero database operations), codegen for every cross-language shape, compute-from-stored-raw (formula changes are free).
  • Watching: the hand-maintained column mirror — if this project grows another writer or another reader, that’s the first thing I’d generate too.
  • The caveat that outranks all of this: none of the architecture matters if your data source’s license doesn’t permit what you’re building toward. I learned that the expensive way — the full story is in Can you use yfinance commercially?.

The system was built with AI agents under a spec-driven, adversarially-reviewed workflow — that build process has its own write-up.

← All build logs