jgt-pricedb

The persistence layer of the next-generation Price Service: bars live in a database with an incremental, anchor-based refresh — CSV files become an export, not the source of truth.

Lineage: implementation of the PDSP specifications reverse-engineered from the Caishen .NET stack — caishen/rispecs/PDSP/ (specs 70–77) — translated into the jgt ecosystem's shapes.

Structural Tension

• Current reality: price data as flat CSVs ($JGTPY_DATA/pds/<INSTR>_<TF>.csv); every refresh rewrites whole files and downstream recomputes whole series.
• Desired state: a database-backed store where refresh finds the anchor (the single forming bar per series) and writes only forward; downstream learns which bar changed and computes incrementally.

Decisions

Production ownership (2026-09-04)

The projection contract is in production: jgtpdb derive-cds (jgtpricedb-util 0.2.0) materializes indicator-complete CDS from a PDS export, and the jgt-data-server updater builds its rolling CDS through the same materialize_cds seam. The Williams engine (jgtpy) is a dependency of that job alone; no chart service imports it.

PDS (today: written by the updater's broker fetch, jgtfxcon)
    -> jgtpdb derive-cds / materialize_cds -> indicator-complete CDS -> chart

The CDS layer is database-native (2026-09-09)

Phase 5's first table exists: cds_rows, keyed (series_id, ts_utc) against bars, indicators in JSONB. jgtpdb derive-cds-db computes them from stored prices through the same Williams engine the file projection uses, and SqlPriceStore.read_cds joins the two in one indexed read. The data server's services/price_store reads chart history from there when JGT_PRICE_STORE_URL is set, which collapses the rolling/deep/CDS/PDS merge the files required — one series holds every depth.

Two things the DB path had to learn that the file path got for free:

• The engine is fed the export quantum, not the raw arithmetic. A mid is (bid+ask)/2 and carries binary dust: 1.1672699999999998 where the quotes say 1.16727. The CSV job never saw it because its input had already been rendered through CsvFormat. Feeding the dust through made a Williams fractal fire on one bar of 941 on live EUR-USD H1. With the quantum applied, the two paths are identical across 62 indicator columns and 941 bars.
• A non-finite indicator is null, not a number. mfi is range over volume and divides by zero on a bar the broker published with none. The CSV spells that inf; JSONB has no infinity, and arithmetic on one propagates it.

What is not yet true: the store is not the production PDS writer. The updater's fetch still writes current/pds and the store ingests from it (updater/src/store_worker.py); jgtpricedb export becomes the source at the cutover. One writer at a time. TTF and MLF remain future layers on the same seam.

• Periods are cut on the broker's trading session, not on UTC midnight (0.1.1). The feed publishes H4, D1, W1 and M1 bars on a 17:00 America/New_York boundary — 21:00Z in summer, 22:00Z in winter — so a UTC grid renamed 100% of them, silently. m1..H1 stay on UTC, where they measurably already were. The session is stated per market, resolved inside bar_key, and recorded on the series; tests/test_session_grid.py is the measurement, run against the real holdings. See rispecs/01-price-store.spec.md, The Session Grid.
• Portable schema (SQLite for local-first dev, PostgreSQL for deployment); the anchor invariant is enforced by a partial unique index (UNIQUE(series_id) WHERE is_forming), not by application discipline.
• No standalone importer. Backfill is the refresh engine's bootstrap path (PDSP spec 72, Algorithm C) fed by a source adapter. CsvSource reads the existing full/ CSVs through the exact same upsert path as BrokerSource live updates. The system can always rebuild itself from its own sources.
• CSV compatibility export (PDSP spec 75 pattern, 0.1.2): consumers (jgtpy, jgtml, jgt-data-server) keep reading the same file paths while the database becomes authoritative underneath. The format is measured rather than assumed — quotes keep the double the feed published, the derived columns are quantized at display precision +1 and +2 (with builtin round, which is not the one pandas reaches for), and the line terminator belongs to the file being replaced. A holdings file read in and written back out is byte-for-byte itself: 89 of 89 live files, 13 instruments, all seven timeframes, 95 208 bars. See rispecs/03-csv-export.spec.md.
• Redis pub/sub (already in the jgt-data-server stack) carries bar-completed events in Phase 4 — the role Rebus/MSMQ played in Caishen.
• pyproject.toml with a static version — deliberately avoiding the setup.py circular-import pattern that blocks other jgt packages from installing in containers.

Phases

Layout

• rispecs/ — jgt-native specifications (the buildable truth; start here)
• src/jgtpricedb/ — the library
• docker-compose.yml — optional Postgres for deployment; SQLite needs nothing
• ../jgt-pricedb-util/ — the sibling package (jgtpricedb-util): the jobs an operator runs against a store — bootstrap, refresh, a bounded forming-bar loop, an OANDA fetch on this session grid, and the freshness and relabel probes. This repository is the store; that one is the steward.