Query

pilot.db — trainers

SQL

CREATE TABLE trainers  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  totp_secret TEXT,
  -- Base32,
  NULL = 2FA nicht eingerichtet
  totp_enabled INTEGER NOT NULL DEFAULT 0,
  -- 0/1; nur aktiv wenn erfolgreich verifiziert
  backup_codes TEXT,
  -- JSON-Array der gehashten Backup-Codes
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  last_login_at TEXT,
  account_id INTEGER REFERENCES accounts(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'owner'
  CHECK(role IN ('owner','member')),
  email_verified_at TEXT,
  verify_token TEXT,
  verify_token_expires_at TEXT
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
email TEXT Rename | Drop
name TEXT Rename | Drop
password_hash TEXT Rename | Drop
totp_secret TEXT ✓ Rename | Drop
totp_enabled INTEGER Rename | Drop
backup_codes TEXT ✓ Rename | Drop
created_at TEXT Rename | Drop
last_login_at TEXT ✓ Rename | Drop
account_id INTEGER ✓ Rename | Drop
role TEXT Rename | Drop
email_verified_at TEXT ✓ Rename | Drop
verify_token TEXT ✓ Rename | Drop
verify_token_expires_at TEXT ✓ Rename | Drop

Foreign Keys

Column Destination
account_id accounts.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_trainers_account account_id SQL 
CREATE INDEX idx_trainers_account
ON trainers(account_id)
Drop
idx_trainers_verify_token verify_token SQL 
CREATE INDEX idx_trainers_verify_token
ON trainers(verify_token)
Drop
sqlite_autoindex_trainers_1 email ✓ SQL
-- no sql found --
 Drop