feat: Provide basic API frame

1 files changed, 62 insertions, 0 deletions

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;