CataSync feeds 2/6: engine shop catalog snapshot (shop_items) + sync

Imported from GitHub issue El-SaMa/oma#245 by @El-SaMa.

---

Part of the Product feeds epic. Needs issue "Connector 0.9.0 /catalog" live.

What

The engine keeps a snapshot of every published product on each connected shop, so feeds are built from what customers actually see (CataSync products and the shop's own products such as spare parts and AliExpress items).

Exact spec

• Migration ops/catasync/engine/migrations/015_shop_items.sql:

  CREATE TABLE IF NOT EXISTS shop_items (
    site_id CHAR(36) NOT NULL,
    remote_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    product_id CHAR(36) NULL,          -- CataSync product when catasync_id matches
    data JSON NOT NULL,                -- the connector row as returned
    data_hash CHAR(64) NOT NULL,
    seen_at DATETIME NOT NULL,
    modified_at DATETIME NULL,
    PRIMARY KEY (site_id, remote_id),
    KEY (site_id, seen_at)
  );
  ALTER TABLE sites ADD COLUMN IF NOT EXISTS catalog_synced_at DATETIME NULL, ADD COLUMN IF NOT EXISTS catalog_cursor VARCHAR(40) NULL;
  

• New module ops/catasync/engine/src/catalog.mjs:
  • syncShopCatalog(siteId, { full }): pages through /lafu-catasync/v1/catalog with callSite (same as adopt.mjs uses /inventory), per_page 100. Incremental by default using sites.catalog_cursor as modified_after; full run every 24 h or when no cursor. Upsert rows (skip when data_hash unchanged except seen_at). On a full run, delete rows not seen in that run (product unpublished or deleted). Save the response now as the new cursor.
  • Map catasync_id -> product_id (validate it exists in products).
  • Sleep 300 ms between pages so the shop is never hammered. Stop and log on connector errors; never throw out of the sweep.
• server.mjs: hourly sweep for every site with push_enabled = 1 (incremental), daily full; route POST /v1/sites/:id/catalog-sync {full} (returns counts) and GET /v1/sites/:id/catalog?q=&limit=&offset= (rows for the UI preview).
• Log events catalog_sync with counts (seen, changed, removed, ms).
• Tests in test/ for row upsert/hash/delete logic (pure functions, no network).

Acceptance

• Engine tests pass (node --test test/*.test.mjs).
• Rolled with bash scripts/catasync-engine-roll.sh figaro; first full sync of maxfort, then niletech; counts in the engine log match the shop's published product + variation count (check with a DB count on the shop, read-only).
• niletech front page TTFB unchanged during the sync (measure before/during).
• Handbook section "Shop catalog snapshot".

Binding rules (read before coding)

• Read CLAUDE.md and every .cursor/rules/*.mdc with alwaysApply: true first. They win over your defaults.
• No OpenAI / AI calls anywhere in this feature. CataSync AI switch (ai_enabled) stays off. Never turn it on.
• Smallest correct change. Match the surrounding code style (engine = plain ESM .mjs, mysql2 pool from src/db.mjs; core-api = TypeScript).
• Every staff action goes through the admin MCP (admin-mcp-first.mdc): executor in services/core-api/src/agent-tools/tools-catasync.ts, confirm handler in services/core-api/src/platform/confirm.ts for writes, registry entry, zod schema in services/mcp-server/src/tools.ts, test in tools-catasync.test.ts. The UI calls the same tool (via apps/web/lib/catasync/admin-actions.ts), never a separate path.
• UI: vertical side nav only (never a second tab row), forms in modals, shadow cards, full width (portal-chrome.mdc).
• Every string: next-intl key with native en, fi, sv (Finnish must read native, not translated). No em/en dashes in prose. Run node scripts/i18n-validate.mjs.
• Handbook: update apps/web/content/admin-docs/products/catasync.md (new section) for every behaviour you add.
• Secrets (tokens, Google tokens, feed tokens) never in logs, commits, handbook or chat.
• Commits: author ELSAMA only, no Co-authored-by / AI attribution. Push to main right after each commit (rebase on origin/main first).
• Deploy: engine with bash scripts/catasync-engine-roll.sh figaro; OMA only via the train bash scripts/deploy-quick.sh (never set OMA_DEPLOY_SERVICES, never build on the VM). Check the live SHA contains your commit.
• WordPress plugin changes: bump version, pass scripts/wp-plugin-gate-remote.sh ops/catasync/plugins/lafu-catasync-connector with 0 errors 0 warnings + security check (wp-plugin-dod.mdc). Install on maxfort.fi first (test shop), only then niletech.
• Never change niletech prices, statuses, shipping rows or the Code Snippets / table-rate plugins as part of this work.
• Clean up test data and temp files when done.