Building an admin users panel when the schema is shared

I needed to add a users panel to the admin section, but the users table is in auth.db, which is shared between dev and prod. Both checkouts symlink database/auth.db to /home/epsteinscan/shared/database/auth.db, so any ALTER TABLE users would break production instantly.

Sibling table instead of columns

I built a separate signup_source table instead of adding nullable columns to users. The User class is constructed via User(**dict(row)) over SELECT * with a fixed signature. A new table is invisible to that constructor.

Client-side first-touch capture

I captured signup source client-side in sessionStorage and posted it as hidden form fields at registration. Writing the Flask session on anonymous GETs would have set a cookie sitewide and made every HTML page uncacheable at nginx and Cloudflare.

AI call logging

The three exception handlers in the answer stream were swallowing exceptions without logging them. Production AI failures had left no trace anywhere for five months. I added a new ai_call_log table and made all three paths write warnings and log entries.

Last login throttling

I added a last_login column to users and set it on successful login. The update is throttled to one per UTC day in user_loader so a user checking the site ten times in one day does not run ten UPDATE queries.

The panel

The new /admin/users route shows email, plan, signup date, last login, AI call count, last AI call, and signup source. It is sortable by email, signup, last login, and AI calls via an ORDER BY whitelist. Bind parameters are not accepted in ORDER BY, so I used a dict. Hostile query strings like ?sort=%3Bdrop&dir=%3Bdrop fall back to the default sort and return 200.

Model health strip

I added a model health strip to /admin/analytics showing the answer model, extract model, calls today, errors in the last 24 hours, last error, and an ASK_ENABLED warning. It has a red left border when errors are greater than zero.

Verified with a real failure

I tested the AI error path by temporarily repointing ASK_MODEL_ANSWER at a nonexistent model, exercising the handler, reverting, and proving byte-identity with diff. A synthetic row would not have proven the branch fires.

Header Ask AI button

I added a sitewide Ask AI button to the header search bar in base.html. It reuses the same livePulse keyframes as the LIVE badge for the nav AI badge. The button carries through via /ai-analyst?q=... and prefills the input, but never auto-submits.

I added a hot-send accent glow on the AI send button when the input is prefilled via ?q=. It uses the same livePulse timing and stops on first user interaction. It never fires on an empty or manually-typed input.

Blog disclaimer on both paths

I extended the fairness disclaimer safety net to /api/blog/publish so the manual path matches the generator path. I dry-ran it five times on dev, then five times again against deployed production code. I duplicated DISCLAIMER_HTML into web_search.py rather than importing it from scripts/auto_blog.py, because that module does load_dotenv() and imports anthropic at module level.

Dry-run isolation

I dry-ran the blog disclaimer against a scratch database, never against dev. blog_posts lives in the shared epstein_ocr.db that both checkouts symlink to, so a test publish on dev would have appeared on production's /blog instantly. I built a scratch database holding a 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 audited and shredded /etc/supervisor/conf.d/epsteinscan.conf.bak, which was mode 0644 and carried 13 credentials inline. I rotated COMMAND_CENTER_SECRET, ADMINSTACK_API_KEY, and FLASK_SECRET_KEY into /home/epsteinscan/.env. I generated values with secrets server-side and never printed them.

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 assertions, including both directions: a cc_token signed with the old secret is now rejected and one signed with the new secret is accepted. Checking only the new value would not have distinguished a working rotation from a route that accepts anything.

I shredded ~/.env.bak-20260625-secretrotate and app-dev/deploy/supervisor.conf. The latter was a stale template carrying wrong values and rotated secrets. Nothing referenced it. I rewrote .env atomically via a temp file in the same directory, 0600 plus original ownership, fsync, then os.replace(). I deliberately created no plaintext backup.

CRLF churn

git diff --stat showed 913 lines in admin_analytics.html and 240 in admin_base.html. With -w it showed 85 and 34. Root cause: patch_admin.py read the files in Python text mode, so universal-newline translation stripped CRLF before the backup was even written. I proved backups were content-identical to HEAD when CR-normalized, restored CRLF wholesale, then put back the one 3-line LF-only block in admin_base.html.

Subtitle wrapping

I added a subtitle under the /admin/users heading. The heading card is a space-between flex row whose two children are the h1 and the Refresh link. An unwrapped paragraph would have become a third flex item and rendered beside Refresh. I wrapped the h1 and the new <p> in a div. I asserted correct placement with Playwright bounding boxes in both themes.