Live2025SaaS · Booking · SME Tools

Cortex Hub Booking

PostgreSQL tsrange Logic — Zero Double-Bookings at Any Concurrency

Booking platform for South African creative hubs. PostgreSQL tsrange overlap operator with GIST index prevents double-bookings under concurrent writes. First PayFast ITN webhook implementation in the portfolio — architectural ancestor of AdminOS.

View Live GitHub

Demo Recording

Serving

50K+ SA creative hubs and SMEs

Market

Zero double-bookings at any concurrency

Problem

WhatsApp booking = inevitable conflicts

Result

Direct architectural ancestor of AdminOS booking system

The Problem

What needed solving

SA creative hubs run on WhatsApp and paper booking registers. The inevitable result: double bookings, lost revenue, and client disputes. A SELECT check before INSERT is not enough under concurrent writes.

The Solution

How I built it

PostgreSQL tsrange type + GIST index + EXCLUDE constraint fires on INSERT if any confirmed booking overlaps the requested range — safe under any concurrency. PayFast ITN webhook verifies payment before confirming booking.

Build Journey

Cortex Hub Booking solved a deceptively simple problem: is this space free at this time? The deception is in the edge cases. Between a user clicking 'Book' and the database writing the reservation, another user could have claimed the slot. A simple SELECT check before INSERT is not enough — in a concurrent system, two users can pass the check simultaneously and both write. The solution was PostgreSQL's tsrange overlap operator combined with a unique constraint. The tsrange type represents a time interval and the && operator checks for intersection — if any confirmed booking overlaps with the requested range, the constraint fires and the second INSERT is rejected. This pattern was later deployed in VarsityOS (study room booking), AdminOS (appointment scheduling), and forms the basis of every calendar feature in the portfolio.

PostgreSQL tsrange — concurrent booking prevention

sql

-- Availability check: prevents double-booking with PostgreSQL tsrange
-- The constraint fires even under concurrent writes

-- 1. Schema: booking overlap constraint
ALTER TABLE bookings ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (
    space_id WITH =,
    tsrange(start_time, end_time, '[)') WITH &&
  ) WHERE (status = 'confirmed');

-- 2. Availability query (returns TRUE if space is free)
SELECT NOT EXISTS (
  SELECT 1 FROM bookings
  WHERE space_id = $1
    AND status = 'confirmed'
    AND tsrange(start_time, end_time, '[)') && tsrange($2::timestamptz, $3::timestamptz, '[)')
) AS is_available;

-- 3. Atomic booking creation (will raise error on conflict)
INSERT INTO bookings (space_id, user_id, start_time, end_time, status)
VALUES ($1, $2, $3, $4, 'pending')
RETURNING id;

What This Taught Me

1
Availability checking must happen at the database layer with a constraint — application-level checks are not safe under concurrency
2
PostgreSQL tsrange + GIST index handles interval arithmetic better than any application code
3
PayFast ITN webhook: the signature must be verified server-side before updating booking status
4
Every manual WhatsApp booking message is a future AdminOS feature specification

Tech Stack & Decision Rationale

Next.js 14

App Router for booking flow + PayFast webhook API routes

TypeScript

Type-safe booking state machine

Supabase

PostgreSQL tsrange + GIST index — concurrent booking prevention

PayFast

ITN webhook: payment verified before booking confirmed

Resend

Booking confirmation emails

Tailwind CSS

Clean booking UI

README.md — cortex-hub

📄 README.md

# Cortex Hub Booking
> PostgreSQL tsrange Logic — Zero Double-Bookings at Any Concurrency

## Project Context
**Category:** SaaS · Booking · SME Tools
**Status:** Live · 2025
**Author:** Nandawula Regine Kabali-Kagwa — East London, South Africa
**Company:** Mirembe Muse (Pty) Ltd · Reg: 2026-005658

## Stack
```
Next.js 14                     # App Router for booking flow + PayFast webhook API routes
TypeScript                     # Type-safe booking state machine
Supabase                       # PostgreSQL tsrange + GIST index — concurrent booking prevention
PayFast                        # ITN webhook: payment verified before booking confirmed
Resend                         # Booking confirmation emails
Tailwind CSS                   # Clean booking UI
```

## Architecture Notes
- All data mutations validated server-side via Next.js API routes
- Row-Level Security enforced at database level (Supabase)
- Mobile-first, PWA-ready, offline-tolerant where connectivity is unreliable
- PayFast integration for ZAR-native payments (no USD conversion)
- SEO-optimised: metadata, JSON-LD, canonical URLs, sitemap
- POPIA compliant — data minimisation + user consent by design

## Environment Variables
```env
NEXT_PUBLIC_SUPABASE_URL=
NEXT_PUBLIC_SUPABASE_ANON_KEY=
SUPABASE_SERVICE_ROLE_KEY=
ANTHROPIC_API_KEY=
NEXT_PUBLIC_PAYFAST_MERCHANT_ID=
NEXT_PUBLIC_PAYFAST_MERCHANT_KEY=
PAYFAST_PASSPHRASE=
RESEND_API_KEY=
```

## Links
- Live:   https://cortex-hub-booking-5e35.vercel.app
- GitHub: https://github.com/Nanda-Regine/Cortex-Hub-Booking
- Portfolio: https://creativelynanda.co.za/projects/cortex-hub

---
Built from East London, South Africa · Nine months · Zero to production

More Projects

Beta

View All Projects

Interested in similar work?

Let's discuss how I can build something like this for your business.

Let's Talk