Personal finance aggregator: One storage tree per user - Part 10
~8 min read
Once identity-system was putting a user id on every request, one assumption baked into the app from post 4 became visibly wrong. Everything - the SQLite database, the OAuth refresh tokens, the fetched JSON - lived in one shared storage/ directory, because there had only ever been one person. The moment a second account could log in, they'd see the first account's banks.
This post is about how I fixed that, and why I picked the less clever of two obvious designs. It's three commits of real work and two small fixes after them.
Two ways to isolate users
The textbook answer is a user_id column on every table, with every query filtered by it. It's compact and it's what most tutorials show. Its failure mode is the problem: forget one WHERE user_id = ? - in a new endpoint, or a rarely-run report - and the app doesn't crash, it quietly returns someone else's data.
The alternative is physical isolation: each user gets their own directory tree and their own SQLite file, and the code resolves paths from the user id:
storage/
providers.db <- shared reference data (the bank list)
logs/
users/
{user_id}/
db/finance.db
secrets/tokens.json
inbound/transactions.json, accounts.json, connections.json, ...
The failure mode here is a wrong path, and a wrong path is loud: a missing file, an empty database, a failing test. The commit message for the main change puts it in one line - "a wrong path is a loud, obvious bug, where a forgotten WHERE clause is a silent cross-user data leak" - and that was the whole decision. For a tool holding people's bank data, I'll take the design whose mistakes announce themselves.
Step one: pull out the one thing that isn't per-user
A design like this has an immediate wrinkle: some data isn't per-user. TrueLayer's list of supported banks is shared reference data, fetched at server startup (_refresh_providers() in main.py) before any request - and so before any user - exists. It can't live in a per-user database.
So I split it out first, as a pure refactor with no behaviour change: connect() stopped touching the providers table, and a new connect_providers() owns a separate providers.db. Doing this as its own commit was a deliberate sequencing choice - the commit message calls it "step 1" of a plan. If the split broke something, I'd find out with the per-user change not yet in the picture. The one deploy-time side effect was documented in the message: the old providers rows inside finance.db become orphaned, harmlessly, because startup refetches the list into the new file.
Step two: paths become functions of a user
Then the main change. Every path that used to be a fixed property on Settings became a method that takes a user id:
def user_dir(self, user_id: str) -> Path:
return self.storage_dir / "users" / user_id
def db_path(self, user_id: str) -> Path:
return self.user_dir(user_id) / "db" / "finance.db"
def tokens_path(self, user_id: str) -> Path:
return self.user_dir(user_id) / "secrets" / "tokens.json"
Turning properties into methods is a small type-level trick with a big effect: you can no longer ask for "the database path" without saying whose. Every call site that used the old properties fails until it supplies a user.
The request path follows from that. get_db() now depends on current_user(), so each request opens that user's SQLite file:
def get_db(user: AuthenticatedUser = Depends(current_user)):
conn = storage.connect(get_settings().db_path(user.id))
...
And current_user() got stricter. Before, id defaulted to an empty string; now AuthenticatedUser.id has no default, and a request with an email header but no id is rejected. The reasoning is in a code comment: the id selects which user's files a request can read, "so a response that somehow carried an email but no id must not fall through to some default." An empty user id would otherwise resolve to storage/users/ itself.
The same commit also added an identity check to the TrueLayer /callback route, which hadn't had one. It had worked anyway, because the browser round-trip is gated by nginx like any other page - but with per-user token files, the route now needs to know whose token file to write.
The commit's confidence rests on one test, test_data_isolation.py. Its docstring says what it's for: it's "the one test that proves that property actually holds through the real dependency chain - get_db is deliberately NOT overridden here." Only the identity is faked (a current_user override that returns user A or user B); everything downstream, including which SQLite file gets opened, is the real code. It seeds a transaction and an account alias into user A's database, then asserts that user A sees them and user B gets an empty list. A test that stubbed out get_db as well would pass no matter how the paths were wired.
The nightly job has to find everyone
The old systemd unit ran one hardcoded fetch. With data per user, it had to iterate. deploy/fetch-all-users.sh discovers users by listing storage/users/*/:
for user_dir in "${USERS_DIR}"/*/; do
user_id="$(basename "${user_dir}")"
echo "=== Fetching for user '${user_id}' ==="
.venv/bin/python -m finance_system.providers.truelayer fetch "${user_id}"
fetch_status=$?
if [[ ${fetch_status} -ne 0 ]]; then
echo "fetch failed for '${user_id}' (exit ${fetch_status}) - skipping its SQLite load" >&2
status=1
continue
fi
.venv/bin/python -m finance_system.storage.sqlite "${user_id}" || status=1
done
exit "${status}"
Two properties are worth noting. A new user is picked up automatically - no edit to the script or the unit - because "who's a user" is just "who has a directory". And one user's failure doesn't stop the rest: it records status=1, skips that user's load step, and carries on, then exits non-zero at the end so systemd still reports the run as failed. That's the same "keep going, but don't hide it" posture fetch() already takes per bank. (The script has since been adjusted twice - a distinct exit code for "no banks linked", and the storage module's new entry point - both in post 17.)
Moving the existing data, once
There was one real user's data in the old flat layout, and the new code would look for it under storage/users/{id}/ - leaving it, in the commit's words, "simply invisible - not deleted, just not where the app is now looking." deploy/migrate-to-per-user-storage.sh moves it. The script is cautious in the ways a one-off script touching live data should be:
- It takes the target user id as a required argument, and tells you where to find it (
GET /api/auth/me, while logged in as the account that should own the data). - It refuses to run if the destination directory already exists, rather than merging into it.
- It
mvs rather than copies, so the header comment says to back upstorage/first. - It's explicitly not wired into any deploy: "run by hand on the server in the same deploy window as this change, not something to automate or run unattended against live data."
The follow-ups were mundane in the way deploy scripts usually are. One of them (fix remote setup user) set the service user to root in remote-setup.sh to match the units from earlier, and another (fix migrate script) replaced the script's relative cd with an absolute cd /opt/finance-system, since it's only ever run on that one server.
Making the split visible
The Debug page from the next post lists raw database tables. A last commit updated it to show the new reality: one "Global data" section (the shared providers table) and one "Your data" section (everything in that user's own file), as two instances of the same component filtering one API response - no backend change needed.
The per-user tree is also what made some later features simple. Hargreaves Lansdown credentials (post 14) live under secrets/ in the same tree, and per-user sync logs (post 17) sit under logs/providers/{user_id}/. Both just fell out of "everything about a user lives under one directory".
Previous: ← Handing authentication to identity-system · Next: Seeing everything in one place →