Query

billing.db — payments

SQL

CREATE TABLE payments  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  invoice_id INTEGER NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  payment_method TEXT NOT NULL,
  reference_number TEXT,
  notes TEXT,
  remittance_status TEXT CHECK(remittance_status IN ('pending', 'remitted', 'cancelled')),
  remittance_date DATETIME,
  remittance_notes TEXT,
  collector_id INTEGER,
  commission_amount DECIMAL(15,2) DEFAULT 0,
  payment_type TEXT DEFAULT 'direct' CHECK(payment_type IN ('direct', 'collector', 'online', 'manual')),
  FOREIGN KEY (invoice_id) REFERENCES invoices (id)
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
invoice_id INTEGER Rename | Drop
amount DECIMAL(10,2) Rename | Drop
payment_date DATETIME ✓ Rename | Drop
payment_method TEXT Rename | Drop
reference_number TEXT ✓ Rename | Drop
notes TEXT ✓ Rename | Drop
remittance_status TEXT ✓ Rename | Drop
remittance_date DATETIME ✓ Rename | Drop
remittance_notes TEXT ✓ Rename | Drop
collector_id INTEGER ✓ Rename | Drop
commission_amount DECIMAL(15,2) ✓ Rename | Drop
payment_type TEXT ✓ Rename | Drop

Foreign Keys

Column Destination
invoice_id invoices.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_payments_collector_id collector_id SQL 
CREATE INDEX idx_payments_collector_id
ON payments(collector_id)
Drop
idx_payments_payment_type payment_type SQL 
CREATE INDEX idx_payments_payment_type
ON payments(payment_type)
Drop
idx_payments_remittance_date remittance_date SQL 
CREATE INDEX idx_payments_remittance_date
ON payments(remittance_date)
Drop
idx_payments_remittance_status remittance_status SQL 
CREATE INDEX idx_payments_remittance_status
ON payments(remittance_status)
Drop