CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  status VARCHAR(20) NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled')),
  total NUMERIC(10,2) NOT NULL CHECK (total >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO users (email, name) VALUES ('asha@example.com', 'Asha');
INSERT INTO users (email, name) VALUES ('asha@example.com', 'Asha 2');
-- ERROR: duplicate key value violates unique constraint "users_email_key"

INSERT INTO orders (user_id, status, total) VALUES (999, 'paid', 500);
-- ERROR: insert or update on table "orders" violates foreign key constraint

-- Latest 20 orders for a user, newest first
SELECT id, status, total, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

-- Revenue per status for an admin dashboard
SELECT status, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
GROUP BY status
HAVING COUNT(*) > 0;

-- Users who never ordered
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Faster than OFFSET for deep pages:
-- pass the last seen created_at and id from the previous page
SELECT id, status, total, created_at
FROM orders
WHERE user_id = 42
  AND (created_at, id) < ('2026-10-01 10:00:00+05:30', 1500)
ORDER BY created_at DESC, id DESC
LIMIT 20;

CREATE INDEX idx_orders_user_created
  ON orders (user_id, created_at DESC);

EXPLAIN ANALYZE
SELECT id, status, total
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;
-- look for "Index Scan using idx_orders_user_created"

import psycopg

with psycopg.connect("dbname=shop") as conn:
    with conn.transaction():
        conn.execute(
            "UPDATE wallets SET balance = balance - %s WHERE user_id = %s AND balance >= %s",
            (500, 42, 500))
        conn.execute(
            "INSERT INTO orders (user_id, status, total) VALUES (%s, 'paid', %s)",
            (42, 500))
# both statements commit together, or neither does
# %s placeholders keep user input out of the SQL text

Schema Review Checklist
☐ Every table has a primary key
☐ Foreign keys on every relationship
☐ UNIQUE where business rules demand it (email, slug)
☐ NOT NULL on required columns
☐ CHECK constraints for status and ranges
☐ Money stored as NUMERIC, not FLOAT
☐ Timestamps with time zone
☐ Indexes on foreign keys used in joins

Slow Page Checklist
Page: ______________
Queries per request: ____ (from logs)
Slowest query: ______________
EXPLAIN shows: seq scan / index scan
N+1 present? Yes / No → fix with JOIN or eager loading
Index added: ______________
Time before: ____ ms   after: ____ ms

-- migration: 2026_11_07_add_order_status_index.sql
-- purpose: speed up admin filter by status
BEGIN;
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders (status);
COMMIT;
-- rollback:
-- DROP INDEX IF EXISTS idx_orders_status;