Auto Sync Feature

This is a current-state explanation of how "Auto Sync" behaves today. For the commit-by-commit history of how it got here, see docs/auto-sync-v3-features.md and docs/auto-sync-v4-features.md in the repo — those are branch-review logs, not a standing reference.

There are two independent systems under the "Auto Sync" umbrella, sharing only a naming convention and the SyncJobStatus enum:

• Sync Jobs — generic, dependency-aware, arbitrary table sets.
• Copy-Sync — a thin scheduler around the two bespoke copiers (BarrierTrials, Sectional), which are already internally incremental.

Sync Jobs

Models: SyncJob (one row per named job) + SyncJobTable (one row per table in that job). Poller: SyncBackgroundService, a BackgroundService that checks every 1 minute (_checkInterval = TimeSpan.FromMinutes(1)) for jobs where IsEnabled && Status != Running && NextRunAt <= now, firing each due job on its own Task.Run with a fresh DI scope. Engine: SyncService.ExecuteJobAsync (QDG.Migration.Web/Services/SyncService.cs). One run does the following, in order:

1. Re-entrancy guard

Refuses to start if the job's tracked Status == SyncJobStatus.Running — protects against a manual "Run Now" landing in the same window as the poller.

2. Seed the floor from config_id_mappings

No watermark is persisted in SQLite. Every run asks the destination database fresh: "what's the highest SourceId already recorded for this table?" (GetMaxMigratedSourceIdAsync in DestinationMappingRepository). It resolves the table's TableId(s) from config_tables first (a source table can fan out to multiple TableIds), then runs SELECT MAX(SourceId) FROM config_id_mappings WHERE TableId = @tableId per TableId, with no JOIN — joining defeats MySQL's index-tail MAX() optimization on tables with tens of millions of rows (the difference is roughly 200ms vs. a 30s+ timeout). The resulting floor becomes a `pk` > {floor} clause, combined with the user's own filter and the snapshot cap below. If the lookup fails, the run proceeds with no floor — slower, but MigrateTableAsync's own duplicate-prevention still skips already-migrated rows.

3. Consistent snapshot

TakeSnapshotAsync opens one connection to the source, sets SET TRANSACTION ISOLATION LEVEL REPEATABLE READ then START TRANSACTION WITH CONSISTENT SNAPSHOT — one fixed InnoDB MVCC view for the entire run, not per table. Inside that single transaction it runs SELECT MAX(pk) for every job table (using each table's real PK column) and commits. Without a shared transaction, a parent+child pair inserted into the source mid-loop (e.g. a new meeting row and its race row) could land on opposite sides of separate per-table cuts — the child's later MAX query would see it, the parent's earlier one wouldn't, pulling in an unmigratable orphan reference. With one shared snapshot, such a pair is either both included or both deferred to the next cycle together. Each table's captured MAX(pk) becomes a `pk` <= {maxId} clause; on failure, capping is simply skipped for that table (best-effort, not blocking).

4. Resolve real primary keys

GetPrimaryKeyColumnsAsync calls ITableService.GetTableInfoAsync per table and takes its first PK column, because many legacy RS tables don't use a literal id (e.g. tblcountrycode.CountryID, tblmeetings.MUniqueID, tblcourses.CrsUniqueID). Both the floor and snapshot-cap clauses are built using this resolved column name. Falls back to "id" per table if schema inspection fails.

5. Auto-compute migration order

Default/fallback is tables.OrderBy(t => t.MigrationOrder) — the manually-dragged order from the UI. Instead, ComputeExecutionOrderAsync tries to build a real dependency order:

1. Real FK constraints detected live from the source schema (RelationshipService.GetDatabaseRelationshipsAsync).
2. Custom FK declarations saved via the wizard's Custom FK tab, from any prior session (GetCustomRelationshipsForTablesAcrossSessionsAsync).
3. Per-table ForeignKeysJson overrides on the SyncJobTable itself.
4. All three are merged and passed to RelationshipService.BuildDependencyGraphAsync, which builds a child → [parents] map and checks for circular dependencies (IDependencyResolver.ValidateDependenciesAsync).
5. A circular result logs a warning and falls back to manual order.
6. Otherwise GetMigrationOrderAsync performs the actual topological sort (IDependencyResolver.ResolveDependencyOrderAsync).
7. Safety net: if the computed order doesn't cover every table in the job for any reason, the whole thing falls back to manual order rather than risk silently dropping a table.
8. Any exception anywhere in this method falls back to manual order.

This matters beyond convenience: migrating a child before its parent triggers MigrationService.AutoCopyParentRowAsync to side-copy a single parent row on the fly, which poisons that parent's config_id_mappings-based watermark floor into skipping rows that were never actually migrated as a full row. Correct ordering avoids the scenario entirely.

6. Single-table vs. multi-table runs

• Single table (MigrateTableForJobAsync): creates its own dedicated MigrationSession, adds one SelectedTable, applies the saved Excel schema / column / FK overrides scoped to that table, then calls MigrationService.ExecuteMigrationAsync.
• Multi-table (MigrateJobTablesSharedSessionAsync): one shared MigrationSession for every table in the run — one SelectedTable per job table, the Excel schema applied once across all of them, one combined floor dictionary, one call to ExecuteMigrationAsync for the whole batch. MigrationService.ExecuteMigrationAsync already has its own multi-table pipeline (dependency order, FK resolution, per-table stats), so sharing a session means a sync run behaves exactly like a wizard-driven multi-table migration — and because /Migration/Progress and /Migration/Results are already generic per session GUID, no separate sync-specific progress/results UI is needed.

Both paths register the session with IActiveMigrationRegistry before executing and unregister in a finally — the same registry the wizard's Stop button on /Migration/Progress uses, so a sync-driven session's Stop button actually cancels it, not just flips a DB flag.

7. CurrentSessionGuid timing

The instant a run is marked Running, CurrentSessionGuid is cleared to null — otherwise, for the 10–15s it can take to resolve PKs/snapshot/order before a new session even exists, /Sync's "View Progress" link would misleadingly still point at the previous, already-finished run. The new GUID is recorded immediately after CreateSessionAsync, before the (potentially long) migration itself runs, so the progress link becomes valid the moment a session exists. In the finally block, if CurrentSessionGuid is still null when the run ends (setup failed or was cancelled before a session was ever created), the previous session's GUID is restored — so a failed/cancelled run doesn't destroy the "View Last Run Results" link to the last successful run.

8. Self-healing stuck jobs

ExecuteJobAsync sets Status = Running and saves it before its try/catch block begins. If the process dies (IIS recycle, crash) after that save but before the catch/finally runs, nothing else ever clears the flag — and because the poller's due-jobs query deliberately excludes Status == Running (to avoid double-triggering), a stuck job becomes permanently invisible to the scheduler: it looks like it's still running forever, but nothing is executing. On every app startup, Program.cs runs UPDATE SyncJobs SET Status = 0, LastError = '...recovered...' WHERE Status = 1 and logs a warning with the recovered count — pairing with the re-entrancy guard in step 1 means this never needs manual DB intervention.

Copy-Sync (BarrierTrials / Sectional)

Model: CopySyncConfig — one row per CopyFeature (BarrierTrials | Sectional), since each has exactly one fixed source→destination table set (nothing to name or list, unlike a SyncJob). Holds IsEnabled/IntervalMinutes/LastRunAt/NextRunAt/Status/LastError, plus CurrentJobGuid (the Copy-Sync analogue of SyncJob.CurrentSessionGuid) and FkConfigJson/FilterConfigJson.

Service: CopySyncService.ExecuteAsync uses the same re-entrancy guard pattern as SyncService.ExecuteJobAsync, sets Status = Running, clears CurrentJobGuid, generates a fresh jobId, registers it with ICopyJobRegistry, records CurrentJobGuid = jobId immediately — before the actual copy work, same "record before the long work" rationale as SyncJob — then calls BarrierTrialsCopyService.RunAsync or SectionalCopyService.RunAsync directly with the persisted FK/filter config.

Why it skips everything Sync Jobs does: no dependency graph, no snapshot transaction, no per-table PK resolution, no shared MigrationSession. There's exactly one fixed table set per feature, and BarrierTrialsCopyService/SectionalCopyService are already internally incremental — they skip rows already present in the destination — so re-running them on a timer with the same saved config is safe without any of that extra machinery.

Poller: CopySyncBackgroundService — structurally identical to SyncBackgroundService: same 1-minute interval, same due-config query shape, same detached-DI-scope pattern.

Self-healing: identical startup UPDATE CopySyncConfigs SET Status = 0 WHERE Status = 1, same rationale as Sync Jobs.

Sync Jobs vs. Copy-Sync

	Sync Jobs	Copy-Sync
Cardinality	many named jobs, arbitrary table sets	exactly one row per CopyFeature, fixed table set
Dependency ordering	auto-computed FK graph, falls back to manual order	none — not needed
Snapshotting	WITH CONSISTENT SNAPSHOT MAX(pk) per table	none
PK resolution	per-table, via ITableService	none — copy services handle their own row identity
Session/job tracking	shared MigrationSession, CurrentSessionGuid, links to /Migration/Progress/Results	ad-hoc jobId, CurrentJobGuid, links to the feature's own Progress(jobId) page
Incremental strategy	floor from config_id_mappings + snapshot cap	delegated to the copy services' own skip-if-exists logic

The /Sync UI

Controller: SyncController (/Sync, [Authorize], no role restriction on any action — see the API Reference page for the full route table). Views: Views/Sync/Index.cshtml, Edit.cshtml, Preview.cshtml.

• Index — table of all jobs: Name, Configuration, table count, interval, Last Run, Next Run, Status (with a spinner while Running, an error tooltip via LastError), and an Enabled toggle (POST /api/sync/{id}/toggle). Auto-refreshes every 30 seconds.
• Create/Edit — shared Edit.cshtml view; Save creates (Id == 0) or updates a job. Backed by /api/sync/tables, /columns, /destination-tables, /destination-columns, /table-mappings, /table-fks, plus UploadSchema for the per-configuration Excel schema.
• Delete — POST /Sync/Delete/{id} after a confirm dialog; removes the job and its SyncJobTable rows.
• Run Now — POST /api/sync/{id}/run-now launches the job on its own detached DI scope, then polls GET /api/sync/{id}/session-status every second (up to 60 attempts) until CurrentSessionGuid changes, before reloading — avoiding a race where the page reloads before the new session exists and shows the stale previous one.
• View Progress / Results — while Status == Running, a button links to /Migration/Progress?sessionId={CurrentSessionGuid}; once idle, the same slot links to /Migration/Results?sessionId={CurrentSessionGuid}. There is no sync-specific progress/results page — it reuses the wizard's own generic session-based pages.
• Cancel — no direct Cancel button on /Sync itself; cancelling happens on the linked /Migration/Progress page's Stop button, which works against sync-driven sessions because SyncService registers each session with the same IActiveMigrationRegistry the wizard uses.
• Preview — GET /Sync/Preview/{id} shows per-table source→destination column mappings (from the saved Excel schema) and detected FK relationships, without running the job.