---
id: M015
title: "Social Proof, Freshness Signals & Admin UX"
status: complete
completed_at: 2026-04-03T04:44:12.851Z
key_decisions:
  - D025: PostgreSQL search_log + Redis read-through cache with 5-min TTL for popular searches (collaborative)
  - Fire-and-forget asyncio.create_task with independent session for non-blocking search logging
  - Correlated scalar subquery for last_technique_at to preserve existing creators query structure
  - Used existing CSS custom property names (--color-accent, --color-bg-surface) rather than plan shorthand
  - matchMedia ref with 150ms leave delay for hover-to-open admin dropdown without breaking mobile
key_files:
  - backend/models.py
  - backend/schemas.py
  - backend/routers/search.py
  - backend/routers/stats.py
  - backend/routers/creators.py
  - backend/main.py
  - alembic/versions/013_add_search_log.py
  - frontend/src/pages/Home.tsx
  - frontend/src/pages/CreatorsBrowse.tsx
  - frontend/src/api/public-client.ts
  - frontend/src/components/AdminDropdown.tsx
  - frontend/src/App.css
lessons_learned:
  - Fire-and-forget async logging with independent DB sessions is the right pattern for non-blocking analytics writes in FastAPI — catch-all exception handling prevents logging failures from surfacing to users
  - Redis read-through cache pattern (check → miss → query → SET with TTL → return) is simple and effective for low-traffic endpoints where stale data is acceptable
  - Correlated scalar subqueries preserve existing query structure better than JOINs when adding a single derived field to an existing endpoint
---

# M015: Social Proof, Freshness Signals & Admin UX

**Added search query logging with trending searches display, creator freshness dates, homepage stats scorecard, and admin dropdown hover behavior across 5 slices touching 14 files.**

## What Happened

M015 added social proof and freshness signals to the Chrysopedia public site. Five slices delivered independently (except S04 depending on S01's popular searches API).

**S01** added search query logging — a SearchLog model with Alembic migration 013, fire-and-forget async INSERT on every non-empty search, and a GET /search/popular endpoint with Redis read-through cache (5-min TTL, 7-day window, LOWER normalization). Deployed and verified with real search data on ub01.

**S02** added creator freshness dates — a correlated MAX(created_at) subquery on the creators endpoint exposes last_technique_at, rendered as "Last updated: Mon D" on the creators browse page. Homepage recently-added cards also gained subtle date stamps.

**S03** added a homepage stats scorecard — GET /api/v1/stats returns live technique_count and creator_count via async COUNT queries. The frontend renders these in a cyan-on-dark scorecard between nav cards and the random technique button.

**S04** wired S01's popular searches API into a Trending Searches homepage section with clickable pill links that navigate to search results. Conditionally hidden when no data exists.

**S05** added hover-to-open on the admin dropdown at desktop widths (≥769px) via matchMedia ref with 150ms leave delay. Mobile retains tap-to-toggle.

All features deployed and verified live on ub01:8096. 14 files changed, 467 insertions across backend (models, schemas, routers, migration) and frontend (pages, components, styles, API client).

## Success Criteria Results

Validated against the Final Integrated Acceptance from M015-CONTEXT.md:

- [x] **Homepage shows stats scorecard with real counts** — GET /api/v1/stats returns {technique_count:21, creator_count:7}. Browser screenshot confirms "21 ARTICLES" and "7 CREATORS" in cyan-on-dark scorecard. (S03)
- [x] **Homepage shows trending searches with real terms** — Trending Searches section renders pills with terms like "reverb", "synthesis", "fx", "drums" sourced from GET /search/popular. (S04)
- [x] **Homepage recently-added cards show date tags** — .recent-card__date elements show "Apr 2", "Apr 3" in muted styling. (S02)
- [x] **Creators browse page shows last-updated per creator** — "Last updated: Apr 3" visible on creator rows. Creators with 0 techniques omit the date. (S02)
- [x] **Admin dropdown opens on hover (desktop) and tap (mobile)** — matchMedia(min-width:769px) guard + onMouseEnter/onMouseLeave with 150ms leave delay. Build passes. (S05)
- [x] **search_log table has rows from real searches** — 3 rows confirmed via `SELECT count(*) FROM search_log` on ub01. (S01)
- [x] **Popular searches endpoint returns cached results from Redis** — curl returns {"items":[{"query":"reverb","count":1}],"cached":true}. (S01)

## Definition of Done Results

- [x] All 5 slices complete (S01–S05 all ✅ in roadmap)
- [x] All 5 slice summaries exist with verification evidence
- [x] Cross-slice integration: S01→S04 dependency verified (S04 consumes S01's popular searches API successfully)
- [x] Frontend builds with zero TypeScript errors
- [x] All features deployed and verified on live ub01:8096
- [x] Code changes verified: 14 files changed, 467 insertions (git diff confirms non-.gsd/ changes)

## Requirement Outcomes

| Requirement | Previous Status | New Status | Evidence |
|-------------|----------------|------------|----------|
| R033 — Creator Last Updated Date | Active | **Validated** | Browser assertions on ub01:8096 confirm "Last updated: Apr 2/3" on creators page and date stamps on homepage cards |
| R034 — Homepage Stats Scorecard | Active | **Validated** | GET /api/v1/stats returns {technique_count:21, creator_count:7}. Browser screenshot confirms scorecard in cyan-on-dark design |
| R035 — Popular Search Terms Display | Active | **Validated** | Backend: search_log table with rows, GET /search/popular returns cached top-N. Frontend: Trending Searches section with clickable pills on homepage |
| R036 — Admin Dropdown Hover on Desktop | Active | **Validated** | AdminDropdown.tsx implements matchMedia guard + hover handlers + 150ms leave delay. Build passes |

## Deviations

Docker Compose service name is chrysopedia-web, not chrysopedia-web-8096 as referenced in CLAUDE.md. Minor plan spec mismatch: router stores full prefix paths ('/search/popular') not bare ('/popular'). CSS custom property names used real codebase conventions (--color-accent) instead of plan shorthand (--accent).

## Follow-ups

Stats endpoint has no caching — would benefit from Redis caching or stale-while-revalidate if traffic grows. Popular searches cache key is global (no per-scope queries). Search analytics dashboard could be built from the search_log data (zero-result queries, time-of-day patterns).
