Titan Command

Internal ASP.NET Core 8 web app that migrates/syncs data from a legacy MySQL database (QDB, tblXxx-prefixed tables) into a redesigned destination MySQL schema (QDG). Migration-session state (sessions, field mappings, ID mappings, sync jobs) lives in a local SQLite database (titan_command.db); the actual source/destination data lives in remote MySQL servers reached via MySqlConnector.

Solution structure

• QDG.Migration.Core — domain models, service interfaces, business logic (Services/). No ASP.NET/EF Core dependency; MySQL access goes through MySqlConnector/Dapper directly.
• QDG.Migration.Data — EF Core AppDbContext (SQLite) + repositories for app state (sessions, mappings, sync jobs, copy-sync configs). No local Users table — auth is delegated.
• QDG.Migration.Web — ASP.NET Core MVC host (MVC controllers + Razor views, a couple of Blazor pages). DI wiring lives in Program.cs.

Build/run: dotnet build QDG-DB-Migrator.sln, dotnet run --project QDG.Migration.Web (serves at http://localhost:5283). No automated tests, no lint config — verification is manual through the running app. Deploys via .github/workflows/deploy.yml: a self-hosted Windows runner builds/publishes/robocopies into an IIS site (QDG-Migrator) on push to master — no staging environment, master deploys straight to production.

Schema evolution for the SQLite app-state DB is mostly done by hand-rolled PRAGMA table_info / ALTER TABLE blocks in Program.cs, not EF migrations, because EnsureCreated() doesn't apply migrations to an existing database file.

Migration pipeline

Everything is organized around a MigrationSession, built from a DatabaseConfig (source/destination host+db+encrypted credentials). A session accumulates:

• SelectedTable rows — which source tables to migrate, optional rename/filter/custom ORDER BY, and an optional date-chunk column for splitting huge tables (e.g. tblruns, ~44M rows) into per-day connections to avoid timeouts.
• TableRelationship rows — parent/child FK graph, topologically sorted by DependencyResolver.
• FieldMapping rows — source→destination column mapping, usually bulk-populated from an uploaded schema-definition Excel file (ExcelSchemaService).

MigrationService.ExecuteMigrationAsync (Core/Services/MigrationService.cs, ~2900 lines, the largest/most important file) runs per table in dependency order: read from source (chunked by date if configured) → apply field mappings → resolve FKs against already-migrated parents → insert into destination → record IdMapping rows. Grep for method names rather than reading top-to-bottom; key entry points are ExecuteMigrationAsync, MigrateTableAsync, ResolveForeignKeysAsync / AutoCopyParentRowAsync.

ID mapping is the crux of the system: every migrated row's old ID maps to its new ID in the IdMappings table, keyed by (SessionId, TableName, SourceId, Discipline). Discipline (T/H/ G, 'A' normalized to 'T' via DisciplineMapping.NormalizeCode) exists because the same source jockey/trainer can need different destination rows per discipline raced. When a child references an unmigrated parent, AutoCopyParentRowAsync copies that parent on the fly.

The production-DB-side mirror of IdMappings is config_tables (registers migrated tables, gets an Id) + config_id_mappings (old ID → new ID per config_tables.Id) — this is how e.g. run.trainer_id resolves through config_tables.Id.

Post-migration repair flows on MigrationService: BackfillNullForeignKeysAsync, BackfillSpecificFkAsync, FindNaturalKeyMatchesAsync, PatchFkTableAsync, BackfillBooleanColumnsAsync (fixes source 'TRUE'/'FALSE' strings MySQL silently casts to 0 on real tinyint(1) columns).

Specialized copy services

BarrierTrialsCopyService and SectionalCopyService are bespoke copiers outside the generic MigrationService pipeline, for tblbarriertrials → trial_meeting/trial_race/trial_run and tblsectionalinrunning → sectional_race/sectional_run. Both batch in groups of 5,000 rows and resolve FKs against already-migrated main-schema tables. Each has its own recurring "Auto Sync" mode (CopySyncConfig, one row per CopyFeature), driven by CopySyncService + CopySyncBackgroundService on a timer — simpler than SyncJob since there's only one fixed source→destination table set per feature.

DisciplineRepairService (/DisciplineRepair, Admin/Operator-only) is a standalone admin tool that repairs jockey/trainer rows auto-copied with no discipline set, recovering the correct discipline from whichever destination child row still references the broken row.

Sync jobs (recurring/incremental migration)

SyncJob + SyncJobTable define a named, recurring migration of a table set, checked every minute by SyncBackgroundService and executed by SyncService. Each run: resolves each table's actual PK column (many RS tables don't use id literally), takes a snapshot MAX(pk) per table inside one InnoDB WITH CONSISTENT SNAPSHOT transaction, seeds the starting point from config_id_mappings on the destination, auto-computes migration order from real FK dependencies (falling back to manual order on failure), and shares one MigrationSession across all tables in a multi-table run. SyncJob.CurrentSessionGuid is recorded before the migration runs, so /Sync can link to live progress. A job stuck at Status = Running from a process that died mid-run self-heals on next app startup.

Cancelling a running migration

IActiveMigrationRegistry (singleton) tracks a CancellationTokenSource per active MigrationSession GUID. Both MigrationController and SyncService register into the same instance, so the Stop button on /Migration/Progress works regardless of which one started the session.

Connection resilience

ConnectionResilience is shared retry/reconnect logic used by every long-running MySQL copy path (MigrationService, BarrierTrialsCopyService, TableService). It retries a dropped socket with exponential backoff (6 attempts, capped at 30s) but does not retry genuine query errors.

Known gap: MigrationService.MigrateTableAsync's destination-write retry path (FlushBatchAsync → EnsureConnectionAsync) has a bug — the first reconnect attempt inside EnsureConnectionAsync isn't itself wrapped in a retry, so if it fails, the exception skips the batch-retry/row-by-row fallback logic and aborts the whole table. Symptom: a table fails mid-run (logged as row -1) with "An established connection was aborted by the software in your host machine" — a local-machine network interruption, not a remote DB issue.

Auth

Delegated to a shared identity provider ("QDBAuth") used across the QDB ecosystem — not ASP.NET Identity, no local accounts. AccountController is a thin client: ICentralAuthService.LoginAsync (email/password) → VerifyTwoFactorAsync (6-digit code) → WhoAmIAsync (resolve identity/permissions). On success, IAuthTokenService mints this app's own local session JWT (cookie-based via AuthCookieHelper). Roles: Admin/Operator/Viewer, derived from QDBAuth's permission string via AuthTokenService.MapPermissionToRole.

QDBAuth access tokens are short-lived (15 min); SilentTokenRefreshFilter proactively refreshes ~2 minutes before expiry, sliding the session forward up to the refresh token's ~1-day ceiling. TokenRefreshCoordinator serializes refresh calls per session since QDBAuth's refresh tokens are single-use. All MVC actions require authentication by default via a global AuthorizeFilter — public actions need explicit [AllowAnonymous].