@@ -399,3 +399,178 @@ func TestSQLiteReaderGroupingRange_UsesAbsoluteTimestampExtremaAcrossOffsets(t *
399399 t .Fatalf ("expected range end %s, got %s" , expectedEnd , end )
400400 }
401401}
402+
403+ func TestSQLiteReaderGetUsageLog_OrdersMixedTimestampFormatsByAbsoluteTime (t * testing.T ) {
404+ db , err := sql .Open ("sqlite" , ":memory:" )
405+ if err != nil {
406+ t .Fatalf ("failed to open sqlite database: %v" , err )
407+ }
408+ defer db .Close ()
409+
410+ if _ , err := NewSQLiteStore (db , 0 ); err != nil {
411+ t .Fatalf ("failed to create sqlite store: %v" , err )
412+ }
413+
414+ ctx := context .Background ()
415+ _ , err = db .ExecContext (ctx , `
416+ INSERT INTO usage (
417+ id, request_id, provider_id, timestamp, model, provider, endpoint,
418+ input_tokens, output_tokens, total_tokens,
419+ input_cost, output_cost, total_cost, costs_calculation_caveat
420+ ) VALUES
421+ (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),
422+ (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),
423+ (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)` ,
424+ "latest-negative-offset" ,
425+ "req-latest" ,
426+ "provider-latest" ,
427+ "2026-03-29 23:30:00-02:00" ,
428+ "gpt-5" ,
429+ "openai" ,
430+ "/v1/chat/completions" ,
431+ 0 ,
432+ 30 ,
433+ 30 ,
434+ 0.0 ,
435+ 0.0 ,
436+ 0.0 ,
437+ "" ,
438+ "middle-zulu" ,
439+ "req-middle" ,
440+ "provider-middle" ,
441+ "2026-03-29T23:00:00Z" ,
442+ "gpt-5" ,
443+ "openai" ,
444+ "/v1/chat/completions" ,
445+ 0 ,
446+ 20 ,
447+ 20 ,
448+ 0.0 ,
449+ 0.0 ,
450+ 0.0 ,
451+ "" ,
452+ "earliest-positive-offset" ,
453+ "req-earliest" ,
454+ "provider-earliest" ,
455+ "2026-03-29 00:30:00+02:00" ,
456+ "gpt-5" ,
457+ "openai" ,
458+ "/v1/chat/completions" ,
459+ 0 ,
460+ 10 ,
461+ 10 ,
462+ 0.0 ,
463+ 0.0 ,
464+ 0.0 ,
465+ "" ,
466+ )
467+ if err != nil {
468+ t .Fatalf ("failed to seed mixed-format usage entries: %v" , err )
469+ }
470+
471+ reader , err := NewSQLiteReader (db )
472+ if err != nil {
473+ t .Fatalf ("failed to create sqlite reader: %v" , err )
474+ }
475+
476+ log , err := reader .GetUsageLog (ctx , UsageLogParams {
477+ Limit : 2 ,
478+ Offset : 0 ,
479+ })
480+ if err != nil {
481+ t .Fatalf ("GetUsageLog returned error: %v" , err )
482+ }
483+
484+ if len (log .Entries ) != 2 {
485+ t .Fatalf ("expected 2 log entries, got %d" , len (log .Entries ))
486+ }
487+ if log .Entries [0 ].ID != "latest-negative-offset" {
488+ t .Fatalf ("expected latest entry first, got %s" , log .Entries [0 ].ID )
489+ }
490+ if log .Entries [1 ].ID != "middle-zulu" {
491+ t .Fatalf ("expected middle entry second, got %s" , log .Entries [1 ].ID )
492+ }
493+ }
494+
495+ func TestSQLiteStoreCleanup_KeepsNewerLegacyOffsetRows (t * testing.T ) {
496+ db , err := sql .Open ("sqlite" , ":memory:" )
497+ if err != nil {
498+ t .Fatalf ("failed to open sqlite database: %v" , err )
499+ }
500+ defer db .Close ()
501+
502+ store , err := NewSQLiteStore (db , 1 )
503+ if err != nil {
504+ t .Fatalf ("failed to create sqlite store: %v" , err )
505+ }
506+ defer store .Close ()
507+
508+ cutoff := time .Now ().AddDate (0 , 0 , - 1 ).UTC ().Truncate (time .Second )
509+ keepTimestamp := cutoff .Add (90 * time .Minute ).In (time .FixedZone ("minus2" , - 2 * 60 * 60 )).Format ("2006-01-02 15:04:05-07:00" )
510+ deleteTimestamp := cutoff .Add (- 90 * time .Minute ).In (time .FixedZone ("plus2" , 2 * 60 * 60 )).Format ("2006-01-02 15:04:05-07:00" )
511+
512+ _ , err = db .Exec (`
513+ INSERT INTO usage (
514+ id, request_id, provider_id, timestamp, model, provider, endpoint,
515+ input_tokens, output_tokens, total_tokens,
516+ input_cost, output_cost, total_cost, costs_calculation_caveat
517+ ) VALUES
518+ (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?),
519+ (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)` ,
520+ "keep-newer-legacy" ,
521+ "req-keep" ,
522+ "provider-keep" ,
523+ keepTimestamp ,
524+ "gpt-5" ,
525+ "openai" ,
526+ "/v1/chat/completions" ,
527+ 0 ,
528+ 10 ,
529+ 10 ,
530+ 0.0 ,
531+ 0.0 ,
532+ 0.0 ,
533+ "" ,
534+ "delete-older-legacy" ,
535+ "req-delete" ,
536+ "provider-delete" ,
537+ deleteTimestamp ,
538+ "gpt-5" ,
539+ "openai" ,
540+ "/v1/chat/completions" ,
541+ 0 ,
542+ 20 ,
543+ 20 ,
544+ 0.0 ,
545+ 0.0 ,
546+ 0.0 ,
547+ "" ,
548+ )
549+ if err != nil {
550+ t .Fatalf ("failed to seed cleanup rows: %v" , err )
551+ }
552+
553+ store .cleanup ()
554+
555+ var remainingIDs []string
556+ rows , err := db .Query (`SELECT id FROM usage ORDER BY id` )
557+ if err != nil {
558+ t .Fatalf ("failed to query remaining rows: %v" , err )
559+ }
560+ defer rows .Close ()
561+
562+ for rows .Next () {
563+ var id string
564+ if err := rows .Scan (& id ); err != nil {
565+ t .Fatalf ("failed to scan remaining id: %v" , err )
566+ }
567+ remainingIDs = append (remainingIDs , id )
568+ }
569+ if err := rows .Err (); err != nil {
570+ t .Fatalf ("failed to iterate remaining rows: %v" , err )
571+ }
572+
573+ if len (remainingIDs ) != 1 || remainingIDs [0 ] != "keep-newer-legacy" {
574+ t .Fatalf ("expected only the newer legacy row to remain, got %v" , remainingIDs )
575+ }
576+ }
0 commit comments