basicmachines-co
diff --git a/‎src/basic_memory/alembic/versions/f8a9b2c3d4e5_add_pg_trgm_for_fuzzy_link_resolution.py‎
Lines changed: 116 additions & 76 deletions b/‎src/basic_memory/alembic/versions/f8a9b2c3d4e5_add_pg_trgm_for_fuzzy_link_resolution.py‎
Lines changed: 116 additions & 76 deletions
diff --git a/‎src/basic_memory/api/app.py‎
Lines changed: 18 additions & 4 deletions b/‎src/basic_memory/api/app.py‎
Lines changed: 18 additions & 4 deletions
diff --git a/‎src/basic_memory/cli/app.py‎
Lines changed: 9 additions & 2 deletions b/‎src/basic_memory/cli/app.py‎
Lines changed: 9 additions & 2 deletions
diff --git a/‎src/basic_memory/cli/commands/command_utils.py‎
Lines changed: 27 additions & 1 deletion b/‎src/basic_memory/cli/commands/command_utils.py‎
Lines changed: 27 additions & 1 deletion
diff --git a/‎src/basic_memory/cli/commands/mcp.py‎
Lines changed: 5 additions & 25 deletions b/‎src/basic_memory/cli/commands/mcp.py‎
Lines changed: 5 additions & 25 deletions
@@ -10,6 +10,42 @@
 
 import sqlalchemy as sa
 from alembic import op
+from sqlalchemy import text
+
+
+def column_exists(connection, table: str, column: str) -> bool:
+    """Check if a column exists in a table (idempotent migration support)."""
+    if connection.dialect.name == "postgresql":
+        result = connection.execute(
+            text(
+                "SELECT 1 FROM information_schema.columns "
+                "WHERE table_name = :table AND column_name = :column"
+            ),
+            {"table": table, "column": column},
+        )
+        return result.fetchone() is not None
+    else:
+        # SQLite
+        result = connection.execute(text(f"PRAGMA table_info({table})"))
+        columns = [row[1] for row in result]
+        return column in columns
+
+
+def index_exists(connection, index_name: str) -> bool:
+    """Check if an index exists (idempotent migration support)."""
+    if connection.dialect.name == "postgresql":
+        result = connection.execute(
+            text("SELECT 1 FROM pg_indexes WHERE indexname = :index_name"),
+            {"index_name": index_name},
+        )
+        return result.fetchone() is not None
+    else:
+        # SQLite
+        result = connection.execute(
+            text("SELECT 1 FROM sqlite_master WHERE type='index' AND name = :index_name"),
+            {"index_name": index_name},
+        )
+        return result.fetchone() is not None
 
 
 # revision identifiers, used by Alembic.
@@ -36,101 +72,105 @@ def upgrade() -> None:
     # Add project_id to relation table
     # -------------------------------------------------------------------------
 
-    # Step 1: Add project_id column as nullable first
-    op.add_column("relation", sa.Column("project_id", sa.Integer(), nullable=True))
+    # Step 1: Add project_id column as nullable first (idempotent)
+    if not column_exists(connection, "relation", "project_id"):
+        op.add_column("relation", sa.Column("project_id", sa.Integer(), nullable=True))
 
-    # Step 2: Backfill project_id from entity.project_id via from_id
-    if dialect == "postgresql":
-        op.execute("""
-            UPDATE relation
-            SET project_id = entity.project_id
-            FROM entity
-            WHERE relation.from_id = entity.id
-        """)
-    else:
-        # SQLite syntax
-        op.execute("""
-            UPDATE relation
-            SET project_id = (
-                SELECT entity.project_id
+        # Step 2: Backfill project_id from entity.project_id via from_id
+        if dialect == "postgresql":
+            op.execute("""
+                UPDATE relation
+                SET project_id = entity.project_id
                 FROM entity
-                WHERE entity.id = relation.from_id
-            )
-        """)
-
-    # Step 3: Make project_id NOT NULL and add foreign key
-    if dialect == "postgresql":
-        op.alter_column("relation", "project_id", nullable=False)
-        op.create_foreign_key(
-            "fk_relation_project_id",
-            "relation",
-            "project",
-            ["project_id"],
-            ["id"],
-        )
-    else:
-        # SQLite requires batch operations for ALTER COLUMN
-        with op.batch_alter_table("relation") as batch_op:
-            batch_op.alter_column("project_id", nullable=False)
-            batch_op.create_foreign_key(
+                WHERE relation.from_id = entity.id
+            """)
+        else:
+            # SQLite syntax
+            op.execute("""
+                UPDATE relation
+                SET project_id = (
+                    SELECT entity.project_id
+                    FROM entity
+                    WHERE entity.id = relation.from_id
+                )
+            """)
+
+        # Step 3: Make project_id NOT NULL and add foreign key
+        if dialect == "postgresql":
+            op.alter_column("relation", "project_id", nullable=False)
+            op.create_foreign_key(
                 "fk_relation_project_id",
+                "relation",
                 "project",
                 ["project_id"],
                 ["id"],
             )
-
-    # Step 4: Create index on relation.project_id
-    op.create_index("ix_relation_project_id", "relation", ["project_id"])
+        else:
+            # SQLite requires batch operations for ALTER COLUMN
+            with op.batch_alter_table("relation") as batch_op:
+                batch_op.alter_column("project_id", nullable=False)
+                batch_op.create_foreign_key(
+                    "fk_relation_project_id",
+                    "project",
+                    ["project_id"],
+                    ["id"],
+                )
+
+    # Step 4: Create index on relation.project_id (idempotent)
+    if not index_exists(connection, "ix_relation_project_id"):
+        op.create_index("ix_relation_project_id", "relation", ["project_id"])
 
     # -------------------------------------------------------------------------
     # Add project_id to observation table
     # -------------------------------------------------------------------------
 
-    # Step 1: Add project_id column as nullable first
-    op.add_column("observation", sa.Column("project_id", sa.Integer(), nullable=True))
+    # Step 1: Add project_id column as nullable first (idempotent)
+    if not column_exists(connection, "observation", "project_id"):
+        op.add_column("observation", sa.Column("project_id", sa.Integer(), nullable=True))
 
-    # Step 2: Backfill project_id from entity.project_id via entity_id
-    if dialect == "postgresql":
-        op.execute("""
-            UPDATE observation
-            SET project_id = entity.project_id
-            FROM entity
-            WHERE observation.entity_id = entity.id
-        """)
-    else:
-        # SQLite syntax
-        op.execute("""
-            UPDATE observation
-            SET project_id = (
-                SELECT entity.project_id
+        # Step 2: Backfill project_id from entity.project_id via entity_id
+        if dialect == "postgresql":
+            op.execute("""
+                UPDATE observation
+                SET project_id = entity.project_id
                 FROM entity
-                WHERE entity.id = observation.entity_id
-            )
-        """)
-
-    # Step 3: Make project_id NOT NULL and add foreign key
-    if dialect == "postgresql":
-        op.alter_column("observation", "project_id", nullable=False)
-        op.create_foreign_key(
-            "fk_observation_project_id",
-            "observation",
-            "project",
-            ["project_id"],
-            ["id"],
-        )
-    else:
-        # SQLite requires batch operations for ALTER COLUMN
-        with op.batch_alter_table("observation") as batch_op:
-            batch_op.alter_column("project_id", nullable=False)
-            batch_op.create_foreign_key(
+                WHERE observation.entity_id = entity.id
+            """)
+        else:
+            # SQLite syntax
+            op.execute("""
+                UPDATE observation
+                SET project_id = (
+                    SELECT entity.project_id
+                    FROM entity
+                    WHERE entity.id = observation.entity_id
+                )
+            """)
+
+        # Step 3: Make project_id NOT NULL and add foreign key
+        if dialect == "postgresql":
+            op.alter_column("observation", "project_id", nullable=False)
+            op.create_foreign_key(
                 "fk_observation_project_id",
+                "observation",
                 "project",
                 ["project_id"],
                 ["id"],
             )
-
-    # Step 4: Create index on observation.project_id
-    op.create_index("ix_observation_project_id", "observation", ["project_id"])
+        else:
+            # SQLite requires batch operations for ALTER COLUMN
+            with op.batch_alter_table("observation") as batch_op:
+                batch_op.alter_column("project_id", nullable=False)
+                batch_op.create_foreign_key(
+                    "fk_observation_project_id",
+                    "project",
+                    ["project_id"],
+                    ["id"],
+                )
+
+    # Step 4: Create index on observation.project_id (idempotent)
+    if not index_exists(connection, "ix_observation_project_id"):
+        op.create_index("ix_observation_project_id", "observation", ["project_id"])
 
     # Postgres-specific: pg_trgm and GIN indexes
     if dialect == "postgresql":
 
@@ -1,6 +1,7 @@
 """FastAPI application for basic-memory knowledge graph API."""
 
 import asyncio
+import os
 from contextlib import asynccontextmanager
 
 from fastapi import FastAPI, HTTPException
@@ -53,12 +54,25 @@ async def lifespan(app: FastAPI):  # pragma: no cover
     app.state.session_maker = session_maker
     logger.info("Database connections cached in app state")
 
-    logger.info(f"Sync changes enabled: {app_config.sync_changes}")
-    if app_config.sync_changes:
+    # Start file sync if enabled
+    is_test_env = (
+        app_config.env == "test"
+        or os.getenv("BASIC_MEMORY_ENV", "").lower() == "test"
+        or os.getenv("PYTEST_CURRENT_TEST") is not None
+    )
+    if app_config.sync_changes and not is_test_env:
+        logger.info(f"Sync changes enabled: {app_config.sync_changes}")
+
         # start file sync task in background
-        app.state.sync_task = asyncio.create_task(initialize_file_sync(app_config))
+        async def _file_sync_runner() -> None:
+            await initialize_file_sync(app_config)
+
+        app.state.sync_task = asyncio.create_task(_file_sync_runner())
     else:
-        logger.info("Sync changes disabled. Skipping file sync service.")
+        if is_test_env:
+            logger.info("Test environment detected. Skipping file sync service.")
+        else:
+            logger.info("Sync changes disabled. Skipping file sync service.")
         app.state.sync_task = None
 
     # proceed with startup
 
@@ -34,8 +34,15 @@ def app_callback(
     # Initialize logging for CLI (file only, no stdout)
     init_cli_logging()
 
-    # Run initialization for every command unless --version was specified
-    if not version and ctx.invoked_subcommand is not None:
+    # Run initialization for commands that don't use the API
+    # Skip for 'mcp' command - it has its own lifespan that handles initialization
+    # Skip for API-using commands (status, sync, etc.) - they handle initialization via deps.py
+    api_commands = {"mcp", "status", "sync", "project", "tool"}
+    if (
+        not version
+        and ctx.invoked_subcommand is not None
+        and ctx.invoked_subcommand not in api_commands
+    ):
         from basic_memory.services.initialization import ensure_initialization
 
         app_config = ConfigManager().config
 
@@ -1,12 +1,14 @@
 """utility functions for commands"""
 
-from typing import Optional
+import asyncio
+from typing import Optional, TypeVar, Coroutine, Any
 
 from mcp.server.fastmcp.exceptions import ToolError
 import typer
 
 from rich.console import Console
 
+from basic_memory import db
 from basic_memory.mcp.async_client import get_client
 
 from basic_memory.mcp.tools.utils import call_post, call_get
@@ -15,6 +17,30 @@
 
 console = Console()
 
+T = TypeVar("T")
+
+
+def run_with_cleanup(coro: Coroutine[Any, Any, T]) -> T:
+    """Run an async coroutine with proper database cleanup.
+
+    This helper ensures database connections are cleaned up before the event
+    loop closes, preventing process hangs in CLI commands.
+
+    Args:
+        coro: The coroutine to run
+
+    Returns:
+        The result of the coroutine
+    """
+
+    async def _with_cleanup() -> T:
+        try:
+            return await coro
+        finally:
+            await db.shutdown_db()
+
+    return asyncio.run(_with_cleanup())
+
 
 async def run_sync(project: Optional[str] = None, force_full: bool = False):
     """Run sync operation via API endpoint.
 
@@ -1,14 +1,13 @@
 """MCP server command with streamable HTTP transport."""
 
-import asyncio
 import os
 import typer
 from typing import Optional
 
 from basic_memory.cli.app import app
 from basic_memory.config import ConfigManager, init_mcp_logging
 
-# Import mcp instance
+# Import mcp instance (has lifespan that handles initialization and file sync)
 from basic_memory.mcp.server import mcp as mcp_server  # pragma: no cover
 
 # Import mcp tools to register them
@@ -17,8 +16,6 @@
 # Import prompts to register them
 import basic_memory.mcp.prompts  # noqa: F401  # pragma: no cover
 from loguru import logger
-import threading
-from basic_memory.services.initialization import initialize_file_sync
 
 config = ConfigManager().config
 
@@ -43,6 +40,8 @@ def mcp(
         - stdio: Standard I/O (good for local usage)
         - streamable-http: Recommended for web deployments (default)
         - sse: Server-Sent Events (for compatibility with existing clients)
+
+        Initialization, file sync, and cleanup are handled by the MCP server's lifespan.
         """
         # Initialize logging for MCP (file only, stdout breaks protocol)
         init_mcp_logging()
@@ -59,27 +58,8 @@ def mcp(
             os.environ["BASIC_MEMORY_MCP_PROJECT"] = project_name
             logger.info(f"MCP server constrained to project: {project_name}")
 
-        app_config = ConfigManager().config
-
-        def run_file_sync():
-            """Run file sync in a separate thread with its own event loop."""
-            loop = asyncio.new_event_loop()
-            asyncio.set_event_loop(loop)
-            try:
-                loop.run_until_complete(initialize_file_sync(app_config))
-            except Exception as e:
-                logger.error(f"File sync error: {e}", err=True)
-            finally:
-                loop.close()
-
-        logger.info(f"Sync changes enabled: {app_config.sync_changes}")
-        if app_config.sync_changes:
-            # Start the sync thread
-            sync_thread = threading.Thread(target=run_file_sync, daemon=True)
-            sync_thread.start()
-            logger.info("Started file sync in background")
-
-        # Now run the MCP server (blocks)
+        # Run the MCP server (blocks)
+        # Lifespan handles: initialization, migrations, file sync, cleanup
         logger.info(f"Starting MCP server with {transport.upper()} transport")
 
         if transport == "stdio":