Audit Logging — QA Plan

Status: Shipped, unflagged since 2026-08-22. The highest-risk areas are (a) owner-only authorization on the read surface, (b) actor attribution across the request / cron / webhook boundary, and (c) the retention cron’s archive-before-delete ordering, which is the only destructive path in the feature.

Prerequisites:

Migration 0102 applied (libs/db/drizzle/0102_glossy_domino.sql).
Clerk webhook endpoint subscribed to session.created, session.removed, session.revoked (plus the existing user.*). Without this, section 3 cannot pass.

Personas: usePersona('owner'), ('admin'), ('coach'), ('member'), plus a second org to prove isolation.

1. Capture is always-on

#	Scenario	Expected
1.1	Change a member’s role	Row appears in `audit_events` (verify via DB)
1.2	Open the viewer	The 1.1 row is visible
1.3	Force an audit insert failure (e.g. temporarily revoke insert on the table) and change a role	The role change succeeds; a `warn` appears in API logs; a Sentry event lands with `subsystem: audit`

1.3 is the single most important test in this plan. If the request fails, the whole design premise is broken.

2. Actor attribution

#	Scenario	Expected
2.1	Owner changes a member’s role from the web app	`actor_type='user'`, `actor_user_id` = owner’s `users.id`, `ip_address` populated, `request_id` matches the pino log line for that request
2.2	Staff record a member’s period-end notice	`actor_type='user'`, actor = the staffer
2.3	Period-end cancellation cron flips a subscription	`actor_type='system'`, `actor_user_id` NULL
2.4	Recurring-charge cron renews a subscription	`subscription.renewed`, `system`
2.5	Provider webhook activates a pending subscription	`subscription.created` recorded; actor is `system`, and `ip_address` is not the provider’s address
2.6	Request behind a proxy with `x-forwarded-for: a, b, c`	`ip_address` is `a` (first hop), not the socket address
2.7	Request with a 60-char IPv6 header	Stored value is truncated to 45 chars, no insert error

3. Clerk auth events

#	Scenario	Expected
3.1	A staff user (owner/admin/coach) signs in	One `auth.login` row per active staff membership that user holds
3.1b	A plain member signs in	No row anywhere. The fan-out is staff-only
3.1c	A user who staffs org A and is a member of org B signs in	Exactly one row, in org A. Org B’s viewer shows nothing
3.2	A coach belonging to two orgs signs in	Two rows, one per org. Neither org’s viewer reveals the other org
3.3	The same user signs out	`auth.logout`
3.4	Clerk sends `session.ended` (idle expiry)	No row. Only `removed` / `revoked` count as a logout
3.5	Staff user changes their email in Clerk’s UI	`auth.user_updated` in every org they staff, and the existing `syncFromClerk` behavior is unchanged. A member’s change records nothing (staff-only applies here too)
3.6	Clerk sends a session event for a `user_id` we’ve never mirrored	No row, no error, webhook still returns 200
3.7	A session payload carries `latest_activity.ip_address`	That IP lands on the row
3.8	A session payload carries no `latest_activity`	`ip_address` is NULL — not Clerk’s sender IP

4. Read surface: entry points

#	Scenario	Expected
4.1	Coach opens `/dashboard/audit-log`	Restricted copy; no request to `/audit-events` fires
4.2	Same, sidebar	No “Audit Log” entry for a coach
4.3	Owner opens `/dashboard/audit-log`	Viewer renders and the list request fires

5. Read surface: authorization

#	Scenario	Expected
5.1	Owner	200 with rows
5.2	Admin	403
5.3	Coach	403
5.4	Member	403
5.5	Non-member of the org	404 from `requireMembership`
5.6	Turn `rbac-v2` ON and repeat 5.1–5.4	Identical results. The `audit` row is the same in both matrices
5.7	Admin / coach in the web app	Sidebar entry absent; direct navigation shows the restricted copy
5.8	Export endpoint, as coach	403 (not just the list)

6. Cross-org isolation (ADR-0004)

#	Scenario	Expected
6.1	Seed events in org A and org B; owner of A lists	Only A’s rows, at every page
6.2	Same, with each filter applied in turn (category, action, actor, target, date, search)	Org scoping survives every filter
6.3	Owner of A passes org B’s id in the path	404
6.4	Export as owner of A	CSV contains no B rows

7. Filters, pagination, export

#	Scenario	Expected
7.1	Filter by each of the five categories	Only matching rows
7.2	Search by a partial action string	Matches on `action`
7.3	Search by an actor’s email	Matches rows that actor wrote
7.4	Search with a `%` or `_` in it	No SQL wildcard leakage into unrelated rows
7.5	Date range from = to = today	Includes events from later the same day (the UI widens `to` to 23:59:59.999)
7.6	`limit=500`	Response `limit` is 100
7.7	Page through 3+ pages	No duplicates, no gaps, newest first throughout
7.8	Export with filters applied	CSV row count matches the filtered `total`
7.9	Export a row whose metadata contains a comma, a quote and a newline	CSV parses correctly in Excel and Google Sheets
7.10	Export with 0 matching rows	Header-only CSV, no crash

8. Viewer UI

#	Scenario	Expected
8.1	Table renders time, actor, action, target, changes
8.2	A `system` row	Actor cell shows the localized “System” badge, not a blank
8.3	Expand a row with before/after	Both blocks render; values match the DB
8.4	Expand a row with `metadata` NULL	”No field-level detail recorded”, not an empty box
8.5	A row with IP and request id	Both shown in the popover
8.6	Type in search	Exactly one request after ~300ms, not one per keystroke
8.7	`he` locale	Full RTL; prev/next chevrons are not individually mirrored (layout flip carries the meaning)
8.8	`ru` locale	All strings translated; no English leaks
8.9	Narrow viewport (375px)	The table scrolls inside its own container; the page body does not scroll horizontally
8.10	Click Export	File downloads with an `audit-log-YYYY-MM-DD.csv` name
8.11	Export while the API is down	Inline error copy, button re-enables

9. Capture catalog spot-checks

Walk one scenario per action and verify the row’s category, action, target_type/target_id, and diff. Priority order:

membership.role_changed — before/after roles both present
membership.status_changed vs membership.removed — a flip to cancelled reads as removed
Profile-only PATCH on a member — no membership row written (the diff guard)
plan.updated with a price change — before.priceInCents / after.priceInCents present
plan.updated with only a name change — price absent from the diff
subscription.cancelled via direct cancel and via request approval — one row each, not two
subscription.plan_change_scheduled on a 409 (race with a pending cancellation) — no row
payment.charge_failed on a declined card — one row, status: 'failed', and metadata.errorClass set to a bounded value (never the gateway’s own text)
payment.charge_succeeded from a desk charge — exactly one row for the whole pending-then-completed pair, and the staffer’s description is absent from metadata
payment.provider_configured after a credential rotation — credentialsRotated: true, and no credential value or ciphertext anywhere in the row
organization.updated — only changed fields; joinToken never present
Member erasure — org row written and the platform audit_logs row still written; no PII in the org row

Item 10 is a security check, not a correctness check. Grep the whole metadata column for known credential key names (apiPassword, apiKeySecret, groupPrivateToken, rivhitApiToken, terminalNumber) and expect zero value hits — the key names may legitimately appear under metadata.config, but every value there must read [redacted].

payment.provider_configured with a config bag — keys preserved, every value [redacted]
Add a card, then remove it — payment.method_added / payment.method_removed, and no token or ciphertext anywhere in the row

9b. Charges and refunds (the choke point)

Every charge attempt must land exactly once, from every path. Run these against a sandbox provider and count rows per target_id.

#	Scenario	Expected
9b.1	Desk charge that succeeds	One `payment.charge_succeeded`; `metadata.source: 'manual_charge'`; actor is the staffer, not `system`
9b.2	Desk charge that the provider declines	One `payment.charge_failed`; `metadata.errorClass` bounded; `error_message` still verbatim on `payment_transactions`
9b.3	Renewal cron charges a due subscription	One `payment.charge_succeeded`; `metadata.source: 'recurring_renewal'`; `actor_type: 'system'`, `actor_user_id` NULL
9b.4	Renewal fails three times (past_due → debt)	Three `payment.charge_failed` rows, one per attempt; none of them names the provider’s message
9b.5	Clear debt from the desk	One row; `metadata.source: 'debt_clear'`
9b.6	Plan change with an immediate proration charge	One row; `metadata.source: 'plan_change'`; plus the separate `subscription.plan_change_applied`
9b.7	Member completes a hosted checkout	One `payment.charge_succeeded` when the webhook settles the pending row, not one at checkout and one at settlement
9b.8	Provider re-delivers the same `payment.completed` webhook	No second row — the prior-status guard suppresses it
9b.9	Zero-price plan renewal	No charge row at all (the cron skips the transaction entirely)
9b.10	Superseded checkout cancelled (`status: 'cancelled'`)	No charge row — a supersede is not a resolved attempt
9b.11	Automatic refund	One `payment.refund_issued`; no `payment.charge_*` row from the refund transaction or the `refunded` flip
9b.12	Manual refund: open the task, then complete it	Two rows, `metadata.stage` `initiated` then `settled`
9b.13	Refund issued in the provider’s own portal	One `payment.refund_issued`, `actor_type: 'system'`, `capability: 'provider'`, `ip_address` NULL
9b.14	Platform-admin refund from the Taikan admin app	One `payment.refund_issued` in the gym’s trail, `capability: 'platform_admin'`

Then grep the whole metadata column across every payment.charge_* row for a card number fragment, a cardholder name and any substring of a provider error message. Expect zero hits: the only failure detail that may appear is the errorClass token.

9c. Destructive staff actions (`operations`)

#	Scenario	Expected
9c.1	Cancel a class session with 12 booked members	One `class_session.cancelled`; `cancelledBookings: 12`; no per-booking rows
9c.2	Delete that session	`class_session.deleted` in the org trail and the existing `class_session.delete` row still in the platform `audit_logs`
9c.3	Bulk-delete 30 sessions via a filter	One `class_session.bulk_deleted` keyed on `batchId`, with `deletedCount: 30`, the `sessionIds` array and the `filter`
9c.4	Staff cancel one member’s booking	`booking.cancelled_by_staff`, `creditRefunded` matching whether the seat was confirmed
9c.5	The member cancels their own booking	No row — self-service is deliberately out
9c.6	Delete a workout, a program template, a program, a class type	`workout.deleted`, `program_template.deleted`, `program.deactivated`, `class_type.deactivated`; the two `deactivated` ones say so rather than claiming a deletion
9c.7	Archive a form template	`form_template.archived`
9c.8	Erase one lead	`lead.deleted` with an id-only `before`; grep the row for the lead’s name, email and phone and expect zero hits
9c.9	Bulk-erase 40 leads, 5 of them already converted	One `lead.bulk_deleted`: `requestedCount: 40`, `deletedCount: 35`, `skippedCount: 5`, `leadIds` listing the 35
9c.10	Bulk-erase where every id is converted or missing	No row — nothing was destroyed
9c.11	Delete an announcement you wrote, and one someone else wrote	Two rows; `authoredByActor` `true` then `false`
9c.12	A coach attempts an owner/admin-only delete and is refused	No row — a refusal is not a change
9c.13	Filter the viewer by “Operations” in `en`, `he` and `ru`	Every action, target and field label translated; no raw dot-strings in the label column

9a. Self-auditing (access to the trail)

#	Scenario	Expected
9a.1	Owner opens the audit log page	One `audit.viewed` row, `settings` category, actor = owner, `metadata.filters` reflects the active filters
9a.2	Owner then filters, paginates and searches within 15 minutes	Still one `audit.viewed` row — coalesced
9a.3	Wait past the coalescing window, reload	A second `audit.viewed` row
9a.4	The `audit.viewed` row itself	Does not appear in the result set of the request that created it (recorded after the query)
9a.5	Owner exports twice in a row	Two `audit.exported` rows — exports are never coalesced
9a.6	Inspect an `audit.exported` row	`metadata.rowCount` matches the CSV’s data-line count; `metadata.filters` matches what was applied
9a.7	Coach hits the list endpoint	403 and one `audit.access_denied` row, `reason: 'insufficient_role'`, `role: 'coach'`
9a.8	Admin hits the export endpoint	403 and an `audit.access_denied` row with `endpoint: 'export'`
9a.10	A user who is not a member hits `/organizations/<org>/audit-events`	403 and no row. Deliberate: recording it would let any authenticated user write into any org’s trail
9a.11	Break the audit insert, then list as owner	The list still returns 200. A self-audit failure must never 500 a page load

9b. CSV injection

#	Scenario	Expected
9b.1	Set a member’s first name to `=cmd\|'/c calc'!A1`, generate an event by them, export	The cell is prefixed with `'`; opening in Excel/Sheets shows text and evaluates nothing
9b.2	Repeat with names starting `+`, `-`, `@`, and a leading tab	Same neutralization
9b.3	A metadata value containing a comma, a quote and a newline	Still parses as one field — the quoting rules are unaffected by the guard

10. Retention cron

Run against a scratch DB. This is the only path that deletes.

#	Scenario	Expected
10.1	`CRONS_ENABLED` unset	Cron does not run
10.2	Seed rows at 25 months and 23 months old; run	Only the 25-month rows archived and deleted
10.3	Two orgs × two months of expired rows	Four R2 objects at `audit-archive/<orgId>/<YYYY-MM>.ndjson`
10.4	Inspect an archived object	Valid NDJSON, one JSON object per line, line count matches the deleted row count, and every column present (incl. `metadata`, `ip_address`, `request_id`, ISO `created_at`)
10.5	Confirm the bucket	Objects land in the compliance bucket, not the general one
10.4a	Seed a row exactly 24 months old; run	Not deleted — 24 months is a floor, and the filter is a strict `lt`
10.6	Make R2 reject the upload; run	Rows still in Postgres. Nothing deleted
10.7	10.6, then fix R2 and re-run	Rows archived and deleted on the second attempt
10.8	Hold the advisory lock in another session; run	Logs “already running”, no upload, no delete
10.9	Kill the process mid-sweep	Lock released on reconnect (session-scoped); next run resumes cleanly
10.10	Run with nothing expired	Logs “nothing older than the cutoff”, zero R2 calls

11. Regression surface

The instrumentation touched thirteen existing services. Re-run these existing suites and confirm the behavior they cover is unchanged:

make test-unit-api, make test-integration-api, make test-e2e-api
make test-unit-web, make test-integration-web, make test-e2e-web

Manually re-verify, since these paths grew a call:

Member invite → accept → activation (the MEMBERSHIP_ACTIVATED listener now also fires)
A full paid subscription purchase through the provider’s hosted page
A recurring charge cycle including one scheduled plan swap at the boundary
Permanent member erasure
Org settings save from /dashboard/settings

12. Rollout

Done. The audit-logging flag was merged permanently ON and deleted on 2026-08-22, so the viewer is live for every org and there is no flag-off rollback. Post-deploy check: walk sections 4–8 in prod as the owner.