js-vitest-period-filter-date-anchoring

Answer

Root cause. queryPeriod computed the window END from wall-clock new Date(). June data viewed in August produced a window (e.g. Aug 8–15) that was entirely after the dataset's last row → misleading "no data in period" empty state. A second latent bug: window math used local-timezone setDate()/setHours(), so a non-UTC server (or a DST transition) silently shifted boundaries by a day.

The fix (full files: /tmp/dsd-gap-016/src/period-utils.js, /tmp/dsd-gap-016/tests/period-utils.test.js):

// 1) Anchor END to the dataset, not the clock:
//    token_usage.MAX(utc_date) → cost_daily.MAX(date) → wall clock (last resort)
export async function resolveWindowEnd(db = globalThis.db) {
  for (const probe of [
    { table: 'token_usage', column: 'utc_date' },
    { table: 'cost_daily', column: 'date' },
  ]) {
    const result = await db.query({ table: probe.table, aggregate: 'MAX', column: probe.column });
    const max = toUtcDate(result?.[0]?.max);          // 'YYYY-MM-DD' → UTC midnight
    if (max) return { end: max, hasAnyData: true };   // cost_daily only probed if usage is empty
  }
  return { end: new Date(), hasAnyData: false };      // dataset never written
}

// 2) UTC-safe arithmetic: setUTCDate/setUTCHours, never setDate/setHours
export function computeWindow(end, period = '7d') {
  const days = PERIOD_DAYS[period] ?? 7;              // { '7d': 7, '30d': 30 }
  const endDate = new Date(end.getTime());
  endDate.setUTCHours(23, 59, 59, 999);               // inclusive end-of-anchor-day
  const startDate = new Date(endDate.getTime());
  startDate.setUTCHours(0, 0, 0, 0);
  startDate.setUTCDate(startDate.getUTCDate() - (days - 1)); // 7d → anchor − 6
  return { start: startDate, end: endDate };
}

export async function queryPeriod(db = globalThis.db, period = '7d') {
  const { end: anchor, hasAnyData } = await resolveWindowEnd(db);
  const { start, end } = computeWindow(anchor, period);
  const rows = await db.query({
    table: 'token_usage',
    filter: { utc_date: { gte: start.toISOString().slice(0, 10),
                          lte: end.toISOString().slice(0, 10) } },
  });
  return { start, end, anchor, hasAnyData, rows: Array.isArray(rows) ? rows : [] };
}

// 3) Distinguish "dataset has data but this window is empty" from "no data at all"
export function emptyStateMessage({ hasAnyData, rows }) {
  if (!hasAnyData) return 'no-data';       // never written → real empty state
  if (rows.length === 0) return 'empty-period'; // has rows, just not in window
  return null;
}

Mock (tests use the keyFilter pattern on globalThis.db). Both queries go through one db.query, and resolveWindowEnd() runs first — so the mock must dispatch the MAX branch before the FROM-token_usage aggregation branch, otherwise the MAX probe is answered with data rows and the anchor silently degrades to wall clock:

const query = vi.fn(({ table, aggregate, column, filter }) => {
  if (aggregate === 'MAX') {                      // ← dispatched FIRST (anchor)
    const src = table === 'token_usage' ? tokenUsage : costDaily;
    const max = src.length ? Math.max(...src.map(r => Date.parse(r[column]))) : null;
    return [{ max: max === null ? null : new Date(max).toISOString().slice(0, 10) }];
  }
  const key = filter?.utc_date ?? {};             // ← FROM token_usage aggregation
  return tokenUsage.filter(r =>
    (!key.gte || r.utc_date >= key.gte) && (!key.lte || r.utc_date <= key.lte));
});