Products, Vendors & Catalog

This is the core commerce domain: how a product a client buys is modelled, priced, made visible (or hidden), kept in sync with vendors, and exported. It ties together the data model, the vendor adapters, and the cron jobs.

The three-table catalog model

The catalog is a three-level hierarchy. Money lives on vendor_products, not on products.

• products — the master brand/SKU a customer sees. Carries Name, Category, SubCategory, CountryID, CurrencyID (the face-value currency), ImageURL, Terms/Details/HowToUse, DeliveryMode/DeliveryTime, Validity, OriginalVendorID, IsLinkEnabled/LinkType (claim-URL delivery), PreFetchEnabled, IsActive. database/models/product.go.
• vendor_products — one vendor's offering of a product, with the denomination range and vendor economics. database/models/vendor_product.go.
• client_product_details (CPD) — a per-client discount override on a specific vendor_product. database/models/client_product_detail.go.

vendor_products fields that matter

What happens when a client has NO CPD row

getDiscountForVendorProduct (repo/vendor_product.go:129-164):

• CPD exists → client discount = cpd.Discount.
• No CPD → discount = VendorDiscount − DEFAULT_DISCOUNT, where DEFAULT_DISCOUNT = 1.0. So the platform keeps a 1% margin by default.

Fixed vs variable denominations

There is no is_range / denominations list / product_type / step field. The distinction is carried entirely by three columns on vendor_products:

At order/charge time both cases go through the same range check — FindBestDiscount (utils/discount_calc.go:249-347):

A $25 fixed card only matches denomination == 25; a range card matches anything in [min, max]. If nothing matches, the charge call fails with denomination %.2f is out of range for this product (utils/charges.go:45-51). When multiple vendors offer the same range, MergeRanges collapses them to the one with the highest VendorDiscount.

Pricing, discount & margin

Margin is the gap between what the vendor gives and what the client gets: margin = VendorDiscount − client Discount. The default 1% (DEFAULT_DISCOUNT) is the platform's baseline margin when there's no CPD. A CPD lets an admin set an explicit client discount (higher or lower). A negative discount is a markup/premium — the client pays more than face value, which is intentional (utils/charges.go:67-71).

The canonical formula

The documented single source of truth (services/order_charges.go:46) — discount applied before FX, a single round at the wallet boundary (NUMERIC(24,2)), conversion fee additive:

amount = round( face × qty × (1 − discount/100) × rate , 2 )

Voucher path (utils/charges.go CalculateCharges) and topup/eSIM path (services/order_charges.go ComputeOrderChargeBreakdown) share this shape:

1. NonDiscountedTotal = qty × denomination
2. DiscountAmount = CalculatePercentage(NonDiscountedTotal, effectiveDiscount)
3. TotalAmount = SubtractPercentage(NonDiscountedTotal, effectiveDiscount)
4. If wallet currency ≠ product currency, look up GetForexValue(source, dest) → NetAmount = TotalAmount × rate; conversionFee = 0 today (hardcoded but fully plumbed via DefaultConversionFeePercent); TotalPayable = NetAmount + HandlingFee.

All arithmetic goes through the decimal-safe Money helper (utils/money.go) to avoid float error. Forex rates come from the admin-managed forex_values table; a separate utils/forex.go pulls live XE.com mid-market rates (XE_USERNAME/XE_PASSWORD) for admin rate population, not order-time pricing.

During vendor sync

For imported vendor products that carry a price (not a discount), services/discount_calculator.go derives the % : same currency → ((face − price)/face)×100; cross-currency → convert price to face currency via forex_values first. Negative result = markup, kept as-is; missing rate → discount 0.

Grasshopper (GH): the gift-card sales surface

All gift-card products sit under the vendor whose code = "GH" (resolved via GetVendorByCode(ctx, "GH")). Gift cards are minted from fixed-amount topup variants and pushed to GH:

pushVariantToGrasshopper (http/handler/admin_grasshopper.go:785-904) does a two-sided upsert (GH + Octopus catalog) and links the GH voucher_type ID back onto the vendor_product. Only fixed-amount variants are pushable; the batch handler skips the rest. admin_esim.go mirrors this for eSIM. See the Memory Appendix for why gift cards must live under GH.

Blacklists & visibility

Two independent blacklist tables scope what a client can see. Both are admin-only — there is no client self-service.

A product blacklist is a scalpel; a vendor blacklist is a shotgun.

Enforcement — two different mechanisms

A product is visible to a client when all hold:

1. products.is_active = true AND deleted_at IS NULL.
2. There EXISTS ≥1 vendor_products row (is_active, not deleted) whose vendor is not blacklisted for the client and not globally inactive. The vendor being active is what matters — the visibility query never references CPD.
3. The product is not in client_product_blacklists for the client.

Vendor catalog sync

Keeps the platform's copy of each vendor's catalog fresh. Backed by two tables (database/models/vendor_catalog.go): vendor_catalog_snapshots (one per vendor/day) and vendor_catalog_products (the rows).

Streaming persist architecture — the core of every sync (services/vendor_catalog_service.go):

• Each page is committed in its own transaction so it lands immediately; a batch failure rolls back that page and marks the snapshot failed.
• The daily cron path also runs the DiscountCalculator per batch (cross-currency price→discount via forex_values) before insert; the async/manual path does not.
• Seed vs manual mapping: only the seed (first) vendor auto-creates master products (one per brand) + vendor_products. Automated catalog→product matching was removed for all other vendors — admins map unmapped rows manually (see admin controls).

Crons (see Jobs): vendor-catalog-sync (daily 2 AM), vendor-catalog-cleanup (Sun 3 AM, drops snapshots >30 days), vendor-catalog-retry (every minute — the inline comment saying "every 5 minutes" is stale). Manual trigger: POST /admin/vendors/:id/catalog/sync ({force:true} deletes today's snapshot first), status polled via .../catalog/sync/status.

Import pipeline (Excel upload)

Bulk create/update products, vendor_products, inventory, or client discounts from an uploaded .xlsx. Four import types (database/models/import_job.go): INVENTORY, PRODUCTS, VENDOR_PRODUCTS, CLIENT_PRODUCT_DETAILS. Each runs through a staged state machine driven by the import-processor cron (every minute):

Each processor (processor/*_processor.go) implements Dump/Sanitize/Enrich/Validate/Commit/GenerateErrorExcel. The orchestrator (services/import_orchestrator.go) is fail-fast: any stage error fails the whole job. After a validate pass with error rows, it generates a styled error .xlsx (only the bad rows + an Errors column). After an INVENTORY commit it pumps just that import's products to Valkey. Uploads cap at 50 MB, .xlsx/.xls only. Sample templates are downloadable per type.

Catalog download / export

Per-client, admin-only: GET /admin/clients/:client_id/product-catalog/export → ExportClientProductCatalog (admin_client.go:936).

• Format: Excel (.xlsx, excelize), sheet "Product Catalog", streamed as <ClientName>_Product_Catalog_<ts>.xlsx. No CSV variant.
• Per-client filtering: yes. It builds a client tenant context (context.WithValue(ctx, types.TenantIDKey, clientID)) and calls the same GetProducts(clientCtx, …, true) as the client-facing API — so blacklisted vendors/products are excluded and the Discount column is that client's effective discount. Pulls up to 10,000 products.
• 16 columns: Product ID, Name, Category, Sub Category, Image URL, Terms, Details, How To Use, Delivery Mode, Delivery Time, Validity, Fixed Denominations, Variable Denominations (Min-Max), Discount, Currency, Country. Up to two rows per product (one fixed, one variable).

Related exports: ExportVendorDiscounts (per-vendor discount sheet) and the various *Sample import templates. (A non-client-filtered "all products" export exists in the same file but isn't wired to a route.)

Vendor wallet balance auto-sync

Keeps each vendor's prepaid balance current for ops visibility. Stored per vendor per currency in vendor_wallets (database/models/vendor_wallet.go); the legacy scalar vendors.balance mirrors the first wallet for backward compatibility.

• Job jobs/vendor_balance_sync_job.go fans out with an errgroup (limit = batch size); one vendor failing never aborts the run. Each vendor call runs under a 30s timeout.
• Which vendors report balance (implement GetBalance): vouchers — epinforce, neo, grasshopper, irewardify, octopus, runa, trs, wupex; topup — seagm, wupex, dtone, octopus; eSIM — dtone, octopus.
• Manual refresh: POST /admin/vendors/:id/balances/sync (SyncVendorWalletBalance, requires HasAPI); display via GET /admin/vendors/:id/balances.

Inventory pump (how it becomes sellable)

Synced catalog + imported inventory become sellable stock only once pushed to Valkey. Two triggers: (a) after an INVENTORY import commit, the orchestrator pumps just that import's product IDs; (b) the inventory-pump cron (~every 6h) pumps broadly. Mechanics (reserved/general pools, prefetched stock) live in services/inventory.go PumpInventoryToValkey — see Jobs & cache. There is no manual "pump" button in admin; the cache refresh runs automatically after tag/untag/import operations.

APIs reference

External client API — /api/v1/* (JWT + IP whitelist)

There is no dedicated catalog-download endpoint on /api/v1 — external clients page through /products (denominations + discounts are embedded per product).

Client portal — /client/api/* (cookie auth)

Blacklists are applied transparently; the portal has no blacklist-management view.

Admin API — /admin/* (cookie session; writes behind writeMiddleware)

Admin panel controls

Server-rendered Jet (views/admin/*.jet). Every mutating action is gated by writeMiddleware and hidden for the viewer role. House rules: showToast(msg, type) not alert(); confirmAction({...})/modal not confirm().

Gotchas checklist

• Discount / ClientProductDetailID on vendor_products are computed at read time, never columns.
• Two default-discount policies (−1.0 vs −4.0) — verify the caller before touching discount math.
• Cache-invalidation web for blacklists; DeleteClientVendorBlacklist doesn't self-invalidate.
• Product-blacklist enforcement is a Go id NOT IN list, not a subquery (long lists for big blacklists).
• Automated catalog→product matching was removed — only the seed vendor auto-creates products; others need manual mapping.
• No proactive vendor low-balance alerting.
• Stale cron comments: vendor-catalog-retry runs every minute, vendor-balance-sync every 15 min.
• Schema drift: client_product_blacklists uses TIMESTAMP (no TZ) + a DB unique index; client_vendor_blacklists uses TIMESTAMPTZ with Go-enforced uniqueness only.