System Architecture

Database Schema & Access Controls

UK/Wales Sport Organisation - GDPR-compliant membership system

Core Tables (Standard Access)

users
AUTH

CREATE TABLE users (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email             VARCHAR(255) UNIQUE NOT NULL,
  full_name         VARCHAR(255) NOT NULL,
  role              ENUM('member', 'coach', 'admin', 'safeguarding_officer') DEFAULT 'member',
  status            ENUM('active', 'suspended', 'deleted') DEFAULT 'active',
  created_date      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_date      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_login        TIMESTAMP,
  
  INDEX idx_email (email),
  INDEX idx_role (role),
  INDEX idx_status (status)
);

-- Access: Admin (all), Member (own record only), Coach (read-only list)
-- Retention: Active + 7 years post-deletion (legal requirement)

members

CREATE TABLE members (
  id                        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_email                VARCHAR(255) REFERENCES users(email) ON DELETE CASCADE,
  full_name                 VARCHAR(255) NOT NULL,
  date_of_birth             DATE NOT NULL,
  phone                     VARCHAR(50) NOT NULL,
  address_line1             VARCHAR(255),
  address_line2             VARCHAR(255),
  address_city              VARCHAR(100),
  address_postcode          VARCHAR(20),
  emergency_contact_name    VARCHAR(255) NOT NULL,
  emergency_contact_phone   VARCHAR(50) NOT NULL,
  emergency_contact_rel     VARCHAR(100) NOT NULL,
  medical_conditions        TEXT,                    -- General medical info
  photo_consent             BOOLEAN DEFAULT FALSE,
  consent_marketing         BOOLEAN DEFAULT FALSE,
  safeguarding_flag         BOOLEAN DEFAULT FALSE,   -- Read: safeguarding_officer only
  notes                     TEXT,                    -- Admin notes only
  created_date              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_date              TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_by                VARCHAR(255),
  
  INDEX idx_user_email (user_email),
  INDEX idx_safeguarding_flag (safeguarding_flag)
);

-- Access: Admin (all), Member (own), Coach (name, emergency contact only in context of event)
-- Retention: Active membership + 7 years
-- Exclusions: safeguarding_flag excluded from default queries; medical_conditions not indexed

memberships

CREATE TABLE memberships (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  member_id             UUID REFERENCES members(id) ON DELETE CASCADE,
  membership_type       ENUM('monthly', 'annual', 'lifetime') DEFAULT 'annual',
  status                ENUM('active', 'expired', 'suspended', 'cancelled') DEFAULT 'active',
  start_date            DATE NOT NULL,
  expiry_date           DATE,
  renewal_date          DATE,
  fee_amount            DECIMAL(10,2),
  fee_currency          VARCHAR(3) DEFAULT 'GBP',
  auto_renew            BOOLEAN DEFAULT FALSE,
  created_date          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_member_id (member_id),
  INDEX idx_status (status),
  INDEX idx_expiry_date (expiry_date)
);

-- Access: Admin (all), Member (own)
-- Retention: Duration of membership + 7 years

events

CREATE TABLE events (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title             VARCHAR(255) NOT NULL,
  description       TEXT,
  event_type        ENUM('class', 'workshop', 'social', 'competition', 'other'),
  date              DATE NOT NULL,
  start_time        TIME NOT NULL,
  end_time          TIME,
  location          VARCHAR(255) NOT NULL,
  capacity          INTEGER NOT NULL,
  price             DECIMAL(10,2) DEFAULT 0,
  members_only      BOOLEAN DEFAULT TRUE,
  coach_id          UUID REFERENCES users(id),
  coach_name        VARCHAR(255),
  coach_email       VARCHAR(255),
  status            ENUM('draft', 'published', 'cancelled', 'completed') DEFAULT 'draft',
  recurring         BOOLEAN DEFAULT FALSE,
  booking_opens     TIMESTAMP,
  booking_closes    TIMESTAMP,
  waitlist_enabled  BOOLEAN DEFAULT TRUE,
  created_date      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_by        VARCHAR(255),
  
  INDEX idx_date (date),
  INDEX idx_status (status),
  INDEX idx_coach_id (coach_id)
);

-- Access: Admin (all), Coach (assigned events), Member (published only)
-- Retention: 3 years post-event

bookings

CREATE TABLE bookings (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id            UUID REFERENCES events(id) ON DELETE CASCADE,
  member_id           UUID REFERENCES members(id) ON DELETE CASCADE,
  member_email        VARCHAR(255),
  member_name         VARCHAR(255),            -- Denormalized for quick access
  status              ENUM('confirmed', 'waitlist', 'cancelled', 'attended', 'no_show') DEFAULT 'confirmed',
  payment_status      ENUM('not_required', 'pending', 'paid', 'refunded') DEFAULT 'not_required',
  payment_amount      DECIMAL(10,2),
  payment_id          UUID REFERENCES payments(id),
  checked_in          BOOLEAN DEFAULT FALSE,
  check_in_time       TIMESTAMP,
  waitlist_position   INTEGER,
  notes               TEXT,                    -- Non-sensitive booking notes only
  created_date        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_event_id (event_id),
  INDEX idx_member_id (member_id),
  INDEX idx_status (status),
  UNIQUE (event_id, member_id)
);

-- Access: Admin (all), Coach (assigned event attendees only), Member (own)
-- Notes field: Non-sensitive info only. Support needs stored in support_needs table
-- Retention: 2 years post-event

payments

CREATE TABLE payments (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  member_id             UUID REFERENCES members(id) ON DELETE RESTRICT,
  member_email          VARCHAR(255),
  payment_type          ENUM('membership_fee', 'event_booking', 'workshop', 'other'),
  amount                DECIMAL(10,2) NOT NULL,
  currency              VARCHAR(3) DEFAULT 'GBP',
  status                ENUM('pending', 'completed', 'failed', 'refunded') DEFAULT 'pending',
  payment_method        ENUM('card', 'bank_transfer', 'cash', 'other'),
  stripe_payment_id     VARCHAR(255),
  reference             VARCHAR(100),
  related_entity_type   VARCHAR(50),            -- 'Booking', 'Membership', etc.
  related_entity_id     UUID,
  discount_code         VARCHAR(50),
  discount_amount       DECIMAL(10,2),
  notes                 TEXT,
  created_date          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_member_id (member_id),
  INDEX idx_status (status),
  INDEX idx_stripe_payment_id (stripe_payment_id)
);

-- Access: Admin (all), Member (own)
-- Retention: 7 years (HMRC/legal requirement)

Sensitive Tables (Restricted Access)

equality_monitoring
PSEUDONYMIZED

CREATE TABLE equality_monitoring (
  id                      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  member_id               VARCHAR(255),              -- Pseudonymized token, NOT direct FK
  gender                  VARCHAR(50),
  gender_self_describe    VARCHAR(100),
  ethnicity               VARCHAR(100),
  disability              VARCHAR(50),
  sexual_orientation      VARCHAR(50),
  religion                VARCHAR(50),
  age_group               VARCHAR(20),
  submission_date         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_date            TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  -- NO INDEX on member_id (prevents easy joins)
  INDEX idx_submission_date (submission_date)
);

-- CRITICAL ACCESS CONTROLS:
-- ✗ Default queries CANNOT join to members table
-- ✗ Individual record access DENIED to all staff
-- ✓ Aggregate reporting ONLY (COUNT, GROUP BY) - no WHERE member_id
-- ✓ Export requires Safeguarding Officer approval + audit log
-- Retention: Anonymized aggregate kept indefinitely; member_id link deleted on member departure

support_needs
RESTRICTED

CREATE TABLE support_needs (
  id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  member_id                   UUID REFERENCES members(id) ON DELETE CASCADE,
  member_email                VARCHAR(255),
  support_category            VARCHAR(100),
  reasonable_adjustments      TEXT,                -- Full text, NOT INDEXED
  communication_preferences   TEXT,
  coach_notes                 TEXT,                -- Visible to coaches if share_with_coaches=TRUE
  safeguarding_notes          TEXT,                -- Safeguarding Officer ONLY
  share_with_coaches          BOOLEAN DEFAULT FALSE,
  last_reviewed_date          DATE,
  reviewed_by                 VARCHAR(255),
  created_date                TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_date                TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_member_id (member_id),
  -- NO FULLTEXT INDEX on adjustment fields (prevents search exposure)
);

-- ACCESS RULES:
-- Admin: Read metadata (category, dates), NO access to text fields
-- Coach: Read coach_notes ONLY if share_with_coaches=TRUE AND member booked in their event
-- Safeguarding Officer: Full access including safeguarding_notes
-- Member: Full access to own record
-- AUDIT: Every read/write logged to audit_log
-- Retention: Active membership + 6 months; reviewed annually

safeguarding_notes
HIGHEST RESTRICTION

CREATE TABLE safeguarding_notes (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  member_id         UUID REFERENCES members(id) ON DELETE RESTRICT,  -- Cannot delete member with safeguarding notes
  note              TEXT NOT NULL,                 -- NOT INDEXED
  risk_level        ENUM('low', 'medium', 'high', 'critical'),
  attachments       JSONB,                         -- File URLs, encrypted at rest
  incident_date     DATE,
  created_by        UUID REFERENCES users(id),
  created_date      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_date      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  reviewed_by       UUID,
  reviewed_date     TIMESTAMP,
  
  INDEX idx_member_id (member_id),
  INDEX idx_risk_level (risk_level),
  -- NO FULLTEXT INDEX
);

-- ACCESS RULES:
-- Safeguarding Officer: Full access
-- ALL OTHER ROLES: DENIED (no read, no write, no knowledge of existence)
-- AUDIT: Every access logged with IP, timestamp, user_id, action
-- ALERTS: High/Critical risk levels trigger email to Safeguarding Officer
-- Retention: Indefinite (legal safeguarding requirement); anonymization only after legal review
-- Encryption: Text fields encrypted at rest (application-level encryption)

Governance & Audit Tables

consent_log

CREATE TABLE consent_log (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  member_id         UUID REFERENCES members(id) ON DELETE RESTRICT,
  member_email      VARCHAR(255),
  consent_type      ENUM('privacy_policy', 'terms_conditions', 'marketing', 'photography', 'data_processing'),
  consent_given     BOOLEAN NOT NULL,
  consent_version   VARCHAR(20),               -- Policy version number
  ip_address        INET,
  user_agent        TEXT,
  timestamp         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_member_id (member_id),
  INDEX idx_consent_type (consent_type),
  INDEX idx_timestamp (timestamp)
);

-- Immutable: Records cannot be updated or deleted (audit trail)
-- Retention: Indefinite (legal proof of consent)
-- Access: Admin (read-only), Member (own records, read-only)

audit_log

CREATE TABLE audit_log (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_email        VARCHAR(255) NOT NULL,
  user_role         VARCHAR(50),
  action_type       ENUM('view', 'create', 'update', 'delete', 'export', 'login'),
  entity_type       VARCHAR(100),               -- 'Member', 'SupportNeeds', 'SafeguardingNotes'
  entity_id         UUID,
  field_accessed    VARCHAR(100),               -- Specific field for sensitive access
  ip_address        INET,
  description       TEXT,
  changes           JSONB,                      -- Before/after for updates
  timestamp         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_user_email (user_email),
  INDEX idx_entity_type (entity_type),
  INDEX idx_entity_id (entity_id),
  INDEX idx_timestamp (timestamp),
  INDEX idx_action_type (action_type)
);

-- Mandatory logging for:
--   - All access to safeguarding_notes (view, create, update)
--   - All access to support_needs (view, create, update)
--   - All exports of member data
--   - Admin modifications to member records
-- Immutable: Cannot be modified or deleted
-- Retention: 7 years minimum (legal requirement)
-- Access: Safeguarding Officer (full), Admin (limited to own actions)

membership_applications

CREATE TABLE membership_applications (
  id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  applicant_email             VARCHAR(255) NOT NULL,
  full_name                   VARCHAR(255) NOT NULL,
  date_of_birth               DATE NOT NULL,
  phone                       VARCHAR(50) NOT NULL,
  address                     JSONB,
  emergency_contact_name      VARCHAR(255) NOT NULL,
  emergency_contact_phone     VARCHAR(50) NOT NULL,
  emergency_contact_rel       VARCHAR(100) NOT NULL,
  medical_conditions          TEXT,
  status                      ENUM('pending', 'approved', 'rejected', 'expired') DEFAULT 'pending',
  reviewed_by                 VARCHAR(255),
  reviewed_date               TIMESTAMP,
  rejection_reason            TEXT,
  consent_privacy_policy      BOOLEAN NOT NULL,
  consent_terms               BOOLEAN NOT NULL,
  consent_marketing           BOOLEAN DEFAULT FALSE,
  consent_date                TIMESTAMP,
  notes                       TEXT,
  created_date                TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_status (status),
  INDEX idx_applicant_email (applicant_email)
);

-- Workflow: Pending → Admin Review → Approved (creates Member record) | Rejected
-- Retention: Approved applications kept 1 year then archived; Rejected kept 6 months
-- Access: Admin only

Role-Based Access Control (RBAC) Matrix

Table / Entity	Member	Coach	Admin	Safeguarding Officer
members	Own: Read/Update	Event attendees: Name, Emergency contact	All: CRUD (except safeguarding_flag)	All: Full CRUD
events	Published: Read	Assigned: Read/Update	All: CRUD	All: Read
bookings	Own: CRUD	Assigned events: Read, Check-in	All: CRUD	All: Read
support_needs	Own: CRUD	coach_notes ONLY (if share_with_coaches=TRUE)	Metadata only (no text fields)	All: Full CRUD + safeguarding_notes
safeguarding_notes	❌ DENIED	❌ DENIED	❌ DENIED	✓ Full CRUD
equality_monitoring	Own: Create only	❌ DENIED	Aggregate reports ONLY	Aggregate reports + Export (logged)
payments	Own: Read	❌ DENIED	All: CRUD	All: Read
audit_log	Own actions: Read	Own actions: Read	Own actions: Read	All: Read (immutable)

Data Retention Schedule

members, usersActive + 7 years (legal/insurance)

payments7 years (HMRC requirement)

bookings, events2-3 years post-event

support_needsActive + 6 months (reviewed annually)

safeguarding_notesIndefinite (legal safeguarding duty)

equality_monitoringMember link deleted on departure; aggregate data retained

audit_log7 years minimum (immutable)

consent_logIndefinite (proof of consent)

Security & Compliance Controls

Encryption

Data at rest: AES-256 encryption for entire database
Data in transit: TLS 1.3 for all API connections
Application-level encryption for safeguarding_notes.note field

Search Index Exclusions

support_needs.reasonable_adjustments (no fulltext index)
safeguarding_notes.note (no fulltext index)
members.medical_conditions (no fulltext index)

Audit Logging (Mandatory)

ALL access to safeguarding_notes (view, create, update)
ALL access to support_needs.safeguarding_notes field
Data exports (DSAR, reports)
Admin changes to member records
Consent withdrawals

Row-Level Security (RLS)

Members can only query own records (WHERE user_email = current_user)
Coaches can only see attendees for assigned events
equality_monitoring cannot be joined to members in default queries

Data Subject Access Request (DSAR)

Export all personal data on request (30 days)
Include: members, bookings, payments, support_needs, consent_log
Exclude: safeguarding_notes (legal exemption)
Log export in audit_log

Right to Erasure

Members: Anonymize after retention period (keep ID, remove PII)
Exceptions: payments (7 years HMRC), safeguarding_notes (indefinite)
Soft delete: Mark status='deleted', retain for legal period