Transaction Filtering & Server Pagination

[jacobrobertsbaca]

Filter Transactions & Server Pagination

This issue is really two in one. One is a client side issue, and the other is a server/client issue, but they belong to broader overall app tweak. I'm not sure which one of these should be implemented first.

This should be implemented after #27 so that there's a better caching system in place to make pagination easier.

Filter Transactions

Add a filter sidebar to allow filtering transactions by

• Date Ranges
• Amount Ranges (i.e. min and max)
• Within budget
• Within budget + category

This filtering should also be exposed via search parameters. E.g. navigating to /transactions?budget=xyz automatically filters to transactions within budget xyz. Then, we can add links from the Budgets page such that clicking on a category in a budget allows us to view all transactions within that category. This could manifest along multiple levels of granularity:

• Show all transactions in a budget
• Show all transactions in a category
• Show all transactions for one period of a category

Server Pagination

Paginate results on the server. Right now, Supabase only returns 1000 rows maximum by default (and for good reasons). Once a user has more than 1000, we won't be able to retrieve/show all of their data. We can overcome this by baking in pagination into the query API for transactions.

Realistically, this will mean modifying the GET /api/transactions endpoint to support a search model. The request would include data about what results to return, evaluated in the following order

• What to filter for (e.g. filtering, as defined above)
  • Effectively adds additional SQL constraints to request for each filter
• What to search for (e.g. the existing search bar)
  • Effectively adds search constraint to SQL request. This page will come in handy to implement.
• How to sort results (e.g. existing column sorting)
  • Effectively an ORDER BY plus ASC/DESC clause
• What page to show (e.g. existing pagination UI)
  • Effectively a SQL LIMIT plus OFFSET clause

When caching is implemented, this search model will become part of the cache key, so pagination shouldn't be too hard to reconcile with caches.

In addition, this will need to wired up to the UI. These docs will be helpful.

[jacobrobertsbaca]

Search Model

A search model defines the criteria for and ordering of returned rows. It does not specify anything about the pagination of those rows (i.e. which chunk of rows will be fetched next). A search model can be defined in terms of a filter model and a sort model.

Filter Model

This specifies which rows are included, but not their order. It is specified by

• A search string
• For each column, a column filter
  • Could be arbitrarily complex
  • Examples: Range filter, starts with, contains, etc.
  • More complex: boolean expressions combining filters together

Sorting Model

This specifies the order of filtered rows. For simplicity and ease of implementing pagination, only one column can be filtered at a time. So the sorting model is specified by

• undefined (use default ordering, e.g. starred transactions first, then sort by date)
• A column sorting
  • Column name to sort
  • Direction to sort items

[jacobrobertsbaca]

https://minimals.cc/dashboard/job has a really nice filtering view