Query

billing.db — payment_gateway_trans...

SQL

CREATE TABLE payment_gateway_transactions  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  invoice_id INTEGER NOT NULL,
  gateway TEXT NOT NULL,
  order_id TEXT NOT NULL,
  payment_url TEXT,
  token TEXT,
  amount DECIMAL(10,2) NOT NULL,
  status TEXT DEFAULT 'pending',
  payment_type TEXT,
  fraud_status TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  payment_method VARCHAR(50),
  gateway_name VARCHAR(50),
  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
gateway TEXT Rename | Drop
order_id TEXT Rename | Drop
payment_url TEXT ✓ Rename | Drop
token TEXT ✓ Rename | Drop
amount DECIMAL(10,2) Rename | Drop
status TEXT ✓ Rename | Drop
payment_type TEXT ✓ Rename | Drop
fraud_status TEXT ✓ Rename | Drop
created_at DATETIME ✓ Rename | Drop
updated_at DATETIME ✓ Rename | Drop
payment_method VARCHAR(50) ✓ Rename | Drop
gateway_name VARCHAR(50) ✓ Rename | Drop

Foreign Keys

Column Destination
invoice_id invoices.id

+ Add index

Indexes

Name Columns Unique SQL Drop?
idx_payment_gateway_transactions_invoice_id invoice_id SQL 
CREATE INDEX idx_payment_gateway_transactions_invoice_id
ON payment_gateway_transactions(invoice_id)
Drop
idx_payment_gateway_transactions_order_id order_id SQL 
CREATE INDEX idx_payment_gateway_transactions_order_id
ON payment_gateway_transactions(order_id)
Drop