Commerce

order_items

carts

cart_items

shipping_records

fulfillments

Enums

order_status

cart_status

shipping_status

fulfillment_status

Row-Level Security

All six tables are scoped to the current user's organization via get_user_org_id(). Any org member can select, insert, and update rows. Only admins (checked via is_admin()) can delete rows.

Dependencies

• billing -- products
• crm (transitive via billing) -- contacts
• identity (transitive via crm) -- organizations, users, get_user_org_id(), is_admin(), set_updated_at()

Example Queries

Calculate total revenue by order status:

SELECT
  status,
  count(*) AS order_count,
  sum(total) / 100.0 AS total_dollars
FROM orders
GROUP BY status
ORDER BY total_dollars DESC;

Show cart value for all active carts:

SELECT
  c.id AS cart_id,
  con.first_name || ' ' || con.last_name AS contact,
  count(ci.id) AS item_count,
  sum(ci.quantity * ci.unit_price) / 100.0 AS cart_value_dollars,
  c.expires_at
FROM carts c
LEFT JOIN contacts con ON con.id = c.contact_id
LEFT JOIN cart_items ci ON ci.cart_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, con.first_name, con.last_name, c.expires_at
ORDER BY cart_value_dollars DESC;

Track shipping status for an order:

SELECT
  o.number AS order_number,
  sr.carrier,
  sr.tracking_number,
  sr.status AS shipping_status,
  sr.shipped_at,
  sr.delivered_at
FROM shipping_records sr
JOIN orders o ON o.id = sr.order_id
WHERE o.id = '<order_id>'
ORDER BY sr.created_at DESC;

List orders awaiting fulfillment:

SELECT
  o.number AS order_number,
  o.status AS order_status,
  f.status AS fulfillment_status,
  o.total / 100.0 AS total_dollars,
  o.placed_at,
  f.notes
FROM orders o
JOIN fulfillments f ON f.order_id = o.id
WHERE f.status IN ('pending', 'processing')
ORDER BY o.placed_at ASC;

Revenue per product from completed orders:

SELECT
  oi.name AS product,
  sum(oi.quantity) AS units_sold,
  sum(oi.total) / 100.0 AS revenue_dollars
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status IN ('shipped', 'delivered')
GROUP BY oi.name
ORDER BY revenue_dollars DESC;

Identify abandoned carts older than 24 hours:

SELECT
  c.id AS cart_id,
  con.email,
  sum(ci.quantity * ci.unit_price) / 100.0 AS cart_value_dollars,
  c.created_at,
  c.expires_at
FROM carts c
LEFT JOIN contacts con ON con.id = c.contact_id
LEFT JOIN cart_items ci ON ci.cart_id = c.id
WHERE c.status = 'active'
  AND c.created_at < now() - interval '24 hours'
GROUP BY c.id, con.email, c.created_at, c.expires_at
ORDER BY cart_value_dollars DESC;