Skip to content

Orders

orders

Table: public.orders

Column Type Default Description
id uuid PK UUID
order_number text NOT NULL Human-readable
user_id uuid FK → profiles(id) Customer
address_id uuid FK → addresses(id) Shipping
shipping_address jsonb nullable Address snapshot
subtotal numeric NOT NULL
shipping_cost numeric 0
tax_amount numeric 0
discount_amount numeric 0
total_amount numeric NOT NULL
payment_method text nullable
payment_status text 'pending'
order_status text 'pending' Lifecycle status
notes text nullable
admin_notes text nullable
tracking_number text nullable
carrier text nullable
prescription_url text nullable Rx image
prescription_status text 'pending'
created_at timestamptz now()
updated_at timestamptz now()

order_items

Column Type Description
id uuid PK
order_id uuid FK → orders(id)
product_id uuid FK → products(id)
seller_id uuid FK → sellers(id)
variant_combination_id uuid nullable
quantity int
price_at_purchase numeric Snapshot
status text Per-item status

payments

Column Type Description
id uuid PK
order_id uuid FK → orders(id)
transaction_id text nullable Chapa ID
payment_method text nullable
amount numeric
currency text ETB
status text pending, success, failed
chapa_tx_ref text nullable Chapa reference
chapa_response jsonb nullable Raw webhook
paid_at timestamptz nullable
created_at timestamptz

order_status_history

Column Type Description
id uuid PK
order_id uuid FK → orders(id)
old_status text nullable
new_status text
updated_by text nullable
notes text nullable
created_at timestamptz

carts

Column Type Description
id uuid PK
user_id uuid FK → profiles(id)

cart_items

Column Type Description
id uuid PK
cart_id uuid FK → carts(id)
product_id uuid FK → products(id)
quantity int

wishlists

Column Type Description
id uuid PK
user_id uuid FK → profiles(id)

wishlist_items

Column Type Description
id uuid PK
wishlist_id uuid FK → wishlists(id)
product_id uuid FK → products(id)