# Deploying the reporting pipeline Covers the M0 foundation. Each later module (M1 Sales Summary, M2 Item Sales, M3 Weekly, M4 Category) adds its own migration and cron entries to this file. Prod database is **MariaDB 10.11**; local dev is MySQL 8.0. Everything here runs on both. --- ## 0. File placement Everything in the repo's `backend/` maps to the app root on the server (`/var/www/html/smartprinter-app/`). `lib/`, `jobs/` and `tests/` are **siblings of `api/`, not children of it** — the existing endpoints resolve `__DIR__ . '/../db.php'` and `__DIR__ . '/../lib/shopify_auth.php'` from inside `api/`, which only works at that level. ``` /var/www/html/smartprinter-app/ ├── config.php ├── db.php ├── api/ endpoints — api/reports/ for the report endpoints ├── lib/ shared libraries (lib/renderers/, lib/rollups/) ├── jobs/ cron jobs ├── output/reports/ cached PDFs └── tests/ PHPUnit suite ``` Copying `lib/` or `tests/` into `api/` puts them one level too deep, and `../config.php` then resolves to a path that does not exist. The test bootstrap checks this and prints the expected layout rather than failing on an unrelated-looking missing require. ```bash APP=/var/www/html/smartprinter-app rsync -a backend/lib/ "$APP/lib/" rsync -a backend/jobs/ "$APP/jobs/" rsync -a backend/tests/ "$APP/tests/" rsync -a backend/api/ "$APP/api/" cp backend/db.php "$APP/db.php" ``` `config.php` is already on the server and must not be overwritten — it is what `config.local.php` and the `SHOPIFY_CLIENT_*` env vars hang off. ## 1. Migrations Run once, in order. 001–004 are already applied on the existing box; 005 is new. ```bash mysql -u -p smartprinter_db < api/migration-005-reporting-core.sql mysql -u -p smartprinter_db < api/migration-006-sales-summary.sql ``` 005 creates `rpt_shop_state`, `rpt_sync_state`, `rpt_webhook_events`, `rpt_dirty_days`, `rpt_dim_location`, `rpt_report_cache`. 006 (M1) adds `rpt_orders`, `rpt_refunds`, `rpt_payments`, `rpt_daily_summary` and the `stg_*` staging tables the bulk loader writes into. `rpt_refunds` is separate from `rpt_orders` on purpose: Square dates a return by the REFUND date, so an order placed 30 June and refunded 2 July belongs to June's gross sales and July's returns. A refund column on the order row would put every month-boundary refund in the wrong month. Verify: ```bash mysql -u -p smartprinter_db -e "SHOW TABLES LIKE 'rpt\_%'" ``` ## 2. App scopes `shopify.app.toml` now requests `read_all_orders`, `read_shopify_payments_accounts`, `read_shopify_payments_payouts` and `read_locations` on top of the original three. `read_shopify_payments_accounts` gates the `shopifyPaymentsAccount` field itself; the payouts scope only covers the balance transactions hanging off it. Both are needed for the fee lines. **`read_shopify_payments` is not a requestable app scope** and fails `shopify app deploy` with `Validation errors • scopes: read_shopify_payments`. Scope changes only take effect after the app config is pushed **and** the app is re-authorized on the shop: ```bash shopify app deploy ``` Then open the app in POS once on each shop so a token with the new scopes is minted. Without `read_all_orders` the `orders` query silently returns only the last 60 days. ## 3. Server prerequisites | Requirement | Why | Check | |---|---|---| | `wkhtmltopdf` on PATH | PDF rendering (already present for `wkhtmltoimage`) | `command -v wkhtmltopdf` | | MySQL `local_infile=1` | bulk loader's `LOAD DATA LOCAL INFILE` fast path | `SHOW VARIABLES LIKE 'local_infile'` | | `backend/output/reports/` writable by the web user | cached PDFs | `sudo -u www-data test -w backend/output/reports` | `local_infile` is an **optimization, not a requirement** — `lib/loader.php` falls back to chunked `INSERT` automatically and logs when it does. If PDF output is needed at a non-standard path, set `WKHTMLTOPDF_BIN`. ```bash mkdir -p backend/output/reports && chown www-data:www-data backend/output/reports ``` ## 4. Cron The client's chosen cadence is a **twice-daily re-sync of the trailing 15 days**. `jobs/sync-recent.sh` runs the three steps in the required order under a lock: ```cron PHP_BIN=/usr/bin/php APP=/var/www/html/smartprinter-app SHOP=roycechocolate.myshopify.com # Twice daily: sync orders -> aggregate -> settle fees -> aggregate -> warm the presets. 15 3,15 * * * $APP/jobs/sync-recent.sh $SHOP 7 >> /var/log/rpt-sync.log 2>&1 # Nightly retention: prune webhook events, superseded cache rows and orphaned PDFs. 40 4 * * * $PHP_BIN $APP/jobs/retention.php --quiet >> /var/log/rpt-retention.log 2>&1 # Health check. Exits non-zero on a problem, so cron mail becomes the alert. 0 * * * * $PHP_BIN $APP/jobs/health-check.php --quiet || $PHP_BIN $APP/jobs/health-check.php # Weekly deep check: compares our order count against Shopify's, catching silent drift. 30 5 * * 1 $PHP_BIN $APP/jobs/health-check.php --deep >> /var/log/rpt-health.log 2>&1 ``` `health-check.php` is quiet when healthy and prints the detail only when something is wrong, so the hourly entry mails you only on a real problem. The window is on **`updated_at`, not `processed_at`**. An order from three months ago that was refunded yesterday was *updated* yesterday, so it is caught and its refund lands on the correct day. A `processed_at` window would miss exactly the late changes this job exists to pick up — refunds, order edits, and settled fees. Ordering inside the script is not incidental: 1. `sync-orders --recent` writes facts and marks days dirty 2. `aggregate` turns those into rollups 3. `sync-payments` applies settled fees, re-marking those days 4. `aggregate` again, to fold the fees in rather than waiting 12 hours Aggregating before ingest finishes would roll up a day whose facts are not all in yet and then clear the flag, leaving a confidently wrong number until something else happened to touch that day. `flock -n` means a slow run causes the next cycle to **skip**, not stack — Shopify permits one bulk query per shop at a time. ### Freshness trade-off Twice a day means the POS tiles can be up to 12 hours stale. `status.php` and the "Data as of" line on every report show exactly how stale, so staff can see it rather than assume the numbers are live. If same-day figures are needed at the register, add the near-real-time layer as well — they compose, and the trailing-15-day job stays as the safety net that repairs anything the webhooks dropped: ```cron */5 * * * * /usr/bin/php $APP/jobs/drain-webhooks.php >> /var/log/rpt-drain.log 2>&1 */5 * * * * sleep 30; /usr/bin/php $APP/jobs/sync-orders.php --shop=$SHOP --quiet >> /var/log/rpt-sync.log 2>&1 */5 * * * * sleep 60; /usr/bin/php $APP/jobs/aggregate.php >> /var/log/rpt-aggregate.log 2>&1 ``` Aggregation itself is safe to run concurrently: days are leased via `claimed_at` (`AGGREGATE_LEASE_SECONDS`, 900s) with `SELECT ... FOR UPDATE SKIP LOCKED`, so workers take disjoint batches and a crashed worker's days are reclaimed on the next tick. ## 4b. First backfill (M1, run once per shop) Order matters. The shop context sync writes the timezone, and every order is bucketed into a LOCAL calendar date — running the backfill first would bucket a whole history against UTC and silently shift every evening sale by a day. ```bash cd /var/www/html/smartprinter-app && php jobs/sync-orders.php --shop= --backfill --from=2024-01-01 ``` `--backfill` walks `updated_at` in 30-day windows (`--window-days` to change). Sequential by necessity: Shopify allows only one bulk query per shop at a time. An interrupted run resumes — a completed window is never re-fetched, and re-running is safe because every write is an upsert. Then aggregate and fetch fees: ```bash cd /var/www/html/smartprinter-app && php jobs/aggregate.php && php jobs/sync-payments.php ``` ## 4c. Webhooks Register these to `https://shopifyprinter.whisperdemo.online/api/webhooks/orders.php`: `orders/create`, `orders/updated`, `orders/cancelled`, `refunds/create`, `order_transactions/create`. The receiver verifies the HMAC and records only which order changed — the payload is never parsed for figures. Webhook bodies are REST-shaped and share no field names with the GraphQL schema, so deriving metrics from them would be a second implementation of the same arithmetic. `jobs/drain-webhooks.php` re-reads each order through the same GraphQL fragment the backfill uses. Webhooks are not trusted alone — deliveries get dropped. The 5-minute `sync-orders.php` poll is the safety net that makes that drift self-correcting. ## 4d. Parity check (the sign-off gate) ```bash cd /var/www/html/smartprinter-app && php tools/parity.php --shop= --from=2026-06-01 ``` Prints our figures in Square's exact line order for a side-by-side comparison. Add `--csv` to diff against a Square export in a spreadsheet, and `--location=` for one store. It warns when days are still queued for aggregation, because a mismatch caused by pending work is a different problem from a mismatch caused by wrong arithmetic. ## 5. Smoke test ```bash curl -s -H "Authorization: Bearer " \ https://shopifyprinter.whisperdemo.online/api/reports/status.php | jq ``` Expected: `200` with `shop`, `timezone`, `currency`, `pending_days`, `locations`. `401` means the session token is missing or invalid; `403` means the shop has no row in `shops` yet (open the app in POS once). ## 6. Tests ```bash php backend/tests/phpunit.phar -c backend/tests/phpunit.xml ``` Unit and render suites need nothing. The integration suite builds a throwaway `smartprinter_test` schema by replaying the real migration files, and **skips itself** when no database is reachable, so this is safe to run anywhere. Credentials come from `config.local.php` automatically — only the schema name is overridden, so tests never touch `smartprinter_db`. The DB user needs rights on the test schema, granted once: ```sql CREATE DATABASE IF NOT EXISTS smartprinter_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; GRANT ALL ON smartprinter_test.* TO 'smartprinter'@'localhost'; ``` **A run reporting `Skipped: 28` means the integration tier never executed** — the suite still prints `OK`, so check the skip count, not just the exit status. Override connection details with `SMARTPRINTER_TEST_DB_HOST` / `_PORT` / `_USER` / `_PASS` / `_NAME` when needed (local MAMP, for instance, wants `SMARTPRINTER_TEST_DB_HOST=127.0.0.1` to force TCP over a socket path). Opt-in suites: ```bash php backend/tests/phpunit.phar -c backend/tests/phpunit.xml --group benchmark php backend/tests/phpunit.phar -c backend/tests/phpunit.xml --group pdf-binary ``` `benchmark` asserts the loader throughput target from REPORTS_PLAN.md §3.1. `pdf-binary` actually invokes `wkhtmltopdf` and skips when it is absent — worth running on the server itself, where the binary lives. Frontend: ```bash node receipt-printer-pos/extensions/shared/test/dates.test.mjs ``` ## 7. Rollback M0 adds no behaviour to any existing endpoint. Reverting is: 1. remove the cron entry 2. `DROP TABLE rpt_report_cache, rpt_dirty_days, rpt_dim_location, rpt_webhook_events, rpt_sync_state, rpt_shop_state;` 3. revert `shopify.app.toml` scopes and `shopify app deploy` `backend/db.php` gained only an env-var fallback and the `LOCAL_INFILE` attribute; its defaults are unchanged, so it is safe to leave in place.