feat: Provide basic API frame

17 files changed, 1202 insertions, 0 deletions

diff --git a/crates/rocie-server/src/api/get.rs b/crates/rocie-server/src/api/get.rs
new file mode 100644
index 0000000..94015cf
--- /dev/null
+++ b/crates/rocie-server/src/api/get.rs
@@ -0,0 +1,48 @@
+use actix_web::{HttpResponse, Responder, Result, get, web};
+
+use crate::{
+    app::App,
+    storage::sql::product::{Product, ProductId},
+};
+
+pub(crate) fn register_paths(cfg: &mut web::ServiceConfig) {
+    cfg.service(product_by_id).service(products);
+}
+
+/// Get Product by id
+#[utoipa::path(
+    responses(
+        (status = OK, description = "Product found from database", body = Product),
+        (status = NOT_FOUND, description = "Product not found in database"),
+        (status = INTERNAL_SERVER_ERROR, description = "Server encountered error", body = String)
+    ),
+    params(
+        ("id" = ProductId, description = "Product id" ),
+    )
+)]
+#[get("/product/{id}")]
+pub(crate) async fn product_by_id(
+    app: web::Data<App>,
+    id: web::Path<ProductId>,
+) -> Result<impl Responder> {
+    let id = id.into_inner();
+
+    match Product::from_id(&app, id).await? {
+        Some(product) => Ok(HttpResponse::Ok().json(product)),
+        None => Ok(HttpResponse::NotFound().finish()),
+    }
+}
+
+/// Return all registered products
+#[utoipa::path(
+    responses(
+        (status = OK, description = "All products founds", body = Vec<Product>),
+        (status = INTERNAL_SERVER_ERROR, description = "Server encountered error", body = String)
+    ),
+)]
+#[get("/products/")]
+pub(crate) async fn products(app: web::Data<App>) -> Result<impl Responder> {
+    let all = Product::get_all(&app).await?;
+
+    Ok(HttpResponse::Ok().json(all))
+}
diff --git a/crates/rocie-server/src/api/mod.rs b/crates/rocie-server/src/api/mod.rs
new file mode 100644
index 0000000..c573122
--- /dev/null
+++ b/crates/rocie-server/src/api/mod.rs
@@ -0,0 +1,2 @@
+pub(crate) mod get;
+pub(crate) mod set;
diff --git a/crates/rocie-server/src/api/set.rs b/crates/rocie-server/src/api/set.rs
new file mode 100644
index 0000000..0a6af1b
--- /dev/null
+++ b/crates/rocie-server/src/api/set.rs
@@ -0,0 +1,99 @@
+use actix_web::{HttpResponse, Responder, Result, post, web};
+use serde::Deserialize;
+use utoipa::ToSchema;
+
+use crate::{
+    app::App,
+    storage::sql::{
+        insert::Operations,
+        product::{Barcode, Product, ProductId},
+    },
+};
+
+#[derive(Deserialize, ToSchema)]
+struct ProductStub {
+    name: String,
+    description: Option<String>,
+    parent: Option<ProductId>,
+}
+
+pub(crate) fn register_paths(cfg: &mut web::ServiceConfig) {
+    cfg.service(register_product).service(associate_barcode);
+}
+
+/// Register a product
+#[utoipa::path(
+    responses(
+        (
+            status = 200,
+            description = "Product successfully registered in database",
+            body = ProductId,
+        ),
+        (
+            status = INTERNAL_SERVER_ERROR,
+            description = "Server encountered error",
+            body = String,
+        )
+    ),
+    request_body = ProductStub,
+)]
+#[post("/product/new")]
+pub(crate) async fn register_product(
+    app: web::Data<App>,
+    product_stub: web::Json<ProductStub>,
+) -> Result<impl Responder> {
+    let mut ops = Operations::new("register product");
+
+    let product = Product::register(
+        product_stub.name.clone(),
+        product_stub.description.clone(),
+        product_stub.parent,
+        &mut ops,
+    );
+
+    ops.apply(&app).await?;
+
+    Ok(HttpResponse::Ok().json(product.id))
+}
+
+/// Associate a barcode with a product
+#[utoipa::path(
+    responses(
+        (
+            status = OK,
+            description = "Barcode successfully associated with product",
+        ),
+        (
+            status = NOT_FOUND,
+            description = "Product id not found in database",
+        ),
+        (
+            status = INTERNAL_SERVER_ERROR,
+            description = "Server encountered error",
+            body = String,
+        )
+    ),
+    params (
+        ("id" = ProductId, description = "The id of the product to associated the barcode with"),
+    ),
+    request_body = Barcode,
+)]
+#[post("/product/{id}/associate")]
+pub(crate) async fn associate_barcode(
+    app: web::Data<App>,
+    id: web::Path<ProductId>,
+    barcode: web::Json<Barcode>,
+) -> Result<impl Responder> {
+    let mut ops = Operations::new("associated barcode with product");
+
+    match Product::from_id(&app, id.into_inner()).await? {
+        Some(product) => {
+            product.associate_barcode(barcode.into_inner(), &mut ops);
+
+            ops.apply(&app).await?;
+
+            Ok(HttpResponse::Ok().finish())
+        }
+        None => Ok(HttpResponse::NotFound().finish()),
+    }
+}
diff --git a/crates/rocie-server/src/app.rs b/crates/rocie-server/src/app.rs
new file mode 100644
index 0000000..ab8f764
--- /dev/null
+++ b/crates/rocie-server/src/app.rs
@@ -0,0 +1,57 @@
+use std::{env, path::PathBuf};
+
+use sqlx::{SqlitePool, sqlite::SqliteConnectOptions};
+
+use crate::storage::migrate::migrate_db;
+
+#[derive(Clone)]
+pub(crate) struct App {
+    pub(crate) db: SqlitePool,
+}
+
+impl App {
+    pub(crate) async fn new() -> Result<Self, app_create::Error> {
+        let db_path: PathBuf = PathBuf::from(env::var("ROCIE_DB_PATH")?);
+
+        let db = {
+            let options = SqliteConnectOptions::new()
+                .filename(&db_path)
+                .optimize_on_close(true, None)
+                .create_if_missing(true);
+
+            SqlitePool::connect_with(options).await.map_err(|err| {
+                app_create::Error::DbConnectionFailed {
+                    inner: err,
+                    db_path,
+                }
+            })?
+        };
+
+        let me = Self { db };
+
+        migrate_db(&me).await?;
+
+        Ok(me)
+    }
+}
+
+pub(crate) mod app_create {
+    use std::{env, path::PathBuf};
+
+    use crate::storage::migrate::migrate_db;
+
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("The `ROCIE_DB_PATH` variable is not accessible: {0}")]
+        MissingDbVariable(#[from] env::VarError),
+
+        #[error("Failed to connect to the sqlite database at `{db_path}`, because: {inner}")]
+        DbConnectionFailed {
+            inner: sqlx::Error,
+            db_path: PathBuf,
+        },
+
+        #[error("Failed to migrate db to the current version")]
+        MigrateDb(#[from] migrate_db::Error),
+    }
+}
diff --git a/crates/rocie-server/src/cli.rs b/crates/rocie-server/src/cli.rs
new file mode 100644
index 0000000..5961ab7
--- /dev/null
+++ b/crates/rocie-server/src/cli.rs
@@ -0,0 +1,16 @@
+use clap::{Parser, Subcommand};
+
+#[derive(Parser)]
+pub(crate) struct CliArgs {
+    #[command(subcommand)]
+    pub(crate) command: Command,
+}
+
+#[derive(Subcommand)]
+pub(crate) enum Command {
+    /// Serve the server on the default ports.
+    Serve,
+
+    /// Print the `OpenAPI` API documentation to stdout.
+    OpenApi,
+}
diff --git a/crates/rocie-server/src/error.rs b/crates/rocie-server/src/error.rs
new file mode 100644
index 0000000..8b13789
--- /dev/null
+++ b/crates/rocie-server/src/error.rs
@@ -0,0 +1 @@
+
diff --git a/crates/rocie-server/src/main.rs b/crates/rocie-server/src/main.rs
new file mode 100644
index 0000000..453a2dc
--- /dev/null
+++ b/crates/rocie-server/src/main.rs
@@ -0,0 +1,77 @@
+use actix_web::{App, HttpServer, middleware::Logger, web::Data};
+use clap::Parser;
+use utoipa::OpenApi;
+
+use crate::cli::{CliArgs, Command};
+
+mod api;
+mod app;
+mod cli;
+mod storage;
+
+#[actix_web::main]
+#[expect(
+    clippy::needless_for_each,
+    reason = "utoipa generates this, we can't change it"
+)]
+async fn main() -> Result<(), std::io::Error> {
+    #[derive(OpenApi)]
+    #[openapi(
+    paths(
+            api::get::product_by_id,
+            api::get::products,
+            api::set::register_product,
+            api::set::associate_barcode
+        ),
+    // security(
+    //     (),
+    //     ("my_auth" = ["read:items", "edit:items"]),
+    //     ("token_jwt" = [])
+    // ),
+    )]
+    struct ApiDoc;
+
+    env_logger::init_from_env(env_logger::Env::new().default_filter_or("info"));
+
+    let args = CliArgs::parse();
+
+    match args.command {
+        Command::Serve => {
+            let host = "127.0.0.1";
+            let port = 8080;
+            let data = Data::new(
+                app::App::new()
+                    .await
+                    .map_err(|err| std::io::Error::other(main::Error::AppInit(err)))?,
+            );
+
+            eprintln!("Serving at http://{host}:{port}");
+
+            HttpServer::new(move || {
+                App::new()
+                    .wrap(Logger::default())
+                    .app_data(Data::clone(&data))
+                    .configure(api::get::register_paths)
+                    .configure(api::set::register_paths)
+            })
+            .bind((host, port))?
+            .run()
+            .await
+        }
+        Command::OpenApi => {
+            let openapi = ApiDoc::openapi();
+            println!("{}", openapi.to_pretty_json().expect("Comp-time constant"));
+            Ok(())
+        }
+    }
+}
+
+pub(crate) mod main {
+    use crate::app::app_create;
+
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to initialize shared application state: {0}")]
+        AppInit(#[from] app_create::Error),
+    }
+}
diff --git a/crates/rocie-server/src/storage/migrate/mod.rs b/crates/rocie-server/src/storage/migrate/mod.rs
new file mode 100644
index 0000000..3fdc400
--- /dev/null
+++ b/crates/rocie-server/src/storage/migrate/mod.rs
@@ -0,0 +1,313 @@
+use std::{
+    fmt::Display,
+    time::{SystemTime, UNIX_EPOCH},
+};
+
+use chrono::TimeDelta;
+use log::{debug, info};
+use sqlx::{Sqlite, SqlitePool, Transaction, query};
+
+use crate::app::App;
+
+macro_rules! make_upgrade {
+    ($app:expr, $old_version:expr, $new_version:expr, $sql_name:expr) => {
+        let mut tx = $app
+            .db
+            .begin()
+            .await
+            .map_err(|err| update::Error::TxnStart(err))?;
+        debug!("Migrating: {} -> {}", $old_version, $new_version);
+
+        sqlx::raw_sql(include_str!($sql_name))
+            .execute(&mut *tx)
+            .await
+            .map_err(|err| update::Error::SqlUpdate(err))?;
+
+        set_db_version(
+            &mut tx,
+            if $old_version == Self::Empty {
+                // There is no previous version we would need to remove
+                None
+            } else {
+                Some($old_version)
+            },
+            $new_version,
+        )
+        .await
+        .map_err(|err| update::Error::SetDbVersion {
+            err,
+            new_version: $new_version,
+        })?;
+
+        tx.commit()
+            .await
+            .map_err(|err| update::Error::TxnCommit(err))?;
+
+        // NOTE: This is needed, so that sqlite "sees" our changes to the table
+        // without having to reconnect. <2025-02-18>
+        query!("VACUUM")
+            .execute(&$app.db)
+            .await
+            .map_err(|err| update::Error::SqlVacuum(err))?;
+
+        Box::pin($new_version.update($app))
+            .await
+            .map_err(|err| update::Error::NextUpdate {
+                err: Box::new(err),
+                new_version: $new_version,
+            })?;
+
+        Ok(())
+    };
+}
+
+#[derive(Debug, Clone, Copy, PartialEq, PartialOrd)]
+pub(crate) enum DbVersion {
+    /// The database is not yet initialized.
+    Empty,
+
+    /// Introduced: 2025-09-02.
+    One,
+}
+const CURRENT_VERSION: DbVersion = DbVersion::One;
+
+async fn set_db_version(
+    tx: &mut Transaction<'_, Sqlite>,
+    old_version: Option<DbVersion>,
+    new_version: DbVersion,
+) -> Result<(), db_version_set::Error> {
+    let valid_from = get_current_date();
+
+    if let Some(old_version) = old_version {
+        let valid_to = valid_from + 1;
+        let old_version = old_version.as_sql_integer();
+
+        query!(
+            "UPDATE version SET valid_to = ? WHERE namespace = 'rocie' AND number = ?;",
+            valid_to,
+            old_version
+        )
+        .execute(&mut *(*tx))
+        .await?;
+    }
+
+    let version = new_version.as_sql_integer();
+
+    query!(
+        "INSERT INTO version (namespace, number, valid_from, valid_to) VALUES ('rocie', ?, ?, NULL);",
+        version,
+        valid_from
+    )
+    .execute(&mut *(*tx))
+    .await?;
+
+    Ok(())
+}
+
+pub(crate) mod db_version_set {
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to perform database action: {0}")]
+        DbError(#[from] sqlx::Error),
+    }
+}
+
+impl DbVersion {
+    fn as_sql_integer(self) -> i32 {
+        match self {
+            DbVersion::One => 1,
+
+            DbVersion::Empty => unreachable!("A empty version does not have an associated integer"),
+        }
+    }
+
+    fn from_db(number: i64, namespace: &str) -> Result<Self, db_version_parse::Error> {
+        match (number, namespace) {
+            (1, "rocie") => Ok(DbVersion::One),
+            (number, namespace) => Err(db_version_parse::Error::UnkownVersion {
+                namespace: namespace.to_owned(),
+                number,
+            }),
+        }
+    }
+
+    /// Try to update the database from version [`self`] to the [`CURRENT_VERSION`].
+    ///
+    /// Each update is atomic, so if this function fails you are still guaranteed to have a
+    /// database at version `get_version`.
+    #[allow(clippy::too_many_lines)]
+    async fn update(self, app: &App) -> Result<(), update::Error> {
+        match self {
+            Self::Empty => {
+                make_upgrade! {app, Self::Empty, Self::One, "./sql/0->1.sql"}
+            }
+
+            // This is the current_version
+            Self::One => {
+                assert_eq!(self, CURRENT_VERSION);
+                assert_eq!(self, get_version(app).await?);
+                Ok(())
+            }
+        }
+    }
+}
+impl Display for DbVersion {
+    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
+        // It is a unit only enum, thus we can simply use the Debug formatting
+        <Self as std::fmt::Debug>::fmt(self, f)
+    }
+}
+pub(crate) mod update {
+    use crate::storage::migrate::{DbVersion, db_version_set, get_db_version};
+
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to determine final database version: {0}")]
+        GetVersion(#[from] get_db_version::Error),
+
+        #[error("Failed to set the db to version {new_version}: {err}")]
+        SetDbVersion {
+            err: db_version_set::Error,
+            new_version: DbVersion,
+        },
+
+        #[error("Failed to vacuum sql database after update: {0}")]
+        SqlVacuum(sqlx::Error),
+
+        #[error("Failed to execute the sql update script: {0}")]
+        SqlUpdate(sqlx::Error),
+
+        #[error("Failed to start the update transaction: {0}")]
+        TxnStart(sqlx::Error),
+        #[error("Failed to commit the update transaction: {0}")]
+        TxnCommit(sqlx::Error),
+
+        #[error("Failed to perform the next chained update (to ver {new_version}): {err}")]
+        NextUpdate {
+            err: Box<Self>,
+            new_version: DbVersion,
+        },
+    }
+}
+pub(crate) mod db_version_parse {
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Db version is {number}, but got unknown namespace: '{namespace}'")]
+        UnkownVersion { namespace: String, number: i64 },
+    }
+}
+
+/// Returns the current data as UNIX time stamp.
+fn get_current_date() -> i64 {
+    let start = SystemTime::now();
+    let seconds_since_epoch: TimeDelta = TimeDelta::from_std(
+        start
+            .duration_since(UNIX_EPOCH)
+            .expect("Time went backwards"),
+    )
+    .expect("Time does not go backwards");
+
+    // All database dates should be after the UNIX_EPOCH (and thus positiv)
+    seconds_since_epoch.num_milliseconds()
+}
+
+/// Return the current database version.
+///
+/// # Panics
+/// Only if internal assertions fail.
+pub(crate) async fn get_version(app: &App) -> Result<DbVersion, get_db_version::Error> {
+    get_version_db(&app.db).await
+}
+
+/// Return the current database version.
+///
+/// In contrast to the [`get_version`] function, this function does not
+/// a fully instantiated [`App`], a database connection suffices.
+///
+/// # Panics
+/// Only if internal assertions fail.
+pub(crate) async fn get_version_db(pool: &SqlitePool) -> Result<DbVersion, get_db_version::Error> {
+    let version_table_exists = {
+        let query = query!(
+            "
+            SELECT 1 as result
+            FROM sqlite_master
+            WHERE type = 'table'
+            AND name = 'version'
+            "
+        )
+        .fetch_optional(pool)
+        .await
+        .map_err(|err| get_db_version::Error::VersionTableExistance(err))?;
+
+        if let Some(output) = query {
+            assert_eq!(output.result, 1);
+            true
+        } else {
+            false
+        }
+    };
+
+    if !version_table_exists {
+        return Ok(DbVersion::Empty);
+    }
+
+    let current_version = query!(
+        "
+        SELECT namespace, number
+        FROM version
+        WHERE valid_to IS NULL;
+        "
+    )
+    .fetch_one(pool)
+    .await
+    .map_err(|err| get_db_version::Error::VersionNumberFetch(err))?;
+
+    Ok(DbVersion::from_db(
+        current_version.number,
+        current_version.namespace.as_str(),
+    )?)
+}
+
+pub(crate) mod get_db_version {
+    use crate::storage::migrate::db_version_parse;
+
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to fetch the version number from db: {0}")]
+        VersionNumberFetch(sqlx::Error),
+
+        #[error("Failed to check for existance of the `version` table: {0}")]
+        VersionTableExistance(sqlx::Error),
+
+        #[error("Failed to parse the db version: {0}")]
+        VersionParse(#[from] db_version_parse::Error),
+    }
+}
+
+pub(crate) async fn migrate_db(app: &App) -> Result<(), migrate_db::Error> {
+    let current_version = get_version(app).await?;
+
+    if current_version == CURRENT_VERSION {
+        return Ok(());
+    }
+
+    info!("Migrate database from version '{current_version}' to version '{CURRENT_VERSION}'");
+
+    current_version.update(app).await?;
+
+    Ok(())
+}
+
+pub(crate) mod migrate_db {
+    use crate::storage::migrate::{get_db_version, update};
+
+    #[derive(thiserror::Error, Debug)]
+    pub(crate) enum Error {
+        #[error("Failed to determine the database version: {0}")]
+        GetVersion(#[from] get_db_version::Error),
+
+        #[error("Failed to update the database: {0}")]
+        Upadate(#[from] update::Error),
+    }
+}
diff --git a/crates/rocie-server/src/storage/migrate/sql/0->1.sql b/crates/rocie-server/src/storage/migrate/sql/0->1.sql
new file mode 100644
index 0000000..13bc1cb
--- /dev/null
+++ b/crates/rocie-server/src/storage/migrate/sql/0->1.sql
@@ -0,0 +1,62 @@
+-- All tables should be declared STRICT, as I actually like to have types checking (and a
+-- db that doesn't lie to me).
+
+CREATE TABLE version (
+    -- The `namespace` is only useful, if other tools ever build on this database
+    namespace   TEXT           NOT NULL,
+
+    -- The version.
+    number      INTEGER UNIQUE NOT NULL PRIMARY KEY,
+
+    -- The validity of this version as UNIX time stamp
+    valid_from  INTEGER        NOT NULL CHECK (valid_from < valid_to),
+    -- If set to `NULL`, represents the current version
+    valid_to    INTEGER UNIQUE          CHECK (valid_to > valid_from)
+) STRICT;
+
+-- Encodes the tree structure of the products.
+-- A parent cannot be a product, but can have parents on it's own.
+-- TODO: Fix the possibility for cyclic parent-ship entries <2025-09-05>
+CREATE TABLE parents (
+    id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
+    parent                      TEXT                             DEFAULT NULL CHECK (id IS NOT parent),
+    FOREIGN KEY(parent) REFERENCES parents(id)
+) STRICT;
+
+CREATE TABLE products (
+    id                          TEXT UNIQUE NOT NULL PRIMARY KEY,
+    name                        TEXT           NOT NULL,
+    description                 TEXT,
+    parent                      TEXT                             DEFAULT NULL,
+    FOREIGN KEY(parent) REFERENCES parents(id)
+) STRICT;
+
+CREATE TABLE barcodes (
+    id                          INTEGER UNIQUE NOT NULL PRIMARY KEY,
+    product_id                  TEXT           NOT NULL,
+    amount                      INTEGER        NOT NULL,
+    unit                        TEXT           NOT NULL,
+    FOREIGN KEY(product_id) REFERENCES products(id),
+    FOREIGN KEY(unit) REFERENCES units(name)
+) STRICT;
+
+CREATE TABLE units (
+    name                        TEXT UNIQUE NOT NULL PRIMARY KEY
+) STRICT;
+
+-- Encodes unit conversions:
+-- {factor} {from_unit} = 1 {to_unit}
+-- E.g.: 1000 g = 1 kg
+CREATE TABLE unit_conversions (
+    from_unit                  TEXT NOT NULL,
+    to_unit                    TEXT NOT NULL,
+    factor                     REAL NOT NULL,
+    FOREIGN KEY(from_unit)     REFERENCES units(name),
+    FOREIGN KEY(to_unit)       REFERENCES units(name)
+) STRICT;
+
+-- Log of all the applied operations to this db.
+CREATE TABLE txn_log (
+    timestamp INTEGER NOT NULL,
+    operation TEXT    NOT NULL
+) STRICT;
diff --git a/crates/rocie-server/src/storage/mod.rs b/crates/rocie-server/src/storage/mod.rs
new file mode 100644
index 0000000..509d15d
--- /dev/null
+++ b/crates/rocie-server/src/storage/mod.rs
@@ -0,0 +1,3 @@
+pub(crate) mod migrate;
+pub(crate) mod txn_log;
+pub(crate) mod sql;
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,
+}
diff --git a/crates/rocie-server/src/storage/txn_log.rs b/crates/rocie-server/src/storage/txn_log.rs
new file mode 100644
index 0000000..d07b514
--- /dev/null
+++ b/crates/rocie-server/src/storage/txn_log.rs
@@ -0,0 +1,63 @@
+// yt - A fully featured command line YouTube client
+//
+// Copyright (C) 2025 Benedikt Peetz <benedikt.peetz@b-peetz.de>
+// SPDX-License-Identifier: GPL-3.0-or-later
+//
+// This file is part of Yt.
+//
+// You should have received a copy of the License along with this program.
+// If not, see <https://www.gnu.org/licenses/gpl-3.0.txt>.
+
+use std::fmt::Display;
+
+use chrono::{DateTime, Utc};
+
+use crate::storage::sql::insert::Transactionable;
+
+pub(crate) struct TxnLog<O: Transactionable> {
+    inner: Vec<(TimeStamp, O)>,
+}
+
+impl<O: Transactionable> TxnLog<O> {
+    pub(crate) fn new(inner: Vec<(TimeStamp, O)>) -> Self {
+        Self { inner }
+    }
+    pub(crate) fn inner(&self) -> &[(TimeStamp, O)] {
+        &self.inner
+    }
+}
+
+/// An UNIX time stamp.
+#[derive(Debug, Default, Clone, Copy)]
+pub(crate) struct TimeStamp {
+    value: i64,
+}
+impl TimeStamp {
+    /// Return the seconds since the UNIX epoch for this [`TimeStamp`].
+    #[must_use]
+    pub(crate) fn as_secs(self) -> i64 {
+        self.value
+    }
+
+    /// Construct a [`TimeStamp`] from a count of seconds since the UNIX epoch.
+    #[must_use]
+    pub(crate) fn from_secs(value: i64) -> Self {
+        Self { value }
+    }
+
+    /// Construct a [`TimeStamp`] from the current time.
+    #[must_use]
+    pub(crate) fn from_now() -> Self {
+        Self {
+            value: Utc::now().timestamp(),
+        }
+    }
+}
+impl Display for TimeStamp {
+    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
+        DateTime::from_timestamp(self.value, 0)
+            .expect("The timestamps should always be valid")
+            .format("%Y-%m-%d")
+            .fmt(f)
+    }
+}