Write /authors performance + data-model foundation design doc

next steps

—

key decisions

• Denormalize total_karma, last_seen_at, registered_at, is_visible, has_verified_flair onto the location_reddit_user pivot to enable index-ordered scans.
• Add three composite covering indexes on pivot leading with (location_id, is_visible, <sortcol>, reddit_user_id) to eliminate filesorts.
• Use B-tree (lat, lng) index on locations and cities instead of SPATIAL because coordinates are nullable and SPATIAL does not support NULL.
• Implement interim per-page cache of reddit_user ids with 15-min TTL to hide cold 338ms on repeat visits.
• Rewrite the location filter from whereHas to whereExists with subquery on pivot to reduce join cost.
• Use MySQL 8.4 INSTANT algorithm for adding columns (if available) to avoid table rebuild on 7.9M pivot.
• Run daily reconcile command to bound drift from write paths that may bypass sync hooks.

open questions

• Proximity cross-location sort-merge performance remains partially unsolved and deferred to downstream work with caching.
• Debounce timing for pivot sync on frequent total_karma/last_seen_at updates is unmeasured and may need adjustment.
• INSTANT algorithm availability for this exact table on prod needs verification before migration.
• Whether the interim page-cache TTL (15 min) matches actual user browsing patterns is untested.