Scrape alert email revamp: BCC recipients (cross-tenant To: leak) + one cross-platform digest with links to the actual new posts

[sweetmantech]

The scrape-triggered "{artist} has new posts on Instagram" alert email has three defects found together on 2026-07-06 while re-scraping a pro customer's roster: it puts every watcher across every tenant in one visible To: line (cross-tenant disclosure), its LLM-generated body is generic and links no actual posts (reads as spam, leaks internal jargon), and it fires on every scrape rather than on genuinely new posts (one customer received 7 in one day). It also only exists for Instagram — no other platform notifies at all. Tracking the revamp of this email end-to-end.

Design decision 2026-07-06 (sweetman): one digest email per scrape, all platforms. Extending the alert per-platform would send 4+ emails per roster scrape. Instead: a single consolidated email covering all processed scraper results from the batch (per-platform sections), sent once. The per-platform alert pattern is superseded.

Correction 2026-07-09 (pre-merge sanity check on database#41): the design sketch's premise that "the scrape start handler already records each run in apify_scraper_runs (chat#1840)" was stale — that table never existed. database#39 (which would have created it) was closed unmerged 2026-07-03 when run polling moved to the capability model, so database#41's ALTER fails on prod with 42P01 (verified live). #41 is closed, superseded by database#47, which creates the table matching api#760's consumer contract (run_id, account_id, social_id, platform, batch_id, completed_at, new_post_urls) — digest bookkeeping only, not an ownership map (the chat#1840 decision stands).

Goal

A recipient gets one digest email per scrape batch (at most one per artist per day), covering all platforms scraped (Instagram, TikTok, X, YouTube, …), sent only when at least one platform found posts genuinely new to the platform; the email names and links the specific new posts per platform; and no recipient can ever see another account's email address. Concretely: api/lib/apify/sendApifyWebhookEmail.ts (send + body) and its single caller api/lib/apify/instagram/handleInstagramProfileScraperResults.ts (recipient gathering + trigger condition).

PRs (updated 2026-07-09)

One PR per task — api#758, database#47, api#759, api#760, and api#761 merged 2026-07-09 (see Done); only api#762 remains.

PR	Item	Base	State
api#758	BCC hard-fix: to: [RECOUP_FROM_EMAIL] + bcc: emails; guard-rail test that to/cc never carry more than our own address	main	✅ merged 2026-07-09 — see Done
database#41	Digest-batch schema: batch_id + completed_at + new_post_urls on apify_scraper_runs	main	❌ closed 2026-07-09 — ALTERed a table that never existed (see correction callout); superseded by database#47
database#47	Digest-batch schema: create apify_scraper_runs (run_id PK, account_id, social_id, platform, batch_id, completed_at, new_post_urls) + partial batch index	main	✅ merged + applied 2026-07-09 — see Done
api#759	Newness diff: reusable filterNewPostUrls (pre-upsert) gates the alert; persistence untouched	main	✅ merged 2026-07-09 — see Done
api#760	Digest aggregation: batch registration at scrape start, completion bookkeeping in the webhook, ONE email on last-run-complete; IG solo alert suppressed for batch runs	main	✅ merged 2026-07-09 — see Done
api#761	Deterministic template: shared renderer, per-platform sections, direct link to every new post, no LLM body, no vendor jargon (byte-identical output test)	main	✅ merged 2026-07-09 — see Done
api#762	Rate cap + audit: 1 digest per watched artist per 24h; every send recorded in email_send_log (scrape-digest:<batchId>)	base: #761 branch	🔄 open — closes the chain, 284-test sweep

Merge sequencing: api#758 shipped first and alone 2026-07-09 (one-line privacy fix for an active leak; nothing gated it). database#47 merged + applied to prod 2026-07-09 (database#41 closed same day, superseded). api#759 merged 2026-07-09 after preview verification (initial + post-branch-update re-run). api#760 merged 2026-07-09 after three preview verification rounds (see Done). api#761 merged 2026-07-09 after five feedback/verification rounds incl. real-account tests (see Done). Last up: api#762 (needs retarget-to-main + main sync). The full-chain E2E (one scrape → one digest; rerun → none) was verified pre-merge on #760's preview since its self-registered webhooks let previews receive Apify callbacks. No docs PR — internal notification email, no API contract.

Evidence (2026-07-06, account 26cba4e3-b652-4b9e-bccb-30b9fe5303be)

Cross-tenant To: line. The 20:59Z "ashnikko has new posts on Instagram" send went to: [patrick@methodmusic.co.uk, sidney@recoupable.com, sam.palm@wmg.com] — three different tenants, mutually visible. Cause: socials rows are global; instagram.com/ashnikko is watched by two independent Ashnikko artist entities (8e1e4e23… owned by patrick@…; 2b7d0b2b… owned by sam.palm@wmg.com + sidney@recoupable.com). The handler resolves social → all account_socials → all owner accounts → all emails, flattens to one array, and sendApifyWebhookEmail.ts passes it as to: on a single Resend send. A manager and the artist's label each learn the other monitors this artist on Recoup.

Generic, post-less body. The body is generated per-send by an LLM (generateText with "write beautiful html email … New Apify Dataset Notification"). Observed effects across the 7 sends that day: branding varies randomly per email ("Recoup · New Instagram dataset update", "Recoup Dataset Alert", "Recoup • Instagram Updates", "Hi there, We've found…"); internal vendor jargon ("Apify Dataset Notification") reaches customers; and although latestPosts (incl. URLs) is in the prompt, the emails do not reliably link the specific posts — the only stable CTA is a generic chat deep-link. Without the actual posts, "new posts available" is unverifiable by the reader and reads as spam.

Fires on every scrape, not on new posts — false positives proven. handleInstagramProfileScraperResults.ts sends whenever the dataset has latestPosts with URLs and the social row matches — there is no comparison against posts already stored. upsertPosts runs unconditionally and its result is not used to distinguish inserted-vs-existing. Three scrape waves that day (16:03, 16:09/16:16, 20:59Z) produced 7 alert emails to one customer, and the stored post timestamps prove at least 6 of the 7 announced nothing new: Kettama's newest Instagram post is 2026-06-26 (10 days old — yet 3 "new posts" emails fired for him that day), Disclosure's newest is 2026-07-04 (2 days old — 3 emails), and only Ashnikko actually posted that day (19:59Z), making her single 20:59Z alert the one arguably legitimate send. Every scrape of any profile with visible posts re-announces its entire recent feed as "new."

Alert sends bypass the email audit log. email_send_log contains zero rows matching these alert emails (they were verified via the Resend admin endpoint instead) — sendApifyWebhookEmail calls sendEmailWithResend directly rather than the logged send path, so there is no platform-side record of who was alerted when. Folded into planned PR #6.

Done

• api#761 — deterministic house-style template: linked posts, media, stats, honest X reporting.
  ✅ Shipped 2026-07-09 (squash-merged to main 8241b61).
  Closes both remaining template items: links the specific new posts (card per post with 72px thumbnail, escaped caption excerpt, UTC date, engagement stats, direct link) and replaces the per-send LLM body with renderScrapeDigestHtml — DESIGN.md achromatic style, artist name in subject/header/footer, Recoup logo → recoupable.com, fixed CTA → chat.recoupable.dev (a derived URL pointed previews at the API deployment). Media/stats enrichment: solo path from the in-memory dataset; digest path re-reads each run's dataset from Apify (getRunDigestSection), degrading to URL-only on failure. Found-and-fixed along the way: the branch's renderer was imported but never wired into sendScrapeDigestEmail (guard-rail test added); 4 orphaned pre-rename supabase files deleted; X accuracy — isOriginalTweet drops retweets/replies at the persistence layer (an all-retweet section had reported the original authors' stats) and X fetch depth raised 1→10 so authored posts survive the filter.
  Verified across five preview rounds 2026-07-09 incl. real-account E2Es on sweetman.eth profiles (IG newness diff against 233-post history; final digest: 7 authored posts across 3 platforms, X = originals + quote only). Sample: artifact. Full trail on the PR.

• api#760 — one consolidated digest per scrape batch, covering every platform.
  ✅ Shipped 2026-07-09 (squash-merged to main 0e0a305).
  POST /api/artist/socials/scrape mints a batch_id and registers every platform run in apify_scraper_runs (upsertApifyScraperRuns); each webhook completion books completed_at + the run's genuinely-new URLs (updateApifyScraperRun, zod-parsed JSONB via parseNewPostUrls); the batch's last completion assembles ONE digest with per-platform sections (maybeSendScrapeDigest → sendScrapeDigestEmail, BCC-only), and the legacy IG solo alert is suppressed for batch runs.
  Verified on previews 2026-07-09, three rounds: simulated-batch webhook test, full scrape-start E2E (real POST /api/artist/socials/scrape → 2 platforms → one digest 13 new posts across 2 platforms; immediate re-scrape → 0 new → none), and a post-review-refactor regression pass. Review feedback shipped: supabase helpers renamed to convention, generated Tables<"apify_scraper_runs"> types replace the hand-rolled shim, parseNewPostUrls moved to lib/apify/digest/.
  Known edge for api#762: an Apify webhook retry after batch completion re-sends the digest (verified live) — the 24h rate cap closes it; retry detection (skip when the run was already completed_at) also suggested.

• api#759 — scrape alerts fire only on genuinely new posts.
  ✅ Shipped 2026-07-09 (merged to main 1b81272).
  New lib/socials/filterNewPostUrls.ts diffs scraped post URLs against posts before the upsert (afterwards nothing is distinguishable as new); handleInstagramProfileScraperResults only calls sendApifyWebhookEmail when ≥1 URL is new. Persistence untouched.
  Verified twice on previews 2026-07-09 (run 1, run 2 after merging main into the branch to include #758): 12 new URLs → send (Resend-delivered); identical re-delivery → sentEmails: null with posts still persisted; 1-of-12 new → send; zero-posts dataset → early return. This closes the "7 emails/day for old posts" defect for the solo Instagram alert path.

• database#47 — create apify_scraper_runs digest-batch table.
  ✅ Shipped 2026-07-09 (merged to main 8e3b882; applied to prod via the Supabase integration the same day).
  Creates the table matching api#760's consumer contract — run_id TEXT PK, created_at, account_id, social_id, platform, batch_id, completed_at, new_post_urls JSONB — plus a partial batch_id index for sibling lookups. Scoped to digest bookkeeping only (not an ownership map; the chat#1840 capability-model decision stands). Loose ids/no FKs, and new_post_urls stores URLs rather than posts ids by design: posts.id churns under upsert/delete-rescrape (observed live 2026-07-09) while post_url is the durable natural key. Supersedes database#41, which ALTERed the never-created table (see correction callout).
  Verified on prod 2026-07-09 post-apply: pre-merge the table 404'd (42P01); post-apply GET /rest/v1/apify_scraper_runs selecting all 8 columns returns 200 with 0 rows.

• api#758 — BCC recipients, never a shared To: (the I complete mobile ui. #1 privacy fix).
  ✅ Shipped 2026-07-09 (squash-merged to main, aba6835).
  sendApifyWebhookEmail.ts now sends to: [RECOUP_FROM_EMAIL] + bcc: emails (Resend requires non-empty to; wrapper already passed bcc through). New unit test asserts recipients land in bcc and that to/cc never carry more than our own address regardless of recipient count (25-recipient guard-rail), plus no-send on empty recipients — RED→GREEN 3/3.
  Verified end-to-end on the PR preview 2026-07-09 (results comment): isolated [TEST] artist → POST /api/socials/{id}/scrape → Apify webhook → send; Resend's stored record shows to: ["Agent by Recoup <agent@recoupable.dev>"], cc: [], recipient in bcc only, last_event: delivered. Side finding cleaned up the same day: 8 stale persistent Apify webhooks deleted (comment).

Open — #2 digest revamp (one email, all platforms)

• YouTube + LinkedIn post persistence, so their sections can appear in the digest.
  • Why: the Goal says the digest covers all platforms scraped, but the YouTube and LinkedIn results handlers persist only the socials row (followers/avatar/bio) and return { social } — no posts rows, no filterNewPostUrls, no newPostUrls — so those platforms can never contribute a digest section. Observed live 2026-07-09: youtube.com/@mycowtf has 0 stored posts despite scrape history; a real-account digest test covered IG/TikTok/X only. Pre-existing gap (chat#1833 wired these handlers for socials only), surfaced by the digest work.
  • Fix: extend handleYoutubeProfileScraperResults and handleLinkedinProfileScraperResults to map dataset items to posts + social_posts (mirror the TikTok/X handlers: persistPostsForSocial + filterNewPostUrls, return newPostUrls), then add extractYoutubePosts / extractLinkedinPosts digest extractors (labels already handled — getPlatformLabel covers both).
  • Done when: a full scrape of an artist with fresh YouTube/LinkedIn content produces a digest with those sections (title/thumbnail/stats where the datasets provide them); rerun → their URLs diff to 0 and the sections are omitted.
• Max one digest per artist per day + audit log. (api#762)
  • Why: no rate limit → 7 emails/day observed; digest sends bypass email_send_log. (The genuinely-new-posts diff itself shipped in api#759/Veerbalsingh369/myc 2276 tool result UI get spotify artist top tracks success #760 — see Done; what remains is the cap + logging.)
  • Fix: suppress repeat digests for the same artist within 24h (check the last alert in email_send_log); record every digest send (scrape-digest:<batchId>). Also close the webhook-retry double-digest edge verified during Veerbalsingh369/myc 2276 tool result UI get spotify artist top tracks success #760's testing (skip when the completing run was already completed_at).
  • Done when: a second digest-worthy batch within 24h sends nothing; every digest send has an email_send_log row; re-delivering a completed run's webhook does not re-send.

Source references

• chat#1851 (closed) — the X-domain scrape work during which this was found; the 7-email burst was triggered by that day's verification scrapes.
• Send site: api/lib/apify/sendApifyWebhookEmail.ts (single caller: the Instagram results handler — fixing one function closes the exposure).
• Recipient gathering: api/lib/apify/instagram/handleInstagramProfileScraperResults.ts L84–101.
• Resend wrapper (already bcc-capable): api/lib/emails/sendEmail.ts.

[sweetmantech]

Side finding from api#758 preview verification, cleaned up 2026-07-09: the Apify account carried 8 stale persistent (account-level) webhooks — three generations of the pre-/api/apify results pipeline. All deleted (HTTP 204 each; 0 persistent webhooks remain):

Target	Actors	Lifetime dispatches	Notes
chat.recoupable.com/api/apify + one cursor-branch preview URL	IG profile, IG comments	159	chat-era (May–Jun 2025), superseded by api#483
api.recoupable.com/api/apify/webhooks/socials ×5	IG profile, TikTok, YouTube, Threads, X	~124,000	path never existed in the current api repo; every dispatch a 404 since the migration
f027e7371dcb.ngrok-free.app/api/apify/webhooks/socials	Facebook	2,567	dead dev tunnel

Deleted rather than repointed to .dev: every run our code starts already attaches a per-run ad-hoc webhook to the exact deployment that started it (getApifyWebhooks()), so account-level delivery would double-process every scrape — including duplicate alert emails, recreating a flavor of the over-sending this issue exists to fix. Nothing starts Apify runs without the ad-hoc webhook (tasks only polls via the api; chat has no Apify code), and these had been 404ing for months with zero fallout.

[sweetmantech]

Follow-up captured 2026-07-09 (sweetman, during api#761 review): the digest/alert CTA should eventually land on a page that notifies the internal team of an email→app conversion (prove the inbound funnel works). For now api#761 hard-points the CTA at https://chat.recoupable.dev (previously it derived from the deployment base URL, which on previews was the API deployment itself). The tracking landing page (e.g. a /from-email redirect that pings Slack/Attio before forwarding into chat) is chat-repo work — not part of the #1855 email chain; file separately when picked up.