Query

pilot.db — invoices

SQL

CREATE TABLE invoices  (
  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,
  invoice_no TEXT NOT NULL,
  -- z. B. "2026-0042" — fortlaufend pro Trainer
  category TEXT NOT NULL,
  -- 'lesson' | 'card' | 'video' | 'bulk'
  lines TEXT NOT NULL,
  -- JSON: [{ service,
  when,
  gross_cents }]
  total_cents INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'open',
  -- 'open' | 'paid' | 'cancelled'
  issued_at TEXT NOT NULL DEFAULT (datetime('now')),
  paid_at TEXT,
  archived_at TIMESTAMP NULL
)

+ 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
invoice_no TEXT Rename | Drop
category TEXT Rename | Drop
lines TEXT Rename | Drop
total_cents INTEGER Rename | Drop
status TEXT Rename | Drop
issued_at TEXT Rename | Drop
paid_at TEXT ✓ Rename | Drop
archived_at TIMESTAMP ✓ Rename | Drop

Foreign Keys

Column Destination
student_id students.id
trainer_id trainers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_invoices_archived
  • trainer_id
  • archived_at
 SQL 
CREATE INDEX idx_invoices_archived
ON invoices(trainer_id, archived_at)
Drop
idx_invoices_no
  • trainer_id
  • invoice_no
 ✓ SQL 
CREATE UNIQUE INDEX idx_invoices_no
ON invoices(trainer_id, invoice_no)
Drop
idx_invoices_student student_id SQL 
CREATE INDEX idx_invoices_student
ON invoices(student_id)
Drop
idx_invoices_trainer trainer_id SQL 
CREATE INDEX idx_invoices_trainer
ON invoices(trainer_id)
Drop