Database Reference

The primary datastore is MongoDB (Troyon_Dev DB). A legacy, read-only MySQL database ("RS" / Racing & Sports) is joined into occasionally for RS-client race cards and an id cross-reference. This doc covers the main pipeline's schema — [[project-troyenraceingestor]] (TroyenRaceIngestor) defines its own separate model classes and two extra collections (ingestorMeetingSource, ingestorRunnerFormCache), deliberately not reusing this schema; not covered here.

The upsert engine (applies to almost everything below)

All writes go through Common.MongoDbHelper.SaveOrGetBySlug<T> (Common/DataBase.cs). What it does to every call, regardless of collection:

1. Whole-record lock: if typeof(T) has a bool property literally named isLocked (checked via reflection — not part of the base class, duck-typed per model) and it's set on both the existing and incoming doc, skip the write entirely. Only Meeting and FinalResult actually declare this property.
2. Field-level freeze: for any CommonForMongo-derived model, fields named in frozenFields keep their old value regardless of what's incoming. Race.Runner (embedded) carries its own independent frozenFields too — a race can be locked at the whole-race level and/or per-runner.
3. Audit log: every insert/update writes a diff into Logs, except for Logs/api_request_logs/datadumps/racecards themselves (_auditSkipCollections).
4. New doc → new slug/id assigned, InsertOne. Existing → ReplaceOne, updatedAt bumped.

CommonForMongo supplies id, createdAt/updatedAt, frozenFields, internalReference. DataDump and RaceCard do not inherit it — no lock/freeze protection applies to those two via this mechanism, since they're derived/denormalized output, not source-of-truth records.

Collections

Core domain

Collection	Model	Key fields	Notes
meetings	Meeting (CommonForMongo, has isLocked)	id, mSlug, mDate(UTC), mCourse(Id), mCountry, mDiscipline, mAvgPrizeMoney, numberOfRaces, tabMeetingId/rsMeetingId (external cross-refs), flags isTrial/isTAB/isNightMeeting/isHidden/isFuture/isBypass/isAbandoned/isLocked	Does not embed races.
races	Race (CommonForMongo)	id, rSlug, meetingId/meetingSlug (ref → Meeting), rNo, rDiscipline, rClass/rDistance, rScheduleTime(UTC), resultString, flags rStatus/isOpen/isAbandoned/isHidden/isBypass/isValidated	Embeds runners: List<Runner>. Each Runner has its own frozenFields, horseName (denormalized string, not a ref to horses), jockey/trainer (plain strings — no Jockey/Trainer collection is actually live, see below).
horses	Horses (CommonForMongo)	id, hSlug, hRacenetSlug, hName, hSire/hDam/hDamSire, hStats/hFullStats (embedded), hSilkURL, isSilkFinal, data (raw blob)	Watch for a case-sensitivity bug: RacenetScraper/Program.cs writes to "Horses" (capital H) while everything else uses lowercase "horses" — Mongo collection names are case-sensitive, so this is a second, likely-accidental collection sitting alongside the real one. Confirm which one you're querying if RacenetScraper data looks disconnected from the rest.
courses	Course (CommonForMongo)	id, cName, cDisplayName, countryId (ref → countries), embeds aliases
countries	Country (CommonForMongo)	name, isoCode/isO2Code/isO3Code, exchangeRate, popularity	Embeds states.
lookups	LookUp (CommonForMongo)	lookupType, discipline, source, sourceReference, mappingStatus (default UNMAPPED), mappedEntityId/mappedReferance (typo preserved from source)	The course/entity reconciliation table — an UNMAPPED entry here is why a meeting silently doesn't appear (see Scraper & File Generator Process).

Generated output (from the file-generator pipeline)

Collection	Model	Key fields	Notes
racecards	RaceCard (no CommonForMongo)	RaceId (ref), Client/Language/Style, embeds Runners: List<RaceCardRunner> (itself embedding PerformanceStatistics/SpotLight/FormLines)	Fully denormalized customer-facing snapshot, one per client/language/style combo. Not lock/freeze-protected.
datadumps	DataDump (no CommonForMongo)	MeetingId, RaceId, Slug, embeds Runners (a different Runner class than Race's, each embedding LastThreeRuns)	Language-agnostic, one per race.
speedMaps	SpeedMap (CommonForMongo)	rId/rSlug/rNo (ref → Race), mId/mDate (ref → Meeting), embeds predictions
inRunningComment	InRunningComment (CommonForMongo)	meetingId/raceId/horseId (refs, not embeds), embeds comments, sourceType/language/style (string enums)
llmContent	LlmContent (CommonForMongo)	meetingId/raceId (refs), embeds contentInfo (language/style/model/promptId), content, hash	hash is SHA256(DataDump JSON + model name) — the idempotency key described in the pipeline doc.
finalResults	FinalResult (CommonForMongo, has isLocked)	meetingId/raceId + denormalized tabMeetingId/rsMeetingId, embeds results, flags isLocked (manual dead-heat confirmation lock), needsReview	The official/validated result, distinct from the raw per-source feed below.
raceResults	RaceResult (CommonForMongo)	same shape as FinalResult minus lock/review flags	The raw per-source (RAS/SRP) result feed before merge/validation.
raceStatus	RaceStatus (CommonForMongo)	meetingId/raceId, status (plain string)	Naming collision to watch for: this is unrelated to the Enums.RaceStatus enum used inside FinalResult/RaceResult — same name, different thing.
raceUpdate	RaceUpdate (CommonForMongo)	meetingId/raceId, data (raw serialized payload)	An event/update audit trail, separate from the general Logs audit collection — written by TroyenDataHelpers.RaceUpdateController.

Auth / admin / config

Collection	Model	Notes
client_api_keys	ClientApiKey (no CommonForMongo)	The keys TroyenAPI validates. ClientName, AllowedCountries/AllowedEndpoints, embeds RequestCaps, IsExpired.
users	TroyenAPI.Models.User (no CommonForMongo)	PasswordHash/PasswordSalt, Role (default "admin"). Note: this is a different user model from TroyenDataWeb's login accounts — don't conflate the two when debugging auth.
api_tokens	TroyenAPI.Models.ApiToken
admin_users / user_invites	(Common/Services/AdminUserService.cs)	Back the TroyenDataWeb custom email+TOTP login covered in the User Guide.
customer_data_templates	CustomerDataTemplate (CommonForMongo)	Backs TroyenDataHelpers.CustomerDataTemplatesController.
client-config	ClientConfig (CommonForMongo)	Hyphenated collection name — inconsistent with every other collection's naming (_/none). Drives RS-client race-card generation (region/type/source matching).
configs	Config (CommonForMongo)
lLMSystemPrompt	LLMSystemPrompt (CommonForMongo)	Lowercase-first casing is intentional and consistent across all 3 call sites (Common, TroyenDataHelpers.SystemPromptsController, TroyenRaceIngestor) — not a typo. Slug, Type, Prompt, IsSelected.

Logging / audit

Collection	Notes
Logs (capital L)	Doubles as both a general log stream and the SaveOrGetBySlug audit trail (CollectionName/RecordId/ActionType/Changes/PerformedBy).
api_request_logs / api_request_logs_archive	Per-request API traffic log; archive adds MovedToArchiveAt.
api_traffic_logs	Separate from the above — Common/Services/TrafficLogService.cs.
scraper_logs	NedsScraper's correlation-id middleware.
change_logs / change_logs_archive	Explicit split by age (recent vs. >90 days) per the model's own doc comment.
updateInfo	Minimal, purpose not fully explored in this pass.

Full inventory — 32 distinct collection-name string literals confirmed via grep across Common/PunterScraper/TroyenDataFilesGenerator/TroyenDataHelpers/NedsScraper/TroyenAPI/TroyenService/RacenetScraper/SilkGenerator/PunterWebScraper (33 counting the accidental "Horses" variant):

meetings, races, speedMaps, racecards, datadumps, horses (+ accidental "Horses"),
updateInfo, courses, inRunningComment, lLMSystemPrompt, raceResults, raceStatus, raceUpdate,
Logs, countries, users, api_tokens, client_api_keys, api_traffic_logs, llmContent,
finalResults, lookups, configs, client-config, customer_data_templates, admin_users,
user_invites, api_request_logs, api_request_logs_archive, scraper_logs, change_logs,
change_logs_archive

Jockey/Trainer model classes exist in TroyenModels/BSONModels but have no live collection anywhere in the codebase — no GetCollection<Jockey> call was found. Treat them as dead/legacy model definitions, not an actual data source (jockey/trainer names on a runner are plain denormalized strings, not references to these).

Embedded vs. referenced — the pattern

• Embedded (sub-document array inside the parent): Race.runners, Course.aliases, Country.states, SpeedMap.predictions, InRunningComment.comments, RaceCard.Runners, DataDump.Runners, FinalResult.results/RaceResult.results.
• Referenced (string id field, separate collection, no Mongo-enforced FK): Race.meetingId → Meeting; Meeting.mCourseId → Course; Course.countryId → Country; InRunningComment/LlmContent/SpeedMap/FinalResult/RaceResult/RaceStatus/RaceUpdate's meetingId/raceId → Meeting/Race; RaceCard.RaceId/DataDump.RaceId → Race.
• The rule holds consistently: anything that's a "child" of a race/meeting is referenced by id, never embedded across collection boundaries — only truly-owned sub-structures (a race's own runners, a country's own states) are embedded.

Legacy RS MySQL (read-only)

A separate, legacy MySQL database ("RS" — Racing & Sports) is read directly via raw ADO.NET (MySqlConnector, no ORM) from Common/RsRaceCardHelper.cs (used by the main pipeline and TroyenDataHelpers), TroyenService's SyncMeetingRsIdJob, and the standalone MeetingIdMapper/meetingIdMapper.py script. Confirmed genuinely read-only everywhere — no INSERT/UPDATE/DELETE against any RS table was found anywhere in the codebase.

Tables read

Table	Columns actually used
tblprerace_meetings	RSId, MeetingID, track, weather, rail
tblprerace_races	RMeetingID, RNumber, RaceID, RCrsID, RDate, RDiscipline, ROriginalPostTime(UTC), RDistance, RClass, RClassDescEN/RClassDesc, RPrizemoney, RCondition, RSurface, RGroup, RAge, RWT, RSex, RHCP, RJumps, RStatus
tblprerace_runners	HID, RunnerID, HName, HTab, HCountryOrig, HHCPDraw, HBP, HWeight, HGearChange, HJockeyClaim, HTrainer, HJockey, HOffRat, scratching, RaceID, FinishingPosition, startingPrice, beatenMargin — also retains each horse's own past entries, doubling as the recent-form-line source (no separate results table needed for that).
tblcourses	CrsUniqueID, CrsDisplayName, CrsCountryID
tblcountrycode	CountryID, CountryISOAlpha3
tblhorse	HUniqueID, HSire, HDam, HSex, HColour, HAge, HYearBorn
tblrsratings	PreID, RSRaceDate, days_last_st, formFigs, winOdds, AQOdds, several RS_WPS_* fields
tblruns (+ tblraces joined)	WHorseID, WRaceID, WFP, wPrizeWonVal; tblraces.RDistVal for win-range aggregates

Queries are batched with IN(...) clauses — a documented optimization that cut roughly 200+ per-race round trips down to about a dozen queries.

Join chain

1. Meeting.rsMeetingId → tblprerace_meetings.RSId → tblprerace_meetings.MeetingID.
2. That MeetingID + the race number → tblprerace_races.RMeetingID + RNumber → tblprerace_races.RaceID.
3. RaceID → tblprerace_runners.RaceID (the runner-level join). Runner rows carry HID (→ tblhorse.HUniqueID, a genuine PK join, unlike matching by horse name) and RunnerID (→ tblrsratings.PreID).
4. Course/country chain: RCrsID → tblcourses.CrsUniqueID → CrsCountryID → tblcountrycode.CountryID → CountryISOAlpha3 — which is then matched against Mongo's countries collection (isO3Code) to resolve a currency code. This is the one place RS and Mongo data are joined in the same code path.
5. Horse history: tblhorse.HUniqueID → tblruns.WHorseID for career prize-money/win-range aggregates.

Important distinction: tblprerace_meetings has both its own MeetingID (matched against Punter's tabMeetingId) and a separate RSId column that gets copied into Meeting.rsMeetingId — these are not the same value, don't conflate them when tracing the back-fill (TroyenService.SyncMeetingRsIdJob / MeetingIdMapper).

Hardcoded fallback RS MySQL credentials exist in Common/RsRaceCardHelper.cs, MeetingIdMapper/meetingIdMapper.py, and TroyenService/Jobs/SyncMeetingRsIdJob.cs (used only if the corresponding env var is unset) — already flagged as a rotation candidate in the Overview. Values are not reproduced here.

Debugging tips

• "Why is this meeting missing an RS card?" — check Meeting.rsMeetingId is populated; if not, TroyenService.SyncMeetingRsIdJob hasn't caught up yet (see Scraper & File Generator Process).
• "Why won't this field update?" — check frozenFields on the doc (whole-record) and, for races, also check the embedded Runner.frozenFields; two independent freeze layers.
• "Data looks duplicated/inconsistent from RacenetScraper" — check whether it landed in horses or the accidental Horses collection.
• Collection-name typos to remember when querying directly (not bugs, just inconsistent conventions): client-config is hyphenated, lLMSystemPrompt is lowercase-first, Logs is capitalized, everything else is lowercase/underscore.