Django DB-Level Time Decay Feed Scoring

When to Use

• Replacing chronological feed (order_by("-created_at")) with engagement + freshness scoring
• Implementing Hacker News-style ranking where new content competes with older popular content
• Need for DB-level sorting (no Python-side sorting of full querysets)
• Must work on both SQLite (dev) and PostgreSQL (prod)

The Formula

score = (like_count + comment_count * COMMENT_WEIGHT + featured_boost) / (age_hours + 2)

• age_hours: hours since created_at (NOT updated_at — use created_at so edits don't inflate score)
• + 2 in denominator: prevents division by near-zero for very new content, stabilises first 2 hours
• comment_count * 2: comments signal deeper engagement than likes
• featured_boost: flat additive boost for curated/featured content