PipelineReplay - CRM Pipeline History Snapshots and Trending

Acceptance Criteria

Salesforce Sync: done when deal data syncs daily and historical snapshots are visible in Supabase. HubSpot Sync: done when HubSpot deal API integration works and produces equivalent data to Salesforce. Dashboard Charts: done when 3 line charts render with real data and respond to date range filters. Team Access: done when user can invite teammate and they see same data. Export: done when CSV export includes all snapshots and deal-level details.

How to build it, step by step

1. npx create-next-app@latest pipelinereplay --typescript --app. 2. npm install @supabase/supabase-js @supabase/auth-helpers-nextjs stripe recharts. 3. In Supabase SQL editor: CREATE TABLE integrations (id uuid PRIMARY KEY, user_id uuid REFERENCES auth.users, crm_type text, access_token text, refresh_token text, token_expires_at timestamptz, created_at timestamptz DEFAULT now()); CREATE TABLE snapshots (id uuid PRIMARY KEY, integration_id uuid REFERENCES integrations, snapshot_date date NOT NULL, total_pipeline_value numeric, deal_count int, created_at timestamptz DEFAULT now()); CREATE TABLE deals (id uuid PRIMARY KEY, snapshot_id uuid REFERENCES snapshots, crm_deal_id text, name text, stage_id text, stage_name text, owner_id text, value numeric, close_date date, created_date date); CREATE INDEX ON deals (snapshot_id); CREATE INDEX ON snapshots (integration_id, snapshot_date DESC). 4. Enable RLS on all three tables: ALTER TABLE integrations ENABLE ROW LEVEL SECURITY; CREATE POLICY user_integrations ON integrations FOR ALL USING (user_id = auth.uid()); repeat equivalent policies for snapshots (JOIN to integrations WHERE user_id = auth.uid()) and deals (JOIN to snapshots to integrations). 5. Register a Salesforce Connected App at developer.salesforce.com — set callback URL to /api/auth/salesforce/callback. Build /api/auth/salesforce/route.ts: redirect to Salesforce OAuth authorize URL with response_type=code, client_id, redirect_uri, scope=api. Build /api/auth/salesforce/callback/route.ts: exchange code for access_token + refresh_token via POST to https://login.salesforce.com/services/oauth2/token, store encrypted in integrations table. 6. Register HubSpot app at developers.hubspot.com — set callback to /api/auth/hubspot/callback, request scopes crm.objects.deals.read and oauth. Build equivalent /api/auth/hubspot and /api/auth/hubspot/callback routes mirroring step 5 but posting to https://api.hubapi.com/oauth/v1/token. 7. Add vercel.json with cron config: { "crons": [{ "path": "/api/jobs/snapshot", "schedule": "0 9 * * *" }] }. Build /api/jobs/snapshot/route.ts: fetch all active integrations, for each refresh OAuth token if expires_at < now + 10min (POST to token URL with grant_type=refresh_token), then for Salesforce run SOQL: SELECT Id, Name, StageName, Amount, CloseDate, CreatedDate, OwnerId FROM Opportunity WHERE IsClosed = false OR (IsClosed = true AND CloseDate = LAST_N_DAYS:90) with pagination via nextRecordsUrl loop until done, INSERT snapshot row, bulk INSERT deals rows. 8. For HubSpot snapshot: GET https://api.hubapi.com/crm/v3/objects/deals?properties=dealname,dealstage,amount,closedate,createdate,hubspot_owner_id&limit=100 with after cursor pagination until no paging.next.after. Normalize to same deals schema. 9. Build /api/dashboard/trends/route.ts: query SELECT snapshot_date, SUM(value) as total_value, COUNT(*) as deal_count, AVG(CURRENT_DATE - created_date) as avg_age_days FROM deals JOIN snapshots ON deals.snapshot_id = snapshots.id WHERE snapshots.integration_id = $1 AND snapshot_date >= NOW() - INTERVAL '90 days' GROUP BY snapshot_date ORDER BY snapshot_date ASC. 10. Build dashboard page in v0: prompt 'Recharts dashboard with 3 LineCharts sharing an XAxis of dates: (1) total pipeline value in dollars, (2) deal count, (3) average deal age in days. Use ResponsiveContainer, Tooltip, Legend. Accept data prop as array of {date, totalValue, dealCount, avgAge}.' Drop generated component into app/dashboard/page.tsx and wire to /api/dashboard/trends. 11. Add Stripe: create two Price IDs in Stripe dashboard ($199/month single CRM, $299/month multi-CRM). Build /api/billing/checkout/route.ts using stripe.checkout.sessions.create with price ID and customer email. Build /api/billing/webhook/route.ts to handle checkout.session.completed (activate subscription in DB) and customer.subscription.deleted (deactivate). 12. Deploy: push to GitHub, connect Vercel, set env vars (SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY, SALESFORCE_CLIENT_ID, SALESFORCE_CLIENT_SECRET, HUBSPOT_CLIENT_ID, HUBSPOT_CLIENT_SECRET, STRIPE_SECRET_KEY, STRIPE_WEBHOOK_SECRET), verify Vercel Cron Jobs tab shows schedule, test OAuth flows end-to-end with real Salesforce Developer Edition org and HubSpot free account.