Migrate October Citation Data to UAT Database

Summary

The UAT database currently only contains 2 days of citation data (Oct 30-31, 211 records). We need to migrate the full October dataset (91,371 records) from the DEV cluster to UAT so the dashboard displays complete monthly metrics.

Current Status

✅ Completed

• Citation columns added to aivisibility database schema:
• citations (JSONB)
• web_search_performed (BOOLEAN)
• num_citations (INTEGER)
• equal_experts_in_citations (BOOLEAN)
• citation_quality_score (NUMERIC)
• Helper function check_equal_experts_in_citations() created in UAT database
• Dashboard API endpoints functional and returning citation data
• Migration script configured correctly:
• Source: DEV cluster (ai-visibility-dev-f6fa088a) → ai_visibility database
• Target: UAT cluster (ai-visibility-cluster-p5-uat) → aivisibility database
• Secret ARN corrected to arn:aws:secretsmanager:us-east-1:804374560053:secret:ai-visibility/uat/db-master-password-p5-FWESEj

❌ Blocking Issue

Migration failing due to missing partition:

ERROR: no partition of relation "ai_model_responses" found for row; SQLState: 23514

The ai_model_responses table is partitioned by response_date, but UAT only has partitions for:

• Oct 30 - Oct 31 (current partition)
• Nov 1 - Dec 1 (next partition)

October data (Oct 1-29) cannot be inserted without the October partition.

What Needs to Be Done

1. Create October 2025 Partition

Execute in UAT aivisibility database:

SELECT create_monthly_partition('ai_model_responses', '2025-10-01'::date);

This will create partition ai_model_responses_2025_10 covering Oct 1 - Nov 1.

2. Run Migration

aws-vault exec ee-data-studio -- python3 scripts/migrate_ai_responses_table.py

Expected result:

• Source rows: 91,371
• Target rows after migration: 91,582 (91,371 + 211 existing)
• Processing time: ~90 minutes (batch size of 5 due to large TEXT columns)

3. Verify Data Migration

Database Verification

aws-vault exec ee-data-studio -- python3 <<'PYEOF'
import boto3

cf = boto3.client('cloudformation', region_name='us-east-1')
stack = cf.describe_stacks(StackName='ai-visibility-phase5-complete-uat')
outputs = {o['OutputKey']: o['OutputValue'] for o in stack['Stacks'][0]['Outputs']}
cluster_arn = outputs['DBClusterArn']

sm = boto3.client('secretsmanager', region_name='us-east-1')
secrets = sm.list_secrets()
secret_arn = [s['ARN'] for s in secrets['SecretList'] 
              if 'ai-visibility/uat/db-master-password-p5' in s['Name']][0]

rds = boto3.client('rds-data', region_name='us-east-1')

sql = """
SELECT 
    MIN(response_date) as earliest,
    MAX(response_date) as latest,
    COUNT(*) as total,
    COUNT(DISTINCT response_date) as unique_dates
FROM ai_model_responses;
"""

result = rds.execute_statement(
    resourceArn=cluster_arn,
    secretArn=secret_arn,
    database='aivisibility',
    sql=sql
)

row = result['records'][0]
print(f"Earliest: {row[0]['stringValue']}")
print(f"Latest: {row[1]['stringValue']}")
print(f"Total records: {row[2]['longValue']}")
print(f"Unique dates: {row[3]['longValue']}")
PYEOF

Expected output:

Earliest: 2025-10-01
Latest: 2025-10-31
Total records: 91,582
Unique dates: 31

API Endpoint Verification

curl -s "https://gzs15y5xyd.execute-api.us-east-1.amazonaws.com/uat/api/citations/analysis" | jq '.eeRateByProvider | length'

Expected: Should show ~31 entries (one per day for October) instead of just 2

Dashboard UI Verification

1. Navigate to the UAT dashboard
2. Check "EE Citation Rate by Provider" graph
3. Verify it shows the full month of October (Oct 1-31) instead of just 2 days

Success Criteria

• [ ] Database contains 91,582+ records
• [ ] Date range spans Oct 1-31, 2025 (31 unique dates)
• [ ] API returns citation metrics for all of October
• [ ] Dashboard displays full month of data in graphs

Files Involved

• Migration script: scripts/migrate_ai_responses_table.py
• Schema deployment: scripts/deploy-database-schema.sh
• Base schema: database/schema/production_schema.sql