Capstone: Designing a Real System

CREATE TABLE organisers (
    id    BIGSERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);
 
CREATE TABLE events (
    id           BIGSERIAL PRIMARY KEY,
    organiser_id BIGINT NOT NULL REFERENCES organisers(id) ON DELETE RESTRICT,
    title        TEXT NOT NULL,
    venue        TEXT NOT NULL,
    starts_at    TIMESTAMPTZ NOT NULL,
    status       TEXT NOT NULL DEFAULT 'draft'
                 CHECK (status IN ('draft','published','cancelled')),
    CHECK (starts_at > created_at)
);
 
CREATE TABLE ticket_types (
    id          BIGSERIAL PRIMARY KEY,
    event_id    BIGINT NOT NULL REFERENCES events(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    price       NUMERIC(10,2) NOT NULL CHECK (price >= 0),   -- NUMERIC, never REAL
    capacity    INTEGER NOT NULL CHECK (capacity > 0),
    sold_count  INTEGER NOT NULL DEFAULT 0 CHECK (sold_count >= 0),
    sales_open  TIMESTAMPTZ NOT NULL,
    sales_close TIMESTAMPTZ NOT NULL,
 
    UNIQUE (event_id, name),
    CHECK (sold_count <= capacity),          -- ← THE INVARIANT, enforced by the database
    CHECK (sales_close > sales_open)
);
 
CREATE TABLE attendees (
    id    BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);
 
CREATE TABLE orders (
    id           BIGSERIAL PRIMARY KEY,
    attendee_id  BIGINT NOT NULL REFERENCES attendees(id) ON DELETE RESTRICT,
    event_id     BIGINT NOT NULL REFERENCES events(id)    ON DELETE RESTRICT,
    status       TEXT NOT NULL DEFAULT 'pending'
                 CHECK (status IN ('pending','paid','cancelled','refunded')),
    total        NUMERIC(10,2) NOT NULL CHECK (total >= 0),
    placed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    paid_at      TIMESTAMPTZ,
    idempotency_key TEXT UNIQUE,             -- retry-safe (Module 7, Chapter 5)
    CHECK (paid_at IS NULL OR paid_at >= placed_at),
    CHECK ((status = 'paid') = (paid_at IS NOT NULL))      -- states stay consistent
);
 
CREATE TABLE order_items (
    order_id       BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    line_no        INTEGER NOT NULL,
    ticket_type_id BIGINT NOT NULL REFERENCES ticket_types(id) ON DELETE RESTRICT,
    quantity       INTEGER NOT NULL CHECK (quantity > 0),
    unit_price     NUMERIC(10,2) NOT NULL,   -- the price PAID — not a copy of the
    PRIMARY KEY (order_id, line_no)          -- current price (Module 4, Chapter 5)
);
 
CREATE TABLE tickets (
    id             BIGSERIAL PRIMARY KEY,
    order_id       BIGINT NOT NULL,
    line_no        INTEGER NOT NULL,
    ticket_type_id BIGINT NOT NULL REFERENCES ticket_types(id),
    code           TEXT NOT NULL UNIQUE,
    checked_in_at  TIMESTAMPTZ,
    FOREIGN KEY (order_id, line_no) REFERENCES order_items(order_id, line_no)
                 ON DELETE CASCADE
);

-- Q1. Live sales dashboard for an organiser. Runs constantly.
SELECT e.title,
       tt.name,
       tt.capacity,
       tt.sold_count,
       tt.capacity - tt.sold_count             AS remaining,
       ROUND(100.0 * tt.sold_count / tt.capacity, 1) AS pct_sold,
       tt.sold_count * tt.price                AS gross
FROM events e
JOIN ticket_types tt ON tt.event_id = e.id
WHERE e.organiser_id = $1 AND e.status = 'published'
ORDER BY e.starts_at, tt.price DESC;

-- Q2. An attendee's order history. Note the aggregate-then-join (Module 3, Chapter 5).
SELECT o.id, o.placed_at, o.status, o.total, e.title, e.starts_at,
       x.ticket_count
FROM orders o
JOIN events e ON e.id = o.event_id
LEFT JOIN (
    SELECT oi.order_id, SUM(oi.quantity) AS ticket_count
    FROM order_items oi GROUP BY oi.order_id
) x ON x.order_id = o.id
WHERE o.attendee_id = $1
ORDER BY o.placed_at DESC
LIMIT 20;

-- Q3. Revenue by event and month, from the price PAID.
SELECT e.title,
       date_trunc('month', o.paid_at) AS month,
       COUNT(DISTINCT o.id)           AS orders,
       SUM(oi.quantity)               AS tickets,
       SUM(oi.quantity * oi.unit_price) AS revenue    -- unit_price, NOT tt.price
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN events e       ON e.id = o.event_id
WHERE o.status = 'paid' AND o.paid_at >= $1 AND o.paid_at < $2   -- SARGABLE half-open
GROUP BY e.title, date_trunc('month', o.paid_at)
ORDER BY month DESC, revenue DESC;

-- Q4. Events at risk: published, starting soon, under 50% sold.
SELECT e.title, e.starts_at,
       SUM(tt.capacity) AS capacity, SUM(tt.sold_count) AS sold
FROM events e
JOIN ticket_types tt ON tt.event_id = e.id
WHERE e.status = 'published' AND e.starts_at BETWEEN now() AND now() + INTERVAL '14 days'
GROUP BY e.id, e.title, e.starts_at
HAVING SUM(tt.sold_count) < 0.5 * SUM(tt.capacity)      -- HAVING: filters GROUPS
ORDER BY e.starts_at;

-- Foreign keys — not automatic in PostgreSQL (Module 5, Chapter 4).
CREATE INDEX idx_events_organiser     ON events (organiser_id);
CREATE INDEX idx_ticket_types_event   ON ticket_types (event_id);
CREATE INDEX idx_orders_attendee      ON orders (attendee_id);
CREATE INDEX idx_orders_event         ON orders (event_id);
CREATE INDEX idx_order_items_type     ON order_items (ticket_type_id);
CREATE INDEX idx_tickets_type         ON tickets (ticket_type_id);
 
-- Q1: composite, equality column first (Module 5, Chapter 3).
CREATE INDEX idx_events_org_published ON events (organiser_id, starts_at)
    WHERE status = 'published';                          -- PARTIAL: skewed status
 
-- Q2: matches both the filter and the ORDER BY, so no sort step.
CREATE INDEX idx_orders_attendee_time ON orders (attendee_id, placed_at DESC);
 
-- Q3: partial + covering. Only paid orders are ever reported on.
CREATE INDEX idx_orders_paid ON orders (paid_at) INCLUDE (event_id, total)
    WHERE status = 'paid';
 
-- Q4: upcoming published events only — a small fraction of the table.
CREATE INDEX idx_events_upcoming ON events (starts_at)
    WHERE status = 'published';
 
-- Ticket scanning at the door: an exact-match lookup, must be instant.
CREATE UNIQUE INDEX idx_tickets_code ON tickets (code);

from psycopg.errors import SerializationFailure, CheckViolation
import time, random
 
PURCHASE = """
WITH claimed AS (
    UPDATE ticket_types
    SET    sold_count = sold_count + %(qty)s          -- ATOMIC read-modify-write
    WHERE  id = %(type_id)s
      AND  sold_count + %(qty)s <= capacity           -- the guard, in the statement
      AND  now() BETWEEN sales_open AND sales_close
    RETURNING id, price
)
INSERT INTO orders (attendee_id, event_id, status, total, idempotency_key)
SELECT %(attendee)s, %(event)s, 'pending', c.price * %(qty)s, %(idem)s
FROM claimed c
RETURNING id;
"""
 
def purchase(conn, attendee, event, type_id, qty, idem_key, attempts=3):
    for attempt in range(attempts):
        try:
            with conn.transaction():
                row = conn.execute(PURCHASE, {
                    "attendee": attendee, "event": event, "type_id": type_id,
                    "qty": qty, "idem": idem_key}).fetchone()
 
                if row is None:                 # the UPDATE matched no row
                    return None                 # ── sold out, or sales closed
                order_id = row[0]
 
                conn.execute("""INSERT INTO order_items
                                (order_id, line_no, ticket_type_id, quantity, unit_price)
                                SELECT %s, 1, id, %s, price
                                FROM ticket_types WHERE id = %s""",
                             (order_id, qty, type_id))     -- unit_price SNAPSHOT
                return order_id
 
        except SerializationFailure:            # Module 7, Chapter 2 — retries required
            if attempt == attempts - 1:
                raise
            time.sleep(0.01 * 2 ** attempt + random.random() * 0.01)
        except CheckViolation:                  # the CHECK caught it — genuinely sold out
            return None

-- The DRIFT CHECK for the denormalised sold_count (Module 4, Chapter 5).
-- Run nightly. Should always return zero rows.
SELECT tt.id, tt.name, tt.sold_count AS stored, COALESCE(SUM(oi.quantity), 0) AS actual
FROM ticket_types tt
LEFT JOIN order_items oi ON oi.ticket_type_id = tt.id
LEFT JOIN orders o       ON o.id = oi.order_id AND o.status IN ('paid','pending')
GROUP BY tt.id, tt.name, tt.sold_count
HAVING tt.sold_count <> COALESCE(SUM(oi.quantity), 0);