#lethe-web-ui-aggregates

Status: Design Module: sourcecraft.dev/bigbes/lethe Branch: master Worktree: none Parent RFC: Personal AI Assistant Log Aggregator (2026-04-25) Design source: docs/design_handoff_assistant_log/ (Direction 4 — Dense Data UI) Sibling tasks:

• lethe-server.md (#1, ✓ Verified)
• lethe-web-ui-foundation.md (#4, ✓ Reviewed)
• lethe-collector-claude-code.md (#2, deferred)
• lethe-search-and-opencode.md (#3, deferred)
• lethe-web-ui-palette-savedsearch.md (#6, deferred)
• lethe-web-ui-search.md (#7, blocked on #3)
• lethe-web-ui-settings-display.md (#8, deferred)
• lethe-web-ui-health-sources.md (#9, blocked on #2)

#Design

#Purpose

Add the two backend aggregation endpoints (/api/v1/projects, /api/v1/stats) the foundation deferred, plus the three frontend screens that consume them: Projects index, Project detail, Stats. Replaces three of the foundation's stub routes (/projects, /project/$, /stats) with real content.

In scope: Go aggregation queries; the new project URL pattern; chart primitives (stacked-bar, heatmap, horizontal-bar, hour-bar) inline in SVG; range/group-by sub-bar controls. Out of scope: Search route, Health route, Settings → Display, palette PROJECT/SESSION items, saved searches.

#Chosen approach

Backend — two endpoints, both additive:

• GET /api/v1/projects?since=<unix>&limit=&offset= returns rows of { cwd, sessions, turn_count, tokens_in_total, tokens_out_total, last_active, hosts[], tools[], top_tool }. SQL groups by working_dir over the existing sessions+turns join, ordered by MAX(ended_at) DESC. Owner-scoped via existing auth middleware.
• GET /api/v1/stats?range=7d|30d|90d|all returns a single fat JSON response with six cards in one round-trip:
  • per_tool: { tool, sessions, turns, tokens_in, tokens_out, daily_sparkline: number[N] }[] for the requested range
  • daily: { date_unix, per_tool: { [tool: string]: number /*turns*/ } }[] over the last 60 days
  • heatmap: { date_unix, count }[] — 84 cells (12 weeks × 7 days), fixed window
  • top_cwd: { cwd, count }[] — top 20 cwds by turn count in the requested range
  • hour_of_day: { hour, count }[] — 24 buckets over the range
  • host_split: { host, count }[] over the range
• Both endpoints reuse the existing apierror.Render for error paths; both honor admin ?owner= override exactly like sessions list.

Backend — one tiny extension to sessions list: GET /api/v1/sessions?cwd=<exact> accepts a new optional filter on working_dir. Project detail page uses this to pull a cwd's sessions; no separate project-detail endpoint. Wire/schema unchanged.

Frontend:

• useProjects(filters) — TanStack Query hook for /api/v1/projects.
• useStats(range) — TanStack Query hook for /api/v1/stats.
• Routes:
  • web/src/routes/projects.tsx (replaces stub) — projects table.
  • web/src/routes/project.$.tsx (new, $ catch-all so cwd-with-slashes works as a single URL-encoded path segment) — header card + sessions table scoped to that cwd via the new ?cwd= filter.
  • web/src/routes/stats.tsx (replaces stub) — sub-bar with range pills (7d / 30d / 90d / all), 2-column card grid.
• New chart primitives (web/src/primitives/): StackedBars (60×N stacked-bar SVG), Heatmap (12×7 cell grid), HorizontalBars (top-cwd list), HourBars (24-bin column chart). Spark from the foundation is reused for the per-tool sparkline cards. All SVGs inline; no chart library.

Project identity: exact working_dir string, no canonicalization. URL is /project/$ with a single segment carrying the URL-encoded cwd. Tradeoff: /Users/x/proj and ~/proj resolved-to-the-same-place show as different projects. Acceptable for v1; canonicalization is a known limitation.

#Hands-off decisions

• udesign — stats endpoint = single fat JSON. Six small queries server-side beat six round-trips; payloads are a few KB.
• udesign — project identity = exact working_dir. Simpler, matches prototype; canonicalization needs OS-level path resolution that's out of scope.
• udesign — project detail uses GET /api/v1/sessions?cwd= rather than a dedicated endpoint. Avoids duplicating the sessions DTO server-side.
• udesign — range-pill semantics are unix-seconds offsets. 7d = now - 7*86400; all omits the range param. Mirrors the existing since pattern on sessions list.
• udesign — top_cwd capped at 20. Card has fixed height; prototype shows ~10 entries.
• udesign — heatmap window is fixed at 12 weeks (84 cells), not range-dependent. Matches the prototype.

#Backwards-compatibility check

All changes additive:

• Two new endpoints; no existing consumers.
• One new optional query param (?cwd=) on /api/v1/sessions; old clients ignore it and get the existing behavior.
• No schema migrations.
• No wire-type changes.
• Frontend stub routes are replaced; no deployed user is affected.

#TDD: yes (scoped)

• Yes for: each backend aggregate query (Go repository tests with seed data per card); the date-bucketing logic that fills missing days/hours with zeros (small Go helper, deterministic); the TS adaptStats adapter that pipes Go DTOs into the chart-friendly shapes.
• No for: SVG chart primitives — visual, no logic worth testing beyond what the rendered output shows.

#Unknowns (resolve during execute)

• Catch-all $ route compatibility with TanStack Router: needs a quick spike — TanStack supports $param (segment) and $ (full splat). Verify the splat captures slash-bearing cwds correctly and Link round-trips them.
• Aggregate query plan at scale: at 10–100k turns the GROUP BY's are fine on existing PKs. If EXPLAIN QUERY PLAN shows a scan on turns for the top_cwd query, add a covering index (owner, timestamp) in the same execute. Not blocking the phase.

#Invariants

• /api/v1/projects and /api/v1/stats reuse the existing auth middleware. No bypass, no special-casing.
• ?owner= admin override behaves identically across /sessions, /projects, /stats (all-owners when set to *, specific owner when set to a username, default = caller's identity).
• No schema migrations; no internal/shared/wire/ changes; no removed/renamed JSON fields.
• All new HTTP error paths render application/problem+json via the existing apierror.Render.
• Stats response is computed fresh per request (no server-side cache). Range parameter is the only knob; no per-tool/host filters at the endpoint level.
• Project identity is exact working_dir; the server never normalizes paths.
• All API calls in the SPA flow through TanStack Query + the existing apiFetch wrapper. No raw fetch outside api/client.ts.
• All chart primitives are inline <svg> in TSX; no chart library dependency added.

#Principles

• One round-trip per stats screen render. The frontend doesn't fan out to six endpoints; the server's GROUP BYs are cheap enough to bundle.
• Server returns numbers; the SPA formats and colors. No HTML, no pre-rendered SVG, no localized strings on the wire.
• Project detail = scoped sessions list. Resist building a parallel "project entity" model on either side.
• Reuse Spark, Tag, ToolDot, EmptyState, Sub, SessionsTable from the foundation. Add chart primitives only where none of these fits.
• No silent fallbacks. If a stats card's query returns zero rows for the requested range, the response includes that card as an empty array; the SPA renders an EmptyState per-card. No "0%" placeholders, no synthesized data.