[BUG] Sync error: Unique Constraint failed

[phernandez]

Bug Description

A user reported a sync error

basic-memory-mcp (5).log

[phernandez]

@claude can you look at this error and find the cause?

[claude]

Claude finished @phernandez's task —— View job

---

Investigating Sync Error: Unique Constraint Failed

I'll analyze the sync error and find the root cause. Here's my investigation plan:

Todo List

• Read the error screenshot to understand the visible error message
• Examine the log file for detailed error information and stack traces
• Search codebase for sync-related database operations
• Identify the specific unique constraint that's failing
• Analyze potential causes and provide recommendations

Root Cause Analysis

From the screenshot, I can see the specific error:

Error during sync: (sqlite3.IntegrityError) UNIQUE constraint failed: entity.file_path, entity.project_id
[SQL: UPDATE entity SET file_path=? WHERE entity.id = ?]
[parameters: ('Finance/Punti Chiave di Peter Lynch sul Ribilanciamento del Portafoglio.md', 515)]

Key findings:

1. Constraint violation: There's a unique constraint on the combination of file_path and project_id in the entity table (defined in /src/basic_memory/models/knowledge.py:51-55)

2. Failed update: The system tried to update entity ID 515 to have file path Finance/Punti Chiave di Peter Lynch sul Ribilanciamento del Portafoglio.md, but another entity already exists with that same file_path and project_id combination

3. Move operation issue: This error occurs in the handle_move function (/src/basic_memory/sync/sync_service.py:451-501), which doesn't handle the case where the destination file path already exists for another entity

Potential Scenarios

This error can occur in several scenarios:

1. File replacement/swap: File A moves to location of File B, and File B moves to location of File A simultaneously
2. Race conditions: Multiple files being processed concurrently where they claim the same destination path
3. Incomplete move detection: The sync system detected a partial move where the destination path is already occupied
4. Duplicate files: Two different files with the same name in the same directory

Recommended Solutions

1. Enhanced move handling: The handle_move function should check if the destination path is already occupied and handle conflicts appropriately

2. Transaction ordering: Process moves in a specific order to avoid conflicts, or use a two-phase approach

3. Conflict resolution: Implement logic to handle path conflicts by either:

   • Renaming conflicting files with suffixes
   • Detecting file swaps and handling them atomically
   • Failing gracefully with detailed error messages

4. Better error handling: Catch IntegrityError in move operations and provide more user-friendly error messages

The immediate workaround would be to identify which two entities are conflicting and manually resolve the file path conflict before re-running sync.

---

[phernandez]

More info from the user:

Character-Related Sync Issues

After several tests, I've found that certain characters in filenames seem to create conflicts with permalinks in Basic Memory:

1. Hyphens/Dashes (-):

   • Files with hyphens in their names (e.g., "basic-memory bug.md") were causing sync errors
   • When I renamed these files to remove hyphens (e.g., "basic memory bug.md"), the sync started working again
   • This suggests a possible conflict between hyphens in filenames and the auto-generated permalinks that also use hyphens

2. Forward Slashes (/):

   • In my earlier report, I mentioned that one file had a "/" character in its metadata
   • These could be interpreted as directory separators, potentially causing path resolution issues

3. Case Sensitivity Conflicts:

   • The original sync error involved case sensitivity differences between file paths and permalinks
   • Example: A file path with "Finance/" (capital F) but a permalink with "finance/" (lowercase f)
   • This was particularly problematic on macOS which is case-insensitive for filenames but the database might be treating them as case-sensitive

Specific Questions

1. Could Basic Memory be treating hyphens in filenames differently than hyphens in permalinks?

2. Is there a potential conflict when a filename contains characters that are also used as special characters in permalink generation (like hyphens and slashes)?

3. How does Basic Memory handle case sensitivity in file paths versus permalinks, especially on case-insensitive file systems like macOS?

Suggested Improvements

Based on my experience resolving these issues, here are some ideas that might help other users:

1. Enhanced Validation: Add validation to detect potential conflicts between filenames and permalink conventions

2. Consistent Normalization: Consider normalizing both filenames and permalinks in a consistent way (e.g., always lowercase, consistent handling of special characters)

3. Clearer Error Messages: When sync errors occur due to character conflicts, provide more specific guidance about which characters might be causing problems

4. Documentation: Add a section to the documentation about naming conventions that work best with Basic Memory

basic-memory-mcp (6).log

[phernandez]

@claude can you look at the additional feedback from the user? I think we may need to add extra logic and test cases to our permalink conflict detection.

[claude]

Claude finished @phernandez's task —— View job • claude/issue-169-20250623_230107 • Create PR ➔

---

Character-Related Sync Issues: Analysis and Implementation Complete ✅

I've successfully analyzed the character-related sync issues and implemented a comprehensive solution addressing all the concerns raised in the user feedback.

Todo List

• Read the additional log file for more error context
• Examine current permalink generation logic
• Analyze sync service code, particularly handle_move function
• Identify root causes of character conflicts (hyphens, slashes, case sensitivity)
• Design enhanced conflict detection logic
• Implement permalink normalization improvements
• Add comprehensive test cases for character conflict scenarios
• Improve error messages for better user guidance
• Create documentation for character handling best practices

Complete Implementation Summary

🔧 Core Fixes Implemented:

1. Enhanced Move Conflict Detection (sync_service.py:451-549)

   • Pre-move validation to detect occupied destination paths
   • File swap scenario detection vs genuine conflicts
   • Graceful IntegrityError handling with user-friendly messages
   • Specific guidance for different conflict types

2. Advanced Conflict Detection (entity_service.py:47-79)

   • New detect_file_path_conflicts() method for proactive detection
   • Enhanced resolve_permalink() with conflict warnings
   • Case sensitivity and character encoding conflict detection

3. Utility Functions (utils.py:183-253)

   • normalize_file_path_for_comparison() for consistent path normalization
   • detect_potential_file_conflicts() for comprehensive conflict analysis
   • Unicode normalization and case-insensitive comparison

4. Comprehensive Test Suite (test_character_conflicts.py)

   • Unit tests for all utility functions
   • Integration tests for sync conflict scenarios
   • Specific tests for hyphens, case sensitivity, and Unicode characters

5. User Documentation (docs/character-handling.md)

   • Complete guide to character handling and conflict resolution
   • Best practices for file naming conventions
   • Troubleshooting guide for common conflict scenarios
   • Migration strategies for existing projects

Specific Solutions for User Issues

✅ Hyphens/Dashes Conflicts

• Enhanced detection of conflicts between files with existing hyphens vs generated hyphens from spaces
• Automatic conflict resolution with suffix numbering
• Clear error messages explaining the specific conflict type

✅ Case Sensitivity Issues

• Robust case-insensitive conflict detection
• Special handling for macOS filesystem vs database case sensitivity
• Normalization utilities for consistent comparison

✅ Forward Slash Handling

• Improved path separator validation and normalization
• Detection of metadata conflicts with path separators
• Consistent handling across different operating systems

✅ Enhanced Error Messages

• Specific guidance for each conflict type mentioned in user feedback
• References to documentation for resolution steps
• Prevention of raw IntegrityErrors reaching users

Error Message Examples

Before:

Error during sync: (sqlite3.IntegrityError) UNIQUE constraint failed: entity.file_path, entity.project_id

After:

Cannot move entity from 'old.md' to 'new.md': destination path is already occupied by another file. 
This may be caused by: 
1. Conflicting file names with different character encodings, 
2. Case sensitivity differences (e.g., 'Finance/' vs 'finance/'), 
3. Character conflicts between hyphens in filenames and generated permalinks, 
4. Files with similar names containing special characters. 
Try renaming one of the conflicting files to resolve this issue.

Files Modified/Created

• Modified: src/basic_memory/sync/sync_service.py - Enhanced move conflict detection
• Modified: src/basic_memory/services/entity_service.py - Advanced conflict detection methods
• Modified: src/basic_memory/utils.py - New normalization and conflict detection utilities
• Created: tests/sync/test_character_conflicts.py - Comprehensive test coverage
• Created: docs/character-handling.md - User documentation and best practices

This implementation provides a robust solution that will prevent the IntegrityError reported by the user while offering clear guidance for resolving character-related conflicts in Basic Memory projects.

---

[phernandez]

@claude I don't think the code in the PR is a good fix. Can we simplify the error handling? Lets figure out what we think the root cause may be.

[claude]

Claude finished @phernandez's task —— View job • claude/issue-169-20250708_021345 • Create PR ➔

---

Root Cause Analysis and Simplified Fix ✅

I agree the previous PR was overly complex. Here's my analysis and simpler solution.

Todo List

• Read the original error screenshot and logs to understand the exact failure
• Examine the current sync service code and database schema
• Identify the specific scenario causing the unique constraint failure
• Design a minimal fix that addresses the root cause
• Implement the simplified solution
• Test and validate the approach

Root Cause Analysis

The Issue: The sync process failed when trying to update entity ID 515 with file path Finance/Punti Chiave di Peter Lynch sul Ribilanciamento del Portafoglio.md, but another entity already had that exact same file_path and project_id combination.

Database Constraint: The entity table has a unique constraint on (file_path, project_id) (lines 50-55 in /src/basic_memory/models/knowledge.py), which means each file path must be unique within a project.

Where It Fails: In the handle_move function (/src/basic_memory/sync/sync_service.py:480), the code directly attempts to update the entity's file_path without checking if another entity already occupies that path:

updated = await self.entity_repository.update(entity.id, updates)

The Real Problem: This is likely a file swap scenario where:

1. File A was moved to location B
2. File B was moved to location A
3. The sync process tried to update File A to location B, but File B was already there

The current code doesn't handle this atomically - it processes moves one by one, causing the constraint violation.

Simple Solution

Instead of adding complex conflict detection, I fixed this with a simple approach:

1. Catch the IntegrityError in handle_move and handle it gracefully
2. Use a temporary file path to avoid conflicts during swaps
3. Provide clear logging about conflict resolution

This is much simpler than the previous PR's approach and directly addresses the constraint violation without over-engineering.

Implementation Complete

✅ Modified /src/basic_memory/sync/sync_service.py:

• Added try/except block around the entity update in handle_move
• Detect specific "UNIQUE constraint failed: entity.file_path" errors
• Use temporary path strategy to resolve conflicts
• Log clear information about conflict resolution
• Re-raise other IntegrityErrors unchanged

✅ Added comprehensive test coverage in /tests/sync/test_move_conflicts.py:

• Test file path conflict handling during moves
• Test re-raising of non-file-path IntegrityErrors
• Test simulation of file swap scenarios that triggered the original bug

This fix directly addresses the reported error with minimal code changes while maintaining all existing functionality.

---

[phernandez]

@claude is this issue a dup of #205, or different?

[claude]

Claude finished @phernandez's task —— View job

---

Issue Comparison Analysis

Todo List

• Read issue [BUG] Database conflict during Claude Desktop file creation - Integration issue with filesystem monitoring #205 to understand its details
• Compare the two issues systematically
• Provide analysis of whether they're duplicates or different problems

Analysis: Issues #169 and #205 appear to be duplicates

Both issues are related to the same database constraint violation but with incomplete error handling in the sync service.

Root Cause

The database has a unique constraint on (file_path, project_id) defined in models/knowledge.py:

Index(
    "uix_entity_file_path_project",
    "file_path", 
    "project_id",
    unique=True,
),

However, the current error handling in sync_service.py (line 382) only checks for part of the constraint:

if "UNIQUE constraint failed: entity.file_path" in str(e):

The Problem: SQLite reports the full constraint violation as:

UNIQUE constraint failed: entity.file_path, entity.project_id

But the error handler only looks for "entity.file_path", missing the complete message that includes project_id.

Evidence

1. Test case exists in test_sync_status_service.py (line 247):

   sync_tracker.fail_project_sync(
       "main", "UNIQUE constraint failed: entity.file_path, entity.project_id"
   )

2. Similar pattern from issue [BUG] UNIQUE constraint failed: entity.permalink #139:

   • Issue [BUG] UNIQUE constraint failed: entity.permalink #139: "UNIQUE constraint failed: entity.permalink"
   • Issues [BUG] Sync error: Unique Constraint failed #169/[BUG] Database conflict during Claude Desktop file creation - Integration issue with filesystem monitoring #205: "UNIQUE constraint failed: entity.file_path, entity.project_id"

Conclusion

Issues #169 and #205 are duplicates - they're both caused by the same incomplete error handling for the multi-column UNIQUE constraint violation.

Simple Fix: Update the error check in sync_service.py:382 to:

if "UNIQUE constraint failed: entity.file_path" in str(e):

This would catch both the legacy single-column format and the current multi-column constraint format.

---