Stats Refresh Dirty Events

This document defines step 2-1 of the incremental stats refresh plan: what events make materialized user exercise stats stale.

Completed-workout writes now enqueue a dirty range and return before the materialized stats refresh finishes. The authenticated app drains its own queue in the background, with a database cron job as the durable fallback. Import and administrative repair paths may still process their queued work inline. The event definitions below are the shared vocabulary for those paths and are stored in public.stats_refresh_event_types so event names stay stable across SQL functions, Edge Functions, and tests.

Dirty Range Contract

Every dirty event must resolve to this minimum payload:

• user_id: owner of the affected training data.
• from_date: earliest completed session date that may have changed stats.
• exercise_ids: affected exercise ids. Include placeholder ids when they are still used by canonical rows.
• event_type: one of the ids in public.stats_refresh_event_types.

from_date is intentionally conservative. When the date can move, use least(old.date, new.date). When deleting rows, capture the old session date and exercise ids before deleting. When an exercise id changes, include both old and new ids.

Event Types

Event	Source	Dirty range
session_created	sessions insert	New completed session date, all exercises in that session
session_updated	sessions update	least(old.date, new.date), old and new completed session exercises
session_deleted	sessions delete	Deleted session date captured before delete, deleted session exercises
session_exercise_created	session_exercise_part insert	Parent completed session date, inserted exercise id
session_exercise_deleted	session_exercise_part delete	Parent completed session date captured before delete, deleted exercise id
session_exercise_exercise_changed	session_exercise_part update	Parent completed session date, old and new exercise ids
exercise_set_created	exercise_set_part insert	Parent completed session date, parent exercise id
exercise_set_updated	exercise_set_part update	Parent completed session date, parent exercise id
exercise_set_deleted	exercise_set_part delete	Parent completed session date captured before delete, parent exercise id
placeholder_resolved	exercise_external_mappings resolve	Earliest completed session using the placeholder, placeholder and canonical ids
wodup_import_materialized	wodup_import_batches bulk	Earliest imported session in the batch, all mapped and placeholder ids in the batch

Stats-Affecting Changes

These changes dirty materialized stats:

• A session becomes completed, is created as completed, is deleted, or changes date/status/title.
• A session exercise is added, deleted, or changes exercise_id.
• A set is added or deleted.
• A set changes fields used by stats: reps, load, set_type, or position.
• A placeholder exercise is resolved to a canonical exercise.
• A Wodup import batch materializes sessions into canonical tables.

These changes do not dirty stats today:

• User notes on sessions, session exercises, or sets.
• rest_seconds and free_rest, unless future stats begin to use them.
• UI-only labels or cached display metadata.

If future product logic starts using one of the non-dirty fields in volume, PR, or intensity calculations, add it to this contract before changing the calculation.

Queue Table

Step 2-2 adds public.user_exercise_stats_refresh_jobs, with event_type referencing public.stats_refresh_event_types(id). See docs/data/stats-refresh-jobs.md for the queue table contract.

Processing Contract

See docs/data/stats-refresh-jobs.md for merge, enqueue-only write, worker, and cron recovery behavior.