Learn SQL with PostgreSQL - Production-Grade E-Commerce Database Case Study

#Introduction

Welcome to episode 20 — the final episode of the Learn SQL with PostgreSQL series! Our journey has been long: from the foundations of tables and queries, joins, window functions, transactions and ACID, indexing and optimization, security, to backup and replication. Now it's time for the real final exam: designing a production-ready e-commerce database by applying all of it at once.

This is the "putting the puzzle together" moment: UUID and RLS from episode 16 protect user data, JSONB and full-text search from episodes 8 and 17 manage the catalog, pessimistic locking from episode 11 prevents double spending, audit triggers from episode 13 record the trail, and materialized views from episode 12 serve analytical reports.

In this episode, we'll design a complete schema in four parts: Users & Auth, Product Catalog, Inventory & Order Processing, and Analytics. Then we'll close with a production readiness checklist and maintenance routine.

#1. Users & Auth: UUID PK, Multi-Tenant RLS, Hashed Credentials

UUID as the primary key (episode 3): a global identity that doesn't leak the number of users and is safe for distributed integration.
Hashed credentials: passwords are stored as a hash, not plaintext.
RLS (episode 16): per-tenant row isolation to support multi-store in a single database.

Users table with UUID and hashed credentials

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    email TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    full_name TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'customer'
        CHECK (role IN ('customer', 'admin', 'staff')),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, email)
);

#2. Product Catalog: JSONB Flexible Attributes & Full-Text Search

Products table with JSONB and FTS

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    name TEXT NOT NULL,
    slug TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    category_id UUID,
    price NUMERIC(12,2) NOT NULL CHECK (price >= 0),
    attributes JSONB NOT NULL DEFAULT '{}',
    search_vector TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('indonesian', name || ' ' || description)
    ) STORED,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, slug)
);

Search products with FTS

SELECT id, name, price
FROM products
WHERE search_vector @@ plainto_tsquery('indonesian', 'sepatu lari')
ORDER BY ts_rank(search_vector, plainto_tsquery('indonesian', 'sepatu lari')) DESC;

#3. Inventory & Order Processing: Strict Constraints, Locking & Audit

Inventory and orders tables

CREATE TABLE inventory (
    product_id UUID PRIMARY KEY REFERENCES products(id) ON DELETE CASCADE,
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    total NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK (total >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Order processing with locking and transactions

BEGIN;
 
SELECT stock FROM inventory
WHERE product_id = '9a8b7c6d-0000-0000-0000-000000000001'
FOR UPDATE;
 
UPDATE inventory
SET stock = stock - 1,
    updated_at = now()
WHERE product_id = '9a8b7c6d-0000-0000-0000-000000000001'
  AND stock >= 1;
 
INSERT INTO orders (user_id, status, total)
VALUES ('1c2d3e4f-0000-0000-0000-000000000001', 'pending', 150000);
 
COMMIT;

#Audit Trigger for Orders

Audit trigger for orders

CREATE TABLE audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name TEXT NOT NULL,
    action TEXT NOT NULL,
    row_id UUID NOT NULL,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
CREATE FUNCTION log_audit()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, row_id)
    VALUES (TG_TABLE_NAME, TG_OP, COALESCE(NEW.id, OLD.id));
    RETURN NEW;
END;
$$;
 
CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION log_audit();

#4. Analytics: Materialized View & Automatic Refresh

Materialized view for the daily sales report

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT
    DATE_TRUNC('day', o.created_at) AS day,
    COUNT(*) AS total_orders,
    COALESCE(SUM(o.total), 0) AS revenue
FROM orders o
WHERE o.status IN ('paid', 'shipped')
GROUP BY 1;
 
CREATE UNIQUE INDEX mv_daily_sales_day_idx ON mv_daily_sales (day);

#Production Readiness Checklist & Maintenance Routine

Activity	Purpose	Frequency
Tune `autovacuum`	Prevent table bloat (leftover old versions from MVCC)	Initial config + monitor
`VACUUM ANALYZE`	Clean bloat, refresh statistics for the planner	Scheduled / automatic
Review `pg_stat_statements`	Find new slow queries	Weekly
Reindex strategy	Repair bloated indexes	Periodically during maintenance
Monitor table bloat	Detect tables swelling without reason	Routine

Check bloat and statistics

SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

#Closing

Key takeaways:

Design (episodes 2-3): normalization, constraints, and the right data types are the foundation of everything else.
Query (episodes 4-10): from DML, joins, subqueries, JSONB, window functions, to recursive CTEs.
Reliability (episodes 11-13): ACID, transactions, locking, views, functions, and triggers.
Performance (episodes 14-15): indexing, EXPLAIN ANALYZE, and pg_stat_statements for evidence-based optimization.
Security & production (episodes 16-19): roles, RLS, FTS, pgvector, partitioning, backup, replication, and PgBouncer.

Finally, remember the three principles that tie it all together: data integrity is not negotiable, optimization must be proven by measurement, and a backup that isn't tested isn't a backup. Apply all three, and you'll build a database that doesn't just run — but runs correctly, securely, and ready to grow.

Congratulations, you've completed the Learn SQL with PostgreSQL series! Keep practicing with real projects, read the PostgreSQL documentation regularly, and make the database not just a tool, but one of the most valuable skills in your engineering career.