trendels · May 27, 2026 · May 27, 2026 · May 27, 2026 · May 28, 2026
diff --git a/Dockerfile b/Dockerfile
@@ -1,4 +1,4 @@
-FROM python:3.9-slim-buster
+FROM python:3.13-slim-trixie
 
 # RUN apt install gcc libpq (no longer needed bc we use psycopg2-binary)
 

diff --git a/requirements.txt b/requirements.txt
@@ -1,5 +1,5 @@
 # app
-sqlalchemy<2
+sqlalchemy
 flask
 psycopg2-binary
 redis

diff --git a/src/allocation/adapters/orm.py b/src/allocation/adapters/orm.py
@@ -1,55 +1,94 @@
-import logging
-from sqlalchemy import (
-    Table,
-    MetaData,
-    Column,
-    Integer,
-    String,
-    Date,
-    ForeignKey,
-    event,
-)
-from sqlalchemy.orm import mapper, relationship
+from datetime import date
+from typing import Self
+from sqlalchemy import Column, ForeignKey, String, Table
+from sqlalchemy.orm import registry as create_registry, relationship, Mapped, mapped_column
 
 from allocation.domain import model
 
-logger = logging.getLogger(__name__)
+registry = create_registry()
 
-metadata = MetaData()
+metadata = registry.metadata
 
-order_lines = Table(
-    "order_lines",
-    metadata,
-    Column("id", Integer, primary_key=True, autoincrement=True),
-    Column("sku", String(255)),
-    Column("qty", Integer, nullable=False),
-    Column("orderid", String(255)),
-)
 
-products = Table(
-    "products",
-    metadata,
-    Column("sku", String(255), primary_key=True),
-    Column("version_number", Integer, nullable=False, server_default="0"),
-)
+@registry.mapped_as_dataclass
+class OrderLine:
+    __tablename__ = "order_lines"
 
-batches = Table(
-    "batches",
-    metadata,
-    Column("id", Integer, primary_key=True, autoincrement=True),
-    Column("reference", String(255)),
-    Column("sku", ForeignKey("products.sku")),
-    Column("_purchased_quantity", Integer, nullable=False),
-    Column("eta", Date, nullable=True),
-)
+    batch_ref: Mapped[str] = mapped_column(ForeignKey("batches.reference"), primary_key=True)
+    _idx: Mapped[int] = mapped_column(primary_key=True)
+    sku: Mapped[str] = mapped_column(String(255))
+    qty: Mapped[int]
+    orderid: Mapped[str] = mapped_column(String(255))
+
+
+@registry.mapped_as_dataclass
+class Product:
+    __tablename__ = "products"
+
+    sku: Mapped[str] = mapped_column(String(255), primary_key=True)
+    version_number: Mapped[int] = mapped_column(server_default="0")
+
+    batches: Mapped[list["Batch"]] = relationship(cascade="all,delete-orphan")
+
+    @classmethod
+    def from_domain(cls, product: model.Product) -> Self:
+        return cls(
+            sku=product.sku,
+            version_number=product.version_number,
+            batches=[Batch.from_domain(b) for b in product.batches],
+        )
+
+    def to_domain(self) -> model.Product:
+        return model.Product(
+            sku=self.sku,
+            batches=[b.to_domain() for b in self.batches],
+            version_number=self.version_number,
+        )
+
+
+@registry.mapped_as_dataclass
+class Batch:
+    __tablename__ = "batches"
+
+    reference: Mapped[str] = mapped_column(String(255), primary_key=True)
+    sku: Mapped[str] = mapped_column(ForeignKey("products.sku"))
+    purchased_quantity: Mapped[int]
+    eta: Mapped[date | None]
+
+    allocations: Mapped[list[OrderLine]] = relationship(cascade="all,delete-orphan")
+
+    @classmethod
+    def from_domain(cls, batch: model.Batch) -> Self:
+        return cls(
+            reference=batch.reference,
+            sku=batch.sku,
+            eta=batch.eta,
+            purchased_quantity=batch._purchased_quantity,
+            allocations=[
+                OrderLine(
+                    batch_ref=batch.reference,
+                    _idx=idx,
+                    sku=ol.sku,
+                    qty=ol.qty,
+                    orderid=ol.orderid,
+                )
+                for idx, ol in enumerate(sorted(batch._allocations))
+            ],
+        )
+
+    def to_domain(self) -> model.Batch:
+        batch = model.Batch(
+            ref=self.reference,
+            sku=self.sku,
+            eta=self.eta,
+            qty=self.purchased_quantity,
+        )
+        batch._allocations = set(
+            model.OrderLine(orderid=ol.orderid, sku=ol.sku, qty=ol.qty)
+            for ol in self.allocations
+        )
+        return batch
 
-allocations = Table(
-    "allocations",
-    metadata,
-    Column("id", Integer, primary_key=True, autoincrement=True),
-    Column("orderline_id", ForeignKey("order_lines.id")),
-    Column("batch_id", ForeignKey("batches.id")),
-)
 
 allocations_view = Table(
     "allocations_view",
@@ -58,29 +97,3 @@
     Column("sku", String(255)),
     Column("batchref", String(255)),
 )
-
-
-def start_mappers():
-    logger.info("Starting mappers")
-    lines_mapper = mapper(model.OrderLine, order_lines)
-    batches_mapper = mapper(
-        model.Batch,
-        batches,
-        properties={
-            "_allocations": relationship(
-                lines_mapper,
-                secondary=allocations,
-                collection_class=set,
-            )
-        },
-    )
-    mapper(
-        model.Product,
-        products,
-        properties={"batches": relationship(batches_mapper)},
-    )
-
-
-@event.listens_for(model.Product, "load")
-def receive_load(product, _):
-    product.events = []
diff --git a/src/allocation/adapters/repository.py b/src/allocation/adapters/repository.py
@@ -1,5 +1,7 @@
 import abc
 from typing import Set
+from sqlalchemy import select
+from sqlalchemy.orm import Session
 from allocation.adapters import orm
 from allocation.domain import model
 
@@ -8,52 +10,55 @@ class AbstractRepository(abc.ABC):
     def __init__(self):
         self.seen = set()  # type: Set[model.Product]
 
-    def add(self, product: model.Product):
+    def add(self, product: model.Product) -> None:
         self._add(product)
         self.seen.add(product)
 
-    def get(self, sku) -> model.Product:
+    def get(self, sku) -> model.Product | None:
         product = self._get(sku)
         if product:
             self.seen.add(product)
         return product
 
-    def get_by_batchref(self, batchref) -> model.Product:
+    def get_by_batchref(self, batchref: str) -> model.Product | None:
         product = self._get_by_batchref(batchref)
         if product:
             self.seen.add(product)
         return product
 
+    def commit(self) -> None:
+        pass
+
     @abc.abstractmethod
-    def _add(self, product: model.Product):
+    def _add(self, product: model.Product) -> None:
         raise NotImplementedError
 
     @abc.abstractmethod
-    def _get(self, sku) -> model.Product:
+    def _get(self, sku) -> model.Product | None:
         raise NotImplementedError
 
     @abc.abstractmethod
-    def _get_by_batchref(self, batchref) -> model.Product:
+    def _get_by_batchref(self, batchref: str) -> model.Product | None:
         raise NotImplementedError
 
 
 class SqlAlchemyRepository(AbstractRepository):
-    def __init__(self, session):
+    def __init__(self, session: Session):
         super().__init__()
         self.session = session
 
-    def _add(self, product):
-        self.session.add(product)
-
-    def _get(self, sku):
-        return self.session.query(model.Product).filter_by(sku=sku).first()
-
-    def _get_by_batchref(self, batchref):
-        return (
-            self.session.query(model.Product)
-            .join(model.Batch)
-            .filter(
-                orm.batches.c.reference == batchref,
-            )
-            .first()
-        )
+    def _add(self, product: model.Product) -> None:
+        self.session.merge(orm.Product.from_domain(product))
+
+    def _get(self, sku) -> model.Product | None:
+        if p := self.session.get(orm.Product, sku):
+            return p.to_domain()
+
+    def _get_by_batchref(self, batchref: str) -> model.Product | None:
+        query = select(orm.Product).join(orm.Batch).where(orm.Batch.reference == batchref)
+        if p := self.session.scalars(query).first():
+            return p.to_domain()
+
+    def commit(self) -> None:
+        for product in self.seen:
+            self._add(product)
diff --git a/src/allocation/bootstrap.py b/src/allocation/bootstrap.py
@@ -1,6 +1,6 @@
 import inspect
 from typing import Callable
-from allocation.adapters import orm, redis_eventpublisher
+from allocation.adapters import redis_eventpublisher
 from allocation.adapters.notifications import (
     AbstractNotifications,
     EmailNotifications,
@@ -9,7 +9,6 @@
 
 
 def bootstrap(
-    start_orm: bool = True,
     uow: unit_of_work.AbstractUnitOfWork = unit_of_work.SqlAlchemyUnitOfWork(),
     notifications: AbstractNotifications = None,
     publish: Callable = redis_eventpublisher.publish,
@@ -18,9 +17,6 @@ def bootstrap(
     if notifications is None:
         notifications = EmailNotifications()
 
-    if start_orm:
-        orm.start_mappers()
-
     dependencies = {"uow": uow, "notifications": notifications, "publish": publish}
     injected_event_handlers = {
         event_type: [

diff --git a/src/allocation/domain/model.py b/src/allocation/domain/model.py
@@ -7,11 +7,15 @@
 
 class Product:
     def __init__(self, sku: str, batches: List[Batch], version_number: int = 0):
-        self.sku = sku
+        self._sku = sku
         self.batches = batches
         self.version_number = version_number
         self.events = []  # type: List[events.Event]
 
+    @property
+    def sku(self):
+        return self._sku
+
     def allocate(self, line: OrderLine) -> str:
         try:
             batch = next(b for b in sorted(self.batches) if b.can_allocate(line))
@@ -37,8 +41,16 @@ def change_batch_quantity(self, ref: str, qty: int):
             line = batch.deallocate_one()
             self.events.append(events.Deallocated(line.orderid, line.sku, line.qty))
 
+    def __eq__(self, other: object) -> bool:
+        if not isinstance(other, Product):
+            return NotImplemented
+        return self.sku == other.sku
+
+    def __hash__(self) -> int:
+        return hash(self.sku)
+
 
-@dataclass(unsafe_hash=True)
+@dataclass(frozen=True, order=True)
 class OrderLine:
     orderid: str
     sku: str

diff --git a/src/allocation/service_layer/handlers.py b/src/allocation/service_layer/handlers.py
@@ -2,6 +2,7 @@
 from __future__ import annotations
 from dataclasses import asdict
 from typing import List, Dict, Callable, Type, TYPE_CHECKING
+from sqlalchemy import text
 from allocation.domain import commands, events, model
 from allocation.domain.model import OrderLine
 
@@ -83,10 +84,12 @@ def add_allocation_to_read_model(
 ):
     with uow:
         uow.session.execute(
-            """
-            INSERT INTO allocations_view (orderid, sku, batchref)
-            VALUES (:orderid, :sku, :batchref)
-            """,
+            text(
+                """
+                INSERT INTO allocations_view (orderid, sku, batchref)
+                VALUES (:orderid, :sku, :batchref)
+                """
+            ),
             dict(orderid=event.orderid, sku=event.sku, batchref=event.batchref),
         )
         uow.commit()
@@ -98,10 +101,12 @@ def remove_allocation_from_read_model(
 ):
     with uow:
         uow.session.execute(
-            """
-            DELETE FROM allocations_view
-            WHERE orderid = :orderid AND sku = :sku
-            """,
+            text(
+                """
+                DELETE FROM allocations_view
+                WHERE orderid = :orderid AND sku = :sku
+                """
+            ),
             dict(orderid=event.orderid, sku=event.sku),
         )
         uow.commit()

diff --git a/src/allocation/service_layer/unit_of_work.py b/src/allocation/service_layer/unit_of_work.py
@@ -20,6 +20,7 @@ def __exit__(self, *args):
         self.rollback()
 
     def commit(self):
+        self.products.commit()
         self._commit()
 
     def collect_new_events(self):

diff --git a/src/allocation/views.py b/src/allocation/views.py
@@ -1,12 +1,15 @@
+from sqlalchemy import text
 from allocation.service_layer import unit_of_work
 
 
 def allocations(orderid: str, uow: unit_of_work.SqlAlchemyUnitOfWork):
     with uow:
         results = uow.session.execute(
-            """
-            SELECT sku, batchref FROM allocations_view WHERE orderid = :orderid
-            """,
+            text(
+                """
+                SELECT sku, batchref FROM allocations_view WHERE orderid = :orderid
+                """
+            ),
             dict(orderid=orderid),
-        )
+        ).mappings()
     return [dict(r) for r in results]