DD

Tables (14)

addresses

  • address_id — BIGSERIAL — Primary key — NOT NULL
  • customer_id — BIGINT — Foreign key → customers.customer_id — NOT NULL
  • line1 — TEXT — NOT NULL
  • line2 — TEXT
  • city — TEXT — NOT NULL
  • state — TEXT
  • postal_code — TEXT
  • country_code — CHAR(2) — Foreign key → country.country_code — NOT NULL
  • is_default — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

categories

  • category_id — BIGSERIAL — Primary key — NOT NULL
  • parent_id — BIGINT — Foreign key → categories.category_id
  • category_name — TEXT — NOT NULL

country

  • country_code — CHAR(2) — Primary key — NOT NULL
  • country_name — TEXT — NOT NULL

customers

  • customer_id — BIGSERIAL — Primary key — NOT NULL
  • email — TEXT — NOT NULL
  • full_name — TEXT — NOT NULL
  • phone — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • marketing_opt_in — BOOLEAN — NOT NULL
  • country_code — CHAR(2) — Foreign key → country.country_code

inventory

  • product_id — BIGINT — Foreign key → products.product_id — NOT NULL
  • qty_on_hand — INT — NOT NULL
  • reorder_point — INT — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

order_items

  • order_item_id — BIGSERIAL — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.order_id — NOT NULL
  • product_id — BIGINT — Foreign key → products.product_id — NOT NULL
  • qty — INT — NOT NULL
  • unit_price — NUMERIC(12,2) — NOT NULL
  • discount — NUMERIC(12,2) — NOT NULL
  • line_total — NUMERIC(12,2)

orders

  • order_id — BIGSERIAL — Primary key — NOT NULL
  • customer_id — BIGINT — Foreign key → customers.customer_id — NOT NULL
  • order_status — TEXT — NOT NULL
  • order_ts — TIMESTAMPTZ — NOT NULL
  • shipping_address_id — BIGINT — Foreign key → addresses.address_id
  • coupon_code — TEXT
  • channel — TEXT — NOT NULL

payments

  • payment_id — BIGSERIAL — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.order_id — NOT NULL
  • method — TEXT — NOT NULL
  • amount — NUMERIC(12,2) — NOT NULL
  • paid_at — TIMESTAMPTZ

product_prices

  • price_id — BIGSERIAL — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → products.product_id — NOT NULL
  • price — NUMERIC(12,2) — NOT NULL
  • currency — CHAR(3) — NOT NULL
  • valid_from — TIMESTAMPTZ — NOT NULL
  • valid_to — TIMESTAMPTZ — NOT NULL
  • is_current — BOOLEAN — NOT NULL

product_suppliers

  • product_id — BIGINT — Foreign key → products.product_id — NOT NULL
  • supplier_id — BIGINT — Foreign key → suppliers.supplier_id — NOT NULL
  • primary_supplier — BOOLEAN — NOT NULL

products

  • product_id — BIGSERIAL — Primary key — NOT NULL
  • sku — TEXT — NOT NULL
  • product_name — TEXT — NOT NULL
  • category_id — BIGINT — Foreign key → categories.category_id
  • unit — TEXT — NOT NULL
  • active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

reviews

  • review_id — BIGSERIAL — Primary key — NOT NULL
  • product_id — BIGINT — Foreign key → products.product_id — NOT NULL
  • customer_id — BIGINT — Foreign key → customers.customer_id — NOT NULL
  • rating — INT — NOT NULL
  • review_text — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • UNIQUE(product_id, — customer_id)

shipments

  • shipment_id — BIGSERIAL — Primary key — NOT NULL
  • order_id — BIGINT — Foreign key → orders.order_id — NOT NULL
  • carrier — TEXT — NOT NULL
  • tracking_no — TEXT
  • shipped_at — TIMESTAMPTZ
  • delivered_at — TIMESTAMPTZ

suppliers

  • supplier_id — BIGSERIAL — Primary key — NOT NULL
  • supplier_name — TEXT — NOT NULL
  • phone — TEXT