Query

billing.db — invoices

SQL

CREATE TABLE invoices  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER NOT NULL,
  package_id INTEGER NOT NULL,
  invoice_number TEXT UNIQUE NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  due_date DATE NOT NULL,
  status TEXT DEFAULT 'unpaid',
  payment_date DATETIME,
  payment_method TEXT,
  payment_gateway TEXT,
  payment_token TEXT,
  payment_url TEXT,
  payment_status TEXT DEFAULT 'pending',
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  description TEXT NULL,
  invoice_type TEXT DEFAULT 'monthly' CHECK (invoice_type IN ('monthly', 'voucher', 'manual')),
  package_name TEXT NULL,
  base_amount DECIMAL(10,2),
  tax_rate DECIMAL(5,2),
  FOREIGN KEY (customer_id) REFERENCES customers (id),
  FOREIGN KEY (package_id) REFERENCES packages (id)
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
customer_id INTEGER Rename | Drop
package_id INTEGER Rename | Drop
invoice_number TEXT Rename | Drop
amount DECIMAL(10,2) Rename | Drop
due_date DATE Rename | Drop
status TEXT ✓ Rename | Drop
payment_date DATETIME ✓ Rename | Drop
payment_method TEXT ✓ Rename | Drop
payment_gateway TEXT ✓ Rename | Drop
payment_token TEXT ✓ Rename | Drop
payment_url TEXT ✓ Rename | Drop
payment_status TEXT ✓ Rename | Drop
notes TEXT ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
description TEXT ✓ Rename | Drop
invoice_type TEXT ✓ Rename | Drop
package_name TEXT ✓ Rename | Drop
base_amount DECIMAL(10,2) ✓ Rename | Drop
tax_rate DECIMAL(5,2) ✓ Rename | Drop

Foreign Keys

Column Destination
package_id packages.id
customer_id customers.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_invoices_base_amount base_amount SQL 
CREATE INDEX idx_invoices_base_amount
ON invoices(base_amount)
Drop
idx_invoices_customer customer_id SQL 
CREATE INDEX idx_invoices_customer
ON invoices(customer_id)
Drop
idx_invoices_due_date due_date SQL 
CREATE INDEX idx_invoices_due_date
ON invoices(due_date)
Drop
idx_invoices_invoice_type invoice_type SQL 
CREATE INDEX idx_invoices_invoice_type
ON invoices(invoice_type)
Drop
idx_invoices_package_name package_name SQL 
CREATE INDEX idx_invoices_package_name
ON invoices(package_name)
Drop
idx_invoices_status status SQL 
CREATE INDEX idx_invoices_status
ON invoices(status)
Drop
idx_invoices_tax_rate tax_rate SQL 
CREATE INDEX idx_invoices_tax_rate
ON invoices(tax_rate)
Drop
sqlite_autoindex_invoices_1 invoice_number ✓ SQL
-- no sql found --
 Drop