MCP Memory Service - TypeScript

# NULL ID Cleanup Guide **Version**: 1.0 **Last Updated**: 2025-10-14 **Status**: ✅ Production Ready --- ## Overview This guide documents the NULL ID cleanup process for the MCP Memory TypeScript project. The database has been fully cleaned of all NULL IDs as of 2025-10-14. ### Background LibSQL/Turso has a quirk where NULL IDs can sometimes be inserted into the database, particularly after bulk operations or cleanup scripts. These NULL IDs can cause UNIQUE constraint violations and other issues. --- ## Current Status **Last Cleanup**: 2025-10-14 **Results**: ✅ 100% SUCCESS | Table | NULL IDs Found | NULL IDs Fixed | Status | |-------|----------------|----------------|--------| | **Entities** | 11 | 11 | ✅ Clean | | **Memories** | 41 | 41 | ✅ Clean | | **Interactions** | 0 | 0 | ✅ Clean | | **TOTAL** | **52** | **52** | ✅ **100% Fixed** | --- ## Quick Commands ### Check for NULL IDs ```bash # Comprehensive check (all tables) npm run check-null-ids:all # Legacy check (memories only) npm run check-null-ids ``` ### Fix NULL IDs ```bash # Fix entities table npm run fix-null-ids:entities # Execute npm run fix-null-ids:entities -- --dry-run # Preview only # Fix memories table npm run fix-null-ids:memories # Execute npm run fix-null-ids:memories -- --dry-run # Preview only # Legacy fix (original script) npm run fix-null-ids # Execute npm run fix-null-ids -- --dry-run # Preview only ``` --- ## Detailed Process ### Step 1: Check for NULL IDs Run the comprehensive checker to identify any NULL IDs: ```bash npm run check-null-ids:all ``` **Expected Output (Clean Database)**: ``` ═══════════════════════════════════════════════════════ 🔍 MCP Memory - Comprehensive NULL ID Check ═══════════════════════════════════════════════════════ 📊 NULL ID Status: ─────────────────────────────────────────────────────── Entities: 0 NULL IDs ✅ Memories: 0 NULL IDs ✅ Interactions: 0 NULL IDs ✅ ─────────────────────────────────────────────────────── Total: 0 NULL IDs ✅ PERFECT! No NULL IDs found in any table! 🎉 Database is production ready! ``` **If NULL IDs Found**: ``` ⚠️ WARNING: Found X total NULL IDs! Recommended Actions: - Run: npx tsx scripts/fix-null-ids-entities.ts - Run: npx tsx scripts/fix-null-ids-v2.ts ``` ### Step 2: Fix Entities (If Needed) If entities have NULL IDs: ```bash # Preview changes first npm run fix-null-ids:entities -- --dry-run # Review the output, then execute npm run fix-null-ids:entities ``` **Process**: 1. Loads all existing entity IDs to prevent duplicates 2. Identifies all NULL ID records using ROWID 3. Generates unique UUIDs for each record 4. Updates records individually with error handling 5. Verifies all NULL IDs are fixed ### Step 3: Fix Memories (If Needed) If memories have NULL IDs: ```bash # Preview changes first npm run fix-null-ids:memories -- --dry-run # Review the output, then execute npm run fix-null-ids:memories ``` **Process**: 1. Loads all existing memory IDs to prevent duplicates 2. Identifies all NULL ID records using ROWID 3. Generates unique UUIDs for each record 4. Updates in batches with progress reporting 5. Verifies all NULL IDs are fixed ### Step 4: Verify Fix After running fixes, verify all NULL IDs are resolved: ```bash npm run check-null-ids:all ``` Should show **0 NULL IDs** across all tables. --- ## Scripts Documentation ### Check Scripts #### `scripts/check-null-ids-comprehensive.ts` - **Purpose**: Check for NULL IDs across all tables - **Tables Checked**: entities, memories, interactions - **Features**: - Counts NULL IDs per table - Shows database statistics - Samples NULL records if found - Exit code 0 if clean, 1 if NULL IDs found - **NPM Command**: `npm run check-null-ids:all` #### `scripts/check-null-ids.ts` (Legacy) - **Purpose**: Check for NULL IDs in memories table only - **NPM Command**: `npm run check-null-ids` ### Fix Scripts #### `scripts/fix-null-ids-entities.ts` - **Purpose**: Fix NULL IDs in entities table - **Method**: ROWID-based targeting - **Features**: - Dry run mode supported - Duplicate prevention - Progress reporting - Backup recommendations - Post-fix verification - **NPM Command**: `npm run fix-null-ids:entities` - **Created**: 2025-10-14 (during cleanup) #### `scripts/fix-null-ids-v2.ts` - **Purpose**: Fix NULL IDs in memories table - **Method**: ROWID-based targeting - **Features**: - Dry run mode supported - Duplicate prevention - Progress reporting (every 5 records) - Backup recommendations - Post-fix verification - **NPM Command**: `npm run fix-null-ids:memories` #### `scripts/fix-null-ids.ts` (Legacy) - **Purpose**: Original NULL ID fix script - **NPM Command**: `npm run fix-null-ids` --- ## Technical Details ### ROWID Targeting Strategy Both fix scripts use SQLite's ROWID for precise record targeting: ```typescript // Instead of this (can cause UNIQUE constraint violations): UPDATE table SET id = ? WHERE id IS NULL // We use this (guaranteed unique targeting): UPDATE table SET id = ? WHERE rowid = ? AND id IS NULL ``` **Benefits**: - ROWID is always unique (even for NULL IDs) - No UNIQUE constraint violations - Precise record targeting - Safe for concurrent operations ### UUID Generation UUIDs are generated using Node's `crypto.randomUUID()`: ```typescript private generateUniqueId(): string { let newId: string; do { newId = randomUUID(); } while (this.existingIds.has(newId)); this.existingIds.add(newId); return newId; } ``` **Features**: - Cryptographically secure UUIDs - Duplicate checking against existing IDs - Collision prevention ### Safety Measures All fix scripts implement multiple safety measures: 1. **Dry Run Mode**: Preview changes before execution 2. **Backup Recommendations**: Suggests backup commands with timestamps 3. **Duplicate Prevention**: Loads existing IDs before generating new ones 4. **Error Handling**: Try/catch blocks with detailed error reporting 5. **Progress Tracking**: Regular progress updates during execution 6. **Verification**: Post-execution counts to confirm success --- ## Troubleshooting ### NULL IDs Reappear After Cleanup **Possible Causes**: 1. Cleanup scripts or migrations not using proper ID generation 2. Direct database manipulation bypassing application layer 3. Concurrent inserts during migration **Solutions**: 1. Review recent cleanup/migration scripts 2. Ensure all new records use `randomUUID()` for IDs 3. Add NOT NULL constraint to ID columns (after full cleanup) ### UNIQUE Constraint Violations During Fix **Cause**: Multiple NULL IDs being treated as identical by SQLite **Solution**: Use ROWID-based targeting (already implemented in v2 scripts) ### Dry Run Shows Different Count Than Actual Run **Cause**: Database changes between dry run and actual execution **Solution**: Run fix immediately after dry run, or lock database during operation --- ## Prevention ### Best Practices 1. **Always Use UUID Generation**: ```typescript import { randomUUID } from 'crypto'; const id = randomUUID(); ``` 2. **Validate Before Insert**: ```typescript if (!record.id) { record.id = randomUUID(); } ``` 3. **Use Transactions**: ```typescript await db.execute('BEGIN TRANSACTION'); try { // ... operations ... await db.execute('COMMIT'); } catch (error) { await db.execute('ROLLBACK'); } ``` 4. **Add Schema Constraints** (Future): ```sql ALTER TABLE entities ADD CONSTRAINT check_id_not_null CHECK (id IS NOT NULL); ``` ### Monitoring Run periodic checks: ```bash # Add to cron or CI/CD pipeline npm run check-null-ids:all ``` Set up alerts if NULL IDs are detected. --- ## Cleanup History ### 2025-10-14: Full NULL ID Cleanup - **NULL IDs Found**: 52 (11 entities + 41 memories) - **Method**: ROWID-based UUID generation - **Result**: 100% success, 0 NULL IDs remaining - **Scripts Created**: - `scripts/fix-null-ids-entities.ts` - `scripts/check-null-ids-comprehensive.ts` - **Documentation Created**: - `NULL_ID_FIX_FINAL_REPORT.md` - `docs/testing/NULL_ID_CLEANUP_GUIDE.md` --- ## Related Documentation - [NULL_ID_FIX_FINAL_REPORT.md](/Users/masa/Projects/mcp-memory-ts/NULL_ID_FIX_FINAL_REPORT.md) - Detailed cleanup report - [CLEANUP_INDEX.md](/Users/masa/Projects/mcp-memory-ts/docs/testing/CLEANUP_INDEX.md) - Index of all cleanup operations - [DATABASE_SCHEMA_ANALYSIS.md](/Users/masa/Projects/mcp-memory-ts/docs/schema/DATABASE_SCHEMA_ANALYSIS.md) - Database schema documentation --- ## Support For issues or questions: 1. Check this guide first 2. Review the NULL_ID_FIX_FINAL_REPORT.md 3. Run comprehensive checker: `npm run check-null-ids:all` 4. Check logs for error details 5. Create issue with full error output --- **Maintained By**: MCP Memory Team **Status**: Active Maintenance **Next Review**: After any major cleanup or migration operation