Query

pilot.db — saas_invoices

SQL

CREATE TABLE saas_invoices  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  invoice_no TEXT NOT NULL UNIQUE,
  -- z.B. "GS-2026-0001"
  period_start TEXT NOT NULL,
  period_end TEXT NOT NULL,
  seats INTEGER NOT NULL,
  unit_price_cents INTEGER NOT NULL,
  subtotal_cents INTEGER NOT NULL,
  vat_rate INTEGER NOT NULL,
  -- 19 | 20 | 0
  vat_cents INTEGER NOT NULL,
  total_cents INTEGER NOT NULL,
  currency TEXT NOT NULL DEFAULT 'EUR',
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK(status IN ('draft','open','paid','failed','void')),
  reverse_charge INTEGER NOT NULL DEFAULT 0,
  -- 1 = 0% USt mit Reverse-Charge-Hinweis
  mollie_payment_id TEXT,
  paid_at TEXT,
  pdf_path TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  commission_base_cents INTEGER NOT NULL DEFAULT 0,
  commission_pct REAL,
  commission_cents INTEGER NOT NULL DEFAULT 0
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
account_id INTEGER Rename | Drop
invoice_no TEXT Rename | Drop
period_start TEXT Rename | Drop
period_end TEXT Rename | Drop
seats INTEGER Rename | Drop
unit_price_cents INTEGER Rename | Drop
subtotal_cents INTEGER Rename | Drop
vat_rate INTEGER Rename | Drop
vat_cents INTEGER Rename | Drop
total_cents INTEGER Rename | Drop
currency TEXT Rename | Drop
status TEXT Rename | Drop
reverse_charge INTEGER Rename | Drop
mollie_payment_id TEXT ✓ Rename | Drop
paid_at TEXT ✓ Rename | Drop
pdf_path TEXT ✓ Rename | Drop
created_at TEXT Rename | Drop
commission_base_cents INTEGER Rename | Drop
commission_pct REAL ✓ Rename | Drop
commission_cents INTEGER Rename | Drop

Foreign Keys

Column Destination
account_id accounts.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_saas_invoices_account
  • account_id
  • period_start
 SQL 
CREATE INDEX idx_saas_invoices_account
ON saas_invoices(account_id, period_start)
Drop
idx_saas_invoices_mollie_payment mollie_payment_id SQL 
CREATE INDEX idx_saas_invoices_mollie_payment
ON saas_invoices(mollie_payment_id)
Drop
idx_saas_invoices_status status SQL 
CREATE INDEX idx_saas_invoices_status
ON saas_invoices(status)
Drop
sqlite_autoindex_saas_invoices_1 invoice_no ✓ SQL
-- no sql found --
 Drop