A02 — Persisted observation and campaign performance reads

Status: in progress — historical status PR #96 is review-ready; cohort scope and database evidence remain gated. Updated: 2026-09-12. Assigned owner: smart analytics owner; bounded parser/route/test work delegated to lower-cost agents. Landed: backend #79 at 40b3263, backend #82 at 5f7c88b, and backend #83 at c3568c4. Protected API: backend #85, merged at 9ae45d7. Historical slice: backend #96, commit d113a4e, on feature/performance-historical-status, stacked on A07 backend #95; historical cohort scope is frozen for this slice.

Issues and acceptance covered

#52. The acceptance boundary is the implementation scope and completion checks below; see the issue acceptance matrix for parent coverage. Shared definitions: financial contract; proof anchors: evidence index.

Dependencies and blockers

A01 backend #78, merged f4ba2c3, is the base. The prospective source-time producer and bounded reader landed through #82/#83; historical status modes still require reliable #41/#42 event readers. #37/#41/#42 remain open as rechecked 2026-09-11, so merged code is not treated as issue completion. Dependable #36 ingestion and disposable-database evidence remain release coverage gates; old rows retain partial coverage.

Repository and expected files

Backend: implemented src/utils/analytics/performance.ts, performance.test.ts, and performanceLegacy.test.ts; later narrow wrappers in src/utils/viewSnapshots.ts, src/api/routes/stats.ts, src/api/routes/admin.ts or a dedicated read router/mount. Read-only access to ViewSnapshot/Submission and later #37 export.

Existing behavior and verified gap

Creator/admin charts read snapshots but also infer live/upload-day values, require adjacent days and clamp negatives. Sponsor already uses signed persisted deltas. Existing snapshots may be budget-clamped and timestamped at reconciliation, not acquisition (E02/E09/E10).

Proposed implementation boundary

One bounded observation query with pre-window baseline, deterministic ordering, UTC windows and platform/campaign/creator scope; daily/cumulative series, full-scope counts and top accepted videos. Historical status modes use committed events at observation time or fixed asOf, with deltas computed before status filtering; preserve explicitly named current-accepted cohort for sponsor comparisons. Keep current stock versus observation flow explicit. Expose creator/admin performance v2; leave earning series unavailable until A03/A06 supplies postings.

Expected API / data contract

Additive performance DTO with window/asOf/cohort/source-time coverage, opening baseline, nullable metric coverage, signed daily changes, cumulative observations, exact bucket count and bounded top-N. Preserve legacy chart payload via a documented adapter during rollout; do not infer missing history.

Required tests

Sparse/first/baseline-before-window snapshots; ingestion/source time difference; lower correction; equal timestamps; null/zero shares; 7-day exactly seven buckets; invalid platform/date/window; cross-user scope; stable top-N and query cardinality. A later approval/rejection cannot alter a fixed historical status-filter result; pre-history status is unknown, not inferred. PostgreSQL behavioral query test on disposable DB, not SQL-string assertions.

Suggested agent tier

Smart owner decides query/time/cohort semantics and reviews SQL; lower-cost agent can wire routes and validation after those contracts freeze.

Expected PR boundary and reason

The pure projection is landed in #79. The next prerequisite is #37's additive MetricObservation schema/writer on feature/metric-observation-producer; this is outside the 17 analytics PR boundaries and must remain narrowly owned by the existing observation issue. Freeze these facts: stable unique source ScrapeJob ID; immutable campaign/submission/webUser/platform attribution; required raw views plus nullable raw likes/comments/shares/saves; source observedAt plus recordedAt; global deterministic sequence; source_observed_at and raw-platform provenance; correction disposition derived against the prior applied raw view in recorded order; restrictive observation FKs blocking hard deletes. Dual-write normalized raw observations and legacy budget-clamped ViewSnapshot in one reconciliation transaction. Bridge-backed initial submissions write a normalized raw observation in their creation transaction; legacy/direct initial paths remain legacy partial. No legacy ViewSnapshot backfill and no fabricated scraped_at.

Migration first, then dual-write application code. Old workers/apps remain compatible; mixed versions may write only legacy, while new versions dual-write when a durable source exists. Rollback reverts readers/writer flag/application while retaining additive facts; never drop facts. Validate fresh and upgrade disposable DBs, replay/idempotency, concurrent guards, out-of-order/regression/null metrics, and legacy/mixed coverage. After #37 lands, add the bounded PostgreSQL reader/API PR behind current guards; historical status filters still require #41/#42. This producer prerequisite does not complete A02 or create a new analytics boundary.

RBAC requirements / TODOs

Preserve own requireAuth filters and existing admin ANALYTICS guard. TODO(RBAC): Require approved cross-creator performance access on staff campaign reads; do not expose private finance or moderation through public campaign routes.

Completion and reconciliation checks

Changing current rate cannot change observed series. Current values never fill a missing past bucket. Legacy snapshots retain explicit recording-time/budget-clamped coverage. Report-equivalent comparison passes for identical current-accepted cohort and window.

Record actual tests, source schema/contract versions, PR/merge SHA, manual evidence and residual coverage before changing status to review/complete. Any unexpected migration must first satisfy the migration gates; never bundle upstream financial writer work into this analytics unit.

Implementation evidence

• Landed producer: #82 merged to dev at 5f7c88b. Its focused five-suite run, build, schema validation and diff checks passed before merge. The guarded PostgreSQL suite remains unrun because no explicit disposable loopback _test database is configured.
• Landed normalized reader: #83 merged to dev at c3568c4. The adapter/reader/performance/legacy suites and build passed before merge. The reader is capped at 10,000 rows, retrieves one last pre-window baseline per submission, enforces caller/scope and asOf, reports prospective partial coverage, and never unions legacy snapshots. Its guarded PostgreSQL behavior test remains unrun pending the disposable database.
• Pure contract: scoped daily/cumulative observation projection with a last pre-window baseline, signed changes, exact UTC [from,to) buckets, deterministic (effective time, recorded time, sequence, ID) ordering, nullable metrics, explicit source/value provenance, and a 366-day limit.
• Cohorts: all_submissions, an explicitly supplied current_accepted set, and committed-event status_at_observation/fixed status_at_as_of. Deltas are computed before historical status filtering; pre-history state is unknown and missing review coverage makes measures unavailable. A transition is effective at its exact occurredAt, so an equal-time observation sees toStatus.
• Legacy adapter: maps only persisted ViewSnapshot values, labels recording-time and budget-clamped view provenance, preserves signed corrections and unavailable saves, and never uses upload dates, live current values, rates, or inferred traffic.
• Validation: #85 build and eight focused route/query/service/adapter/reader/projection suites pass. The full offline run is 43 passing / 5 existing environment-dependent failures against the deliberately unreachable database URL; no A02 suite failed. git diff --check passed.
• Protected API landed through #85: GET /api/stats/performance derives creator scope only from authenticated identity; GET /api/admin/analytics/performance preserves the existing staff ANALYTICS action. Both use strict reproducible UTC query bounds, normalized facts only, no-store responses, and only all_submissions or explicitly present-current current_accepted. The staff boundary carries the documented TODO(RBAC) marker; central mappings remain unchanged.
• Residual gate: do not mark A02 complete until #37/#41/#42 supply accepted persisted readers, a disposable-PostgreSQL behavior test passes, additive protected routes are wired, and report-equivalent current-accepted comparison is recorded. A10/A11/A13 remain locked.

Current historical-status slice

A02's active feature/performance-historical-status branch is backend PR #96 (d113a4e), stacked on #95. It carries the frozen historical cohort scope and composes historical status filtering only from the normalized committed review reader; it does not infer current state, issue a current-accepted query, or widen the platform/campaign contract. Per submission, events are ordered by (occurredAt, recordedAt, eventId); the first event before the observation is the baseline, while no prior event is unknown. A malformed event, campaign-conflicting event, or discontinuity makes the affected submission unknown. Validation passed 11 analytics/routes suites, creator-route checks, build, and diff check; no migration or RBAC map changed. The branch remains in progress pending the upstream #41/#42 producer/reader state, #68 normalized accountability coverage, and disposable loopback _test database behavior evidence. Keep #41/#42 and #91/#92/#93/#95/#96 open. A02 is not complete and A10/A11/A13 remain locked.