Query

billing.db — collector_payments

SQL

CREATE TABLE collector_payments  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  collector_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL,
  invoice_id INTEGER NOT NULL,
  payment_amount DECIMAL(15,2) NOT NULL,
  commission_amount DECIMAL(15,2) NOT NULL,
  payment_method TEXT DEFAULT 'cash' CHECK(payment_method IN ('cash', 'transfer', 'other')),
  payment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  notes TEXT,
  status TEXT DEFAULT 'completed' CHECK(status IN ('completed', 'pending', 'cancelled')),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  collected_at DATETIME,
  FOREIGN KEY (collector_id) REFERENCES collectors(id),
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (invoice_id) REFERENCES invoices(id)
)

+ Add column

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ Rename | Drop
collector_id INTEGER Rename | Drop
customer_id INTEGER Rename | Drop
invoice_id INTEGER Rename | Drop
payment_amount DECIMAL(15,2) Rename | Drop
commission_amount DECIMAL(15,2) Rename | Drop
payment_method TEXT ✓ Rename | Drop
payment_date DATETIME ✓ Rename | Drop
notes TEXT ✓ Rename | Drop
status TEXT ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop
collected_at DATETIME ✓ Rename | Drop

Foreign Keys

Column Destination
invoice_id invoices.id
customer_id customers.id
collector_id collectors.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_collector_payments_collector_id collector_id SQL 
CREATE INDEX idx_collector_payments_collector_id
ON collector_payments(collector_id)
Drop
idx_collector_payments_customer_id customer_id SQL 
CREATE INDEX idx_collector_payments_customer_id
ON collector_payments(customer_id)
Drop
idx_collector_payments_invoice_id invoice_id SQL 
CREATE INDEX idx_collector_payments_invoice_id
ON collector_payments(invoice_id)
Drop
idx_collector_payments_payment_date payment_date SQL 
CREATE INDEX idx_collector_payments_payment_date
ON collector_payments(payment_date)
Drop

Triggers

Name SQL Drop?
update_collector_payments_updated_at SQL 
CREATE TRIGGER update_collector_payments_updated_at
    AFTER UPDATE ON collector_payments
    FOR EACH ROW
BEGIN
    UPDATE collector_payments SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END
Drop