6 files changed, 461 insertions, 0 deletions
diff --git a/crates/rocie-server/src/storage/sql/get/mod.rs b/crates/rocie-server/src/storage/sql/get/mod.rs
new file mode 100644
index 0000000..2268e85
--- /dev/null
+++ b/crates/rocie-server/src/storage/sql/get/mod.rs
@@ -0,0 +1 @@
+pub(crate) mod product;
diff --git a/crates/rocie-server/src/storage/sql/get/product/mod.rs b/crates/rocie-server/src/storage/sql/get/product/mod.rs
new file mode 100644
index 0000000..bcc3e32
--- /dev/null
+++ b/crates/rocie-server/src/storage/sql/get/product/mod.rs
@@ -0,0 +1,81 @@
+use crate::{
+    app::App,
+    storage::sql::product::{Product, ProductId},
+};
+
+use sqlx::query;
+
+impl Product {
+    pub(crate) async fn from_id(app: &App, id: ProductId) -> Result<Option<Self>, from_id::Error> {
+        let record = query!(
+            "
+        SELECT name, description, parent
+        FROM products
+        WHERE id = ?
+",
+            id
+        )
+        .fetch_optional(&app.db)
+        .await?;
+
+        if let Some(record) = record {
+            Ok(Some(Self {
+                id,
+                name: record.name,
+                description: record.description,
+                associated_bar_codes: vec![], // todo
+            }))
+        } else {
+            Ok(None)
+        }
+    }
+
+    pub(crate) async fn get_all(app: &App) -> Result<Vec<Self>, get_all::Error> {
+        let records = query!(
+            "
+        SELECT id, name, description, parent
+        FROM products
+"
+        )
+        .fetch_all(&app.db)
+        .await?;
+
+        Ok(records
+            .into_iter()
+            .map(|record| {
+                Self {
+                    id: ProductId::from_db(&record.id),
+                    name: record.name,
+                    description: record.description,
+                    associated_bar_codes: vec![], // todo
+                }
+            })
+            .collect())
+    }
+}
+
+pub(crate) mod from_id {
+    use actix_web::ResponseError;
+
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to execute the sql query")]
+        SqlError(#[from] sqlx::Error),
+    }
+
+    impl ResponseError for Error {
+    }
+}
+
+pub(crate) mod get_all {
+    use actix_web::ResponseError;
+
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to execute the sql query")]
+        SqlError(#[from] sqlx::Error),
+    }
+
+    impl ResponseError for Error {
+    }
+}
diff --git a/crates/rocie-server/src/storage/sql/insert/mod.rs b/crates/rocie-server/src/storage/sql/insert/mod.rs
new file mode 100644
index 0000000..99f1e71
--- /dev/null
+++ b/crates/rocie-server/src/storage/sql/insert/mod.rs
@@ -0,0 +1,144 @@
+use std::{fmt::Display, mem};
+
+use crate::app::App;
+
+use chrono::Utc;
+use log::{debug, trace};
+use serde::{Serialize, de::DeserializeOwned};
+use sqlx::{SqliteConnection, query};
+
+pub(crate) mod product;
+
+pub(crate) trait Transactionable:
+    Sized + std::fmt::Debug + Serialize + DeserializeOwned
+{
+    type ApplyError: std::error::Error + Display;
+    type UndoError: std::error::Error + Display;
+
+    /// Apply this transaction.
+    ///
+    /// This should change the db state.
+    async fn apply(self, txn: &mut SqliteConnection) -> Result<(), Self::ApplyError>;
+
+    /// Undo this transaction.
+    ///
+    /// This should return the db to the state it was in before this transaction.
+    async fn undo(self, txn: &mut SqliteConnection) -> Result<(), Self::UndoError>;
+}
+
+#[derive(Debug)]
+pub(crate) struct Operations<O: Transactionable> {
+    name: &'static str,
+    ops: Vec<O>,
+}
+
+impl<O: Transactionable> Default for Operations<O> {
+    fn default() -> Self {
+        Self::new("<default impl>")
+    }
+}
+
+impl<O: Transactionable> Operations<O> {
+    #[must_use]
+    pub(crate) fn new(name: &'static str) -> Self {
+        Self {
+            name,
+            ops: Vec::new(),
+        }
+    }
+
+    pub(crate) async fn apply(mut self, app: &App) -> Result<(), apply::Error<O>> {
+        let ops = mem::take(&mut self.ops);
+
+        if ops.is_empty() {
+            return Ok(());
+        }
+
+        trace!("Begin commit of {}", self.name);
+        let mut txn = app.db.begin().await?;
+
+        for op in ops {
+            trace!("Commiting operation: {op:?}");
+            add_operation_to_txn_log(&op, &mut txn).await?;
+            op.apply(&mut txn)
+                .await
+                .map_err(|err| apply::Error::InnerApply(err))?;
+        }
+
+        txn.commit().await?;
+        trace!("End commit of {}", self.name);
+
+        Ok(())
+    }
+
+    pub(crate) fn push(&mut self, op: O) {
+        self.ops.push(op);
+    }
+}
+
+pub(crate) mod apply {
+    use actix_web::ResponseError;
+
+    use crate::storage::sql::insert::{Transactionable, add_operations_to_txn_log};
+
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error<O: Transactionable> {
+        #[error("Failed to execute sql statments")]
+        Sql(#[from] sqlx::Error),
+
+        #[error("Failed to append operations to the txn log: {0}")]
+        TxnLogAppend(#[from] add_operations_to_txn_log::Error),
+
+        #[error("Failed to apply one of the operations: {0}")]
+        InnerApply(<O as Transactionable>::ApplyError),
+    }
+
+    impl<O: Transactionable> ResponseError for Error<O> {
+        // TODO(@bpeetz): Actually do something with this. <2025-09-05>
+    }
+}
+
+impl<O: Transactionable> Drop for Operations<O> {
+    fn drop(&mut self) {
+        assert!(
+            self.ops.is_empty(),
+            "Trying to drop uncommitted operations (name: {}) ({:#?}). This is a bug.",
+            self.name,
+            self.ops
+        );
+    }
+}
+
+async fn add_operation_to_txn_log<O: Transactionable>(
+    operation: &O,
+    txn: &mut SqliteConnection,
+) -> Result<(), add_operations_to_txn_log::Error> {
+    debug!("Adding operation to txn log: {operation:?}");
+
+    let now = Utc::now().timestamp();
+    let operation = serde_json::to_string(&operation).expect("should be serializable");
+
+    query!(
+        r#"
+        INSERT INTO txn_log (
+            timestamp,
+            operation
+        )
+        VALUES (?, ?);
+        "#,
+        now,
+        operation,
+    )
+    .execute(txn)
+    .await?;
+
+    Ok(())
+}
+
+pub(crate) mod add_operations_to_txn_log {
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to execute sql statments")]
+        SqlError(#[from] sqlx::Error),
+    }
+}
diff --git a/crates/rocie-server/src/storage/sql/insert/product/mod.rs b/crates/rocie-server/src/storage/sql/insert/product/mod.rs
new file mode 100644
index 0000000..562e809
--- /dev/null
+++ b/crates/rocie-server/src/storage/sql/insert/product/mod.rs
@@ -0,0 +1,160 @@
+use serde::{Deserialize, Serialize};
+use sqlx::query;
+use uuid::Uuid;
+
+use crate::storage::sql::{
+    insert::{Operations, Transactionable},
+    product::{Barcode, Product, ProductId},
+};
+
+#[derive(Debug, Deserialize, Serialize)]
+pub(crate) enum Operation {
+    RegisterProduct {
+        id: ProductId,
+        name: String,
+        description: Option<String>,
+        parent: Option<ProductId>,
+    },
+    AssociateBarcode {
+        id: ProductId,
+        barcode: Barcode,
+    },
+}
+
+impl Transactionable for Operation {
+    type ApplyError = apply::Error;
+    type UndoError = undo::Error;
+
+    async fn apply(self, txn: &mut sqlx::SqliteConnection) -> Result<(), apply::Error> {
+        match self {
+            Operation::RegisterProduct {
+                id,
+                name,
+                description,
+                parent,
+            } => {
+                query!(
+                    "
+                    INSERT INTO products (id, name, description, parent)
+                    VALUES (?,?,?,?)
+",
+                    id,
+                    name,
+                    description,
+                    parent
+                )
+                .execute(txn)
+                .await?;
+            }
+            Operation::AssociateBarcode { id, barcode } => {
+                let barcode_id = i64::from(barcode.id);
+                let barcode_amount_value = i64::from(barcode.amount.value);
+                let barcode_amount_unit = barcode.amount.unit;
+
+                query!(
+                    "
+                    INSERT INTO barcodes (id, product_id, amount, unit)
+                    VALUES (?,?,?,?)
+",
+                    barcode_id,
+                    id,
+                    barcode_amount_value,
+                    barcode_amount_unit,
+                )
+                .execute(txn)
+                .await?;
+            }
+        }
+        Ok(())
+    }
+
+    async fn undo(self, txn: &mut sqlx::SqliteConnection) -> Result<(), undo::Error> {
+        match self {
+            Operation::RegisterProduct {
+                id,
+                name,
+                description,
+                parent,
+            } => {
+                query!(
+                    "
+                    DELETE FROM products
+                    WHERE id = ? AND name = ? AND description = ? AND parent = ?;
+",
+                    id,
+                    name,
+                    description,
+                    parent
+                )
+                .execute(txn)
+                .await?;
+            }
+            Operation::AssociateBarcode { id, barcode } => {
+                let barcode_id = i64::from(barcode.id);
+                let barcode_amount_value = i64::from(barcode.amount.value);
+                let barcode_amount_unit = barcode.amount.unit;
+
+                query!(
+                    "
+                    DELETE FROM barcodes
+                    WHERE id = ? AND product_id = ? AND amount = ? AND unit = ?;
+",
+                    barcode_id,
+                    id,
+                    barcode_amount_value,
+                    barcode_amount_unit
+                )
+                .execute(txn)
+                .await?;
+            }
+        }
+        Ok(())
+    }
+}
+
+pub(crate) mod undo {
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to execute sql statments")]
+        SqlError(#[from] sqlx::Error),
+    }
+}
+pub(crate) mod apply {
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to execute sql statments")]
+        SqlError(#[from] sqlx::Error),
+    }
+}
+
+impl Product {
+    pub(crate) fn register(
+        name: String,
+        description: Option<String>,
+        parent: Option<ProductId>,
+        ops: &mut Operations<Operation>,
+    ) -> Self {
+        let id = ProductId::from(Uuid::new_v4());
+
+        ops.push(Operation::RegisterProduct {
+            id,
+            name: name.clone(),
+            description: description.clone(),
+            parent,
+        });
+
+        Self {
+            id,
+            name,
+            description,
+            associated_bar_codes: vec![],
+        }
+    }
+
+    pub(crate) fn associate_barcode(&self, barcode: Barcode, ops: &mut Operations<Operation>) {
+        ops.push(Operation::AssociateBarcode {
+            id: self.id,
+            barcode,
+        })
+    }
+}
diff --git a/crates/rocie-server/src/storage/sql/mod.rs b/crates/rocie-server/src/storage/sql/mod.rs
new file mode 100644
index 0000000..871ae3b
--- /dev/null
+++ b/crates/rocie-server/src/storage/sql/mod.rs
@@ -0,0 +1,5 @@
+pub(crate) mod get;
+pub(crate) mod insert;
+
+// Types
+pub(crate) mod product;
diff --git a/crates/rocie-server/src/storage/sql/product.rs b/crates/rocie-server/src/storage/sql/product.rs
new file mode 100644
index 0000000..e0216dd
--- /dev/null
+++ b/crates/rocie-server/src/storage/sql/product.rs
@@ -0,0 +1,70 @@
+use std::{fmt::Display, str::FromStr};
+
+use serde::{Deserialize, Serialize};
+use sqlx::{Database, Encode, Type};
+use utoipa::ToSchema;
+use uuid::Uuid;
+
+#[derive(Clone, ToSchema, Serialize, Deserialize)]
+pub(crate) struct Product {
+    pub(crate) id: ProductId,
+    pub(super) name: String,
+    pub(super) description: Option<String>,
+    pub(super) associated_bar_codes: Vec<Barcode>,
+}
+
+#[derive(
+    Deserialize, Serialize, Debug, Default, ToSchema, Clone, Copy, PartialEq, Eq, PartialOrd, Ord,
+)]
+pub(crate) struct ProductId(Uuid);
+
+impl ProductId {
+    pub(crate) fn from_db(id: &str) -> ProductId {
+        Self(Uuid::from_str(id).expect("We put an uuid into the db, it should also go out again"))
+    }
+}
+
+impl Display for ProductId {
+    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
+        write!(f, "{}", self.0)
+    }
+}
+
+impl From<Uuid> for ProductId {
+    fn from(value: Uuid) -> Self {
+        Self(value)
+    }
+}
+
+impl<'q, DB: Database> Encode<'q, DB> for ProductId
+where
+    String: Encode<'q, DB>,
+{
+    fn encode_by_ref(
+        &self,
+        buf: &mut <DB as Database>::ArgumentBuffer<'q>,
+    ) -> Result<sqlx::encode::IsNull, sqlx::error::BoxDynError> {
+        let inner = self.0.to_string();
+        Encode::<DB>::encode_by_ref(&inner, buf)
+    }
+}
+impl<DB: Database> Type<DB> for ProductId
+where
+    String: Type<DB>,
+{
+    fn type_info() -> DB::TypeInfo {
+        <String as Type<DB>>::type_info()
+    }
+}
+
+#[derive(ToSchema, Debug, Clone, Serialize, Deserialize)]
+pub(crate) struct Barcode {
+    pub(crate) id: u32,
+    pub(crate) amount: UnitAmount,
+}
+
+#[derive(ToSchema, Debug, Clone, Serialize, Deserialize)]
+pub(crate) struct UnitAmount {
+    pub(crate) value: u32,
+    pub(crate) unit: String,
+}