Preserve gamer history and optimize recent activity reads #962

Merged

jimmyb merged 2 commits from codex/issue-3-scalable-gamer-updates into master

2026-10-07 10:19:27 -05:00

Codex commented

2026-10-07 08:15:03 -05:00

Member

Preserve gamer update history and make recent feed reads use a composite index with stable newest-first ordering.

The dedicated feed remains a fixed list of the 50 newest visible entries; cards remain limited to 10. Admin activity shows the latest 15 admin actions. Spatie cleanup now preserves gamer updates, including explicitly targeted cleanup. Legacy activities without a subject still render; comment visibility uses indexed primary-key checks; the index migration safely tolerates repeated up/down calls.

Producer and consumer inventory

Gamer producers: game-list status/replay changes; favorites/wishlists through FavoriteWishlistActivity; registration; blog publication from controllers and profile settings; game, blog, and profile comments. SampleGamerUpdatesSeeder produces development fixtures. Review-published activity is supported by the renderer, but has no current producer; future producers must use the same log and preserve the existing property contract.
Public consumers: RecentGamerUpdates supplies the homepage, terms/privacy pages, game search, platform page, newly-added-games page, and dedicated feed page. Existing presentation, privacy, banned/deleted-user and comment-subject filters remain shared.
Other consumers: admin dashboard logs now select admin; profile blog settings check existing publication activity to avoid duplicate events. Admin user editing has no activity-history query in the current Laravel implementation. Production data synchronization continues copying the unchanged activity_log table.
Admin producers: game management, game-request processing, content-flag resolution, user management and user-media changes all write admin activities.

The visibility check uses a correlated scalar count instead of an EXISTS semi-join. On the million-row fixture, MariaDB otherwise selected the old log-name index, read 950,000 entries, and filesorted the feed before applying LIMIT. The scalar check retains the same visibility result and allows ordered index reads. Comment-subject checks also use scalar counts to avoid materializing the entire comment history; regression coverage verifies live, soft-deleted and missing game/blog/profile comments.

Storage and rollout

Keep all historical gamer updates in activity_log. Add (log_name, created_at, id) without changing existing records or indexes. No archive/backfill, ID reset, truncation, retention deletion, pagination, or new dependencies are required. Other log types retain their existing 365-day cleanup policy.

Deploy requires running the new additive migration in a planned window after verifying index-build capacity and locking behavior on the live table. Production deployment has not been performed. Migration rollback drops only the added index and preserves history; reverting the cleanup configuration would restore the previous destructive gamer-update cleanup behavior.

Verification

213 affected regression tests passed (1,658 assertions), covering feed/card output, history, timestamp ties, privacy, missing/banned/deleted users, cleanup targeting, admin-only logs, and existing activity producers/blog duplicate suppression.
Pint, PHPStan, and frontend build passed.
Expanded disposable MariaDB 12.3.3 benchmark on ArchLinux: 1,000,000 historical activities plus 1,000,000 game comments, including mixed comment/non-comment subjects, soft-deleted comments, 950,000 gamer updates, 50,000 admin actions, and 750,000 activities from banned/deleted/private users.
ANALYZE FORMAT=JSON confirmed the composite index, primary-key comment lookups and no filesort: 10-entry feed examined 65 activity/7 comment rows in 1.52 ms; 50-entry feed examined 312 activity/33 comment rows in 4.51 ms; admin query examined 15 activity rows in 0.25 ms. These are warmed server execution timings on synthetic data; initial PHP query timings including the SSH connection and cache warming were 1,396 ms / 166 ms / 60 ms respectively.
Index creation took 17.12 seconds on this dataset. Migration up/down preserved all one million activities and the pre-existing log-name index; focused SQLite tests also verify repeated up/up/down/down resumes and exact record preservation. No row-moving job is needed for this strategy.
Forgejo lint and full MariaDB CI passed on de69e0902696977b0133752543df80510f566c62 (quality run, test run). CI completed migrations, asset build, browser checks and PHP tests with no failures; Pest reported 763 warnings, 56 passed and 5,407 assertions. The existing warning output is retained here rather than described as a warning-free run.
Visibility-heavy distributions can require scanning more index entries before finding 10/50 visible records; the benchmark establishes this fixture's behavior, not a constant bound for every possible privacy distribution.

Fixes #3

Preserve gamer update history and make recent feed reads use a composite index with stable newest-first ordering. The dedicated feed remains a fixed list of the 50 newest visible entries; cards remain limited to 10. Admin activity shows the latest 15 admin actions. Spatie cleanup now preserves gamer updates, including explicitly targeted cleanup. Legacy activities without a subject still render; comment visibility uses indexed primary-key checks; the index migration safely tolerates repeated up/down calls. ### Producer and consumer inventory - Gamer producers: game-list status/replay changes; favorites/wishlists through `FavoriteWishlistActivity`; registration; blog publication from controllers and profile settings; game, blog, and profile comments. `SampleGamerUpdatesSeeder` produces development fixtures. Review-published activity is supported by the renderer, but has no current producer; future producers must use the same log and preserve the existing property contract. - Public consumers: `RecentGamerUpdates` supplies the homepage, terms/privacy pages, game search, platform page, newly-added-games page, and dedicated feed page. Existing presentation, privacy, banned/deleted-user and comment-subject filters remain shared. - Other consumers: admin dashboard logs now select `admin`; profile blog settings check existing publication activity to avoid duplicate events. Admin user editing has no activity-history query in the current Laravel implementation. Production data synchronization continues copying the unchanged `activity_log` table. - Admin producers: game management, game-request processing, content-flag resolution, user management and user-media changes all write `admin` activities. The visibility check uses a correlated scalar count instead of an EXISTS semi-join. On the million-row fixture, MariaDB otherwise selected the old log-name index, read 950,000 entries, and filesorted the feed before applying LIMIT. The scalar check retains the same visibility result and allows ordered index reads. Comment-subject checks also use scalar counts to avoid materializing the entire comment history; regression coverage verifies live, soft-deleted and missing game/blog/profile comments. ### Storage and rollout Keep all historical gamer updates in `activity_log`. Add `(log_name, created_at, id)` without changing existing records or indexes. No archive/backfill, ID reset, truncation, retention deletion, pagination, or new dependencies are required. Other log types retain their existing 365-day cleanup policy. Deploy requires running the new additive migration in a planned window after verifying index-build capacity and locking behavior on the live table. Production deployment has not been performed. Migration rollback drops only the added index and preserves history; reverting the cleanup configuration would restore the previous destructive gamer-update cleanup behavior. ### Verification - 213 affected regression tests passed (1,658 assertions), covering feed/card output, history, timestamp ties, privacy, missing/banned/deleted users, cleanup targeting, admin-only logs, and existing activity producers/blog duplicate suppression. - Pint, PHPStan, and frontend build passed. - Expanded disposable MariaDB 12.3.3 benchmark on ArchLinux: 1,000,000 historical activities plus 1,000,000 game comments, including mixed comment/non-comment subjects, soft-deleted comments, 950,000 gamer updates, 50,000 admin actions, and 750,000 activities from banned/deleted/private users. - `ANALYZE FORMAT=JSON` confirmed the composite index, primary-key comment lookups and no filesort: 10-entry feed examined 65 activity/7 comment rows in 1.52 ms; 50-entry feed examined 312 activity/33 comment rows in 4.51 ms; admin query examined 15 activity rows in 0.25 ms. These are warmed server execution timings on synthetic data; initial PHP query timings including the SSH connection and cache warming were 1,396 ms / 166 ms / 60 ms respectively. - Index creation took 17.12 seconds on this dataset. Migration up/down preserved all one million activities and the pre-existing log-name index; focused SQLite tests also verify repeated up/up/down/down resumes and exact record preservation. No row-moving job is needed for this strategy. - Forgejo lint and full MariaDB CI passed on `de69e0902696977b0133752543df80510f566c62` ([quality run](https://code.myvideogamelist.com/MyVideoGameList/myvideogamelist.com/actions/runs/1191), [test run](https://code.myvideogamelist.com/MyVideoGameList/myvideogamelist.com/actions/runs/1192)). CI completed migrations, asset build, browser checks and PHP tests with no failures; Pest reported 763 warnings, 56 passed and 5,407 assertions. The existing warning output is retained here rather than described as a warning-free run. - Visibility-heavy distributions can require scanning more index entries before finding 10/50 visible records; the benchmark establishes this fixture's behavior, not a constant bound for every possible privacy distribution. Fixes #3