ARCH.7.2: Database Schema & Query Optimization

[echetoui]

Objective

Optimize database schema and queries for production PostgreSQL.

Tasks

• Create indexes for Phase 6 analytics (scam_type, created_at, user_id)
• Design report_analytics table (for Phase 6)
• Optimize user report history query (reports WHERE user_id = ? ORDER BY created_at DESC)
• Test concurrent rate limit checks (no deadlocks)
• Migrate all DynamoDB-style queries to SQL
• Setup connection monitoring

Success Criteria

• All 20+ queries optimized
• Analytics queries ready for Phase 6
• No N+1 query problems
• Connection pool stable under load

Effort: 8 hours

Sprint: Week of Apr 28-May 4

Depends on: #88