Samara Studio Management System

At a Glance

The Problem

Samara Studio — a photography, videography, and content-production studio — was running its entire operation on Excel spreadsheets. Staff manually tracked bookings, room occupancy, equipment loans, client balances, and daily cash closings across disconnected sheets. Double-bookings happened when two staff members booked the same room simultaneously without syncing. Equipment was overcommitted because there was no real-time visibility of active reservations. Financial drift crept in as partial payments and deposits were manually tracked. And daily closing required summing rows in a spreadsheet with no audit trail.

The Solution

A real-time, role-based web application that centralizes every studio operation. Booking conflict prevention happens at three layers simultaneously: client-side validation, server-side API guards, and PostgreSQL exclusion constraints with row-level locking — making double-booking structurally impossible. All billing logic lives in PostgreSQL RPCs (`end_booking_session`, `confirm_booking`, `cancel_booking`), so the critical financial path is transactional, lock-aware, and database-enforced regardless of which client triggers it.

Architecture

• →Browser: Next.js 16 App Router with React Server Components for the calendar, booking forms, finance, and settings modules.
• →API Layer: Next.js Route Handlers (REST-style) serving all mutations and queries, backed by the Supabase JS SDK.
• →Database: Supabase-hosted PostgreSQL with 39 migration files covering schema, triggers, RLS policies, ACID RPCs, and 13 performance indexes on hot query paths.
• →Auth: Supabase Auth with JWT sessions and cookie-based SSR. Row Level Security on every table.
• →Realtime: Supabase Realtime subscriptions keep the FullCalendar resource-timegrid live across all open tabs.
• →State: React Query for all server state (targeted invalidation by key). Zustand for ephemeral UI state (booking form steps, drawer open/close, filter panels).

Key Features

• ✓Three-layer booking conflict prevention — frontend check → Next.js API guard → PostgreSQL FOR UPDATE lock + exclusion constraint
• ✓Open-session (walk-in) billing with whole-hour ceiling rounding and 15-minute short-session guard
• ✓Production vs. Rental booking categories with camera operator fee splitting
• ✓Dynamic per-room buffer time engine visualized on FullCalendar resource-timegrid
• ✓Pre-paid bundle management — group sessions sold at package price, individually schedulable
• ✓Double-entry financial ledger — every charge, payment, and refund is an immutable ledger row
• ✓Daily cash closing with drawer reconciliation and variance flagging
• ✓Equipment quantity-aware reservation system — rejects bookings that exceed stock at API and DB level
• ✓Excel + PDF export pipeline for financial reports, booking lists, and client ledgers
• ✓E2E tests with Playwright + integration tests with Node.js native test runner

Screenshots

Code Highlight

-- From: 035_minimum_hour_billing.sql — end_booking_session()

-- 1. Fetch booking with exclusive row lock (prevents concurrent finalization)
SELECT * INTO v_booking
FROM bookings
WHERE id = p_booking_id AND deleted_at IS NULL
FOR UPDATE;

-- 2. Guard: reject sub-15-minute sessions
v_billing_origin   := COALESCE(v_booking.started_at, v_booking.start_time);
v_duration_minutes := EXTRACT(EPOCH FROM (v_end_time - v_billing_origin)) / 60.0;

IF v_duration_minutes < 15 THEN
    RAISE EXCEPTION
        'Session lasted less than 15 minutes (%.1f min). Use cancel_short_session instead.',
        v_duration_minutes;
END IF;

-- 3. Whole-hour ceiling billing, minimum 1 billable hour
v_duration_ms := EXTRACT(EPOCH FROM (v_end_time - v_billing_origin)) * 1000.0;
v_room_hours  := GREATEST(CEIL(v_duration_ms / 3600000.0), 1);
v_room_cost   := COALESCE(v_booking.room_hourly_rate, 0) * v_room_hours;

-- Equipment follows the same rule (non-camera items):
v_eq_hours := GREATEST(CEIL(v_duration_ms / 3600000.0), 1);
v_eq_total  := v_eq_hours * COALESCE(v_eq_row.unit_price, 0) * v_eq_row.quantity;

Challenges & Solutions

Tech Stack

What I Learned

• 💡Put critical logic in the database, not the app layer. Billing, conflict detection, and ledger updates that must not partially fail belong in PostgreSQL transactions — the switch to RPCs eliminated an entire class of data-consistency bugs.
• 💡React Query's query key design deserves as much thought as the schema. Broad invalidations caused cascade re-fetches that hurt perceived performance; narrowing to specific keys (`['bookings', bookingId]`) made the UI feel significantly more responsive.
• 💡Schema migrations are permanent decisions — make them conservative. "Add, don't replace" prevented data loss but left some semantic debt. More upfront ERD planning before writing a single migration would pay dividends.

Links