Skip to content

Plan: CreatorTrack Social data layer rebuild + new Scripts app (voice profiles)

URL: https://mkdocs.justinsforge.com/memory/plans/creatortrack-social-data-layer-and-scripts-app-2026-07-14/

Date: 2026-07-14 Status: APPROVED by Justin 2026-07-14. Ready to execute. Repo: /home/justinwieb/creatortrack Branch: feat/social-data-layer-and-scripts (one branch, all four parts, single ship)

Context for the executing agent

Read this whole section before touching code. It is the result of a live audit of the database and the codebase on 2026-07-14, and several "obvious" assumptions turned out to be wrong.

The origin question

Justin wants to "train a model" on ~150 Gus the Bass short-form videos so CreatorTrack can generate new scripts in that voice.

It is not a fine-tune, and the plan must not become one. The entire Gus short-form corpus is roughly 25k tokens, which fits in a single Claude context window with room to spare. There is nothing a fine-tune could learn from that corpus that we cannot simply show the model on every request. Fine-tuning 150 examples would overfit onto verbal tics and trade Opus-class reasoning for an 8B model. The correct build is a voice profile: a structured, human-readable style document plus a retrieval library of the creator's own best-performing scripts, injected into the prompt at generation time. Revisit fine-tuning only at tens of thousands of examples across many creators.

What the audit actually found

The Social app's OAuth is fine. This was the surprise. lib/google-connections.ts:38-41 already requests both youtube.readonly and yt-analytics.readonly. It sets access_type=offline and prompt=consent, and fails loud if Google returns no refresh token (lib/google-connections.ts:136-138,178). The refresh token is persisted AES-256-GCM encrypted in app.api_connections.credentials. syncYouTube already calls the Analytics API v2, not just the public Data API (lib/youtube.ts:51-52).

A red herring to not chase: core.auth_account has zero refresh tokens. That table belongs to the NextAuth login flow, which only ever requests openid email profile. It is a different code path from the social connect flow. It is not broken.

So we already hold owner-level Analytics authority on Gus's channel. We have the right to ask for retention and CTR and simply never ask. That is the core insight of this plan: most of Part 2 is changing what we request, not changing what we are permitted to request.

What is genuinely broken:

  1. There is no scheduled sync anywhere in the repo. syncSocial has exactly two callers: app/(suite)/[ws]/social/page.tsx:63 (fires on page open) and app/api/apps/social/route.ts:93 (the Sync button). No cron, no vercel.json, no systemd timer. Snapshot metrics (subscribers, views, video_count, followers) upsert at metricDate = chicagoTodayISO() (lib/social/data.ts:379-381), so they are only true for days someone happened to open the page. Skip a week and that week is a permanent hole, because these APIs only return current values, never history. This is silent, ongoing, unrecoverable data loss. It is why Gus's channel snapshot only has 4 days of history (2026-07-11 through 07-14) despite 92 days of daily rows.
  2. YouTube daily series is self-healing (re-pulls a rolling 90d each sync, data.ts:354), but 90 days is therefore a hard backfill ceiling. Anything older is gone forever.

  3. growthPct (data.ts:254-258) silently degrades across gaps rather than erroring.

  4. Per-video data is a 12-item JSON blob, not rows. data.ts:390 writes a single recent_videos metric row with { videos: uploads } in raw. There is no social_videos table. No per-video history, no trends, nothing queryable. The 12-cap is hardcoded in all four platforms: data.ts:376 and youtube.ts:165 (default max = 12; the YouTube API allows 50/page with unlimited paging), instagram.ts:36 (limit: "12"), tiktok.ts:51 (max_count: 12). No pagination anywhere. The app structurally cannot see video 13. Gus has 547.

  5. The blob's per-video fields are: videoId, title, thumbnail, publishedAt, views, likes, comments, engagementRate. No watch time, no retention, no impressions, no CTR, no duration.

  6. The YouTube Analytics query asks for 4 metrics when it could ask for 20. fetchDailyAnalytics (lib/youtube.ts:122-130) requests metrics: views,estimatedMinutesWatched,subscribersGained,subscribersLost with dimensions: day. Missing, all available on the scope we already hold: averageViewPercentage, averageViewDuration, audienceWatchRatio (retention curve), impressions, impressionClickThroughRate, insightTrafficSourceType, and ageGroup,gender. videos.list requests only snippet,statistics (youtube.ts:177), so we do not even get contentDetails.duration, meaning we cannot distinguish Shorts from long-form.

  7. yt-analytics-monetary.readonly is the one genuinely missing scope (gates revenue/RPM/CPM). Irrelevant to voice profiles; add it while we are in there.

  8. Instagram, TikTok, and Facebook are effectively fiction. lib/tiktok.ts:4 and lib/instagram.ts:4 both carry "UNTESTED against a live token" headers. lib/facebook.ts:8 says "UNTESTED against a live Meta app" and captures exactly 2 metrics. No daily time-series for any of them. Instagram and Facebook read a raw stored access_token (data.ts:402, data.ts:480) with no refresh logic; Meta tokens expire in ~60 days, so these will silently start failing and nothing renews them. Messages sync for IG/TikTok is an explicit stub (lib/social/messages.ts:39-40, wired: false).

  9. 9 of 11 App Playbook Section 7 definition-of-done rows FAIL for Social: activity feed, notifications, automations, cockpit, getting-started, admin analytics, help site, search, dashboard, mobile tabs, dev-verify script. Also missing: loading.tsx (the page does a network sync server-side on open with zero loading UI), SidePeek (clicking a video does nothing), useWorkspaceChanges live refresh, and PersonalAppEmptyState (empty states are bare divs, SocialClient.tsx:150). AppSidebar rail is empty (SocialShell.tsx:9).

What is already correct, do not "fix" it: idempotency is genuinely right. The unique key (workspace_id, platform, account, metric_date, metric_key) (migration 20260703T184852:33) matches the onConflictDoUpdate target (data.ts:341-345). Fail-loud is honored in the fetchers. Connect-state copy (connectInfo, data.ts:177) is well done.

The architecture, and why Scripts is a separate app

Social is a data layer. Scripts is a consumer of it. Social's job is to know the truth about what was published and how it performed: every video, its transcript, its retention, its CTR. Facts. Scripts' job is to generate using those facts.

The seam: - Transcripts live in Social, because a transcript is a property of a video, exactly like its view count. Social owns app.social_videos. - Scripts owns app.voice_profiles and app.generated_scripts, and reads app.social_videos. - Scripts never calls YouTube. Social never generates anything.

This split is load-bearing for the product, not just for tidiness. The voice profile is derived entirely from data that any workspace's connected channel produces. A new user signs up, connects YouTube, and the same pipeline that built Gus's profile builds theirs, with zero Gus-specific code. It is workspace-scoped like everything else in CT. Nothing in this plan may hardcode Gus, workspace 85, or channel UC-L81FotWBnmnz6ajNcQKNA outside of a backfill script's CLI argument. If you find yourself special-casing Gus, stop and reconsider.

The commercial logic, which should inform tradeoffs: "connect your channel, we learn your voice from your best-performing videos, now write in it" is the most sellable surface in CreatorTrack, and the moat is the customer's own data. The analytics app is the data moat for the scripts app. That is why we build the data layer properly rather than patching it.

Environment and conventions

  • DB: Postgres lifeos, user lifeos_app, creds in ~/.forge-secrets/forge-data.env (PG* vars). CreatorTrack reads it via lib/db.ts.
  • Workspaces live in core.workspaces (NOT app.workspaces). Gus The Bass = id 85, slug gus-the-bass. (Note: workspace 74 "Gus Outdoor Co." is a different, empty workspace. Do not confuse them.)
  • All app tables are RLS-protected via core.is_owner() reading the app.is_owner / app.user_id GUCs. Every new table MUST carry workspace_id and MUST have RLS policies matching the existing social_metrics pattern.
  • Migrations: db/migrations/YYYYMMDDTHHMMSS_description.sql, applied against lifeos. Check whether sibling migrations issue explicit GRANT ... TO lifeos_app (roughly 54 of 146 do; the social ones do not, which may be fine via default privileges, but verify grants work before assuming).
  • Dev: use a warm CT dev slot (forge_creatortrack_slot_claim.sh, ports 3067-3069). WORKSPACE_DEV_AUTH=1. Run next dev --webpack (turbopack breaks on worktree symlinks). Commit often: the dev-slot reaper deletes worktrees with uncommitted work.
  • Read ~/creatortrack/docs/APP-PLAYBOOK.md before building the Scripts app. It is the definition of done for a CT app. Review your diff against its Section 7 table and append any gaps back into the fixing commit.
  • Ship via /ship, never a manual prod deploy. Migrations gate applies before deploy.
  • Forge doctrine: no em dashes in any prose or committed markdown. Fail loud, no catch-and-ignore. Idempotent by default.

Task list

Part 1: Social data foundation

Task 1: Create app.social_videos table

  • Files: db/migrations/20260714T000001_social_videos.sql, db/schema.ts
  • What: One row per video per platform. Columns: id, workspace_id, platform, account, video_id, script_id (nullable FK, for cross-post dedupe later), title, description, published_at, duration_seconds, is_short (bool), thumbnail_url, permalink, plus metrics: views, likes, comments, shares, engagement_rate, estimated_minutes_watched, average_view_duration, average_view_percentage, impressions, impression_ctr, and raw jsonb for anything unmodeled. Plus transcript (text, nullable), transcript_source (captions | whisper), metrics_updated_at, created_at, updated_at.
  • Unique key (workspace_id, platform, video_id) for upsert idempotency. RLS policies copied from the social_metrics pattern. script_id stays null this round; the column exists so the cross-post dedupe lands without a second migration.
  • Verification: psql $LIFEOS -c "\d app.social_videos" shows the table; psql $LIFEOS -c "SELECT * FROM app.social_videos LIMIT 1" succeeds as lifeos_app (confirms grants + RLS).
  • Commit: feat(social): app.social_videos table, one row per video

Task 2: Remove the 12-video cap and add pagination

  • Files: lib/youtube.ts (fetchRecentVideos, ~line 165), lib/social/data.ts (~line 376), lib/instagram.ts:36, lib/tiktok.ts:51
  • What: Replace the hardcoded max = 12 with a paginating fetch that walks playlistItems via pageToken until exhausted or a caller-supplied limit is hit. Request snippet,statistics,contentDetails on videos.list so duration comes back. Default limit for incremental sync stays modest (e.g. 50); backfill passes a high limit. Do the same for IG/TikTok cursors so the code is honest, even though those platforms are not connected yet.
  • Verification: npx tsx scripts/dev-verify-social-videos.ts --ws 85 --platform youtube --limit 100 prints >12 videos with durations populated.
  • Commit: feat(social): paginate video fetch, drop hardcoded 12-cap

Task 3: Enrich the YouTube Analytics query (per-video dimension)

  • Files: lib/youtube.ts (fetchDailyAnalytics ~line 122, plus a new fetchVideoAnalytics), lib/google-connections.ts:38-41
  • What: Add a fetchVideoAnalytics that queries the Analytics API with dimensions: video and metrics: views,estimatedMinutesWatched,averageViewDuration,averageViewPercentage,impressions,impressionClickThroughRate (impression metrics may require a separate report call; split it if the API rejects the combination). Keep the existing day-dimensioned call for the channel time series. Add yt-analytics-monetary.readonly to the youtube scope group.
  • Note: This works retroactively. YouTube serves Analytics history, so this backfills retention and CTR across the full catalog on first run. This is the highest-value task in the plan.
  • Verification: npx tsx scripts/dev-verify-social-videos.ts --ws 85 --analytics prints per-video averageViewPercentage and impressionClickThroughRate for at least 10 videos.
  • Commit: feat(social): per-video YouTube Analytics, retention + CTR + impressions

Task 4: Write per-video rows in syncYouTube

  • Files: lib/social/data.ts (syncYouTube, ~lines 371-390)
  • What: Stop writing the recent_videos blob as the source of truth. Upsert into app.social_videos on (workspace_id, platform, video_id), merging Data API stats with Analytics API metrics. Keep writing the blob for one release so the existing UI does not break, and mark it deprecated in a comment.
  • Verification: open the Social page for ws 85, then psql $LIFEOS -c "SELECT count(*) FROM app.social_videos WHERE workspace_id=85" returns >12.
  • Commit: feat(social): persist per-video rows from syncYouTube

Task 5: Backfill Gus's full catalog

  • Files: scripts/backfill-social-videos.ts (new, in the CT repo)
  • What: CLI taking --ws <id> and optional --platform. Walks the entire uploads playlist with pagination, pulls Analytics per video in batches (respect the API's max video IDs per request), upserts into app.social_videos. Idempotent: re-running updates in place. Handles quota exhaustion by checkpointing progress and exiting cleanly with a resume message, not by dying halfway.
  • Verification: npx tsx scripts/backfill-social-videos.ts --ws 85 completes; psql $LIFEOS -c "SELECT count(*), count(average_view_percentage) FROM app.social_videos WHERE workspace_id=85" shows ~547 rows with retention populated on most.
  • Commit: feat(social): full-catalog backfill script

Task 6: Scheduled sync

  • Files: app/api/cron/social-sync/route.ts (new), plus a systemd timer at /home/justinwieb/forge/systemd/forge-ct-social-sync.{service,timer} OR a Vercel cron entry, whichever matches how CT prod actually runs (check first, do not assume)
  • What: A route that iterates every workspace with a live social connection and calls syncSocial. Auth-gate it with a shared secret header so it is not publicly callable. Runs daily. This is what stops the ongoing permanent data loss, so it is not optional.
  • Verification: curl -fsS -H "x-cron-secret: $SECRET" http://127.0.0.1:3070/api/cron/social-sync | jq . returns per-workspace results; systemctl --user status forge-ct-social-sync.timer is active (if systemd route).
  • Commit: feat(social): scheduled daily sync, stop the snapshot data loss

Part 2: Social app rehab

Task 7: Token refresh for Meta and TikTok

  • Files: lib/social/data.ts (~lines 402, 480), lib/instagram.ts, lib/facebook.ts, lib/tiktok.ts
  • What: Instagram and Facebook currently read a raw stored access_token with no refresh. Implement Meta long-lived token exchange and refresh-before-expiry, matching the pattern accessTokenForConnection uses for Google. Fail loud with a clear "reconnect required" state, surfaced in the UI, when refresh is impossible.
  • Verification: npx tsx scripts/dev-verify-social-tokens.ts reports token expiry and refresh status per connection without throwing.
  • Commit: fix(social): Meta/TikTok token refresh, no more silent 60-day death

Task 8: Rate-limit and quota handling

  • Files: lib/youtube.ts:62, lib/instagram.ts, lib/tiktok.ts, lib/social/data.ts:394
  • What: A 403 quotaExceeded is currently indistinguishable from a scope error; both become a generic thrown string. Parse the error body, distinguish quota / rate-limit / auth / other, add retry with backoff on 429 and 5xx, and log with the console.error("[apps/social]", ...) namespace the Playbook Section 4 requires. Surface a broken connection as a notification, not just a string on a page nobody is looking at.
  • Verification: npx tsx scripts/dev-verify-social-errors.ts (simulates 403/429 responses) shows correct classification and backoff.
  • Commit: fix(social): classify quota vs auth errors, retry with backoff

Task 9: Social UI, per-video views

  • Files: app/(suite)/[ws]/social/loading.tsx (new), app/(suite)/[ws]/social/SocialClient.tsx, SocialShell.tsx:9
  • What: Add loading.tsx (the page currently does a server-side network sync on open with no loading UI). Add a sortable/filterable per-video table reading app.social_videos, with retention and CTR columns. Add SidePeek on a video row showing its metrics, transcript, and retention curve. Replace bare-div empty states with PersonalAppEmptyState. Populate the AppSidebar rail with counts. Wire useWorkspaceChanges for live refresh.
  • Verification: on a dev slot, open /gus-the-bass/social, sort by retention, click a video, side peek opens with transcript and metrics.
  • Commit: feat(social): per-video table, side peek, loading + empty states

Task 10: App Playbook Section 7 parity pass

  • Files: lib/activity.ts, lib/notifications.ts, lib/automations.ts, lib/cockpit.ts, lib/search.ts, lib/mobile-tabs.ts, lib/editorShortcuts.ts, scripts/dev-verify-social.ts (new)
  • What: Register Social in each of the 9 failing Section 7 surfaces. Re-read the Playbook's Section 7 table and work it row by row. Append any newly discovered gaps back into the Playbook in this same commit, per its own instructions.
  • Verification: npx tsx scripts/dev-verify-social.ts passes; Social appears in global search, the cockpit, and mobile tabs on a dev slot.
  • Commit: feat(social): App Playbook section 7 parity

Part 3: Transcripts

Task 11: Transcript ingest, captions first

  • Files: scripts/ingest-transcripts.ts (new, CT repo), lib/transcripts.ts (new)
  • What: For each social_videos row lacking a transcript, pull YouTube auto-captions via yt-dlp --write-auto-sub --skip-download. Store into social_videos.transcript with transcript_source='captions'. Fall back to Whisper (local, on Console) only for videos with no captions available, marking transcript_source='whisper'. Idempotent: skips rows that already have a transcript unless --force.
  • Verification: npx tsx scripts/ingest-transcripts.ts --ws 85 completes; psql $LIFEOS -c "SELECT transcript_source, count(*) FROM app.social_videos WHERE workspace_id=85 GROUP BY 1" shows near-full coverage.
  • Commit: feat(social): transcript ingest, captions with Whisper fallback

Part 4: Scripts app (voice profiles)

Before starting Part 4, read ~/creatortrack/docs/APP-PLAYBOOK.md end to end. This is a new app and must satisfy the full definition of done, not 2 of 11 rows like Social did.

Task 12: Scripts app schema

  • Files: db/migrations/20260714T000002_scripts_app.sql, db/schema.ts
  • What: app.voice_profiles (id, workspace_id, name, profile jsonb (the voice card), source_video_count, generated_at, model, created_at, updated_at). app.generated_scripts (id, workspace_id, voice_profile_id, topic, platform_target, body jsonb (hooks[], beats[], cta), source_video_ids (the retrieved few-shot examples, for auditability), task_id nullable, page_id nullable, created_at). RLS on both, matching the existing pattern.
  • Verification: psql $LIFEOS -c "\d app.voice_profiles" and \d app.generated_scripts succeed; insert/select as lifeos_app works.
  • Commit: feat(scripts): voice_profiles + generated_scripts schema

Task 13: Per-video structure extraction

  • Files: lib/scripts/extract.ts (new)
  • What: One LLM pass per video producing JSON stored in social_videos.raw.structure: hook_text, hook_type (question | contradiction | cold_open | pov | stat), beats[] with timestamps, cta, tone_tags[], words_per_second, signature_phrases[]. Batched, idempotent, skips already-extracted rows.
  • Verification: npx tsx scripts/extract-structure.ts --ws 85 --limit 10 populates structure for 10 videos; spot-check one against the actual video.
  • Commit: feat(scripts): per-video structure extraction

Task 14: Voice profile synthesis

  • Files: lib/scripts/profile.ts (new), scripts/build-voice-profile.ts (new)
  • What: Aggregate pass over a workspace's videos, weighted by retention (average_view_percentage), not raw views, producing the voice card: hook taxonomy with frequencies, sentence-length distribution, vocabulary and banned words, pacing targets, structural template, explicit do/don't rules. Human-readable and hand-editable, since that is the entire advantage over a fine-tune. Writes to app.voice_profiles.
  • Verification: npx tsx scripts/build-voice-profile.ts --ws 85 writes a profile; read the voice card out and confirm it describes Gus recognizably.
  • Commit: feat(scripts): performance-weighted voice profile synthesis

Task 15: Script generation via retrieval few-shot

  • Files: lib/scripts/generate.ts (new), app/api/apps/scripts/generate/route.ts (new)
  • What: Given a topic and target platform: embed the topic, retrieve top-k most similar high-retention videos from social_videos, build a prompt of voice card + retrieved transcripts + beat template + topic, and generate. Output 3 hook variants, timed beats, CTA, shot list. Persist to app.generated_scripts with source_video_ids recorded so any output can be audited back to the examples that produced it.
  • Verification: curl -fsS -X POST .../api/apps/scripts/generate -d '{"ws":85,"topic":"catching bass in heavy vegetation"}' | jq . returns a script that reads like Gus.
  • Commit: feat(scripts): retrieval few-shot script generation

Task 16: Scripts app UI

  • Files: app/(suite)/[ws]/scripts/* (new: page.tsx, loading.tsx, ScriptsClient.tsx, ScriptsShell.tsx), app/(suite)/scripts/page.tsx (thin redirect), lib/apps.ts (catalog entry)
  • What: Voice profile view (read and hand-edit the card, regenerate button, "built from N videos"), script generator (topic in, script out), script library. Send a generated script to a Task or a Page. Full Playbook compliance: side peek, empty states, loading, mobile tabs.
  • Verification: on a dev slot, open /gus-the-bass/scripts, generate a script, send it to a Task, confirm the task exists.
  • Commit: feat(scripts): Scripts app UI

Task 17: Scripts as a chat-agent tool

  • Files: wherever CT registers chat agent tools (grep lib/chat / agent tool registry)
  • What: Expose script generation as a tool so Justin can ask for a script conversationally in CT chat.
  • Verification: in CT chat on ws 85, "write me a Gus script about topwater frogs" returns a generated script.
  • Commit: feat(scripts): chat-agent tool for script generation

Task 18: Holdout eval

  • Files: scripts/eval-voice-profile.ts (new)
  • What: Hold out 20 videos from profile synthesis. Generate a script from each holdout's topic alone, score against the real transcript (embedding similarity + an LLM judge on voice match). Report a score. This is the gate: if generated scripts do not measurably resemble held-out real ones, the profile is not working and we say so rather than shipping it.
  • Verification: npx tsx scripts/eval-voice-profile.ts --ws 85 prints a score table; Justin blind-votes 5 generated vs 5 real.
  • Commit: feat(scripts): holdout eval for voice profile quality

Task 19: Register and document

  • Files: /home/justinwieb/forge/MEMORY.md, /home/justinwieb/forge/memory/general/reference_creatortrack_scripts_app.md (new), /home/justinwieb/forge/memory/general/reference_creatortrack_social_app.md (new), ~/creatortrack/docs/APP-PLAYBOOK.md (append any gaps found)
  • What: Topic files for both apps (what they are, the Social/Scripts seam, where data lives, the "not a fine-tune" decision and why). One-line index entries in MEMORY.md. Include the URL: line on both new .md files.
  • Verification: bash /home/justinwieb/forge/scripts/forge_eval_run.sh passes.
  • Commit: docs(scripts): register Scripts + Social apps in memory

Out of scope this round

  • TikTok and Instagram corpus ingest. YouTube only. The script_id column on social_videos exists so the cross-post dedupe (one canonical script, N platform posts) lands later without a migration, but it stays null this round.
  • Any fine-tuning. See the context section. If a future agent proposes a LoRA, they need to re-argue it against the 25k-token corpus math.
  • Auto-posting. Draft only, per feedback_social_media_draft_only.md.
  • Revenue reporting. The monetary scope gets added in Task 3, but nothing consumes it yet.

Known unknowns for the executing agent

  • Whether CT prod runs cron via systemd on Console or via a hosted scheduler. Check before writing Task 6.
  • Whether the app schema's default privileges cover lifeos_app on new tables, or whether explicit GRANT lines are needed (roughly 54 of 146 migrations issue them; the social ones do not). Verify in Task 1, do not assume.
  • Whether YouTube's Analytics API will accept retention and impression metrics in a single report call, or requires splitting them. Split if it rejects.
  • Per-platform engagement-rate denominators currently differ (IG uses followers, TikTok uses views, instagram.ts:51 vs tiktok.ts:57). Arguably each is the platform's native convention, but the UI presents the cards side by side as if comparable. Raise with Justin rather than silently normalizing.