Spaces:
Build error
Build error
BankBot AI — Entity Relationship Diagram
ER Diagram (Text Notation)
┌─────────────────────────────────────────────────────────────────────┐
│ USERS │
│ PK id VARCHAR UUID │
│ email VARCHAR UNIQUE NOT NULL │
│ password_hash VARCHAR NOT NULL (bcrypt, rounds=12) │
│ profile_data JSON {name, phone, avatar, plan} │
│ financial_personality VARCHAR (Saver/Investor/Balanced/...) │
│ ai_personalization_settings JSON │
│ created_at TIMESTAMP │
│ updated_at TIMESTAMP │
└──────────────────────────────┬──────────────────────────────────────┘
│ 1
┌────────────────────┼────────────────────────────┐
│ N │ N │ N
┌─────────▼──────────┐ ┌──────▼──────────────┐ ┌──────────▼──────────┐
│ ACCOUNTS │ │ SUBSCRIPTIONS │ │ GOALS │
│ PK id UUID │ │ PK id UUID │ │ PK id UUID │
│ FK user_id │ │ FK user_id │ │ FK user_id │
│ type VARCHAR │ │ merchant VARCHAR │ │ title VARCHAR │
│ (checking/ │ │ amount FLOAT │ │ target_amount │
│ savings/ │ │ billing_cycle │ │ current_amount │
│ investment) │ │ active BOOLEAN │ │ target_date │
│ balance FLOAT │ │ ai_usage_ │ │ ai_generated_ │
│ currency VARCHAR│ │ detection JSON │ │ plan JSON │
│ status VARCHAR │ └─────────────────────-─┘ └──────────────────-──┘
└─────────┬───────────┘
│ 1
│ N
┌─────────▼──────────────────────────────────────────────────────────┐
│ TRANSACTIONS │
│ PK id VARCHAR UUID │
│ FK account_id → ACCOUNTS.id │
│ amount FLOAT NOT NULL │
│ type VARCHAR (credit / debit) │
│ category VARCHAR (Food/Shopping/Income/...) │
│ merchant VARCHAR │
│ timestamp TIMESTAMP │
│ tags JSON [] │
│ ai_generated_metadata JSON {} │
│ spending_emotion_label VARCHAR (impulsive/planned/recurring) │
└──────────────────────────────┬─────────────────────────────────────┘
│ 1
│ 0..1
┌──────────▼──────────────┐
│ FRAUD_LOGS │
│ PK id UUID │
│ FK transaction_id │
│ risk_score FLOAT │
│ (0.0 – 1.0) │
│ suspicious_activity_ │
│ details TEXT │
│ status VARCHAR │
│ (pending/resolved/ │
│ false_positive) │
└───────────────────────────┘
┌─────────────────────────────────────────────────────────────────────┐
│ INVESTMENTS │
│ PK id VARCHAR UUID │
│ FK user_id → USERS.id │
│ asset_name VARCHAR (S&P 500, AAPL, BTC, ...) │
│ type VARCHAR (stock/crypto/mutual_fund/bond) │
│ amount_invested FLOAT │
│ current_value FLOAT │
│ portfolio_allocation FLOAT (percentage) │
│ ai_risk_analysis JSON {risk, expected_return, rec} │
└─────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────┐
│ NOTIFICATIONS │
│ PK id VARCHAR UUID │
│ FK user_id → USERS.id │
│ title VARCHAR NOT NULL │
│ message TEXT NOT NULL │
│ type VARCHAR (alert/insight/warning) │
│ read_status BOOLEAN DEFAULT false │
│ created_at TIMESTAMP │
└─────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────┐
│ AI_INSIGHTS │
│ PK id VARCHAR UUID │
│ FK user_id → USERS.id │
│ type VARCHAR (recommendation/briefing/cashflow) │
│ content TEXT NOT NULL │
│ created_at TIMESTAMP │
└─────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────┐
│ ANALYTICS_SNAPSHOTS │
│ PK id VARCHAR UUID │
│ FK user_id → USERS.id │
│ date TIMESTAMP NOT NULL │
│ total_balance FLOAT │
│ total_spending FLOAT │
│ total_savings FLOAT │
│ financial_score FLOAT │
│ trends_json JSON │
└─────────────────────────────────────────────────────────────────────┘
Relationships Summary
| From | To | Type | Description |
|---|---|---|---|
| USERS | ACCOUNTS | 1:N | User has multiple bank accounts |
| USERS | SUBSCRIPTIONS | 1:N | User has multiple subscriptions |
| USERS | GOALS | 1:N | User has multiple financial goals |
| USERS | INVESTMENTS | 1:N | User has multiple investments |
| USERS | NOTIFICATIONS | 1:N | User receives notifications |
| USERS | AI_INSIGHTS | 1:N | User has AI-generated insights |
| USERS | ANALYTICS_SNAPSHOTS | 1:N | Daily financial snapshots |
| ACCOUNTS | TRANSACTIONS | 1:N | Account has many transactions |
| TRANSACTIONS | FRAUD_LOGS | 1:0..1 | Transaction may have one fraud log |
Indexes (Performance)
-- Primary lookup patterns
CREATE INDEX idx_transactions_account_id ON transactions(account_id);
CREATE INDEX idx_transactions_timestamp ON transactions(timestamp DESC);
CREATE INDEX idx_transactions_category ON transactions(category);
CREATE INDEX idx_fraud_logs_transaction ON fraud_logs(transaction_id);
CREATE INDEX idx_notifications_user_read ON notifications(user_id, read_status);
CREATE INDEX idx_accounts_user_id ON accounts(user_id);
CREATE INDEX idx_goals_user_id ON goals(user_id);
CREATE INDEX idx_investments_user_id ON investments(user_id);