Paginate the audit log for org admins

Java · Spring Boot · intermediate · modification

Adds the audit log endpoint behind the admin console's new Audit tab. It pages with a keyset cursor instead of `OFFSET`: each page asks for the rows after the last one it returned, ordered by `(createdAt, id)` descending, so page 500 costs about what page 1 does, and fetching one extra row tells us whether there is a next page without a count query. The cursor is the last row's timestamp in microseconds and its id, base64url-encoded, and anything that does not decode is a 400. Rows come back as a DTO built by a constructor expression with a left join to the actor, so there are no entities, no lazy loading and no query per row. Migration V27 adds the `(org_id, created_at DESC, id DESC)` index the query walks. Tests cover a walk across events that share a timestamp, system events, org isolation and the rejected limits and cursors.

Workspace SaaS backend on Spring Boot 3.3 (Java 21), Spring Data JPA 3.3 and Hibernate 6.5 on PostgreSQL 16; Jackson writes `Instant` as ISO-8601 (Spring Boot's default). Existing and unchanged: `AuditEvent` (`audit_events`: `id` bigint identity, `org_id`, nullable `actor_id`, `action`, `target_type`, `target_id`, `created_at` `timestamptz`; constructor `AuditEvent(Long orgId, Member actor, String action, String targetType, String targetId, Instant createdAt)`) is written only by the existing `AuditLogger`, which sets `createdAt` from the server clock when the event happens; events are append-only, never updated or deleted (retention is out of scope), and an actor always belongs to the event's org. `Member` (`members`, constructor `Member(Long orgId, String displayName)`) is deactivated, never deleted. `org_id` columns are plain bigints without a foreign key (orgs live in the accounts service). `audit_events` has only its primary key index today. `SecurityConfig` requires authentication on `/api/**`, enables `@EnableMethodSecurity` and puts a `MemberPrincipal(long memberId, long orgId)` in the context of every authenticated request; org admins carry `ROLE_ADMIN`. A `ResponseStatusException` answers with Spring Boot's default error body. Flyway runs at startup; the newest migration is V26, and the service runs as a single instance whose deploys stop the old version before the new one starts (a short downtime is accepted). Tests run against a Testcontainers PostgreSQL 16 with the Flyway migrations applied (`src/test/resources/application.properties`, unchanged). The admin console's Audit tab ships in a separate frontend PR; nothing else calls this endpoint.

Requirements

Files touched

--- src/main/java/com/acme/workspace/audit/AuditEventRepository.java
 package com.acme.workspace.audit;
 
+import java.time.Instant;
+import java.util.List;
+import org.springframework.data.domain.Limit;
 import org.springframework.data.jpa.repository.JpaRepository;
+import org.springframework.data.jpa.repository.Query;
+import org.springframework.data.repository.query.Param;
 
 public interface AuditEventRepository extends JpaRepository<AuditEvent, Long> {
+
+    @Query("""
+            select new com.acme.workspace.audit.AuditEventView(
+                e.id, e.createdAt, e.action, e.targetType, e.targetId, a.id, a.displayName)
+            from AuditEvent e left join e.actor a
+            where e.orgId = :orgId
+            order by e.createdAt desc, e.id desc
+            """)
+    List<AuditEventView> firstPage(@Param("orgId") long orgId, Limit limit);
+
+    // A row comparison rather than the equivalent OR, so PostgreSQL can use it as
+    // the index condition and start the scan right after the cursor.
+    @Query("""
+            select new com.acme.workspace.audit.AuditEventView(
+                e.id, e.createdAt, e.action, e.targetType, e.targetId, a.id, a.displayName)
+            from AuditEvent e left join e.actor a
+            where e.orgId = :orgId
+              and (e.createdAt, e.id) < (:createdAt, :id)
+            order by e.createdAt desc, e.id desc

Review this PR

Java practice