🔗 Complete System Schema

erDiagram
    %% SQL Tables
    ORDERS {
        uuid order_id PK
        uuid user_id
        uuid cart_id FK
        string status
        integer total_cents
        string address
        timestamp created_at
        timestamp expires_at
    }
    
    CARTS {
        uuid cart_id PK
        uuid user_id
        timestamp created_at
    }
    
    CART_ITEMS {
        uuid cart_id FK
        string sku FK
        integer qty
        integer price_cents
    }
    
    PRODUCTS {
        string sku PK
        string name
        string description
        integer price_cents
        timestamp created_at
    }
    
    STOCK {
        string sku PK,FK
        integer quantity
        integer reserved
    }
    
    PAYMENTS {
        uuid payment_id PK
        uuid order_id FK
        string status
        string provider
        string external_ref
        timestamp created_at
    }
    
    %% DynamoDB Entities (logical representation)
    USER {
        string user_id PK
        string name
        string email
        string phone
    }
    
    USER_ADDRESS {
        string user_id PK
        string address_id
        string city
        string zip
    }
    
    USER_PAYMENT {
        string user_id PK
        string provider
        string token
        boolean is_default
    }
    
    DELIVERY_SHIPMENT {
        string order_id PK
        string status
        object address
    }
    
    DELIVERY_TRACKING {
        string order_id PK
        string timestamp
        string status
    }
    
    %% Relationships
    USER ||--o{ CARTS : creates
    USER ||--o{ ORDERS : places
    USER ||--|| USER_ADDRESS : has
    USER ||--o{ USER_PAYMENT : has
    
    CARTS ||--o{ CART_ITEMS : contains
    CARTS ||--|| ORDERS : creates
    ORDERS ||--|| PAYMENTS : has
    ORDERS ||--|| DELIVERY_SHIPMENT : has
    DELIVERY_SHIPMENT ||--|| DELIVERY_TRACKING : tracks
    
    PRODUCTS ||--o{ CART_ITEMS : "in cart"
    PRODUCTS ||--|| STOCK : has