grocery-manager

Grocery Manager

This a database driven app - meaning it's all about the data and nothing but the data.

The database is assumed to be postgresql, but any modern relational database should work with some tweaks.

The following apps make the data useful:

• grocery_transactions.py - facilitate data entry with a TUI
• price_check.py - show price history graph per product (TUI)
• SQLPage - basic website to view price trends

Workflow

1. Grocery purchases are tracked by entering reciepts into the database using grocery_transactions.py or otherwise
2. That's it! So long as the data is in the database, you can do whatever you want with it.

The database itself is designed to be the first class UI.

This is because I wanted data integrity first and foremost, and I wanted to be able to start using the data without need for a UI (I hate UI development).

However, there are helper tools to ease the workflow.

grocery_transactions.py

This is a python + urwid TUI to help make data entry easy.

• New products can be added to the db without manually modifying the database
• Reciepts can be entered quickly using autocomplete based off existing data
• Items can be tagged with any number of free-form tags

Once done, the app can be exited by hitting ESC on the keyboard.

The result is a .txn file that can be commited to the database using grocery_transactions.sh

Not all features of the database are supported including (needs to be done manually for now):

• Creating new stores
• Adding new units

price_check.py

This is another python + urwid TUI to look up products to see their price history.

It's designed to be usable on mobile (I use termux and have a shortcut to ssh into a jumphost)

SQLPage

SQLPage is a lightweight webserver written in Rust and generates web pages from SQL.

Features the SQLPage grocery website provides:

• trending price data over all products (can be filtered) rendered with appexcharts.js
• product listings
• tag listings and usage counts

Technology stack

postgresql

The schema is designed to protect data from human error.

Stored procedures are supplied where CHECK constraints can not be used to prevent all invalid usage.

python

All the helper apps are written it python

psycopg

psycopg is used heavily.

No ORM is used in any apps.

Remember the database is designed to keep queries simple.

Queries are built up from SQL fragments in app. While postgresql is assumed, it should be straight forward to replace the db connection with another RBDMS db connecion and adjust query syntax where needed.

Some window functions are used it the TUI apps but only because it was convienient at the time. This is the most significant thing that may need reworking to interact with other RBDMS.

Some client side data transforms are done with pandas.

SQLPage

SQLPage provides web components out-of-the-box to generate beautiful web pages.

I make heavy use of the table, form and chart components.

The filter options are internally stored as JSON format and stored in a cookie to preserve the filter on page changes.

• I took this approach because it was not very elegant to use buttons that post forms for navigation
• The cookie only stores the user selections, so while there can be a lot of filter options (300+ products), only the selected options are stored in the cookie.

I'll revisit this later with aim to avoid using cookies entirely.

Get Started

Install Requirements

pip3 install -r requirements.txt

Usage

Enter receipt from New World (TUI)

python3 grocery_transactions.py 2023-06-02-NW.txn

Commit when done

# check all is looking good first
./grocery_transactions.sh -d 2023-06-02 < 2023-06-02-NW.txn

# then commit (supply -c)
./grocery_transactions.sh -d 2023-06-02 -c < 2023-06-02-NW.txn

Check prices (TUI)

python3 price_check.py

Check the web inerface: https://shandan.one/grocery/internal/apply.sql?title=Trend&apply=Apply&groups[]=Fish%2C+Meat%2C+Eggs