Appearance

Fix Double Counting Bug - Detailed Implementation Plan ​

Progress Summary ​

  • ✅ Phase 1: Remove Dead Code - COMPLETED 2024-08-29
  • ✅ Phase 2: Fix Resume Check Order - COMPLETED 2024-08-29
  • ✅ Phase 3: Create AnalyticsRecorder Service - COMPLETED
  • ✅ Phase 4: Remove Tracking from generateLLMResponse - COMPLETED
  • ✅ Phase 5: Update All Routes - COMPLETED
  • ⏳ Phase 6: Clean Database Functions - PENDING
  • ⏳ Phase 7: Update TypeScript Types - PENDING
  • ⏳ Phase 8: Final Testing - PENDING

Overview ​

This plan addresses the double counting bug where each job analysis is counted twice in the database, along with related architectural improvements.

Key Issues to Fix ​

  1. Double counting bug: Both Stage 1 and Stage 2 of job analysis use 'job_analysis' operation type
  2. Inefficient resume check: Happens between stages instead of before Stage 1
  3. Dead code: Unused compatibility endpoint and resume-analyzer.ts (~500 lines)
  4. Database functions with business logic: Duplicate CASE statements in multiple functions
  5. Tight coupling: update_user_usage calls update_daily_stats directly

Phase 1: Remove Dead Code ✅ COMPLETED ​

Files to Delete ​

  1. /apps/web/src/app/api/jobs/[id]/compatibility/route.ts - Unused API endpoint ✅
  2. /apps/web/src/lib/resume-analyzer.ts - Only used by dead endpoint ✅

Verification Steps ​

bash
# Check for any remaining references
grep -r "compatibility/route" apps/web/src
grep -r "resume-analyzer" apps/web/src
grep -r "performFullResumeAnalysis" apps/web/src

Testing After Phase 1 ​

bash
npm run build --workspace=apps/web  ✅ Passed
npm run lint --workspace=apps/web   ✅ No warnings or errors
npm run type-check --workspace=apps/web  ✅ Passed

Execution Results (2024-08-29) ​

  • Files Deleted:
    • /apps/web/src/app/api/jobs/[id]/compatibility/route.ts (303 lines)
    • /apps/web/src/lib/resume-analyzer.ts (459 lines)
    • Empty directory /apps/web/src/app/api/jobs/[id]/compatibility/
  • Total Lines Removed: ~762 lines
  • Verification: All references checked, only found in deleted files and plan doc
  • Build Status: ✅ Success - compiled in 5.0s
  • Lint Status: ✅ No ESLint warnings or errors
  • TypeScript: ✅ No type errors

Phase 2: Fix Resume Check Order in /api/jobs/analyze ✅ COMPLETED ​

Current Code (Inefficient) ​

typescript
// apps/web/src/app/api/jobs/analyze/route.ts (lines ~350-430)

// STAGE 1: Generate structured job data
const systemPrompt1 = getJobStructuringPrompt();
// ... prepare prompts ...

llmResponse1 = await generateLLMResponse(
  {
    systemPrompt: systemPrompt1,
    userPrompt: userPrompt1,
    temperature: 0.1,
    maxTokens: GEMINI_MAX_OUTPUT_TOKENS,
  },
  model,
  userId,
  supabase,
  USAGE_OPERATION_TYPES.JOB_ANALYSIS  // ← First count
);

// Parse Stage 1 response
structuredJob = parseLLMJsonResponse(llmResponse1);

// STAGE 2: Generate resume compatibility analysis
if (resumeContent) {  // ← Resume check happens AFTER Stage 1
  const systemPrompt2 = getResumeCompatibilityPrompt();
  // ... prepare prompts ...
  
  llmResponse2 = await generateLLMResponse(
    {
      systemPrompt: systemPrompt2,
      userPrompt: userPrompt2,
      temperature: 0.1,
      maxTokens: GEMINI_MAX_OUTPUT_TOKENS,
    },
    model,
    userId,
    supabase,
    USAGE_OPERATION_TYPES.JOB_ANALYSIS  // ← Second count (BUG!)
  );
  
  compatibilityAnalysis = parseLLMJsonResponse(llmResponse2);
}

New Code (Efficient & Fixed) ​

typescript
// apps/web/src/app/api/jobs/analyze/route.ts

// Check for resume FIRST
const hasResume = !!resumeContent;

// STAGE 1: Generate structured job data
const systemPrompt1 = getJobStructuringPrompt(hasResume); // ← Pass flag to adjust prompt
// ... prepare prompts ...

llmResponse1 = await generateLLMResponse(
  {
    systemPrompt: systemPrompt1,
    userPrompt: userPrompt1,
    temperature: 0.1,
    maxTokens: GEMINI_MAX_OUTPUT_TOKENS,
  },
  model,
  userId,
  supabase,
  null  // ← No tracking here
);

// Parse Stage 1 response
structuredJob = parseLLMJsonResponse(llmResponse1);

// STAGE 2: Generate resume compatibility analysis (only if resume exists)
if (hasResume) {
  const systemPrompt2 = getResumeCompatibilityPrompt();
  // ... prepare prompts ...
  
  llmResponse2 = await generateLLMResponse(
    {
      systemPrompt: systemPrompt2,
      userPrompt: userPrompt2,
      temperature: 0.1,
      maxTokens: GEMINI_MAX_OUTPUT_TOKENS,
    },
    model,
    userId,
    supabase,
    null  // ← No tracking here either
  );
  
  compatibilityAnalysis = parseLLMJsonResponse(llmResponse2);
}

// Track ONCE after both stages complete
const totalCost = llmResponse1.usage.estimatedCost + 
                  (llmResponse2?.usage?.estimatedCost || 0);

await analyticsRecorder.trackJobAnalysis(userId, totalCost, {
  hasResume,
  stage1Tokens: llmResponse1.usage.totalTokens,
  stage2Tokens: llmResponse2?.usage?.totalTokens || 0,
});

Testing After Phase 2 ​

bash
npm run lint --workspace=apps/web
npm run type-check --workspace=apps/web
# Test the API endpoint manually

Execution Results (2024-08-29) ​

  • Key Changes:

    • Moved resume check from line 208 to line 103 (right after auth)
    • IMPORTANT: Removed resume sanitization - resume now used as-is from database
    • Deleted duplicate Supabase client creation and resume fetching (lines 245-291)
    • Resume content stored as: const resumeContent = userProfile.resume_markdown; (NO sanitization)

  • Benefits:

    • Fail fast if no resume exists (skip expensive operations)
    • Resume markdown preserved exactly (quotes, apostrophes, all formatting intact)
    • Removed ~45 lines of duplicate code

  • Verification:

    • Build Status: ✅ Success
    • Lint Status: ✅ No ESLint warnings or errors
    • TypeScript: ✅ No type errors

  • Note: The plan originally didn't mention removing sanitization, but we discovered during implementation that sanitizing the resume was harmful (removed quotes, apostrophes, and HTML-like content from markdown). Per user directive, resume is now used exactly as stored in the database.

Phase 3: Create AnalyticsRecorder Service ​

New File: /apps/web/src/lib/analytics-recorder.ts ​

typescript
/**
 * AnalyticsRecorder - Centralized service for tracking operations and usage
 * 
 * This service decouples LLM API calls from usage tracking, providing
 * explicit control over what gets tracked and when.
 */

import { SupabaseClient } from '@supabase/supabase-js';
import { errorLog, infoLog } from './logger';

/**
 * Operation types that increment counts in the database
 */
export type TrackedOperation = 
  | 'job_analysis'           // Complete job analysis (both stages)
  | 'resume_customization'    // Resume customization for a job
  | 'cover_letter'           // Cover letter generation
  | 'interview_prep'         // Interview preparation
  | 'saved_job'              // Job saved (auto-tracked by DB trigger)
  | 'google_drive'           // Google Drive operations
  | 'google_docs';           // Google Docs operations

/**
 * Internal operation types for granular tracking (cost only, no count increment)
 * These leverage the ELSE clause in the database function
 */
export type InternalOperation = 
  | 'job_structuring'        // Stage 1 of job analysis
  | 'compatibility_check'    // Stage 2 of job analysis
  | 'llm_internal';          // Generic internal LLM operation

export type OperationType = TrackedOperation | InternalOperation;

export interface TrackingData {
  userId: string;
  cost: number;
  operation: OperationType;
  metadata?: Record<string, unknown>;
}

export interface OperationResult {
  success: boolean;
  cost: number;
  error?: string;
}

/**
 * Main AnalyticsRecorder class
 */
export class AnalyticsRecorder {
  constructor(private supabase: SupabaseClient) {}

  /**
   * Track a complete user operation with cost
   */
  async trackOperation(data: TrackingData): Promise<OperationResult> {
    const maxRetries = 3;
    let lastError: Error | unknown;

    for (let attempt = 1; attempt <= maxRetries; attempt++) {
      try {
        if (attempt > 1) {
          const delay = 100 * Math.pow(2, attempt - 1);
          await new Promise(resolve => setTimeout(resolve, delay));
        }

        const currentDate = new Date().toISOString().split('T')[0];

        // Call the database function
        const { error } = await this.supabase.rpc('update_user_usage', {
          p_user_id: data.userId,
          p_date: currentDate,
          p_cost: data.cost,
          p_operation_type: data.operation,
        });

        if (error) {
          lastError = error;
          errorLog('AnalyticsRecorder', 'Failed to track operation', { attempt, error });
          continue;
        }

        if (process.env.NODE_ENV === 'development') {
          infoLog('AnalyticsRecorder', 'Operation tracked successfully', {
            userId: data.userId,
            operation: data.operation,
            cost: data.cost,
            metadata: data.metadata,
          });
        }

        return { success: true, cost: data.cost };
      } catch (error) {
        lastError = error;
        errorLog('AnalyticsRecorder', 'Failed to track operation', { attempt, error });
      }
    }

    const errorMessage = `Failed to track operation after ${maxRetries} attempts`;
    errorLog('AnalyticsRecorder', errorMessage, lastError);
    
    return {
      success: false,
      cost: data.cost,
      error: errorMessage,
    };
  }

  /**
   * Track a complete job analysis (both stages)
   */
  async trackJobAnalysis(
    userId: string,
    totalCost: number,
    metadata?: Record<string, unknown>
  ): Promise<OperationResult> {
    return this.trackOperation({
      userId,
      cost: totalCost,
      operation: 'job_analysis',
      metadata,
    });
  }

  /**
   * Track internal operations (cost only, no count increment)
   */
  async trackInternalOperation(
    userId: string,
    cost: number,
    operation: InternalOperation,
    metadata?: Record<string, unknown>
  ): Promise<OperationResult> {
    return this.trackOperation({
      userId,
      cost,
      operation,
      metadata,
    });
  }

  // Additional convenience methods
  async trackResumeCustomization(userId: string, cost: number, metadata?: Record<string, unknown>): Promise<OperationResult> {
    return this.trackOperation({ userId, cost, operation: 'resume_customization', metadata });
  }

  async trackCoverLetter(userId: string, cost: number, metadata?: Record<string, unknown>): Promise<OperationResult> {
    return this.trackOperation({ userId, cost, operation: 'cover_letter', metadata });
  }

  async trackInterviewPrep(userId: string, cost: number, metadata?: Record<string, unknown>): Promise<OperationResult> {
    return this.trackOperation({ userId, cost, operation: 'interview_prep', metadata });
  }
}

/**
 * Factory function to create AnalyticsRecorder instance
 */
export function createAnalyticsRecorder(supabase: SupabaseClient): AnalyticsRecorder {
  return new AnalyticsRecorder(supabase);
}

Testing After Phase 3 ​

bash
npm run lint --workspace=apps/web
npm run type-check --workspace=apps/web

Phase 4: Remove Tracking from generateLLMResponse ​

Current Code (with automatic tracking) ​

typescript
// apps/web/src/lib/llm-providers.ts (lines ~252-371)

export async function generateLLMResponse(
  params: {
    systemPrompt: string;
    userPrompt: string;
    temperature?: number;
    maxTokens?: number;
  },
  model: LLMModel,
  userId: string,
  supabase: SupabaseClient,
  operationType?: string
): Promise<LLMResponse<string>> {
  // ... LLM call logic ...
  
  // Automatic tracking (REMOVE THIS)
  if (userId && supabase && operationType) {
    await logLLMUsage(
      supabase,
      userId,
      model,
      usage,
      operationType
    );
  }
  
  return response;
}

New Code (no automatic tracking) ​

typescript
// apps/web/src/lib/llm-providers.ts

export async function generateLLMResponse(
  params: {
    systemPrompt: string;
    userPrompt: string;
    temperature?: number;
    maxTokens?: number;
  },
  model: LLMModel,
  userId?: string,  // ← Now optional
  supabase?: SupabaseClient,  // ← Now optional
  operationType?: string  // ← Deprecated, will be removed
): Promise<LLMResponse<string>> {
  // ... LLM call logic ...
  
  // NO AUTOMATIC TRACKING - caller handles this
  
  // Return response with usage data
  return {
    data: completionText,
    usage: {
      promptTokens,
      completionTokens,
      totalTokens,
      estimatedCost,
    },
    model: model.modelId,
    provider: model.provider,
    timestamp: Date.now(),
  };
}

/**
 * @deprecated Use AnalyticsRecorder instead
 */
export async function logLLMUsage(
  supabase: SupabaseClient,
  userId: string,
  model: LLMModel,
  usage: LLMUsage,
  operation: string
): Promise<void> {
  // Mark as deprecated
  console.warn('logLLMUsage is deprecated. Use AnalyticsRecorder instead.');
  
  // Delegate to AnalyticsRecorder for backward compatibility
  const { createAnalyticsRecorder } = await import('./analytics-recorder');
  const recorder = createAnalyticsRecorder(supabase);
  
  await recorder.trackOperation({
    userId,
    cost: usage.estimatedCost,
    operation: operation as any,
    metadata: {
      model: model.modelId,
      tokens: usage.totalTokens,
    },
  });
}

Testing After Phase 4 ​

bash
npm run lint --workspace=apps/web
npm run type-check --workspace=apps/web

Phase 5: Update All Routes ​

5.1: Fix /api/jobs/analyze ​

Already shown in Phase 2 above.

5.2: Update /api/jobs/structure ​

Before ​

typescript
// apps/web/src/app/api/jobs/structure/route.ts
const llmResponse = await generateLLMResponse(
  { systemPrompt, userPrompt, temperature: 0.1, maxTokens: 16384 },
  model,
  userId,
  supabase,
  'job_analysis'  // ← This increments count
);

After ​

typescript
// apps/web/src/app/api/jobs/structure/route.ts
import { createAnalyticsRecorder } from '@/lib/analytics-recorder';

const llmResponse = await generateLLMResponse(
  { systemPrompt, userPrompt, temperature: 0.1, maxTokens: 16384 },
  model
  // No userId, supabase, or operation type
);

// Track as internal operation (cost only, no count)
const recorder = createAnalyticsRecorder(supabase);
await recorder.trackInternalOperation(
  userId,
  llmResponse.usage.estimatedCost,
  'job_structuring',
  { source: 'structure_endpoint' }
);

5.3: Update /api/jobs/[id]/resume/customize ​

Before ​

typescript
const response = await generateLLMResponse(
  { systemPrompt, userPrompt, maxTokens: GEMINI_MAX_OUTPUT_TOKENS },
  model,
  userId,
  supabase,
  USAGE_OPERATION_TYPES.RESUME_CUSTOMIZATION
);

After ​

typescript
import { createAnalyticsRecorder } from '@/lib/analytics-recorder';

const response = await generateLLMResponse(
  { systemPrompt, userPrompt, maxTokens: GEMINI_MAX_OUTPUT_TOKENS },
  model
);

const recorder = createAnalyticsRecorder(supabase);
await recorder.trackResumeCustomization(
  userId,
  response.usage.estimatedCost,
  { jobId: resolvedParams.id }
);

5.4: Update /api/jobs/[id]/cover-letter ​

Before ​

typescript
const response = await generateLLMResponse(
  { systemPrompt, userPrompt, maxTokens: GEMINI_MAX_OUTPUT_TOKENS },
  model,
  userId,
  supabase,
  USAGE_OPERATION_TYPES.COVER_LETTER
);

After ​

typescript
import { createAnalyticsRecorder } from '@/lib/analytics-recorder';

const response = await generateLLMResponse(
  { systemPrompt, userPrompt, maxTokens: GEMINI_MAX_OUTPUT_TOKENS },
  model
);

const recorder = createAnalyticsRecorder(supabase);
await recorder.trackCoverLetter(
  userId,
  response.usage.estimatedCost,
  { jobId: resolvedParams.id }
);

5.5: Update /api/jobs/[id]/interview-prep ​

Before ​

typescript
const response = await generateLLMResponse(
  { systemPrompt, userPrompt, maxTokens: GEMINI_MAX_OUTPUT_TOKENS },
  model,
  userId,
  supabase,
  USAGE_OPERATION_TYPES.INTERVIEW_PREP
);

After ​

typescript
import { createAnalyticsRecorder } from '@/lib/analytics-recorder';

const response = await generateLLMResponse(
  { systemPrompt, userPrompt, maxTokens: GEMINI_MAX_OUTPUT_TOKENS },
  model
);

const recorder = createAnalyticsRecorder(supabase);
await recorder.trackInterviewPrep(
  userId,
  response.usage.estimatedCost,
  { jobId: resolvedParams.id }
);

Testing After Phase 5 ​

bash
npm run build --workspace=apps/web
npm run lint --workspace=apps/web
npm run type-check --workspace=apps/web
# Manual API testing for each endpoint

Phase 6: Clean Database Functions (Option B - Clean Architecture) ​

6.1: Create New Simplified Functions ​

sql
-- Migration: Create clean tracking functions

-- Simple cost tracking function (no business logic)
CREATE OR REPLACE FUNCTION update_usage_cost(
  p_user_id UUID,
  p_date DATE,
  p_cost NUMERIC,
  p_column_name TEXT  -- Dynamic column name
)
RETURNS VOID AS $$
BEGIN
  -- Dynamically update the specified cost column
  EXECUTE format(
    'UPDATE user_usage_summary 
     SET %I = %I + $1, 
         total_cost_usd = total_cost_usd + $1,
         updated_at = NOW()
     WHERE user_id = $2 
       AND $3 >= period_start 
       AND $3 <= period_end',
    p_column_name, p_column_name
  ) USING p_cost, p_user_id, p_date;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Simple count tracking function (no business logic)
CREATE OR REPLACE FUNCTION update_usage_count(
  p_user_id UUID,
  p_date DATE,
  p_column_name TEXT  -- Dynamic column name
)
RETURNS VOID AS $$
BEGIN
  -- Dynamically update the specified count column
  EXECUTE format(
    'UPDATE user_usage_summary 
     SET %I = %I + 1,
         updated_at = NOW()
     WHERE user_id = $2 
       AND $3 >= period_start 
       AND $3 <= period_end',
    p_column_name
  ) USING p_user_id, p_date;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Simple daily stats function (no business logic)
CREATE OR REPLACE FUNCTION update_daily_count(
  p_user_id UUID,
  p_date DATE,
  p_column_name TEXT
)
RETURNS VOID AS $$
BEGIN
  -- Ensure row exists
  INSERT INTO user_daily_stats (user_id, date, updated_at)
  VALUES (p_user_id, p_date, NOW())
  ON CONFLICT (user_id, date) DO NOTHING;
  
  -- Update the count
  EXECUTE format(
    'UPDATE user_daily_stats 
     SET %I = %I + 1,
         updated_at = NOW()
     WHERE user_id = $1 AND date = $2',
    p_column_name, p_column_name
  ) USING p_user_id, p_date;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

6.2: Update AnalyticsRecorder to Use New Functions ​

typescript
// apps/web/src/lib/analytics-recorder.ts

async trackOperation(data: TrackingData): Promise<OperationResult> {
  // ... retry logic ...
  
  const currentDate = new Date().toISOString().split('T')[0];
  
  // Map operation types to database columns
  const columnMapping: Record<TrackedOperation, { costColumn: string; countColumn: string }> = {
    'job_analysis': { 
      costColumn: 'job_analysis_cost_usd', 
      countColumn: 'job_analysis_count' 
    },
    'resume_customization': { 
      costColumn: 'resume_customization_cost_usd', 
      countColumn: 'resume_customization_count' 
    },
    'cover_letter': { 
      costColumn: 'cover_letter_cost_usd', 
      countColumn: 'cover_letter_count' 
    },
    'interview_prep': { 
      costColumn: 'interview_prep_cost_usd', 
      countColumn: 'interview_prep_count' 
    },
    'saved_job': { 
      costColumn: null, // No cost for saved jobs
      countColumn: 'saved_jobs_count' 
    },
    'google_drive': { 
      costColumn: 'other_cost_usd', 
      countColumn: null // No count tracking
    },
    'google_docs': { 
      costColumn: 'other_cost_usd', 
      countColumn: null // No count tracking
    },
  };
  
  // Internal operations only update cost
  const isTrackedOperation = data.operation in columnMapping;
  
  if (isTrackedOperation) {
    const mapping = columnMapping[data.operation as TrackedOperation];
    
    // Update cost if applicable
    if (mapping.costColumn && data.cost > 0) {
      await this.supabase.rpc('update_usage_cost', {
        p_user_id: data.userId,
        p_date: currentDate,
        p_cost: data.cost,
        p_column_name: mapping.costColumn,
      });
    }
    
    // Update count if applicable
    if (mapping.countColumn) {
      await this.supabase.rpc('update_usage_count', {
        p_user_id: data.userId,
        p_date: currentDate,
        p_column_name: mapping.countColumn,
      });
      
      // Also update daily stats
      await this.supabase.rpc('update_daily_count', {
        p_user_id: data.userId,
        p_date: currentDate,
        p_column_name: mapping.countColumn,
      });
    }
  } else {
    // Internal operations - cost only in other_cost_usd
    if (data.cost > 0) {
      await this.supabase.rpc('update_usage_cost', {
        p_user_id: data.userId,
        p_date: currentDate,
        p_cost: data.cost,
        p_column_name: 'other_cost_usd',
      });
    }
  }
  
  return { success: true, cost: data.cost };
}

6.3: Migration to Remove resume_analysis_count ​

sql
-- Migration: Remove unused resume_analysis columns

-- Remove from user_usage_summary
ALTER TABLE user_usage_summary 
DROP COLUMN IF EXISTS resume_analysis_count,
DROP COLUMN IF EXISTS resume_analysis_cost_usd;

-- Remove from user_daily_stats
ALTER TABLE user_daily_stats
DROP COLUMN IF EXISTS resume_analysis_count;

-- Drop old functions with business logic
DROP FUNCTION IF EXISTS update_user_usage(UUID, DATE, NUMERIC, TEXT);
DROP FUNCTION IF EXISTS update_daily_stats(UUID, DATE, TEXT);

Testing After Phase 6 ​

bash
# Run migrations
npx supabase db push

# Test the application
npm run build --workspace=apps/web
npm run lint --workspace=apps/web
npm run type-check --workspace=apps/web

Phase 7: Update TypeScript Types ​

Remove resume_analysis fields from types ​

typescript
// apps/web/src/types/usage.ts (or wherever these are defined)

// Before
export interface UsageSummary {
  job_analysis_count: number;
  job_analysis_cost_usd: number;
  resume_analysis_count: number;  // ← Remove
  resume_analysis_cost_usd: number;  // ← Remove
  resume_customization_count: number;
  // ...
}

// After
export interface UsageSummary {
  job_analysis_count: number;
  job_analysis_cost_usd: number;
  resume_customization_count: number;
  resume_customization_cost_usd: number;
  cover_letter_count: number;
  cover_letter_cost_usd: number;
  interview_prep_count: number;
  interview_prep_cost_usd: number;
  saved_jobs_count: number;
  other_cost_usd: number;
  total_cost_usd: number;
  // ...
}

Update API responses ​

typescript
// apps/web/src/app/api/usage/stats/route.ts

// Remove references to resume_analysis_count and resume_analysis_cost_usd
// Update the response structure accordingly

Testing After Phase 7 ​

bash
npm run build --workspace=apps/web
npm run lint --workspace=apps/web
npm run type-check --workspace=apps/web

Final Testing Checklist ​

Functional Tests ​

  • [ ] Job analysis counts as 1, not 2
  • [ ] Chrome extension shows correct daily counts
  • [ ] Dashboard shows correct usage percentages
  • [ ] Costs are properly tracked for all operations
  • [ ] Internal operations don't increment counts

Code Quality ​

  • [ ] All lint checks pass
  • [ ] All TypeScript checks pass
  • [ ] Build completes successfully
  • [ ] No console errors in browser

Database Verification ​

sql
-- Check that counts are correct
SELECT 
  user_id,
  job_analysis_count,
  resume_customization_count,
  cover_letter_count,
  interview_prep_count,
  saved_jobs_count,
  total_cost_usd
FROM user_usage_summary
WHERE user_id = '[test-user-id]'
ORDER BY created_at DESC
LIMIT 1;

-- Verify resume_analysis columns are gone
SELECT column_name 
FROM information_schema.columns 
WHERE table_name = 'user_usage_summary' 
  AND column_name LIKE '%resume_analysis%';
-- Should return 0 rows

Rollback Plan ​

If issues arise:

  1. Revert code changes: Git revert the commits
  2. Restore database functions: Keep backup of original functions
  3. Re-add columns if needed: Have migration ready to re-add resume_analysis columns

Success Metrics ​

  • Job analysis count matches actual number of analyses performed
  • No increase in error rates
  • Performance improvement from checking resume first
  • Cleaner codebase with ~500 fewer lines
  • Clear separation of concerns between LLM calls and tracking

Built with VitePress

Copyright © RESUMIXX. All rights reserved.