> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cfosilvia.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Backfill embeddings

# Chat Log Embeddings Backfill Documentation

## Overview

The embeddings backfill route (`/api/backfill-embeddings`) is designed to generate embeddings for all existing chat logs that don't have embeddings yet. This enables the memory functionality for all existing users immediately, allowing the AI to search through past conversations for context.

## Purpose

When the chat memory feature was initially deployed, only new chat logs received embeddings. This backfill route ensures that:

* 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

First, check how many chat logs need embeddings:

```bash theme={null}
# 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
```

This will return:

* 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)

```bash theme={null}
# Make sure your development server is running
npm run dev

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

The script will:

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

```bash theme={null}
# 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

The backfill process provides detailed console output:

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

## Response Format

### Success Response

```json theme={null}
{
  "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

```json theme={null}
{
  "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

The route handles several types of errors:

* **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

After running the backfill, verify success by:

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

```sql theme={null}
-- 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

Once embeddings are generated:

* 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

Potential enhancements for the backfill process:

* 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


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.