db_schema.xml: die Punkte-Ledger-Tabelle entwerfen

Der Punktestand eines Kunden ließe sich naiv als einzelne Zahl in einer Spalte auf customer_entity speichern - Kapitel 21 tut das sogar zusätzlich, als schneller Lese-Cache. Aber jede seriöse Punktelogik braucht mehr: eine nachvollziehbare, unveränderliche Historie jeder einzelnen Gutschrift, Einlösung und jedes Verfalls. Diese Historie heißt in dieser Serie Punkte-Ledger (englisch: ledger, Kontobuch) und ist die erste Tabelle des Moduls.

Das Ledger-Prinzip: anhängen statt überschreiben

Ein Ledger wird nie geändert oder gelöscht, nur ergänzt ("append-only"). Jede Zeile ist ein einzelnes Ereignis - eine Gutschrift beim Kauf, eine Einlösung gegen eine Prämie, ein automatischer Verfall, eine manuelle Korrektur. Der aktuelle Punktestand ergibt sich rechnerisch aus der Summe aller Zeilen, wird aber zusätzlich pro Zeile als balance_after mitgeschrieben - das erlaubt eine O(1)-Abfrage des aktuellen Stands (letzte Zeile lesen) UND eine vollständige Nachvollziehbarkeit, welcher Stand zu welchem Zeitpunkt galt. Kapitel 9 nutzt genau diese Eigenschaft, um Abweichungen zwischen berechnetem und gespeichertem Stand automatisiert zu erkennen.

Spaltenentwurf

ledger_id - Primärschlüssel, Autoincrement.
customer_id - Fremdschlüssel auf customer_entity.entity_id, nicht nullbar.
order_id - Fremdschlüssel auf sales_order.entity_id, nullbar (eine manuelle Korrektur oder ein reiner Prämien-Einlösevorgang hat keine zugehörige Bestellung).
points - positiv bei Gutschrift, negativ bei Einlösung oder Verfall.
type - einer von earn, redeem, expire, adjust.
balance_after - der Punktestand des Kunden unmittelbar nach diesem Eintrag.
created_at - Zeitstempel, automatisch von der Datenbank gesetzt.
expires_at - nullbar, nur bei type = earn gesetzt; Kapitel 33 nutzt dieses Feld für den täglichen Verfall.

app/code/Mironsoft/Loyalty/etc/db_schema.xml

<?xml version="1.0"?>
<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="mironsoft_loyalty_points_ledger" resource="default" engine="innodb"
           comment="Mironsoft Loyalty Points Ledger">
        <column xsi:type="int" name="ledger_id" padding="10" unsigned="true"
                nullable="false" identity="true" comment="Ledger ID"/>
        <column xsi:type="int" name="customer_id" padding="10" unsigned="true"
                nullable="false" identity="false" comment="Customer ID"/>
        <column xsi:type="int" name="order_id" padding="10" unsigned="true"
                nullable="true" identity="false" comment="Order ID"/>
        <column xsi:type="int" name="points" nullable="false" identity="false"
                comment="Points, positive = credit, negative = debit"/>
        <column xsi:type="varchar" name="type" nullable="false" length="16"
                comment="Ledger Entry Type: earn, redeem, expire, adjust"/>
        <column xsi:type="int" name="balance_after" nullable="false" identity="false"
                comment="Customer Balance Immediately After This Entry"/>
        <column xsi:type="timestamp" name="created_at" on_update="false" nullable="false"
                default="CURRENT_TIMESTAMP" comment="Created At"/>
        <column xsi:type="datetime" name="expires_at" on_update="false" nullable="true"
                comment="Expires At"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="ledger_id"/>
        </constraint>
        <constraint xsi:type="foreign"
                    referenceId="MIRONSOFT_LOYALTY_POINTS_LEDGER_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID"
                    table="mironsoft_loyalty_points_ledger" column="customer_id"
                    referenceTable="customer_entity" referenceColumn="entity_id"
                    onDelete="CASCADE"/>
        <constraint xsi:type="foreign"
                    referenceId="MIRONSOFT_LOYALTY_POINTS_LEDGER_ORDER_ID_SALES_ORDER_ENTITY_ID"
                    table="mironsoft_loyalty_points_ledger" column="order_id"
                    referenceTable="sales_order" referenceColumn="entity_id"
                    onDelete="SET NULL"/>
        <index referenceId="MIRONSOFT_LOYALTY_POINTS_LEDGER_CUSTOMER_ID" indexType="btree">
            <column name="customer_id"/>
        </index>
    </table>
</schema>

Die beiden Fremdschlüssel im Detail

onDelete="CASCADE" bei customer_id bedeutet: wird ein Kunde gelöscht, verschwindet auch seine komplette Punkte-Historie - datenschutzkonform und ohne verwaiste Zeilen. onDelete="SET NULL" bei order_id ist bewusst anders gewählt: wird eine Bestellung archiviert oder gelöscht, soll die Punkte-Gutschrift selbst als Ereignis erhalten bleiben - nur der Bezug zur konkreten Bestellung entfällt.

Tipp: referenceId bei Constraints und Indizes muss projektweit eindeutig sein. Die hier verwendete Konvention <TABELLE>_<SPALTE>_<ZIELTABELLE>_<ZIELSPALTE> in Großbuchstaben verhindert Kollisionen mit Fremdschlüsseln aus anderen Modulen und macht auf einen Blick klar, worauf der Schlüssel zeigt.

Achtung: Ohne den index auf customer_id würde jede Abfrage der Punkte-Historie eines Kunden (Kapitel 6, Kapitel 9, später die Kontoseite in Kapitel 48) einen vollständigen Tabellenscan auslösen. Bei einer wachsenden Ledger-Tabelle - und die wächst per Definition nur, nie schrumpft sie - wird das schnell zum Performance-Problem.

setup:upgrade ausführen

bin/magento setup:upgrade
bin/magento cache:flush

Kapitel 4 baut auf dieser Tabelle die klassischen Model-, ResourceModel- und Collection-Klassen auf.