# Maintenance Memory — Shopify Customer Integration (Customers page + Returns + Sorting)

**Created:** 2026-06-02
**Scope tenant:** Pascucci USA Inc (PUI) — `tenant_id=837eebaa-1ae8-5b79-95c3-26649fb25c42`,
shopify `integration_instance_id=3c7e91d9-8a2d-5382-9da0-dd2d7bfb8213`,
erpnext `integration_instance_id=70b35b22-1430-5595-b698-6e551970a14c`, company `Pascucci USA Inc`.
**Builds on:** `plans/SHOPIFY_ORDER_CRM_GEO_PLAN_V1.md` (P5–P11 — the order→Customer→Sales-Order pipeline).
**Status:** **DEPLOYED + verified 2026-06-02.** Rebuilt connector-erpnext, connector-shopify, core-api
(migration `0041` auto-applied), finanly-ui. 224 Shopify customers tagged group `Shopify`; 231 order links
backfilled with refund/cancel; 1 cancelled order (#1005) SO cancelled; 7 refunded orders (each 1 refund
after the $0-restock fix). Pipeline idempotency re-verified live (re-running creates zero duplicates).

> Read this before touching: the Customers/Suppliers pages, the Shopify order→CRM pipeline, refund/cancel
> handling, or the `customer_group = "Shopify"` tagging. It documents how customer creation/update is driven
> **by payout categorization** and the invariants that keep the books safe.

---

## 0. The one-sentence model

When a Pascucci **Shopify payout is categorized/exported** in the UI, Finanly walks every order in that
payout and ensures, in ERPNext, a **Customer + geocoded Address + Submitted-and-Closed Sales Order** for it,
and records the order's **refunds/cancellation**. The Sales Order posts **ZERO** to the GL — it exists only
to attach the order to its customer for CRM and to power the Customers page. **Revenue is booked solely by the
payout Journal Entry**, never by these SOs. Never create a Sales *Invoice* from them (it would re-book revenue).

---

## 1. Trigger — it is driven by payout categorization (not a separate sync)

`finanly/services/core-api/src/finanly_core_api/routers/recon.py`:

- `export_shopify_payout(...)` (categorize a payout → posts the payout JE) **and**
  `backfill_shopify_payout_cogs(...)` both call **`_ensure_shopify_order_crm(...)`** (same file).
- `_ensure_shopify_order_crm`:
  1. Reads the payout's **balance-transactions** from connector-shopify
     (`GET /v1/shopify/payouts/{payout_id}/balance-transactions`).
  2. Enumerates **every order** referenced by ANY balance-txn type that carries a `source_order_id`
     (charge / refund / credit / debit / adjustment) — deduped. (COGS enumeration stays charge-only; this
     is CRM linkage, not stock.)
  3. **Ensures the `Shopify` Customer Group exists once** (idempotent) before the loop:
     `POST {connector_erpnext}/v1/ledger/customers/customer-groups {"name":"Shopify"}` (409 = already there).
  4. Per order → `ensure_order_customer(...)` (failure-isolated: a bad order increments `failed` and is
     skipped; it never raises into the export request).
- Per-order internal JWTs are **re-minted inside the loop** (`_shop_headers()`/`_erp_headers()`) so a long
  payout can't outlive the 5-min token TTL.

**Going forward = automatic.** New payouts create/dedup customers + SOs and capture refunds/cancellation with
no extra step. The historical 231 orders were one-time backfilled (see §7).

---

## 2. Per-order pipeline — `services/shopify_crm.py :: ensure_order_customer`

Idempotent. Does NOT commit (caller owns the txn). Steps:

1. **Idempotency short-circuit:** if `shopify_order_links` row is already `status='linked'` with both a
   customer and a Sales Order, it **still calls `apply_crm_status`** (to catch refunds/cancellations issued
   *after* the order was first linked) and returns `already_linked`.
2. Fetch the order's customer + shipping address from connector-shopify
   (`GET /v1/shopify/orders/{order_id}/customer`).
3. **Dedup** against `shopify_customer_links` by `shopify_customer_id`, else by lowercased `email`.
4. If new → create the ERPNext **Customer** (`POST {erpnext}/v1/ledger/customers`) with
   `customer_group = SHOPIFY_CUSTOMER_GROUP` (= `"Shopify"`, the single constant at the top of
   `shopify_crm.py`) + a **geocoded Address** (Shopify shipping lat/long first; Nominatim fallback).
5. Upsert the `shopify_order_links` row (`status='linked'`).
6. **`apply_crm_status(...)`** (see §3). If it reports the order is **cancelled → skip Sales Order
   creation** (so a cancelled order never counts) and return early.
7. Else **`_ensure_sales_order(...)`** — build the draft SO from `GET /v1/shopify/orders/{id}/sales-order-lines`
   (per-line sku/qty/unit_price), create it, then **submit + close** it (zero-GL — see §5).

`SHOPIFY_CUSTOMER_GROUP = "Shopify"` is the single source of truth, imported by `recon.py`. Future B2B / other
sources will use **different** group tags; the `Shopify` tag is reserved for Shopify-imported customers.

---

## 3. Refund + cancellation — `services/shopify_crm.py :: apply_crm_status`

Reads **`GET {connector_shopify}/v1/shopify/orders/{order_id}/crm-status`** and persists onto the
`shopify_order_links` row: `is_cancelled`, `cancelled_at`, `refund_count`, `refunded_amount`, `refunds`
(JSONB list of `{id, date, amount, items:[{sku,name,qty,subtotal}]}`).

- **Best-effort:** any connector failure / non-200 leaves the fields unchanged and returns the current
  `is_cancelled`. It is fully `try/except`-wrapped and **can never raise into the pipeline.**
- **Exclude-cancelled:** if the order `is_cancelled` AND a Sales Order exists, it cancels the SO via
  `POST {erpnext}/v1/ledger/docs/cancel {doctype:"Sales Order", name, delete_if_draft:true}` and **clears
  `erpnext_sales_order`**. Because the Customers list counts only non-cancelled SOs (`docstatus != 2`), the
  order then drops out of orders/totals entirely.

### crm-status endpoint — `connectors/shopify/.../app.py :: order_crm_status`
`GET /v1/shopify/orders/{order_id}/crm-status` → `{cancelled_at, is_cancelled, refund_count,
refunded_amount, refunds[]}`. PII-free (sku/qty/amounts only). **Distinct from `/refunds`** (the GAAP
hardening breakdown used by the payout posting path — do not confuse them).

> **GOTCHA (fixed 2026-06-02): Shopify splits one return into TWO refund records.** A money refund
> (`totalRefunded > 0`, often with empty `refundLineItems`) **plus** a separate `$0` record that only marks
> the item `restocked:true`. Counting both double-counts the return (this is why #1063 once showed
> "2 refunds"). `order_crm_status` now **skips refunds with `amount <= 0`** — a return = money actually
> refunded; the `$0`/restock record is the inventory side of a refund already counted.

---

## 4. Data model (Postgres, tenant-scoped via RLS)

`finanly/services/core-api/src/finanly_core_api/db/models.py`:

- **`shopify_customer_links`** — one row per distinct Shopify customer → the ERPNext Customer created for it.
  `unique(tenant_id, shopify_customer_id)` is the dedup key.
- **`shopify_order_links`** — one row per Shopify order → its ERPNext Customer + Sales Order.
  `unique(tenant_id, shopify_order_id)` is the idempotency guard. Columns added by **migration
  `0041_shopify_order_links_refunds`**: `is_cancelled` (bool), `cancelled_at` (tstz),
  `refund_count` (int), `refunded_amount` (numeric 18,2), `refunds` (JSONB, default `[]`).

ERPNext refund/cancel data lives **only in Finanly** (`shopify_order_links`) — ERPNext knows nothing about
Shopify refunds. That is why the Returns column + per-order refund detail are enriched **in core-api**, not
the connector (§6).

---

## 5. Accounting invariants (DO NOT BREAK)

- A **Sales Order posts NOTHING to the GL** in any docstatus (create→submit→close = 0 GL Entries, 0 Stock
  Ledger Entries). Proven empirically. These SOs are CRM-only.
- SO finalize = `frappe.client.submit` then
  `erpnext.selling.doctype.sales_order.sales_order.update_status {"status":"Closed"}`. Connector httpx client
  uses `json=` (not form `data=`) for frappe method calls.
- **Cancelling a Closed SO requires UNCLOSE first.** ERPNext refuses (`"Closed order cannot be canceled.
  Unclose to cancel."`). `connectors/erpnext/.../app.py :: cancel_generic_doc` (`POST /v1/ledger/docs/cancel`)
  now: if `doctype == "Sales Order"` and status `Closed`, it first calls `update_status {"status":"Draft"}`
  (unclose; docstatus stays 1), then `frappe.client.cancel` (docstatus → 2).
- **Never** create a Sales Invoice from these SOs.
- ERPNext is reached **only** through connector-erpnext's least-privilege site creds, never root.

---

## 6. Customers page — `ui/finanly-ui/app/parties/customers/page.tsx`

Data path: UI → core-api `GET /v1/ledger/customers` (router `ledger_parity.list_customers`, connector-first)
→ connector-erpnext `list_customers_parity`.

**Connector `list_customers_parity` (`connectors/erpnext/.../app.py`):**
- `company_only=true` means: include customers with a **Sales Invoice OR a (Shopify) Sales Order** for the
  company. (Before, it required an invoice → Shopify customers, who have no invoice, never appeared → the
  "Customers page shows nothing" bug.)
- **SO-based stats (orders count, total, first/last order date) are computed ONLY for customers in the
  `Shopify` group** — resolved by first querying `Customer where customer_group='Shopify'`. Non-Shopify (e.g.
  future B2B) customers keep **invoice-based** metrics so existing books aren't corrupted.
- **`first_order_date` / `last_order_date`** = min/max SO `transaction_date` (the real Shopify order date),
  surfaced as "Since" / "Latest". (The ERPNext Customer `creation` date is just the backfill import date — do
  NOT use it for "Since".)
- **Pagination/sort for `company_only` is done in Python** (fetch all matching customers, then
  intersect-members/sort/paginate). This is deliberate: a `name IN [hundreds]` ERPNext filter blows the GET
  URL past the server limit and returns **502** — do not reintroduce it.
- Params: `customer_group` (show only), `exclude_customer_group` (hide), `acquisition_year` (Customer
  `creation` year), `year`/`start_date`/`end_date` (activity), `sort_by`/`sort_order`.
  `sort_field_map` includes name/type/group/email/modified/creation/since; the Python `_sort_key` also
  handles `amount`/`orders`/`latest`.

**core-api enrichment (`ledger_parity.py`):**
- `list_customers` attaches **`returns_count` + `refunded_amount`** per customer by summing
  `shopify_order_links.refund_count`/`refunded_amount` **where `is_cancelled = false`** (a cancellation is not
  a product return).
- `list_customer_sales_orders` (`GET /v1/ledger/customers/{name}/sales-orders`) proxies the connector's SO
  list (with line items) and **attaches per-order `refunds`** from `shopify_order_links` (matched by
  `shopify_order_id`).

**Categorization picker exclusion:** `GET /v1/ledger/vendors/search` (core-api `ledger.search_vendors`)
defaults `exclude_customer_group="Shopify"`, forwarded to the connector, so Shopify-tagged customers are
**hidden by default** from the transaction categorization counterparty picker (those txns are auto-categorized
by the payout tool). Pass empty to include them.

**UI features:** search; **Shopify filter** (Show all / Only Shopify / Hide Shopify); **Acquired** (year);
**page size 20/50/100**; **Returns** column; **click-to-expand** a customer → their orders with line items
(Product/SKU/Qty/Rate/Subtotal) and a **Returns / Refunds** block per order; **sortable headers** (every
sortable `<th>` has `role="button"` + a persistent `↕`/`▲`/`▼` indicator; CSS `.data-table th[role=button]`
gives cursor + hover). **Returns is intentionally NOT sortable** (its data lives in core-api, not the
connector-paginated list). Suppliers page got the same persistent sort indicator.

**Customer-group management (mirrors suppliers):** connector `create_customer_group`
(`POST /v1/ledger/customers/customer-groups`) + core-api proxy of the same path (`ledger.py`,
scope `ledger_customer_group_create`). The connector's `create_customer` no longer auto-creates the group —
the group lifecycle is the dedicated endpoint, exactly like Supplier Groups.

---

## 7. One-time backfill (already run; scripts were under `temp/`, which is git-ignored)

If you ever need to re-run for a new tenant or after a data reset, the logic was:
1. **Tag existing Shopify customers:** for each `shopify_customer_links.erpnext_customer_name`, ensure the
   `Shopify` group exists then `PUT {erpnext}/v1/ledger/customers/{name} {"customer_group":"Shopify"}`.
2. **Populate refund/cancel:** load every `ShopifyOrderLink` inside `db_session_ctx(tenant_id=...)`, call
   `shopify_crm.apply_crm_status(link, ...)` (reuses the exact pipeline logic), commit. This also cancels the
   SO of any now-cancelled order.

Both mint internal JWTs with `create_internal_jwt(secret=settings.internal_jwt_secret, issuer="core-api",
audience="connector-erpnext"|"connector-shopify", subject="core-api", tenant_id=...)` and run **inside the
core-api container** (it can reach the connectors on the docker network).

Pascucci result: 224 customers tagged; 231 order links populated; 1 cancelled (#1005, SO `SAL-ORD-2026-00010`
cancelled); 7 refunded orders (each `refund_count=1` after the $0-restock fix).

---

## 8. Rebuild + verify

```bash
# Backend (baked images — rebuild to take effect; migrations auto-apply on core-api start):
docker compose -f infra/docker/compose.core.yml up -d --build connector-erpnext connector-shopify core-api
# UI:
docker compose -f infra/docker/compose.routing.npm.phase3_5c.local.yml up -d --build finanly-ui
```

Health: `docker exec finanly-core-api-1 python -c "import urllib.request;print(urllib.request.urlopen('http://localhost:8000/health').status)"`.
Live checks are done by minting an internal JWT in the core-api container and calling the connector endpoints
(see §7 pattern). Unit test: `tests/test_shopify_order_crm_wiring.py` (run in a throwaway container with a
`*_test` `DATABASE_URL`, e.g. `postgresql+psycopg://x:x@localhost:5432/finanly_test`, working tree mounted
over `/app/src` + `/app/tests`).

---

## 9. Gotchas checklist (for the next person)

- **$0 refunds are skipped** by `crm-status` (restock-only records) — don't "fix" them back in.
- **Closed SOs need unclose-then-cancel** — handled in `cancel_generic_doc`; don't bypass it.
- **`Shopify` group must exist** before `create_customer` (group=Shopify) — ensured per payout + permanently
  exists; if a brand-new tenant's first ensure fails transiently, customer creation fails that run but is
  failure-isolated and retried next payout.
- **Don't add a big `name IN [...]` ERPNext filter** to the customers list (502 URL-length).
- **"Since"/"Latest" use SO `transaction_date`**, not Customer `creation`.
- **SO-based metrics are Shopify-group-only** — keep non-Shopify customers on invoice metrics.
- **Returns count excludes cancelled orders.**
- **grep/rg terminal output in this workspace is redacted** on some path/word terms — use the Read tool to
  inspect files reliably.