Database Schema — OneTwo v3

Design Principles

Short String IDs

All primary keys use custom short string IDs (max 15 chars) via BaseEntity. No auto-increment integers exposed to clients.

DATETIMEOFFSET

All timestamps use DATETIMEOFFSET for timezone awareness. Application operates in Asia/Riyadh (UTC+3), no DST.

DB-Level Enforcement

Critical invariants enforced by SQL Server constraints, triggers, and unique indexes — not just application code.

Full Audit Trail

Immutable append-only audit_logs table. Every mutation recorded.

Critical Constraints

Double-booking prevention must be enforced at the database level. In SQL Server, use a combination of a UNIQUE index on (field_id, date, start_time) plus an INSTEAD OF INSERT trigger (or serializable transaction) that checks for overlapping time ranges. Even if application code has a bug, the database must reject conflicting inserts.

Constraint	Table	Purpose
`UNIQUE INDEX + Trigger`	`bookings`	Prevents overlapping bookings on same field + time range. Non-negotiable.
`NOT NULL + CHECK(price > 0)`	`bookings`	Every booking must have a price. v2 had 58.3% missing.
Short string IDs (`BaseEntity`)	All tables	Custom short IDs (max 15 chars). No auto-increment integers exposed.
`DATETIMEOFFSET`	All timestamp columns	Timezone-aware timestamps (UTC+3).
Enums as strings	Status columns	EF Core `HasConversion<string>()` + CHECK constraints for valid values.
`UNIQUE(field_id, date, start_time)`	`booking_holds`	Only one hold per slot at a time.
`INSERT ONLY` (no UPDATE/DELETE)	`audit_logs`	Immutable audit trail. Enforced via trigger or application policy.

Enum Types Stored as Strings

All enums are stored as NVARCHAR columns using EF Core's HasConversion<string>(). The application defines 18 enum types.

Enum	Values
`booking_status`	booked confirmed completed cancelled no_show
`payment_status`	unpaid partial paid refunded
`booking_source`	admin fm customer contract guest
`user_role`	admin executive field_manager customer
`notification_status`	pending sent delivered failed
`sport_type`	football volleyball padel
`schedule_rule_type`	add_slots remove_slots modify_price modify_time

Tables by Domain

Location Hierarchy

cities → locations → fields — one city has many locations, one location has many fields.

Table	Key Columns	Purpose
`cities`	id, name_ar, name_en, is_active	Top-level geographic grouping (e.g., Buraydah, Unayzah)
`locations`	id, city_id, name_ar, name_en, lat, lng, address, operating_hours	Physical venue with GPS coordinates. One location can have multiple fields.
`fields`	id, location_id, name_ar, name_en, sport_type, is_active, base_price, schedule_profile_id	Individual playing field. Has a sport type, base price, and assigned schedule profile.

Scheduling

Template-based system replacing v2's unsustainable 4-level cascade. A profile contains ordered rules that resolve available slots.

Table	Key Columns	Purpose
`schedule_profiles`	id, name, description, is_default	Named template assigned to fields. Contains a set of rules.
`schedule_rules`	id, profile_id, rule_type, priority, days_of_week, date_from, date_to, start_time, end_time, slot_duration, price_override	Individual rule with numeric priority. Higher priority wins conflicts. Types: add_slots, remove_slots, modify_price, modify_time.
`prayer_times`	id, city_id, date, fajr, zuhr, asr, maghrib, isha	Daily prayer times per city. Schedule rules can reference these with offsets.

Booking Lifecycle

Table	Key Columns	Purpose
`bookings`	id, field_id, customer_id, creator_id, date, start_time, end_time, status, source, price, payment_status, cancellation_reason, notes	Central table. State machine (5 statuses). UNIQUE index + trigger prevents overlaps. Price is mandatory.
`booking_holds`	id, field_id, date, start_time, end_time, held_by, expires_at	5-minute slot reservations during form completion. Expired by background job.
`tickets`	id, booking_id, slot_date, slot_start, slot_end	Individual time slots within a booking (for multi-slot bookings).

Contracts (Recurring Bookings)

Table	Key Columns	Purpose
`contracts`	id, customer_id, creator_id, start_date, end_date, is_active, total_price, notes	Recurring booking agreement. Generates bookings automatically.
`contract_days`	id, contract_id, field_id, day_of_week, start_time, end_time, price, is_enabled	Per-day configuration. Toggle days on/off without recreating the contract.

Users & Authentication

Table	Key Columns	Purpose
`users`	id, phone, email, name, role, pin_hash, is_active, assigned_field_ids	All roles in one table. Phone is the primary identifier for customers. FM scoped via assigned_field_ids.
`otp_codes`	id, phone, code, expires_at, is_used, attempts	6-digit OTP codes. 5-minute expiry. Max 3 per 15 minutes per phone.
`sessions`	id, user_id, token, ip_address, user_agent, expires_at	Active sessions for web (cookie) and mobile (JWT).
`login_attempts`	id, phone_or_email, attempt_type, ip_address, success, created_at	Track login attempts for rate limiting and security.

Financial

Table	Key Columns	Purpose
`payments`	id, booking_id, amount, method, recorded_by, vat_amount, notes	Cash payments at launch. Gateway-ready for future. First payment auto-confirms booking.
`refunds`	id, payment_id, amount, reason, processed_by	Linked to original payment. Tracks who processed and why.

Communication

Table	Key Columns	Purpose
`notifications`	id, user_id, booking_id, template_id, channel, status, sent_at, delivered_at, provider, external_id	Outbound notifications (WhatsApp). Tracks delivery status and provider used.
`notification_templates`	id, key, name_ar, body_ar, variables	4 core templates: booking_created, booking_confirmed, 24h_reminder, booking_cancelled.
`incoming_messages`	id, from_phone, body, received_at, is_processed	WhatsApp incoming messages via WAHA webhook. For future bidirectional messaging.

Audit & Customer

Table	Key Columns	Purpose
`audit_logs`	id, user_id, entity_type, entity_id, action, field_name, old_value, new_value, reason, created_at	Immutable append-only. No UPDATE or DELETE allowed. Records every data mutation.
`customer_contacts`	id, phone, name, notes, created_by	Phone-based customer lookup for autocomplete during booking creation.

Deferred Tables Schema Only

These tables exist in the schema but are feature-flagged off at launch. Create the migrations but do not build the application logic.

Table	Purpose
`captains`	Team captains who organize groups of players.
`teams`	Teams associated with captains.
`team_members`	Members belonging to teams.
`booking_attendance`	Check-in tracking for individual bookings.

Key Relationships

// Location hierarchy
City  1 ──> * Location  1 ──> * Field

// Field scheduling
Field * ──> 1 ScheduleProfile  1 ──> * ScheduleRule

// Booking core
Field    1 ──> * Booking
Customer 1 ──> * Booking
User     1 ──> * Booking  (as creator)
Booking  1 ──> * Ticket
Booking  1 ──> * Payment
Payment  1 ──> * Refund

// Contracts
Customer 1 ──> * Contract
Contract 1 ──> * ContractDay
Contract 1 ──> * Booking  (generated bookings)

// Notifications
Booking  1 ──> * Notification
User     1 ──> * Notification
Template 1 ──> * Notification

// Audit
User 1 ──> * AuditLog  (who made the change)

Full schema specification: See design/SCHEMA.md in the repository for complete column definitions and indexes. The EF Core migration in OneTwoApi/Migrations/ shows the current implemented schema.