# PostgreSQL → MySQL 8 translation

The Database Architecture document remains authoritative for **intent**. This file records every PostgreSQL-origin constraint that has no identical MySQL equivalent, the MySQL enforcement used instead, and the automated test that proves it.

Minimum engine: **MySQL 8.0.16** (CHECK constraints enforced). Runtime here: InnoDB, `utf8mb4`, `utf8mb4_0900_ai_ci` table default, `READ COMMITTED` on business transactions.

## Type map

| PostgreSQL | MySQL 8 | Notes |
|---|---|---|
| `bigint GENERATED ALWAYS AS IDENTITY` | `BIGINT UNSIGNED AUTO_INCREMENT` | Surrogate keys |
| `timestamptz` | `DATETIME(6)` | Stored UTC only. Display conversion is application-side (UX-DEC-005). Test: `testDatetimeMicrosecondColumn` |
| `boolean` | `TINYINT(1)` + `CHECK (col IN (0, 1))` | Test: `testCheckConstraintOnCompanyActive` |
| `jsonb` | `JSON` | MySQL validates JSON text. No GIN / jsonb operators. Test: `testJsonColumnAcceptsObjectAndRejectsInvalid` |
| `numeric` / `decimal` | `DECIMAL(p,s)` | Application values are canonical decimal **strings** via BCMath. Never PHP float. Test: `testDecimalScaleIsNotFloat`, `DecimalTest` |
| `interval` (shelf life) | `DECIMAL` value + explicit unit column | Phase 1 tables. Not installed in Phase 0 |
| `text` unique (case-sensitive) | `VARCHAR(...) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs` | Test: `testCaseSensitiveUniqueCompanyName` |
| `citext` | Not used | If a later PG object is citext, use `utf8mb4_0900_ai_ci` on that column and add an equivalence test before shipping |

## Constraint map

| PostgreSQL | MySQL mechanism | Equivalence test |
|---|---|---|
| `UNIQUE (vehicle_id) WHERE assigned_until IS NULL` (U1) | Stored generated column + UNIQUE | `testGeneratedColumnPartialUniqueVehicleAssignment` |
| `UNIQUE (challan_id) WHERE link_status IN ('DRAFT_RESERVED','FINAL_ACTIVE')` (U2) | Stored generated column + UNIQUE | `testGeneratedColumnPartialUniqueActiveBillingLink` |
| Partial unique for active rates with nullable company (U5) | Stored generated `CONCAT` scope + UNIQUE (NULL generated value allows many inactive rows) | `testNullableCompanyActiveRateScope` |
| `UNIQUE (user_id, company_id) WHERE plant_id IS NULL` | Stored generated `(all_plants_key, all_plants_company_key)` + UNIQUE. MySQL UNIQUE allows multiple NULLs, so the triple unique on `(user_id, company_id, plant_id)` is **not** sufficient | `testAllPlantsGrantPartialUnique` |
| Table CHECK | Native `CHECK` (8.0.16+) | `testCheckConstraintOnCompanyActive`, `testCheckConstraintFinancialYearDates` |
| `EXCLUDE` / GiST | No equivalent. Use generated UNIQUE and/or service validation. Document before adding | None in Phase 0 schema |
| Deferrable constraints | Not supported. Enforce in the same InnoDB transaction with ordered `FOR UPDATE` | G1–G3 policy below |
| Constraint triggers on the same table | **Forbidden** (error 1442). Do not emulate G1–G3 with MySQL triggers | `testSameTableTriggerIsRejected` |
| `SELECT … FOR UPDATE SKIP LOCKED` | Native | `testSelectForUpdateSkipLockedSyntax` |
| `SET TRANSACTION ISOLATION LEVEL READ COMMITTED` | `TransactionManager` sets session isolation per business transaction | `testReadCommittedSession` |
| Append-only `audit_event` | No native append-only table. Application never UPDATE/DELETE; privileges on cPanel/VPS should grant INSERT+SELECT only to the app user in production | Documented; Phase 0 app writer is insert-only |
| `LISTEN/NOTIFY` | Not used. Spark + cron replaces queues | N/A |

## Aggregate invariants G1–G3 and MLB-1

PostgreSQL same-table constraint triggers cannot be copied to MySQL.

Established meanings (do not reuse these ids for other invariants):

- **G1** — cumulative credit-note quantity/value
- **G2** — cumulative disposition
- **G3** — discount/calculation cross-row
- **MLB-1** — `material_lot_balance` projection (new id; not G1)

Authoritative register: `docs/PHASE1_PLAN.md` §5.

**Approved Phase 0 approach (no projection tables yet):**

1. One business transaction at `READ COMMITTED`.
2. Deterministic row-lock ordering (`FOR UPDATE` in primary-key / natural-key order).
3. Service/domain validation of the invariant before commit.
4. Concurrency tests in the phase that introduces the aggregate (Phase 1+).
5. Deadlock / lock-wait retry only when the caller marks the boundary **idempotent** (`TransactionManager`, max 3).

Projection tables are allowed later where they give real database-level enforcement. Each projection and its invariant must be documented in `docs/PROJECTION_TABLES.md` **before** the migration is written.

## Intentionally not installed in Phase 0

Lot, challan, invoice, IRN attempt, and shelf-life tables belong to later phases. Their PG constraints are listed here so they are not dropped on arrival:

- Partial uniques U1/U2/U5 — pattern proven in Phase 0 tests; apply on those tables when created.
- Shelf life — `DECIMAL` + unit, never `INTERVAL` or PHP float.
- IRN — append-only `irn_attempt` only; **no** queue, cron, or automatic retry.
- G1–G3 and MLB-1 — transactions + ordered locks + validation (+ projection if documented). Do not install until Phase 1 is approved.