Database Architecture & Schema

Quantum Competitions uses dedicated custom MySQL tables alongside WooCommerce product postmeta to ensure high performance, prevent database bloat, and guarantee atomic concurrency during ticket sales.

All table names are accessible via core constants defined in the main plugin file:

---

1. Participant Log (QCW_LOG_TABLE)

Constant: QCW_LOG_TABLE → Default: {$wpdb->prefix}wc_lottery_log

Tracks every participation entry linking users, orders, and competition products with indexed timestamps.

Column	Type	Attributes	Description
id	BIGINT(20) UNSIGNED	NOT NULL AUTO_INCREMENT	Primary Key
userid	BIGINT(20) UNSIGNED	NOT NULL	WordPress User ID (0 for guest)
orderid	BIGINT(20) UNSIGNED	NOT NULL	Linked WooCommerce Order ID
lottery_id	BIGINT(20) UNSIGNED	DEFAULT NULL	Product ID (wp_posts.ID)
date	TIMESTAMP	NOT NULL DEFAULT CURRENT_TIMESTAMP	Order timestamp

Indexes:

• PRIMARY KEY (id)
• KEY idx_lottery_userid (lottery_id, userid)
• KEY idx_lottery_date (lottery_id, date, id)
• KEY orderid (orderid)
• KEY userid (userid)
• KEY date (date)

---

2. Ticket & Answer Allocation (QCW_PN_LOG_TABLE)

Constant: QCW_PN_LOG_TABLE → Default: {$wpdb->prefix}wc_lottery_pn_log

Stores the exact assigned ticket numbers and selected skill question answers for each entry.

Column	Type	Attributes	Description
log_id	BIGINT(20) UNSIGNED	NOT NULL	References wc_lottery_log.id
order_id	BIGINT(20) UNSIGNED	NOT NULL	Linked WooCommerce Order ID
ticket_number	BIGINT(20) UNSIGNED	DEFAULT NULL	The assigned numerical ticket
answer_id	BIGINT(20) UNSIGNED	DEFAULT NULL	Selected answer option ID
lottery_id	BIGINT(20) UNSIGNED	NOT NULL	Product ID (wp_posts.ID)

Indexes:

• PRIMARY KEY (log_id)
• KEY lottery_id (lottery_id)
• KEY idx_lottery_ticket (lottery_id, ticket_number)
• KEY order_id (order_id)

---

3. Cart Reservations & Locks (QCW_PN_RESERVED_TABLE)

Constant: QCW_PN_RESERVED_TABLE → Default: {$wpdb->prefix}wc_lottery_pn_reserved

Maintains temporary locks on ticket numbers currently in customer carts to eliminate double-booking race conditions during high-traffic drops.

Column	Type	Attributes	Description
lottery_id	BIGINT(20) UNSIGNED	NOT NULL	Product ID
ticket_number	BIGINT(20) UNSIGNED	NOT NULL	Locked ticket number
order_id	BIGINT(20) UNSIGNED	NOT NULL	Active order / cart checkout ID
date	TIMESTAMP	NOT NULL DEFAULT CURRENT_TIMESTAMP	Lock creation timestamp
session_key	CHAR(32)	NOT NULL	Unique customer session identifier

Indexes:

• PRIMARY KEY (lottery_id, ticket_number)
• KEY idx_lottery_session_date (lottery_id, session_key, date)
• KEY date (date)

---

4. Instant Win Winners Log (QCW_PN_INSTANTWIN_TABLE)

Constant: QCW_PN_INSTANTWIN_TABLE → Default: {$wpdb->prefix}wc_lottery_pn_instantwin

Audit log of all instant win prizes claimed by participants.

Column	Type	Attributes	Description
log_id	BIGINT(20) UNSIGNED	NOT NULL AUTO_INCREMENT	Primary Key
order_id	BIGINT(20) UNSIGNED	NOT NULL	Winning WooCommerce Order ID
user_id	BIGINT(20) UNSIGNED	NOT NULL	Customer User ID
user_mail	VARCHAR(100)	NULL DEFAULT NULL	Customer billing email
user_display_name	VARCHAR(255)	NULL DEFAULT NULL	Customer display name
user_first_name	VARCHAR(255)	NULL DEFAULT NULL	Customer first name
user_last_name	VARCHAR(255)	NULL DEFAULT NULL	Customer last name
lottery_title	VARCHAR(255)	NULL DEFAULT NULL	Competition title
ticket_number	BIGINT(20) UNSIGNED	DEFAULT NULL	Winning ticket number
lottery_id	BIGINT(20) UNSIGNED	NOT NULL	Competition Product ID
prize	VARCHAR(255)	NULL DEFAULT NULL	Awarded prize title
date	TIMESTAMP	NOT NULL DEFAULT CURRENT_TIMESTAMP	Award timestamp

Indexes:

• PRIMARY KEY (log_id)
• KEY lottery_id (lottery_id)
• KEY order_id (order_id)
• KEY user_id (user_id)
• KEY date (date)

---

5. Instant Win Prize Configuration (QCW_PN_INSTANT_PRIZES_TABLE)

Constant: QCW_PN_INSTANT_PRIZES_TABLE → Default: {$wpdb->prefix}wc_lottery_pn_instant_prizes

Stores the catalog of instant win prizes configured per ticket number for each competition.

Column	Type	Attributes	Description
ID	BIGINT(20) UNSIGNED	NOT NULL AUTO_INCREMENT	Primary Key
ticket_number	BIGINT(20) UNSIGNED	NOT NULL	Designated lucky ticket number
lottery_id	BIGINT(20) UNSIGNED	NOT NULL	Competition Product ID
prize	VARCHAR(255)	NULL DEFAULT NULL	Prize label
value	DECIMAL(12,2)	NULL DEFAULT NULL	Cash or prize monetary value
category	VARCHAR(255)	NULL DEFAULT NULL	Prize category (e.g. Cash, Product, Credit)
img	VARCHAR(255)	NULL DEFAULT NULL	Prize image attachment URL
custom_data	LONGTEXT	NULL DEFAULT NULL	Serialized extra attributes / metadata

Indexes:

• PRIMARY KEY (ID)
• KEY lottery_id (lottery_id)
• KEY ticket_number (ticket_number)

---

Database Repair & Maintenance

If database tables ever need to be verified or updated according to schema definitions, administrators can navigate to: Competitions > Settings > Tools > click Repair Database Tables. This executes WordPress dbDelta() routines to ensure all tables, columns, and indexes are synchronized.

High-Volume Scaling & Custom Architecture

Planning a high-concurrency raffle portal handling millions of ticket rows, Redis caching layers, or custom analytics pipelines? Consult with the wpgenie engineering team for performance tuning and custom architecture.