DB Schema Assumptions for Handler SQL Queries

DB Schema Assumptions for Handler SQL Queries

The PHP source is not available in this repo. SQL queries in handlers are inferred from CodeIgniter conventions (snake_case table names, id PKs, created_at/updated_at timestamps), the plan’s Endpoint Migration Map, and the OpenAPI schema. These assumptions must be verified against the actual Aurora schema during Phase 6 integration testing before production cutover.

Key assumptions:

• console DB: tables advertisers, campaigns, sites, placements, ad_units, delivery_groups, api
• aggregate_ro DB: tables analytics (or campaign_metrics), clickthroughs
• All queries filter by account_id which comes from the auth result stored in the Hono context
• Campaigns join to sites/placements/ad_units/delivery_groups via foreign keys (campaign_id, site_id, placement_id, ad_unit_id)
• Analytics rows are keyed by (campaign_id, date) with columns: impressions, clicks, conversions, spend
• v2 analytics adds: engagements, video_starts, video_q1, video_q2, video_q3, video_completions
• removeZeros strips rows where impressions + clicks + conversions = 0
• data_connector applies a flatten/reshape transform for Google Data Studio schema compatibility