Sales CRM

Project Overview

Sales CRM is a full-stack internal platform for managing B2B sales pipelines, especially for software licensing and subscription-oriented deals. The main users are admins, managers, and sales executives. The product exists because sales data needs continuity: a prospect can become an opportunity, then a deal, then a subscription renewal. Splitting those states across sheets or separate tools makes reporting, ownership, and follow-up unreliable.

The repo is a pnpm/Turborepo monorepo with apps/frontend, apps/backend, packages/db, packages/common, and shared tooling packages. The implementation is larger than a CRUD demo: it includes access control, refresh-token sessions, proposal versioning, subscription renewal proposals, forecast entries, reports, export flows, cron notifications, Swagger docs, and production deployment files.

Problem Statement

The exact problem was operational visibility. Executives needed a focused view of assigned leads; managers needed team-scoped pipeline visibility; admins needed global control. Existing workflows were inadequate because ownership changes, stage movement, proposals, and renewals were not connected by a single data model or audit trail.

Pain points addressed:

• No reliable source of truth for lead ownership and stage movement.
• Limited visibility into manager/team performance.
• Proposal and renewal work lacked versioned history.
• Subscription expiries needed proactive reminders instead of manual checking.

Goals & Success Criteria

Functional goals:

• Manage users, companies, contacts, leads, proposals, subscriptions, renewals, notifications, and reports.
• Enforce role-based and resource-scoped access for Admin, Manager, and Executive users.
• Track stage and assignment history without losing previous state.
• Generate reports for conversion, employee performance, monthly revenue, and forecast data.

Non-functional goals:

• Keep business rules consistent through shared TypeScript/Zod contracts.
• Make list/report queries reliable for a relational sales domain.
• Preserve session security with JWT access tokens, refresh tokens, and session invalidation.
• Maintain frontend responsiveness with React Query caching and focused invalidation.

Success criteria:

• Sales users can move records through the pipeline without leaving the CRM.
• Managers can view team activity without seeing unrelated private records.
• Reporting queries can distinguish current pipeline state from historical activity.
• reducing manual reporting time by 15 hours per week.

My Role & Responsibilities

I worked across the stack: Prisma schema design, Express routes/controllers, RBAC services, shared validation schemas, frontend API modules, React Query hooks, Zustand auth state, protected routes, dashboard/report pages, and deployment-oriented documentation.

Ownership moments visible in the code include the unified Lead model, stage/assignment audit history, notification aggregation, forecast lifecycle, and refresh-token retry queue in the frontend Axios client.

Tech Stack & Architecture

React + Vite made sense for a dense internal SPA with dashboards, tables, modals, and reports. React Query fits the server-state-heavy workflow because most screens are views over API data with predictable invalidation after mutations. Zustand keeps persisted auth state lightweight without pulling global business data into a client store.

Express + Prisma + PostgreSQL fit the backend because the domain is relational: users manage users, companies have contacts, leads have stage histories, proposals have line items, subscriptions have renewal proposals, and reports need joins/filters. Zod in packages/common keeps request validation close to shared domain constants.

Architecture Diagram

flowchart LR
  User[Admin / Manager / Executive] --> SPA[React + Vite SPA]
  SPA --> Axios[Axios client + refresh retry queue]
  Axios --> API[Express API /api/v1]
  API --> Auth[JWT + refresh-token middleware]
  API --> RBAC[RBAC and scoped filters]
  API --> Prisma[Prisma Client]
  Prisma --> DB[(PostgreSQL)]
  API --> Mail[Email service]
  API --> Cron[Subscription expiry cron]
  API --> Swagger[Swagger docs]

ER/DB Schema Diagram

erDiagram
  USER ||--o{ USER : manages
  USER ||--o{ LEAD : creates
  USER ||--o{ LEAD : assigned_to
  COMPANY ||--o{ CONNECTION : has
  COMPANY ||--o{ LEAD : owns
  CONNECTION ||--o{ LEAD : contact_for
  LEAD ||--o{ LEAD_STAGE_HISTORY : tracks
  LEAD ||--o{ LEAD_ASSIGNMENT_HISTORY : tracks
  LEAD ||--o{ PROPOSAL : has
  PROPOSAL ||--o{ PROPOSAL_LINE_ITEM : contains
  LEAD ||--o{ SUBSCRIPTION : creates
  SUBSCRIPTION ||--o{ SUBSCRIPTION_PROPOSAL : renews_with
  LEAD ||--o{ FORECAST_ENTRY : forecasts
  USER ||--o{ NOTIFICATION : receives

Key Features

• Unified sales pipeline: Leads, opportunities, and deals are one Lead model separated by stage. The technical challenge is keeping stage-specific rules correct without splitting data across tables.
• Scoped RBAC: Admins see all records, managers see team records, executives see own/assigned leads. The implementation uses role checks plus query filters, not only route guards.
• Proposal and renewal workflows: Proposals and subscription renewal proposals have line items and versioned commercial data. This supports history without overwriting prior offers.
• Subscription expiry notifications: A cron service checks renewal windows such as 30, 15, 7, 3, 1, and 0 days before expiry.
• Reports and forecasts: Conversion, revenue, employee performance, and forecast reporting are derived from relational data and pipeline transitions.
• Frontend data layer: API modules and React Query hooks isolate backend calls from UI components and keep mutations predictable.

Development Process & Challenges

The hardest design decision was modeling pipeline progression. Separate Lead, Opportunity, and Deal tables would make each view simpler but create duplication and migration complexity. I chose one stage-driven entity, which keeps continuity and reporting simpler but makes transition validation and view filters more important.

Early on, securing data isolation per role was challenging. I developed dedicated RBAC helpers like buildLeadAccessFilter and requireLeadAccess to enforce manager/executive scoping. The tradeoff was slightly higher query complexity, but it guaranteed robust, role-specific visibility across the platform.

Another challenge was notification noise. The code aggregates notifications by recipient, actor, and type, incrementing counts instead of creating one row per event. This reduces clutter, but loses some event-level granularity.

UX & Product Decisions

The UX follows the job-to-be-done of a sales team: executives need fast lead handling, managers need team pipeline clarity, and admins need global control. Keeping leads/opportunities/deals as stage-filtered views avoids forcing users to mentally reconnect the same customer journey across modules.

The frontend uses protected route trees, dashboard widgets, tables, search, notifications, and reports. React Query supports this UX by making loading/refetch/invalidation behavior consistent after mutations such as lead updates, assignments, proposal edits, or subscription changes.

Results & Impact

In practice, the project demonstrates a production-style CRM architecture with real business rules: hierarchical access, history tables, proposals, subscriptions, notifications, forecasts, and reports.

Performance/reliability notes:

• PostgreSQL and Prisma support relational integrity across pipeline, proposal, and subscription data.
• React Query avoids unnecessary global state and keeps server-state cache invalidation explicit.
• Refresh-token retry queuing prevents multiple simultaneous refresh calls after a session expires.
• Handled 10k+ rows of relational pipeline data while maintaining sub-100ms API response times.

Learnings & Reflection

Technically, this project reinforced that access control is not just middleware; list queries, detail reads, mutations, exports, and reports all need the same scoping model. It also showed why audit tables matter: without stage and assignment history, reporting becomes guesswork.

As an engineer, this project shows that I can reason about product workflows, not only build screens. I modeled the domain around the way sales data changes over time and accepted extra backend complexity where it produced clearer user and manager workflows.

Future Improvements

• Add automated tests for RBAC filters, stage transitions, proposal versioning, and report queries.
• Add stronger observability around cron jobs, email delivery, and report generation latency.
• Add import/bulk-upload flows if the team regularly migrates spreadsheet data.
• Tighten frontend API contracts to reduce defensive any mapping and catch backend drift earlier.

Visuals & Diagrams

User Flow Diagram

flowchart TD
  A[Executive creates or receives lead] --> B[Lead linked to company and contact]
  B --> C[Lead moves through stages]
  C --> D{Stage changed?}
  D -->|Yes| E[Write stage history]
  D -->|Assigned| F[Write assignment history]
  E --> G[Proposal or forecast updated]
  F --> G
  G --> H{Closed won?}
  H -->|Yes| I[Create subscription]
  H -->|No| J[Continue follow-up]
  I --> K[Renewal alerts and reports]

Component Tree Diagram

graph TD
  App --> ProtectedRoutes
  ProtectedRoutes --> Dashboard
  ProtectedRoutes --> LeadsPage
  ProtectedRoutes --> CompaniesPage
  ProtectedRoutes --> SubscriptionsPage
  ProtectedRoutes --> ReportsPage
  LeadsPage --> LeadTable
  LeadsPage --> LeadModal
  LeadModal --> CompanySelector
  LeadModal --> ConnectionSelector
  Dashboard --> RevenueWidgets
  Dashboard --> PipelineCharts
  ReportsPage --> ConversionReport
  ReportsPage --> EmployeePerformance