WeSpend - Data Model
All money is stored as integer paise. Every domain row carries household_id so Row Level Security can scope access to the member's household. SQL lives in supabase/migrations/.
Entities
erDiagram
households ||--o{ members : has
households ||--o{ budgets : has
households ||--o{ categories : has
households ||--o{ expenses : has
households ||--o{ settlements : has
households ||--o{ invites : has
households ||--o{ notifications : has
households ||--o{ device_push_tokens : has
households ||--|| meal_card_settings : has
households ||--|| bill_settings : has
households ||--o{ bill_categories : has
households ||--o{ bill_entries : has
members ||--o{ expenses : "spent_by / created_by"
members ||--o{ invites : "for"Core budget tables
| Table | Key columns | Notes |
|---|---|---|
households | id, name, owner_id | owner_id points at the founding member |
members | id, household_id, auth_user_id?, is_owner, is_finance_manager, is_active | auth_user_id null = name-only profile. Partial unique index enforces exactly one finance manager per household. is_active false = archived (soft delete; history preserved) |
budgets | household_id, month, amount_paise, daily_limit | daily_limit frozen at write time. Unique per (household, month) |
categories | household_id, name, is_default | Seeded with defaults on household creation |
expenses | household_id, budget_month, amount_paise, category, spent_by, paid_from, date, source, created_by, updated_at | paid_from in (pot, own-pocket, card). card spends belong to the meal card and are excluded from budget/settlement. updated_at drives sync conflict resolution |
settlements | household_id, budget_month, week_index, entries (jsonb), status | Unique per (household, month, week) |
invites | household_id, member_id, code, expires_at, accepted_at | Invite-link onboarding |
Meal card, bills, and notifications
| Table | Key columns | Notes |
|---|---|---|
meal_card_settings | household_id (PK), monthly_amount_paise, warning_threshold_paise, start_month, enabled | One row per household. Prepaid card balance carries over month to month; the ledger is derived from top-ups and paid_from='card' expenses |
bill_settings | household_id (PK), enabled | Off by default. Toggles the Bills tab |
bill_categories | id, household_id, name, is_default | Separate category set from budget categories; seeded on household creation |
bill_entries | id, household_id, amount_paise, category, entry_date, entry_month, note, created_by | Standalone tracker rows; never touch budget, settlement, or the meal card |
notifications | id, household_id, type, title, body, route, actor_member_id, read_by (uuid[]) | In-app notification feed; read_by tracks per-member read state |
device_push_tokens | id, household_id, member_id, expo_push_token | Expo push tokens per device for cross-device push |
Indexes
members (household_id),members (auth_user_id)budgets (household_id, month)expenses (household_id, budget_month),expenses (household_id, date),expenses (spent_by)settlements (household_id, budget_month)invites (code)
Row Level Security
is_household_member(household_id) returns whether the current auth.uid() belongs to that household. Every table's policy uses it for both USING and WITH CHECK. Bootstrapping (create_household, accept_invite) runs as security definer because the caller is not yet a member when those rows are created.
Realtime
Publication supabase_realtime includes budgets, expenses, settlements, members, categories, bill_settings, bill_categories, and bill_entries. meal_card_settings, notifications, and device_push_tokens are not published; those sync on refetch/foreground rather than live.
Scheduled work
The weekly settlement reminder runs server-side: a Postgres function (migration 0007) is scheduled by pg_cron (migrations 0008-0009) to POST through an Edge Function gateway, authenticated by a dedicated CRON_SECRET. See Supabase backend.