feat(portal-bff): admin audit-log read endpoint + audit_reader-locked path #132
No Assignees
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: julien/apf_portal#132
Reference in New Issue
Block a user
Delete Branch "feat/portal-bff-admin-audit-query-endpoint"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Summary
First real consumer of the admin module —
GET /api/admin/audit, the paginated audit-log viewer named in ADR-0020's v1 catalogue. Gated by@RequireAdmin, reads through theaudit_readerPostgres role only, and emitsadmin.audit.queryon every call as the "fishing expedition" deterrent ADR-0020 calls out (§"Read actions are also captured … to deter fishing expeditions"). This is the BFF half of the audit-viewer chantier — the SPA screen lands later.What ships
AuditReaderSET LOCAL ROLE audit_reader. Symmetric withAuditWriter'sSET LOCAL ROLE audit_writer: the runtime role contract holds even when the BFF connection is otherwise privileged. UPDATE / INSERT / DELETE onaudit.eventsfail at the Postgres level regardless of what gets through the application layer.$queryRawUnsafe's positional params, never concatenated into the SQL string. Subject-prefix filter usesLIKEwith explicitESCAPE '\\'and escapes%/_/\in the literal so an admin-side wildcard can't masquerade as a meta-character.LIMIT/OFFSETpagination. Ordering iscreated_at DESC, id DESCfor deterministic page boundaries on identical timestamps (UUIDs break ties).limitatMAX_LIMIT(200) even if a caller bypasses the DTO — defense in depth on the BFF's event loop.AdminAuditQueryDtoeventTypeauth.sign_in).actorIdHashaudienceworkforce|customer.outcomesuccess|failure|denied.subjectPrefixLIKE 'prefix%', escaped literal.createdAtFromcreatedAtTolimitoffsetBound through Nest's global
ValidationPipe— unknown query keys are rejected byforbidNonWhitelisted(defends against query-string smuggling),transform: truecoerces numeric strings into numbers.AdminAuditController@Controller('admin/audit')+@RequireAdmin()at the class level. The handler:AuditReader.findEvents(filters).admin.audit.querywith{ filters, resultCount }so a reviewer can see exactly what the admin searched for and how many rows came back.@RequireMfa({ freshness: 600 })is intentionally not applied in v1: the admin surface already sits behind a freshly-MFA'd session at sign-in (per ADR-0020), and the per-query audit row is the deterrent. Adding@RequireMfalater is a one-line change — that's why the decorator was designed-in by PR #128.AuditWriter.adminAuditQuery()New typed method using
outcome=success. The read happened regardless of whether it matched rows; row count lives inresultCount. Anoutcome=deniedfrom this surface is reserved for the day we add per-row authZ.Operational notes
SET LOCAL ROLEpattern (per ADR-0018): the BFF talks to Postgres on the sharedDATABASE_URLpool and switches role per-transaction. In production the audit-write pool is already split viaAUDIT_DATABASE_URL; a dedicatedaudit_reader-only pool is a future follow-up if read-side isolation is desired (the role-locking on the shared pool already prevents privilege bleed at the Postgres level).Test plan
pnpm nx test portal-bff— 308 specs pass (was 278; +30: AuditReader 10, AdminAuditController 6, AuditWriter typed event 2, DTO validation 12).pnpm exec nx affected -t format:check lint test build --base=origin/main— clean (the pre-existing_res/_nextwarnings inrate-limit.middleware.tsare unrelated).'; DROP TABLE events; --aseventType) — value lands in the params array, SQL stays templated.%,_,\insubjectPrefixare escaped to their literal form.adminEntra role assignment. Once both exist:curl --cookie 'portal_admin_session=…' /api/admin/audit?eventType=auth.sign_in&limit=10returns the most recent sign-ins andpsqlshows the matchingadmin.audit.queryrow inaudit.events.First real consumer of the admin module — paginated audit-log viewer per ADR-0020's v1 catalogue, gated by @RequireAdmin and emitting admin.audit.query on every read as the "fishing expedition" deterrent called out in ADR-0020 §"Audit log captures … Read actions are also captured … to deter fishing expeditions". What ships - AuditReader (apps/portal-bff/src/admin/audit-reader.service.ts): - SET LOCAL ROLE audit_reader inside the transaction — symmetric with AuditWriter's SET LOCAL ROLE audit_writer. The runtime role contract holds even when the BFF connection is otherwise privileged; UPDATE/INSERT/DELETE on audit.events fail at the Postgres level. - Parameterised SELECT only — never concatenates filter values into SQL. Subject prefix uses LIKE with explicit ESCAPE and escapes %/_ in the literal so an admin-side wildcard can't masquerade as a meta-character. - COUNT(*) + LIMIT/OFFSET pagination. Order: created_at DESC, id DESC for deterministic page boundaries on identical timestamps. - Hard caps limit at MAX_LIMIT (200) even if a caller bypasses the DTO — defense in depth on the BFF's event loop. - AdminAuditQueryDto (apps/portal-bff/src/admin/audit-query.dto.ts): - Filters: eventType, actorIdHash, audience, outcome, subjectPrefix, createdAtFrom/createdAtTo (ISO-8601). - Pagination: limit (1..200, default 50), offset (default 0). - Wired through Nest's global ValidationPipe so unknown query keys are rejected (forbidNonWhitelisted) and limit is bounded. - AdminAuditController: - GET /api/admin/audit, @RequireAdmin at the class level. - Forwards the validated DTO to AuditReader, then emits admin.audit.query with { filters, resultCount } as payload so a reviewer can see what the admin searched for. - @RequireMfa intentionally NOT applied in v1: the admin surface already sits behind a freshly-MFA'd session at sign-in, and the per-query audit row is the deterrent. Adding @RequireMfa later is a one-line change. - AuditWriter gains adminAuditQuery() typed method emitting the event with outcome=success (the read happened; whether it returned rows is captured separately in resultCount). Tests: +30 specs (AuditReader 10, AdminAuditController 6, AuditWriter typed event 2, DTO validation 12). All 308 BFF specs pass.