Database Tables - Canaccom Travel Group Platform

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NULL	auto_increment
booking_id	int	NO	MUL	NULL
block_booking_id	int	YES		NULL
invoice_number	varchar(50)	NO	UNI	NULL
share_token	varchar(64)	YES	UNI	NULL
invoice_date	date	NO	MUL	NULL
due_date	date	NO	MUL	NULL
invoice_type	enum('hotel','client','commission')	NO	MUL	NULL
recipient_name	varchar(255)	NO		NULL
recipient_email	varchar(255)	YES		NULL
recipient_contact	varchar(255)	YES		NULL
recipient_address	text	YES		NULL
recipient_phone	varchar(20)	YES		NULL
group_name	varchar(255)	YES		NULL
status	enum('draft','sent','paid','overdue','cancelled')	YES	MUL	draft
paid_date	date	YES	MUL	NULL
viewed_at	datetime	YES		NULL
sent_at	datetime	YES		NULL
payment_method	varchar(100)	YES		NULL
payment_reference	varchar(100)	YES		NULL
subtotal	decimal(12,2)	NO		0.00
tax_amount	decimal(10,2)	YES		0.00
tax_rate	decimal(5,2)	YES		0.00
discount_amount	decimal(10,2)	YES		0.00
commission_rate	decimal(5,2)	YES		12.00
commission_amount	decimal(10,2)	YES		0.00
additional_charges	decimal(10,2)	YES		0.00
total_amount	decimal(12,2)	NO		0.00
amount_paid	decimal(12,2)	YES		0.00
balance_due	decimal(12,2)	YES		0.00
notes	text	YES		NULL
terms_and_conditions	text	YES		NULL
reminder_enabled	tinyint(1)	NO		0
reminder_interval_days	int	NO		30
reminder_last_sent_at	datetime	YES		NULL
created_by	int	YES	MUL	NULL
created_at	timestamp	YES		CURRENT_TIMESTAMP	DEFAULT_GENERATED
updated_at	timestamp	YES		CURRENT_TIMESTAMP	DEFAULT_GENERATED on update CURRENT_TIMESTAMP

id	booking_id	block_booking_id	invoice_number	share_token	invoice_date	due_date	invoice_type	recipient_name	recipient_email	recipient_contact	recipient_address	recipient_phone	group_name	status	paid_date	viewed_at	sent_at	payment_method	payment_reference	subtotal	tax_amount	tax_rate	discount_amount	commission_rate	commission_amount	additional_charges	total_amount	amount_paid	balance_due	notes	terms_and_conditions	reminder_enabled	reminder_interval_days	reminder_last_sent_at	created_by	created_at	updated_at
1	1	NULL	10000	53f97aabdf7fe59d2f521e690745390aae67ba372f0d992e	2026-03-11	2026-04-10	hotel	Calgary Marriott Downtown Hotel	NULL	Jason Johnson	110 9 Ave SE, Calgary, AB T2G 0P5, Canada Calgary, AB T2G 0P5	(403) 266-7331	St. Albert Slash U18AAA	paid	2026-03-13	2026-03-13 19:43:22	NULL	E-Transfer	NULL	25263.00	126.31	5.00	0.00	10.00	2526.30	233.00	2885.62	2885.62	0.00	NULL	Payment due within 30 days of invoice date. Please remit to: [email protected]	1	30	NULL	NULL	2026-03-11 21:36:30	2026-03-13 19:43:22