Skip to content

Atraxion Middleware - Implementation Plan

Problem Statement

The current middleware has four main issues: 1. Full data on updates: Odoo sends complete records, making it impossible to know what changed 2. Partner hierarchy mismatch: Odoo's complex hierarchy (Main→Billing→Delivery→Contacts) cannot be represented in the current webshop 3. Stock performance: Querying stock for 2800+ products is slow 4. Price update explosion: When a pricelist changes, Odoo marks ALL 128k products as updated, causing days of processing - even though only a fraction have actual price changes

Solution Overview

Build a new Symfony middleware that: - Stores state to compute field-level diffs when Odoo pushes updates - Preserves hierarchy for partners to support future webshop upgrade - Queues outbound sync for orders and customers going to Odoo - Provides a new API optimized for the webshop's needs

Phase 1: Core Entities & Database Schema

Entities to Create

Product Entity (src/Entity/Product.php) - id, createdAt, updatedAt - odooId (nullable, Odoo's internal ID) - externalId (unique, webshop reference) - currentData (JSON - latest data from Odoo) - previousData (JSON - data before last update, for diff computation) - changedFields (JSON array - list of fields that changed in last update) - processingState, errorMessage, retryCount - context (price_update, product_spec, etc.) - isNew (boolean - true if first sync)

Partner Entity (src/Entity/Partner.php) - id, createdAt, updatedAt - odooId, externalId - type (B=Billing, C=Contact, D=Delivery, O=Anonymous Shipment Address) - parentId (self-referencing FK for hierarchy) - currentData, previousData, changedFields - processingState, errorMessage, retryCount - isNew

SaleOrder Entity (src/Entity/SaleOrder.php) - id, createdAt, updatedAt - odooId, externalId - currentData, previousData, changedFields - processingState, errorMessage, retryCount - direction (INBOUND from Odoo, OUTBOUND to Odoo) - isNew

Stock Entity (src/Entity/Stock.php) - id, createdAt, updatedAt - supplierExternalId - productExternalId - quantity - purchasePrice, suggestedSalePrice - supplierProductId - Index on (supplierExternalId, productExternalId)

PricelistItem Entity (src/Entity/PricelistItem.php) - id, createdAt, updatedAt - pricelistExternalId (e.g., "-4" for GARAGE) - pricelistName (e.g., "GARAGE") - productExternalId (FK-like reference to product) - price (nullable decimal) - startDate, endDate (nullable) - isPromoPrice, isAction, untilAtxStockIs0 (booleans) - previousPrice (for change detection) - priceChanged (boolean - true if price differs from previous) - Composite index on (pricelistExternalId, productExternalId)

PurchaseOrder Entity (src/Entity/PurchaseOrder.php) - id, createdAt, updatedAt - odooId, name (e.g., "PO0151232") - vendor, state, createdBy - currentData, previousData, changedFields - processingState, errorMessage, retryCount - isNew

PurchaseOrderLine Entity (src/Entity/PurchaseOrderLine.php) - id - purchaseOrderId (FK to PurchaseOrder) - productExternalId - name, quantity, qtyReceived, priceUnit - datePlanned - repliedByVendor (JSON for vendor response)

OutboundQueue Entity (src/Entity/OutboundQueue.php) - id, createdAt - entityType (partner, sale_order) - entityId - payload (JSON) - status (pending, processing, completed, failed) - attempts, lastAttemptAt, lastError - completedAt

Files to Create/Modify

  • src/Entity/Product.php
  • src/Entity/Partner.php
  • src/Entity/SaleOrder.php
  • src/Entity/Stock.php
  • src/Entity/OutboundQueue.php
  • migrations/Version*.php (generated)

Phase 2: Change Detection Service

Core Service: ChangeDetectionService

class ChangeDetectionService
{
    public function detectChanges(array $newData, ?array $previousData): ChangeResult
    {
        // Returns: changedFields[], isNew, hasChanges
    }

    public function computeFieldDiff(array $oldData, array $newData): array
    {
        // Deep comparison, handles nested objects/arrays
        // Returns list of field paths that changed: ['name', 'price', 'category.name']
    }
}

ChangeResult DTO

  • changedFields: array of field paths
  • isNew: boolean (no previous data)
  • hasChanges: boolean

Files to Create

  • src/Service/ChangeDetectionService.php
  • src/DTO/ChangeResult.php
  • tests/Unit/Service/ChangeDetectionServiceTest.php

Phase 3: Inbound API (Odoo → Middleware)

Endpoints

POST /api/v1/products - Receive product update from Odoo - Accept product data (single or batch) - Compute changes against stored state - Store new state with change metadata - Return acknowledgment

POST /api/v1/partners - Receive partner update from Odoo - Handle hierarchy (resolve parent references) - Compute changes - Store with change metadata

POST /api/v1/sale-orders - Receive sale order update from Odoo - Store order with lines, sets, pickings - Compute changes

POST /api/v1/stocks - Receive stock update from Odoo - Bulk update stock levels - Efficient upsert (update existing, insert new)

Request/Response Format

// POST /api/v1/products (Odoo pushes)
{
  "products": [
    { "id": 383069, "external_id": "-383069", "name": "...", ... }
  ]
}

// Response
{
  "success": true,
  "processed": 1,
  "errors": []
}

Files to Create

  • src/Controller/Api/V1/ProductController.php
  • src/Controller/Api/V1/PartnerController.php
  • src/Controller/Api/V1/SaleOrderController.php
  • src/Controller/Api/V1/StockController.php
  • src/Service/ProductSyncService.php
  • src/Service/PartnerSyncService.php
  • src/Service/SaleOrderSyncService.php
  • src/Service/StockSyncService.php

Phase 4: Outbound API (Middleware → WebShop)

Endpoints

GET /api/v1/updated-products - Fetch products with changes - Query params: since (timestamp), limit, offset - Returns products with changedFields metadata - Includes isNew flag for new products

GET /api/v1/updated-partners - Fetch partners with changes - Same pattern as products - Includes hierarchy info (parentExternalId)

GET /api/v1/updated-sale-orders - Fetch sale orders with changes - Filter: direction=inbound (from Odoo)

GET /api/v1/stocks - Fetch current stock levels - Query by supplier, product, or get all - Efficient pagination

DELETE /api/v1/updated-products/{id} - Mark product update as processed POST /api/v1/delete-updated-products - Batch mark as processed

Response Format with Change Metadata

{
  "payload": [
    {
      "id": 123,
      "externalId": "-383069",
      "isNew": false,
      "changedFields": ["name", "purchase_price", "product_specifications[0].value"],
      "data": { /* full product data */ }
    }
  ],
  "meta": {
    "total": 150,
    "limit": 50,
    "offset": 0
  }
}

Files to Create

  • src/Controller/Api/V1/UpdatedProductController.php
  • src/Controller/Api/V1/UpdatedPartnerController.php
  • src/Controller/Api/V1/UpdatedSaleOrderController.php
  • src/Controller/Api/V1/StockQueryController.php
  • src/DTO/UpdatedProductResponse.php
  • src/DTO/UpdatedPartnerResponse.php
  • src/DTO/UpdatedSaleOrderResponse.php

Phase 5: Outbound Queue (Middleware → Odoo)

Queue Processing

Queue new customers/orders from webshop: - WebShop POSTs to middleware - Middleware stores and queues for Odoo sync - Background worker processes queue - Retry on failure with exponential backoff

Endpoints

POST /api/v1/partners (from WebShop) - Accepts new partner data - Stores locally - Queues for Odoo sync - Returns middleware ID immediately

POST /api/v1/sale-orders (from WebShop) - Same pattern for orders

GET /api/v1/queue/status/{entityType}/{entityId} - Check sync status

Queue Worker

Symfony Messenger component for: - Processing outbound items - Retry handling (3 attempts, exponential backoff) - Dead letter handling for persistent failures - Admin notifications on backlog

Files to Create

  • src/Message/SyncToOdooMessage.php
  • src/MessageHandler/SyncToOdooHandler.php
  • src/Service/OdooClient.php (HTTP client for Odoo API)
  • config/packages/messenger.yaml

Phase 6: Partner Hierarchy

Hierarchy Storage

Partners store parentId (FK to self) representing: 

Main Account (type=null or M?)
  └── Billing Account (type=B)
        └── Delivery Address (type=D)
              └── Contact/User (type=C)

Online addresses (type=O) are linked to their parent account.

Hierarchy API

GET /api/v1/partners/{id}/hierarchy - Get full tree from any node GET /api/v1/partners/{id}/children - Get immediate children GET /api/v1/partners/{id}/parent - Get parent

Resolution Service

When processing partner updates: 1. Resolve external_parent_id to internal parentId 2. Create placeholder if parent not yet synced 3. Update placeholders when actual parent arrives

Files to Create

  • src/Service/PartnerHierarchyService.php
  • src/Controller/Api/V1/PartnerHierarchyController.php

Phase 7: Price & Purchase Order Extraction

The Price Update Problem

When a pricelist changes in Odoo, it marks ALL 128k products as updated. The webshop currently processes all of them, taking days.

Solution: Extract Prices to Separate Entity

When products arrive with embedded prices array: 1. Extract each price item to PricelistItem table 2. Compare with previously stored price for that (pricelist, product) pair 3. Set priceChanged = true only if price actually differs 4. Product's changedFields does NOT include prices (they're separate)

API for Price Updates

GET /api/v1/updated-prices - Fetch pricelist items where priceChanged = true - Returns: pricelistExternalId, productExternalId, price, isNew - After processing, webshop marks items as processed

GET /api/v1/pricelists/{pricelistId}/items - Get all items for a pricelist - For full sync scenarios

Purchase Order Extraction

Same pattern: 1. Extract purchase_orders from products into PurchaseOrder + PurchaseOrderLine 2. Track changes separately 3. Products don't carry PO data in their changedFields

GET /api/v1/updated-purchase-orders - Fetch changed POs GET /api/v1/purchase-orders/{id}/lines - Get lines for a PO

Future: Direct Endpoints

Phase 2 adds direct push endpoints from Odoo: - POST /api/v1/pricelists - Direct pricelist updates (skips product extraction) - POST /api/v1/purchase-orders - Direct PO updates

Files to Create

  • src/Service/PriceExtractorService.php
  • src/Service/PurchaseOrderExtractorService.php
  • src/Controller/Api/V1/PricelistController.php
  • src/Controller/Api/V1/PurchaseOrderController.php

Phase 8: Stock Optimization

Note: Phase numbering updated - Authentication moved to Phase 9.

Efficient Stock Storage

Instead of JSON blobs, use normalized table: - stock table with (supplier_id, product_id, quantity, prices) - Composite index for fast lookups - Bulk upsert for updates

Delta Responses

Track stock changes: - Add previousQuantity column or separate change log - Return only changed stock in API response - Support full catalog fetch when needed

Files to Modify

  • src/Entity/Stock.php (normalized structure)
  • src/Service/StockSyncService.php (bulk upsert)
  • src/Controller/Api/V1/StockQueryController.php (delta support)

Phase 9: Authentication & Security

API Authentication

Use the existing User entity with API tokens: - ApiToken entity linked to User - Token validation middleware - Rate limiting per token

Endpoints Security

  • Odoo endpoints: Odoo API token (env variable)
  • WebShop endpoints: WebShop API token
  • Admin endpoints: User JWT authentication

Files to Create

  • src/Entity/ApiToken.php
  • src/Security/ApiTokenAuthenticator.php
  • config/packages/security.yaml (update)

Implementation Order

Phase A: Foundation

  1. ✅ User auth (already done)
  2. Create core entities: Product, Partner, SaleOrder, Stock
  3. Create extraction entities: PricelistItem, PurchaseOrder, PurchaseOrderLine
  4. Run migrations
  5. Create ChangeDetectionService with tests
  6. Create PriceExtractorService with tests

Phase B: Inbound API (Odoo → Middleware)

  1. Product sync endpoint + service (extracts prices & POs)
  2. Partner sync endpoint + service (with hierarchy resolution)
  3. SaleOrder sync endpoint + service
  4. Stock sync endpoint + service

Phase C: Outbound API (Middleware → WebShop)

  1. Updated products API (with changedFields, excludes embedded prices)
  2. Updated partners API (with hierarchy info)
  3. Updated pricelists API (only actually changed prices)
  4. Updated purchase orders API
  5. Stock query endpoints
  6. Batch acknowledge endpoints

Phase D: Outbound Queue & Sync Back

  1. Outbound queue (Messenger) for orders/customers to Odoo
  2. OdooClient service for API calls
  3. Retry logic and dead letter handling

Phase E: Security & Polish

  1. API token authentication
  2. Rate limiting
  3. Integration tests
  4. Admin monitoring endpoints

Verification

Unit Tests

  • ChangeDetectionService - field comparison logic
  • Sync services - data transformation

Integration Tests

  • Full flow: Odoo push → Store → WebShop fetch
  • Queue processing
  • Error handling and retry

Manual Testing

  1. POST product data to /api/v1/products
  2. Verify storage with changed fields computed
  3. GET /api/v1/updated-products and verify changedFields
  4. DELETE to mark as processed
  5. Test hierarchy with nested partners
  6. Test stock bulk updates

Critical Files Summary

New Entities

  • src/Entity/Product.php
  • src/Entity/Partner.php
  • src/Entity/SaleOrder.php
  • src/Entity/Stock.php
  • src/Entity/PricelistItem.php ← NEW: Extracted prices
  • src/Entity/PurchaseOrder.php ← NEW: Extracted POs
  • src/Entity/PurchaseOrderLine.php ← NEW: PO line items
  • src/Entity/OutboundQueue.php
  • src/Entity/ApiToken.php

Core Services

  • src/Service/ChangeDetectionService.php - Field-level diff computation
  • src/Service/PriceExtractorService.php ← NEW: Extracts prices from products
  • src/Service/PurchaseOrderExtractorService.php ← NEW: Extracts POs from products
  • src/Service/ProductSyncService.php
  • src/Service/PartnerSyncService.php
  • src/Service/PartnerHierarchyService.php
  • src/Service/SaleOrderSyncService.php
  • src/Service/StockSyncService.php
  • src/Service/OdooClient.php

Controllers (Inbound from Odoo)

  • src/Controller/Api/V1/ProductController.php
  • src/Controller/Api/V1/PartnerController.php
  • src/Controller/Api/V1/SaleOrderController.php
  • src/Controller/Api/V1/StockController.php

Controllers (Outbound to WebShop)

  • src/Controller/Api/V1/UpdatedProductController.php
  • src/Controller/Api/V1/UpdatedPartnerController.php
  • src/Controller/Api/V1/UpdatedSaleOrderController.php
  • src/Controller/Api/V1/PricelistController.php ← NEW: Price updates
  • src/Controller/Api/V1/PurchaseOrderController.php ← NEW: PO updates
  • src/Controller/Api/V1/PartnerHierarchyController.php
  • src/Controller/Api/V1/StockQueryController.php

Key Architecture Decisions

  1. Change Detection: Middleware stores previous state to compute field-level diffs
  2. Price Extraction: Prices stored separately from products to avoid 128k update explosions
  3. Purchase Order Extraction: POs stored separately for same reason
  4. Partner Hierarchy: Self-referencing FK preserves full Odoo hierarchy
  5. Outbound Queue: Symfony Messenger for reliable sync back to Odoo
  6. Hybrid Approach: Initially extract from embedded data, add direct endpoints later