Ecommerce Core Schema — Lab

Diagram Views

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 }

SQL Snippets

schema.sql

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);

seed.sql

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');

queries.sql

-- 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;