fix(dashboard): use an indexable UTC month-range predicate for used-this-month (#10381)

sumUsageTokensThisMonth() filtered usage_history with
substr(timestamp, 1, 7) = strftime('%Y-%m', 'now') — a substr() expression
SQLite cannot use a range index on, and fragile against any timestamp
that isn't exactly ISO-shaped. Replace with an indexable inclusive-start/
exclusive-end UTC range: timestamp >= <month start> AND timestamp <
<next month start>, matching the ISO 8601 format saveRequestUsage()
already writes.

Adds a boundary regression test: the first instant of the current month
is included, the last instant of the previous month is excluded, and a
next-month row is excluded too (covers the upper bound substr() could
never express).

changelog.d/fixes/10381-free-tier-usage-history.md

@@ -0,0 +1 @@
+- fix(dashboard): Free Tier 'used this month' now includes live usage_history rows, not just the rolled-up daily summary (#10381)

src/lib/db/usageSummary.ts

@@ -1,14 +1,38 @@
 import { getDbInstance } from "./core.ts";
 import type { SqliteAdapter } from "./adapters/types.ts";
 
-/** Total input+output tokens rolled up in daily_usage_summary for the current calendar month. */
+/**
+ * Total input+output tokens for the current calendar month, across BOTH storage legs:
+ *
+ * 1. `daily_usage_summary` — the rolled-up aggregate (filled only by the retention
+ *    cleanup path rolling up rows OLDER than `retention.usageHistory`, default 365 days),
+ *    so the current month's rows never appear here until ~a year later (or the operator
+ *    lowers retention).
+ * 2. `usage_history` — the live per-request rows (written by `saveRequestUsage` with
+ *    `tokens_input`/`tokens_output` and an ISO `timestamp`), which hold the current month's
+ *    actual usage.
+ *
+ * #10381: reading only leg 1 always returned ~0 for the current month. No double-count:
+ * a row is rolled into `daily_usage_summary` only immediately before being deleted from
+ * `usage_history` by the same cleanup step. The analytics layer uses the same
+ * reconciliation in `buildUnifiedSource`.
+ */
 export function sumUsageTokensThisMonth(db: SqliteAdapter = getDbInstance()): number {
   try {
     const row = db
       .prepare(
-        `SELECT COALESCE(SUM(total_input_tokens + total_output_tokens), 0) AS used
-         FROM daily_usage_summary
-         WHERE date >= strftime('%Y-%m-01','now')`
+        `SELECT
+           COALESCE((
+             SELECT SUM(tokens_input + tokens_output)
+             FROM usage_history
+             WHERE timestamp >= strftime('%Y-%m-01T00:00:00.000Z','now')
+               AND timestamp < strftime('%Y-%m-01T00:00:00.000Z','now','+1 month')
+           ), 0) +
+           COALESCE((
+             SELECT SUM(total_input_tokens + total_output_tokens)
+             FROM daily_usage_summary
+             WHERE date >= strftime('%Y-%m-01', 'now')
+           ), 0) AS used`
       )
       .get() as { used: number } | undefined;
     return row?.used ?? 0;

tests/unit/free-tier-used-this-month.test.ts

@@ -21,3 +21,81 @@ test("sumUsageTokensThisMonth sums only the current calendar month's rolled-up t
   insert.run("groq", "llama", "2000-01-01", 9999, 9999); // long ago — excluded
   assert.equal(sumUsageTokensThisMonth(), 400);
 });
+
+// #10381: the current month's LIVE usage lives in usage_history (per-request rows written
+// by saveRequestUsage) and is never rolled into daily_usage_summary until retention cleanup
+// (~365 days). sumUsageTokensThisMonth must count both legs without double-counting.
+test("sumUsageTokensThisMonth includes the current month's raw usage_history rows (#10381)", () => {
+  const db = getDbInstance();
+  // Ensure both tables exist (defensive, same shape as the migrations).
+  db.exec(`CREATE TABLE IF NOT EXISTS usage_history (
+    id INTEGER PRIMARY KEY AUTOINCREMENT, provider TEXT, model TEXT, connection_id TEXT,
+    api_key_id TEXT, api_key_name TEXT, tokens_input INTEGER DEFAULT 0, tokens_output INTEGER DEFAULT 0,
+    tokens_cache_read INTEGER DEFAULT 0, tokens_cache_creation INTEGER DEFAULT 0, tokens_reasoning INTEGER DEFAULT 0,
+    service_tier TEXT DEFAULT 'standard', status TEXT, success INTEGER DEFAULT 1, latency_ms INTEGER DEFAULT 0,
+    ttft_ms INTEGER DEFAULT 0, error_code TEXT, timestamp TEXT NOT NULL);`);
+  db.exec(`CREATE TABLE IF NOT EXISTS daily_usage_summary (id INTEGER PRIMARY KEY AUTOINCREMENT, provider TEXT NOT NULL, model TEXT NOT NULL, date TEXT NOT NULL, total_requests INTEGER NOT NULL DEFAULT 0, total_input_tokens INTEGER NOT NULL DEFAULT 0, total_output_tokens INTEGER NOT NULL DEFAULT 0, total_cost REAL NOT NULL DEFAULT 0.0, created_at TEXT NOT NULL DEFAULT (datetime('now')));`);
+
+  // Isolate from the shared DB (getDbInstance is a singleton across test cases): start empty.
+  db.exec("DELETE FROM usage_history");
+  db.exec("DELETE FROM daily_usage_summary");
+
+  const now = new Date();
+  const thisMonth = now.toISOString().slice(0, 7); // YYYY-MM
+  const liveStamp = `${thisMonth}-15T12:00:00.000Z`;
+  const pastStamp = "2000-01-15T12:00:00.000Z";
+
+  const insHistory = db.prepare(
+    "INSERT INTO usage_history (provider, model, tokens_input, tokens_output, timestamp) VALUES (?,?,?,?,?)"
+  );
+  insHistory.run("openai", "gpt-4.1", 150, 250, liveStamp); // 400 current-month live tokens
+  insHistory.run("openai", "gpt-4.1", 9999, 9999, pastStamp); // very old — excluded
+
+  // A rolled-up current-month row coexisting (no double-count — the source usage_history row
+  // was already deleted by the retention rollup, so both legs are additive and disjoint).
+  const insSummary = db.prepare(
+    "INSERT INTO daily_usage_summary (provider, model, date, total_input_tokens, total_output_tokens) VALUES (?,?,?,?,?)"
+  );
+  insSummary.run("groq", "llama", `${thisMonth}-20`, 25, 25); // +50 rolled-up
+
+  assert.equal(sumUsageTokensThisMonth(), 400 + 50);
+});
+
+// #10509 sweep: the `substr(timestamp, 1, 7) = strftime('%Y-%m', 'now')` predicate was
+// fragile/non-indexable (SQLite cannot use a range index on a substr() expression, and a
+// non-ISO-shaped timestamp string silently mismatches). Replaced with an indexable UTC
+// month-range comparison (`timestamp >= <month start> AND timestamp < <next month start>`).
+// This test pins the exact boundary: the first instant of the current month is INCLUDED,
+// the last instant of the PREVIOUS month is EXCLUDED, and a NEXT-month row is EXCLUDED too
+// (guards the upper-bound half of the range, which substr() could never express directly).
+test("sumUsageTokensThisMonth uses an inclusive-start/exclusive-end UTC month range (#10509)", () => {
+  const db = getDbInstance();
+  db.exec(`CREATE TABLE IF NOT EXISTS usage_history (
+    id INTEGER PRIMARY KEY AUTOINCREMENT, provider TEXT, model TEXT, connection_id TEXT,
+    api_key_id TEXT, api_key_name TEXT, tokens_input INTEGER DEFAULT 0, tokens_output INTEGER DEFAULT 0,
+    tokens_cache_read INTEGER DEFAULT 0, tokens_cache_creation INTEGER DEFAULT 0, tokens_reasoning INTEGER DEFAULT 0,
+    service_tier TEXT DEFAULT 'standard', status TEXT, success INTEGER DEFAULT 1, latency_ms INTEGER DEFAULT 0,
+    ttft_ms INTEGER DEFAULT 0, error_code TEXT, timestamp TEXT NOT NULL);`);
+  db.exec(`CREATE TABLE IF NOT EXISTS daily_usage_summary (id INTEGER PRIMARY KEY AUTOINCREMENT, provider TEXT NOT NULL, model TEXT NOT NULL, date TEXT NOT NULL, total_requests INTEGER NOT NULL DEFAULT 0, total_input_tokens INTEGER NOT NULL DEFAULT 0, total_output_tokens INTEGER NOT NULL DEFAULT 0, total_cost REAL NOT NULL DEFAULT 0.0, created_at TEXT NOT NULL DEFAULT (datetime('now')));`);
+  db.exec("DELETE FROM usage_history");
+  db.exec("DELETE FROM daily_usage_summary");
+
+  const monthStart = db
+    .prepare("SELECT strftime('%Y-%m-01T00:00:00.000Z','now') AS s")
+    .get() as { s: string };
+  const nextMonthStart = db
+    .prepare("SELECT strftime('%Y-%m-01T00:00:00.000Z','now','+1 month') AS s")
+    .get() as { s: string };
+  const lastInstantOfPrevMonth = new Date(
+    new Date(monthStart.s).getTime() - 1
+  ).toISOString();
+
+  const insHistory = db.prepare(
+    "INSERT INTO usage_history (provider, model, tokens_input, tokens_output, timestamp) VALUES (?,?,?,?,?)"
+  );
+  insHistory.run("openai", "gpt-4.1", 10, 0, monthStart.s); // first instant of THIS month — included
+  insHistory.run("openai", "gpt-4.1", 9999, 0, lastInstantOfPrevMonth); // last ms of PREV month — excluded
+  insHistory.run("openai", "gpt-4.1", 9999, 0, nextMonthStart.s); // first instant of NEXT month — excluded
+
+  assert.equal(sumUsageTokensThisMonth(), 10);
+});