javascript-export-column-swap

Answer

Root cause. The export used SELECT * FROM token_usage and mapped raw row positions into the CSV. The table's physical column order is (id, workspace_id, utc_date, model, api_key_name, type, price, amount), so SELECT * produces r[0]=id, r[1]=workspace_id, r[2]=utc_date, r[3]=model, r[4]=api_key_name, r[5]=type, r[6]=price, r[7]=amount. Mapping r[6], r[7] under the amount, price header put price in the amount column and amount in the price column — every "amount" cell held a per-token price (< 1) and every "price" cell held a token count (> 1).

Fix (two parts): (1) explicit column list in the SELECT so the projection order is (utc_date, model, api_key_name, type, amount, price) — r[4]=amount, r[5]=price regardless of physical schema order; (2) extract a pure exportRowToCsv mapper keyed to that projection and derive the header from the same column list so header and values can never drift.

// exportTokenUsage.js
export const TOKEN_USAGE_COLUMNS = [
  'utc_date', 'model', 'api_key_name', 'type', 'amount', 'price',
];
export const CSV_HEADER = TOKEN_USAGE_COLUMNS.join(',');
// r[0]=utc_date r[1]=model r[2]=api_key_name r[3]=type r[4]=amount r[5]=price
export const SELECT_TOKEN_USAGE =
  `SELECT ${TOKEN_USAGE_COLUMNS.join(', ')} FROM token_usage`;

function escapeCsv(value) {
  const s = String(value);
  return /[",\n\r]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
}

// Pure mapper — unit-testable, no I/O.
export function exportRowToCsv(row) {
  return row.map(escapeCsv).join(',');
}

export async function exportAllRawCsv(db) {
  const rows = await db.raw(SELECT_TOKEN_USAGE); // never SELECT *
  return [CSV_HEADER, ...rows.map(exportRowToCsv)].join('\n');
}

// exportTokenUsage.test.js (vitest)
it('asserts amount>1 and price<1 for realistic token rows (fails if swapped)', () => {
  const rows = [
    ['2026-01-02', 'gpt-4o', 'prod-key', 'input',  123456, 0.0000025],
    ['2026-01-02', 'gpt-4o', 'prod-key', 'output', 98765, 0.00001],
    ['2026-01-03', 'claude-3.5-sonnet', 'staging', 'input', 50000, 0.000003],
  ];
  for (const line of rows.map(exportRowToCsv)) {
    const [utcDate, model, key, type, amount, price] = line.split(',');
    expect(utcDate).toMatch(/^\d{4}-\d{2}-\d{2}$/);
    expect(model).not.toBe('');
    expect(key).not.toBe('');
    expect(type).toBeTruthy();
    expect(Number(amount)).toBeGreaterThan(1); // token counts are large
    expect(Number(price)).toBeLessThan(1);     // per-token prices are tiny
  }
});

Additional tests cover index/header parity, CSV quoting/escaping, zero values, empty result sets, and an integration check asserting the query is the explicit 6-column SELECT (and never SELECT *).