Auditing Catalog Data Integrity in Magento 2: A Custom CLI Audit Command

1. Typical data inconsistencies in a grown catalog

Three categories of inconsistency show up over and over in practice. Orphaned EAV values appear when a product gets removed from catalog_product_entity through direct SQL deletes or a broken custom import, but the corresponding value rows in the attribute tables remain, because referential integrity only holds through foreign keys with cascading deletes, which get bypassed often on direct bulk deletes.

Missing store assignments typically show up after store view restructuring or half-finished migration scripts: a product is correctly assigned to a website in product_website but doesn't appear at all, or appears with the wrong category reference, in catalog_category_product for a given store view. Broken category paths, finally, happen when a category's path value doesn't get updated consistently with parent_id and level after a manual move within the tree, a state that only surfaces as a symptom the next time a breadcrumb renders or a URL gets generated.

2. Identifying orphaned EAV values

The basic idea for detecting orphaned EAV values is a LEFT JOIN from the relevant attribute value table, catalog_product_entity_varchar, catalog_product_entity_int, and so on, against catalog_product_entity, followed by a check for NULL in the main table's entity_id column. Since Magento attributes are spread across several value tables by data type, this check needs to run separately for each relevant data type, a single query isn't enough to fully capture all orphaned values.

On very large catalogs, a naive LEFT JOIN across the entire varchar table is noticeably expensive, since that table typically has by far the most rows of all EAV value tables. Batch processing across entity_id ranges with a smaller, dedicated query each significantly reduces peak database load compared to a single join running across the entire catalog.

-- Identify orphaned values in catalog_product_entity_varchar
SELECT v.entity_id, v.attribute_id, v.value
FROM catalog_product_entity_varchar v
LEFT JOIN catalog_product_entity e ON e.entity_id = v.entity_id
WHERE e.entity_id IS NULL
LIMIT 500;

-- The same pattern applies to int, decimal, text and datetime attribute
-- tables, each data type table needs to be checked separately.
SELECT i.entity_id, i.attribute_id, i.value
FROM catalog_product_entity_int i
LEFT JOIN catalog_product_entity e ON e.entity_id = i.entity_id
WHERE e.entity_id IS NULL
LIMIT 500;

3. Tracking down missing store assignments

Checking for missing store assignments starts by determining the set of products assigned to a website via product_website, then comparing that against the actual category assignments of the relevant store views in catalog_category_product. A product assigned to a website but appearing in no single active category for that website is reachable through a direct URL in the frontend but undiscoverable through navigation, a failure mode that in live operation often only surfaces through declining organic visibility.

A second, more subtle case involves store view specific attribute overrides without a matching global base, for example when a product name was overridden for a given store view but the product itself has since been removed from that store view. These orphaned store view overrides are usually functionally harmless but needlessly bloat the attribute tables and should be captured in the same audit run.

4. Detecting broken category paths

A category's path value, typically a dot-separated chain of category IDs from the root to the current category, has to exactly match the tree structure reconstructible via parent_id. A check reconstructs the expected path recursively via parent_id for every category and compares it against the stored path field, any deviation points to an inconsistency typically caused by a failed or interrupted move within the category tree.

It's also worth checking the level field against the actual depth of the reconstructed path, since both values have to stay consistent with each other in a clean tree structure. A category with a correct path but a wrong level, or vice versa, leads in practice to broken breadcrumbs or incorrectly sorted category lists in the admin area, without a reindex being able to fix this underlying data problem.

5. Implementing a custom CLI audit command

For practical use, a custom Symfony console command gets registered via Magento\Framework\Console\Cli that wraps the individual checks as separate, independently invokable checks. Each check implements a shared interface with a single execute method that returns a structured result list instead of writing output directly to the console, letting the same check logic later be reused for a CSV export feature or an automated CI check.

The command itself orchestrates the registered checks, collects their results, and at the end prints a summary plus, given the right option, a detailed row list. Getting the exit code right matters for later CI integration: if the audit finds at least one critical inconsistency, the command has to terminate with a non-zero exit code so a pipeline recognizes that state as a failure.

<?php

declare(strict_types=1);

namespace Mironsoft\CatalogIntegrity\Console\Command;

use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;
use Mironsoft\CatalogIntegrity\Model\Check\CheckPool;

/**
 * Runs all registered catalog data integrity checks and
 * returns an exit code that a CI pipeline can evaluate.
 */
class AuditCatalogCommand extends Command
{
    /**
     * @param CheckPool $checkPool Holds all registered audit checks
     */
    public function __construct(
        private readonly CheckPool $checkPool,
    ) {
        parent::__construct();
    }

    /**
     * Configures name, description and options of the command.
     *
     * @return void
     */
    protected function configure(): void
    {
        $this->setName('mironsoft:catalog:audit');
        $this->setDescription('Checks the catalog for typical data inconsistencies.');
        $this->addOption('detailed', null, InputOption::VALUE_NONE, 'Print row-level details');
    }

    /**
     * Runs all checks and prints a summary plus optional details.
     *
     * @param InputInterface $input
     * @param OutputInterface $output
     * @return int
     */
    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        $hasCriticalIssues = false;

        foreach ($this->checkPool->getChecks() as $check) {
            $result = $check->run();
            $output->writeln(sprintf('%s: %d found', $check->getLabel(), $result->getIssueCount()));

            if ($result->hasCriticalIssues()) {
                $hasCriticalIssues = true;
            }
            if ($input->getOption('detailed')) {
                foreach ($result->getDetails() as $line) {
                    $output->writeln('  - ' . $line);
                }
            }
        }

        return $hasCriticalIssues ? Command::FAILURE : Command::SUCCESS;
    }
}

6. Implementing a single check as its own class

Each individual check is a lean class answering a single business question, for example whether orphaned EAV values exist in the varchar table, and works directly against the ResourceConnection instead of a fully loaded collection to do so. That's a deliberate choice: a collection loading several million rows wastes memory unnecessarily, while a targeted SQL query with LIMIT and batch processing keeps memory usage bounded even on very large catalogs.

New checks get registered via di.xml as a virtual type inside the CheckPool's checks arguments, letting new checks be added additively without touching the command itself. This pattern follows the same composite idea used for filter registration in the admin grid's FilterPool.

<?php

declare(strict_types=1);

namespace Mironsoft\CatalogIntegrity\Model\Check;

use Magento\Framework\App\ResourceConnection;

/**
 * Checks the catalog database for orphaned entries in the
 * catalog_product_entity_varchar attribute table.
 */
class OrphanedVarcharValuesCheck implements CheckInterface
{
    /**
     * @param ResourceConnection $resourceConnection Database connection access
     */
    public function __construct(
        private readonly ResourceConnection $resourceConnection,
    ) {
    }

    /**
     * Runs the check and returns a structured result.
     *
     * @return CheckResult
     */
    public function run(): CheckResult
    {
        $connection = $this->resourceConnection->getConnection();
        $select = $connection->select()
            ->from(
                ['v' => $this->resourceConnection->getTableName('catalog_product_entity_varchar')],
                ['entity_id', 'attribute_id']
            )
            ->joinLeft(
                ['e' => $this->resourceConnection->getTableName('catalog_product_entity')],
                'e.entity_id = v.entity_id',
                []
            )
            ->where('e.entity_id IS NULL')
            ->limit(1000);

        $rows = $connection->fetchAll($select);
        $details = array_map(
            static fn (array $row) => sprintf('entity_id=%d attribute_id=%d', $row['entity_id'], $row['attribute_id']),
            $rows
        );

        return new CheckResult(count($rows), $details, isCritical: count($rows) > 0);
    }

    /**
     * Human-readable label of the check for CLI output.
     *
     * @return string
     */
    public function getLabel(): string
    {
        return 'Orphaned EAV Varchar Values';
    }
}

7. Result reporting: CSV export and CI-friendly output

Console output from the command usually isn't enough for human follow-up once several hundred or thousand rows are affected. An additional option exports the detail results of each check to its own CSV file, sorted by entity_id, so a data analyst or developer can work through affected records specifically without having to write SQL themselves.

For CI-friendly output, on the other hand, the plain exit code combined with a compact summary line per check is enough, since a pipeline usually evaluates only a success or failure status, not detail rows. Both output forms can be supported in parallel without much extra work, since they're both built on the same structured CheckResult object and don't need to be reimplemented.

8. Drawing the line against pure reindex issues

A widespread misconception is treating every odd catalog behavior with a reflexive full reindex. A reindex, however, only derives the frontend- and search-optimized index tables from the existing EAV raw data, it doesn't repair or remove broken raw data itself. If the underlying EAV values, store assignments, or category paths are already inconsistent, a reindex simply produces an equally inconsistent, but freshly computed, index table.

The practical consequence is a clear order of operations: clean up the raw data through an audit command like the one described here first, then reindex. Reverse that order and a reindex looks like a fix at first, because individual symptoms shift briefly, but the underlying data inconsistency remains and produces the same symptoms again after the next scheduled reindex.

9. Integrating the audit into the deployment pipeline

The audit command can be wired into a GitLab CI or GitHub Actions pipeline as an additional step, ideally ahead of a production go-live after a major data migration or a large import. Since the command terminates with a defined exit code, the pipeline can be configured to stop a deployment automatically on critical data inconsistencies instead of silently shipping broken data to production.

For ongoing operation outside specific deployments, a regular, cron-driven run of the audit command pays off too, with results reported to the development team by email, similar to a failed automated import. That catches slowly accumulating inconsistencies well before they show up as a visible problem in the frontend or in search.

10. Summary

11. FAQ: Catalog Data Integrity in Magento 2: The Essentials