# Database Schema

## Entity Relationship Diagram

```mermaid
erDiagram
    User ||--o{ Record : has
    User ||--o{ Category : has
    User ||--o{ Debt : has
    User ||--o{ Goal : has
    User ||--o{ Project : has
    User ||--o{ Investment : has
    Record }o--|| Category : belongs_to
    Record }o--o| Investment : may_link_to

    User {
        ObjectId _id PK
        String name
        String email
        String password
        String avatar
        Number budget "default: 20000"
        String googleId
        String facebookId
        Date createdAt
    }

    Record {
        ObjectId _id PK
        String type "expense | revenue"
        String name
        Date date
        ObjectId categoryId FK
        Number amount
        String merchant
        ObjectId userId FK
        String ledger "Personal | Business"
        Boolean mustPay "default: false"
        Boolean recurring "default: false"
        ObjectId linkedInvestment FK "nullable"
    }

    Category {
        ObjectId _id PK
        String type "expense | revenue"
        String name
        String icon "FontAwesome class"
        String ledger "Personal | Business"
    }

    Debt {
        ObjectId _id PK
        String name
        Number totalOwed
        Number amountPaid "default: 0"
        String status "NotRepaying | ActivelyRepaying"
        ObjectId userId FK
    }

    Goal {
        ObjectId _id PK
        String name
        Number targetAmount
        Number savedAmount "default: 0"
        String tier "MustFund | ActivelySaving | Someday"
        Date targetDate
        ObjectId userId FK
    }

    Project {
        ObjectId _id PK
        String name
        String status "Active | Maintenance | Paused | Archived"
        Date lastTouched "default: Date.now"
        String nextStep
        String notes
        ObjectId userId FK
    }

    Investment {
        ObjectId _id PK
        String name
        Number cost
        Date dateAcquired
        String expectedReturnType "Revenue | CostSavings | Both"
        Number returnToDate "default: 0"
        String status "Recouping | BreakEven | Profitable"
        ObjectId userId FK
        String notes
    }
```

## Model Details

### Record

The central transaction model. Every entry is scoped to a user and tagged with a ledger.

```mermaid
flowchart LR
    R[Record] -->|categoryId| C[Category]
    R -->|userId| U[User]
    R -->|linkedInvestment| I[Investment]
    
    style R fill:#e1f5fe
    style C fill:#f3e5f5
    style U fill:#e8f5e9
    style I fill:#fff3e0
```

**Fields:**

| Field | Type | Required | Default | Notes |
|---|---|---|---|---|
| `type` | String | Yes | — | `"expense"` or `"revenue"` |
| `name` | String | Yes | — | Transaction description |
| `date` | Date | Yes | — | Transaction date |
| `categoryId` | ObjectId | Yes | — | Ref → Category |
| `amount` | Number | Yes | — | Stored positive, flipped at display |
| `merchant` | String | No | — | Optional merchant name |
| `userId` | ObjectId | Yes | — | Ref → User (indexed) |
| `ledger` | String | Yes | `"Personal"` | `"Personal"` or `"Business"` |
| `mustPay` | Boolean | No | `false` | Personal expenses only |
| `recurring` | Boolean | No | `false` | Visual flag for v1 |
| `linkedInvestment` | ObjectId | No | `null` | Ref → Investment (Business costs) |

### Category

Categories belong to exactly one ledger. No `Both` option.

**Personal categories:**

| Name | Type | Icon |
|---|---|---|
| Revenue | revenue | `fas fa-money-check-alt` |
| Home | expense | `fas fa-home` |
| Transportation | expense | `fas fa-shuttle-van` |
| Entertainment | expense | `fas fa-grin-beam` |
| Food | expense | `fas fa-utensils` |
| Other | expense | `fas fa-pen` |

**Business categories:**

| Name | Type | Icon |
|---|---|---|
| Dog Food | expense | `fas fa-bone` |
| Deworming | expense | `fas fa-pills` |
| Pesticide | expense | `fas fa-bug` |
| Puppy Sale | revenue | `fas fa-dog` |
| Food Resale | revenue | `fas fa-store` |

### Debt

Passive balance tracker. `remainingBalance` is computed, never stored.

```mermaid
flowchart LR
    D[Debt] -->|userId| U[User]
    
    D --- R["remainingBalance = totalOwed - amountPaid"]
    
    style D fill:#fce4ec
```

| Field | Type | Default | Notes |
|---|---|---|---|
| `name` | String | — | e.g. "HELB" |
| `totalOwed` | Number | — | Original debt amount |
| `amountPaid` | Number | `0` | Running total paid |
| `status` | String | `"NotRepaying"` | Flips when income allows |
| `userId` | ObjectId | — | Ref → User (indexed) |

### Goal

Tiered savings with progress tracking.

| Field | Type | Default | Notes |
|---|---|---|---|
| `name` | String | — | e.g. "Emergency Fund" |
| `targetAmount` | Number | — | Goal amount |
| `savedAmount` | Number | `0` | Current progress |
| `tier` | String | — | Priority ranking |
| `targetDate` | Date | — | Optional deadline |
| `userId` | ObjectId | — | Ref → User (indexed) |

**Tier meanings:**

| Tier | Display | Color | Meaning |
|---|---|---|---|
| `MustFund` | Must Fund | Red | Non-negotiable savings |
| `ActivelySaving` | Actively Saving | Blue | Currently contributing |
| `Someday` | Someday | Gray | Aspirational, no pressure |

### Project

Simple maintenance list. Sorted by `lastTouched` ascending on dashboard.

| Field | Type | Default | Notes |
|---|---|---|---|
| `name` | String | — | Project name |
| `status` | String | `"Active"` | Current state |
| `lastTouched` | Date | `Date.now` | Last activity date |
| `nextStep` | String | — | Short note on what's next |
| `notes` | String | — | Free-form scratchpad |
| `userId` | ObjectId | — | Ref → User (indexed) |

### Investment

Tracks specific investments and whether they're paying off.

| Field | Type | Default | Notes |
|---|---|---|---|
| `name` | String | — | e.g. "Freezer" |
| `cost` | Number | — | Initial investment |
| `dateAcquired` | Date | — | Purchase date |
| `expectedReturnType` | String | — | Revenue / CostSavings / Both |
| `returnToDate` | Number | `0` | Running total of returns |
| `status` | String | `"Recouping"` | Current ROI status |
| `userId` | ObjectId | — | Ref → User (indexed) |
| `notes` | String | — | Free-form notes |

**Status derivation:**

```mermaid
flowchart TD
    A[returnToDate] --> B{Compare to cost}
    B -->|< 50% of cost| C[Recouping]
    B -->|50-100% of cost| D[BreakEven]
    B -->|> 100% of cost| E[Profitable]
```

## Indexes

All models with `userId` fields have an index on that field for query performance:

- `Record.userId` — indexed
- `Record.categoryId` — indexed
- `Debt.userId` — indexed
- `Goal.userId` — indexed
- `Project.userId` — indexed
- `Investment.userId` — indexed