Database Schema - Football Field Booking System

Version: 1.0 Last Updated: 2026-01-13 Owner: Tech Lead Datastores: PostgreSQL (4 service DBs + Keycloak DB), MongoDB (notification logs), Redis (cache + locks)

---

1) Overview

The system uses a database-per-service model:

• User Service DB (PostgreSQL): users, profiles, (optional) player profile/rating entities
• Field Service DB (PostgreSQL): fields, amenities, images, operating hours
• Booking Service DB (PostgreSQL): bookings, booking_participants, receipts/outbox (optional)
• Payment Service DB (PostgreSQL): payments, payment_transactions, idempotency keys (optional)
• Keycloak DB (PostgreSQL): Keycloak internal tables (managed by Keycloak)
• Notification Service (MongoDB): notification delivery logs and provider responses
• Redis: caches, rate limiting counters, booking distributed locks

This document is consistent with the repository reference schema in requirements/DATABASE_SCHEMA.md and extends it with:

• A consolidated ERD (mermaid)
• Service-local schemas with constraints, indexes, and example queries
• MongoDB & Redis design details

---

2) ERD (Logical Model)

erDiagram
  USERS ||--o{ USER_PROFILES : has
  USERS ||--o{ FIELDS : owns
  USERS ||--o{ BOOKINGS : creates
  FIELDS ||--o{ BOOKINGS : booked_for
  BOOKINGS ||--|| PAYMENTS : paid_by

  USERS {
    uuid id PK
    varchar email "unique"
    varchar name
    varchar phone
    varchar role
    varchar status
    timestamptz created_at
    timestamptz updated_at
  }

  USER_PROFILES {
    uuid id PK
    uuid user_id FK "unique"
    varchar avatar_url
    jsonb preferences
    timestamptz created_at
    timestamptz updated_at
  }

  FIELDS {
    uuid id PK
    uuid owner_id "user id"
    varchar name
    varchar location
    numeric hourly_rate
    int capacity
    varchar surface_type
    varchar status
    numeric latitude
    numeric longitude
    timestamptz created_at
    timestamptz updated_at
  }

  BOOKINGS {
    uuid id PK
    uuid field_id FK
    uuid user_id FK
    timestamptz start_time
    timestamptz end_time
    numeric total_price
    varchar status
    text notes
    timestamptz created_at
    timestamptz updated_at
  }

  PAYMENTS {
    uuid id PK
    uuid booking_id FK "unique"
    numeric amount
    varchar currency
    varchar payment_method
    varchar status
    varchar provider_transaction_id "unique nullable"
    timestamptz created_at
    timestamptz updated_at
  }

---

3) PostgreSQL Schemas (By Service)

Notes:

• All timestamps use UTC (timestamptz recommended).
• Primary keys are UUID.
• For enum columns, you can use Postgres ENUM types or VARCHAR with CHECK constraints. This doc uses ENUM for clarity.
• Each service runs migrations only against its own DB.

3.1 User Service (PostgreSQL)

3.1.1 Enums

CREATE TYPE user_role AS ENUM ('ADMIN', 'CUSTOMER', 'OWNER');
CREATE TYPE user_status AS ENUM ('ACTIVE', 'INACTIVE', 'BANNED');

3.1.2 users

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  phone VARCHAR(20) NULL,

  -- If Keycloak fully manages password, keep this nullable; otherwise required.
  password_hash VARCHAR(255) NULL,

  -- For ZNS integration (see repo docs)
  zalo_user_id UUID NULL UNIQUE,

  role user_role NOT NULL DEFAULT 'CUSTOMER',
  status user_status NOT NULL DEFAULT 'ACTIVE',

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_zalo_user_id ON users(zalo_user_id);

3.1.3 user_profiles

CREATE TABLE user_profiles (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL UNIQUE,

  avatar_url TEXT NULL,
  preferences JSONB NOT NULL DEFAULT '{}'::jsonb,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_user_profiles_user
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_user_profiles_user_id ON user_profiles(user_id);

3.1.4 Optional: Player Profile (Phase 2 / Thesis Weeks 9–10)

If implementing player ranking and ratings (consistent with requirements/DATABASE_SCHEMA.md):

CREATE TABLE player_profiles (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL UNIQUE,
  total_games_played INT NOT NULL DEFAULT 0,
  total_wins INT NOT NULL DEFAULT 0,
  total_losses INT NOT NULL DEFAULT 0,
  total_draws INT NOT NULL DEFAULT 0,
  total_goals_scored INT NOT NULL DEFAULT 0,
  total_goals_conceded INT NOT NULL DEFAULT 0,
  average_rating DECIMAL(3,2) NOT NULL DEFAULT 0,
  joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_player_profiles_user
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_player_profiles_avg_rating ON player_profiles(average_rating);
CREATE INDEX idx_player_profiles_games ON player_profiles(total_games_played);

CREATE TABLE player_ratings (
  id UUID PRIMARY KEY,
  rated_player_id UUID NOT NULL,
  rater_player_id UUID NOT NULL,
  booking_id UUID NOT NULL,
  rating INT NOT NULL CHECK (rating >= 1 AND rating <= 5),
  comment TEXT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_player_ratings_rated
    FOREIGN KEY (rated_player_id) REFERENCES player_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_player_ratings_rater
    FOREIGN KEY (rater_player_id) REFERENCES player_profiles(id) ON DELETE CASCADE,
  CONSTRAINT uq_rating_once_per_booking
    UNIQUE (rated_player_id, rater_player_id, booking_id)
);

CREATE INDEX idx_player_ratings_rated ON player_ratings(rated_player_id);
CREATE INDEX idx_player_ratings_rater ON player_ratings(rater_player_id);

---

3.2 Field Service (PostgreSQL)

3.2.1 Enums

CREATE TYPE field_status AS ENUM ('ACTIVE', 'INACTIVE', 'MAINTENANCE', 'PENDING_APPROVAL');
CREATE TYPE surface_type AS ENUM ('GRASS', 'SYNTHETIC', 'CONCRETE');

3.2.2 fields

CREATE TABLE fields (
  id UUID PRIMARY KEY,
  owner_id UUID NOT NULL,

  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  location VARCHAR(255) NOT NULL,

  latitude DECIMAL(10,8) NULL,
  longitude DECIMAL(11,8) NULL,

  hourly_rate DECIMAL(10,2) NOT NULL,
  capacity INT NOT NULL DEFAULT 22,

  surface_type surface_type NOT NULL DEFAULT 'GRASS',
  status field_status NOT NULL DEFAULT 'PENDING_APPROVAL',

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_fields_owner_id ON fields(owner_id);
CREATE INDEX idx_fields_location ON fields(location);
CREATE INDEX idx_fields_status ON fields(status);

3.2.3 field_amenities (Optional normalization)

CREATE TABLE field_amenities (
  id UUID PRIMARY KEY,
  field_id UUID NOT NULL,
  amenity_code VARCHAR(64) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_field_amenities_field
    FOREIGN KEY (field_id) REFERENCES fields(id) ON DELETE CASCADE,
  CONSTRAINT uq_field_amenity UNIQUE (field_id, amenity_code)
);

CREATE INDEX idx_field_amenities_field_id ON field_amenities(field_id);

3.2.4 field_images (Optional)

CREATE TABLE field_images (
  id UUID PRIMARY KEY,
  field_id UUID NOT NULL,
  url TEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_field_images_field
    FOREIGN KEY (field_id) REFERENCES fields(id) ON DELETE CASCADE
);

CREATE INDEX idx_field_images_field_id ON field_images(field_id);

---

3.3 Booking Service (PostgreSQL)

3.3.1 Enums

CREATE TYPE booking_status AS ENUM ('PENDING', 'CONFIRMED', 'COMPLETED', 'CANCELLED');

3.3.2 bookings

CREATE TABLE bookings (
  id UUID PRIMARY KEY,
  field_id UUID NOT NULL,
  user_id UUID NOT NULL,
  start_time TIMESTAMPTZ NOT NULL,
  end_time TIMESTAMPTZ NOT NULL,
  total_price DECIMAL(10,2) NOT NULL,
  status booking_status NOT NULL DEFAULT 'PENDING',
  notes TEXT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT check_booking_times CHECK (start_time < end_time)
);

CREATE INDEX idx_bookings_field_id ON bookings(field_id);
CREATE INDEX idx_bookings_user_id ON bookings(user_id);
CREATE INDEX idx_bookings_start_time ON bookings(start_time);
CREATE INDEX idx_bookings_status ON bookings(status);

Cross-service FK note: In strict database-per-service, you typically avoid FK across DBs. The reference schema in requirements/DATABASE_SCHEMA.md shows cross-table FKs for clarity. In production, enforce referential integrity at application level or via shared IDs and validation calls.

3.3.3 booking_outbox (Recommended, Phase 2)

CREATE TABLE booking_outbox (
  id UUID PRIMARY KEY,
  aggregate_id UUID NOT NULL, -- bookingId
  event_type VARCHAR(128) NOT NULL,
  payload JSONB NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'PENDING', -- PENDING, PUBLISHED, FAILED
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  published_at TIMESTAMPTZ NULL
);

CREATE INDEX idx_booking_outbox_status ON booking_outbox(status);
CREATE INDEX idx_booking_outbox_created_at ON booking_outbox(created_at);

---

3.4 Payment Service (PostgreSQL)

3.4.1 Enums

CREATE TYPE payment_status AS ENUM ('PENDING', 'SUCCESS', 'FAILED', 'REFUNDED');
CREATE TYPE payment_method AS ENUM ('CREDIT_CARD', 'BANK_TRANSFER', 'E_WALLET', 'STRIPE', 'VNPAY');

3.4.2 payments

CREATE TABLE payments (
  id UUID PRIMARY KEY,
  booking_id UUID NOT NULL UNIQUE,
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) NOT NULL DEFAULT 'VND',
  payment_method payment_method NOT NULL,
  status payment_status NOT NULL DEFAULT 'PENDING',

  -- Provider references
  provider_transaction_id VARCHAR(255) NULL UNIQUE,
  provider_payment_intent_id VARCHAR(255) NULL UNIQUE,

  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_payments_booking_id ON payments(booking_id);
CREATE INDEX idx_payments_status ON payments(status);
CREATE INDEX idx_payments_created_at ON payments(created_at);

3.4.3 payment_transactions (Optional)

CREATE TABLE payment_transactions (
  id UUID PRIMARY KEY,
  payment_id UUID NOT NULL,
  provider VARCHAR(32) NOT NULL, -- STRIPE, VNPAY, etc.
  provider_event_id VARCHAR(255) NULL UNIQUE,
  type VARCHAR(64) NOT NULL, -- AUTH, CAPTURE, REFUND, WEBHOOK
  status VARCHAR(32) NOT NULL, -- SUCCESS/FAILED/PENDING
  raw_payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_payment_transactions_payment
    FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE CASCADE
);

CREATE INDEX idx_payment_transactions_payment_id ON payment_transactions(payment_id);
CREATE INDEX idx_payment_transactions_created_at ON payment_transactions(created_at);

3.4.4 idempotency_keys (Recommended)

CREATE TABLE idempotency_keys (
  id UUID PRIMARY KEY,
  key VARCHAR(128) NOT NULL UNIQUE,
  scope VARCHAR(64) NOT NULL, -- PAYMENTS_CREATE, REFUND_CREATE, etc.
  request_hash VARCHAR(128) NOT NULL,
  response_body JSONB NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'IN_PROGRESS', -- IN_PROGRESS/COMPLETED/FAILED
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX idx_idempotency_keys_expires_at ON idempotency_keys(expires_at);

---

4) MongoDB Schemas (Notification Service)

MongoDB stores notification delivery logs (append-only) to avoid impacting OLTP Postgres DBs.

4.1 Collection: notification_logs

Suggested schema (document shape)

{
  "_id": "ObjectId",
  "messageId": "uuid",
  "userId": "uuid",
  "channel": "EMAIL|SMS|ZNS|PUSH",
  "type": "BOOKING_CONFIRMED|BOOKING_REMINDER|PAYMENT_SUCCESS|PLAYER_RATED",
  "status": "QUEUED|SENT|FAILED|RETRYING",
  "provider": "SENDGRID|AWS_SES|ZALO|TWILIO|...",
  "to": "user@example.com",
  "templateId": "booking_confirmed_v1",
  "payload": { "bookingId": "uuid", "fieldName": "..." },
  "providerResponse": { "raw": {} },
  "error": { "code": "PROVIDER_TIMEOUT", "message": "..." },
  "traceId": "trace_abc",
  "requestId": "req_123",
  "createdAt": "2026-01-13T10:00:00Z",
  "updatedAt": "2026-01-13T10:00:10Z"
}

Indexes

• { userId: 1, createdAt: -1 } for user-centric views
• { status: 1, createdAt: -1 } for retry workers
• { type: 1, createdAt: -1 } for analytics

---

5) Redis Key Patterns (Cache + Locks)

5.1 Caching

Purpose	Key Pattern	TTL	Notes
User profile cache	cache:user:{userId}	1h	Bust on profile update
Field detail cache	cache:field:{fieldId}	1h	Bust on field update
Field search cache	cache:field-search:{hash(query)}	5–15m	Include radius/sportType filters
Field availability cache	cache:availability:{fieldId}:{date}	1–5m	Bust on booking create/cancel

5.2 Distributed Locks (Booking Conflict)

Purpose	Key Pattern	TTL	Notes
Create booking lock	lock:booking:{fieldId}:{startTime}:{endTime}	30s	Use SET key value NX PX
Slot lock (optional)	lock:slot:{fieldId}:{date}:{slotStart}	30s	If using discrete slots

Lock Value

• Use a unique lock value (UUID) and release only if value matches.

5.3 Rate Limiting (Optional)

Purpose	Key Pattern	TTL
Per-user counter	rl:user:{userId}:{windowStart}	window
Per-IP counter	rl:ip:{ip}:{windowStart}	window

---

6) Sample Data

6.1 Users

INSERT INTO users (id, email, name, phone, role, status)
VALUES
  ('11111111-1111-1111-1111-111111111111', 'admin@example.com', 'Admin', '+84900000001', 'ADMIN', 'ACTIVE'),
  ('22222222-2222-2222-2222-222222222222', 'owner@example.com', 'Field Owner', '+84900000002', 'OWNER', 'ACTIVE'),
  ('33333333-3333-3333-3333-333333333333', 'customer@example.com', 'Customer', '+84900000003', 'CUSTOMER', 'ACTIVE');

6.2 Field

INSERT INTO fields (id, owner_id, name, location, hourly_rate, capacity, status)
VALUES
  ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '22222222-2222-2222-2222-222222222222', 'Golden Field Arena', 'District 1, HCMC', 200000, 14, 'ACTIVE');

6.3 Booking

INSERT INTO bookings (id, field_id, user_id, start_time, end_time, total_price, status)
VALUES
  ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '33333333-3333-3333-3333-333333333333',
   '2026-01-20T10:00:00Z', '2026-01-20T12:00:00Z', 400000, 'PENDING');

6.4 Payment

INSERT INTO payments (id, booking_id, amount, currency, payment_method, status)
VALUES
  ('cccccccc-cccc-cccc-cccc-cccccccccccc', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', 400000, 'VND', 'STRIPE', 'PENDING');

---

7) Implementation Notes & Constraints

• Avoid cross-database foreign keys in production. Use:
• application-level validation (e.g., booking-service validates fieldId via field-service), and/or
• asynchronous validation via events.
• For strict no-double-booking, combine:
• Redis locks (fast), and
• a DB-level constraint approach (slot-based unique index), where feasible.
• All migrations must be idempotent and versioned per service.

---

Document Version: 1.0 Last Updated: 2026-01-13 Owner: Tech Lead