The problem

Take the online shop split into services again. The order service holds the orders and the catalogue: products (Product), variants (ProductVariant) and prices. Each order line (OrderLine) points to the variant that was bought.

One Monday, the blue T-shirt in size M goes from €19.90 to €21.90. A customer who ordered on the Friday asks for a copy of their invoice. The line reads the variant again and shows the new price: the invoice no longer matches the payment.

A renamed variant also changes the label of past orders. A variant moved to another product disappears from the history of the first one.

The reflex answer: product_version and product_variant_version tables, one row per change. Before going there, let us separate three needs that the word "version" mixes up.

Three needs behind the word "version"

The technical audit trail. Who changed what, when, and why? Support or an auditor consults it after the fact: they need a tamper-proof trace, not a way back.

Usage traceability. Which price, which label, which VAT rate did this order use? The invoice, the return and the dispute depend on it.

Functional versioning. Preparing prices and publishing them on a date, comparing two versions, restoring the previous one. The version becomes an object people handle, with an identifier and a lifecycle.

A version table answers the third need. It is often built for the first two, which do not ask for that much. Yet these are the two an audit checks: who changed a piece of data, and which value was actually used.

The options

One version table per entity

  • What it solves: all three needs. The order line points to the version it used.
  • What it costs: every read must pick the current version. Does a variant version point to a product or to a product version? Every migration touches the whole history.

A technical history, queried at the order date

  • What it solves: the audit trail, without touching the code. Triggers copy every changed row into a history table. SQL:2011 defines system-versioned tables for this, which PostgreSQL 18 does not implement.
  • What it costs: the price of an order is found with an "as of" query. The history records columns, not actions: a fixed typo and a price increase look the same.

Columns copied into the order line

  • What it solves: usage traceability, with typed columns (unit_price, label) that are easy to query.
  • What it costs: every added field is a migration of order_line, and existing rows get NULL or a default value. Nobody can tell any more what each line knew when it was created.

A snapshot at the point of use and an append-only journal

  • What it solves: the order line keeps, as JSONB, the catalogue values it used. An append-only journal (rows are added, never changed) tells what happened to the catalogue.
  • What it costs: no automatic way back.

Our recommendation

As long as going back is not a need, no version entity: a JSONB snapshot at the point of use and an append-only journal.

The snapshot answers usage traceability. The journal, written by business actions, answers the audit trail. Functional versioning waits for a real need, object by object.

Three needs, two mechanisms: a snapshot where the data is used, an append-only journal Technical audit trail Who, what, when, why? → append-only journal Usage traceability Which values were used? → snapshot in the order line Functional versioning Publish on a date, restore? → only if both tests pass catalog_journal append only, never modified variant price_changed product A variant_detached product B variant_attached variant product_changed OrderLine variant_id : identity snapshot (JSONB): schema_version: 2 sku, label, vat_rate unit_price: 19.90 EUR Version table only for an object that makes sense alone and that other records cite by version (e.g. terms of sale) Catalogue Product archived, not deleted variants ProductVariant current price: €21.90 identity reference written by business actions Avoid writing the journal from a postUpdate listener (it only sees columns)

Two tests before creating a version entity

Does the object make sense on its own? The terms of sale of March 1st can be read on their own: they are published, compared, cited. The state of a variant on a Tuesday morning only matters to the order that used it.

Do external records point to it durably? Each order must designate the version of the terms of sale the customer accepted. From a variant, it only needs the values it used.

The terms of sale pass both tests: they deserve versions. The variant passes neither: a snapshot is enough.

Identity reference, version reference

An identity reference designates an object across its changes: order_line.variant_id stays the blue T-shirt in size M, whatever its price. It is used to navigate and aggregate, for example to list the orders of a variant.

A version reference designates a frozen state, like orders.terms_version_id pointing to a precise text of the terms of sale. It assumes the version exists as an object.

The order line keeps an identity reference to the variant. The snapshot replaces the version reference a version table would have provided.

The snapshot: what was used, nothing more

The snapshot copies only the fields that influence the result: SKU, printed label, unit price, currency, VAT rate. The description and the photos stay in the catalogue.

Each snapshot carries a schema_version. When its shape changes, the code writes the new version and can still read the old ones. Rule: an old snapshot is never migrated.

A migration would rewrite evidence with what we know today. A field missing from an old snapshot stays missing: reading it returns null, never a guessed value.

The journal: written where the action happens

Every action on the catalogue writes an entry: price changed, variant moved to another product, product archived. The entry goes into the transaction of the change. It also names its author, the authenticated user: the journal tells who changed what, not only what changed.

We do not write it from a Doctrine postUpdate listener. It sees changed columns, not an intent: a fixed typo and a promotion produce the same changeset. It is not called for a DQL UPDATE, and what it persists is not written by the current flush.

This is the reasoning of our article on the Outbox pattern: write on the business fact, not on a persistence hook.

Two constraints

No physical deletion in the catalogue. A product or a variant taken off sale is archived (archived_at). By default, a PostgreSQL foreign key already refuses to delete an ordered variant. The journal has no foreign key: only archiving keeps its subjects.

Journal both sides of a relation. Moving a variant to another product writes three entries: variant_detached on the old product, variant_attached on the new one, product_changed on the variant. Each timeline can then be read on its own.

Composition becomes a display question

A product's timeline reads its own entries. To add those of its variants, it follows its variant_attached and variant_detached entries: they say which variants to include, and over which period. Creating a variant therefore also writes variant_attached on its product.

Aggregating or not becomes an interface choice, not a data model one. This journal is not event sourcing: the current state stays in the tables, the journal tells how it got there.

The trade-offs we accept

No automatic way back. Going back to the old price is a new action, journaled like the others. The journal can recover a value; it does not restore a complete state.

Reading code for every snapshot shape. The code reading version 1 never goes away. We keep it in a single class, with one test per version.

A journal only as complete as the discipline that writes it. An action that forgets its entry leaves a gap, and a fix made in raw SQL does not show up. If that risk matters, a technical history fed by triggers complements it.

Less direct reports. A report on the prices paid reads JSONB, version by version. A field that is often aggregated also deserves its typed column.

The signal that should trigger a review of this choice: a need to publish prices on a date, to compare or to restore versions. Or an external record that must cite a precise state of the catalogue. We then version that object, not the whole catalogue.

Implementation

The examples use Symfony 7.4, Doctrine ORM 3 with DBAL 4.3 or later, and PostgreSQL. Types::JSONB has existed since DBAL 4.3, which deprecates the jsonb option of the json type.

1. The order line and its snapshot

ProductVariant carries sku, label, price, currency and vatRate, amounts as decimal strings. 

// src/Entity/OrderLine.php (order service)
namespace App\Entity;

use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Bridge\Doctrine\Types\UuidType;
use Symfony\Component\Uid\Uuid;

#[ORM\Entity]
class OrderLine
{
    #[ORM\Id]
    #[ORM\Column(type: UuidType::NAME)]
    private Uuid $id;

    #[ORM\ManyToOne(inversedBy: 'lines')]
    #[ORM\JoinColumn(nullable: false)]
    private Order $order;

    // Identity reference to the variant.
    #[ORM\ManyToOne]
    #[ORM\JoinColumn(nullable: false)]
    private ProductVariant $variant;

    // Decimal string, for example '2.000'.
    #[ORM\Column(type: Types::DECIMAL, precision: 12, scale: 3)]
    private string $quantity;

    // Frozen when the order is placed.
    #[ORM\Column(type: Types::JSONB, nullable: true)]
    private ?array $snapshot = null;

    // Constructor (UUID v7 generated in PHP), getters, getProduct() through the variant, setSnapshot().
}

A single class writes the current version and reads all of them. It returns a readonly DTO, OrderLineSnapshot: SKU, label, price, currency and a nullable VAT rate. A match with no default arm throws on an unknown version. 

// src/Order/OrderLineSnapshotter.php (order service)
namespace App\Order;

use App\Entity\ProductVariant;

final class OrderLineSnapshotter
{
    public const SCHEMA_VERSION = 2;

    public function take(ProductVariant $variant): array
    {
        return [
            'schema_version' => self::SCHEMA_VERSION,
            'sku' => $variant->getSku(),
            'label' => $variant->getProduct()->getName().', '.$variant->getLabel(),
            'unit_price' => ['amount' => $variant->getPrice(), 'currency' => $variant->getCurrency()],
            'vat_rate' => $variant->getVatRate(),
        ];
    }

    public function read(array $snapshot): OrderLineSnapshot
    {
        return match ($snapshot['schema_version']) {
            // Version 1: the euro was the only currency, the VAT rate was not recorded.
            1 => new OrderLineSnapshot($snapshot['sku'], $snapshot['label'], $snapshot['unit_price'], 'EUR', null),
            2 => new OrderLineSnapshot(
                $snapshot['sku'],
                $snapshot['label'],
                $snapshot['unit_price']['amount'],
                $snapshot['unit_price']['currency'],
                $snapshot['vat_rate'],
            ),
        };
    }
}

2. Freeze the snapshot when the order is placed

The order goes from draft to placed through the place transition of a Symfony workflow, applied in a transaction. A listener of this transition freezes the snapshot of each line. 

// src/EventListener/FreezeOrderLinesListener.php (order service)
namespace App\EventListener;

use App\Entity\Order;
use App\Order\OrderLineSnapshotter;
use Symfony\Component\Workflow\Attribute\AsTransitionListener;
use Symfony\Component\Workflow\Event\TransitionEvent;

final class FreezeOrderLinesListener
{
    public function __construct(
        private readonly OrderLineSnapshotter $snapshotter,
    ) {
    }

    #[AsTransitionListener(workflow: 'order', transition: 'place')]
    public function onPlace(TransitionEvent $event): void
    {
        /** @var Order $order */
        $order = $event->getSubject();

        foreach ($order->getLines() as $line) {
            $line->setSnapshot($this->snapshotter->take($line->getVariant()));
        }
    }
}

The snapshot column then contains:

{
  "schema_version": 2,
  "sku": "TSHIRT-BLUE-M",
  "label": "Cotton T-shirt, blue, M",
  "unit_price": { "amount": "19.90", "currency": "EUR" },
  "vat_rate": "20.00"
}

3. The journal

The actions are the constants of a final class CatalogAction: PRICE_CHANGED, PRODUCT_CHANGED, VARIANT_ATTACHED, VARIANT_DETACHED, ARCHIVED. 

// src/Entity/CatalogJournalEntry.php (order service)
namespace App\Entity;

use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;
use Symfony\Bridge\Doctrine\Types\UuidType;
use Symfony\Component\Uid\Uuid;

// readOnly: Doctrine ignores any change to these entries.
#[ORM\Entity(readOnly: true)]
#[ORM\Table(name: 'catalog_journal')]
#[ORM\Index(name: 'catalog_journal_subject_idx', fields: ['subjectType', 'subjectId', 'occurredAt'])]
class CatalogJournalEntry
{
    public const SUBJECT_PRODUCT = 'product';
    public const SUBJECT_VARIANT = 'variant';

    public function __construct(
        #[ORM\Id]
        #[ORM\Column(type: UuidType::NAME)]
        private Uuid $id,
        #[ORM\Column(length: 32)]
        private string $subjectType,
        // No foreign key: a product or a variant.
        #[ORM\Column(type: UuidType::NAME)]
        private Uuid $subjectId,
        #[ORM\Column(length: 64)]
        private string $action,
        #[ORM\Column(type: Types::JSONB)]
        private array $data,
        // null for a scheduled task.
        #[ORM\Column(length: 180, nullable: true)]
        private ?string $actor,
        #[ORM\Column(type: Types::DATETIMETZ_IMMUTABLE)]
        private \DateTimeImmutable $occurredAt,
    ) {
    }

    // Getters, no method that changes anything.
}

CatalogJournal::record() persists an entry with a UUID v7, the identifier of the current user and the date. It does not call flush(): the calling action does, in its transaction.

4. Write the journal in the business action

The reason for a price change goes into the journal: no changeset knows it. 

// src/Catalog/CatalogActions.php (order service)
namespace App\Catalog;

use App\Entity\CatalogJournalEntry as Entry;
use App\Entity\Product;
use App\Entity\ProductVariant;
use Doctrine\DBAL\LockMode;
use Doctrine\ORM\EntityManagerInterface;

final class CatalogActions
{
    public function __construct(
        private readonly EntityManagerInterface $em,
        private readonly CatalogJournal $journal,
    ) {
    }

    public function changePrice(ProductVariant $variant, string $price, string $reason): void
    {
        $this->em->wrapInTransaction(function () use ($variant, $price, $reason): void {
            // SELECT ... FOR UPDATE and re-read: 'from' is the price actually replaced.
            $this->em->refresh($variant, LockMode::PESSIMISTIC_WRITE);
            $this->journal->record(Entry::SUBJECT_VARIANT, $variant->getId(), CatalogAction::PRICE_CHANGED, [
                'from' => $variant->getPrice(),
                'to' => $price,
                'reason' => $reason,
            ]);
            $variant->setPrice($price);
        });
    }

    public function moveVariant(ProductVariant $variant, Product $target): void
    {
        $this->em->wrapInTransaction(function () use ($variant, $target): void {
            $source = $variant->getProduct();
            $data = ['variant_id' => $variant->getId()->toRfc4122()];

            $this->journal->record(Entry::SUBJECT_PRODUCT, $source->getId(), CatalogAction::VARIANT_DETACHED, $data);
            $this->journal->record(Entry::SUBJECT_PRODUCT, $target->getId(), CatalogAction::VARIANT_ATTACHED, $data);
            $this->journal->record(Entry::SUBJECT_VARIANT, $variant->getId(), CatalogAction::PRODUCT_CHANGED, [
                'from' => $source->getId()->toRfc4122(),
                'to' => $target->getId()->toRfc4122(),
            ]);

            $variant->setProduct($target);
        });
    }
}

wrapInTransaction() calls flush() then commits, or rolls everything back on an exception: the change and its entries are written together, or not at all.

5. Make the journal append-only in PostgreSQL

readOnly prevents neither a removal through Doctrine nor an SQL query. So the database enforces the rule itself. 

-- Migration of the order service: catalog_journal append-only.
CREATE FUNCTION catalog_journal_append_only() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    RAISE EXCEPTION 'catalog_journal is append-only: % refused', TG_OP;
END;
$$;

-- TRUNCATE does not fire DELETE triggers: it is listed on its own.
CREATE TRIGGER catalog_journal_append_only
    BEFORE UPDATE OR DELETE OR TRUNCATE ON catalog_journal
    FOR EACH STATEMENT EXECUTE FUNCTION catalog_journal_append_only();

-- The application role keeps SELECT and INSERT.
REVOKE UPDATE, DELETE, TRUNCATE ON catalog_journal FROM order_app;

The error raised by the trigger rolls the transaction back. But the owner of a table can disable its triggers and grant itself its privileges back, and a superuser bypasses privileges. So the application connects with a role that does not own its tables; the owner role is used for migrations.

Checklist

  • The need named: audit, usage or functional versioning.
  • Both tests passed before any version entity.
  • An identity reference to the variant, a snapshot for the values.
  • The snapshot limited to the fields that influence the result.
  • A schema_version per snapshot, no migration of old ones.
  • One reading test per snapshot version.
  • The journal written by business actions, in their transaction.
  • Both sides of a relation journaled.
  • Archiving instead of deletion.
  • UPDATE, DELETE and TRUNCATE refused on the journal.

Sources