1  /**
  2   * Tests for LLM Usage Tracker Module
  3   *
  4   * Tests calculateCost, logLLMUsage, getSiteCost, and getCostByStage functions
  5   * using a temporary SQLite database.
  6   */
  7  
  8  import { describe, test, before, after, beforeEach } from 'node:test';
  9  import assert from 'node:assert/strict';
 10  import { tmpdir } from 'os';
 11  import { join } from 'path';
 12  import { readFileSync, unlinkSync, existsSync } from 'fs';
 13  import Database from 'better-sqlite3';
 14  import { setTimeout as sleep } from 'timers/promises';
 15  
 16  // Set up temp database BEFORE importing the module under test
 17  const testDb = join(tmpdir(), `test-llm-usage-${Date.now()}.db`);
 18  process.env.DATABASE_PATH = testDb;
 19  
 20  // Dynamic import after setting DATABASE_PATH
 21  const {
 22    calculateCost,
 23    logLLMUsage,
 24    getSiteCost,
 25    getCostByStage,
 26    checkBudgetVariance,
 27    getDailySpend,
 28    getHourlySpend,
 29  } = await import('../../src/utils/llm-usage-tracker.js');
 30  
 31  const schemaPath = join(import.meta.dirname, '..', '..', 'db', 'schema.sql');
 32  const schemaSql = readFileSync(schemaPath, 'utf-8');
 33  
 34  /**
 35   * Initialize the test database with the full schema
 36   */
 37  function initSchema() {
 38    const db = new Database(testDb);
 39    db.exec(schemaSql);
 40    db.close();
 41  }
 42  
 43  /**
 44   * Clear all rows from llm_usage and sites tables
 45   */
 46  function clearTables() {
 47    const db = new Database(testDb);
 48    db.exec('DELETE FROM llm_usage');
 49    db.exec('DELETE FROM sites');
 50    db.close();
 51  }
 52  
 53  /**
 54   * Insert a test site and return its id
 55   */
 56  function insertTestSite(domain = 'example.com') {
 57    const db = new Database(testDb);
 58    const result = db
 59      .prepare(
 60        `INSERT INTO sites (domain, landing_page_url, keyword, status)
 61         VALUES (?, ?, ?, 'found')`
 62      )
 63      .run(domain, `https://${domain}`, 'test keyword');
 64    db.close();
 65    return result.lastInsertRowid;
 66  }
 67  
 68  /**
 69   * Query all rows from llm_usage table
 70   */
 71  function getAllUsageRows() {
 72    const db = new Database(testDb);
 73    const rows = db.prepare('SELECT * FROM llm_usage ORDER BY id').all();
 74    db.close();
 75    return rows;
 76  }
 77  
 78  describe('LLM Usage Tracker', () => {
 79    before(() => {
 80      initSchema();
 81    });
 82  
 83    beforeEach(() => {
 84      clearTables();
 85    });
 86  
 87    after(() => {
 88      if (existsSync(testDb)) {
 89        unlinkSync(testDb);
 90      }
 91    });
 92  
 93    describe('calculateCost', () => {
 94      test('returns correct cost for openai/gpt-4o-mini', () => {
 95        // Input: $0.15 per 1M tokens, Output: $0.60 per 1M tokens
 96        const promptTokens = 1_000_000;
 97        const completionTokens = 1_000_000;
 98        const cost = calculateCost('openai/gpt-4o-mini', promptTokens, completionTokens);
 99  
100        // 1M * $0.15/1M + 1M * $0.60/1M = $0.75
101        assert.equal(cost, 0.75);
102      });
103  
104      test('returns correct cost for openai/gpt-4o-mini with smaller token counts', () => {
105        // 500 input tokens, 200 output tokens
106        const cost = calculateCost('openai/gpt-4o-mini', 500, 200);
107  
108        // (500 / 1M) * 0.15 + (200 / 1M) * 0.60
109        // = 0.000075 + 0.000120
110        // = 0.000195
111        const expected = parseFloat((0.000075 + 0.00012).toFixed(6));
112        assert.equal(cost, expected);
113      });
114  
115      test('returns correct cost for claude-3-5-sonnet-20241022', () => {
116        // Input: $3.00 per 1M tokens, Output: $15.00 per 1M tokens
117        const promptTokens = 1000;
118        const completionTokens = 500;
119        const cost = calculateCost('claude-3-5-sonnet-20241022', promptTokens, completionTokens);
120  
121        // (1000 / 1M) * 3.0 + (500 / 1M) * 15.0
122        // = 0.003 + 0.0075
123        // = 0.0105
124        const expected = parseFloat((0.003 + 0.0075).toFixed(6));
125        assert.equal(cost, expected);
126      });
127  
128      test('uses default pricing for unknown model', () => {
129        const promptTokens = 1_000_000;
130        const completionTokens = 1_000_000;
131        const cost = calculateCost('unknown/model-xyz', promptTokens, completionTokens);
132  
133        // Default: input $0.50/1M, output $1.50/1M
134        // (1M * 0.5 + 1M * 1.5) / 1M = 2.0
135        const expected = ((promptTokens * 0.5 + completionTokens * 1.5) / 1_000_000).toFixed(6);
136        assert.equal(cost, expected);
137      });
138  
139      test('returns string for unknown model (toFixed returns string)', () => {
140        const cost = calculateCost('unknown/model', 100, 50);
141        // Unknown model path returns result of toFixed(6), which is a string
142        assert.equal(typeof cost, 'string');
143      });
144  
145      test('returns 0 for zero tokens', () => {
146        const cost = calculateCost('openai/gpt-4o-mini', 0, 0);
147        assert.equal(cost, 0);
148      });
149  
150      test('returns 0 for zero tokens with claude model', () => {
151        const cost = calculateCost('claude-3-5-sonnet-20241022', 0, 0);
152        assert.equal(cost, 0);
153      });
154    });
155  
156    describe('logLLMUsage', () => {
157      test('inserts a record into llm_usage table', () => {
158        const siteId = insertTestSite('test-log.com');
159  
160        logLLMUsage({
161          siteId,
162          stage: 'scoring',
163          provider: 'openrouter',
164          model: 'openai/gpt-4o-mini',
165          promptTokens: 1000,
166          completionTokens: 500,
167          requestId: 'req-123',
168        });
169  
170        const rows = getAllUsageRows();
171        assert.equal(rows.length, 1);
172        assert.equal(rows[0].site_id, siteId);
173        assert.equal(rows[0].stage, 'scoring');
174        assert.equal(rows[0].provider, 'openrouter');
175        assert.equal(rows[0].model, 'openai/gpt-4o-mini');
176        assert.equal(rows[0].prompt_tokens, 1000);
177        assert.equal(rows[0].completion_tokens, 500);
178        assert.equal(rows[0].total_tokens, 1500);
179        assert.equal(rows[0].request_id, 'req-123');
180      });
181  
182      test('handles null siteId', () => {
183        logLLMUsage({
184          siteId: null,
185          stage: 'other',
186          provider: 'anthropic',
187          model: 'claude-3-5-sonnet-20241022',
188          promptTokens: 200,
189          completionTokens: 100,
190        });
191  
192        const rows = getAllUsageRows();
193        assert.equal(rows.length, 1);
194        assert.equal(rows[0].site_id, null);
195        assert.equal(rows[0].stage, 'other');
196      });
197  
198      test('handles omitted siteId (defaults to null)', () => {
199        logLLMUsage({
200          stage: 'proposals',
201          provider: 'openrouter',
202          model: 'openai/gpt-4o',
203          promptTokens: 300,
204          completionTokens: 150,
205        });
206  
207        const rows = getAllUsageRows();
208        assert.equal(rows.length, 1);
209        assert.equal(rows[0].site_id, null);
210      });
211  
212      test('calculates cost correctly and stores it', () => {
213        const siteId = insertTestSite('cost-calc.com');
214  
215        logLLMUsage({
216          siteId,
217          stage: 'scoring',
218          provider: 'openrouter',
219          model: 'openai/gpt-4o-mini',
220          promptTokens: 1_000_000,
221          completionTokens: 1_000_000,
222        });
223  
224        const rows = getAllUsageRows();
225        assert.equal(rows.length, 1);
226        // 1M * $0.15/1M + 1M * $0.60/1M = $0.75
227        assert.equal(rows[0].estimated_cost, 0.75);
228      });
229  
230      test('stores total_tokens as sum of prompt and completion', () => {
231        logLLMUsage({
232          stage: 'enrichment',
233          provider: 'anthropic',
234          model: 'claude-3-5-haiku-20241022',
235          promptTokens: 750,
236          completionTokens: 250,
237        });
238  
239        const rows = getAllUsageRows();
240        assert.equal(rows[0].total_tokens, 1000);
241      });
242  
243      test('handles null requestId by default', () => {
244        logLLMUsage({
245          stage: 'rescoring',
246          provider: 'openrouter',
247          model: 'openai/gpt-4o',
248          promptTokens: 100,
249          completionTokens: 50,
250        });
251  
252        const rows = getAllUsageRows();
253        assert.equal(rows[0].request_id, null);
254      });
255    });
256  
257    describe('getSiteCost', () => {
258      test('returns 0 for site with no usage', () => {
259        const siteId = insertTestSite('no-usage.com');
260        const cost = getSiteCost(siteId);
261        assert.equal(cost, 0);
262      });
263  
264      test('returns 0 for non-existent site ID', () => {
265        const cost = getSiteCost(99999);
266        assert.equal(cost, 0);
267      });
268  
269      test('returns correct total cost for single usage entry', () => {
270        const siteId = insertTestSite('single-usage.com');
271  
272        logLLMUsage({
273          siteId,
274          stage: 'scoring',
275          provider: 'openrouter',
276          model: 'openai/gpt-4o-mini',
277          promptTokens: 1_000_000,
278          completionTokens: 1_000_000,
279        });
280  
281        const cost = getSiteCost(siteId);
282        // $0.15 + $0.60 = $0.75
283        assert.equal(cost, 0.75);
284      });
285  
286      test('returns correct total cost summing multiple usage entries', () => {
287        const siteId = insertTestSite('multi-usage.com');
288  
289        // First usage: scoring with gpt-4o-mini
290        logLLMUsage({
291          siteId,
292          stage: 'scoring',
293          provider: 'openrouter',
294          model: 'openai/gpt-4o-mini',
295          promptTokens: 1_000_000,
296          completionTokens: 1_000_000,
297        });
298  
299        // Second usage: rescoring with gpt-4o-mini
300        logLLMUsage({
301          siteId,
302          stage: 'rescoring',
303          provider: 'openrouter',
304          model: 'openai/gpt-4o-mini',
305          promptTokens: 1_000_000,
306          completionTokens: 1_000_000,
307        });
308  
309        const cost = getSiteCost(siteId);
310        // $0.75 + $0.75 = $1.50
311        assert.equal(cost, 1.5);
312      });
313  
314      test('does not include costs from other sites', () => {
315        const siteId1 = insertTestSite('site-one.com');
316        const siteId2 = insertTestSite('site-two.com');
317  
318        logLLMUsage({
319          siteId: siteId1,
320          stage: 'scoring',
321          provider: 'openrouter',
322          model: 'openai/gpt-4o-mini',
323          promptTokens: 1_000_000,
324          completionTokens: 1_000_000,
325        });
326  
327        logLLMUsage({
328          siteId: siteId2,
329          stage: 'scoring',
330          provider: 'openrouter',
331          model: 'openai/gpt-4o',
332          promptTokens: 1_000_000,
333          completionTokens: 1_000_000,
334        });
335  
336        const cost1 = getSiteCost(siteId1);
337        const cost2 = getSiteCost(siteId2);
338  
339        // site1: gpt-4o-mini = $0.75
340        assert.equal(cost1, 0.75);
341        // site2: gpt-4o = $2.50 + $10.00 = $12.50
342        assert.equal(cost2, 12.5);
343      });
344    });
345  
346    describe('getCostByStage', () => {
347      test('returns empty array when no usage exists', () => {
348        const result = getCostByStage();
349        assert.deepEqual(result, []);
350      });
351  
352      test('groups costs by stage correctly', () => {
353        const siteId = insertTestSite('stage-grouping.com');
354  
355        // Two scoring entries
356        logLLMUsage({
357          siteId,
358          stage: 'scoring',
359          provider: 'openrouter',
360          model: 'openai/gpt-4o-mini',
361          promptTokens: 1_000_000,
362          completionTokens: 1_000_000,
363        });
364  
365        logLLMUsage({
366          siteId,
367          stage: 'scoring',
368          provider: 'openrouter',
369          model: 'openai/gpt-4o-mini',
370          promptTokens: 1_000_000,
371          completionTokens: 1_000_000,
372        });
373  
374        // One proposals entry
375        logLLMUsage({
376          siteId,
377          stage: 'proposals',
378          provider: 'anthropic',
379          model: 'claude-3-5-sonnet-20241022',
380          promptTokens: 1000,
381          completionTokens: 500,
382        });
383  
384        const result = getCostByStage();
385  
386        assert.equal(result.length, 2);
387  
388        // Find each stage in results
389        const scoringRow = result.find(r => r.stage === 'scoring');
390        const proposalsRow = result.find(r => r.stage === 'proposals');
391  
392        assert.ok(scoringRow, 'scoring stage should exist');
393        assert.ok(proposalsRow, 'proposals stage should exist');
394  
395        // Scoring: 2 * $0.75 = $1.50
396        assert.equal(scoringRow.total_cost, 1.5);
397        assert.equal(scoringRow.request_count, 2);
398        assert.equal(scoringRow.total_tokens, 4_000_000);
399  
400        // Proposals: (1000/1M) * 3.0 + (500/1M) * 15.0 = 0.0105
401        assert.equal(proposalsRow.request_count, 1);
402        assert.equal(proposalsRow.total_tokens, 1500);
403      });
404  
405      test('orders results by total_cost DESC', () => {
406        const siteId = insertTestSite('order-test.com');
407  
408        // Low cost entry (enrichment)
409        logLLMUsage({
410          siteId,
411          stage: 'enrichment',
412          provider: 'openrouter',
413          model: 'openai/gpt-4o-mini',
414          promptTokens: 100,
415          completionTokens: 50,
416        });
417  
418        // High cost entry (proposals with expensive model)
419        logLLMUsage({
420          siteId,
421          stage: 'proposals',
422          provider: 'anthropic',
423          model: 'claude-3-opus-20240229',
424          promptTokens: 1_000_000,
425          completionTokens: 1_000_000,
426        });
427  
428        // Medium cost entry (scoring)
429        logLLMUsage({
430          siteId,
431          stage: 'scoring',
432          provider: 'openrouter',
433          model: 'openai/gpt-4o',
434          promptTokens: 1_000_000,
435          completionTokens: 1_000_000,
436        });
437  
438        const result = getCostByStage();
439  
440        assert.equal(result.length, 3);
441        // Opus: $15 + $75 = $90 (highest)
442        assert.equal(result[0].stage, 'proposals');
443        // GPT-4o: $2.50 + $10 = $12.50 (medium)
444        assert.equal(result[1].stage, 'scoring');
445        // GPT-4o-mini: tiny cost (lowest)
446        assert.equal(result[2].stage, 'enrichment');
447      });
448  
449      test('returns correct request_count per stage', () => {
450        const siteId = insertTestSite('count-test.com');
451  
452        // 3 scoring requests
453        for (let i = 0; i < 3; i++) {
454          logLLMUsage({
455            siteId,
456            stage: 'scoring',
457            provider: 'openrouter',
458            model: 'openai/gpt-4o-mini',
459            promptTokens: 100,
460            completionTokens: 50,
461          });
462        }
463  
464        // 1 rescoring request
465        logLLMUsage({
466          siteId,
467          stage: 'rescoring',
468          provider: 'openrouter',
469          model: 'openai/gpt-4o-mini',
470          promptTokens: 100,
471          completionTokens: 50,
472        });
473  
474        const result = getCostByStage();
475        const scoringRow = result.find(r => r.stage === 'scoring');
476        const rescoringRow = result.find(r => r.stage === 'rescoring');
477  
478        assert.equal(scoringRow.request_count, 3);
479        assert.equal(rescoringRow.request_count, 1);
480      });
481    });
482  });
483  
484  describe('checkBudgetVariance', () => {
485    before(() => {
486      initSchema();
487      // llm_cost_budgets is only in migration 079, not in schema.sql — add it here
488      const db = new Database(testDb);
489      db.exec(`
490        CREATE TABLE IF NOT EXISTS llm_cost_budgets (
491          call_type TEXT PRIMARY KEY,
492          expected_cost_per_call REAL NOT NULL,
493          max_cost_per_call REAL NOT NULL,
494          expected_model TEXT NOT NULL,
495          updated_at TEXT DEFAULT (datetime('now'))
496        );
497        INSERT OR IGNORE INTO llm_cost_budgets (call_type, expected_cost_per_call, max_cost_per_call, expected_model) VALUES
498          ('scoring', 0.003, 0.009, 'openai/gpt-4o-mini'),
499          ('rescoring', 0.005, 0.015, 'openai/gpt-4o-mini');
500      `);
501      db.close();
502    });
503    beforeEach(clearTables);
504    after(() => {
505      if (existsSync(testDb)) unlinkSync(testDb);
506    });
507  
508    test('returns empty array when no usage in last hour', () => {
509      const alerts = checkBudgetVariance();
510      assert.ok(Array.isArray(alerts));
511      assert.equal(alerts.length, 0);
512    });
513  
514    test('returns no alerts when cost is within budget', () => {
515      // Insert usage with cost well under the budget for scoring (max: 0.009)
516      const db = new Database(testDb);
517      db.prepare(
518        `INSERT INTO sites (domain, landing_page_url, keyword, status) VALUES ('test.com', 'https://test.com', 'test', 'found')`
519      ).run();
520      const siteId = db.prepare('SELECT last_insert_rowid() as id').get().id;
521      db.prepare(
522        `INSERT INTO llm_usage (site_id, stage, provider, model, prompt_tokens, completion_tokens, total_tokens, estimated_cost)
523         VALUES (?, 'scoring', 'openrouter', 'openai/gpt-4o-mini', 100, 50, 150, 0.003)`
524      ).run(siteId);
525      db.close();
526  
527      const alerts = checkBudgetVariance();
528      const costAlert = alerts.filter(a => a.type === 'cost_variance');
529      assert.equal(costAlert.length, 0, 'No cost variance alert when within budget');
530    });
531  
532    test('raises cost_variance alert when avg cost exceeds max', () => {
533      const db = new Database(testDb);
534      db.prepare(
535        `INSERT INTO sites (domain, landing_page_url, keyword, status) VALUES ('test.com', 'https://test.com', 'test', 'found')`
536      ).run();
537      const siteId = db.prepare('SELECT last_insert_rowid() as id').get().id;
538      // scoring max_cost_per_call = 0.009; insert cost of 0.05 to trigger alert
539      db.prepare(
540        `INSERT INTO llm_usage (site_id, stage, provider, model, prompt_tokens, completion_tokens, total_tokens, estimated_cost)
541         VALUES (?, 'scoring', 'openrouter', 'openai/gpt-4o-mini', 10000, 5000, 15000, 0.05)`
542      ).run(siteId);
543      db.close();
544  
545      const alerts = checkBudgetVariance();
546      const costAlerts = alerts.filter(a => a.type === 'cost_variance' && a.stage === 'scoring');
547      assert.ok(costAlerts.length > 0, 'Should raise cost_variance alert');
548      assert.equal(costAlerts[0].stage, 'scoring');
549    });
550  
551    test('raises model_mismatch alert when wrong model used', () => {
552      const db = new Database(testDb);
553      db.prepare(
554        `INSERT INTO sites (domain, landing_page_url, keyword, status) VALUES ('test.com', 'https://test.com', 'test', 'found')`
555      ).run();
556      const siteId = db.prepare('SELECT last_insert_rowid() as id').get().id;
557      // scoring expected model: openai/gpt-4o-mini; use wrong model
558      db.prepare(
559        `INSERT INTO llm_usage (site_id, stage, provider, model, prompt_tokens, completion_tokens, total_tokens, estimated_cost)
560         VALUES (?, 'scoring', 'openrouter', 'anthropic/claude-opus-4', 100, 50, 150, 0.003)`
561      ).run(siteId);
562      db.close();
563  
564      const alerts = checkBudgetVariance();
565      const modelAlerts = alerts.filter(a => a.type === 'model_mismatch' && a.stage === 'scoring');
566      assert.ok(modelAlerts.length > 0, 'Should raise model_mismatch alert');
567      assert.equal(modelAlerts[0].actualModel, 'anthropic/claude-opus-4');
568      assert.equal(modelAlerts[0].expectedModel, 'openai/gpt-4o-mini');
569    });
570  
571    test('ignores stages not in llm_cost_budgets', () => {
572      const db = new Database(testDb);
573      db.prepare(
574        `INSERT INTO sites (domain, landing_page_url, keyword, status) VALUES ('test.com', 'https://test.com', 'test', 'found')`
575      ).run();
576      const siteId = db.prepare('SELECT last_insert_rowid() as id').get().id;
577      // 'other' is in the CHECK constraint but not in llm_cost_budgets
578      db.prepare(
579        `INSERT INTO llm_usage (site_id, stage, provider, model, prompt_tokens, completion_tokens, total_tokens, estimated_cost)
580         VALUES (?, 'other', 'openrouter', 'openai/gpt-4o-mini', 100, 50, 150, 0.999)`
581      ).run(siteId);
582      db.close();
583  
584      const alerts = checkBudgetVariance();
585      const otherAlerts = alerts.filter(a => a.stage === 'other');
586      assert.equal(otherAlerts.length, 0, 'No alert for stage not in llm_cost_budgets');
587    });
588  });
589  
590  // ─── Helper: insert a usage row with explicit created_at ────────────────────
591  function insertUsageWithTimestamp(db, { stage, model, cost, createdAt }) {
592    db.prepare(
593      `INSERT INTO llm_usage
594         (stage, provider, model, prompt_tokens, completion_tokens, total_tokens, estimated_cost, created_at)
595       VALUES (?, 'openrouter', ?, 100, 50, 150, ?, ?)`
596    ).run(stage, model, cost, createdAt);
597  }
598  
599  // ─── getDailySpend ───────────────────────────────────────────────────────────
600  
601  describe('getDailySpend', () => {
602    before(() => {
603      initSchema();
604    });
605    beforeEach(clearTables);
606    after(() => {
607      if (existsSync(testDb)) unlinkSync(testDb);
608    });
609  
610    test('returns 0 when no usage today', () => {
611      const spend = getDailySpend();
612      assert.equal(spend, 0);
613    });
614  
615    test('returns correct sum for usage rows with today UTC date', () => {
616      const db = new Database(testDb);
617      // Use SQLite date('now') which equals today in UTC
618      insertUsageWithTimestamp(db, {
619        stage: 'scoring',
620        model: 'openai/gpt-4o-mini',
621        cost: 0.5,
622        createdAt: new Date().toISOString().replace('T', ' ').substring(0, 19),
623      });
624      insertUsageWithTimestamp(db, {
625        stage: 'proposals',
626        model: 'claude-3-5-sonnet-20241022',
627        cost: 0.25,
628        createdAt: new Date().toISOString().replace('T', ' ').substring(0, 19),
629      });
630      db.close();
631  
632      const spend = getDailySpend();
633      // 0.5 + 0.25 = 0.75
634      assert.ok(Math.abs(spend - 0.75) < 0.001, `Expected ~0.75 but got ${spend}`);
635    });
636  
637    test('excludes usage from a previous day', () => {
638      const db = new Database(testDb);
639      // Yesterday's row
640      insertUsageWithTimestamp(db, {
641        stage: 'scoring',
642        model: 'openai/gpt-4o-mini',
643        cost: 99.0,
644        createdAt: '2000-01-01 12:00:00',
645      });
646      // Today's row
647      insertUsageWithTimestamp(db, {
648        stage: 'scoring',
649        model: 'openai/gpt-4o-mini',
650        cost: 0.1,
651        createdAt: new Date().toISOString().replace('T', ' ').substring(0, 19),
652      });
653      db.close();
654  
655      const spend = getDailySpend();
656      assert.ok(Math.abs(spend - 0.1) < 0.001, `Expected ~0.10 but got ${spend}`);
657    });
658  
659    test('returns a number (not string)', () => {
660      const spend = getDailySpend();
661      assert.equal(typeof spend, 'number');
662    });
663  });
664  
665  // ─── getHourlySpend ──────────────────────────────────────────────────────────
666  
667  describe('getHourlySpend', () => {
668    before(() => {
669      initSchema();
670    });
671    beforeEach(clearTables);
672    after(() => {
673      if (existsSync(testDb)) unlinkSync(testDb);
674    });
675  
676    test('returns 0 when no usage in last hour', () => {
677      const spend = getHourlySpend();
678      assert.equal(spend, 0);
679    });
680  
681    test('returns correct sum for usage in the last hour', () => {
682      const db = new Database(testDb);
683      const nowIso = new Date().toISOString().replace('T', ' ').substring(0, 19);
684      insertUsageWithTimestamp(db, {
685        stage: 'scoring',
686        model: 'openai/gpt-4o-mini',
687        cost: 0.3,
688        createdAt: nowIso,
689      });
690      insertUsageWithTimestamp(db, {
691        stage: 'enrichment',
692        model: 'openai/gpt-4o-mini',
693        cost: 0.2,
694        createdAt: nowIso,
695      });
696      db.close();
697  
698      const spend = getHourlySpend();
699      assert.ok(Math.abs(spend - 0.5) < 0.001, `Expected ~0.50 but got ${spend}`);
700    });
701  
702    test('excludes usage older than 1 hour', () => {
703      const db = new Database(testDb);
704      // Row created 2 hours ago (well outside the window)
705      const twoHoursAgo = new Date(Date.now() - 2 * 60 * 60 * 1000)
706        .toISOString()
707        .replace('T', ' ')
708        .substring(0, 19);
709      insertUsageWithTimestamp(db, {
710        stage: 'scoring',
711        model: 'openai/gpt-4o-mini',
712        cost: 50.0,
713        createdAt: twoHoursAgo,
714      });
715      // Recent row
716      const nowIso = new Date().toISOString().replace('T', ' ').substring(0, 19);
717      insertUsageWithTimestamp(db, {
718        stage: 'scoring',
719        model: 'openai/gpt-4o-mini',
720        cost: 0.05,
721        createdAt: nowIso,
722      });
723      db.close();
724  
725      const spend = getHourlySpend();
726      assert.ok(Math.abs(spend - 0.05) < 0.001, `Expected ~0.05 but got ${spend}`);
727    });
728  
729    test('returns a number (not string)', () => {
730      const spend = getHourlySpend();
731      assert.equal(typeof spend, 'number');
732    });
733  });