Skip to content

CRM & Clients Flow

This page describes how Workbench AI handles client records, CRM views, and appointments.

Screens & views

Clients screen (/clients)

File: src/pages/Clients.tsx

Responsibilities:

  • Main clients list and CRM entry point.
  • Provides:
    • Stats summary (total clients, revenue, VIPs, etc.).
    • Filters for segmentation.
    • Table of clients with inline actions.
    • Details modal with client profile + orders.

Key components:

  • ClientStatsCards – high-level metrics:
    • Total clients.
    • Total revenue.
    • Average order value.
    • Active clients (last 30 days).
    • New clients (last 30 days).
    • VIP clients.
  • ClientFilters – segmentation controls (search, has email, address, spend, orders, recency, activity status).
  • ClientsTable – tabular list of clients.
  • ClientDetailsModal – view/edit a client and see related orders.
  • ClientForm – create/edit form.

CRM view (/clients/crm)

File: src/pages/clients/CRM.tsx

Currently a simple placeholder that:

  • Uses DashboardLayout with a title and description.
  • Intended future home for higher-level CRM tooling (pipelines, tags, campaigns).

For now, most CRM behavior lives on /clients and in the associated stats/filters rather than this page.

Appointments & calendar (/clients/appointments)

File: src/pages/clients/AppointmentsCalendar.tsx

Currently a placeholder page that:

  • Uses DashboardLayout with a title and description.
  • Declares scope for: consultations, fittings, pickups, reminders, and availability.

Appointments are not yet fully implemented in the codebase; when they are, this page should be updated to describe the actual data model and flows.

Appointments status

As of this documentation pass, appointments are conceptual. There is no appointments table or concrete implementation yet. This page documents the intent; update it when appointments ship.

Clients data & operations

useClients hook

File: src/hooks/useClients.ts

Exposes the core client operations:

  • fetchClients(filters?) – list clients for the current tenant.
  • createClient(input) – create a new client (with plan-limit checks).
  • updateClient(input) – update existing client data.
  • deleteClient(id) – delete a client.
  • getClientStats(id) – per-client stats (total spent, order count, last order date).
  • fetchClientStats() – aggregate stats for the tenant.

Clients table

Migration: supabase/migrations/20250114000000_create_clients_table.sql

  • Table: clients
  • Fields (subset):
    • id
    • tenant_id – tenant/company owner.
    • first_name, last_name.
    • email (nullable).
    • phone.
    • address (nullable).
    • notes (nullable).
    • date_of_birth (nullable).
    • preferred_contact_method (nullable) – e.g. phone, email, SMS.
    • created_at, updated_at.
  • Indexes for efficient lookup by tenant, name, email, and phone.
  • RLS is enabled to restrict access by tenant.

Fetching & filtering clients

fetchClients(filters) builds a Supabase query like:

  • Base:
    • from('clients').select('*').eq('tenant_id', tenantId)
  • Search:
    • If filters.search is set, applies case-insensitive search across:
    • first_name, last_name, email, phone.
  • Boolean filters:
    • has_email – include only clients with/non-with email.
    • has_address – include only clients with/non-with address.
  • Preferred contact:
    • preferred_contact_method = filters.preferred_contact.
  • Created date range:
    • created_at >= filters.created_date_from.
    • created_at <= filters.created_date_to.
  • Sorting:
    • Name (alphabetical), date created, or default.

After the base query, it derives stats per client using RPCs:

  • get_client_total_spent(client_id_param).
  • get_client_order_count(client_id_param).
  • get_client_last_order_date(client_id_param).

These stats are attached to each client as:

  • total_spent – aggregate order revenue.
  • order_count – number of orders.
  • last_order_date – date of last order.

Client-side filters are then applied using these stats:

  • Money spent range.
  • Order count range.
  • Last order date range.
  • Activity status:
    • active – ordered in last 30 days.
    • inactive – no orders in last 30 days or never ordered.
    • new – client created in last 30 days.
    • vip – total_spent > $1000 or order_count > 10.

Creating & updating clients

  • createClient(input):

    • Checks current client count for the tenant.
    • Loads subscription plan info from subscriptions.
    • Uses getPlanLimits(plan_name, subscription) to enforce plan-specific client limits.
    • Inserts a row into clients with tenant-scoped data.
    • Normalizes optional fields to null when absent.

  • updateClient(input):

    • Updates clients row, normalizing optional fields.

  • deleteClient(id):

    • Deletes the client row.
    • Downstream effects (e.g. orders referencing this client) are governed by DB constraints and should be handled with care.

Stats aggregation

fetchClientStats() summarizes CRM health:

  • Queries all clients for the tenant.
  • Queries all orders for the tenant.
  • Computes:
    • total_clients – count of clients.
    • total_revenue – sum of orders.price.
    • average_order_value.
    • active_clients_30d – clients with orders in last 30 days.
    • new_clients_30d – clients created in last 30 days.
    • vip_clients – clients over thresholds for spend or order count.

These metrics feed ClientStatsCards on the /clients page.

Tables & relationships

Key tables:

  • clients

    • Master CRM record for each customer.
    • Linked to orders.client_id.

  • orders

    • Used to derive client stats (revenue, order count, recency).
    • See Orders Flow for details.

  • RPC functions

    • get_client_total_spent – sum of order totals per client.
    • get_client_order_count – count of orders per client.
    • get_client_last_order_date – latest order date per client.

There is no dedicated appointments table yet; when it exists, update this doc to include its schema and how it links to clients and orders.

Flow diagram

flowchart LR
  U[User] --> CLI[/Clients screen/]
  CLI --> HOOK[useClients]
  HOOK --> C[(clients)]
  HOOK --> O[(orders)]

  subgraph Stats
    RPC1[get_client_total_spent]
    RPC2[get_client_order_count]
    RPC3[get_client_last_order_date]
  end

  O --> RPC1
  O --> RPC2
  O --> RPC3

  click CLI "../architecture/frontend/" "Clients page & components"
  click HOOK "../architecture/frontend/" "useClients hook"
  click C "../architecture/supabase/" "Clients table"
  click O "./orders.md" "Orders flow & data model"

Narrative:

  1. User opens /clients.
  2. useClients fetches clients rows for the tenant.
  3. For each client, RPCs compute spending and order-based stats using orders.
  4. Filters and stats are applied client-side to segment customers.
  5. UI surfaces insights (VIPs, inactive clients, new clients) for follow-up.