Module: database/repositories/activityRepo

Delete all activities in a workspace.

SEC-007: Compute the next prevHash from the previous row's hash and the current row's content. Exported for tests and re-used by verifyAuditChain so a single implementation is the source of truth (any drift between INSERT and verify breaks the chain immediately rather than silently).

prevHash_i = sha256(prevHash_{i-1} ++ JSON.stringify(rowMinusHash_i))

The leading hash for the very first row is the empty string.

Count DISTINCT testId across activity rows matching the filter (AUTO-003b).

Used by the approval-stats 7-day revert-rate calculation, which asks "how many distinct tests were auto-approved in the window?" and "how many distinct tests were revoked in the window?" — set sizes, not row counts, because a test that auto-approved twice in the window should still count as one.

Previously computed by fetching up to 10,000 rows via getFiltered and building two Sets in JS; at ~1 KB per row that's ~10 MB of transferred data per project per call. This query returns a single integer, and the activities(type, projectId, createdAt) access pattern is index-friendly on both adapters.

The metaIsAutoApproved filter matches the JSON-encoded flag meta.wasAutoApproved = true via a portable LIKE on the serialised meta TEXT column (migration 018). LIKE is case-sensitive on SQLite and case-insensitive on PostgreSQL (the adapter rewrites LIKE→ILIKE) — fine here because logActivity always writes the lowercase "wasAutoApproved":true shape, so case-variation isn't possible on real data. Using LIKE instead of json_extract keeps the query portable across the SQLite/PostgreSQL adapters without a dialect branch (INF-001).

Count activities with optional workspace/project scope.

Create an activity entry.

Dedup (SEC-007)

For high-volume read-shaped events (audit.read, audit.export, auth.login.failed), consecutive identical events within the dedup window collapse into a single row with count++ and lastAt = now. Matches Splunk / CloudTrail / Auth0 industry practice — every event is still counted (anti-exfiltration signals intact) but the feed isn't dominated by duplicate noise. PCI-DSS 10.5.3 permits this provided attribution is preserved. Window is AUDIT_DEDUP_WINDOW_SEC (default 60, 0 disables).

Dedup is automatically DISABLED when AUDIT_HASH_CHAIN=true — mutating lastAt/count on a persisted row would invalidate its prevHash and break the verification walk. With chain on, every event is its own row (the chain is the higher-priority compliance signal).

When AUDIT_HASH_CHAIN=true, computes prevHash from the last row in the same workspace and stores it in the same transaction as the INSERT. Otherwise prevHash is persisted as null (chain disabled — default).

Delete all activities for a project.

Get all activities.

Get all activities as a dictionary keyed by ID.

Get activities filtered by type for dashboard analytics. Only returns type, status, createdAt — skips detail, names, etc.

Get filtered activities.

after / before accept ISO-8601 strings (matching the column's storage format). The comparison is lexicographic on ISO strings, which is correct for the YYYY-MM-DDTHH:MM:SS.sssZ shape Date.toISOString() produces.

offset pairs with limit for cursor-style "Load more" — clients pass the count of rows they've already rendered. Combined with the default ORDER BY createdAt DESC, this gives a stable forward window even when new rows arrive between fetches (the ordering key is the row's own timestamp, so a new row only shifts the cursor on the first page, not subsequent pages).

SEC-007: workspace-scoped audit-log fetch with cursor pagination (ENH-010).

Cursor pagination — vs. LIMIT/OFFSET — is stable under concurrent writes: a new row arriving between two pages cannot shift the window because the cursor is the row's own createdAt, not its ordinal position. The cursor is opaque to the caller (an ISO string), so the API surface can later change to a composite cursor without a breaking change.

The ordering tuple is (createdAt DESC, id DESC) — matches the INSERT- side tiebreaker in create() so sub-millisecond bursts stay deterministic.

Filters are AND-combined and all parameterised. types is an array; an empty array means "no type filter".

SEC-007: retention sweep — delete activity rows older than days days.

Called from the scheduler's daily retention task. The cutoff is computed in JS (ISO 8601 UTC) and passed as a parameterised string so the comparison stays lexicographic — matching the column's storage format and the existing after / before filters in getFiltered.

Caller is responsible for the SOC-2 floor (≥ 90 days or 0 to disable); this function simply does what it's told. Boot-time validation in backend/src/index.js rejects AUDIT_RETENTION_DAYS values below 90.

SEC-007: Walk the audit-log chain for a workspace in chronological order and verify each row's prevHash matches the recomputed sha256(prev.prevHash + JSON.stringify(rowMinusHash(row))).

Uses the shared computePrevHash helper so any drift between the INSERT path and the verification path is impossible — both call the same function.

Returns { verified: true, total, chainStartedAt } on a clean walk, or { verified: false, firstBrokenRowId, total } at the first mismatch. An empty workspace is trivially verified.

Pre-chain rows (prevHash IS NULL)

When AUDIT_HASH_CHAIN=true is enabled on a deployment with pre-existing activity rows, those legacy rows have prevHash = null and were never hash-linked. Reporting a tamper break at the first such row would be a false positive — the rows aren't tampered, they predate the feature. So the verifier skips the leading prevHash IS NULL rows and begins the walk at the first row with a non-null prevHash. chainStartedAt surfaces the createdAt of that first chained row so operators can see how much history is outside the cryptographic coverage.

Caller is expected to gate this behind AUDIT_HASH_CHAIN=true — when the chain is disabled, every row has prevHash = null and the walk would skip everything (returning { verified: true, total, chainStartedAt: null }). The route handler in backend/src/routes/system.js short-circuits to { chainDisabled: true } in that case to avoid the empty-walk ambiguity.

Re-parse the JSON meta column into a plain object for callers. Tolerant of legacy rows where the column is null/empty/non-JSON — those rows predate migration 018 and surface as meta: null.