[BUG] Errors on project reset

[ryanstraight]

Bug Description

Large number of errors on running basic-memory reset

Steps To Reproduce

Steps to reproduce the behavior:

1. Install version '0.10.1'
2. Run command 'basic-memory reset'
3. Using Powershell
4. See error

Expected Behavior

Emptied database.

Actual Behavior

Reset doesn't seem to occur, and the following errors (full log of errors exceeds space in the content window here):

This will delete all data in your db. Are you sure? [y/N]: y
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running downgrade cc7172b46608 -> 502b60eaa905, Update search index schema

------------------------------------------------------------------
IMPORTANT: After downgrade completes, manually run the reindex command:
basic-memory sync
------------------------------------------------------------------

INFO  [alembic.runtime.migration] Running downgrade 502b60eaa905 -> b3c3938bacdb, remove required from entity.permalink
╭───────────────────────────────────────────────────────────────────── Traceback (most recent call last) ──────────────────────────────────────────────────────────────────────╮
│ C:\Users\ryan\AppData\Roaming\uv\tools\basic-memory\Lib\site-packages\sqlalchemy\engine\base.py:1964 in _exec_single_context                                                 │
│                                                                                                                                                                              │
│   1961 │   │   │   │   │   │   │   evt_handled = True                                                                                                                        │
│   1962 │   │   │   │   │   │   │   break                                                                                                                                     │
│   1963 │   │   │   │   if not evt_handled:                                                                                                                                   │
│ ❱ 1964 │   │   │   │   │   self.dialect.do_execute(                                                                                                                          │
│   1965 │   │   │   │   │   │   cursor, str_statement, effective_parameters, context                                                                                          │
│   1966 │   │   │   │   │   )                                                                                                                                                 │
│   1967                                                                                                                                                                       │
│                                                                                                                                                                              │
│ ╭───────────────────────────────────────────────────── locals ─────────────────────────────────────────────────────╮                                                         │
│ │              context = <sqlalchemy.dialects.sqlite.base.SQLiteExecutionContext object at 0x0000022CDEC2C6B0>     │                                                         │
│ │               cursor = <sqlite3.Cursor object at 0x0000022CDEBBD040>                                             │                                                         │
│ │              dialect = <sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x0000022CDE7B5450> │                                                         │
│ │ effective_parameters = ()                                                                                        │                                                         │
│ │          evt_handled = False                                                                                     │                                                         │
│ │           parameters = [()]                                                                                      │                                                         │
│ │                 self = <sqlalchemy.engine.base.Connection object at 0x0000022CDE834E90>                          │                                                         │
│ │            statement = <sqlalchemy.dialects.sqlite.base.SQLiteCompiler object at 0x0000022CDEC25450>             │                                                         │
│ │        str_statement = 'INSERT INTO _alembic_tmp_entity (id, title, entity_type, entity_metadata, conten'+263    │                                                         │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯                                                         │
│                                                                                                                                                                              │
│ C:\Users\ryan\AppData\Roaming\uv\tools\basic-memory\Lib\site-packages\sqlalchemy\engine\default.py:942 in do_execute                                                         │
│                                                                                                                                                                              │
│    939 │   │   cursor.executemany(statement, parameters)                                                                                                                     │
│    940 │                                                                                                                                                                     │
│    941 │   def do_execute(self, cursor, statement, parameters, context=None):                                                                                                │
│ ❱  942 │   │   cursor.execute(statement, parameters)                                                                                                                         │
│    943 │                                                                                                                                                                     │
│    944 │   def do_execute_no_params(self, cursor, statement, context=None):                                                                                                  │
│    945 │   │   cursor.execute(statement)                                                                                                                                     │
│                                                                                                                                                                              │
│ ╭──────────────────────────────────────────────── locals ────────────────────────────────────────────────╮                                                                   │
│ │    context = <sqlalchemy.dialects.sqlite.base.SQLiteExecutionContext object at 0x0000022CDEC2C6B0>     │                                                                   │
│ │     cursor = <sqlite3.Cursor object at 0x0000022CDEBBD040>                                             │                                                                   │
│ │ parameters = ()                                                                                        │                                                                   │
│ │       self = <sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x0000022CDE7B5450> │                                                                   │
│ │  statement = 'INSERT INTO _alembic_tmp_entity (id, title, entity_type, entity_metadata, conten'+263    │                                                                   │
│ ╰────────────────────────────────────────────────────────────────────────────────────────────────────────╯                                                                   │
╰──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯
IntegrityError: NOT NULL constraint failed: _alembic_tmp_entity.permalink

The above exception was the direct cause of the following exception:

╭───────────────────────────────────────────────────────────────────── Traceback (most recent call last) ──────────────────────────────────────────────────────────────────────╮
│ C:\Users\ryan\AppData\Roaming\uv\tools\basic-memory\Lib\site-packages\basic_memory\cli\commands\db.py:17 in reset                                                            │
│                                                                                                                                                                              │
│   14 │   """Reset database (drop all tables and recreate)."""                                 ╭──── locals ─────╮                                                            │
│   15 │   if typer.confirm("This will delete all data in your db. Are you sure?"):             │ reindex = False │                                                            │
│   16 │   │   logger.info("Resetting database...")                                             ╰─────────────────╯                                                            │
│ ❱ 17 │   │   migrations.reset_database()                                                                                                                                     │
│   18 │   │                                                                                                                                                                   │
│   19 │   │   if reindex:                                                                                                                                                     │
│   20 │   │   │   # Import and run sync                                                                                                                                       │
│                                                                                                                                                                              │
│ C:\Users\ryan\AppData\Roaming\uv\tools\basic-memory\Lib\site-packages\basic_memory\alembic\migrations.py:23 in reset_database                                                │
│                                                                                                                                                                              │
│   20 │   """Drop and recreate all tables."""                                                  ╭─────────────────────────── locals ────────────────────────────╮              │
│   21 │   logger.info("Resetting database...")                                                 │ config = <alembic.config.Config object at 0x0000022CDEC0A350> │              │
│   22 │   config = get_alembic_config()                                                        ╰───────────────────────────────────────────────────────────────╯              │
│ ❱ 23 │   command.downgrade(config, "base")                                                                                                                                   │
│   24 │   command.upgrade(config, "head")                                                                                                                                     │
│   25                                                                                                                                                                         │
│                                                                                                                                                                              │
│ C:\Users\ryan\AppData\Roaming\uv\tools\basic-memory\Lib\site-packages\alembic\command.py:455 in downgrade                                                                    │
│                                                                                                                                                                              │
│   452 │   │   destination_rev=revision,                                                                                                                                      │
│   453 │   │   tag=tag,                                                                                                                                                       │
│   454 │   ):                                                                                                                                                                 │
│ ❱ 455 │   │   script.run_env()                                                                                                                                               │
│   456                                                                                                                                                                        │
│   457                                                                                                                                                                        │
│   458 def show(config: Config, rev: str) -> None:                                                                                                                            │
│                                                                                                                                                                              │
│ ╭───────────────────────────────────── locals ──────────────────────────────────────╮                                                                                        │
│ │       config = <alembic.config.Config object at 0x0000022CDEC0A350>               │                                                                                        │
│ │     revision = 'base'                                                             │                                                                                        │
│ │       script = <alembic.script.base.ScriptDirectory object at 0x0000022CDEC0A990> │                                                                                        │
│ │          sql = False                                                              │                                                                                        │
│ │ starting_rev = None                                                               │                                                                                        │
│ │          tag = None                                                               │                                                                                        │
│ ╰───────────────────────────────────────────────────────────────────────────────────╯                                                                                        │
│                                                                                                                                                                              │

[truncated error here, continues below]

IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: _alembic_tmp_entity.permalink
[SQL: INSERT INTO _alembic_tmp_entity (id, title, entity_type, entity_metadata, content_type, permalink, file_path, checksum, created_at, updated_at) SELECT entity.id,
entity.title, entity.entity_type, entity.entity_metadata, entity.content_type, entity.permalink, entity.file_path, entity.checksum, entity.created_at, entity.updated_at
FROM entity]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

Environment

• OS: Windows 11 Insider Preview 10.0.26120.3585
• Python version: 3.13.2
• Basic Memory version: [e.g. 0.10.1]
• Installation method: uv (version 0.6.8 (c1ef48276 2025-03-18))
• Claude Desktop version (if applicable): 0.9.0

Possible Solution

No idea, sorry!

[phernandez]

This is very interesting. I'll see if I can reproduce this locally.

@ryanstraight FYI, Basic Memory always assumes that the filesystem is the source of truth. So you can also delete the sqlite db in the project and it will rebuild it the next time the app starts up (you will need to resync).

The db is located within the project directory in the .basic-memory folder.

For example:

➜  basic-memory-pro git:(app-mockups) ✗ basic-memory project list
                                      Basic Memory Projects
┏━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┳━━━━━━━━┓
┃ Name     ┃ Path                                                             ┃ Default ┃ Active ┃
┡━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━╇━━━━━━━━┩
│ main     │ ~/basic-memory                                                   │ ✓       │ ✓      │
│ coffee   │ ~/dev/basicmachines/basic-memory/examples                        │         │        │
│ personal │ ~/Library/Mobile Documents/iCloud~md~obsidian/Documents/personal │         │        │
└──────────┴──────────────────────────────────────────────────────────────────┴─────────┴────────┘

in the main project:

➜  basic-memory-pro git:(app-mockups) ✗ tree ~/basic-memory/.basic-memory
/Users/phernandez/basic-memory/.basic-memory
├── memory.db
└── watch-status.json

you can delete the memory.db, then run basic-memory sync again and it will rebuild the db from the files in the project directory.

[bm-claudeai]

Root Cause Analysis

This error occurs during the database migration downgrade process when running basic-memory reset. The specific error NOT NULL constraint failed: _alembic_tmp_entity.permalink reveals the underlying issue.

During the downgrade operation (from migration cc7172b46608 to 502b60eaa905 and then to b3c3938bacdb), Alembic is creating a temporary table _alembic_tmp_entity to hold data while it restructures the main table. The error indicates:

1. The temporary table has a NOT NULL constraint on the permalink field
2. When copying data from the original entity table, some records have NULL values in the permalink field
3. This violates the constraint, causing the error

This mismatch between the schema changes in the downgrade path and the actual data in the database is causing the migration to fail.

Proposed Solutions

Short-term workaround

As mentioned by @phernandez, the filesystem is the source of truth for basic-memory, not the database. A quick workaround is to delete the SQLite database file and let it rebuild from scratch:

1. Close all running basic-memory processes
2. Delete the database file at ~/.basic-memory/memory.db
3. Run basic-memory sync to rebuild the database from the filesystem

Long-term fix options

1. Modify the reset function to use file deletion instead of migrations:

def reset_database():
    """Drop and recreate all tables."""
    from basic_memory.config import config
    
    logger.info("Resetting database...")
    
    # Get database path
    db_path = config.database_path
    
    # Delete the database file if it exists
    if db_path.exists():
        try:
            db_path.unlink()
            logger.info(f"Database file deleted: {db_path}")
        except Exception as e:
            logger.error(f"Failed to delete database file: {e}")
            # Fall back to migration method if file deletion fails
            config = get_alembic_config()
            command.downgrade(config, "base")
            command.upgrade(config, "head")
            return
            
    # Create a new empty database
    config = get_alembic_config()
    command.upgrade(config, "head")
    
    logger.info("Database reset complete")

2. Add a force option to the reset command:

@app.command()
def reset(reindex: bool = False, force: bool = False):
    """Reset database (drop all tables and recreate)."""
    if typer.confirm("This will delete all data in your db. Are you sure?"):
        logger.info("Resetting database...")
        
        if force:
            # Force delete the database file
            from basic_memory.config import config
            db_path = config.database_path
            if db_path.exists():
                try:
                    db_path.unlink()
                    logger.info(f"Database file deleted: {db_path}")
                except Exception as e:
                    logger.error(f"Failed to delete database file: {e}")
                    return
            
            # Create a new empty database
            config = get_alembic_config()
            command.upgrade(config, "head")
        else:
            # Use migration method
            migrations.reset_database()

3. Fix the migration script: The permanent solution would be to modify the specific Alembic migration causing the issue to properly handle NULL values in the permalink field during downgrade.