Database Schema

PostgreSQL on Supabase. 20+ tables with Row-Level Security, triggers, functions, and vector search.

1 Entity Relationships

2 Core Tables

users

CORE

Column	Type	Notes
id	UUID PK	Synced from Supabase Auth
email	TEXT NOT NULL
name	TEXT
default_wallet_id	UUID	FK → wallets
default_currency	VARCHAR(3)	Default: RUB
is_premium	BOOLEAN	Default: false
subscription_type	VARCHAR(20)	free / monthly / yearly / lifetime
chat_retention_days	INTEGER	0 = unlimited
sync_version	INTEGER	For multi-device conflict resolution
created_at, updated_at	TIMESTAMPTZ

wallets

CORE

Column	Type	Notes
id	UUID PK
user_id	UUID NOT NULL	FK → users (CASCADE)
name	VARCHAR(100)
currency	VARCHAR(3)	Default: RUB
balance	NUMERIC	Cached, updated by triggers
icon	TEXT	Asset icon name
color	VARCHAR(7)	Hex color
is_archived	BOOLEAN

transactions

CORE

Column	Type	Notes
id	UUID PK
user_id	UUID NOT NULL	FK → users
wallet_id	UUID	FK → wallets (SET NULL on delete)
category_id	UUID	FK → categories
merchant_id	UUID	FK → merchants
type	VARCHAR(10)	income / expense / transfer
amount	NUMERIC NOT NULL	Always positive
currency	VARCHAR(3)
exchange_rate	NUMERIC	NULL if same currency as wallet
description	TEXT
date	TIMESTAMPTZ
parent_id	UUID	FK → transactions (split)
is_split	BOOLEAN
contact_name	VARCHAR(100)	For debt transactions
debt_due_date	DATE

7 indexes: user, wallet, merchant, date DESC, recurrence, parent, split, orphaned

contacts

CORE

Column	Type	Notes
id	UUID PK
user_id	UUID NOT NULL	FK → users
name	VARCHAR(100)
phone, email	VARCHAR
cached_balance	NUMERIC	+ = they owe me, - = I owe them
due_date	DATE	When debt should be repaid
reminder_days	INTEGER	Default: 3
is_archived	BOOLEAN

Debt tracking uses transactions with contact_name field linked to contacts

3 Financial Tables

💡

Architecture Notes Budgets are stored as fields on the categories table (budget_amount, budget_period, budget_currency). No separate budgets table. current_spent is computed at query time from transactions.
Goals are implemented as wallets with type='goal' (target_amount, current_amount fields).
Debts are tracked via contacts table + transactions with contact_name field.

recurring_templates

CORE

Column	Type	Notes
id	UUID PK
user_id	UUID NOT NULL	FK → users (CASCADE)
wallet_id	UUID	FK → wallets (CASCADE)
category_id	UUID	FK → categories (SET NULL)
amount	NUMERIC NOT NULL	CHECK > 0
currency	VARCHAR(3)	Default: RUB
description	TEXT
frequency	VARCHAR(20) NOT NULL	daily / weekly / monthly / yearly
interval_value	INTEGER	Default: 1. E.g. 2 = every 2 weeks
start_date	DATE	When the recurring schedule started
next_run_date	DATE NOT NULL	Processed by scheduler/cron function
last_run_date	DATE
is_active	BOOLEAN	Default: true
cancelled_at	TIMESTAMPTZ	Set when subscription is cancelled

All recurring templates create expense transactions. Processed by process_recurring_templates() DB function.

mcc_categories

SUPPORT

Column	Type	Notes
id	UUID PK
mcc	VARCHAR(4) UNIQUE	Merchant Category Code
name_en, name_ru	VARCHAR(200)	Localized names
default_category_slug	VARCHAR(50)	Maps to categories.slug
category_type	VARCHAR(10)	income / expense
group_name	VARCHAR(100)	MCC group (e.g. "Groceries")
is_active	BOOLEAN	Default: true

Global table (no user_id). Maps MCC codes to default categories for auto-categorization.

merchant_patterns

SUPPORT

Column	Type	Notes
id	UUID PK
pattern	VARCHAR(200) NOT NULL	Match pattern text
pattern_type	VARCHAR(20)	exact / prefix / contains / regex
mcc	VARCHAR(4)	Associated MCC code
merchant_name	VARCHAR(200)	Resolved merchant name
priority	INTEGER	Higher = matched first
is_active	BOOLEAN	Default: true

Global table (no user_id). Used for automatic merchant detection from transaction descriptions.

merchants

SUPPORT

Column	Type	Notes
id	UUID PK
user_id	UUID NOT NULL
name	VARCHAR(200)	Normalized display name. UNIQUE(user_id, name)
raw_patterns	TEXT[]	Original descriptions that matched
mcc	VARCHAR(4)	Merchant category code
category_id	UUID	FK → categories
transaction_count	INTEGER	Usage frequency

4 AI & Notification Tables

ai_trigger_configs

Column	Type	Notes
user_id	UUID UNIQUE	1:1 with users
enabled	BOOLEAN
push_enabled	BOOLEAN
quiet_hours_start/end	TIME	Default: 23:00 - 08:00
triggers	JSONB	Per-trigger settings: { limit_warning: { enabled, threshold } }

ai_notifications

Column	Type	Notes
trigger_type	VARCHAR(50)	limit_warning, goal_milestone, salary_detected, etc.
title, message	VARCHAR / TEXT
status	VARCHAR(20)	pending / seen / accepted / dismissed / snoozed
priority	VARCHAR(10)	low / normal / high / urgent
related_entity_type/id	VARCHAR / UUID	budget, goal, contact, recurring
action	JSONB	{ type, payload, label }

conversation_memories

AI / RAG

Column	Type	Notes
summary	TEXT	Summarized conversation
embedding	vector(1536)	text-embedding-3-small. HNSW index.
topics	TEXT[]
message_count	INTEGER

Requires CREATE EXTENSION IF NOT EXISTS vector;

5 Supporting Tables

📋

Other tables chat_messages, action_history, pending_actions, reminders, notification_milestones, device_tokens, user_devices, entity_aliases, financial_health_history, currency_rates, automation_rules, rule_applications, suggestions, execution_plans, docs_feedback, chat_feedback. Full definitions in Server/sql/schema.sql.

6 Database Functions & Triggers

update_wallet_balance(wallet_id, amount)

Atomically updates wallet balance: balance = balance + amount. Used after transaction creation.

transfer_between_wallets(from, to, amount, user_id, desc)

Atomic transfer with row locking (FOR UPDATE). Validates balance, creates transfer transaction, returns new balances.

process_recurring_templates()

Cron function: creates expense transactions from active recurring templates where next_run_date <= today. Updates wallet balances and advances next run date using interval_value.

update_merchant_usage(merchant_id, raw_pattern)

Increments merchant transaction_count, updates last_used_at, and appends new patterns to raw_patterns array.

cleanup_old_action_history() TRIGGER

After INSERT on action_history: keeps only last 50 records per user, deletes older ones.

delete_user_data(user_id)

GDPR compliance: atomically deletes all user data from all tables in correct FK order (including recurring_templates, contacts, entity_aliases, etc.). Returns deletion report.

💡

Budget tracking Budget current_spent is computed at query time by summing transactions for the budget period. No database trigger or cron function is needed for budget tracking.

7 Row-Level Security

🔒

All tables have RLS enabled Every table uses the policy: auth.uid() = user_id. Users can only access their own data. Server uses service_role key which bypasses RLS for admin operations.

8 Field Naming Conventions

Database (snake_case)	Server TS (camelCase)	iOS Swift (camelCase)
budget_amount	budgetAmount	budgetAmount
current_spent	currentSpent	currentSpent
category_id	categoryId	categoryId
wallet_name	walletName	walletName
is_default	isDefault	isDefault
created_at	createdAt	createdAt
next_run_date	nextRunDate	nextRunDate