Building an admin panel for registered users with signup source tracking

I needed visibility into registered users and where they were coming from. The admin panel had analytics but no user list.

The user panel

I built /admin/users with email, plan, signup date, last login, AI call count, last AI call, and signup source. Sortable by email, signup, last login, and AI calls via an ORDER BY whitelist. Four metric tiles including anonymous AI calls.

I added a signup_source table instead of nullable columns on users. The User class is built via User(**dict(row)) over SELECT * with a fixed signature, and auth.db is shared with production. An ALTER TABLE users would have broken prod instantly. A new table is invisible to that constructor.

Signup source capture

I record first-touch referrer and landing path client-side in sessionStorage and post them as hidden fields at registration. I chose client-side over server-side session capture because writing the Flask session on anonymous GETs would Set-Cookie sitewide and make every HTML page uncacheable at nginx and Cloudflare.

Last login tracking

I added a last_login column and set it on successful login, throttled to one UPDATE per UTC day in user_loader.

AI call logging

The three exception handlers in the answer stream were silently swallowing failures. I added warning logs and writes to a new ai_call_log table. Production AI failures had left no trace anywhere for five months.

AI model health strip

I added a health strip on /admin/analytics showing answer model, extract model, calls today, errors last 24h, last error, and ASK_ENABLED warning. Red left border when errors are greater than zero.

Problems

The AI error path verification used a real API failure, not a synthetic INSERT. I temporarily repointed ASK_MODEL_ANSWER at a nonexistent model, exercised the handler, reverted, and proved byte-identity with diff. A synthetic row would not have proven the branch fires.

The ai_call_log table was empty after the first AI call. The Playwright harness closed the page mid-SSE, killing the generator so no post-stream code ran. Fixed by polling until the rendered answer text stops growing.

Dry-running the blog disclaimer against a scratch DB instead of dev was necessary because blog_posts lives in the shared epstein_ocr.db that both checkouts symlink to. A test publish on dev would have appeared on production's /blog instantly. I built a scratch DB holding a real copy of the 89-row people table plus an empty blog_posts, pointed DATABASE_PATH at it, and drove the real route through Flask's test client.

Security cleanup

I found /etc/supervisor/conf.d/epsteinscan.conf.bak carrying 13 credentials inline in its environment= line. Mode 0644, readable by the app user while .env itself is 0600. I shredded both supervisor backups after confirming supervisor's include glob is *.conf.

I rotated COMMAND_CENTER_SECRET, ADMINSTACK_API_KEY, and FLASK_SECRET_KEY into /home/epsteinscan/.env. Values generated with secrets server-side, never printed. I restarted prod and dev with supervisorctl restart, not kill -HUP. Gunicorn only re-reads its environment at exec, so a HUP would have silently kept the old values. Dev was restarted too because it sources the same .env and dev.epsteinscan.org is public.

I verified 13/13, including both directions. A cc_token signed with the old secret is now rejected and one signed with the new secret is accepted. /api/blog/posts 401s on the old key and 200s on the new. Checking only the new value would not have distinguished a working rotation from a route that accepts anything.

I also shredded ~/.env.bak-20260625-secretrotate and app-dev/deploy/supervisor.conf. The latter was shredded, not chmod'd, because nothing references it. No script, Makefile, or doc. It was a stale template carrying wrong values and a rotated Stripe key. The live config is /etc/supervisor/conf.d/epsteinscan.conf, which sources .env.

What's left

AdminStack on Netlify must be updated or two integrations stay broken. EpsteinScan COMMAND_CENTER_SECRET maps to AdminStack env var COMMAND_CENTER_SECRET. EpsteinScan ADMINSTACK_API_KEY maps to AdminStack env var EPSTEINSCAN_API_KEY.

DB_DOWNLOAD_URL rotation is blocked. No R2 credentials or tooling on the box. The URL is a bucket-level public dev domain that also serves all document media. Moving only backups/epstein_ocr.db.gz to an unguessable key via S3 CopyObject within the bucket would fix it, leaving images and PDFs alone.