Skip to content

CreatorTrack Sales CRM (business pipeline)

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

LIVE on prod since 2026-07-06 (merges 3ada10d+c385c65, proxy fix 00c3fab line; timer forge-creatortrack-sales-gmail-sync.timer enabled, :07/30). Migrations applied to live lifeos; dev slot torn down. Install the app per workspace via the App Library; the Gmail ingest picks a workspace up on the next timer tick after install. Phase A plan: creatortrack-sales-crm-phase-a-2026-07-06.

What it is

Workspace-scoped business CRM inside CreatorTrack: companies, contacts, and a brand-deal kanban pipeline. Distinct from the Personal CRM (app id crm, node-backed). App id sales, name "Sales CRM", route /sales, ownerOnly, installable per workspace, so JustinWieb / Gus Outdoor Co / Gus the Bass each get an isolated pipeline. Data model cribbed from Twenty CRM.

Schema (migration 20260706T153305_feat_sales_crm_sales_crm_core.sql)

Plain PG tables in the crm schema, all with workspace_id FK + workspace_isolation RLS (finances-native pattern), grants to lifeos_app:

  • crm.companies: name, domain, linkedin_url, industry, city, notes, archived_at
  • crm.contacts: company_id, first/last name, emails text[] (GIN-indexed for Phase B email matching), phone, job_title, linkedin_url, cadence_days, last_touch_at, notes
  • crm.deal_stages: per-workspace (key, label, position, is_won, is_lost); 7 defaults seeded idempotently on first open (lead, contacted, negotiating, contracted, delivered, paid, lost)
  • crm.deals: company_id, primary_contact_id, amount_cents, currency, stage_key, close_date, deliverables jsonb [{label,done}], usage_rights, exclusivity_until, sort_order
  • crm.activities: contact_id/deal_id, kind (note|email|call|meeting|touch), body, occurred_at; logging one on a contact advances last_touch_at

Code

  • lib/sales-crm.ts: data layer, raw SQL under asUser(), every query filters workspace_id explicitly (RLS alone can't split one owner's workspaces). Gotcha fixed: postgres.js returns bigint as string, so all id selects cast ::int. Array params route through jsonb (emailsArraySql).
  • app/api/apps/sales/route.ts: op-dispatched (create/update/archive x company/contact/deal, move_deal, add/delete_activity); GET returns one-shot dataset or ?contactId=/?dealId= timeline.
  • components/apps/shared/KanbanBoard.tsx + kanban.css: GENERIC shared kanban (WeekStrip pattern), HTML5 DnD, no deps; reuse for tasks-by-status/content pipeline.
  • components/apps/sales/: SalesClient (shell + API plumbing), CompaniesView, ContactsView (profile panel + activity timeline + cadence/overdue), DealsView (kanban + deal dialog with deliverables checklist).

Verified end-to-end on the dev slot: full CRUD round-trip via API (create, move, activity, last-touch, archive), page renders in suite shell.

Phase B: Gmail ingest + cadence follow-ups (built 2026-07-06, same branch)

Plan: sales-crm-phase-b-gmail-ingest-2026-07-06. Business Gmail ONLY (spec decision; personal stays out).

  • Migration 20260706T162326_..._email_ingest.sql: activities gain gmail_message_id / gmail_thread_id / direction (in|out) / source_account + partial unique (workspace_id, gmail_message_id); new crm.sync_state watermark table (workspace_id, job_key).
  • lib/sales-crm-jobs/gmail.ts: runGmailSync chunks contact emails 15/query ({from:a to:a ...} after:<epoch>), business account via lib/gmail.ts token seam (searchMessagesMeta export), upserts kind=email activities (subject+snippet as body, conflict-do-nothing = idempotent), advances last_touch_at via greatest() for EVERY matched contact either direction. Watermark = run start, 1-day overlap rescan; 180d initial backfill. Per-chunk failures collect; throws only if all chunks fail.
  • Triggers: bearer route /api/apps/sales/jobs (SALES_JOBS_TOKEN, timingSafeEqual, jobIdentity seam from finance-jobs, runs every workspace with sales installed) + in-app op gmail_sync (single workspace) + "Sync email" button.
  • Timeline: email activities render direction arrow + subject + snippet; "full message" expander pulls body live via GET ?messageBody=<gmail id> (ownership-checked in crm.activities first, getMessageBody in lib/gmail.ts, plain-text preferred, HTML stripped). Bodies NEVER stored in PG.
  • Follow-ups strip (FollowUpsStrip.tsx) above tabs: contacts past cadence_days sorted most-overdue, Log touch quick action, sync stamp; shared touchState exported from there.
  • systemd: forge-creatortrack-sales-gmail-sync.{service,timer} (infra/systemd + installed user scope, :07/30 offset from campaigns) + scripts/forge_creatortrack_sales_jobs.sh. ENABLED 2026-07-06; SALES_JOBS_TOKEN in creatortrack-auth.env (prod service env). Gotcha found at deploy: /api/apps/sales/jobs had to join the proxy's cookie-exempt PUBLIC_PATHS (same as finance/campaigns jobs), else the middleware 401s bearer calls.
  • Verified on :3064 against real business Gmail: 127 msgs matched a probe contact ([email protected]), 124 activities inserted, idempotent re-run inserted 0, body expander loads live, 400/401/404 guards tested. Probe contact "ZZTest IngestProbe" (id 22) left in the DEV clone for click-through QA; delete before merge-day data migration (dev clone is throwaway anyway).

Phases still owed

  • C: contracts (attachment + status), tasks linkage, ERPNext invoice link (Business Manager app)
  • D: outreach draft agent; LinkedIn stays URL-field only (no usable personal API)

[Claude Code]