Reporting Dashboard Backend

Problem/Feature Description

An e-commerce company needs a reporting backend for their internal analytics dashboard. The backend is built with Python and PostgreSQL and serves data to a React frontend. The reports involve aggregating large amounts of order data.

The PostgreSQL database has these tables:

• customers -- id, email, name, segment (new/returning/vip), region, created_at
• orders -- id, customer_id (FK), status (pending/confirmed/shipped/delivered/cancelled), total_cents, currency, placed_at, updated_at
• order_items -- id, order_id (FK), product_id (FK), quantity, unit_price_cents, created_at
• products -- id, name, category, sku (unique), cost_cents, created_at

The backend needs these report functions:

1. Revenue by region -- For a given date range, calculate total revenue (sum of order totals for delivered orders) grouped by customer region. This query joins customers and orders and uses aggregation.

2. Product performance -- For a given date range, return each product with its total units sold, total revenue, and profit margin (revenue minus cost). Include products with zero sales. Sort by revenue descending.

3. Customer cohort export -- Export all customers who placed their first order in a given month, along with how many orders they placed total and their lifetime spend. The company has 2 million customers, so this must stream results, not load them all into memory.

4. Daily order sync -- Receive a batch of order updates from the fulfillment system (500-2000 records). Each record has an order ID and a new status. Update all orders to their new status and record the updated_at timestamp. All updates in a batch must succeed or fail together.

The application uses a mix of psycopg 3 (sync) for the report queries (run by background workers) and asyncpg for the API endpoints. Both need proper connection management.

Output Specification

Produce:

• app/db.py -- Both sync (psycopg) and async (asyncpg) pool setup
• app/reports.py -- The three report functions using psycopg (sync)
• app/api.py -- The order sync endpoint using asyncpg (async)
• app/main.py -- Application with lifecycle management for both pools