# v7.0 Phase 5.2 — Audit log viewer + cost reporting Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development to implement task-by-task.

**Goal:** Ship `/dashboard/admin/audit` (paginated audit log w/ filters) + `/dashboard/admin/cost` (per-user cost report + CSV export). Reuse Phase 5.1 patterns (RPC-only reads, REVOKE FROM authenticated; GRANT service_role; thin routes).

**Architecture:** 2 SECURITY DEFINER read RPCs (`list_audit_events`, `org_cost_report`) replace direct service-role reads; cursor validation lives in route, not RPC; cost period parsing is route-side; CSV formatting is route-side (RFC 4180).

**Tech Stack:** Next.js 16 App Router (apps/web), React 19, Supabase Postgres + RPCs, vitest. Deploys to Vercel. NO new env vars.

**Skip Vercel preview deploy verification** — agent env doesn't have Vercel access; CI does the real verification.

---

## File Structure

### New backend
- `data/deltas/20260508160000_phase5_2_audit_cost_rpcs.sql` — 2 RPCs + index + REVOKE/GRANT
- `apps/web/lib/dashboard/cost-csv.ts` — RFC 4180 encoder + safe filename builder
- `apps/web/lib/dashboard/audit-cursor.ts` — base64 cursor encode/decode + validate
- `apps/web/lib/dashboard/period.ts` — `YYYY-MM` validation + `(since, until)` to `(sinceTs, untilTs)` conversion
- `apps/web/app/api/dashboard/orgs/[orgId]/audit/route.ts` — GET list
- `apps/web/app/api/dashboard/orgs/[orgId]/cost/route.ts` — GET JSON
- `apps/web/app/api/dashboard/orgs/[orgId]/cost.csv/route.ts` — GET CSV (different filename to avoid Next conflict; same `[orgId]` dir)

### New frontend
- `apps/web/app/dashboard/admin/audit/page.tsx`
- `apps/web/app/dashboard/admin/cost/page.tsx`
- `apps/web/components/admin/AuditTable.tsx` — client
- `apps/web/components/admin/AuditFilterBar.tsx` — client
- `apps/web/components/admin/CostTable.tsx` — client
- `apps/web/components/admin/CostPeriodPicker.tsx` — client

### New tests
- `apps/web/__tests__/api/dashboard/orgs/audit.test.ts` — Tests 1-11
- `apps/web/__tests__/api/dashboard/orgs/cost-json.test.ts` — Tests 12-18
- `apps/web/__tests__/api/dashboard/orgs/cost-csv.test.ts` — Tests 19-22
- `apps/web/__tests__/api/dashboard/orgs/audit-cost-privilege.test.ts` — Test 27 (migration grep)
- `apps/web/__tests__/lib/dashboard/cost-csv.test.ts` — RFC 4180 encoder unit tests
- `apps/web/__tests__/lib/dashboard/period.test.ts` — month parser unit tests
- `apps/web/__tests__/lib/dashboard/audit-cursor.test.ts` — cursor unit tests
- `apps/web/__tests__/integration/admin-audit-cost-pages.test.tsx` — Tests 23-26

### Modified
- `apps/web/app/dashboard/admin/layout.tsx` — adds Audit + Cost links
- `apps/web/lib/dashboard/membership-guard.ts` — extend MAP with `bad_group_by:422, bad_period:422, bad_cursor:422`
- `apps/web/__tests__/_helpers/supabase-stub.ts` — `list_audit_events` + `org_cost_report` handlers
- `package.json` — bump `6.3.0-pre.7` to `6.3.0-pre.8`
- `CHANGELOG.md`

---

## Task 0: Pull spec from spec branch

```
git fetch origin spec/v7.0-phase5.2-audit-cost
git checkout origin/spec/v7.0-phase5.2-audit-cost -- docs/specs/v7.0-phase5.2-audit-cost.md
git add docs/specs/v7.0-phase5.2-audit-cost.md
git commit -m "docs: pull Phase 5.2 spec from PR #128 for impl reference"
```

---

## Task 1: Migration — 2 SECURITY DEFINER RPCs + index

**Codex plan-pass CRITICAL — schema-qualify everything.** RPCs reference `audit.events`, `public.runs`, `public.memberships`, `auth.users`. Index also references `audit.events`. Each function declares `SET search_path = public, audit, auth, pg_temp` so SECURITY DEFINER bypasses do not pivot through user-controlled schemas. Test 27 asserts `SET search_path` line + `audit.events` schema qualification.

**Codex plan-pass CRITICAL — runs.cost_usd dependency.** This delta MUST run after Phase 4's `20260508120000_phase4_runs_metadata.sql`. Filename timestamp ordering enforces this; Test 27 also greps for the Phase 4 file's existence in `data/deltas/` to fail fast if missing.

See spec for full SQL. Key points:
- Index `audit_events_org_keyset_idx` on `audit.events (organization_id, occurred_at DESC, id DESC)`
- `list_audit_events(p_caller_user_id, p_org_id, p_cursor_occurred_at, p_cursor_id, p_limit, p_action, p_actor_user_id, p_since, p_until) RETURNS jsonb`
  - Authorize first via memberships lookup; raise `not_admin` (P0001) if NULL or not in admin/owner
  - Clamp limit to [1, 200]
  - Keyset paginate: `WHERE (e.occurred_at, e.id) < (p_cursor_occurred_at, p_cursor_id)` when cursor present
  - LEFT JOIN auth.users for actor email
  - Return `{ events: [...], nextCursor: { occurredAt, id } | null }`
- `org_cost_report(p_caller_user_id, p_org_id, p_since, p_until, p_group_by) RETURNS jsonb`
  - p_group_by must equal 'user'; raise `bad_group_by` else
  - Authorize via memberships; raise `not_admin` if not admin/owner
  - Aggregate runs WHERE organization_id AND deleted_at IS NULL AND created_at >= p_since AND created_at < p_until GROUP BY user_id
  - Use `coalesce(sum(coalesce(col, 0)), 0)` to NULL-safe both directions
  - LEFT JOIN auth.users for email
  - Return `{ rows: [...], total: {...}, period: { since, until } }`
- REVOKE ALL ... FROM PUBLIC, anon, authenticated; GRANT EXECUTE ... TO service_role

**Steps:**
- [ ] Write migration
- [ ] Skip running locally; CI validates
- [ ] Commit

---

## Task 2: period.ts helper (TDD)

Implement `apps/web/lib/dashboard/period.ts` exporting `parsePeriod(since, until)`. See spec for exact regex `^\d{4}-(0[1-9]|1[0-2])$` and conversion: sinceTs = start-of-since-month UTC, untilTs = start-of-(until+1)-month UTC exclusive. Default to current UTC month when both null.

**Tests** at `__tests__/lib/dashboard/period.test.ts`:
- same-month (2026-04 → sinceTs=2026-04-01, untilTs=2026-05-01)
- multi-month
- December → January (year boundary)
- leap-year February (2028-02)
- malformed (`2026-13`, `26-04`, `abc`)
- since > until
- defaults (both null → current UTC month)

**Steps:**
- [ ] Write tests, FAIL
- [ ] Implement, PASS
- [ ] Commit

---

## Task 3: cost-csv.ts helper (TDD)

`apps/web/lib/dashboard/cost-csv.ts`:
- `encodeCostCsv(rows: CostRow[]): string` — RFC 4180, CRLF, UTF-8 no BOM, double-quote escape on `"`/`,`/`\n`/`\r`
- `buildCsvFilename(orgId, since, until)` — `cost-${orgId}-${since}-${until}.csv` validated against `/^[a-zA-Z0-9._-]+$/`; throw `unsafe_filename` else

**Tests:**
- simple row
- comma in email
- quote in email (doubled)
- newline in email
- CRLF terminator on each row + trailing
- filename safety (orgId UUID + valid period → safe; bad orgId → throw)

**Steps:** TDD, commit.

---

## Task 4: audit-cursor.ts helper (TDD)

`apps/web/lib/dashboard/audit-cursor.ts`:
- `decodeCursor(s: string | null | undefined): AuditCursor | null | 'invalid'` — base64 → JSON → typed `{occurredAt, id}` validated; ISO 8601 UTC required
- `encodeCursor(c: AuditCursor): string`

**Tests:**
- null/undefined → null
- valid roundtrip
- malformed base64 → 'invalid'
- malformed JSON → 'invalid'
- missing fields → 'invalid'
- wrong types → 'invalid'

**Steps:** TDD, commit.

---

## Task 5: GET /audit route + Tests 1-11

Route at `apps/web/app/api/dashboard/orgs/[orgId]/audit/route.ts`. See spec for full code.

Key points:
- isValidUuid on orgId, return 422 malformed_params if bad
- decodeCursor; if 'invalid' return 422 bad_cursor
- limit defaults 50, must be integer >= 1
- ISO regex on since/until query params
- Resolve session; 401 if null
- Call `list_audit_events` RPC; on `not_admin` → 404 (no enumeration); else mapPostgresError
- Re-encode nextCursor as base64 in response
- Cache-Control: private, no-store on every response

Stub extension `list_audit_events` in supabase-stub mirrors SQL: filter by org_id + status + cursor + action + actorId + since/until; ORDER BY occurred_at DESC, id DESC; emit nextCursor when more rows exist; LEFT JOIN to auth.users seeded.

**Tests 1-11** (per spec test plan):
1. admin GET → 200, nextCursor null
2. member → 404
3. non-member → 404
4. paginates via cursor
5. filter by action
6. filter by actorId
7. filter by since/until inclusive/exclusive
7b. malformed since (bad ISO) → 422
8. malformed orgId → 422
9. limit > 200 clamped (test via stub: pass limit=500, assert returned events.length <= 200); limit = 0 → 422; limit = -1 → 422
10. actor_user_id NULL on event row → email = null but event listed
11. prev_hash / this_hash passed through

**Steps:** TDD, commit.

---

## Task 6: GET /cost route (JSON) + Tests 12-18

Route at `apps/web/app/api/dashboard/orgs/[orgId]/cost/route.ts`. See spec for code.

Stub extension `org_cost_report` mirrors SQL: enforce p_group_by === 'user' or raise bad_group_by; authorize via memberships; aggregate runs in stub; LEFT JOIN auth.users for emails; return { rows, total, period }.

Update `mapPostgresError` MAP to add `bad_group_by: 422, bad_period: 422, bad_cursor: 422` (though bad_period is route-side, mapping for safety).

**Codex plan-pass WARNING — period response shape.** Route normalizes RPC response: replace `period: { since: timestamptz, until: timestamptz }` with `period: { since: 'YYYY-MM', until: 'YYYY-MM', sinceTs: ISO, untilTs: ISO }` so CSV filename builder and UI period picker have stable strings. RPC return is internal; route is the public contract.

**Codex plan-pass WARNING — frozen time on default-period tests.** Use `vi.useFakeTimers(); vi.setSystemTime(new Date('2026-04-15T12:00:00Z'))` before any test that exercises the default-period branch (Test 12b). Restore with `vi.useRealTimers()` in afterEach.

**Tests 12-18:**
12. admin GET current month → 200 with rows + totals
12b. default period when no params (current UTC month, frozen time)
13. member → 404
14. malformed YYYY-MM → 422 bad_period
14b. since > until → 422 bad_period
15. since=until same-month → 200 (valid; sinceTs < untilTs after the +1 month conversion)
16. deleted_at runs excluded
17. cost_usd NULL rows treated as 0; deleted user (no auth row) → email null but row listed
18. empty period → 200 rows: [], total all zeros

**Steps:** TDD, commit.

---

## Task 7: GET /cost.csv route + Tests 19-22

Route at `apps/web/app/api/dashboard/orgs/[orgId]/cost.csv/route.ts`. See spec for code.

**Tests 19-22:**
19. admin GET → 200, content-type text/csv; charset=utf-8, content-disposition with filename
19b. **codex plan-pass WARNING fix:** parse Content-Disposition header; extract value of `filename=` after the `attachment;` prefix. Assert that *extracted filename* contains no `"` (already stripped), `\r`, `\n`, `;` — NOT the full header (which has `;` separator). The filename itself must match `cost-<UUID>-YYYY-MM-YYYY-MM.csv`.
20. CSV escaping correct (comma, quote, newline in email); CRLF row terminator
21. non-admin → 404
22. column order: user_id,email,run_count,cost_usd_sum,duration_ms_sum,total_bytes_sum

**Steps:** TDD, commit.

---

## Task 8: Pages + components + sidebar

- [ ] `app/dashboard/admin/audit/page.tsx` — Server Component, role gate, RPC call, hand to `<AuditTable>`. `export const dynamic = 'force-dynamic'`.
- [ ] `app/dashboard/admin/cost/page.tsx` — Server Component, role gate, RPC call, hand to `<CostTable>` + `<CostPeriodPicker>`.
- [ ] `<AuditTable>` (client) — table + cursor pagination via fetch to `/audit?cursor=...`.
- [ ] `<AuditFilterBar>` (client) — single-select action dropdown, actor dropdown, date range, syncs to URL.
- [ ] `<CostTable>` (client) — per-user rows + total row + Download CSV anchor.
- [ ] `<CostPeriodPicker>` (client) — month-pair selector.
- [ ] `app/dashboard/admin/layout.tsx` — add Audit + Cost links.
- [ ] **Integration tests 23-26** at `__tests__/integration/admin-audit-cost-pages.test.tsx`

**Codex plan-pass WARNING — page-RPC contract.** Pages MUST resolve `orgId` via the same Phase 5.1 pattern: query memberships for caller's first admin/owner row; 404 (notFound) if none. Pages call the same RPCs the routes call (NOT the routes themselves) — no internal HTTP roundtrip. Period parsing for cost page: use `parsePeriod(searchParams.since, searchParams.until)`; route validation logic is duplicated (small, and the alternative is shared helpers). Cursor parsing for audit page: same — use `decodeCursor`. Document this duplication as accepted; future refactor to a shared `lib/dashboard/audit-cost-server.ts` is a follow-up.

---

## Task 9: Privilege + hardening grep test (Test 27)

`apps/web/__tests__/api/dashboard/orgs/audit-cost-privilege.test.ts` — readFileSync the migration; assert:
- `REVOKE ALL ... FROM PUBLIC, anon, authenticated`
- `GRANT EXECUTE ... TO service_role`
- NO `GRANT EXECUTE ... TO authenticated` (regression guard)
- Both functions declare `SECURITY DEFINER`
- Both functions declare `SET search_path = public, audit, auth, pg_temp` (codex plan-pass CRITICAL hardening)
- References to `audit.events` are schema-qualified (codex plan-pass CRITICAL)
- Phase 4's `20260508120000_phase4_runs_metadata.sql` exists in `data/deltas/` (cost_usd dependency)

**Steps:** Commit.

---

## Task 10: Version bump + push + PR

- [ ] `package.json` -> `6.3.0-pre.8`
- [ ] CHANGELOG entry summarizing 2 RPCs, 3 routes, 2 pages, 27 tests, codex passes folded.
- [ ] `npm run typecheck --workspace apps/web`, `npm run build --workspace apps/web`, `npm test --workspace apps/web` — all green.
- [ ] `git push -u origin feat/v7.0-phase5.2-audit-cost`
- [ ] `gh pr create --title "feat(v7.0-phase5.2): audit log viewer + cost reporting"` with body listing routes/RPCs/test count/codex passes/operator follow-up to run /migrate.

---

## Self-review

- **Spec coverage:** every requirement has a route + test
- **No placeholders:** all code samples in spec; this plan refers to spec for full bodies + adds concrete test specs
- **Type consistency:** RPC return shape `{ events, nextCursor }` and `{ rows, total, period }` consistent across SQL stub and routes
- **Skip notes:** Vercel preview / migration / e2e all deferred per Phase 1 lesson

## Skip directives (per Phase 1 lesson)

- **Skip Vercel preview deploy** — agent env doesn't have Vercel access; CI does the real verification.
- **Skip running migration locally** — `Delegance migrate flow regression` workflow validates in CI.
- **Skip end-to-end browser test** — jsdom is the integration ceiling.
