71 lines
3.3 KiB
Markdown
71 lines
3.3 KiB
Markdown
# 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.
|