🔑 Add Unique Field Constraints in Schema Builder

[yash-pouranik]

📋 Description

Currently, when users create collection schemas in urBackend, there's no way to mark fields as unique. This means users can't enforce critical constraints like:

• Unique email addresses
• Unique usernames
• Unique product SKUs
• Unique order numbers

This leads to data integrity issues where duplicate values can be inserted, breaking business logic.

Example Problem:

// User creates "users" collection with "email" field
// Current behavior: Can insert duplicate emails ❌
{ email: "john@example.com" }
{ email: "john@example.com" } // This should be rejected but isn't!

This feature will add unique field constraints to the schema builder, with MongoDB unique indexes enforced at the database level.

---

🎯 Problem Statement

Current Behavior:

• Schema builder has field types (String, Number, Boolean, Date, Object, Array)
• No validation for unique values
• Users can insert duplicate data without errors ❌

User Story:

"As a developer building a user authentication system, I want to ensure email addresses are unique so that each user has a distinct account."

Desired Behavior:

1. User creates "email" field in schema
2. Marks it as "unique" via checkbox ✅
3. MongoDB creates unique index on that field
4. If duplicate value is inserted, API returns 409 error with clear message
5. Existing data is validated when adding unique constraint

---

💡 Proposed Solution

Implement unique field constraints with:

1. Schema definition updates - add unique: Boolean property to field schema
2. MongoDB unique indexes - automatically create indexes when schema is saved
3. Validation on insert/update - return clear errors for duplicates (409 Conflict)
4. Frontend UI - add "Unique" checkbox in schema builder
5. Migration handling - check existing data before adding unique constraint
6. Composite unique constraints (bonus) - multiple fields together unique

---

✅ Acceptance Criteria

Backend Requirements

• Schema Model Update

  • Add unique property to field definitions in schema
  • Schema structure:

    fields: [
      {
        name: "email",
        type: "String",
        required: true,
        unique: true,  // ← NEW
        default: null
      }
    ]

• Unique Index Creation

  • When schema is saved with unique fields, create MongoDB unique indexes
  • Use Model.collection.createIndex({ fieldName: 1 }, { unique: true })
  • Handle index creation errors gracefully

• Index Removal

  • When unique constraint is removed from field, drop the index
  • Use Model.collection.dropIndex('fieldName_1')

• Duplicate Value Detection

  • Before adding unique constraint, check if existing data has duplicates
  • If duplicates exist, return error with count:

    {
      "success": false,
      "error": "Cannot add unique constraint: 3 duplicate values found for field 'email'"
    }

• Insert/Update Validation

  • Catch MongoDB duplicate key errors (code 11000)
  • Return 409 Conflict with user-friendly message:

    {
      "success": false,
      "error": "Value 'john@example.com' already exists for field 'email'",
      "code": "DUPLICATE_VALUE"
    }

• Case Sensitivity Handling

  • Add option for case-insensitive unique (e.g., "John@example.com" = "john@example.com")
  • Use collation: { locale: 'en', strength: 2 }

• Composite Unique Constraints (Optional)

  • Allow multiple fields to be unique together
  • Example: firstName + lastName unique together
  • Schema structure:

    uniqueConstraints: [
      { fields: ["firstName", "lastName"], name: "unique_full_name" }
    ]

Frontend Requirements

• Schema Builder UI Updates

  • Add "Unique" checkbox next to each field in schema builder
  • Position: After "Required" checkbox
  • Show tooltip: "Prevent duplicate values for this field"

• Visual Indicators

  • Show 🔑 icon next to unique fields in schema list
  • Display "Unique" badge in field properties

• Validation Feedback

  • When adding unique constraint, show loading state: "Checking for duplicates..."
  • If duplicates found, show error with details
  • Allow user to view duplicate records before fixing

• Case Sensitivity Toggle (Optional)

  • Add checkbox: "Case insensitive" (only shown when unique is checked)
  • Example: "john@example.com" treated same as "JOHN@EXAMPLE.COM"

• Error Display

  • When insert/update fails due to duplicate, show clear error
  • Highlight the field that caused the duplicate
  • Example: "Email 'john@example.com' already exists"

Database & Migration

• Index Management

  • List all unique indexes for a collection via endpoint
  • Allow viewing index status in frontend

• Existing Schema Migration

  • For collections already created, allow adding unique constraint
  • Validate existing data first
  • Show warning: "This will check X existing records for duplicates"

Testing

• Unit Tests

  • Test unique index creation
  • Test duplicate value rejection
  • Test case-insensitive unique
  • Test index removal

• Integration Tests

  • Create schema with unique field → insert duplicate → verify 409 error
  • Add unique constraint to existing schema with duplicates → verify error
  • Remove unique constraint → verify duplicates now allowed

---

🛠️ Implementation Guide

High-Level Approach

Step 1: Backend - Schema Model

• Add unique: Boolean field to your schema definition
• Consider adding uniqueOptions for advanced features (case sensitivity, etc.)

Step 2: Backend - Index Management

• Create utility to manage MongoDB indexes
• Key functions needed:
  • Create unique index on a field
  • Drop unique index from a field
  • Check if data has duplicates before creating index

Step 3: Backend - Schema Controller

• When schema is saved/updated, check which fields are marked unique
• Before creating unique index, scan existing data for duplicates
• If duplicates found, return error
• If clean, create the unique index using MongoDB's createIndex()

Step 4: Backend - Data Controller

• Catch MongoDB duplicate key errors (error code 11000)
• Return user-friendly 409 error with field name and value

Step 5: Frontend - Schema Builder

• Add checkbox for "Unique" next to each field
• Optionally add "Case insensitive" toggle for String fields

Step 6: Frontend - Error Handling

• Display duplicate errors clearly when insert/update fails
• Show which field and value caused the duplicate

---

💡 Implementation Hints

Backend Hints

Creating Unique Index:

// MongoDB collection method to create index
Model.collection.createIndex(
  { fieldName: 1 },
  { unique: true, sparse: true }
);

Detecting Duplicates (Aggregation):

// Use $group to find duplicate values
Model.aggregate([
  { $group: { _id: '$fieldName', count: { $sum: 1 } } },
  { $match: { count: { $gt: 1 } } }
]);

Catching MongoDB Duplicate Error:

// MongoDB throws error with code 11000 for duplicates
if (error.code === 11000) {
  // Extract field from error.keyPattern
  // Extract value from error.keyValue
}

Case Insensitive Unique:

// Use collation with strength: 2
{ collation: { locale: 'en', strength: 2 } }

Frontend Hints

Schema Builder UI:

• Add checkbox similar to "Required" checkbox
• Show/hide case-insensitive option based on field type (String only)

Displaying Unique Fields:

• Add 🔑 icon or badge next to field name
• Show in field list/table view

Error Handling:

• Check response status code (409 for duplicates)
• Extract field name and value from error response
• Show toast/alert with clear message

---

📂 Files You'll Need to Modify

Backend

• backend/models/Schema.js - Add unique field to schema
• backend/utils/indexManager.js - NEW FILE (create/drop indexes)
• backend/controllers/schema.controller.js - Index creation logic
• backend/controllers/data.controller.js - Error handling for duplicates

Frontend

• frontend/src/components/SchemaBuilder.jsx - Add unique checkbox
• frontend/src/components/SchemaViewer.jsx - Display unique badge
• frontend/src/pages/Database.jsx - Handle duplicate errors

---

🔍 Key Concepts to Research

1. MongoDB Unique Indexes - How they work, when they're created
2. Sparse Indexes - Allow multiple null values for non-required fields
3. MongoDB Error Code 11000 - Duplicate key error
4. Aggregation Pipeline - For finding duplicates
5. Collations - For case-insensitive comparisons
6. Index Naming - MongoDB names indexes as fieldName_1

---

📊 Testing Checklist

Manual Testing

• Create Unique Field

  • Create new schema with email field marked as unique
  • Insert record: { email: "test@example.com" }
  • Try inserting duplicate → verify 409 error
  • Verify error message mentions field name and value

• Case Insensitive Unique

  • Mark field as unique + case insensitive
  • Insert: { email: "Test@Example.com" }
  • Try insert: { email: "test@example.com" } → should fail

• Add Unique to Existing Schema

  • Create schema without unique constraint
  • Insert 2 records with duplicate emails
  • Try to add unique constraint → should show error with duplicate count
  • Fix duplicates, then add constraint → should succeed

• Remove Unique Constraint

  • Remove unique checkbox from field
  • Verify duplicates are now allowed

• Null/Empty Values

  • Non-required unique field
  • Insert multiple records with null/undefined value
  • Should succeed (sparse index allows multiple nulls)

• Update with Duplicate

  • Record 1: { email: "a@example.com" }
  • Record 2: { email: "b@example.com" }
  • Try update Record 2 to { email: "a@example.com" } → should fail

Automated Tests

describe('Unique Field Constraints', () => {
  test('rejects duplicate values', async () => {
    const schema = {
      fields: [
        { name: 'email', type: 'String', unique: true }
      ]
    };
    
    // Create schema
    await createSchema(schema);
    
    // Insert first record
    await insertData({ email: 'test@example.com' });
    
    // Try duplicate
    const response = await insertData({ email: 'test@example.com' });
    expect(response.status).toBe(409);
    expect(response.body.code).toBe('DUPLICATE_VALUE');
  });
  
  test('prevents adding unique constraint with existing duplicates', async () => {
    // Insert duplicates first
    await insertData({ email: 'test@example.com' });
    await insertData({ email: 'test@example.com' });
    
    // Try to add unique constraint
    const response = await updateSchema({
      fields: [{ name: 'email', type: 'String', unique: true }]
    });
    
    expect(response.status).toBe(400);
    expect(response.body.error).toContain('duplicate values found');
  });
  
  test('case insensitive unique works', async () => {
    const schema = {
      fields: [{
        name: 'username',
        type: 'String',
        unique: true,
        uniqueOptions: { caseInsensitive: true }
      }]
    };
    
    await createSchema(schema);
    await insertData({ username: 'JohnDoe' });
    
    const response = await insertData({ username: 'johndoe' });
    expect(response.status).toBe(409);
  });
});

---

🎓 Learning Outcomes

By completing this issue, you will learn:

• ✅ MongoDB unique indexes and their behavior
• ✅ Database constraints vs application-level validation
• ✅ Index management (create, drop, list)
• ✅ Duplicate detection using aggregation pipelines
• ✅ Error code handling (MongoDB error codes)
• ✅ Sparse indexes for optional unique fields
• ✅ Collations for case-insensitive comparisons
• ✅ Schema migrations with data validation
• ✅ UX for constraint errors (clear messaging)

---

🏷️ Labels

• enhancement
• intermediate ✅
• backend
• frontend
• database
• schema
• help wanted

---

📚 Resources

Existing Code to Reference:

• Schema model: backend/models/Schema.js
• Schema controller: backend/controllers/schema.controller.js
• Data controller: backend/controllers/data.controller.js
• Model injection: backend/utils/injectModel.js
• Frontend schema builder: frontend/src/components/SchemaBuilder.jsx

MongoDB Documentation:

• Unique indexes: https://www.mongodb.com/docs/manual/core/index-unique/
• Sparse indexes: https://www.mongodb.com/docs/manual/core/index-sparse/
• Collations: https://www.mongodb.com/docs/manual/reference/collation/
• Error codes: https://github.com/mongodb/mongo/blob/master/src/mongo/base/error_codes.yml

Similar Patterns:

• Mongoose unique: https://mongoosejs.com/docs/validation.html#the-unique-option-is-not-a-validator

---

💡 Edge Cases to Handle

• Multiple unique constraints on same collection
• Unique constraint on nested object fields (e.g., address.zipCode)
• Unique constraint on array fields (each element unique vs array unique)
• Compound unique indexes (multiple fields together)
• Renaming a field that has unique constraint
• Changing field type that has unique constraint
• BYOD (external databases) - index management

---

🚀 Estimated Time

Time to complete: 4-5 hours (for intermediate developer)

Breakdown:

• Index manager utility: 1 hour
• Backend schema/data controller updates: 1.5 hours
• Frontend UI (checkbox + error handling): 1 hour
• Duplicate checking logic: 45 minutes
• Testing: 45 minutes

---

👥 Assignee

Looking for an intermediate contributor who has:

• Good understanding of MongoDB indexes
• Experience with Mongoose schemas
• Understanding of database constraints
• Comfortable with aggregation pipelines
• Error handling experience
• Frontend form validation experience

Difficulty Level: ⭐⭐⭐ Intermediate (requires database knowledge + full-stack)

---

💬 Implementation Tips

1. Start with backend: Get index creation working before touching frontend
2. Test with Postman first: Create schema with unique field, try inserting duplicates
3. MongoDB shell is your friend: Play with indexes manually to understand behavior
4. Sparse indexes matter: Research when to use them (non-required unique fields)
5. Error code 11000: This is the key to catching duplicates - look it up!
6. Aggregation practice: Test duplicate detection query separately in MongoDB Compass
7. Frontend is simple: Just a checkbox + error display, backend is the real work
8. Don't forget cleanup: When removing unique constraint, drop the index!

---

🚧 Common Pitfalls to Avoid

• ❌ Forgetting to use sparse indexes for optional fields
• ❌ Not checking for duplicates before creating index (will fail)
• ❌ Not dropping index when unique constraint is removed
• ❌ Poor error messages (tell user which field + value is duplicate)
• ❌ Not handling case where index already exists
• ❌ Hardcoding index names instead of using MongoDB's naming convention

---

Ready to add data integrity? 🔑 Drop a comment and let's make schemas powerful!

[Special7ka]

Hi! I’d like to work on this issue.

I’m planning to start with the backend part:

• add support for unique fields in schema
• create MongoDB unique indexes
• handle duplicate key errors (code 11000) and return 409

I’ll skip frontend and advanced features (like composite constraints) for now.

Does that scope sound good?

[yash-pouranik]

Hi! @Special7ka Thanks for picking this up - really appreciate it 🙌

Yes, that scope sounds perfect to start with. Focusing on the backend core (schema support, index creation, and duplicate handling with proper 409 responses) is exactly the right approach.

A couple of things to keep in mind while implementing:

Please ensure we check for existing duplicates before creating the unique index

Use sparse indexes for non-required fields

Handle index creation/removal cleanly to avoid inconsistencies

Frontend and advanced features (like composite constraints) can definitely come in follow-up PRs.

Feel free to ask if you get stuck anywhere. Looking forward to your PR 🚀