db_schema.xml: Designing the Points Ledger Table

A customer's point balance could naively be stored as a single number in one column on customer_entity - chapter 21 actually does this too, as a fast read cache. But any serious points logic needs more: a traceable, immutable history of every single credit, redemption, and expiry. In this series that history is called the points ledger, and it's the module's first table.

The ledger principle: append, never overwrite

A ledger is never updated or deleted, only appended to ("append-only"). Every row is a single event - a credit on purchase, a redemption against a reward, an automatic expiry, a manual correction. The current point balance can be derived as the sum of all rows, but is additionally written per row as balance_after - that allows an O(1) lookup of the current balance (read the last row) AND full traceability of which balance applied at which point in time. Chapter 9 uses exactly this property to automatically detect drift between calculated and stored balance.

Column design

ledger_id - primary key, auto-increment.
customer_id - foreign key to customer_entity.entity_id, not nullable.
order_id - foreign key to sales_order.entity_id, nullable (a manual correction or a pure reward redemption has no associated order).
points - positive on credit, negative on redemption or expiry.
type - one of earn, redeem, expire, adjust.
balance_after - the customer's point balance immediately after this entry.
created_at - timestamp, set automatically by the database.
expires_at - nullable, only set for type = earn; chapter 33 uses this field for the daily expiry.

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>

The two foreign keys in detail

onDelete="CASCADE" on customer_id means: when a customer is deleted, their entire points history disappears with them - privacy compliant and without orphaned rows. onDelete="SET NULL" on order_id is deliberately chosen differently: when an order is archived or deleted, the points credit itself should remain as an event - only the reference to the specific order is dropped.

Tipp: The referenceId on constraints and indexes must be unique across the whole project. The convention used here - <TABLE>_<COLUMN>_<TARGET_TABLE>_<TARGET_COLUMN> in uppercase - avoids collisions with foreign keys from other modules and makes it obvious at a glance what the key points to.

Achtung: Without the index on customer_id, every query for a customer's points history (chapter 6, chapter 9, and later the account page in chapter 48) would trigger a full table scan. On a growing ledger table - and by definition it only grows, never shrinks - that quickly becomes a performance problem.

Running setup:upgrade

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

Chapter 4 builds the classic model, resource model, and collection classes on top of this table.