sciagent/docs/audit-log-manager-plan.md

# Audit Log Manager — Admin Planning Document

This plan describes the **Audit Log Manager**: an admin-only surface for tracing and monitoring **per-user activity** in the web application, with **time** as the primary navigation axis. It is derived from and must stay consistent with [`assets/docs/audit-log-implementation.md`](../assets/docs/audit-log-implementation.md) (schema, `record_audit`, role-based logging rules, and Step 6 admin viewer).

---

## 1. Goals

| Goal | Description |
|------|-------------|
| **Forensic traceability** | Answer: “What did user *U* do, and when?” and “What happened to entity *E* between *T₁* and *T₂*?” |
| **Time-first exploration** | Default and sort order anchored on **`occurred_at`** (UTC stored as `TIMESTAMPTZ`; display in admin’s locale or a configured org timezone). |
| **Tamper-aware source** | Queries read from append-only `audit_events` (app role: `INSERT`/`SELECT` only per implementation guide). |
| **No scope creep** | v1 is **query + view**, not anomaly detection, not primary shipping to external log stacks (see implementation guide §9). |

---

## 2. Relationship to backend data model

The implementation guide defines a unified **`audit_events`** table keyed by time:

- **`occurred_at`** — canonical **timestamp** for all list, filter, and timeline views; indexed with `actor_user_id` and other dimensions.
- **Actor columns** — `actor_user_id`, `actor_email`, `actor_role` (denormalized at event time).
- **Target** — `action`, `entity_type`, `entity_id`, optional `before` / `after` JSONB snapshots, `metadata`, `request_id`.

**Note:** The repository may also contain legacy `audit_log` / trigger-based patterns. The Audit Log Manager should target the **new** `audit_events` model from the implementation guide once migrated; until then, scope API field mapping explicitly so the UI contract does not depend on legacy tables.

---

## 3. Admin API design

### 3.1 Endpoint

- **`GET /api/v1/admin/audit`** — admin role check on router **and** service layer (mirror implementation guide §8).

### 3.2 Query parameters (time + filters)

| Parameter | Purpose |
|-----------|---------|
| **`from`** | Inclusive lower bound on `occurred_at` (ISO-8601 / RFC3339, timezone-aware). |
| **`to`** | Exclusive or inclusive upper bound (pick one, document in OpenAPI; default: *inclusive* end-of-day if date-only strings are allowed). |
| **`actor_user_id`** | Filter by user UUID (strong identifier for “this user’s timeline”). |
| **`actor_email`** | Partial or exact match per product choice; prefer **exact** for predictable forensic use. |
| **`entity_type`**, **`entity_id`** | Narrow to one record’s history. |
| **`action`** | One or many `audit_action` values (`create`, `read`, `update`, `delete`, `login`, `logout`, `login_failed`). |
| **`request_id`** | Optional: correlate all events from one HTTP request. |
| **`page`**, **`page_size`** | Paginated results; cap `page_size` (e.g. 100) for performance. |
| **`sort`** | Default **`occurred_at,desc`**; optional `asc` for chronological “playback” within a window. |

### 3.3 Response shape

Each item should expose at minimum:

- `id`, `occurred_at`, `actor_user_id`, `actor_email`, `actor_role`, `action`, `entity_type`, `entity_id`, `metadata`, `request_id`, and flags or URLs indicating presence of `before` / `after` (full JSON may be omitted from list rows and loaded on detail fetch if payloads are large).

Optional follow-up:

- **`GET /api/v1/admin/audit/{id}`** — full row including `before` / `after` for the detail panel.

### 3.4 Query strategy

- Always constrain by **`occurred_at`** when the admin does not pass `from`/`to` — e.g. default **last 24 hours** or **last 7 days** to avoid full table scans.
- Use existing indexes: `(actor_user_id, occurred_at DESC)`, `(entity_type, entity_id, occurred_at DESC)`, `(action, occurred_at DESC)`.

---

## 4. Admin UI — Audit Log Manager

### 4.1 Placement and access

- Route: **`/dashboard/admin/audit`** (or equivalent admin layout path).
- Wrapped in **`ProtectedRoute`** (or equivalent) **admin-only**; no feature flags that downgrade the check in production.

### 4.2 Layout

1. **Filter bar** — time range, user (email or ID), entity type/id, action multiselect, optional free-text on `metadata` paths (defer to v2 unless trivial; GIN on `metadata` supports it later).
2. **Results table** — columns: **Time** (`occurred_at`), **Actor** (email + role), **Action**, **Entity** (type + id), **Summary** (one-line from metadata or action), **Request** (link/filter by `request_id` if present).
3. **Detail drawer / panel** — on row click: show full metadata, **`before` / `after`** side-by-side; **diff in the browser** with a JSON diff library (per implementation guide — do not compute diff on server).

### 4.3 Time-centric UX presets

Presets speed up monitoring:

- Last 24 hours, last 7 days, last 30 days, custom range (date-time pickers with timezone label).
- **“This user’s activity”** deep link: `/dashboard/admin/audit?actor_user_id=…&from=…&to=…`.

### 4.4 Monitoring-oriented views (v1 vs later)

| View | v1 | Later |
|------|----|--------|
| Paginated event list with time filters | Yes | — |
| Sort by `occurred_at` | Yes | — |
| Per-user timeline (same data, fixed `actor_user_id`) | Yes | Optional density / grouping by day |
| Per-entity history | Yes | — |
| Export (CSV/JSON) for compliance | Optional | Regulated customers often need it |
| Live / websocket tail | No | Only if product requires real-time |

---

## 5. Timestamp semantics

- **Storage:** `TIMESTAMPTZ` in PostgreSQL; all APIs use ISO-8601 with offset or `Z`.
- **Display:** Convert to admin-visible timezone; show UTC in tooltip or secondary row for disputes.
- **Day boundaries:** If the UI sends date-only `from`/`to`, define explicitly (e.g. start of day / end of day in chosen timezone, then convert to UTC for the query).

---

## 6. Privacy, security, and correctness

- **Admin-only** on UI and API; verify with automated tests.
- **Do not** log secrets in `before`/`after` (enforce `to_dict()` hygiene per implementation guide §5.1 and §8).
- **Failed login** events: `actor_user_id` null, `actor_email` = attempted address; UI must not imply existence of accounts beyond what policy allows (align with auth UX).
- **Read volume:** Applicant read-audit is mostly suppressed by design; admins see **all** reads they are allowed to see — train admins that “quiet” applicant timelines are expected.

---

## 7. Implementation phases (aligned with implementation guide §7)

| Phase | Deliverable for Audit Log Manager |
|-------|-----------------------------------|
| **After Step 1** | Stub UI + API returns empty or mock; contract freeze. |
| **After Steps 2–4** | Real data for auth, user admin, initiative writes; filters and table usable for forensic queries. |
| **After Step 5** | Read events appear per role rules; admin sees broad read coverage. |
| **Step 6 (shipping target)** | Full **Audit Log Manager** as specified here: filters, pagination, detail panel, time presets. |
| **Step 7** | Retention/partitioning — Manager may add UI notice “data older than X may be purged per policy.” |

---

## 8. Acceptance criteria (Audit Log Manager)

- [ ] Admin cannot access `/api/v1/admin/audit` as non-admin (403).
- [ ] Listing is **ordered by `occurred_at`** descending by default; ascending works for a bounded window.
- [ ] Filtering by **`from` / `to`** reduces rows correctly and uses indexed columns.
- [ ] Filtering by **`actor_user_id`** or **`actor_email`** returns only that actor’s events.
- [ ] Row detail shows **`before` / `after`** without server-side diff; client-side diff renders correctly for large JSON (smoke test with sample initiative snapshot).
- [ ] Time preset “last 7 days” matches server interpretation (document timezone).
- [ ] No secrets appear in exported or displayed JSON (spot-check against `to_dict()` tests).

---

## 9. References

- Primary spec: [`assets/docs/audit-log-implementation.md`](../assets/docs/audit-log-implementation.md) — sections 2 (schema), 4 (`record_audit`), 5 (where writes happen), 6 (what to log per role), 7 Step 6 (admin viewer), 8 (verification).