Rental Unit Accounting Application

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.

image

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:

image

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.

5 Likes

Hey Brad! this is amazing! Love the buttons to filter for each calander month - looks sleek :slight_smile:

Would love a copy!

:thinking: 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

Like performing the calcs here: Rental Property Calculator

but allowing users to save results and compare different Rental properties.

Would colab on this with you if interested :slight_smile:

I like the calculator you linked to. A house two doors down just sold for a nosebleed price and while I have not officially met them yet they look like they belong in Beverley Hills, not south San Jose. But that's the market here, now you gotta be a millionaire to buy a small fixer upper. I thought they might rent it out and was skeptical it would pencil out, this calculator confirms there is a 100% chance they lose their shirt trying based on the going rental rates for smaller houses.

That would be a neat little app. Honestly, I am not really interested in doing something like that right now, kinda slammed. But exploring how we might do it would be interesting and educational. I think I would do it as a public facing app (haven't done one of those yet, but have a couple on the docket). We would assign a GUID as an authentication proxy and email them a link so he get back to it when they wanted.

How would you do it?

1 Like

Hey Brad! This is great, I would love to fork and use this :slight_smile:

@ihugacownow,

Sure thing. Here is a link to a folder with the three app files and a sql file.

The sql file has what you need to build the db in Postgres. The table defs may not be in the correct order so you may need a little trial and error to see in which order they need to be created to keep the foreign key dependencies. It can be modified for other databases with a tad more work or you could probably make this work with Firebase or something with more than a tad more work.

I would love to know how you change/improve it.

2 Likes