Optimize /api/activity/ endpoint (SQL, cache, indexes)

[tystar86]

Context

Public header ticker data is served by GET /api/activity/ (blog/api/data/router.py). Payload is built by build_activity_payload() in blog/api_views.py (raw SQL with CTEs) and cached under ACTIVITY_CACHE_KEY with ACTIVITY_CACHE_TTL (blog/api/constants.py).

Goals

Improve latency, database load, and observability for this hot read path without breaking the JSON contract consumed by the React header ticker.

Suggested work (pick / split)

• Baseline: Profile with Django Silk (silk_profiler already lists /api/activity/) and/or EXPLAIN ANALYZE on the raw SQL; record p50/p95 and query plans.
• SQL / indexes: Confirm filters and ORDER BY match existing indexes on Post (status, published_at, created_at); add or adjust indexes if plans show seq scans or heavy sorts at scale.
• Correctness vs “latest post”: Review ordering for latest published post (lp CTE uses published_at only); align with product rules (e.g. COALESCE(published_at, created_at) if drafts/unpublished edge cases matter).
• Caching: Evaluate TTL (300s), cache stampede, optional cache versioning or invalidation hooks when posts/comments/users change (tradeoffs vs simplicity).
• API layer: Optional HTTP caching (Cache-Control, ETag) if CDN/browser caching is desired (coordinate with ticker refresh UX).
• Tests / contract: Extend or add tests around payload shape and cache-miss behavior (tests/unit/test_views_activity.py).

References

• Router: blog/api/data/router.py — activity
• Payload: blog/api_views.py — build_activity_payload
• Constants: blog/api/constants.py — ACTIVITY_CACHE_KEY, ACTIVITY_CACHE_TTL

Tracking

Create a Linear issue with the same scope; link that Linear ID here once filed.

[tystar86]

Linear ↔ GitHub link (automation note)

This agent session cannot call Linear’s API: the Linear MCP tools are not available here, and no LINEAR_API_KEY is in the environment.

Link them manually (about 1 minute)

1. In Linear, New issue → title: Optimize /api/activity/ endpoint (SQL, cache, indexes) (match GitHub).
2. Description: paste Optimize /api/activity/ endpoint (SQL, cache, indexes) #78 and a one-line note that it tracks this GitHub ticket.
3. Add a link attachment on the Linear issue pointing to that same URL (so GitHub appears on the Linear issue).

Optional: API (Personal API key from Linear → Settings → API)

1. List teams and copy a teamId:

   curl -s https://api.linear.app/graphql -H "Authorization: $LINEAR_API_KEY" -H "Content-Type: application/json" -d '{"query":"query { teams { nodes { id name key } } }"}'

2. Create issue with issueCreate (IssueCreateInput: teamId, title, description including the GitHub URL).

3. Optionally attachmentCreate on the new issue with url = this issue URL.

Next time in Cursor

Enable + authenticate the Linear MCP plugin (mcp_auth). Then the agent can run save_issue with links: [{ "url": "<this issue>", "title": "GitHub #78" } to create and connect automatically.