Architecture Review Document

OpenRally Architecture

Technical architecture for the OpenRally Protocol reference implementation in Setpoint. Covers system design, database schema, API routing, SSE push, Google Reserve integration, caching, security, and deployment.

Shippedv1.0Updated 2026-03-06

System Components

Component	Responsibility	Technology
API Gateway	Auth, rate limiting, request validation, response caching	`Next.js API routes`
Discovery Service	Facility and resource lookup, sport registry	`REST + DB queries`
Availability Service	Slot generation, availability queries, conflict detection	`REST + DB + cache`
Booking Engine	Hold management, booking CRUD, state machine enforcement	`REST + DB transactions`
ID Registry	or:// URI generation, alias mapping, provider registration	`DB-backed registry`
Event Bus	Capture state changes, fan out to SSE connections	`Supabase Realtime → SSE`
Google Feed Generator	Export Merchant/Service/Availability feeds for Actions Center	`Cron + feed formatter`

Database Schema (11 tables)

or_providersnewRegistered namespace owners (e.g. 'setpoint', 'playtomic')

id (UUID PK)namespace (TEXT UNIQUE)nameurlapi_base_urlauth_methods (JSONB)capabilities (JSONB)is_activecreated_at

or_sportsnewCanonical sport registry with extensible IDs

id (UUID PK)canonical_id (TEXT UNIQUE)namecategoryis_core (BOOL)provider_id (FK)created_at

or_facilities (view)modifyMaps to existing facilities table + or_uri column

idprovider_idor_uri (TEXT UNIQUE)nameaddress_*geo_lat/lngtimezoneoperating_hours (JSONB)is_active

or_resources (view)modifyMaps to existing courts table + or_uri column

idfacility_id (FK)or_uri (TEXT UNIQUE)nameresource_typesurfaceis_indooris_active

or_resource_sportsnewJunction table: which sports a resource (court) supports

resource_id (FK)sport_id (FK)is_primary (BOOL)

or_slotsnewTime-based availability units — computed from schedules minus bookings

id (UUID PK)resource_id (FK)or_uri (TEXT UNIQUE)sport_id (FK)start_timeend_timeduration_minstatus (available|held|booked|blocked)price_amountcurrency

or_holdsnewTime-limited leases on slots with TTL enforcement

id (UUID PK)slot_id (FK)or_uri (TEXT UNIQUE)consumer_idconsumer_typestatus (active|confirmed|released|expired)idempotency_key (UNIQUE)expires_atcreated_at

or_bookings (view)modifyMaps to existing bookings table + or_uri column

idslot_id (FK)hold_id (FK)or_uri (TEXT UNIQUE)status (confirmed|completed|cancelled)customer_name/email/phoneplayers (JSONB)source_channelidempotency_keycreated_at

or_aliasesnewCross-platform identity mapping (same facility on multiple providers)

id (UUID PK)entity_typeprimary_urialias_uriprovider_id (FK)

or_eventsnewAppend-only event log — powers SSE replay, Google push, and cache invalidation

id (BIGSERIAL PK)event_typefacility_id (FK)resource_id (FK)entity_idpayload (JSONB)is_durable (BOOL)created_at

or_google_feed_exportsnewTracks Google feed export history and status

id (UUID PK)feed_type (merchant|service|availability)facility_id (FK)exported_atrecord_countstatuserror_message

Setpoint Database Mapping

Adapter pattern — OpenRally endpoints query existing Setpoint tables and transform results. No data duplication.

OpenRally	Setpoint Table	Strategy
`or_facilities`	`facilities`	Add or_uri column — 1:1 mapping
`or_resources`	`courts`	Add or_uri column — 1:1 mapping
`or_slots`	`court_schedules + bookings`	Computed — generate from schedules, subtract bookings
`or_holds`	`bookings (status='held')`	Map held bookings → OpenRally holds
`or_bookings`	`bookings (status='confirmed')`	Map confirmed bookings → OpenRally bookings
`or_sports`	`New lookup table`	Seed with 8 core sports
`or_providers`	`New table`	Seed with 'setpoint' as first provider
`or_events`	`New table`	DB triggers on bookings/slots write events

API Routes (20 total)

Path	Methods	Description
`/api/openrally/v1/`	GET	Protocol metadata + capabilities
`/api/openrally/v1/facilities`	GET	List facilities
`/api/openrally/v1/facilities/[id]`	GET	Facility details
`/api/openrally/v1/facilities/[id]/resources`	GET	List courts/resources
`/api/openrally/v1/facilities/[id]/resources/[id]`	GET	Resource details
`/api/openrally/v1/facilities/[id]/availability`	GET	Query available slots
`/api/openrally/v1/facilities/[id]/pricing`	GET	Get pricing for a specific slot
`/api/openrally/v1/sports`	GET	List sports
`/api/openrally/v1/holds`	POST	Create hold
`/api/openrally/v1/holds/[id]`	GET, DELETE	Get/release hold
`/api/openrally/v1/bookings`	GET, POST	List/create bookings
`/api/openrally/v1/bookings/[id]`	GET, PATCH, DELETE	Get/modify/cancel booking
`/api/openrally/v1/stream`	GET	SSE event stream
`/api/google/v3/HealthCheck`	GET	Google health check
`/api/google/v3/CheckAvailability`	POST	Google availability check
`/api/google/v3/BatchAvailabilityLookup`	POST	Google batch availability
`/api/google/v3/CreateBooking`	POST	Google create booking
`/api/google/v3/UpdateBooking`	POST	Google update booking
`/api/google/v3/GetBookingStatus`	POST	Google booking status
`/api/google/v3/ListBookings`	POST	Google list bookings

Amber rows = Google Booking Server (adapter over OpenRally endpoints)

Google Reserve Integration

OpenRally	Google Concept	Mapping
`or_facilities`	`Merchant Feed`	or_uri → merchant_id, geo/address/name direct
`Sport booking service`	`Service Feed`	One service per sport per facility
`or_slots (available)`	`Availability Feed`	start_time → start_sec (epoch), duration → duration_sec, court → room_id
`or_bookings`	`Booking Status`	confirmed → CONFIRMED, cancelled → CANCELED
`POST /holds + /bookings`	`CreateBooking`	Google calls → we create hold + confirm atomically
`PATCH /bookings/{id}`	`UpdateBooking`	Google calls → we modify booking
`GET /bookings/{id}`	`GetBookingStatus`	Google calls → we return booking status

SSE Push Architecture

DB Trigger

INSERT/UPDATE on bookings/slots

or_events

Append-only event log

Supabase Realtime

Listens to inserts

SSE Adapter

Formats + filters + sends

Event Bus: DB trigger → or_events table → Supabase Realtime → SSE stream to clients

Caching Strategy

Endpoint	Cache-Control	Rationale
`GET /`	`max-age=3600`	Metadata rarely changes
`GET /facilities`	`max-age=3600, stale-while-revalidate=600`	Facility list changes rarely
`GET /availability`	`max-age=30, stale-while-revalidate=60`	Critical freshness — 30s max
`GET /sports`	`max-age=86400`	Effectively static
`GET /holds/{id}`	`no-store`	Time-critical, never cache
`GET /bookings/*`	`no-cache`	Must revalidate every time
`GET /stream`	`no-cache`	SSE — never cache

Security Model

Layer 1

API Key (X-API-Key)

All requests. Format: ork_live_{32-char}. SHA-256 hashed in DB.

Layer 2

OAuth 2.0 (optional)

Channel managers. Client Credentials grant for elevated access.

Layer 3

Google Basic Auth

Google Booking Server endpoints only. Credentials rotated every 6 months.

Migration Path (6 weeks)

Schema

Week 1

Create new tables: or_providers, or_sports, or_events, or_aliases, or_google_feed_exports
Add or_uri columns to facilities and courts
Backfill or_uri values for existing records
Create triggers for auto-generation of or_uri on insert
Create event emission triggers on bookings/slots
Seed or_sports with 8 core sports, or_providers with 'setpoint'

Core Endpoints

Week 2-3

Protocol metadata endpoint (GET /)
Discovery: facilities, resources, sports
Availability: query with date/sport/time/duration filters
Holds: create, get, release with advisory locks
Bookings: create (from hold + direct), get, modify, cancel, list
Auth layer, rate limiting, error formatting

Push + Google

Week 4-5

SSE stream endpoint with Supabase Realtime adapter
Event replay via or_events + Last-Event-ID
Heartbeat (30s) and reconnection handling
Google Merchant/Service/Availability feed generators (daily cron)
Google Booking Server (7 adapter endpoints)
Maps Booking API client for real-time push to Google

Test + Certify

Week 6

Compliance test suite against Setpoint
E2E tests for OpenRally + Google endpoints
Performance benchmarks (availability < 200ms p50)
Self-certification at OpenRally Full level
OpenAPI 3.1 spec + documentation

Open Questions

Should slots be materialized or computed on-the-fly?

Options: Materialized (faster) vs. Computed (always fresh)Impact: Performance vs. freshness

SSE stream: Vercel or Supabase Edge?

Options: Vercel streaming (simpler, 300s limit) vs. Edge Function (unlimited)Impact: SSE reliability

Slot generation for facilities without schedules?

Options: Require schedules vs. default 7am-11pm with 1hr slotsImpact: Onboarding friction

Google Actions Center onboarding timing?

Options: Start now (12-16 weeks) vs. after protocol v1.0Impact: Time to market