beanflows/CHANGELOG.md

# Changelog

All notable changes to BeanFlows are documented here.

## [Unreleased]

### Changed
- **Monorepo copier migration**: moved all deployment files from `web/` to repo root so
  `copier update` can manage them from the template
  - `Dockerfile` at root: updated for monorepo layout (`web/src/` paths, `--package beanflows`)
  - `docker-compose.yml`, `docker-compose.prod.yml`, `deploy.sh`, `litestream.yml`, `router/`
    all moved to root
  - `deploy.sh`: fixed sops path (`$APP_DIR/.env.prod.sops`, was `$APP_DIR/../.env.prod.sops`)
  - `.copier-answers.yml` at root: points to local template, `_commit: v0.19.0`
  - `.env.example` at root: updated paths for root-relative DuckDB locations
  - `web/src/beanflows/core.py` (`Config`): added `ENABLE_CMS`, `ENABLE_DAAS`, `ENABLE_DIRECTORY`,
    `ENABLE_LEADS`, `BUSINESS_MODEL` feature flags (mirrors copier.yml questions)
  - `supervisor.py`: `web_code_changed()` now checks root `Dockerfile`; deploy script is `./deploy.sh`

### Added
- **ICE certified stock aging report** — Monthly age-bucket × port breakdown extracted via ICE API, stored as gzip CSV, modelled through raw→foundation→serving, exposed at `GET /api/v1/commodities/<code>/stocks/aging`
- **ICE historical warehouse stocks by port** — End-of-month data from Nov 1996 to present, downloaded from static ICE URL, full SQLMesh pipeline, exposed at `GET /api/v1/commodities/<code>/stocks/by-port`
- **ICE API-based URL discovery** — Replaces brittle `ICE_STOCKS_URL` env var; queries ICE Report Center JSON API, finds latest report by label, falls back automatically on 404
- **XLS parsing** (`ice_stocks/xls_parse.py`) — Magic-byte format detection (`OLE2_MAGIC`) and xlrd-based row extraction; handles both CSV and XLS responses from ICE
- **`extract_ice_all`** CLI command — Runs all three ICE extractors (daily stocks, aging, by-port) in one command
- **`extract_all`** meta-pipeline — Sequences all four data source extractors (`extract`, `extract_cot`, `extract_prices`, `extract_ice_all`), stops on first failure
- **ICE aging dashboard section** — Metric cards (total bags, youngest %, oldest %, bucket count) + stacked horizontal bar chart (age buckets × ports)
- **ICE by-port dashboard section** — Metric cards (total, MoM change, 12m avg, 30yr history) + stacked area chart (30-year view by delivery port)
- **Origin Intelligence page** — Full redesign of country comparison: dark espresso selector panel + metric segment tabs + colored chips + Chart.js line chart + live-built rankings table with proportional bars
- **HATEOAS country comparison** — Clicking an origin immediately fetches an HTMX partial and swaps the canvas; URL updates via `history.pushState()`; no Apply button needed
- **Axis labels on all charts** — Y-axis titles on every overview and Origins chart; column unit header in YoY and rankings tables

### Fixed
- **ICE XLS date parsing** — Handle `"Feb 20, 2026  1:35:39PM"` format (previously only `"1/30/2026"` worked); tries multiple `strptime` formats across single-token and three-token date candidates

### Changed
- **Countries nav item renamed to "Origins"** — Matches "Origin Intelligence" page heading in both sidebar and mobile bottom nav
- **`dim_commodity`** — Added `ticker` and `ice_stock_report_code` columns (`KC=F`, `COFFEE-C`)
- **SQLMesh macros** — Added `ice_aging_glob()` and `ice_stocks_by_port_glob()` alongside existing `ice_stocks_glob()`

## [0.2.0] - 2026-02-01

### Added
- **CFTC COT disaggregated futures** — Weekly trader positioning (managed money net, COT index 26w/52w, % of open interest); full SQLMesh pipeline; `GET /api/v1/commodities/<code>/positioning`
- **KC=F Coffee C futures prices** — Daily OHLCV from Yahoo Finance (1971–present), 20d/50d SMA, 52-week range; `GET /api/v1/commodities/<code>/prices`
- **ICE certified warehouse stocks (daily rolling)** — Certified Arabica bags, WoW change, 30d average, 52-week drawdown; `GET /api/v1/commodities/<code>/stocks`
- **COT positioning dashboard section** — Metric cards + dual-axis line chart (managed money net + COT index)
- **Price dashboard section** — Metric cards + three-line chart (close, 20d MA, 50d MA)
- **ICE daily stocks dashboard section** — Metric cards + area chart with 30d MA
- **`/methodology` page** — Full data source documentation: lineage, field definitions, update cadence for every source
- **Automated supervisor pipeline** — Systemd service runs all extractors daily; Discord/webhook alerting on failure
- **`extract_all` meta-pipeline precursor** — Individual pipeline entries for all four data sources

### Fixed
- **COT pipeline nulls** — `TRY_CAST` instead of `CAST` in foundation model; leading zeros in CFTC commodity codes preserved
- **Concurrent DuckDB queries** — `cursor()` per thread prevents connection state corruption

## [0.1.0] - 2025-12-01

### Added
- **Coffee analytics dashboard** — Chart.js visualizations for global supply/demand time series, stock-to-use ratio trend, top producing countries bar chart, and YoY production change table
- **Country comparison page** — Multi-select country picker with metric selector and overlay line chart at `/dashboard/countries`
- **DuckDB data access layer** (`web/src/beanflows/analytics.py`) — Async bridge to read-only DuckDB via `asyncio.to_thread()` with 7 domain query functions and metric allowlist for injection prevention
- **Commodity REST API** — `GET /api/v1/commodities`, `/commodities/<code>/metrics`, `/commodities/<code>/countries`, `/commodities/<code>/metrics.csv` (CSV export)
- **BeanFlows plan tiers** — Free (coffee, 5yr history), Starter (full history, CSV, API), Pro (all 65 commodities, unlimited API)
- **Landing, features, and pricing pages** rewritten for coffee market intelligence positioning
- **Health check** now verifies both SQLite and DuckDB connectivity
- **Admin panel** shows commodity count and data year range from DuckDB
- **Tests** — `test_dashboard.py` (8 tests), `test_api_commodities.py` (8 tests), analytics mock fixture in conftest

### Fixed
- **Pipeline time granularity** — Added `market_year` to GROUP BY in `cleaned.psdalldata__commodity_pivoted`; previously summed across all market years making per-year metrics meaningless

### Removed
- `items` table, FTS virtual table, and triggers from schema (boilerplate)
- Items CRUD from API routes

### Changed
- `PLAN_FEATURES` updated from generic to domain-specific scopes
- `PLAN_LIMITS` changed from `items`/`api_calls` to `commodities`/`history_years`/`api_calls`
- API now requires Starter or Pro plan (free plan gets 403)