-- 0023_host_repo_stats_history.sql
--
-- Daily time-series of per-host repo metrics, used by the P6-03
-- trend sparkline + chart. One row per (host_id, UTC date),
-- last-write-wins per column. Population is best-effort and
-- piggy-backs on the existing repo.stats WS message — nothing
-- else writes here.

CREATE TABLE host_repo_stats_history (
  host_id           TEXT NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
  day               TEXT NOT NULL,           -- 'YYYY-MM-DD' UTC
  total_size_bytes  INTEGER,                 -- nullable: partial patches preserve existing value
  snapshot_count    INTEGER,                 -- nullable
  recorded_at       TEXT NOT NULL,           -- RFC3339Nano of latest write
  PRIMARY KEY (host_id, day)
);

CREATE INDEX host_repo_stats_history_host_day
  ON host_repo_stats_history(host_id, day DESC);
