# Technical documentation — sync_coordinator

Evidence date: 2026-10-01.

Source: `git@bitbucket.org:overdrive/sync_coordinator.git`, `main` at `0e109de` (2026-08-17).

This file is the implementation map. Product behaviour and business rules are in `prd.md` and `business-rules.md`. Long Access-to-T-SQL mapping tables stay in the source guide `docs/sync/access-sql-express-compatibility.md`; they were not copied here.

The scan-host path was not available. Facts below are from the git tree at `0e109de`. The executable was not built or run in this pass.

## What this repository is

A Windows console application, assembly name `SyncEngine`, that copies an Access `.mdb` into SQL Server Express and then upserts active products into MySQL. Solution file: `SyncEngine.sln`.

It is not a web application. It has no `Dockerfile`, no `bitbucket-pipelines.yml`, and no `sonar-project.properties` (the onboarding scan listed a Sonar file; git history has none).

## Application structure

```text
SyncEngine.sln
SyncEngine/                 net48 exe
  Program.cs
  Access/                   OLEDB, DAO/COM, translators
  Configuration/            .env, validation, exclude list
  Logging/                  SyncStatistics
  Metadata/                 sync_metadata schema
  MySql/                    shop publish and log
  SqlServer/                DDL, data sync, collisions, views
  Sync/                     orchestrator, lock, retry, hash, soft delete
  App.config
  Properties/AssemblyInfo.cs
SyncEngine.Tests/           MSTest, references SyncEngine
config/                     .env.example, product-sync-map.json, table-overrides.json, access-views.json.example
docs/sync/                  operator and architecture guides
docs/anniesapp/             notes about the Access front-end (source not in this repo)
scripts/Test-AccessRuntimeCom.ps1
README.md
```

`GenerateAssemblyInfo` is false. Assembly attributes are in `SyncEngine/Properties/AssemblyInfo.cs`. Language version is 7.3.

Namespaces follow the folders. `Program` is `internal`. Most sync types are `public`. Collision types used only inside the exe are `internal`.

## Frameworks and libraries

| Piece | Version | File |
|---|---|---|
| Target framework | `net48` | both csproj files |
| Platform | `x64` | both csproj files |
| Microsoft.Data.SqlClient | 5.2.2 | `SyncEngine.csproj` |
| MySqlConnector | 2.4.0 | same |
| Newtonsoft.Json | 13.0.3 | same |
| Polly | 7.2.4 | same |
| Serilog | 4.2.0 | same |
| Serilog.Sinks.Console | 6.0.0 | same |
| Serilog.Sinks.File | 6.0.0 | same |
| Microsoft.NET.Test.Sdk | 17.12.0 | test csproj |
| MSTest | 3.6.4 | test csproj |

Framework references: `System`, `System.Core`, `System.Data`, `System.Net.Http`, `Microsoft.VisualBasic` (DAO/COM interop helpers).

ACE OLEDB 12.0 and Access DAO are machine installs, not NuGet packages.

## Runtime flow

`Program.Main`:

1. `EnvSettings.LoadFromStandardLocations` — exe directory, then repo root.
2. Serilog, minimum Information, console plus `logs/sync-.log` rolling daily. The documented name `logs/sync-YYYYMMDD.log` matches Serilog's day rolling of the `sync-` prefix.
3. `SyncInstanceLock.TryAcquire`.
4. `SyncOptions.LoadFromEnvironment` then `SyncOptionsValidator.Validate`.
5. `new SyncOrchestrator(options, Log.Logger).Run(mode)`.

`SyncOrchestrator.Run` calls `RunAccessToSqlExpress` when mode is `all`, `access`, or `schema-only` and `SyncAccessToSqlExpress` is true. It then calls `RunSqlExpressToMySql` when mode is `all` or `mysql` and `SyncSqlExpressToMySql` is true. It does not return between the legs. Exit code is `Math.Max` of the leg codes.

## Databases

### Access

OLEDB provider Microsoft.ACE.OLEDB.12.0 through `AccessConnectionFactory`. Password from `ACCESS_DB_PASSWORD` as the Jet database password.

Schema enrichment uses `Access.Application` when full Access is installed. On Access Runtime, `AccessApplicationSysReader` falls back to `DAO.DBEngine.OpenDatabase` with `;PWD=`. Getting-started documents that MSys GRANT is skipped on the Runtime path, and that data sync does not need COM.

### SQL Server Express

`SqlServerConnectionFactory.Open`:

- Reads the target catalog from the connection string, otherwise `SQL_EXPRESS_DATABASE` (default `AnniesAngels`).
- Opens `master`.
- `CREATE DATABASE` if `DB_ID` is null. The database name is bracket-escaped. It is concatenated into `EXEC('CREATE DATABASE ...')` after `]` is doubled. It is not a SQL parameter inside the `CREATE DATABASE` text (`DB_ID` uses a parameter).
- `ChangeDatabase` to the target.

`SyncOptions.BuildSqlConnectionString` appends `Database=` from `SQL_EXPRESS_DATABASE` only when the base string has neither `Database=` nor `Initial Catalog=`. If the operator puts a database in the connection string, that name wins over `SQL_EXPRESS_DATABASE`.

Default base string when `SQL_EXPRESS_CONNECTION_STRING` is empty: `Server=.\SQLEXPRESS;Database={name};Trusted_Connection=True;TrustServerCertificate=True`.

Mirrored objects use `SQL_EXPRESS_SCHEMA` (default `dbo`). Audit objects use schema `sync_metadata`:

| Table | Role |
|---|---|
| `runs` | Leg name, times, status, counts, error JSON |
| `checkpoints` | Per run, per table |
| `schema_versions` | DDL snapshots |

Created by `SyncMetadataRepository`. Called from `SqlSchemaProvisioner.EnsureDatabase`, which does not create the SQL Server database (the factory does).

Sync columns on every mirrored table: `_sync_row_hash` nvarchar(64), `_sync_is_deleted` bit, `_sync_last_seen_at` datetime2, `_sync_updated_at` datetime2. **Documented** in the architecture guide.

### MySQL

Connection string built in `SyncOptions.MySqlConnectionString`:

```text
Server={host};Port={port};User ID={user};Password={password};Database={database};SslMode=Preferred;TlsVersion=Tls12;Connection Timeout=30;Allow User Variables=True;Use Affected Rows=True
```

`Use Affected Rows=True` makes `INSERT ... ON DUPLICATE KEY UPDATE` report classic 1 (insert), 2 (update), 0 (unchanged). **Documented** and present in the string.

`ShopSchemaValidator.RequireTables` runs `SELECT 1 FROM `{table}` LIMIT 0` for `shop_products` and `shop_sync_log`, and `SELECT `sqlexpress_product_id` FROM `shop_products` LIMIT 0`. MySQL error for unknown column (1054) and unknown table become `ConfigurationException`. Dry-run returns before those statements.

Upsert SQL is built by `ShopProductSql.BuildMultiRowUpsert`. Chunk size constant `UpsertChunkSize` is 500. Update columns are listed in that class and do not include `image_url_1`, `image_url_2`, or `image_url_3`.

The expected shop column list and index names are **Documented** in `docs/sync/shop_products.md`. They are not created by this repository. **Unknown / Requires Confirmation** against the CI4 shop.

## External integration

### CI4 shop sync log API

Used only when the MySQL insert into `shop_sync_log` fails and both `SHOP_SYNC_API_URL` and `SHOP_SYNC_API_KEY` are non-empty.

| | |
|---|---|
| Method | `POST` |
| URL | The configured absolute URL, not a path appended by the client |
| Header | `X-Shop-Sync-Key: {SHOP_SYNC_API_KEY}` |
| Body | JSON: `status`, `source_reachable` (0/1), `products_upserted`, `products_withdrawn`, `run_started_at`, `run_finished_at`, `message` |
| Timeout | 30 seconds (`HttpClient.Timeout`) |
| Success | Any HTTP success status |
| Failure | Warning log, exception, then local JSONL |

Source: `SyncEngine/MySql/ShopSyncLogApiClient.cs`. The server side is not in this repo. Configuration comments say the key must match CI4 `shop.syncApiKey`. That setting was not verified here.

## Configuration

| Input | Purpose |
|---|---|
| `.env` | Connections, switches, batch size, retries, timeout, soft delete, dry run. Gitignored. Example: `config/.env.example` |
| `config/product-sync-map.json` | `barcodeColumn`, `imageUrl1Column`, `productIdColumn` used. `productTable` and `lookupJoins` not used |
| `config/access-views.json` | Optional. Not committed. Example file is committed |
| `config/table-overrides.json` | Committed, empty, unused |
| `App.config` | Binding redirects, copied to output |
| csproj `PlatformTarget` | x64. Changing `SYNC_PLATFORM_TARGET` in `.env` does not rebuild or rehost the process |

`.env` search order and the MSBuild `CopyEnvToOutput` target are **Implemented**. A build with no repo-root `.env` skips the copy (`Condition="Exists(...)"`).

Code defaults when a variable is missing (`SyncOptions.LoadFromEnvironment`):

| Variable | Code default | Example file |
|---|---|---|
| `SQL_EXPRESS_DATABASE` | `AnniesAngels` | `AnniesAngels` |
| `SQL_EXPRESS_SCHEMA` | `dbo` | `dbo` |
| `MYSQL_HOST` | `127.0.0.1` | `127.0.0.1` |
| `MYSQL_PORT` | `3306` | `3306` |
| `MYSQL_DATABASE` | `annies_angels_shop` | `annies_angels_shop` |
| `SYNC_ACCESS_TO_SQL_EXPRESS` | true | true |
| `SYNC_SQL_EXPRESS_TO_MYSQL` | true | true |
| `SYNC_BATCH_SIZE` | **5000** | **2000** |
| `SYNC_MAX_RETRIES` | 3 | 3 |
| `SYNC_COMMAND_TIMEOUT_SECONDS` | 120 | 120 |
| `SYNC_SOFT_DELETE_ENABLED` | true | true |
| `SYNC_DRY_RUN` | false | false |
| `SYNC_PLATFORM_TARGET` | `x64` | `x64` (unread) |
| `ACCESS_SQL_EXCLUDE_TABLES` | empty | the six-name example list |

Full variable notes: `docs/sync/configuration.md`.

## Authentication and secrets

| Secret | Where | Handling |
|---|---|---|
| Access database password | `ACCESS_DB_PASSWORD` | In `.env`, not in git |
| Access workgroup password | `ACCESS_WORKGROUP_PASSWORD` | Separate from the database password |
| SQL Express | Connection string | Example uses trusted connection. A SQL login would be in the same string if an operator set one |
| MySQL password | `MYSQL_PASSWORD` | May be empty and still pass validation |
| Shop API key | `SHOP_SYNC_API_KEY` | Sent as a request header |

`.gitignore` also ignores `docs/password.txt`, `logs/`, `*.log`, `New_Annies_Angels_be.mdb`, `shop_docs/`, `AnniesAngels.mysql.sql`, and `AnniesAngelsProducts.mysql.sql`. None of those ignored payloads are in the tree. Do not treat them as available evidence.

There is no encryption of `.env` at rest in this repository. The process logs the paths it loaded, not a documented redaction of password values. Whether Serilog or connection exceptions can print a connection string is **Unknown / Requires Confirmation**; this pass did not fault a live connection.

## Background work, queues, storage

No queue and no hosted service. The process is a single foreground run started by a person or by Task Scheduler. Overlap is rejected by the mutex rather than queued.

File storage is the log directory beside the exe, and the Access file path. Images are not copied as files. `strImagePath` is a string stored in MySQL on insert only.

## Logging and monitoring

| Destination | Content |
|---|---|
| Console | Information and errors |
| `logs/sync-YYYYMMDD.log` | Same stream |
| `sync_metadata.runs` | Access-leg audit |
| `shop_sync_log` | MySQL-leg audit |
| `logs/shop-sync-log-failed-YYYYMMDD.jsonl` | Log payload when MySQL and API both fail |

No health endpoint, metrics exporter, or alerting integration is in the tree. Exit codes are the scheduler's signal (0, 1, 2, 3). See `prd.md` FR-018.

## Deployment

**Documented** ship set: `SyncEngine.exe`, `.env`, `config\`, `App.config`, writable `logs\`.

Build:

```bat
dotnet build SyncEngine.sln -c Release
```

Output path documented in the README: `SyncEngine\bin\Release\net48\SyncEngine.exe`. That matches an SDK-style `net48` Release build. This pass did not produce the binary.

For 32-bit ACE, both csproj files must be changed to `<PlatformTarget>x86</PlatformTarget>` and rebuilt. **Documented** in `docs/sync/build-and-test.md`.

Scheduling is Windows Task Scheduler. Arguments `all`. Start-in directory must contain the exe and `.env`. A run that is still going causes the next trigger to exit 3.

Documented host constraints include SBS 2011 / Windows Server 2008 R2 (TLS 1.2 disabled by default in Schannel, which is why the connector pins `TlsVersion=Tls12`) and PowerShell 2.0 for the COM probe script. Whether the current sync server is still that generation is **Unknown / Requires Confirmation**.

## Development and tests

```bat
dotnet test SyncEngine.sln -c Release
```

Test classes and `[TestMethod]` counts in source:

| File | Methods |
|---|---|
| `ProductFieldMapperTests` | 19 |
| `SqlViewTranslatorTests` | 14 |
| `UniqueKeyCollisionClassifierTests` | 11 |
| `MySqlProductSyncTests` | 10 |
| `MySqlConnectionFailuresTests` | 10 |
| `AccessApplicationSysReaderTests` | 8 |
| `TableExcludeFilterTests` | 6 |
| `SyncOptionsValidatorTests` | 6 |
| `SqlSchemaKeyBuilderTests` | 6 |
| `AccessDefaultTranslatorTests` | 6 |
| `AccessTypeMapperTests` | 5 |
| `MySqlTlsConnectionStringTests` | 4 |
| `ShopSyncLogClientFallbackTests` | 4 |
| `ChangeDetectorTests` | 4 |
| `DataSyncPrimaryKeyHelperTests` | 4 |
| `SyncInstanceLockTests` | 3 |
| `ShopSyncLogApiClientTests` | 3 |
| `AccessIndexGrouperTests` | 3 |
| `ShopSyncLogClientTests` | 2 |
| `SyncStatisticsTests` | 2 |
| `SqlIdentityHelperTests` | 2 |
| `AccessValidationTranslatorTests` | 2 |
| `ShopSyncLogLocalWriterTests` | 1 |
| `SqlIdentifierHelperTests` | 1 |

No CI definition is in the repository, so it is **Unknown** whether Bitbucket runs these tests on push. There is no checked-in coverage threshold.

`docs/sync/build-and-test.md` is the operator build note. `docs/sync/index.md` also tells editors to use a `-docs` trigger and `TASK-XXX` commits. That index section matches a documentation template more than this repo's history. **Discrepancy.**

## Security notes visible in the tree

- Credentials are environment-file based and excluded from git.
- The shop log API key is a shared static header, not an OAuth flow.
- `CREATE DATABASE` interpolates the database name after escaping `]`. Other identifiers go through `SqlIdentifierHelper` (tested). A full SQL-injection review was not the task of this document.
- Dry-run still writes `shop_sync_log` when the MySQL leg runs.
- The mutex name is fixed and global. An account that cannot create it exits 3, which looks the same as "another instance is running".

## Related source documents

Read these in the source repository when changing behaviour. They are more detailed than this summary and were the starting evidence:

| Path | Subject |
|---|---|
| `README.md` | Commands, exit codes, ship folder. Links are stale after `0e109de` |
| `docs/sync/getting-started.md` | Prerequisites, first run, troubleshooting |
| `docs/sync/configuration.md` | Every setting, including unused ones |
| `docs/sync/sync-architecture.md` | Leg flows, failure semantics, class list |
| `docs/sync/access-sql-express-compatibility.md` | Type and SQL translation |
| `docs/sync/shop_products.md` | MySQL column contract as documented |
| `docs/sync/build-and-test.md` | Build and test commands |
| `docs/anniesapp/index.md` | Pointers to AnniesApp notes that are not this exe |

## Unknown / Requires Confirmation

- Live host, instance names, and whether Task Scheduler is installed.
- Match between `docs/sync/shop_products.md` and the real CI4 migration.
- Repository that contains AnniesApp VBA and `Resolve-SqlOdbcDriver.ps1`.
- Whether Bitbucket Pipelines or SonarQube are configured outside the git tree.
- Whether connection errors leak secrets into `logs/sync-*.log`.