Skip to content

Reference creatortrack social app

URL: https://mkdocs.justinsforge.com/memory/general/reference_creatortrack_social_app/

Built on feat/social-data-layer-and-scripts (plan memory/plans/creatortrack-social-data-layer-and-scripts-app-2026-07-14.md, Tasks 1-11) plus feat/social-meta-ingest (plan memory/plans/creatortrack-social-meta-ingest-2026-07-15.md, M1-M5). Not yet merged to main or deployed to prod as of this writing; see Ship checklist below.

What it is

Social is a route app (no node face) that owns raw multi-platform video/post ingest. It is deliberately the data layer only: it syncs metrics, stores transcripts, extracts per-video structure, and links cross-posted twins. It never generates content. Scripts (see reference_creatortrack_scripts_app.md) is the consumer that reads Social's tables and never calls a platform API itself.

Where the data lives

app.social_videos: one row per video/post per platform, UNIQUE (workspace_id, platform, video_id), RLS via core.apply_workspace_rls. Columns of note: platform (CHECK youtube/instagram/tiktok/facebook), views/likes/comments (nullable, never fabricated to 0), shares (still NOT NULL DEFAULT 0 for non-Instagram platforms, a known data-layer gap, see the productization file), average_view_percentage/average_view_duration/impressions/impression_ctr (YouTube-only, nullable), transcript/transcript_source, raw jsonb (platform API payload plus nested derived data, see the wipe-bug gotcha below), embedding vector(384) (added by Scripts' Task 15, pgvector, no ANN index at this scale), crosspost_of_id (self-FK, non-YouTube row points at its YouTube twin, one direction only), script_id (nullable FK to app.generated_scripts).

app.social_crosspost_exclusions: permanent human-rejected cross-post pairs, (source_id, excluded_candidate_id) PK, written via scripts/match-crossposts.ts --reject.

Live counts on ws 85 (Gus The Bass, the only workspace with real data as of this writing): 2646 rows across 4 platforms, YouTube 548, Instagram 505, Facebook 706 (699 with retention-eligible data), TikTok 887 (incl. 6 photo-carousel posts with NULL duration).

Transcripts

transcript/transcript_source are YouTube-only (yt-dlp auto-captions, no whisper wired, isWhisperAvailable() probes and throws loud if a future env has whisper but the transcription path isn't implemented). Sentinel semantics, read carefully: transcript = '' (with transcript_source IS NULL) means "confirmed no caption track exists," NOT "never attempted." transcript IS NULL means never attempted. A naive GROUP BY transcript_source cannot distinguish these; always filter explicitly (transcript IS NOT NULL AND transcript <> '' for real transcripts).

Corpus size: 515 real transcripts ingested (94% of 548), 32 confirmed-no-caption, 1 not-yet-attempted (an unaired live event). During Scripts' Task 16 build, 18 of the 515 were found to be junk (meme/music-overlay audio, not creator speech) and quarantined, leaving 497 verified transcripts as the corpus Scripts actually trains against. ~140k tokens at the 515-transcript stage (chars/4 estimate).

raw.structure (Task 13): per-video LLM-extracted {hook_text, hook_type, beats[], cta, tone_tags[], signature_phrases[], words_per_second}. beats[].position is a 0-1 fraction of video progress, not a real timestamp (VTT cue timing is stripped during caption cleanup, no timing signal survives). 100% of the 515 real-transcript rows are structured.

Export/restore, the durable source of truth. A dev-slot wipe destroyed the social_videos table once mid-plan. data/creatortrack/social-transcripts-ws85.jsonl (workspace_id/platform/video_id/title/transcript/transcript_source/raw.structure, one JSON object per line) plus scripts/forge_creatortrack_transcripts_restore.py are now the canonical way to recover this corpus without re-scraping (which takes hours and is rate-limited, see below). Restore is conservative: only fills null fields on matched rows, never overwrites. Deliberately NOT psql \copy (COPY's text format corrupts the JSON's backslash escapes).

Gotcha: raw JSON wholesale-replace wipes derived data

See reference_creatortrack_social_raw_json_extraction_wipe_bug.md. Any upsert into app.social_videos.raw (a metrics resync, a new platform ingest) must preserve-merge raw.structure and any other nested derived key, never blind-replace the column. Fixed in fe89ba2.

Cross-post linking

crosspost_of_id links a non-YouTube row to its YouTube twin. Matching (scripts/match-crossposts.ts, lib/social/crosspost.ts) uses a two-tier confidence scheme (duration+trigram-title-similarity, or exact-normalized-title when duration is unavailable), a template-swap guard (Gus's recurring "WILL MY PET BASS EAT A ___" title format defeats plain lexical similarity), and a global reference pass with no date-window restriction (Gus reposts up to 1168 days after the original). On ws 85: 175 auto-linked (56 FB / 49 IG / 70 TikTok), 650 ambiguous sitting in a gitignored per-workspace review report (scripts/.crosspost-review.<ws>.md), 1273 no-match. The 650-pair ambiguous queue has no in-app review UI yet, only the flat-file report and --reject CLI (see productization file). Tier 3 (embedding-similarity matching, using the same embedding column Scripts uses) is a documented but unimplemented extension point that would close the residual lexical-collision false-positive class.

Scheduled sync (NOT enabled)

app/api/cron/social-sync/route.ts: systemd timer (forge-creatortrack-social-sync.service/.timer in forge/scripts/systemd/) posts to a shared-secret-gated route on the running Next.js process, matching the established Campaigns-sync pattern (systemd timer -> shell wrapper -> HTTP POST, no in-app scheduler, no Vercel cron). Secret env var SOCIAL_SYNC_CRON_SECRET, lives in ~/.forge-secrets/creatortrack-auth.env. Workspace discovery uses an owner-gated SECURITY DEFINER function (core.social_sync_workspace_ids) rather than a plain RLS join, because core.workspaces/core.memberships have no owner-bypass policy (a real per-user privacy boundary, not a bug): only workspaces with a live app.api_connections row, plus the personal-apps owner's own personal workspace, get synced by cron. Neither the service/timer files nor the DB migration have been enabled/applied to prod; this is inert until ship.

Operational gotchas

  • RLS psql zero-rows trap. A bare psql connection with no GUCs set silently returns 0 rows from any app.* table (RLS active, no bypass), not an error. Use the admin role (PGADMIN_USER/PGADMIN_PASSWORD in ~/.forge-secrets/forge-data.env) or set app.is_owner/app.user_id GUCs explicitly for manual verification.
  • Retention/impressions/CTR are YouTube-only and null-by-default. average_view_percentage etc. never coalesce to 0; a missing value is missing, not zero. Impressions/CTR specifically require Justin to reconnect his YouTube OAuth with the broader analytics scope on ws 85; pending as of this writing.
  • Instagram has no duration field at all, platform-wide, so IG average_view_percentage is permanently NULL by design (not a bug to chase); IG does carry reach/saved (from Graph API insights) and Reels-only watch time.
  • Facebook's shares column is not real data: it's a hardcoded schema default (no such Graph API field exists for Facebook), unlike Instagram/TikTok's genuine shares metrics. Don't trust it in any Facebook-specific display or analysis.
  • Meta app review: not required for Justin's own Gus The Bass accounts (he is the app's own admin), but IS required before any other CreatorTrack user can connect their own Meta accounts.
  • TikTok: Login Kit + sandbox connect works today; the Business API tier (needed for real retention/watch-time data beyond views/likes/comments/shares) requires a separate TikTok audit, not yet requested.

Ship checklist (when this branch merges)

  1. Apply all pending migrations to prod lifeos (social_videos, voice_profiles/generated_scripts, embedding column, crosspost_of_id + exclusions table, views/nullable fixes).
  2. Generate SOCIAL_SYNC_CRON_SECRET, add to ~/.forge-secrets/creatortrack-auth.env, confirm creatortrack-prod.service's EnvironmentFile= picks it up, restart.
  3. Install and enable forge-creatortrack-social-sync.{service,timer} (~/.config/systemd/user/, systemctl --user daemon-reload && systemctl --user enable --now forge-creatortrack-social-sync.timer). Do this only after prod has the new route deployed.
  4. Restore the durable transcript/structure export against prod (scripts/forge_creatortrack_transcripts_restore.py), then re-run the backfill scripts (backfill-social-videos.ts, backfill-script-embeddings.ts, match-crossposts.ts) against prod data, since the dev-slot corpus and prod's live catalog will have diverged.
  5. Justin reconnects YouTube OAuth on ws 85 for impressions/CTR.
  6. TikTok connect is already live; no action needed there.
  7. Someone (Justin, or a follow-up task) works through the 650-pair cross-post ambiguous review queue via the --reject CLI or a future in-app UI.