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
link_id	int	YES		NULL
guest_first_name	varchar(100)	NO		NULL
guest_last_name	varchar(100)	NO		NULL
guest_email	varchar(255)	YES		NULL
guest_phone	varchar(50)	YES		NULL
guest_address	varchar(500)	YES		NULL
booking_hotel_id	int	YES		NULL
room_type	varchar(100)	YES		NULL
rate_per_night	decimal(10,2)	YES		NULL
occupants_json	text	YES		NULL
special_requests	text	YES		NULL
cardholder_name	varchar(150)	YES		NULL
card_brand	varchar(30)	YES		NULL
card_last4	char(4)	YES		NULL
exp_month	tinyint unsigned	YES		NULL
exp_year	smallint unsigned	YES		NULL
encrypted_payload	mediumtext	YES		NULL
nonce_b64	varchar(255)	YES		NULL
cipher_meta	varchar(40)	YES		NULL
key_version	varchar(30)	YES		NULL
consent_text	varchar(500)	YES		NULL
consent_accepted_at	datetime	YES		NULL
status	enum('pending','approved','rejected','withdrawn')	NO		pending
approved_room_id	int	YES		NULL
approved_room_instance	smallint unsigned	YES		NULL
reviewed_by	varchar(255)	YES		NULL
reviewed_at	datetime	YES		NULL
review_notes	varchar(500)	YES		NULL
ip_address	varchar(64)	YES		NULL
user_agent	varchar(500)	YES		NULL
created_at	timestamp	YES	MUL	CURRENT_TIMESTAMP	DEFAULT_GENERATED
updated_at	timestamp	YES		CURRENT_TIMESTAMP	DEFAULT_GENERATED on update CURRENT_TIMESTAMP
block_booking_hotel_id	int	YES		NULL
block_booking_room_id	int	YES		NULL
chosen_hotel_name	varchar(255)	YES		NULL
code_of_conduct_ack_at	datetime	YES		NULL

Sample Data (First 20 rows)

id	booking_id	link_id	guest_first_name	guest_last_name	guest_email	guest_phone	guest_address	booking_hotel_id	room_type	rate_per_night	occupants_json	special_requests	cardholder_name	card_brand	card_last4	exp_month	exp_year	encrypted_payload	nonce_b64	cipher_meta	key_version	consent_text	consent_accepted_at	status	approved_room_id	approved_room_instance	reviewed_by	reviewed_at	review_notes	ip_address	user_agent	created_at	updated_at	block_booking_hotel_id	block_booking_room_id	chosen_hotel_name	code_of_conduct_ack_at
1	8	6	John	Smith	[email protected]	NULL	NULL	15	1_king	165.00	[{"first_name":"4514011690207533","last_name":"Jason Johnson","guest_type":"adult"}]	NULL	Jason Johnson	visa	7533	9	2028	NULL	NULL	sodium_xchacha20poly1305	v1	I authorize Canaccom Travel Group to securely share my credit card details with the hotel for the purpose of holding and paying for this room reservation. I understand the card details will be stored encrypted and removed after the stay.	2026-06-25 08:35:06	approved	128	3	admin	2026-06-25 08:50:24	NULL	162.158.146.238	Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/149.0.0.0 Safari/537.36	2026-06-25 08:35:06	2026-06-25 08:50:24	NULL	NULL	NULL	2026-06-25 08:35:06
2	8	6	John	Smith	NULL	NULL	NULL	15	1_king	165.00	NULL	NULL	Jason Johnson	visa	6332	9	2028	NULL	NULL	sodium_xchacha20poly1305	v1	I authorize Canaccom Travel Group to securely share my credit card details with the hotel for the purpose of holding and paying for this room reservation. I understand the card details will be stored encrypted and removed after the stay.	2026-06-25 08:49:44	approved	128	4	admin	2026-06-25 08:50:26	NULL	162.158.146.238	Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/149.0.0.0 Safari/537.36	2026-06-25 08:49:44	2026-06-25 08:50:26	NULL	NULL	NULL	2026-06-25 08:49:44
3	9	7	Tom	Smith	[email protected]	NULL	NULL	17	2_queens	165.00	[{"first_name":"Joe","last_name":"Blow","guest_type":"adult"},{"first_name":"","last_name":"Johnson","guest_type":"adult"}]	NULL	Jason Johnson	visa	7533	9	2028	NULL	NULL	sodium_xchacha20poly1305	v1	I authorize Canaccom Travel Group to securely share my credit card details with the hotel for the purpose of holding and paying for this room reservation. I understand the card details will be stored encrypted and removed after the stay.	2026-06-25 10:30:05	approved	134	12	admin	2026-06-25 10:48:30	NULL	162.158.146.238	Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/149.0.0.0 Safari/537.36	2026-06-25 10:30:05	2026-06-25 10:48:30	NULL	NULL	NULL	2026-06-25 10:30:05