2 files changed, 100 insertions, 0 deletions
diff --git a/schist_models/migrations/2024-08-31-084439_initial_setup/down.sql b/schist_models/migrations/2024-08-31-084439_initial_setup/down.sql
new file mode 100644
index 0000000..ba65012
--- /dev/null
+++ b/schist_models/migrations/2024-08-31-084439_initial_setup/down.sql
@@ -0,0 +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 transactions;
diff --git a/schist_models/migrations/2024-08-31-084439_initial_setup/up.sql b/schist_models/migrations/2024-08-31-084439_initial_setup/up.sql
new file mode 100644
index 0000000..a09bf7b
--- /dev/null
+++ b/schist_models/migrations/2024-08-31-084439_initial_setup/up.sql
@@ -0,0 +1,93 @@
+PRAGMA foreign_keys = ON;
+
+CREATE TABLE accounts(
+    id INTEGER NOT NULL PRIMARY KEY,
+    name TEXT NOT NULL,
+    opening_balance INTEGER NOT NULL,
+    opening_date TEXT NOT NULL
+);
+
+CREATE TABLE account_transfers(
+    id INTEGER NOT NULL PRIMARY KEY,
+    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)
+        REFERENCES accounts (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT,
+    FOREIGN KEY (to_account_id)
+        REFERENCES accounts (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT
+);
+
+
+CREATE TABLE budget_drips(
+    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)
+);
+
+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(
+    id INTEGER NOT NULL PRIMARY KEY,
+    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)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT,
+    FOREIGN KEY (to_category_id)
+        REFERENCES categories (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE transactions(
+    id INTEGER NOT NULL PRIMARY KEY,
+    description TEXT NOT NULL,
+    payee TEXT NOT NULL,
+    quantity INTEGER NOT NULL,
+    date TEXT NOT NULL,
+    account_id INTEGER NOT NULL,
+    FOREIGN KEY (account_id)
+        REFERENCES accounts (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT
+);
+
+CREATE TABLE transaction_categorisations(
+    id INTEGER NOT NULL PRIMARY KEY,
+    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)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT
+);