rtgibbons/SCHEMA_REVIEW_FEEDBACK.md

## SCHEMA_REVIEW_FEEDBACK.md

      
    Raw
  

              SCHEMA_REVIEW_FEEDBACK.md
            
          
    Schema Review Feedback & Proposed Changes

Summary

I found inconsistencies across the MongoDB restructure documentation. This document lists them and proposes fixes.

Issues Identified

Issue 1: Bidirectional References Create Sync Nightmares

Current State:
┌──────────────────┐                    ┌──────────────────┐
│     BROKER       │                    │      AGENT       │
├──────────────────┤                    ├──────────────────┤
│ agents[]         │───────────────────►│ brokerId         │
│                  │◄───────────────────│                  │
└──────────────────┘                    └──────────────────┘
     ❌ Both sides maintain the relationship

Problem:

When an agent joins a broker, you must update BOTH Broker.agents[] AND Agent.brokerId
If one update fails, data becomes inconsistent
No transactional guarantee in MongoDB for multi-document updates


Issue 2: Role Field Conflicts with UserRole Junction

Current State (Flowchart):
User {
  role: "buyer" | "seller" | "agent" | "broker" | "admin"  // ❌ Single role
}
Current State (Restructure Plan):
User { }  // No role field
UserRole {
  userId: ObjectId,
  roleId: ObjectId,
  isPrimary: boolean  // ✅ Supports multiple roles
}
Problem:

Documents describe two different approaches
Cannot determine which is the source of truth
Junction table approach works better because a user can be both buyer and seller


Issue 3: Naming Inconsistency (Agent vs AgentProfile)

Current State:


Document
Model Name


MONGODB_RESTRUCTURE_PLAN.md
AgentProfile, BuyerProfile, SellerProfile, BrokerProfile


DATABASE_SCHEMA_FLOWCHART.md
Agent, Broker (no "Profile" suffix)


Problem:

Confusing for developers
Unclear which naming convention to follow in code
Will cause issues during implementation


Issue 4: Missing Index Definitions for Junction Collections

Current State:
PropertyAttributeValue {
  propertyId: ObjectId,
  attributeId: ObjectId,
  valueBoolean: boolean,
  valueText: string,
  valueNumber: number
}
// ❌ No indexes defined
Problem:

This collection will be HUGE (properties × attributes per property)
Without proper indexes, queries will be slow
Common queries: "all attributes for property X", "all properties with attribute Y"


Issue 5: Over-Normalized Attribute Hierarchy

Current State:
┌────────────────────┐    ┌────────────────────┐    ┌────────────────────┐
│ AttributeCategory  │───►│ AttributeSubcat    │───►│ PropertyAttribute  │
├────────────────────┤    ├────────────────────┤    ├────────────────────┤
│ slug: 'appliances' │    │ slug: 'kitchen'    │    │ slug: 'dishwasher' │
│ name: 'Appliances' │    │ categoryId ────────┼────│ categoryId         │
│                    │    │                    │    │ subcategoryId ─────┼───┐
└────────────────────┘    └────────────────────┘    └────────────────────┘   │
                                                              ▲              │
                                                              └──────────────┘

Problem:

Querying "all appliances for a property" requires 3+ lookups
Subcategory adds complexity but may not provide value
Most UI displays need category + attribute name together


When Junction Collections Make Sense in MongoDB

Here's how I think about when to use junction collections vs. embedding.
When to Use Each Pattern


Use Case
Junction OK?
Why


Many-to-many (User ↔ Roles)
✅ Yes
No other clean option


Relationship has metadata (grantedAt, revokedAt)
✅ Yes
Can't store metadata in arrays


High-cardinality (1000s of attributes per property)
✅ Yes
Arrays don't scale past ~1000 items


Low-cardinality (user has 2-3 roles)
⚠️ Consider embedding
Array in User doc is simpler


Frequently queried together
⚠️ Consider embedding
Avoids $lookup overhead


Independently queried
✅ Yes
Junction allows flexible queries


Recommendations for Quotivo Schema


Collection
Recommended Pattern
Reason


UserRole
Embed as array in User
Users have 1-4 roles max


PropertyAttributeValue
Junction collection
Properties have 50+ attributes


AgentBrokerageAssignment
Single ref on AgentProfile
Simple 1:N relationship


User.favoriteAgents
Embed as array
Typical user has <50 favorites


Chat.participants
Embed as array
Always 2-10 participants


Hybrid Approach

Use embedding for low-cardinality relationships instead of junction tables everywhere:
// ✅ EMBED: UserRole (low cardinality, always fetched with user)
User {
  email: "user@example.com",
  passwordHash: "...",
  roles: [
    { role: "buyer", isPrimary: true, grantedAt: ISODate("2026-01-01") },
    { role: "seller", isPrimary: false, grantedAt: ISODate("2026-01-15") }
  ]
}

// ✅ JUNCTION: PropertyAttributeValue (high cardinality, queried independently)
PropertyAttributeValue {
  propertyId: ObjectId("..."),
  attributeId: ObjectId("..."),
  valueBoolean: true
}

// ✅ EMBED: Favorites (moderate cardinality, always fetched with user)
User {
  favoriteProperties: [ObjectId("..."), ObjectId("...")],  // Max ~100
  favoriteAgents: [ObjectId("...")]  // Max ~50
}
Performance Comparison


Pattern
Read Performance
Write Performance
Consistency


Embedded Array
⚡ Fast (single doc)
⚡ Fast (single update)
✅ Atomic


Junction Collection
🐢 Slower ($lookup)
🐢 Slower (multi-doc)
⚠️ Eventual


Bidirectional Refs
🐢 Slower
🐌 Slowest (2 updates)
❌ Risk of inconsistency


Decision Matrix

                    Low Cardinality          High Cardinality
                    (< 100 items)            (100+ items)
                ┌─────────────────────┬─────────────────────┐
  Always        │                     │                     │
  Fetched       │   EMBED             │   JUNCTION          │
  Together      │   (roles, favorites)│   (rare case)       │
                ├─────────────────────┼─────────────────────┤
  Queried       │                     │                     │
  Independently │   EITHER            │   JUNCTION          │
                │   (case by case)    │   (attributes)      │
                └─────────────────────┴─────────────────────┘


Proposed Changes

Change 1: Single-Direction Broker-Agent Relationship

Proposed Structure:
┌──────────────────┐                    ┌──────────────────┐
│     BROKERAGE    │                    │   AGENT PROFILE  │
├──────────────────┤                    ├──────────────────┤
│ _id              │                    │ _id              │
│ brokerProfileId  │                    │ userId           │
│ companyName      │                    │ brokerageId ─────┼────► Brokerage._id
│ licenseNumber    │                    │ licenseNumber    │
│                  │                    │ isActive         │
└──────────────────┘                    └──────────────────┘
     (1)                                        (N)
     
     ✅ Only AgentProfile references Brokerage
     ✅ Query agents by brokerage: AgentProfile.find({ brokerageId })

Schema:
@Schema()
export class AgentProfile {
  @Prop({ type: Types.ObjectId, ref: 'User', required: true, unique: true })
  userId: Types.ObjectId;

  @Prop({ type: Types.ObjectId, ref: 'Brokerage' })
  brokerageId?: Types.ObjectId;  // ✅ Single source of truth

  @Prop({ required: true })
  licenseNumber: string;

  @Prop({ default: true })
  isActive: boolean;
}

@Schema()
export class Brokerage {
  @Prop({ type: Types.ObjectId, ref: 'BrokerProfile', required: true })
  brokerProfileId: Types.ObjectId;

  @Prop({ required: true })
  companyName: string;

  // ❌ REMOVED: agents[] array
  // ✅ Query instead: AgentProfile.find({ brokerageId: this._id })
}

Change 2: Embed Roles in User (Hybrid Approach)

Based on the patterns above, embedding makes more sense for roles:

Users have 1-4 roles max
We always fetch roles with the user anyway for auth checks
Updates are atomic, so no consistency problems

Proposed Structure:
┌─────────────────────────────────────────────────────────┐
│                         User                             │
│                      (Auth + Roles)                      │
├─────────────────────────────────────────────────────────┤
│ _id                                                      │
│ email                                                    │
│ passwordHash                                             │
│ googleId                                                 │
│ status                                                   │
│                                                          │
│ roles: [                                    ◄── EMBEDDED │
│   { role: "buyer", isPrimary: true, grantedAt: Date },   │
│   { role: "seller", isPrimary: false, grantedAt: Date }  │
│ ]                                                        │
│                                                          │
│ ❌ REMOVED: single role field                            │
│ ❌ NOT NEEDED: UserRole junction collection              │
└─────────────────────────────────────────────────────────┘

     ✅ User can have multiple roles (buyer + seller)
     ✅ Role changes are auditable (grantedAt in subdoc)
     ✅ Single document read for auth
     ✅ Atomic updates

Schema:
// Embedded subdocument for roles
export class UserRoleSubdoc {
  @Prop({ enum: RoleName, required: true })
  role: RoleName;

  @Prop({ default: false })
  isPrimary: boolean;

  @Prop({ default: Date.now })
  grantedAt: Date;

  @Prop()
  revokedAt?: Date;  // ✅ Soft revoke for audit trail
}

@Schema()
export class User {
  @Prop({ required: true, unique: true })
  email: string;

  @Prop()
  passwordHash?: string;

  @Prop()
  googleId?: string;

  @Prop({ enum: UserStatus, default: UserStatus.PENDING })
  status: UserStatus;

  @Prop({ type: [UserRoleSubdoc], default: [] })
  roles: UserRoleSubdoc[];  // ✅ Embedded array

  // Helper method
  hasRole(role: RoleName): boolean {
    return this.roles.some(r => r.role === role && !r.revokedAt);
  }

  getPrimaryRole(): RoleName | null {
    return this.roles.find(r => r.isPrimary && !r.revokedAt)?.role || null;
  }
}

// Index for role queries
UserSchema.index({ 'roles.role': 1 });
Query Examples:
// Find all users with agent role
await User.find({ 'roles.role': 'agent', 'roles.revokedAt': null });

// Add a role to user (atomic)
await User.updateOne(
  { _id: userId },
  { $push: { roles: { role: 'seller', isPrimary: false, grantedAt: new Date() } } }
);

// Revoke a role (soft delete)
await User.updateOne(
  { _id: userId, 'roles.role': 'seller' },
  { $set: { 'roles.$.revokedAt': new Date() } }
);
When to Use Junction Instead:
If you query "all users with role X" frequently with complex filters, a junction collection might work better. But for typical auth flows, embedding is simpler and faster.

Change 3: Unified Naming Convention (Use "Profile" Suffix)

Proposed Naming:


Old (Inconsistent)
New (Unified)
Purpose


Agent / AgentProfile
AgentProfile
Professional agent data


Broker / BrokerProfile
BrokerProfile
Professional broker data


User
User
Authentication only


RealEstateOffice
Brokerage
Company entity


Complete Model List:
Authentication Layer:
├── User              (auth credentials)
├── Role              (role definitions)
└── UserRole          (user-role assignments)

Profile Layer:
├── BuyerProfile      (buyer preferences)
├── SellerProfile     (seller info)
├── AgentProfile      (agent professional data)
└── BrokerProfile     (broker professional data)

Business Layer:
├── Brokerage         (company entity)
├── Property          (listings)
└── PropertyAttributeValue (property features)

Attribute Layer:
├── AttributeCategory
└── PropertyAttribute


Change 4: Required Indexes for Junction Collections

Proposed Indexes:
// PropertyAttributeValue - High volume collection
@Schema()
export class PropertyAttributeValue {
  @Prop({ type: Types.ObjectId, ref: 'Property', required: true, index: true })
  propertyId: Types.ObjectId;

  @Prop({ type: Types.ObjectId, ref: 'PropertyAttribute', required: true, index: true })
  attributeId: Types.ObjectId;

  @Prop()
  valueBoolean?: boolean;

  @Prop()
  valueText?: string;

  @Prop()
  valueNumber?: number;
}

// Indexes to create:
// 1. { propertyId: 1, attributeId: 1 } - unique, prevents duplicates
// 2. { propertyId: 1 } - get all attributes for a property
// 3. { attributeId: 1, valueBoolean: 1 } - find properties with specific attribute
Index Definitions:
// In migration or schema definition
PropertyAttributeValueSchema.index({ propertyId: 1, attributeId: 1 }, { unique: true });
PropertyAttributeValueSchema.index({ propertyId: 1 });
PropertyAttributeValueSchema.index({ attributeId: 1, valueBoolean: 1 });

// UserRole indexes
UserRoleSchema.index({ userId: 1, roleId: 1 }, { unique: true });
UserRoleSchema.index({ userId: 1 });
UserRoleSchema.index({ roleId: 1 });

// AgentProfile indexes
AgentProfileSchema.index({ userId: 1 }, { unique: true });
AgentProfileSchema.index({ brokerageId: 1 });

Change 5: Simplified Attribute Hierarchy (Remove Subcategory)

Proposed Structure:
┌────────────────────┐              ┌────────────────────┐
│ AttributeCategory  │              │ PropertyAttribute  │
├────────────────────┤              ├────────────────────┤
│ _id                │              │ _id                │
│ slug: 'appliances' │◄─────────────│ categoryId         │
│ name: 'Appliances' │              │ categorySlug ──────┼──► Denormalized
│ icon               │              │ slug: 'dishwasher' │
│ displayOrder       │              │ name: 'Dishwasher' │
└────────────────────┘              │ valueType          │
                                    │ isSearchable       │
     ❌ REMOVED: AttributeSubcategory
     ✅ Denormalized categorySlug on PropertyAttribute

Schema:
@Schema()
export class PropertyAttribute {
  @Prop({ type: Types.ObjectId, ref: 'AttributeCategory', required: true })
  categoryId: Types.ObjectId;

  @Prop({ required: true })
  categorySlug: string;  // ✅ Denormalized for fast queries

  @Prop({ required: true, unique: true })
  slug: string;

  @Prop({ required: true })
  name: string;

  @Prop({ enum: ['boolean', 'text', 'number'], default: 'boolean' })
  valueType: string;

  @Prop({ default: true })
  isSearchable: boolean;

  @Prop({ default: 0 })
  displayOrder: number;
}

// Index for category queries
PropertyAttributeSchema.index({ categorySlug: 1, displayOrder: 1 });
Query Benefit:
// OLD: 3 lookups required
const category = await AttributeCategory.findOne({ slug: 'appliances' });
const subcats = await AttributeSubcategory.find({ categoryId: category._id });
const attrs = await PropertyAttribute.find({ subcategoryId: { $in: subcats.map(s => s._id) } });

// NEW: 1 lookup
const attrs = await PropertyAttribute.find({ categorySlug: 'appliances' }).sort('displayOrder');

Updated ER Diagram

┌─────────────────────────────────────────────────────────────────────────────────────┐
│                              AUTHENTICATION LAYER                                    │
├─────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                      │
│  ┌─────────────────────────────────────────────────────────────────────────────┐   │
│  │                              User                                            │   │
│  │                        (Auth + Embedded Roles)                               │   │
│  ├─────────────────────────────────────────────────────────────────────────────┤   │
│  │ email                                                                        │   │
│  │ passwordHash                                                                 │   │
│  │ googleId                                                                     │   │
│  │ status                                                                       │   │
│  │ emailVerifiedAt                                                              │   │
│  │                                                                              │   │
│  │ roles: [                                                      ◄── EMBEDDED  │   │
│  │   { role: "buyer", isPrimary: true, grantedAt: Date },                       │   │
│  │   { role: "agent", isPrimary: false, grantedAt: Date }                       │   │
│  │ ]                                                                            │   │
│  │                                                                              │   │
│  │ favoriteProperties: [ObjectId, ...]                           ◄── EMBEDDED  │   │
│  │ favoriteAgents: [ObjectId, ...]                               ◄── EMBEDDED  │   │
│  └────────────────────────────────────────────────────────────────────┬────────┘   │
│                                                                       │             │
│  ❌ REMOVED: UserRole junction collection (not needed for 1-4 roles)  │             │
│  ❌ REMOVED: Role master collection (use enum instead)                │             │
│                                                                       │             │
└───────────────────────────────────────────────────────────────────────┼─────────────┘
            │
            │ One user can have multiple role profiles
            ▼
┌─────────────────────────────────────────────────────────────────────────────────────┐
│                               PROFILE LAYER                                          │
├─────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                      │
│  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐│
│  │  BuyerProfile   │  │ SellerProfile   │  │  AgentProfile   │  │ BrokerProfile   ││
│  ├─────────────────┤  ├─────────────────┤  ├─────────────────┤  ├─────────────────┤│
│  │ userId (unique) │  │ userId (unique) │  │ userId (unique) │  │ userId (unique) ││
│  │ firstName       │  │ firstName       │  │ firstName       │  │ firstName       ││
│  │ lastName        │  │ lastName        │  │ lastName        │  │ lastName        ││
│  │ phoneNumber     │  │ phoneNumber     │  │ phoneNumber     │  │ phoneNumber     ││
│  │ preferences {}  │  │                 │  │ licenseNumber   │  │ licenseNumber   ││
│  │                 │  │                 │  │ brokerageId ────┼──┼─────────┐       ││
│  └─────────────────┘  └─────────────────┘  └─────────────────┘  └─────────┼───────┘│
│                                                                           │         │
└───────────────────────────────────────────────────────────────────────────┼─────────┘
                                                                            │
                                                                            ▼
┌─────────────────────────────────────────────────────────────────────────────────────┐
│                               BUSINESS LAYER                                         │
├─────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                      │
│  ┌─────────────────────────┐              ┌─────────────────────────┐               │
│  │       Brokerage         │              │        Property         │               │
│  ├─────────────────────────┤              ├─────────────────────────┤               │
│  │ _id                     │◄─────────────│ brokerageId             │               │
│  │ brokerProfileId ────────┼──► BrokerProfile │ ownerId ────────────┼──► User       │
│  │ companyName             │              │ agentProfileId ─────────┼──► AgentProfile
│  │ licenseNumber           │              │ listingType             │               │
│  │ location (GeoJSON)      │              │ status                  │               │
│  │ subscriptionStatus      │              │ location (GeoJSON)      │               │
│  │                         │              │ listingPrice            │               │
│  │ ❌ agents[] REMOVED     │              └────────────┬────────────┘               │
│  └─────────────────────────┘                           │                            │
│                                                        │                            │
│  Query agents: AgentProfile.find({ brokerageId })      │                            │
│                                                        ▼                            │
└─────────────────────────────────────────────────────────────────────────────────────┘
                                                         │
                                                         ▼
┌─────────────────────────────────────────────────────────────────────────────────────┐
│                          ATTRIBUTE LAYER (Simplified)                                │
├─────────────────────────────────────────────────────────────────────────────────────┤
│                                                                                      │
│  ┌────────────────────┐              ┌────────────────────┐                         │
│  │ AttributeCategory  │              │ PropertyAttribute  │                         │
│  ├────────────────────┤              ├────────────────────┤                         │
│  │ _id                │◄─────────────│ categoryId         │                         │
│  │ slug               │              │ categorySlug ◄─────┼── Denormalized          │
│  │ name               │              │ slug               │                         │
│  │ icon               │              │ name               │                         │
│  │ displayOrder       │              │ valueType          │                         │
│  └────────────────────┘              │ isSearchable       │                         │
│                                      └──────────┬─────────┘                         │
│  ❌ REMOVED: AttributeSubcategory               │                                   │
│                                                 ▼                                   │
│                                      ┌────────────────────────────┐                 │
│                                      │ PropertyAttributeValue     │                 │
│                                      ├────────────────────────────┤                 │
│                                      │ propertyId ────────────────┼──► Property     │
│                                      │ attributeId ───────────────┼──► PropertyAttribute
│                                      │ valueBoolean               │                 │
│                                      │ valueText                  │                 │
│                                      │ valueNumber                │                 │
│                                      └────────────────────────────┘                 │
│                                                                                      │
│  Indexes:                                                                           │
│  • { propertyId: 1, attributeId: 1 } unique                                         │
│  • { propertyId: 1 }                                                                │
│  • { attributeId: 1, valueBoolean: 1 }                                              │
│                                                                                      │
└─────────────────────────────────────────────────────────────────────────────────────┘


What Changed


Area
Before
After
Why


Broker-Agent
Bidirectional refs
Single ref on AgentProfile
Avoids sync issues


User roles
role field OR junction
Embedded array in User
Few roles, atomic updates


Favorites
Separate Favorite collection
Embedded arrays in User
Always fetched with user


Model naming
Mixed (Agent/AgentProfile)
Consistent "Profile" suffix
Clearer naming


Indexes
Not defined
Explicitly defined
Performance


Attribute hierarchy
3 levels
2 levels + denormalization
Fewer lookups


PropertyAttributeValue
N/A
Junction collection
Many attributes, queried separately


Pattern Summary


Relationship
Pattern
Collection/Field


User → Roles
Embed
User.roles[]


User → Favorites
Embed
User.favoriteProperties[], User.favoriteAgents[]


Agent → Brokerage
Reference
AgentProfile.brokerageId


Property → Attributes
Junction
PropertyAttributeValue collection


Chat → Participants
Embed
Chat.participants[]


Version 1.0, January 2026
Document	Model Name
MONGODB_RESTRUCTURE_PLAN.md	`AgentProfile`, `BuyerProfile`, `SellerProfile`, `BrokerProfile`
DATABASE_SCHEMA_FLOWCHART.md	`Agent`, `Broker` (no "Profile" suffix)
Use Case	Junction OK?	Why
Many-to-many (User ↔ Roles)	✅ Yes	No other clean option
Relationship has metadata (grantedAt, revokedAt)	✅ Yes	Can't store metadata in arrays
High-cardinality (1000s of attributes per property)	✅ Yes	Arrays don't scale past ~1000 items
Low-cardinality (user has 2-3 roles)	⚠️ Consider embedding	Array in User doc is simpler
Frequently queried together	⚠️ Consider embedding	Avoids $lookup overhead
Independently queried	✅ Yes	Junction allows flexible queries
Collection	Recommended Pattern	Reason
UserRole	Embed as array in User	Users have 1-4 roles max
PropertyAttributeValue	Junction collection	Properties have 50+ attributes
AgentBrokerageAssignment	Single ref on AgentProfile	Simple 1:N relationship
User.favoriteAgents	Embed as array	Typical user has <50 favorites
Chat.participants	Embed as array	Always 2-10 participants
Pattern	Read Performance	Write Performance	Consistency
Embedded Array	⚡ Fast (single doc)	⚡ Fast (single update)	✅ Atomic
Junction Collection	🐢 Slower ($lookup)	🐢 Slower (multi-doc)	⚠️ Eventual
Bidirectional Refs	🐢 Slower	🐌 Slowest (2 updates)	❌ Risk of inconsistency
Old (Inconsistent)	New (Unified)	Purpose
Agent / AgentProfile	`AgentProfile`	Professional agent data
Broker / BrokerProfile	`BrokerProfile`	Professional broker data
User	`User`	Authentication only
RealEstateOffice	`Brokerage`	Company entity
Area	Before	After	Why
Broker-Agent	Bidirectional refs	Single ref on AgentProfile	Avoids sync issues
User roles	`role` field OR junction	Embedded array in User	Few roles, atomic updates
Favorites	Separate Favorite collection	Embedded arrays in User	Always fetched with user
Model naming	Mixed (Agent/AgentProfile)	Consistent "Profile" suffix	Clearer naming
Indexes	Not defined	Explicitly defined	Performance
Attribute hierarchy	3 levels	2 levels + denormalization	Fewer lookups
PropertyAttributeValue	N/A	Junction collection	Many attributes, queried separately
Relationship	Pattern	Collection/Field
User → Roles	Embed	`User.roles[]`
User → Favorites	Embed	`User.favoriteProperties[]`, `User.favoriteAgents[]`
Agent → Brokerage	Reference	`AgentProfile.brokerageId`
Property → Attributes	Junction	`PropertyAttributeValue` collection
Chat → Participants	Embed	`Chat.participants[]`