1  -- Migration 055: Create OpenRouter credit log table for historical tracking
 2  -- Tracks credit balance, usage, and rate limits over time
 3  
 4  CREATE TABLE IF NOT EXISTS openrouter_credit_log (
 5    id INTEGER PRIMARY KEY AUTOINCREMENT,
 6    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
 7  
 8    -- Key info
 9    label TEXT,                    -- API key label/name
10  
11    -- Credit balance
12    usage REAL NOT NULL,           -- Total spend in USD
13    credit_limit REAL,             -- Credit limit in USD (null if unlimited)
14    remaining REAL,                -- Calculated remaining credits (limit - usage)
15    is_free_tier INTEGER DEFAULT 0, -- 1 if free tier, 0 if paid
16  
17    -- Rate limiting
18    rate_limit TEXT,               -- JSON string of rate limit info
19  
20    -- Raw response for debugging
21    raw_response TEXT,             -- Full JSON response from API
22  
23    CONSTRAINT valid_boolean CHECK (is_free_tier IN (0, 1))
24  );
25  
26  -- Index for time-based queries
27  CREATE INDEX IF NOT EXISTS idx_openrouter_credit_log_timestamp
28    ON openrouter_credit_log(timestamp);
29  
30  -- Index for finding low balance alerts
31  CREATE INDEX IF NOT EXISTS idx_openrouter_credit_log_remaining
32    ON openrouter_credit_log(remaining);