Every account. Every bill. Every income source. Every crypto holding. Every financial goal. Every net worth snapshot. One JSONB column. profiles.data_nexus.
It worked. That's the dangerous part.
I needed to ship. Not plan. Ship. JSONB lets you store anything. No migrations. No foreign keys. No schema design meetings with yourself at midnight where you're arguing both sides and losing. You have a user. The user has financial data. Throw it in a column. Parse it with Zod on the way out. Done.
For a solo operator prototyping, JSONB is the ultimate speedrun strat. You change the shape of your data by changing the Zod schema. Deploy. The old data still parses because defaults fill the gaps. No ALTER TABLE. No migration files. No rollback plan.
I stored portfolio accounts, crypto positions, bill categories, bill templates, monthly bill instances, income sources, financial goals, and net worth history inside one column on the profiles table. Months of finance features. One column.
And it worked... until it didn't.
You can't query across users. "Show me all users whose savings rate exceeds 40%." Impossible without pulling every profile, parsing the JSONB, and filtering in application code. Postgres can't index inside your blob.
You can't enforce referential integrity. A bill template references a category by ID. In JSONB, that ID is just a string. Delete the category, the template still points to a ghost. No cascade. No constraint. Just a bug waiting to ruin someone's day.
You can't do Row Level Security properly. RLS policies operate on rows in tables. When everything is one column on one row, your security policy is "all or nothing." Either the user can see their entire financial life or none of it.
And you can't do joins. Want bill instances with their template names and category types? In normalized tables, that's a three-table join. In JSONB, that's a JavaScript reduce with nested find calls that makes you hate yourself.
I hit all four walls. I don't know... maybe some people find a way to live with JSONB longer. Min/max the tradeoffs a bit more. But I was writing reduce chains that were harder to read than the actual SQL would've been, and that felt like a sign.
Time to normalize.
February 3rd. The migration.
9 tables. 57 functions. One day.
finance_settings -- user preferences, account type ordering. portfolio_accounts -- investment and cash accounts with balance tracking. crypto_holdings -- cryptocurrency positions linked to parent accounts. net_worth_snapshots -- monthly calculations with full breakdown. bill_categories -- user-defined groupings. bill_templates -- recurring bill definitions. bill_instances -- monthly tracking with paid status. income_sources -- income streams with frequency metadata. financial_goals -- savings and debt targets with progress.
Every table got RLS policies. ON DELETE CASCADE for referential integrity. Custom ENUMs for account_type, goal_type, goal_priority, income_source_type, income_frequency. Indexes on user_id + sort_order.
Then the data access layer. 57 CRUD functions in finance-data.server.ts. Get, create, update, delete, reorder. For every entity. Every function takes a userId parameter and filters by it -- defense-in-depth on top of RLS. If RLS fails (misconfigured policy, Supabase bug, cosmic ray, whatever), the application layer still won't let User A touch User B's data.
EGO alert... I say "defense-in-depth" like I'm running a bank. It's a personal finance tracker. But yeah, you don't secure financial data with one lock. Even if "financial data" is just my own bills and savings goals right now.
The migration had to work on the first run. Production data. Real accounts. Real bills. Real net worth history. No "drop and recreate." No "export to CSV and reimport." The migration function had to extract data from the JSONB blob and insert it into the right tables, preserving relationships, sort orders, and IDs.
migrate_finance_data(). One function. Idempotent -- ON CONFLICT DO NOTHING on every insert so you can run it twice without duplicating data. Handles missing fields. Handles null values. Handles the weird shapes that accumulate when you've been shoving data into JSONB for months without strict schema enforcement at write time.
I wrote it to run once. I wrote it to survive being run again. Both mattered.
The same file size discipline that split portfolio.tsx from 1,896 lines to 782 meant each route was small enough to rewire in one pass. Six routes touched on the application side. nexus.tsx, budget.tsx, max-fi.tsx, finance/_index.tsx, _index.tsx, finance.settings.ts. Every loader rewritten to call the new CRUD functions instead of parsing JSONB. Every action file updated: account.actions.ts (5 calls), budget.actions.ts (5 calls), goal.actions.ts (3 calls), income.actions.ts (3 calls), snapshot.actions.ts (1 call).
All UPDATE and DELETE operations now require userId. Not just for RLS. For the application layer check. .eq("user_id", userId) on every mutation. Belt and suspenders.
Regardless... end of day. 523 tests passing. Typecheck clean. Build clean. npm run deploy ready.
The JSONB column still exists. I didn't delete it. It's the backup. If the migration corrupted something, the original data is still there in profiles.data_nexus, unchanged. Delete it after a validation period. Not before. I'm not that reckless.
...okay I'm a little reckless. I did this entire migration in one day. But yeah, the JSONB column stays until I'm sure.
JSONB bought me speed when speed was the only thing that mattered. I was prototyping. Iterating daily. Changing the shape of financial data every session. JSONB let me do that without friction. It was the right call.
Normalized tables buy me everything else. Queries that work. Security that layers. Integrity that the database enforces. Joins that don't make me write a parser.
The mistake isn't starting with JSONB. The mistake is staying with JSONB after you've found the shape of your data. I stored everything in one column until I knew what "everything" was. Then I gave each thing its own table.
9 tables. 57 functions. 523 passing tests. New game+ on the data layer. The same architecture that let me replace a payment provider in one day made this possible -- small files, clear boundaries, swap the source and keep the shape.