1"""/prices · /prices/history · /prices/index — append-only pricing table (`valid_to` closes a row)."""2from __future__ import annotations34from typing import Any56from fastapi import APIRouter, Query, Request78from aiatlas.api.common import (9 EVENT_COLS,10 EVENT_FROM,11 PAGINATION,12 PRICE_COLS,13 PRICE_FROM,14 ApiError,15 Pagination,16 cached,17 change_event,18 page,19 price_row,20 resolve_id,21)22from aiatlas.db import connection, fetch_all, fetch_val2324router = APIRouter(prefix="/api/v1/prices", tags=["prices"])25SORTS = {"input": "p.input_per_mtok asc nulls last", "output": "p.output_per_mtok asc nulls last", "model": "m.canonical_name asc", "provider": "pv.canonical_name asc",26 "observed": "p.observed_at desc", "valid_from": "p.valid_from desc"}272829@router.get("")30@cached(300)31async def list_prices(request: Request, p: Pagination = PAGINATION, model: str | None = None, provider: str | None = None, sort: str = "model", order: str = "",32 current: int = Query(1, ge=0, le=1)) -> dict[str, Any]:33 if sort not in SORTS:34 raise ApiError(400, f"sort must be one of {', '.join(SORTS)}")35 order_sql = SORTS[sort]36 if order == "desc" and " asc" in order_sql:37 order_sql = order_sql.replace(" asc", " desc")38 elif order == "asc" and " desc" in order_sql:39 order_sql = order_sql.replace(" desc", " asc")40 where = ["p.valid_to is null"] if current else ["true"]41 params: dict[str, Any] = {}42 async with connection() as conn:43 if model:44 where.append("p.model_id = :model")45 params["model"] = await resolve_id(conn, model)46 if provider:47 where.append("p.provider_id = :provider")48 params["provider"] = await resolve_id(conn, provider)49 where_sql = " and ".join(where)50 rows = await fetch_all(conn, f"select {PRICE_COLS} from {PRICE_FROM} where {where_sql} order by {order_sql}, p.id limit :lim offset :off", lim=p.limit, off=p.offset, **params)51 total = await fetch_val(conn, f"select count(*) from prices p where {where_sql}", **params)52 return page([price_row(r) for r in rows], int(total or 0), p)535455@router.get("/history")56@cached(300)57async def price_history(request: Request, model: str | None = None, provider: str | None = None, limit: int = Query(2000, ge=1, le=5000)) -> dict[str, Any]:58 if not model and not provider:59 raise ApiError(400, "model or provider is required")60 where = ["true"]61 params: dict[str, Any] = {"lim": limit}62 async with connection() as conn:63 if model:64 where.append("p.model_id = :model")65 params["model"] = await resolve_id(conn, model)66 if provider:67 where.append("p.provider_id = :provider")68 params["provider"] = await resolve_id(conn, provider)69 rows = await fetch_all(conn, f"select {PRICE_COLS} from {PRICE_FROM} where {' and '.join(where)} order by p.valid_from asc, p.id limit :lim", **params)70 return {"items": [price_row(r) for r in rows]}717273@router.get("/index")74@cached(600)75async def price_index(request: Request, days: int = Query(180, ge=7, le=1825)) -> dict[str, Any]:76 async with connection() as conn:77 series = await fetch_all(conn, """78 with days as (select generate_series(((now() at time zone 'UTC') - make_interval(days => :d))::date::timestamp, (now() at time zone 'UTC')::date::timestamp, interval '1 day')::date as day),79 live as (select p.model_id, p.input_per_mtok, p.output_per_mtok, p.valid_from, p.valid_to from prices p80 where p.input_per_mtok is not null and p.input_per_mtok > 0 and p.valid_from < now())81 select d.day, percentile_cont(0.5) within group (order by l.input_per_mtok) as median_input,82 percentile_cont(0.5) within group (order by l.output_per_mtok) as median_output,83 min(l.input_per_mtok) as min_input, max(l.input_per_mtok) as max_input, count(distinct l.model_id) as models, count(l.model_id) as offers84 from days d left join live l on l.valid_from < (d.day + 1)::timestamp at time zone 'UTC' and (l.valid_to is null or l.valid_to >= (d.day + 1)::timestamp at time zone 'UTC')85 group by d.day order by d.day""", d=days)86 movers = await fetch_all(conn, f"select {EVENT_COLS} from {EVENT_FROM} where ev.category = 'price' and ev.observed_at > now() - make_interval(days => :d) "87 f"order by ev.importance desc, ev.observed_at desc limit 30", d=days)88 return {"days": days, "series": [{"day": r["day"].isoformat(), "median_input": r["median_input"], "median_output": r["median_output"], "min_input": r["min_input"],89 "max_input": r["max_input"], "models": int(r["models"] or 0), "offers": int(r["offers"] or 0)} for r in series],90 "movers": [change_event(r) for r in movers], "note": "Daily medians of live USD-per-1M-token input/output prices across all provider offers valid at the end of each UTC day."}91