Modelling quotes for a business that installs things

An installer's quote is not a shopping cart. It is a priced list of devices and labour, grouped the way the client reads it, built from a catalogue whose costs move with suppliers and exchange rates. The model that worked for ZS Tech, a smart home and access control company in Cairo: products with several supplier sources, packages that expand into lines, sections for the client view, and a cost snapshot on every line that stays live while the deal is open and freezes once it is won.

This is part 1 of a five-part build log of that CRM. It runs on Next.js 16 and Supabase, and the rules below live in Postgres, not in the screens.

A sales CRM is not enough for installers

Off-the-shelf CRMs stop at "won". An installation business only starts there. A deal begins as a contact, gets a site survey, becomes a priced quote, turns into a project with tasks and serial-numbered devices, and ends as instalments that someone has to collect. The deal's stage is one enum that covers the whole path:

create type public.deal_stage as enum (
  'lead', 'surveyed', 'quoted', 'won',
  'in_progress', 'handed_over', 'closed', 'lost'
);

Every other decision in this post follows from that path. A quote is a snapshot of a moment in it, and the numbers on it have to stay true after the moment passes.

The price book: one product, many suppliers

The same smart switch can come from an importer, a local distributor, or an authorised dealer, at different prices, in different currencies, with different lead times. So a product does not have a cost. It has sources, and each source has one:

create table public.product_sources (
  id uuid primary key default gen_random_uuid(),
  product_id uuid not null references public.products(id) on delete cascade,
  supplier_id uuid not null references public.suppliers(id) on delete restrict,
  unit_cost numeric(14,2) not null check (unit_cost >= 0),
  currency char(3) not null default 'EGP' references public.fx_rates(currency),
  fx_rate numeric(12,6) not null default 1 check (fx_rate > 0),
  unit_cost_egp numeric(14,2) generated always as (round(unit_cost * fx_rate, 2)) stored,
  lead_days int not null default 0 check (lead_days >= 0),
  unique (product_id, supplier_id)
);

Two details matter. The exchange rate is stored on the row at the moment of entry, so a cost quoted in dollars last month does not silently change when today's rate does. And the EGP value is a generated column, so nothing in the app ever multiplies by hand. A product's effective cost is its preferred source, or the cheapest one when no preference is set:

create or replace function public.product_effective_cost(p_product_id uuid) returns numeric
language sql stable security definer set search_path = public as $$
  select coalesce(
    (select ps.unit_cost_egp from public.products p
       join public.product_sources ps on ps.id = p.preferred_source_id
      where p.id = p_product_id),
    (select min(unit_cost_egp) from public.product_sources where product_id = p_product_id))
$$;

Products also carry the facts the client cares about: a list price, warranty months, and the protocol (Wi-Fi, Zigbee, or wired). SKUs are filled in by a trigger when left blank, so nobody invents a numbering scheme by hand.

Packages: one click, many lines

Most quotes start from a known bundle, such as an apartment package with switches, a hub, and labour. A package is just a list of products with quantities. Adding it to a deal adds each product, and a partial unique index turns a repeat into a quantity bump instead of a duplicate line:

create unique index deal_items_product_uq
  on public.deal_items (deal_id, product_id) where product_id is not null;

insert into public.deal_items (deal_id, product_id, qty)
values (p_deal_id, p_product_id, p_qty)
on conflict (deal_id, product_id) where product_id is not null
do update set qty = public.deal_items.qty + excluded.qty;

Free-text lines (a custom bracket, extra cable) have no product, so the index ignores them and they can repeat.

Sections: how the client reads the quote

The first design had a flat list of lines. The owners asked for sections instead, because clients read a quote as "devices", then "installation and labour", then anything custom. Each product category has a default section, so a line lands in the right place without anyone choosing.

Grouping is a small pure function with its own tests: sections in their saved order, empty sections dropped, and any line without a known section collected into an "Other" group at the end so nothing disappears from the printed quote.

Cost snapshots: the decision everything depends on

When a line is added, a trigger copies today's effective cost and list price onto the line itself. The line then owns its numbers: the price can be edited per quote, and the cost is what the business expects to pay.

Suppliers change prices all the time, though. If a source gets cheaper or the preferred supplier changes, open quotes should reflect it. Won deals should not: the job was sold at a margin, and rewriting its cost after the fact would make every project's profit report wrong. So the re-pricing trigger is deliberately narrow:

update public.deal_items di
   set unit_cost = coalesce(public.product_effective_cost(v_pid), 0)
  from public.deals d
 where d.id = di.deal_id
   and di.product_id = v_pid
   and d.stage in ('lead', 'surveyed', 'quoted');

There is an integration test for exactly this: switch a product's preferred source and check that lines on lead, surveyed, and quoted deals moved while every won deal kept its old cost.

Totals live in one formula

The quote total is calculated the same way in Postgres (for lists, the dashboard, and the print view) and in TypeScript (for live editing), and the TypeScript version is unit tested:

The quote builder also shows an internal price floor: cost times one plus the margin floor from settings (25% by default). A margin below the floor turns red; above 38% it turns green. Those colours are the whole point of showing cost to the people allowed to see it.

Quote numbers nobody can duplicate

The first time a deal reaches "quoted", it gets a number like Q-2026-001 from a per-year counter. The increment and the read happen in one statement, so two people quoting at the same second still get different numbers:

insert into public.counters (key, value) values ('quote:' || y, 1)
on conflict (key) do update set value = public.counters.value + 1
returning value into n;
return 'Q-' || y || '-' || lpad(n::text, 3, '0');

Stage changes go through one database function that also sets the quote's validity date from settings, records the stage history, and, on "won", creates the project's tasks and a default 50% deposit and 50% final payment plan. That plan is the subject of part 3.

What I would keep for any installer

• Model supplier sources, not a single product cost. Prices, currencies, and lead times differ per supplier.
• Snapshot cost onto the quote line, and re-price only open deals.
• Store the exchange rate with the record, and generate the local-currency value.
• Group lines by section, because that is how the client reads the quote.
• Calculate totals with one formula, tested, in both the database and the app.

Next in the series: who is allowed to see the cost and margin this model produces, and how the database, not the screen, enforces it. The full project is in the ZS Tech CRM case study, and this is the kind of system I build on the CRM and ERP page.