Sport Tech Club - Entity-Relationship Diagram (MER)

📊 Overview

This document presents the complete Entity-Relationship Model for the Sport Tech Club platform, a multi-tenant SaaS system for beach sports arena management with gamification, queue management, and payment processing.

Architecture Principles

• Multi-tenant isolation: Row-level security (RLS) and schema-based isolation
• Domain-Driven Design (DDD): Organized into bounded contexts
• Event sourcing ready: Audit logs track all entity changes
• LGPD compliant: Consent tracking and data retention policies
• Scalability: Partitioning strategy for high-volume tables

---

🏗️ Bounded Contexts

The database is organized into 4 PostgreSQL schemas:

1. identity - User management, authentication, gamification
2. arena - Arena, courts, queues, game sessions
3. booking - Bookings, payments, check-ins
4. public - Audit logs, shared data

---

📐 Complete Entity-Relationship Diagram

---

🔐 Multi-Tenant Isolation Strategy

Row-Level Security (RLS)

-- Enable RLS on tenant-scoped tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE bookings ENABLE ROW LEVEL SECURITY;

-- Policy example: Users can only see their tenant's data
CREATE POLICY tenant_isolation_policy ON users
  USING (tenant_id = current_setting('app.current_tenant')::UUID);

-- Application sets tenant context per request
BEGIN;
  SET LOCAL app.current_tenant = 'uuid-of-tenant';
  SELECT * FROM users; -- Automatically filtered
COMMIT;

Schema-Based Isolation

• identity: Tenant data with RLS
• arena: Per-tenant arenas with foreign keys to identity.tenant
• booking: Bookings always linked to arena (transitive tenant isolation)
• public: Audit logs with explicit tenantId column

---

📊 Indexing Strategy

Critical Indexes for Performance

-- Multi-tenant queries (most common)
CREATE INDEX idx_users_tenant_status ON users(tenant_id, status);
CREATE INDEX idx_arenas_tenant_status ON arenas(tenant_id, status);
CREATE INDEX idx_bookings_arena_time ON bookings(arena_id, start_time, end_time);

-- Time-based queries (booking availability)
CREATE INDEX idx_bookings_court_time ON bookings(court_id, start_time, end_time)
  WHERE status NOT IN ('CANCELLED', 'NO_SHOW');

-- Player matchmaking
CREATE INDEX idx_skill_rating_sport_level ON skill_ratings(sport, rating DESC);
CREATE INDEX idx_queue_entries_status_position ON queue_entries(queue_id, status, position);

-- Payment reconciliation
CREATE INDEX idx_payments_status_created ON payments(status, created_at);
CREATE INDEX idx_payments_gateway_external ON payments(gateway, external_id);

-- Audit logs (time-series)
CREATE INDEX idx_audit_tenant_created ON audit_logs(tenant_id, created_at DESC);
CREATE INDEX idx_audit_entity ON audit_logs(entity_type, entity_id);

-- Full-text search
CREATE INDEX idx_users_search ON users USING gin(to_tsvector('portuguese', display_name || ' ' || email));
CREATE INDEX idx_arenas_search ON arenas USING gin(to_tsvector('portuguese', name || ' ' || address_city));

Composite Indexes for Common Queries

-- "Show available courts for beach tennis on Saturday 2pm"
CREATE INDEX idx_courts_sport_status ON courts(arena_id, sport, status)
  WHERE status = 'ACTIVE';

-- "Top 10 players by skill rating for beach tennis"
CREATE INDEX idx_skill_rating_leaderboard ON skill_ratings(sport, rating DESC, games_played);

-- "User's active bookings"
CREATE INDEX idx_bookings_user_active ON bookings(user_id, status, start_time)
  WHERE status IN ('CONFIRMED', 'CHECKED_IN');

---

🗂️ Partitioning Strategy

Time-Series Partitioning

High-volume tables partitioned by month for efficient archival:

-- Bookings (estimated 10K-50K/month per large arena)
CREATE TABLE bookings_2025_01 PARTITION OF bookings
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE bookings_2025_02 PARTITION OF bookings
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Payments (follows booking volume)
CREATE TABLE payments_2025_01 PARTITION OF payments
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- Audit logs (estimated 100K-500K/month)
CREATE TABLE audit_logs_2025_01 PARTITION OF audit_logs
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

Retention Policy

-- Automated archival job
CREATE OR REPLACE FUNCTION archive_old_partitions()
RETURNS void AS $$
BEGIN
  -- Detach partitions older than 2 years
  EXECUTE format('
    ALTER TABLE bookings DETACH PARTITION bookings_%s;
  ', to_char(NOW() - INTERVAL '2 years', 'YYYY_MM'));
  
  -- Move to archival schema or external storage (S3 via pg_dump)
END;
$$ LANGUAGE plpgsql;

-- Schedule via pg_cron
SELECT cron.schedule('archive-old-data', '0 2 1 * *', 'SELECT archive_old_partitions();');

---

🔗 Key Relationships Explained

1. Multi-Tenant Hierarchy

Tenant (SaaS Customer)
  └── Arena (Physical Location)
       ├── Court (Bookable Resource)
       ├── User (Staff/Players)
       └── Booking (Reservation)

Design Decision: Arena is child of Tenant to support franchises with multiple locations.

---

2. Booking Lifecycle

Booking (PENDING)
  ├── Payment (PROCESSING)
  │    └── Refund (if cancelled)
  ├── CheckIn (QR code scan)
  └── BookingParticipant (invited players)

States:

1. PENDING → User selects time slot
2. CONFIRMED → Payment successful
3. CHECKED_IN → User arrives at arena
4. IN_PROGRESS → Game started
5. COMPLETED → Game finished (triggers XP/achievements)

---

3. Queue System (Winner Stays)

GameQueue (per Court)
  ├── QueueEntry (position 1)
  │    └── GameTeam [Player A, Player B]
  ├── QueueEntry (position 2)
  │    └── GameTeam [Player C, Player D]
  └── GameSession (active match)
       ├── HomeTeam (from position 1)
       ├── AwayTeam (from position 2)
       └── MatchParticipant (stats)

Algorithm:

1. Winner stays on court
2. Loser goes to end of queue
3. Next team (position 2) challenges winner
4. ELO rating updated after each match

---

4. Gamification Flow

MatchParticipant (game completed)
  ├── Update SkillRating (ELO +15)
  ├── Update PlayerGamification (XP +50)
  ├── Check Achievement Criteria
  └── Create PlayerAchievement (if unlocked)
       └── Notification (ACHIEVEMENT_UNLOCKED)

XP Sources:

• Complete game: +50 XP
• Win game: +30 XP
• Win streak (3+): +20 XP bonus
• First game of day: +10 XP
• Achievement unlock: Variable (10-500 XP)

---

5. Payment Flow

Booking (totalPrice = 120.00)
  ├── Promotion (code: SUMMER2025, -20.00)
  ├── PricingRule (peak hour, +30.00)
  └── Payment
       ├── Gateway: MERCADO_PAGO
       ├── Method: PIX
       ├── Amount: 130.00
       ├── Fees: 3.99
       └── NetAmount: 126.01

Gateway Integration:

• Webhook receives payment confirmation
• Update Payment.status = PAID
• Update Booking.status = CONFIRMED
• Send Notification (BOOKING_CONFIRMATION)

---

🎯 Critical Constraints

Business Rules Enforced by DB

-- No overlapping bookings
CREATE UNIQUE INDEX idx_bookings_no_overlap ON bookings(court_id, start_time, end_time)
  WHERE status NOT IN ('CANCELLED', 'NO_SHOW');

-- Max 1 active queue entry per player
CREATE UNIQUE INDEX idx_queue_one_per_player ON queue_entries(queue_id, unnest(player_ids))
  WHERE status = 'WAITING';

-- Achievement can only be unlocked once per player
ALTER TABLE player_achievements ADD CONSTRAINT uq_achievement_once
  UNIQUE (player_profile_id, achievement_id);

-- Refund cannot exceed payment amount
ALTER TABLE refunds ADD CONSTRAINT chk_refund_amount
  CHECK (amount <= (SELECT amount FROM payments WHERE id = payment_id));

Soft Delete Pattern

Tables with deleted_at column use soft delete:

-- Archive instead of delete
UPDATE users SET deleted_at = NOW(), status = 'DELETED'
WHERE id = 'uuid-here';

-- Queries exclude deleted rows
SELECT * FROM users WHERE deleted_at IS NULL;

-- Permanent deletion after 90 days (LGPD right to be forgotten)
DELETE FROM users WHERE deleted_at < NOW() - INTERVAL '90 days';

---

📈 Scalability Considerations

Performance Targets

Operation	Target Latency	Volume
Booking availability check	< 100ms	1000 req/s
Payment processing	< 500ms	100 req/s
Queue position update	< 50ms	500 req/s
Skill rating calculation	< 200ms	50 req/s
Dashboard analytics	< 2s	10 req/s

Optimization Strategies

1. Materialized Views for analytics:

CREATE MATERIALIZED VIEW mv_arena_daily_stats AS
SELECT
  arena_id,
  DATE(start_time) as date,
  COUNT(*) as total_bookings,
  SUM(total_price) as revenue,
  AVG(EXTRACT(EPOCH FROM (end_time - start_time))/60) as avg_duration_minutes
FROM bookings
WHERE status = 'COMPLETED'
GROUP BY arena_id, DATE(start_time);

-- Refresh nightly
CREATE INDEX ON mv_arena_daily_stats(arena_id, date DESC);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_arena_daily_stats;

2. Read Replicas for analytics queries:

   • Primary: Write operations (bookings, payments)
   • Replica 1: Dashboard queries (read-only)
   • Replica 2: Reporting/data exports

3. Caching Layer (Redis):

   • Court availability (5min TTL)
   • Queue positions (30s TTL)
   • User sessions (24h TTL)
   • Pricing rules (1h TTL)

---

🔄 Data Migration Strategy

Version Control

-- migrations/001_create_identity_schema.sql
CREATE SCHEMA IF NOT EXISTS identity;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- migrations/002_create_tenants_table.sql
CREATE TABLE identity.tenants (...);

-- migrations/003_add_rls_policies.sql
ALTER TABLE identity.users ENABLE ROW LEVEL SECURITY;

Zero-Downtime Deployment

1. Backward Compatible Changes:

   • Add nullable columns first
   • Populate data in background job
   • Make column NOT NULL in next release

2. Schema Changes:

-- Bad: ALTER TABLE locks table
ALTER TABLE bookings ADD COLUMN notes TEXT;

-- Good: Use transactional DDL
BEGIN;
  ALTER TABLE bookings ADD COLUMN notes TEXT;
  CREATE INDEX CONCURRENTLY idx_bookings_notes ON bookings USING gin(to_tsvector('portuguese', notes));
COMMIT;

---

📚 Data Quality Tests (dbt)

Referential Integrity

# models/schema.yml
models:
  - name: bookings
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: arena_id
        tests:
          - relationships:
              to: ref('arenas')
              field: id
      - name: total_price
        tests:
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 10000

Business Logic Validation

-- tests/no_overlapping_bookings.sql
SELECT
  court_id,
  start_time,
  end_time,
  COUNT(*) as overlaps
FROM bookings
WHERE status NOT IN ('CANCELLED', 'NO_SHOW')
GROUP BY court_id, start_time, end_time
HAVING COUNT(*) > 1;
-- Should return 0 rows

---

🛡️ Security & Compliance

LGPD (Brazilian GDPR) Compliance

1. Right to Access: User can export all their data
2. Right to Deletion: Soft delete + permanent purge after 90 days
3. Consent Tracking: user_consents table with IP + timestamp
4. Data Minimization: Only collect necessary fields
5. Audit Trail: All changes logged in audit_logs

Sensitive Data Encryption

-- PII encrypted at rest (using pgcrypto)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt CPF
UPDATE users SET cpf = pgp_sym_encrypt(cpf, 'encryption-key')
WHERE cpf IS NOT NULL;

-- Decrypt on read (application layer)
SELECT id, pgp_sym_decrypt(cpf::bytea, 'encryption-key') as cpf
FROM users WHERE id = 'uuid';

---

📊 Analytics Queries Examples

Daily Revenue Report

SELECT
  a.name as arena_name,
  DATE(b.start_time) as date,
  COUNT(DISTINCT b.id) as bookings,
  SUM(p.net_amount) as revenue,
  AVG(p.net_amount) as avg_ticket
FROM bookings b
JOIN arenas a ON b.arena_id = a.id
JOIN payments p ON b.id = p.booking_id
WHERE p.status = 'PAID'
  AND b.start_time >= NOW() - INTERVAL '30 days'
GROUP BY a.id, a.name, DATE(b.start_time)
ORDER BY date DESC, revenue DESC;

Player Engagement Metrics

WITH player_stats AS (
  SELECT
    pp.id,
    u.display_name,
    pg.level,
    pg.total_games_played,
    pg.current_streak,
    COUNT(DISTINCT mp.game_session_id) as games_last_30d,
    AVG(sr.rating) as avg_rating
  FROM player_profiles pp
  JOIN users u ON pp.user_id = u.id
  JOIN player_gamification pg ON pp.id = pg.player_profile_id
  LEFT JOIN match_participants mp ON pp.id = mp.player_profile_id
    AND mp.created_at >= NOW() - INTERVAL '30 days'
  LEFT JOIN skill_ratings sr ON pp.id = sr.player_profile_id
  GROUP BY pp.id, u.display_name, pg.level, pg.total_games_played, pg.current_streak
)
SELECT
  display_name,
  level,
  total_games_played,
  games_last_30d,
  CASE
    WHEN games_last_30d >= 15 THEN 'High Engagement'
    WHEN games_last_30d >= 5 THEN 'Medium Engagement'
    ELSE 'Low Engagement'
  END as engagement_tier,
  current_streak,
  ROUND(avg_rating) as skill_rating
FROM player_stats
ORDER BY games_last_30d DESC
LIMIT 100;

Court Utilization Rate

SELECT
  c.name as court_name,
  COUNT(*) as total_bookings,
  SUM(EXTRACT(EPOCH FROM (b.end_time - b.start_time))/3600) as hours_booked,
  ROUND(
    SUM(EXTRACT(EPOCH FROM (b.end_time - b.start_time))/3600) /
    (oh.close_time - oh.open_time) * 30, -- assuming 30 days in month
    2
  ) as utilization_rate_pct
FROM courts c
JOIN bookings b ON c.id = b.court_id
JOIN operating_hours oh ON c.arena_id = oh.arena_id
WHERE b.start_time >= DATE_TRUNC('month', NOW())
  AND b.status IN ('COMPLETED', 'IN_PROGRESS')
GROUP BY c.id, c.name, oh.open_time, oh.close_time
ORDER BY utilization_rate_pct DESC;

---

🎓 Design Decisions Rationale

1. Why UUID over Auto-Increment IDs?

✅ Advantages:

• Multi-tenant safe (no ID leaking between tenants)
• Distributed systems friendly (no coordination needed)
• Merge-friendly (staging → production)
• URL-safe (no sequential ID enumeration attacks)

⚠️ Trade-offs:

• Larger index size (16 bytes vs 4 bytes)
• Slightly slower joins (mitigated by proper indexing)

2. Why JSONB over Separate Tables?

Use JSONB when:

• Schema is dynamic (user settings, metadata)
• Rarely queried (audit log details)
• Nested structures (scoreboard sets, dimension measurements)

Use separate tables when:

• Need to query/filter (skill ratings per sport)
• Referential integrity required (booking participants)
• Frequent updates (counters, aggregates)

3. Why Soft Delete?

• LGPD compliance: Users can request data deletion, but need grace period
• Audit trail: Keep record of "who deleted what when"
• Accidental deletion recovery: 7-day window to restore

Permanent deletion after 90 days via scheduled job.

4. Why Enum over Varchar?

✅ Type safety: Database rejects invalid values ✅ Storage efficiency: Stored as integers internally ✅ Performance: Faster comparisons vs string ⚠️ Migration complexity: Adding enum value requires ALTER TYPE

Alternative: varchar with CHECK constraint for easier updates.

---

🚀 Next Steps

1. Create seed data: Use Prisma seed script to populate dev database
2. Generate documentation: prisma generate + dbdocs.io
3. Setup CI/CD: Automate migration testing
4. Monitor slow queries: pg_stat_statements extension
5. Implement caching: Redis for hot paths

---

📞 Contact

For questions about this data model, contact:

• DataFlow (Data Engineering Team)
• Architecture Review: Weekly Thursdays 3pm

---

Last Updated: 2025-01-09 Schema Version: 1.0.0 PostgreSQL Version: 16.x