# PRD — sync_coordinator

Evidence date: 2026-10-01.

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

There is no prior PRD in Silicon Ark. The source README and `docs/sync/` are the written product description. Requirements below were checked against `Program.cs`, `SyncOptions`, `SyncOptionsValidator`, `SyncOrchestrator`, `SqlServerConnectionFactory`, `SqlDataSync`, `UniqueKeyCollisionClassifier`, `ProductFieldMapper`, `ShopProductSql`, `MySqlProductSync`, `ShopSchemaValidator`, `ShopSyncLogApiClient`, and `SyncInstanceLock`. Provenance is labelled.

This document was not verified by running a sync. Unit tests exist; they were not executed in this pass.

## Product purpose

**Documented.** One-way coordinator: Access `.mdb` → SQL Server Express mirror → MySQL `shop_products` for the web shop.

**Implemented.** A Windows console process with four modes. It provisions and fills SQL Express from Access, then upserts an active product catalogue into MySQL. It never writes back to Access.

**Inferred.** The catalogue belongs to Annie's Angels (database and file names, AnniesApp docs, hire-style fields such as daily rate and Camera Ready).

**Unknown / Requires Confirmation:** who operates it day to day, whether it is still scheduled after 2026-08-17, and which shop repository owns the CI4 schema.

## Users and interactions

| Actor | Evidence | Status |
|---|---|---|
| Operator on Windows | CLI arguments and Task Scheduler instructions in `README.md` and `docs/sync/getting-started.md` | **Documented.** No UI in this repo |
| Service account running Task Scheduler | Same docs: "Run whether user is logged on or not" | **Documented.** The account is not named |
| Access or Access Runtime on the same machine | DAO/COM for schema enrichment only | **Implemented.** Data reads use ACE OLEDB |
| SQL Server Express | Connection string, database create-if-missing | **Implemented** |
| MySQL shop database | Leg 2 upsert and `shop_sync_log` | **Implemented** as a client. The server and schema live elsewhere |
| CI4 shop HTTP API | Optional `POST` with header `X-Shop-Sync-Key` when the MySQL log insert fails | **Implemented** client. The API implementation is not in this repo |
| AnniesApp users | `docs/anniesapp/` — forms use ODBC linked tables to SQL Express | **Documented** here. The front-end source is not in this repo |
| Person editing the repo | `docs/sync/index.md` coding conventions | **Documented.** Git history does not follow the stated branch and commit format |

There is no login, role, or end-user account inside `SyncEngine`. Anyone who can start the exe and read `.env` can run a sync.

## Systems the product talks to

1. A Jet/ACE `.mdb` file (`ACCESS_DB_PATH`). Example name `New_Annies_Angels_be.mdb`. The file was removed from git in `f79676b` and is gitignored.
2. SQL Server, default instance `.\SQLEXPRESS`, Windows trusted connection in `.env.example`. `SqlServerConnectionFactory.Open` connects to `master` and runs `CREATE DATABASE` when `DB_ID` is null, then switches to the target database (default name `AnniesAngels`).
3. MySQL (`MYSQL_HOST`, default `127.0.0.1:3306`, database `annies_angels_shop`). Connection string sets `SslMode=Preferred`, `TlsVersion=Tls12`, `Connection Timeout=30`, `Use Affected Rows=True`.
4. Optional CI4 endpoint `SHOP_SYNC_API_URL` (example comment: `POST /api/shop-sync/log`).

No queue, mailer, cache, object store, or metrics vendor is configured. Logging is Serilog to the console and `logs/sync-YYYYMMDD.log`, plus SQL Express `sync_metadata.runs` and MySQL `shop_sync_log`.

## Major workflows

### WF-1 — Start a run

**Implemented** in `Program.Main`.

1. Load `.env` from the exe directory, then the repo root (`EnvSettings.LoadFromStandardLocations`).
2. Create `logs` beside the exe and configure Serilog (information and above; rolling daily file `sync-.log`).
3. Acquire mutex `Global\SyncCoordinator.SyncEngine`. If the mutex already exists, or creation throws `UnauthorizedAccessException`, log a warning and exit **3**. Configuration is not loaded.
4. Mode is `args[0]`, or `all` when there are no arguments.
5. Missing `.env` exits **2**.
6. `SyncOptionsValidator.Validate` exits **2** on configuration errors (`ConfigurationException`).
7. `SyncOrchestrator.Run` performs the legs. An unhandled exception exits **1**.

### WF-2 — Access to SQL Express (`access`, `schema-only`, or `all` when the leg is enabled)

**Implemented** in `SyncOrchestrator.RunAccessToSqlExpress`. **Documented** in `docs/sync/sync-architecture.md`.

1. Read user tables, columns, and AutoNumber via ACE OLEDB. Skip system tables (`MSys*`, `~TMPCLP*`) and names in `ACCESS_SQL_EXCLUDE_TABLES`.
2. Enrich defaults and validation rules through DAO when Access or Access Runtime can open the file.
3. Read queries, indexes, and foreign keys (OleDb system tables, then COM/DAO fallback, then `config/access-views.json` for view SQL).
4. Order tables by foreign-key dependencies.
5. Open SQL Express (create the database if needed) and ensure schema `sync_metadata`.
6. Start a row in `sync_metadata.runs` (`access_sql`).
7. Provision tables, keys, indexes, foreign keys, check constraints, and views. Every mirrored table gains `_sync_row_hash`, `_sync_is_deleted`, `_sync_last_seen_at`, `_sync_updated_at`.
8. Unless the mode is `schema-only`, upsert Access rows in batches. Skip tables with no detectable primary key. Match on the primary key (composite keys included). Insert or update only when the SHA256 row hash changed. Then soft-delete SQL Express rows not seen in this run, unless soft delete or dry-run is off.
9. Finish the run row as `success` or `failed`.

A failure during schema discovery, before `StartRun`, writes no run row. A failure after `StartRun` records `failed` and returns exit **1** (configuration failures inside the leg return **2**).

### WF-3 — SQL Express to MySQL (`mysql` or `all` when the leg is enabled)

**Implemented** in `RunSqlExpressToMySql` and `MySqlProductSync`.

1. Open SQL Express and MySQL.
2. Unless dry-run, require tables `shop_products` and `shop_sync_log`, and column `shop_products.sqlexpress_product_id`. Missing table or column is exit **2**.
3. Read `tblProduct` rows with `_sync_is_deleted = 0`, left-joined to subcategory, category, item, sex, and location.
4. Map fields (`ProductFieldMapper`). Skip a row with no `ProductID` (counted as skipped).
5. Upsert by unique `code` in multi-row chunks of 500. Images are written on insert only.
6. Count `tblProduct` rows with `_sync_is_deleted = 1` (`products_withdrawn`). Do not update those MySQL rows.
7. Insert `shop_sync_log`. On failure of that insert, POST the same payload if both API settings are set. If both writes fail, append JSONL under `logs/shop-sync-log-failed-YYYYMMDD.jsonl`.

### WF-4 — Full run (`all`)

**Implemented.** Leg 1 runs, then Leg 2 runs even when Leg 1 returned a non-zero code. The process exit code is the maximum of the two leg codes. **Documented** in `docs/sync/sync-architecture.md`: after an Access failure, Leg 2 can still publish stale SQL Express data.

### WF-5 — Deploy and schedule

**Documented** only. Ship `SyncEngine.exe`, `.env`, `config\`, `App.config`, and a writable `logs\`. Schedule `SyncEngine.exe all` with "Start in" set to the exe directory. Overlapping triggers exit 3. This pass did not confirm a live scheduled task.

## Functional requirements

### FR-001 — Four CLI modes

The process accepts `all`, `access`, `mysql`, and `schema-only`. No argument means `all`. Any other token is a configuration error.

| | |
|---|---|
| Trigger | Process start |
| Inputs | First argument, feature switches |
| Behaviour | See WF-1 to WF-4. Unknown mode is rejected by `SyncOptionsValidator` before the orchestrator runs |
| Output | Exit 0, 1, 2, or 3 |
| Evidence | `Program.cs`, `SyncOptionsValidator`, `SyncOrchestrator.Run` |
| Implementation | Implemented |
| Confidence | High |

`SyncOrchestrator` also contains a branch that would return exit 1 for an unknown mode. That branch is not reached when `Program` validates first. **Implemented** exit for a bad mode, through `Program`, is 2.

### FR-002 — Single instance per machine

A second `SyncEngine.exe` on the same machine exits 3 immediately, for every mode, with no database connection.

| | |
|---|---|
| Evidence | `SyncInstanceLock.DefaultMutexName`, `Program.cs`, `SyncInstanceLockTests` |
| Implementation | Implemented. `UnauthorizedAccessException` on the mutex also returns null and therefore exit 3. Docs describe the already-running case and do not mention access denied |
| Confidence | High |

The lock is per machine, not per database. Two servers are not coordinated. **Documented** in getting-started. There is no `.env` switch to disable it.

### FR-003 — Configuration file and placeholder rejection

A run that needs configuration exits 2 when `.env` is missing, when a required value is missing, or when a checked string contains `c:\path`, `/path/`, `your_`, `yoursite.com`, `changeme`, `<`, or `>`.

Access leg (when that leg will run): `ACCESS_DB_PATH` must be set, not a placeholder, and must be an existing file. `SQL_EXPRESS_DATABASE`, connection string, and schema are required.

MySQL leg (when that leg will run): SQL Express connection string, `MYSQL_HOST`, `MYSQL_USER`, `MYSQL_DATABASE`, and a port from 1 to 65535. `MYSQL_PASSWORD` may be empty. The validator does not require it.

`SHOP_SYNC_API_URL` and `SHOP_SYNC_API_KEY` must both be set or both be empty. A set URL must be absolute `http` or `https` and must not look like a placeholder.

At least one of `SYNC_ACCESS_TO_SQL_EXPRESS` and `SYNC_SQL_EXPRESS_TO_MYSQL` must be true. `SYNC_BATCH_SIZE` must be > 0. `SYNC_MAX_RETRIES` must be ≥ 0.

| | |
|---|---|
| Evidence | `SyncOptionsValidator`, `SyncOptionsValidatorTests`, `docs/sync/configuration.md` |
| Implementation | Implemented |
| Confidence | High |

### FR-004 — Feature switches can disable a leg

`SYNC_ACCESS_TO_SQL_EXPRESS=false` skips Leg 1 even in `all`, `access`, and `schema-only`. `SYNC_SQL_EXPRESS_TO_MYSQL=false` skips Leg 2 even in `all` and `mysql`. The skip is an information log, not an error, as long as the other leg is enabled.

**Documented** operating mode when SQL Express is the live application database: disable Leg 1 and schedule `mysql` only, so a stale `.mdb` cannot overwrite SQL Express. That is an operator instruction in `docs/sync/configuration.md`, not a separate code path.

| | |
|---|---|
| Evidence | `SyncOrchestrator.Run`, configuration.md |
| Implementation | Implemented |
| Confidence | High |

### FR-005 — SQL Express database is created when missing

Opening SQL Express connects to `master`. If `DB_ID` of the target name is null, the process executes `CREATE DATABASE`. It then changes to that database.

| | |
|---|---|
| Evidence | `SqlServerConnectionFactory.Open`, `EnsureDatabaseExists` |
| Implementation | Implemented |
| Confidence | High |

`SqlSchemaProvisioner.EnsureDatabase` only calls `SyncMetadataRepository.EnsureSchema`. Database creation is the factory, not that method. Getting-started describes the outcome correctly.

### FR-006 — Schema mirror includes sync columns and skips excluded tables

Mirrored user tables receive the four `_sync_*` columns. Tables named in `ACCESS_SQL_EXCLUDE_TABLES` are omitted from table sync and from FK and index provisioning. The example list is `Switchboard Items,adn,adni,tblDeliveryNoteItemORIG,UsysDN,UsysIN`. An empty variable means no extra exclusions (system tables are still skipped).

Tables with no detectable primary key are skipped at data sync with a warning. They can still be created as tables during schema provision. `config/table-overrides.json` does not supply a key. **Documented** as unimplemented. **Implemented** behaviour is skip.

| | |
|---|---|
| Evidence | `TableExcludeFilter`, `SqlDataSync`, `docs/sync/configuration.md`, `docs/sync/access-sql-express-compatibility.md` |
| Implementation | Implemented, except table-overrides |
| Confidence | High |

### FR-007 — Data sync is an idempotent upsert with soft delete

For each keyed table, in dependency order:

- Read Access in pages ordered by the primary key (`SYNC_BATCH_SIZE`, code default 5000, example file 2000).
- Insert when the key is absent (`IDENTITY_INSERT` when the table has an AutoNumber column). Update when `_sync_row_hash` differs.
- Mark every Access row in the batch as seen, including rows whose hash did not change.
- After the table, if `SYNC_SOFT_DELETE_ENABLED` and not dry-run, set `_sync_is_deleted = 1` on rows not seen in this run.
- Checkpoints in `sync_metadata.checkpoints` are stored when a table finishes, for that run only. The next run reads each table from the start.

| | |
|---|---|
| Evidence | `docs/sync/sync-architecture.md`, `SqlDataSync`, `SoftDeleteTracker`, `ChangeDetector` |
| Implementation | Implemented |
| Confidence | High for the rules above. Batch SQL was not re-executed here |

### FR-008 — Unique secondary-key collisions are classified

When an insert hits SQL Server 2601 or 2627, the row is classified (`UniqueKeyCollisionClassifier.SimilarityThreshold` is 0.90):

| Situation | Action |
|---|---|
| Occupying primary key still in Access with the same unique value | Fail (true duplicate) |
| Occupying primary key still in Access with a different unique value | Treat occupying SQL row as stale |
| Occupying primary key gone from Access and descriptions similar (≥ 0.90) | Remap: rename occupying code to `{code}_removed` (truncated to the column), retarget `refProductID` children, retry the insert |
| Occupying primary key gone and descriptions differ | Recycle: rename occupying code only |

`{code}_removed` stays on the old product id and is soft-deleted later, so Leg 2 does not publish it as an active product. The new Access product keeps the original code and is what Leg 2 upserts.

| | |
|---|---|
| Evidence | `UniqueKeyCollisionClassifier`, `UniqueKeyCollisionResolver`, `UniqueKeyCollisionClassifierTests`, architecture doc section "Unique strCode collisions" |
| Implementation | Implemented |
| Confidence | High for classification. Resolver SQL was not executed against a database in this pass |

### FR-009 — MySQL receives active products only, matched on code

The shop upsert key is `code` (documented unique index `uk_shop_products_code`). `id` is never written. `sqlexpress_product_id` stores Access/SQL Express `ProductID`.

Synced columns on insert and on duplicate-key update: `sqlexpress_product_id`, `description`, `category`, `sub_category`, `item`, `gender`, `era`, `barcode`, `price`, `size`, `slug`, `is_visible`. `synced_at` changes only when a synced field changed (NULL-safe comparison in the SQL).

| | |
|---|---|
| Evidence | `ShopProductSql.UpdateColumns`, `docs/sync/shop_products.md` |
| Implementation | Implemented in the SQL builder |
| Confidence | High |

Rows with `_sync_is_deleted = 1` are excluded from the product query and counted by `CountWithdrawnOnSource`. They are not hidden or deleted in MySQL. **Documented** and **implemented**.

### FR-010 — Camera Ready owns shop visibility

`tblProduct.blnCameraReady` maps to `shop_products.is_visible`. Null, false, or 0 becomes false. Any other non-zero value, including Access Yes stored as -1, becomes true. Unchecking Camera Ready still upserts the row with `is_visible = 0`. A soft-deleted product is not updated, so its last MySQL visibility remains.

| | |
|---|---|
| Evidence | `ProductFieldMapper.ToBool`, `MySqlProductSync` select list, `shop_products.md` |
| Implementation | Implemented |
| Confidence | High |

### FR-011 — Web-owned columns and images are not overwritten

`is_featured`, `featured_sort`, `web_copy`, and `web_notes` are not in the insert column list or the update list.

`image_url_1` is set on insert from the configured image column (default `strImagePath`). `image_url_2` and `image_url_3` are inserted as null. None of the three image columns is in `UpdateColumns`, so a later sync does not replace values already stored in MySQL.

| | |
|---|---|
| Evidence | `ShopProductSql`, commit `517f5a6`, `shop_products.md` |
| Implementation | Implemented |
| Confidence | High |

### FR-012 — Slug, price, and string limits

Slug is `{productId}-{urlSafeCode}-{urlSafeDescription}`. `UrlTitle` lowercases, strips diacritics, turns whitespace into hyphens, and keeps only `a-z`, digits, `_`, and `-`. An empty code yields a double hyphen after the id. Asterisks are removed from the code before slugging.

Price comes from `dblDailyRate`, rounded to 2 decimal places, away from zero. Optional strings that are only whitespace become null. Description is trimmed and may be empty.

Truncation maxima used by `ProductFieldMapper.Truncate`: code 32, category / sub_category / item / era 128, gender 32, barcode 64, size 64, slug 255, image URL 512.

| | |
|---|---|
| Evidence | `ProductFieldMapper`, `ProductFieldMapperTests`, `shop_products.md` |
| Implementation | Implemented |
| Confidence | High |

### FR-013 — Product map file only partly applies

`config/product-sync-map.json` is loaded when present. `barcodeColumn`, `imageUrl1Column`, and `productIdColumn` are used (defaults `strCode`, `strImagePath`, `ProductID`). `productTable` and `lookupJoins` are deserialized and not used. The query hardcodes `tblProduct` and the five lookup tables.

| | |
|---|---|
| Evidence | `ProductSyncMap`, `docs/sync/configuration.md`, `MySqlProductSync` |
| Implementation | Implemented as documented (partial use) |
| Confidence | High |

### FR-014 — Dry run skips writes except the shop log

`SYNC_DRY_RUN=true` skips SQL Express DDL and data writes, skips soft delete, skips MySQL table checks, and counts MySQL upserts without writing them. `shop_sync_log` is still written. **Documented** in configuration.md. The orchestrator calls the log writer after `MySqlProductSync.Sync` without a dry-run guard.

| | |
|---|---|
| Evidence | `SoftDeleteTracker`, `SqlSchemaProvisioner`, `SqlDataSync`, `ShopSchemaValidator`, `MySqlProductSync`, `SyncOrchestrator` |
| Implementation | Implemented |
| Confidence | High that the guards exist. Not executed |

### FR-015 — Shop sync log fallback

Direct MySQL insert is attempted first. If it fails and both API settings are present, the client POSTs JSON:

`status`, `source_reachable` (1 or 0), `products_upserted`, `products_withdrawn`, `run_started_at`, `run_finished_at`, `message`.

Header: `X-Shop-Sync-Key`. Timeout: 30 seconds. Non-success HTTP status throws. Any Leg 2 exception sets `source_reachable` to 0, including failures that are not a SQL Express outage. **Documented** in the architecture doc. **Implemented** in the catch block.

| | |
|---|---|
| Evidence | `ShopSyncLogApiClient`, `SyncOrchestrator` catch, `ShopSyncLogApiClientTests` |
| Implementation | Implemented |
| Confidence | High |

### FR-016 — Retries

`BatchProcessor` uses Polly. Max attempts `SYNC_MAX_RETRIES` (default 3), exponential backoff `2^attempt` seconds.

Leg 1 retries SQL Express batch upserts on the same connection. Access reads are not retried. A dropped SQL connection is not reopened mid-run.

Leg 2 retries a failed MySQL chunk and can open a new connection when `MySqlConnectionFailures` classifies the error as reconnectable. Other MySQL errors fail immediately.

| | |
|---|---|
| Evidence | `docs/sync/sync-architecture.md` retries section, `MySqlConnectionFailuresTests` |
| Implementation | Implemented |
| Confidence | High for the policy as documented. Polly call sites were not line-audited beyond the architecture doc and the failure-classifier tests |

### FR-017 — Views that cannot be translated are skipped

Access queries using TRANSFORM/CROSSTAB, or queries whose SQL cannot be translated, are skipped. An operator can supply SQL in `config/access-views.json` (example file is committed; the live file is not).

| | |
|---|---|
| Evidence | `SqlViewTranslator`, `AccessViewConfigLoader`, `SqlViewTranslatorTests`, getting-started |
| Implementation | Implemented |
| Confidence | High |

### FR-018 — Exit codes

| Code | Meaning | Evidence |
|---|---|---|
| 0 | Legs that ran completed without error | `Program`, orchestrator returns |
| 1 | Runtime/sync failure or unhandled exception | `Program` catch, leg catch |
| 2 | Configuration error | Missing `.env`, `ConfigurationException` |
| 3 | Mutex not acquired | `Program` |

**Implemented.** Confidence high.

## Important constraints

- Windows, and ACE bitness equal to the build. Default build is x64. `SYNC_PLATFORM_TARGET` does not change the running binary.
- Access database password (`ACCESS_DB_PASSWORD`) is the Jet encrypt password. It is not the MSACCESS `/pwd` workgroup password (`ACCESS_WORKGROUP_PASSWORD`). **Documented** in `.env.example` and getting-started, with a pointer to an old Microsoft KB.
- Access Runtime cannot `CreateInstance` `Access.Application` (documented HRESULT `0x80080005`). The engine then uses `DAO.DBEngine.OpenDatabase`. MSys GRANT is skipped on that path. Data sync still uses OleDb.
- MySQL tables are not created here. A missing table is exit 2, not an attempt to migrate.
- Credentials live in `.env`, which is gitignored. The build copies repo-root `.env` into the output directory when the file exists.
- `docs/password.txt` is gitignored. It is not in the tree.

## Assumptions and open points

- Column types and indexes printed in `docs/sync/shop_products.md` describe the expected CI4 table. They are **Documented** in this repo and **not implemented** here. Confirm against the shop repository before treating them as the live contract.
- AnniesApp behaviour in `docs/anniesapp/` (linked tables, `UsysSqlConfig`, delivery-note form parity) is **Documented** beside the sync engine. The VBA and the cited `scripts/Resolve-SqlOdbcDriver.ps1` are not in this repository. Confirm which repository is the source of truth for AnniesApp.
- Whether Leg 2 should hide withdrawn products in the shop is not an open code path. The code deliberately does not. Whether that is still the business rule is **Requires Confirmation** if the shop shows products that Access has removed.
- No product owner, SLA, or schedule interval is written down.