# Sync Architecture

This document describes how the sync engine works: pipeline overview, CLI routing, per-leg flows, metadata, and edge-case behavior.

## High-level pipeline

```mermaid
flowchart LR
    Access[Access_mdb] -->|"ACE_OLEDB"| SqlExpress[SQL_Express_mirror]
    SqlExpress -->|"tblProduct_joins"| MySQL[MySQL_shop_products]
    SqlExpress --> Meta[sync_metadata_schema]
    MySQL --> Log[shop_sync_log]
```

| Stage | Technology | Scope |
|-------|------------|-------|
| **Access** | ACE OLEDB 12.0 | Source of truth — `.mdb` file |
| **SQL Express** | Microsoft.Data.SqlClient | Full table mirror, FKs, translated views, sync metadata |
| **MySQL** | MySqlConnector | Curated product catalogue (`shop_products`) for the web shop |

Sync is **one-way only**. Nothing writes back to Access.

## Entry point

`Program.cs` loads `.env`, configures Serilog (console + `logs/sync-YYYYMMDD.log`), validates options, and delegates to `SyncOrchestrator.Run(mode)`.

### Single instance

Only one `SyncEngine.exe` may run on a machine at a time. `SyncInstanceLock` acquires a global named mutex (`Global\SyncCoordinator.SyncEngine`) in `Program.cs` immediately after logging is configured. The lock is held for the full process lifetime and released in `Dispose` on normal exit.

```mermaid
flowchart TD
    Start[SyncEngine.exe starts] --> LogSetup[Configure Serilog]
    LogSetup --> TryLock{SyncInstanceLock.TryAcquire}
    TryLock -->|mutex held| Skip["Log warning, exit 3"]
    TryLock -->|acquired| RunSync[Load config, run orchestrator]
    RunSync --> Release[Dispose mutex]
    Skip --> End[Exit]
    Release --> End
```

If another instance is already running — for example an overlapping Task Scheduler trigger — the new process logs a warning and exits with code **3** without connecting to databases.

| Topic | Behavior |
|-------|----------|
| Scope | One process per machine (all CLI modes share the same lock) |
| Mutex name | `Global\SyncCoordinator.SyncEngine` (`Global\` works across Task Scheduler service accounts) |
| Crash / kill | OS releases the mutex when the process ends — the guard does not get stuck |
| Hung process | Mutex stays held until that process exits — end it in Task Manager if needed |
| Configuration | Not configurable — no `.env` bypass |

Implementation: [`SyncEngine/Sync/SyncInstanceLock.cs`](../SyncEngine/Sync/SyncInstanceLock.cs). Tests: `SyncInstanceLockTests`.

## CLI mode routing

```mermaid
flowchart TD
    Start[SyncEngine.exe mode] --> Parse[Parse_and_validate_mode]
    Parse --> CheckAccess{access_leg_enabled?}
    CheckAccess -->|all_access_schemaOnly| Leg1[RunAccessToSqlExpress]
    CheckAccess -->|mysql_only| Skip1[Skip_Access_leg]
    Leg1 --> CheckMySql{mysql_leg_enabled?}
    Skip1 --> CheckMySql
    CheckMySql -->|all_or_mysql| Leg2[RunSqlExpressToMySql]
    CheckMySql -->|access_or_schemaOnly| Done[Return_exit_code]
    Leg2 --> Done
```

| Mode | Leg 1 (Access → SQL Express) | Leg 2 (SQL Express → MySQL) |
|------|------------------------------|-----------------------------|
| `all` | Schema + data | Product sync |
| `access` | Schema + data | Skipped |
| `schema-only` | Schema only (no data) | Skipped |
| `mysql` | Skipped | Product sync |

Feature switches (`SYNC_ACCESS_TO_SQL_EXPRESS`, `SYNC_SQL_EXPRESS_TO_MYSQL`) can disable either leg regardless of mode.

## Leg 1: Access → SQL Express

```mermaid
flowchart TD
    A1[Read_Access_schema] --> A2[Read_view_column_catalog]
    A2 --> A3[Read_MSys_metadata_and_FKs]
    A3 --> A4[Topological_sort_by_FK_deps]
    A4 --> A5[Open_SQL_Express_create_DB]
    A5 --> A6[Ensure_sync_metadata_schema]
    A6 --> A7[Start_run_audit]
    A7 --> A8[Provision_schema_DDL]
    A8 --> A9{schema_only?}
    A9 -->|no| A10[Sync_table_data_in_batches]
    A10 --> A11[Soft_delete_missing_rows]
    A9 -->|yes| A12[Skip_data_sync]
    A11 --> A13[Finish_run_audit]
    A12 --> A13
```

### Step 1 — Schema discovery

`AccessSchemaReader` connects via ACE OLEDB and:

1. Lists user tables via `GetSchema("Tables")`
2. Filters system tables (`MSys*`, `p`, `~TMPCLP*`)
3. Applies `ACCESS_SQL_EXCLUDE_TABLES`
4. Reads column definitions per table (`SELECT TOP 1 *` with `SchemaOnly | KeyInfo` — includes `IsAutoIncrement`)
5. **Enriches from DAO** — `DefaultValue` and `ValidationRule` via `Access.Application` (`EnrichColumnMetadataFromDao`)

### Step 2 — View column catalog

All user tables (including excluded ones) are catalogued for `table.*` expansion during view translation.

### Step 3 — Metadata reads

Queries, indexes, and foreign keys are discovered in order:

1. OleDb system tables (`MSysObjects`, `MSysRelationships`, `MSysIndexes`)
2. COM fallback — `Access.Application` via `AccessApplicationSysReader` (GRANT on `MSysObjects` / `MSysRelationships`, then QueryDefs / Relations / TableDefs.Indexes)
3. OleDb retry after GRANT when FK or index lists are empty
4. DAO index fallback when `MSysIndexes` is unreadable
5. Manual fallback — `config/access-views.json`

Composite indexes from `MSysIndexes` are grouped by [`AccessIndexGrouper`](../SyncEngine/Access/AccessIndexGrouper.cs). Primary key columns are applied to each `AccessTableSchema` for data sync.

### Step 4 — Dependency ordering

Tables are topologically sorted by FK parent→child relationships so parent rows exist before children.

### Step 5 — SQL Express provisioning

`SqlSchemaProvisioner` runs in this order:

| Step | Method | What it does |
|------|--------|--------------|
| 1 | `ProvisionTables` | Identity rebuild if needed; `CREATE TABLE` / add columns; inline defaults; `ALTER` defaults; NOT NULL tightening |
| 2 | `ProvisionKeysAndIndexes` | Primary keys, then unique/non-unique indexes (filtered unique on nullable single columns) |
| 3 | `ProvisionForeignKeys` | FK constraints with cascade when set in Access |
| 4 | `ProvisionCheckConstraints` | Field/table `ValidationRule` → `CHECK` where translatable |
| 5 | `ProvisionViews` | Access query SQL → T-SQL views |

Details of type mapping, defaults, identity rebuild, and index semantics: [access-sql-express-compatibility.md](access-sql-express-compatibility.md).

**Sync columns** — four columns appended to every mirrored table (see [Sync columns](#sync-columns-every-mirrored-sql-express-table)).

**Statistics** — Leg 1 logs a summary via `SyncStatistics.FormatAccessSqlSummary`. Counters use `*Created` (new this run), `*Unchanged` (already satisfied), and `*Skipped` (failures only). A healthy re-run shows `pksSkipped=0`, `indexesSkipped=0`, `defaultsSkipped=0` with high `*Unchanged` counts.

### Step 6 — Data sync

`SqlDataSync` processes each table in dependency order:

1. **Skip** if no primary key detected (`IsKey` flag, or first `*ID` column)
2. **Composite PKs** — all key columns used for upsert matching, updates, and Access pagination (e.g. `Switchboard Items`)
3. **Paginate** Access with lexicographic ORDER BY on all PK columns; resume with a composite `WHERE` predicate
3. **Upsert** to SQL Express in a transaction:
   - Insert if PK missing (uses `IDENTITY_INSERT` when table has AutoNumber column)
   - Update only if `_sync_row_hash` changed (SHA256 of normalized column values)
   - Set `_sync_is_deleted = 0`, `_sync_last_seen_at`, `_sync_updated_at`
   - After each batch, **mark every Access row in the batch as seen** (even when hash matched and no data UPDATE ran — avoids false soft deletes when `CHAR(64)` hash padding makes SQL skip the change UPDATE)
4. **Reseed** `DBCC CHECKIDENT` after each table when AutoNumber column present
5. **Checkpoint** in `sync_metadata.checkpoints` when each **table** finishes (not after every batch)

### Step 7 — Soft delete

`SoftDeleteTracker` marks rows not seen in the current run:

```sql
UPDATE [table] SET _sync_is_deleted = 1
WHERE _sync_is_deleted = 0
  AND (_sync_last_seen_at IS NULL
       OR DATEDIFF(millisecond, _sync_last_seen_at, @runStart) > 0)
```

Controlled by `SYNC_SOFT_DELETE_ENABLED`. Skipped in dry-run mode.

### Leg 1 failure modes

Leg 1 uses **multiple short-lived Access connections** during schema discovery (`ReadTables`, `ReadViewColumnCatalog`, MSys/COM reads), then **one `OleDbConnection`** for the entire data sync (`SqlDataSync.SyncTables`). **One `SqlConnection`** to SQL Express is opened for provisioning, data sync, soft delete, and run audit — held until the leg completes. There is no reconnect mid-run on either side.

| Phase | Access needed? | SQL Express writes? | On failure |
|-------|----------------|---------------------|------------|
| Schema discovery (`ReadTables`, catalog, MSys/COM) | Yes | No | Exit **1** immediately; no `sync_metadata.runs` row |
| SQL Express `Open()` / provisioning / views | No | Yes (DDL) | Exit **1**; run audit may record `failed` if `StartRun` already ran |
| Access `ReadBatch` (paginated SELECT) | Yes | No | Exit **1** immediately — **no retry** on Access reads |
| SQL Express batch upsert | No (batch already in memory) | Yes (transaction) | Retries then exit **1**; see partial sync below |
| Per-table soft delete | No | Yes | Skipped for the current table if upserts failed; later tables not processed |
| `sync_metadata.runs` finish | No | Yes | `status = failed` with partial stats when SQL Express is still reachable |

```mermaid
flowchart TD
    start[Leg1_starts] --> accessSchema[Access_schema_discovery]
    accessSchema --> sqlOpen[Open_SQL_Express]
    sqlOpen --> provision[Provision_DDL_and_start_run]
    provision --> openAccess[Open_single_Access_connection]
    openAccess --> tableLoop[For_each_table_in_FK_order]
    tableLoop --> readBatch[ReadBatch_from_Access]
    readBatch --> upsert[UpsertBatch_to_SQL_Express_in_tx]
    upsert --> moreBatch{More_rows_in_table?}
    moreBatch -->|yes| readBatch
    moreBatch -->|no| softDel[Soft_delete_missing_rows]
    softDel --> moreTable{More_tables?}
    moreTable -->|yes| tableLoop
    moreTable -->|no| finishRun[Finish_run_audit_success]
    finishRun --> success[Exit_0]

    accessSchema -->|Access_drop| failAccess[Exit_1]
    readBatch -->|Access_drop| failAccess
    upsert -->|SQL_Express_drop| retry[Polly_retries_same_batch]
    retry -->|still_dead_conn| failSql[Orchestrator_catch]
    failSql --> finishFailed[Finish_run_audit_failed]
    finishFailed --> exit1[Exit_1]
```

#### Access disconnects

- **File locked** — another process (often Microsoft Access UI) holds the `.mdb`; `SyncErrorFormatter` reports *"Access database is locked (file already in use)"*.
- **File missing / bad password** — fails during `Open()` with a configuration-style message.
- **Mid-table read** — `ReadBatch` is not wrapped in `BatchProcessor`; a dropped or locked file during data sync fails the leg immediately with no retry.
- **COM/OleDb MSys fallback** — some metadata reads retry via alternate paths (OleDb retry, `Access.Application` COM); data sync does not.

#### SQL Express disconnects

- Batch upserts call [`BatchProcessor`](../SyncEngine/Sync/BatchProcessor.cs) with `SYNC_MAX_RETRIES` (default 3) — see [Retries](#retries).
- Each batch runs in a **transaction** (`BeginTransaction` / `Commit`); failure rolls back the **current batch only**.
- Retries re-execute on the **same** `SqlConnection` — effective for transient query errors, **not** for a dropped TCP connection (no fresh `Open()` mid-run).
- `SyncErrorFormatter` maps connection errors (e.g. error -1, 2, 53) to *"Cannot connect to SQL Express…"*.

#### Partial sync semantics

- Batches **committed** before the failure remain in SQL Express.
- The **failed batch** is fully rolled back (transaction).
- **Checkpoint** (`sync_metadata.checkpoints`) is saved only when a **table completes** — not after each batch. A mid-table failure leaves no checkpoint for that table in the current run.
- **Soft delete** for the interrupted table does not run; subsequent tables are not processed.
- Tables fully synced before the failure are complete (including soft delete for those tables).
- Checkpoints are **per-run only** — the next run re-reads each table from the beginning (`last_pk` is null for the new `run_id`). Upserts are idempotent via `_sync_row_hash`.

#### Orchestrator handling

When Leg 1 throws inside the SQL Express `using` block, [`SyncOrchestrator.RunAccessToSqlExpress`](../SyncEngine/Sync/SyncOrchestrator.cs) calls `sync_metadata.runs` with `status = failed` and partial row/table counts, then rethrows. Exit code **1**.

If the failure occurs **before** `StartRun` (e.g. during Access schema discovery), no run audit row is written.

In **`all` mode**, Leg 2 (SQL Express → MySQL) **still runs** after a Leg 1 failure — it syncs whatever is already in SQL Express, which may be partial or stale.

#### Recovery

Re-run when Access and SQL Express are healthy. No manual rollback of mirrored tables is required; the next successful run reconciles from Access.

Implementation: [`SqlDataSync`](../SyncEngine/SqlServer/SqlDataSync.cs), [`AccessConnectionFactory`](../SyncEngine/Access/AccessConnectionFactory.cs), [`SqlServerConnectionFactory`](../SyncEngine/SqlServer/SqlServerConnectionFactory.cs), [`SyncMetadataRepository`](../SyncEngine/Metadata/SyncMetadataRepository.cs).

### Sync columns (every mirrored SQL Express table)

| Column | Type | Purpose |
|--------|------|---------|
| `_sync_row_hash` | `NVARCHAR(64)` | SHA256 hash for change detection |
| `_sync_is_deleted` | `BIT` | `1` = row removed from Access |
| `_sync_last_seen_at` | `DATETIME2` | Last run where row appeared in Access |
| `_sync_updated_at` | `DATETIME2` | Last sync write timestamp |

### SQL Express metadata schema

`sync_metadata` schema (created by `SyncMetadataRepository`):

| Table | Purpose |
|-------|---------|
| `sync_metadata.runs` | Run audit: leg, timestamps, status, row counts, errors JSON |
| `sync_metadata.checkpoints` | Per-run, per-table: last PK, insert/update/delete counts |
| `sync_metadata.schema_versions` | DDL snapshots (`table:tblProduct`, `view:qryName`) |

Checkpoints are per-run only — there is no cross-run resume.

## Leg 2: SQL Express → MySQL

```mermaid
flowchart TD
    M1[Open_SQL_Express_and_MySQL] --> M2[RequireTables_no_DDL]
    M2 --> M3[Query_active_products_with_JOINs]
    M3 --> M4[Map_fields_via_ProductFieldMapper]
    M4 --> M5[Batch_upsert_by_code]
    M5 --> M6[Count_withdrawn_on_source]
    M6 --> M7[Write_shop_sync_log]
```

### Schema validation

Leg 2 **does not create** MySQL tables. `shop_products` and `shop_sync_log` must exist before sync (CI4 shop migrations). [`ShopSchemaValidator`](../SyncEngine/MySql/ShopSchemaValidator.cs) validates required tables and the `shop_products.sqlexpress_product_id` column at leg start. Missing table or column → `ConfigurationException` (exit **2**).

### Product query

Active products (`_sync_is_deleted = 0`) are read from `tblProduct` with LEFT JOINs to:

- `tblSubCategory` → `tblCategory`
- `tblItem`
- `tblSex`
- `tblLocation`

Barcode, image, and product ID columns are configurable via `product-sync-map.json` (defaults: `strCode`, `strImagePath`, `ProductID`).

### Field mapping

`ProductFieldMapper` handles:

- **Truncate** — string fields to MySQL column max lengths
- **Slug** — always `{productId}-{code}-{description}` (slugified); empty code → `{productId}--{description}`
- **Price** — `dblDailyRate` parsed to `DECIMAL(10,2)`
- **Visibility** — `tblProduct.blnCameraReady` → `shop_products.is_visible` (`ToBool`: null/false/0 → false; non-zero incl. Access `-1` → true)
- **Product link** — `tblProduct.ProductID` → `shop_products.sqlexpress_product_id` (not MySQL `id`)

See [shop_products.md](shop_products.md) for the full mapping table.

### Upsert logic

Leg 2 upserts with **multi-row** `INSERT ... ON DUPLICATE KEY UPDATE` (chunks of [`ShopProductSql.UpsertChunkSize`](../SyncEngine/MySql/ShopProductSql.cs) = 500), not per-row SELECT/INSERT/UPDATE. That keeps MySQL round-trips low when `MYSQL_HOST` is off-box (large catalogues over WAN were previously dominated by ~2 statements × product count).

| Case | Behavior |
|------|----------|
| New product (`code` not in MySQL) | INSERT with `sqlexpress_product_id`, `is_visible` from Camera Ready; MySQL `id` auto-assigned; web-only columns at defaults |
| Existing product | `ON DUPLICATE KEY UPDATE` sets synced columns from `VALUES(...)`; `synced_at` updates only when any synced field changed (NULL-safe `<=>`) |
| Missing `ProductID` on source | Row skipped with warning |
| Web-only columns | `is_featured`, `featured_sort`, `web_copy`, `web_notes` — **never listed** in the UPDATE clause |
| `image_url_1` / `image_url_2` / `image_url_3` | Set on INSERT only (`image_url_1` from Access; `_2`/`_3` null); **omitted** from `ON DUPLICATE KEY UPDATE` so existing values are preserved |

Each upsert chunk runs in a **MySQL transaction**. Flush batches log `elapsedMs` / `msPerRow` for remote timing.

### Withdrawn products

Products with `_sync_is_deleted = 1` on SQL Express are **counted** (`products_withdrawn` in `shop_sync_log`) but **not hidden or deleted** in MySQL. Soft-deleted rows are not updated; Camera Ready / `is_visible` only applies to products still in the active sync query.

### Leg 2 failure modes

Leg 2 uses [`MySqlConnectionHolder`](../SyncEngine/MySql/MySqlConnectionHolder.cs) to manage the MySQL connection. On connection loss during upserts, it **reconnects** (new connection, no DDL) and **retries the failed upsert chunk** (within-run resume). Table validation runs once at leg start only.

| Phase | MySQL writes? | On failure |
|-------|---------------|------------|
| Initial `Open()` / `RequireTables` | No | Exit **2** if table missing; exit **1** if connection fails |
| Batch upserts | Yes (transaction per multi-row IODKU chunk) | Reconnect + retry failed chunk up to `SYNC_MAX_RETRIES`; then exit **1** |
| `CountWithdrawnOnSource` | No (SQL Express only) | Skipped if upserts failed |
| `shop_sync_log` insert | Yes | Exit **1** even if products already synced |

```mermaid
flowchart TD
    start[Leg2_starts] --> open[Open_MySQL_connection]
    open --> validate[RequireTables]
    validate --> read[Stream_products_from_SQL_Express]
    read --> chunk[Upsert_multi_row_IODKU_chunk]
    chunk --> more{More_rows?}
    more -->|yes| chunk
    more -->|no| count[Count_withdrawn_on_SQL_Express]
    count --> log[Write_shop_sync_log]
    log --> success[Exit_0]

    chunk -->|connection_lost| reconnect[Open_new_connection]
    reconnect --> retryChunk[Retry_same_chunk]
    retryChunk -->|success| more
    retryChunk -->|max_retries| fail[Orchestrator_catch_exit_1]
    validate -->|table_missing| exit2[Exit_2]
```

#### Retry behavior

Upsert chunks use Polly with `SYNC_MAX_RETRIES` (default 3) and exponential backoff — see [Retries](#retries). On a reconnectable error ([`MySqlConnectionFailures`](../SyncEngine/MySql/MySqlConnectionFailures.cs)), the holder opens a **new** connection and retries the **same chunk** (not the whole leg). Non-connection errors (access denied, missing table, constraint violation) fail immediately.

Contrast with Leg 1: SQL Express data sync also wraps each batch in a **transaction**; Leg 2 MySQL chunks do the same.

#### Partial sync semantics

- Chunks committed before the failure remain in MySQL.
- After reconnect, the failed chunk is retried; upserts are idempotent by `code`.
- Web-only columns (`is_featured`, etc.) are unaffected; `is_visible` is synced from Camera Ready.
- No cross-run checkpoint — a **crashed process** still relies on the next full sync run.

#### Orchestrator handling

When Leg 2 throws, [`SyncOrchestrator.RunSqlExpressToMySql`](../SyncEngine/Sync/SyncOrchestrator.cs):

1. Logs the error via [`SyncErrorFormatter`](../SyncEngine/Sync/SyncErrorFormatter.cs)
2. Sets `source_reachable = 0` for **any** Leg 2 failure — not only when SQL Express is unreachable
3. Attempts to INSERT a failed row into `shop_sync_log` via a **new** connection (`ShopSyncLogClient`)
4. If MySQL INSERT fails and `SHOP_SYNC_API_URL` + `SHOP_SYNC_API_KEY` are configured, POSTs the same run data to `POST /api/shop-sync/log` (`ShopSyncLogApiClient`)
5. Returns exit code **1** (or **2** for missing MySQL tables)

If both MySQL and API log writes fail, the run JSON is appended to `logs/shop-sync-log-failed-YYYYMMDD.jsonl` and Serilog records: *"Could not write shop_sync_log (MySQL and API fallback both failed)"* — check that file or `logs/sync-YYYYMMDD.log`.

#### Recovery

Within the same run, reconnect + chunk retry handles transient MySQL drops. After a process crash or exhausted retries, the next successful run re-reads all active products from SQL Express and upserts from scratch.

Implementation: [`MySqlProductSync`](../SyncEngine/MySql/MySqlProductSync.cs), [`ShopSchemaValidator`](../SyncEngine/MySql/ShopSchemaValidator.cs), [`ShopSyncLogClient`](../SyncEngine/MySql/ShopSyncLogClient.cs).

## Change detection

`ChangeDetector` computes SHA256 over normalized column values. On SQL Express:

- Unchanged hash → skip UPDATE (no write)
- Changed hash → UPDATE row and refresh `_sync_row_hash`

On MySQL, the UPDATE statement includes a WHERE clause comparing all synced columns with `<=>` to avoid no-op writes.

## View translation

Access query SQL is translated to T-SQL via `SqlViewTranslator` before views are provisioned. Full list of type, literal, naming, and SQL corrections: [access-sql-express-compatibility.md](access-sql-express-compatibility.md).

Skipped: TRANSFORM / CROSSTAB queries and queries that fail translation. Fallback: `config/access-views.json`.

## Retries

`BatchProcessor` wraps batch operations with Polly retry:

- Max attempts: `SYNC_MAX_RETRIES` (default 3)
- Backoff: exponential (2^attempt seconds)
- **Leg 1** (Access → SQL Express): retries **SQL Express batch upserts** only (inside a transaction). Access `ReadBatch` calls are **not** retried. Does **not** reconnect after a connection drop — see [Leg 1 failure modes](#leg-1-failure-modes)
- **Leg 2** (SQL Express → MySQL): retries **multi-row IODKU chunks** with **MySQL reconnect** on connection loss — see [Leg 2 failure modes](#leg-2-failure-modes)

## Logging

| Destination | Content |
|-------------|---------|
| Console | Information-level progress, schema summary, and errors |
| `logs/sync-YYYYMMDD.log` | Rolling daily file (same content) |
| `sync_metadata.runs` | Per-leg run history with stats and error JSON |
| `shop_sync_log` | MySQL leg summary (upserted, withdrawn, status) |
| `logs/shop-sync-log-failed-YYYYMMDD.jsonl` | Run JSON when MySQL and API log writes both fail |

## Key classes

| Class | Responsibility |
|-------|----------------|
| `SyncInstanceLock` | Global mutex — single-instance guard (exit 3 if already running) |
| `SyncOrchestrator` | Mode routing, coordinates both legs |
| `AccessSchemaReader` | Tables, columns, AutoNumber, queries, FKs, indexes, dependency sort |
| `AccessIndexGrouper` | Groups `MSysIndexes` rows into composite index definitions |
| `AccessConnectionFactory` | ACE OLEDB connection |
| `AccessApplicationSysReader` | COM fallback for MSys metadata and DAO field enrichment |
| `AccessDefaultTranslator` | Access `DefaultValue` → T-SQL `DEFAULT` literal |
| `AccessValidationTranslator` | Access `ValidationRule` → T-SQL `CHECK` predicate |
| `SqlSchemaProvisioner` | DDL: tables, keys, defaults, NOT NULL, FKs, CHECKs, views + sync columns |
| `SqlSchemaKeyBuilder` | PK/index DDL, filtered unique index helper |
| `SqlIdentityHelper` | AutoNumber rebuild, `IDENTITY_INSERT`, `DBCC CHECKIDENT` |
| `SqlViewTranslator` | Access SQL → T-SQL |
| `SqlDataSync` | Batch read Access → upsert SQL Express |
| `SoftDeleteTracker` | Mark unseen rows deleted |
| `SyncMetadataRepository` | `sync_metadata.*` audit tables |
| `MySqlProductSync` | Product query + upsert to `shop_products` |
| `ShopSchemaValidator` | Validates required MySQL tables exist (no DDL) |
| `MySqlConnectionHolder` | MySQL open/reconnect for Leg 2 |
| `MySqlConnectionFailures` | Classifies reconnectable connection errors |
| `ShopSyncLogClient` | Writes `shop_sync_log` (MySQL first, API fallback) |
| `ShopSyncLogApiClient` | POSTs run JSON to CI4 when MySQL log write fails |
| `ChangeDetector` | SHA256 row hash |
| `BatchProcessor` | Polly retry wrapper |
| `TableExcludeFilter` | Parse/filter excluded tables, FKs, and indexes |

## Related

- [configuration.md](configuration.md) — settings that control sync behavior
- [access-sql-express-compatibility.md](access-sql-express-compatibility.md) — Access → SQL Express schema mirroring
- [shop_products.md](shop_products.md) — MySQL schema and field mapping
- [getting-started.md](getting-started.md) — first-run walkthrough