Bob's Sales Tracking

Multi-user sales team dashboard with real-time commission visibility and account health monitoring.

A production-ready SaaS application for sales teams to track performance, manage customer relationships, and optimize territory management. Built with React 18, Supabase, and TanStack Query.

---

🚀 Quick Start

For Users

1. Follow DEPLOY.md for step-by-step production setup (~1 hour)
2. After setup, open your Vercel URL
3. Login with test credentials
4. Upload daily CSV files to sync sales data

For Developers

npm install
cp .env.local.example .env.local
# Fill in VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY
npm run dev
# Open http://localhost:5173/login

---

📊 Features

Rep Dashboard (PersonalDashboard)

• KPI Cards: YTD Commission, Revenue, Gross Profit, Active Customers
• Dormant Account Alerts: Automatic detection of accounts silent 7+ days
• Product Mix: Category breakdown with revenue % and margin %
• Real-time Data: Updates automatically when managers upload CSV

Manager Dashboards

• Team Leaderboard: Rep rankings by commission with team totals
• Product Mix (Aggregated): Category performance across all reps
• Opportunity Pipeline: Critical (14+ days) and at-risk (7-13 days) accounts
• Territory Analysis: Territory revenue with concentration risk flagging

Admin Features

• CSV Upload: Drag-and-drop daily sales file upload
• Upload History: Track all uploads with status and error logs
• Automatic Processing: Edge Function batch-inserts rows in ~10 seconds

---

🏗️ Architecture

Frontend

• React 18 with TypeScript for type safety
• Vite for fast dev/build (101ms production build)
• React Router v6 for role-based navigation
• TanStack Query for data caching and automatic invalidation
• Recharts ready for advanced visualizations
• Tailwind CSS for responsive design

Backend

• Supabase PostgreSQL with Row-Level Security (RLS)
• 4 SQL views for aggregated metrics
• Edge Functions (Deno) for CSV processing
• Automatic Commission Calculation (generated columns)

Security

• RLS Policies: Reps can only see their own data (enforced at DB layer)
• Role-Based Access Control: rep → manager → admin with progressive access
• Auth via Supabase: Email/password with session management

---

📁 Project Structure

~/bobs-sales-tracking/
├── src/
│   ├── App.tsx                 # Router + auth guards
│   ├── hooks/
│   │   ├── useAuth.ts         # Session + profile + role
│   │   └── useQueries.ts      # TanStack Query hooks for all views
│   ├── components/
│   │   ├── layout/            # AppShell, Sidebar
│   │   ├── ui/                # KPICard, DataTable, StatusBadge
│   │   └── charts/            # Ready for Recharts
│   └── pages/                 # 7 routes (login + 6 dashboards)
├── supabase/
│   ├── migrations/            # 3 SQL migrations
│   └── functions/             # Edge Function (CSV processing)
├── scripts/
│   └── seed_from_json.py      # Load 1,981 rows from agchem-tracker
├── PHASE-1-COMPLETE.md        # Foundation build summary
├── PHASE-2-COMPLETE.md        # Upload + dashboards build summary
├── SETUP.md                   # Detailed Supabase configuration
├── DEPLOY.md                  # Step-by-step deployment checklist
└── README.md                  # This file

---

📈 Data Model

Database Schema

• profiles: Users with role (rep/manager/admin) and rep_name
• sales_rows: Individual invoice line items with auto-calculated commission
• csv_uploads: Audit trail of all uploads
• 4 SQL views: Aggregated metrics for dashboard efficiency

RLS Policies

• Reps see only their own rows
• Managers see all rows
• Admins control uploads

---

🔄 CSV Upload Flow

1. Admin uploads CSV file (Date, Rep, Customer, Product_Category, Revenue, Cost, Territory)
2. UploadZone validates headers
3. File uploaded to Supabase Storage
4. Edge Function fetches → parses → batch inserts (500 rows/call)
5. React Query auto-invalidates all dashboards
6. All users see updated metrics

Processing Time: ~10 seconds for 2,000 rows

---

📊 Commission Calculation

Formula: (Revenue - Cost) × 0.275

Example:

• Revenue: $10,000
• Cost: $7,000
• Commission: ($10,000 - $7,000) × 0.275 = $825

Calculated automatically in PostgreSQL using generated columns (always in sync).

---

🚀 Deployment

Production Ready

• Build: 194 KB JS (60 KB gzipped)
• Load time: <1 second
• Vercel deployment: Push to main → auto-deploys
• Supabase free tier supports 2M monthly API calls

Steps (See DEPLOY.md for detailed guide)

1. Create Supabase project
2. Run 3 migrations
3. Create storage bucket
4. Deploy Edge Function
5. Create test users
6. Seed 1,981 rows
7. Push to GitHub
8. Import to Vercel
9. Add env variables
10. Done! 🎉

---

🛠️ Development

Local Development

npm run dev        # Start dev server on port 5173
npm run build      # Build for production
npm run preview    # Preview production build locally

Database

• Supabase SQL console for queries
• View migrations in supabase/migrations/
• Edit Edge Function in supabase/functions/process-csv/

Adding Features

• New pages: Add .tsx file in src/pages/
• New queries: Add hook in src/hooks/useQueries.ts
• Components: Reuse KPICard, DataTable, StatusBadge

---

📋 Configuration

Environment Variables

VITE_SUPABASE_URL=https://your-project.supabase.co
VITE_SUPABASE_ANON_KEY=your-anon-key

Get these from:

• Supabase Dashboard → Settings → API

Commission Rate

Currently hardcoded as 0.275 (27.5%) in:

• Database schema: sales_rows.commission generated column
• Edge Function: process-csv/index.ts (for future CSV rows)

To change: Update both locations and re-deploy Edge Function.

---

🐛 Troubleshooting

Login Not Working

• Check profiles table has a row for your email
• Verify .env.local has correct VITE_SUPABASE_URL

CSV Upload Fails

• Validate column headers (exact match required)
• Check csv-uploads bucket exists in Supabase Storage
• Verify Edge Function deployed: supabase functions list

Dashboard Shows No Data

• Verify seed script completed (1,981 rows inserted)
• Check profiles table has rep_name matching CSV "Rep" column
• Verify RLS policies (Settings → SQL → Check policies)

See SETUP.md for detailed troubleshooting.

---

📚 Documentation

• DEPLOY.md — Step-by-step deployment (1 hour, ~1000 lines)
• SETUP.md — Detailed configuration guide
• PHASE-1-COMPLETE.md — Foundation architecture
• PHASE-2-COMPLETE.md — Dashboard build details

---

🎯 Future Enhancements (Phase 3)

• Date range filters (YTD vs custom)
• Commission rate configuration form
• Mobile responsive improvements
• Chart visualizations (Recharts)
• CSV upload automation (email attachment)
• Custom branding (logo, colors)
• Multi-language support
• Real-time notifications for new orders

---

🔐 Security Notes

• RLS Enforced: Reps cannot query other reps' data (DB-layer security)
• No Secrets in Code: Environment variables only
• HTTPS Required: Use only for production
• Session Management: Supabase Auth handles tokens
• Backups: Enable in Supabase Settings → Backups

---

📞 Support

Questions?

• Check the relevant guide: DEPLOY.md, SETUP.md, or PHASE-2-COMPLETE.md
• Review Supabase docs: https://supabase.com/docs
• Check React docs: https://react.dev

Found a bug?

1. Check browser console for errors
2. Check Supabase logs (Dashboard → Logs)
3. Check Vercel build logs (Deployments tab)

---

📄 License

Internal project for Bob's sales team.

---

Built with: React 18 • Vite • Supabase • Tailwind CSS • TypeScript

Status: ✅ Production ready

Last Updated: 2026-03-27

---

Quick Links

• 🚀 Deployment Guide
• 🔧 Setup Instructions
• 📊 Architecture
• 💻 Local Dev

Next Step: Follow DEPLOY.md to go live!