Is a personal expense dashboard viewer built using SQLPage. It assumes data is in a single table called expense in a sqlite3 database (see sample/migrations/01_create_schema.sql).
- A dynamic menu is built using the data available in the database to provide queries by various date ranges like recent months, quarters, financial years etc. By default, current month is shown. See
shell.sql. - Various data summarization for a given search/filter is available as tables and graphs.You can also choose to show individual records. See
search_results.sql. - A search form pre-filled with current filter is there at the bottom. It also has an option to multi-select categories of expenses to further filter down. You can also exclude selected categories. See
search_form.sql.
- Clone this repository
- Install
sqlpage.binsomewhere. - Run
sqlpage.bin -d ./sample. This should create an empty sqlpage.db and run migrations to create schema. - Navigate to http://localhost:8080/ - you should see an empty dashboard.
- Go to http://localhost:8080/csv*import.sql and load _sample/sample.csv*.
- Once it is done, you should have 10K records in the database. You can now explore the dasboard via menus.
- If you want to create a larger sample, see
sample/gensamplecsv.pydescription below.
- Create CSV file like given in the sample, with your own data. Either use the web interface for importing CSV as above or make your own that does not delete existing data if you prefer appending new ones periodically.
- Best is to create a new
personal.jsonconfig file that points to your ownpersonal.dbfile like below.{ "database_url": "sqlite://./personal.db?mode=rwc" }
You can then run using this database as
sqlpage.bin -c personal.json- index.sql - main container file. This points to the root of the rendered site. If query string has missing parameters, it is filled with default values before calling
shell.sql.- shell.sql - included to draw the menu items. It has SQL for generating various date ranges based on minimum and maximum available data in the
expensetable. It also includes calls to toggle menu items for dark mode and side menu.- toggle_menu.sql - sets a cookie when clicked and switch to side menu. On clicking again, cookie is removed. Presence of cookie is used to change default menu location and default icon.
- toggle_theme.sql - sets a cookie when clicked and switch to dark theme. On clicking again, cookie is removed. Action is like previous one.
- search_results.sql - runs a series of queries to build the dashboard based on the input parameters like date range, categories etc. Initially, these queries all had similar looking CTEs. I changed it to generate a temporary table that has filtered data and then onwards, that table is used for all queries. This serves two purposes (a) size of SQL code is less (b) temporary table acts as a cache. Since temporary tables are specific to sessions, it shouldn't collide with other parallel users. Also, I've hard coded Indian Rupee (₹) as the currency and formatted it using printf('₹%,.0f', amount) because Indian formatting is different from thousands/millions. SQLPage currently formats according to that convention even when currency is specified as Indian Rupee (INR).
- search_form.sql - has parameters pre-filled according to query string (or defaults in its absence). Changing these and filtering will reload the page with form parameters passed as query string.
- shell.sql - included to draw the menu items. It has SQL for generating various date ranges based on minimum and maximum available data in the
- csv_import.sql - has a form to replace the
expensetable with a csv file. It calls csv_process.sql to load the csv uploaded into the table. - assets/ - favicon files
- sample/migrations/01_create_schema.sql - this runs on server startup and is used to create the schema - just one table
expense - sample/migrations/02_load_sample.sqlite3 - this is NOT run automatically because it does not have .sql extension. It can be used to directly load csv file into database using sqlite3 cli - it is much faster than the web interface.
- sample/sample.csv - a generated sample file with 10K records
- sample/gensamplecsv.py - file that generates random sample data into sample.csv. It requires pandas and faker as dependencies and will overwrite sample.csv. So, better to copy the script somewhere else, edit it and then run it. You can use this to create larger or customized sample data to load. Dependencies are available in uv. If you install uv, you can simply run
uv run gensamplecsv.py.
- There is no security. It is supposed to be used by you on your local machine. Ensure there is a firewall!
- If you want security for such websites, say for internal use, my go to solution has been using nginx and oauth_proxy in the front.
- Some columns in the db are pre-computed and loaded, but not used at the moment. I added these for some future ideas I have.
- Favicon was generated using Grok 3, then removed background using remove bg and converted using favicon.ico.
- SQLite for a phenomenal database engine.
- SQLPage community for very useful discussions.