# Shopify Payout Reconciliation — Pascucci USA Inc — Handoff V1 > **Audience**: anyone maintaining or extending the Shopify-payout reconciliation flow. > **Last updated**: 2026-04-30 (session 2 — first production postings landed) > **Tenant scope**: Currently wired specifically for `Pascucci USA Inc` (`tenant.slug = pascucci`, > `tenant.id = 837eebaa-1ae8-5b79-95c3-26649fb25c42`). Other tenants fall back to the legacy mapping > path which uses fictitious account names — they will fail at JE-post time until similarly mapped. > **Status**: Live in production for Pascucci. Two real Journal Entries posted, both balanced and > reconciled to the cent. UI, scheduler, and reconciliation invariant verified end-to-end. --- ## 1. Why this feature exists Pascucci sells coffee + machines through Shopify. Shopify Payments deposits net payouts to Mercury. Each deposit is a single bank line that aggregates many Shopify-side balance transactions: charges, refunds, processing fees, chargebacks, ad credits, etc. Without this feature, the user sees only "Shopify $96.03" in the bank ledger and has to manually unpack what went into that number. This feature takes any Shopify deposit/withdrawal that hits the bank account, looks up the matching Shopify payout via the connector, decomposes the payout into one ERPNext-ready journal-entry line per balance transaction (per product family for charges/refunds), and returns a preview that **reconciles to the cent against the actual bank deposit**. The user reviews and approves; the export is then queued and the JE is posted via `connector-erpnext`. --- ## 2. End-to-end flow (manual at every gate) ``` ┌──────────────┐ webhook/poll ┌──────────────────┐ │ Mercury bank │ ─────────────────▶│ canonical_transac│ │ deposit │ │ tions (unreview) │ └──────────────┘ └────────┬─────────┘ │ user clicks the row ▼ ┌────────────────────────────────────────────────┐ │ UI: choose "Shopify payout reconciliation", │ │ pick candidate from `recon_items` (shopify_pay │ │ out, last 90d, unlinked). │ └────────────────────┬───────────────────────────┘ │ POST /v1/recon/shopify/payouts/{id}/preview ▼ ┌──────────────────────────────────────────────────────────┐ │ core-api `_build_shopify_preview_payload` │ │ - GET connector-shopify /v1/shopify/payouts/{id}/ │ │ balance-transactions │ │ - For each balance txn: look up │ │ PASCUCCI_SHOPIFY_TXN_TYPE_MAP │ │ - charge/refund: GET /orders/{id}/line-items, classify │ │ via product_tags / product_type / order_tags │ │ - aggregate per (account, cost_center) │ │ - fold per-txn `fee` into CC Processing Fees (sign- │ │ flipped to debit) │ │ - VERIFY: sum(gl_entries) == payout_net (cent-precise) │ │ - persist ReconPreview, audit recon.preview_generated │ └──────────────────────┬───────────────────────────────────┘ │ user reviews UI → click Export │ POST /v1/recon/shopify/payouts/{id}/export ▼ ┌──────────────────────────────────────────────────────────┐ │ Approval (kind=shopify_payout_export, status=approved) │ │ ExportBatch + ExportItem │ │ (still NO ledger write) │ └──────────────────────┬───────────────────────────────────┘ │ Celery worker picks up export ▼ ┌──────────────────────────────────────────────────────────┐ │ connector-erpnext `_post_shopify_payout_journal_entry` │ │ - POST /api/resource/Journal Entry to ERPNext │ │ - link to Bank Transaction (reconcile) │ │ - submit if `auto_submit=true` else leave Draft │ └──────────────────────────────────────────────────────────┘ ``` Manual gates: - Selecting which payout matches the bank line (UI step). - Reviewing the preview before approving export. - Choosing whether to auto-submit the JE in ERPNext (`auto_submit` flag in export request). Automatic backstops: - Nightly sync run materializes new `recon_items` (`tasks.py:_default_sync_resources_for` returns `["payouts","fulfillments"]` for Shopify integrations). - Reconciliation invariant blocks any preview that doesn't sum to the cent — the JE cannot be exported until the math is clean. --- ## 3. Critical files ### Core-API (orchestration + preview) | Path | What it does | |---|---| | `finanly/services/core-api/src/finanly_core_api/services/product_classifier.py` | Pascucci account map: `PASCUCCI_SHOPIFY_TXN_TYPE_MAP` (115 types), `PASCUCCI_SHOPIFY_CHARGE_BY_CATEGORY`, `PASCUCCI_SHOPIFY_REFUND_BY_CATEGORY`, account/cost-center constants, `pascucci_shopify_routing()` lookup. `ProductClassifier` now classifies by `product_tags` → `product_type` → SKU/name keywords (in that order). | | `finanly/services/core-api/src/finanly_core_api/routers/recon.py` | `_build_shopify_preview_payload` (line ~281): the engine. Per-type routing, per-fee sign flip, reconciliation invariant, refusal for SKIP-FLEXPORT/NOT-USED types. `preview_shopify_payout` HTTP handler persists `ReconPreview`. `export_shopify_payout` HTTP handler creates approval+export. | ### Connector-Shopify (data fetcher) | Path | What it does | |---|---| | `finanly/services/connectors/shopify/src/finanly_connector_shopify/app.py` | `_fetch_balance_transactions` (REST `/shopify_payments/balance/transactions.json?payout_id=X`). `order_line_items` (GraphQL — now also pulls `product.tags`, `product.productType`, `order.tags` for tag-based classification). | ### Connector-ERPNext (JE posting) | Path | What it does | |---|---| | `finanly/services/connectors/erpnext/src/finanly_connector_erpnext/app.py` | `_post_shopify_payout_journal_entry` (line ~2030). Consumes `gl_entries` from the preview. **Sign convention**: `amount > 0` → credit on the account; `amount < 0` → debit. Also reconciles the underlying ERPNext Bank Transaction to the new JE name. | ### Jobs (scheduler + ingest) | Path | What it does | |---|---| | `finanly/services/jobs/src/finanly_jobs/tasks.py` | `_default_sync_resources_for(itype)` (new). Per-integration resource resolver. Shopify → `["payouts", "fulfillments"]`. Replaces the previous hardcoded `["accounts", "transactions"]` that turned Shopify nightly into a no-op. Also: ingestion of `shopify_payout` recon_items (line ~2035). | ### Mapping reference | Path | What it is | |---|---| | `/mnt/sata2tb/Dropbox/pascucci_shopify_account_mapping.csv` | User-confirmed source-of-truth mapping for all 115 Shopify balance-transaction types. Edited by Luca. **The TxnRouting map in product_classifier.py mirrors this CSV — keep them in sync if you change one.** | --- ## 4. Pascucci account map (verified live in ERPNext) ERPNext database: **`erpnext_recovered_20250814`** (NOT `erpnext`; the latter is a stale snapshot from 2025-07. `frappe-bench/sites/erp.capula.co/site_config.json::db_name` is the source of truth). ### Income (parent: `Sales of Product Income - PUI`) - `Product Sales - Coffee - PUI` — sales tagged coffee/blend/beans/pods/ground/espresso - `Product Sales - Machines - PUI` — sales tagged starter-kit/machine/maker - `Product Sales - Other - PUI` — fallback + tips - `Product Sales - Shipping - PUI` — shipping income charged to customer (currently flagged REVIEW; fold into product sales if not desired) - `Sales Returns & Allowances - PUI` — refunds + chargeback losses + Shop Cash refunds - `Discounts - PUI` — order/line discounts at checkout ### Liability - `Sales Tax Payable - PUI` — sales tax + tax adjustments ### Expense (parent: `05 - Expenses - PUI`) - `CC Processing Fees - PUI` — Stripe / Shopify Payments processing fees + chargeback protection coverage fees - `Shopify Merchant Fees - PUI` — Shopify platform commission (referral fees, marketplace fees, channel credits, goodwill, ad credits) - `Shopify Platform - PUI` (under Office Supplies & Software) — Shopify subscription, app refunds - `Bank Charges - PUI` — bank-side fees (currently unused for Shopify; reserved) - `Chargeback - PUI` — chargeback fees + protection income (offset) - `Uncategorized Expense - PUI` — fallback (only used by legacy non-Pascucci tenants) ### Asset (parent: `07 - Current Assets - PUI`) - `Shopify Clearing - PUI` (Receivable type) — inflight cash: reserves, transfers, risk holds ### COGS (NOT booked via Shopify — handled by Flexport) - `Coffee - PUI`, `Machines - PUI`, `Other - PUI`, `Replacements - PUI` — exist in chart but the Shopify recon flow does NOT touch them. Fulfillment-time COGS posting goes through the Flexport-driven flow. ### Cost Centers | Cost Center | Used for | |---|---| | `eCommerce - Coffee - PUI` | charges + refunds with coffee tags | | `eCommerce - Machines - PUI` | charges + refunds with starter-kit/machine tags | | `eCommerce - Other - PUI` | other charges + tips + processing fees + chargebacks + most non-product flows | | `eCommerce Revenues - PUI` | discounts | | `eCommerce liabilities - PUI` | inflight cash (reserved_funds, transfers, risk holds) | | `Shopify Warehouse - PUI` | shipping income (when active) | | `Advertising - PUI` | ad credits, promotion credits | | `Admin and Overheads - PUI` | Shopify subscription billing, app fee refunds | | `Taxes - PUI` | sales tax payable + adjustments | There is NO `Main - PUI` cost center. There is NO coffee-specific COGS cost center. --- ## 5. The 115-type routing map `PASCUCCI_SHOPIFY_TXN_TYPE_MAP` in `product_classifier.py` is the single source of truth. Each entry is a `TxnRouting(account, cost_center, status, requires_order_context)`. Status enum: - **ACTIVE** — book to GL using (account, cost_center). 39 types. - **SKIP** — informational; do not book (the payout itself, payout_failure). 2 types. - **SKIP-FLEXPORT** — handled by the Flexport flow. Refused. 11 types (shipping_label*, customs_duty*, import_tax*, vat). - **NOT-USED** — Pascucci does not use this Shopify feature. Refused. 63 types (lending/Capital, ACH, balance transfers, M2M, Collective, source, channel, collections, marketplace fees, Markets Pro, Shop Cash, anomaly). When the preview encounters a SKIP-FLEXPORT or NOT-USED type, it raises HTTP 412 with the offending balance-transaction ids. This is the deliberate gate that prevents silent miscategorization — if Shopify ever emits one of these, the operator MUST resolve it manually (and either map it properly or post a separate JE). `requires_order_context=True` for `charge` and `refund` only — those need the order's product tags to choose between Coffee/Machines/Other. --- ## 6. Tag-based product classification Pascucci wants charges and refunds split by product family. Resolution order in `ProductClassifier.classify_item`: 1. **Product tags** (`product.tags` from Shopify GraphQL). Lower-cased substring match against `_MACHINE_TAGS = ("starter-kit", "machine", "maker")` then `_COFFEE_TAGS = ("coffee", "blend", "beans", "pods", "ground", "espresso")`. 2. **Product type** (`product.productType`). Same substring rules. 3. **SKU/name keywords** (legacy fallback). For an order, `classify_order` first checks `order.tags` for tag matches before falling back to per-line-item classification. Order-level tag wins if present. If you change the tag rules, update both this doc and the user-confirmed CSV. --- ## 7. Reconciliation invariant The preview builder enforces: ``` round(sum(gl_entries.amount), 2) == round(payout_net, 2) ``` Where: - `gl_entries.amount` is signed (positive → credit, negative → debit) per ERPNext's posting rules in `connector-erpnext._post_shopify_payout_journal_entry`. - `payout_net = sum(charges/refunds/adjustments) − sum(per-txn fees)`. If the diff exceeds 0.005, the preview returns HTTP 502 with the diff and refuses to persist. The export endpoint cannot be called without a valid preview, so a misbalanced JE can never be queued. This is the safety net. A second cross-check compares against Shopify's reported `payout_amount` when the upstream response includes it — disagreement also returns HTTP 502. --- ## 8. Sign convention (DO NOT CHANGE WITHOUT TRACING ERPNext) `gl_entries.amount` semantics: | Sign | ERPNext side | Used for | |---|---|---| | **Positive** | `credit_in_account_currency` | Revenue (Product Sales), liabilities (Sales Tax Payable, Shopify Dispute Holding), refund reversals | | **Negative** | `debit_in_account_currency` (abs value) | Expenses (CC Processing Fees, Bank Charges), contra-revenue (Sales Returns & Allowances, Discounts) | Shopify's `fee` field on each balance transaction is reported as a **positive** number even though it's a debit on the merchant balance. The preview builder explicitly negates fees when adding them to `gl_entries`. **If you change this, the JE will post inverted and the books will be wrong.** See `recon.py` near "Sign convention in the resulting JE". --- ## 9. Operational state at handoff ### Tenant: Pascucci USA Inc - Shopify integration instance: `3c7e91d9-8a2d-5382-9da0-dd2d7bfb8213` (status: active) - Mercury integration instance: `109f1d3d-4619-591f-a1c2-d593f6f22988` (status: active) - ERPNext integration instance: `70b35b22-1430-5595-b698-6e551970a14c` (status: active, bound company `Pascucci USA Inc`) - Started selling on Shopify ~2026-03-30. Earliest payout in `recon_items`: 2026-04-17. - 13 `shopify_payout` recon_items materialized (one $-119 clawback from 2026-04-17 + 12 normal payouts). - 32 `shopify_fulfillment_order` recon_items (informational; Stock Entries via Flexport flow). ### Production postings (live in ERPNext) | Date | Payout id | Bank deposit | ERPNext JE | ERPNext BT | Status | |---|---|---|---|---|---| | 2026-04-23 | 130365587626 | $115.49 | `ACC-JV-2026-00367` | `ACC-BTN-2026-00405` | Unreconciled, JE Submitted | | 2026-04-27 | 130620162218 | $95.05 | `ACC-JV-2026-00368` | `ACC-BTN-2026-00429` | Unreconciled, JE Submitted | Both JEs balance to the cent against the bank deposit. Both classify as `machine` via Shopify product tags. Both post to cost center `eCommerce - Machines - PUI`. **Lifecycle correction (2026-05-01)**: JEs are now **auto-submitted** by Finanly's recon export (`_ExportRequest.submit` defaults to `True`). The Bank Transaction goes to `Unreconciled` — meaning "payment doc posted, awaiting accountant's bank-statement reconciliation". The accountant later runs ERPNext's Bank Reconciliation Tool against the actual statement to flip BT to the final `Reconciled` state. Finanly never sets `Reconciled` directly — that's the accountant's call after matching reality. The connector also sets `set_posting_time: 1` on every JE so ERPNext doesn't auto-bump posting_date to "today" on submit; the bank-transaction date is the correct accounting period. ### UI work shipped (this session) - The Shopify radio in the per-row expansion (business/transactions/page.tsx) is now enabled with full implementation. Previously a hardcoded "not yet available" placeholder. - Candidate list highlights the matching payout: `EXACT` (same date, same amount, green) and `MATCH · T+1` (Shopify-issued date is 1–3 days before bank credit; still green — this is the normal ACH lag). Best match floats to the top with a green left-border accordion when expanded. - Inline accordion preview within the candidate row (rather than a separate panel below). - After clicking Export, UI calls `waitForExportBatchCompletion(exportBatchId)` to poll until the Celery worker actually posts the JE — only THEN does it run `refreshAfterMutation`. Same pattern `doCategorize` uses. Without this the row briefly stayed visible as unreviewed because the worker hadn't yet flipped `canonical_transactions.status`. - `formatTxnDate` parses ISO calendar dates as UTC instead of via `new Date(...). toLocaleDateString()` — fixes the bug where 2026-04-23 00:00:00 UTC rendered as 4/22 in any US timezone (ERPNext-mirrored bank rows store midnight UTC). ### List endpoint dedup behavior `/v1/recon/shopify/payouts` (recon.py:list_shopify_payouts) hides only payouts that are already actively exported (have a non-failed/non-cancelled `ExportItem` of kind `shopify_payout_posting`). Preview-only links no longer hide payouts — operators can preview multiple candidates against the same bank line without "losing" payouts they peeked at. ### Two parallel canonical sources for the same Mercury deposit The `canonical_transactions` table contains TWO rows per physical Mercury deposit: one from the `mercury` connector (live bank API) and one from the `erpnext` connector (the `tabBank Transaction` row that the legacy `/home/docker/erpnext/modular_sync/ bank_connection_manager.py` already created in ERPNext from the same Mercury feed). The business-transactions list endpoint already filters by `LEDGER_SOURCE_SYSTEMS = {"erpnext"}` (business_transactions.py:610, applied at line 952), so this duplication is **invisible in the UI** — operators only ever see the `erpnext` source row. The categorize/recon UI correctly anchors on `erpnext_bank_transaction_name` (`ACC-BTN-...`) which is what ERPNext needs for the JE link. No action required unless you want cross-source dedup in the DB itself; that's a separate ingest-time fix for housekeeping. --- ## 10. How to test (without writing to ERPNext) ```python # Inside the core-api container: docker exec finanly-core-api-1 python -c " import uuid from types import SimpleNamespace from finanly_core_api.routers.recon import _build_shopify_preview_payload req = SimpleNamespace(state=SimpleNamespace( tenant_id=uuid.UUID('837eebaa-1ae8-5b79-95c3-26649fb25c42'), request_id='probe', correlation_id='probe', )) shopify_iid = uuid.UUID('3c7e91d9-8a2d-5382-9da0-dd2d7bfb8213') out = _build_shopify_preview_payload( request=req, shopify_integration_instance_id=shopify_iid, payout_id='130822176938', company='Pascucci USA Inc', ) print(out['summary']) for e in out['gl_entries']: print(e) " ``` This calls Shopify's API and runs the preview engine but **does NOT persist anything** in the DB and does NOT touch ERPNext. Pure read-side verification. To verify zero side-effects on ERPNext: ```bash docker exec erpnext-mariadb bash -c 'mysql -uroot -p"CJ395nh!capula" \ erpnext_recovered_20250814 -N -B -e " select count(*) from \`tabJournal Entry\` where company=\"Pascucci USA Inc\" and user_remark like \"%Finanly%\""' ``` --- ## 11. Known gaps / future work 1. **Order-level money decomposition is NOT yet implemented.** The connector still doesn't pull `currentTotalTaxSet`, `currentShippingPriceSet`, `currentTotalDiscountsSet`, etc. When a `charge` is booked today, tax/shipping/discount are baked into the gross charge amount (lumped into `Product Sales - {family}`), not split into separate GL lines. The user's CSV anticipates these splits (`shipping_income`, `discount`, `sales_tax` components in `PASCUCCI_SHOPIFY_ORDER_COMPONENTS`); wiring requires extending `_fetch_fulfilled_orders` and `order_line_items` GraphQL queries plus a per-order itemizer in the preview builder. 2. **Sales tax** on charges currently lives in `Product Sales - {family}` rather than `Sales Tax Payable - PUI` because the underlying balance transaction doesn't carry tax — tax lives on the order. Split when (1) is done. 3. **Other tenants** (Nourishing Inc, Fine Line LLC, Ad Astrum LLC) still use the legacy `PASCUCCI_MAPPINGS` path with non-existent account names. Posting WILL fail at JE-post time with ERPNext "account not found". To extend: query each tenant's chart of accounts, build a `*_SHOPIFY_TXN_TYPE_MAP` analogous to the Pascucci one, and dispatch in the `is_pascucci` branch of `_build_shopify_preview_payload`. 4. **Webhooks**: Shopify API 2025-07 has no payout webhook topics. Daily polling is the only mechanism. Order/refund/dispute webhooks exist but are not wired (per user direction: bank-side trigger is the only one we care about). 5. **Historic backfill** for tenants other than Pascucci: not done. Run a one-shot `SyncRun(run_kind='backfill', resources={"payouts","fulfillments"}, since=integration.created_at)` per tenant once their account map is in place. --- ## 12. Quick troubleshooting ### Preview returns HTTP 502 `shopify_payout_reconciliation_drift` The sum of `gl_entries` does not match `payout_net` to the cent. Inspect `transactions[]` in the response — there's a balance-transaction whose sign is being misread, or a fee that isn't being subtracted, or a balance-transaction type that's being routed wrong. The diff in the response payload tells you the magnitude. ### Preview returns HTTP 412 `shopify_payout_unmapped_balance_transactions` A balance transaction has type `SKIP-FLEXPORT` or `NOT-USED`. Either: (a) The user did something they said they wouldn't (e.g. bought a Shopify shipping label). Add an active mapping for that type or refuse to post and handle manually. (b) An entirely new balance-transaction type appeared (none is currently None). Add it to `PASCUCCI_SHOPIFY_TXN_TYPE_MAP` with the right routing. ### Nightly sync didn't materialize new payouts Check `sync_runs.resources` — if it shows `["accounts","transactions"]` for a Shopify run, `_default_sync_resources_for` regressed. Should be `["payouts","fulfillments"]`. ### JE posting fails with ERPNext "account not found" The account names in the live `erpnext_recovered_20250814` DB don't match the constants in `product_classifier.py` (`ACCT_*`). Compare `tabAccount where company='Pascucci USA Inc'` against the constants and reconcile. ### Tag-based classification falling back to OTHER The product or order didn't have matching tags. Check the Shopify product admin and add the right tag (or extend `_MACHINE_TAGS`/`_COFFEE_TAGS` if a new vocabulary is needed). --- ## Session 2026-05-04 — bug fixes + behavior clarifications ### Sales Returns family-specific accounts - Refunds book to per-family accounts: `Sales Returns - Machines - PUI`, `Sales Returns - Coffee - PUI`, `Sales Returns - Others - PUI` (under group `Sales Returns - PUI`, root_type=Income). - Legacy `Sales Returns & Allowances - PUI` is DISABLED. New refund postings to it are blocked by ERPNext. - Code: `PASCUCCI_SHOPIFY_REFUND_BY_CATEGORY` in `services/core-api/.../services/product_classifier.py`. ### Disposal recognition — refund-shipped vs refund-cancelled - `_disposal_decision_from_flexport` (recon.py:880) calls connector `/v1/flexport/orders/by-shopify-name/{name}` to ask Flexport "what shipped?" - Aggregates `shipped_per_dsku` and `cancelled_per_dsku` across ALL Flexport orders matching the Shopify name (handles DELIVERRSPLIT + cancelled+refilled patterns). - `flexport_shipped_units = max(shipped_per_dsku.values())`; compared to `kept_qty = original_total - refund_total`. - 5 rationale branches: `flexport_no_units_shipped`, `flexport_shipped_matches_kept`, `flexport_partial_disposal`, `flexport_shipped`, `flexport_shipped_less_than_kept`. - Disposal Loss `Return Disposal Loss - PUI` ONLY booked when `flexport_shipped > kept` (units physically shipped were refunded). - Order #1005 / #1026 / #1028 examples: `flexport_shipped_matches_kept` → 0 disposed, full refund to `Sales Returns - Machines - PUI`. ### Shipping CC — family priority (not Shopify Warehouse) - Shipping income's cost-center now follows the order's family priority: any Machine/StarterKit → `eCommerce - Machines - PUI`; else all-Coffee → `eCommerce - Coffee - PUI`; else `eCommerce - Other - PUI`. - `Shopify Warehouse - PUI` is reserved for Flexport storage charges, NOT shipping revenue. - Code: `_split_charge_amount` and `_split_refund_amount` in recon.py. ### Discount handling - Order discount books as contra-revenue debit to `Discounts - PUI` cc=`eCommerce Revenues - PUI`. - Per-order — full discount amount on the discount line (not split per family). - Example: order #1010 ($132 sub, $20 discount) booked $20 dr Discounts. ### COGS at fulfillment — Stock Entry pattern - Each fulfilled order generates a Stock Entry (Material Issue type) per `shopify_fulfillment_stock_entry`. - Items debited to family expense accounts (`Coffee - PUI` or `Machines - PUI`); credit reduces inventory at the source warehouse (`Flexport Coffee - PUI` or `Flexport Machines - PUI`). - **Starter Kit composition**: 1× `PUI-MACH-CAPSULE-WHI` (machine, $54.88) + 1× `PUI-CAP-SAMPLE-120` (sample, $33.22). Per Pascucci policy ("Starter Kit sells as Machine"), BOTH components book COGS to `Machines - PUI`. The Sample item physically lives in Flexport Coffee warehouse (s_warehouse) but its EXPENSE account is `Machines - PUI`. - Refunded items with `restocked=true` → no COGS reversal (Pascucci policy: refunded inventory stays out of books, no Material Receipt). ### Phase 11 — pre-stored balance-transactions for fast preview - `connector-shopify /v1/sync` now embeds the full balance-transactions list in `recon_items.raw_payload.transactions` per payout. - `_build_shopify_preview_payload` reads from `prefetched_transactions` first, skipping the live `/balance-transactions` Shopify call. - First click on a previously-synced payout = fast (no Shopify network hop for the txn list). - Code: connector-shopify app.py:485, recon.py:1226-1244. ### `_get_or_create_recon_link` bug fix - Function used to search for existing link by triple `(tenant, canonical_tx, recon_item)`. Unique index is on `(tenant, recon_item)` only. - When user re-clicks preview with a different canonical_tx for an already-linked payout (e.g. UI sent stale state), the lookup missed → INSERT → unique violation → 500 error. - Fixed: now searches by `(tenant, recon_item)`. If link exists for that recon_item, returns it (regardless of canonical_tx in the request). - Code: recon.py:646-668. ### UI bug — polluted recon_links - Observed pattern: clicking preview on multiple payouts in quick succession can result in the UI sending the WRONG `canonical_transaction_id` (stale state from previously-viewed payout). - This creates a polluted `recon_link` row pointing the new payout to the previous payout's bank txn. - Diagnostic: query `recon_items` left-joined with `recon_links` and `canonical_transactions`; if `link.canonical_transaction_id`'s amount/date doesn't match the recon_item's payout amount/date, the link is polluted. - Cleanup: DELETE from `recon_previews` where `recon_link_id = polluted_id`, then DELETE from `recon_links` where `id = polluted_id`. The payout returns to "no link" state and user re-categorizes correctly. ### Pascucci-specific data points (as of 2026-05-04) - Total payouts in this 90-day window: 16 (15 originally + 1 newly-synced $185.32 May 5 in_transit) - Categorized: 11 (with their respective JEs) - Most recent payout categorized: 130796257450 ($96.03, May 1) → JE 00442 - Most recent order seen: #1046 (in May 5 Scheduled $96.03 payout 130883027114, processed 2026-05-02) - Coffee-only orders observed: 0 (every Pascucci order so far contains a Starter Kit / machine product) - Per-cent reconciliation invariant: bank-line dr = Shopify-fresh-net = expected payout amount, verified for all 11 categorized payouts ### Known fragility (accept-and-monitor) - **UI sometimes sends wrong `canonical_transaction_id` on preview click** (stale state from previously-viewed payout). Backend tolerates it (lookup by `(tenant, recon_item)` returns existing link regardless of caller's canonical_tx) but doesn't auto-correct. If a polluted link is created, export endpoint will 404 because `ReconLink.canonical_transaction_id == req.canonical_transaction_id` mismatches. Cleanup is manual via DB DELETE on `recon_links` + `recon_previews` for that recon_item. - **Stock Entry cost-center misalignment for mixed bundles.** Coffee items (e.g., MORORA, MAMA AFRICA) inside a mixed-bundle order (with a Starter Kit/Machine) inherit the order's per-order priority cost-center `eCommerce - Machines - PUI` instead of `eCommerce - Coffee - PUI`. Account is correct (`Coffee - PUI` for COGS); only the COST CENTER dimension on the STE line is misaligned. Inventory side correctly uses warehouse-physical split (`Flexport Coffee - PUI` warehouse for Coffee items). - **Duplicate `canonical_transactions` per Shopify deposit.** Two distinct sync sources create twin rows per Mercury deposit ("Shopify" and "Other - Shopify"). Only the "Other -" row is currently used for matching; the other is dead weight. Cosmetic; doesn't affect categorization correctness.