Pipeline Stages

Deals move through pipelines made up of ordered stages. Understanding the stage model is required for any feature that reads or moves deals.

Tables

Table	Purpose
pipelines	Pipeline definitions (name, type, tenant)
pipeline_stages	Ordered stages belonging to a pipeline
deals	Each deal has a stage_id pointing to a stage

Pipeline types

The pipeline_type column on the pipelines table controls behavior. Valid values:

pipeline_type	Description
sales	Standard sales pipeline — default type
onboarding	Post-sale onboarding flow
support	Support ticket pipeline
custom	Freeform — no enforced stage semantics

Always use one of these exact strings. An unknown pipeline_type will not cause a DB error but will break pipeline-specific UI rendering and automation triggers.

Stage schema

SELECT id, id_pipeline, id_company, name, sort_order,
       is_won, is_lost, color
FROM pipeline_stages
WHERE id_company = :id_company
  AND id_pipeline = :id_pipeline
ORDER BY sort_order ASC;

Key columns:

Column	Notes
sort_order	Visual order in the pipeline board
is_won	Boolean — marks this as the "won" terminal stage
is_lost	Boolean — marks this as the "lost" terminal stage
color	Hex string for the stage label

Only one stage per pipeline should have is_won = true and one should have is_lost = true. These drive the deal.won and deal.lost automation triggers.

Moving a deal

UPDATE deals
SET stage_id = :new_stage_id,
    updated_at = NOW()
WHERE id = :id
  AND id_company = :id_company
  AND deleted_at IS NULL;

Always verify the target stage belongs to the same id_company before updating — never trust a stage_id from user input directly.

-- Verify stage ownership before moving
SELECT id FROM pipeline_stages
WHERE id = :stage_id AND id_company = :id_company;
-- If 0 rows returned, reject the move

Creating a pipeline and stages

-- 1. Create the pipeline
INSERT INTO pipelines (id_company, name, pipeline_type)
VALUES (:id_company, 'New Sales Pipeline', 'sales');

-- 2. Add stages in order
INSERT INTO pipeline_stages
  (id_pipeline, id_company, name, sort_order, is_won, is_lost, color)
VALUES
  (:pipeline_id, :id_company, 'New Lead',    1, false, false, '#94a3b8'),
  (:pipeline_id, :id_company, 'Contacted',   2, false, false, '#60a5fa'),
  (:pipeline_id, :id_company, 'Proposal',    3, false, false, '#f59e0b'),
  (:pipeline_id, :id_company, 'Won',         4, true,  false, '#22c55e'),
  (:pipeline_id, :id_company, 'Lost',        5, false, true,  '#ef4444');

Querying deals by stage

SELECT d.*, ps.name AS stage_name, ps.color AS stage_color
FROM deals d
JOIN pipeline_stages ps ON ps.id = d.stage_id
WHERE d.id_company = :id_company
  AND d.deleted_at IS NULL
  AND d.stage_id = :stage_id;