Multi-Vendor E-Commerce Marketplace
CS-Cart marketplace where a single customer order splits into per-vendor child orders, commission is captured at split time, and vendor earnings settle through a Stripe Connect payout ledger.
- Client
- Confidential · E-commerce
- Role
- {{TODO: e.g. Solution Architect / Lead Backend Engineer}}
- Period
- {{TODO: e.g. 2022–2023}}
Summary
Overview
The platform is a CS-Cart multi-vendor marketplace where independent sellers list products under a unified storefront while the operator owns payments, disputes and financial settlement. A single customer basket can contain items from several vendors, so every order is split into per-vendor child orders that ship and settle independently.
The architectural core is order orchestration plus payout accounting: a customer's parent order fans out into child orders scoped by company_id, commission is deducted at split time, and each vendor's earnings accrue in a payout ledger that is later settled in batches through Stripe Connect.
Context
The Problem
A marketplace has to look like one store to the customer and like many independent businesses to its vendors. One checkout, one payment, one refund surface — but N sellers who each ship, get rated, and get paid separately.
Constraints
- A single basket may span multiple vendors, yet the customer pays once.
- Each vendor must settle independently, with commission accounted for and auditable.
- Vendor A must never see Vendor B's orders, inventory or balances on shared infrastructure.
- Full-text discovery across a large aggregated catalog without overloading MySQL.
- Scale within a single region and a single primary database before sharding is justified.
Scope
Requirements
Functional
- ▹Multi-vendor catalog — vendors manage products, storefront aggregates inventory
- ▹Order splitting — one customer order into per-vendor child orders shipped independently
- ▹Vendor panel — self-service inventory, sales, payout history and comms
- ▹Payment orchestration — marketplace collects, then splits commission and vendor share
- ▹Vendor onboarding — registration, KYC-lite, store setup, fee and plan assignment
- ▹Payout workflows — automated and manual withdrawal via Stripe Connect or bank transfer
- ▹Vendor rating and reputation aggregated from order-level feedback
Non-functional
- ▹Tenant isolation — vendor data scoped by company_id, no cross-vendor queries by design
- ▹Full-text product and vendor discovery via OpenSearch, not MySQL full-text
- ▹Multi-language storefront via lang_code in the schema
- ▹Stateless nginx and php-fpm tier for horizontal scale behind the load balancer
- ▹Redis-backed cache and locks so horizontal scale stays cache-coherent
System Design
Architecture
Order Router
Splits a parent order into per-vendor child orders keyed on company_id, linking children to the parent and deducting commission at split time so downstream refunds and payouts reconcile cleanly.
Payout Service
Maintains the vendor earnings ledger with typed entries for orders, refunds, withdrawals and adjustments plus an approval workflow, then initiates Stripe Connect transfers for approved balances.
Multi-tenant Data Layer
Single MySQL instance with logical isolation — every vendor-owned row is scoped by company_id and access is gated by a user to company mapping rather than physical per-vendor databases.
Catalog Search
OpenSearch index kept in sync with the product catalog to serve faceted full-text discovery across the aggregated marketplace without loading the transactional database.
Cache and Locks
Redis provides shared cache and locking so the stateless php-fpm tier can scale horizontally while staying cache-coherent.
Stack
Technology Stack
Platform
Data
Payments
Frontend / Infra
Persistence
Data Model
- cscart_orders (MySQL): parent order marks the aggregated basket with is_parent_order; child orders carry parent_order_id and a company_id assigning them to a vendor, where company_id 0 is the marketplace
- cscart_order_details (MySQL): line items per order — product, quantity and price
- cscart_vendor_payouts (MySQL): vendor earnings ledger with payout_type of order, refund, withdrawal or adjustment and an approval_status of pending, approved or denied
- cscart_order_transactions (MySQL): payment event audit trail
- cscart_companies (MySQL): vendor profiles, the tenant boundary for company_id scoping
- cscart_settings_vendor_values (MySQL): per-vendor configuration overrides
Reasoning
Key Decisions & Trade-offs
Split orders at the database with commission captured at split time
trade-off
Refund logic must propagate from child back to parent, but splitting up front keeps commission and vendor share auditable and avoids reconciling a post-payment split.
Logical multi-tenancy — single MySQL scoped by company_id
trade-off
Isolation depends on disciplined query scoping rather than physical separation, but it avoids the cost and operational sprawl of a database per vendor.
Stripe Connect for vendor payouts
trade-off
Adds KYC overhead and regional coverage gaps, but each vendor owns their bank relationship and the marketplace is not a money-transmitter of record for held balances.
OpenSearch for discovery instead of MySQL full-text
trade-off
An extra service to operate and an index-sync path to keep healthy, but faceted search scales to a 100k+ SKU catalog without contending with transactional load.
Operations
Scaling & Reliability
Application tier: stateless nginx and php-fpm workers scale horizontally with no session affinity once Redis backs cache and locks.
Search: OpenSearch offloads full-text and faceted discovery from MySQL; it must be deployed as a cluster in production rather than the single node used in development.
Database is the bottleneck: a single MySQL 5.7 InnoDB primary carries orders, the vendor ledger and payout batches. Large payout runs and high-frequency updates contend for row locks, so the first scaling step is read replicas for reporting and vendor-panel reads.
Beyond the low tens of thousands of vendors, a single primary stops being sufficient and sharding by company_id becomes the path forward — the same tenant key that enforces isolation also defines a natural shard boundary. Payout settlement stays batch and cron-driven rather than event-driven.
What made this hard
Architect's Challenges
- →Fanning one customer order into per-vendor child orders while keeping commission auditable at split time
- →Orchestrating vendor payouts through a ledger and Stripe Connect batch settlement with an approval workflow
- →Enforcing tenant isolation via company_id scoping on a single shared database
- →Keeping OpenSearch discovery in sync with the catalog without overloading MySQL
Results
Outcome
- ▹Order splitting and commission-at-split model that keeps vendor settlement reconcilable
- ▹Ledger-backed payout workflow with hold, review and manual-withdrawal paths
- ▹{{TODO: add real metrics — vendors onboarded, order throughput, payout latency, SKUs indexed}}