Price history storage

Lesson 8 · about 15 minutes · why Stock_Price_History is four tables wearing one name, and which job owns each.

Every chart in the app reads one partitioned table. Every ingestion job writes to exactly one partition of it. The bugs that ran longest in this repo were all the same bug: two writers disagreeing about who owns a tier or what timestamp a bar carries. Learn the ownership map and you can predict the failure before reading the ticket.

One parent, four partitions

The parent is Stock_Price_History, LIST-partitioned on retention_tier. Postgres routes each insert to the child whose list value matches, and a query with WHERE retention_tier = 'daily' scans only that child. migrations/20251117230000_restructure_price_history_tiered_retention.sql:20-76

Partitioninterval_typeWriterRetentionServes
Stock_Price_CurrentcurrentPOST /stocks/refresh via upsert_current_price1 row per ticker, overwrittenlive price on Stock_Current_Quotes; terminal
Stock_Price_Intraday5minrollup_minute_bars_to_intraday() from the Stock_Price_Minute staging table2 days1d chart
Stock_Price_Hourly1hourlive: rollup_intraday_to_hourly_live() (data_source='derived'); overnight: backfill_hourly_from_massive() ('massive')7 days5d chart; terminal
Stock_Price_DailydailyPOST /stocks/backfill-daily (Massive grouped EOD)2 years1m to ytd

Every row carries both interval_type and retention_tier. They are always paired the same way, but only retention_tier is the partition key. The upsert key is (ticker, timestamp, retention_tier). stocks/docs/TIERED_RETENTION_ARCHITECTURE.md

Which tier a chart reads

TIMEFRAME_CONFIG in the price-history handler is the whole mapping: 1d reads intraday, 5d reads hourly, everything longer reads daily. Range mode (a from/to pair, used for sparklines) picks hourly for spans of 7 days or less and daily otherwise. stocks/handlers/priceHistory.ts:11-75

Why this table is the blast radius. A gap in the intraday tier can only break the 1d chart. Before 2026-08-07 hourly was derived from intraday, so the same gap also broke 5d a day and a half later, and ON CONFLICT DO NOTHING meant repairing intraday never repaired hourly. Fetching hourly from Massive directly removed the dependency instead of monitoring it. docs/PRICE_TIER_SOURCING.md

The lifecycle, in order

1Twice a minute during the session, ingest-minute-bars pulls a whole-market snapshot from Massive into the Stock_Price_Minute staging table. migrations/20260811100500_schedule_minute_bar_ingestion.sql:65
2Every minute, rollup_minute_bars_to_intraday() publishes closed 5-minute buckets to the intraday partition. Every 10 minutes prune_minute_bars() deletes staged minutes whose bucket is already published. same file:75,83
3Every 5 minutes (2-59/5), rollup_intraday_to_hourly_live() folds the current ET session's 5-minute bars into hour buckets, including the hour in progress, stamped derived. The session window is computed in America/New_York from now() so the cron string never encodes DST. migrations/20260909203216, 20260909203451
4Overnight (*/5 2-5 * * 2-6 UTC), backfill-hourly-from-massive slices the universe into 48 batches and fetches real hourly bars per ticker through refresh-historical, upserting with data_source='massive'. migrations/20260811101000_source_hourly_tier_from_massive.sql:199
5Nightly, backfill-daily calls Massive's grouped endpoint once per trading date for the whole market and re-stamps each bar to midnight ET. stocks/handlers/dailyBackfill.ts · docs/BACKFILL_RUNBOOK.md
6Nightly, cleanup_old_price_data() prunes intraday past 2 days, hourly past 7, daily past 2 years. It is the sole pruner. migrations/20260707200457_rearchitect_price_history_massive_authoritative.sql

Two invariants the schema now enforces

Provenance decides precedence

The live roll-up's ON CONFLICT DO UPDATE carries a WHERE data_source = 'derived' once the hour has settled, so derived never overwrites a Massive bar. The overnight fetch upserts with merge-duplicates and flips the bar to massive. Whichever runs first, the fetched bar wins; if the fetch fails one night, derived bars stay and the chart degrades rather than empties. docs/PRICE_TIER_SOURCING.md § "provenance is the precedence rule"

Daily bars are midnight ET, and the constraint says so

For seven months two writers stamped the same trading day differently: the retired sampler wrote 00:00Z, Massive per-ticker aggregates wrote midnight ET (04:00Z or 05:00Z). Because the upsert key includes timestamp, they never collided. They accumulated, doubling average-volume and returning two bars per day. The fix is a CHECK constraint on the daily partition, added NOT VALID so it bites new writes immediately while legacy rows are purged. migrations/20260713202225_enforce_midnight_et_on_daily_bars.sql

Why AT TIME ZONE 'UTC' and not date_trunc. CHECK expressions must be IMMUTABLE. timezone(text, timestamptz) with an explicit zone is; date_trunc on a timestamptz is only STABLE and Postgres rejects it. DST flips at 2am on a Sunday, never a trading day, so a daily bar is always exactly one of the two times.

What was retired, and why it matters when you read old migrations

Until 2026-07-07 a sampler chain copied Stock_Price_Current into intraday every 5 minutes, then rolled intraday to hourly to daily overnight. It copied the cumulative daily OHLC into every "5min" bar and wrote approximate daily rows that competed with Massive's real EOD. aggregate_current_to_intraday() and aggregate_hourly_to_daily() were dropped; aggregate_intraday_to_hourly() followed on 2026-09-09. Migrations older than that describe a system that no longer exists. Trust the doc's ownership table, not a 2025 migration's comments. migrations/20260707200457

Check yourself

Do this in the repo

  1. With the local stack up, list the partitions and their row counts: 
    psql postgresql://postgres:postgres@localhost:54322/postgres -c "
select tableoid::regclass, retention_tier, data_source, count(*)
from public.\"Stock_Price_History\" group by 1,2,3 order by 1;"
  2. Try to violate the daily constraint and read the error text: 
    psql postgresql://postgres:postgres@localhost:54322/postgres -c "
insert into public.\"Stock_Price_Daily\" (ticker, company_name, open_price, high_price, low_price, close_price, volume, timestamp, interval_type, retention_tier)
values ('AAPL','Apple',1,1,1,1,0,'2026-09-25 20:00:00Z','daily','daily');"
  3. Run the pgTAP guard for this area: cd src && npx supabase test db, then open tests/database/price-history-rearchitecture.test.sql and find the assertion that the retired functions no longer exist.

Primary source

Read docs/PRICE_TIER_SOURCING.md in full; it is the post-mortem and the design in one file. For the partitioning mechanics, PostgreSQL: Table Partitioning.