Restock Notification — Schema (L1)

Doctrine entities and DDL for the restock notification campaign (parent: MZ-9533). Business logic is delivered by follow-up tickets (L2..L9). This document covers only the schema introduced by L1.

Tables

mz_product_reservation_restock_notif

Entity: AppBundle\Entity\ProductReservationRestockNotification. Repository: AppBundle\Repository\ProductReservationRestockNotificationRepository.

One row per (reservation_id, container_id). Holds:

• the per-recipient coupon (coupon_code, coupon_expires_at)
• one short-URL token per channel (short_token_email, short_token_sms, short_token_whatsapp, short_token_mail2) so click attribution stays per channel
• one unsubscribe_token per recipient
• per-channel send timestamps (mail1_sent_at, mail2_sent_at, sms_sent_at, whatsapp_sent_at) and matching *_error columns
• purchased_at set by the post-notification purchase reconciliation
• skipped_reason (already_purchased | out_of_stock | coupon_expired | unsubscribed | invalid_phone | invalid_email)
• created_at, updated_at

Constraints:

• UNIQUE (reservation_id, container_id) — gates idempotency for the orchestrator's upsert.
• indexes on arrival_at, mail1_sent_at, mail2_sent_at — Wave 1/Wave 2 cron scans.

mz_product_restock_optout

Entity: AppBundle\Entity\ProductRestockOptout. Repository: AppBundle\Repository\ProductRestockOptoutRepository.

Local mirror of axelites_restock_optout (Menzzo / Magento). Populated by the menzzo:restock:sync-optouts cron in a later ticket. Logidav reads from this table only; Menzzo remains the source of truth.

Constraints:

• UNIQUE (customer_email, sku, scope, channel)
• index on synced_at for cursor-driven sweeps.

scope is one of product (default) | global. channel is one of email | sms | whatsapp | all (default). menzzo_id mirrors the upstream row id for reconciliation.

mz_sync_cursor

Entity: AppBundle\Entity\SyncCursor. Repository: AppBundle\Repository\SyncCursorRepository.

Generic key/value cursor table. Seed row for this feature is created by the sync command on first run; this L1 ticket only ships the table.

name	initial value
restock_optout_sync	0

Applying the schema

The project does not use doctrine/migrations; DDL is applied with doctrine:schema:update.

php bin/console doctrine:schema:update --dump-sql   # shows the 3 new tables
php bin/console doctrine:schema:update --force      # applies; second run is a no-op

What L1 does NOT touch

• No service, command, controller, Twig, route, or Chatwoot/OVH/Menzzo client code. Those are L2..L9.
• No changes to mz_product_reservation, mz_container, or any pre-existing entity. Cross-table links are model-side associations only.