Deals

Crate: platyn-shop · Migration: 0004_shop.sql · Status: Schema built API planned

The tables exist and are applied. The HTTP surface and the pricing engine are planned.

One table, not two

A deal is one job from the first phone call to the shipped box. Quote and order are not two records — they are one record at two points in its life, and the id is preserved across the whole journey. Printavo does the same thing.

Splitting them is the obvious design and it is wrong. Every conversion becomes a copy: line items duplicated, art re-attached, and three places to look when a customer asks what was agreed. Worse, the copy drifts — the order says 144 pieces, the work order says 120 because someone edited one and not the other. And the migration happens at the single highest-value moment in the business process, which is the worst possible place to put a copy-and-relink step.

Lifecycle

Fifteen statuses, because a decorator’s board genuinely has this many columns:

CREATE TYPE app.deal_status AS ENUM (
    'draft','quoted','quote_sent','quote_approved','quote_declined',
    'confirmed','art_pending','art_approved','in_production',
    'ready','shipped','delivered','invoiced','paid','cancelled'
);

flowchart LR
    draft --> quoted --> quote_sent
    quote_sent --> quote_approved
    quote_sent --> quote_declined
    quote_approved --> confirmed
    confirmed --> art_pending --> art_approved --> in_production
    in_production --> ready --> shipped --> delivered
    delivered --> invoiced --> paid
    quote_declined --> cancelled
    confirmed --> cancelled
    in_production --> cancelled

    style quote_sent fill:#00A3D9,stroke:#007AA6,color:#14131A
    style confirmed fill:#E4006C,stroke:#B00054,color:#ffffff
    style in_production fill:#FF5B23,stroke:#D9410F,color:#14131A

confirmed_at is the commitment point, and cancelled_at is a timestamp rather than a terminal status alone — deals_board indexes WHERE cancelled_at IS NULL so a cancelled job leaves the board without leaving the database.

Model

erDiagram
    TENANTS   ||--o{ DEALS : owns
    COMPANIES ||--o{ DEALS : "billed to"
    CUSTOMERS ||--o{ DEALS : "contact"
    ADDRESSES ||--o{ DEALS : "ships and bills to"
    DEALS     ||--o{ DEAL_ASSIGNMENTS : "staffed by"
    USERS     ||--o{ DEAL_ASSIGNMENTS : "assigned"
    DEALS     ||--o{ LINE_ITEMS : "quotes"
    LINE_ITEMS ||--o{ LINE_ITEM_SIZES : "size run"
    LINE_ITEMS ||--o{ IMPRINTS : "decorated with"
    STYLES    ||--o{ LINE_ITEMS : "references"
    VARIANTS  ||--o{ LINE_ITEM_SIZES : "references"
    DECORATION_METHODS ||--o{ LINE_ITEMS : "method"

app.deals

Column	Type	Notes
id	UUID PK
tenant_id	UUID	RLS key.
deal_number	BIGINT	Human-facing. UNIQUE (tenant_id, deal_number).
status	app.deal_status	Default draft.
company_id	UUID NOT NULL	A deal always has a buyer.
customer_id	UUID nullable	The contact may not be known yet.
name, description	TEXT	name is required — “Spring 5K tees”.
ship_to_address_id, bill_to_address_id	UUID
customer_due_date, production_due_date	DATE	Distinct: in the customer’s hands vs. off the press.
quote_sent_at, quote_expires_at, confirmed_at	TIMESTAMPTZ
po_number, style_guide_number	TEXT
customer_notes, production_notes	TEXT	Customer-visible vs. never-visible.
currency	CHAR(3)	Default USD.
subtotal, discount_total, tax_total, shipping_total, grand_total	NUMERIC(14,2)	All default 0.
created_by	UUID → app.users
created_at, updated_at, cancelled_at	TIMESTAMPTZ

SELECT app.apply_tenant_rls('app.deals');

CREATE INDEX deals_board      ON app.deals (tenant_id, status, customer_due_date)
    WHERE cancelled_at IS NULL;
CREATE INDEX deals_by_company ON app.deals (tenant_id, company_id, created_at DESC);
CREATE INDEX deals_recent     ON app.deals (tenant_id, created_at DESC);

Totals are stored NUMERIC, not computed on read. Recomputing on every read means the number can change without anyone editing the deal — a rounding rule or tax table changes and last quarter’s invoices quietly disagree with themselves.

Per-tenant deal numbers

CREATE OR REPLACE FUNCTION app.next_deal_number(p_tenant UUID) RETURNS BIGINT
LANGUAGE sql AS $$
  SELECT coalesce(max(deal_number), 0) + 1 FROM app.deals WHERE tenant_id = p_tenant
$$;

Every shop’s numbering starts at 1 and stays dense. A global sequence would leak how many deals every other tenant has created — shop #2 signing up and seeing their first quote numbered 84,102 tells them something they should not know.

`max() + 1` needs the unique constraint behind it

This function is racy on its own: two concurrent inserts can read the same max. The UNIQUE (tenant_id, deal_number) constraint is what makes it correct — the loser gets a unique violation and retries. That is the intended design, not an oversight, but it means callers must handle the retry, and it is worth an explicit concurrency test.

app.deal_assignments

Column	Type
deal_id, tenant_id	UUID
role	app.deal_role — owner · account_manager · designer · production_lead · csr
user_id	UUID → app.users

PRIMARY KEY (deal_id, role) — one person per role per deal.

This replaces the four fixed owner columns the HubSpot model used. Adding “production lead” is a row, not a migration.

app.line_items

A line item is a garment in a color, decorated one way.

Column	Type	Notes
id, tenant_id, deal_id	UUID
position	INTEGER	Display order.
catalog_style_id, catalog_style_color_id	BIGINT → catalog.*	Nullable — customer-supplied goods have no catalog row.
decoration_method_id	SMALLINT → app.decoration_methods
name	TEXT NOT NULL
sku	TEXT
catalog_snapshot	JSONB	Frozen catalog data. See below.
catalog_snapshot_at	TIMESTAMPTZ	When it was frozen.
garment_color	TEXT
base_color, secondary_color, accent_color	TEXT	For sublimation. See below.
unit_price	NUMERIC(12,4)
setup_fee	NUMERIC(12,2)
unit_cost	NUMERIC(12,4)	What the shop pays.
quantity	INTEGER	Derived by trigger. Never hand-written.
extended_total	NUMERIC(14,2)
personalization_type	TEXT	Names, numbers.
is_taxable	BOOLEAN	Default true.
notes	TEXT

`catalog_snapshot` is what stops a quote repricing itself

Catalog prices move daily. A customer who approved a quote at $8.40 is owed $8.40 regardless of what a vendor pushed overnight.

catalog_snapshot freezes the catalog data at quote time. The live catalog is for browsing and re-pricing, never for rendering an existing deal. A handler that joins a saved deal to catalog.variant_prices to display it is a bug, even though it will look correct in every test written on the same day the fixture was created.

Why there are four colour columns

Sublimated goods are dyed, so garment_color is meaningless for them and the colour lives in base_color / secondary_color / accent_color. Modelling it explicitly beats a conditional repeated across three files, each of which will eventually disagree with the others.

app.line_item_sizes

Apparel quantity is two-dimensional. The size run hangs off the line:

Line:  PC61 · Athletic Heather
       └── S:12  M:36  L:48  XL:36  2XL:12   = 144

Column	Type	Notes
id, tenant_id, line_item_id	UUID
size_id	SMALLINT → catalog.sizes	Canonical. A typo’d size is an FK violation.
catalog_variant_id	BIGINT → catalog.variants	The specific SKU.
quantity	INTEGER	CHECK (quantity >= 0)
unit_price	NUMERIC(12,4)	Per-size price override.
size_upcharge	NUMERIC(12,4)	2XL costs more; the model says so.

UNIQUE (line_item_id, size_id).

This is relational, replacing a size_data JSON blob. The payoff is concrete: SUM/GROUP BY work, a size can carry its own price, and a misspelled size is a foreign key violation instead of a silently dropped quantity.

The quantity rollup is a database trigger, so it cannot drift:

CREATE TRIGGER line_item_sizes_rollup
AFTER INSERT OR UPDATE OR DELETE ON app.line_item_sizes
FOR EACH ROW EXECUTE FUNCTION app.sync_line_item_quantity();

The hottest read in the product is index-only

CREATE INDEX line_item_sizes_grid ON app.line_item_sizes (tenant_id, line_item_id, size_id)
    INCLUDE (quantity, unit_price, catalog_variant_id);

The INCLUDE clause makes rendering a twelve-size grid an index-only scan — it never touches the heap. Every quote screen, every work order, every board card does this read.

app.decoration_methods

Seven system defaults plus per-tenant custom methods, in one table:

code	label
screen_print	Screen Print
embroidery	Embroidery
dtf	DTF
dtg	DTG
sublimation	Sublimation
heat_transfer	Heat Transfer
patch	Patch

System rows have tenant_id IS NULL. That needs a hand-written policy rather than the generic helper, and the asymmetry is the point:

CREATE POLICY tenant_isolation ON app.decoration_methods
    USING      (tenant_id IS NULL OR tenant_id = app.current_tenant_id())
    WITH CHECK (tenant_id = app.current_tenant_id());

Everyone can read the system methods; nobody can write one. Copying the USING clause into WITH CHECK would let any tenant inject a fake system-wide method visible to every other shop — which is why this pair has its own test.

Pricing (planned)

The engine has not been built. The intended order:

1. Blank cost — the current variant price at the qualifying quantity break.
2. Tenant cost overlay — the shop’s negotiated tier from app.vendor_accounts.
3. Size upcharges — line_item_sizes.size_upcharge.
4. Markup — the shop’s margin rule.
5. Decoration — per imprint: setup_fee once, plus a per-piece run charge.
6. Fees — rush, folding and bagging, freight.
7. Tax — unless the company has a tax_exempt_id.

Every step is rust_decimal::Decimal; every stored value is NUMERIC; every JSON representation is a string, so a JavaScript client cannot silently reintroduce a float. The pricing function is pure — inputs in, priced deal out, no IO — which makes it the best-covered thing in the unit test layer.

API surface (planned)

Method	Path	Permission
GET	/v1/deals	deal.read
GET	/v1/deals/{id}	deal.read
POST	/v1/deals	deal.write
PATCH	/v1/deals/{id}	deal.write
POST	/v1/deals/{id}/transition	deal.write
POST	/v1/deals/{id}/line-items	deal.write
PATCH	/v1/line-items/{id}	deal.write
PUT	/v1/line-items/{id}/sizes	deal.write — replaces the whole size run
POST	/v1/deals/{id}/assignments	deal.write
POST	/v1/deals/{id}/price	deal.price.write
GET	/v1/deals/{id}/pdf	deal.read

deal.price.read is separate from deal.read because production needs to see the job without seeing the margin. POST /transition validates the target status server-side — the UI hides invalid transitions, and the API rejects them.

Planned

• The pricing engine, above.
• Reorders. “Same as last spring, in navy, 96 pieces.” Clone a closed deal, swap the colour, keep the design and the screens.
• Purchase orders to vendors, generated from confirmed lines and reconciled on receipt.
• Invoicing and payments — likely an integration via app.external_refs rather than a domain.
• Production scheduling — press assignment and capacity against production_due_date.
• Customer-facing quote acceptance via a scoped token, matching the approval flow that design already models.