# 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.