# Business rules — sync_coordinator Evidence date: 2026-10-01. Source: `git@bitbucket.org:overdrive/sync_coordinator.git`, `main` at `0e109de` (2026-08-17). Rules below are taken from code or from the source docs that describe that code. Each rule is marked **Documented**, **Implemented**, or **Inferred**. A rule that appears only in a doc and not in code is not treated as enforced. No separate business-rules document existed in the source repository. Domain language (Annie's Angels, Camera Ready, daily rate, delivery notes) comes from configuration examples and `docs/anniesapp/`. ## Direction of data ### BR-001 — Sync does not write to Access **Documented** in `README.md` and `docs/sync/sync-architecture.md`: one-way Access → SQL Express → MySQL. **Implemented.** No Access update or insert was found in the sync write path. SQL Express and MySQL are the write targets. Confidence: high. ### BR-002 — Access is the source of truth for the mirror when Leg 1 is enabled **Inferred** from the upsert and soft-delete design: after a successful Leg 1, SQL Express rows match Access rows that have a primary key, and rows missing from Access are flagged `_sync_is_deleted`. **Documented** exception in `docs/sync/configuration.md`: if SQL Express is the live application database, the operator must set `SYNC_ACCESS_TO_SQL_EXPRESS=false` and must not run `access` or `all`, so a stale `.mdb` cannot overwrite it. That is an operator rule. The code will overwrite SQL Express whenever the Access leg is enabled. Confidence: high for the code. Whether production still runs Leg 1 is **Unknown / Requires Confirmation**. ### BR-003 — `all` still publishes MySQL after Leg 1 fails **Documented** and **Implemented** in `SyncOrchestrator.Run`. Leg 2 is not skipped because Leg 1 returned 1 or 2. The shop can receive a partial or stale catalogue. Confidence: high. ## Who may run it ### BR-004 — There is no application login **Implemented.** No user table, password check, or role exists for the executable. The Windows account that starts the process is the actor. SQL Express uses the connection string (example: Windows trusted connection). MySQL uses `MYSQL_USER` / `MYSQL_PASSWORD`. Confidence: high. ### BR-005 — One sync process per machine **Documented** and **Implemented.** A second process exits 3 and does not connect. The guard does not coordinate two machines. There is no setting to turn it off. **Implemented** extra case: if creating the mutex throws `UnauthorizedAccessException`, the process also exits 3. The docs describe only the already-running case. Confidence: high. ## What is mirrored ### BR-006 — System and named tables are left out of the mirror **Implemented.** Names starting with `MSys` or `~TMPCLP` are system tables. `ACCESS_SQL_EXCLUDE_TABLES` removes further names from table sync, foreign keys, and indexes. **Documented** example exclusion list: `Switchboard Items`, `adn`, `adni`, `tblDeliveryNoteItemORIG`, `UsysDN`, `UsysIN`. That list is the example file's value, not a hard-coded list. An operator who deletes the variable changes the set. Code default when the variable is absent is an empty exclude list (`EnvSettings.Get(..., "")`). Confidence: high. ### BR-007 — A table without a primary key is not data-synced **Documented** and **Implemented.** Schema provision can still create the table. Data sync skips it with a warning. `config/table-overrides.json` does not override this. **Documented** as not implemented. Confidence: high. ### BR-008 — Primary key values from Access are kept **Documented** in `shop_products.md`: AutoNumber `ProductID` is preserved with `IDENTITY_INSERT`, and `sqlexpress_product_id` is that value, not the MySQL `id`. **Implemented** in the Leg 1 identity path and in the Leg 2 column mapping. Confidence: high. ### BR-009 — Untranslatable Access constraints and queries are skipped, not invented **Documented** in getting-started and the compatibility guide: defaults the translator does not understand are skipped; NOT NULL is not applied when existing rows contain NULL; TRANSFORM/CROSSTAB queries are not created as views. The operator can supply view SQL in `access-views.json`. **Implemented** in the translators and provisioner (skip counters `*Skipped`). The full expression grammar is in `docs/sync/access-sql-express-compatibility.md` and the translator classes. This pass did not re-derive every Access function mapping. Confidence: high for the skip policy. Medium for any single Access expression not opened in this pass. ## Change detection and deletes ### BR-010 — Unchanged rows are not rewritten **Implemented.** Leg 1 updates a SQL Express row when `_sync_row_hash` (SHA256 of normalised column values) changes. A matching hash still marks the row seen, so it is not soft-deleted. **Implemented** on MySQL: `synced_at` is left unchanged when every synced column is NULL-safe equal. Confidence: high. ### BR-011 — A row missing from Access is soft-deleted on SQL Express, not removed **Documented** and **Implemented** when `SYNC_SOFT_DELETE_ENABLED` is true (default) and dry-run is false. The row stays. `_sync_is_deleted` becomes 1. Soft delete is skipped for a table whose upsert failed, and later tables in that run are not processed. Confidence: high. ### BR-012 — A withdrawn product is not hidden in the shop **Documented** and **Implemented.** Leg 2 does not select `_sync_is_deleted = 1` rows for upsert. It counts them (`SELECT COUNT(*)` on `tblProduct` where `_sync_is_deleted = 1`) into `products_withdrawn`. The MySQL row is left as it was, including `is_visible`. **Inferred** product effect: the web shop can keep showing a product that Access no longer has, until something else changes that MySQL row. Whether that is acceptable is **Requires Confirmation**. The code is explicit. Confidence: high for the code. The business intent is the documented behaviour, not a separate approval record. ### BR-013 — Camera Ready is the shop visibility flag **Documented** and **Implemented.** `blnCameraReady` is the source for `is_visible` on every upsert of an active product. Null or 0 means not visible. Access Yes (−1) means visible. This column is not one of the web-only fields. Clearing Camera Ready updates the shop row. Deleting the Access product does not (BR-012). Confidence: high. ## Identity of a product in the shop ### BR-014 — Shop identity for upsert is `code`, not MySQL `id` or ProductID **Documented** and **Implemented.** `INSERT ... ON DUPLICATE KEY UPDATE` matches `code`. `ProductID` is stored in `sqlexpress_product_id` and is updated when the code already exists. MySQL `id` is assigned by the database and never set by sync. **Inferred** from the collision design: if Access retires a product and reuses `strCode` on a new `ProductID`, the shop row for that code is updated to the new `sqlexpress_product_id` after Leg 1 remaps the old code to `{code}_removed`. Confidence: high. ### BR-015 — Reused unique codes are remapped when the old product is gone and the description is similar **Implemented.** Similarity threshold is 0.90 (`UniqueKeyCollisionClassifier.SimilarityThreshold`). Similar descriptions: rename the occupying SQL Express code to `{code}_removed` (or a shortened form if the column is shorter), retarget child `refProductID`, retry the insert. Dissimilar descriptions: rename only (recycle). If the occupying product is still in Access with the same code, the sync fails that collision as a true duplicate. The suffix `_removed` is a fixed constant, not configuration. Confidence: high for the classifier. Resolver effects are implemented in `UniqueKeyCollisionResolver` and were not executed against SQL Express in this pass. ### BR-016 — Slug is derived and replaced on every upsert **Implemented.** Slug is always rebuilt from product id, code, and description (`MakeSlug` / `UrlTitle`). It is in the MySQL update column list, so an existing shop slug is overwritten. Characters outside URL-safe letters, digits, `_`, and `-` are removed. Asterisks in the code are stripped before slugging. **Documented** in `shop_products.md` and commit `ba43ca4`. Confidence: high. ## What the shop is allowed to keep ### BR-017 — Featured flags, web copy, and web notes belong to the shop **Documented** and **Implemented.** `is_featured`, `featured_sort`, `web_copy`, and `web_notes` are not written by sync. Confidence: high. ### BR-018 — Image URLs already in MySQL are kept **Documented** and **Implemented** since `517f5a6`. `image_url_1` is filled from Access only on insert (default column `strImagePath`). `image_url_2` and `image_url_3` are inserted as null and never updated. A later Access image path does not replace `image_url_1`. Confidence: high. ### BR-019 — Price published to the shop is the daily rate, rounded to two decimals **Documented** and **Implemented.** Source column `dblDailyRate`. `ProductFieldMapper.ToPrice` uses midpoint rounding away from zero. The product query also rounds. Empty or unparseable values become null. **Inferred** name: "daily rate" is a hire price. The code does not apply tax, currency, or a second price field. Confidence: high for the mapping. Business meaning of the rate is inferred from the column name. ### BR-020 — Lookup labels come from fixed tables **Implemented.** Category, sub-category, item, gender, and era are `strDescription` from `tblCategory`, `tblSubCategory`, `tblItem`, `tblSex`, and `tblLocation`, joined from `tblProduct` foreign keys. `product-sync-map.json` `lookupJoins` does not change this. Missing lookups become null (optional-string normalisation), not empty strings. Confidence: high. ### BR-021 — String fields are cut to the shop column sizes **Documented** and **Implemented** via `Truncate` before bind. Limits: code 32; category, sub-category, item, and era 128; gender 32; barcode 64; size 64; slug 255; image URL 512. Description is not in that truncation table (shop type is documented as `TEXT`). Confidence: high for the mapper limits. The live MySQL column sizes were not inspected. ## Catalogue membership ### BR-022 — Only non-deleted `tblProduct` rows are candidates **Implemented.** The Leg 2 query filters `ISNULL(p._sync_is_deleted, 0) = 0`. Other Access tables are not published to MySQL. A row with a null `ProductID` is skipped with a warning. Confidence: high. ### BR-023 — MySQL catalogue tables must already exist **Documented** and **Implemented.** SyncEngine does not create `shop_products` or `shop_sync_log`. Absence is a configuration error (exit 2) except in dry-run, which skips the check. The message tells the operator to run CI4 shop migrations. The column `sqlexpress_product_id` must exist. Confidence: high. ## Safety switches ### BR-024 — Dry run does not write the mirrors, and still writes the shop log **Documented** in `docs/sync/configuration.md`. **Implemented** as skip flags on DDL, upserts, soft delete, and MySQL validation. `SyncOrchestrator` still calls the shop log writer after the MySQL leg. Confidence: high. ### BR-025 — Placeholder configuration must not run **Implemented.** Paths and URLs containing the placeholder fragments in `SyncOptionsValidator` fail with exit 2. The example `ACCESS_DB_PATH=c:\path\...` therefore cannot be used as-is. The Access file must exist on disk at start. Confidence: high. ### BR-026 — Database password and workgroup password are different settings **Documented** in `.env.example` and getting-started. `ACCESS_DB_PASSWORD` is the Jet database password. `ACCESS_WORKGROUP_PASSWORD` is only for the MSACCESS `/pwd` switch. The docs say not to put the database password on `/pwd`. **Implemented** as two distinct `SyncOptions` fields. This pass did not re-read every COM call site. Confidence: high that they are separate settings. Medium that every call site honours the distinction; the source docs and comments state the rule, and `a5773a1` exists specifically because Runtime automation was getting it wrong. ## Failure and audit ### BR-027 — A failed batch does not roll back earlier committed batches **Documented** and **Implemented** for both legs: each SQL Express batch and each MySQL chunk is its own transaction. Checkpoints are not a resume cursor for the next process. The next run reads from the start and upserts again. Confidence: high. ### BR-028 — Leg 2 failure is recorded even when the log row itself cannot be written **Implemented.** The orchestrator tries MySQL, then the API, then a local JSONL file. Any Leg 2 failure sets `source_reachable` to 0, including errors that are not "SQL Express is down". **Documented** in the architecture doc. Confidence: high. ### BR-029 — Exit 3 is not a sync failure of the databases **Documented** and **Implemented.** Exit 3 means this process did not start a sync. Exit 1 means a leg failed. Exit 2 means configuration. Exit 0 means the legs that ran finished without throwing. In `all` mode the process exit code is the higher of the two leg codes. A successful MySQL leg does not clear a failed Access leg. Confidence: high. ## Rules that are not enforced | Statement | Where it appears | What the code does | |---|---|---| | Branch names `feature/TASK-XXX-*` and commit format `[TASK-XXX]` | `docs/sync/index.md` | Not a runtime rule. Git history does not follow it | | `SYNC_PLATFORM_TARGET` must match ACE bitness | configuration.md | Loaded and ignored. The built exe's platform is whatever the csproj compiled | | `table-overrides.json` can set primary keys | file exists | Not read | | `productTable` / `lookupJoins` in the map file select the shop query | JSON file | Not read for SQL | | Sync creates MySQL tables | not claimed by current docs | Not implemented. Older readers of the task scan should not assume it | | Withdrawn products disappear from the shop | not claimed | Not implemented. See BR-012 | ## Requires confirmation - Is leaving withdrawn products visible in MySQL still the intended shop rule? - Is Leg 1 still allowed to overwrite SQL Express, or has AnniesApp made SQL Express the source of truth (the configuration doc's warning)? - Are the example exclude-table names still the production exclude list? - Is 0.90 description similarity the accepted rule for treating two codes as the same garment? - Who may see `.env` on the sync server? The repository does not define an access policy beyond "do not commit `.env`".