Database
Where the data lives
Overview
The database is a Postgres instance on Supabase. There are 10 tables covering portfolios, assets, transactions, valuations, savings transactions, alerts, push tokens, notification logs, user preferences, and sync metadata. Every single table has RLS enabled, so a user can only ever touch their own data. The schema supports multiple asset types through a flexible type_data JSONB column on the assets table, which means adding new asset types does not require a migration.
How it works
The portfolios table is the top level entity. Each portfolio has a name, a client_id for offline sync, and a version number for optimistic locking.
Assets belong to a portfolio via portfolio_id. The type column is an enum: stock, crypto, etf, bond, commodity, cash, forex, fixed_income, real_estate, savings. The is_manual flag distinguishes assets with live prices from ones users value themselves. Type specific data (maturity date, APR, estimated value) lives in the type_data JSONB column.
Transactions record buy, sell, dividend, split, and transfer events for auto priced assets. Each transaction tracks quantity, price per unit, total amount, fee, date, platform, and currency. Valuations and savings_transactions serve the same purpose for manual and savings assets respectively.
Alerts store price watch conditions. The alert_type column is an enum: price_above, price_below, percent_change, news. When an alert triggers, is_triggered flips to true and triggered_at gets set. The check_alerts edge function handles evaluation.
The user_preferences table stores notification settings: daily digest toggle, preferred digest hour (0 to 23), timezone, alert notifications toggle, and maturity reminder days as an integer array. Push tokens live in a separate push_tokens table with platform (ios/android) and is_active for managing stale tokens.
sync_metadata tracks the last successful sync timestamp per user, which the SyncService uses to do delta pulls instead of full refreshes.
Key decisions
JSONB type_data instead of separate tables per asset type
Bonds need a maturity date. Savings accounts need an APR. Real estate needs an estimated value. We could have made a table for each, but that means joins everywhere and a migration every time we add an asset type. Instead, the assets table has a type_data JSONB column that stores whatever extra fields that asset type needs. The tradeoff is less strict typing at the database level, but the TypeScript types on the client keep things safe.
Soft deletes everywhere
Every table has a deleted_at column instead of actually removing rows. This matters for sync. When device A deletes a portfolio and device B syncs later, device B needs to know that the deletion happened. With hard deletes the row would just be gone and device B would have no way to tell the difference between "deleted" and "never existed". Soft deletes make that obvious.
client_id for offline first sync
Every record gets a client_id on creation. The unique constraint is (user_id, client_id), so if the app retries an insert that already went through, the upsert just updates the existing row instead of creating a duplicate. This is what makes the whole offline first architecture work reliably.
RLS over application level filtering
We let Postgres enforce data isolation instead of doing it in code. Every query automatically gets filtered by user_id from the JWT. This means even if there is a bug in the app logic, a user physically cannot access another user's data. The database itself prevents it.