Expense tracker

An expense panel that records spend per currency and shows a total and category breakdown for every currency in use.

The problem

Context: An internal panel for recording company expenses in any of twelve currencies, with categories, receipts and an analytics page that shows the total and a category breakdown for every currency in use. Admins manage users, categories and currencies; editors record expenses.
Constraints: There is no row-level security in the database, so every server action has to check permissions itself. Receipts are private financial documents and must never become public files. Amounts have to stay exact.
What was at stake: Adding amounts in different currencies together, or converting them at a guessed rate, produces totals nobody can reconcile. One missed permission check would let anyone read or change company spending, and a careless file path would leak receipts.

Totals kept per currency, in SQL
Two SQL functions do the analytics: one totals spend per currency, the other breaks a single currency down by category. No figure is ever added across currencies, so every number on the page reconciles.
Exact amounts with guard rails
Amounts are fixed-precision decimals that must be positive, with at most two decimal places and no future dates, checked both in the form and in the database. Currencies come from a reference table rather than free text.
Nothing in use can be deleted
A currency, category or user still referenced by a recorded expense cannot be removed; the database refuses and the panel explains why, so history never loses its labels.

Edge redirect plus a check in every action
An edge check keeps signed-out users out and non-admins away from admin pages. Every server action then re-reads the user's role from the database before acting, so a stale or tampered session cannot raise anyone's permissions.
Signed sessions and safe sign-in
Sessions are signed tokens in HTTP-only cookies, passwords are hashed with bcrypt, and the post-login redirect only accepts paths on the site. The first admin is created from the command line, not from a public page.

Receipts outside the public folder
Receipts are stored outside anything the web server publishes and served by one route that requires a session, confirms the file belongs to a recorded expense, and tells browsers never to cache it.
Uploads checked and paths contained
Only images up to 5 MB and PDFs up to 10 MB are accepted, each saved under a random name per expense. Every path is resolved against the storage root so it cannot escape it, and a failed save removes the uploaded file.

Soft deletes with a full audit trail
Expenses record who added, changed and deleted them and when; deleting hides a row instead of erasing it, and a partial index keeps the active list fast.
Parameterised SQL in one place
All queries go through one server-only connection pool with placeholders for every value, including the dynamically built filters, so user input is never stitched into SQL.

I’ll tell you the cleanest path forward, including if it’s “don’t build that.”