Persist per-post engagement metrics so briefings can rank best-performing content

[sweetmantech]

Follow-up to chat#1840 (closed 2026-07-03), which made TikTok/X posts persist. Posts now land in posts/social_posts, but the rows carry only post_url + updated_at — no engagement numbers — so "best performing content" cannot be ranked from the database, for any artist. Surfaced by the Brauxelion weekly-briefing task, whose top-content section currently has to re-read live scrape datasets every run (works post-#1840, but costs scrape credits weekly and accumulates no history).

Goal

GET /api/artists/{id}/posts returns per-post engagement (platform, likes, views, comments, shares/reposts, metrics_updated_at) so an agent can rank an artist's top content with one authenticated call — no re-scrape, comparable week over week.

Proposed contract

Each item in the posts array gains nullable fields: platform, likes_count, views_count, comments_count, shares_count, metrics_updated_at. Null for posts persisted before this ships or from sources without the metric.

Merge sequencing

1. docs — extend the Get Artist Posts response schema (contract first).
2. database — migration adding the metric columns (see decision below).
3. api — per-platform handler mapping + endpoint response; PRs target main.

Decision surface (pick before implementing)

• Latest-snapshot columns on posts (recommended): add the columns above; change upsertPosts from ignoreDuplicates: true to a merge so a re-scrape refreshes metrics. KISS; one row per post; loses metric history.
• Append-only post_measurements (a song_measurements sibling): keeps time-series growth per post, more plumbing, and GET needs a latest-per-post join. Only worth it if we want engagement trends per post, not just current ranking.

⚠️ Whichever lands: upsertPosts is currently ignoreDuplicates: true by design for webhook replays — the merge variant must stay idempotent for replays (same run id → same values, safe).

Field sources (verified in real actor datasets, 2026-07-03 runs)

platform	run	fields
TikTok (clockworks~tiktok-scraper)	jQzDU3uR2JbEpUVGF	playCount, diggCount, commentCount, shareCount, repostCount
X (apidojo~twitter-scraper-lite)	RRV75MiciJbCYhhwX	viewCount, likeCount, replyCount, retweetCount, quoteCount, bookmarkCount
Instagram	(profile snapshot latestPosts)	likes/comments per post item
YouTube / LinkedIn	via posts depth param (docs #258/#260)	view/like/comment; reactions breakdown

Done when

• A TikTok and an X scrape → posts rows carry the platform's counts with fresh metrics_updated_at.
• GET /api/artists/{id}/posts returns the new fields (null-safe for legacy rows), verified live against a real artist.
• The Brauxelion weekly-briefing top-content section can rank from this endpoint alone (no in-run scrape reads).

Source references

• chat#1840 closing comment (what shipped + this gap), api#753 (persistPostsForSocial — the write path these columns extend).
• api/lib/supabase/posts/upsertPosts.ts (ignoreDuplicates), api/lib/apify/{tiktok,twitter,instagram} handlers, api/types/database.types.ts posts shape.