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