Backend and Database

Backend API and Database

The sAIve backend is built primarily with FastAPI (Python 3.10+) and uses SQLite for local, persistent storage. It operates locally on the user's machine, ensuring complete data privacy. The server is packaged via PyInstaller so it doesn't require users to install Python manually.

---

Architecture Overview

• Framework: FastAPI
• ASGI Server: Uvicorn
• Database: SQLite
• Validation: Pydantic models
• Event Bus: Custom Server-Sent Events (SSE) bus for real-time frontend updates
• Security: Input validation via Pydantic, output HTML sanitization via bleach

Dynamic Port Discovery

When launched by Electron in production, the backend binds to an open local port and prints PORT:<number> to stdout. Electron reads this and dynamically configures the frontend API client, ensuring no port clashes with other local services.

---

Database Schema

The SQLite database (database.db) is stored in the OS-specific %APPDATA% / ~/.config/ directory.

users

Column	Type	Description
id	INTEGER	Primary Key
name	TEXT	User's name
net_worth	REAL	Current real-time net worth

user_assets

Tracks monthly financial states for historical charting and charting overflow.

Column	Type	Description
id	INTEGER	Primary Key
user_id	INTEGER	Foreign Key -> users.id
year	INTEGER	Year (e.g., 2026)
month	INTEGER	Month (1-12)
TIncome	REAL	Total Income for the month
TExpense	REAL	Total Expense for the month
TSavings	REAL	Savings for the month
NetWorth	REAL	Snapshot of Net Worth

transactions

Records every logged financial movement.

Column	Type	Description
id	INTEGER	Primary Key
user_id	INTEGER	Foreign Key -> users.id
date	DATE	Format YYYY-MM-DD
amount	REAL	Transaction amount
category	TEXT	Whitelisted category
recipient	TEXT	Merchant / Source
type	TEXT	income or expense

recurring_transactions

Handles automated subscription and bill logging.

Column	Type	Description
id	INTEGER	Primary Key
user_id	INTEGER	Foreign Key -> users.id
amount	REAL	Transaction amount
category	TEXT	Categories (e.g. Subscriptions, Bills)
recipient	TEXT	Merchant / Source
type	TEXT	income or expense
interval	TEXT	daily, weekly, monthly, yearly
start_date	DATE	When the recurring transaction began
next_date	DATE	When the next instance is due

budgets

Category spending caps set by the user.

Column	Type	Description
id	INTEGER	Primary Key
user_id	INTEGER	Foreign Key -> users.id
category	TEXT	E.g. Food, Transportation
amount	REAL	The budget limit

(Note: Enforces UNIQUE(user_id, category) to prevent duplicate budgets)

notifications

Alerts pushed to the user (e.g., "Subscription automatically logged").

Column	Type	Description
id	INTEGER	Primary Key
user_id	INTEGER	Foreign Key -> users.id
title	TEXT	Alert Title
message	TEXT	Alert Description
date	DATETIME	Timestamp
is_read	BOOLEAN	Read flag (Default 0)
type	TEXT	E.g., system

---

Security and Validation Constraints

Data enters the API through strict Pydantic models (models.py).

1. Category Whitelist: Transactions must fall into strict categories: Housing, Food, Transportation, Subscriptions, Bills, Income, Other.
2. Amount Constraints: Must be > 0 and <= 1,000,000,000.0.
3. Date Constraints: Strict YYYY-MM-DD format between 1900-01-01 and the current system date. No future dates.
4. Recipient Sanitization: The recipient string is stripped of leading/trailing whitespace, constrained to 200 characters, and pumped through bleach.clean(..., tags=[]) to completely remove HTML/script payloads, effectively neutralizing stored XSS.

---

Event-Driven Architecture (SSE)

Instead of the frontend constantly polling the backend or attempting complex optimistic cache updates, sAIve uses a lightweight Server-Sent Events (SSE) architecture.

How it works

1. Frontend connects to GET /events/{user_id} on mount.
2. The endpoint registers the client's asyncio.Queue in sse_bus.py.
3. An active keepalive (: ping) is sent every 20 seconds.
4. When a mutation occurs (e.g. POST /transactions/), the backend calls sse_bus.emit_event("transactions_changed", user_id).
5. The frontend receives the event and instantly invalidates the relevant React Query cache, triggering a silent, targeted refetch of just the affected data.

Available Events

• transactions_changed
• recurring_changed
• budgets_changed
• notifications_changed

---

MCP Server Integration

sAIve embeds a local Model Context Protocol (MCP) server via mcp.server.fastmcp.FastMCP. Mounted at /mcp (using sse_app()), it allows the frontend's local AI Engine to query structured financial data and execute actions using defined functions rather than scraping data.

Available MCP Tools

• get_net_worth(user_id: int)
• log_transaction(user_id, amount, tx_type, category, recipient, date)
• batch_log_transactions(user_id, transactions)
• get_expense_categories(user_id, year, month)
• update_budget(user_id, category, amount)
• get_user_info(user_id)
• get_transactions(user_id, year, month)
• delete_transaction(transaction_id, user_id)
• get_budgets(user_id)
• get_monthly_summary(user_id, year, month)
• get_financial_history(user_id)
• get_recurring_transactions(user_id)
• create_recurring_transaction(...)
• update_recurring_transaction(...)
• delete_recurring_transaction(...)
• get_notifications(user_id)

---

Background Processing

A permanent async task (process_recurring_transactions_loop) runs in main.py whenever the server is active.

• Every 30 seconds, it queries recurring_transactions.
• If next_date <= current_date, it:
  1. Inserts a standard ledger Transaction.
  2. Spawns a Notification informing the user.
  3. Advances next_date based on the interval (daily, weekly, monthly, yearly).
  4. Triggers sse_bus.emit_event so the UI instantly updates without a reload.