Spaces:
Build error
Build error
File size: 10,319 Bytes
a282d4b | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 | # 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)
```sql
-- 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);
```
|