Analytics Content Tab: Top Posts Ranked by Reach Instead of Engagement and Date Range Filter Not Applied

Summary

The Analytics Content tab contains two independent bugs in the Top Posts feature. First, the "Top Posts by Engagement" card ranks posts by a total that includes impression/view counts, causing reach to dominate the sort order rather than engagement. Second, getTopPostsByEngagement() is called without a since parameter, so the Top Posts table always reflects all-time data regardless of the date range picker selection. Together, these issues produce misleading metrics and a visible data asymmetry when filtering by a short time window.

—

Root-Cause Analysis

Bug 1: Engagement total includes impressions

Confirmed evidence:

• totalExpression() at src/lib/db/queries/analytics.ts:312 sums every counter a platform exposes, including impressions.
• Impressions are an order of magnitude larger than engagement counters. A post with 4,210 views and 138 engagements produces a total of 4,348, making the sort effectively ORDER BY views.
• The barKey="total" bar in the UI reflects this inflated total.
• The card is explicitly titled "Top Posts by Engagement", creating a direct contradiction between label and behavior.

Confirmed context (deliberate trade-off in PR #218):

• The total was designed to reconcile with the visible row sum: total = sum(all visible columns). Because Views is a visible column, excluding impressions from total while retaining the Views column would break that row-sum invariant. The decision was deferred, not overlooked.

Root cause: totalExpression() does not distinguish between reach metrics (impressions/views) and engagement metrics (likes, comments, shares, quotes, etc.) when constructing the sort key and bar value.

—

Bug 2: getTopPostsByEngagement() ignores the since parameter

Confirmed evidence:

• src/app/api/analytics/content/route.ts:20 calls getTopPostsByEngagement() with no since argument.
• Lines :19 (publishedOverTime) and :22 (avgMetrics) both receive the since value derived from the range picker.
• getAverageEngagementMetrics at analytics.ts:430 already filters on em.snapshot_at >= ?, confirming the pattern exists and works.

Practical impact:

• The Top Posts table always returns all-time results regardless of the "Last 7 / 30 / 90 days" selection.
• With platform-grouped layout (introduced in #218), a populated all-time table sits directly above near-empty 7-day stat cards, which appears to the user as a data error rather than a scope mismatch.

Root cause: The since value is not threaded into getTopPostsByEngagement(), and the underlying query contains no time-bound predicate.

—

Related latent issue (not blocking, noted for awareness)

getAverageEngagementMetrics counts every snapshot row in the time range (the field is named snapshots intentionally), while the Top Posts table deduplicates to the latest snapshot per post. These two strategies are currently consistent only because each writer inserts exactly one snapshot per post. When a re-snapshot code path is introduced, averages will inflate relative to per-post values. This is not a current bug but should be addressed before re-snapshotting lands.

—

Resolution Steps

Fix 1: Separate engagement total from reach total

A product decision is required before implementation. Three options, ordered by preference as documented in the issue:

1. Recommended — Split the column: Introduce a separate engagement aggregate that sums only engagement counters (likes, comments, shares, quotes, reposts, etc.) and excludes impressions. Rank by engagement. Rename the existing column to Views. The row invariant is preserved: Views + Engagement = sum(all visible columns). Update the card title and barKey to reference engagement.

1. Alternative — Drop the Views column from the table: Exclude impressions from total, drop the Views column from the table (it is already surfaced in the Avg Views stat card), and rank by the corrected total. Fewer UI changes but loses per-post view data from the table.

1. Fallback — Retitle only: If neither column change is acceptable, rename the card to "Top Posts by Reach" or "Top Posts by Total Activity" to match the actual sort behavior. This is a documentation fix, not a data fix.

Implementation steps for Option 1 (recommended):

1. In src/lib/db/queries/analytics.ts, add a new engagementExpression() function that sums only engagement counters, excluding impressions. Use the existing getAverageEngagementMetrics filter logic as a reference for which fields qualify as engagement.
2. Add engagement as a computed column in getTopPostsByEngagement() alongside the existing total (now representing Views + Engagement sum, or repurposed as Views alone).
3. Change the ORDER BY clause to sort by engagement DESC.
4. Update the API response shape and the frontend component to use barKey="engagement" and render the split columns.
5. Update the card title to "Top Posts by Engagement" (or confirm it remains correct).

—

Fix 2: Thread since into getTopPostsByEngagement()

1. Update the function signature in src/lib/db/queries/analytics.ts to accept an optional since: string | Date | undefined parameter, matching the pattern used by getAverageEngagementMetrics.

1. Add the time predicate to the query. Decide which timestamp to filter on:

The snapshot-time filter is recommended for consistency and to allow surfacing older posts that had recent engagement spikes.

• em.snapshot_at >= ? — answers "which posts had recorded activity in this window?" (consistent with getAverageEngagementMetrics).
• p.published_at >= ? — answers "which posts were published in this window?".

1. Pass since at the call site in src/app/api/analytics/content/route.ts:20.

—

CLI Commands

Locate all call sites of getTopPostsByEngagement to audit parameter passing:

grep -rn "getTopPostsByEngagement" src/

Confirm the current query structure and identify all aggregate expressions:

grep -n "totalExpression\|impressions\|snapshot_at\|published_at" src/lib/db/queries/analytics.ts

Check the API route for parameter threading:

grep -n "since\|getTopPostsByEngagement\|publishedOverTime\|avgMetrics" src/app/api/analytics/content/route.ts

—

Configuration Snippets

Corrected API route call site (route.ts)

// Before
const topPosts = await getTopPostsByEngagement(userId, platform);

// After — thread since to match publishedOverTime and avgMetrics
const topPosts = await getTopPostsByEngagement(userId, platform, since);

Engagement-only aggregate in analytics.ts (Option 1 sketch)

// Add alongside or replacing totalExpression()
function engagementExpression(platform: Platform): SQL {
  // Sum only engagement counters — exclude impressions/views
  // Adjust field list to match actual schema columns per platform
  return sql`(
    COALESCE(em.likes, 0) +
    COALESCE(em.comments, 0) +
    COALESCE(em.shares, 0) +
    COALESCE(em.reposts, 0) +
    COALESCE(em.quotes, 0)
  )`;
}

> Note: Replace the field list with the actual engagement column names for each platform as defined in your schema. Do not include impressions, views, or equivalent reach columns.

Time predicate addition in getTopPostsByEngagement()

// Mirror the pattern from getAverageEngagementMetrics (analytics.ts:430)
...(since ? [sql`em.snapshot_at >= ${since}`] : [])

—

Verification

Verify Bug 1 fix (engagement ranking)

1. Open the Analytics Content tab.
2. Find a post with high views but low engagement (e.g., 4,210 views, 138 engagements).
3. Confirm it no longer appears at the top of "Top Posts by Engagement" if other posts have higher engagement counts.
4. Confirm the total or engagement bar value shown matches only engagement counters (likes + comments + shares + etc.), not the view count.

# Query the DB directly to confirm sort order
# Replace <USER_ID> and <PLATFORM> with real values
sqlite3 <DB_PATH> "
  SELECT post_id, SUM(likes + comments + shares + reposts + quotes) AS engagement,
         SUM(impressions) AS views
  FROM engagement_metrics
  GROUP BY post_id
  ORDER BY engagement DESC
  LIMIT 10;
"

Verify Bug 2 fix (date range respected)

1. Select "Last 7 days" in the range picker.
2. Confirm the Top Posts table changes to show only posts with snapshot activity within the last 7 days.
3. Switch to "Last 90 days" — confirm the table expands accordingly.
4. On a 7-day range over older content, the Top Posts table should now be empty or sparse, consistent with the near-empty stat cards above it.

# Confirm the API request includes `since` for topPosts
# Check network requests in browser DevTools or inspect server logs for the route handler
grep -n "since" src/app/api/analytics/content/route.ts

Rollback indicator: If the Top Posts table becomes unexpectedly empty after the fix on a range that previously showed results, verify that since is being serialized and passed correctly (check for timezone or ISO string formatting issues).

—

Prevention

1. Add a query contract test: Write an integration test for getTopPostsByEngagement() that inserts posts with known view and engagement counters, then asserts the sort order matches engagement rank, not view rank.

1. Add a parameter coverage test for the API route: Assert that all three data-fetching calls in route.ts (publishedOverTime, topPosts, avgMetrics) receive the same since value when a range is provided. A missing parameter to any one of them should be caught at CI time.

1. Lint rule or code review checklist: When adding a new data-fetching call to a range-aware API route, require explicit handling of since (either pass it or document why it is intentionally excluded).

1. Metric label validation: Add a frontend test or Storybook story that asserts the barKey prop value and the card title are consistent. A bar keyed to a field named total on a card titled "by Engagement" should fail a snapshot or label-matching check.

1. Document the row-sum invariant: Add a comment in totalExpression() and engagementExpression() explicitly stating which metric categories each aggregate includes, so future contributors understand why impressions are excluded and do not re-add them.

1. Track the snapshot-deduplication divergence: File a separate task to align getAverageEngagementMetrics (currently snapshot-count-weighted) with the per-post deduplication strategy used in the Top Posts table before any re-snapshot path is merged.