Query

pilot.db — students

SQL

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
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
trainer_id INTEGER Rename | Drop
name TEXT Rename | Drop
email TEXT ✓ Rename | Drop
phone TEXT ✓ Rename | Drop
hcpi REAL ✓ Rename | Drop
tags TEXT Rename | Drop
marketing_consent INTEGER Rename | Drop
video_consent INTEGER Rename | Drop
video_consent_date TEXT ✓ Rename | Drop
notes TEXT ✓ Rename | Drop
created_at TEXT Rename | Drop
status TEXT Rename | Drop
birthday TEXT ✓ Rename | Drop
member_since TEXT ✓ Rename | Drop
home_club TEXT ✓ Rename | Drop
source TEXT ✓ Rename | Drop
contact_channel TEXT ✓ Rename | Drop
prefs TEXT ✓ Rename | Drop
video_consent_granted_at TEXT ✓ Rename | Drop
example_note TEXT ✓ Rename | Drop
address TEXT ✓ Rename | Drop
calendar_token TEXT ✓ Rename | Drop
password_hash TEXT ✓ Rename | Drop
activation_token TEXT ✓ Rename | Drop
activation_expires_at TEXT ✓ Rename | Drop
activated_at TEXT ✓ Rename | Drop
password_reset_token TEXT ✓ Rename | Drop
password_reset_expires_at TEXT ✓ Rename | Drop
payment_method TEXT ✓ Rename | Drop
payment_notes TEXT ✓ Rename | Drop
marketing_consent_at TEXT ✓ Rename | Drop
marketing_consent_source TEXT ✓ Rename | Drop
marketing_token TEXT ✓ Rename | Drop
privacy_accepted_at TEXT ✓ Rename | Drop

Foreign Keys

Column Destination
trainer_id trainers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_students_activation_token activation_token ✓ SQL 
CREATE UNIQUE INDEX idx_students_activation_token
ON students(activation_token) WHERE activation_token IS NOT NULL
Drop
idx_students_calendar_token calendar_token ✓ SQL 
CREATE UNIQUE INDEX idx_students_calendar_token
ON students(calendar_token) WHERE calendar_token IS NOT NULL
Drop
idx_students_email email SQL 
CREATE INDEX idx_students_email
ON students(email) WHERE email IS NOT NULL
Drop
idx_students_reset_token password_reset_token ✓ SQL 
CREATE UNIQUE INDEX idx_students_reset_token
ON students(password_reset_token) WHERE password_reset_token IS NOT NULL
Drop
idx_students_trainer trainer_id SQL 
CREATE INDEX idx_students_trainer
ON students(trainer_id)
Drop