sankofa-hw-infra/docs/erd.md

Database ERD

Entity relationship overview

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.