🏑POWLAX SUPABASE SCHEMA

Comprehensive database schema merging all sources: CSV analysis, existing schema, and Development Framework

---

🎯Overview

This schema represents the complete POWLAX ecosystem, integrating:

• 137 drills with multi-dimensional categorization
• 240 master terms with contextual variations
• 100 skills in hierarchical progression
• 50 communication terms with phase-specific meanings
• 661 workouts/quizzes for Skills Academy
• 5 universal player states across 10 game phases
• Strategic layer linking drills to broader coaching concepts

---

🏗️Core Content System

1. Master Terms Table (NEW from CSV 01)

CREATE TABLE master_terms (
  -- Core Identity
  id SERIAL PRIMARY KEY,
  term_id TEXT UNIQUE NOT NULL, -- Pattern: T001, T002, etc.
  term_name TEXT NOT NULL,
  term_type TEXT NOT NULL, -- 'skill', 'communication', 'concept', 'position', 'drill'
  
  -- Definition
  primary_definition TEXT NOT NULL,
  is_universal BOOLEAN DEFAULT true, -- Same meaning in all contexts
  has_variations BOOLEAN DEFAULT false, -- Changes by game phase
  
  -- Teaching Integration
  teaching_concepts TEXT[], -- Array of concept IDs (TC001, TC002)
  
  -- Metadata
  date_added DATE,
  added_by TEXT, -- 'PDF_Analysis', 'Manual_Entry', 'Coach_Submission'
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_master_terms_type ON master_terms(term_type);
CREATE INDEX idx_master_terms_universal ON master_terms(is_universal);

2. Drills Table (ENHANCED)

CREATE TABLE drills (
  -- Core Identity
  id SERIAL PRIMARY KEY,
  wp_id INTEGER UNIQUE, -- WordPress post ID for migration
  title TEXT NOT NULL,
  content TEXT,
  excerpt TEXT, -- Short description for cards
  
  -- Categorization
  category TEXT NOT NULL, -- "Skill Drills", "Defensive", "Offensive", "Transition"
  drill_types TEXT[], -- WordPress taxonomy array
  
  -- Timing & Complexity
  duration_minutes INTEGER DEFAULT 10, -- Renamed from 'duration'
  complexity_level TEXT, -- "Foundation", "Building", "Advanced"
  
  -- Equipment & Setup (from Skills Academy CSV)
  equipment_needed TEXT[], -- ["Balls", "Goal", "Cones", "Bounce Back", "Speed Ladder"]
  location_setup TEXT, -- "Goal Area Spaces>20x20 Yard Box"
  
  -- Game Context (Practice Planner)
  game_states_raw TEXT[], -- ["pp-settled-offense", "pp-transition"]
  game_states_display TEXT[], -- ["Settled Offense", "Transition"]
  
  -- Content & Media
  coaching_notes TEXT, -- Renamed from 'notes'
  primary_video_url TEXT, -- Main demonstration video
  custom_url TEXT, -- Links to Master Classes
  images TEXT[], -- Array of image URLs
  
  -- Teaching Integration (NEW from CSV)
  teaching_concepts TEXT[], -- Links to teaching_concepts table
  movement_principles TEXT[], -- Links to movement_principles table
  pressure_progressions JSONB, -- {no_pressure: {...}, light_pressure: {...}, game_pressure: {...}}
  phase_relevance JSONB, -- {face_off: 'critical', transition: 'important'}
  player_states INTEGER[], -- [1, 2, 3] - Primary player states trained
  
  -- Skills Development (NEW from CSV)
  prerequisite_skills TEXT[], -- Skill IDs required before this drill
  leads_to_skills TEXT[], -- Skills this drill helps develop
  
  -- Communication (NEW from CSV)
  communication_priorities TEXT[], -- Key terms to use during drill
  visual_cues TEXT[], -- Non-verbal indicators
  regional_variations JSONB, -- {east_coast: '...', west_coast: '...'}
  
  -- WordPress Integration
  wp_post_type TEXT DEFAULT 'powlax_drill',
  wp_status TEXT DEFAULT 'publish',
  wp_author_id INTEGER,
  wp_slug TEXT,
  
  -- Status
  status TEXT DEFAULT 'active',
  tags TEXT, -- Comma-separated for search
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  wp_modified_date TIMESTAMPTZ
);

3. Drill Variations Table (Normalized Lab URLs)

CREATE TABLE drill_variations (
  id SERIAL PRIMARY KEY,
  drill_id INTEGER REFERENCES drills(id) ON DELETE CASCADE,
  
  -- Variation Details
  variation_number INTEGER NOT NULL, -- 1-5 (from lab_url_1 through lab_url_5)
  variation_name TEXT DEFAULT 'Variation',
  
  -- Video Content
  lab_url TEXT NOT NULL,
  embed_code TEXT, -- Full iframe embed
  vimeo_url TEXT, -- Alternative video source
  
  -- Metadata
  is_primary BOOLEAN DEFAULT false, -- lab_url_1 = primary
  sequence_order INTEGER,
  description TEXT, -- What this variation shows
  
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

---

🎮Game & Player Understanding

5. Game Phases Table (NEW from CSV 02)

CREATE TABLE game_phases (
  id SERIAL PRIMARY KEY,
  phase_id TEXT UNIQUE NOT NULL, -- P001, P002, etc.
  name TEXT NOT NULL, -- 'face_off', 'transition_offense', etc.
  
  -- Timing & Flow
  duration TEXT, -- '0-5 seconds', '5-20 seconds'
  primary_objective TEXT,
  
  -- Phase Transitions
  preceded_by TEXT[], -- Array of phase_ids
  leads_to TEXT[], -- Array of phase_ids
  triggers TEXT[], -- What starts this phase
  
  -- Player Distribution
  player_state_distribution JSONB, -- {primary: [1], secondary: [2,3], rare: [4,5]}
  
  -- Skills & Communication
  key_skills TEXT[], -- Critical skill IDs
  communication_priorities TEXT[], -- Top terms for this phase
  
  -- Success Metrics
  success_metrics JSONB, -- {win_percentage: '55%+', possession_time: '<5s'}
  
  created_at TIMESTAMPTZ DEFAULT NOW()
);

6. Player States Table (NEW from CSV 08)

The 5 universal player states that apply across all game phases:

CREATE TABLE player_states (
  id SERIAL PRIMARY KEY,
  state_id INTEGER UNIQUE NOT NULL, -- 1-5
  name TEXT NOT NULL,
  universal_definition TEXT NOT NULL,
  
  -- State Details
  primary_objective TEXT,
  key_decisions TEXT[], -- Decision points in this state
  primary_skills TEXT[], -- Essential skill IDs
  
  -- Communication
  communication_focus JSONB, -- {priorities: [...], typical_sequence: '...'}
  
  -- Phase Distribution
  phase_occurrence JSONB, -- {high_frequency: [...], medium: [...], low: [...]}
  
  -- Transitions
  transitions_to INTEGER[], -- Next possible states
  
  -- Coaching
  coaching_points TEXT[],
  
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- The 5 universal states
INSERT INTO player_states (state_id, name) VALUES
(1, 'Ball on Ground'),
(2, 'I Have Ball'),
(3, 'Supporting Teammate with Ball'),
(4, 'Defending Player with Ball'),
(5, 'Defending Player without Ball');

---

📚Teaching & Development

8. Teaching Concepts Table (NEW from CSV 06 & 12)

CREATE TABLE teaching_concepts (
  id SERIAL PRIMARY KEY,
  concept_id TEXT UNIQUE NOT NULL, -- TC001, TCE001, etc.
  name TEXT NOT NULL, -- '22_ground_balls', 'triple_threat'
  
  -- Categorization
  category TEXT NOT NULL, -- 'mnemonic', 'visual_cue', 'movement_principle', 'perceptual_framework'
  concept_type TEXT, -- 'memory_aid', 'coaching_method'
  
  -- Teaching Details
  description TEXT NOT NULL,
  teaches_skills TEXT[], -- Array of skill IDs
  
  -- Implementation
  implementation JSONB, -- {introduction: '...', reinforcement: '...', correction_cue: '...'}
  visual_components TEXT[], -- Visual cues to demonstrate
  verbal_cues TEXT[], -- What to say
  
  -- Age Adaptations
  age_adaptations JSONB, -- {youth: '...', middle_school: '...', high_school: '...'}
  
  -- Related Concepts
  related_concepts TEXT[], -- Other concept IDs
  
  -- Success Measurement
  success_indicators TEXT[],
  common_errors TEXT[],
  
  -- Implementation Tips
  implementation_tips TEXT[],
  measurement TEXT[], -- How to track success
  
  created_at TIMESTAMPTZ DEFAULT NOW()
);

---

👥User Management

14. User Profiles Table

CREATE TABLE user_profiles (
  -- Core Identity
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id) UNIQUE,
  
  -- Profile Information
  full_name TEXT,
  display_name TEXT,
  avatar_url TEXT,
  bio TEXT,
  
  -- Role & Type
  primary_role TEXT, -- 'player', 'coach', 'parent', 'director'
  is_coach BOOLEAN DEFAULT false,
  is_player BOOLEAN DEFAULT false,
  is_parent BOOLEAN DEFAULT false,
  coach_level TEXT, -- 'assistant', 'head', 'director'
  
  -- Player Specific
  jersey_number TEXT,
  position TEXT, -- 'attack', 'midfield', 'defense', 'goalie', 'fogo'
  dominant_hand TEXT, -- 'right', 'left', 'both'
  
  -- Age & Development
  birth_date DATE,
  age_band TEXT, -- '8U', '10U', '12U', '14U', '16U', '18U'
  skill_level TEXT, -- 'beginner', 'intermediate', 'advanced'
  
  -- Team Associations
  primary_team_id INTEGER,
  teams JSONB, -- Multiple team memberships
  
  -- Parent-Child Relationships
  parent_user_ids UUID[], -- For child accounts
  child_user_ids UUID[], -- For parent accounts
  
  -- WordPress Integration
  wp_user_id INTEGER UNIQUE,
  wp_username TEXT,
  wp_email TEXT,
  
  -- Preferences
  notification_preferences JSONB,
  privacy_settings JSONB,
  
  -- Metadata
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

---

🎓Skills Academy System

19. Workouts Table

Structured learning paths with position-specific progressions (A1-A13, M1-M13, D1-D13):

CREATE TABLE workouts (
  -- Core Identity
  id SERIAL PRIMARY KEY,
  wp_id INTEGER UNIQUE,
  
  -- Workout Details
  title TEXT NOT NULL,
  description TEXT,
  
  -- Categorization (A1-A13, M1-M13, D1-D13)
  position_type TEXT, -- 'attack', 'midfield', 'defense'
  level_number INTEGER, -- 1-13
  level_code TEXT GENERATED ALWAYS AS (
    CASE 
      WHEN position_type = 'attack' THEN 'A' || level_number
      WHEN position_type = 'midfield' THEN 'M' || level_number
      WHEN position_type = 'defense' THEN 'D' || level_number
    END
  ) STORED,
  
  -- Content
  drill_ids INTEGER[], -- Ordered array of drill IDs
  drill_count INTEGER,
  total_duration_minutes INTEGER,
  
  -- Requirements
  prerequisite_workouts INTEGER[], -- Must complete these first
  required_equipment TEXT[],
  space_requirements TEXT,
  
  -- Points & Rewards
  base_points INTEGER DEFAULT 10,
  completion_badge_id INTEGER, -- Badge earned on completion
  
  -- Media
  intro_video_url TEXT,
  thumbnail_url TEXT,
  
  -- Status
  is_active BOOLEAN DEFAULT true,
  
  -- Metadata
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

---

🏆Gamification & Progress

25. User Progress Table

CREATE TABLE user_progress (
  id SERIAL PRIMARY KEY,
  user_id UUID REFERENCES auth.users(id),
  
  -- Content Reference
  content_id INTEGER,
  content_type TEXT, -- 'drill', 'workout', 'quiz', 'practice'
  
  -- Progress Details
  status TEXT, -- 'started', 'completed', 'mastered'
  completion_percentage INTEGER DEFAULT 0,
  
  -- Performance Metrics
  score INTEGER, -- For quizzes
  time_spent_minutes INTEGER,
  attempts_count INTEGER DEFAULT 1,
  
  -- Rep Tracking (NEW)
  reps_completed INTEGER,
  perfect_reps INTEGER,
  success_criteria_met BOOLEAN DEFAULT false,
  
  -- Completion Details
  completed_at TIMESTAMPTZ,
  first_started_at TIMESTAMPTZ,
  last_activity_at TIMESTAMPTZ,
  
  -- Points & Rewards
  points_earned INTEGER DEFAULT 0,
  point_type_id INTEGER REFERENCES point_types(id),
  badges_earned INTEGER[], -- Array of badge IDs earned
  
  -- Context
  team_id INTEGER REFERENCES teams(id),
  practice_plan_id INTEGER REFERENCES practice_plans(id), -- If part of practice
  
  -- Metadata
  device_type TEXT, -- 'mobile', 'desktop', 'tablet'
  completion_method TEXT, -- 'guided', 'self-directed', 'practice'
  
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

---

🔒Row Level Security

Core RLS Policies

-- Enable RLS on all tables
ALTER TABLE drills ENABLE ROW LEVEL SECURITY;
ALTER TABLE drill_variations ENABLE ROW LEVEL SECURITY;
ALTER TABLE practice_plans ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_progress ENABLE ROW LEVEL SECURITY;

-- Drills: Public read, authenticated write
CREATE POLICY "Drills are viewable by everyone" ON drills
  FOR SELECT USING (true);

CREATE POLICY "Authenticated users can create drills" ON drills
  FOR INSERT WITH CHECK (auth.role() = 'authenticated');

-- Practice Plans: Team-based access
CREATE POLICY "Users can view their team's practice plans" ON practice_plans
  FOR SELECT USING (
    team_id IN (
      SELECT team_id FROM team_memberships 
      WHERE user_id = auth.uid()
    )
    OR is_public = true
  );

-- User Progress: Personal data only
CREATE POLICY "Users can only view their own progress" ON user_progress
  FOR SELECT USING (user_id = auth.uid());

-- Parent-Child Access
CREATE POLICY "Parents can view children's progress" ON user_progress
  FOR SELECT USING (
    user_id IN (
      SELECT unnest(child_user_ids) 
      FROM user_profiles 
      WHERE user_id = auth.uid()
    )
  );

---

This comprehensive schema integrates all aspects of the POWLAX ecosystem into a unified, scalable database structure.