Database Schema

The plugin creates 16 custom tables plus uses standard WP / WC tables. All prefixed by your $wpdb->prefix (typically wp_).

Schema version: 1.5.0

Table index

Key tables in detail

wkafw_auctions

The central table — one row per auction.

Indexes: id (PK), product_id, status, start_date, end_date

wkafw_bids

Every bid placed.

Indexes: id (PK), auction_id, user_id, bid_amount, created_at, status, auction_status_amount (composite)

wkafw_watchlist

Unique index: (auction_id, user_id) — one watch per user per auction.

wkafw_proxy_bids

wkafw_auction_logs

The general activity log (per-auction events).

This is separate from the bid hash-chain (Wkafw_Bid_Audit_Chain) which uses an in-memory + file-backed structure.

wkwc_wallet_transactions

Wallet ledger (provided by submodule).

Balance is derived from this table — not stored. See Wallet System.

wkafw_vendor_commissions

Multi-vendor revenue split.

Unique index: (auction_id, vendor_id) — one commission per vendor per auction.

Relationships

wp_users (WP)
   ↓ 1:N
wkafw_bids ───► wkafw_auctions ◄─── wkafw_proxy_bids
                     │
                     ├─► wkafw_watchlist
                     ├─► wkafw_auction_logs
                     ├─► wkafw_disputes
                     ├─► wkafw_event_lots ─► wkafw_events
                     ├─► wkafw_messages
                     ├─► wkafw_auction_seats (seated)
                     ├─► wkafw_bid_packs (penny — by user)
                     └─► wkafw_vendor_commissions ─► wp_users (vendor)
                                                    └─► wp_wc_orders (HPOS) or wp_posts

wp_users
   ↓ 1:N
wkwc_wallet_transactions

Query patterns

Count active auctions

SELECT COUNT(*) FROM wp_wkafw_auctions WHERE status = 'active';

User's wallet balance

SELECT
  SUM(CASE WHEN transaction_type='cr' THEN CAST(amount AS DECIMAL(19,4)) ELSE 0 END) -
  SUM(CASE WHEN transaction_type='dr' THEN CAST(amount AS DECIMAL(19,4)) ELSE 0 END)
FROM wp_wkwc_wallet_transactions
WHERE customer = ? AND transaction_status = 'completed';

Top bidders (lifetime)

SELECT u.display_name, COUNT(*) AS bid_count
FROM wp_wkafw_bids b
JOIN wp_users u ON b.user_id = u.ID
WHERE b.status = 'active'
GROUP BY b.user_id
ORDER BY bid_count DESC
LIMIT 10;

Bid history for a specific auction

SELECT b.*, u.display_name
FROM wp_wkafw_bids b
JOIN wp_users u ON b.user_id = u.ID
WHERE b.auction_id = ? AND b.status = 'active'
ORDER BY b.created_at DESC;

Performance

Every plugin query uses indexed columns. Common access patterns are O(log N) on the relevant index.

For very large databases (1M+ bids):

• Add a composite index on (auction_id, status, created_at) for bid history pagination
• Partition wkafw_auction_logs by created_at (monthly partitions)
• Archive old auctions (status paid / failed older than 1 year) to a separate table

The plugin doesn't auto-partition or archive — operator's responsibility.

Migrations

Schema changes are versioned. On plugin upgrade, Wkafw_Installer::maybe_migrate_db() checks the stored version against the code version and runs incremental migrations.

Manual migration: Tools → Database → Re-run migrations (Tools →).

HPOS

Order data is queried from:

The plugin auto-detects via \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled().

Backup recommendations

• Daily: full DB dump including all wkafw_* and wkwc_wallet_* tables
• Hourly: wkafw_bids + wkafw_auction_logs (for audit recovery)
• Real-time: replicate to a read-only standby (for high-availability sites)

The plugin's data is not recoverable from WP exports — use mysqldump or your hosting provider's backup tool.

See also

• Hooks & Filters — actions / filters reference
• Options Reference — all 132 plugin options
• Tools → Database — admin migration / repair tools