Org schema

Tenant-scoped tables. Every row carries org_id UUID NOT NULL REFERENCES platform.organizations(id) for shared-DB isolation. Per-tenant uniqueness is enforced via composite constraints (UNIQUE(org_id, name), UNIQUE(org_id, code), etc.).

There are too many tables in org.* for a single ER diagram to be useful. They're presented here in clusters; each cluster is a sidebar entry of its own.

Conventions

• org_id everywhere. Required for shared-DB isolation. Becomes redundant and is dropped at physical-split time. The DEFAULT placeholder is removed — every insert must pass org_id explicitly.
• Archive + delete policy. Every entity supports delete (for mistakes; blocked at the FK level when referenced). is_active (archive) is added selectively:
  • Master data + entity tables (customers, vendors, skus, materials, locations, lookup tables, contacts) — is_active ✅
  • Transactional headers (purchase_orders, work_orders, sales_orders) — is_active ❌; cancellation lives on the workflow status.
  • Line items, receipts, append-only logs (ledgers, timeline notes, documents) — is_active ❌; delete only.
• Per-tenant unique constraints. All "natural key" uniqueness is (org_id, ...) rather than just on the column. (org_id, sort_order) constraints are DEFERRABLE INITIALLY IMMEDIATE to support reorders.
• Lifecycle dates match statuses. Every lifecycle date column on a transactional table corresponds to a value in its workflow status enum. No orphan dates.
• Aggregate totals are computed on read — no order_value on sales_orders, no costs on purchase_orders. The API computes SUM(line_items) per response.
• Renames in this redesign:
  • fulfillment_locations → org.locations (FK columns fulfillment_location_id → location_id; work_site_id keeps its semantic name on work_orders).
  • sales_orders.MABD → mabd.
• Status types are ENUMs, not tables. PO / WO / SO / fulfillment workflow statuses are Postgres ENUM types defined in org.* (org.po_status, org.wo_status, org.order_status, org.fulfillment_status) and stored directly on the transactional tables — no FK lookups, no separate status type tables.
• Other ENUMs. org.po_target_type for PO target; org.entity_contact_kind for contact-link kind; org.entity_document_kind for document attachments; materials_inventory_event_type and finished_goods_inventory_event_type for ledger events.

Clusters

Page	What's in it
Organization settings	Per-tenant operational data (legal name, branding, billing) split off from platform.organizations.
Customers	customers + classification tables (customer_types, customer_segments, brokers).
Vendors	vendors.
Products	skus + sku_types.
Materials	materials + material_types.
Locations	locations (formerly fulfillment_locations).
Order channels	order_channels.
Contacts	contacts + contact_methods + entity_contacts.
Documents	documents (polymorphic file attachments for SO / PO / WO).
Bill of materials	bom_items.
Purchase orders	purchase_orders + po_items + po_receipts + po_receipt_items.
Work orders	work_orders + work_order_items + work_order_inputs (recipe snapshot) + work_order_receipts + work_order_receipt_items.
Sales orders	sales_orders + sales_order_items + sales_order_timeline_notes + (documents via the polymorphic table).
Materials inventory ledger	materials_inventory_ledger.
Finished goods inventory ledger	finished_goods_inventory_ledger.

Cross-schema relationships

There's only one — org_id.

Every org.* table's org_id references platform.organizations(id). That's it — status types are ENUMs in org.* (Q8), so they don't introduce cross-schema FKs. At physical-split time the org_id column gets dropped (one tenant per DB), and the cross-schema reference becomes an application-level invariant ("when an org is created in the platform DB, provision its tenant DB").