# Grateful Journal — MongoDB Schema Documentation

**Version:** 2.0 (Refactored)  
**Last Updated:** 2026-03-05

---

## Overview

This document describes the refactored MongoDB schema for the Grateful Journal application. The schema has been redesigned to:

- Ensure one user per email (deduplicated)
- Use ObjectId references instead of strings
- Optimize queries for common operations (history pagination, calendar view)
- Prepare for client-side encryption
- Add proper indexes for performance

---

## Collections

### 1. `users` Collection

Stores user profile information. One document per unique email.

#### Schema

```javascript
{
  _id: ObjectId,
  email: string (unique),
  displayName: string,
  photoURL: string,
  theme: "light" | "dark",
  createdAt: Date,
  updatedAt: Date
}
```

#### Field Descriptions

| Field         | Type     | Required | Notes                                    |
| ------------- | -------- | -------- | ---------------------------------------- |
| `_id`         | ObjectId | Yes      | Unique primary key, auto-generated       |
| `email`       | String   | Yes      | User's email; unique constraint; indexed |
| `displayName` | String   | Yes      | User's display name (from Google Auth)   |
| `photoURL`    | String   | No       | User's profile photo URL                 |
| `theme`       | String   | Yes      | Theme preference: "light" or "dark"      |
| `createdAt`   | Date     | Yes      | Account creation timestamp               |
| `updatedAt`   | Date     | Yes      | Last profile update timestamp            |

#### Unique Constraints

- `email`: Unique index ensures one user per email address

#### Example Document

```json
{
  "_id": ObjectId("507f1f77bcf86cd799439011"),
  "email": "jeet.debnath2004@gmail.com",
  "displayName": "Jeet Debnath",
  "photoURL": "https://lh3.googleusercontent.com/a/ACg8...",
  "theme": "light",
  "createdAt": ISODate("2026-03-04T06:51:32.598Z"),
  "updatedAt": ISODate("2026-03-05T10:30:00.000Z")
}
```

---

### 2. `entries` Collection

Stores journal entries for each user. Each entry has a logical journal date and optional encryption metadata.

#### Schema

```javascript
{
  _id: ObjectId,
  userId: ObjectId,
  title: string,
  content: string,
  mood: "happy" | "sad" | "neutral" | "anxious" | "grateful" | null,
  tags: string[],
  isPublic: boolean,

  entryDate: Date,        // Logical journal date
  createdAt: Date,
  updatedAt: Date,

  encryption: {
    encrypted: boolean,
    iv: string | null,      // Base64-encoded initialization vector
    algorithm: string | null // e.g., "AES-256-GCM"
  }
}
```

#### Field Descriptions

| Field        | Type     | Required | Notes                                     |
| ------------ | -------- | -------- | ----------------------------------------- |
| `_id`        | ObjectId | Yes      | Entry ID; auto-generated; indexed         |
| `userId`     | ObjectId | Yes      | Reference to user.\_id; indexed; enforced |
| `title`      | String   | Yes      | Entry title/headline                      |
| `content`    | String   | Yes      | Entry body content                        |
| `mood`       | String   | No       | Mood selector (null if not set)           |
| `tags`       | Array    | Yes      | Array of user-defined tags []             |
| `isPublic`   | Bool     | Yes      | Public sharing flag (currently unused)    |
| `entryDate`  | Date     | Yes      | Logical journal date (start of day, UTC)  |
| `createdAt`  | Date     | Yes      | Database write timestamp                  |
| `updatedAt`  | Date     | Yes      | Last modification timestamp               |
| `encryption` | Object   | Yes      | Encryption metadata (nested)              |

#### Encryption Metadata

```javascript
{
  encrypted: boolean,       // If true, content is encrypted
  iv: string | null,        // Base64 initialization vector
  algorithm: string | null  // Encryption algorithm name
}
```

**Notes:**

- `encrypted: false` by default (plain text storage)
- When setting `encrypted: true`, client provides `iv` and `algorithm`
- Server stores metadata but does NOT decrypt; decryption happens client-side

#### Example Document

```json
{
  "_id": ObjectId("507f1f77bcf86cd799439012"),
  "userId": ObjectId("507f1f77bcf86cd799439011"),
  "title": "Today's Gratitude",
  "content": "I'm grateful for my family, coffee, and a good day at work.",
  "mood": "grateful",
  "tags": ["family", "work", "coffee"],
  "isPublic": false,
  "entryDate": ISODate("2026-03-05T00:00:00.000Z"),
  "createdAt": ISODate("2026-03-05T12:30:15.123Z"),
  "updatedAt": ISODate("2026-03-05T12:30:15.123Z"),
  "encryption": {
    "encrypted": false,
    "iv": null,
    "algorithm": null
  }
}
```

---

## Indexes

Indexes optimize query performance. All indexes are created by the `scripts/create_indexes.py` script.

### Users Indexes

```javascript
// Unique index on email (prevents duplicates)
db.users.createIndex({ email: 1 }, { unique: true });

// For sorting users by creation date
db.users.createIndex({ createdAt: -1 });
```

### Entries Indexes

```javascript
// Compound index for history pagination (most recent first)
db.entries.createIndex({ userId: 1, createdAt: -1 });

// Compound index for calendar queries by date
db.entries.createIndex({ userId: 1, entryDate: 1 });

// For tag-based searches (future feature)
db.entries.createIndex({ tags: 1 });

// For sorting by entry date
db.entries.createIndex({ entryDate: -1 });
```

### Index Rationale

- **`(userId, createdAt)`**: Supports retrieving a user's entries in reverse chronological order with pagination
- **`(userId, entryDate)`**: Supports calendar view queries (entries for a specific month/date)
- **`tags`**: Supports future tag filtering/search
- **`entryDate`**: Supports standalone date-range queries

---

## Query Patterns

### User Queries

#### Find or Create User (Upsert)

```python
db.users.update_one(
    { "email": email },
    {
        "$setOnInsert": {
            "email": email,
            "displayName": displayName,
            "photoURL": photoURL,
            "theme": "light",
            "createdAt": datetime.utcnow()
        },
        "$set": {
            "updatedAt": datetime.utcnow()
        }
    },
    upsert=True
)
```

**Why:** Ensures exactly one user per email. Frontend calls this after any Firebase login.

#### Get User by Email

```python
user = db.users.find_one({ "email": email })
```

**Index Used:** Unique index on `email`

---

### Entry Queries

#### Create Entry

```python
db.entries.insert_one({
    "userId": ObjectId(user_id),
    "title": title,
    "content": content,
    "mood": mood,
    "tags": tags,
    "isPublic": False,
    "entryDate": entry_date,  # Start of day UTC
    "createdAt": datetime.utcnow(),
    "updatedAt": datetime.utcnow(),
    "encryption": {
        "encrypted": False,
        "iv": None,
        "algorithm": None
    }
})
```

#### Get Entries for User (Paginated, Recent First)

```python
entries = db.entries.find(
    { "userId": ObjectId(user_id) }
).sort("createdAt", -1).skip(skip).limit(limit)
```

**Index Used:** `(userId, createdAt)`  
**Use Case:** History page with pagination

#### Get Entries by Month (Calendar View)

```python
start_date = datetime(year, month, 1)
end_date = datetime(year, month + 1, 1)

entries = db.entries.find({
    "userId": ObjectId(user_id),
    "entryDate": {
        "$gte": start_date,
        "$lt": end_date
    }
}).sort("entryDate", -1)
```

**Index Used:** `(userId, entryDate)`  
**Use Case:** Calendar view showing entries for a specific month

#### Get Entry for Specific Date

```python
target_date = datetime(year, month, day)
next_date = target_date + timedelta(days=1)

entries = db.entries.find({
    "userId": ObjectId(user_id),
    "entryDate": {
        "$gte": target_date,
        "$lt": next_date
    }
})
```

**Index Used:** `(userId, entryDate)`  
**Use Case:** Daily view or fetching today's entry

#### Update Entry

```python
db.entries.update_one(
    { "_id": ObjectId(entry_id), "userId": ObjectId(user_id) },
    {
        "$set": {
            "title": new_title,
            "content": new_content,
            "mood": new_mood,
            "updatedAt": datetime.utcnow()
        }
    }
)
```

#### Delete Entry

```python
db.entries.delete_one({
    "_id": ObjectId(entry_id),
    "userId": ObjectId(user_id)
})
```

#### Delete All User Entries (on account deletion)

```python
db.entries.delete_many({ "userId": ObjectId(user_id) })
```

---

## Data Types & Conversions

### ObjectId

**MongoDB Storage:** `ObjectId`  
**Python Type:** `bson.ObjectId`  
**JSON Representation:** String (24-character hex)

**Conversion:**

```python
from bson import ObjectId

# String to ObjectId
oid = ObjectId(string_id)

# ObjectId to String (for JSON responses)
string_id = str(oid)

# Check if valid ObjectId string
try:
    oid = ObjectId(potential_string)
except:
    # Invalid ObjectId
    pass
```

### Datetime

**MongoDB Storage:** ISODate (UTC)  
**Python Type:** `datetime.datetime`  
**JSON Representation:** ISO 8601 string

**Conversion:**

```python
from datetime import datetime

# Create UTC datetime
now = datetime.utcnow()

# ISO string to datetime
dt = datetime.fromisoformat(iso_string.replace("Z", "+00:00"))

# Datetime to ISO string
iso_string = dt.isoformat()
```

---

## Migration from Old Schema

### What Changed

| Aspect       | Old Schema              | New Schema                     |
| ------------ | ----------------------- | ------------------------------ |
| Users        | Many per email possible | One per email (unique)         |
| User \_id    | ObjectId (correct)      | ObjectId (unchanged)           |
| Entry userId | String                  | ObjectId                       |
| Entry date   | Only `createdAt`        | `createdAt` + `entryDate`      |
| Encryption   | Not supported           | Metadata in `encryption` field |
| Settings     | Separate collection     | Merged into `users.theme`      |
| Indexes      | None                    | Comprehensive indexes          |

### Migration Steps

See [MIGRATION_GUIDE.md](./MIGRATION_GUIDE.md) for detailed instructions.

**Quick Summary:**

```bash
# 1. Backup database
mongodump --db grateful_journal --out ./backup

# 2. Run migration script
python backend/scripts/migrate_data.py

# 3. Create indexes
python backend/scripts/create_indexes.py

# 4. Verify data
python backend/scripts/verify_schema.py
```

---

## Security

### User Isolation

- All entry queries filter by `userId` to ensure users only access their own data
- Frontend enforces user_id matching via Firebase auth token
- Backend validates ObjectId conversions

### Encryption Ready

- `entries.encryption` metadata prepares schema for future client-side encryption
- Server stores encrypted content as-is without decryption
- Client responsible for IV, algorithm, and decryption keys

### Indexes & Performance

- Compound indexes prevent full collection scans
- Unique email index prevents user confusion
- Pagination support prevents memory overload

---

## Backup & Recovery

### Backup

```bash
# Full database
mongodump --db grateful_journal --out ./backup-$(date +%Y%m%d-%H%M%S)

# Specific collection
mongodump --db grateful_journal --collection entries --out ./backup-entries
```

### Restore

```bash
# Full database
mongorestore --db grateful_journal ./backup-2026-03-05-120000

# Specific collection
mongorestore --db grateful_journal ./backup-entries
```

---

## FAQ

### Q: Can I change the entryDate of an entry?

**A:** Yes. Send a PUT request with `entryDate` in the body. The entry will be re-indexed for calendar queries.

### Q: How do I encrypt entry content?

**A:**

1. Client encrypts content client-side using a key (not transmitted)
2. Client sends encrypted content + metadata (iv, algorithm)
3. Server stores content + encryption metadata as-is
4. On retrieval, client decrypts using stored IV and local key

### Q: What if I have duplicate users?

**A:** Run the migration script:

```bash
python backend/scripts/migrate_data.py
```

It detects duplicates, keeps the oldest, and consolidates entries.

### Q: Should I paginate entries?

**A:** Yes. Use `skip` and `limit` to prevent loading thousands of entries:

```
GET /api/entries/{user_id}?skip=0&limit=50
```

### Q: How do I query entries by date range?

**A:** Use the calendar endpoint or build a query:

```python
db.entries.find({
    "userId": oid,
    "entryDate": {
        "$gte": start_date,
        "$lt": end_date
    }
})
```

---

## References

- [FastAPI Backend Routes](../routers/)
- [Pydantic Models](../models.py)
- [Migration Script](../scripts/migrate_data.py)
- [Index Creation Script](../scripts/create_indexes.py)
- [MongoDB Documentation](https://docs.mongodb.com/)

---

_For questions or issues, refer to the project README or open an issue on GitHub._
