Database Architecture

PostgreSQL on Supabase. 123 tables across 190 migration files. Full index: Database Tables.

Rules

• Never modify an existing migration file — always create a new one.
• Migration naming: YYYYMMDD_NNN_descriptive_name.sql.
• All new RPCs: SECURITY DEFINER, SET search_path = public.
• REVOKE ALL ON FUNCTION … FROM PUBLIC before GRANT EXECUTE TO authenticated.
• RLS required on every user-facing table (defense-in-depth, even with EF enforcement).
• Append-only tables (e.g. trade_plan_updates) have no UPDATE/DELETE RLS policy.
• row_to_json(table.*) in RPCs auto-includes new columns without RPC changes.

Access model

• Reads flow through RPCs (defined in migrations, called from hooks).
• Writes flow through edge functions using the service-role client.
• RLS is defense-in-depth; the EF is the primary enforcement point. Both are always required.
• The client never issues supabase.from() — see Data Access Strategy.

Naming conventions

Entity	Convention	Example
Tables	snake_case	trade_planner_plans
RPCs	get_{entity} / get_{entity}_list / get_{feature}_summary	get_nifty50_market_mood
Migrations	YYYYMMDD_NNN_name.sql	20260601_133_index_radar_rpc.sql

Testing

pgTAP tests run against local Supabase via npm run test:db. Cover RPC projections (no leaking sensitive columns), RLS isolation (user A cannot read user B's rows), and grants.

The table reference is generated from migrations by docs:gen: Database Tables.