From bfe4aa78d882b9d95bd1f954371136f3aa5c38c9 Mon Sep 17 00:00:00 2001
From: Joris
Date: Fri, 7 Feb 2025 08:25:28 +0100
Subject: Migrate database at startup

---
 Cargo.lock                               |  11 ++++
 Cargo.toml                               |   1 +
 README.md                                |  14 ++--
 bin/db                                   |  26 --------
 sql/migrations/1.sql                     |  65 -------------------
 sql/migrations/2.sql                     |  44 -------------
 sql/migrations/3.sql                     |   5 --
 sql/migrations/4.sql                     |  91 --------------------------
 sql/migrations/5.sql                     | 107 -------------------------------
 src/db/migration/01-init.sql             |  65 +++++++++++++++++++
 src/db/migration/02-payment-category.sql |  44 +++++++++++++
 src/db/migration/03-sign-in-token.sql    |   5 ++
 src/db/migration/04-plural-naming.sql    |  91 ++++++++++++++++++++++++++
 src/db/migration/05-strict-tables.sql    | 107 +++++++++++++++++++++++++++++++
 src/db/mod.rs                            |  35 ++++++++--
 15 files changed, 363 insertions(+), 348 deletions(-)
 delete mode 100755 bin/db
 delete mode 100644 sql/migrations/1.sql
 delete mode 100644 sql/migrations/2.sql
 delete mode 100644 sql/migrations/3.sql
 delete mode 100644 sql/migrations/4.sql
 delete mode 100644 sql/migrations/5.sql
 create mode 100644 src/db/migration/01-init.sql
 create mode 100644 src/db/migration/02-payment-category.sql
 create mode 100644 src/db/migration/03-sign-in-token.sql
 create mode 100644 src/db/migration/04-plural-naming.sql
 create mode 100644 src/db/migration/05-strict-tables.sql

diff --git a/Cargo.lock b/Cargo.lock
index 69ab396..bcb6771 100644
--- a/Cargo.lock
+++ b/Cargo.lock
@@ -198,6 +198,7 @@ dependencies = [
  "rand",
  "rand_core",
  "rusqlite",
+ "rusqlite_migration",
  "serde",
  "serde_json",
  "serde_urlencoded",
@@ -1072,6 +1073,16 @@ dependencies = [
  "smallvec",
 ]
 
+[[package]]
+name = "rusqlite_migration"
+version = "1.3.1"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "923b42e802f7dc20a0a6b5e097ba7c83fe4289da07e49156fecf6af08aa9cd1c"
+dependencies = [
+ "log",
+ "rusqlite",
+]
+
 [[package]]
 name = "rustc-demangle"
 version = "0.1.24"
diff --git a/Cargo.toml b/Cargo.toml
index e301c69..88eac72 100644
--- a/Cargo.toml
+++ b/Cargo.toml
@@ -19,6 +19,7 @@ minijinja = { version = "2.7", features = ["loader"] }
 rand = { version = "0.9", features = ["os_rng"] }
 rand_core = "0.9"
 rusqlite = { version = "0.32", features = ["chrono"] }
+rusqlite_migration = "1.3"
 serde = { version = "1.0", features = ["derive"] }
 serde_json = "1.0"
 serde_urlencoded = "0.7"
diff --git a/README.md b/README.md
index 3905ef2..8e02d05 100644
--- a/README.md
+++ b/README.md
@@ -9,11 +9,15 @@
 
 1. Use `nix develop` to download dependencies.
 
-2. Initialize the database with `bin/db init` if required.
+2. Start the application:
 
-3. Start the application with `bin/dev-server`.
+        bin/dev-server
 
-4. Connect with either:
+3. Add fixtures:
 
-- `john@mail.com` / `password`
-- or `lisa@mail.com` / `password`.
+        sqlite3 database.db < sql/fixtures.sql
+
+4. Credentials will be:
+
+    - john@mail.com / password
+    - lisa@mail.com / password
diff --git a/bin/db b/bin/db
deleted file mode 100755
index 186f83d..0000000
--- a/bin/db
+++ /dev/null
@@ -1,26 +0,0 @@
-#!/usr/bin/env bash
-set -euo pipefail
-cd $(dirname "$0")/..
-
-DB_PATH="database.db"
-
-if [ "$1" == "init" ]; then
-
-  if [ -f "$DB_PATH" ]; then
-    rm "$DB_PATH"
-  fi
-
-  for MIGRATION in $(ls sql/migrations/*.sql); do
-    printf "\n- Applying $MIGRATION\n\n"
-    sqlite3 database.db < "$MIGRATION"
-  done
-
-  printf "\n- Applying sql/fixtures.sql\n\n"
-  sqlite3 database.db < sql/fixtures.sql
-
-else
-
-  echo "Usage: $0 init"
-  exit 1
-
-fi
diff --git a/sql/migrations/1.sql b/sql/migrations/1.sql
deleted file mode 100644
index d7c300e..0000000
--- a/sql/migrations/1.sql
+++ /dev/null
@@ -1,65 +0,0 @@
-CREATE TABLE IF NOT EXISTS "user" (
-  "id" INTEGER PRIMARY KEY,
-  "creation" TIMESTAMP NOT NULL,
-  "email" VARCHAR NOT NULL,
-  "name" VARCHAR NOT NULL,
-  CONSTRAINT "uniq_user_email" UNIQUE ("email"),
-  CONSTRAINT "uniq_user_name" UNIQUE ("name")
-);
-
-CREATE TABLE IF NOT EXISTS "job" (
-  "id" INTEGER PRIMARY KEY,
-  "kind" VARCHAR NOT NULL,
-  "last_execution" TIMESTAMP NULL,
-  "last_check" TIMESTAMP NULL,
-  CONSTRAINT "uniq_job_kind" UNIQUE ("kind")
-);
-
-CREATE TABLE IF NOT EXISTS "sign_in"(
-  "id" INTEGER PRIMARY KEY,
-  "token" VARCHAR NOT NULL,
-  "creation" TIMESTAMP NOT NULL,
-  "email" VARCHAR NOT NULL,
-  "is_used" BOOLEAN NOT NULL,
-  CONSTRAINT "uniq_sign_in_token" UNIQUE ("token")
-);
-
-CREATE TABLE IF NOT EXISTS "payment"(
-  "id" INTEGER PRIMARY KEY,
-  "user_id" INTEGER NOT NULL REFERENCES "user",
-  "name" VARCHAR NOT NULL,
-  "cost" INTEGER NOT NULL,
-  "date" DATE NOT NULL,
-  "frequency" VARCHAR NOT NULL,
-  "created_at" TIMESTAMP NOT NULL,
-  "edited_at" TIMESTAMP NULL,
-  "deleted_at" TIMESTAMP NULL
-);
-
-CREATE TABLE IF NOT EXISTS "income"(
-  "id" INTEGER PRIMARY KEY,
-  "user_id" INTEGER NOT NULL REFERENCES "user",
-  "date" DATE NOT NULL,
-  "amount" INTEGERNOT NULL,
-  "created_at" TIMESTAMP NOT NULL,
-  "edited_at" TIMESTAMP NULL,
-  "deleted_at" TIMESTAMP NULL
-);
-
-CREATE TABLE IF NOT EXISTS "category"(
-  "id" INTEGER PRIMARY KEY,
-  "name" VARCHAR NOT NULL,
-  "color" VARCHAR NOT NULL,
-  "created_at" TIMESTAMP NOT NULL,
-  "edited_at" TIMESTAMP NULL,
-  "deleted_at" TIMESTAMP NULL
-);
-
-CREATE TABLE IF NOT EXISTS "payment_category"(
-  "id" INTEGER PRIMARY KEY,
-  "name" VARCHAR NOT NULL,
-  "category" INTEGER NOT NULL REFERENCES "category",
-  "created_at" TIMESTAMP NOT NULL,
-  "edited_at" TIMESTAMP NULL,
-  CONSTRAINT "uniq_payment_category_name" UNIQUE ("name")
-);
diff --git a/sql/migrations/2.sql b/sql/migrations/2.sql
deleted file mode 100644
index c1d502f..0000000
--- a/sql/migrations/2.sql
+++ /dev/null
@@ -1,44 +0,0 @@
--- Add payment categories with accents from payment with accents
-
-INSERT INTO
-  payment_category (name, category, created_at)
-SELECT
-  DISTINCT lower(payment.name), payment_category.category, datetime('now')
-FROM
-  payment
-INNER JOIN
-  payment_category
-ON
-  replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(payment.name), 'é', 'e'), 'è', 'e'), 'à', 'a'), 'û', 'u'), 'â', 'a'), 'ê', 'e'), 'â', 'a'), 'î', 'i'), 'ï', 'i'), 'ô', 'o'), 'ë', 'e') = payment_category.name
-WHERE
-  payment.name
-IN
-  (SELECT DISTINCT payment.name FROM payment WHERE lower(payment.name) NOT IN (SELECT payment_category.name FROM payment_category) AND payment.deleted_at IS NULL);
-
--- Remove unused payment categories
-
-DELETE FROM
-  payment_category
-WHERE
-  name NOT IN (SELECT DISTINCT lower(name) FROM payment);
-
--- Add category id to payment table
-
-PRAGMA foreign_keys = 0;
-
-ALTER TABLE payment ADD COLUMN "category" INTEGER NOT NULL REFERENCES "category" DEFAULT -1;
-
-PRAGMA foreign_keys = 1;
-
-UPDATE
-  payment
-SET
-  category = (SELECT category FROM payment_category WHERE payment_category.name = LOWER(payment.name))
-WHERE
-  EXISTS (SELECT category FROM payment_category WHERE payment_category.name = LOWER(payment.name));
-
-DELETE FROM payment WHERE category = -1;
-
--- Remove
-
-DROP TABLE payment_category;
diff --git a/sql/migrations/3.sql b/sql/migrations/3.sql
deleted file mode 100644
index a3d8a13..0000000
--- a/sql/migrations/3.sql
+++ /dev/null
@@ -1,5 +0,0 @@
-DROP TABLE sign_in;
-
-ALTER TABLE user ADD COLUMN "password" TEXT NOT NULL DEFAULT "password";
-
-ALTER TABLE user ADD COLUMN "sign_in_token" TEXT NULL;
diff --git a/sql/migrations/4.sql b/sql/migrations/4.sql
deleted file mode 100644
index ec386cb..0000000
--- a/sql/migrations/4.sql
+++ /dev/null
@@ -1,91 +0,0 @@
--- Payments
-
-CREATE TABLE IF NOT EXISTS "payments"(
-  "id" INTEGER PRIMARY KEY,
-  "user_id" INTEGER NOT NULL REFERENCES "users",
-  "name" TEXT NOT NULL,
-  "cost" INTEGER NOT NULL,
-  "date" DATE NOT NULL,
-  "frequency" TEXT NOT NULL,
-  "category_id" INTEGER NOT NULL REFERENCES "categories",
-  "created_at" DATE NULL DEFAULT (datetime('now')),
-  "updated_at" DATE NULL,
-  "deleted_at" DATE NULL
-);
-
-INSERT INTO payments (id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at)
-  SELECT id, user_id, name, cost, date, frequency, category, created_at, edited_at, deleted_at
-  FROM payment;
-
-DROP TABLE payment;
-
-CREATE INDEX payment_date ON payments(date);
-
--- Categories
-
-CREATE TABLE IF NOT EXISTS "categories"(
-  "id" INTEGER PRIMARY KEY,
-  "name" TEXT NOT NULL,
-  "color" TEXT NOT NULL,
-  "created_at" DATE NULL DEFAULT (datetime('now')),
-  "updated_at" DATE NULL,
-  "deleted_at" DATE NULL
-);
-
-INSERT INTO categories (id, name, color, created_at, updated_at, deleted_at)
-  SELECT id, name, color, created_at, edited_at, deleted_at
-  FROM category;
-
-DROP TABLE category;
-
--- Users
-
-CREATE TABLE IF NOT EXISTS "users"(
-  "id" INTEGER PRIMARY KEY,
-  "email" TEXT NOT NULL,
-  "name" TEXT NOT NULL,
-  "password" TEXT NOT NULL,
-  "login_token" TEXT NULL,
-  "created_at" DATE NULL DEFAULT (datetime('now')),
-  "updated_at" DATE NULL,
-  "deleted_at" DATE NULL,
-  CONSTRAINT "uniq_user_email" UNIQUE ("email"),
-  CONSTRAINT "uniq_user_name" UNIQUE ("name")
-);
-
-INSERT INTO users (id, created_at, email, name, password, login_token)
-  SELECT id, creation, email, name, password, sign_in_token
-  FROM user;
-
-DROP TABLE user;
-
--- Jobs
-
-CREATE TABLE IF NOT EXISTS "jobs"(
-  "name" TEXT PRIMARY KEY,
-  "last_execution" DATE NOT NULL DEFAULT (datetime('now'))
-);
-
-INSERT INTO jobs (name, last_execution)
-  SELECT kind, last_execution
-  FROM job;
-
-DROP TABLE job;
-
--- Incomes
-
-CREATE TABLE IF NOT EXISTS "incomes"(
-  "id" INTEGER PRIMARY KEY,
-  "user_id" INTEGER NOT NULL REFERENCES "users",
-  "date" DATE NOT NULL,
-  "amount" INTEGER NOT NULL,
-  "created_at" DATE NULL DEFAULT (datetime('now')),
-  "updated_at" DATE NULL,
-  "deleted_at" DATE NULL
-);
-
-INSERT INTO incomes (id, user_id, date, amount, created_at, updated_at, deleted_at)
-  SELECT id, user_id, date, amount, created_at, edited_at, deleted_at
-  FROM income;
-
-DROP TABLE income;
diff --git a/sql/migrations/5.sql b/sql/migrations/5.sql
deleted file mode 100644
index cf7ef4b..0000000
--- a/sql/migrations/5.sql
+++ /dev/null
@@ -1,107 +0,0 @@
--- Activate strict mode
-
--- Start with users and categories, as it’s referenced in other tables.
--- Otherwise, the reference is set to the renamed non strict table.
-
--- Users
-
-ALTER TABLE "users" RENAME TO "users_non_strict";
-
-CREATE TABLE IF NOT EXISTS "users"(
-  "id" INTEGER PRIMARY KEY,
-  "email" TEXT NOT NULL,
-  "name" TEXT NOT NULL,
-  "password" TEXT NOT NULL,
-  "login_token" TEXT NULL,
-  "created_at" TEXT NULL DEFAULT (datetime('now')),
-  "updated_at" TEXT NULL,
-  "deleted_at" TEXT NULL,
-  CONSTRAINT "uniq_user_email" UNIQUE ("email"),
-  CONSTRAINT "uniq_user_name" UNIQUE ("name")
-) STRICT;
-
-INSERT INTO users (id, created_at, email, name, password, login_token)
-  SELECT id, created_at, email, name, password, login_token
-  FROM users_non_strict;
-
-DROP TABLE users_non_strict;
-
--- Categories
-
-ALTER TABLE "categories" RENAME TO "categories_non_strict";
-
-CREATE TABLE IF NOT EXISTS "categories"(
-  "id" INTEGER PRIMARY KEY,
-  "name" TEXT NOT NULL,
-  "color" TEXT NOT NULL,
-  "created_at" TEXT NULL DEFAULT (datetime('now')),
-  "updated_at" TEXT NULL,
-  "deleted_at" TEXT NULL
-) STRICT;
-
-INSERT INTO categories (id, name, color, created_at, updated_at, deleted_at)
-  SELECT id, name, color, created_at, updated_at, deleted_at
-  FROM categories_non_strict;
-
-DROP TABLE categories_non_strict;
-
--- Payments
-
-ALTER TABLE "payments" RENAME TO "payments_non_strict";
-
-CREATE TABLE IF NOT EXISTS "payments"(
-  "id" INTEGER PRIMARY KEY,
-  "user_id" INTEGER NOT NULL REFERENCES "users",
-  "name" TEXT NOT NULL,
-  "cost" INTEGER NOT NULL,
-  "date" TEXT NOT NULL,
-  "frequency" TEXT NOT NULL,
-  "category_id" INTEGER NOT NULL REFERENCES "categories",
-  "created_at" TEXT NULL DEFAULT (datetime('now')),
-  "updated_at" TEXT NULL,
-  "deleted_at" TEXT NULL
-) STRICT;
-
-DROP INDEX IF EXISTS payment_date;
-CREATE INDEX payment_date ON payments(date);
-
-INSERT INTO payments (id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at)
-  SELECT id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at
-  FROM payments_non_strict;
-
-DROP TABLE payments_non_strict;
-
--- Jobs
-
-ALTER TABLE "jobs" RENAME TO "jobs_non_strict";
-
-CREATE TABLE IF NOT EXISTS "jobs"(
-  "name" TEXT PRIMARY KEY,
-  "last_execution" TEXT NOT NULL DEFAULT (datetime('now'))
-) STRICT;
-
-INSERT INTO jobs (name, last_execution)
-  SELECT name, last_execution
-  FROM jobs_non_strict;
-
-DROP TABLE jobs_non_strict;
-
--- Incomes
-
-ALTER TABLE "incomes" RENAME TO "incomes_non_strict";
-
-CREATE TABLE IF NOT EXISTS "incomes"(
-  "id" INTEGER PRIMARY KEY,
-  "user_id" INTEGER NOT NULL REFERENCES "users",
-  "date" TEXT NOT NULL,
-  "amount" INTEGER NOT NULL,
-  "created_at" TEXT NULL DEFAULT (datetime('now')),
-  "updated_at" TEXT NULL,
-  "deleted_at" TEXT NULL
-) STRICT;
-
-INSERT INTO incomes (id, user_id, date, amount, created_at, updated_at, deleted_at)
-  SELECT id, user_id, date, amount, created_at, updated_at, deleted_at
-  FROM incomes_non_strict;
-
-DROP TABLE incomes_non_strict;
diff --git a/src/db/migration/01-init.sql b/src/db/migration/01-init.sql
new file mode 100644
index 0000000..d7c300e
--- /dev/null
+++ b/src/db/migration/01-init.sql
@@ -0,0 +1,65 @@
+CREATE TABLE IF NOT EXISTS "user" (
+  "id" INTEGER PRIMARY KEY,
+  "creation" TIMESTAMP NOT NULL,
+  "email" VARCHAR NOT NULL,
+  "name" VARCHAR NOT NULL,
+  CONSTRAINT "uniq_user_email" UNIQUE ("email"),
+  CONSTRAINT "uniq_user_name" UNIQUE ("name")
+);
+
+CREATE TABLE IF NOT EXISTS "job" (
+  "id" INTEGER PRIMARY KEY,
+  "kind" VARCHAR NOT NULL,
+  "last_execution" TIMESTAMP NULL,
+  "last_check" TIMESTAMP NULL,
+  CONSTRAINT "uniq_job_kind" UNIQUE ("kind")
+);
+
+CREATE TABLE IF NOT EXISTS "sign_in"(
+  "id" INTEGER PRIMARY KEY,
+  "token" VARCHAR NOT NULL,
+  "creation" TIMESTAMP NOT NULL,
+  "email" VARCHAR NOT NULL,
+  "is_used" BOOLEAN NOT NULL,
+  CONSTRAINT "uniq_sign_in_token" UNIQUE ("token")
+);
+
+CREATE TABLE IF NOT EXISTS "payment"(
+  "id" INTEGER PRIMARY KEY,
+  "user_id" INTEGER NOT NULL REFERENCES "user",
+  "name" VARCHAR NOT NULL,
+  "cost" INTEGER NOT NULL,
+  "date" DATE NOT NULL,
+  "frequency" VARCHAR NOT NULL,
+  "created_at" TIMESTAMP NOT NULL,
+  "edited_at" TIMESTAMP NULL,
+  "deleted_at" TIMESTAMP NULL
+);
+
+CREATE TABLE IF NOT EXISTS "income"(
+  "id" INTEGER PRIMARY KEY,
+  "user_id" INTEGER NOT NULL REFERENCES "user",
+  "date" DATE NOT NULL,
+  "amount" INTEGERNOT NULL,
+  "created_at" TIMESTAMP NOT NULL,
+  "edited_at" TIMESTAMP NULL,
+  "deleted_at" TIMESTAMP NULL
+);
+
+CREATE TABLE IF NOT EXISTS "category"(
+  "id" INTEGER PRIMARY KEY,
+  "name" VARCHAR NOT NULL,
+  "color" VARCHAR NOT NULL,
+  "created_at" TIMESTAMP NOT NULL,
+  "edited_at" TIMESTAMP NULL,
+  "deleted_at" TIMESTAMP NULL
+);
+
+CREATE TABLE IF NOT EXISTS "payment_category"(
+  "id" INTEGER PRIMARY KEY,
+  "name" VARCHAR NOT NULL,
+  "category" INTEGER NOT NULL REFERENCES "category",
+  "created_at" TIMESTAMP NOT NULL,
+  "edited_at" TIMESTAMP NULL,
+  CONSTRAINT "uniq_payment_category_name" UNIQUE ("name")
+);
diff --git a/src/db/migration/02-payment-category.sql b/src/db/migration/02-payment-category.sql
new file mode 100644
index 0000000..c1d502f
--- /dev/null
+++ b/src/db/migration/02-payment-category.sql
@@ -0,0 +1,44 @@
+-- Add payment categories with accents from payment with accents
+
+INSERT INTO
+  payment_category (name, category, created_at)
+SELECT
+  DISTINCT lower(payment.name), payment_category.category, datetime('now')
+FROM
+  payment
+INNER JOIN
+  payment_category
+ON
+  replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(payment.name), 'é', 'e'), 'è', 'e'), 'à', 'a'), 'û', 'u'), 'â', 'a'), 'ê', 'e'), 'â', 'a'), 'î', 'i'), 'ï', 'i'), 'ô', 'o'), 'ë', 'e') = payment_category.name
+WHERE
+  payment.name
+IN
+  (SELECT DISTINCT payment.name FROM payment WHERE lower(payment.name) NOT IN (SELECT payment_category.name FROM payment_category) AND payment.deleted_at IS NULL);
+
+-- Remove unused payment categories
+
+DELETE FROM
+  payment_category
+WHERE
+  name NOT IN (SELECT DISTINCT lower(name) FROM payment);
+
+-- Add category id to payment table
+
+PRAGMA foreign_keys = 0;
+
+ALTER TABLE payment ADD COLUMN "category" INTEGER NOT NULL REFERENCES "category" DEFAULT -1;
+
+PRAGMA foreign_keys = 1;
+
+UPDATE
+  payment
+SET
+  category = (SELECT category FROM payment_category WHERE payment_category.name = LOWER(payment.name))
+WHERE
+  EXISTS (SELECT category FROM payment_category WHERE payment_category.name = LOWER(payment.name));
+
+DELETE FROM payment WHERE category = -1;
+
+-- Remove
+
+DROP TABLE payment_category;
diff --git a/src/db/migration/03-sign-in-token.sql b/src/db/migration/03-sign-in-token.sql
new file mode 100644
index 0000000..a3d8a13
--- /dev/null
+++ b/src/db/migration/03-sign-in-token.sql
@@ -0,0 +1,5 @@
+DROP TABLE sign_in;
+
+ALTER TABLE user ADD COLUMN "password" TEXT NOT NULL DEFAULT "password";
+
+ALTER TABLE user ADD COLUMN "sign_in_token" TEXT NULL;
diff --git a/src/db/migration/04-plural-naming.sql b/src/db/migration/04-plural-naming.sql
new file mode 100644
index 0000000..ec386cb
--- /dev/null
+++ b/src/db/migration/04-plural-naming.sql
@@ -0,0 +1,91 @@
+-- Payments
+
+CREATE TABLE IF NOT EXISTS "payments"(
+  "id" INTEGER PRIMARY KEY,
+  "user_id" INTEGER NOT NULL REFERENCES "users",
+  "name" TEXT NOT NULL,
+  "cost" INTEGER NOT NULL,
+  "date" DATE NOT NULL,
+  "frequency" TEXT NOT NULL,
+  "category_id" INTEGER NOT NULL REFERENCES "categories",
+  "created_at" DATE NULL DEFAULT (datetime('now')),
+  "updated_at" DATE NULL,
+  "deleted_at" DATE NULL
+);
+
+INSERT INTO payments (id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at)
+  SELECT id, user_id, name, cost, date, frequency, category, created_at, edited_at, deleted_at
+  FROM payment;
+
+DROP TABLE payment;
+
+CREATE INDEX payment_date ON payments(date);
+
+-- Categories
+
+CREATE TABLE IF NOT EXISTS "categories"(
+  "id" INTEGER PRIMARY KEY,
+  "name" TEXT NOT NULL,
+  "color" TEXT NOT NULL,
+  "created_at" DATE NULL DEFAULT (datetime('now')),
+  "updated_at" DATE NULL,
+  "deleted_at" DATE NULL
+);
+
+INSERT INTO categories (id, name, color, created_at, updated_at, deleted_at)
+  SELECT id, name, color, created_at, edited_at, deleted_at
+  FROM category;
+
+DROP TABLE category;
+
+-- Users
+
+CREATE TABLE IF NOT EXISTS "users"(
+  "id" INTEGER PRIMARY KEY,
+  "email" TEXT NOT NULL,
+  "name" TEXT NOT NULL,
+  "password" TEXT NOT NULL,
+  "login_token" TEXT NULL,
+  "created_at" DATE NULL DEFAULT (datetime('now')),
+  "updated_at" DATE NULL,
+  "deleted_at" DATE NULL,
+  CONSTRAINT "uniq_user_email" UNIQUE ("email"),
+  CONSTRAINT "uniq_user_name" UNIQUE ("name")
+);
+
+INSERT INTO users (id, created_at, email, name, password, login_token)
+  SELECT id, creation, email, name, password, sign_in_token
+  FROM user;
+
+DROP TABLE user;
+
+-- Jobs
+
+CREATE TABLE IF NOT EXISTS "jobs"(
+  "name" TEXT PRIMARY KEY,
+  "last_execution" DATE NOT NULL DEFAULT (datetime('now'))
+);
+
+INSERT INTO jobs (name, last_execution)
+  SELECT kind, last_execution
+  FROM job;
+
+DROP TABLE job;
+
+-- Incomes
+
+CREATE TABLE IF NOT EXISTS "incomes"(
+  "id" INTEGER PRIMARY KEY,
+  "user_id" INTEGER NOT NULL REFERENCES "users",
+  "date" DATE NOT NULL,
+  "amount" INTEGER NOT NULL,
+  "created_at" DATE NULL DEFAULT (datetime('now')),
+  "updated_at" DATE NULL,
+  "deleted_at" DATE NULL
+);
+
+INSERT INTO incomes (id, user_id, date, amount, created_at, updated_at, deleted_at)
+  SELECT id, user_id, date, amount, created_at, edited_at, deleted_at
+  FROM income;
+
+DROP TABLE income;
diff --git a/src/db/migration/05-strict-tables.sql b/src/db/migration/05-strict-tables.sql
new file mode 100644
index 0000000..cf7ef4b
--- /dev/null
+++ b/src/db/migration/05-strict-tables.sql
@@ -0,0 +1,107 @@
+-- Activate strict mode
+
+-- Start with users and categories, as it’s referenced in other tables.
+-- Otherwise, the reference is set to the renamed non strict table.
+
+-- Users
+
+ALTER TABLE "users" RENAME TO "users_non_strict";
+
+CREATE TABLE IF NOT EXISTS "users"(
+  "id" INTEGER PRIMARY KEY,
+  "email" TEXT NOT NULL,
+  "name" TEXT NOT NULL,
+  "password" TEXT NOT NULL,
+  "login_token" TEXT NULL,
+  "created_at" TEXT NULL DEFAULT (datetime('now')),
+  "updated_at" TEXT NULL,
+  "deleted_at" TEXT NULL,
+  CONSTRAINT "uniq_user_email" UNIQUE ("email"),
+  CONSTRAINT "uniq_user_name" UNIQUE ("name")
+) STRICT;
+
+INSERT INTO users (id, created_at, email, name, password, login_token)
+  SELECT id, created_at, email, name, password, login_token
+  FROM users_non_strict;
+
+DROP TABLE users_non_strict;
+
+-- Categories
+
+ALTER TABLE "categories" RENAME TO "categories_non_strict";
+
+CREATE TABLE IF NOT EXISTS "categories"(
+  "id" INTEGER PRIMARY KEY,
+  "name" TEXT NOT NULL,
+  "color" TEXT NOT NULL,
+  "created_at" TEXT NULL DEFAULT (datetime('now')),
+  "updated_at" TEXT NULL,
+  "deleted_at" TEXT NULL
+) STRICT;
+
+INSERT INTO categories (id, name, color, created_at, updated_at, deleted_at)
+  SELECT id, name, color, created_at, updated_at, deleted_at
+  FROM categories_non_strict;
+
+DROP TABLE categories_non_strict;
+
+-- Payments
+
+ALTER TABLE "payments" RENAME TO "payments_non_strict";
+
+CREATE TABLE IF NOT EXISTS "payments"(
+  "id" INTEGER PRIMARY KEY,
+  "user_id" INTEGER NOT NULL REFERENCES "users",
+  "name" TEXT NOT NULL,
+  "cost" INTEGER NOT NULL,
+  "date" TEXT NOT NULL,
+  "frequency" TEXT NOT NULL,
+  "category_id" INTEGER NOT NULL REFERENCES "categories",
+  "created_at" TEXT NULL DEFAULT (datetime('now')),
+  "updated_at" TEXT NULL,
+  "deleted_at" TEXT NULL
+) STRICT;
+
+DROP INDEX IF EXISTS payment_date;
+CREATE INDEX payment_date ON payments(date);
+
+INSERT INTO payments (id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at)
+  SELECT id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at
+  FROM payments_non_strict;
+
+DROP TABLE payments_non_strict;
+
+-- Jobs
+
+ALTER TABLE "jobs" RENAME TO "jobs_non_strict";
+
+CREATE TABLE IF NOT EXISTS "jobs"(
+  "name" TEXT PRIMARY KEY,
+  "last_execution" TEXT NOT NULL DEFAULT (datetime('now'))
+) STRICT;
+
+INSERT INTO jobs (name, last_execution)
+  SELECT name, last_execution
+  FROM jobs_non_strict;
+
+DROP TABLE jobs_non_strict;
+
+-- Incomes
+
+ALTER TABLE "incomes" RENAME TO "incomes_non_strict";
+
+CREATE TABLE IF NOT EXISTS "incomes"(
+  "id" INTEGER PRIMARY KEY,
+  "user_id" INTEGER NOT NULL REFERENCES "users",
+  "date" TEXT NOT NULL,
+  "amount" INTEGER NOT NULL,
+  "created_at" TEXT NULL DEFAULT (datetime('now')),
+  "updated_at" TEXT NULL,
+  "deleted_at" TEXT NULL
+) STRICT;
+
+INSERT INTO incomes (id, user_id, date, amount, created_at, updated_at, deleted_at)
+  SELECT id, user_id, date, amount, created_at, updated_at, deleted_at
+  FROM incomes_non_strict;
+
+DROP TABLE incomes_non_strict;
diff --git a/src/db/mod.rs b/src/db/mod.rs
index 1282f0c..ef5ccef 100644
--- a/src/db/mod.rs
+++ b/src/db/mod.rs
@@ -1,4 +1,5 @@
 use anyhow::{Error, Result};
+use rusqlite_migration::{Migrations, M};
 use tokio_rusqlite::Connection;
 
 pub mod categories;
@@ -13,13 +14,37 @@ pub async fn init(path: &str) -> Result<Connection> {
         Error::msg(format!("Error opening connection: {err}"))
     })?;
 
-    support_foreign_keys(&connection).await?;
-
+    apply_migrations(&connection).await?;
+    set_pragma(&connection, "foreign_keys", "ON").await?;
+    set_pragma(&connection, "journal_mode", "wal").await?;
     Ok(connection)
 }
 
-async fn support_foreign_keys(conn: &Connection) -> Result<()> {
+async fn apply_migrations(conn: &Connection) -> Result<()> {
+    let migrations = Migrations::new(vec![
+        M::up(include_str!("migration/01-init.sql")),
+        M::up(include_str!("migration/02-payment-category.sql")),
+        M::up(include_str!("migration/03-sign-in-token.sql")),
+        M::up(include_str!("migration/04-plural-naming.sql")),
+        M::up(include_str!("migration/05-strict-tables.sql")),
+    ]);
+
+    Ok(conn
+        .call(move |conn| {
+            migrations.to_latest(conn).map_err(|migration_err| {
+                tokio_rusqlite::Error::Other(Box::new(migration_err))
+            })
+        })
+        .await?)
+}
+
+async fn set_pragma(conn: &Connection, key: impl Into<String>, value: impl Into<String>) -> Result<()> {
+    let key = key.into();
+    let value = value.into();
     Ok(conn
-        .call(move |conn| Ok(conn.pragma_update(None, "foreign_keys", "ON")))
-        .await??)
+        .call(move |conn| {
+            conn.pragma_update(None, &key, &value)
+                .map_err(tokio_rusqlite::Error::Rusqlite)
+        })
+        .await?)
 }
-- 
cgit v1.2.3