Database

QDG CMS API reads/writes three separate MySQL databases (same server in the current environment, but modeled as distinct connections/credentials in code — see Architecture). All access goes through Dapper (MySqlConnector), never an ORM/migrations tool — there is no EF Core and no migration scripts in this repo.

cms database (DefaultConnection)

tblcms

The CMS story/article table — shared with the public-facing Racing.CMS API (different consumer, same table). Mapped 1:1 onto the Story domain entity via Dapper's convention-based mapping (SELECT s.* / SELECT c.*), so new columns only need adding to the entity.

Notable quirks (see Story.cs and StoryRepository.cs doc comments for full detail):

• Story (column) → StoryContent (property) — the obvious property name Story was already taken by the entity class name, so this one column needs an explicit CustomPropertyTypeMap entry in DapperTypeMapConfig.
• visible is an ENUM('TRUE','FALSE') string column, not a real bit/boolean — filters and writes translate bool?/bool to the literal strings 'TRUE'/'FALSE'.
• linked_story_id / sort_order implement parent/child story linking (a story with linked_story_id set is a "child" of that parent).
• Six image slots (StoryImage1..6 + *Id + *Tag), three external video slots, three "RS video" slots, two audio slots, four uploaded-file slots — all flat columns, not normalized child tables.
• Various flag columns mix representations: some are real tinyint booleans (JunkFree, TnUse, Interactive), some are bit(1) (EnableSlider, IsApproved, FullWidth, NewsWire), and some (visible, AusHorse, SyndicateNews, Standout, Linktome, FrontPage, etc.) are the ENUM('TRUE','FALSE') string pattern above.
• AccessScope maps to the StoryAccessScope enum, used by RestrictedStoryTagValidator to require certain tags in StoryContent when a story is restricted.
• StoryUpdated is client-supplied (from CreateStoryDto/UpdateStoryDto), not server-generated — whatever the Create/Edit Story form's Update Time field shows is exactly what gets persisted.

tblcms_site

Many-to-many mapping: CmsID → SiteID. Synced by delete-then-reinsert on every story create/update (StoryRepository.SyncSiteMappingsAsync); also used to scope story listing (EXISTS subquery, not a JOIN, so a multi-site story doesn't produce duplicate list rows) and story-link search.

tblcms_newstype

Many-to-many mapping: cmsID → NewsTypeID. Same sync pattern as tblcms_site.

tblsite

Site reference data (SiteCode, Name) — the sites lookup dropdown, and the site-scoping join target for tblcms_site.

tbljourno

Journalist/author reference data (JID, First, Last, Username) — the authors lookup falls back to Username when both name fields are blank.

tblcontributor

Contributor reference data (ID, Name) — the contributors lookup.

tbladmin / tblsite_newstype

tbladmin.sessionCode/Description joined against tblsite_newstype.NewsTypeID/SiteID to resolve news types available to a set of sites (the news-types lookup).

qdb database (QdbConnection)

Racing reference data, queried read-only by LookupRepository for editor dropdowns/search-as-you-type:

• horse (id, runner) — searched by runner LIKE %query%.
• trainer (id, name) — searched by name LIKE %query%.
• jockey (id, name) — searched by name LIKE %query%.
• course (id, name, last_update) — optionally filtered to a calendar date.
• race (id, race_name, course_id) — scoped to a required course_id.

photogallery database (PhotoGalleryConnection)

tblphotos

Stores both photo and video records in a single table, discriminated by the type column ('PHOTO'/'VIDEO', translated at the repository boundary to/from MediaType). Mapped onto the Media domain entity.

Key columns:

• id, name (base filename — the field Wasabi resolution actually matches on, see Architecture), title, description, keywords, type, photoType.
• origFile, small, medium, hd, hires, large — resized-variant URLs/keys as stored in the DB. Not used for read-side resolution (their labels don't reliably correspond to a fixed resolution across historical rows) but written on create/update as bare object keys.
• photoDate, entryDate.
• horse/horseID, jockey/jockeyID, trainer/trainerID, race/raceID — denormalized racing associations.
• photobyID (photographer), photoVersion (enum('Old','New','NewV2'), hardcoded to 'NewV2' on every new insert), site_id (hardcoded to 1 on insert per a 31 Aug 2026 decision), viewed (0 on insert), deleted (soft-delete flag; reads filter deleted = 0).

Video rows never populate small/medium/hd/hires/large (no resized renditions are generated for video uploads — confirmed against real data).

Query patterns worth knowing

• All list queries use CommandDefinition with bound parameters — the legacy Appsmith queries they were ported from used raw string interpolation and were injectable; this codebase deliberately parameterizes everything.
• Paged list queries run as a single QueryMultipleAsync (count + page) on one connection round-trip rather than two separate queries.
• Create/Update for Story run inside an explicit IDbTransaction covering the main row write plus both mapping-table syncs, with rollback on any failure — the legacy Appsmith version didn't wrap the mapping deletes in a transaction and could leave orphaned rows on partial failure.