Configure an Event

Overview

This procedure sets up the structure of a new event: the event record, its event_category rows, its event_race_type rows, the course per distance, the race matrix, the start_group rows, and the WooCommerce/admin-service products that price the entries and fees.

It assumes the organisation (tenant) has already been provisioned — see Provision a Registration Portal Tenant for the org, RegistrationSystem, WooCommerce site and default product. This guide covers the per-event configuration on top of that.

Today this is a manual procedure run through SQL. It is defined by the outcome it produces, not the tools used. The admin-portal use case E03 Event Setup will automate it; keep this page current as its source of truth. For the entity model and design rationale behind these tables, see Event / Race / Participant Design, Event Entities, Race Configuration: Rounds & Heats, and EventCategory Cascading Inheritance.

The event structure

Event
 ├── EventCategory   (one per entry class — as fine-grained as possible)
 ├── EventRaceType   (Event ↔ RaceType join, e.g. Road, MTB, E-Bike)
 ├── Course          (one per distance)
 └── Race            = EventCategory × EventRaceType (+ Course)
      └── StartGroup (one or more; combine categories that start together)

Two rules drive the whole design:

  • An EventParticipant has exactly one EventCategory but many EventRaceType\s.

  • A Race is the matrix intersection of one EventCategory and one EventRaceType (plus a Course).

Make categories as fine-grained as possible. It is always easy to combine categories into a shared StartGroup or Race later, but very hard to split participants back out of a category. So create e.g. 120km Road and 120km E-Bike as separate EventCategories even if they will ride together — then combine them into one StartGroup if they start as one group.

Database and access

Event structure lives in the admin-service Event DB (wpca_prod on the idealogic-prod cluster in the 2026 H2 parallel run). Reach it by SSH tunnel — see the Number & Tag Runbook for the tunnel recipe:

mysql -h 127.0.0.1 -P 3307 -u claude -p"$(cat ~/dev/ems/claude.pwd)" wpca_prod

Run the whole set of inserts inside a transaction and use LAST_INSERT_ID() (captured into @-variables) to wire the foreign keys, so a mistake rolls back cleanly.

Prerequisites

  • The organisation is provisioned (tenant guide).

  • Event details: name, start/end date-time, timezone, venue, and the owning organisation_id.

  • The category list (each entry class you will offer), the race types (Road / MTB / E-Bike / …), and the distances.

  • CSA rules for the event (membership / licence / day-licence policy) if it is a sanctioned event.

E1. Create the Event

Clone a comparable prior event and change the details, rather than building the row from scratch. The event table has many columns; cloning guarantees the NOT NULL flags and inherited settings are consistent. The tables below are the authoritative field list — use them both to verify a clone and as the field set for the future event-creation wizard.

Required fields (NOT NULL)

Every field here must be set for a valid event row.

Column Type Notes

name

varchar(100)

Event name.

timezone

varchar(50)

IANA zone, e.g. Africa/Johannesburg.

start_date_time

datetime

Event start (zoned).

end_date_time

datetime

Event end (zoned).

all_day

bit

All-day event flag.

organiser_id

FK → organisation

The organising organisation (the tenant/owner).

sanctioning_organisation_id

FK → organisation

The sanctioning body (may equal the organiser).

csa_membership_required

bit

Entry requires CSA membership.

csa_license_required

bit

Entry requires a CSA licence.

allow_uci_license

bit

Accept a UCI licence (default 0).

csa_strict_registration

bit

Enforce strict CSA validation.

csa_day_license_disallow

bit

Block the day-licence option (0 = day licence allowed).

custom_list_1_required

bit

Custom list 1 mandatory (default 0).

custom_list_2_required

bit

Custom list 2 mandatory (default 0).

custom_list_3_required

bit

Custom list 3 mandatory (default 0).

auto_assign_numbers_on_registration

bit

Auto-allocate race numbers at registration (default 0).

preferred_timing_identifier

varchar(20)

Enum, default PERSON_ID. One of EPID, PERSON_ID, REGISTRATION_ID. Becomes read-only once preferred_timing_identifier_locked_at is set (first results export).

Key optional fields

Not NOT NULL, but commonly set — and part of the wizard’s field set.

Column Type Notes

venue, address, city, postal_code, province, country

varchar

Location.

latitude, longitude

decimal(10,6)

Geocode.

contact_email, contact_name, contact_url

varchar

Public contact.

ticket_url, cost, calendar_type

varchar

Listing metadata.

product_mode

char(1)

ProductMode enum: EVENT_ONLY, CATEGORY_ELSE_EVENT, BOTH — where entry products resolve from.

series_id

FK → series

Series the event belongs to; inherits Series defaults (products, categories).

number_type_id, tag_type_id

FK

Number/tag inventory types for this event.

source_registration_system_id

FK → registration_system

Registration system the event’s entries come through.

product_default_id

FK → product

Default entry product (overrides the Series default; see E7).

product_number_first_id, product_number_add_id

FK → product

Fees for first / additional race number.

product_tag_first_id, product_tag_add_id

FK → product

Fees for first / additional tag.

product_day_license_id

FK → product

CSA day-licence product (see E7.3).

custom_list_1_id/_2_id/_3_id + custom_list_1_name/_2_name/_3_name

FK / varchar

Custom lists + their display names.

enrol_process_id

FK → process_definition

The registration ProcessDefinition driving the entry form.

disciplines

M:N via event_discipline

Road (1) / MTB (2) / Track (3).

Representative insert

INSERT INTO event
    (name, timezone, start_date_time, end_date_time, all_day,
     organiser_id, sanctioning_organisation_id,
     csa_membership_required, csa_license_required, allow_uci_license,
     csa_strict_registration, csa_day_license_disallow,
     custom_list_1_required, custom_list_2_required, custom_list_3_required,
     auto_assign_numbers_on_registration, preferred_timing_identifier)
VALUES (
    '<Event name>', 'Africa/Johannesburg',
    '<YYYY-MM-DD HH:MM:SS>', '<YYYY-MM-DD HH:MM:SS>', b'0',
    <organiser_org_id>, <sanctioning_org_id>,
    b'0', b'0', b'0', b'0', b'0',          -- CSA flags
    b'0', b'0', b'0',                       -- custom-list requirements
    b'0', 'PERSON_ID'                       -- auto-assign numbers; timing identifier
);
-- note the generated id → <event_id>
The CSA flags drive registration policy: csa_membership_required / csa_license_required gate entry on CSA membership/licence, csa_day_license_disallow blocks the day-licence option, and csa_strict_registration enforces strict validation. They determine whether the CSA day-licence product (E7.3) applies.

E2. Create the EventCategories

One event_category per entry class, as fine-grained as possible (see the tip above). The name matters: the participant importer matches event_category.name exactly (trimmed), so it must equal the category string produced by your import spreadsheet.

INSERT INTO event_category
    (name, event_id, owner_organisation_id, entry_category, race_category, gender, min_age, max_age)
VALUES
    ('<Category name>', <event_id>, <organisation_id>, b'1', b'0', NULL, NULL, NULL);

  • entry_category = b'1' — the category can be selected for new entries.

  • race_category — whether the category is used as a combined race category; set per your race design.

  • gender ('M'/'F'), min_age, max_age are optional age/gender restrictions.

E3. Create the EventRaceTypes

event_race_type joins the Event to global race_type codes (Road, MTB, E-Bike, …). One row per race type the event uses; unique on (race_type_id, event_id).

INSERT INTO event_race_type (name, race_type_id, event_id)
VALUES ('<Race type name>', <race_type_id>, <event_id>);

The global race_type catalogue is seeded by Liquibase for ids 1–18 only (Road Race=1, Time Trial=2, Criterium=3, and the track types). MTB/gravel/e-bike ids (19–24, e.g. MTB, Gravel, E-Bike=24) were added directly in production and are not in the repo — query SELECT id, name FROM race_type; on the live DB for the authoritative list before referencing an id.

E4. Create the Courses

One course per distance.

INSERT INTO course (name, distance_meters, event_id)
VALUES ('<Distance label e.g. 120km>', <metres>, <event_id>);

distance_meters is the JPA-managed column. Some legacy prod scripts write a dist column instead (and leave distance_meters NULL) — that is schema drift; prefer distance_meters.

E5. Create the Races

Each race is the intersection of one EventCategory, one EventRaceType, and one Course. Create one race per category. Same-distance categories that ride together (e.g. MTB 55km and E-Bike 55km) share a Course but are separate Races — that is the point of fine-grained categories.

INSERT INTO race
    (name, start_time, active, status, event_race_type_id, course_id, event_category_id, deleted, closed)
VALUES
    ('<Race name>', '<YYYY-MM-DD HH:MM:SS>', b'1', 'PLANNED',
     <event_race_type_id>, <course_id>, <event_category_id>, '', '');

E6. Create the StartGroups

A start_group puts participants on the start line. Create one per race for a simple mass-start event. To start several categories together, point their StartGroups at a shared start time/programme (or model them under one StartGroup) — this is how fine-grained categories are combined without merging the categories themselves. StartGroups are ordered on the programme via a program_entry.

INSERT INTO program_entry (name, program_type, active, date_time, event_id)
VALUES ('<Race name>', 'EVENT', b'1', '<YYYY-MM-DD HH:MM:SS>', <event_id>);
-- @pe := LAST_INSERT_ID()

INSERT INTO start_group (start_time, seq, race_id, program_entry_id)
VALUES ('<YYYY-MM-DD HH:MM:SS>', 1, <race_id>, @pe);

E7. Event registration products and fees

Each priced item — every entry category and every fee (e.g. a CSA day licence) — is a WooCommerce product on the organisation’s site, mirrored by an admin-service product row and linked to the category.

E7.1 Create the WooCommerce products

Create one Simple product per entry category, one per fee, on the organisation’s WooCommerce site. For each:

  • Type — Simple product.

  • Virtual — tick it (entries, memberships and fees are not shipped).

  • Name and description — required; appears on the order/invoice.

  • Inventory → "Limit purchases to 1 item per order" — tick it. Quantity is always 1 because the participant’s metadata is attached to each individual line item.

  • Slug — optional but recommended.

  • Product image — optional, recommended.

Group them with Product categories — e.g. an Entries category for the event entries, and a CSA category for the Cycling South Africa day licence (the fee required from participants who are not members of the national federation at a sanctioned event).

Hide entries and fees from the storefront. These products are added to orders programmatically, so they should never appear in the public shop or search. On each product, in the Publish panel click Edit next to Catalog visibility and choose Hidden, then Update. WooCommerce’s four options are Shop and search results (default), Shop only, Search results only, and Hidden. "Hidden" still lets the product be reached by direct link and added to an order — exactly what the plugin does.

E7.2 Create the admin-service products and link to categories

Mirror each WooCommerce product with an admin-service product row whose external_id holds the WooCommerce product ID, then link the product to its EventCategory via event_category.product_id.

INSERT INTO product (name, external_id, price)
VALUES ('<Category / fee name>', '<woocommerce-product-id>', <price>);
-- @prod := LAST_INSERT_ID()

UPDATE event_category SET product_id = @prod WHERE id = <event_category_id>;

The product table has no reference to the registration_system (or organisation) the product belongs to — it is keyed only by external_id. Two tenants' WooCommerce sites can assign the same numeric product ID, and admin-service cannot currently disambiguate them. Keep product IDs distinct across tenants until a discriminator exists.

E7.3 Wire the CSA day licence

The day-licence fee needs one extra link beyond E7.2: after creating its product row (whose external_id is the day-licence WooCommerce product ID), set it as productDayLicense on the Event — or on the Series, which every event inherits from — via the REST API (PUT /api/events, PUT /api/series) or a direct database update. Without this link, riders can still select the day-licence option during registration, but no day-licence line item is added to the order (admin-service logs a warning instead). This applies when the event’s CSA flags (E1) allow the day licence (csa_day_license_disallow = b'0').

Worked example: Event 144 — Rooibos MTB (2026)

Fine-grained categories, one Course per distance, four EventRaceTypes, one Race per category (same-distance MTB/E-Bike share a course but are separate races). Modelled on event 113 (Rooibos MTB 2025).

EventCategories (event_category.name, entry_category=b'1', race_category=b'0', event_id=144) — these strings must match the import spreadsheet’s Category column verbatim:

  • Gravel - 120km

  • MTB - 80km

  • MTB - 55km

  • MTB - 30km

  • E-Bike - 55km

  • E-Bike - 30km

  • Single Start - Wine Walk

Courses: 120km (120000 m), 80km, 55km, 30km, Wine Walk.

EventRaceTypes: MTB (race_type 20), Gravel (21), E-Bike (24), Single Start (23).

Races (one per category = EventCategory × EventRaceType × Course; active=b'1', status='PLANNED'): Gravel 120km, MTB 80km, MTB 55km, E-Bike 55km, MTB 30km, E-Bike 30km, Single Start Wine Walk — with a program_entry (program_type='EVENT') and one seq=1 start_group per race, staggered from 08:00.

The full script is ~/dev/ems/.local/rooibos-2026/event144-structure.sql, run with:

mysql -h 127.0.0.1 -P 3307 -u claude -p"$(cat ~/dev/ems/claude.pwd)" wpca_prod < event144-structure.sql
That script inserts race_type id 24 (E-Bike, discipline 2) and uses the legacy course.dist column — both are prod-only and not reproducible from the repo seed data.

Related