Data Model

Tables

• `products` (uuid, identifier, enabled, family_uuid, timestamps)
• `product_attributes` (uuid, code, type, group, labels, properties)
• `product_attribute_values` (uuid, product_uuid, attribute_code, channel, locale, value (JSON), unit)

- FK: (`product_uuid`) → `products.uuid` (cascade on delete)

- FK: (`attribute_code`) → `product_attributes.code`

- Notes: stores JSON `value` and optional `unit` for metric attributes; one row per (product, attribute_code, channel, locale).

• `categories` (uuid, code, labels, parent_uuid)
• `families` (uuid, code, labels, attribute_requirements)
• `category_product` (pivot)
• `integrations` (uuid, name, identifier, driver, configuration, filters, priorities, retries)
• `mutations` (uuid, entity_type, entity_uuid, operation, changed_fields, previous, payload)

- Indexes: `entity_uuid`, `entity_type`, `(entity_uuid, entity_type)`, `operation`, plus generated columns: `payload_identifier`, `payload_code`, and time-based indexes (`created_at`, `(entity_type, created_at)`).

• `mutation_jobs` (uuid, mutation_uuid, integration_uuid, status, priority, attempts, last_attempt_at, last_error)

- Indexes: `status`, `priority`, `(mutation_uuid, status)`, `(integration_uuid, status)`, `updated_at`.

• `entity_mappings` (uuid, integration_uuid, mappable_id, mappable_type, external_id, external_data)

- Uniqueness: one mapping per (integration_uuid, mappable_id, mappable_type).

- Indexes: `external_id`, `(integration_uuid, mappable_type, mappable_id)` for dashboards.

• `settings` (grouped app settings via spatie/laravel-settings)

- Stores per-entity sync markers under groups:

- `pim_attribute.updated_at`, `pim_product.updated_at`, `pim_family.updated_at`, `pim_category.updated_at`

- Timestamps are stored as JSON strings and read as ISO 8601 datetimes by the import filter builders.

Eloquent Models

• Product, Category, Family, ProductAttribute, ProductAttributeValue
• Mutation, MutationJob, Integration, EntityMapping

Constraints & Validations

• Entity UUIDs are primary keys (UUID v4 recommended); foreign keys enforce referential integrity (deleting a product cascades its EAV values).
• `product_attributes.code` is unique and referenced by EAV rows; ensure attribute imports run before product imports.
• Generated columns on `mutations` allow indexed lookups by identifier/code without JSON scan.
• `MutationJob.status` is string-based; expected values include: `pending`, `queued`, `processing`, `success`, `failed`.
• Integration `priority_map` determines numeric job priority (lower is higher priority) and thus queue selection.

Integration Configuration Notes

• `integrations.field_mapping` is reserved for driver-specific mappings (e.g., Magento 2 website/store mapping under `field_mapping.websites`).

Integration Payload

Each entity exposes `toIntegrationPayload()` to produce a consistent, integration-ready payload. Example for `Product` includes identifier, enabled, completeness, attributes (EAV values), categories, and updated_at.

Entity Mapping

`DatabaseEntityMappingService` provides lookup and creation of external ID mappings for a given entity UUID + integration UUID. Drivers should consult this before creating external entities and should persist mappings after creation.

For Magento 2, `external_data` stores the latest raw JSON body returned by the REST API for the created/updated entity.

External refs:

• Eloquent models: https://laravel.com/docs/12.x/eloquent