Stub System for Dependency Resolution

Status: Design in progress Date: 2026-02-01

Problem Statement

When syncing data between Odoo and the webshop, dependency issues occur. For example: - A sale_order references a partner that hasn't been synced yet - A sale_order line references a product that doesn't exist or failed to sync

Currently, records get stuck in AWAITING_DEPENDENCY state waiting for data that may never arrive (if Odoo has issues).

Solution Overview

Use the legacy Odoo/Middleware PostgreSQL database as a fallback data source. When a dependency is missing:

Query the legacy PostgreSQL tables (read-only)
If the dependency exists there, create a stub record in MySQL
The stub contains minimal data (external_id, odoo_id, key fields)
Processing can continue without waiting for Odoo
When Odoo eventually pushes full data, the stub gets upgraded to a complete record

Benefits

Self-healing: System continues even if Odoo crashes or has delays
No duplicate updates: We only pull identifiers, not full data. Odoo still owns the data.
Visibility: STUB state shows how often the fallback is used (indicates Odoo health)

Architecture

Database Connections

┌─────────────────────────────────────────────────────────────┐
│                     Symfony Middleware                       │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  ┌─────────────────┐          ┌─────────────────────────┐  │
│  │ MySQL (Primary) │          │ PostgreSQL (Legacy, RO) │  │
│  │                 │          │                         │  │
│  │ - product       │  ◄────── │ - product               │  │
│  │ - partner       │  lookup  │ - partner               │  │
│  │ - sale_order    │          │ - sale_order            │  │
│  │ - stock         │          │ - stock                 │  │
│  │ - etc.          │          │                         │  │
│  └─────────────────┘          └─────────────────────────┘  │
│         ▲                                                   │
│         │ write                                             │
│         │                                                   │
└─────────────────────────────────────────────────────────────┘

Doctrine Configuration

Two entity managers: - default - MySQL (read/write) - existing entities - legacy - PostgreSQL (read-only) - legacy table mappings

# config/packages/doctrine.yaml
doctrine:
    dbal:
        default_connection: default
        connections:
            default:
                url: '%env(resolve:DATABASE_URL)%'
            legacy:
                url: '%env(resolve:LEGACY_DATABASE_URL)%'
    orm:
        default_entity_manager: default
        entity_managers:
            default:
                connection: default
                mappings:
                    App:
                        type: attribute
                        dir: '%kernel.project_dir%/src/Entity'
                        prefix: 'App\Entity'
            legacy:
                connection: legacy
                mappings:
                    Legacy:
                        type: attribute
                        dir: '%kernel.project_dir%/src/Entity/Legacy'
                        prefix: 'App\Entity\Legacy'

Environment variable needed:

LEGACY_DATABASE_URL="postgresql://user:pass@host:5432/legacy_db"

Entities Supported

Partner (Primary use case)

Partners have the most dependency issues due to parent/child hierarchies.

Legacy table columns used: - id (internal legacy ID, not used) - external_id - unique identifier - odoo_id - Odoo record ID - json_data - contains type field (B/C/D/O)

Stub record contains: - external_id (from legacy) - odoo_id (from legacy) - type (parsed from json_data if available, nullable) - processing_state = STUB

Product (Secondary use case)

Products can block sale_order lines.

Legacy table columns used: - external_id - unique identifier - odoo_id - Odoo record ID - json_data - contains context field - context column (direct)

Stub record contains: - external_id (from legacy) - odoo_id (from legacy) - context (from legacy context column or json_data, nullable) - processing_state = STUB

Processing State Changes

New enum value added to ProcessingState:

enum ProcessingState: string
{
    case PENDING = 'pending';
    case PROCESSING = 'processing';
    case PROCESSED = 'processed';
    case FAILED = 'failed';
    case AWAITING_DEPENDENCY = 'awaiting_dependency';
    case STUB = 'stub';  // NEW: Created from legacy data lookup
}

State Transitions

                                  Odoo pushes full data
    STUB ─────────────────────────────────────────────► PENDING ──► PROCESSED
     ▲
     │ create stub from legacy
     │
AWAITING_DEPENDENCY ◄──── dependency missing, not in legacy

Service Design

LegacyDataService

New service to query legacy PostgreSQL:

class LegacyDataService
{
    public function __construct(
        private EntityManagerInterface $legacyEntityManager
    ) {}

    public function findPartner(string $externalId): ?LegacyPartner;
    public function findPartnerByOdooId(int $odooId): ?LegacyPartner;
    public function findProduct(string $externalId): ?LegacyProduct;
    public function findProductByOdooId(int $odooId): ?LegacyProduct;
}

StubCreationService

Creates stub records from legacy data:

class StubCreationService
{
    public function createPartnerStub(LegacyPartner $legacy): Partner;
    public function createProductStub(LegacyProduct $legacy): Product;
}

Updated Sync Services

PartnerSyncService and ProductSyncService modified:

When dependency missing, call LegacyDataService
If found in legacy, call StubCreationService
If not found in legacy, keep AWAITING_DEPENDENCY state

Stub Upgrade Flow

When Odoo pushes full data for an existing stub:

Sync service finds existing record by external_id
Record has processing_state = STUB
Update with full data
Change state to PROCESSED
Log: "Stub upgraded to full record" (for monitoring)

Legacy Entity Mappings

Read-only entities in src/Entity/Legacy/:

#[ORM\Entity(repositoryClass: LegacyPartnerRepository::class, readOnly: true)]
#[ORM\Table(name: 'partner')]
class LegacyPartner
{
    #[ORM\Id]
    #[ORM\Column]
    private int $id;

    #[ORM\Column(name: 'external_id', length: 255)]
    private string $externalId;

    #[ORM\Column(name: 'odoo_id', nullable: true)]
    private ?int $odooId = null;

    #[ORM\Column(name: 'json_data', type: 'json', nullable: true)]
    private ?array $jsonData = null;

    // Getters only, no setters (read-only)

    public function getType(): ?string
    {
        return $this->jsonData['type'] ?? null;
    }
}

Open Questions

To be decided:

Stub upgrade logging - How much to log when stub becomes full record?
Option A: Just log the transition
Option B: Track metrics (stub_created_at, time to upgrade)
Failed legacy lookups - What if legacy DB is also unavailable?
Option A: Keep AWAITING_DEPENDENCY, retry later
Option B: Fail fast with clear error
Stale stubs - What if a stub never gets upgraded (orphaned)?
Option A: Report in monitoring
Option B: Scheduled cleanup job
Option C: Leave as-is, manual review

Testing Strategy

Critical paths to test:

Happy path: Dependency missing → found in legacy → stub created → Odoo pushes full data → stub upgraded
Legacy miss: Dependency missing → not in legacy → stays AWAITING_DEPENDENCY
Duplicate prevention: Stub exists → Odoo pushes data → updates (doesn't create duplicate)
Connection failure: Legacy DB unavailable → graceful degradation
JSON parsing: Malformed json_data → stub created without optional fields

Implementation Order

Add STUB to ProcessingState enum
Configure second Doctrine connection
Create legacy entities (LegacyPartner, LegacyProduct)
Create LegacyDataService
Create StubCreationService
Update PartnerSyncService to use stubs
Update ProductSyncService to use stubs
Write tests for all scenarios
Manual QA with real data

Validation Feedback Loop - Future: notifications for sync failures