Database Schema

Eventonomy uses no custom post types. Every event, occurrence, RSVP, ticket and order lives in its own table, which is what lets a list of 20,000 events paginate with an indexed LIMIT instead of a meta_query.

Free creates 10 tables; Pro adds 5. All use the evnm_ prefix after the WordPress table prefix (so wp_evnm_events on a default install).

Read this before writing SQL: the Import and Admin layers must never issue raw $wpdb queries - that is architecture invariant E1. Go through a repository contract (EventRepositoryInterface and friends). This page exists so you can reason about indexes and query shape, not so you can bypass the repositories.

Conventions

• Primary keys are bigint unsigned AUTO_INCREMENT.
• Times are stored twice where it matters: *_utc is the instant, *_local is the wall-clock the organizer typed. Never derive one from the other at read time.
• Money is decimal(14,4). Four decimal places, not two, so currencies with three (KWD, BHD, OMR, JOD) are exact at rest. See docs/standards/MONEY.md in the Pro repo.
• Status columns are varchar(20) holding a domain string, never an enum, so a filter can add one.
• longtext columns holding JSON (settings, meta, recurrence, billing, venue, organizer, response, config, early_bird) are decoded by the repository. Treat them as opaque from SQL; there is no JSON indexing.

Free Tables

evnm_events (28 columns)

The core record. Note next_occurrence_utc - a denormalized pointer to the soonest unfinished occurrence, maintained by a daily cron. Every "upcoming" query sorts on it, which is why listings stay fast without joining occurrences.

Column	Type	Notes
id	bigint PK
author_id	bigint	Owning member
title · slug	varchar(255) · varchar(200)	slug is unique-ified on create
content · excerpt	longtext · text
status	varchar(20)	draft｜pending｜published｜cancelled｜private - published, not WordPress's publish
visibility	varchar(20)	public｜private - the private branch is a real WHERE clause, not a display filter
featured · is_virtual · is_recurring · all_day	tinyint(1)
timezone	varchar(64)	Olson name
next_occurrence_utc	datetime	Denormalized pointer (see above)
organizer_id · venue_id	bigint	Canonical link to the shared catalog
organizer · venue	longtext	JSON snapshot for display, kept alongside the id
lat · lng	decimal(10,7)
geohash	varchar(12)	Proximity bucketing
city · country	varchar(120) · char(2)	ISO-3166 alpha-2
recurrence · settings	longtext	JSON
space_id	bigint	Community scoping
created_at · updated_at	datetime

Indexes: author_status(author_id,status), status_next(status,next_occurrence_utc), featured_next(featured,next_occurrence_utc), space_status(space_id,status), geohash_idx, city_idx, slug_idx, title_idx, plus a FULLTEXT ft_search(title,content).

The composite ordering is deliberate: the public archive filters on status and sorts on next_occurrence_utc, so status_next serves both halves from one index.

evnm_occurrences (11 columns)

One row per materialized instance. A one-off event has exactly one; a recurring series has one per expansion, capped by the recurrence horizon.

event_id, start_utc, end_utc, start_local, end_local, status, is_exception (a moved/cancelled single instance), seq, timestamps. Indexes: unique event_start(event_id,start_utc), start_status(start_utc,status).

That uniqueness is load-bearing: re-materializing a series cannot double-insert the same instant for the same event, so a recompute is idempotent.

evnm_rsvps (19 columns)

Attendance. Covers member RSVPs, guest RSVPs and paid-ticket attendees - one shape, because a "who is coming" question should never need a UNION.

Key columns: event_id, occurrence_id, user_id or guest_name/guest_email (members store neither), status, guests_count, waitlist_position, ticket_id, order_id, checkin_token char(32), checked_in_at, magic_token char(64) + magic_expires, response (JSON answers), guest_phone.

checkin_token and magic_token are secrets. They must never appear in REST output or an admin table. Indexes: unique checkin(checkin_token), event_status(event_id,status), occ_status, user_status, email_idx, order_idx, ticket_idx, status_idx.

evnm_tickets (19 columns)

Ticket types, not issued tickets (an issued ticket is an RSVP row). event_id, name, description, type (free｜donation｜paid), price decimal(14,4), currency, capacity, sold, min_per_order/max_per_order, sales_start/ sales_end, early_bird (JSON), sort_order, status, settings.

sold is a denormalized counter incremented under a conditional update - that atomic claim is what prevents overselling. Indexes: event_status, sales_window(sales_start,sales_end).

evnm_orders (20 columns)

order_number, event_id, user_id, email, name, subtotal/discount/tax/ total (all decimal(14,4)), currency, gateway, gateway_txn_id, coupon_code, status, billing (JSON snapshot taken at purchase), meta (JSON - holds the reserved-seat list, refund ledger and access token), idempotency_key.

gateway_txn_id and the whole meta blob are withheld from REST output. Indexes: unique order_number, unique idempotency, event_idx, user_idx, status_date, gateway_txn, email_idx, event_status(event_id,status).

evnm_venues (18) and evnm_organizers (14)

The shared, dedupable catalog. Both carry slug, status, verified, merged_into (a merge leaves a tombstone rather than breaking references), created_by, and a FULLTEXT index. Venues add address/city/country/lat/lng/capacity; organizers add url/email/user_id.

evnm_meta (5 columns)

Generic key/value for any object: meta_id (PK), object_type, object_id, meta_key, meta_value. Unique index object_lookup(object_type,object_id,meta_key) makes a single-key fetch a point lookup (type=ref, rows=1), which is what keeps import dedup O(1) per row - and, being unique, makes a write an upsert rather than a duplicate. A second index key_idx(meta_key(100)) serves the "find every row with this key" direction, which is how the import redirect table is scanned.

Unlike WordPress post meta there is one row per key, not many.

evnm_notifications (9 columns)

The in-app feed, also served to the mobile app. user_id, type, title, body, object_type/object_id, read_at, created_at. Indexes: user_unread(user_id,read_at), user_created(user_id,created_at).

evnm_webhook_events (4 columns)

gateway, event_id (the gateway's id), received_at, with a unique provider_event(gateway,event_id). That uniqueness is the idempotency mechanism: a replayed webhook fails the insert and is ignored.

Pro Tables

evnm_earnings (16 columns)

One row per paid order: gross, commission, net, commission_rate, currency, status (booked｜paid｜reversed), payout_id, plus the Connect settlement trio settlement (platform｜connect), transfer_id, destination_account, so a Connect-settled row is self-describing. Unique order_earning(order_id) makes booking idempotent.

evnm_payouts (14 columns)

What was actually paid: organizer_id, net, gross, debt_applied, earnings_count, currency, reference, note, method, transfer_id, destination_account, created_by.

evnm_payout_debts (9 columns)

Refund-after-payout netting. Unique order_debt(order_id) keeps the debt idempotent; settled_amount supports partial settlement carried across payouts.

evnm_follows (6 columns)

user_id, object_type (event｜organizer｜member｜space｜category), object_id, notify. Unique follow(user_id,object_type,object_id).

evnm_pro_feeds (11 columns)

Calendar Sync subscriptions: owner_id (0 = site-wide), source_type, config (JSON, tokens encrypted at rest), cadence, status, last_run_utc, last_status, last_error, last_hash. Indexes: owner_status, due(status,cadence).

Migrations

Schema changes go through Core\Migrator, version-gated by an option. Bump CURRENT_VERSION and add a numbered case; migrations must be idempotent - they run again on any site that upgrades in steps.

Free is at v12 (Migrator::CURRENT_VERSION, stored in the evnm_db_version option). Pro is at v10 on its own counter (evnm_pro_db_version). The two are independent: Pro does not read Free's version and vice versa.

Recent Free versions: v10 widened the money columns to decimal(14,4), v11 set the money display decimals from the store currency, and v12 backfilled the import-reference lookup rows. Neither counter is a data-only/schema-only marker - both carry a mix, so never assume a version bump means the tables changed.

evnm_settings, evnm_db_version and evnm_delete_data_on_uninstall are options in wp_options, not tables. Counting them as tables is what produced the wrong table counts in earlier drafts. (evnm_delete_data_on_uninstall is legacy: the migrator folds any stray value into evnm_settings['delete_data_on_uninstall'] and deletes the standalone option.)

Some changes cannot go through dbDelta() at all - it will not convert an existing index to UNIQUE, and it will not narrow a column. Those run as explicit ALTER statements after the dbDelta() call, each guarded by an existence check so a re-run is a no-op: the unique event_start on occurrences, the unique object_lookup on meta, the composite event_status on orders, and the money-column widening.

Testing a migration: wp-cli boots WordPress on every invocation, so migrations have already run before your test does. Reset, migrate and assert inside one wp eval, or you will "prove" a migration created nothing.

What's Next?

• Architecture - the layers above these tables
• Capabilities - who is allowed to touch them
• Extending - repository contracts, if you need to change the source of truth