Dev’s Log: Getting started

This is officially my first post about exploring some of the application logic that will power the StackorSpend app.

As a recap, what we're aiming to build is a system that can take in transactions from a connected account and calculate your average buy price across all bitcoin purchased. It can then alert you to whether it's a good time to stack or spend, and what the discount/premium will be on your next action given the current price.

To achieve this, I was thinking that the best approach would be to import all the transactions from a connected service and to perform any required calculations before storing them in our own persistence layer. This has the advantage of being able to support multiple connected services in a single view.

Modeling the data

I decided to go with an SQL-based database because we're dealing with monetary transactions and having the ability to do ACID transactions could be useful. Also, I'm thinking it might be possible to run the app entirely on-device in the case of a mobile app if we go with an on-device SQLite database.

SQLite

My first step was to explore what could be a good schema for our data, and to lay it out using Typescript code. The goal here was to come up with a schema that's agnostic enough to likely be able to support multiple connected services while still having everything we'd need to fulfill the desired functionality of the app.

I also made some very specific decisions around modeling certain fields like the price field alongside a complementary offset column so that we can keep values to INTEGER types and not have to use any REAL values. Any derived values from the source data is expressed as TEXT e.g. the in-db calculated display_amount field.

The below image shows a screenshot of some of the code and some sample data read back out from the in-memory SQLite database.

Link: Replit project

This took me a little while to get used to again because I haven't worked with SQL and SQLite in quite a while. Part of this work involved me simply reacquainting myself with the features and functionality of SQLite.

I'm quite happy with where this has gotten to though, and it sets me up nicely for the next steps which would involve importing actual data and mapping it across to this new schema.

Iterating in spreadsheets

Alongside simply modeling my transactions data schema, I also started exploring what some of the calculations would look like to calculate the profit/loss and current stack cost price for each new transaction.

To do this, I simply laid out some sample data in a Google Sheet and started playing around with different calculated fields for the values I would be interest in. The goal here was to identify the calculated fields that would be necessary for the app and to see how simple I could get the calculations down to potentially also be able to do them right in SQL.

The tricky thing with this exercise is that there are a few different ways to go about calculating a wallet's average cost price and the effect of different types of new transactions on it. This exercise would also be one in choosing a single sensible approach to doing which can then inform the UX around what we would like to communicate about relative wallet state against changing BTC prices and new transactions.

Below is a screenshot of what this is starting to look like with a link to the Google Sheet I've been exploring some of this in.

Link: Google Sheet

There is another element too where state can change if we retroactively change the price of specific transactions (in the case of a move type transaction explained in the first spec of this project). This is also something that we will eventually have to accommodate for, ideally also within SQL logic, but this may not be necessary in the first iteration of the app.

Ultimate goal

I would like to sketch out as much of the application logic and persistence upfront so that when it comes time to design the actual mobile app, the work will be more focused on creating visual components and less on figuring out application logic. This gives us a nice separation of concerns for our development tasks in the upcoming weeks.