Analytics dashboard cut from 67s to 1.36s with daily rollups

The admin analytics dashboard was taking 43-67 seconds to render. I shipped daily rollup tables and a backfill script to fix it.

The rollup script

I wrote scripts/analytics_rollup.py at 167 lines. It rolls finished days into analytics_daily and doc_view_daily. It's idempotent per day using delete-then-insert inside one transaction. It caps doc_view_daily at the top 500 documents per day. It accepts --backfill N, --day, and --top flags.

I documented RAW_RETENTION_DAYS = 35 as policy but did not wire deletion. Nothing in the script deletes anything.

Backfill and deploy

I backfilled 190 days in 30.63 seconds. The counts matched the raw data exactly on totals and on all 190 individual days: 346,735 searches and 10,752,053 doc views.

I deployed commit 7839571 to both master and dev. I installed the rollup as a cron job at 04:10 CT.

I modified web_search.py to use the rollup tables with a live fallback when rollup data is missing. I dropped users_week and users_month from both branches because a rollup can only sum per-day unique IPs, which is not a distinct count across a range. users_today stayed and is exact.

The write-time filter went stale immediately

I shipped a write-time filter in log_search and log_doc_view that checks User-Agent against a list of 10 fleet strings I pulled from the Aug 16 access log.

The fleet rotated User-Agents on Aug 17 to 8 new Chrome 142-145 strings at about 172,000 distinct IPs. The filter matched 40.88% of traffic on Aug 16, 0.09% on Aug 17 before the edge rule went live, and 0.00% after. It went obsolete within hours.

I decided not to convert the literal list to a version pattern because Chrome 142-145 are current real browser versions and a pattern would suppress logging of genuine visitors.

Edge challenge is holding

Cloudflare rule 14 went live at 12:00 CT. Origin traffic for /ai-analyst dropped from 17,475 requests per hour to 9. /search dropped from 4,708 to 94 per hour. 502 errors fell from 130,105 to 10. Googlebot passed 13,832 requests with zero 403s and zero 429s. Non-pool /api/me traffic stayed steady across the cutover.

Worker recycling stopped. I counted 20 recycles in the 95 minutes before the 14:43 restart, about one per 4.75 minutes across the 5-worker pool, then 0 recycles in the 30 minutes after rule 14 collapsed traffic.

Rule 14 does not cover /document/, so about 733 requests per hour of rotated fleet still reach origin there. That's down 94% from 12,830 per hour but not zero.

Cron must not source .env

The cron job does not source .env. .env defines DATABASE_PATH pointing at the app-dev symlink. Sourcing it would silently retarget the prod cron to dev. The visitor-count job dodges the same trap by re-setting DATABASE_PATH explicitly after sourcing.

I ran the backfill as root from /home/epsteinscan/app, exactly as cron will, so any WAL ownership problem would surface under supervision rather than at 04:10 unattended. It did not happen.

Results

/admin/analytics now renders in 1.36 seconds cold and 0.01 seconds cached, down from 43-67 seconds. The cache is per-worker across 5 workers, so a request can still be slow if it lands on a cold worker.