tradestats: trading journal with isolation enforced in Postgres
CLIENT
tradestats (independent SaaS)
ROLE
Founder & full-stack
DURATION
2026 to present
TECH_STACK
PROJECT_URL
view_siteRLS
Isolation layer
15
Analytics functions
UTC
Stored, rendered local
Free
No subscription
CONTEXT
Most traders keep their journal in a spreadsheet, and most spreadsheets answer the wrong question. They record what a trade made in dollars, which says more about position size than about the decision. They rarely record the sizing that came first, and they never make it easy to ask whether the London session is carrying the account while the New York session quietly gives it back. tradestats started as my own tool for that, with a position size calculator on the front and a journal behind it, and grew into something worth opening to other traders.
THE CHALLENGE
The interesting problem was not building the features. It was that the application had been single-user for its entire life, and going multi-user is not a login screen. The Supabase anon key ships inside the JavaScript bundle, so any check written in React is advisory. Every table, every policy, and every reporting function had to be audited on the assumption that a signed-in stranger would query the database directly. The audit found two tables with row-level security never enabled and nine analytics functions running as security definer, reading trades with no owner filter. In a single-user world both are harmless. One signup later they are a data breach.
- Two tables were readable by anyone holding the public key
- Nine reporting functions bypassed policies by design
- Shared reference rows needed to stay shared, not be claimed by the first account
- Existing history had to survive the migration intact
ARCHITECTURE & APPROACH
React 19 and Vite on the front, Supabase behind it for Postgres, auth, storage, and realtime. There is no API tier of my own, which means there is no API tier to bypass: authorisation is a row-level security policy, and the policy is the only place the question gets answered. Google OAuth handles sign-in so no password ever reaches the app. Timestamps are stored as absolute instants and rendered in the timezone on the profile, while calendar dates are formatted verbatim so a date the user picked never slides by a day.
The database is the authorisation layer
Every table carries an owner column defaulting to the session user, with a policy filtering reads and writes to that owner. A hand-written query from the browser console returns exactly what the app returns, because the filter is applied below the query rather than inside the client.
Aggregates obey the same policies as rows
The analytics functions were switched from security definer to security invoker. They execute as the caller, so the row-level filter applies to the aggregate too. No function body changed. The privilege they ran under did, and that was the whole fix.
A null owner marks shared reference data
Built-in trading sessions and expense categories belong to everyone. Read policies allow your own rows plus the built-ins, write policies require ownership, so nobody can edit or delete the shared set while still being able to add their own.
One instant, many clocks
Instants are stored once as TIMESTAMPTZ and rendered in the chosen zone. Day and hour buckets in analytics are computed in that zone, so a trade opened at 02:00 local counts against the local day rather than the previous UTC one. Wall-clock inputs are converted through a resolver with a refinement pass for daylight-saving boundaries, covered by tests across five zones.
VERIFICATION
The isolation claim was not verified by re-reading the migrations. The deployed API was queried with nothing but the public anon key, the same key any visitor can lift out of the bundle, and the responses were compared against what an unauthenticated caller should be able to see. Any table returning rows to that request is a table with a hole in it. After the migrations landed, those requests come back empty, and the analytics functions reject the call rather than answering it.
OUTCOME
tradestats is live at tradestats.online, free, with no subscription and no broker connection. Any account can sign in with Google and gets its own isolated record: trades, strategies, expenses, patterns, and uploads, all scoped in the database rather than the interface. Account deletion is self-service and removes every owned row and file. The market-analysis surfaces stay gated to a single internal role at the policy layer, so they return nothing to a general account even if the route is reached directly.