When an M1 bar is ingested, six higher timeframes (M5, M15, M30, H1, H4, D1) get materialized in the same transaction as the raw insert. If the derive step fails, the raw insert rolls back. Half-written state isn’t possible.
Everything else falls out of that. One DuckDB file, one writer, one Python threading.Lock.
What it is
A FastAPI service that stores OHLC bars and tick data. Bars come in over HTTP, raw bars get stored, and the higher timeframes derive immediately in the same transaction. Queries hit DuckDB directly, behind cursor pagination and API-key auth.
It runs on one VPS. A 4GB API container, a 1GB Postgres container, and a DuckDB file on disk. Postgres holds users and API keys. The DuckDB file holds every byte of market data: 80+ instruments, 25 years of history back to July 2000.
Stack
Storage: one bind-mounted DuckDB file, two tables. ohlc_data keyed on (instrument, timeframe, timestamp), tick_data on (instrument, timestamp). No partitioning.
Compute: DuckDB in-process, 2GB memory cap. Aggregations use time_bucket with arg_min(open, ts) and arg_max(close, ts) to keep OHLC semantics.
API: FastAPI + uvicorn. Cursor pagination over base64 JSON. Page sizes capped at 10k bars or 100k ticks. Max upload 500MB, read in 1MB chunks.
Auth: API keys in Postgres, scopes read / write / admin. No JWT, no signup endpoint.
Three decisions
One writer, enforced in Python. DuckDB tolerates a single writer per file. The conventional way to enforce that is to be careful. The reliable way is a process-wide threading.Lock around a write_transaction() that opens BEGIN, runs the body, and either commits or rolls back. Concurrent ingests serialize cleanly. The shutdown hook acquires the same lock with a 25s timeout so in-flight writes drain before the container exits. Cost: writes don’t parallelize. For what this thing actually sees, that’s fine.
Derivation in the same transaction as raw ingest. The conventional shape is raw, then an orchestrator, then silver tables. I didn’t want any of that. It adds a scheduler, a retry story, and a window where data is half-derived. Instead POST /ingest does the raw upsert and all six derivations inside one write_transaction(). ON CONFLICT makes it idempotent. A WHERE existing.source = 'raw' guard keeps derived rows from overwriting raw ones. Ingest latency now includes derivation latency. That’s worth it to never debug a half-derived hour at 2am.
Snap timestamps to canonical UTC buckets on ingest. Brokers shift their session boundaries with DST. Before this, the same hour could land twice with timestamps an hour apart, and both rows would survive the primary key check. One commit added a bucket-snapping step on the way in. After that, the PK collapses duplicates instead of preserving them. The database has no record of broker-local time anymore. Fine, nothing asks DuckDB for that.
What’s naive
Per-row error handling on ingest doesn’t exist. If row 4,238 of a 10k-row CSV is malformed, the whole batch fails partway through parse. You get a stack trace, not a partial commit with a list of bad rows. Fine for one trusted source. Embarrassing for anything wider.
Late-arriving data isn’t handled. An M1 bar that lands after its hour has been derived gets inserted as raw, but the H1 it should have updated stays stale. There’s no path to re-derive the window. I know this is wrong. I haven’t hit it in practice yet.
No metrics beyond logs. Structured JSON with request IDs, Caddy access logs. No Prometheus, no tracing. The day I want “p95 ingest latency last week,” I’ll have to add something.
What I’d change first
The threading.Lock is the single load-bearing assumption. One process, one writer. The moment a second ingestor shows up on a different host, that invariant is gone and either a coordinator service or a different engine has to appear.