ERP/docs/material-supplier-category-migration-plan.md

Migration Plan: Material → Supplier → Category Redesign

Schema: Procurement Schema v2.4 Change owner: [fill in] Status: Ready for engineering review

---

1. Objective

Today, a material can only exist inside a category, and a category is the only path that connects a material to a supplier:

material → category → supplier

We are flipping this so materials are global, catalog-wide entities that connect to a supplier first. Category is derived automatically from the supplier, not chosen by a category-first hierarchy:

material → supplier → category

Confirmed business rules driving this design:

1. Material names are globally unique across the entire catalog — not just unique within a category.
2. A supplier deals in exactly one category (1 supplier → 1 category; 1 category → N suppliers). A supplier is "known" for that category.
3. Category assignment must be fully automatic — no manual/admin step required after a material is linked to a supplier.

---

2. Schema changes — summary table

#	Table	Change	Type
1	materials	category_id becomes nullable	Alter column
2	materials	Unique index changes from (category_id, name) → name	Alter index
3	suppliers	Add category_id (not null, FK to material_categories.id)	New column
4	suppliers	Add index on category_id	New index
5	—	New table supplier_materials	New table
6	—	Trigger: auto-derive materials.category_id on first supplier link	New trigger
7	—	(Recommended, separate decision) sync job/trigger for supplier_materials.last_quoted_price	New trigger/job

Nothing else in the schema changes. category_attributes and the EAV machinery (material_attribute_values, attribute_definitions) are untouched — they continue to key off category_id, which now just gets populated automatically instead of being set at material-creation time.

---

3. Detailed DBML changes

3.1 materials — loosen the category requirement

Table materials {
  id uuid [pk, default: `gen_random_uuid()`]
  category_id uuid [ref: > material_categories.id, note: 'Nullable — populated automatically once linked to a supplier']
  name text [not null]
  canonical_unit_id integer [not null, ref: > units.id]
  status material_status [not null, default: `'active'`]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]

  indexes {
    name [unique]
  }
}

Removed: not null on category_id, and the composite (category_id, name) unique index. Added: bare name [unique].

⚠️ Pre-migration check required — see §5.1. A bare unique index on name will fail to apply if any duplicate material names currently exist across different categories.

3.2 suppliers — add the category tag

Table suppliers {
  id uuid [pk, default: `gen_random_uuid()`]
  name text [not null]
  category_id uuid [not null, ref: > material_categories.id, note: 'A supplier deals in exactly one category']
  location text
  phone text
  contact_person text
  tax_id text
  created_at timestamptz [not null, default: `now()`]

  indexes {
    category_id
  }
}

The index on category_id supports "list all suppliers in category X" lookups and category-based reporting.

⚠️ Pre-migration check required — see §5.2. not null can't be applied until every existing supplier row has a category assigned.

3.3 New table: supplier_materials

The catalog-layer edge that currently doesn't exist — what a supplier actually sells, independent of any quote.

// M:N Supplier <-> Material catalog junction
Table supplier_materials {
  id uuid [pk, default: `gen_random_uuid()`]
  supplier_id uuid [not null, ref: > suppliers.id]
  material_id uuid [not null, ref: > materials.id]
  is_active boolean [not null, default: true]
  last_quoted_price "numeric(18,4)" [note: 'Denormalized — see §3.5 sync mechanism']
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (supplier_id, material_id) [unique]
    material_id
  }
}

Two indexes, two different access patterns:

• (supplier_id, material_id) unique — "show this supplier's catalog," and prevents duplicate links.
• Standalone material_id — "which suppliers carry this material" (price comparison UI). Without this, that query has to scan the whole table.

3.4 Trigger: automatic category derivation

Fires the moment a material is linked to a supplier for the first time. If the material has no category yet, it inherits the supplier's category. Idempotent — only touches rows where category_id IS NULL, so it never overwrites a manually-set or already-derived category.

CREATE OR REPLACE FUNCTION fn_derive_material_category()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE materials
  SET category_id = (SELECT category_id FROM suppliers WHERE id = NEW.supplier_id),
      updated_at = now()
  WHERE id = NEW.material_id
    AND category_id IS NULL;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_supplier_materials_derive_category
AFTER INSERT ON supplier_materials
FOR EACH ROW
EXECUTE FUNCTION fn_derive_material_category();

Because suppliers.category_id is not null, this trigger can never pull a null category — it will always resolve to something as long as the supplier row is valid.

3.5 last_quoted_price sync — needs a team decision

supplier_materials.last_quoted_price is denormalized, and nothing in the schema keeps it in sync automatically yet. This wasn't part of the original ask, but it's a loose end worth closing now rather than discovering later. The team needs to pick the trigger event — the most natural candidate is when a line item's decision is finalized:

CREATE OR REPLACE FUNCTION fn_sync_last_quoted_price()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status = 'chosen' THEN
    UPDATE supplier_materials sm
    SET last_quoted_price = COALESCE(NEW.adjusted_unit_price, it.unit_price)
    FROM item_termsheets it
    JOIN line_items li ON li.id = it.line_item_id
    JOIN quotes q ON q.id = li.quote_id
    WHERE it.id = NEW.chosen_item_termsheet_id
      AND sm.supplier_id = q.supplier_id
      AND sm.material_id = li.material_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_item_decisions_sync_price
AFTER UPDATE OF status ON item_decisions
FOR EACH ROW
EXECUTE FUNCTION fn_sync_last_quoted_price();

Open question for the team: should this fire on item_decisions.status = 'chosen' (as above — reflects the price actually decided on), or on quotes.status = 'accepted' (reflects the whole quote being accepted, before line-level decisions are made)? These can produce different values if a decision is later adjusted. Pick one source of truth and confirm before implementing.

---

4. What is explicitly NOT changing

• category_attributes, attribute_definitions, material_attribute_values — the EAV system stays exactly as-is. It already keys off material_id directly with no FK to category, so a material sitting with category_id = NULL for a while doesn't break anything at the data layer — it just means the UI won't prompt for category-specific spec fields until category_id gets populated by the trigger.
• brand_materials, material_units — untouched.
• quotes, line_items, termsheets, item_decisions, item_receipts — untouched. supplier_materials is a catalog-layer table; it doesn't replace or interact with the transactional quote flow.

---

5. Migration steps (in order)

5.1 Pre-check: duplicate material names

Run this against production before touching the materials unique index:

SELECT name, COUNT(*) AS occurrences
FROM materials
GROUP BY name
HAVING COUNT(*) > 1;

If this returns any rows, resolve them (rename, merge, or archive duplicates) before proceeding — the new name [unique] index will fail to create otherwise.

5.2 Pre-check / backfill: supplier categories

Every existing supplier needs a category_id before the column can be made not null.

-- 1. Add as nullable first
ALTER TABLE suppliers ADD COLUMN category_id uuid REFERENCES material_categories(id);

-- 2. Backfill (manual, or scripted from existing quote/line_item history —
--    e.g. infer from the most common category among a supplier's past line items)

-- 3. Verify zero nulls remain
SELECT id, name FROM suppliers WHERE category_id IS NULL;

-- 4. Only once that returns zero rows:
ALTER TABLE suppliers ALTER COLUMN category_id SET NOT NULL;
CREATE INDEX idx_suppliers_category_id ON suppliers(category_id);

5.3 Apply materials changes

ALTER TABLE materials ALTER COLUMN category_id DROP NOT NULL;
DROP INDEX IF EXISTS materials_category_id_name_idx; -- adjust to actual constraint name
CREATE UNIQUE INDEX materials_name_idx ON materials(name);

5.4 Create supplier_materials

Create table and both indexes as in §3.3.

5.5 Create the category-derivation trigger

Deploy the function and trigger from §3.4.

5.6 Decide and implement last_quoted_price sync

Resolve the open question in §3.5, then deploy.

5.7 Backfill existing materials (optional, recommended)

Materials that already have a category_id from the old flow don't need anything — the trigger only acts on nulls. But if you want supplier_materials populated retroactively from quote history so the "which suppliers carry this material" UI works immediately instead of only going forward, backfill it from line_items:

INSERT INTO supplier_materials (supplier_id, material_id, is_active)
SELECT DISTINCT q.supplier_id, li.material_id, true
FROM line_items li
JOIN quotes q ON q.id = li.quote_id
ON CONFLICT (supplier_id, material_id) DO NOTHING;

---

6. Open decisions for the team

Question	Why it matters
Can a supplier ever legitimately sell more than one category (e.g. a general distributor)?	Current design hard-enforces 1 supplier = 1 category. If this happens in practice, the workaround is separate supplier records per category — confirm this is acceptable before locking it in.
Which event syncs last_quoted_price — item_decisions.status = 'chosen' or quotes.status = 'accepted'?	Determines what "last quoted price" actually means to users; needs one source of truth.
Do we backfill supplier_materials from historical line_items (§5.7)?	Affects whether "supplier catalog" and "which suppliers carry X" features work on day one or only going forward.
What happens to materials that never get linked to any supplier?	They stay permanently uncategorized (category_id = NULL) by design — confirm reporting/spend-by-category views handle a NULL bucket gracefully.

---

7. Testing checklist

• Inserting a material with no category_id succeeds.
• Inserting a duplicate material name (any category) fails with a unique violation.
• Inserting a supplier with no category_id fails (not null violation).
• Linking a material to a supplier via supplier_materials auto-populates materials.category_id to match the supplier's category.
• Linking a material to a second supplier (different category) does not overwrite an already-populated category_id.
• category_attributes-driven UI still correctly shows spec fields once category_id is populated.
• Reverse lookup ("suppliers carrying material X") uses the material_id index and performs well at scale.
• last_quoted_price updates correctly once the chosen sync trigger fires (per §3.5 decision).