Product catalog synchronization between website and marketplaces

Catalog Synchronization Between Website and Marketplaces

Manually maintaining current catalogs on 3–5 marketplaces is unrealistic with large product ranges. A synchronization system transmits new products, updates changes in descriptions and attributes, and removes discontinued items.

Mapping Data Schema

CREATE TABLE marketplace_product_mappings (
    id              BIGSERIAL PRIMARY KEY,
    product_id      BIGINT REFERENCES products(id),
    marketplace     TEXT,              -- 'ozon', 'wb', 'ym'
    external_id     TEXT,              -- ID on marketplace
    external_sku    TEXT,              -- SKU on marketplace (may differ)
    status          TEXT,              -- 'active', 'pending', 'error', 'removed'
    last_synced_at  TIMESTAMPTZ,
    sync_hash       CHAR(64),          -- SHA-256 of synced fields
    error_message   TEXT,
    UNIQUE (product_id, marketplace)
);

Change Detection

class ProductChangeDetector
{
    // Fields whose changes require re-synchronization
    private array $trackFields = [
        'name', 'description', 'brand', 'sku', 'price',
        'category_id', 'attributes', 'images'
    ];

    public function hasChanges(Product $product, string $marketplace): bool
    {
        $mapping = $product->marketplaceMappings()->where('marketplace', $marketplace)->first();
        if (!$mapping) return true;  // new product — needs sync

        $currentHash = $this->computeHash($product);
        return $currentHash !== $mapping->sync_hash;
    }

    private function computeHash(Product $product): string
    {
        $data = $product->only($this->trackFields);
        $data['images'] = $product->images->pluck('url')->sort()->values()->all();
        return hash('sha256', json_encode($data, JSON_SORT_KEYS));
    }
}

Category Mapping

Each marketplace has its own category tree. Without mapping, products cannot be listed:

class CategoryMapper
{
    // Stored in DB, managed through UI
    public function getMarketplaceCategory(int $siteCategoryId, string $marketplace): ?int
    {
        return DB::table('category_mappings')
            ->where('site_category_id', $siteCategoryId)
            ->where('marketplace', $marketplace)
            ->value('marketplace_category_id');
    }

    // For Ozon, use name-based search
    public function suggestOzonCategory(string $categoryName): array
    {
        return Http::withHeaders($this->ozonHeaders)
            ->post('https://api-seller.ozon.ru/v1/description-category/search', [
                'language' => 'DEFAULT',
                'query'    => $categoryName,
            ])
            ->json('result');
    }
}

Synchronization Handler

class CatalogSyncJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue;

    public int $tries   = 3;
    public int $backoff = 300;  // retry in 5 minutes

    public function handle(): void
    {
        $products = Product::where('active', true)->get();
        $detector = app(ProductChangeDetector::class);

        foreach (['ozon', 'wb', 'ym'] as $marketplace) {
            $toSync = $products->filter(fn($p) => $detector->hasChanges($p, $marketplace));

            $toSync->chunk(50)->each(function ($chunk) use ($marketplace) {
                $adapter = $this->getAdapter($marketplace);
                foreach ($chunk as $product) {
                    try {
                        $adapter->upsertProduct($product);
                        $this->updateMapping($product, $marketplace, 'active');
                    } catch (Exception $e) {
                        $this->updateMapping($product, $marketplace, 'error', $e->getMessage());
                        Log::error("Catalog sync failed", compact('marketplace', 'e'));
                    }
                }
            });
        }
    }
}

Synchronization Status Dashboard

-- Current catalog state by marketplaces
SELECT
    marketplace,
    COUNT(*) FILTER (WHERE status = 'active')  AS active,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending,
    COUNT(*) FILTER (WHERE status = 'error')   AS errors,
    MAX(last_synced_at) AS last_sync
FROM marketplace_product_mappings
GROUP BY marketplace;

Timeline

Catalog synchronization system for 3 marketplaces with dashboard: 18–24 business days.