tradestats was single-user for its whole first life. Opening it to signups meant auditing every table and every function before a second account existed. This is what the audit found.
Adding sign-in feels like the hard part. It is not. Sign-in without isolation is decoration: the anon key sits in the bundle, so a signed-in account can query anything the policies permit, and a single-user app permits everything. The work is not the login screen. It is proving that no read path, including reporting functions, can return a row it does not own.
One rule made the audit tractable. Authorisation is a database concern, and the client is never trusted to filter. That turns a review of every component into a review of every table and every function, which is a finite list you can walk to the end of.
Trades and strategies were created before the app had accounts, so nobody had ever enabled RLS on them. With the anon key in the bundle, that meant every trade in the database was readable by anyone who opened a console. In a single-user app that is harmless. One signup later it is a breach.
Enable RLS on both, add the owner column, and write the owner-only policy in the same migration that introduces accounts.
Every reporting function was written as security definer, which runs as the function owner and ignores row-level security. Each one read the trades table with no owner filter, because in a single-user world there was nothing to filter. As multi-user code they hand every account's P&L to any caller.
Flip the functions to security invoker. They run as the caller, the policies apply, and no function body needed editing.
The built-in trading sessions and expense categories are shared reference data, not user data. Assigning them to an owner would give the first account exclusive rights to the concept of "London session".
A null owner marks a shared built-in. Read policies allow your own rows plus the built-ins; write policies require ownership, so nobody can edit or delete the shared set.
Strategy names were unique across the whole table. Under multi-user that means the second account to create a strategy called "Breakout" gets an error caused by a stranger's data, and learns something about what that stranger named their strategy.
Scope the unique index to the owner. Names are unique per account, and two accounts never collide.
Screenshots and receipts were stored under a path keyed by the row id. Storage policies can only reason about the path, so with no account segment there was nothing to scope a write or delete rule against.
New uploads are namespaced by account id, with policies matching that segment. Files written before the change keep their old path and are writable only by the original owner.
Adding an owner column to existing rows leaves them owned by nobody. Enforce the policy first and the data becomes invisible; backfill first and there is a window where the column is nullable and unenforced.
Accept the invisible window deliberately. Policies land first, existing rows go quiet, then a backfill claims them for the original account and the column becomes non-null. Documented so the blank screen between steps is expected rather than alarming.
Not by reading the code twice. The deployed API was queried with nothing but the public anon key, the same key any visitor can lift from the bundle, and the responses were compared against what an unauthenticated caller should be able to see. A table that returns rows to that request is a table with a hole in it. After the migrations, it returns [].