# Project plan — sync_coordinator

Evidence date: 2026-10-01.

Source repository: `git@bitbucket.org:overdrive/sync_coordinator.git`.

Inspected revision: `main` at `0e109de` (2026-08-17, joshgatdew, "cleanup docs"). The scan-host path `~/projects/sonar_scan/repos/sync_coordinator` was not on this machine. The tree below was read from that git revision. This pass did not run `SyncEngine.exe` against Access, SQL Express, or MySQL.

This plan describes what the repository contains. It does not decide what the product should become.

There was no prior Silicon Ark directory for this project. The source repository already has a README and two documentation trees (`docs/sync/`, `docs/anniesapp/`). Those files are evidence. Where they disagree with code, the disagreement is recorded below and in the other Silicon Ark documents.

## Product

**Documented** in `README.md`: a one-way sync coordinator, Microsoft Access → SQL Server Express → MySQL table `shop_products`.

**Documented** in `docs/anniesapp/index.md`: the Access front-end is AnniesApp, "Microsoft Access front-end for Annie's Angels." Default database names in `config/.env.example` are `AnniesAngels` (SQL Express) and `annies_angels_shop` (MySQL). The example Access file name is `New_Annies_Angels_be.mdb`.

**Inferred** from those names, from product columns (`dblDailyRate`, `blnCameraReady`, `strCode`), and from excluded tables such as delivery-note leftovers: the business is the Annie's Angels hire catalogue, and the MySQL leg publishes an active product catalogue for a CodeIgniter 4 web shop. The CI4 shop and the AnniesApp `.mdb` front-end are not source in this repository.

**Implemented** is a Windows console executable, `SyncEngine.exe` (.NET Framework 4.8), scheduled by the operator (documented: Windows Task Scheduler). It does not expose an HTTP API of its own. The only outbound HTTP call is an optional fallback that POSTs a failed `shop_sync_log` row.

## Branch reality

| Line | Tip | Date | What it is |
|---|---|---|---|
| `main` (default; `origin/HEAD`) | `0e109de` | 2026-08-17 | Current tree |
| `josh_work` | `0e109de` | 2026-08-17 | Same commit as `main` |

Nothing in git is dated after 2026-08-17. Whether a scheduled sync is running on a server is **Unknown / Requires Confirmation**.

| Commit | Date | Author | Subject |
|---|---|---|---|
| `b71081a` | 2026-06-09 | Jaco Brits | Initial commit |
| `14616ce` | 2026-06-09 | joshgatdew | Initial code |
| `c40b663` | 2026-06-18 | joshgatdew | Initial sync engine code |
| `f79676b` | 2026-06-18 | joshgatdew | Remove file |
| `a8df371` | 2026-06-18 | joshgatdew | Performance improvements |
| `a697f33` | 2026-06-18 | joshgatdew | Prevent multiple SyncEngine processes running concurrently |
| `ba43ca4` | 2026-06-22 | joshgatdew | Make slug SEO and url safe |
| `b7e6338` | 2026-06-22 | joshgatdew | Add retries and better logging |
| `4fe50dd` | 2026-06-23 | joshgatdew | Improve sync to better mirror the access db into sqlexpress |
| `1c57ef3` | 2026-06-24 | joshgatdew | Improve data sync awareness of compound keys and primary keys |
| `da90db1` | 2026-06-25 | joshgatdew | Improve sync |
| `a5773a1` | 2026-07-20 | joshgatdew | Workaround metadata not syncing on redistributable only access installs |
| `b1f3d57` | 2026-07-20 | joshgatdew | More debugging |
| `8d227b6` | 2026-07-21 | joshgatdew | mysql connection fix and improve logging |
| `517f5a6` | 2026-07-29 | joshgatdew | Stop sync overwriting images, improve mysql syn speed |
| `506a8c6` | 2026-08-14 | joshgatdew | Introduce better collision handeling |
| `0e109de` | 2026-08-17 | joshgatdew | cleanup docs |

Git author email on the joshgatdew commits is `joshua@overdrive.co.za`. Fifteen commits are by joshgatdew. One commit is by Jaco Brits.

`docs/sync/index.md` says branch naming is `feature/TASK-XXX-<slug>` and commit format is `[TASK-XXX] <verb> <object>`. No commit subject or branch name in this history matches that pattern. **Discrepancy:** documented convention versus git history.

## Objectives

No project-plan or issue tracker exists in the source repository. The behaviour the commits introduced, checked against the files they changed:

1. `14616ce` / `c40b663` — .NET Framework 4.8 solution `SyncEngine.sln`: console project `SyncEngine` and test project `SyncEngine.Tests`. Access → SQL Express mirror, then SQL Express → MySQL `shop_products`.
2. `f79676b` — Removed `New_Annies_Angels_be.mdb` from git (about 78 MB) and ignore it. The Access database is local-only after this commit.
3. `a697f33` — One `SyncEngine.exe` per machine, mutex `Global\SyncCoordinator.SyncEngine`, exit code 3.
4. `ba43ca4` — Product slug made URL-safe (`ProductFieldMapper.UrlTitle`).
5. `b7e6338` — Polly retries and richer failure logging.
6. `4fe50dd` through `1c57ef3` — Schema mirroring (keys, defaults, checks, views) and composite primary keys for data sync.
7. `a5773a1` — Access Runtime (redistributable-only) path for DAO metadata when `Access.Application` cannot be created.
8. `8d227b6` — MySQL connection string pins `TlsVersion=Tls12` (documented for SBS 2011 / Server 2008 R2).
9. `517f5a6` — `image_url_1`, `image_url_2`, and `image_url_3` are insert-only. Leg 2 uses multi-row `INSERT ... ON DUPLICATE KEY UPDATE` in chunks of 500.
10. `506a8c6` — Unique secondary-key collisions (SQL errors 2601 and 2627), including `tblProduct.strCode`, classified and resolved instead of failing the batch.
11. `0e109de` — Moved sync guides from `docs/*.md` to `docs/sync/` and AnniesApp notes to `docs/anniesapp/`. `README.md` still links the old `docs/*.md` paths. **Discrepancy.**

## Scope

### In scope of `main` at `0e109de`

- Solution `SyncEngine.sln` (Visual Studio): projects `SyncEngine` and `SyncEngine.Tests`.
- CLI modes `all` (default), `access`, `mysql`, `schema-only`.
- Leg 1: read an Access `.mdb` through ACE OLEDB 12.0, enrich schema through DAO/COM when possible, create or alter a SQL Server Express database, mirror tables, keys, defaults, foreign keys, check constraints, and translated views, then upsert rows and soft-delete rows missing from Access.
- Leg 2: read active `tblProduct` rows from SQL Express (with category lookups) and upsert `shop_products` in MySQL. Count withdrawn products. Write `shop_sync_log`, with an optional HTTP fallback.
- Configuration via repo-root `.env` (copied to the build output) and `config/`.
- MSTest unit tests for translators, validators, collision classification, slug/price mapping, MySQL TLS connection string, and log clients. Tests do not open a real Access file or a real server.
- Operator documentation under `docs/sync/` and notes about the AnniesApp front-end under `docs/anniesapp/`.
- `scripts/Test-AccessRuntimeCom.ps1` — interactive check that DAO can open the backend.

### Out of scope of this tree

- The AnniesApp front-end (forms, VBA module `mdSqlExpress`, `UsysSqlConfig` maintenance). Described in `docs/anniesapp/`. `scripts/Resolve-SqlOdbcDriver.ps1`, cited there, is not in this repository.
- The CodeIgniter 4 shop, its migrations for `shop_products` / `shop_sync_log`, and the route `POST /api/shop-sync/log`. This repo only calls that route.
- Creating MySQL tables. Leg 2 validates that they already exist.
- Writing anything back to Access.
- Hiding or deleting a MySQL product when Access soft-deletes it. Leg 2 counts those rows and leaves the MySQL row unchanged.
- A pipeline file, Docker image, Sonar project file, or hosted environment definition. The task-creation scan listed `sonar-project.properties` at the repo root. That file is not in git at `0e109de`, and `git log -- sonar-project.properties` is empty.
- `config/table-overrides.json`. The file is committed (empty `primaryKeys`) and is not read by any `.cs` file. `docs/sync/configuration.md` already says so.

## System architecture

```text
Access .mdb  --ACE OLEDB / DAO-->  SQL Server Express  --product query-->  MySQL shop_products
                                      sync_metadata.*                         shop_sync_log
                                                                              optional POST /api/shop-sync/log
```

Sync is one-way. `SyncOrchestrator` runs Leg 1 and then Leg 2 when mode and feature switches allow it. In `all` mode, a Leg 1 failure does not skip Leg 2. Leg 2 then publishes whatever is already in SQL Express.

Major components (namespaces under `SyncEngine/`):

| Area | Role |
|---|---|
| `Program` | Load `.env`, Serilog, single-instance mutex, validate, run |
| `Configuration` | `SyncOptions`, `SyncOptionsValidator`, table exclude list |
| `Access` | Schema, DAO enrichment, type/default/validation translation, view SQL fallback |
| `SqlServer` | Create database, DDL, data upsert, identity, view translation, unique-key collision handling |
| `Sync` | Orchestrator, batch retry, change hash, soft delete, instance lock, error text |
| `Metadata` | `sync_metadata.runs`, `checkpoints`, `schema_versions` |
| `MySql` | Product query, field mapping, upsert SQL, schema check, sync log, API fallback |
| `Logging` | `SyncStatistics` summaries |

## Milestones

There is no milestone plan in the repository. The history above is the implementation record. The latest change (`0e109de`) reorganised documentation. It did not add a sync behaviour.

## Known dependencies

| Dependency | Where stated | Role |
|---|---|---|
| Windows | `docs/sync/getting-started.md` | ACE OLEDB and Access COM |
| .NET Framework 4.8 | `SyncEngine.csproj` `TargetFramework` `net48` | Runtime |
| .NET SDK | getting-started | Build (`dotnet build`) |
| ACE OLEDB 12.0, x64 by default | csproj `PlatformTarget`, getting-started | Read the `.mdb` |
| Access or Access Runtime | getting-started | DAO schema enrichment only. Data sync uses OleDb |
| SQL Server Express | `.env.example` default `.\SQLEXPRESS` | Mirror and Leg 2 source |
| MySQL 5.7+ or 8.x | getting-started | Shop catalogue |
| CI4 shop migrations | `docs/sync/shop_products.md`, `ShopSchemaValidator` | Must already have created `shop_products` and `shop_sync_log` |
| NuGet: Microsoft.Data.SqlClient 5.2.2, MySqlConnector 2.4.0, Newtonsoft.Json 13.0.3, Polly 7.2.4, Serilog 4.2.0 plus console and file sinks | `SyncEngine.csproj` | Libraries |
| MSTest 3.6.4, Microsoft.NET.Test.Sdk 17.12.0 | `SyncEngine.Tests.csproj` | Unit tests |

## Current status

**Implemented** and documented in-repo as an operator-run Windows sync, last changed 2026-08-17. `main` and `josh_work` are the same commit.

**Unknown / Requires Confirmation:**

- Whether Task Scheduler (or any other host) still runs `SyncEngine.exe`, and on which machine.
- The live Access path, SQL Express instance, and MySQL host. Only placeholders and example names are in git. `.env` is gitignored.
- Whether the live `shop_products` table matches `docs/sync/shop_products.md`. The migrations are not in this repo.
- Whether AnniesApp on the shop floor reads the same SQL Express database this engine writes.
- Whether `SHOP_SYNC_API_URL` is configured anywhere. It is optional and commented out in `.env.example`.
- Ownership and whether further development is expected. No open branch diverges from `main`.

## Discrepancies to keep in view

- README links `docs/getting-started.md` (and the other guides) at `docs/`. After `0e109de` those files live under `docs/sync/`.
- `docs/sync/index.md` branch and commit conventions do not match git history.
- `.env.example` sets `SYNC_BATCH_SIZE=2000`. If the variable is absent, `SyncOptions` uses 5000. `docs/sync/configuration.md` documents 5000 as the code default.
- `SYNC_PLATFORM_TARGET` is loaded and never read. Bitness is the csproj `PlatformTarget` (`x64`).
- AnniesApp docs describe VBA and a PowerShell driver script that are not in this repository.
- `sonar-project.properties` was listed at task creation and is not in the git tree.