pilot.db — students

• account_invites
• accounts
• applied_migrations
• audit_log
• availability
• availability_exceptions
• bookings
• campaign_leads
• campaign_offers
• campaigns
• cards
• commission_rates
• commission_runs
• drills_library
• invoices
• magic_tokens
• offers
• practice_logs
• quotes
• saas_invoices
• service_student_prices
• services
• sessions
• sqlite_sequence
• student_summaries
• students
• subscription_events
• trainer_settings
• trainers

Toggle helper tables

• Structure
• Content
• Query
• Insert
• Drop
• Import
• Export

CREATE TABLE students  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  trainer_id INTEGER NOT NULL REFERENCES trainers(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  email TEXT,
  -- für Magic-Link
  phone TEXT,
  hcpi REAL,
  -- Handicap-Index
  tags TEXT NOT NULL DEFAULT '[]',
  -- JSON-Array: ["Einzel",
  "Stamm",
  "VIP"]
  marketing_consent INTEGER NOT NULL DEFAULT 0,
  video_consent INTEGER NOT NULL DEFAULT 0,
  video_consent_date TEXT,
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  status TEXT NOT NULL DEFAULT 'active'   -- 'active' | 'archived',
  birthday TEXT,
  member_since TEXT,
  home_club TEXT,
  source TEXT,
  contact_channel TEXT,
  prefs TEXT,
  video_consent_granted_at TEXT,
  example_note TEXT,
  address TEXT,
  calendar_token TEXT,
  password_hash TEXT,
  activation_token TEXT,
  activation_expires_at TEXT,
  activated_at TEXT,
  password_reset_token TEXT,
  password_reset_expires_at TEXT,
  payment_method TEXT,
  payment_notes TEXT,
  marketing_consent_at TEXT,
  marketing_consent_source TEXT,
  marketing_token TEXT,
  privacy_accepted_at TEXT,
  anamnesis TEXT,
  anamnesis_at TEXT,
  anamnesis_consent_at TEXT,
  first_name TEXT,
  last_name TEXT,
  avatar TEXT,
  social_website TEXT,
  social_instagram TEXT,
  social_linkedin TEXT,
  social_youtube TEXT,
  training_plan TEXT,
  training_plan_at TEXT
)

CREATE UNIQUE INDEX idx_students_activation_token
ON students(activation_token) WHERE activation_token IS NOT NULL

CREATE UNIQUE INDEX idx_students_calendar_token
ON students(calendar_token) WHERE calendar_token IS NOT NULL

CREATE INDEX idx_students_email
ON students(email) WHERE email IS NOT NULL

CREATE UNIQUE INDEX idx_students_reset_token
ON students(password_reset_token) WHERE password_reset_token IS NOT NULL

CREATE INDEX idx_students_trainer
ON students(trainer_id)