Architecture & Data Model

<aside> 🧠

One catalog drives everything. database/catalog/model.mjs defines every entity once; it generates the SQL schema, the data dictionary, the runtime Zod validation and the frontend Data Register forms.

</aside>

<aside> 🗄️

PostgreSQL 16

63 tables · NUMERIC money · SQL views for totals

</aside>

<aside> ⚙️

Express 5 API

Zod-validated · role-gated · audited

</aside>

<aside> 🖥️

React 19 SPA

Vite · MSAL · workshop planner UI

</aside>

🗺️ System overview

flowchart LR
    User["Browser (React 19 SPA)"] -->|"HTTPS + Entra token"| App["Express 5 API<br>server/index.mjs"]
    App -->|"restricted role / TLS"| PG["PostgreSQL 16"]
    App -->|"managed identity"| Blob["Azure Blob<br>(local disk in dev)"]
    App -.->|"MYOB_ENABLED only"| MYOB["MYOB AccountRight"]
    Catalog["catalog/model.mjs"] --> SQL["001_native.sql"]
    Catalog --> Zod["Zod schemas"]
    Catalog --> UI["/api/model → Data Register"]

🔗 Core entity relationships

Show ER diagram (overview, not all 233 relationships)

📚 Reference

API layers

Endpoint	Purpose
`/api/entities/:kind`	Generic native CRUD for all 62 non-file collections (paged, max 1,000/request)
`/api/records/:kind`	Workflow view models; PATCH is the normal write path
`/api/actions/:kind/:id/:action`	Guarded lifecycle transitions: quote accept, PO/receipt, delivery sign-off, invoice status
`/api/bootstrap`	Workshop overview: newest 1,000 records per kind + settings, reference names, audit
`/api/config`	Unauthenticated: auth mode, Entra IDs, demo flag
`/api/projects/:id/identifiers`	External aliases; only Admin/Director verify
`/api/integrations/myob/*`	MYOB status, connect, outbox, reconcile, CSV export

Design rules

🆔 Permanent project identity: one Project UUID from quoting onward; accepting a quote opens its single Job
💲 Money: NUMERIC; totals in SQL views (quote_totals, invoice_balances, purchase_order_totals)
🔥 Traceability: requirement → PO line → receipt → heat cert → stock lot → nest → project
🚫 QA gates dispatch: failed QA or open NCRs block delivery
🧾 Delivery → invoice allocations with quantity/value limits and issue lock
🔁 Concurrency: optimistic version (409), row locks, idempotency keys
📜 Audit: DB triggers capture before/after + actor; runtime can't touch it
🗑️ No deletes: closure via status; only draft lines are deletable

Migrations

#	Adds
001	Generated catalog schema (63 tables)
002–005	Integrity triggers, history, allocation limits, commercial history
006–007	`app_settings`: GST, currency, timezone
008	`workflow_events`
009	PDF documents, issue-time snapshots
010	Import rows, exceptions, external aliases
011	MYOB outbox, OAuth state, events

<aside> 🛑

Never edit an applied migration. Add a new numbered file. Checksums are verified at startup.

</aside>

Access model

Role	Typical write access	💲 Values
Admin / Director	All operations	Yes
Sales	Customers, enquiries, quotes, estimates, variations	Yes
ProjectManager	Enquiries, projects, drawings, workshop, receipts, delivery	No
Workshop	Tasks/time, receipts, nests, delivery	No
Quality	Quality checks	No
Finance	Purchase orders, invoices	Yes

Proposed policy; validate with owners (task P06).