Plan scalable Recent Gamer Updates feed storage #3

New issue

Closed

opened 2024-01-14 14:12:23 -06:00 by jimmyb · 7 comments

jimmyb commented

2024-01-14 14:12:23 -06:00

Owner

Copy link

The Recent Gamer Updates feed needs a scalable Laravel storage strategy so the site can keep public activity pages fast without blindly deleting historical user activity.

The original issue proposed trimming or truncating the old feed table, but later comments clarified that people do use the feed and that historical records should ideally be preserved. In the current Laravel checkout, feed entries are written to Spatie activity log table with log_name = gamer_updates, displayed through the RecentGamerUpdates presenter, and surfaced on the homepage/sidebar and the dedicated Recent Gamer Updates page. This issue tracks deciding and implementing the long-term storage, indexing, archival, and query strategy for that feed.

Scope

activity_log records used for gamer_updates.
Recent Gamer Updates homepage/sidebar card.
/recent-gamer-updates listing page showing the 50 newest visible entries, without pagination.
Any admin/profile/feed surfaces that query activity records.
Feed query performance, indexing, archival, retention, and migration/backfill behavior.
Preservation of historical feed data unless a future product decision explicitly approves deletion.

Acceptance Criteria

Current feed producers and consumers are documented before storage changes are made.
Gamer update feed queries use bounded, indexed, newest-first reads and do not require scanning the full activity table.
The active/recent feed remains fast when the activity table contains a large historical record count.
Historical feed records are preserved by default through an archive/current-table strategy, partitioning strategy, or another documented Laravel-compatible approach.
No implementation relies on truncating the table, resetting IDs, or deleting historical data as the primary solution.
Any archival or migration process is idempotent, resumable, and safe to run in batches.
Existing gamer_updates activity entries continue to render with user, avatar, game, platform, activity type, and timestamp data.
Banned, deleted, and soft-deleted users remain excluded from public feed rendering.
Admin activity log usage remains separate from public gamer update feed behavior where appropriate.
Empty feed states render clearly.
User-facing UI supports light/dark mode and follows the existing Laravel/Tailwind site layout patterns.

Test Coverage Required

Feature/unit tests confirming Recent Gamer Updates reads are ordered newest-first and limited appropriately.
Regression tests confirming existing gamer_updates records render expected card/list data.
Tests confirming banned, deleted, and soft-deleted users are excluded from public feed output.
Tests confirming the dedicated feed page displays only the 50 newest visible entries, without pagination.
Tests covering any archival/current-feed split, including no missing records, no duplicate records, and stable ordering across boundaries.
Command/job tests for archival behavior if a batch archive process is added, including dry-run or idempotency behavior where available.
Regression tests confirming admin activity log queries still work after feed storage/index changes.
Run the focused affected tests, then run vendor/bin/pint --dirty before closing the issue.

Progress Checklist

Spatie activity log table exists
gamer_updates activity entries are written from game-list status changes
RecentGamerUpdates presenter exists
Homepage/sidebar Recent Gamer Updates card exists
Sidebar link for /recent-gamer-updates exists
Activity log cleanup protects gamer history, including explicit gamer cleanup; other logs retain age-based cleanup (verified in PR #962 and deployed to dev/prod)
Inventory all current and planned gamer update feed producers
Inventory all public/admin consumers of activity feed data
Choose the long-term current-vs-history storage strategy
Add required indexes or migrations for bounded newest-first feed queries
Archival/migration command or job: not applicable — the confirmed strategy preserves the existing single table and requires no row-moving process
Ensure public feed queries preserve historical data and avoid destructive truncation
Keep the dedicated /recent-gamer-updates page limited to the 50 newest visible entries, without pagination
Add tests for feed ordering, limits, filtering, cleanup protection, migration repeatability and regressions; archive/split-boundary tests are not applicable
Confirm the final strategy keeps Recent Gamer Updates usable at large table sizes

Confirmed implementation direction

Preserve historical gamer updates in the existing activity_log table. Add a composite (log_name, created_at, id) index and stable newest-first reads; protect gamer_updates from age-based cleanup. Homepage/sidebar cards show 10 entries and the dedicated page shows 50, without pagination. The admin dashboard activity list shows admin actions only. Validate the strategy with a disposable million-row MariaDB dataset before closeout.

Acceptance review and implementation evidence

Implementation and inventory are documented in PR #962. The PR was merged and the user authorized deployment to both environments. The merged release and new index are now verified on dev and prod.

Producers and public/admin consumers are inventoried in the PR. Existing single-table storage and production synchronization are preserved; there is no archive, backfill, ID reset, truncation or destructive gamer retention.
Public reads apply shared privacy, banned/deleted/missing-causer and comment-subject visibility filters, then order by created_at DESC, id DESC. Cards show 10 and the dedicated page shows 50 without pagination. Admin reads select only admin, ordered identically and limited to 15.
213 affected regression tests passed with 1,658 assertions. Coverage includes legacy records with null subjects, status/replay fallbacks, existing activity variants and presentation, timestamp ties, limits, privacy/user exclusions, all three deleted/missing comment subject types, empty state, cleanup targeting, admin isolation and blog publication duplicate detection. Existing layout and light/dark presentation are retained.
The additive composite-index migration tolerates repeated up/up/down/down, preserves records and old indexes, and can resume after index DDL succeeds before migration bookkeeping. No archival/batch process is needed for this strategy.
Disposable MariaDB validation used one million activities plus one million comments, with 750,000 activities from hidden users. The 10/50/admin reads used the composite index with no filesort, examining 65/312/15 activity rows and 7/33/0 comment rows respectively. Warmed server timings were 1.52/4.51/0.25 ms; index creation took 17.12 seconds. The PR records initial connection/cache-warming timings and representative-data limitations. Index creation/rollback preserved all one million activities.
Pint, PHPStan and the frontend build passed. Forgejo lint and full MariaDB CI passed on de69e0902696977b0133752543df80510f566c62; the PR links the runs and records the existing Pest warnings alongside the no-failure result (5,407 assertions).

Deployment verification — 2026-10-07

Merged revision 04b64436b75a4cc06934d4a7ee883c689a15acf6 was deployed to both dev and prod with application rollback backups. Only 2026_10_07_120000_add_feed_order_index_to_activity_log_table was migrated (190 ms dev / 68 ms prod); the two older dev migrations were initially left untouched and subsequently applied after separate authorization (game-list status-count index and unavailable-badge cleanup).

Both environments have the new composite index and protected cleanup action, retain all 15,761 activity records and their ID range, and match all 414 staged application files. The 10/50-entry reads, live 50-entry page, homepage/login responses, settings login redirect and served CSS/JavaScript hashes were verified. Both environments now have no pending migrations. The follow-up dev badge migration had no existing awards to delete; affected badge rows were backed up before execution.

The Recent Gamer Updates feed needs a scalable Laravel storage strategy so the site can keep public activity pages fast without blindly deleting historical user activity. The original issue proposed trimming or truncating the old feed table, but later comments clarified that people do use the feed and that historical records should ideally be preserved. In the current Laravel checkout, feed entries are written to Spatie activity log table with `log_name = gamer_updates`, displayed through the `RecentGamerUpdates` presenter, and surfaced on the homepage/sidebar and the dedicated Recent Gamer Updates page. This issue tracks deciding and implementing the long-term storage, indexing, archival, and query strategy for that feed. ## Scope - `activity_log` records used for `gamer_updates`. - Recent Gamer Updates homepage/sidebar card. - `/recent-gamer-updates` listing page showing the 50 newest visible entries, without pagination. - Any admin/profile/feed surfaces that query activity records. - Feed query performance, indexing, archival, retention, and migration/backfill behavior. - Preservation of historical feed data unless a future product decision explicitly approves deletion. ## Acceptance Criteria - Current feed producers and consumers are documented before storage changes are made. - Gamer update feed queries use bounded, indexed, newest-first reads and do not require scanning the full activity table. - The active/recent feed remains fast when the activity table contains a large historical record count. - Historical feed records are preserved by default through an archive/current-table strategy, partitioning strategy, or another documented Laravel-compatible approach. - No implementation relies on truncating the table, resetting IDs, or deleting historical data as the primary solution. - Any archival or migration process is idempotent, resumable, and safe to run in batches. - Existing `gamer_updates` activity entries continue to render with user, avatar, game, platform, activity type, and timestamp data. - Banned, deleted, and soft-deleted users remain excluded from public feed rendering. - Admin activity log usage remains separate from public gamer update feed behavior where appropriate. - Empty feed states render clearly. - User-facing UI supports light/dark mode and follows the existing Laravel/Tailwind site layout patterns. ## Test Coverage Required - Feature/unit tests confirming Recent Gamer Updates reads are ordered newest-first and limited appropriately. - Regression tests confirming existing `gamer_updates` records render expected card/list data. - Tests confirming banned, deleted, and soft-deleted users are excluded from public feed output. - Tests confirming the dedicated feed page displays only the 50 newest visible entries, without pagination. - Tests covering any archival/current-feed split, including no missing records, no duplicate records, and stable ordering across boundaries. - Command/job tests for archival behavior if a batch archive process is added, including dry-run or idempotency behavior where available. - Regression tests confirming admin activity log queries still work after feed storage/index changes. - Run the focused affected tests, then run `vendor/bin/pint --dirty` before closing the issue. ## Progress Checklist - [x] Spatie activity log table exists - [x] `gamer_updates` activity entries are written from game-list status changes - [x] `RecentGamerUpdates` presenter exists - [x] Homepage/sidebar Recent Gamer Updates card exists - [x] Sidebar link for `/recent-gamer-updates` exists - [x] Activity log cleanup protects gamer history, including explicit gamer cleanup; other logs retain age-based cleanup (verified in PR #962 and deployed to dev/prod) - [x] Inventory all current and planned gamer update feed producers - [x] Inventory all public/admin consumers of activity feed data - [x] Choose the long-term current-vs-history storage strategy - [x] Add required indexes or migrations for bounded newest-first feed queries - [x] Archival/migration command or job: not applicable — the confirmed strategy preserves the existing single table and requires no row-moving process - [x] Ensure public feed queries preserve historical data and avoid destructive truncation - [x] Keep the dedicated `/recent-gamer-updates` page limited to the 50 newest visible entries, without pagination - [x] Add tests for feed ordering, limits, filtering, cleanup protection, migration repeatability and regressions; archive/split-boundary tests are not applicable - [x] Confirm the final strategy keeps Recent Gamer Updates usable at large table sizes ## Confirmed implementation direction Preserve historical gamer updates in the existing `activity_log` table. Add a composite `(log_name, created_at, id)` index and stable newest-first reads; protect `gamer_updates` from age-based cleanup. Homepage/sidebar cards show 10 entries and the dedicated page shows 50, without pagination. The admin dashboard activity list shows admin actions only. Validate the strategy with a disposable million-row MariaDB dataset before closeout. ## Acceptance review and implementation evidence Implementation and inventory are documented in [PR #962](https://code.myvideogamelist.com/MyVideoGameList/myvideogamelist.com/pulls/962). The PR was merged and the user authorized deployment to both environments. The merged release and new index are now verified on dev and prod. - Producers and public/admin consumers are inventoried in the PR. Existing single-table storage and production synchronization are preserved; there is no archive, backfill, ID reset, truncation or destructive gamer retention. - Public reads apply shared privacy, banned/deleted/missing-causer and comment-subject visibility filters, then order by `created_at DESC, id DESC`. Cards show 10 and the dedicated page shows 50 without pagination. Admin reads select only `admin`, ordered identically and limited to 15. - 213 affected regression tests passed with 1,658 assertions. Coverage includes legacy records with null subjects, status/replay fallbacks, existing activity variants and presentation, timestamp ties, limits, privacy/user exclusions, all three deleted/missing comment subject types, empty state, cleanup targeting, admin isolation and blog publication duplicate detection. Existing layout and light/dark presentation are retained. - The additive composite-index migration tolerates repeated up/up/down/down, preserves records and old indexes, and can resume after index DDL succeeds before migration bookkeeping. No archival/batch process is needed for this strategy. - Disposable MariaDB validation used one million activities plus one million comments, with 750,000 activities from hidden users. The 10/50/admin reads used the composite index with no filesort, examining 65/312/15 activity rows and 7/33/0 comment rows respectively. Warmed server timings were 1.52/4.51/0.25 ms; index creation took 17.12 seconds. The PR records initial connection/cache-warming timings and representative-data limitations. Index creation/rollback preserved all one million activities. - Pint, PHPStan and the frontend build passed. Forgejo lint and full MariaDB CI passed on `de69e0902696977b0133752543df80510f566c62`; the PR links the runs and records the existing Pest warnings alongside the no-failure result (5,407 assertions). ## Deployment verification — 2026-10-07 Merged revision `04b64436b75a4cc06934d4a7ee883c689a15acf6` was deployed to both dev and prod with application rollback backups. Only `2026_10_07_120000_add_feed_order_index_to_activity_log_table` was migrated (190 ms dev / 68 ms prod); the two older dev migrations were initially left untouched and subsequently applied after separate authorization (game-list status-count index and unavailable-badge cleanup). Both environments have the new composite index and protected cleanup action, retain all 15,761 activity records and their ID range, and match all 414 staged application files. The 10/50-entry reads, live 50-entry page, homepage/login responses, settings login redirect and served CSS/JavaScript hashes were verified. Both environments now have no pending migrations. The follow-up dev badge migration had no existing awards to delete; affected badge rows were backed up before execution.

jimmyb added the

Rows
Columns