[RFC PATCH v1 0/1] GNU/Weeb Financial Transaction Record Design

[RFC PATCH v1 0/1] GNU/Weeb Financial Transaction Record Design

[Alviro Iskandar Setiawan]

Hi,

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.

Please comment!

tq

-- Viro

Signed-off-by: Alviro Iskandar Setiawan <alviro.iskandar@gnuweeb.org>
---

Alviro Iskandar Setiawan (1):
  db: Create starting DDL for saving transaction

 database.sql | 25 +++++++++++++++++++++++++
 1 file changed, 25 insertions(+)
 create mode 100644 database.sql

-- 
Alviro Iskandar Setiawan

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

[Alviro Iskandar Setiawan]

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

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

[Ammar Faizi]

Hi Al,

On 6/28/22 1:21 PM, Alviro Iskandar Setiawan wrote:
> +CREATE TABLE `transactions` (
> +  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
> +  `trx_id` binary(20) NOT NULL,
> +  `parent_trx_id` binary(20) NOT NULL,

My worry on this is: you reference a parent transaction ID with the
sha1 hash, there is no backup if that gets changed. What about using
the sequence ID to reference it?

> +  `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`),

As per my comment above, this can be a foreign key to the same
table, to the PK id. Yes? No? Comment?

-- 
Ammar Faizi

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

[Alviro Iskandar Setiawan]

On Tue, Jun 28, 2022 at 2:27 PM Ammar Faizi wrote:
> Hi Al,
>
> On 6/28/22 1:21 PM, Alviro Iskandar Setiawan wrote:
> > +CREATE TABLE `transactions` (
> > +  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
> > +  `trx_id` binary(20) NOT NULL,
> > +  `parent_trx_id` binary(20) NOT NULL,
>
> My worry on this is: you reference a parent transaction ID with the
> sha1 hash, there is no backup if that gets changed. What about using
> the sequence ID to reference it?

That should never change, if it ever gets changed, something has gone
very wrong.

> > +  `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`),
>
> As per my comment above, this can be a foreign key to the same
> table, to the PK id. Yes? No? Comment?

Sup.

tq

-- Viro

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

[Ammar Faizi]

On 6/28/22 2:31 PM, Alviro Iskandar Setiawan wrote:
> On Tue, Jun 28, 2022 at 2:27 PM Ammar Faizi wrote:
>> Hi Al,
>>
>> On 6/28/22 1:21 PM, Alviro Iskandar Setiawan wrote:
>>> +CREATE TABLE `transactions` (
>>> +  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
>>> +  `trx_id` binary(20) NOT NULL,
>>> +  `parent_trx_id` binary(20) NOT NULL,
>>
>> My worry on this is: you reference a parent transaction ID with the
>> sha1 hash, there is no backup if that gets changed. What about using
>> the sequence ID to reference it?
> 
> That should never change, if it ever gets changed, something has gone
> very wrong.

That's not what I mean.

Yes, if we ever had a corruption, something has gone very wrong. But
my point is: if that gets corrupted, you can't no longer find the
parent ID because the SHA1 is not sequential, but our primary key is.

Plus we will be dealing with transaction, which makes the situation
better because the last transaction will never have a smaller seq ID
than the previous transaction, the PK generation is atomic, so the
increment too.

Also, considering space...

You can save more space using the sequence ID, because it's smaller
than SHA1. You can still get the parent SHA1 by doing a simple lookup
with that foreign key. At the end of the day, we get nothing to lose
using seq ID as a reference. We even save more space.

-- 
Ammar Faizi

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

[Ammar Faizi]

On 6/28/22 2:47 PM, Ammar Faizi wrote:
> you can't no longer find

Sorry, that's a typo, should be:

   "you can no longer find"

-- 
Ammar Faizi

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

[Alviro Iskandar Setiawan]

On Tue, Jun 28, 2022 at 2:47 PM Ammar Faizi wrote:
> On 6/28/22 2:31 PM, Alviro Iskandar Setiawan wrote:
> > On Tue, Jun 28, 2022 at 2:27 PM Ammar Faizi wrote:
> > > Hi Al,
> > >
> > > On 6/28/22 1:21 PM, Alviro Iskandar Setiawan wrote:
> > > > +CREATE TABLE `transactions` (
> > > > +  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
> > > > +  `trx_id` binary(20) NOT NULL,
> > > > +  `parent_trx_id` binary(20) NOT NULL,
> > >
> > > My worry on this is: you reference a parent transaction ID with the
> > > sha1 hash, there is no backup if that gets changed. What about using
> > > the sequence ID to reference it?
> >
> > That should never change, if it ever gets changed, something has gone
> > very wrong.
>
> That's not what I mean.
>
> Yes, if we ever had a corruption, something has gone very wrong. But
> my point is: if that gets corrupted, you can't [sic] no longer find the
> parent ID because the SHA1 is not sequential, but our primary key is.

[ sic: s/can't/can/ ]

ic ic, i will do it.

> Plus we will be dealing with transaction, which makes the situation
> better because the last transaction will never have a smaller seq ID
> than the previous transaction, the PK generation is atomic, so the
> increment too.
>
> Also, considering space...
>
> You can save more space using the sequence ID, because it's smaller
> than SHA1. You can still get the parent SHA1 by doing a simple lookup
> with that foreign key. At the end of the day, we get nothing to lose
> using seq ID as a reference. We even save more space.

I will send v2 with that change later.

tq

-- Viro

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

[Ammar Faizi]

On 6/28/22 3:03 PM, Alviro Iskandar Setiawan wrote:
> On Tue, Jun 28, 2022 at 2:47 PM Ammar Faizi wrote:
>> On 6/28/22 2:31 PM, Alviro Iskandar Setiawan wrote:
>>> On Tue, Jun 28, 2022 at 2:27 PM Ammar Faizi wrote:
>>>> Hi Al,
>>>>
>>>> On 6/28/22 1:21 PM, Alviro Iskandar Setiawan wrote:
>>>>> +CREATE TABLE `transactions` (
>>>>> +  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
>>>>> +  `trx_id` binary(20) NOT NULL,
>>>>> +  `parent_trx_id` binary(20) NOT NULL,
>>>>
>>>> My worry on this is: you reference a parent transaction ID with the
>>>> sha1 hash, there is no backup if that gets changed. What about using
>>>> the sequence ID to reference it?
>>>
>>> That should never change, if it ever gets changed, something has gone
>>> very wrong.
>>
>> That's not what I mean.
>>
>> Yes, if we ever had a corruption, something has gone very wrong. But
>> my point is: if that gets corrupted, you can't [sic] no longer find the
>> parent ID because the SHA1 is not sequential, but our primary key is.
> 
> [ sic: s/can't/can/ ]

Heh, this is my first time seeing someone using [sic] in email.

> ic ic, i will do it.
> 
>> Plus we will be dealing with transaction, which makes the situation
>> better because the last transaction will never have a smaller seq ID
>> than the previous transaction, the PK generation is atomic, so the
>> increment too.
>>
>> Also, considering space...
>>
>> You can save more space using the sequence ID, because it's smaller
>> than SHA1. You can still get the parent SHA1 by doing a simple lookup
>> with that foreign key. At the end of the day, we get nothing to lose
>> using seq ID as a reference. We even save more space.
> 
> I will send v2 with that change later.
> 
> tq

Great, thanks!

-- 
Ammar Faizi