Local Database Schema

This documents the app's own local SQLite database (titan_command.db) — the state store for migration sessions, field mappings, ID mappings, and sync jobs. It is not the remote MySQL data being migrated; for the QDG-side config_tables/config_id_mappings mirror, see docs/config-tables-explained.md in the repo.

How the schema is created

Program.cs calls dbContext.Database.EnsureCreated() on startup, not Database.Migrate(). Only three columns were ever added through real EF Core migrations (QDG.Migration.Data/Migrations/): SelectedTable.DateChunkColumn, FieldMapping.Scale, and IdMapping.Discipline. Everything else added after the app's initial release is patched in by hand-rolled PRAGMA table_info / ALTER TABLE / CREATE TABLE IF NOT EXISTS blocks in Program.cs (roughly lines 145–555), because EnsureCreated() does not apply migrations to an already-existing database file. When adding a new column to a model that's part of the already-created schema, add a matching idempotent ALTER TABLE block here too — a migration alone won't reach existing installs.

Table catalog

DatabaseConfigurations → DatabaseConfig

Named source (QDB) + destination (QDG) MySQL connection profile. Passwords are encrypted with EncryptionHelper before storage.

Column	Type	Notes
Id	int	PK
Name	string	required, unique index
SourceHost, SourceDatabase	string	required
SourcePort	int
SourceUsername, SourcePassword	string	password encrypted
DestinationHost, DestinationDatabase	string	required
DestinationPort	int
DestinationUsername, DestinationPassword	string	password encrypted
IsActive	bool	one "active" config drives the wizard/API by default
CreatedAt, UpdatedAt	DateTime

Parent of MigrationSession and SyncJob (cascade delete on both). Owned by ConfigRepository (IConfigRepository), which also implements the single-active-config pattern (GetActiveAsync/SetActiveAsync).

MigrationSessions → MigrationSession

The central "saved run" record — one per wizard-driven or sync-driven migration attempt.

Column	Type	Notes
Id	int	PK
SessionGuid	Guid	unique index, the public identifier used in URLs (/Migration/Progress?sessionId=)
ConfigurationId	int	FK → DatabaseConfigurations, required, cascade delete
Name	string?
Status	MigrationStatus enum	Draft/Ready/Running/Completed/Failed/Cancelled, stored as int, indexed
CreatedAt, StartedAt, CompletedAt	DateTime/DateTime?
PatchMode	bool	added via hand-rolled patch

Has-many SelectedTable, TableRelationship, FieldMapping. Owned by SessionRepository (ISessionRepository) — by far the largest repository (~755 lines), covering this table and everything below it down to TableNameLookups.

SelectedTables → SelectedTable

One row per source table chosen for a session.

Column	Type	Notes
Id	int	PK
SessionId	int	FK, indexed
TableName	string	required
DestinationTableName	string?	optional rename, hand-rolled patch
RowCount	long
FilterCondition	string?
CustomOrderBy	string?	hand-rolled patch
DateChunkColumn	string?	real EF migration (AddDateChunkColumn) — splits huge tables into per-day connections
SortOrder	int

TableRelationships → TableRelationship

Parent/child FK edges for a session's dependency graph, consumed by DependencyResolver to topologically order migration.

Column	Type	Notes
Id	int	PK
SessionId	int	indexed
ParentTable, ParentColumn, ChildTable, ChildColumn	string	required
IsCustom	bool	true when manually added rather than auto-detected from real source FKs

ColumnConfigurations → ColumnConfiguration

Per-column skip/drop flags for a table within a session.

Column	Type	Notes
Id	int	PK
SessionId	int	indexed
TableName, ColumnName	string	required
SkipCopy	bool	excluded from copy
DropColumn	bool	dropped in destination

FieldMappings → FieldMapping

Source column → destination column mapping per table — the core of the copy logic, usually bulk-populated from an uploaded schema Excel file (ExcelSchemaService).

Column	Type	Notes
Id	int	PK
SessionId	int	indexed
TableName, SourceColumn, DestinationColumn	string	required
Include	bool	default true
ForceNull	bool	hand-rolled patch
SortOrder	int?	hand-rolled patch
SortDirection	string	default "ASC", hand-rolled patch
Scale	double?	real EF migration (AddFieldMappingScale)

MigrationStats → MigrationStats

Per-table run outcome/metrics.

Column	Type	Notes
Id	int	PK
SessionId	int	indexed
TableName	string
SourceRowCount, DestinationRowCount, RowsCopied, RowsSkipped	long
ErrorCount	int
Duration	int
StartedAt, CompletedAt	DateTime/DateTime?

IdMappings → IdMapping

The crux table. Maps every migrated row's old source PK to its new destination PK, keyed additionally by Discipline since one source jockey/trainer can map to different destination rows per discipline raced (T/H/G).

Column	Type	Notes
Id	int	PK
SessionId	int	0 is a sentinel for cross-session/global mappings (see below)
TableName	string	source table name
SourceId, DestinationId	string	stored as strings even though source PKs are ints
Discipline	string?	real EF migration (AddDisciplineToIdMappings)

Constraints: unique composite index (SessionId, TableName, SourceId, Discipline) prevents duplicate mappings while allowing multiple destinations per source id across disciplines; a second, non-unique index (TableName, SourceId, Discipline) speeds up cross-session lookups. Not modeled as an EF navigation/FK to MigrationSession — a loose join, which is what allows the SessionId=0 sentinel. SessionRepository.ConfirmPendingIdMappingAsync writes SessionId=0 rows when promoting a confirmed PendingIdMapping. This is the SQLite counterpart to the remote MySQL config_tables/config_id_mappings pair.

MigrationLogs → MigrationLog

Free-form log lines emitted during a run, surfaced in the progress/results UI.

Column	Type	Notes
Id	int	PK
SessionId	int
TableName	string?
LogLevel	string	e.g. "Error"
Message	string
CreatedAt	DateTime	composite index (SessionId, CreatedAt)

NaturalKeyColumns → NaturalKeyColumn

Records which source columns form a table's "natural"/business key (e.g. meeting = date+course+country+discipline), used by backfill to find manually-added destination rows lacking an IdMapping. Table created via hand-rolled CREATE TABLE even though it's fully modeled in AppDbContext.

Column	Type	Notes
Id	int	PK
SessionId	int	composite index (SessionId, TableName)
TableName	string	source table name
ColumnName	string	one row per key column

PendingIdMappings → PendingIdMapping

Candidate source→destination ID matches (from natural-key matching or manual entry) awaiting human confirmation before promotion into IdMappings. Table created via hand-rolled CREATE TABLE.

Column	Type	Notes
Id	int	PK
SessionId	int?	null for manually-entered rows
TableName, SourceId, DestinationId	string	required
MatchType	string	"NaturalKey" / "Manual"
MatchDetails	string?	JSON of matched column=value pairs
Status	PendingMappingStatus enum	Pending=0/Confirmed=1/Rejected=2, stored as int
CreatedAt	DateTime	default UtcNow
ReviewedAt	DateTime?

Indexes: (TableName, SourceId) and (Status).

TableNameLookups → TableNameLookup

Per-DatabaseConfig source↔destination table name lookup, used to resolve a destination table back to its source name (e.g. for repair/backfill tooling) independent of any specific session. Table created via hand-rolled CREATE TABLE IF NOT EXISTS.

Column	Type	Notes
Id	int	PK
ConfigurationId	int	FK to DatabaseConfigurations, app-level (no formal EF FK)
SourceTableName, DestinationTableName	string	required

Composite index (ConfigurationId, DestinationTableName).

SyncJobs → SyncJob

A named, recurring incremental-migration job for a fixed table set against one DatabaseConfig, polled every minute by SyncBackgroundService. Table created via hand-rolled CREATE TABLE IF NOT EXISTS.

Column	Type	Notes
Id	int	PK
Name	string
ConfigurationId	int	FK → DatabaseConfigurations, required, cascade delete
IsEnabled	bool	indexed
IntervalMinutes	int	default 60
LastRunAt, NextRunAt	DateTime?
Status	SyncJobStatus enum	Idle/Running/Error, stored as int
LastError	string?
CreatedAt	DateTime
CurrentSessionGuid	Guid?	hand-rolled patch, links to the MigrationSession for the job's current/latest run

Has-many SyncJobTable (cascade delete). Self-heals stuck Status=Running rows on every app startup. No dedicated repository — accessed directly via AppDbContext in QDG.Migration.Web/Services/SyncService.cs and SyncBackgroundService.cs.

SyncJobTables → SyncJobTable

One row per table included in a SyncJob — its own filter/order/date-chunk/column-override/ FK-override config, independent of the wizard's SelectedTable/FieldMapping for the same source table. The four nullable JSON/string columns below were added later via a loop over a column-name dictionary in Program.cs.

Column	Type	Notes
Id	int	PK
SyncJobId	int	FK, indexed, cascade delete
TableName	string	required
DestinationTableName	string?
MigrationOrder	int	manual fallback order
FilterCondition	string?
CustomOrderBy	string?	hand-rolled patch
DateChunkColumn	string?	hand-rolled patch
ColumnOverridesJson	string?	hand-rolled patch, overrides Excel-based field mappings
ForeignKeysJson	string?	hand-rolled patch, custom FK overrides for this table

CopySyncConfigs → CopySyncConfig

"Auto Sync" settings for the two bespoke, non-generic copy pipelines (BarrierTrials, Sectional) — exactly one row per CopyFeature since each has a single fixed source→destination table set. Table created entirely via hand-rolled CREATE TABLE IF NOT EXISTS + CREATE UNIQUE INDEX; also self-heals stuck Status=Running rows on startup.

Column	Type	Notes
Id	int	PK
Feature	CopyFeature enum	BarrierTrials/Sectional, stored as int, unique index
IsEnabled	bool
IntervalMinutes	int	default 60
LastRunAt, NextRunAt	DateTime?
Status	SyncJobStatus enum	reused from SyncJob
LastError	string?
CreatedAt	DateTime
CurrentJobGuid	Guid?	the Copy-Sync analogue of SyncJob.CurrentSessionGuid
FkConfigJson	string?	JSON list of BarrierTrialsFkConfig/SectionalFkConfig
FilterConfigJson	string?	JSON filter config

No dedicated repository — direct AppDbContext access in QDG.Migration.Web/Services/CopySyncService.cs and CopySyncBackgroundService.cs.

Repository map

Repository	Interface	Tables owned
ConfigRepository	IConfigRepository	DatabaseConfigurations
SessionRepository	ISessionRepository	MigrationSessions, SelectedTables, TableRelationships, ColumnConfigurations, FieldMappings, MigrationStats, IdMappings, MigrationLogs, NaturalKeyColumns, PendingIdMappings, TableNameLookups
(direct AppDbContext access)	—	SyncJobs, SyncJobTables, CopySyncConfigs
DestinationMappingRepository	IDestinationMappingRepository	Remote MySQL config_tables/config_id_mappings — not SQLite, listed for context only

Notable quirks

• Stale Users table: still present in QDG.Migration.Data/Migrations/AppDbContextModelSnapshot.cs (Username/PasswordHash/TotpSecret/Role/etc.) but has no corresponding DbSet in AppDbContext anymore — a leftover from before auth was delegated to QDBAuth. EnsureCreated() on a fresh DB will not create it, since it's no longer in the live model graph.
• Shadow FK columns: EF auto-generates an implicit MigrationSessionId (nullable int) column on SelectedTables, TableRelationships, and FieldMappings, distinct from the app-managed SessionId int the code actually reads/writes. This happens because MigrationSession has List<SelectedTable>/List<TableRelationship>/List<FieldMapping> navigation properties with no matching inverse navigation, so EF infers an extra relationship on top of the manual FK.
• IdMappings.SessionId = 0 sentinel: used for mappings promoted from a confirmed PendingIdMapping that isn't tied to one specific session — see SessionRepository.ConfirmPendingIdMappingAsync.
• Hand-rolled schema, not EF migrations: NaturalKeyColumns, PendingIdMappings, TableNameLookups, SyncJobs, SyncJobTables, and CopySyncConfigs all exist only because of CREATE TABLE IF NOT EXISTS blocks in Program.cs, and several columns on otherwise EF-migrated tables (SelectedTable.DestinationTableName/CustomOrderBy, FieldMapping.ForceNull/SortOrder/SortDirection, SyncJob.CurrentSessionGuid, all four extra SyncJobTable columns) were added the same way. If you add a new column to a model that's part of the already-created schema, you need a matching ALTER TABLE block here — an EF migration alone won't reach an existing titan_command.db file.