Order Tables

Tables for managing orders, customers, and cart abandonment tracking.

orders

Customer orders from all channels.

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  customer_id UUID REFERENCES customers(id),
  order_number TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'paid', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded')),
  subtotal_amount NUMERIC(10,2) NOT NULL,
  shipping_amount NUMERIC(10,2) DEFAULT 0,
  tax_amount NUMERIC(10,2) DEFAULT 0,
  discount_amount NUMERIC(10,2) DEFAULT 0,
  total_amount NUMERIC(10,2) NOT NULL,
  platform_fee NUMERIC(10,2) DEFAULT 0,
  currency TEXT DEFAULT 'USD',
  customer_email TEXT,
  customer_name TEXT,
  shipping_address JSONB,
  billing_address JSONB,
  shipping_method TEXT,
  tracking_number TEXT,
  carrier TEXT,
  notes TEXT,
  channel TEXT DEFAULT 'direct',
  channel_order_id TEXT,
  stripe_payment_intent_id TEXT,
  paid_at TIMESTAMPTZ,
  shipped_at TIMESTAMPTZ,
  delivered_at TIMESTAMPTZ,
  cancelled_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_orders_org_id ON orders(org_id);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_order_number ON orders(order_number);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
CREATE INDEX idx_orders_channel ON orders(channel);

Column	Type	Description
id	UUID	Primary key
org_id	UUID	FK to organization
customer_id	UUID	FK to customer
order_number	TEXT	Human-readable order ID
status	TEXT	Order lifecycle status
subtotal_amount	NUMERIC	Items total before fees
shipping_amount	NUMERIC	Shipping cost
tax_amount	NUMERIC	Tax collected
discount_amount	NUMERIC	Discounts applied
total_amount	NUMERIC	Final charged amount
platform_fee	NUMERIC	Artbase commission
currency	TEXT	ISO currency code
customer_email	TEXT	Customer email
customer_name	TEXT	Customer name
shipping_address	JSONB	Delivery address
billing_address	JSONB	Billing address
shipping_method	TEXT	Shipping service
tracking_number	TEXT	Shipment tracking
carrier	TEXT	Shipping carrier
notes	TEXT	Order notes
channel	TEXT	direct, etsy, gumroad
channel_order_id	TEXT	External order reference
stripe_payment_intent_id	TEXT	Stripe reference

Address JSONB Structure

{
  "line1": "123 Main St",
  "line2": "Apt 4B",
  "city": "Portland",
  "state": "OR",
  "postal_code": "97201",
  "country": "US",
  "phone": "+1-555-123-4567"
}

---

order_items

Line items for each order.

CREATE TABLE order_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  org_id UUID REFERENCES organizations(id),
  product_id UUID REFERENCES products(id) ON DELETE SET NULL,
  variant_id UUID REFERENCES product_variants(id) ON DELETE SET NULL,
  title TEXT NOT NULL,
  variant_title TEXT,
  sku TEXT,
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_price NUMERIC(10,2) NOT NULL,
  total_price NUMERIC(10,2) NOT NULL,
  weight_grams INTEGER,
  properties JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_org_id ON order_items(org_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

Column	Type	Description
id	UUID	Primary key
order_id	UUID	FK to order
org_id	UUID	FK to organization
product_id	UUID	FK to product
variant_id	UUID	FK to variant
title	TEXT	Product title snapshot
variant_title	TEXT	Variant name snapshot
sku	TEXT	SKU snapshot
quantity	INTEGER	Quantity ordered
unit_price	NUMERIC	Price per unit
total_price	NUMERIC	quantity * unit_price
weight_grams	INTEGER	Weight for shipping
properties	JSONB	Custom line properties

---

customers

Customer records normalized across orders.

CREATE TABLE customers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  first_name TEXT,
  last_name TEXT,
  phone TEXT,
  default_address JSONB,
  total_orders INTEGER DEFAULT 0,
  total_spent NUMERIC(10,2) DEFAULT 0,
  tags TEXT[],
  notes TEXT,
  accepts_marketing BOOLEAN DEFAULT FALSE,
  source TEXT DEFAULT 'direct',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(org_id, email)
);

CREATE INDEX idx_customers_org_id ON customers(org_id);
CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_customers_created_at ON customers(created_at DESC);

Column	Type	Description
id	UUID	Primary key
org_id	UUID	FK to organization
email	TEXT	Customer email
first_name	TEXT	First name
last_name	TEXT	Last name
phone	TEXT	Phone number
default_address	JSONB	Preferred address
total_orders	INTEGER	Lifetime order count
total_spent	NUMERIC	Lifetime spend
tags	TEXT[]	Customer tags
notes	TEXT	Internal notes
accepts_marketing	BOOLEAN	Marketing consent
source	TEXT	Acquisition source

---

abandoned_carts

Cart abandonment tracking for recovery emails.

CREATE TABLE abandoned_carts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  cart_items JSONB NOT NULL,
  cart_total NUMERIC(10,2) NOT NULL,
  recovery_email_sent_at TIMESTAMPTZ,
  recovered_at TIMESTAMPTZ,
  order_id UUID REFERENCES orders(id),
  session_id TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_abandoned_carts_org_id ON abandoned_carts(org_id);
CREATE INDEX idx_abandoned_carts_email ON abandoned_carts(email);
CREATE INDEX idx_abandoned_carts_created_at ON abandoned_carts(created_at);
CREATE INDEX idx_abandoned_carts_pending ON abandoned_carts(org_id, created_at)
  WHERE recovery_email_sent_at IS NULL AND recovered_at IS NULL;

Column	Type	Description
id	UUID	Primary key
org_id	UUID	FK to organization
email	TEXT	Customer email
cart_items	JSONB	Cart contents
cart_total	NUMERIC	Cart value
recovery_email_sent_at	TIMESTAMPTZ	When email was sent
recovered_at	TIMESTAMPTZ	When cart was purchased
order_id	UUID	Resulting order (if any)
session_id	TEXT	Browser session ID

Cart Items Structure

[
  {
    "product_id": "uuid",
    "variant_id": "uuid",
    "title": "Ceramic Vase",
    "variant_title": "Large / White",
    "quantity": 1,
    "price": 85.00,
    "image_url": "https://..."
  }
]

---

channel_orders

Raw order data from external channels (audit trail).

CREATE TABLE channel_orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  channel_account_id UUID NOT NULL REFERENCES channel_accounts(id) ON DELETE CASCADE,
  channel_order_id TEXT NOT NULL,
  order_id UUID REFERENCES orders(id),
  raw_data JSONB NOT NULL,
  synced_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(channel_account_id, channel_order_id)
);

CREATE INDEX idx_channel_orders_account ON channel_orders(channel_account_id);
CREATE INDEX idx_channel_orders_order_id ON channel_orders(order_id);

Column	Type	Description
id	UUID	Primary key
channel_account_id	UUID	FK to channel account
channel_order_id	TEXT	External order ID
order_id	UUID	FK to normalized order
raw_data	JSONB	Original API response
synced_at	TIMESTAMPTZ	Sync timestamp

---

RLS Policies

-- Orders: Organization access
CREATE POLICY "Members view org orders"
  ON orders FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Order items: Through order relationship
CREATE POLICY "Members view order items"
  ON order_items FOR SELECT
  USING (order_id IN (
    SELECT id FROM orders
    WHERE org_id IN (
      SELECT org_id FROM organization_members
      WHERE user_id = auth.uid()
    )
  ));

-- Customers: Organization access
CREATE POLICY "Members view org customers"
  ON customers FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

-- Abandoned carts: Organization access
CREATE POLICY "Members view org abandoned carts"
  ON abandoned_carts FOR SELECT
  USING (org_id IN (
    SELECT org_id FROM organization_members
    WHERE user_id = auth.uid()
  ));

---

Common Queries

Get Orders with Items

SELECT
  o.*,
  COALESCE(json_agg(oi.*) FILTER (WHERE oi.id IS NOT NULL), '[]') as items
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
WHERE o.org_id = $1
GROUP BY o.id
ORDER BY o.created_at DESC
LIMIT 50;

Get Customer Order History

SELECT o.*
FROM orders o
WHERE o.customer_id = $1
ORDER BY o.created_at DESC;

Abandoned Carts Ready for Recovery

SELECT *
FROM abandoned_carts
WHERE org_id = $1
  AND recovery_email_sent_at IS NULL
  AND recovered_at IS NULL
  AND created_at < NOW() - INTERVAL '1 hour'
ORDER BY created_at;

Update Order Status with Trigger

-- Trigger function for shipping notifications
CREATE OR REPLACE FUNCTION notify_order_shipped()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status = 'shipped' AND OLD.status != 'shipped' THEN
    PERFORM pg_notify('order_shipped', json_build_object(
      'order_id', NEW.id,
      'org_id', NEW.org_id,
      'customer_email', NEW.customer_email,
      'tracking_number', NEW.tracking_number
    )::text);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_order_shipped
  AFTER UPDATE ON orders
  FOR EACH ROW
  EXECUTE FUNCTION notify_order_shipped();