# MediCore — Local ↔ Cloud Database Synchronization Architecture

This document describes the production architecture that keeps a Cloud
Server's copy of the MediCore database up to date with the Local
Server running inside the pharmacy LAN, and the code delivered
alongside it. It follows the same transactional-outbox philosophy
already used for real-time stock sync between terminals on the Local
Server (see `REALTIME_STOCK_SYNC.md`) — this is the same pattern,
applied one level up, from Local to Cloud.

## 1. Deployment model

```
┌─────────────────────────── Pharmacy LAN ───────────────────────────┐
│                                                                      │
│   Sale Terminal   Purchase Terminal   Admin PC   Warehouse PC       │
│        │                 │               │            │            │
│        └─────────────────┴───────┬───────┴────────────┘            │
│                                   │  (existing intra-LAN stock      │
│                                   │   sync — REALTIME_STOCK_SYNC.md)│
│                                   ▼                                 │
│                        ┌────────────────────┐                      │
│                        │   Local Server      │  ◄── MASTER          │
│                        │   XAMPP / Apache    │      database        │
│                        │   MySQL (medicore)  │                      │
│                        └──────────┬──────────┘                      │
│                                   │ same-transaction outbox write   │
│                                   ▼                                 │
│                        ┌────────────────────┐                      │
│                        │   sync_queue table  │                      │
│                        └──────────┬──────────┘                      │
│                                   │ drained by                      │
│                                   ▼                                 │
│                        ┌────────────────────┐                      │
│                        │  sync/worker.php    │  CLI process,        │
│                        │  (Task Scheduler /  │  outbound HTTPS      │
│                        │   background loop)  │  only                │
│                        └──────────┬──────────┘                      │
└───────────────────────────────────┼──────────────────────────────────┘
                                     │  HTTPS POST, HMAC-signed batch
                                     │  (only when Internet is up)
                                     ▼
                        ┌─────────────────────────┐
                        │  Cloud Server            │
                        │  api/sync-receiver.php   │  ◄── receive-only
                        │  MySQL (medicore, mirror)│
                        └─────────────────────────┘
                                     │
                                     ▼
                     Owner / Head Office reporting & monitoring
                     dashboards, remote access, backup visibility
```

Local is the single source of truth. Cloud never writes back into
Local — it is a read-oriented mirror for reporting, remote visibility,
and disaster-recovery backup. That asymmetry is what makes the whole
design simple: there is exactly one direction of flow, so there is no
merge-conflict problem to solve at all.

## 2. Recommended architecture — and why (Requirement 10)

| Option | Verdict | Why |
|---|---|---|
| **MySQL replication** (binlog-based) | ❌ Rejected | Requires a stable, low-latency link and matching MySQL versions/configuration; a pharmacy's Internet connection dropping for hours (very normal) leaves replication needing manual intervention to catch up cleanly, and it replicates *everything* including tables you may not want mirrored (sessions, audit noise). No natural place to add per-branch identity or field-level filtering. Too brittle for "Internet unavailable most of the day" reality. |
| **Raw message-queue architecture** (RabbitMQ/Kafka in front of Cloud) | ❌ Rejected *for now* | Real infrastructure to install, secure, and keep alive on both ends, for a workload (one pharmacy, a few hundred transactions/day) that doesn't need it yet. Revisited in §16 once branch count actually justifies it. |
| **Pure event-based push (fire-and-forget HTTP on every write)** | ❌ Rejected alone | Nothing durable on the Local side if the Cloud/Internet is down at the moment of the write — the event is just lost unless you also keep a durable log of it, at which point you've reinvented an outbox anyway. |
| **API-based sync with a durable transactional outbox (`sync_queue`)** | ✅ **Recommended** | Combines the simplicity of "just call an API" with the durability of a real queue: every operational write enqueues a row in the *same* database transaction as the write itself, so nothing can be lost, duplicated by a crash, or left inconsistent with the data it describes. A CLI worker drains the queue over plain HTTPS whenever the Internet is up. No new infrastructure beyond what already runs (PHP + MySQL), no long-lived connections (see the Apache-worker-thread lesson already learned from this app's SSE history), and it degrades to "just keep queuing" instead of failing when offline. |

This is deliberately the same pattern this codebase already uses for
`stock_events` (real-time stock push between terminals) and
`sync_queue` for holds' auto-save — proven, understood, and now reused
one level up for Local→Cloud.

## 3. Local is master (Requirement 1–2)

Every write to these tables enqueues a sync row inside the same
transaction as the write:

| Activity | Local table(s) | Enqueued as |
|---|---|---|
| Sales | `sales` + `sale_items` (embedded) | INSERT on create, UPDATE on due-collection/soft-delete |
| Purchases | `purchases` + `purchase_items` (embedded) | INSERT on create, UPDATE on approve/reject/soft-delete |
| Sale Returns | `sales_returns` + `sales_return_items` (embedded) | INSERT on create, UPDATE on soft-delete |
| Purchase Returns | `purchase_returns` + `purchase_return_items` (embedded) | INSERT on create, UPDATE on soft-delete |
| Payments (to suppliers) | `cash_ledger` (type=out) | INSERT |
| Receipts (from customers) | `cash_ledger` (type=in), `customers.advance/due` | INSERT / UPDATE |
| Manual cash entries | `cash_ledger` | INSERT, DELETE (hard-delete — see §9) |
| Expenses / Incomes | `expenses` / `incomes` + linked `cash_ledger` row | INSERT, DELETE |
| Stock Adjustments | *(not built yet — see note below)* | — |

Line items (`sale_items`, `purchase_items`, etc.) are sent embedded
inside their parent's payload as a JSON `items` array rather than as
separate queue rows — they're created once with the parent and never
edited independently in this app, so there's no benefit to tracking
them as their own outbox entries, and it halves the number of HTTP
round trips per transaction. `api/sync-receiver.php` unpacks `items`
and upserts each one into the correct child table (see
`ITEM_TABLE_MAP` in that file).

**Stock Adjustments** don't exist as a feature in this codebase yet
(confirmed while building this — same finding as the earlier
real-time stock-sync work). The moment that feature is built, wiring
it in is a one-line addition: call
`enqueue_sync('stock_adjustments', $id, 'INSERT', $snapshot)` inside
its transaction, exactly like every other endpoint below.

## 4. The Sync Queue (Requirement 6, 12)

### `sync_queue` (Local Server)

```sql
CREATE TABLE sync_queue (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_id      INT UNSIGNED NOT NULL DEFAULT 1,
    table_name     VARCHAR(64)  NOT NULL,
    record_id      BIGINT UNSIGNED NOT NULL,
    operation_type ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    payload        MEDIUMTEXT   DEFAULT NULL,      -- full row snapshot as JSON (NULL for DELETE)
    status         ENUM('pending','sent','failed','done') NOT NULL DEFAULT 'pending',
    attempts       INT UNSIGNED NOT NULL DEFAULT 0,
    last_error     TEXT DEFAULT NULL,
    next_retry_at  TIMESTAMP(6) NULL DEFAULT NULL,
    created_at     TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    sent_at        TIMESTAMP(6) NULL DEFAULT NULL,
    INDEX idx_status_id (status, id),
    INDEX idx_table_record (table_name, record_id),
    INDEX idx_branch (branch_id)
) ENGINE=InnoDB;
```

Written by `enqueue_sync()` in `includes/db.php`, called from inside
every operational endpoint's existing transaction, right after the
write it describes — the same rule already established for
`publish_stock_event()`. If the transaction rolls back, the queue row
rolls back with it; there is no code path where data changes but the
queue doesn't hear about it, or vice versa.

### `sync_log` (both servers)

```sql
CREATE TABLE sync_log (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    queue_id       BIGINT UNSIGNED NOT NULL,
    branch_id      INT UNSIGNED NOT NULL DEFAULT 1,
    table_name     VARCHAR(64)  NOT NULL,
    record_id      BIGINT UNSIGNED NOT NULL,
    operation_type VARCHAR(10)  NOT NULL,
    batch_id       VARCHAR(40)  DEFAULT NULL,
    http_status    INT DEFAULT NULL,
    success        TINYINT(1)   NOT NULL DEFAULT 0,
    duration_ms    INT DEFAULT NULL,
    response_snip  VARCHAR(500) DEFAULT NULL,
    created_at     TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_queue (queue_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;
```

An immutable audit trail of *every attempt*, success or failure, on
both ends — the Local worker logs each push it makes, the Cloud
receiver logs each row it applies or rejects. This is what you'd query
to answer "did invoice INV-2026-000482 actually reach the Cloud, and
when."

### `sync_status` (Local Server, one row per branch)

```sql
CREATE TABLE sync_status (
    branch_id            INT UNSIGNED PRIMARY KEY,
    branch_name          VARCHAR(100) DEFAULT NULL,
    last_synced_queue_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
    last_attempt_at      TIMESTAMP(6) NULL DEFAULT NULL,
    last_success_at      TIMESTAMP(6) NULL DEFAULT NULL,
    is_online             TINYINT(1)  NOT NULL DEFAULT 0,
    consecutive_failures  INT UNSIGNED NOT NULL DEFAULT 0,
    pending_count         INT UNSIGNED NOT NULL DEFAULT 0,
    updated_at            TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB;
```

A single small table a dashboard widget or admin page can poll to
show "Sync: ● Online — last synced 4s ago — 0 pending" or "⚠ Offline
since 11:42am — 37 changes queued." Updated by `sync/worker.php` after
every cycle, whether it succeeded, failed, or found nothing to do.

### `sync_applied` (Cloud Server only)

```sql
CREATE TABLE sync_applied (
    branch_id      INT UNSIGNED NOT NULL,
    table_name     VARCHAR(64)  NOT NULL,
    record_id      BIGINT UNSIGNED NOT NULL,
    last_queue_id  BIGINT UNSIGNED NOT NULL,
    updated_at     TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    PRIMARY KEY (branch_id, table_name, record_id)
) ENGINE=InnoDB;
```

This is the piece that makes duplicate/out-of-order delivery safe (see
§6) — it remembers the highest `sync_queue.id` already applied for
every record, per branch.

All four tables are created automatically and idempotently the first
time they're needed (`ensure_sync_tables()` / `ensure_cloud_sync_tables()`
— same defensive-migration convention used everywhere else in this
codebase), no manual schema setup required.

## 5. Change tracking fields (Requirement 11)

| Field | Where | Purpose |
|---|---|---|
| `table_name` | `sync_queue`, `sync_log`, `sync_applied` | Which operational table changed. |
| `record_id` | same | Which row (the Local row's own `id` — Local's PK is preserved as-is, never re-generated by Cloud). |
| `operation_type` | same | `INSERT` / `UPDATE` / `DELETE` — tells the receiver which action to take (though INSERT and UPDATE are handled identically, see §9). |
| `version` | *(not a separate column — see below)* | |
| `updated_at` | inside `payload` (each table's own `updated_at`/`created_at` columns travel as part of the row snapshot) | Lets Cloud-side reports show the same timestamps Local shows. |

**On "version":** we deliberately did **not** add a separate per-record
version counter. `sync_queue.id` is already global, strictly
monotonic, and gap-free (a standard auto-increment) — it functions as
a Local Sequence Number (LSN) with zero extra bookkeeping. The Cloud
receiver stores the highest applied `queue_id` per `(branch_id,
table_name, record_id)` in `sync_applied.last_queue_id`, and only
applies an incoming change if its `queue_id` is greater. That single
comparison **is** the version check — simpler than maintaining a
separate revision counter on every synced table, and just as correct
because Local is the only writer for any given record (single-writer
per record eliminates the need for a full optimistic-concurrency
scheme; there's nothing to reconcile).

## 6. Preventing duplicate synchronization (Requirement 7)

Three independent layers, each catching a different failure mode:

1. **Coalescing on the send side** (`coalesce_batch()` in
   `sync/worker.php`): if the same record changed more than once
   before the worker got to it (e.g. a sale created, then its due
   collected, both within one polling cycle), only the *latest*
   snapshot is transmitted — it already reflects every earlier change.
   The superseded queue rows are still tracked and marked `done`
   alongside the one that was actually sent, so nothing is resent or
   silently dropped.
2. **Idempotency key** (`queue_id`, carried in every request): each
   push batch identifies every row by its immutable `sync_queue.id`.
   If a network blip causes the Local worker to time out *after* the
   Cloud already received and applied the batch, the next retry sends
   the same `queue_id`s again — see point 3 for why that's harmless.
3. **Version-gated UPSERT on the receive side** (`sync_applied`, §5):
   the Cloud receiver checks `last_queue_id` before applying anything.
   A replayed batch with the same or older `queue_id`s is a guaranteed
   no-op — it's recognized and skipped, but still reported back as
   "applied" so the Local worker can clear it from its own queue.

## 7. Insert / Update / Delete (Requirement 9)

- **INSERT and UPDATE** are handled by the exact same code path on the
  Cloud side: `upsert_row()` builds `INSERT ... ON DUPLICATE KEY
  UPDATE` dynamically from whatever columns are present in the
  payload. There's no meaningful difference between "create this row"
  and "make this row look like this" when Local already sends the
  full current snapshot either way — one function handles both.
- **DELETE** happens in two different shapes depending on the table's
  own convention on Local:
  - *Soft-delete tables* (`sales`, `purchases`, `sales_returns`,
    `purchase_returns`, and others using this app's standard
    `is_deleted` flag): "deleting" on Local is really an `UPDATE`
    setting `is_deleted = 1`. It's enqueued as `operation_type =
    'UPDATE'`, and the Cloud copy ends up with the same flag set — the
    row is preserved on both sides for audit/reporting, exactly
    matching Local's own behavior.
  - *Hard-delete tables* (`cash_ledger`, `expenses`, `incomes` — none
    of these have an `is_deleted` column on Local) send a real
    `operation_type = 'DELETE'` with no payload (only `record_id` is
    needed), and the Cloud receiver issues an actual `DELETE FROM`.

## 8. Stock consistency (Requirement 8)

Stock correctness on Local itself is already handled by the real-time
stock-sync system described in `REALTIME_STOCK_SYNC.md` (atomic
`WHERE stock_pcs >= ?` guarded updates, `stock_events` outbox for
same-LAN terminal push). This Local↔Cloud layer doesn't duplicate that
logic — it simply carries the *result* of every stock-affecting
transaction (sales, purchases, both kinds of returns) to Cloud as part
of each transaction's own row snapshot, in the same order those
transactions actually committed on Local (queue rows are always
applied to Cloud in ascending `queue_id` order within a batch, and the
`sync_applied` version gate prevents an older batch from ever
overwriting a newer one). Cloud's `products.stock_pcs` is therefore
always a faithful (if briefly delayed) reflection of Local's — it is
never independently written to, so there's no possibility of Cloud's
number *causing* a discrepancy.

## 9. Offline behavior (Requirement 5)

- **Local operations continue completely normally.** `enqueue_sync()`
  only ever writes to the *local* `sync_queue` table — it has no
  network dependency at all. A sale can be completed with zero
  Internet connectivity and behaves identically either way.
- **Failed/unreachable sync attempts are queued, never dropped.**
  `sync/worker.php`'s reachability probe (`cloud_is_reachable()`) runs
  before attempting a real batch; if it fails, the cycle exits
  immediately, `sync_status.is_online` flips to 0, and every pending
  row simply stays `pending` for the next cycle.
- **Sync resumes automatically** the next time the worker runs and
  the probe succeeds — no manual restart, no lost queue state, since
  everything durable lives in MySQL, not in the worker process's
  memory. Whether the worker runs once a minute via Task Scheduler or
  continuously via `--loop`, the very next successful cycle picks up
  exactly where the last one left off.

## 10. Near-real-time timing (Requirement 4)

`sync/worker.php` is designed to run every few seconds to one minute
depending on how it's deployed (see §12 Deployment). Because every
enqueue happens inside the same transaction as the operational write,
there is no additional lag between "the sale was saved" and "it's in
the queue" — the very next worker cycle after that commit will pick it
up. End-to-end Local-commit-to-Cloud-applied latency is therefore
bounded by the worker's cycle interval (as low as a few seconds in
`--loop` mode) plus one HTTPS round trip — not "immediate" in the
sub-second sense of the intra-LAN stock sync, but "almost immediately
after commit" as specified, and appropriately so: Cloud's role here is
monitoring/reporting/backup, not live cross-branch POS decisions.

## 11. Retry mechanism and failure recovery (Requirement 13)

On any failure (offline, HTTP error, timeout, or a row Cloud
explicitly rejects):

1. Every row in the failed batch gets `attempts += 1` and a
   `next_retry_at` computed with **exponential backoff**:
   `min(SYNC_BACKOFF_MAX_MS, SYNC_BACKOFF_BASE_MS × 2^attempts)`,
   default base 2 seconds, capped at 5 minutes — so a brief blip
   retries almost immediately, while a sustained outage backs off to a
   gentle 5-minute cadence instead of hammering a dead endpoint.
2. Rows stay `status = 'pending'` (and keep retrying forever) until
   `attempts` passes `SYNC_MAX_ATTEMPTS` (default 20), at which point
   they flip to `status = 'failed'` — still visible in
   `sync_status.pending_count`, still retried on the same backoff
   schedule, just flagged so an admin dashboard can distinguish "still
   trying" from "needs a human look."
3. Every attempt, successful or not, writes a `sync_log` row — full
   forensic trail of exactly what was tried, when, and what error came
   back.
4. **Partial-batch failure is handled per-row, not per-batch.** The
   Cloud receiver applies each row independently and reports back
   which `queue_id`s succeeded (`applied`) versus which it explicitly
   rejected (`rejected`, with a reason). One malformed row never blocks
   the other 99 rows in the same batch from landing.

## 12. Delta sync — only changed records (Requirement 14)

There is no periodic "compare and diff entire tables" step anywhere in
this design — that would be the wrong model for this workload. Instead:

- Every operational write enqueues **exactly the one row it changed**
  (not the whole table) at the moment it changes, via
  `enqueue_sync()`.
- The worker only ever queries `sync_queue WHERE status IN
  ('pending','failed')` — a small, indexed set, never a full-table
  scan of `sales`/`purchases`/etc.
- Coalescing (§6) further reduces "changed records" to "records with a
  *pending, not-yet-sent* change," so a record edited five times before
  the worker gets to it is still just one transmission.

This is what makes the design scale — the cost of syncing is
proportional to how much *changed*, never to how big the tables have
grown.

## 13. Multi-branch support (Requirement 15)

Every table involved already carries `branch_id`:

- `sync_queue`, `sync_log`, `sync_status` on Local — default `1`,
  configurable per Local Server install via `SYNC_BRANCH_ID` in
  `includes/sync-config.php`.
- Every *synced* table on Cloud gets a `branch_id` column and a
  `UNIQUE INDEX (branch_id, id)` added automatically the first time
  it's touched (`ensure_branch_column()` in `api/sync-receiver.php`).
  With one branch this behaves exactly like a plain `id` primary key
  (branch_id is always 1, so the composite key never collides with
  itself). The moment a second Local Server starts pushing with
  `branch_id = 2`, its rows — which will naturally have overlapping
  raw `id` values with branch 1's, since each Local Server has its own
  independent auto-increment — coexist safely on Cloud because the key
  is `(branch_id, id)`, not `id` alone.
- To bring a new branch online: install MediCore + this sync system on
  its Local Server, set a unique `SYNC_BRANCH_ID`/`SYNC_BRANCH_NAME` in
  its `includes/sync-config.php`, point `SYNC_CLOUD_URL` at the same
  Cloud receiver, and give it the same `SYNC_SHARED_SECRET`. No Cloud-side
  code change is required — the receiver is already generic across
  branches.

## 14. Scalability: 1 → 10 → 100 branches (Requirement 16)

| Scale | What's needed | What isn't |
|---|---|---|
| **1 branch** | Exactly what's built here: one worker, direct HTTPS push, `sync-receiver.php` writing straight to the Cloud MySQL database. Sub-minute (or sub-10-second in `--loop` mode) lag at low transaction volume. | Any message broker, load balancer, or queue infrastructure — pure overhead at this scale. |
| **10 branches** | Each branch runs its own independent `sync/worker.php` against its own Local database, all pushing to the same Cloud `sync-receiver.php` URL. `branch_id` already prevents any cross-branch data collision (§13). At this volume the Cloud web server and MySQL instance handle concurrent POSTs from 10 workers without issue — this is a modest write load for a single reasonably-sized cloud VM/MySQL instance. Worth adding: a Cloud-side admin view over `sync_status`-equivalent data (or simply querying each branch's `sync_log`) to monitor which branches are current vs. lagging. | Still no broker needed — direct HTTP-to-DB-write remains simple and fast enough. |
| **100 branches** | This is the point where synchronous "HTTP request writes straight to MySQL" starts to strain a single database's write capacity and a single web server's request-handling capacity under peak concurrent load (100 workers potentially pushing around the same moments, e.g. end-of-day). The recommended evolution: put a real **message queue** (e.g. AWS SQS, RabbitMQ, or a managed equivalent) in front of ingestion — `api/sync-receiver.php` (or its replacement) does the lightweight job of validating the signature and enqueuing the batch onto the broker, then immediately acks the Local worker; a pool of separate consumer workers drains the broker and applies rows to MySQL asynchronously, decoupled from the ingestion path and horizontally scalable independent of it. This is exactly the "Message Queue architecture" option from Requirement 10 — deliberately *not* adopted at launch because it isn't justified yet, but the design here (idempotent per-row apply, `sync_applied` version gate, `branch_id` isolation) carries over to that future architecture unchanged; only the transport between "received" and "applied" changes. | Rearchitecting the apply logic, the schema, or the Local-side worker at all — `upsert_row()`/`apply_one_row()` stay exactly as-is; they'd just be called by a queue consumer instead of directly inside the HTTP handler. |

The honest scaling story here is: **start simple, add the message
queue only when the numbers actually demand it** — not before. Nothing
in the schema or the Local-side code needs to change when that day
comes.

## 15. Security

- **Authentication:** every batch is HMAC-SHA256 signed with a shared
  secret (`SYNC_SHARED_SECRET` in `includes/sync-config.php`, set
  identically on both servers, never in a browser or a public repo).
  The receiver rejects any request whose signature doesn't match with
  a timing-safe comparison (`hash_equals()`).
- **Transport:** always deploy the Cloud receiver behind HTTPS/TLS —
  `sync/worker.php`'s cURL calls have `CURLOPT_SSL_VERIFYPEER` on.
  Never point `SYNC_CLOUD_URL` at a plain-HTTP endpoint in production.
- **No inbound access required from Cloud to Local** — the Local
  Server only ever makes outbound connections. This means a pharmacy's
  router/firewall needs zero inbound port-forwarding rules for sync to
  work, a meaningfully smaller attack surface than the alternative.
- **SQL injection:** `upsert_row()` validates `table_name` against a
  strict `^[a-zA-Z_][a-zA-Z0-9_]*$` pattern before interpolating it
  into SQL (table/column names can't be parameterized with placeholders
  in MySQL), and restricts applied columns to ones that actually exist
  on the Cloud table via `information_schema` — an unexpected or
  malicious column name in a payload is silently dropped, not executed.

## 16. Deployment — running the worker

1. **Local Server:** edit `includes/sync-config.php` — set
   `SYNC_CLOUD_URL` to the deployed Cloud receiver's URL and
   `SYNC_SHARED_SECRET` to a long random value
   (`php -r "echo bin2hex(random_bytes(32));"`).
2. **Cloud Server:** deploy the same codebase (or at minimum
   `api/sync-receiver.php`, `includes/db.php`, `includes/sync-config.php`)
   with the **same** `SYNC_SHARED_SECRET`, pointed at the Cloud MySQL
   database.
3. **Schedule the worker** on the Local Server (XAMPP on Windows has no
   native cron, hence Task Scheduler):
   - Program: `C:\xampp\php\php.exe`
   - Arguments: `C:\xampp\htdocs\medicore28\sync\worker.php`
   - Trigger: repeat every 1 minute, indefinitely
   - This single-pass mode is safe to let overlap between runs — every
     step in `sync/worker.php` is idempotent (see inline comments).
   - For lower latency than Task Scheduler's 1-minute floor, run it
     instead as a persistent process (`php sync/worker.php --loop
     --verbose`, ideally wrapped as a Windows service via NSSM) — it
     sleeps 5 seconds between cycles. This is a single outbound HTTP
     client on a timer, not a held-open connection, so — unlike this
     app's earlier SSE experiment — it does not risk exhausting
     Apache's worker-thread pool; it isn't an Apache request at all.
4. **Verify:** check `sync_status` on Local (`is_online = 1`,
   `pending_count` trending to 0) and `sync_log` on both ends after a
   test transaction.

## 17. Files delivered

| File | Role |
|---|---|
| `includes/db.php` | `ensure_sync_tables()` + `enqueue_sync()` — the write-side outbox helper (added alongside the existing `stock_events` helpers). |
| `includes/sync-config.php` | Branch identity, Cloud URL, shared secret, tuning constants. Same file is deployed (with matching secret) on both servers. |
| `sync/worker.php` | CLI-only script that drains `sync_queue` and pushes to Cloud. Not reachable over HTTP. |
| `api/sync-receiver.php` | Deployed on the **Cloud Server**. Verifies signatures, applies rows idempotently, reports back per-row success/failure. |
| `api/sales.php`, `api/purchases.php`, `api/sale-returns.php`, `api/purchase-returns.php`, `api/cash.php`, `api/accounts.php`, `api/advance.php` | Existing operational endpoints, each now calling `enqueue_sync()` inside its existing transactions. |

## 18. What this does *not* do (by design)

- It does not sync `users`, `sessions`, `settings`, or any
  configuration/identity table — those are meant to be configured
  independently per server, not overwritten from Local.
- It does not let Cloud write back to Local in any direction — Cloud
  is strictly a downstream mirror, consistent with its stated role
  (remote monitoring, reporting, owner access, backup visibility).
- It does not attempt sub-second cross-branch consistency — that's
  what the existing intra-LAN real-time stock sync
  (`REALTIME_STOCK_SYNC.md`) is for, on the Local Server, between
  terminals that actually need it for POS decisions.
