​

Chat Log Embeddings Backfill Documentation

​

Overview

​

Purpose

• All historical chat logs have embeddings
• Users can immediately benefit from the memory search functionality
• The AI can reference past conversations for better context

​

Technical Details

​

Endpoint

• URL: /api/backfill-embeddings
• Methods:
  • POST - Run the backfill process
  • GET - Check current status and statistics

​

Security

• Bearer authentication (required): Authorization: Bearer $INTERNAL_API_TOKEN on both GET and POST. Same env var and shared guard (lib/internal-auth.ts) as the other internal Lambda-only endpoints. Missing/wrong token → 401.
• Environment Restriction: POST additionally 403s when NODE_ENV === 'production' as defense-in-depth on top of the Bearer gate.
• Database access: Uses Supabase service role internally, so RLS is bypassed — this is why the Bearer gate is mandatory.

​

Configuration

• Batch Size: 50 records per batch
• Rate Limiting: 250ms delay between batches
• Max Retries: 3 attempts per embedding generation
• Max Duration: 5 minutes (300 seconds)
• Embedding Model: OpenAI’s text-embedding-3-small

​

Usage Instructions

​

1. Check Current Status

# Using curl (Bearer token required — pull the same value used by the
# Vercel/AWS Lambdas from .env.local or Secrets Manager):
curl -H "Authorization: Bearer $INTERNAL_API_TOKEN" \
     http://localhost:3000/api/backfill-embeddings

# Or using the test script (reads INTERNAL_API_TOKEN from env):
#   - `grep -m1` stops at the first match so a duplicate uncommented line
#     (rare, but possible during a rotation window) doesn't clobber the
#     token with a stale value.
#   - `cut -d= -f2-` keeps EVERYTHING after the first `=`, preserving any
#     `=` characters inside the token value (base64 padding, arbitrary
#     secret bytes, etc.). Plain `-f2` would silently truncate.
INTERNAL_API_TOKEN=$(grep -m1 '^INTERNAL_API_TOKEN=' .env.local | cut -d= -f2-) \
  node scripts/test-backfill-embeddings.js

• Number of chat logs without embeddings
• Number of chat logs with embeddings
• Total chat logs in the system
• Current configuration settings

​

2. Run the Backfill Process

​

Option A: Using the Test Script (Recommended)

# Make sure your development server is running
npm run dev

# In another terminal, run the test script
node scripts/test-backfill-embeddings.js

1. Check the current status
2. Verify the environment is development
3. Run the backfill if needed
4. Display progress and statistics
5. Show final results

​

Option B: Direct API Call

# Using curl (Bearer token required):
curl -X POST \
     -H "Authorization: Bearer $INTERNAL_API_TOKEN" \
     http://localhost:3000/api/backfill-embeddings

# Or using fetch in JavaScript:
fetch('http://localhost:3000/api/backfill-embeddings', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json',
    'Authorization': `Bearer ${process.env.INTERNAL_API_TOKEN}`,
  }
})

​

3. Monitor Progress

• Progress percentage
• Number of records processed
• Success/failure counts
• Time estimates
• Any errors encountered

​

Response Format

​

Success Response

{
  "success": true,
  "message": "Successfully backfilled embeddings for chat logs",
  "statistics": {
    "totalProcessed": 150,
    "successful": 148,
    "failed": 2,
    "totalToProcess": 150,
    "percentComplete": 100,
    "processingTime": "45.2s",
    "avgTimePerRecord": "0.301s",
    "batchSize": 50,
    "rateLimitDelay": "250ms"
  },
  "failedRecords": [
    {
      "id": "abc123",
      "clerk_id": "user_xyz",
      "error": "Rate limit exceeded",
      "prompt_preview": "What is my net worth..."
    }
  ]
}

​

Error Response

{
  "error": "This endpoint is only available in development environment",
  "environment": "production"
}

​

Processing Strategy

1. Batch Processing: Processes 50 records at a time to manage memory and API limits
2. Skip Existing: Only processes rows where embedding IS NULL
3. Error Recovery: Continues processing even if individual records fail
4. Rate Limiting: 250ms delay between batches to avoid OpenAI rate limits
5. Retry Logic: 3 attempts per record with exponential backoff
6. Progress Tracking: Logs progress every 10 successful embeddings

​

Error Handling

• Environment errors: Blocks execution in production
• Database errors: Returns detailed error messages
• OpenAI API errors: Retries with backoff
• Rate limit errors: Automatic retry with delay
• Individual record failures: Logs and continues with next record

​

Performance Considerations

• Processing Time: ~0.3 seconds per record on average
• API Usage: One OpenAI embedding call per chat log
• Memory: Processes in batches to avoid memory issues
• Database Load: Minimal due to batch processing

​

Troubleshooting

​

Common Issues

1. 401 Unauthorized on the request
   • Set INTERNAL_API_TOKEN in .env.local and re-export it into your shell before running the script/curl.
   • Verify the value matches what Vercel/AWS Secrets Manager has for the same variable (they must agree).
2. “Endpoint not available” error
   • Ensure NODE_ENV is set to development (POST only; GET returns metadata regardless).
   • Check environment variables.
3. Rate limit errors
   • Increase RATE_LIMIT_DELAY in the route
   • Reduce BATCH_SIZE
4. Timeout errors
   • Increase maxDuration in the route
   • Process in smaller batches
5. No records to process
   • All chat logs already have embeddings
   • Check the database directly to verify

​

Verification

1. Checking the GET endpoint for statistics
2. Querying the database:

-- Count chat logs without embeddings
SELECT COUNT(*) FROM ai_chat_logs WHERE embedding IS NULL;

-- Count chat logs with embeddings
SELECT COUNT(*) FROM ai_chat_logs WHERE embedding IS NOT NULL;

3. Testing the chat memory functionality in the application

​

Best Practices

1. Run during low-traffic periods to minimize impact on users
2. Monitor OpenAI API usage to avoid unexpected costs
3. Test on a subset first by modifying the query limit
4. Keep logs of the backfill process for debugging
5. Verify embeddings by testing chat memory after completion

​

Integration with Chat System

• The searchChatHistory tool in app/api/chat/chat-history-search-tool.ts can find relevant past conversations
• The AI uses these embeddings for semantic search
• Users get more contextual and personalized responses

​

Future Improvements

• Add support for partial backfills (date ranges, specific users)
• Implement parallel processing for faster completion
• Add a web UI for monitoring progress
• Support for different embedding models
• Automatic scheduling for periodic backfills