# CLAUDE.md This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository. ## What this project is Symfony 7.2 / PHP 8.2 backend that ingests Shopify order webhooks, normalises them into a relational schema, and exposes read endpoints (`GET /api/orders` and friends) for the sibling Remix app (`../kayo-reporting`) to render its Operations Report. The Remix UI does *not* talk to Shopify directly — it talks to this service at `http://127.0.0.1:8000/api/orders`. If you change the request/response shape here, update `app/routes/app.fetch-orders.jsx` in `kayo-reporting`. There is no test suite configured (no `tests/`, no PHPUnit in `composer.json`). ## Commands ```bash composer install docker compose up -d # MariaDB on :3306 (volume db-data) bin/console doctrine:migrations:migrate # apply migrations under migrations/ symfony serve --port=8000 # or: php -S 127.0.0.1:8000 -t public/ bin/console app:consume-webhooks # drains pending shopify_webhooks into shopify_orders + shopify_line_items bin/console app:import-webhooks # one-off backfill: copies rows from pndelive_parson_shopify.shopify_orders into the webhooks queue # --dry-run (count only), --chunk-size=2000, --from-id=N (resume); copies server-side via chunked INSERT..SELECT bin/console app:register-webhooks # reconciles the ORDERS_CREATE/ORDERS_UPDATED subscriptions with the # ApiController receiver routes; --dry-run shows the diff, --url overrides # SHOPIFY_WEBHOOK_BASE_URL. Reports subscriptions it does not manage, never deletes. bin/console app:import-orders --from=YYYY-MM-DD --to=YYYY-MM-DD # backfills a created_at range straight from Shopify via bulkOperationRunQuery, # reshapes the JSONL into the REST webhook payload and queues it as orders/create. # --dry-run, --chunk-size, --poll-interval, --timeout; --keep-jsonl=PATH saves the # export and --jsonl=PATH re-imports it without paying for a second export. # Skips orders already in shopify_orders -- consume between runs or they queue twice. # --refresh queues those held orders as orders/updated instead of skipping them, # which is the only way to repair rows imported before a field was added to the # export: createOrder() skips an order it already holds, updateOrder() rewrites # the blob and replaces the line items. **Every field a report reads has to be named in `OrderPayloadMapper::ORDER_FIELDS` / `LINE_ITEM_FIELDS`.** Both `app:import-orders`' bulk export and the force update's single-order lookup are built from those two lists. An export that omits one does not fail -- it writes a thinner `data_object` than the webhook path does, and the report that needs the field silently reports nothing for every backfilled order, which looks like missing data rather than a missing field. That happened once already: the query asked only for id/sku/title/variantTitle/quantity/product/variant/taxLines, so 84,547 line items landed without `customAttributes`, `originalUnitPriceSet` or `discountAllocations` -- the Masterclass report had no `Booking-Date` to print and Weekly Product Profit had no price to take a margin from. If you add a report that reads a new field off the line, add it there and re-import the affected range with `--refresh`. bin/console app:backfill-line-item-variants # after importing into an empty database: the export returns no variant for a # deleted one, so those lines land with shopify_variant blank. Copies the ids from # an older line-item table (default: the legacy connection; --source-url=DSN for # another), matched on Shopify's line item id, filling blanks only. Dry run unless # --write. Re-run after --refresh or an orders/updated for an old order re-blanks them. bin/console app:import-catalogue # DEV ONLY: refreshes the local copy of shopify_product_variant from Shopify. # Upsert, never delete; merges inventory_object so a locally recorded cost # survives a variant Shopify has no cost for. --dry-run reports the diff. # Refuses to run unless LEGACY_DATABASE_URL is loopback -- in production # that table belongs to the fulfilment portal. bin/console make:migration # generate a migration from entity diffs bin/console cache:clear ``` The Remix app expects this service on `127.0.0.1:8000` — keep that port unless you also change `ORDERS_API_URL` in the Remix `.env`. ## Architecture ### Three-stage pipeline Shopify hits this app on order events. Data flows: **webhook receiver → queue table → consumer command → reporting endpoint**. 1. **Receive** (`ApiController::shopifyWebhookOrderCreate`, `shopifyWebhookOrderUpdate`, both through `queueWebhook()`) — raw `$request->getContent()` is stored as a JSON blob in `shopify_webhooks(posted_data, webhook_type, output, created)`, along with Shopify's `X-Shopify-Webhook-Id` in the unique `shopify_webhook_id` column. No parsing happens at this stage; the receiver returns 200 immediately so Shopify doesn't retry. A redelivery (same webhook id; Shopify retries anything it didn't see acknowledged quickly) also gets a 200 and is not stored again. Rows queued by `app:import-orders` or a force update have no webhook id, so the column is NULL for them. 2. **Consume** (`bin/console app:consume-webhooks` → `ConsumeWebhooksCommand`) — picks up rows where `output IS NULL` (see `ShopifyWebhooksRepository::fetchPendingWebhooks`), parses them, and writes `ShopifyOrders` + `ShopifyLineItems` through `App\Service\OrderMirror`, which owns the parsing so the force update writes an order exactly as a webhook does. This is run on demand / on a cron — there is no message queue or worker daemon. A consumed row has `output` set to `1`, so it drops out of the pending filter. A row that throws has its `attempts` incremented and its error kept in `last_error`. It stays pending until it has failed `ConsumeWebhooksCommand::MAX_ATTEMPTS` (5) runs, then `output` becomes `failed` and the give-up is logged at error level. Before that change, a payload that could never be applied (a checkout bug writes `Pickup-Date` as `undefined-09-13`) was retried by every run forever and made every run exit 1. A failed row keeps its payload; setting `output` back to NULL re-queues it. For a bad checkout attribute, the fix is usually to correct the order in Shopify: that sends a fresh `orders/updated`, and `orders/updated` is applied through `OrderMirror::sync()`, which creates an order the mirror does not hold. `update()` alone used to return without writing and the row was marked consumed anyway, so an order whose create had failed stayed missing however many updates arrived for it. `OrderMirror::update()` skips a payload whose `updated_at` is older than the stored `last_updated`, because Shopify doesn't deliver in order and `--refresh` queues export-time snapshots. A late arrival used to roll an order back. A payload with an equal `updated_at` is applied. The force update passes `current: true` and is applied regardless, because it reads Shopify live and is the tool for fixing a row whose `last_updated` is itself wrong. **`created` and `last_updated` are stored as Europe/London wall-clock time** (`OrderMirror::shopTime()`). The payloads disagree about the offset: webhooks send shop-local time, while the bulk export and the lookup send UTC. `new DateTime($iso)` kept whichever offset it was given, and the DATETIME column drops it, so the same moment was stored an hour apart in summer depending on which path wrote it. That broke the stale-update comparison above and moved late-night summer orders onto the wrong day in the Masterclass report. `app:repair-orders` converts rows written before this change. 3. **Serve** (`ApiController::getCachedOrders` at `/api/operations-report`) — `findByDateRange(startDate, endDate, locations)` on `ShopifyOrdersRepository`, then re-decodes the stored `data_object` JSON per row to flatten it into the response shape the Remix UI expects. The other report endpoints on the same controller (`/api/order-summary`, `/api/sales-report`, `/api/customers-by-date`, `/api/product-list`, `/api/product-customers`, `/api/stock-report`, `/api/kitchen-sales-report`, `/api/profit-report`, `/api/collection-list`, `/api/category-report`, `/api/variant-list`, `/api/weekly-product-profit`, `/api/masterclass-report`, `/api/postcode-report`, `/api/loyalty-app-codes`) follow the same shape: read, pivot in PHP, return JSON. `/api/profit-report` is the one that takes no parameters at all -- it prices the catalogue as it stands, so there is no window to ask it for. `/api/weekly-product-profit` is the one the Remix side POSTs rather than GETs: its `variants` parameter can carry all 785 catalogue ids, which is past the request line most servers accept. It reads them with `$request->get()`, which looks in the body as well as the query, so nothing else about it is special. `/api/masterclass-report` is the one that windows on `shopify_orders.created` instead of `delivery_date`: a class is not delivered, so the delivery date those lines carry is whatever the checkout happened to put there, and the date the customer actually wants is a free-text line-item property. `/api/postcode-report` is the one whose window is a calendar month rather than a range: it reports a month against the same month a year earlier, so it derives the comparison month from the one it is given rather than taking two. It is also the only endpoint here that groups in SQL and normalises the grouping key on the way out -- `OrderMirror` writes `delivery_postcode` as `substr($zip, 0, -3)` without trimming, so every stored value ends in a space and a handful are lower case; the collation already merges them for the `GROUP BY`, but the value the group reports back is whichever row MySQL picked first, hence the `UPPER(TRIM())` on both the select and the group. `/api/loyalty-app-codes` is the one that reads none of this app's own data at all -- see "Three DBAL connections". ### Force update: the one write endpoint `POST /api/force-update` (`orderNumber`, with or without a leading `#`) is the replacement for the legacy `force.php`. It fetches that one order from Shopify through `App\Service\ShopifyOrderLookup` and runs it through `OrderMirror::sync()`: created if the mirror never had it, rewritten if it does, deleted if Shopify says it was cancelled. It is for repairing an order whose webhook was missed or mangled without a backfill. The response is `{outcome, orderNumber, before, after}`, where `before`/`after` are a snapshot of the reporting columns (not the order payload) and `outcome` is one of `created`, `updated`, `deleted`, `unchanged` or `not_found_in_shopify`. An order Shopify has no record of is reported and **left alone**, and a Shopify failure is a 502 that writes nothing. The legacy page deleted the local order on any exception, so a timeout removed a real order from every report; do not make "not found" the fallback for an error. A date the parser cannot read is a 422. Each successful write also stores the fetched payload as a `shopify_webhooks` row of type `orders/force` with `output` already `1`, as an audit trail. The consumer never dispatches on that type. The lookup asks for `updatedAt` and `cancelledAt` on top of the shared field lists. The bulk export asks for `updatedAt` as well, because the stale-update check would otherwise skip every `--refresh` snapshot for an order that had ever had a live update. It still does not ask for `cancelledAt`, because `--refresh` must not start deleting the cancelled orders it re-queues. ### Single source of truth: Shopify `note_attributes` Delivery type, delivery date, delivery time, and pickup location are *not* on the Shopify order proper — they're encoded in the `note_attributes` array (cart attributes set during checkout). The keys this app cares about: `Checkout-Method`, `Delivery-Date`, `Pickup-Date`, `Shipping-Date`, `Delivery-Time`, `Pickup-Time`, `Pickup-Location-Id`, `Pickup-Location-Company`, `Pickup-Location-City`. An order carrying none of the date keys is stored with `delivery_date` NULL. NULL fails the `BETWEEN` in `findByDateRange`, so it stays out of every reporting window. Do not reintroduce a "now" fallback here: it stamps the day of consumption onto the row, which then makes the order appear in whatever window happens to cover that day. `OrderMirror::notesFormatter()` is the only parser of these attributes. `ApiController` used to keep a second copy that re-derived `pickupLocation` from the blob at read time. That copy had fallen behind: it parsed dates with `new DateTime()`, so `20/12/2022` crashed the Operations Report. The report now reads the `pickup_location` column. `create()` and `update()` both write every derived column through `OrderMirror::applyOrder()`, because the two setter chains had drifted: `update()` never rewrote `pickup_location`, `checkout_method` or `item_count`, so an order moved from Collection to Delivery stayed filed under its old shop. **An update never blanks a delivery field** (`OrderMirror::keepStoredDelivery()`). If an `orders/updated` payload leaves the date, time, location or checkout method empty, the stored value is kept. The dispatch type is kept only when the payload has no delivery attribute at all. The reason is Shopify's subscription app (source `2506359`): it rewrites a reorder's attributes seconds after checkout, sometimes dropping the pickup location and sometimes emptying the whole list, and applying those updates wiped dates and locations. An order whose delivery really changes carries attributes saying so; moving to delivery sets the location to "Delivery", not to nothing. Draft orders (`shopify_draft_order`) never carry these attributes, so their NULL date is correct and not a loss. `app:repair-orders` re-derives the reporting columns of every stored order from payloads already on hand and prints what differs. It changes nothing unless given `--write`. When an order's stored payload has lost its delivery attributes, it recovers them from the newest webhook for that order that still has them, following the same keep rule. It never calls Shopify and never clears a value. ### The one live read: collections Everything else here reports off the local mirror. Collection membership has no mirror to read: the webhook pipeline columnises orders and line items, never products, and the fulfilment portal's `shopify_products.primary_collection` column has never been populated (every row is an empty string) and would only hold one collection per product anyway. So `App\Service\ShopifyCollectionRepository` asks the Admin GraphQL API, through the thin `App\Service\ShopifyGraphqlClient`, for the collection list and for the products in the collections a report run selected. Nothing is cached — most of the store's 41 collections are rule-based, so their membership moves whenever a product's tags or type change. A Shopify failure surfaces as a 502 from `/api/collection-list` and `/api/category-report` rather than an empty result, because an empty collection and an unreachable Shopify are indistinguishable once the rows are gone. `ImportOrdersCommand` still has its own copy of the GraphQL POST helper, entangled with the bulk-operation polling it wraps; it was left alone rather than reworked to go through the client. ### `/api` authentication Every `/api` path requires the `X-Kayo-Token` header to match `ORDERS_API_TOKEN`, checked by `App\EventSubscriber\ApiTokenSubscriber` on `kernel.request` at priority 256 -- ahead of the router, so an unauthenticated caller gets 401 for every `/api` path, real or not, and cannot map which endpoints exist. The Remix app sends the header from its proxies. Nothing on the controller checks it, so a new endpoint is covered by being under `/api` and needs no code of its own. The reason it is not optional: these endpoints return customer names, addresses and emails, cost prices, and the loyalty app's live one-time passcodes, which sign whoever reads them in as that customer for fifteen minutes. `/api/health` sits behind the token like everything else, which is deliberate: the Remix home page uses it to tell "backend down" (no answer) apart from "tokens differ" (401). It runs `SELECT 1` on each of the three connections and always answers 200 with a flag per connection (`orders`, `portal`, `loyalty`). A database being down is the answer, not an error. It reports which connection failed but never why; the driver message goes to the log. `/api/webhook/*` is exempt, because Shopify cannot send the header; those receivers are authenticated by `ShopifyWebhookVerifier`'s HMAC check instead. An empty `ORDERS_API_TOKEN` makes every other `/api` request a 500 (a `LogicException`, the same stance the verifier takes on an empty `SHOPIFY_WEBHOOK_SECRET`) rather than letting it through. Secrets live in `.env.local`, which is gitignored: `SHOPIFY_ACCESS_TOKEN`, `RECHARGE_ACCESS_TOKEN`, `ORDERS_API_TOKEN`, and in production the database URLs and webhook secret as well. The committed `.env` holds blanks. Do not put any of those keys in `.env.dev` at all, not even blank: `.env.$APP_ENV` loads after `.env.local`, so a blank line there wipes out the real value. ### Three DBAL connections `default` is this app's own database (`pn_portal`) and owns the only entity manager. `legacy` is a **read-only** connection to the fulfilment portal's database (`LEGACY_DATABASE_URL`, locally `pndelive_parson_shopify`), which owns `stock_request`, `stock_request_item` and `shopify_product_variant`. Those tables are still written by `../parsons-nose-fulfilment-portal`, and no webhook feed exists to mirror them here — a copy in `pn_portal` would be stale as soon as the portal took another stock request, so the Stock Report reads them live instead. There are no entities and no migration for them; `App\Service\StockRequestRepository` queries them with plain SQL over that connection. Nothing in this app writes to it, with one deliberate exception: `app:import-catalogue` refreshes a **developer's** copy of `shopify_product_variant` from Shopify, and refuses to run unless that connection is loopback. A dev copy is a dump frozen at some past date, and a variant created after it carries no tag locally and drops silently out of every report that selects by tag — the Masterclass report lost four of its five bookings that way, which reads as a broken report rather than an old dump. `shopify_product_variant` is also the only place product **tags** are stored — the webhook pipeline columnises orders and line items, never products — so any report that selects products by tag has to come through the `legacy` connection too. `App\Service\ProductVariantRepository` does that for the Kitchen Sales Report, and reads the same table's cost and price blobs for the Custom Profit Report -- the order pipeline records what a line sold for, never what the catalogue currently asks, so a margin report has no other source. That one pulls the variant's price out of the product blob with `JSON_TABLE` rather than shipping ~3MB of product JSON to PHP to loop over. Because the tags and the line items sit in different schemas on different connections, that report runs two queries (tagged variant ids, then an `IN` filter over the line items) rather than one join. Do not turn it into a cross-schema join: the two databases only happen to share a server locally. The Masterclass report selects through the same tag route: `classes` marks the ten class products, and `findVariantIdsByTag()` resolves it to the fourteen variant ids the line-item query filters on. Nothing else about the report touches the catalogue -- a class has no cost and no margin, and the shop that runs it is already spelled out in the line's description. The Weekly Product Profit report needs that table for the opposite half of its sum: it prices lines from what they actually sold for and takes only the cost from here, so it calls `findCostsByVariantIds()` once per run rather than the per-line-item lookup the legacy report did. Its variant picker is fed from here too (`findVariantOptions()`), unfiltered -- the "... STORE" and Big Green Egg exclusions belong to `findCatalogue()`, where they stop a catalogue-wide margin total double-counting, not to a picker where they would only hide a product somebody sold. `loyalty` is a second **read-only** connection, to the loyalty card app's database (`LOYALTY_DATABASE_URL`, locally `pnloyalty_loyalty_app`), which owns the `customer` table the Loyalty App Codes report reads. That schema belongs to `../parsons-nose-loyalty-lion-integration`, and in production it is a different *server* from both this app and the fulfilment portal, not another schema alongside them -- so it gets its own connection rather than a second database name on `legacy`. Nothing is mirrored and nothing should be: a one-time passcode is valid for fifteen minutes, so a copy taken on any schedule would only ever hold codes that had already expired. `App\Service\LoyaltyCustomerRepository` queries it with plain SQL, the same way `StockRequestRepository` queries the portal, and names its columns rather than selecting `*` -- the table also carries a push token, a saved basket and a search history, none of which belong in a report. Because that database is reachable independently of everything else here, a failure to connect surfaces as a 502 from `/api/loyalty-app-codes` rather than an empty list: an empty week and an unreachable loyalty app look identical once the rows are gone, and the first of those reads as "nobody signed up". Note `.env.local.php` exists. Once Symfony sees it, it **replaces** the `.env*` files entirely — a new variable added only to `.env`/`.env.dev` will not be picked up locally until it is also in `.env.local.php` (or `composer dump-env dev` is re-run). ### Entities - `ShopifyWebhooks` — raw inbox. `posted_data` (JSON blob), `webhook_type`, `output` (the "processed" flag — set to `1` once a row is consumed), `created`. - `ShopifyOrders` — flattened reporting row. `shopify_order` is **unique**; `createOrder()` skips an order that already exists, so a redelivered `orders/create` or a re-run backfill is a no-op rather than a duplicate. Changes arrive as `orders/updated`. Stores both the parsed delivery fields *and* the original `data_object` blob, because the `/api/orders` response re-reads fields like `shipping_address`, `total_price`, `phone`, `email` straight from the blob rather than columnising them. - `ShopifyLineItems` — one per line, with its own `data_object` blob. `OneToMany` from `ShopifyOrders` with `orphanRemoval: true`, so deleting an order takes its lines with it. ## Conventions - Routing: PHP 8 attributes (`#[Route(...)]`) on controllers; `config/routes/framework.yaml` auto-discovers `src/Controller/`. - DI: autowiring + autoconfigure are on; entities are excluded from the service container in `config/services.yaml`. - Naming strategy: `underscore_number_aware` (entity property `delivery_date` → column `delivery_date`; getter is `getDeliveryDate()`). - Property names on entities use snake_case to match the columns directly — this is intentional, don't camelCase them. - Secrets come from env via `%env(...)%`, with real values in `.env.local` (see "`/api` authentication"). Never hard-code a token in source: an old `/api` debug endpoint did, which put the live store's Admin API token in git history.