adopt-a-street/COUCHDB_DESIGN.md

CouchDB Document Structure Design

Overview

This document outlines the comprehensive CouchDB document structure to replace the existing MongoDB models for the Adopt-a-Street application. The design prioritizes query performance, data consistency, and the denormalization requirements of a document-oriented database.

Design Principles

1. Denormalization over Normalization: Since CouchDB doesn't support joins, we'll embed frequently accessed data
2. Document Type Identification: Each document includes a type field for easy filtering
3. String IDs: Convert MongoDB ObjectIds to strings for consistency
4. Timestamp Handling: Use CouchDB's built-in timestamps plus custom createdAt/updatedAt fields
5. Query-First Design: Structure documents based on common access patterns

Document Types and Structures

1. User Documents (type: "user")

{
  "_id": "user_1234567890abcdef",
  "type": "user",
  "name": "John Doe",
  "email": "john@example.com",
  "password": "hashed_password_here",
  "isPremium": false,
  "points": 150,
  "profilePicture": "https://cloudinary.com/image.jpg",
  "cloudinaryPublicId": "abc123",
  "adoptedStreets": ["street_abc123", "street_def456"],
  "completedTasks": ["task_123", "task_456"],
  "posts": ["post_789", "post_012"],
  "events": ["event_345", "event_678"],
  "earnedBadges": [
    {
      "badgeId": "badge_123",
      "name": "Street Hero",
      "description": "Adopted 5 streets",
      "icon": "🏆",
      "rarity": "rare",
      "earnedAt": "2024-01-15T10:30:00Z",
      "progress": 100
    }
  ],
  "stats": {
    "streetsAdopted": 2,
    "tasksCompleted": 5,
    "postsCreated": 3,
    "eventsParticipated": 2,
    "badgesEarned": 1
  },
  "createdAt": "2024-01-01T00:00:00Z",
  "updatedAt": "2024-01-15T10:30:00Z"
}

Design Decisions:

• Embedded badge data to avoid additional lookups in profile views
• Stats field for quick dashboard queries
• Keep arrays of IDs for detailed queries when needed

2. Street Documents (type: "street")

{
  "_id": "street_abc123def456",
  "type": "street",
  "name": "Main Street",
  "location": {
    "type": "Point",
    "coordinates": [-74.0060, 40.7128]
  },
  "adoptedBy": {
    "userId": "user_1234567890abcdef",
    "name": "John Doe",
    "profilePicture": "https://cloudinary.com/image.jpg"
  },
  "status": "adopted",
  "stats": {
    "tasksCount": 5,
    "completedTasksCount": 3,
    "reportsCount": 2,
    "openReportsCount": 1
  },
  "createdAt": "2024-01-01T00:00:00Z",
  "updatedAt": "2024-01-15T10:30:00Z"
}

Design Decisions:

• Embedded adopter info for map display without additional queries
• Stats field for quick overview on street details
• Keep GeoJSON format for geospatial queries

3. Task Documents (type: "task")

{
  "_id": "task_1234567890abcdef",
  "type": "task",
  "street": {
    "streetId": "street_abc123def456",
    "name": "Main Street",
    "location": {
      "type": "Point",
      "coordinates": [-74.0060, 40.7128]
    }
  },
  "description": "Clean up litter on sidewalk",
  "completedBy": {
    "userId": "user_1234567890abcdef",
    "name": "John Doe",
    "profilePicture": "https://cloudinary.com/image.jpg"
  },
  "status": "completed",
  "completedAt": "2024-01-15T10:30:00Z",
  "pointsAwarded": 10,
  "createdAt": "2024-01-10T00:00:00Z",
  "updatedAt": "2024-01-15T10:30:00Z"
}

Design Decisions:

• Embedded street info for task list display
• Embedded completer info for activity feeds
• Added completedAt and pointsAwarded for gamification tracking

4. Post Documents (type: "post")

{
  "_id": "post_1234567890abcdef",
  "type": "post",
  "user": {
    "userId": "user_1234567890abcdef",
    "name": "John Doe",
    "profilePicture": "https://cloudinary.com/image.jpg"
  },
  "content": "Great day cleaning up Main Street!",
  "imageUrl": "https://cloudinary.com/post_image.jpg",
  "cloudinaryPublicId": "post_abc123",
  "likes": ["user_456", "user_789"],
  "likesCount": 2,
  "commentsCount": 5,
  "createdAt": "2024-01-15T10:30:00Z",
  "updatedAt": "2024-01-15T10:30:00Z"
}

Design Decisions:

• Embedded user info for social feed display
• Denormalized likesCount for quick sorting
• Keep likes as array of user IDs for like/unlike operations

5. Comment Documents (type: "comment")

{
  "_id": "comment_1234567890abcdef",
  "type": "comment",
  "post": {
    "postId": "post_1234567890abcdef",
    "content": "Great day cleaning up Main Street!",
    "userId": "user_1234567890abcdef"
  },
  "user": {
    "userId": "user_1234567890abcdef",
    "name": "John Doe",
    "profilePicture": "https://cloudinary.com/image.jpg"
  },
  "content": "Awesome work! 🎉",
  "createdAt": "2024-01-15T11:00:00Z",
  "updatedAt": "2024-01-15T11:00:00Z"
}

Design Decisions:

• Embedded both post and user info for comment display
• Post reference for updating comment counts

6. Event Documents (type: "event")

{
  "_id": "event_1234567890abcdef",
  "type": "event",
  "title": "Community Cleanup Day",
  "description": "Join us for a neighborhood cleanup event",
  "date": "2024-02-01T09:00:00Z",
  "location": "Central Park",
  "participants": [
    {
      "userId": "user_1234567890abcdef",
      "name": "John Doe",
      "profilePicture": "https://cloudinary.com/image.jpg",
      "joinedAt": "2024-01-15T10:30:00Z"
    }
  ],
  "participantsCount": 1,
  "status": "upcoming",
  "createdAt": "2024-01-10T00:00:00Z",
  "updatedAt": "2024-01-15T10:30:00Z"
}

Design Decisions:

• Embedded participant objects with join timestamps
• Denormalized participantsCount for quick display
• Rich participant data for event management

7. Report Documents (type: "report")

{
  "_id": "report_1234567890abcdef",
  "type": "report",
  "street": {
    "streetId": "street_abc123def456",
    "name": "Main Street",
    "location": {
      "type": "Point",
      "coordinates": [-74.0060, 40.7128]
    }
  },
  "user": {
    "userId": "user_1234567890abcdef",
    "name": "John Doe",
    "profilePicture": "https://cloudinary.com/image.jpg"
  },
  "issue": "Broken streetlight needs repair",
  "imageUrl": "https://cloudinary.com/report_image.jpg",
  "cloudinaryPublicId": "report_abc123",
  "status": "open",
  "createdAt": "2024-01-15T10:30:00Z",
  "updatedAt": "2024-01-15T10:30:00Z"
}

Design Decisions:

• Embedded street and user info for report management
• Keep status for filtering and workflow management

8. Badge Documents (type: "badge")

{
  "_id": "badge_1234567890abcdef",
  "type": "badge",
  "name": "Street Hero",
  "description": "Adopted 5 streets",
  "icon": "🏆",
  "criteria": {
    "type": "street_adoptions",
    "threshold": 5
  },
  "rarity": "rare",
  "order": 10,
  "isActive": true,
  "createdAt": "2024-01-01T00:00:00Z",
  "updatedAt": "2024-01-01T00:00:00Z"
}

Design Decisions:

• Static badge definitions with criteria for gamification engine
• Added isActive flag for badge management
• order field for display sorting

9. Point Transaction Documents (type: "point_transaction")

{
  "_id": "transaction_1234567890abcdef",
  "type": "point_transaction",
  "user": {
    "userId": "user_1234567890abcdef",
    "name": "John Doe"
  },
  "amount": 10,
  "type": "task_completion",
  "description": "Completed task: Clean up litter on sidewalk",
  "relatedEntity": {
    "entityType": "Task",
    "entityId": "task_1234567890abcdef",
    "entityName": "Clean up litter on sidewalk"
  },
  "balanceAfter": 150,
  "createdAt": "2024-01-15T10:30:00Z"
}

Design Decisions:

• Embedded user info for transaction history
• Rich related entity data for audit trail
• No updates needed - transactions are immutable

Mango Index Strategy

Primary Indexes

// User authentication
{
  "index": {
    "fields": ["type", "email"]
  },
  "name": "user-by-email",
  "type": "json"
}

// Geospatial queries for streets
{
  "index": {
    "fields": ["type", "location"]
  },
  "name": "streets-by-location",
  "type": "json"
}

// User's related data
{
  "index": {
    "fields": ["type", "user.userId"]
  },
  "name": "by-user",
  "type": "json"
}

// Leaderboards
{
  "index": {
    "fields": ["type", "points"]
  },
  "name": "users-by-points",
  "type": "json"
}

// Social feed
{
  "index": {
    "fields": ["type", "createdAt"]
  },
  "name": "posts-by-date",
  "type": "json"
}

// Street status
{
  "index": {
    "fields": ["type", "status"]
  },
  "name": "streets-by-status",
  "type": "json"
}

// Event queries
{
  "index": {
    "fields": ["type", "date", "status"]
  },
  "name": "events-by-date-status",
  "type": "json"
}

Common Query Patterns

1. User Authentication

// Find user by email
{
  "selector": {
    "type": "user",
    "email": "john@example.com"
  },
  "limit": 1
}

2. Geospatial Street Search

// Find streets within bounding box
{
  "selector": {
    "type": "street",
    "status": "available",
    "location": {
      "$geoWithin": {
        "$box": [[-74.1, 40.6], [-73.9, 40.8]]
      }
    }
  }
}

3. User's Activity Feed

// Get user's posts, tasks, and events
{
  "selector": {
    "$or": [
      {
        "type": "post",
        "user.userId": "user_123"
      },
      {
        "type": "task", 
        "completedBy.userId": "user_123"
      },
      {
        "type": "event",
        "participants": {
          "$elemMatch": {
            "userId": "user_123"
          }
        }
      }
    ]
  },
  "sort": [{"createdAt": "desc"}]
}

4. Leaderboard

// Top users by points
{
  "selector": {
    "type": "user",
    "points": {"$gt": 0}
  },
  "sort": [{"points": "desc"}],
  "limit": 10
}

5. Social Feed with Comments

// Get posts with user info and comment counts
{
  "selector": {
    "type": "post"
  },
  "sort": [{"createdAt": "desc"}],
  "limit": 20
}

// Then fetch comments for each post
{
  "selector": {
    "type": "comment",
    "post.postId": {"$in": ["post_123", "post_456"]}
  },
  "sort": [{"createdAt": "asc"}]
}

Data Consistency Strategy

Update Patterns

1. User Points Update:

   • Update user document points
   • Create point transaction document
   • Check and award badges if thresholds met

2. Post Creation:

   • Create post document
   • Update user document (add to posts array, increment stats)
   • Update user's post creation count for badge criteria

3. Task Completion:

   • Update task document (status, completedBy, completedAt)
   • Update user document (add to completedTasks, increment stats, add points)
   • Update street document (increment completedTasksCount)
   • Create point transaction
   • Check for badge awards

4. Street Adoption:

   • Update street document (adoptedBy, status)
   • Update user document (add to adoptedStreets, increment stats)
   • Create point transaction
   • Check for badge awards

Cascade Deletion Handling

Since CouchDB doesn't have transactions, use a "soft delete" approach:

// Mark document as deleted
{
  "_id": "post_123",
  "type": "post",
  "isDeleted": true,
  "deletedAt": "2024-01-15T10:30:00Z"
}

// Background cleanup job can periodically remove truly deleted documents

Counter Management

For counters like commentsCount, likesCount, etc.:

1. Optimistic Updates: Update counter immediately, rollback if needed
2. Periodic Reconciliation: Background job to recalculate counts from actual data
3. Event-Driven Updates: Use CouchDB changes feed to trigger counter updates

Migration Strategy

Phase 1: Data Export and Transformation

// MongoDB to CouchDB transformation script
const transformUser = (mongoUser) => ({
  _id: `user_${mongoUser._id}`,
  type: "user",
  name: mongoUser.name,
  email: mongoUser.email,
  password: mongoUser.password,
  isPremium: mongoUser.isPremium,
  points: mongoUser.points,
  profilePicture: mongoUser.profilePicture,
  cloudinaryPublicId: mongoUser.cloudinaryPublicId,
  adoptedStreets: mongoUser.adoptedStreets.map(id => `street_${id}`),
  completedTasks: mongoUser.completedTasks.map(id => `task_${id}`),
  posts: mongoUser.posts.map(id => `post_${id}`),
  events: mongoUser.events.map(id => `event_${id}`),
  stats: {
    streetsAdopted: mongoUser.adoptedStreets.length,
    tasksCompleted: mongoUser.completedTasks.length,
    postsCreated: mongoUser.posts.length,
    eventsParticipated: mongoUser.events.length,
    badgesEarned: 0 // Will be populated from UserBadge collection
  },
  createdAt: mongoUser.createdAt,
  updatedAt: mongoUser.updatedAt
});

Phase 2: Relationship Resolution

1. First Pass: Migrate all documents with ID references
2. Second Pass: Resolve relationships and embed data
3. Third Pass: Calculate and populate stats and counters

Phase 3: Validation and Testing

1. Data Integrity: Verify all relationships are maintained
2. Query Performance: Test common query patterns
3. Functionality: Ensure all application features work

Benefits of This Design

1. Query Performance: Most common queries require single document lookups
2. Reduced Network Calls: Embedded data eliminates multiple round trips
3. Offline Capability: Rich documents support better offline functionality
4. Scalability: Denormalized design scales well with read-heavy workloads
5. Flexibility: Document structure can evolve without schema migrations

Trade-offs

1. Data Duplication: User data appears in multiple documents
2. Update Complexity: Changes to user data require updating multiple documents
3. Storage Overhead: Larger documents due to embedded data
4. Consistency Challenges: Eventual consistency for related data updates

This design prioritizes read performance and user experience over write efficiency, which aligns well with the social community nature of the Adopt-a-Street application.