Function fetchModeRankingsData

apps/web/src/lib/model-stats/sync-internal-data.ts:65–123

* Fetch mode rankings data from PostHog * * Queries PostHog for model usage across all modes in the last 7 days * and returns structured data ready for database updates

()

 * and returns structured data ready for database updates
 */
async function fetchModeRankingsData(): Promise<ModelChartDataUpdates> {
  console.log('[sync-internal-data] Fetching model usage data from PostHog...');

  // Query PostHog using the materialized view
  // This view is maintained in PostHog and provides the same data as the complex query
  // Explicitly select columns to avoid confusion from column order changes
  const response = await posthogQuery(
    'sync-model-stats-internal',
    `
select mode, rank, model, tokens
from models_by_modes_last_7_days
limit 100000
`
  );

  if (response.status === 'error') {
    console.error('[sync-internal-data] PostHog query failed:', response.error);
    throw new Error(`PostHog query failed: ${JSON.stringify(response.error)}`);
  }

  const results = (response.body.results ?? []) as ModeRankingRawResult[];
  console.log(`[sync-internal-data] Received ${results.length} results from PostHog`);

  console.log('[sync-internal-data] Sample results:', results.slice(0, 10));

  // Group results by model
  const modelRankings: ModelModeRankings = {};
  const modelTokens: ModelTokenCounts = {};
  const modesSeen = new Set<string>();
  for (const [mode, rank, model, tokens] of results) {
    modesSeen.add(mode);
    if (!modelRankings[model]) {
      modelRankings[model] = {};
    }
    modelRankings[model][mode as keyof ModeRankings] = rank;

    // Accumulate total tokens across all modes
    modelTokens[model] = (modelTokens[model] || 0) + tokens;
  }

  console.log('[sync-internal-data] Modes found in data:', Array.from(modesSeen));

  console.log(
    `[sync-internal-data] Processed rankings for ${Object.keys(modelRankings).length} unique models`
  );

  // Build the chart data updates object
  const lastUpdated = new Date().toISOString();
  const chartDataUpdates: ModelChartDataUpdates = {};

  for (const [normalizedModelName, rankings] of Object.entries(modelRankings)) {
    chartDataUpdates[normalizedModelName] = {
      modeRankings: { ...rankings, lastUpdated },
      last7DaysTokens: modelTokens[normalizedModelName] || 0,
    };
  }

  return chartDataUpdates;