Stats Refresh Jobs

Exercise batches — #1590 deployed to Production (2026-09-14)

#1590 adds a transaction boundary inside large accounts. App commit a8e5871f and migration 20260915083000 passed local precheck and the scoped populated upgrade test. Both staging deployment 34771151454 and Production deployment 34771543350 succeeded. Production runs main 1711ae2e. Its executable files are byte-identical to a8e5871f; the intervening change only updated AGENTS.md. Main also passed additional static/build/artifact checks.

The previous one-owner boundary still allowed one transaction to create and drop temporary relations for every exercise. Dropping those relations did not release their relation locks before commit. A Production account with 658 sessions and 296 exercises exhausted shared lock memory (53200), leaving requested generation 637 ahead of applied generation 633. A one-owner transaction is not a workload cap.

For accounts with more than 100 candidate exercises, compute now runs preparation, sequential exercise batches of at most 100, and final aggregation in separate transactions. Preparation freezes the exact exercise list. Private typed outputs remain in the existing stats_output_* tables under the same job and lease, with the stage, next offset, source/policy, anchor, output revision and retry count in user_stats_projection_runs.payload._batch. The phase remains claimed until finalization produces a complete computed manifest. Accounts with at most 100 candidate exercises retain the single-compute response.

Compute response	Consumer action
computed	The existing publisher may validate and atomically publish the complete generation.
batch_pending with stage, next_offset, total_exercises	Commit and call compute in a new RPC/transaction. Do not publish the unfinished output.
batch_pending, reason=transaction_boundary_required	End this invocation; repeated calls within the same transaction cannot advance the cursor.
retry_pending with retry_not_before	Preserve committed predecessors and wait for the server retry time. No immediate retry or publication.
failed	Keep the existing failure/successor-scope recovery contract. No partial publication.

57014/55P03 during a resumed batch roll back that batch's savepoint and use the existing maximum-attempt and backoff settings. A changed source, anchor, base or private-output revision invalidates the attempt. Expired, unlocked batch runs may resume the same token and cursor only after checking those inputs and consuming a bounded retry; a new lease never adopts another lease's private output.

The Edge worker uses separate compute RPCs, yields on retry or its existing round budget, and reports batch_count, pending_count and retry_not_before. The synchronous adapters return deferred for a pending batch and do not count it as completed or validate incomplete output. Cron resumes committed work. Public projections and applied_version still change together in the final publication.

Within a batch, sequential projectors reuse scratch relations and reset their contents for each exercise. The commit between batches releases that batch's relation locks. The value 100 is a measured candidate boundary, not an unlimited safety guarantee: initial normalization, final aggregation/publication, total bytes and one exercise's long history still grow with the account. The configured compute/publish SQL budgets remain 60 s/6 s; Edge RPC role budgets are unchanged. See the rollout contract and the verification record for measured scales, environment differences and remaining verification.

The local precheck passed once in 9 min 24 s: static/unused/build/artifact checks, 3,799 passing unit tests with 148 conditional skips out of 3,947, 122 pgTAP files with 2,444 assertions, and 107/107 real DB Node tests with no skips. Large-history isolation and concurrent CRUD convergence probes also passed. Populated upgrade B restored ten old runtime functions and removed two new helpers on the current 222-migration table shape; it reproduced 53200, applied the complete repair migration and reached generation convergence while preserving source hashes. It is distinct from replaying the complete former 221-migration schema.

At 2026-09-14 02:28:52 KST, Production stale users fell from 1 to 0, unresolved 53200 jobs from 1 to 0, open jobs were 0, and the affected account advanced from applied generation 633 to requested=applied=639. Its recovery job completed with no error (cumulative compute 43,853.488 ms; publish 1,984.670 ms). The three screen RPCs subsequently returned contract 4 and stale=false in a read-only DB transaction. These checks are distinct from HTTP or physical-device browser verification. The approved direct integration used no PR/Full CI; Production browser journeys remained skipped by the existing if: false policy. See the linked verification record for exact scope and remaining unmeasured limits.

D10 bounded maintenance candidate — v0.18.0 (#1418)

Repair, catalog invalidation and date rollover use persistent source-row cursors, catalog tokens and a due-time index. Discovery enqueues a new D04 generation and the existing D08 worker publishes it. Rollover no longer rewrites an applied generation. The former hourly global scan becomes one bounded source page per minute. Budgets, restart behaviour and migration/retirement are specified in the D10 maintenance contract. This is a v0.18.0 candidate, not evidence of Production deployment.

Current isolated worker and v0.17.7 cadence (#1465)

The v0.17.5 worker uses three committed stages: claim records an attempt and lease, compute builds private derived output, and publish atomically replaces the public projections after checking the generation and lease. The legacy bounded-worker description below does not replace this publication path.

D08 stats worker — settings budgets, lease fence, one-owner transactions (#1412)

D08 (#1412) hardens the isolated worker without changing any projector's numbers. Five behaviours land together.

One transaction carries one owner. The synchronous maintenance door process_user_exercise_stats_refresh_jobs used to hold up to p_limit users' locks in a single transaction, so a finished account A stayed locked until a slow account B in the same batch finished — and A's next save waited behind that lock. The door now looks at the oldest pending accounts, skips any account another worker holds (user advisory lock) or the isolated worker is computing (live lease), and processes exactly one account per call before returning. It reports has_more/remaining_user_count; a caller drains by looping while has_more is true. A transaction never spans two owners. The Edge worker stats-process-refresh-jobs drives the same three committed stages (claim → compute → publish, each its own RPC) instead of the retired batch door.

Lease fence (55P03). A before update trigger stats_projection_run_lease_fence_v1 refuses to change the phase, payload, lease token or lease time of a live lease (claimed/computed, lease_until still in the future) unless the transaction declared the current lease token in lift_guild.stats_lease_token. So when a lease expires and another worker reclaims the job with a new token, a late write from the old worker (a slow connection that came back, a repair script) is rejected. Expired leases and completed/failed rows are free — claim's expiry-recovery owns that path. A live worker holds the run's row lock through its compute (for update skip locked), so the expiry loop never steals a running job, and the worker renews its lease at each stage boundary (a long statement cannot heartbeat from inside itself).

One budget source. public.stats_projection_worker_settings is a single-row table (id = 1) holding the claim/compute/publish statement budgets, lease length, active-lease cap, attempt cap and retry backoff. The three cron commands arm their statement budget by calling stats_projection_statement_budget_v1('<stage>') before the worker call, and the functions read the caps from the row. Production's 6-second publish budget — hand-edited into the cron command during the v0.17.5 incident — is now this row's default, so re-applying migrations or a fresh environment no longer resets it to 3 s. The publish budget must stay below the 8 s save-RPC deadline and the lease must cover one compute plus one publish (table CHECKs). Defaults: claim 3 s · compute 60 s · publish 6 s · lease 5 min · 2 active leases · 3 attempts · 30 s backoff.

Compute during a save keeps its work. A newer requested generation arriving while a job computes no longer discards that computed payload. The payload publishes as its own generation (D04 settled-prefix publication) and the newer writes stay a pending successor with their own dirty scope. Only a moved published base, or a canonical row the payload references being deleted during computation (evaluated as 23503 because publication forces deferred foreign keys immediate), rejects the payload — and then the successor claim absorbs the scope. A failed attempt records retry_not_before (attempts × retry_backoff_seconds), so claim waits out the backoff before reviving it; an expired lease (a dead worker) is revived at once with no backoff.

Observation. stats_projection_worker_status_v1() (service_role/admin) returns the pending users/jobs, oldest wait, active/expired/backing-off leases, attempt-exhausted failures and the last hour's completion/failure/compute-publish timings, plus the settings row. The Edge GET health probe exposes it with a bearer token. The pre-v0.17.5 cron wrapper run_user_exercise_stats_refresh_cron (dropped from scheduling in v0.17.5) is removed; the every-second cron calls claim/compute/publish directly (service_role EXECUTE only).

Operating this worker (change a budget, pause, resume an exhausted job) is the stats worker runbook.

The v0.17.7 change in #1465 changes each stage's scheduling interval from five seconds to one second. It is prepared and locally verified; Production deployment is pending. The migration uses cron.alter_job(..., schedule := '1 second') so the job identity, command, owner and active/paused state survive. It does not combine the stages into one transaction or execute heavy work inside a save request.

Job	Interval before → candidate	Statement budget preserved
lift-guild-stats-refresh	5 s → 1 s	claim 3 s
barbelic-stats-projection-compute	5 s → 1 s	compute 60 s
barbelic-stats-projection-publish	5 s → 1 s	default/staging 3 s; approved Production override 6 s

Production's 6-second publish override was introduced during the v0.17.5 incident and remains intentional. See the incident record. Do not reset the command to the snapshot's 3-second default when applying this cadence change. Lease expiry, retry limits, generation checks and the two in-flight projection slots remain unchanged.

The three scheduling waits fall from up to approximately 15 seconds to approximately 3 seconds, excluding calculation, publication, contention and backlog. This is not a three-second guarantee for every account. The read-only Production probe on 2026-09-09 observed compute up to 26.26 seconds and publish up to 5.89 seconds for completed projection runs. General saves, long histories and simultaneous writers must be reported separately.

At one execution per second, the three jobs can create 259,200 cron run records per day. barbelic-stats-cron-history-purge runs every ten minutes at minutes 7, 17, 27, 37, 47 and 57. Each run deletes at most 5,000 records belonging to these three jobs whose status is succeeded or failed and whose end time is older than seven days. Recent/running jobs and other cron histories remain. This does not delete workout facts, refresh jobs or projection-run records. Seven days of retained execution logs can still approach 1.81 million rows; runtime, WAL and storage must be evaluated with the measurement results.

The scheduler supports second intervals and does not automatically purge execution history; see the pg_cron documentation. The same job cannot run concurrently with itself, so a long compute continues to be bounded by the existing worker rather than spawning extra computes.

Verification for the candidate includes the installed cron state in pgTAP, rollback-only migration reapplication with a paused 6-second publish job, log-retention boundaries, and real authenticated saves completed by the cron workers. Measurement and release record.

This document defines steps 2-2 through 2-6 of the incremental stats refresh plan: the persistent queue table, enqueue functions, bounded worker processor, and asynchronous processing policy for completed-workout writes.

The table is public.user_exercise_stats_refresh_jobs. It is intentionally not granted to anon or authenticated; app write RPCs, import functions, and future worker functions should write and consume it through controlled SQL functions.

The app-facing entry point is enqueue_user_exercise_stats_refresh(user_id, from_date, exercise_ids, reason). It delegates to the lower-level merge function and is the only stats refresh queue API granted to authenticated.

Table Contract

Column	Meaning
id	Job id
user_id	User whose materialized stats are dirty
event_type	FK to public.stats_refresh_event_types(id)
from_date	Earliest session date to recalculate from; null means full user history
exercise_ids	Affected exercise ids; empty array means every exercise for that user
reason	Human-readable reason useful for operations/debugging
source_ref	Stable source reference for dedupe, such as session:{id} or wodup_batch:{id}
status	pending, processing, completed, or failed
locked_at	Time a worker claimed the job
lock_owner	Worker id/process label that claimed the job
attempts	Number of worker attempts
error_message	Last failure message
metadata	Extra structured context, kept out of the fixed contract
created_at, updated_at, completed_at	Lifecycle timestamps

Range Semantics

• from_date should usually be present. Use null only for deliberate full history rebuilds.
• exercise_ids = '{}'::text[] means all exercises for that user. It should be rare, but it is useful for full rebuilds and unknown broad changes.
• Placeholder ids are valid in exercise_ids until placeholder resolution has updated canonical rows and refreshed both placeholder and canonical exercise ranges.
• event_type must be one of the events defined in public.stats_refresh_event_types.

Past Edit Dirty Range

Past session edits must invalidate the smallest safe range instead of rebuilding the user's whole history.

• Session create: from_date is the saved session date, and exercise_ids is the set of exercises in the new session.
• Session update without a date change: from_date is the edited session date.
• Session update with a date change: from_date is the earlier of the old date and the new date. Using only the new date can leave stale aggregates at the old date.
• Session update with exercise changes: exercise_ids is the union of old and new exercise ids. If an exercise id changes, both the previous and replacement ids must be refreshed.
• Session delete: from_date is the deleted session date, and exercise_ids is the set of exercises that were in the deleted session.

This is why the save engine (save_session_v5, update path) reads the old session date and old exercise ids before it rewrites session_exercise_part, then reads the new date and new exercise ids after the write before enqueueing the dirty range.

Merge Semantics

enqueue_user_exercise_stats_refresh_job(...) keeps one mergeable pending job per user in the normal case. If another dirty event arrives while an unlocked pending job already exists for that user, the function merges the new dirty range into the existing row instead of inserting another job.

Merge rules:

• from_date: keep the earlier date. If either side is null, keep null because that means full user history.
• exercise_ids: union both arrays. If either side is empty, keep an empty array because that means every exercise for that user.
• reason: append the new reason unless it already exists in the accumulated reason text.
• metadata: preserve existing metadata and record the latest event_type, source_ref, enqueue time, and merge count.

processing jobs are never mutated by enqueue. If a worker has already claimed a pending row but has not committed its status change yet, enqueue uses FOR UPDATE SKIP LOCKED and creates the next pending row without waiting for the long stats transaction. Enqueuers themselves are serialized by a per-user advisory lock, so subsequent writes merge into that new row.

Status Semantics

• pending: queued and available for a worker.
• processing: claimed by a worker. locked_at and lock_owner should be set.
• completed: worker successfully ran refresh_user_exercise_stats_from(user_id, from_date, exercise_ids).
• failed: worker gave up or hit a non-retryable error. error_message should explain the failure.

Worker Processor

process_user_exercise_stats_refresh_jobs(limit, validate_integrity, lock_owner) is the bounded processor for pending jobs.

It:

• requires a privileged service context or a Barbelic admin;
• acquires one non-blocking global batch advisory lock before claiming rows; an overlapping global invocation returns zero processed rows with global_worker_busy: true while the active invocation drains its batch;
• selects pending jobs with FOR UPDATE SKIP LOCKED so multiple workers do not process the same job;
• marks each selected job as processing, increments attempts, and records lock_owner;
• calls refresh_user_exercise_stats_from(user_id, from_date, exercise_ids);
• optionally calls validate_user_exercise_stats_integrity(...);
• marks successful jobs as completed;
• marks refresh or integrity failures as failed with error_message and diagnostic metadata.

The app-scoped and cron wrappers requeue failed jobs while attempts < 3. After the third failure the job remains failed for operator inspection. The last error is retained in metadata when a retry is scheduled.

limit is clamped to 0..100 so one worker call cannot accidentally process an unbounded backlog.

The global lock is intentionally separate from the per-user advisory-lock namespace. It prevents two multi-user transactions from retaining user locks in opposite orders; user-scoped workers can still run concurrently for independent users.

process_user_exercise_stats_refresh_jobs_for_user(user_id, limit, validate_integrity, lock_owner) is the scoped processor for app write/import paths. It can only process the authenticated user's jobs unless the caller is a Barbelic admin or privileged service context.

Its response includes remaining_count and has_more. Those fields count both pending and processing rows, so a scoped request cannot mistake a row held by the global worker for a fully drained queue. The client drains additional pages with a bounded single-flight loop and leaves any excess work to Cron.

A PostgreSQL statement timeout (57014, exactly canceling statement due to statement timeout) is a retryable worker interruption. The scoped wrapper keeps its advisory lock and worker call inside a nested subtransaction. On that exact timeout the subtransaction rolls back, the durable job remains pending, and the RPC returns HTTP 200 with worker_timed_out: true, remaining_count, and has_more. The client retries that result once before emitting one warning and leaves any remaining work to Cron. Other 57014 cancellations, including a client or operator cancellation, are rethrown and are never hidden by this recovery path.

Numbered full-stack cases drain the latest statsRequestedVersion produced by their cleanup deletes and then prove requested_version = applied_version and zero pending/processing jobs. This keeps one case's cleanup projection work from leaking into the next case's user journey.

Completed Workout Async Policy

The app has one session write surface (issue #1215, 2026-09-04): save_session_v5(...) for create/update and delete_session_v5(...) for delete. The older save_workout_v4 / update_completed_session_v4 / delete_completed_session_v4 are retired LG426 stubs. Each RPC is the atomic implementation; it does not wrap or call an older writer. The canonical rows, durable mutation receipt, and dirty-range request commit in one transaction. The RPC does not wait for materialized volume, PR, period, or calendar stats to rebuild.

New clients send expected_user_id in save/update payloads and p_expected_user_id to delete; the database rejects a mismatch with auth.uid() before writing. Deploy the database migration before the client so that check is active immediately. Missing owner fields are also rejected. These owner fields are required by the app contract. The repository calls only these three RPCs; a missing function is a deployment failure and never triggers another RPC or table fallback.

The database-owned dirty-range worker publishes the derived read models. Client refreshes are best-effort presentation updates after the durable receipt and are never part of write success. Raw completed-workout table mutation grants and all retired writer/reconciliation RPCs are removed; SELECT remains available only for owner-scoped read/export paths.

Import and explicit manual refresh paths remain inline for now because they are separate administrative/bulk workflows:

• import_wodup_batch_to_canonical(...)

The former runtime resolve_exercise_external_placeholder(...) boundary is retired by the exercise-identity hard cutover. Provider identities are resolved through the exact external mapping during import; consolidating an already-used placeholder requires an offline source rewrite and full projection replay.

PR Overview Snapshot Materialization

PR 전광판의 종목별 값은 get_pr_overview() 요청 안에서 종목 수만큼 다시 계산하지 않습니다. 내부 read model과 refresh control은 네 테이블로 나뉩니다.

• pr_overview_projection_control: 전역 exercise catalog epoch
• user_pr_overview_rollover_state: 사용자별 due 시각, snapshot generation, 적용 catalog epoch, retry 상태
• user_pr_overview_snapshot_headers: 사용자·generation·as_of_date별 완성 snapshot 메타데이터
• user_pr_exercise_summary_snapshots: 한 세대에 속한 typed 종목 요약 행

refresh_user_exercise_stats_from(...)의 최종 wrapper는 기존 exercise, training, calendar read model을 갱신한 뒤 refresh_user_pr_overview_snapshot_window(...)를 실행합니다. snapshot 행과 header가 모두 준비된 뒤 같은 transaction에서 user_stats_refresh_state.applied_version이 전진합니다. 별도 published/ready 상태를 두지 않으며 applied version 자체가 publication fence입니다.

as_of는 rolling 30-day와 전년도 비교의 정합성 경계입니다. 화면은 임의 historical 날짜를 요구하지 않고 브라우저의 local today를 전달합니다. worker는 timezone 경계를 위해 DB current_date - 1, current_date, current_date + 1의 3일 window를 항상 미리 만듭니다. reader는 요청한 as_of_date와 applied generation이 정확히 일치하지 않으면 SQLSTATE 55000으로 fail closed합니다. 다른 날짜 snapshot 재사용이나 read-time 동적 계산 fallback은 허용하지 않습니다. 동일 사용자의 concurrent worker는 사용자별 transaction advisory lock으로 build/publish 순서를 직렬화하고, 더 오래된 작업이 최신 generation을 덮어쓰지 못하게 합니다.

v0.18.0 D10에서는 lift-guild-pr-overview-snapshot-rollover가 매분 due 인덱스의 페이지를 FOR UPDATE SKIP LOCKED로 집습니다. 기본 10명·최대 25명에게 새 D04 generation을 요청하고, 실패 횟수와 다음 재시도 시각을 상태 행에 남깁니다. 현재 applied generation을 직접 보충하지 않으며 D08 worker가 새 세대의 3일 창을 발행합니다.

종목 변경 로그 INSERT와 같은 트랜잭션에서 scope별 미처리 token을 기록합니다. 전역 종목은 사용자 페이지를 순회하고, 개인 종목은 그 사용자만 요청합니다. 변경 중인 순회를 앞에서 다시 시작하지 않고 최신 token의 후속 순회를 남기므로, 늦은 낮은 seq의 커밋·로그 보존 정리도 요청을 잃지 않습니다. 쓰기 transaction에서 모든 사용자 snapshot을 동기 갱신하지 않습니다. 자세한 계약과 운영 전환은 D10을 따릅니다.

3일 window는 정상적으로 매분 보충되므로 rollover가 이틀 넘게 연속 실패하면 요청 날짜의 exact snapshot이 사라져 SQLSTATE 55000 fail-closed가 발생합니다. 운영 알림은 그 전에 last_success_at, attempt_count, last_error와 due backlog를 감시해야 합니다.

완료 운동 저장·수정·삭제는 기존처럼 canonical commit 후 detached worker를 사용합니다. 직접 1RM과 onboarding 초기 1RM처럼 user_manual_pr_records를 바꾸는 경로도 해당 종목의 PR snapshot을 무효화하거나 갱신해야 합니다. 그렇지 않으면 canonical manual PR은 저장됐지만 전광판만 이전 세대를 읽는 상태가 됩니다.

초기 migration은 기존 사용자 3일 window를 백필하고 기존 결과와 parity를 검증한 다음 thin reader를 snapshot으로 전환합니다. 운영 검증은 migration history와 remote schema 확인에 더해, applied generation의 3일 header 누락·고아 snapshot·동일 사용자/generation/as_of 중복이 없는지도 확인합니다. 프론트는 내부 snapshot 테이블을 직접 읽지 않고 계속 get_pr_overview(p_as_of)만 호출합니다.

Direct Refresh Boundary

Generation-scoped client reconciliation

같은 사용자·requested_version을 관찰한 전역 stale 복구와 workout sync는 서로 다른 worker/read 요청을 만들지 않는다. 클라이언트 generation coordinator가 stats drain → Home → 현재 PR/Volume overview → 선택 PR 상세 전체를 하나의 shared flight로 합류시키며, 완료된 fresh 결과와 terminal 결과는 같은 generation observer가 재사용한다. incomplete 또는 네트워크 실패 결과는 보관하지 않아 bounded retry가 새 flight를 만들 수 있다. 모든 reconciliation 요청은 receipt/read freshness가 제공한 양의 requested_version을 필수로 전달하며 누락되거나 0인 generation은 계약 오류다.

repository worker flight도 coalesced caller를 구분한다. 같은 requested generation의 read-only observer는 진행 중 promise만 공유하고 빈 trailing worker page를 요구하지 않는다. 반면 canonical write 또는 더 높은 requested generation은 마지막 pass 뒤 한 번 더 drain하도록 rerunRequested를 세워 late enqueue를 놓치지 않는다.

refresh_user_exercise_stats_from(...) is the public refresh wrapper. It calls the base per-exercise refresh engine and then materializes user_training_period_stats for user-level day/week/month/quarter/year totals. App write paths and import/admin mapping paths should not call it directly. They enqueue a dirty range through enqueue_user_exercise_stats_refresh(...).

Direct execution of the heavy refresh functions is revoked from authenticated; a worker function should call the refresh engine after it claims a pending job.

Worker Entry Point

enqueue_stale_user_exercise_stats_refresh_jobs(limit) is the backfill scanner. It looks for completed sessions without rollups, rollups without user_training_period_stats, and rollups newer than training period stats. Matching users are merged into the same dirty range queue with scheduled_stats_backfill.

It is scheduled hourly (barbelic-stats-backfill-scan, added by 20260820120400) through the private run_user_exercise_stats_backfill_scan_cron(limit) wrapper. Hourly rather than per minute: the scan walks every user, while the per-minute lift-guild-stats-refresh worker is what drains whatever it enqueues. Between 2026-07-20 and 2026-08-20 the function was documented as scheduled and had no scheduler; only the Edge Function enqueue_stale flag ever ran it (audit STATS-03).

The scan signals themselves have not moved since 2026-06 and are a separate follow-up: calendar day summaries, PR snapshots, and measured-PR gaps are not yet scanned for.

The authenticated app drains its own jobs through the scoped SQL processor. A Supabase Cron job named lift-guild-stats-refresh calls the privileged global processor every minute as a durable fallback for a closed tab, interrupted network request, incomplete bounded drain, or older client. Cron calls the private run_user_exercise_stats_refresh_cron(...) security-definer wrapper; that wrapper supplies the service request context expected by the global processor and applies the same three-attempt retry policy.

stats-process-refresh-jobs remains the admin/service Edge Function worker entry point for global batches and stale-data scans. It accepts a bounded limit, validate_integrity, optional lock_owner, and optional enqueue_stale/enqueue_limit. When enqueue_stale is true, it first calls the scanner and then calls:

process_user_exercise_stats_refresh_jobs(limit, validate_integrity, lock_owner)

This keeps request/write functions separate from heavy stats processing while retaining an explicit global backfill and repair path.