# MediCore — Real-Time Stock Synchronization

How stock changes on one terminal reach every other open terminal without a
page reload, and why this app uses fast cursor-based polling rather than
WebSockets or Server-Sent Events.

## 1. Recommendation: fast cursor-based polling, not WebSockets/SSE

The brief asked for a push-based architecture and named WebSockets, SSE, and
long polling as the candidates. This implementation deliberately picks a
fourth option — **short, indexed, cursor-based polling** — for a reason
specific to this deployment, not a generic preference.

**SSE was already tried in this exact codebase and reverted.** `api/status.php`
still contains a working SSE stream (bounded to 55 seconds, auto-reconnecting)
that used to power the top status bar's live notification counts. It was
switched back to plain polling because XAMPP's default Windows Apache build
has a small worker-thread pool, and every open browser tab holding one PHP
request open — even a *bounded, auto-reconnecting* SSE stream — consumes an
Apache worker essentially continuously (the moment one connection closes,
`EventSource` reconnects immediately). With several tabs open across a normal
working session, that pool can run out, and **unrelated requests anywhere else
in the app** then queue for a free worker and appear to hang indefinitely with
no error message. For a pharmacy POS this is worse than the stale-stock
problem it would be fixing — a stuck sale screen mid-transaction is a much
bigger deal than a stock count that's 2 seconds old.

WebSockets have the same fundamental issue on this stack (Apache/mod_php
has no first-class WebSocket support; a real WS server means a second,
separate persistent process — e.g. Node.js or PHP Ratchet/Swoole — running
alongside XAMPP, with its own port, its own Windows auto-start entry, and its
own crash recovery). That's a legitimate long-term architecture and is
documented as an upgrade path in §8, but it's new infrastructure to install
and keep running, not a same-day fix.

**What this implementation does instead:** every stock-mutating write now
publishes a row to a new `stock_events` table (an event outbox) inside the
same database transaction as the stock change. The sync endpoint
(`api/stock-sync.php`) doesn't scan the `products` table anymore — it does a
trivial indexed `WHERE id > ?` range read against `stock_events`. That's so
cheap that terminals can poll every **~600ms** instead of the previous 2.5s
without meaningfully increasing database load, because a poll that finds
nothing new costs almost nothing, and a poll that finds something returns it
immediately. In practice this delivers "another terminal's sale shows up in
well under a second," which is what the brief actually needs, without
reintroducing a failure mode this app already hit once.

If sub-200ms latency or true instant push later becomes a hard requirement,
§8 describes the WebSocket upgrade path — the `stock_events` table and
`publish_stock_event()` call sites built here are exactly what a WebSocket
broadcaster would consume too, so nothing here is wasted by choosing polling
first.

## 2. Architecture diagram

```
 ┌─────────────┐   sale/purchase/return    ┌──────────────────┐
 │  Terminal 1 │ ────────────────────────▶ │   api/*.php       │
 │  (browser)  │                           │  (sales.php etc.) │
 └─────────────┘                           └─────────┬─────────┘
                                                       │ ONE transaction
                                                       ▼
                                            ┌─────────────────────┐
                                            │ 1. UPDATE products   │
                                            │    SET stock_pcs=... │
                                            │    (atomic, guarded) │
                                            │ 2. INSERT INTO       │
                                            │    stock_events      │
                                            │ 3. COMMIT             │
                                            └─────────┬─────────────┘
                                                       │
                       ┌───────────────────────────────┼───────────────────────────────┐
                       ▼                                ▼                                ▼
             ┌──────────────────┐            ┌──────────────────┐            ┌──────────────────┐
             │ Terminal 1        │            │ Terminal 2        │            │ Terminal 3        │
             │ stock-sync.js     │            │ stock-sync.js     │            │ stock-sync.js     │
             │ polls every 600ms │            │ polls every 600ms │            │ polls every 600ms │
             │ GET stock-sync.php│            │ GET stock-sync.php│            │ GET stock-sync.php│
             │  ?since_id=N      │            │  ?since_id=N      │            │  ?since_id=N      │
             └─────────┬─────────┘            └─────────┬─────────┘            └─────────┬─────────┘
                       │  WHERE id > N (indexed, cheap)   │                                │
                       ▼                                ▼                                ▼
             ┌───────────────────────────────────────────────────────────────────────────────┐
             │                          stock_events (MySQL, InnoDB)                            │
             │  id  product_id  event_type  qty_delta  new_stock  source_type  source_id  ...  │
             └───────────────────────────────────────────────────────────────────────────────┘
                       │                                │                                │
                       ▼                                ▼                                ▼
             window.dispatchEvent(          window.dispatchEvent(          window.dispatchEvent(
               'medi:stock-sync')             'medi:stock-sync')             'medi:stock-sync')
                       │                                │                                │
        ┌──────────────┼──────────────┐                 │                                │
        ▼              ▼              ▼                 ▼                                ▼
  Product List   Sale Entry cart   Dashboard KPIs   (same 3 listeners, every open tab, automatically)
  [data-stock-id] warning label    + widgets
  DOM patch       recalculated     rebuilt
```

**Workflow, step by step, for "T1 sells 5 units of Product A":**

1. T1's browser POSTs the sale to `api/sales.php`.
2. Inside one DB transaction: the sale/sale_items rows are inserted, stock is
   decremented with an atomic, conditionally-guarded `UPDATE` (§7), and
   `publish_stock_event()` inserts one row into `stock_events` with the
   resulting `new_stock` value. The transaction commits — stock change and
   event publication succeed or fail together; a rolled-back sale can never
   leave a phantom event behind.
3. Within at most ~600ms, T2 and T3's `stock-sync.js` polls run
   `SELECT ... FROM stock_events WHERE id > <their last-seen id>`, get back
   the new row, and advance their cursor.
4. Each terminal patches every `[data-stock-id="A"]` element on the page
   directly (Product List's stock column, any open Sale Entry search
   results), then dispatches `medi:stock-sync` so page-specific logic
   (dashboard widgets, the cart's "selling beyond stock" check) can react.
5. No terminal ever re-fetches stock it's already seen, and no terminal can
   see the same event twice — the monotonic cursor makes both structurally
   impossible (§9).

## 3. What publishes events today

| Source | File | Event type | Direction |
|---|---|---|---|
| Sale | `api/sales.php` (`api_save`) | `sale` | stock down |
| Sale deleted (admin) | `api/sales.php` (`api_delete`) | `sale_delete` | stock restored up |
| Purchase received | `api/purchases.php` (`api_add`, when not pending approval) | `purchase` | stock up |
| Purchase approved | `api/purchases.php` (`api_approve`) | `purchase` | stock up |
| Purchase deleted/rejected (was applied) | `api/purchases.php` (`api_delete`) | `purchase_reversal` | stock down |
| Sale return | `api/sale-returns.php` | `sale_return` | stock up |
| Purchase return | `api/purchase-returns.php` | `purchase_return` | stock down |

**Stock Adjustments and Stock Transfers** (brief requirement #6) don't exist
as features in this codebase today — there's no adjustment or transfer screen
to wire up. The pattern is ready for them the moment they're built: any new
endpoint that changes `products.stock_pcs` just needs to call
`publish_stock_event($productId, '<event_type>', $delta, $newStock,
'<source_type>', $sourceId)` inside its existing transaction, immediately
after its stock `UPDATE` — that's the entire integration, nothing else in the
pipeline needs to know a new source exists.

## 4. Database changes

```sql
CREATE TABLE stock_events (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id   INT UNSIGNED NOT NULL,
    event_type   VARCHAR(30)  NOT NULL,   -- 'sale' | 'sale_delete' | 'purchase' | 'purchase_reversal' | 'sale_return' | 'purchase_return'
    qty_delta    INT          NOT NULL,   -- signed: negative = decrease, positive = increase
    new_stock    INT          NOT NULL,   -- resulting stock_pcs right after this change
    source_type  VARCHAR(30)  NOT NULL,   -- 'sale' | 'purchase' | 'sale_return' | 'purchase_return'
    source_id    INT UNSIGNED DEFAULT NULL,
    created_by   INT UNSIGNED DEFAULT NULL,
    created_at   TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

Created automatically on first use via `ensure_stock_events_table()` in
`includes/db.php` — same idempotent, self-installing migration convention
already used throughout this codebase, no manual SQL to run.

## 5. `publish_stock_event()`

```php
function publish_stock_event(
    int $productId, string $eventType, int $qtyDelta, int $newStock,
    string $sourceType, ?int $sourceId = null
): void {
    ensure_stock_events_table();
    db_run(
        "INSERT INTO stock_events
            (product_id, event_type, qty_delta, new_stock, source_type, source_id, created_by)
         VALUES (?,?,?,?,?,?,?)",
        [$productId, $eventType, $qtyDelta, $newStock, $sourceType, $sourceId,
         current_user_id() ?: null]
    );
}
```

Always called from inside the caller's already-open transaction, right after
the `products` UPDATE it corresponds to. Because our stock writes are
relative deltas (`stock_pcs = stock_pcs - ?`), `new_stock` is captured with a
follow-up `SELECT stock_pcs FROM products WHERE id = ?` on the same
connection/transaction — safe to read back because the preceding `UPDATE`
already holds that row's exclusive lock until `COMMIT`, so nothing else can
interleave a change in between.

## 6. The poll endpoint (`api/stock-sync.php`)

```php
$sinceId = (int) ($_GET['since_id'] ?? 0);   // client's last-seen cursor

$rows = db_rows(
    "SELECT se.id, se.product_id, se.event_type, se.source_type, se.new_stock,
            p.name, p.low_stock_alert, p.sale_price
       FROM stock_events se
       JOIN products p ON p.id = se.product_id
      WHERE se.id > ?
      ORDER BY se.id ASC
      LIMIT 500",
    [$sinceId]
);
// ... collapse to one row per product (latest wins), track max id seen,
// return { since_id, changed: [...], low_stock, out_of_stock, stock_value }
```

- `since_id` is the entire cursor — no timestamps, no clock-skew risk between
  terminals, no "same-second collision" ambiguity.
- A brand-new page load jumps straight to "now" (`MAX(id)` of `stock_events`)
  instead of replaying the table's full history.
- Aggregate KPIs (low-stock count, out-of-stock count, total stock value) are
  only recomputed when the batch actually contains a change — a poll that
  finds nothing new costs one indexed lookup and nothing else, so 10 terminals
  polling every 600ms is roughly 17 queries/second total, all trivial.
- `session_write_close()` runs immediately after `require_login()`, same
  convention used by every other frequently-polled endpoint in this app —
  without it, PHP's file-based session lock would serialize every other
  request from the same browser behind this one.

## 7. Locking strategy — preventing oversell across terminals (brief #12)

Every stock write in this app was already wrapped in an explicit
`beginTransaction()`/`commit()`/`rollBack()` and used a **relative** update
(`stock_pcs = stock_pcs ± ?`, never a read-then-recompute-then-write), which
is the right foundation — InnoDB takes an exclusive row lock on `UPDATE`
automatically, so two concurrent updates to the same product's row are
already serialized correctly at the storage-engine level.

There was one real gap, found while wiring this up: when "Allow Negative
Stock Sale" is **off**, the insufficient-stock check ran as a `SELECT`
*before* the transaction started — purely advisory. It could not prevent two
terminals both reading "5 in stock, sale of 5 OK" a moment apart, both
proceeding, and the actual write (`GREATEST(0, stock_pcs - 5)`) silently
clamping to zero and letting *both* sales complete — a real oversell, exactly
the concurrent-terminal scenario in requirement #12.

Fixed by making the write itself the enforcement point:

```sql
UPDATE products
   SET stock_pcs = stock_pcs - ?,
       updated_at = NOW()
 WHERE id = ? AND stock_pcs >= ?
```

If two terminals race to sell the last few units, only one `UPDATE` can
match and affect a row (InnoDB serializes them via the row lock either way);
the other affects **0 rows**, and that request rolls back its whole
transaction and returns a proper "Insufficient stock" error instead of
completing an oversold sale. Applied to both stock-decrementing paths that
had this gap: `api/sales.php` (sale creation, when negative-stock sale is
off) and `api/purchase-returns.php` (returning stock to a supplier, which has
no "allow negative" override at all — it's always enforced).

When negative-stock sale **is** enabled, the original unconditional
`GREATEST(0, stock_pcs - ?)` behavior is unchanged by design — that setting's
whole purpose is to permit exactly this.

MySQL's default `REPEATABLE READ` isolation level is left as-is; nothing here
depends on changing it, since the guard lives in the `UPDATE`'s `WHERE`
clause rather than in an application-level read-check-write sequence.

## 8. Reconnection, catch-up, and duplicate prevention (brief #9, #10)

- **Duplicate prevention**: structural, not best-effort. A terminal only ever
  asks for `id > last_seen_id`; once an id has been returned, it can never be
  returned again to that terminal, because the cursor only moves forward.
- **Reconnection**: if a terminal's network drops mid-session, the next
  successful poll simply resumes from wherever `sinceId` was left — nothing
  needs to detect "we were offline" or trigger a special recovery path,
  because polling never assumed a connection was continuously open in the
  first place. This is a structural advantage over SSE/WebSockets here,
  which both need explicit reconnect-and-catch-up logic; polling's "catch-up"
  *is* just its normal steady state.
- A terminal that was closed and reopened later starts with an empty
  `since_id` and gets jumped to "now" rather than replaying potentially
  thousands of historical events (see §6).

## 9. Updating the UI without a page reload (brief #11)

All four surfaces already listen for `medi:stock-sync` — the event contract
was not changed, so **none of them needed any code changes**:

- **Product List** (`product/list.php`): patches `stock_pcs`/`sale_price` in
  the currently-loaded page's rows in place, recomputes the
  low/out-of-stock row styling.
- **Sale Entry** (`sale/entry.php`): updates any cart line's `max_stock` (used
  for the "selling beyond stock" warning) and live price, without touching
  the price already locked in for that sale.
- **Dashboard** (`dashboard.php`): rebuilds the low-stock/out-of-stock widget
  cards and the stock-value KPI text from `lowStockCount`/`outOfStockCount`/
  `stockValue`.
- **Any element** anywhere tagged `data-stock-id="<id>"` or
  `data-price-id="<id>"` — e.g. a plain table cell — is patched automatically
  by `stock-sync.js` itself with a brief highlight flash, with zero
  JavaScript required on that page at all.

**Inventory Reports** (`accounting/reports-inventory.php`) does not currently
listen for `medi:stock-sync` — those are point-in-time report tables, not a
live dashboard, and re-rendering a large report table on every stock tick
isn't the right UX for a report someone is actively reading/exporting. If
live updates are wanted there too, the same one-line pattern used everywhere
else applies: add a `window.addEventListener('medi:stock-sync', ...)` block
that re-runs whatever row-filter/recompute logic the report already has.

## 10. Performance for 10 terminals (brief #13)

- Steady state: 10 terminals × ~1.67 polls/sec (600ms interval) ≈ 17
  requests/sec against `api/stock-sync.php`. Each poll's DB work is a single
  indexed `stock_events.id > ?` range scan (near-zero cost when there's
  nothing new, which is the overwhelming majority of polls in a real
  pharmacy's transaction rate).
- Each request is short-lived — connects, queries, responds, releases its
  Apache worker, done. No persistent connections are held at any point, so
  there's no analogue to the worker-pool-exhaustion incident this app already
  had with SSE (§1), regardless of how many browser tabs happen to be open.
- Aggregate KPI queries (`COUNT`/`SUM` over `products`) only run when a poll
  actually finds a change, not on every cycle — so idle periods cost only the
  cheap cursor check, and busy periods (the only time the aggregates would
  meaningfully differ anyway) are exactly when they're recomputed.
- `stock_events` is a narrow, append-only, indexed table — at even a few
  thousand transactions/day this stays small; it's a candidate for a periodic
  cleanup job (e.g. delete rows older than 7 days) if it's ever left
  completely unpruned for years, but nothing in this design requires that
  maintenance to happen soon.

## 11. If sub-200ms/true push is needed later: WebSocket upgrade path

Not built now (new persistent-process infrastructure is a bigger decision
than this pass), but the groundwork here makes it a additive change, not a
rewrite:

1. Run a small standalone WebSocket server (Node.js + `ws`, or PHP
   Ratchet/Swoole) alongside XAMPP on its own port, set to auto-start with
   Windows.
2. It polls `stock_events` itself (or is notified via a lightweight internal
   HTTP call from `publish_stock_event()`) and broadcasts new rows to every
   connected client — no Apache worker is ever involved, so the original
   worker-exhaustion risk doesn't apply to this path at all.
3. Swap `assets/stock-sync.js`'s `fetch()` polling loop for a `WebSocket`
   connection that dispatches the same `medi:stock-sync` CustomEvent — every
   consumer (`dashboard.php`, `sale/entry.php`, `product/list.php`) keeps
   working unmodified, exactly as it did when polling replaced the old
   timestamp-scan approach in this pass.

## 12. Files changed in this pass

| File | Change |
|---|---|
| `includes/db.php` | `ensure_stock_events_table()`, `publish_stock_event()` |
| `api/sales.php` | Atomic conditional stock decrement (oversell fix) + event publishing (sale, sale_delete) |
| `api/purchases.php` | Event publishing (purchase, purchase_reversal) |
| `api/sale-returns.php` | Event publishing (sale_return) |
| `api/purchase-returns.php` | Atomic conditional stock decrement (oversell fix) + event publishing (purchase_return) |
| `api/stock-sync.php` | Rewritten: cursor-based (`stock_events.id`) instead of timestamp-scan (`products.updated_at`) |
| `assets/stock-sync.js` | Rewritten: 600ms interval, `since_id` cursor, same `medi:stock-sync` event contract |
