[RFC PATCH v1 1/1] db: Create starting DDL for saving transaction

[Alviro Iskandar Setiawan <alviro.iskandar@gnuweeb.org>]

In order to keep the transaction record consistent, we can use a
chaining hash system that generates the transaction ID by using its
parent ID and the current transaction details.

For example, to create a new transaction, we have several things to
note, like:

  1. date
  2. description
  3. commiter
  4. amount

  [ other metadata stuff may be included ]

We can then generate the transaction id by using a hash function,
let's say SHA1:

 trx_info = date . description . commiter . amount . parent_trx_id
 trx_id   = SHA1(info)
 SAVE(trx_id, date, description, commiter, amount, parent_trx_id)

Note that since the current trx ID uses its parent trx ID to generate
itself, it makes the situation impossible to rewrite the history
without breaking the whole chain. That's the point of the consistency
in this design. We can verify that the data is consistent by just
re-hashing the transaction info together with its parent trx id.

Also note that, for the first transaction, it doesn't have a parent
trx id. So only the first transaction doesn't use a parent trx id to
generate itself. Let's call it the genesis transaction.

Signed-off-by: Alviro Iskandar Setiawan <alviro.iskandar@gnuweeb.org>
---
 database.sql | 25 +++++++++++++++++++++++++
 1 file changed, 25 insertions(+)
 create mode 100644 database.sql

diff --git a/database.sql b/database.sql
new file mode 100644
index 0000000..d191a45
--- /dev/null
+++ b/database.sql
@@ -0,0 +1,25 @@
+SET NAMES utf8;
+SET time_zone = '+00:00';
+SET foreign_key_checks = 0;
+SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+
+SET NAMES utf8mb4;
+
+DROP TABLE IF EXISTS `transactions`;
+CREATE TABLE `transactions` (
+  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
+  `trx_id` binary(20) NOT NULL,
+  `parent_trx_id` binary(20) NOT NULL,
+  `committer` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL,
+  `amount` bigint unsigned NOT NULL,
+  `notes` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
+  `created_at` datetime NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `committer` (`committer`),
+  KEY `amount` (`amount`),
+  KEY `created_at` (`created_at`),
+  KEY `trx_id` (`trx_id`),
+  KEY `parent_trx_id` (`parent_trx_id`),
+  FULLTEXT KEY `notes` (`notes`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
+
-- 
2.34.1