Magento EAV Schema Migration & UUID Identity Mapping

Prerequisite: Familiarity with the concepts introduced in Part 4 — Grpc Rest Gateway. Review it first if the terminology in this part is unfamiliar.

The EAV schema is why most Magento migrations fail.

It looks manageable from the outside: products stored across catalog_product_entity, catalog_product_entity_varchar, catalog_product_entity_int, catalog_product_entity_decimal, catalog_product_entity_datetime, and catalog_product_entity_text. Six tables, straightforward ETL job, done in a weekend.

Then you discover that attribute_id = 75 means “product name” in your Magento instance and “color” in your staging instance. Every attribute ID is generated at install time and differs between environments. Any ETL script that hardcodes attribute IDs will produce corrupted data in production.

Answer-first: Migrate Magento EAV schemas to microservices by mapping integer IDs to UUIDs, using stable attribute codes, and executing dynamic SQL pivots. The extraction runs in three phases: full historical load, incremental CDC delta sync, and cutover validation to ensure complete data integrity.

1. The EAV Data Model

Magento Entity-Attribute-Value (EAV) data model spreads catalog data across multiple normalized tables, causing slow JOIN queries at scale.

A Magento product with SKU MSH-BLK-L (Men’s Black Shirt, Large) is stored like this:

catalog_product_entity (the entity table):

entity_id | sku           | entity_type_id | attribute_set_id
2841      | MSH-BLK-L     | 4              | 9

catalog_product_entity_varchar (string attributes):

entity_id | attribute_id | store_id | value
2841      | 75           | 0        | Men's Black Shirt
2841      | 76           | 0        | MSH-BLK-L
2841      | 97           | 0        | Men's Shirts

catalog_product_entity_int (integer attributes):

2841      | 80           | 0        | 8                ← status: 1=Enabled, 2=Disabled
2841      | 81           | 0        | 4                ← visibility
2841      | 134          | 0        | 2                ← tax_class_id

catalog_product_entity_decimal (decimal attributes):

2841      | 77           | 0        | 299000.0000     ← price
2841      | 78           | 0        | 0.0000          ← special_price

The problem: attribute_id = 75 means “name” in this database. In your staging database (set up from a fresh Magento install), attribute_id = 75 might mean “color”. Attribute IDs are instance-specific, generated by AUTO_INCREMENT during Magento installation.

2. The Trap: Hardcoded Attribute IDs

Relying on hardcoded attribute IDs risks data corruption across environments; migration scripts must query attribute codes dynamically.

Hardcoding attribute numbers directly in SQL statements creates hidden environment fragility. Because attribute IDs drift whenever modules are installed or re-indexed in different order across dev, staging, and production environments, hardcoded queries fail silently by mapping incorrect column values to entity records.

The naive migration approach, copied from legacy ETL scripts:

-- ❌ WRONG: Hardcoded attribute IDs break in production
SELECT
    e.entity_id,
    e.sku,
    v_name.value AS name,
    d_price.value AS price
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar v_name
    ON v_name.entity_id = e.entity_id AND v_name.attribute_id = 75  -- "name" hardcoded!
LEFT JOIN catalog_product_entity_decimal d_price
    ON d_price.entity_id = e.entity_id AND d_price.attribute_id = 77  -- "price" hardcoded!
WHERE v_name.store_id = 0

This query will produce correct results in your development environment, possibly incorrect results in staging, and silently wrong results in production if anyone ran a Magento re-index or added attributes in a different order.

3. The Fix: Lookup by Attribute Code

Dynamic attribute code lookups retrieve metadata definitions at runtime, ensuring production-grade SQL extraction queries across staging and production.

To eliminate attribute ID ambiguity, extraction scripts must resolve system metadata dynamically from the eav_attribute table using human-readable attribute_code strings. Standard codes like name, price, sku, status, and visibility remain constant across every Magento deployment.

The SQL statement below resolves target attribute IDs dynamically before querying value tables:

-- ✅ CORRECT: Resolve attribute IDs dynamically from eav_attribute
SELECT attr.attribute_id
FROM eav_attribute attr
JOIN eav_entity_type et ON et.entity_type_id = attr.entity_type_id
WHERE et.entity_type_code = 'catalog_product'
  AND attr.attribute_code = 'name';      -- 'name' is stable, 'attribute_id' is not

The attribute_code column is stable across instances. name, price, sku, status, visibility — these codes are defined by Magento core and don’t change between installs. Always look up attribute IDs from eav_attribute at runtime. Never hardcode them.

4. The magento_id_map: Integer → UUID Translation

The magento_id_map translation table maps legacy Magento auto-increment integer IDs to microservice UUID primary keys reliably.

Legacy Magento MySQL databases rely on sequential 32-bit or 64-bit AUTO_INCREMENT integer primary keys, which create tight coupling and security vulnerabilities when exposed via public APIs. Modern Go microservices use globally unique UUIDs for entity primary keys. In 2026 PostgreSQL 16/17 architectures, UUID v7 (time-ordered UUIDs) is standard for primary keys, replacing random UUID v4 to eliminate B-tree index page splitting and increase bulk INSERT throughput by 25–40%.

The table definition and initialization script below establishes the identity cross-reference mapping:

-- Migration step 1: Create the identity map
CREATE TABLE magento_id_map (
    id              BIGSERIAL PRIMARY KEY,
    entity_type     VARCHAR(64) NOT NULL,    -- 'product', 'customer', 'order'
    magento_id      BIGINT NOT NULL,         -- Original Magento AUTO_INCREMENT ID
    platform_uuid   UUID NOT NULL DEFAULT gen_random_uuid(),
    migrated_at     TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(entity_type, magento_id)
);

-- Populate for all products
INSERT INTO magento_id_map (entity_type, magento_id)
SELECT 'product', entity_id
FROM catalog_product_entity
ON CONFLICT (entity_type, magento_id) DO NOTHING;

-- Verify: every product has a UUID
SELECT COUNT(*) AS total_products,
       COUNT(platform_uuid) AS mapped_products
FROM magento_id_map
WHERE entity_type = 'product';
-- Expected: total_products = mapped_products

After this step, the Debezium CDC sync service (described in Part 6) uses magento_id_map to translate every entity_id in CDC events to its corresponding UUID before publishing to Dapr PubSub.

5. Dynamic SQL Pivot: Reading at Runtime

Dynamic SQL pivot queries flatten complex EAV entity tables into single-row JSON representations ready for Go domain struct unmarshaling.

The extraction query uses a Common Table Expression (CTE) named attr_ids to pre-fetch and map attribute codes to IDs for the active instance. It then joins catalog_product_entity against type-specific value tables (varchar, int, decimal, text) using filtered LEFT JOIN operations, aggregating values per entity using MAX(CASE ...) conditional expressions.

The complete CTE dynamic pivot query below extracts flattened product records:

-- EAV extraction with dynamic attribute ID resolution
WITH attr_ids AS (
    -- Resolve all attribute codes to IDs for this Magento instance
    SELECT
        attribute_id,
        attribute_code,
        backend_type                -- 'varchar', 'int', 'decimal', 'datetime', 'text'
    FROM eav_attribute
    WHERE entity_type_id = (
        SELECT entity_type_id FROM eav_entity_type
        WHERE entity_type_code = 'catalog_product'
    )
    AND attribute_code IN ('name', 'price', 'special_price', 'status',
                           'visibility', 'description', 'short_description',
                           'tax_class_id', 'weight', 'manufacturer')
)
SELECT
    e.entity_id                                             AS magento_id,
    idmap.platform_uuid                                     AS id,
    e.sku,
    e.attribute_set_id,
    MAX(CASE WHEN a.attribute_code = 'name'
        THEN v.value END)                                   AS name,
    MAX(CASE WHEN a.attribute_code = 'description'
        THEN t.value END)                                   AS description,
    MAX(CASE WHEN a.attribute_code = 'status'
        THEN i.value END)                                   AS status,
    MAX(CASE WHEN a.attribute_code = 'price'
        THEN d.value END)                                   AS price,
    MAX(CASE WHEN a.attribute_code = 'special_price'
        THEN d2.value END)                                  AS special_price,
    MAX(CASE WHEN a.attribute_code = 'weight'
        THEN d3.value END)                                  AS weight,
    e.created_at,
    e.updated_at

FROM catalog_product_entity e
JOIN magento_id_map idmap
    ON idmap.entity_type = 'product' AND idmap.magento_id = e.entity_id

-- Varchar attributes (name, manufacturer, etc.)
LEFT JOIN catalog_product_entity_varchar v
    ON v.entity_id = e.entity_id AND v.store_id = 0
    AND v.attribute_id IN (SELECT attribute_id FROM attr_ids WHERE backend_type = 'varchar')
LEFT JOIN attr_ids a_v ON a_v.attribute_id = v.attribute_id

-- Text attributes (description, short_description)
LEFT JOIN catalog_product_entity_text t
    ON t.entity_id = e.entity_id AND t.store_id = 0
    AND t.attribute_id IN (SELECT attribute_id FROM attr_ids WHERE backend_type = 'text')
LEFT JOIN attr_ids a_t ON a_t.attribute_id = t.attribute_id

-- Integer attributes (status, visibility, tax_class_id)
LEFT JOIN catalog_product_entity_int i
    ON i.entity_id = e.entity_id AND i.store_id = 0
    AND i.attribute_id IN (SELECT attribute_id FROM attr_ids WHERE backend_type = 'int')

-- Decimal attributes (price, special_price, weight)
LEFT JOIN catalog_product_entity_decimal d
    ON d.entity_id = e.entity_id AND d.store_id = 0
    AND d.attribute_id = (SELECT attribute_id FROM attr_ids WHERE attribute_code = 'price')
LEFT JOIN catalog_product_entity_decimal d2
    ON d2.entity_id = e.entity_id AND d2.store_id = 0
    AND d2.attribute_id = (SELECT attribute_id FROM attr_ids WHERE attribute_code = 'special_price')
LEFT JOIN catalog_product_entity_decimal d3
    ON d3.entity_id = e.entity_id AND d3.store_id = 0
    AND d3.attribute_id = (SELECT attribute_id FROM attr_ids WHERE attribute_code = 'weight')

GROUP BY e.entity_id, e.sku, e.attribute_set_id, idmap.platform_uuid, e.created_at, e.updated_at
ORDER BY e.entity_id;

This query is the safe, production-ready version. The WITH attr_ids AS (...) CTE runs once at query start and resolves all attribute IDs from the current database state — no hardcoding, no environment-specific constants.

6. The Go Transformation Layer & PostgreSQL JSONB Hybrid Storage

Go transformation layers unmarshal SQL pivot JSON outputs into strongly typed domain models, storing dynamic attributes in PostgreSQL JSONB with GIN indexing.

Rather than replicating Magento’s complex EAV tables in PostgreSQL, the target Go Catalog microservice adopts a Hybrid Relational + JSONB storage model. Core domain attributes (id, sku, name, price_cents, status, uuid) are stored in strongly typed relational columns. Custom, dynamic, or tenant-specific merchant attributes are preserved in a PostgreSQL attributes jsonb column indexed with a Generalized Inverted Index (CREATE INDEX idx_products_attrs ON products USING gin (attributes jsonb_path_ops);). This pattern delivers sub-5ms lookup performance on dynamic attributes across 1,000,000+ SKUs while avoiding EAV JOIN overhead.

The Go transformer module maps extracted SQL rows into domain entities:

// migration/transformer/product_transformer.go

type MagentoProduct struct {
    MagentoID   int64
    UUID        string
    SKU         string
    Name        string
    Description string
    Status      int   // 1=Enabled, 2=Disabled
    Price       float64
    SpecialPrice *float64
    Weight      float64
    CreatedAt   time.Time
    UpdatedAt   time.Time
}

func TransformProduct(mp *MagentoProduct) *catalog.Product {
    p := &catalog.Product{
        ID:          mp.UUID,
        SKU:         mp.SKU,
        Name:        mp.Name,
        Description: mp.Description,
        Status:      transformStatus(mp.Status),
        // Convert float64 price to Money type (no floating point in API layer)
        Price: money.FromFloat("VND", mp.Price),
        Weight: mp.Weight,
        CreatedAt: mp.CreatedAt,
        UpdatedAt: mp.UpdatedAt,
    }

    if mp.SpecialPrice != nil {
        p.SpecialPrice = money.FromFloat("VND", *mp.SpecialPrice)
    }

    return p
}

func transformStatus(magentoStatus int) catalog.ProductStatus {
    switch magentoStatus {
    case 1: return catalog.ProductStatus_ENABLED
    case 2: return catalog.ProductStatus_DISABLED
    default: return catalog.ProductStatus_UNSPECIFIED
    }
}

7. Customer EAV: The Same Pattern

Customer EAV extraction applies identical SQL pivot patterns, converting user profiles, addresses, and credentials into Go identity models.

Customer data in Magento is also EAV (customer_entity + type-specific tables). The extraction follows the same CTE dynamic pivot strategy to extract user profiles, contact information, and group memberships cleanly:

-- Customer extraction with dynamic attribute resolution
WITH customer_attrs AS (
    SELECT attribute_id, attribute_code, backend_type
    FROM eav_attribute
    WHERE entity_type_id = (
        SELECT entity_type_id FROM eav_entity_type
        WHERE entity_type_code = 'customer'
    )
    AND attribute_code IN ('firstname', 'lastname', 'dob', 'gender', 'taxvat')
)
SELECT
    ce.entity_id                    AS magento_id,
    idmap.platform_uuid             AS id,
    ce.email,
    ce.group_id                     AS customer_group,
    ce.is_active,
    ce.created_at,
    MAX(CASE WHEN ca.attribute_code = 'firstname'
        THEN v.value END)           AS first_name,
    MAX(CASE WHEN ca.attribute_code = 'lastname'
        THEN v.value END)           AS last_name
FROM customer_entity ce
JOIN magento_id_map idmap
    ON idmap.entity_type = 'customer' AND idmap.magento_id = ce.entity_id
LEFT JOIN customer_entity_varchar v
    ON v.entity_id = ce.entity_id
    AND v.attribute_id IN (SELECT attribute_id FROM customer_attrs WHERE backend_type = 'varchar')
JOIN customer_attrs ca ON ca.attribute_id = v.attribute_id
GROUP BY ce.entity_id, ce.email, ce.group_id, ce.is_active, ce.created_at, idmap.platform_uuid;

8. Incremental Delta Sync

Incremental delta sync tracks updated_at timestamps to stream newly modified Magento catalog records into Go microservices continuously.

During the initial data migration window, legacy Magento transactions continue modifying catalog records. Polling-based delta queries filter entities updated after the initial historical snapshot timestamp:

-- Delta sync: products changed since last extraction
SELECT e.entity_id
FROM catalog_product_entity e
WHERE e.updated_at > :last_sync_timestamp
ORDER BY e.updated_at ASC
LIMIT 1000;

This is a fallback only — it misses DELETEs and is vulnerable to clock skew. As soon as Debezium is running (Phase 1 milestone), the polling-based delta sync is disabled in favor of real-time MySQL binlog streaming.

9. Validation: Before Declaring Extraction Complete

Validating extraction requires running record count reconciliations and checksum comparisons between Magento MySQL and Go PostgreSQL stores.

Automated verification scripts execute total count comparisons across Magento MySQL source tables and Go microservice PostgreSQL target tables. The verification step includes random sample row exports to validate attribute value precision and field mapping alignment.

The shell script below performs automated extraction validation prior to promoting the service to Phase 1 traffic cutover:

#!/bin/bash
# validate-product-extraction.sh

MAGENTO_COUNT=$(mysql -h $MAGENTO_DB -e "
    SELECT COUNT(*) FROM catalog_product_entity
    WHERE status = 1  -- Enabled products only
" | tail -1)

PLATFORM_COUNT=$(psql $PLATFORM_DB -t -c "
    SELECT COUNT(*) FROM products WHERE status = 'ENABLED'
")

echo "Magento enabled products: $MAGENTO_COUNT"
echo "Platform products: $PLATFORM_COUNT"

# Count must match before proceeding to Phase 1
if [ "$MAGENTO_COUNT" -ne "$PLATFORM_COUNT" ]; then
    echo "❌ Count mismatch — extraction incomplete"
    exit 1
fi

# Sample validation: spot-check 100 products
psql $PLATFORM_DB -c "
    SELECT p.sku, p.name, p.price
    FROM products p
    ORDER BY RANDOM()
    LIMIT 100
" > /tmp/platform_sample.csv

echo "✅ Count matches. Review sample in /tmp/platform_sample.csv"

Key Takeaways

Migrating Magento EAV schemas requires dynamic attribute lookups, integer-to-UUID translation tables, and automated data checksum validation.

1. Never hardcode attribute_id — always resolve from eav_attribute.attribute_code
2. Build magento_id_map first — every subsequent migration step depends on UUID cross-references
3. Use dynamic SQL pivot — the CTE approach works on any Magento instance regardless of installation order
4. Validate count before Phase 1 — a count mismatch means your extraction is incomplete
5. Price is decimal in MySQL, Money in proto — convert explicitly, never pass float through the API layer

The EAV extraction is the most labor-intensive part of the migration. Once magento_id_map is populated and the dynamic pivot query produces the correct count, you’re ready for the actual migration phases. Part 6 shows how Debezium makes the incremental sync continuous and reliable — replacing the polling-based delta with binlog-level CDC.

FAQ

Handling Magento EAV migration safely requires converting complex entity JOINs into flattened JSON documents for Go microservice consumption.

This article is part of the Composable Commerce Migration Series. Check out the full index to see the complete architectural context.

Need help assessing the risks of your own platform migration? → Book a 1:1 Architecture Consultation

🔗 Next Step: Continue to Part 6 — Phase1 Strangler Fig for the following module in the series.