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
)

+ 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

Foreign Keys

Column Destination
trainer_id trainers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_students_email email SQL 
CREATE INDEX idx_students_email
ON students(email) WHERE email IS NOT NULL
Drop
idx_students_trainer trainer_id SQL 
CREATE INDEX idx_students_trainer
ON students(trainer_id)
Drop