Magento 1 chatbot: the read-only shape that survives

Where the request usually starts

It is a Tuesday lunchtime ping from an agency lead in Utrecht. A client of theirs runs a Magento 1.9.4.5 store doing roughly €40k per month in floor tiles and bathroom fittings. The brief is two sentences long: "Customer wants an AI chatbot on the product pages. Can you scope a price?" Attached is a Loom recorded at 23:41 the night before, the agency owner walking through the storefront with the camera shaking, pointing at the "Need help?" widgets they have seen on competitor sites.

The site is on PHP 7.4. Mage_Core has eight community patches stacked on top of the official SUPEE-11346. The checkout is a custom one-page job from 2018 that nobody at the agency has touched since the original developer left. The MySQL database is 18 GB, mostly EAV bloat. The /checkout/onepage controller still uses the original prototype.js stack.

This is the part where the conversation has to stop being about "an AI chatbot" and start being about what shape that chatbot is allowed to take. On a Magento 1 store of this vintage, the wrong shape will break checkout inside a week.

Why Magento 1 punishes write paths

Magento 1 went end-of-life in June 2020. The official support window closed, security patches stopped, and the entire EAV model became a museum piece. In practice, any third-party code that writes to the database is now operating without guardrails. There is no community patch coming for the corner case where your chatbot widget races a cart update and leaves a stale quote_id_mask row.

The checkout flow on a typical Magento 1.9 store touches at least 14 tables in a single order placement. sales_flat_quote, sales_flat_quote_item, sales_flat_quote_address, sales_flat_order, sales_flat_order_item, sales_flat_order_payment, plus their _grid counterparts. The quote subsystem is famously sensitive to writes from outside the standard checkout controllers. We have seen agencies inherit stores where a half-finished "abandoned cart" extension was still writing to sales_flat_quote rows that the standard observer then re-processed at the wrong moment, two minutes after the customer had already paid.

Any chatbot that writes anything to that database is taking on responsibility for a 2017 schema with no upstream patches. That is the wrong trade.

The read-only shape

The shape we recommend, every time, is this. The chatbot reads. It does not write. It does not session. It does not personalise. It answers product questions from the catalog, shipping questions from a flat YAML, and policy questions from the CMS blocks. It cannot see who the customer is, cannot see what is in their cart, and cannot trigger anything in the order pipeline.

Concretely, that means:

• The bot endpoint reads from a small set of catalog tables via a dedicated MySQL user with SELECT-only grants.
• The widget loads on category and product pages only, never on /checkout, /customer, /onestepcheckout, or any admin route.
• The bot has its own path prefix (typically /bot/) that is excluded from Magento's index.php routing entirely.
• No session cookie is set by the bot, so it cannot accidentally invalidate the Magento frontend session.

A working .htaccess fragment at the document root:

# Keep the bot widget script out of checkout entirely
<LocationMatch "^/(checkout|onestepcheckout|customer/account|admin)">
    Header always set Content-Security-Policy "script-src 'self' 'unsafe-inline'; connect-src 'self'"
</LocationMatch>

# Route /bot/ to a standalone PHP file, not Magento's index.php
RewriteRule ^bot/api/(.*)$ /bot/api.php?path=$1 [L,QSA]

The CSP block is the load-bearing line. If the bot's loader script ends up in a CMS block that bleeds into checkout, the browser refuses to fetch it. The checkout JS keeps running on its old prototype.js stack, untouched. MDN's CSP reference is the canonical source for the connect-src and script-src semantics if you need to defend the rule in code review.

The MySQL grant

The bot runs as its own user, not as the Magento user. The grants are scoped at the table level:

CREATE USER 'pier_bot'@'localhost' IDENTIFIED BY '...';

GRANT SELECT ON shop.catalog_product_entity TO 'pier_bot'@'localhost';
GRANT SELECT ON shop.catalog_product_entity_varchar TO 'pier_bot'@'localhost';
GRANT SELECT ON shop.catalog_product_entity_decimal TO 'pier_bot'@'localhost';
GRANT SELECT ON shop.catalog_product_entity_text TO 'pier_bot'@'localhost';
GRANT SELECT ON shop.eav_attribute TO 'pier_bot'@'localhost';
GRANT SELECT ON shop.cataloginventory_stock_item TO 'pier_bot'@'localhost';

FLUSH PRIVILEGES;

No access to sales_*, no access to customer_*, no access to core_session. If the bot is compromised at any layer (prompt injection, library CVE, bad deploy), the blast radius is the public catalog. Which is already public.

The view layer that keeps EAV out of the prompt

The catalog tables are not readable as a chat context. They are EAV. A product name lives in catalog_product_entity_varchar with attribute_id 71 on most installs, joined back to catalog_product_entity by entity_id. Throwing raw EAV at any language model is a bad idea, both for token cost and because the model will hallucinate joins.

The shape that works is a denormalised view, refreshed by the bot when it boots:

CREATE OR REPLACE VIEW bot_product_card AS
SELECT
    e.entity_id,
    e.sku,
    name.value      AS name,
    descr.value     AS short_description,
    price.value     AS price,
    stock.qty       AS qty,
    stock.is_in_stock
FROM catalog_product_entity e
LEFT JOIN catalog_product_entity_varchar name
    ON name.entity_id = e.entity_id AND name.attribute_id = 71 AND name.store_id = 0
LEFT JOIN catalog_product_entity_text descr
    ON descr.entity_id = e.entity_id AND descr.attribute_id = 73 AND descr.store_id = 0
LEFT JOIN catalog_product_entity_decimal price
    ON price.entity_id = e.entity_id AND price.attribute_id = 75 AND price.store_id = 0
LEFT JOIN cataloginventory_stock_item stock
    ON stock.product_id = e.entity_id;

Attribute IDs vary per install. Confirm yours with SELECT attribute_id, attribute_code FROM eav_attribute WHERE entity_type_id = 4 before you ship the view. The bot queries bot_product_card, not the EAV tables directly. The view is the contract. If you ever want to change what the bot sees, you change the view, not the code path.

What we ended up shipping at Pier

When we built Pier we ran into this exact shape often enough that we baked the read-only mode in: point it at the FTP and MySQL credentials of a legacy site, hand it a SELECT-only user, and the MySQL editor refuses to write. The version history still tracks every file the operator touches outside the bot, so a Saturday-night tweak rolls back with one keystroke.

The smallest thing worth doing today, before you write a single line of bot code: run SHOW GRANTS FOR 'magento'@'localhost' on the store you have been asked to "just add a chatbot" to. If the user that owns the connection has GRANT ALL, you already know which conversation to have with the client first.