Query

pilot.db — quotes

SQL

CREATE TABLE quotes  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  trainer_id INTEGER NOT NULL REFERENCES trainers(id) ON DELETE CASCADE,
  student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  intro TEXT,
  -- persönliche Anrede/Einleitung in Mail & Angebotsseite
  lines TEXT NOT NULL DEFAULT '[]',
  -- JSON: [{ label,
  qty,
  price_cents }]
  total_cents INTEGER NOT NULL DEFAULT 0,
  valid_until TEXT,
  -- ISO-Datum; danach nicht mehr annehmbar
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK(status IN ('draft','sent','accepted','declined','expired','cancelled')),
  token TEXT UNIQUE,
  -- öffentlicher Annahme-Link
  auto_invoice INTEGER NOT NULL DEFAULT 1,
  proposed_service_id INTEGER REFERENCES services(id) ON DELETE SET NULL,
  proposed_starts_at TEXT,
  -- optionaler Terminvorschlag
  invoice_id INTEGER,
  -- nach Annahme
  booking_id INTEGER,
  -- nach Annahme (wenn Terminvorschlag)
  sent_at TEXT,
  decided_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
trainer_id INTEGER Rename | Drop
student_id INTEGER Rename | Drop
title TEXT Rename | Drop
intro TEXT ✓ Rename | Drop
lines TEXT Rename | Drop
total_cents INTEGER Rename | Drop
valid_until TEXT ✓ Rename | Drop
status TEXT Rename | Drop
token TEXT ✓ Rename | Drop
auto_invoice INTEGER Rename | Drop
proposed_service_id INTEGER ✓ Rename | Drop
proposed_starts_at TEXT ✓ Rename | Drop
invoice_id INTEGER ✓ Rename | Drop
booking_id INTEGER ✓ Rename | Drop
sent_at TEXT ✓ Rename | Drop
decided_at TEXT ✓ Rename | Drop
created_at TEXT Rename | Drop
updated_at TEXT Rename | Drop

Foreign Keys

Column Destination
proposed_service_id services.id
student_id students.id
trainer_id trainers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_quotes_trainer
  • trainer_id
  • status
 SQL 
CREATE INDEX idx_quotes_trainer
ON quotes(trainer_id, status)
Drop
sqlite_autoindex_quotes_1 token ✓ SQL
-- no sql found --
 Drop