# MongoDB → MySQL/MariaDB Port — Technical Notes

This backend was ported from MongoDB (Mongoose 8) to MySQL/MariaDB without
rewriting the application code. All ~21,000 lines of controllers, services
and helpers are unchanged except for import paths; a Mongoose-compatible
data layer at `src/shared/db/` provides the same API on top of MySQL.

## Architecture

```
controllers / services (unchanged)
        │  Mongoose API: Model.find/aggregate/populate/save/...
        ▼
src/shared/db/            the compatibility layer
  mongoose.js             drop-in module: Schema, model(), Types, connect()
  schema.js               schema parsing, casting, defaults, validators
  model.js                statics, SQL read/write, transactions, pushdown
  document.js             Document instances, hooks, DocumentArrays, Maps
  query.js / populate.js  chainable queries, populate (ref/refPath/virtual)
  aggregate.js            aggregation pipelines (incl. $lookup)
  update-ops.js           $set/$inc/$push/... and pipeline updates
  ddl.js / connection.js  table DDL, mysql2 pool, connection facade
        │  exact MongoDB query semantics via `mingo`
        ▼
MySQL / MariaDB
```

**Storage model** — one InnoDB table per collection:

| column | purpose |
|---|---|
| `_seq` | auto-increment; preserves insertion ("natural") order |
| `_id` | `CHAR(24)` — same 24-hex ids as MongoDB (string `_id`s use `VARCHAR(191)`) |
| `doc` | the full document as JSON — the source of truth (dates stored as `{"$date": ...}` markers) |
| one column per top-level scalar field | typed mirrors (`TEXT`/`DOUBLE`/`DATETIME(3)`/`TINYINT(1)`/`CHAR(24)`), maintained on every write — for phpMyAdmin readability, real `UNIQUE` constraints and indexed `WHERE` narrowing |

Reads always parse `doc` and run the exact MongoDB filter through
[mingo](https://github.com/kofrasa/mingo); the SQL `WHERE` derived from the
filter is only ever a *superset* prefilter, so MySQL collation etc. can never
change results. Aggregation pipelines (including `$lookup`,
`$dateToString` with timezones, pipeline-form `$lookup` with `let`/`$expr`)
run through mingo over SQL-prefiltered documents.

**Writes**: `Document.save()` diffs the document against its loaded state and
updates only the changed top-level fields (like mongoose's modified-path
`$set`), so concurrent saves touching different fields merge; saves that
modify array paths carry a `__v` version guard and the loser of a concurrent
race gets a `VersionError` instead of silently clobbering (mongoose parity —
prevents e.g. double-refunding a bill). Atomic operations (`findOneAndUpdate`
counters, `$inc`, upserts) run inside transactions with `SELECT ... FOR
UPDATE` row locks and automatic deadlock/duplicate retry — bill/invoice/
session numbers are safe under concurrent cashiers.

Tables are created and upgraded automatically at startup
(`schema.sql` export available via `node src/scripts/exportSchemaSql.js`).

## Verification performed

- 42-check data-layer suite + permanent self-test
  (`DB_SELFTEST_URI=... node src/scripts/dbLayerSelfTest.js`)
- The app's own integration-test module (`/api/v1/tests/integration/modules`):
  13/15 modules pass all CRUD ops; the 2 failing modules
  (drawer-sessions, medicines) fail **identically on the original MongoDB
  build** (stale test payloads that don't match the current models).
- Side-by-side equivalence: original backend on MongoDB vs this backend on
  MySQL, seeded with byte-identical data, compared across 44 GET endpoints —
  34 byte-identical, 8 identical content with different array order,
  2 differing only in which *tied-sort* rows land on page 1 (ordering that
  MongoDB itself does not guarantee; full result sets verified identical).
- Write flows (bill create, full refund incl. `__v` bump and refund history,
  duplicate/validation error shapes, invalid-id behavior) produce
  field-identical responses on both stacks.
- Backup export → import round-trip across all 29 models.
- Jest: identical results to the original (124 passed; the 10 failures are
  pre-existing and fail the same way on the MongoDB build).

## Known, intentional divergences

1. **`__v` (version key)** is stored, incremented on array-modifying saves,
   and used as an optimistic-concurrency guard there (VersionError on
   conflict) — but not bumped on every internal path mongoose would.
   Nothing in the app or frontend reads `__v` beyond its presence.
2. **Tie ordering**: when documents share identical sort-key values, page
   boundaries may differ from the Mongo build (unspecified in MongoDB too).
3. **Aggregations now match string ids**: mongoose never cast values inside
   `.aggregate()` pipelines, so a `$match` written with a plain string id
   silently matched nothing on MongoDB unless the developer remembered
   `new Types.ObjectId(...)`. In this port ids are strings everywhere, so
   such `$match`es now find their rows. Sums that silently showed 0 on
   Mongo due to this developer footgun now show correct values.
4. **Sparse unique + explicit null**: MongoDB indexes an explicit `null`
   (second `null` collides); MySQL allows multiple `NULL`s. Missing fields
   behave identically.
5. **Unique string columns** are `VARCHAR(191) BINARY` — values longer than
   191 chars would collide on their prefix (no such values exist in this app).
6. **Sessions/transactions passed by controllers are no-ops** — same as the
   original, which ran standalone MongoDB where transactions were unavailable.
7. Data should be edited through the app; hand-editing mirror columns in
   phpMyAdmin does not update `doc` (mirrors are derived data).
8. Subdocument `updatedAt` (medicine batches) is bumped when an element
   changes in a `save()` diff, but not on every mongoose-internal path.
9. The correlated pipeline-form `$lookup` in analytics runs in Node memory
   over SQL-prefiltered documents; on very large datasets that endpoint is
   heavier than a server-side Mongo join (results are identical).

## Operational notes

- Env: `DATABASE_LOCAL=mysql://user:pass@host:3306/db` **or**
  `DB_HOST`/`DB_PORT`/`DB_USER`/`DB_PASSWORD`/`DB_NAME`. Pool size via
  `DB_POOL_SIZE` (default 5). `DB_PUSHDOWN=off` disables SQL prefiltering
  (debug escape hatch).
- Migration from a live MongoDB: `src/scripts/migrateMongoToMysql.js`
  (see DEPLOY-CPANEL.md), or the app's backup export/import.
- Works on MySQL 5.7+/8.x/9.x and MariaDB 10.2+ (JSON column, DATETIME(3),
  utf8mb4).