I rent out the secondary unit (aka inlaw or granny unit) in my backyard and a spare bedroom and was looking for a cheap web service make the tracking all of the accounting relatively easy. But they all have 100 more features than I need. I looked at spreadsheet templates, but all required manually calculations of depreciation and calculating space size percentages required for divvying up things like insurance. The thought of adding that to the existing spreadsheets filled me with dread. I wondered how quickly I could build a Retool app to do this for me? So I embarked on a speed(ish) run to build it. Took me about a day (spread over 2-1/2 days). Here is the result:
First, I spun up a new project on Supabase, linked it to pgAdmin and built my best guess at a database structure.
Then I built an app to manage the utility tables with full CRUD on each table.
Properties and Units have modal forms to add a record.
Next up was a way to add/view the transactions
I have optional filtering by month as well as year and unit selections. I used my favorite filtering pattern: Get all records from the database using a normal SQL select query, then use a filtering JSON SQL query to fill the table. This saves round trips to the database and works great if your row count is in the thousands or lower. 5 figure counts leave you stuck with hitting the database each time. The available functions in SQL for JSON SQL is a bit limiting so you have to get creative. Here is the query linked to the table in which I had to treat the date as a string to get the results I wanted.:
select * from {{qryTransactionsSelect.data}}
where ({{selYear.value || 'All'}} = 'All' OR SUBSTRING(date_entered,1,4) = {{selYear.value}})
AND ({{bgMonths.value || '00'}} = '00' OR SUBSTRING(date_entered,6,2) = {{bgMonths.value}})
AND unit_id={{selUnit.value}}
I use a modal to enter new records:
If the transaction is one that needs to be depreciated (furniture, major improvements, etc.) the submit button will tell you that will happen. It will then divide the entry by number of years to depreciate and add those entries to future years. There is also a Calc Ratio button which gets enabled when an expense, like insurance, shows up that needs to be shared with the main house.
I also created a dashboard which is still a work in progress.
I spent less than half the time doing (and changing my mind and redoing) all of the UI stuff, CRUD and queries. The rest was evolving the database, getting the filtering logic working with AlaSql (the library that Query JSON with SQL uses), business logic - depreciation, ratio calcs and such - and distraction debt.
Not bad for a day's work. Doing this in MS Access would have taken a few hours less, but doing it in React or Vue would have taken me at least a day more.
It still needs some features:
- Making the Dashboard more useful.
- Adding a way to automatically add utilities costs
- Calc and download data for tax forms.
If anyone would like a copy of the apps and database structure to fork this, let me know.
It would be sick to make an app that allowed users to save properties and perform calculations while making their choice in purchasing rental properties