task-012 (#2)

Co-authored-by: Joe Carstairs <jcarstairs@scottlogic.com>
Reviewed-on: https://git.joeac.net/joeac/schist/pulls/2
Co-authored-by: Joe Carstairs <me@joeac.net>
Co-committed-by: Joe Carstairs <me@joeac.net>

16 files changed, 199 insertions, 132 deletions

diff --git a/schist_core/schist_models/diesel.toml b/schist_core/schist_models/diesel.toml
deleted file mode 100644
index 2b8ebd8..0000000
--- a/schist_core/schist_models/diesel.toml
+++ /dev/null
@@ -1,9 +0,0 @@
-# For documentation on how to configure this file,
-# see https://diesel.rs/guides/configuring-diesel-cli
-
-[print_schema]
-file = "src/schema.rs"
-custom_type_derives = ["diesel::query_builder::QueryId", "Clone"]
-
-[migrations_directory]
-dir = "/home/joeac/src/schist/rust/schist_models/migrations"
diff --git a/schist_core/schist_models/migrations/2024-08-31-084439_initial_setup/down.sql b/schist_core/schist_models/migrations/2024-08-31-084439_initial_setup/down.sql
index ba65012..3b2c73e 100644
--- a/schist_core/schist_models/migrations/2024-08-31-084439_initial_setup/down.sql
+++ b/schist_core/schist_models/migrations/2024-08-31-084439_initial_setup/down.sql
@@ -1,7 +1,7 @@
 DROP TABLE accounts;
 DROP TABLE account_transfers;
-DROP TABLE budget_drips;
-DROP TABLE categories;
-DROP TABLE transaction_categorisations;
-DROP TABLE category_transfers;
+DROP TABLE buckets;
+DROP TABLE bucket_transfers;
+DROP TABLE drips;
+DROP TABLE pipes;
 DROP TABLE transactions;
diff --git a/schist_core/schist_models/migrations/2024-08-31-084439_initial_setup/up.sql b/schist_core/schist_models/migrations/2024-08-31-084439_initial_setup/up.sql
index a09bf7b..275f280 100644
--- a/schist_core/schist_models/migrations/2024-08-31-084439_initial_setup/up.sql
+++ b/schist_core/schist_models/migrations/2024-08-31-084439_initial_setup/up.sql
@@ -9,9 +9,9 @@ CREATE TABLE accounts(
 
 CREATE TABLE account_transfers(
     id INTEGER NOT NULL PRIMARY KEY,
+    amount INTEGER NOT NULL,
     date TEXT NOT NULL,
     description TEXT NOT NULL,
-    quantity INTEGER NOT NULL,
     from_account_id INTEGER NOT NULL,
     to_account_id INTEGER NOT NULL,
     FOREIGN KEY (from_account_id)
@@ -24,70 +24,66 @@ CREATE TABLE account_transfers(
         ON DELETE RESTRICT
 );
 
-
-CREATE TABLE budget_drips(
+CREATE TABLE buckets(
     id INTEGER NOT NULL PRIMARY KEY,
-    category_id INTEGER NOT NULL,
-    date TEXT NOT NULL,
-    quantity INTEGER NOT NULL,
-    FOREIGN KEY (category_id)
-        REFERENCES categories (id)
-        ON UPDATE CASCADE
-        ON DELETE RESTRICT,
-    UNIQUE(category_id, date)
+    balance INTEGER,
+    balance_cache_key TEXT,
+    bucket_group TEXT NOT NULL,
+    name TEXT NOT NULL
 );
 
-CREATE TABLE categories(
-    id INTEGER NOT NULL PRIMARY KEY,
-    name TEXT NOT NULL,
-    balance INTEGER NOT NULL,
-    balance_date TEXT NOT NULL,
-    budget_period INTEGER NOT NULL,
-    budget_period_unit TEXT NOT NULL,
-    budget_quantity INTEGER NOT NULL
-);
-
-CREATE TABLE category_transfers(
+CREATE TABLE bucket_transfers(
     id INTEGER NOT NULL PRIMARY KEY,
+    amount INTEGER NOT NULL,
     description TEXT NOT NULL,
-    quantity INTEGER NOT NULL,
-    from_category_id INTEGER NOT NULL,
-    to_category_id INTEGER NOT NULL,
-    FOREIGN KEY (from_category_id)
-        REFERENCES categories (id)
+    from_bucket_id INTEGER NOT NULL,
+    to_bucket_id INTEGER NOT NULL,
+    FOREIGN KEY (from_bucket_id)
+        REFERENCES buckets (id)
         ON UPDATE CASCADE
         ON DELETE RESTRICT,
-    FOREIGN KEY (to_category_id)
-        REFERENCES categories (id)
+    FOREIGN KEY (to_bucket_id)
+        REFERENCES buckets (id)
         ON UPDATE CASCADE
         ON DELETE RESTRICT
 );
 
-CREATE TABLE transactions(
+CREATE TABLE drips(
     id INTEGER NOT NULL PRIMARY KEY,
-    description TEXT NOT NULL,
-    payee TEXT NOT NULL,
-    quantity INTEGER NOT NULL,
+    amount INTEGER NOT NULL,
+    bucket_id INTEGER NOT NULL,
     date TEXT NOT NULL,
-    account_id INTEGER NOT NULL,
-    FOREIGN KEY (account_id)
-        REFERENCES accounts (id)
+    inserted_at_unix_seconds INTEGER NOT NULL,
+    pipe_id INTEGER NOT NULL,
+    FOREIGN KEY (bucket_id)
+        REFERENCES buckets (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT,
+    UNIQUE(bucket_id, date)
+);
+
+CREATE TABLE pipes(
+    id INTEGER NOT NULL PRIMARY KEY,
+    amount INTEGER NOT NULL,
+    bucket_id INTEGER NOT NULL,
+    period TEXT NOT NULL,
+    start_date TEXT NOT NULL,
+    FOREIGN KEY (bucket_id)
+        REFERENCES buckets (id)
         ON UPDATE CASCADE
         ON DELETE RESTRICT
 );
 
-CREATE TABLE transaction_categorisations(
+CREATE TABLE transactions(
     id INTEGER NOT NULL PRIMARY KEY,
+    account_id INTEGER NOT NULL,
+    amount INTEGER NOT NULL,
+    bucket_id INTEGER,
+    counterparty TEXT NOT NULL,
+    date TEXT NOT NULL,
     description TEXT NOT NULL,
-    quantity INTEGER NOT NULL,
-    transaction_id INTEGER NOT NULL,
-    category_id INTEGER NOT NULL,
-    FOREIGN KEY (transaction_id)
-        REFERENCES transactions (id)
-        ON UPDATE CASCADE
-        ON DELETE RESTRICT,
-    FOREIGN KEY (category_id)
-        REFERENCES categories (id)
+    FOREIGN KEY (account_id)
+        REFERENCES accounts (id)
         ON UPDATE CASCADE
         ON DELETE RESTRICT
 );
diff --git a/schist_core/schist_models/migrations/2025-08-15-090859_dummy_data/down.sql b/schist_core/schist_models/migrations/2025-08-15-090859_dummy_data/down.sql
new file mode 100644
index 0000000..8050572
--- /dev/null
+++ b/schist_core/schist_models/migrations/2025-08-15-090859_dummy_data/down.sql
@@ -0,0 +1,39 @@
+DELETE FROM accounts
+    WHERE id=0
+    AND name="Nationwide bank account"
+    AND opening_balance=0
+    AND opening_date="2024-01-01";
+
+DELETE FROM accounts
+    WHERE id=1
+    AND name="cash account"
+    AND opening_balance=0
+    AND opening_date="2024-01-01";
+
+DELETE FROM accounts
+    WHERE id=2
+    AND name="YBS savings account"
+    AND opening_balance=0
+    AND opening_date="2024-01-01";
+
+DELETE FROM buckets
+    WHERE id=0
+    AND balance IS NULL
+    AND balance_cache_key IS NULL
+    AND bucket_group="normal expenses"
+    AND name="groceries";
+
+DELETE FROM buckets
+    WHERE id=1
+    AND balance IS NULL
+    AND balance_cache_key IS NULL
+    AND bucket_group="luxuries"
+    AND name="bevvy";
+
+DELETE FROM buckets
+    WHERE id=2
+    AND balance IS NULL
+    AND balance_cache_key IS NULL
+    AND bucket_group="income"
+    AND name="salary";
+
diff --git a/schist_core/schist_models/migrations/2025-08-15-090859_dummy_data/up.sql b/schist_core/schist_models/migrations/2025-08-15-090859_dummy_data/up.sql
new file mode 100644
index 0000000..f5c97d0
--- /dev/null
+++ b/schist_core/schist_models/migrations/2025-08-15-090859_dummy_data/up.sql
@@ -0,0 +1,14 @@
+INSERT INTO accounts
+    (id, name, opening_balance, opening_date)
+    VALUES
+    (0, "Nationwide bank account", 0, "2024-01-01"),
+    (1, "cash account", 0, "2024-01-01"),
+    (2, "YBS savings account", 0, "2024-01-01");
+
+INSERT INTO buckets
+    (id, balance, balance_cache_key, bucket_group, name)
+    VALUES
+    (0, NULL, NULL, "normal expenses", "groceries"),
+    (1, NULL, NULL, "luxuries", "bevvy"),
+    (2, NULL, NULL, "income", "salary");
+
diff --git a/schist_core/schist_models/src/account_transfer.rs b/schist_core/schist_models/src/account_transfer.rs
index 06c51ac..ba33dee 100644
--- a/schist_core/schist_models/src/account_transfer.rs
+++ b/schist_core/schist_models/src/account_transfer.rs
@@ -7,9 +7,9 @@ use crate::date_utc::DateUtc;
 #[diesel(table_name = crate::schema::account_transfers)]
 pub struct AccountTransfer {
     pub id: i32,
+    pub amount: i32,
     pub date: DateUtc,
     pub description: String,
-    pub quantity: i32,
     pub from_account_id: i32,
     pub to_account_id: i32,
 }
diff --git a/schist_core/schist_models/src/bucket.rs b/schist_core/schist_models/src/bucket.rs
index 1ee6a13..1a78628 100644
--- a/schist_core/schist_models/src/bucket.rs
+++ b/schist_core/schist_models/src/bucket.rs
@@ -2,8 +2,6 @@ use derive_builder::Builder;
 use diesel::prelude::*;
 use serde::{Deserialize, Serialize};
 
-use crate::{budget_period_unit::BudgetPeriodUnit, date_utc::DateUtc};
-
 #[derive(
     Builder,
     Clone,
@@ -19,11 +17,9 @@ use crate::{budget_period_unit::BudgetPeriodUnit, date_utc::DateUtc};
 #[diesel(table_name = crate::schema::buckets)]
 pub struct Bucket {
     pub id: i32,
-    pub name: String,
-    pub balance: i32,
-    pub balance_date: DateUtc,
-    pub budget_period: i32,
-    pub budget_period_unit: BudgetPeriodUnit,
-    pub budget_quantity: i32,
+    pub balance: Option<i32>,
+    pub balance_cache_key: Option<String>,
+    #[column_name = "bucket_group"]
     pub group: String,
+    pub name: String,
 }
diff --git a/schist_core/schist_models/src/bucket_transfer.rs b/schist_core/schist_models/src/bucket_transfer.rs
index bdf95d9..7c3a9d2 100644
--- a/schist_core/schist_models/src/bucket_transfer.rs
+++ b/schist_core/schist_models/src/bucket_transfer.rs
@@ -5,8 +5,8 @@ use diesel::prelude::*;
 #[diesel(table_name = crate::schema::bucket_transfers)]
 pub struct BucketTransfer {
     pub id: i32,
+    pub amount: i32,
     pub description: String,
-    pub quantity: i32,
     pub from_bucket_id: i32,
     pub to_bucket_id: i32,
 }
diff --git a/schist_core/schist_models/src/budget_drip.rs b/schist_core/schist_models/src/budget_drip.rs
deleted file mode 100644
index 419c8f9..0000000
--- a/schist_core/schist_models/src/budget_drip.rs
+++ /dev/null
@@ -1,14 +0,0 @@
-use derive_builder::Builder;
-use diesel::prelude::{Identifiable, Insertable, Queryable, Selectable};
-use serde::{Deserialize, Serialize};
-
-use crate::date_utc::DateUtc;
-
-#[derive(Builder, Clone, Copy, Queryable, Identifiable, Selectable, Debug, PartialEq, Serialize, Deserialize, Insertable)]
-#[diesel(table_name = crate::schema::budget_drips)]
-pub struct BudgetDrip {
-    pub id: i32,
-    pub bucket_id: i32,
-    pub date: DateUtc,
-    pub quantity: i32,
-}
diff --git a/schist_core/schist_models/src/drip.rs b/schist_core/schist_models/src/drip.rs
new file mode 100644
index 0000000..f092ae9
--- /dev/null
+++ b/schist_core/schist_models/src/drip.rs
@@ -0,0 +1,28 @@
+use derive_builder::Builder;
+use diesel::prelude::{Identifiable, Insertable, Queryable, Selectable};
+use serde::{Deserialize, Serialize};
+
+use crate::date_utc::DateUtc;
+
+#[derive(
+    Builder,
+    Clone,
+    Copy,
+    Queryable,
+    Identifiable,
+    Insertable,
+    Selectable,
+    Debug,
+    PartialEq,
+    Serialize,
+    Deserialize,
+)]
+#[diesel(table_name = crate::schema::drips)]
+pub struct Drip {
+    pub id: i32,
+    pub amount: i32,
+    pub bucket_id: i32,
+    pub date: DateUtc,
+    pub inserted_at_unix_seconds: i32,
+    pub pipe_id: i32,
+}
diff --git a/schist_core/schist_models/src/lib.rs b/schist_core/schist_models/src/lib.rs
index db5c593..503db17 100644
--- a/schist_core/schist_models/src/lib.rs
+++ b/schist_core/schist_models/src/lib.rs
@@ -2,11 +2,11 @@ pub mod account;
 pub mod account_transfer;
 pub mod bucket;
 pub mod bucket_transfer;
-pub mod budget_drip;
 pub mod budget_period_unit;
 pub mod date_utc;
 pub mod datetime_utc;
+pub mod drip;
 pub mod migrations;
+pub mod pipe;
 pub mod schema;
 pub mod transaction;
-pub mod transaction_categorisation;
diff --git a/schist_core/schist_models/src/pipe.rs b/schist_core/schist_models/src/pipe.rs
new file mode 100644
index 0000000..fb66a20
--- /dev/null
+++ b/schist_core/schist_models/src/pipe.rs
@@ -0,0 +1,27 @@
+use derive_builder::Builder;
+use diesel::prelude::*;
+use serde::{Deserialize, Serialize};
+
+use crate::{budget_period_unit::BudgetPeriodUnit, date_utc::DateUtc};
+
+#[derive(
+    Builder,
+    Insertable,
+    Queryable,
+    Identifiable,
+    Selectable,
+    Debug,
+    PartialEq,
+    Clone,
+    Serialize,
+    Deserialize,
+)]
+#[diesel(table_name = crate::schema::pipes)]
+#[diesel(belongs_to(Bucket))]
+pub struct Pipe {
+    pub id: i32,
+    pub amount: i32,
+    pub bucket_id: i32,
+    pub period: BudgetPeriodUnit,
+    pub start_date: DateUtc,
+}
diff --git a/schist_core/schist_models/src/schema.rs b/schist_core/schist_models/src/schema.rs
index 1c9fc62..3799479 100644
--- a/schist_core/schist_models/src/schema.rs
+++ b/schist_core/schist_models/src/schema.rs
@@ -3,9 +3,9 @@
 diesel::table! {
     account_transfers (id) {
         id -> Integer,
+        amount -> Integer,
         date -> Text,
         description -> Text,
-        quantity -> Integer,
         from_account_id -> Integer,
         to_account_id -> Integer,
     }
@@ -21,69 +21,68 @@ diesel::table! {
 }
 
 diesel::table! {
-    budget_drips (id) {
+    bucket_transfers (id) {
         id -> Integer,
-        bucket_id -> Integer,
-        date -> Text,
-        quantity -> Integer,
+        amount -> Integer,
+        description -> Text,
+        from_bucket_id -> Integer,
+        to_bucket_id -> Integer,
     }
 }
 
 diesel::table! {
     buckets (id) {
         id -> Integer,
+        balance -> Nullable<Integer>,
+        balance_cache_key -> Nullable<Text>,
+        bucket_group -> Text,
         name -> Text,
-        balance -> Integer,
-        balance_date -> Text,
-        budget_period -> Integer,
-        budget_period_unit -> Text,
-        budget_quantity -> Integer,
-        group -> Text,
     }
 }
 
 diesel::table! {
-    bucket_transfers (id) {
+    drips (id) {
         id -> Integer,
-        description -> Text,
-        quantity -> Integer,
-        from_bucket_id -> Integer,
-        to_bucket_id -> Integer,
+        amount -> Integer,
+        bucket_id -> Integer,
+        date -> Text,
+        inserted_at_unix_seconds -> Integer,
+        pipe_id -> Integer,
     }
 }
 
 diesel::table! {
-    transaction_categorisations (id) {
+    pipes (id) {
         id -> Integer,
-        description -> Text,
-        quantity -> Integer,
-        transaction_id -> Integer,
+        amount -> Integer,
         bucket_id -> Integer,
+        period -> Text,
+        start_date -> Text,
     }
 }
 
 diesel::table! {
     transactions (id) {
         id -> Integer,
-        description -> Text,
-        payee -> Text,
-        quantity -> Integer,
-        date -> Text,
         account_id -> Integer,
+        amount -> Integer,
+        bucket_id -> Nullable<Integer>,
+        counterparty -> Text,
+        date -> Text,
+        description -> Text,
     }
 }
 
-diesel::joinable!(budget_drips -> buckets (bucket_id));
-diesel::joinable!(transaction_categorisations -> buckets (bucket_id));
-diesel::joinable!(transaction_categorisations -> transactions (transaction_id));
+diesel::joinable!(drips -> buckets (bucket_id));
+diesel::joinable!(pipes -> buckets (bucket_id));
 diesel::joinable!(transactions -> accounts (account_id));
 
 diesel::allow_tables_to_appear_in_same_query!(
     account_transfers,
     accounts,
-    budget_drips,
-    buckets,
     bucket_transfers,
-    transaction_categorisations,
+    buckets,
+    drips,
+    pipes,
     transactions,
 );
diff --git a/schist_core/schist_models/src/transaction.rs b/schist_core/schist_models/src/transaction.rs
index 9e983f5..7e754be 100644
--- a/schist_core/schist_models/src/transaction.rs
+++ b/schist_core/schist_models/src/transaction.rs
@@ -3,14 +3,17 @@ use diesel::prelude::{Associations, Identifiable, Insertable, Queryable, Selecta
 
 use crate::{account::Account, date_utc::DateUtc};
 
-#[derive(Builder, Clone, Queryable, Identifiable, Selectable, Insertable, Associations, Debug, PartialEq)]
+#[derive(
+    Builder, Clone, Queryable, Identifiable, Selectable, Insertable, Associations, Debug, PartialEq,
+)]
 #[diesel(table_name = crate::schema::transactions)]
 #[diesel(belongs_to(Account))]
 pub struct Transaction {
     pub id: i32,
-    pub description: String,
-    pub payee: String,
-    pub quantity: i32,
-    pub date: DateUtc,
     pub account_id: i32,
+    pub amount: i32,
+    pub bucket_id: Option<i32>,
+    pub counterparty: String,
+    pub date: DateUtc,
+    pub description: String,
 }
diff --git a/schist_core/schist_models/src/transaction_categorisation.rs b/schist_core/schist_models/src/transaction_categorisation.rs
deleted file mode 100644
index 36752ee..0000000
--- a/schist_core/schist_models/src/transaction_categorisation.rs
+++ /dev/null
@@ -1,12 +0,0 @@
-use derive_builder::Builder;
-use diesel::prelude::*;
-
-#[derive(Builder, Queryable, Identifiable, Selectable, Insertable, Debug, PartialEq)]
-#[diesel(table_name = crate::schema::transaction_categorisations)]
-pub struct TransactionCategorisation {
-    pub id: i32,
-    pub description: String,
-    pub quantity: i32,
-    pub transaction_id: i32,
-    pub bucket_id: i32,
-}
diff --git a/schist_core/schist_models/user_data.sqlite b/schist_core/schist_models/user_data.sqlite
index a428ff0..175ce60 100644
--- a/schist_core/schist_models/user_data.sqlite
+++ b/schist_core/schist_models/user_data.sqlite