Project: E-Commerce Orders

Introduction

E-commerce schemas tie products, inventory, orders, and order lines with strict transactional integrity—stock must not go negative when two buyers checkout together. This project builds PostgreSQL tables, implements checkout in a transaction with RETURNING, and writes sales reports with aggregates and window functions. Compare with MySQL e-commerce project.

Prerequisites

• Transactions Basics
• Isolation and Locking
• Subqueries and Aggregates
• Window Functions

Schema

Seed Data

INSERT INTO customers (email) VALUES ('buyer@example.com') RETURNING id;
 
INSERT INTO products (name) VALUES ('Widget'), ('Gadget') RETURNING id;
 
INSERT INTO skus (product_id, sku_code, price) VALUES
  (1, 'WIDGET-01', 19.99),
  (2, 'GADGET-01', 29.99)
RETURNING id, sku_code;
 
INSERT INTO inventory (sku_id, qty) VALUES (1, 10), (2, 10);

Checkout Transaction

Purchase 2 units of SKU 1—PostgreSQL style with RETURNING:

Simpler step-by-step version:

Code explanation:

• qty >= 2 in WHERE makes decrement atomic—concurrent checkout gets 0 rows updated if stock insufficient
• RETURNING id replaces MySQL LAST_INSERT_ID()
• App should ROLLBACK when inventory update affects zero rows

Stricter Locking

High contention—lock inventory row first:

BEGIN;
 
SELECT qty FROM inventory WHERE sku_id = 1 FOR UPDATE;
 
-- validate qty, insert order + items, update inventory
 
COMMIT;

See Isolation and Locking.

Report: Sales by Day

SELECT
  date_trunc('day', o.created_at)::date AS day,
  COUNT(DISTINCT o.id) AS order_count,
  SUM(o.total) AS revenue
FROM orders o
WHERE o.status = 'paid'
  AND o.created_at >= '2026-05-01'
GROUP BY date_trunc('day', o.created_at)
ORDER BY day;

Report: Top SKUs

SELECT
  s.sku_code,
  SUM(oi.qty) AS units_sold,
  SUM(oi.qty * oi.unit_price) AS gross
FROM order_items oi
INNER JOIN skus s ON s.id = oi.sku_id
INNER JOIN orders o ON o.id = oi.order_id
WHERE o.status = 'paid'
GROUP BY s.id, s.sku_code
ORDER BY gross DESC
LIMIT 10;

Report: Top Product per Day (Window)

Coupons (Design Sketch)

CREATE TABLE coupons (
  code TEXT PRIMARY KEY,
  discount_pct NUMERIC(5, 2) NOT NULL,
  valid_until DATE,
  CONSTRAINT chk_discount CHECK (discount_pct >= 0 AND discount_pct <= 100)
);
 
CREATE TABLE order_coupons (
  order_id BIGINT PRIMARY KEY REFERENCES orders (id),
  coupon_code TEXT NOT NULL REFERENCES coupons (code)
);

Apply discount inside the checkout transaction before final total update.

Deliverables

• Checkout reduces inventory.qty
• Oversell attempt rolls back (zero-row inventory update)
• Daily revenue query runs
• Top SKU report runs
• EXPLAIN ANALYZE on at least one report

FAQ

Idempotency?

Add client_order_id TEXT UNIQUE on orders to prevent double submit.

Payment gateway?

Store payment_ref TEXT on orders—outside DB tutorial scope.

NUMERIC for money?

Always NUMERIC—never float in Java or Python either.

Serializable isolation?

Default READ COMMITTED + atomic UPDATE … WHERE qty >= n` is enough for many shops; use SERIALIZABLE only if you measure conflicts.

Scale inventory?

Warehouse id on inventory, row locks per (warehouse_id, sku_id).

Deploy with cache?

PostgreSQL + Redis + API Deploy.