Data Model

Data Model

The schema is owned by Alembic migrations and organised into domain modules under backend/app/models/. Every entity has a UUID primary key and date_created / last_updated audit columns (via IdMixin / TimestampMixin in core/db.py). Money is stored as SQL Numeric. The same models run on SQLite (dev) and Postgres 16 (prod).

---

The provenance spine

The design's central idea: an asset's continuous identity. A serial unit is born on receipt and the same row moves through the warehouse into a rack — its location and status change, but its identity and its link back to the order line never break.

 Product ──< ProductSupplier ──< OrderItem ──< ReceiptItem ──> Asset
 (the spec)   (a source:          (a PO line,    (a receiving    (the serial unit,
              price/MOQ/          points at the   event line)     source_order_item_id
              lead-time/rank)     chosen source)                  → never broken)
                                       ▲                                │
                                  PurchaseOrder                    provenance link
                                  (one per supplier)          (spend & origin tracing)

GET /assets/{id}/provenance walks this chain: asset → order line → order → supplier → unit spend. Spend analytics is computed from received assets via this link, so every figure traces to a real unit.

Catalog (models/catalog.py) — the what & the who-we-buy-from

Entity	Role	Key fields
Organization	A company we deal with — supplier and/or manufacturer (role-flagged, one org can be both)	is_supplier, active, onboarding_status; is_approved_supplier = supplier + active + APPROVED
Product	The supplier-independent spec (server model, CPU SKU, DIMM). No expiry field — hardware doesn't expire	product_code, name, category
ProductSupplier	One source for a product. Multiple rows = multi-sourcing	contract_price, min_order_quantity, standard_lead_time_days, preference_rank (lower = preferred), active, currency_code
ContractDocument	An uploaded contract artifact for a source	—

Organization, Product (and PurchaseOrder below) carry ExternalRefMixin — the (source_system, external_ref) key for round-tripping ERP-synced records.

Procurement (models/procurement.py) — the buying

Entity	Role
PurchaseOrder	A buy from a supplier, with a status lifecycle and a destination location
OrderItem	A line; points at the chosen ProductSupplier — re-sourcing a line is just repointing this link

OrderStatus: PENDING → APPROVED → PLACED → PARTIALLY_RECEIVED → RECEIVED (or CANCELLED). Receipt-driven statuses advance automatically from cumulative received-vs-ordered; over-receipt is rejected. Hand-set statuses are blocked by a guarded transition table.

Flow & lifecycle (models/flow.py) — receiving, then the life of a unit

Entity	Role
Location	A place — self-referential (a rack nests under a datacenter; the transit warehouse is a location). capacity is a tunable knob
Receipt / ReceiptItem	An inbound receiving event against a PO
Asset	The spine. A serial unit; keeps a current location and an unbroken source_order_item_id link
AssetEvent	Append-only log of every status/location change (type, from→to, actor, note, timestamp)

LocationType: WAREHOUSE · DATACENTER · RACK · SUPPLIER · DISPOSAL.

AssetStatus state machine (enforced by the pure transition table in services/lifecycle.py; illegal jumps → 422):

 RECEIVED ──► IN_STORAGE ──► DEPLOYED ──► MAINTENANCE ──► DECOMMISSIONED ──► DISPOSED
    │                          ▲   │           │                 ▲
    └──────────────────────────┘   └───────────┘                 │
        (deploy directly)        (back in service)        (retire from maintenance)

Requisition (models/requisition.py) — auto-buy + approval

Entity	Role
PurchaseRequisition	A staged Purchase Request (the editable cart), one per supplier bundle
RequisitionLine	A line on a PR; can be edited or excluded before approval
RequisitionFeedback	The calibration signal: action (approved/edited/rejected), proposed_qty, final_qty, confidence, auto_placed per product×supplier

RequisitionStatus: STAGED (editable cart) → PLACED (approved, converted to a PO, po_id set) / REJECTED.

Costing (models/costing.py) — should-cost inputs

Entity	Role
Commodity	A tracked index (DRAM_DDR5, NAND_TLC, STEEL_CR, COPPER_LME); baseline_value is the level the component cost is quoted at
CommodityPrice	A (price_date, value) point; engine reads the most recent on/before as-of (step function, not interpolation)
ComponentClass	A component category and its costing method
BOM / BOMLine	The bill of materials that decomposes a product into costed lines
CostParams	Integration / SG&A / target-margin percentages
ShouldCostRun	A persisted run (floor + target) for a product, used by TCO as a read-only variance

CostingMethod: teardown (index-driven material build-up — DRAM, NAND, metals, PCB) · reference_price (vendor list × discount band — CPU/GPU silicon, no public commodity to track).

TCO (models/tco.py) — the cost beyond the price

Acquisition is not stored here — it's read from the provenance chain. These five layer tables stack on top:

Table	Cardinality	Captures
LandedCost	multi-row	freight / insurance / duty / handling; cost_type excludable at query time (tariff scenarios); incoterm
DeploymentCost	multi-row	receiving / racking / cabling / imaging labour (labor_hours × rate)
OpexLedger	~60 rows/asset	one month of run-time cost: power_kwh × pue × energy_rate + cooling + maintenance + license
EolCost	one/asset	decommission + data destruction + WEEE + ITAD fee
RecoveryValue	one/asset	residual / resale (subtracts in the sum); depr_method (straight-line / declining / none)

All carry a currency column (default EUR); the TCO service fails loud on any non-EUR row. tco = acquisition + Σlanded + Σdeployment + Σopex + Σeol − recovery.

Decision & auth

Entity	Role
DecisionLog (models/decision.py)	An auditable record of each agent decision incl. the confidence factor breakdown
User / Role (models/auth.py)	JWT identity + RBAC — see Security-Model
Package (models/ordering.py)	Named reusable bundles (Compute rack, GPU pod) that expand to order lines ×N — distinct from a costing BOM

Indexed hot paths

An additive migration indexes the four columns the analytics joins, capacity counts, and provenance lookups filter on: receipt_item.order_item_id, asset.status, asset.source_order_item_id, asset.current_location_id — sequential scans become index lookups at scale. See Design-Decisions.