Deals & Payments — Technical Reference

This page covers the data models, Kanban architecture, and revenue calculation logic behind the Pipelines & Deals module.

Data Models

pipelines

Column	Type	Description
`id`	`uuid`	Primary key
`workspace_id`	`uuid`	Tenant isolation
`name`	`text`	Pipeline name
`created_at`	`timestamptz`	Created timestamp
`updated_at`	`timestamptz`	Last updated timestamp

pipeline_stages

Column	Type	Description
`id`	`uuid`	Primary key
`pipeline_id`	`uuid`	FK → `pipelines.id` (`ON DELETE CASCADE`)
`name`	`text`	Stage name
`order_index`	`integer`	Sort position within the pipeline

deals

Column	Type	Description
`id`	`uuid`	Primary key
`workspace_id`	`uuid`	Tenant isolation
`pipeline_stage_id`	`uuid`	FK → `pipeline_stages.id`
`title`	`text`	Deal name
`value`	`numeric(12,2)`	Monetary value
`status`	`text`	`'open'`, `'won'`, or `'lost'` (CHECK constraint)
`contact_id`	`uuid`	FK → `contacts.id` (required, `ON DELETE CASCADE`)
`company_id`	`uuid`	FK → `companies.id` (optional, `ON DELETE SET NULL`)
`assigned_user_id`	`uuid`	FK → `user_profiles.id`
`created_by`	`uuid`	FK → `user_profiles.id` — creator
`updated_by`	`uuid`	FK → `user_profiles.id` — last modifier
`closed_at`	`timestamptz`	Set when status changes to won/lost
`contract_url`	`text`	Optional URL to external contract documents

Design Note: company_id uses ON DELETE SET NULL to preserve deal revenue history even if the linked company is deleted. contact_id uses ON DELETE CASCADE since the human contact is the mandatory entity.

deal_payments

Column	Type	Description
`id`	`uuid`	Primary key
`workspace_id`	`uuid`	Tenant isolation — FK → `workspaces.id` (`ON DELETE CASCADE`)
`deal_id`	`uuid`	FK → `deals.id` (`ON DELETE CASCADE`)
`label`	`text`	Milestone name
`sequence_order`	`integer`	Sort position of the payment milestone
`expected_amount`	`numeric(12,2)`	Expected payment amount
`paid_amount`	`numeric(12,2)`	Actual amount received
`status`	`text`	`'scheduled'`, `'paid'`, or `'waived'` (CHECK constraint)
`due_date`	`date`	Expected payment date
`paid_date`	`date`	Actual payment date
`payment_method`	`text`	How payment was made (e.g., PayPal, ACH)
`payment_reference`	`text`	Transaction identifier
`notes`	`text`	Optional internal notes for the payment
`processing_fee`	`numeric(12,2)`	Informational processor fee
`created_by`	`uuid`	Creator profile — FK → `user_profiles.id`
`created_at`	`timestamptz`	Record creation timestamp
`updated_at`	`timestamptz`	Last updated timestamp

Kanban Architecture

The Kanban board (/deals/[pipelineId]) uses the @dnd-kit library for drag-and-drop interactions.

Optimistic Updates

Drag Start — Shows a drag overlay with the deal title and value.
Drop — The UI immediately updates the card's position locally.
Background Sync — The moveDealToStage server action is called.
Revert on Failure — If the action fails, the card reverts to its original stage and an error toast is shown.

Activation Constraint

A minimum drag distance of 8 pixels is required before a drag starts. This prevents accidental drags when a user intends to click the card or open the kebab menu.

Hydration Safety

Since @dnd-kit relies on browser-only APIs, the board uses a client-side mount guard to prevent Next.js hydration mismatch errors:

1. Component mounts → isMounted = false → renders skeleton columns
2. useEffect fires  → isMounted = true  → renders DndContext with full board

Terminal States

When a deal's status is changed to won or lost, it is removed from the active Kanban board, which only fetches deals where status = 'open'.

Revenue Calculations

Payments Progress Bar

The Deal Detail view includes a payments progress bar calculated as follows:

Total Collected = SUM of paid_amount where status = 'paid'
Total Expected = SUM of expected_amount where status != 'waived'
Progress % = Total Collected / Total Expected

Dashboard Revenue (30d)

The main dashboard Revenue (30d) metric aggregates three sources: Stripe Purchases + Event Registrations + Deal Payments

For deal payments, only the paid_amount from payments where status = 'paid' and paid_date is within the last 30 days is included. processing_fee is informational only and does not reduce the gross revenue metric.

Overdue Detection

Scheduled payments with a due_date in the past display a red "Overdue" badge. This is a UI-only detection calculated at render time. There is no cron job or automated notification system for overdue payments.

Automation Triggers

Deal transitions hook into the unified automation engine:

Trigger Type	Fires When	Trigger ID Format
`deal_stage_changed`	A deal is moved to a new stage	Stage UUID
`deal_status_changed`	A deal is marked as Won/Lost	`{pipelineId}_{status}`

The composite {pipelineId}_{status} ID allows automation rules to be scoped to specific outcomes in specific pipelines (e.g., triggering onboarding only when a deal in the "Sales" pipeline is "won").

Performance & Hydration Optimization

Server-Side Pre-fetching & Lazy Loading

Server Component (pages/deals/page.tsx): Parallel-fetches getDealsDirectoryBundle (deals, pipelines, and automation rules count) server-side to support instant first paint.
Client Component (deals-client.tsx): Manages the Kanban board, deal searches, stages filtering, and client state.
Dynamic imports: The NewDealDialog and DependenciesDrawer are dynamically imported and conditionally rendered.
Eager Mount Mitigation: The new pipeline creation sheet is conditionally mounted so that closed state element DOM nodes do not bloat the DOM tree.
Sync Gates: Uses isFirstQueryRef and initialWorkspaceIdRef to guard mounts and clean filters on workspace changes.

Security (RLS)

Entity	SELECT	INSERT	UPDATE	DELETE
Pipelines	All members	Admin+	Admin+	Admin+
Stages	All members	Admin+	Admin+	Admin+
Deals	All members	All members	All members	Admin+
Deal Payments	All members	All members	All members	Admin+

Pipelines and Stages follow the Structural Data access model. Deals and Payments follow the Operational Data model (all members can create/edit, but only Admins can delete).

Appointments Companies