search

solid-adventure / trivial-api

Other
1 stars 1 forks source link

introducing registers #226

Closed vgkids closed 9 months ago

vgkids commented 9 months ago

Creating a register:

register = Register.create(name: "Test", owner: User.first, units: "USD")

Creating an item in the register:

register_item = register.register_items.create(amount: 1.00, description: "First transaction", owner: register.owner, units: "USD", unique_key: Time.now)
vgkids commented 9 months ago

Two deviations from the build plan after I started

RegisterItem meta data I started implementing this with a single jsonb meta column for items, but it felt like grouping by those keys was going to be both tedious to write, and non-performant once it was done.

Instead, I opted for 10 fixed columns with names meta0, meta1, etc, and carry customize labels for those columns in the register.meta, where we aren't expecting the same volume of data.

Doing a group by meta columns with 1m rows in a result set, we're reasonably performant, and I think this is worth spending some time with this design to see how it feels in the wild. Completed 200 OK in 150ms (Views: 1.3ms | ActiveRecord: 136.2ms | Allocations: 11687)

We add the custom labels as attributes to the RegisterItem, so they feel like first class citizens, allowing for things like @register_item.income_account, which is stored as register_items.meta0 in the db, assuming the register.meta is {"meta0": "income_account"}. This would also allow renaming the attributes by changing the name of a single row, while leaving the large table of items untouched.

Amount column type We had initially decided amounts would be stored as integers and carry a multiplier value to restore to the user's value, but after working on the reports, it became clear this would be burden to interact with over time. Opted for a much simpler decimal, which translates to Postgres numeric, with no precision or scale set. If there are drawbacks to this approach, I don't know what they are yet.

I don't see a way to enforce precision at the DB level, given that the table will be shared for a variety of currencies and other data of arbitrary precision, so decimal feels very well suited.