Story 1163: audit query API — two surfaces, scope timelines, seq cursors (S4)

Fourth implementation story of the locked audit-log design (pramaan-docs docs/designs/audit-log/README.md §4.5 / S4, Spike-1152 outcome). S1 (Story-1160) gave the ops.audit_log table its target shape and the seq-aligned, firm-leading indexes; S2 (Story-1161) gave it the typed audit.emit stamping point. S4 is the read side: the firm-facing query API the Settings audit pages (S5) call. It adds only new route and query-service files — it does not instrument any mutation call-site (that is S3) and reuses the audit domain and registry unchanged.

Two surfaces, split by the partial indexes

The design stores human and agent events in one physical table and separates them at read time with the actor_kind partial indexes (Q4). The API mirrors that exactly:

Route	Rows	Index
`GET /app/audit/human`	`actor_kind IN ('firm_user','platform_staff')`	`ix_audit_log_firm_human_seq`
`GET /app/audit/agent`	`actor_kind = 'agent'`	`ix_audit_log_firm_agent_seq`

The path prefix is the repo-conventional /app/...; the design writes /audit as shorthand. Operation IDs derive from the handler names per the app factory's generate_unique_id_function.

Scope timelines via the chain bridge (§4.5)

Only matter and area get their own scope index in v1; proceeding / filing / document timelines ride the matter index plus a residual filter, because the chain CHECK guarantees a deeper-scope event always carries its matter_id. The routes encode that bridge by nesting under the matter, so matter_id is always present to lead ix_audit_log_firm_matter_seq:

GET /app/audit/matters/{matter_id}
GET /app/audit/matters/{matter_id}/proceedings/{proceeding_id}
GET /app/audit/matters/{matter_id}/filings/{filing_id}
GET /app/audit/matters/{matter_id}/documents/{document_id}
GET /app/audit/subjects/{kind}/{subject_id}      # ix_audit_log_firm_subject_seq

The subject path token is {kind} rather than {subject_kind} on purpose: a path param named subject_kind would collide with the shared subjectKind filter, which FastAPI then mistakes for the path param and rejects for having a default.

seq cursor pagination — no OFFSET, no created_at sort

The cursor is the design's seq (strictly monotonic, clock-independent), surfaced opaquely as the last row's seq. Every query is WHERE firm_id = ? AND … ORDER BY seq DESC LIMIT n; because every index trails in seq DESC, newest-first pages are pure index range scans with no sort node and no OFFSET.

preds = [AuditLog.firm_id == firm_id, *base_predicates, *filters.predicates()]
if cursor: preds.append(AuditLog.seq < int(cursor))   # older than the last page
select(...).where(and_(*preds)).order_by(desc(AuditLog.seq)).limit(limit + 1)

Filters, validated against the registry

The shared AuditFilters dependency carries actor, verb, category, subject kind, area, and a date range (the surfaces add matter as a first-class param). Registry-governed values (verb, category, area, subject kind) are validated against the S2 registry frozensets — a typo is a 400 at the boundary, never a silently empty page. The verb filter matches the part after the dot via split_part(action, '.', 2) as a residual predicate.

Access control: RLS firm-isolation

The design fixes access at one boundary — "Log access is itself RLS-protected (firm members only)" (§9.4). The routes use AppDbDep, which sets app.current_firm_id; the table's firm_isolation SELECT policy then restricts every row to the caller's firm. No extra app-layer role gate is added, matching the acceptance criterion ("firm members see own firm only") and the repo's settings-read convention. The stricter firm-admin gate on the legacy FEAT-268 /app/firm/audit feed is a separate surface and is left untouched.

EXPLAIN-verified query shapes (codex finding)

tests/integration/test_audit_query_explain.py proves the seq-index property structurally. With enable_seqscan and enable_bitmapscan disabled, the planner is forced onto plain index scans, so a combined-filter shape that still plans without a Sort can only be doing so because a single seq-aligned index already provides the ordering. Each shape (human/agent combined, matter, proceeding/document bridge, subject) asserts: no Sort node, no Seq Scan on ops.audit_log, and an ix_audit_log_*_seq index as the access path.

Tests

Surfaces + isolation — human vs agent split by actor kind; an ordinary firm_member can read (RLS gate, not admin); another firm's events are invisible on both surfaces.
seq pagination — newest-first, opaque cursor, clean stop; a non-integer cursor is a 400.
Filters — category / verb / subjectKind / actor narrow the set; an unknown verb is a 400.
Timelines — matter, document (chain bridge), and subject timelines return exactly their scoped rows; an unknown subject kind is a 400.
EXPLAIN — every combined-filter shape is an ordered seq-index scan with no Sort.

One-sentence recall

S4 reads the audit log the way S1 indexed it: two actor-split surfaces and a set of scope/subject timelines, all paginated by seq so each query is a firm-leading, seq-ordered index scan — verified by an EXPLAIN test, scoped by RLS to the caller's firm.