06 — Database Schema

The entire TogoActive platform — legacy production app, v3 admin backend, WL API and (indirectly) the WL public sites — runs on two MySQL databases. There is no per-app database and no dev copy in normal use: the same live rows are read and written by every codebase. This doc is the table-level reference: which tables exist, what their important columns are, which system reads and writes each one, and the denormalization contracts that keep the two databases consistent.

Related docs: ./01-architecture-overview.md · ./03-admin-backend-api.md · ./04-wl-api.md · ./07-legacy-app-and-tgp-integration.md

Scope note. Column lists below are evidenced from v3 migrations, model $fillable/$casts and controller usage. Legacy tables (events, event_users, payments, …) predate the v3 repos and have no migrations here — for those, only columns actually used by v3 code are listed; the live table may have more.

---

1. The two databases & who connects to what

                    ┌─────────────────────────────────────────────┐
                    │  TGA DB  ("togoactive", DigitalOcean MySQL) │
                    │  staging variant: "togoactive-stage"        │
                    │                                             │
                    │  events, event_users, payments, donations,  │
                    │  configuration, rewards, achievements,      │
                    │  admin_*, event_pages, triggers, crons ...  │
                    └───────▲──────────────▲──────────────▲───────┘
                            │              │              │
              mysql (strict=false)   mysql (strict=true)  mysql (strict=true)
                            │              │              │
                 ┌──────────┴───┐   ┌──────┴───────┐   ┌──┴───────────┐
                 │  Legacy app  │   │ Admin backend│   │    WL API    │
                 │/var/www/     │   │ admin-backend│   │ frontend-api-│
                 │ togoactive   │   │ (v3, /api/v1)│   │wl-development│
                 └──────────┬───┘   └──────┬───────┘   └──┬───────────┘
                            │              │              │
             mysql_tgp (strict=false)  mysql_tgp      mysql_tgp
                            │              │              │
                    ┌───────▼──────────────▼──────────────▼───────┐
                    │  TGP DB  (Togoparts platform database)      │
                    │                                             │
                    │  users (userid PK), user_addresses,         │
                    │  challenge_leaderboard, challenge_*_        │
                    │  leaderboard, challenge_strava_activities,  │
                    │  stored procedures (UpdateRanks, ...)       │
                    └─────────────────────────────────────────────┘

React admin SPA ──HTTP──▶ admin backend        Next.js WL sites ──HTTP──▶ WL API
(never touches a DB directly)                  (never touch a DB directly)

1.1 The TGA DB (default mysql connection)

• What it is: the TogoActive application database. DigitalOcean managed MySQL; production database name togoactive, staging variant togoactive-stage (host tga-production-…do-user-….k.db.ondigitalocean.com).
• Who shares it: three Laravel codebases connect to the same live database:
  1. Legacy production app — /var/www/togoactive (old admin + production crons),
  2. v3 admin backend — /var/www/togoactive-development/admin-backend,
  3. WL API — /var/www/togoactive-development/wl-event/frontend-api-wl-development.
• The two frontends (React admin SPA, Next.js WL sites) reach it only through their respective APIs.

1.2 The TGP DB (mysql_tgp connection)

The Togoparts platform database. Holds the platform-wide user accounts (auth credentials), profile data, Strava/challenge activity data, the denormalized challenge leaderboards, and the rank-computation stored procedures. All three backends define a mysql_tgp connection; v3 models for TGP tables live under App\Models\Tgp\* (admin backend) and set protected $connection = 'mysql_tgp'.

1.3 Connection matrix (from each repo's config/database.php)

Codebase	Connection	Env keys	Strict mode	Points at
Legacy /var/www/togoactive	mysql (default)	DB_HOST / DB_DATABASE / DB_USERNAME / DB_PASSWORD	false	TGA DB (live)
Legacy	mysql_tgp	DB_HOST_TGP / DB_DATABASE_TGP / …	false	TGP DB
Legacy	mysql_tga_stage	DB_HOST_TGA_STAGE / DB_DATABASE_TGA_STAGE / …	false	TGA staging DB
Legacy	mysql_wp	DB_HOST_WP / DB_DATABASE_WP / …	false	WordPress DB (marketing site)
admin-backend	mysql (default)	DB_HOST / DB_DATABASE / …	true	TGA DB (live)
admin-backend	mysql_tgp	DB_HOST_TGP / DB_DATABASE_TGP / …	false	TGP DB
WL API	mysql (default)	DB_HOST / DB_DATABASE / …	true	TGA DB (live)
WL API	mysql_tgp	DB_HOST_TGP / DB_DATABASE_TGP / …	false	TGP DB

Throughout this doc the read/write column uses: legacy (old app + its crons), admin-api (v3 admin backend), wl-api (v3 WL API, which serves wl-web, the Next.js sites — wl-web itself never touches SQL).

---

2. Shared-DB operational model

Because all three backends point at the same live TGA DB, "data" and "code" have completely different deployment semantics:

• Config/data edits are global and instant. A row written by the admin backend (e.g. a configuration blob, an events_dates change) is immediately visible to the legacy crons and to every WL site. There is no propagation step and no undo.
• Code is deployed per-server. The admin preview + production WL API run on a separate server (wl-api.togoparts.com); this box is dev/staging. A schema-dependent feature must be deployed to every server whose code touches the affected rows, or the stale deploy will misread the new shape (classic symptom: admin email preview breaks only because the remote WL API deploy is behind).
• Migrations run against the live DB. The v3 admin backend's database/migrations have been applied directly to the production togoactive database (applied as batch 121). New migrations must be written to be safe on live data: additive columns with defaults, backfills in the migration itself (see add_created_by_to_events_table), and reversible down() methods.
• Strict-mode asymmetry. The v3 apps run the default connection with 'strict' => true; the legacy app runs false. Legacy code can therefore insert rows that omit NOT-NULL columns (MySQL silently fills them) — the same insert from v3 code throws. When v3 code starts writing to a legacy table, every NOT-NULL column must be supplied explicitly (e.g. the social_seo columns had to be made nullable by migration before v3 writes worked). mysql_tgp is non-strict everywhere because TGP tables rely on implicit defaults.
• Eloquent caveat on this schema: whereColumn does not work inside eager-loading constraints against these tables — use DB subquery selects instead (the participant/transaction controllers are built this way).

---

3. TGA DB — table reference by domain

3.0 Quick index: Eloquent model → table

Handy when grepping. Non-obvious mappings are bold (table name is not the snake-plural of the model).

admin-backend (app/Models/) — default mysql connection unless noted:

Model	Table	Model	Table
AdminUser	admin_users	EventUserMeta	event_user_meta
AdminRole	admin_roles	EventAvatar	event_avatars
AdminPermission	admin_permissions	Team	teams
AdminDashboard	admin_dashboard	TeamUser	team_users
User	users	Reward	rewards
Event	events	Customization	customizations
EventDate	events_dates	Coupon	coupons
EventMeta	events_meta	MultiQuantityDiscount	multi_quantity_discount
EventMetaTemplate	event_meta_templates	UserReward	user_rewards
EventFaq	events_faqs	Achievement	achievements
EventHighlight	event_highlight	AchievementGroup	achievement_groups
EventPage	event_pages	AchievementCondition	achievement_cron_setup
EventImage	event_images	ConditionSubcondition	achievement_cron_condition
SocialSeo	social_seo	ChallengeAchievementWinner	challenge_achievement_winners
Configuration	configuration	LeaderboardSetting	leaderboard_setting
LandingPage	landing_page	EventLeaderboardTab	event_leaderboard_tab
RegistrationSetup	registration_setup	LeaderboardSortingTab	leaderboard_sorting_tab
EventDomain	event_domains	EventLeaderboardActivity	event_leaderboard_activities
EventIntegration	event_integrations	Payment	payments
EventFeatureSetting	event_feature_settings	PaymentDetail	payment_details
EventFeaturedProfile	event_featured_profiles	PaymentMethod	payment_method
EventUser	event_users	PaymentGatewayCredential	payment_gateway_config
Donation	donations	TaxDeductionDetail	tax_deduction_details
StravaActivity	strava_activity	PortRun / PortRunItem	port_runs / port_run_items
CronJob / CronExecution / CronFailure	cron_jobs / cron_executions / cron_failures

TriggerManagement\*: ConditionRegistry→condition_registry, EmailTemplate→email_templates, EmailTemplateAuditLog→email_template_audit_logs, EventTriggerSetting→event_trigger_settings, TriggerSystemConfig→trigger_system_config, TriggerAuditLog→trigger_audit_logs.

Tgp\* (all $connection = 'mysql_tgp', no timestamps): TgpUser→users (PK userid), ChallengeLeaderboard→challenge_leaderboard (PK clid), ChallengeTeamLeaderboard→challenge_team_leaderboard, ChallengeStravaActivity→challenge_strava_activities (PK caid), ChallengeStravaActivityAudit→challenge_strava_activity_audit.

WL API (app/Models/) — a deliberately small set; most access is raw DB::table(): Event, EventDate, EventDomain, EventImage, EventMeta, Configuration, Achievement, ChallengeAchievementWinner, Reward, Team, TeamUser, User, AdminDashboard map as above, plus UserAddress→user_addresses on mysql_tgp (see §4.1). Raw-table hotspots in wl-api (by call count): users, configuration, event_users, team_users, payments, event_user_meta, teams, events_meta, donations, events_dates.

3.0.1 Core relationship sketch

Event configuration.

Participants and teams.

Money, rewards and achievements.

Two links cross into the Togoparts database and are not drawn above, because at the database level they are not relationships: users.tgp_userid → TGP users.userid, and events_meta.TGP_CHALLENGE_ID → the TGP challenge cid.

There are no cross-database foreign keys. Both are maintained by application code alone, so nothing at the database level stops them going stale.

3.1 Admin auth & RBAC (v3-created, admin-api only)

All created by v3 migrations (2026-02/06). Written and read only by admin-api.

Table	Purpose	Key columns
admin_users	v3 admin accounts (separate from participant users)	name, email (unique), password (hashed), avatar, is_active, email_verified_at, last_login_at, last_login_ip
admin_roles	Role definitions	name (unique), display_name, description, is_system
admin_permissions	Permission catalogue	name (unique), display_name, group, description; no timestamps
admin_user_roles	user↔role pivot	composite PK (admin_user_id,role_id), FKs cascade
admin_role_permissions	role↔permission pivot	composite PK (role_id,permission_id)
admin_user_events	Per-event membership + per-event role	composite PK (admin_user_id,event_id); role enum owner/admin/editor/viewer (viewer = read-only via CheckEventAccess)

3.2 Event core & configuration

Table	Model	Purpose / key columns	Read/write
events	Event (both v3 apps)	The event row. Evidenced columns: title, slug, description, mode (enum default_mode / sessionalmode / donation_mode — drives feature defaults), event_status, visibility, is_hidden, email_active, upgrade_enabled, opt_out_enabled, finisher_distance, elite_finisher_distance, social_seo, plus v3 additions created_by (FK → admin_users, backfilled from the first owner in admin_user_events) and event_seo_template (JSON, per-page-type SEO meta templates).	legacy R/W · admin-api R/W · wl-api R
events_dates	EventDate	One row per event; all the date gates as datetimes: registration_start_date, registration_end_date, free_registration_end_date, leaderboard_start_date, leaderboard_end_date, results_date, update_info_end_date, opt_out_end_date, upgrade_start_date, upgrade_end_date, fund_raise_start_date, fund_raise_end_date, change_team_name_end_date, quite_team_end_date (sic).	legacy R · admin-api W · wl-api R
events_meta	EventMeta	Free-form key/value per event (event_id, meta_key, meta_value; no timestamps). Known keys: TGP_CHALLENGE_ID (TGA event → TGP challenge cid mapping fallback), reward_instructions (merch step intro copy).	legacy R/W · admin-api R/W · wl-api R
event_images	EventImage	Image slot URLs per event (one row per event, columns per slot; organizer_profile and favicon slots evidenced). Uploading to the bucket does not save — the admin must PATCH the slot URL onto this row.	admin-api W · wl-api R · legacy R
social_seo	SocialSeo	v3 table (unique event_id): page_title (60), share_image, share_title (100), share_description (158), fb_pixel_id. Columns made nullable in a follow-up migration (strict-mode fix).	admin-api R/W · wl-api R
event_meta_templates	EventMetaTemplate	v3 SEO templates per page type (unique event_id+page_type): title_template, description_template, og_*_template, og_image_url, og_type, twitter_*, json_ld_type, no_index, canonical_template. Resolved by wl-api's MetaResolver.	admin-api R/W · wl-api R
event_feature_settings	EventFeatureSetting	Mode-aware feature flags (~130 features / 8 groups). Unique (event_id,feature_key); is_enabled, is_overridden, overridden_by, overridden_at. Mode supplies defaults; a row exists only when an admin overrides.	admin-api R/W · wl-api R
landing_page	LandingPage	Legacy per-event landing config; v3 uses show_faq + Short_faq (note the capital S — legacy naming) for the short home-page FAQ, distinct from the FAQ Manager.	legacy R/W · admin-api R/W · wl-api R
registration_setup	RegistrationSetup	Per-event registration switches: enable_teams, enable_referral, enable_coupon, enable_delivery_address, enable_grouping, plus v3 booleans allow_re_registration (QA bypass of gates), allow_early_registration (soft-launch before start), allow_free_registration.	legacy R · admin-api R/W · wl-api R

3.3 The configuration blob store

configuration (Configuration model in both v3 apps) is the workhorse config table: event_id, key, value (JSON text blob), enable (boolean). One row per (event, concern). The convention is one key per concern — extend an existing blob rather than minting sibling keys.

Known key values and their owners:

Key	Contents	Written by	Read by
appearance	Theme/branding for the WL site	admin-api	wl-api
email_branding	Email header/footer/sender branding	admin-api	wl-api (EmailTemplateRenderer)
payment_gateways	Which gateway credential an event uses	admin-api	wl-api (StripeService)
event_registration	Form-builder schema (steps/fields) + merchandise_section copy	admin-api (Form Builder)	wl-api (RegistrationFormController, AuthController)
event_qualification	Donation-form-as-qualification-step config (donation_type, donation_amount, qualification_fields)	admin-api	wl-api
event_upgrade	Upgrade/Buy-Merch tab config; step_count.enable gates the /upgrade route	admin-api	wl-api (UpgradeController)
event_success_page	Post-registration success-page copy (merch/donation branches)	admin-api	wl-api
event_donation	Donation flow config	admin-api	wl-api, legacy
event_fundraiser	Fundraising goals/presets (target fund & distance defaults, preset chips, deadline)	admin-api	wl-api
event_default_message	Default copy blobs (share text, fundraising messages, misc admin copy)	admin-api	wl-api
couponJsonInfo	Returning-participant default coupon	admin-api	wl-api
host_config	Event-host presentation (e.g. logo_url in the host-avatar cascade)	admin-api	wl-api
social	Footer social links + per-link toggles	admin-api	wl-api
event_host_tga_user_id / event_host_tgp_user_id	Host account ids (used to exclude hosts from ranks/achievements)	admin-api	legacy cron, wl-api
achievement_email_active	Achievement email kill-switch	admin-api	wl-api

event_registration and event_upgrade are configuration keys, not tables — a common misconception because they behave like whole subsystems.

3.4 Participants & metas

Table	Model	Purpose / key columns	Read/write
users	User	TGA participant accounts. The name column is fullname, not name. Also evidenced: email, username, gender, profile_img, tgp_userid (link to the TGP users.userid — provisioned at WL signup by TgpAccountProvisioner).	legacy R/W · admin-api R · wl-api R/W
event_users	EventUser	Registration row (one per user per event). Evidenced: event_id, user_id, bib (zero-padded 5-digit, allocated under a MySQL named lock), token, is_paid_user, referral_code (the registration referral — see §5.5), mobile, country, group, total_paid, is_finisher, is_elite_finisher, is_autoPorted, port_run_id (v3, FK-ish to port_runs).	legacy R/W · admin-api R/W · wl-api R/W
event_user_meta	EventUserMeta	Key/value per event_user_id (key, value, v3 port_run_id). Holds form answers (rows are deleted and re-inserted on re-registration, not appended), plus flags like is_tester (per-event tester who may preview date-gated leaderboard views) and fundraising_message.	legacy R/W · admin-api R/W · wl-api R/W
users_meta	— (raw)	Legacy per-user key/value; minor reads from wl-api and legacy.	legacy R/W · wl-api R
event_featured_profiles	EventFeaturedProfile	Featured/popular profiles for an event (this table — not flags on event_users).	admin-api R/W · wl-api R
mail_logs	— (raw)	Legacy email log, one row per Mailable send. Real columns: id, event_id, to_email, is_sent, maildata (JSON: subject, mail_type, from_*, status, error_message, source, sent_at, user_id…), timestamps. Written by wl-api's EmailLogger/EmailLogListener; read by admin-api's EmailLogController (Setup → Communications → Email Logs).	wl-api W · admin-api R · legacy W

3.5 Teams

Table	Model	Purpose / key columns	Read/write
teams	Team	Event teams: event_id, team_name, user_id (owner), enabled, change_name, quit_team, and v3 team_avatar_img (added by a wl-api migration). Renames must sync TGP challenge_team_leaderboard.team_name — see §5.1.	legacy R/W · admin-api R/W · wl-api R/W
team_users	TeamUser	Membership: team_id, event_id, user_id, is_owner, v3 port_run_id.	legacy R/W · admin-api R/W · wl-api R/W

3.6 Rewards, coupons & discounts

Table	Model	Purpose / key columns	Read/write
rewards	Reward	Merch/reward SKUs: event_id, name, sku, description, max_quantity, size (JSON), sizing_images/rewards_images (JSON), restrict_to_country + countries_allowed (JSON), price (JSON per currency), is_hidden, is_core_item, is_dependent_sku + dependent_sku, is_default_checked_sku, enable_customization, reward_instruction, addon_instruction, sort_id (drag-reorder). Deleting a purchased SKU hits an FK violation → surfaced as 409.	admin-api R/W · wl-api R · legacy R
customizations	Customization	Per-SKU customization fields: reward_id, title, name, form_name, type, field_prop (JSON), order.	admin-api R/W · wl-api R
coupons	Coupon	Event coupons: name (code), discount, rewards (JSON scope), max_use, expiry_date, previous_joined_event_ids (JSON, returning-participant eligibility).	admin-api R/W · wl-api R/W
multi_quantity_discount	MultiQuantityDiscount	Bulk-buy rules: condition, quantity, discount, reward_ids (JSON).	admin-api R/W · wl-api R
user_rewards	UserReward	A participant's purchased SKUs: event_id, user_id, reward_id, size, quantity, amount, discount, currency, payment_status, payment_id, reward_custmization (sic).	wl-api W · admin-api R · legacy R/W

3.7 Payments, donations & tax

Table	Model	Purpose / key columns	Read/write
payments	Payment	One row per money movement. Evidenced: event_id, user_id, payment_type (registration, donation, upgrade, …), payment_method, payment_intent, total_amount, discount, total_paid, currency, coupon_code (the per-payment coupon — see §5.5), status, transaction_id, has_upgraded, v3 port_run_id.	wl-api W · legacy R/W · admin-api R
payment_details	PaymentDetail	Line-item / payload detail row referenced by donations.payment_detail_id.	wl-api W · legacy R/W · admin-api R
payment_method	PaymentMethod	Per-event enabled payment methods; payment_method column is a JSON blob.	admin-api R/W · wl-api R
payment_gateway_config	PaymentGatewayCredential	Shared Stripe credential library (not strictly per-event): event_id, name, credentials (JSON), type, mode. Includes the shared TEST credential togopart-test that brand-new events fall back to (StripeService::defaultTestCredentialId()).	admin-api R/W · wl-api R
donations	Donation	One row per donation credit: event_id, user_id (donor), payment_detail_id, receiver_id, email, display_name, message, amount, type (individual/team/team_split/host…). Team donations insert one direct team row plus team_split rows dividing the amount among members.	wl-api W · legacy R/W · admin-api R
tax_deduction_details	TaxDeductionDetail	Donor tax info keyed by payment_id (works for every recipient type — the old event_user_meta stash dropped team donations): is_anonymous, security_type (NRIC/UEN), security_name, security_id, mobile_no.	wl-api W · admin-api R
stripe_session_logs	— (raw)	Stripe checkout session audit written by wl-api.	wl-api W

3.8 Achievements

Table	Model	Purpose / key columns	Read/write
achievements	Achievement	Achievement definitions: event_id, title, description, icon, type, level, more-info fields, email fields (email_subject, email_text, CTA fields), notification fields, share/sponsor blocks, list_order, achievements_hide, achievement_group_id + achievement_group_order, season.	admin-api R/W · wl-api R · legacy R
achievement_groups	AchievementGroup	Grouping/ordering: event_id, name, order.	admin-api R/W · wl-api R
achievement_cron_setup	AchievementCondition	Unlock condition header per achievement: achievement_id, achievement_type, connector, description, result_date_condition, result_date. Consumed by the legacy AchievementUnlockService cron — key names must match what legacy reads (a v3/legacy key mismatch once made donation/purchase/referral conditions award nobody).	admin-api W · legacy R
achievement_cron_condition	ConditionSubcondition	Sub-conditions: achievement_cron_setup_id, condition_type, date_start/date_end (Early-Bird date windows), additional_options (JSON, e.g. Strava activity-type lists).	admin-api W · legacy R
challenge_achievement_winners	ChallengeAchievementWinner	Awarded achievements: user_id, event_id, achievement_id, team, notified (boolean handshake — see §5.4), rewards_data (JSON; voucher-tier coupon payload minted lazily at email time).	legacy W (award crons — all events, incl. ≥ 49) · wl-api R/W (events ≥ 49: notification cron flips notified) · admin-api R

3.9 Leaderboard settings & activities

Settings only — the actual leaderboard data lives in the TGP DB (§4).

Table	Model	Purpose / key columns	Read/write
leaderboard_setting	LeaderboardSetting	Per-event leaderboard behaviour: display toggles (show_event_highlights, show_sponsors_bar, show_event_stats, show_event_host_donation, show_info_box, leaderboard_hide), sync switches (cron_enable, cron_force_sync, enable_team_sync, enable_gallery_sync, sync_activity_cron, allow_mannual_sync (sic)), activities, info_data (JSON), cover_image (JSON).	admin-api R/W · legacy R (sync crons) · wl-api R
event_leaderboard_tab	EventLeaderboardTab	Tabs: event_id, enable, name, type, filter (JSON), slug, order, notice, enable_sorting_tab, table_column (JSON).	admin-api R/W · wl-api R
leaderboard_sorting_tab	LeaderboardSortingTab	Sort options within a tab: leaderboard_tab_id, name, info_message, type, show_order.	admin-api R/W · wl-api R
event_leaderboard_activities	EventLeaderboardActivity	Which activity types count: event_id, name, type, enable.	admin-api R/W · legacy R (sync)
strava_activity	StravaActivity	TGA-side copy of activity rows used by admin Analytics (strava_id, activity_type, moving_time, elapsed_time evidenced). Primary Strava data lives in TGP challenge_strava_activities.	legacy W · admin-api R

3.10 Avatars

Table	Purpose / key columns	Read/write
event_avatars	Avatar pool: event_id (NULL = global pool), category, url, position, enabled. Auto-assigned gender-matched at registration when the user has no photo.	admin-api R/W · wl-api R
event_avatar_categories	Category ordering, unique (event_id,category); event-level categories male/female/gender_neutral, global male_classic/female_classic/animal.	admin-api R/W · wl-api R

3.11 Pages, FAQ, highlights

Table	Model	Purpose / key columns	Read/write
event_pages	EventPage	v3 page-builder pages: event_id, title, slug (unique per event), type enum (custom, landing, faq, upgrade, participants, leaderboard, achievements, + user_detail_donation added later), blocks (JSON block tree), seo (JSON), status (published/draft), sort_order, url_pattern + previous_patterns (JSON) + is_active_for_type (URL routing per page type).	admin-api R/W · wl-api R
events_faqs	EventFaq	Legacy FAQ/TnC/rules blobs: event_faq, event_tnc, event_rules.	legacy R/W · admin-api R/W · wl-api R
event_highlight	EventHighlight	Highlights: event_id, name, content (legacy HTML), blocks (v3 JSON block tree; content_unlayer made nullable when added), order, display_settings (JSON).	admin-api R/W · wl-api R · legacy R

3.12 Domains & integrations (v3-created)

Table	Purpose / key columns	Read/write
event_domains	Custom domains: event_id (multiple domains per event allowed since 2026-07), domain (unique), status enum (pending/txt_verified/active/failed), txt_record_name/txt_record_value, a_record_ip, txt_verified_at, dns_verified_at, last_checked_at. Verification is executed by the WL API server on the admin backend's behalf.	admin-api R/W · wl-api R (domain → event resolution)
event_integrations	Analytics tag providers (GA4/GTM/Pixel/Contentsquare): unique (event_id,provider), status, account_email, ga_property_id, ga_measurement_id, tag_id, refresh_token (encrypted at the model layer via Laravel encrypted cast — needs the same APP_KEY to decrypt), scopes, connected_by, connected_at.	admin-api R/W · wl-api R (resolveAnalytics)

3.13 Automation, triggers & email templates (v3-created)

Two engines coexist (see §5 and 07): the legacy automation mails (events 28–48) and the v3 wl-api AutomationCron (events ≥ 49). They share one dedupe log.

Table	Purpose / key columns	Read/write
event_automation_rules	v3 automation rules: unique (event_id,rule_name), is_enabled, config (JSON), priority, trigger_every_cycle (default every_six_hours), last_run_at, last_user_count.	admin-api R/W · wl-api R (AutomationCron)
trigger_email_logs	Shared dedupe log for BOTH engines: user_id, event_id, condition_id (rule id), condition_data (JSON of fields), condition_hash (md5 of fields), sent_at. v3 dedupes on (user, event, rule, hash) + optional from_days window. Never configure one event's rules in both engines.	legacy R/W · wl-api R/W
condition_registry	Catalogue of trigger conditions: condition_name (unique), display_name, description, category enum, is_enabled, is_experimental, required_settings (JSON), available_tokens (JSON), cooldown_hours_default, max_emails_per_run_default, order.	admin-api R/W
trigger_system_config	Global kill-switches: is_enabled, dry_run_only (emergency brake), max_emails_per_cron, notification_email.	admin-api R/W
event_trigger_settings	Per-event trigger tuning: unique (event_id,condition_name), is_enabled, require_manual_approval, custom_data (JSON), max_emails_per_run, cooldown_hours, last_run_at, last_user_count, last_error.	admin-api R/W
trigger_audit_logs	Per-send audit: event_id, user_id (recipient), condition_name, action enum, performed_by_user_id, email, status (success/failed/skipped), reason, details (JSON).	admin-api R/W
email_templates	Trigger email templates: unique (condition_name,event_id) where event_id NULL = global default; subject, body_html, body_text, preview_text, from_name (default TogoActive), from_email, reply_to_email, available_tokens (JSON), is_enabled.	admin-api R/W · wl-api R (render/test-send)
email_template_audit_logs	Template change audit: template_id, action enum, changes (JSON), performed_by_user_id.	admin-api W

3.14 Port runs (participant porting, v3-created)

Table	Purpose / key columns	Read/write
port_runs	A porting job from one event to another: source_event_id, dest_event_id, admin_user_id, status (default draft), filters_json, mapping_json, policy_json, counts_json, error_message, started_at/finished_at.	admin-api R/W
port_run_items	Per-participant outcome: port_run_id, source_event_user_id, dest_event_user_id, outcome, reason, meta_snapshot_json.	admin-api R/W
(columns)	port_run_id added to event_users, event_user_meta, team_users, payments — provenance stamp for ported rows (event_users also has the older is_autoPorted flag).	admin-api W

3.15 Cron infrastructure (v3-created)

Table	Purpose / key columns	Read/write
cron_jobs	Registered scheduled commands: name (unique), command, schedule (e.g. everyFiveMinutes), is_enabled, timeout_seconds, max_retries, notification_channel.	admin-api R/W
cron_executions	One row per run: cron_job_id, started_at/completed_at, status enum (pending/running/success/failed/timeout/skipped), output, error_message, duration_ms, jobs_queued/jobs_completed/jobs_failed.	admin-api R/W
cron_failures	Failure triage: cron_execution_id, failure_reason, retry_count, next_retry_at, resolved_at, resolved_by_user_id, resolution_notes.	admin-api R/W

3.16 Misc

Table	Purpose	Read/write
admin_dashboard	Legacy per-event site-admin login (admin_username, admin_password_hash, admin_active, settings_json, updated_by). Modelled in both v3 apps.	admin-api R/W · wl-api R
password_reset_tokens	WL participant password-reset tokens (standard Laravel shape).	wl-api R/W

---

4. TGP DB — table reference & stored procedures

Everything here is on the mysql_tgp connection (non-strict, no Laravel timestamps on legacy tables). v3 models: App\Models\Tgp\* in admin-backend; the WL API mostly uses raw DB::connection('mysql_tgp') queries plus the UserAddress model.

4.1 Tables

Table	PK	Purpose / key columns	Read/write
users	userid	Platform accounts + auth credentials: passwd holds legacy crypt (and some bcrypt) hashes; WL login verifies via crypt() handling both. Strictly read-only for password columns — never write them (no auto-upgrade of hashes). Also profile_img (values starting uploads/ resolve to the TogoActive DO bucket, not the Togoparts CDN). Linked from TGA users.tgp_userid; WL signups provision a row via TgpAccountProvisioner.	legacy R/W · wl-api R (+insert on signup provisioning) · admin-api R
user_profile	—	Extended profile data.	wl-api R · legacy R/W
user_addresses	id	Delivery addresses (WL API UserAddress model — note: lives in TGP, not TGA): userid, blk, address, subdistrict, city, state, country, postal_code, phonecode, mobile, default_address, newformat. Address saves fail if the TGA user has no tgp_userid.	wl-api R/W · legacy R/W
challenge_leaderboard	clid	Denormalized individual activity leaderboard per challenge (cid): distance/hours totals + rank columns recomputed by procs.	legacy R/W (sync crons + procs) · wl-api R · admin-api R
challenge_team_leaderboard	id	Denormalized team leaderboard. Keyed to TGA by tga_team_id; carries denormalized team_name (must be synced on TGA team rename — §5.1) and raised_fund.	legacy R/W · wl-api R/W · admin-api R/W (rename sync)
challenge_donation_leaderboard	id	Per-(cid, userid, teamid) fundraising row: cid, userid (TGP id), teamid (0 for individual rows — never NULL, §5.3), raised_fund, target_fund, target_distance, fundraising_description (pledge/share message), fundraising_share_text, rank columns. Seeded at registration by FundraisingMessageService::seedForUser (idempotent; only fills empty fields/zero goals).	wl-api R/W · legacy R/W · admin-api R
challenge_strava_activities	caid	The Strava activity store (per challenge). Heaviest-used table in legacy sync crons.	legacy R/W · admin-api R (analytics)
challenge_strava_activity_audit	—	Audit trail for activity mutations (suspicious-activity review).	legacy R/W · admin-api R
challenge_activities_log	—	Strava ingest queue/backlog: unsynced webhook rows drained by the legacy DrainStravaBacklog command one-by-one (so one bad row can't abort a batch).	legacy R/W
challenge_users	—	Challenge membership on the TGP side.	legacy R/W
users_meta (TGP)	—	TGP-side user key/value.	legacy R/W
challenge_detail	—	Challenge definitions (cid) — the thing events_meta.TGP_CHALLENGE_ID points at.	legacy R/W
challenge_group_leaderboard, challenge_seasonal_leaderboard, challenge_regional_leaderboard	—	Additional denormalized leaderboard variants recomputed by their procs.	legacy R/W

4.2 Stored procedures

All live in the TGP DB and recompute rank columns on the denormalized leaderboard tables. Excluded-user parameters take a comma-separated string; pass '' (empty string) for "exclude nobody" — passing NULL makes the proc rank nobody.

Procedure	Recomputes	Called by
UpdateRanks(cid, excluded)	Distance ranks on challenge_leaderboard	legacy sync crons
UpdateHoursRanks(...)	Hours-based ranks	legacy
UpdateDonationRanks(cid, excludedHosts)	Individual fundraising ranks on challenge_donation_leaderboard	legacy UpdateDonationRank cron and wl-api donations:recalc (DonationRecalcCron, 10-min sweep)
UpdateTeamDonationRanks(cid)	Team fundraising ranks	legacy cron and wl-api DonationRecalcCron
UpdateTeamRanks(...)	Team activity ranks on challenge_team_leaderboard	legacy
UpdateGroupDonationRanks(...)	Group fundraising ranks	legacy
UpdateSeasonalRanks(...)	Seasonal leaderboard ranks	legacy
GetSuspiciousActivities(...)	Flags anomalous activities for review	legacy

The TGA event → TGP challenge link is the cid. The WL API's TgpChallenge::cid() reads the configuration TGP_CHALLENGE_ID key, then falls back to the events_meta row (which admins set via the CID field); the admin backend's TgpMappingService and the legacy app additionally keep a static map for old events (8–37).

---

5. Denormalization contracts

Cross-database and cross-table invariants that code must actively maintain. Break one and a user-visible number goes wrong somewhere else.

5.1 team_name is denormalized into TGP

teams.team_name (TGA) is copied into challenge_team_leaderboard.team_name (TGP, keyed by tga_team_id). Any rename — admin All-Teams page or WL — must update both. The admin rename endpoint does this; new rename paths must too.

5.2 raised_fund has one recompute path per level

• Individual: DonationLeaderboardService::recalculateForPayment recomputes challenge_donation_leaderboard.raised_fund — wired to the Stripe webhook and the verify endpoint (either may land first).
• Team: challenge_team_leaderboard.raised_fund is the authoritative team total (member sum), maintained by the recalc services/procs. The WL team header/list/chips read this — do not re-derive a team total by summing only direct-to-team donations (that bug shipped once).
• Sweeper: wl-api donations:recalc cron re-runs recalcs and then UpdateDonationRanks / UpdateTeamDonationRanks, so a missed webhook heals within ~10 minutes.

5.3 challenge_donation_leaderboard.teamid is 0, never NULL

Individual rows use teamid = 0. Queries must filter teamid = 0 (not IS NULL), and inserts must set it explicitly (the column is NOT NULL on TGP). Registration seeds a complete individual row via seedForUser (goals from event_fundraiser config; idempotent — never overwrites participant-edited values).

5.4 challenge_achievement_winners.notified is the legacy↔v3 handshake

The legacy unlock cron (AchievementMasterCron) inserts winner rows for every event with an open window — including events ≥ 49; only the notification step splits by event id: the legacy ChallengeNotification cron notifies events < 49, while the v3 wl-api cron sends the achievement email and flips notified for events ≥ 49. Never point both notification engines at one event. Voucher-tier achievements stash their lazily-minted coupon in rewards_data at email time.

5.5 referral_code vs coupon_code

Two different concepts that both look like "code" columns:

• event_users.referral_code — the referral captured at registration (who referred this participant).
• payments.coupon_code — the discount coupon applied to one payment. Reporting joins that conflate them double-count or attribute wrongly.

5.6 trigger_email_logs is the cross-engine dedupe ledger

Both the legacy automation mailer and v3 AutomationCron write here. v3's uniqueness key is (user_id, event_id, condition_id, condition_hash=md5 of the rendered fields) with an optional from_days recency window; a failed audit insert is logged loudly because it risks a re-send next cycle.

---

6. Conventions & pitfalls

• users.fullname, not users.name. The TGA participants table has no name column. (The v3 admin_users table does use name — different table, different convention.)
• whereColumn in eager loading silently misbehaves against this schema — use DB subquery selects (see EventParticipantController for the pattern).
• NULL vs '' for stored-proc excluded lists. '' = exclude nobody; NULL = rank nobody. Always pass '' when there is no excluded list.
• Strict-mode split. v3 apps: mysql strict; legacy + all mysql_tgp: non-strict. When porting a legacy insert into v3 code, supply every NOT-NULL column explicitly or add a nullable-columns migration first (as done for social_seo).
• Never write TGP password columns. users.passwd (TGP) is verified via crypt() for both legacy-crypt and bcrypt hashes, strictly read-only — no hash upgrades, no resets from v3 code.
• event_user_meta.is_tester marks per-event testers who can preview date-gated leaderboard views; check for it before "fixing" a participant who sees pre-launch data.
• Legacy spelling survives: landing_page.Short_faq (capital S), leaderboard_setting.allow_mannual_sync, user_rewards.reward_custmization, events_dates.quite_team_end_date. Match them exactly; do not "correct" column names in new code.
• configuration is a blob store, not a settings table. Extend the existing key for a concern (event_default_message, etc.) instead of adding sibling keys; the WL side deep-merges saved blobs over defaults, and list-valued fields need list-aware merging (plain array_replace_recursive resurrects deleted preset entries).
• Encrypted columns tie rows to APP_KEY. event_integrations.refresh_token uses the Eloquent encrypted cast — rotating the admin backend's APP_KEY orphans stored tokens.
• Schema changes ARE production changes. Migrations in admin-backend run against the live shared DB, and the legacy cron (schedule:run every minute, from this same box) is reading those tables while you migrate. Additive, defaulted, backfilled — always.