User Analytics Service

Problem/Feature Description

A SaaS company needs a user analytics service built with Python and PostgreSQL. The service tracks user actions (page views, button clicks, feature usage) and provides aggregated reports. The system receives thousands of events per minute during peak hours.

The PostgreSQL database has three tables:

• users -- id, email, name, plan (free/pro/enterprise), created_at
• events -- id, user_id (FK to users), event_type, page_url, metadata (JSONB), created_at
• daily_summaries -- id, user_id (FK to users), date, event_count, unique_pages, created_at

The service needs these capabilities:

1. Ingest events -- Accept a batch of user events (typically 100-500 at a time from a queue) and store them efficiently. Each event has user_id, event_type, page_url, and optional metadata.

2. Generate daily summaries -- At end of day, compute a summary for each active user: total events and count of unique pages visited. If a summary already exists for that user+date, update it rather than creating a duplicate.

3. User activity report -- Given a user ID and date range, return all their events sorted by most recent first. Some users have millions of events, so the query must handle large result sets without loading everything into memory.

4. Active users query -- Return users who had at least one event in the last 7 days, with their event count, sorted by most active first.

The service uses asyncpg since it runs in an async FastAPI application.

Output Specification

Produce:

• app/db.py -- Database connection setup and pool management
• app/analytics.py -- The four functions: ingest_events, generate_daily_summaries, get_user_activity, get_active_users
• app/main.py -- FastAPI application with the pool lifecycle and routes