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) | |
| ```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); | |
| ``` | |