Ecommerce Core Schema — StealThis.dev

Code

create table users (
  id bigint primary key generated always as identity,
  email text not null unique,
  full_name text not null,
  created_at timestamptz not null default now()
);

create table products (
  id bigint primary key generated always as identity,
  sku text not null unique,
  name text not null,
  price_cents integer not null check (price_cents >= 0),
  is_active boolean not null default true,
  created_at timestamptz not null default now()
);

create table inventories (
  product_id bigint primary key references products(id) on delete cascade,
  quantity integer not null default 0 check (quantity >= 0),
  updated_at timestamptz not null default now()
);

create table orders (
  id bigint primary key generated always as identity,
  user_id bigint not null references users(id),
  status text not null check (status in ('pending','paid','shipped','cancelled')),
  total_cents integer not null default 0 check (total_cents >= 0),
  created_at timestamptz not null default now()
);

create table order_items (
  id bigint primary key generated always as identity,
  order_id bigint not null references orders(id) on delete cascade,
  product_id bigint not null references products(id),
  quantity integer not null check (quantity > 0),
  unit_price_cents integer not null check (unit_price_cents >= 0),
  unique (order_id, product_id)
);

create table payments (
  id bigint primary key generated always as identity,
  order_id bigint not null unique references orders(id) on delete cascade,
  provider text not null,
  provider_ref text not null,
  amount_cents integer not null check (amount_cents >= 0),
  status text not null check (status in ('authorized','captured','failed','refunded')),
  created_at timestamptz not null default now()
);

create index idx_orders_user_id_created_at on orders(user_id, created_at desc);
create index idx_order_items_product_id on order_items(product_id);
create index idx_payments_status on payments(status);

insert into users (email, full_name)
values
  ('ana@example.com', 'Ana Gomez'),
  ('leo@example.com', 'Leo Cruz');

insert into products (sku, name, price_cents)
values
  ('TSHIRT-BLK-M', 'Black T-Shirt M', 2599),
  ('HOODIE-NVY-L', 'Navy Hoodie L', 4999),
  ('CAP-GRY-ONE', 'Grey Cap', 1899);

insert into inventories (product_id, quantity)
values
  (1, 42),
  (2, 18),
  (3, 70);

insert into orders (user_id, status, total_cents)
values
  (1, 'paid', 7598),
  (2, 'pending', 1899);

insert into order_items (order_id, product_id, quantity, unit_price_cents)
values
  (1, 1, 1, 2599),
  (1, 2, 1, 4999),
  (2, 3, 1, 1899);

insert into payments (order_id, provider, provider_ref, amount_cents, status)
values
  (1, 'stripe', 'pi_001', 7598, 'captured');

-- 1) Top selling products by quantity
select
  p.sku,
  p.name,
  sum(oi.quantity) as units_sold
from order_items oi
join products p on p.id = oi.product_id
join orders o on o.id = oi.order_id
where o.status in ('paid', 'shipped')
group by p.id
order by units_sold desc;

-- 2) Revenue by day
select
  date_trunc('day', o.created_at) as day,
  sum(o.total_cents) / 100.0 as revenue_usd
from orders o
where o.status in ('paid', 'shipped')
group by day
order by day desc;

-- 3) User purchase history
select
  o.id as order_id,
  o.status,
  o.total_cents,
  o.created_at
from orders o
where o.user_id = 1
order by o.created_at desc;

erDiagram
  users ||--o{ orders : places
  orders ||--|{ order_items : contains
  products ||--o{ order_items : included_in
  products ||--|| inventories : tracks_stock
  orders ||--|| payments : paid_by

  users {
    bigint id PK
    text email
    text full_name
    timestamptz created_at
  }

  products {
    bigint id PK
    text sku
    text name
    int price_cents
    bool is_active
  }

  inventories {
    bigint product_id PK,FK
    int quantity
  }

  orders {
    bigint id PK
    bigint user_id FK
    text status
    int total_cents
  }

  order_items {
    bigint id PK
    bigint order_id FK
    bigint product_id FK
    int quantity
    int unit_price_cents
  }

  payments {
    bigint id PK
    bigint order_id FK
    text provider
    text provider_ref
    int amount_cents
    text status
  }

Relational baseline for a typical ecommerce backend.

Includes:

Product catalog + inventory
Order lifecycle
Payment registration
Query examples for reporting and operations