# Technical Manual

Architecture, API reference, database schema, and deep-dive technical reference for Crussell.

---

## Architecture

### Timezone Architecture (UTC-Normalised)

**How it works:** All timestamps are stored and processed in UTC. The backend uses `clock.Now()` (returns `time.Now().UTC()`) everywhere instead of `time.Now()`. The PostgreSQL connection pool is configured with `timezone = "UTC"`, so all SQL `NOW()` and `CURRENT_TIMESTAMP` calls also return UTC.

**Why this matters:** The previous architecture used `time.Now()` (which returns local time, `Europe/London`) for all Go-side timestamp generation, while PostgreSQL stored `TIMESTAMPTZ` values in UTC. This caused subtle DST bugs — a booking created at "10:00 BST" was stored as "09:00 UTC" in the DB, and closing-hours comparisons would drift by 1 hour when BST→GMT changed.

**The fix:**
1. **`clock.Now()`** — single source of truth for all Go-side current time. Returns `time.Now().UTC()`. Replaces all ~200 direct `time.Now()` calls across all handler packages.
2. **DB timezone** — `PostgreSQL` pool sets `timezone = "UTC"` via `RuntimeParams`, ensuring SQL `NOW()` matches Go's `clock.Now()`.
3. **Frontend** — `formatLocalDateTime()` (converts local `Date` to UTC `+00:00` string) and `parseWallClockDate()` (parses UTC ISO string into local Date for display) handle the client-side conversion. **JavaScript `Date.toISOString()`/`SvelteDate` replaced** — these were sending browser-local time as if it were UTC.
4. **Closing hours comparisons** — The only place `Europe/London` conversion remains is in closing-hours validation (`CreateBookingHandler`, `ReserveSlotHandler`, `AdminReserveSlotHandler`). Closing times stored in DB as `TIME WITHOUT TIME ZONE` (e.g., "20:00") need London timezone context when comparing against the end of a booking. This is done via `localEnd.In(londonLocation)` — a deliberate conversion on the end time only.

**Key insight:** The backend now treats all `time.Time` values as UTC. JSON marshalling uses RFC3339 with `+00:00` or `Z` suffix. The frontend is responsible for converting between UTC and the browser's local timezone (always `Europe/London` since the app is UK-only).

### Docker Compose Stack

| Service | Image | Port | Purpose |
|---------|-------|------|---------|
| `postgres` | postgres:17 | 5432 | Primary database, init-script.sql mounted |
| `backend` | Custom Go build | 8080 | API server (chi router) |
| `sabredav` | php:8.2-fpm | 9000 | CardDAV/CalDAV server |
| `nginx` | nginx:stable | 80, 443 | Reverse proxy, static frontend, DAV proxy |

### Request Flow

```
Client → Nginx (:80/:443)
  ├── Static files → frontend/build/
  ├── /api/* → Proxy to backend:8080
  └── /dav/* → Proxy to sabredav:9000

Backend (:8080)
  ├── chi Router → Middleware → Handlers
  ├── PostgreSQL (pgx pool)
  ├── SabreDAV (HTTP calls for CardDAV)
  └── S3/R2 (RustFS dev, Cloudflare R2 prod)
```

### External Services

| Service | Status | Purpose |
|---------|--------|---------|
| SabreDAV (CardDAV/CalDAV) | Active | Contact sync (profile photos), calendar events |
| S3/R2 | Active (dev) | Portfolio images (AVIF), profile pictures (WebP) |
| Square | **Active** | Payment processing — in-person Terminal + online Web Payments SDK. Dev mock (`//go:build dev`) simulates async checkout; prod stub (`//go:build !dev`) connects to live API. |
| SMTP | Not implemented | Email/SMS notifications — backend not wired |

---

## Backend Structure

### Handler Packages

| Package | File(s) | Purpose |
|---------|---------|---------|
| `handlers/auth` | local.go, social.go | Registration (with referral code validation), login, refresh, email verification |
| `handlers/bookings` | bookings.go, reserve.go, manage.go, admin_reserve.go, cancel_reservation.go, admin_cancel_reservation.go | Booking CRUD, reservations with **self-blocking prevention** (`excludeUserID` parameter on `CheckTimeBlockerOverlap` + pre-overlap DELETE with IP hash anon cleanup), admin management, edit requests, discounts, closing hours validation, active booking limits, GetBookingsByCreatedRange, created_by_name resolution, **explicit reservation cancellation** (`DELETE /api/bookings/reserve` for users, `DELETE /api/admin/bookings/reserve` for admin walk-in/call-in) |
| `handlers/payments` | handlers.go, service.go, validators.go, giftcards.go, till.go, refunds.go, refund_policy.go | Square payments: terminal, online, refunds, tips, saved cards, gift cards (CRUD, topup, transfer, redeem, buy, expired balances, till sales). Refund calculation with notice-period tiers and deposit protection |
| `handlers/webhooks` | square.go | Square webhook handler for payment status updates. **Fail-closed signature check** — rejects requests with 403 when `SQUARE_WEBHOOK_SIGNATURE_KEY` is set but header is missing. Dev mode: skips verification when env var is empty. Still uses hex-encoding stub (`verifySquareSignature`) — production requires HMAC-SHA256 with base64 output, `x-square-hmacsha256-signature` header. See `TODO(PROD)` in source. |
| `handlers/admin` | users.go, analytics.go, custom_services.go, discount_campaigns.go, settings.go | Admin user management, custom services CRUD (list/create/get/update/promote/delete), discount campaigns, analytics (stub), business settings (GET/PUT with VAT, gift card config) |
| `handlers/today` | today.go | Current/next appointment, today's grid, pending approvals, `DoneForDay` state with daily/weekly summary (`DailySummary` with `total_bookings`, `customers_served`, `summary_scope`), auto-status transitions, closed-day aggregation via `findWeekSummaryRange` + `computeAggregateSummary`. Exceptional hours lookup uses `exceptional_group_applications.week_start` (0=Monday). |
| `handlers/user` | profile.go, account.go, guest.go, loyalty.go, customer_relationship.go, gdpr_export.go | User profile, guest creation (with CheckEmailHandler for registered-email detection), loyalty, contact info, GDPR export (async with 12h cache) |
| `handlers/services` | services.go | Service catalog, eligibility filtering, patch_test_duration_hours auto-creates patch test records |
| `handlers/scheduling` | default-hours.go, exceptional-hours.go, time-blockers.go | Working hours, exceptional groups, time blockers with **excludeUserID filtering** (user's own `RESERVATION` entries excluded from blocker results when authenticated), gift card expiry cleanup (24-month rolling), idle account cleanup (2yr/5yr) |
| `handlers/portfolio` | images.go | Image CRUD, cursor-paginated listing with fuzzy tag search & exact category filters, relevance-sorted tag results |
| `handlers/notifications` | notifications.go | Admin notifications (GET, acknowledge) |

### Middleware (`mw/`)

| Middleware | Purpose |
|------------|---------|
| `RequireAuth` | Validates JWT, extracts `jti` claim, adds user_id and user_role to context; rejects revoked JTIs |
| `OptionalAuth` | Extracts user info if token present, passes through otherwise |
| `RequireAdmin` | Allows admin role only |
| `RequireVerified` | Allows verified_email or admin |
| `RequireRole(roles...)` | Generic role check |
| `RateLimit(limit, window)` | IP-based rate limiting (supports CF-Connecting-IP header). Dev build tag (`dev`): no-op pass-through. |
| `ProgressiveRateLimit` | Per-IP dual-window rate limiter for bot-spam prevention. Burst: 30 req/5s, Sustained: 120 req/60s. Progressive delays (500ms–10s). Applied to login/register. Skips in dev build. |
| `JsonContentType` | Sets `Content-Type: application/json` on all API responses. Individual handlers that need to override (e.g. binary/image responses) set their own Content-Type header after this middleware runs. Applied globally in `main.go:182`. This replaced ~80+ individual `w.Header().Set("Content-Type", "application/json")` calls across all handlers. |
| `RespondJSON(w, status, data)` | Helper function (in `response.go`, not middleware) for consistent JSON responses. Always sets `Content-Type: application/json`. |
| `RespondError(w, status, message)` | Helper that wraps `RespondJSON` with `{"error": message}`. Replaces `http.Error()` in all new code for consistent JSON error format. |

### Database Layer (`db/`)

- **Driver**: pgx/v5 (PostgreSQL)
- **Connection**: `postgres://USER:PASSWORD@HOST:5432/DB`
- **Connection pooling**: Built-in via pgxpool
- **Timezone**: All connections explicitly set `timezone = "UTC"` via `pgxpool.ParseConfig` + `RuntimeParams["timezone"] = "UTC"` (both `db.go` and `db_dev.go`). This ensures PostgreSQL's `NOW()`, `CURRENT_TIMESTAMP`, and all `TIMESTAMPTZ` operations are in UTC, matching `clock.Now()` on the Go side.
- **Build tags**: `db_dev.go` (dev, localhost) vs `db.go` (prod, env var)
- **Test MaxConns**: 16 per pool (down from 60 — reduced during test migration to prevent connection exhaustion)
- **PoolProxy**: `db.Conn` is a `*PoolProxy` that checks `context.Context` for an active transaction via `TxFromContext()`. All `Exec`/`Query`/`QueryRow` calls route through the transaction if present, otherwise delegate to the pool.

### DAV Service Isolation (`internal/dav/`)

The DAV service has a completely separate database connection from the rest of the application:

- **Separate pool**: `internal/dav/service_prod.go` and `service_dev.go` create their own `*pgxpool.Pool` in `init()`, stored in `dav.Service`
- **Not wrapped in PoolProxy**: DAV queries bypass the transaction routing that PoolProxy provides. DAV operations always hit the real database, not test transactions
- **Usage**: `dav.Service.CreateContact()` (registration), `dav.Service.DeleteContact()` (account deletion), `dav.Service.CreateEvent()` (booking confirmation) — all best-effort side effects after main DB transactions commit
- **Tests**: `dav.Service` is replaced with `&dav.BaseService{}` (nil db) — DAV operations are no-ops during tests
- **CardDAV HTTP path**: `updateCardDAV()` in `handlers/user/profile.go` makes HTTP PUT directly to the SabreDAV PHP server (not using `dav.Service` at all)

### Internal Packages

| Package | Purpose |
|---------|---------|
| `clock` | `clock.Now()` returns `time.Now().UTC()`. Used everywhere for wall-clock consistency — all scheduling comparisons, timestamp recording, and duration calculations use this function so they're consistent with the database (TIMESTAMPTZ in UTC). Replaces all direct `time.Now()` calls in handlers. |
| `internal/validators` | ID validation (12-char hex format), cursor parsing |
| `internal/dav` | SabreDAV CardDAV integration (build tags: `service_dev.go` / `service_prod.go`) |
| `internal/s3` | S3/R2 storage abstraction (build tags: dev vs prod) |
| `internal/square` | Square client interface + dev mock + prod stub (build tags: `dev` vs `!dev`) |
| `internal/zxcvbnjs` | Bundles @zxcvbn-ts/core via goja (ExecJS-style). Exact parity with frontend password scoring. Go binary embeds the 1.7MB IIFE JS bundle. |

---

## Frontend Structure

### SvelteKit Routes

| Route | File | Purpose |
|-------|------|---------|
| `/` | +page.svelte | Home — welcome, services overview, portfolio carousel |
| `/book` | book/+page.svelte | Customer booking wizard (wraps BookingFlow) |
| `/login` | login/+page.svelte | Login/Register with form validation |
| `/account` | account/+page.svelte | User profile, bookings, loyalty stamps, gift cards, GDPR export |
| `/admin` | admin/+page.svelte | Admin dashboard — users, bookings, services, scheduling, gift cards, discount campaigns |
| `/today` | today/+page.svelte | Staff daily view — appointments, approvals, calendar grid |
| `/portfolio` | portfolio/+page.svelte | Image gallery with tag/category filtering |
| `/prices` | prices/+page.svelte | Service price list |
| `/schedule` | schedule/+page.svelte | User's upcoming appointments with .ics export, payment buttons |
| `/admin/schedule` | admin/schedule/+page.svelte | Admin weekly calendar view — Google Calendar-style week grid |
| `/contact` | contact/+page.svelte | Dynamic contact info from first admin user + MapLibre GL map |
| `/demo` | demo/+page.svelte | Demo mode |
| `/booking-confirmed/[id]` | booking-confirmed/[id]/+page.svelte | Booking confirmation with payment summary, deposit info |
| `/pay-tip/[id]` | pay-tip/[id]/+page.svelte | Tip payment page — percentage-based or custom |
| `/gdpr` | gdpr/+page.svelte | GDPR data export — skeleton loading, polling, styled reports, PDF export, JSON download |
| `/admin/notifications` | admin/notifications/+page.svelte | Admin notifications with priority sorting, acknowledge flow |
| `/api/[...path]` | api/[...path]/+server.ts | API catch-all proxy (dev) |

### Component Hierarchy

```
src/lib/components/
├── admin/
│   ├── ApprovalModal.svelte        # Booking approval/decline
│   ├── BookingCreateModal.svelte   # Admin booking creation (4-step, uses shared timeSlots utils)
│   ├── BookingModal.svelte         # View booking details (shows created_by_name)
│   ├── BookingsCard.svelte         # Bookings list
│   ├── CustomServicesManagement.svelte # Custom services CRUD (list/create/edit/promote/delete)
│   ├── CallInBooking.svelte        # Call-in booking flow
│   ├── EditBookingModal.svelte     # Edit booking services
│   ├── HolidayHours.svelte         # Exceptional schedule management
│   ├── ImageUpload.svelte          # Portfolio image upload
│   ├── PatchTestModal.svelte       # Record patch test
│   ├── ServicesManagement.svelte   # Service CRUD (patch_test_duration_hours)
│   ├── DiscountsManagement.svelte  # Discount campaign management
│   ├── UserModal.svelte            # User details + relationship data
│   ├── UsersCard.svelte            # Users list
│   ├── WalkInBooking.svelte        # Walk-in booking flow
│   ├── WalkInCreateModal.svelte    # Walk-in 3-step wizard
│   ├── RescheduleModal.svelte      # Admin reschedule — available slot lookup, conflict detection
│   └── TimeBlockers.svelte         # Time blocker CRUD — one-off + recurring, overlap detection
├── booking/
│   ├── BookingActions.svelte       # Next/Back buttons
│   ├── BookingFlow.svelte          # 5-step booking wizard (welcome, auto-select, shared timeSlots)
│   ├── BookingSummary.svelte       # Booking review summary
│   ├── DatePicker.svelte           # Calendar date selection
│   ├── ServiceCard.svelte          # Individual service display
│   ├── ServiceSelector.svelte      # Service selection UI
│   ├── StepIndicator.svelte        # Progress steps
│   ├── TimeSlotPicker.svelte       # Time slot grid
│   ├── TimeSlotList.svelte         # Scrollable time slot list (admin booking)
│   └── SelectedTimeSummary.svelte  # Selected date/time display
├── payments/
│   ├── PaymentModal.svelte         # Admin payment — multi-method: Card (Terminal), Cash (change), Gift Card (12-digit ID), account balance, service price overrides, tip presets
│   └── UserPaymentModal.svelte     # User payment — deposit, partial, full, balance (no tip)
├── today/
│   ├── CurrentAppointment.svelte   # Active appointment display
│   ├── PendingApprovals.svelte     # Pending bookings + edit requests (dedup refresh)
│   ├── TodayCalendar.svelte        # Interactive day view with time blockers
│   └── TodayStats.svelte           # Today's appointment stats summary
├── layout/
│   ├── NavBar.svelte               # Navigation bar (responsive, notification badge)
│   └── PortfolioCarousel.svelte    # Home page gallery
├── ui/
│   ├── CharCounter.svelte          # Grapheme counter for notes (Intl.Segmenter)
│   └── phone-input/
│       ├── PhoneInput.svelte       # UK phone input with inline validation, auto-formatting, digit filtering
│       └── index.ts                # Re-export
└── account/
    ├── EditRequestModal.svelte     # User edit request modal
    └── UserBookingModal.svelte     # User booking details
```

### State Management

- **Svelte 5 runes**: `$state`, `$bindable`, `$effect`, `$derived`, `SvelteDate`
- **Auth store** (`lib/stores/auth.svelte.ts`): JWT tokens in localStorage, auto-refresh, role checks. 1h access token, 90-day refresh token rotation with consumption-based invalidation.
- **Token refresh**: Every hour, automatically refreshes access token. Refresh token rotates each use (old token invalidated via DELETE-based consumption).
- **Role-based UI**: `hasRole()`, `isAdmin()`, `isVerified()` control visible elements

### Shared Utilities

- **`lib/utils/timeSlots.ts`**: Common time slot logic
  - `buildLunchProtection()` — lunch break proximity warnings
  - `generateAvailableTimeSlots()` — individual 15-min slot times
  - `generateGroupedTimeSlots()` — grouped by availability
  - `formatTime()` — minutes-since-midnight to HH:MM
  - `calculateEndTime()` — start + duration
  - `getDayWithOrdinal()` — "January 15th"
  - `formatLocalDateTime(date)` — converts local Date to UTC `+00:00` string. Replaces `.toISOString()` everywhere for sending times to the backend. The suffix is `+00:00` (not `Z`) because Go's `time.Time` JSON unmarshalling prefers the explicit offset form when constructing wall-clock timestamps that shouldn't be shifted by the backend's timezone.
  - `parseWallClockDate(iso)` — parses a UTC ISO string from the backend and returns a Date whose `getHours()/getMinutes()` reflect the local wall-clock time. Replaces `new SvelteDate(iso)` everywhere for displaying booking times.
  - `formatWallClockTime(iso)` — formats a UTC ISO string as HH:MM wall-clock time
  - `formatWallClockDate(iso)` — formats a UTC ISO string as a readable wall-clock date
  - `normalizeTime(time)` — improved padding for edge-case time strings
  - Types: `DayHours`, `DayAvailability`
  - All debug `console.log()` statements removed from slot generation functions

- **`lib/utils/format.ts`**: Formatting utilities
  - `formatDuration(minutes)` — "1h 30m"
  - `formatDateTime(date)` — "Weekday, Month Day at HH:MM AM/PM"
  - `formatDate(date)` — "Weekday, Month Day"
  - `formatTime(date)` — "HH:MM AM/PM"
  - `calculateAge(dateOfBirth)` — years from DOB
  - `formatDateISO(date)` — YYYY-MM-DD for API calls

- **`lib/utils/phone.ts`**: UK phone formatting
  - `formatPhoneDisplay()` — formats as they type (e.g., 07700 900 000)
  - `formatPhoneInput()` — raw digits
  - Max 11 digits for both landline and mobile

- **`CharCounter`**: `Intl.Segmenter` for grapheme counting. Hidden below 750K, color-coded above: green (<800K), yellow (800K-950K), red (>950K)

### Security Headers

Added in the June 2026 security pass:

### Security Headers

| Header | Value | Location |
|--------|-------|----------|
| `Content-Security-Policy` | `default-src 'none'; frame-ancestors 'none'` | Global middleware (`main.go:139`) |
| `Access-Control-Allow-Origin` | `*` (dev only) | Global middleware (`main.go:141`) |
| `Access-Control-Allow-Methods` | `GET, POST, PUT, PATCH, DELETE, OPTIONS` | Global middleware |
| `Access-Control-Allow-Headers` | `Authorization, Content-Type, Idempotency-Key` | Global middleware |

### Other Security Fixes

| Fix | File | Description |
|-----|------|-------------|
| Removed verification code logging | `handlers/auth/local.go:545` | Deleted `log.Printf("DEBUG: Verification code for %s: %s ...")` — was leaking verification codes to stdout |
| Webhook signature fail-closed | `handlers/webhooks/square.go:59-69` | Changed from "skip verification if header missing" to "reject 403 if key set but header missing" |
| S3 delete error checking | `handlers/portfolio/images.go:975` | Changed `s3.Client.Delete(...)` (ignored return) → `if err := s3.Client.Delete(...); err != nil { log.Printf(...) }` |

CORS uses `*` in local dev. In production behind Cloudflare, nginx handles CORS. No CSP violations expected — the SvelteKit SPA doesn't load external scripts or fonts.

---

## API Reference

### Public Endpoints

| Method | Path | Auth | Rate Limit | Description |
|--------|------|------|------------|-------------|
| GET | `/api/services` | Optional | 120/min | List active services (eligibility for authenticated) |
| GET | `/api/services/eligible-for/{user_id}` | Admin | 120/min | Services filtered by user's age/patch test |
| POST | `/api/register` | None | 10/min | Create user account (optional `referralCode` field) |
| POST | `/api/login` | None | ProgressiveRateLimit + RateLimit(10, 1min) | Authenticate, receive JWT + refreshToken. Account lockout after 5 failures (15min→30min→1h→2h). |
| POST | `/api/verify/generate` | None | — | Generate email verification or password reset code |
| POST | `/api/verify/check` | None | — | Verify code |
| GET | `/api/health` | None | — | Health check (DB, S3, Square, frontend status) |
| GET | `/api/contact` | None | — | Business contact info (first admin user) |
| POST | `/api/users/guest` | None | 10/min | Create disposable guest account |
| GET | `/api/check-email` | None | 60/min | Proactive registered-email detection (params: email, firstName, lastName, phone). Returns `suggestion`: `"login"` (full match), `"check"` (partial), or `null` (not found/guest) |
| GET | `/api/scheduling/default-hours` | None | 120/min | Get weekly default hours |
| GET | `/api/scheduling/exceptional-groups` | None | 120/min | List holiday/special hour groups |
| GET | `/api/scheduling/working-hours` | None | 120/min | Merged default + exceptional hours |
| GET | `/api/scheduling/available-hours` | None | 120/min | Available slots (cleanup removed — runs on cron) |
| POST | `/api/bookings/reserve` | Optional | 30/min | Reserve slot (user=1h, anon=10min, 50-cap) |
| POST | `/api/bookings` | Optional | 30/min | Create booking (with `Idempotency-Key` header) |
| GET | `/api/portfolio/images` | None | — | List images (cursor pagination, tag & category filters). Query params: `limit` (1-100, default 20), `cursor` (from `next_cursor` in response), `tag` (fuzzy substring), `tags` (comma-separated, fuzzy), `filter[category]=value` (exact). Tag results sorted by relevance then date; filter-only sorted by date. Returns `{ images: [...], next_cursor: "..." }`. |
| GET | `/api/portfolio/tags` | None | — | List all tags |
| GET | `/api/portfolio/filters` | None | 60/min | Get filter categories with counts |
| GET | `/api/portfolio/images/{id}` | None | 120/min | Get single image by UUID or timestamp |

### Authenticated User Endpoints

| Method | Path | Description |
|--------|------|-------------|
| POST | `/api/logout` | Revoke current JWT token |
| POST | `/api/refresh-token` | Revoke old JTI, issue new JWT + refreshToken. Role-change detection (rejects if user's role changed since login). |
| GET | `/api/user/profile` | Get current user profile |
| PUT | `/api/user/profile` | Update profile |
| POST | `/api/user/profile-picture` | Upload profile picture |
| PUT | `/api/user/change-password` | Change password |
| GET | `/api/user/notification-preferences` | Get notification preferences |
| PUT | `/api/user/notification-preferences` | Update notification preferences |
| DELETE | `/api/user/account` | Delete account (GDPR anonymization + external scrubbing) |
| GET | `/api/user/gdpr-export` | Async GDPR data export (12h cache, background generation) |
| GET | `/api/user/loyalty` | Get loyalty stamp count |
| GET | `/api/bookings` | List user's bookings |
| GET | `/api/bookings/{id}` | Get specific booking |
| GET | `/api/bookings/{id}/calendar` | Download ICS calendar file |
| PUT | `/api/bookings/{id}` | Update booking (user edit) |
| DELETE | `/api/bookings/{id}` | Cancel booking (with forgiveness option) |
| POST | `/api/bookings/{id}/edit-request` | Request booking reschedule |
| DELETE | `/api/bookings/{id}/edit-request` | Cancel edit request |
| GET | `/api/bookings/{id}/edit-request` | View own pending edit request (enriched) |
| GET | `/api/bookings/edit-requests` | List all own pending edit requests (enriched) |
| DELETE | `/api/bookings/reserve` | Release user's own active reservation. Targets `RESERVATION:user:%` only (does not touch admin or anon reservations — partitioned by WHERE clause). Idempotent: 200 with no-op if no reservation exists. |
| POST | `/api/bookings/{id}/payment` | Create online payment (deposit, full, partial, balance) |
| POST | `/api/bookings/{id}/tip` | Add tip to completed booking |
| GET | `/api/bookings/{id}/payment-summary` | Get payment summary for booking |
| GET | `/api/user/payment-methods` | Get saved cards |
| DELETE | `/api/user/payment-methods/{id}` | Soft-delete a saved card |
| POST | `/api/user/payment-methods` | Add a saved card |

### Admin Endpoints

| Method | Path | Description |
|--------|------|-------------|
| GET | `/api/admin/services` | List all services |
| POST | `/api/admin/services` | Create service |
| DELETE | `/api/admin/services/{id}` | Delete service |
| PUT | `/api/admin/services/{id}/toggle` | Toggle active status |
| GET | `/api/admin/bookings` | List all bookings. Supports `per_page` up to **500** (values over 500 default to 10), `page`, `start_date`, `end_date`, `status` filters. Pagination formula: `totalPages = ceil(total / perPage)`. If `per_page` is omitted, defaults to 10. |
| POST | `/api/admin/bookings` | Create booking for user (with `Idempotency-Key` header) |
| GET | `/api/admin/bookings/search` | Search bookings |
| GET | `/api/admin/bookings/by-created-range` | Get bookings by created_at range (query: `start`, `end` ISO 8601) |
| GET | `/api/admin/bookings/user/{user_id}` | User's bookings |
| GET | `/api/admin/bookings/{id}` | Get booking details |
| PUT | `/api/admin/bookings/{id}` | Update booking services, overrides, notes |
| GET | `/api/admin/bookings/{id}/overlapping` | Get overlapping bookings |
| PUT | `/api/admin/bookings/{id}/progress` | Progress booking status |
| POST | `/api/admin/bookings/{id}/confirm` | Confirm booking |
| POST | `/api/admin/bookings/{id}/cancel` | Cancel booking |
| POST | `/api/admin/bookings/reserve` | Reserve slot (walkin=5min, callin=1h) |
| DELETE | `/api/admin/bookings/reserve` | Release admin's walk-in/call-in reservation. Targets `RESERVATION:admin:%` only (does not touch user or anon reservations — partitioned by WHERE clause). Idempotent: 200 with no-op if no reservation exists. |
| GET | `/api/admin/bookings/{id}/edit-requests` | List edit requests (paginated, with total) |
| GET | `/api/admin/bookings/edit-requests` | List ALL edit requests (enriched) |
| GET | `/api/admin/bookings/{id}/edit-request` | View pending edit request for specific booking (enriched) |
| POST | `/api/admin/bookings/{id}/edit-requests/{request_id}/approve` | Approve edit request |
| POST | `/api/admin/bookings/{id}/edit-requests/{request_id}/deny` | Deny edit request |
| GET | `/api/admin/users` | List users |
| GET | `/api/admin/users/{id}` | Get user details |
| GET | `/api/admin/users/{id}/relationship` | Customer relationship data (spend, visits, top services) |
| GET | `/api/admin/users/{id}/patch-tests/eligible` | Eligible patch test services |
| POST | `/api/admin/users/{id}/patch-tests` | Record patch test |
| GET | `/api/admin/today/current-next` | Current and next appointment. Returns `done_for_day`, `closing_time`, `summary` (DailySummary: `customers_served`, `total_bookings`, `summary_scope`). When today is closed: `summary_scope="week"`, range covers `[weekStart, todayEnd)`. When tomorrow is closed: additional `week_summary` with `scope="week"`. |
| GET | `/api/admin/today/appointments` | Today's appointments (all statuses) |
| GET | `/api/admin/today/pending-approvals` | Pending approval queue |
| GET | `/api/admin/notifications` | List notifications |
| GET | `/api/admin/notifications/unread-count` | Unread count for bell icon |
| POST | `/api/admin/notifications/{id}/acknowledge` | Acknowledge notification |
| GET | `/api/admin/time-blockers` | List time blockers |
| POST | `/api/admin/time-blockers` | Create time blocker |
| DELETE | `/api/admin/time-blockers/{id}` | Delete time blocker |
| GET | `/api/admin/discount-campaigns` | List discount campaigns |
| POST | `/api/admin/discount-campaigns` | Create discount campaign |
| PUT | `/api/admin/discount-campaigns/{id}` | Update discount campaign |
| DELETE | `/api/admin/discount-campaigns/{id}` | Cancel discount campaign |
| GET | `/api/admin/discount-campaigns/{id}/stats` | Campaign stats |
| PUT | `/api/scheduling/default-hours` | Update weekly hours |
| POST | `/api/scheduling/exceptional-groups` | Create exception group |
| DELETE | `/api/scheduling/exceptional-groups` | Delete exception group |
| PUT | `/api/scheduling/exceptional-applications` | Apply exceptions to dates |
| POST | `/api/portfolio/images` | Upload portfolio image |
| DELETE | `/api/portfolio/images/{id}` | Delete portfolio image |
| POST | `/api/admin/bookings/{id}/payment` | Create Terminal payment |
| GET | `/api/admin/payments/{checkout_id}/status` | Poll checkout status |
| POST | `/api/admin/payments/{payment_id}/refund` | Refund payment |
| GET | `/api/webhooks/square` | Square webhook endpoint |
| POST | `/api/admin/gift-cards/expired-balances` | List expired/dormant balances |
| POST | `/api/admin/gift-cards/expired-balances/claim` | Claim an expired balance (409 if already claimed) |
| GET | `/api/admin/settings` | Get business settings (name, address, VAT, gift card config) |
| PUT | `/api/admin/settings` | Update business settings (partial update, validates voucher_type and expiry months) |
| GET | `/api/admin/gift-cards` | List gift cards (paginated, with search) |
| POST | `/api/admin/gift-cards` | Create gift card (with payment method validation) |
| PUT | `/api/admin/gift-cards/{id}/topup` | Top up gift card |
| POST | `/api/admin/gift-cards/{id}/transfer` | Transfer gift card to another user |
| POST | `/api/admin/gift-cards/{id}/redeem` | Redeem gift card to account balance |
| POST | `/api/gift-cards/buy` | User buys gift card online (with idempotency_key) |
| GET | `/api/admin/custom-services` | List custom services (search `q`, popular, pagination `page`/`per_page`) |
| POST | `/api/admin/custom-services` | Create custom service (name, price, duration, minimum age, notes) |
| GET | `/api/admin/custom-services/{id}` | Get single custom service |
| PUT | `/api/admin/custom-services/{id}` | Update custom service fields |
| POST | `/api/admin/custom-services/{id}/promote` | Promote custom service to regular service (migrates booking references) |
| DELETE | `/api/admin/custom-services/{id}` | Delete custom service (409 if usage_count > 0) |

---

## Database Schema

### Enums (13 total)

| Enum | Values |
|------|--------|
| `account_role` | `unverified_email`, `verified_email`, `admin`, `guest`, `affiliate` |
| `account_type` | `email`, `google`, `microsoft`, `facebook`, `guest` |
| `booking_status` | `pending`, `confirmed`, `in_progress`, `completed`, `client_cancelled`, `we_cancelled`, `no_show`, `pending_release`, `deposit_lapsed` |
| `verification_purpose` | `email_verify`, `password_reset` |
| `payment_type` | `deposit`, `full`, `tip`, `balance`, `partial` |
| `payment_method` | `online_square`, `in_person_card`, `cash`, `giftcard`, `discount`, `on_the_house` |
| `payment_status` | `pending`, `completed`, `failed`, `refunded` |
| `admin_notification_reason` | `pending_booking`, `cancelled_booking`, `rescheduled_booking`, `1_week_no_pay`, `1_month_no_pay`, `affiliate_claim`, `late_cancellation`, `no_deposit`, `deposit_paid`, `edit_request`, `new_booking`, `edit_requested`, `deposit_not_paid_by_deadline` |
| `campaign_type` | `time_based`, `milestone` |
| `milestone_type` | `per_user_booking_count`, `global_booking_count`, `anniversary` |
| `milestone_unit` | `bookings`, `months`, `years` |
| `discount_campaign_scope` | `all_bookings`, `first_booking_only`, `new_customers_only` |
| `discount_campaign_status` | `draft`, `active`, `completed`, `cancelled` |
| `till_item_type` | `gift_card`, `merchandise`, `service` |

### Tables (37 total)

| Table | Purpose |
|-------|---------|
| `users` | User accounts (partial unique email: WHERE account_role != 'guest') |
| `user_social_logins` | Social auth provider links |
| `verification_codes` | Email verification and password reset codes |
| `patch_tests` | Patch test definitions (notice_duration_hours, expiry_months, service_ids) |
| `user_patch_tests` | User patch test completion records (tested_at, notes) |
| `services` | Service offerings |
| `custom_services` | One-off / special-request services (usage_count, created_by) |
| `booking_custom_services` | Custom services per booking (override_price, override_duration_minutes) |
| `bookings` | Appointment records (idempotency_key, deposit_required, deposit_paid, deposit_amount, deposit_deadline). Statuses include pending_release (deposit deadline passed, slot vulnerable) and deposit_lapsed (slot evicted by another booking) |
| `booking_services` | Services per booking (override_price, override_duration_minutes) |
| `booking_edit_requests` | Pending customer edit requests |
| `user_referrals` | Referral tracking |
| `working_hours` | Default weekly schedule |
| `exceptional_working_hours_groups` | Holiday/special hour groups |
| `exceptional_working_hours` | Hours for exception groups |
| `exceptional_group_applications` | Apply exceptions to date ranges |
| `time_blockers` | Admin time blocks + slot reservations (description LIKE 'RESERVATION:%') |
| `forgiven_no_shows` | Tracks no-shows forgiven by admin (booking_id, forgiven_by FK to users, created_at). Used by `CountUnforgivenNoShows()` to exclude forgiven records |
| `payments` | Payment transactions (VAT fields, invoice_number sequence, fees column for Square deductions, saved_card_id, gift_card_id) |
| `user_saved_cards` | Saved card details (square_card_id, brand, last4, fingerprint, soft delete with retained_until) |
| `refunds` | Refund records linked to bookings (amount, reason, square_refund_id, created_by FK to users, ON DELETE SET NULL) |
| `financial_aggregates` | Monthly aggregated financial statistics (no PII) — populated when granular records expire |
| `square_deposits` | Square deposit batch tracking for bank reconciliation (batch_id, total_amount, deposited_at) |
| `affiliate_payouts` | Affiliate commission tracking |
| `loyalty_redemptions` | Loyalty stamp redemptions (pending → applied, 6-month expiry, FIFO) |
| `discount_campaigns` | Discount campaigns: time-based and milestone (draft → active → completed lifecycle) |
| `booking_discounts` | Applied discounts per booking (multiple rows per booking when stacking) |
| `business_settings` | Business configuration (VAT, currency, contact, gift_card_expiry_months, voucher_type) |
| `admin_notifications` | Admin notification queue |
| `user_notification_preferences` | User notification preferences (email/sms/push) |
| `images` | Portfolio gallery images with tags |
| `tags` | Image tag autocomplete |
| `gift_card_transactions` | Audit log for all gift card actions (purchase, topup, redeem_to_balance, expire) |
| `gift_card_expired_balances` | Dormant balances from expired gift cards or deleted accounts (account_id + amount only, no PII) |
| `gift_cards` | Gift card records (amount_remaining, is_inventory, expiry_date, last_used_at, redeemed_by) |
| `user_giftcard_balances` | Pooled account balance from redeemed gift cards (one per user) |
| `till_sales` | Till sales (gift_card, merchandise, service items with VAT, payment method, idempotency_key) |

### Key Functions

| Function | Purpose |
|----------|---------|
| `generate_short_id(table_name)` | Generate 12-char hex IDs with collision detection |
| `generate_user_id()` | Wrapper for users table |
| `generate_service_id()` | Wrapper for services table |
| `generate_booking_id()` | Wrapper for bookings table |
| `generate_payment_id()` | Wrapper for payments table |
| `generate_verification_code()` | 12-char verification code |
| `generate_referral_code()` | 12-char referral code with collision detection |
| `anonymize_user(target_id)` | GDPR right-to-be-erased for registered users — child table PII scrubbing |
| `delete_guest_user(target_id)` | Full removal of guest account |
| `export_all_user_data(target_user_id)` | GDPR Article 15 SAR — 21-section JSON export (excludes verification_codes; includes admin_audit_log, gift_cards, name_history) |
| `get_vat_return_data(start, end)` | VAT return summary for MTD. **Updated:** Now includes `till_sales` via `UNION ALL` — till sales (gift cards, merchandise, services) are counted alongside booking payments for VAT reporting. |
| `export_sales_transactions(start, end, include_vat)` | Tax-compatible transaction export. **Updated:** Uses dynamic `vat_rate` from the `payments` table (or `business_settings.default_vat_rate`) instead of hardcoded 1.20. |
| `get_monthly_business_summary(start, end)` | Monthly revenue breakdown |
| `get_sales_totals(start, end)` | Quick sales snapshot |
| `enable_vat_registration(...)` | Enable VAT registration |
| `apply_vat_to_payment(payment_id, vat_rate)` | Apply VAT to a payment |
| `calculate_vat(gross_amount, vat_rate)` | Calculate net + VAT from gross |
| `get_receipt_data(payment_id)` | Receipt generation data |
| `apply_vat_to_till_sale(sale_id, vat_rate)` | Apply VAT to a till sale (SPV treatment) |
| `CleanupExpiredFinancialRecords(ctx)` | Go — aggregates expired payments/refunds into monthly stats |
| `CleanupExpiredGiftCards(ctx)` | Go — expires gift cards unused for 24+ months |
| `CleanupIdleAccounts(ctx)` | Go — anonymizes accounts idle 2+ years (no balance) or 5+ years (with balance) |

### Partial Indexes

| Index | Table | Condition |
|-------|-------|-----------|
| `idx_users_email_registered` | users | `WHERE account_role != 'guest'` |
| `idx_verification_codes_user_purpose` | verification_codes | `WHERE used_at IS NULL` |
| `idx_verification_codes_expires` | verification_codes | `WHERE used_at IS NULL` |
| `idx_time_blockers_cron` | time_blockers | `WHERE cron_expression IS NOT NULL` |
| `idx_discount_campaigns_dates` | discount_campaigns | `WHERE start_date IS NOT NULL` |
| `idx_gift_cards_last_used_at` | gift_cards | `WHERE last_used_at IS NOT NULL AND redeemed_by IS NULL AND amount_remaining > 0` |
| `idx_gift_card_expired_balances_unclaimed` | gift_card_expired_balances | `WHERE claimed_at IS NULL` |
| `idx_gift_card_transactions_created_at` | gift_card_transactions | — |

### Default Working Hours

| Weekday | Start | End | Open |
|---------|-------|-----|------|
| Monday | 09:00 | 17:00 | Yes |
| Tuesday | 09:00 | 17:00 | Yes |
| Wednesday | 12:00 | 20:00 | Yes |
| Thursday | 09:00 | 17:00 | Yes |
| Friday | 09:00 | 17:00 | Yes |
| Saturday | — | — | No |
| Sunday | — | — | No |

---

## Key Systems

### Slot Reservation System

**How it works:** Reservations are stored as `time_blocker` entries with `RESERVATION:*` descriptions. When a user selects a slot, it's temporarily blocked to prevent double-booking.

**4 TTL Types:**

| Type | TTL | Description Pattern |
|------|-----|---------------------|
| Logged-in user | 1 hour | `RESERVATION:user:{userID}:{timestamp}` |
| Anonymous user | 10 minutes | `RESERVATION:anon:{ipHash}:{timestamp}` |
| Admin walk-in | 5 minutes (15min cleanup) | `RESERVATION:admin:walkin:{customerID}:{timestamp}` |
| Admin call-in | 1 hour (15min cleanup) | `RESERVATION:admin:callin:{customerID}:{timestamp}` |
| Edit request | 24 hours | `RESERVATION:edit_request:{timestamp}` |

**Anonymous Cap:** 50 reservations per 10-minute rolling window. Returns 429 if exceeded.

**Self-Block Fix:** `GetTimeBlockersInRange` excludes `RESERVATION:*` entries so overlap checks don't reject the user's own reservation.

**Cleanup:** `CleanupOldReservations()` runs on availability fetch. Deletes expired entries by type:
- User reservations: > 1 hour old
- Anonymous: > 10 minutes old
- Admin walk-in/call-in: > 15 minutes old
- Edit request reservations: > 24 hours old

**Financial cleanup:** `CleanupExpiredFinancialRecords()` runs on the same availability fetch. Aggregates expired payments/refunds into monthly stats and deletes granular records past their retention threshold.

**Why designed this way:** Storing reservations in `time_blockers` means they automatically participate in availability calculations — no separate reservation table needed. TTL-based cleanup runs on the centralised `jobs` scheduler (cron-based, see below).

---

### Gift Card System

**How it works:** Gift cards are Single-Purpose Vouchers (SPVs) under UK VAT law — VAT is charged at point of purchase, not at redemption. The system supports physical gift cards with codes, account balance credit, inventory cards for stock management, and expired balance recovery.

**Key tables:**

| Table | Purpose |
|-------|---------|
| `gift_cards` | Card records (amount_remaining, is_inventory, expiry_date, last_used_at) |
| `user_giftcard_balances` | Pooled balance from redeemed gift cards (one per user) |
| `gift_card_transactions` | Audit log for every action on a gift card |
| `gift_card_expired_balances` | Dormant balances (from expiry or account deletion), recoverable by admin |

**Gift Card Types:**

| Type | `is_inventory` | `amount_remaining` | Behaviour |
|------|----------------|-------------------|-----------|
| Standard | `FALSE` | > 0 | Normal purchased card, 24-month rolling expiry |
| Inventory | `TRUE` | 0 | Blank card for stock management, topped up later |
| Redeemed | `FALSE` | 0 | Redeemed to account (balance now in `user_giftcard_balances`) |

**24-Month Rolling Expiry:**

- Unused gift cards expire after 24 months of inactivity (`redeemed_by IS NULL` check)
- "Last use" includes: balance check, topup, redeem, payment, any admin action
- Rolling expiry resets on each use
- Expired balance moves to `gift_card_expired_balances` for recovery
- `CleanupExpiredGiftCards()` runs on the centralised `jobs` scheduler (daily at 5am)

**Idle Account Cleanup:**

| Scenario | Threshold | Action |
|----------|-----------|--------|
| No balance, idle | 2 years | Account anonymized (PII removed) |
| With balance, idle | 5 years | Balance → `gift_card_expired_balances`, then account anonymized |

- Admin and guest accounts are never automatically cleaned up
- `gift_card_expired_balances` is retained indefinitely (no PII, only account ID + amount)
- Account ID becomes the recovery key — if lost, balance cannot be recovered (by design — GDPR)

**Payment Methods for Topup:**
- `cash` — Record cash received
- `card_machine` — Square Terminal payment
- `online_square` — Online card entry
- `on_the_house` — Giveaway (no payment collected)

**Idempotency:** `BuyGiftCard` and `CreateTillSale` support `idempotency_key` to prevent duplicate purchases on retry.

**VAT Treatment:**
- SPV: VAT charged at purchase, not at redemption (default)
- MPV: VAT charged at redemption (configurable via `business_settings.voucher_type`)
- `apply_vat_to_till_sale()` function handles VAT calculation for till sales

**Decision:** 24-month rolling expiry (not fixed) matches the CMA's 24-month industry standard and avoids an unfair-contract-term challenge under the Consumer Rights Act 2015. The `gift_card_expired_balances` table stores only account ID + amount (no PII) — indefinite retention by design, with no claim deadline.

---

### Guest User System

**How it works:** Disposable accounts created on-the-fly via `POST /api/users/guest`. Each booking gets a fresh guest user — no identity tracking across bookings.

**Email Uniqueness:** Partial unique index `idx_users_email_registered ON users (email) WHERE account_role != 'guest'`. Guests can share emails; registered users cannot.

**Creation Flow:**
1. Validate name (1-50 chars, unicode letters/spaces/hyphen/apostrophe/dot), email, phone (UK format → E.164)
2. Check if email exists for non-guest user → 409 "Please log in to book"
3. Always create new guest (no guest-to-guest collision check)
4. Set `account_role = 'guest'`, `account_type = 'email'`, `date_of_birth = '1900-01-01'`

**GDPR Anonymization:** `AnonymizeStaleGuestAccounts()` runs on every availability fetch:
- Scrubs PII 6 months after booking's `start_time`
- Preserved: `account_role`, `account_type`, `deposits_required`, `id`, `created_at`
- Scrubbed: name → "Guest Anonymized", email → "anon-{id}@anon.invalid", phone → "000000000000", DOB → "1900-01-01", profile_pic_url → NULL, referral_code → NULL, notes → NULL, data_retention_consent → FALSE
- Excludes users with active/pending bookings

**Decision:** Guest accounts are always created fresh (no guest-to-guest dedup) because the business model is a UK GDPR-compliant salon where each booking is a discrete interaction. Attempting to link guest bookings across sessions would create a tracking profile, which violates the spirit of disposable accounts.

---

### Deposit System

**How it works:** `users.deposits_required` integer (0-3) tracks outstanding deposit obligations. When a user books a service with deposit requirements, the booking gets a `deposit_deadline` (24h before start_time) and `deposit_required = true`.

**Deposit Deadline Flow:**

1. **Booking created** → status is `confirmed` (or `pending` if notes require approval). User has until 24h before the start time to pay >= 20% of the total.
2. **Payment received** → `CreateBookingPayment` checks total paid across all completed payments. If total paid >= 20% of booking total, `deposit_paid` is set to true and any `pending_release` booking is promoted back to `confirmed`. This threshold-based check works regardless of payment type label (`deposit`, `full`, `partial`, `balance`).

   **Payment split:** When a card payment arrives before the booking start time, `buildSplitRecords` (in `handlers/payments/handlers.go`) automatically splits a single Square charge into up to 3 payment records:

   - **Deposit portion:** The first 50% of the booking total (minus any already deposited) is **always** carved out as `payment_type='deposit'`, regardless of payment size. A £25 payment on a £100 booking produces `[deposit=25]`; a £65 payment produces `[deposit=50, partial=15]`.

   - **Balance portion:** The remainder first covers whatever is still owed on the booking. The label is `'balance'` (if previous payments exist and this completes it), `'full'` (if this single portion covers the entire remaining balance) or `'partial'` (if the booking still has a balance after this payment).

   - **Tip portion:** Any amount beyond the booking total overflows into `payment_type='tip'`. A £125 payment on a £100 booking produces `[deposit=50, balance=50, tip=25]`.

   All split records share the same `square_payment_id` so the refund loop can avoid duplicate Square API calls. After the booking start time, no split is applied — the charge records as a single payment with its original type.

   **Concurrency guard:** `CreateBookingPayment` acquires a PostgreSQL session-level advisory lock (`pg_advisory_lock(hashtext('crussell:payment:' || booking_id))`) at entry and releases it in a defer. This serializes all payment attempts for the same booking — if two browser tabs try to pay simultaneously, the second request blocks until the first commits or rolls back. After the lock, the handler re-checks booking status (a concurrent payment may have promoted it) and runs a payment-type duplicate guard that prevents two `'full'` or `'deposit'` payments from being created for the same booking, even with different idempotency keys.

3. **Deposit deadline passes without payment** → `CleanupExpiredDeposits()` moves the booking to `pending_release`. The slot becomes vulnerable — another booking can claim it via eviction. An admin notification `deposit_not_paid_by_deadline` is created. The user's time blocker reservations are also cleaned up.
4. **Slot claimed by another booking** → If a new booking overlaps a `pending_release` slot, `EvictPendingReleaseOverlapping` (a shared function in `bookings.go`) evicts the pending_release booking to `deposit_lapsed`. The eviction runs inside the same transaction as the new booking's creation, so it rolls back if the new booking fails. The function is called by all 4 eviction sites: `CreateBookingHandler`, `ConfirmBookingHandler`, `AdminCreateBookingForUserHandler`, and `AdminRescheduleBookingHandler`. A `PAYMENT_IN_FLIGHT` time_blocker guard prevents evicting a booking that the user is currently paying for (5-minute window).
5. **Payment arrives after deadline but before eviction** → The 20% threshold check promotes `pending_release` back to `confirmed` — the booking is saved and the slot is no longer vulnerable.

**Booking Status Transitions:**

```
pending ──→ confirmed ──→ in_progress ──→ completed
  │            │                              │
  │            ├──(dup complete guard)─────────┘
  │            │     (second completion is idempotent — no extra stamps/discounts)
  │            │
  │            ├──(deadline passed, unpaid)──→ pending_release ──(slot claimed)──→ deposit_lapsed
  │            │                              │
  │            │                         (payment received) ──→ confirmed
  │            │
  │            ├──→ client_cancelled
  │            ├──→ we_cancelled
  │            └──→ no_show (if <24h notice and not forgiven)
  │
  ├──→ client_cancelled
  ├──→ we_cancelled
  └──→ completed
       (pending can complete directly for low-value bookings)

pending_release ──→ pending / confirmed / client_cancelled / we_cancelled
no_show          ──→ (no valid transitions — terminal state)
deposit_lapsed   ──→ (no valid transitions — terminal state)
```

**Status Transition Validation (NEW):** `ProgressBookingHandler` now enforces a `validTransitions` map in Go code. Transitions not in the map are rejected with HTTP 400. This prevents accidental status corruption from API calls, race conditions, or manual DB changes:

```go
validTransitions := map[string]map[string]bool{
    "pending":         {"confirmed": true, "completed": true, "client_cancelled": true, "we_cancelled": true},
    "confirmed":       {"in_progress": true, "completed": true, "client_cancelled": true, "we_cancelled": true},
    "in_progress":     {"completed": true},
    "pending_release": {"pending": true, "confirmed": true, "client_cancelled": true, "we_cancelled": true},
    "no_show":         {},
    "deposit_lapsed":  {},
}
```

**Duplicate Completion Guard:** Calling `ProgressBookingHandler` with `status = "completed"` on an already-completed booking is now idempotent. The handler checks `currentStatus == "completed"` before running any stamp-awarding or discount logic, preventing duplicate loyalty stamps, discount payments, or anniversary credit.

**No-Show Guard for Past Bookings:** `DeleteBookingHandler` now wraps the no-show penalty logic with `startTime.After(clock.Now())`. Past bookings that happen to still be "confirmed" (e.g., because the admin never completed them) are no longer retroactively penalised as no-shows when cancelled after the fact.

**Eviction mechanism:** Eviction is handled by the shared `EvictPendingReleaseOverlapping(ctx, tx, startTime, endTime)` function in `bookings.go`, which is called by all 4 handlers that can claim a slot: `CreateBookingHandler`, `ConfirmBookingHandler`, `AdminCreateBookingForUserHandler`, and `AdminRescheduleBookingHandler`. The function uses a single `UPDATE ... RETURNING` query to evict overlapping `pending_release` bookings and return the affected IDs and user IDs. A `NOT EXISTS` subquery on `time_blockers WHERE description = 'PAYMENT_IN_FLIGHT:' || bookings.id` prevents evicting a booking that a user is currently paying for (the 5-minute payment lock window). Eviction is BAU, so no admin notification is generated — a TODO remains for user notification when that system is built.

**`pending_release` as overlap (NEW):** `AdminCreateBookingForUserHandler` and `AdminReserveSlotHandler` now treat `pending_release` bookings as overlapping — a `pending_release` booking blocks new bookings at the same time, preventing double-booking scenarios where two customers could claim the same slot. The `EvictPendingReleaseOverlapping` eviction strategy was removed from `AdminCreateBookingForUserHandler` — admin-created bookings now reject conflicts with `pending_release` instead of silently evicting them. This matches the `UpdateBookingServicesHandler` and `EditBookingHandler` patterns which also include `pending_release` in their overlap checks.

**24-Hour Late Cancellation Rule (no-show tracking):**
- < 24h without forgiveness → `no_show` status, `deposits_required = 3` (resets, not adds)
- < 24h with forgiveness → `client_cancelled`, no penalty
- ≥ 24h → `client_cancelled`, no penalty
- Pending bookings → deleted, no penalty

**Deposit Reduction:** When booking transitions to `completed` with ≥ 1 payment → `deposits_required -= 1`

**Booking Restriction:** Users with `deposits_required > 0` must book ≥ 24h in advance. Limited to one active booking.

**Admin Bypass:** `enforce_deposits: false` in admin booking creation bypasses all deposit checks.

**Guest Exemption:** Guest bookings bypass deposit and patch-test checks entirely.

**Forgiven No-Shows:** `forgiven_no_shows` table tracks no-shows that were forgiven by admin. Used by `CountUnforgivenNoShows()` to exclude forgiven records. Admins can forgive via `AdminCancelBookingHandler` (`forgive_noshow`) or `AdminRescheduleBookingHandler` (`forgive_noshow`).

**Decision:** The deposit system uses a simple integer (0-3) rather than a separate table of deposit records, because the business rule is a "3-strike" system. The `pending_release`/`deposit_lapsed` two-step status gives a grace period after the deadline for late payments, while still freeing the slot for other customers.

---

### Refund System

**How it works:** When a booking with payments is cancelled (admin or user), `ProcessCancellationRefund` calculates and executes the refund based on notice period and deposit protection rules.

**Refund Calculation Tiers:**

| Tier | Condition | Refund |
|------|-----------|--------|
| Full refund | >= 72h notice | 100% of amount paid |
| Partial refund | 24-72h notice | Protected deposit kept (up to 50%), rest refunded |
| No refund | < 24h notice (no-show) | Nothing refunded |
| Admin forgiveness | `forgive_fees = true` | 100% refunded, overrides deposit protection |

**Deposit Protection Logic:**
- The **protected deposit** is `min(amount_paid, max(total * protected_deposit_max_pct, amount_paid * required_deposit_pct))` where `protected_deposit_max_pct = 50%` and `required_deposit_pct = 20%`.
- In the 24-72h window: you always get back everything above the protected deposit.
- Under 24h (no-show): the protected deposit is the maximum that can be retained. Any amount paid beyond the protected deposit is refunded.

**Policy constants** (shared between frontend `policy.ts` and backend `refund_policy.go`):

| Constant | Value | Meaning |
|----------|-------|---------|
| `FULL_REFUND_THRESHOLD_HOURS` | 72 | Hours of notice needed for full refund |
| `PARTIAL_REFUND_THRESHOLD_HOURS` | 24 | Hours of notice for partial refund |
| `NO_SHOW_THRESHOLD_HOURS` | 24 | Hours before no-show applies |
| `DEPOSIT_ADVANCE_HOURS` | 36 | Min hours advance booking for deposit-required users |
| `DEPOSIT_DEADLINE_WINDOW` | 24 | Hours before start_time to pay deposit |
| `PROTECTED_DEPOSIT_MAX_PCT` | 50% | Max percentage that can be retained as deposit |
| `REQUIRED_DEPOSIT_PCT` | 20% | Minimum deposit needed for protection |
| `RESCHEDULE_BLOCK_HOURS_WITH_PAYMENTS` | 72 | Min notice to reschedule online with payments |
| `RESCHEDULE_BLOCK_HOURS_NO_PAYMENTS` | 24 | Min notice to reschedule online without payments |

**Cancellation flow:**
1. Admin or user calls cancel endpoint
2. `ProcessCancellationRefund` queries booking + payments + start time — now uses an **explicit transaction** (`tx` variant) to ensure atomicity. The new `ProcessCancellationRefundTx` accepts an external `pgx.Tx` so the caller (e.g., `AdminCancelBookingHandler`) can share its transaction.
3. `CalculateRefundForCancellation` determines tier and amounts
4. Refund is executed inside the transaction: Square refund for online card payments, user balance credit for cash/gift card/no-Square-ID payments
5. Refund record created in `refunds` table inside the same transaction
6. If a deposit payment was fully refunded, `deposit_paid` is set to false
7. Loyalty stamps are refunded inside the same transaction (10 stamps refunded if a loyalty discount was applied to the cancelled booking)

**Transaction atomicity across the board:** Multiple handlers were refactored to move status checks and idempotency verification inside their transactions:
- `CreateBookingPayment` — status check, idempotency check, payment-type duplicate guard, and discount application (`applyEligibleCampaignsAtPayment`) now all run inside the payment transaction
- `GetCheckoutStatus` — idempotency check moved inside the transaction
- `CreateTerminalPayment` (cash/giftcard) — status and idempotency checks inside the transaction
- `AdminCancelBookingHandler` — refund processing moved inside the cancel transaction (status update + refund are atomic)
- `ClaimExpiredBalance` — now uses a transaction with `FOR UPDATE` row lock
- `RefundPayment` — Square refund + DB record are atomically linked in a transaction

**`applyEligibleCampaignsAtPayment` refactored:** Now accepts `db.Querier` (instead of `context.Context`) so it runs inside the caller's transaction. The function no longer manages its own `Begin`/`Commit` — the caller owns the transaction lifecycle. This ensures discount writes roll back atomically with the payment writes if the payment commit fails.

**`FOR UPDATE` row locking added to:**
- `AdminCancelBookingHandler` — locks the booking row before reading status and updating
- `ProgressBookingHandler` — locks the booking row before reading status and validating transitions
- `ClaimExpiredBalance` — locks the expired balance row to prevent double-claim races
- `AdminCreateBookingForUserHandler` and `AdminReserveSlotHandler` — overlap checks use `SELECT 1 ... FOR UPDATE`

**Split payment dedup:** When split payment records share the same `square_payment_id` (one Square charge split into deposit + balance records), `ProcessCancellationRefund` tracks already-refunded Square IDs in a local map and only refunds each Square payment once. Subsequent records sharing the same ID skip the Square API call and credit the user's balance instead.

**Admin forgiveness:** Admin can set `forgive_fees = true` (100% refund regardless of notice) or `forgive_noshow = true` (no no-show penalty) on both `AdminCancelBookingHandler` and `AdminRescheduleBookingHandler`.

---

### Scheduling System

**Default Hours:** `working_hours` table (weekday 0-6, start_time, end_time, is_open). Bulk updateable via PUT.

**Exceptional Groups:** Three-table design:
1. `exceptional_working_hours_groups` — group metadata
2. `exceptional_working_hours` — 7 days of hours per group
3. `exceptional_group_applications` — which weeks the group applies to (`week_start` = Monday date). Column is `week_start` (not `monday_week_start`).

**Working Hours Merge:** `GetWorkingHours` loads default hours, overlays exceptional groups for applicable weeks. Returns `DayWorkingHours[]` with `source` field ("default" or "exceptional").

**Available Hours:** `GetAvailableHours` calculates free slots by:
1. Loading working hours for the date range
2. Subtracting existing bookings (with gap logic)
3. Subtracting time blockers (including reservations)
4. **Late night lock**: After 22:00, blocks next morning 00:00-11:00 for non-admin users
5. All data cleanup runs on the centralised `jobs` scheduler (cron-based), not inline during the HTTP request. See `backend/internal/jobs/cleanup.go` for the full schedule.

**Time Blockers:** Can be one-off (no cron) or recurring (cron expression). Cron expansion via `robfig/cron/v3` parser.

**Decision:** The three-table design for exceptional hours (group → hours → applications) allows reuse of the same holiday schedule across multiple years. For example, a "Christmas Week" group can be created once and applied to multiple years by adding multiple application rows. This avoids duplicating the same 7-day schedule for every year.

---

### Patch Test System

**How it works:** Services can require patch tests. `patch_tests` table defines notice periods and expiry. `user_patch_tests` records completions.

**Validation on Booking:**
1. For each service, find associated patch test
2. No record → 400 "Patch test required"
3. Within notice period (24h default) → 400 "Must wait X hours"
4. Expired (6 months default) → 400 "Patch test expired"
5. Valid → service available

**Admin Recording:** Admin can record patch tests via UserModal. On booking completion, patch test validity is updated (UPSERT pattern).

**Decision:** Patch test validity is enforced at booking time, not at service selection time. This is because the service selection UI shows services with eligibility hints (greyed out) but the final check happens at submission. This prevents a customer from adding a valid patch test between service selection and booking submission.

---

### Notifications

**How it works:** Two-tier notification system. Every public booking creates a `new_booking` notification (low priority, acknowledge-only). If the booking has notes or is for today, an additional `pending_booking` notification is also created (high priority, approve/deny action).

**Endpoints:**
- `GET /api/admin/notifications` — List notifications. Query params: `page`, `per_page`, `include_acknowledged` (bool), `reason` (filter). Default: unacknowledged only, sorted by priority CASE WHEN then oldest-first. With `include_acknowledged=true`: all notifications, newest-first.
- `GET /api/admin/notifications/unread-count` — Returns `{"count": N}` for the bell icon.
- `POST /api/admin/notifications/{id}/acknowledge` — Sets `acknowledged_at = NOW()`. Idempotent (404 if already acknowledged).

**Priority order** (SQL CASE WHEN):

| Priority | Reason | Frontend Action |
|----------|--------|-----------------|
| 1 | `pending_booking` | Approve/Decline (ApprovalModal) |
| 2 | `cancelled_booking` | Acknowledge |
| 3 | `late_cancellation` | Acknowledge + See User |
| 4 | `no_deposit` | Acknowledge + See User |
| 5 | `deposit_paid` | Acknowledge |
| 6 | `affiliate_claim` | Acknowledge |
| 7 | `edit_requested` | See Booking (BookingModal) |
| 8 | `new_booking` | See Booking (BookingModal) |
| 9 | `1_month_no_pay` | Acknowledge + See User |
| 10 | `1_week_no_pay` | Acknowledge + See User |

**Auto-acknowledge behavior:** Clicking "Approve Booking", "See Booking", or "See User" automatically acknowledges the notification before opening the modal. The standalone "Acknowledge" button is for dismissing without action.

**Creation sources:**
- Public bookings (`POST /api/bookings`) → always `new_booking`, plus `pending_booking` if notes or today
- Edit requests (`POST /api/bookings/{id}/edit-request`) → always `edit_requested`, plus `pending_booking` if booking status is pending
- Admin bookings (`POST /api/admin/bookings`) → no notifications (admin already knows)

**User notification preferences:**
Users manage their preferred notification channels via `/account` → Admin tab → Notifications section. The `user_notification_preferences` table stores per-user flags for email, SMS, and browser push. These flags are not yet used by any delivery system — they will be consumed when the email/SMS notification system (E5) is built.

**Decision:** The two-tier notification system (new_booking + pending_booking) was designed to avoid overwhelming the admin with approval notifications. Most bookings are routine and don't need attention — only those with notes or same-day bookings are flagged as "needs review." The auto-acknowledge behavior reduces the number of clicks required for common actions.

---

### Enriched Edit Request System

**How it works:** When a user requests a booking edit (time change, notes, or services), the system creates a `booking_edit_requests` row and returns an **enriched response** with side-by-side `original` and `proposed` snapshots. Each snapshot includes start/end times, full service details (name, price, duration), and notes.

**Enriched Response Types:**

```go
type EditServiceDetail struct {
    ID              string  `json:"id"`
    Name            string  `json:"name"`
    Price           float64 `json:"price"`
    DurationMinutes int     `json:"duration_minutes"`
}

type EditSnapshot struct {
    StartTime *time.Time         `json:"start_time"`
    EndTime   *time.Time         `json:"end_time"`
    Services  []EditServiceDetail `json:"services"`
    Notes     *string            `json:"notes"`
}

type EnrichedEditRequest struct {
    ID          string           `json:"id"`
    BookingID   string           `json:"booking_id"`
    RequestedBy string           `json:"requested_by"`
    RequestedAt time.Time        `json:"requested_at"`
    Notes       *string          `json:"notes"`
    Original    *EditSnapshot    `json:"original"`
    Proposed    *EditSnapshot    `json:"proposed"`
    User        *EditUserSummary `json:"user,omitempty"`
}
```

**End-time calculation:** `end_time = start_time + sum(service durations)`. If total duration is 0, falls back to 60 minutes.

**`has_overrides` branch:** When a booking has override prices/durations on its services, the `proposed` snapshot uses the original booking services (not the `new_services` array) since service changes are blocked for overridden bookings.

**New endpoints:**

| Endpoint | Auth | Response |
|----------|------|----------|
| `GET /api/bookings/{id}/edit-request` | User (owner only) | `{"edit_request": EnrichedEditRequest}` |
| `GET /api/bookings/edit-requests` | User (own only) | `{"edit_requests": [EnrichedEditRequest]}` |
| `GET /api/admin/bookings/edit-requests` | Admin | `{"edit_requests": [EnrichedEditRequest]}` |
| `GET /api/admin/bookings/{id}/edit-request` | Admin | `{"edit_request": EnrichedEditRequest}` |

**Cancellation cleanup:** When a user cancels their booking (`UserCancelBookingHandler`), any pending edit request, associated `RESERVATION:edit_request` time_blocker, and `edit_requested` admin_notification are all deleted.

**Notification upsert:** When a user submits a second edit request (upsert), the old `edit_requested` notification is deleted and a fresh one is created — admins see a single refreshed notification with an updated timestamp, never duplicates.

**Exceptional hours validation:** When admin approves an edit request, the proposed time is checked against `exceptional_working_hours`. If the time falls during a closed period, approval is rejected with 409 Conflict.

**Decision:** The enriched response format (with side-by-side original/proposed snapshots) was designed so the admin can see the full context without making additional API calls. The original approach required the admin to fetch the booking separately, which created a race condition where the booking could change between the edit request creation and the admin viewing it.

---

### Loyalty & Discount System

**Loyalty Stamps:**
- 1 stamp per completed paid booking (max 1 per calendar day)
- Zero-total bookings (£0) don't earn stamps
- At 10 stamps → pending `loyalty_redemption` created (6-month expiry)
- Next completed paid booking → 10% discount applied, stamps reset via `GREATEST(0, stamps - 10)`, +1 stamp earned for this completion → net stamps = 1
- Oldest pending redemption used first (FIFO by `redeemed_at`)

**Discount Stacking:**
All applicable discounts stack additively (not compound). Each discount is calculated against the **original booking total** and creates its own `booking_discounts` row and `payments` row (`payment_method = 'discount'`).

| # | Source | Trigger | Dedup |
|---|---|---|---|
| 1 | Loyalty | Pending redemption exists (`status = 'pending'`, `expires_at > NOW()`) | One redemption consumed per use |
| 2 | Time-based campaign | `status = 'active'`, within `start_date`–`end_date` range | Single best (highest %) selected |
| 3 | Per-user milestone | User's completed booking count matches `milestone_value` exactly | `NOT EXISTS` on `booking_discounts` per user per campaign |
| 4 | Global milestone | Salon-wide completed booking count matches `milestone_value` exactly | `times_redeemed < max_redemptions` |
| 5 | Anniversary | Time since user's first completed booking ≥ `milestone_value` (months/years) | `NOT EXISTS` on `booking_discounts` per user per campaign |

**Campaign Lifecycle:** `draft → active → completed` (also: any → `cancelled`, `active → draft` for re-editing). Campaigns created as `draft` by default; must be manually activated.

**Formula:** `discountAmount = roundTo2(bookingTotal × discountPercent / 100)` — always against original total.

**Example:** £100 booking with loyalty (10%) + time-based campaign (5%) + anniversary milestone (10%) = 3 discount rows totalling £25.00. Customer pays £75.00.

**Tables:** `loyalty_redemptions`, `discount_campaigns`, `booking_discounts`

**Frontend UI notes:**
- Tip percentages (10%, 15%, 20%) in the Take Payment modal are calculated dynamically on the **net total after discounts** (`subtotal - discountSum`) instead of the pre-discount subtotal
- The Booking Details modal uses elevated z-index (`!z-[60]`) to ensure it always opens in front of the User Details modal (`z-50`)
- Applied discounts are presented as negative numbers (e.g., `-£2.50`) in Payment History to visually distinguish them from customer cash/card payments
- Svelte uses index-based chronological lookup to match each discount payment row uniquely to its exact Campaign or Loyalty source, preventing duplicate descriptions on multiple discounts with identical amounts

**Decision:** Discounts are calculated against the original total (not post-discount) to avoid the complexity of compound discounts. Each discount creates its own `payments` row with `method = 'discount'` so the financial records are complete and auditable. The "discount payments" approach also makes it easy to show customers exactly how much each discount saved them.

---

### Financial Data Retention & Aggregation

**How it works:** Granular payment and refund records are retained for `MAX(created_at + 7 years, user_anonymized_at + 1 year)` — whichever is further into the future. Once a record's retention period expires, it is aggregated into `financial_aggregates` (monthly totals, no PII) and the granular record is deleted.

**Retention logic:**

| Scenario | Retention period |
|----------|-----------------|
| Active user (not anonymized) | 7 years from `payments.created_at` |
| Walk-in (guest account, not anonymized) | 7 years from `payments.created_at` |
| Anonymized user | MAX(7 years from `payments.created_at`, 1 year from `users.updated_at`) |

A record is only deleted when **both** applicable conditions are met — the 7-year rule AND the 1-year post-anonymization buffer (if applicable).

**Trigger:** `CleanupExpiredFinancialRecords(ctx)` runs on the centralised `jobs` scheduler (daily at 4am).

**Aggregation columns** (`financial_aggregates` table):

| Column | Source |
|--------|--------|
| `month` | `DATE_TRUNC('month', created_at)::date` |
| `total_payments` | `SUM(amount)` |
| `total_refunds` | `SUM(amount)` from refunds |
| `total_square_fees` | `SUM(fees)` |
| `total_cash` | `SUM(amount)` WHERE `payment_method = 'cash'` |
| `total_online` | `SUM(amount)` WHERE `payment_method = 'online_square'` |
| `total_in_person` | `SUM(amount)` WHERE `payment_method = 'in_person_card'` |
| `total_discounts` | `SUM(amount)` WHERE `payment_method = 'discount'` |
| `total_giftcard` | `SUM(amount)` WHERE `payment_method = 'giftcard'` |
| `total_tips` | `SUM(amount)` WHERE `payment_type = 'tip'` |
| `total_deposits` | `SUM(amount)` WHERE `payment_type = 'deposit'` |
| `total_balances` | `SUM(amount)` WHERE `payment_type = 'balance'` |
| `total_partials` | `SUM(amount)` WHERE `payment_type = 'partial'` |
| `total_vat_amount` | `SUM(p.vat_amount)` — new column, tracks VAT for MTD reporting |
| `total_net_amount` | `SUM(p.net_amount)` — new column, tracks net for MTD reporting |
| `booking_count` | `COUNT(DISTINCT booking_id)` |

**Idempotency:** Uses `ON CONFLICT (month) DO UPDATE` with additive upserts (`table.col + EXCLUDED.col`). Running the cleanup twice produces the same result — no double-counting.

**Live data unaffected:** Aggregation only reads and deletes records past their retention threshold. Recent payments/refunds within their retention period are never touched. User anonymization (`anonymize_user()`, `AnonymizeStaleGuestAccounts`) scrubs PII only — it never deletes financial records.

**Tables:** `financial_aggregates`, `payments`, `refunds`

**Decision:** The aggregation is idempotent — safe to run repeatedly. All cleanup now runs on the centralised `jobs` scheduler (cron-based). See `backend/internal/jobs/cleanup.go` for schedules.

---

### Service Eligibility

**Age Filtering:** `services.minimum_age_required` compared to user's `date_of_birth`. If user's age < minimum → service excluded.

**Patch Test Filtering:** Services linked to `patch_tests` via `service_ids[]`. User must have valid `user_patch_tests` record.

**`patch_test_duration_hours`:** The `Service` and `ServiceResponse` types include `patch_test_duration_hours`. All service list endpoints `LEFT JOIN patch_tests` to populate this field. When creating a service with `patch_test_duration_hours > 0`, a corresponding `patch_tests` record is auto-created with the service ID in `service_ids`.

**Admin vs. Customer:**
- `/api/services` — returns services with eligibility for authenticated users
- `/api/services/eligible-for/{user_id}` — admin-only, returns services for specific user (used in admin booking flows)

---

### GetBookingsByCreatedRange

**How it works:** `GET /api/admin/bookings/by-created-range?start=ISO8601&end=ISO8601` returns all bookings whose `created_at` falls within the specified range. Results are ordered by `created_at` ascending.

**Use case:** Admin reporting — "show me all bookings created this week" or "show me all bookings created in January".

**Response format:** Returns `OverlappingBookingsResponse` with `bookings` array — same structure as the overlapping bookings endpoint, including user details and services.

**Validation:**
- Both `start` and `end` query parameters are required (400 if missing)
- Must be valid ISO 8601 timestamps (400 if invalid format)
- Requires admin authentication

---

### Lunch Protection Refactor

**Before:** `findLargestLunchGap()` returned a single number — the largest gap in the middle window after accounting for bookings.

**After:** `findAllLunchGaps()` returns an array of all gap durations in the middle window, sorted descending. This enables the Today calendar to show all available lunch break opportunities rather than just the best one.

**Frontend:** `buildLunchProtection()` in `lib/utils/timeSlots.ts` consolidates lunch protection logic that was previously duplicated across BookingFlow, BookingCreateModal, and EditRequestModal. All booking flows now use the shared utility.

**`shouldApplyLunchProtection()`:** A new helper that skips lunch protection entirely for short working days (5 hours or less). This prevents false lunch-break warnings on half-days or days with abbreviated hours.

---

### Referral Code System

**How it works:** During registration, users can optionally provide a 12-character alphanumeric referral code. The backend validates the code format, looks up the referrer by their `referral_code`, and records the relationship in the `user_referrals` table.

**Validation:**
- Format: exactly 12 characters, alphanumeric (A-Z, a-z, 0-9)
- Case-insensitive lookup
- Referred user's `referral_code` field is set to their own unique code (auto-generated on registration)
- The relationship is recorded in `user_referrals` (referrer_id, referred_id, created_at)

**Frontend:** The login page has a formatted input field for the referral code (xxxx-xxxx-xxxx, auto-formatted as the user types).

**Decision:** Referral codes are stored on the `users` table (one code per user) and relationships are tracked in `user_referrals` (many-to-many). This allows users to both refer others and be referred by someone. The 12-character format matches the gift card ID format for consistency.

---

### Email Check System

**How it works:** `GET /api/check-email` proactively detects when a guest booking email matches a registered user. It requires all guest fields (email + firstName + lastName + phone) before returning a suggestion, preventing false positives from email-only matches.

**Response:**
- `{"suggestion": "login"}` — full match (email + name + phone all match a registered user)
- `{"suggestion": "check"}` — partial match (email matches but name/phone don't)
- `{"suggestion": null}` — not found or guest

**Integration:** The BookingFlow Step 3 performs a debounced check (300ms) after the user fills in all fields. If the suggestion is `"login"`, the system shows a prompt encouraging the user to log in.

**Decision:** The multi-field matching approach prevents false positives. Without it, a customer using a shared email address (e.g., a family email) would be incorrectly prompted to log in every time they booked. By requiring all fields, the system only suggests login when it's genuinely likely to be the same person.

---

### Idempotency Keys

**How it works:** The `idempotency_key` column (VARCHAR(64) UNIQUE) is present on `bookings`, `payments`, and `till_sales` tables. The header `Idempotency-Key` is extracted from incoming requests. If a record with that key already exists, the existing record is returned (200 for bookings, appropriate for others). If not, the operation proceeds and the key is stored.

**Frontend:** UUID generated via `crypto.randomUUID()` in the browser. The same key is reused on retry.

**Endpoints with idempotency:**
- `POST /api/bookings` — returns existing booking with 200
- `POST /api/admin/bookings` — returns existing booking with 200
- `POST /api/gift-cards/buy` — prevents duplicate gift card purchases
- `POST /api/admin/till-sales` — prevents duplicate till sales

**Decision:** Idempotency keys are stored as a unique column on the table rather than a separate `idempotency_keys` table. This is simpler and avoids the need for a separate cleanup mechanism. The trade-off is that keys accumulate indefinitely (see Future Work #25 for cleanup).

---

### JWT Authentication

**Architecture:**
- **Access token**: 1 hour expiry (changed from 30 days June 2026 security pass)
- **Refresh token**: 90-day sliding opaque token stored in `refresh_tokens` table (SHA-256 hash)
- **Rotation**: Each refresh invalidates the previous token via `DELETE`-based consumption. If a used token is replayed, it returns no rows → rejection (family-based revocation).
- **Signing**: HS256 with secret from `JWT_SECRET_KEY` env var

**JTI Revocation:**
Every JWT carries a unique `jti` claim (UUID v4). Revoked JTIs are stored in the `revoked_jtis` PostgreSQL table with an expiry timestamp. `IsJTIRevoked()` is called by `VerifyToken()` on every request. A background cleanup runs every 30 minutes (with panic recovery via `defer recover()`):

```sql
DELETE FROM revoked_jtis WHERE expires_at < NOW();
```

This replaces the old in-memory map (pre-June 2026 security pass). The DB-backed approach survives server restarts and doesn't leak memory.

**All `time.Now()` replaced with `clock.Now()`** — token expiry, JTI revocation timestamps, login state cleanup, and account lockout checks all use `clock.Now()` to ensure UTC consistency.

**When JTIs are revoked:**
1. `POST /api/logout` — revokes current JTI with 1h expiry
2. `POST /api/refresh-token` — revokes old JTI before issuing new token (rotation)
3. `PUT /api/user/password` — logs the change (full session revocation is a TODO — the current system only revokes the specific JTI, not all user sessions)

**Account Lockout:**
After 5 failed login attempts, the account is locked with progressive durations:
- 5 failures → 15 minute lockout
- 7 failures → 30 minute lockout
- 10 failures → 1 hour lockout
- 20 failures → 2 hour lockout

Lockout state is stored in `users.failed_attempts` and `users.locked_until` columns. Successful login resets both.

**Password Validation:**
- Minimum length: 6 characters (enforced by validator tag + explicit check)
- Server-side strength: `@zxcvbn-ts/core` via goja (same JS library as frontend). Requires score ≥ 2. Skipped when `GO_TESTING=1`.
- bcrypt max: 72 characters (truncated by bcrypt internally — checked server-side)

---

### PhoneInput Component

**How it works:** `PhoneInput.svelte` is a reusable component with inline UK phone validation, auto-formatting, and key filtering.

**Features:**
- Validates UK phone numbers as the user types
- Auto-formats the display (e.g., 07700 900 000)
- Filters out non-diallable characters at the keypress level
- Enforces max 11 digits for both landline and mobile
- `onerrorchange` callback for external validation integration
- Used in login registration and account phone editing

**Validation:**
- UK landline: max 11 digits
- UK mobile: max 11 digits
- Non-digit keys are filtered out

**Decision:** The component filters characters at the keypress level rather than post-processing. This prevents the user from ever entering invalid characters, which is a better UX than showing an error after the fact. The 11-digit limit matches UK phone number standards.

---

### Business Settings API

**How it works:** `GET /api/admin/settings` and `PUT /api/admin/settings` endpoints manage business configuration.

**Fields:**
- `business_name` — salon name
- `business_address` — address
- `vat_registration_number` — VAT number
- `is_vat_registered` — boolean
- `gift_card_expiry_months` — default 12 (configurable, but actual expiry logic uses 24 months)
- `voucher_type` — `SPV` (default) or `MPV`

**Validation:**
- `voucher_type` must be `SPV` or `MPV`
- `gift_card_expiry_months` must be a positive integer
- `vat_registration_number` must be a valid UK VAT number: `GB` followed by 9 digits (standard) or 12 digits (branch). Previously allowed up to 20 arbitrary characters.
- `business_email` must be 254 characters or fewer
- Website URL validated with Go's `url.ParseRequestURI`
- Partial update — only provided fields are changed

**Decision:** The `gift_card_expiry_months` field is configurable but the actual expiry logic uses 24 months (hardcoded in `CleanupExpiredGiftCards`). The `gift_card_expiry_months` field exists for future flexibility but is not currently used by the expiry logic.

---

## Test Architecture

### TestMain per Package

Each test package has a `TestMain` that runs schema migration once per package (not per test). This reduces setup time by ~60% compared to per-test migration.

### TruncateTables

Between tests, `TruncateTables()` runs `TRUNCATE TABLE ... CASCADE` on all tables. This is ~60% faster than DROP+CREATE.

### Payment Serialization Lock (advisory lock 1339)

`CreateBookingPayment` acquires a PostgreSQL session-level advisory lock (`pg_advisory_lock(hashtext('crussell:payment:' || booking_id))`) to serialize concurrent payment attempts for the same booking. This prevents the two-tab double-payment race where two browser tabs submit payments with different idempotency keys but the same payment type.

**Key implementation detail:** The lock must be acquired and released on the **same** database connection. Using `db.DB.Exec()` for both would be unsafe — each call may get a different pool connection, and `pg_advisory_unlock` on a different session is a silent no-op, leaking the lock. The code uses `db.DB.Acquire()` to pin a dedicated connection for the duration of the handler, with `defer pinConn.Release()` ensuring the connection is returned when done.

```go
pinConn, err := db.DB.Acquire(r.Context())
defer pinConn.Release()
pinConn.Exec(ctx, "SELECT pg_advisory_lock(hashtext('crussell:payment:' || $1))", bookingID)
defer pinConn.Exec(ctx, "SELECT pg_advisory_unlock(hashtext('crussell:payment:' || $1))", bookingID)
```

After acquiring the lock, the handler re-checks the booking status (a concurrent payment may have promoted it) and runs a payment-type duplicate guard that prevents two `'full'` or `'deposit'` payments from being created for the same booking, even with different idempotency keys. The guard accounts for `buildSplitRecords` which converts `'full'` input to `'deposit'` + `'balance'` records.

### Cursor-Based Pagination Pattern

All list endpoints use cursor-based pagination with `(created_at, id)` tuples. The query fetches `perPage + 1` items — if we got the extra item, there's a next page. The cursor is set only when a next page exists:

```go
query += " LIMIT $N"
args = append(args, perPage+1)
// ... fetch rows ...
var nextCursor *string
if len(results) > perPage {
    results = results[:perPage]
    last := results[perPage-1]
    cursor := last.CreatedAt.Format(time.RFC3339) + "|" + last.ID
    nextCursor = &cursor
}
```

This prevents clients from making an extra empty-page request to determine the end of results. The cursor must be URL-encoded by the client (`url.QueryEscape()`) because the `+` in RFC3339 timezone offsets is decoded as a space in query strings.

Files with this pattern: `bookings.go` (4 handlers), `custom_services.go`, `user/profile.go`, `notifications/notifications.go`, `payments/giftcards.go`, `portfolio/images.go`.

**Performance note:** The data query uses `LIMIT perPage + 1` with no window function. The `COUNT(*) OVER()` was removed from all cursor-paginated queries because it forces PostgreSQL to materialise all matching rows before applying the LIMIT, defeating cursor pagination's key advantage. Instead, a separate `SELECT COUNT(*)` query runs before the data query using a simplified FROM+WHERE (no CTEs, no SELECT subqueries, no LEFT JOINs unless needed for WHERE filtering). This count query uses indexes and doesn't block the data query's streaming LIMIT.

### Advisory Lock Pattern (test infrastructure)

**Migration lock (1337):** `pg_advisory_lock(1337)` protects concurrent schema migration. When multiple test packages run simultaneously, only one executes the migration DDL at a time.

**Truncation lock (1338):** `pg_advisory_lock(1338)` prevents CASCADE truncation deadlocks. When two tests try to truncate simultaneously, one waits for the other.

**Implementation:** Both use `pool.Acquire()` for a dedicated connection (same pattern as the payment serialization lock above).

### Statement-by-Statement SQL Parser

`splitSQLStatements()` in `testdb.go` respects dollar-quoted PL/pgSQL blocks (`$$...$$`). It splits the init script into individual statements and executes them sequentially. This avoids the pgx protocol hang that occurs when sending multi-statement scripts with PL/pgSQL functions.

### Test Configuration

- **Per-package databases:** Each package gets its own `crussell_test_*` database, created in `TestMain` via `CreateTestDatabase()`
- **Parallel execution:** `-p` defaults to `GOMAXPROCS` — packages run concurrently against their own databases
- **⚠️ Build tag:** Always `-tags "test,dev"`. Without `dev`, `square_dev.go` (mock client) and `ratelimit_dev.go` are excluded, causing `handlers/payments` and `handlers/bookings` tests to be silently skipped.
- **Build tag:** `//go:build test`
- **Test secret:** `test-secret-key-for-testing-only`
- **Fixtures:** Auto-generate unique emails using `fmt.Sprintf("test-%d-%d@example.com", time.Now().UnixNano(), rand.Int())`

### Test Coverage

**1,251/1,255 tests run** across all packages (4 skipped, 0 failures). Recent additions: centralised job scheduler tests (3 — RegisterAll count, schedules, handler signatures), scheduled-cleanup handler tests (21 — NotifyUnpaidOneWeek/Month, TransitionDiscountCampaigns, CleanupExpiredVerificationCodes/RefreshTokens), GDPR export cache cleanup (4), stale login entry cleanup (4), rate limiter cleanup tests (6), rate limiter production behavior tests (6). Self-blocking prevention tests (excludeUserID coverage for GetAvailableHours, EditBookingHandler, AdminRescheduleBookingHandler, ReserveSlotHandler anon IP cleanup, AdminReserveSlotHandler anon IP cleanup), closing_time tests (3), content-type middleware tests (2), booking handler tests (FOR UPDATE overlap checks, admin reserve with closing_time, gift card buy with VAT), and the new admin reservation cancel coverage (12 tests covering walkin + callin + isolation + no-op + idempotency + response format parity, plus 2 inverse-isolation tests on the user-side `cancel_reservation_test.go` to prove the two cancel endpoints are properly partitioned by their WHERE clauses). Booking integration tests continue to expand: duplicate completion guard, daily stamp cap (handler + SQL subquery), invalid status transitions, sequential edit, timezone independence, and past-booking no-show guard. The `clock` package itself has tests for Now() and clock interface correctness.

| Package | Coverage Area |
|---------|--------------|
| `handlers/auth` | Authentication (login, register, refresh, verification) |
| `handlers/bookings` | User booking flow, guest bookings, reservations, edit requests, approval, time-blockers, exceptional hours, cancellation, cross-user isolation |
| `handlers/payments` | Square payments (terminal, online, refunds, tips, saved cards, gift cards) |
| `internal/square` | Square client interface, dev mock, prod stub |
| `handlers/admin` | Admin bookings, today view, users, services, GetBookingsByCreatedRange |
| `handlers/scheduling` | Working hours, exceptional groups, available hours, time blockers, gift card expiry cleanup, idle account cleanup |
| `handlers/services` | Service eligibility (age + patch test filtering) |
| `handlers/user` | User profile, guest creation, loyalty, GDPR export, anonymization |
| `handlers/portfolio` | Image upload, listing, tags, filters |
| `handlers/notifications` | Admin notifications (GET, acknowledge) |
| `handlers/handlers_test.go` | Common handler tests |
| `bookings_test.go` | Main booking integration tests |

---

## Build Tags Reference

| Tag | File | Meaning |
|-----|------|---------|
| `dev` | `square_dev.go`, `db_dev.go`, `s3_dev.go`, `service_dev.go` | Development mode — uses mocks, localhost, RustFS |
| `!dev` | `square.go`, `db.go`, `s3.go`, `service_prod.go` | Production mode — uses live Square, env vars, Cloudflare R2 |
| `test` | `*_test.go` | Test files — only compiled with `go test` |

**Build command:** `go build -tags dev` (development) or `go build` (production, default).

**Test command:** `go test -tags "test,dev" ./...`

**Decision:** Build tags are used for service selection (Square, S3, DB, DAV) rather than runtime configuration. This keeps the dev/prod switch compile-time safe — if prod code is missing credentials, it fails at build time rather than runtime.

---

## Mermaid Sequence Diagrams

### Booking Creation Flow

```mermaid
sequenceDiagram
    participant C as Customer
    participant F as Frontend
    participant B as Backend
    participant DB as PostgreSQL

    C->>F: Select services, date, time
    F->>B: POST /api/bookings/reserve
    B->>DB: INSERT time_blocker (RESERVATION:user:...)
    DB-->>B: OK
    B-->>F: Reservation confirmed
    C->>F: Fill details (name, email, phone, notes)
    F->>B: POST /api/bookings (with Idempotency-Key)
    B->>DB: Check idempotency key
    alt Key exists
        DB-->>B: Return existing booking
        B-->>F: 200 + existing booking
    else Key new
        B->>DB: Check email for registered user
        B->>DB: INSERT booking + booking_services
        B->>DB: INSERT time_blocker (slot)
        B->>DB: DELETE reservation time_blocker
        B->>DB: Create notifications
        DB-->>B: OK
        B-->>F: 201 + booking
    end
    F-->>C: Confirmation screen
```

### Payment Flow (Card via Terminal)

```mermaid
sequenceDiagram
    participant A as Admin
    participant F as Frontend
    participant B as Backend
    participant S as Square API
    participant T as Terminal Device

    A->>F: Click "Take Payment" → Select Card (Terminal)
    F->>B: POST /api/admin/bookings/{id}/payment
    B->>S: Create checkout
    S-->>B: checkout_id
    B->>S: Create terminal payment
    S-->>B: terminal_payment_id
    B-->>F: 202 + checkout_id
    F-->>A: Show "Waiting for terminal..."
    T->>S: Customer inserts card
    S-->>B: Webhook: payment.completed
    B->>DB: INSERT payment (status=completed)
    B->>DB: Update booking status
    B->>DB: Apply discounts (if completed)
    B-->>F: SSE / Poll: payment complete
    F-->>A: Show "Payment successful"
```

### Gift Card Purchase Flow

```mermaid
sequenceDiagram
    participant C as Customer
    participant F as Frontend
    participant B as Backend
    participant DB as PostgreSQL

    C->>F: Go to gift card purchase page
    F->>C: Enter amount, payment method
    C->>F: Submit
    F->>B: POST /api/gift-cards/buy (with Idempotency-Key)
    B->>DB: Check idempotency key
    alt Key exists
        DB-->>B: Return existing gift card
        B-->>F: 200 + existing
    else Key new
        B->>S: Process payment (if online)
        S-->>B: Payment confirmed
        B->>DB: INSERT gift_card
        B->>DB: INSERT gift_card_transaction (purchase)
        B->>DB: INSERT payment (if applicable)
        DB-->>B: OK
        B-->>F: 201 + gift card
    end
    F-->>C: Show gift card code
```

### Edit Request Lifecycle

```mermaid
sequenceDiagram
    participant C as Customer
    participant F as Frontend
    participant B as Backend
    participant DB as PostgreSQL

    C->>F: Request reschedule
    F->>B: POST /api/bookings/{id}/edit-request
    B->>DB: INSERT booking_edit_request
    B->>DB: INSERT time_blocker (RESERVATION:edit_request)
    B->>DB: Create notification (edit_requested)
    B->>DB: Create notification (pending_booking if applicable)
    DB-->>B: OK
    B-->>F: 201 + enriched edit request
    F-->>C: Show "Request submitted"

    A->>F: View Pending Approvals
    F->>B: GET /api/admin/today/pending-approvals
    B->>DB: SELECT notifications
    DB-->>B: Notifications + edit requests
    B-->>F: Enriched responses
    F->>A: Show side-by-side original vs proposed

    A->>F: Click Approve
    F->>B: POST /api/admin/bookings/{id}/edit-requests/{rid}/approve
    B->>DB: Update booking (new time/services)
    B->>DB: DELETE edit request
    B->>DB: DELETE reservation time_blocker
    B->>DB: Update notification (acknowledged)
    B->>DB: Check exceptional hours
    alt Hours conflict
        DB-->>B: Closed period
        B-->>F: 409 Conflict
        F-->>A: Show "Time is during closed hours"
    else Hours OK
        DB-->>B: OK
        B-->>F: 200 + updated booking
        F-->>A: Show "Reschedule approved"
    end
```

### GDPR Data Export

```mermaid
sequenceDiagram
    participant C as Customer
    participant F as Frontend
    participant B as Backend
    participant DB as PostgreSQL

    C->>F: Go to /gdpr
    F->>B: GET /api/user/gdpr-export
    B->>B: Check cache
    alt Cache HIT
        B-->>F: 200 + cached data
    else Cache MISS
        B-->>F: 202 + "generating"
        F->>C: Show skeleton loading
        B->>DB: Call export_all_user_data()
        DB-->>B: 16-section JSON
        B->>B: Store in cache (12h TTL)
        F->>B: Poll every 2 seconds
        B-->>F: 200 + data
        F-->>C: Show report cards/tables
    end
    C->>F: Click "Download JSON"
    F-->>C: Download raw JSON
    C->>F: Click "Print PDF"
    F-->>C: Print (CSS hides navbar)
```

**June 2026 additions:**
- `failed_attempts`, `locked_until` added to user profile section
- `login_audit` — new section (attempt_type, ip_address, success, created_at)
- `refresh_tokens` — new section (role, revoked, created_at, expires_at; token_hash excluded)
- Empty sections return `null` instead of `[]` for cleaner output

---

## Key Code Patterns

### Test Pattern: Advisory Lock

```go
// testdb.go
func runMigrations(pool *pgxpool.Pool) error {
    conn, err := pool.Acquire(context.Background())
    if err != nil {
        return err
    }
    defer conn.Release()

    // Acquire lock on dedicated connection
    _, err = conn.Exec(context.Background(), "SELECT pg_advisory_lock(1337)")
    if err != nil {
        return err
    }
    defer conn.Exec(context.Background(), "SELECT pg_advisory_unlock(1337)")

    // Run migration
    statements := splitSQLStatements(migrationSQL)
    for _, stmt := range statements {
        _, err = conn.Exec(context.Background(), stmt)
        if err != nil {
            return err
        }
    }
    return nil
}
```

### Test Pattern: Fixture Generation

```go
func createTestUser(t *testing.T, pool *pgxpool.Pool) *models.User {
    email := fmt.Sprintf("test-%d-%d@example.com", time.Now().UnixNano(), rand.Int())
    user := &models.User{
        Name:     "Test User",
        Email:    email,
        Phone:    "+447700900000",
        Password: "password",
        Role:     "verified_email",
    }
    err := db.CreateUser(context.Background(), pool, user)
    require.NoError(t, err)
    return user
}
```

### Pattern: Idempotency Check

```go
func (h *BookingHandler) CreateBooking(w http.ResponseWriter, r *http.Request) {
    idempotencyKey := r.Header.Get("Idempotency-Key")
    if idempotencyKey != "" {
        existing, err := h.db.GetBookingByIdempotencyKey(r.Context(), idempotencyKey)
        if err == nil && existing != nil {
            respondWithJSON(w, http.StatusOK, existing)
            return
        }
    }
    // ... proceed with creation
}
```

### Pattern: Enriched Response

```go
func enrichEditRequest(ctx context.Context, db *pgxpool.Pool, req *models.EditRequest) (*EnrichedEditRequest, error) {
    original, err := buildSnapshot(ctx, db, req.BookingID, nil)
    if err != nil {
        return nil, err
    }
    proposed, err := buildSnapshot(ctx, db, req.BookingID, req.NewServices)
    if err != nil {
        return nil, err
    }
    return &EnrichedEditRequest{
        ID:       req.ID,
        Original: original,
        Proposed: proposed,
    }, nil
}
```

---

## Schema Decisions

### Why 12-character hex IDs?

All tables use 12-character hex IDs (e.g., `a1b2c3d4e5f6`). This is shorter than UUIDs (36 chars) while still providing ~4.7×10¹⁴ possible values (16¹²). Collision probability is negligible for the expected data volume (thousands of records, not billions). The short IDs are URL-friendly and easier to display in admin UIs.

### Why partial unique index on email?

The `idx_users_email_registered` index is `UNIQUE ON (email) WHERE account_role != 'guest'`. This allows guest accounts to share email addresses (a common scenario for family bookings) while enforcing uniqueness for registered accounts.

### Why soft-delete for saved cards?

Saved cards use `retained_until` instead of `deleted_at`. This is because UK financial regulations require 7-year retention of payment records. The `retained_until` timestamp is set to 7 years from deletion, after which the record can be permanently removed.

### Why payments use pence (int64) in API but float in DB?

The API uses pence (int64) for all monetary values to avoid floating-point precision issues. The database stores pounds as `NUMERIC(10,2)` for SQL-level precision. The conversion happens at the API boundary: pence → pounds on read, pounds → pence on write.

### Why gift cards are SPVs by default?

Under UK VAT law, most salon gift cards are Single-Purpose Vouchers (SPVs) because they can only be redeemed for the salon's own services. VAT is charged at the point of purchase. This is the default behavior. Multi-Purpose Vouchers (MPVs) — where VAT is charged at redemption — are configurable for future flexibility.

### Why idle account cleanup uses two thresholds?

Accounts with no gift card balance are anonymized after 2 years of inactivity. Accounts with a balance are kept for 5 years. This two-tier approach balances GDPR compliance (storage limitation) with customer fairness (giving them time to use their balance). Before anonymization, the balance is moved to `gift_card_expired_balances` for recovery.

### Why the notification system is pull-based?

The admin notification system uses polling (pull) rather than WebSockets or SSE (push). This is because the admin dashboard is a management tool, not a real-time monitoring system. Polling every 30 seconds is sufficient for booking notifications. Push-based systems would require WebSocket infrastructure, which adds complexity for a single-admin deployment.

---

## Performance Considerations (Cloud Hosting)

Every database query has a compute cost. On cloud-hosted PostgreSQL (RDS, Cloud SQL, Supabase), this translates directly to monthly bills. The following patterns are used throughout the codebase to minimise query count and table scan size.

### 1. `UPDATE ... RETURNING` over `UPDATE + SELECT WHERE updated_at = NOW()`

**Anti-pattern found and eliminated:**
```sql
-- BEFORE (2 queries, 1 table scan):
UPDATE bookings SET status = 'deposit_lapsed', updated_at = NOW() WHERE ...;
INSERT INTO admin_notifications ... SELECT id, user_id FROM bookings WHERE status = 'deposit_lapsed' AND updated_at = NOW();

-- AFTER (1 query, 0 table scans):
UPDATE bookings SET status = 'deposit_lapsed', updated_at = NOW() WHERE ... RETURNING id, user_id;
```

The `updated_at = NOW()` approach relied on transaction-timestamp matching, which is both fragile (race-prone under high concurrency) and expensive (it rescans the table for rows that were just updated). Using `RETURNING` eliminates the second scan entirely and guarantees the inserted rows are exactly those that were updated, regardless of timing. Eviction is BAU, so the `RETURNING` results are returned to the caller for optional use (e.g. future user notification); no admin notification is created.

**Cloud cost impact**: Each eviction saves 1 full table scan. At scale (hundreds of bookings/day), this adds up to thousands of eliminated scans per month.

**Files**: `bookings.go` (`EvictPendingReleaseOverlapping` — shared function used by all 4 eviction sites), `time-blockers.go` (`CleanupExpiredDeposits` — 2 UPDATEs feeding 1 INSERT + 1 DELETE)

### 2. `unnest()` over `JOIN ... WHERE updated_at = NOW()`

**Anti-pattern found in `CleanupExpiredDeposits` and eliminated:**
```sql
-- BEFORE (JOIN on timestamp — fragile, scans time_blockers table):
DELETE FROM time_blockers tb USING bookings b
WHERE (tb.created_by = b.user_id OR ...) AND b.status = 'pending_release' AND b.updated_at = NOW();

-- AFTER (direct ID lookup — O(n) vs O(n*m)):
DELETE FROM time_blockers tb
USING unnest($1::text[]) AS evicted_ids(id)
WHERE tb.created_by = ANY($2::text[]) OR tb.description ILIKE ANY(...);
```

The old approach joined the `time_blockers` table against `bookings` using `updated_at = NOW()` as a tag — a fragile coupling that breaks if clock skew occurs or another operation touches the same rows within the same transaction. The new approach collects evicted IDs from `UPDATE ... RETURNING` and passes them directly.

**Cloud cost impact**: Eliminates the `bookings` table scan from the DELETE query. For a salon with 10,000+ historical bookings, this is a meaningful reduction in I/O per cleanup run.

**Files**: `time-blockers.go` (CleanupExpiredDeposits)

### 3. Transaction batching over row-by-row

Multiple split payment records from a single Square charge are inserted inside one `pgx.Tx` instead of individual `db.DB.Exec` calls. This:
- Reduces network round-trips (1 commit vs N individual inserts)
- Ensures atomicity (if the 2nd record fails, the 1st rolls back)
- Eliminates partial-payment corruption (customer charged £100, DB only shows £50)

**Files**: `handlers.go` (CreateBookingPayment), `service.go` (CreatePaymentRecordTx)

### 4. Combined SELECT over N separate queries

**Example** — `DeleteBookingHandler` previously ran two queries:
```sql
SELECT status FROM bookings WHERE id = $1;       -- query 1
SELECT start_time FROM bookings WHERE id = $1;    -- query 2 (later)
```
Now runs one:
```sql
SELECT status, start_time FROM bookings WHERE id = $1;  -- 1 query
```

**Files**: `bookings.go` (DeleteBookingHandler)

### 5. pg_trgm GIN indexes for ILIKE search

Search queries using `ILIKE '%...%'` with a leading wildcard cannot use standard B-tree indexes. PostgreSQL's `pg_trgm` extension enables GIN indexes with `gin_trgm_ops` that support these searches. The following GIN indexes are defined in `init-script.sql`:

| Table | Columns | Index name |
|-------|---------|------------|
| `users` | `fn`, `email`, `phone` | `idx_users_fn_trgm`, `idx_users_email_trgm`, `idx_users_phone_trgm` |
| `users` | `n_first_name`, `n_last_name` | `idx_users_n_first_name_trgm`, `idx_users_n_last_name_trgm` |
| `custom_services` | `name`, `description` | `idx_custom_services_name_trgm`, `idx_custom_services_desc_trgm` |
| `services` | `name` | `idx_services_name_trgm` |
| `bookings` | `notes` | `idx_bookings_notes_trgm` |
| `images` | `url`, `thumbnail_url` | `idx_images_url_trgm`, `idx_images_thumbnail_url_trgm` |
| `time_blockers` | `description` | `idx_time_blockers_desc_trgm` |

**Deliberately excluded from pg_trgm indexing:**
- `CHAR(12)` ID columns — IDs must never use `ILIKE` search (fixed-width identifiers, exact-match only)
- `booking_status` enum — the `status::text` cast is not `IMMUTABLE`, which PostgreSQL requires for index expressions
- `images.tag_names` (`text[]` array) — `gin_trgm_ops` does not support array types. For ILIKE search on unnested tags at scale, normalise into a separate `image_tags` table

These indexes are used by:
- Admin booking search (`bookings.go:1524`) — searches across user name, email, phone, booking notes
- Admin user search (`profile.go:430`) — user name and email fields
- Custom services search (`custom_services.go:125`) — name + description
- Admin service listing (`services.go`) — service name
- Time-blocker overlap check (`time-blockers.go:689`) — description ILIKE ANY

The `pg_trgm` extension was already enabled; only the indexes were missing. These convert sequential scans into index scans at the cost of additional storage (roughly 1.5× the indexed text column size on disk).

### 6. `parseCursor` deduplication

`parseCursor("createdAt|id")` was previously implemented as 5 identical copies across `bookings.go`, `custom_services.go`, `profile.go`, `notifications.go`, and `images.go`. Deduplicated to a single `validators.ParseCursor()` in `internal/validators/validators.go` with unit tests in `email_test.go`.

### 7. N+1 → batch preloading (services listing)

**Anti-pattern found in `ServicesHandler` and `ServicesEligibleForUserHandler`:**

`checkPatchTestStatus(ctx, userID, serviceID)` was called inside `for rows.Next()` loops, issuing 2 queries per service (one for `patch_tests`, one for `user_patch_tests`). For a salon with 20 services, this meant 40 DB round-trips per page load.

**Fix:** A new `loadPatchTests(ctx, userID) map[string]*patchTestInfo` function does 2 total queries before the loop. `checkPatchTestStatus` now accepts the preloaded map and performs O(1) in-memory lookups.

**Cloud cost impact**: Eliminates 2N-2 queries per service listing. For 20 services: 40 queries → 2.

**Files**: `services.go` (ServicesHandler, ServicesEligibleForUserHandler, checkPatchTestStatus)

### 8. 14-day loop → single CTE+LATERAL query (today dashboard)

**Anti-pattern found in `findNewBookingServices`:**

A `for i := 1; i <= 14; i++` loop issued 2 queries per iteration (exceptional hours, then default working hours) to find the most recent open day's closing time. Max 28 round-trips per dashboard load.

**Fix:** A single SQL query using `generate_series(1, 14)` as a CTE, with `LEFT JOIN LATERAL` for both exceptional and default hours, `COALESCE` to prefer exceptional over defaults, and `ORDER BY day_date DESC LIMIT 1` to pick the most recent open day. Same fallback (`yesterday 5pm`) preserved in Go.

**Cloud cost impact**: 28 queries → 1 per dashboard load. Every admin "Today" page view hits this.

**Files**: `today.go` (findNewBookingServices)

### 9. 9→1 aggregate queries (today dashboard)

**Anti-pattern found in `computeAggregateSummary`:**

Nine separate `db.DB.QueryRow()` calls each scanned the same booking/payment/gift card tables over the same `startDate ≤ x < endDate` range. This ran on every admin dashboard load.

**Fix:** Single combined query with 11 independent subquery columns sharing the same `$1, $2` parameters. `COALESCE(..., 0)` handles nulls for all numerics; `COUNT(DISTINCT b.id)` prevents overcounting visits when a booking has multiple payments.

| Aggregate | Original | After |
|-----------|----------|-------|
| Payments (non-tip) | 1 query | — |
| Tips | 1 query | — |
| Amount due | 1 query | — |
| Duration spent | 1 query | — |
| Total bookings | 1 query | — |
| New customers | 1 query | — |
| Returning customers | 1 query | — |
| Gift cards sold | 1 query | — |
| Last customer name | 1 query | — |
| Last customer visits | 1 query | — |
| Guest customers | 1 query | — |
| **Total** | **9 queries** | **1 query** |

**Cloud cost impact**: 9 queries → 1 per dashboard load. Every admin dashboard page view.

**Files**: `today.go` (computeAggregateSummary)

### 10. N+1 → `ANY($1)` batch (booking service duration)

**Anti-pattern found in `UpdateBookingServicesHandler`:**

A `for _, serviceID := range req.ServiceIDs` loop issued individual `SELECT duration_minutes FROM services WHERE id = $1` per service to look up durations.

**Fix:** A single `SELECT id, duration_minutes FROM services WHERE id = ANY($1)` before the loop, stored in a `map[string]int`. In-loop lookups use the map instead of the database. Same 400 error raised for missing services.

**Cloud cost impact**: N queries → 1 per booking edit. Impact multiplies with the number of services per booking.

**Files**: `bookings.go` (UpdateBookingServicesHandler)

### 11. `UPDATE ... RETURNING` for loyalty stamps

**Anti-pattern found in `ProgressBookingHandler`:**

On booking completion, the code ran:
1. `UPDATE users SET loyalty_stamps = stamps + 1 WHERE id = $1`
2. `SELECT loyalty_stamps FROM users WHERE id = $1` (to check if stamps hit 10)

**Fix:** Added `RETURNING loyalty_stamps` to the UPDATE, eliminating the separate SELECT. The RETURNING value is scanned directly into `newStampCount` and used for the `== 10` redemption trigger.

**Cloud cost impact**: 1 query eliminated per booking completion. For high-volume salons, hundreds of queries/month.

**Files**: `bookings.go` (ProgressBookingHandler)

### 12. 3→1 combined query with FILTER (customer relationship)

**Anti-pattern found in `GetCustomerRelationshipHandler`:**

Three separate `db.DB.QueryRow()` calls computed total spend, total saved (discounts), and tips+visits+dates — each joining the same `bookings` and `payments` tables for the same user.

**Fix:** Single query using `LEFT JOIN bookings → payments` with `FILTER` clauses on each aggregate. `COUNT(DISTINCT b.id)` prevents double-counting visits when a booking has multiple payments. Key design decision: a single join with FILTER instead of two LEFT JOINs (which would cause row multiplication).

**Query design:**
```sql
SELECT
  COALESCE(SUM(p.amount) FILTER (WHERE ... AND payment_method != 'discount'), 0),  -- total_spend
  COALESCE(SUM(p.amount) FILTER (WHERE ... AND payment_method = 'discount'), 0),   -- total_saved
  COALESCE(SUM(p.amount) FILTER (WHERE payment_type = 'tip'), 0),                  -- total_tips
  COUNT(DISTINCT b.id) FILTER (WHERE b.status = 'completed'),                      -- total_visits
  MIN(b.start_time) FILTER (WHERE b.status = 'completed'),                         -- first_visit
  MAX(b.start_time) FILTER (WHERE b.status = 'completed')                          -- last_visit
FROM bookings b LEFT JOIN payments p ON p.booking_id = b.id WHERE b.user_id = $1;
```

**Cloud cost impact**: 3 queries → 1 per admin customer lookup. Accessed when viewing any customer's profile.

**Files**: `customer_relationship.go` (GetCustomerRelationshipHandler)

---

### Background Jobs Scheduler

**Package**: `backend/internal/jobs/` — `Scheduler` struct wrapping `github.com/robfig/cron/v3` with per-job concurrency control, timeout, panic recovery, and logging.

**How it works:** All background data maintenance jobs are registered in `RegisterAll()` (`cleanup.go`) with cron expressions. Each job runs in its own goroutine on schedule. Jobs with `Concurrency: 1` skip a tick if the previous run is still in-flight. Graceful shutdown via `sched.Shutdown()` which cancels the base context and waits for in-flight jobs to complete.

**Job catalogue** (all defined in `backend/internal/jobs/cleanup.go`):

| Frequency | Jobs | Cron |
|-----------|------|------|
| Every min | Progressive rate limiter cleanup | `* * * * *` |
| Every 5 min | Reservation cleanup, expired deposits, rate limiter cleanup, GDPR cache cleanup | `*/5 * * * *` |
| Hourly | Loyalty redemptions, idempotency keys, revoked JTIs, stale login entries, discount campaign auto-transition | `0 * * * *` |
| Daily 7am | Unpaid booking notifications (1-week and 1-month overdue) | `0 7 * * *` |
| Daily 2am | Expired verification codes, expired/revoked refresh tokens | `0 2 * * *` |
| Daily 3am | Stale guest account anonymization | `0 3 * * *` |
| Daily 3:30am | Idle account cleanup | `30 3 * * *` |
| Daily 4am | Financial record aggregation + deletion | `0 4 * * *` |
| Daily 4:30am | Name history cleanup | `30 4 * * *` |
| Daily 5am | Expired gift cards | `0 5 * * *` |

**Previously lazy (side-effects in `GetAvailableHours`):** All cleanup functions were called synchronously on every `GET /api/scheduling/available-hours` request. They are now extracted into the cron scheduler, removing ~43 lines of side-effect code from the HTTP handler.

**Previously ad-hoc goroutines migrated:**

| Old Location | Old Pattern | New Home |
|-------------|-------------|----------|
| `main.go:432-450` | `time.NewTicker(5min)` + `go func()` | `jobs.CleanupOldReservations` |
| `auth/jwt.go:108-124` | `StartJTICleanup()` — `time.NewTicker(30min)` | `jobs.CleanupRevokedJTIs` (hourly) |
| `handlers/user/gdpr_export.go:27-48` | `init()` + `time.NewTicker(5min)` | `user.CleanupGDPRExportCache` |
| `handlers/auth/local.go:42-65` | `init()` + `time.NewTicker(1h)` | `authHandlers.CleanupStaleLoginEntries` |
| `mw/ratelimit.go:31-43` | `NewRateLimiter()` + `go func()` | `mw.CleanupAllRateLimiters` |
| `mw/ratelimit.go:104-116` | `NewProgressiveRateLimiter()` + `go func()` | `mw.CleanupProgressiveRateLimiter` |

### Summary

| Pattern | Queries eliminated | Risk replaced | Cloud cost |
|---------|-------------------|---------------|------------|
| `UPDATE ... RETURNING` | 3 per eviction (1 scan each) | Timestamp race | Lower I/O → lower RDS cost |
| `unnest()` over JOIN | 1 table scan per cleanup | Clock-skew fragility | Lower I/O per cron run |
| Transaction batching | N-1 round-trips per payment | Partial-corruption bug | Same I/O, safer |
| Combined SELECT | 1 per cancellation | N/A | Marginal CPU save |
| pg_trgm GIN indexes | N/A — converts seq scans to index scans | Full-table scans under ILIKE | Higher insert cost, much lower read cost |
| N+1 → batch preload (services) | 2N-2 per listing | N/A | 40→2 queries @ 20 services |
| Loop → CTE+LATERAL (today) | 27 per dashboard | N/A | 28→1 queries per dashboard load |
| 9→1 aggregates (today) | 8 per dashboard | N/A | 9→1 queries per dashboard load |
| `ANY($1)` batch (bookings) | N-1 per edit | N/A | N→1 queries per service edit |
| `RETURNING` loyalty stamps | 1 per completion | N/A | 2→1 queries per booking completion |
| 3→1 FILTER query (customer) | 2 per lookup | Row-multiplication bug | 3→1 queries per admin lookup |
