# mcp-znet — Architecture

Read-only MCP server and Catalog Data Layer for Znet's Magento 2 store.
Scope source: *Magento Integration — MCP Server & Catalog Data Layer* (review call 3 Oct 2026).

Decisions so far:

| Decision | Choice | Status |
|---|---|---|
| Language/runtime | Node.js 22 LTS + TypeScript | **Decided** (8 Oct 2026) |
| Phase 1 access | Read-only (Magento integration has read ACLs only) | Decided |
| AI client | ChatGPT (custom connector / developer mode) | From Znet call |
| Users | Internal Znet team | From Znet call |
| Order/customer data visibility | Znet asked for "ALL" — see [§7 PII](#7-personal-data-pii) | **Open — needs sign-off** |
| Staff sign-in (ChatGPT → MCP) | **Magento admin login** (username + password + 2FA) via built-in OAuth 2.1 server | **Decided** (8 Oct 2026) |
| MCP → Magento auth | Magento Integration, **OAuth 1.0a** signed requests | **Decided** |
| Hosting | Nexcess (ticket raised) or Lightsail/Render/Fly | **Open** |
| Store views / languages, catalog size, freshness | — | **Open — asked Znet** |

---

## 1. System overview

```
                        ┌──────────────────────── mcp-znet host (Linux, 1–2 vCPU / 2–4 GB) ───────────────────────┐
                        │                                                                                           │
  Magento 2 (Nexcess)   │   apps/sync  ── pg-boss schedules ──┐                                                     │
  ┌───────────────┐     │   (Catalog Sync Service)            │                                                     │
  │ REST /V1/...  │◀────┼── pulls products, categories,       ▼                                                     │
  │ GraphQL       │     │   attributes, stock          ┌─────────────┐      ┌────────────────────┐                  │
  │ (source of    │     │                              │ PostgreSQL  │◀─────│ apps/catalog-api   │◀── feeds,        │
  │  truth)       │     │                              │ catalog.*   │      │ (Fastify, private) │    reporting,    │
  └───────────────┘     │                              │ ops.*       │      └────────────────────┘    future        │
          ▲             │                              └─────────────┘               ▲                integrations  │
          │ live orders,│                                     ▲ audit log            │ service token              │
          │ live stock  │                              ┌──────┴──────────────────────┴─┐                            │
          └─────────────┼──────────────────────────────│ apps/mcp-server               │                            │
                        │                              │ Streamable HTTP  /mcp         │                            │
                        │                              │ OAuth 2.1 resource server     │                            │
                        └──────────────────────────────┴───────────────▲───────────────┴────────────────────────────┘
                                                                       │ HTTPS (public), Bearer JWT
                                                              ChatGPT connector  ◀── OAuth ──▶  Identity Provider
```

**Magento is the only source of truth.** Nothing in this system writes to Magento in Phase 1 — the
Magento Integration we use is created with read-only ACL resources, so this is enforced by Magento,
not just by our code.

### Three processes, one database

| Process | Package | Exposure | Responsibility |
|---|---|---|---|
| Catalog Sync Service | `apps/sync` | none (outbound only) | Pull Magento → normalise → upsert into `catalog.*`. Runs scheduled jobs via pg-boss. |
| Catalog API | `apps/catalog-api` | **private** (localhost / VPC) | The only reader of `catalog.*`. Serves MCP, feeds, reporting. |
| MCP Server | `apps/mcp-server` | **public** HTTPS | MCP tools for ChatGPT. Catalog questions → Catalog API. Live order/stock questions → Magento. |

Shared libraries:

| Package | Purpose |
|---|---|
| `packages/shared` | env/config loading (zod), logger (pino), errors |
| `packages/magento` | Magento REST/GraphQL client: OAuth 1.0a or bearer auth, `searchCriteria` builder, pagination, retry/backoff, typed DTOs |
| `packages/catalog-db` | Drizzle schema, migrations, DB client, repositories (upserts, quality queries) |

Why three processes rather than one: the sync service can be restarted, backfilled or scaled
independently of the public MCP endpoint; the public process never holds DB write credentials for
the catalog schema; and feeds/reporting (later phases) reuse the Catalog API without touching MCP.
On a single small box they run as three `node` processes under Docker Compose or systemd.

---

## 2. Technology choices

| Concern | Choice | Why |
|---|---|---|
| Runtime | Node 22 LTS, TypeScript (strict, ESM) | Decided with Znet; MCP TS SDK is the reference implementation |
| Monorepo | pnpm workspaces + TS project references | Strict dep isolation, one lockfile (npm 10's resolver crashed on this tree) |
| Database | PostgreSQL 16 | Per scope doc. JSONB for long-tail attributes, `pg_trgm` for fuzzy search |
| ORM / migrations | Drizzle ORM + drizzle-kit | SQL-first, plain `.sql` migrations, no binary engine (avoids Prisma CLI issues we hit elsewhere) |
| Job scheduling | **pg-boss** (Postgres-backed queue) | No Redis to run on a 2–4 GB box; cron schedules, retries, singleton jobs, all in the same Postgres |
| Catalog API | Fastify 5 | Fast, schema-validated, small footprint |
| MCP | `@modelcontextprotocol/sdk` (Streamable HTTP, stateless) + Express 5 | ChatGPT requires Streamable HTTP; SDK auth helpers are Express middleware |
| Token validation | `jose` (JWKS) | Validates IdP-issued JWT access tokens |
| Search (later) | OpenSearch | Only if Postgres `pg_trgm` + full-text proves insufficient |

---

## 3. Catalog Data Layer (PostgreSQL)

Two schemas:

* `catalog` — normalised catalog data. Written **only** by `apps/sync`.
* `ops` — sync state, sync runs, MCP audit log, OAuth clients/codes/tokens (hashed).

We deliberately do **not** mirror Magento's EAV tables. The model below is what consumers want.

### 3.1 Tables

| Table | Key | Notes |
|---|---|---|
| `catalog.stores` | `store_id` | Store views + website/group, locale, base currency. `store_id = 0` is admin/default scope. |
| `catalog.attributes` | `attribute_code` | Label, input type, `is_required`, scope, option list (`[{value,label}]`). |
| `catalog.attribute_set_attributes` | `(attribute_set_id, attribute_code)` | Which attributes belong to which set — needed for "missing required attributes". |
| `catalog.categories` | `category_id` | Tree via `parent_id` + materialised `path`, level, position. |
| `catalog.category_store_data` | `(category_id, store_id)` | Store-scoped name, url_key, is_active, include_in_menu. |
| `catalog.products` | `product_id` (Magento entity_id) | Global identity: sku (unique), type, attribute_set_id, website_ids, price, special price + dates, tier prices, weight, Magento `updated_at`, `deleted_at` (soft-delete on reconciliation). |
| `catalog.product_store_data` | `(product_id, store_id)` | Store-scoped content: name, status, visibility, url_key, description, short_description, meta fields, plus `attributes JSONB` for all other custom attributes. |
| `catalog.product_relations` | `(parent_id, child_id, kind)` | Configurable → variants, bundle/grouped children. |
| `catalog.product_links` | `(product_id, linked_sku, link_type)` | related / upsell / crosssell with position. |
| `catalog.product_categories` | `(product_id, category_id)` | With position. |
| `catalog.product_images` | `(product_id, file)` | Label, position, roles (`image`, `small_image`, `thumbnail`), disabled flag. |
| `catalog.inventory_source_items` | `(sku, source_code)` | MSI per-source quantity + status (single-source stores have `default`). |
| `catalog.inventory_salable` | `(sku, stock_id)` | Salable qty / is_salable, refreshed live on demand and cached with `checked_at`. |
| `ops.sync_state` | `stream` | Cursor per stream (e.g. `products:default`) — high-water mark of Magento `updated_at`. |
| `ops.sync_runs` | `id` | One row per job run: counts, duration, error. Powers the `sync_status` tool. |
| `ops.audit_log` | `id` | Every MCP tool call: who (IdP subject), client, tool, redacted args, outcome, latency. |

### 3.2 Store-view handling

Magento returns store-scoped values when called on `/rest/{store_code}/V1/...`. The sync service
fetches the admin/default scope (`/rest/all/...`, stored as `store_id = 0`) and then each configured
store view. Consumers read `product_store_data` for the requested store and fall back to store 0.
Which store views to include is configured by `MAGENTO_STORE_CODES` (default: all active).

### 3.3 Price model and a known limitation

Stored: base `price`, `special_price` with from/to dates, tier prices. The Catalog API derives
`effective_price` = special price if active today and lower, else base price.

**Catalog price rules and customer-group pricing are not visible via REST product data.** When the
exact shopper-facing price matters, the `get_price` MCP tool can ask Magento's storefront GraphQL
(`price_range` per store view) live. This is called out to Znet as a Phase 1 limitation.

---

## 4. Sync strategy

All jobs are pg-boss queues with the `singleton` policy (never two runs of the same job at once).

| Job | Schedule (default) | What it does |
|---|---|---|
| `sync-reference` | hourly + on boot | Stores, attributes + options, attribute-set membership, category tree |
| `sync-products-incremental` | every 2 min | Skipped while a full sweep is running. Per store scope: products with `updated_at >= cursor − 2 min`, sorted ASC, page size 200. Upsert idempotently; advance cursor to max `updated_at` seen. |
| `sync-inventory` | every 10 min — **off for Znet** (`CATALOG_STOCK_ENABLED=false`, stock not managed in Magento) | Full page-through of `/V1/inventory/source-items` (no `updated_at` filter exists for stock) |
| `sync-products-full` | **hourly** at :15 (agreed 8 Oct 2026; move to nightly if `magento:check` estimates > 20 min) | Full product sweep + **reconciliation**: any product in DB not returned by Magento is soft-deleted (`deleted_at`). Catches deletes, which `updated_at` polling cannot see. |
| `sync-product-sku` | on demand | Refresh one SKU (used by webhooks later, and by an admin endpoint) |

Notes:

* **Overlap window.** The 2-minute overlap tolerates clock skew and same-second updates; upserts make
  re-processing harmless.
* **Stock changes do not bump product `updated_at`** in Magento — hence the separate inventory job.
* **Salable quantity** (stock minus reservations) has no bulk REST endpoint. We store per-source
  quantity in bulk and fetch salable qty live per SKU when a question needs it
  (`/V1/inventory/get-product-salable-quantity/{sku}/{stockId}`), caching the answer.
* **Load on Magento.** One request at a time per job, `fields=` projection to trim payloads,
  exponential backoff on 429/5xx. Expected steady-state load: a handful of requests every 5 minutes.
* **Event-based updates (Phase 1.5).** Magento Open Source has no native outbound webhooks.
  Options: a tiny Magento observer module that POSTs `{sku}` on product/stock save, or Adobe Commerce
  webhooks if Znet is on Commerce. Either feeds `sync-product-sku`. Polling remains as the safety net.

---

## 5. Catalog API (internal)

Fastify on a private interface; authenticated with a shared service token (`CATALOG_API_TOKEN`).
All routes accept `?store=<code>` (default store if omitted).

| Route | Purpose |
|---|---|
| `GET /health` | Liveness + DB check |
| `GET /products?q=&category=&status=&in_stock=&limit=&offset=` | Search (trigram on name/SKU) |
| `GET /products/:sku` | Full product: store data, attributes, images, categories, variants, links, price, stock |
| `GET /products/:sku/visibility` | **Visibility diagnosis** — ordered list of reasons a product is not visible on the storefront |
| `GET /categories` / `GET /categories/:id` | Tree / single category with product count |
| `GET /attributes/:code` | Attribute definition + options |
| `GET /prices/:sku` | Base, special (with dates), tier, effective price |
| `GET /inventory/:sku` | Per-source quantities + last cached salable qty |
| `GET /quality/summary` | Counts per issue type |
| `GET /quality/issues?type=&limit=&offset=` | Products with a given issue |
| `GET /sync/status` | Freshness per stream, last runs, last errors |

### Catalog quality checks (Phase 1, report-only)

`missing_images`, `missing_description`, `missing_required_attributes`, `no_category`,
`invalid_price` (null/≤0 on simple products, special ≥ base), `enabled_not_visible`
(enabled but visibility = Not Visible Individually and not a child of a configurable),
`not_in_website`, `out_of_stock_enabled`, `configurable_without_children`.
Implemented as SQL in `packages/catalog-db` so feeds/reporting share the same definitions.

---

## 6. MCP server

### 6.1 Transport and auth

There are two separate logins:

```
ChatGPT user ──(A: OAuth 2.1, Magento admin sign-in)──► MCP server ──(B: OAuth 1.0a integration)──► Magento
```

**B — MCP/sync → Magento: OAuth 1.0a Integration.** A dedicated read-only Integration
(Admin › System › Integrations) gives consumer key/secret + access token/secret; every REST call is
signed (HMAC-SHA256). It does not expire and works on Magento ≥ 2.4.4 without enabling
"integration tokens as standalone Bearer". All four values are required together — a partial set
fails at startup. (Bearer-only and admin-password tokens are supported/rejected respectively for
the reasons in the README.)

**A — ChatGPT user → MCP: built-in OAuth 2.1 authorization server, Magento admin sign-in**
(`AUTH_MODE=magento`, default).

1. ChatGPT reads `/.well-known/oauth-protected-resource/mcp`, which names this server as the
   authorization server, then registers itself via Dynamic Client Registration (`/register`).
2. `/authorize` (PKCE S256 required) renders our sign-in page: Magento admin **username, password
   and Google Authenticator code** (Magento 2.4 enforces admin 2FA by default).
3. `POST /login` checks the credentials against Magento
   (`/V1/tfa/provider/google/authenticate`, or `/V1/integration/admin/token` when
   `MAGENTO_ADMIN_2FA=none`). The password is never stored or logged; the admin token Magento
   returns is discarded. 5 failures per IP+username → 15-minute lockout (Magento also locks the
   account itself).
4. The username is mapped to scopes by `MCP_USER_SCOPES` (e.g.
   `alice=catalog:read orders:read orders:pii;*=catalog:read orders:read`). An admin with no entry
   and no `*` is refused.
5. A single-use code (5 min) is exchanged at `/token` for an opaque access token (1 h) and a rotating
   refresh token (7 days). Only SHA-256 hashes are stored (`ops.oauth_*`).

Offboarding: disabling the admin in Magento blocks new sign-ins immediately; existing sessions end
at the next refresh-token expiry (≤ `MCP_REFRESH_TOKEN_TTL_DAYS`), or at once with
`pnpm auth:revoke <username>`. Allowlist changes apply at the next token refresh.

The login form carries the pending authorization as an HMAC-signed blob (`MCP_AUTH_SECRET`), so no
server-side session is needed and the redirect URI cannot be tampered with.

Alternatives kept behind config: `AUTH_MODE=oauth` (external IdP issuing JWTs — validated via
JWKS, `aud` = MCP URL) and `AUTH_MODE=static` (fixed tokens, refused in production).

Transport: **Streamable HTTP, stateless** at `POST /mcp` — a fresh `McpServer` per request, no
sticky sessions. ChatGPT does not support stdio for remote connectors.

### 6.2 Scopes

| Scope | Grants |
|---|---|
| `catalog:read` | catalog, pricing, stock, quality, sync status tools |
| `orders:read` | order tools with PII **masked** |
| `orders:pii` | order tools with full customer name/email/phone/address |

### 6.3 Phase 1 tools (all `readOnlyHint: true`)

| Tool | Source | Answers |
|---|---|---|
| `search_products` | Catalog API | "Find red trainers under £50" |
| `get_product` | Catalog API | Full product detail |
| `diagnose_product_visibility` | Catalog API (+ live salable qty) | "Why is this product not visible?" |
| `get_price` | Catalog API (+ optional live GraphQL) | "What is the current price?" |
| `get_stock` | Catalog API + live Magento salable qty | "Is this product in stock?" |
| `list_categories` / `get_category` | Catalog API | Category tree / detail |
| `get_attribute` | Catalog API | Attribute definition + options |
| `catalog_quality_summary` | Catalog API | Overview of all issue counts |
| `find_products_with_issues` | Catalog API | "Which products are missing images?" |
| `search_orders` | **Magento live** | By status, date range, customer email |
| `get_order` | **Magento live** | Order by increment ID incl. items, status history, invoices, shipments + tracking |
| `get_customer_orders` | **Magento live** | Order history for an email |
| `list_stores` | Catalog API | Store view codes for the `store` argument |
| `sync_status` | Catalog API | "How fresh is this data?" |

Every tool result includes `as_of` (catalog freshness or "live") so the model can tell the user.

### 6.4 Guardrails

* Rate limit per token subject; payload caps (max 50 items per page; long text truncated).
* Outbound Magento calls time out at 15 s; errors are mapped to safe messages (no stack traces / URLs).
* Every call written to `ops.audit_log` with arguments passed through the same PII redactor.

---

## 7. Personal data (PII)

Znet answered "ALL" for order/customer visibility. Every tool result is sent to OpenAI, so before
enabling full PII in production we need from Znet:

1. Confirmation their ChatGPT workspace is Business/Enterprise/Edu (no training on inputs by
   default) and that a DPA with OpenAI covers customer data.
2. GDPR/UK GDPR position if they have EU/UK customers (lawful basis, record of processing).
3. Which staff get `orders:pii` vs `orders:read`.

Until then the implementation **defaults to masked** (`j***@gmail.com`, `J. S.`, postcode district
only, phone last 3 digits). Full PII is a scope grant, not a code change.

---

## 8. Security

* Dedicated Magento Integration with **read-only ACLs only**:
  `Magento_Catalog::products`, `Magento_Catalog::categories`, `Magento_Backend::stores` (+attributes),
  `Magento_Sales::actions_view` (orders/invoices/shipments), `Magento_InventoryApi::source` + stock read.
  Stored as env/secrets, never in the DB.
* HTTPS terminated by a reverse proxy (Caddy) in front of `mcp-server` only. Catalog API and
  Postgres bind to private interfaces.
* **Database roles** — one login per service, grants re-applied after every migration
  (`packages/catalog-db/src/grants.ts`, verified by `test/db-roles.test.ts`):

  | Role | Can | Cannot |
  |---|---|---|
  | `mcp_znet` (owner) | run migrations (`migrate` job only) | — not used by any long-running service |
  | `mcp_sync` | read/write `catalog.*`, write `ops.sync_*`, own `pgboss` schema | read audit log or OAuth sessions |
  | `mcp_api` | read `catalog.*`, `ops.sync_*` | write anything; read audit log or sessions |
  | `mcp_server` | insert `ops.audit_log`, manage `ops.oauth_*` | read the catalog; read, change or delete the audit log |

  So the public-facing MCP process cannot read catalog tables directly or tamper with its own audit trail.
* If Nexcess restricts the REST API by IP, whitelist the mcp-znet host's egress IP.
* Logs are JSON (pino) with request IDs; tokens and PII are redacted at the logger.

---

## 9. Deployment

Single Ubuntu 24.04 host, Docker Compose: `postgres` → `migrate` (one-shot, owner role, applies
migrations + grants) → `sync`, `catalog-api`, `mcp-server` (each as its own role) → `caddy`.
Roles are created on first volume init by `deploy/postgres-init/01-roles.sh`. Verified locally on
8 Oct 2026: image builds (423 MB), stack starts, migrate grants all roles, sync runs as `mcp_sync`.
Same region as Magento (Nexcess) to keep live-call latency low. Backups: nightly `pg_dump` — the
catalog is fully rebuildable from Magento, so the audit log is the only irreplaceable data.

### Performance (measured 8 Oct 2026, `pnpm perf 50000`)

Synthetic catalog: 50,000 products (8,333 configurables × 5 variants + 8,335 simples, ~25 attributes
each), 2 scopes, 41,669 stock rows. MacBook, local Postgres 16, mock Magento in a separate process
(so real Magento response times will add to sync durations).

| Measure | Result |
|---|---|
| Initial full load (both scopes) | ~30 s, peak RSS ~240 MB |
| Inventory full sweep | 1.5 s |
| Incremental, no changes / 500 edited products | < 0.1 s / 0.5 s |
| Database size | 220 MB |
| Search (`q=`), 10 concurrent | p50 102 ms · p95 380 ms |
| Product detail / visibility diagnosis / stock | p50 ≤ 7 ms · p95 ≤ 12 ms |
| Category tree with counts | p50 11 ms |
| Quality issue list / summary (full-catalog scans) | ~0.4–1.1 s |

Conclusion: 2–4 GB RAM / 20 GB disk is ample for ≤ 100k SKUs × ≤ 5 store views. Real-Magento sync
time will be dominated by Magento's own response time (estimate, unmeasured: 0.5–3 s per 200-product page →
~5–15 min for an initial 50k load; incremental runs stay small). Quality reports are full scans by
design; if they become hot, precompute them after each sync.

---

## 10. Phase plan

| Phase | Contents |
|---|---|
| **1 (now)** | Repo, DB schema, sync (reference, products, inventory, reconciliation), Catalog API, read-only MCP tools, OAuth, audit, deploy |
| 1.5 | Event-based updates (Magento module / webhooks), OpenSearch if needed |
| 2 | Feed engine (Google Merchant, Meta) reading from Catalog API with per-channel rules |
| 3 | Controlled writes (enrichment, attribute cleanup, SEO) — separate integration credentials, approval workflow, dry-run + diff |

## 11. Open questions for Znet (blocking items in bold)

1. **Day-one use cases** (10–15 real questions their team would ask).
2. **PII sign-off** (§7).
3. Magento admin 2FA method in use (Google Authenticator assumed; Duo/U2F need extra work) and the list of admins + scopes for `MCP_USER_SCOPES`.
4. Store views / languages to cover; catalog + order volumes.
5. ~~Freshness~~ — agreed: changed products every 2 min, full sweep hourly, no stock.
6. Magento Open Source vs Adobe Commerce (affects webhooks and B2B pricing).
7. Hosting outcome from the Nexcess ticket.