# Database ERD

## Entity relationship overview

```mermaid
erDiagram
  org_units ||--o{ org_units : parent
  org_units ||--o{ users : org_unit
  users ||--o{ user_roles : user
  roles ||--o{ user_roles : role
  sites ||--o{ user_roles : scope_site

  vendors ||--o{ vendor_bank_details : vendor
  vendors ||--o{ offers : vendor
  vendors ||--o{ purchase_orders : vendor

  regions ||--o{ sites : region
  sites ||--o{ rooms : site
  rooms ||--o{ rows : room
  rows ||--o{ racks : row
  racks ||--o{ positions : rack
  sites ||--o{ assets : site
  positions ||--o{ assets : position
  users ||--o{ assets : owner

  assets ||--o{ asset_components : parent
  assets ||--o{ asset_components : child
  assets ||--o{ provisioning_records : asset
  assets ||--o{ maintenances : asset

  purchase_orders }o--|| sites : inspection_site
  purchase_orders }o--|| sites : delivery_site
  purchase_orders ||--o{ shipments : po
  users ||--o{ audit_events : actor

  org_units { uuid id text name uuid parent_id text org_id }
  users { uuid id text email text org_id uuid org_unit_id }
  vendors { uuid id text org_id text legal_name text trust_tier }
  offers { uuid id text org_id uuid vendor_id int quantity decimal unit_price text status }
  purchase_orders { uuid id text org_id uuid vendor_id jsonb line_items text status }
  shipments { uuid id uuid purchase_order_id text tracking text status }
  regions { uuid id text org_id text name }
  sites { uuid id text org_id uuid region_id text name jsonb network_metadata }
  rooms { uuid id uuid site_id text name }
  rows { uuid id uuid room_id text name }
  racks { uuid id uuid row_id text name int ru_total jsonb power_feeds }
  positions { uuid id uuid rack_id int ru_start int ru_end uuid asset_id }
  assets { uuid id text org_id text asset_id text category text status uuid site_id uuid position_id }
  asset_components { uuid id uuid parent_asset_id uuid child_asset_id text role }
  provisioning_records { uuid id uuid asset_id text hypervisor_node text cluster_id }
  maintenances { uuid id text org_id uuid asset_id text type text status }
  audit_events { uuid id text org_id uuid actor_id text action text resource_type text resource_id jsonb before_state jsonb after_state timestamp occurred_at }
  roles { uuid id text name jsonb permissions }
  user_roles { uuid user_id uuid role_id uuid scope_site_id text scope_project_id }
```

## Core tables

- **org_units**, **users**: Tenancy and org hierarchy.
- **vendors**, **vendor_bank_details**: Vendor master; versioned bank details with dual approval.
- **offers**: SKU/MPN, quantity, price, evidence_refs, risk_score, status.
- **purchase_orders**: Line items, approval_stage, escrow_terms, inspection_site_id, delivery_site_id.
- **shipments**: PO link, tracking, customs_docs_refs.
- **regions**, **sites**, **rooms**, **rows**, **racks**, **positions**: Site hierarchy and RU mapping.
- **assets**: asset_id, category, serials, proof_artifact_refs, site_id, position_id, status, chain_of_custody.
- **asset_components**: parent_asset_id, child_asset_id, role (gpu/cpu/dimm/nic).
- **provisioning_records**: OS image, hypervisor node, cluster_id.
- **maintenances**: RMA/incident/part_swap; vendor_ticket_ref.
- **audit_events**: Append-only; actor_id, action, resource_type, resource_id, before_state, after_state.
- **roles**, **user_roles**: RBAC; scope_site_id, scope_project_id for ABAC.