[FEATURE] Unify database_url configuration for Postgres and SQLite #539

Feature Description

Extend BASIC_MEMORY_DATABASE_URL to accept SQLite URLs (for custom paths) and extract search_path from Postgres URLs (for schema isolation).

Problem This Feature Solves

Users need database isolation without dedicated infrastructure:

• Git worktrees share ~/.basic-memory/memory.db, causing index conflicts between branches
• Project portability - can't clone a repo and have BM "just work" with a project-local index
• Shared Postgres - can't coexist with other apps in an existing database (no schema support)

Currently database_url only works for Postgres and ignores SQLite.

Proposed Solution

1. Accept SQLite URLs - Enable project-local databases:

   sqlite+aiosqlite:///.basic-memory/memory.db
   

2. Extract search_path from Postgres URLs - Enable schema isolation:

   postgresql+asyncpg://host/db?search_path=basic_memory
   

   (asyncpg rejects this param directly, so BM extracts it and passes to server_settings)

3. Auto-create schema - Run CREATE SCHEMA IF NOT EXISTS before migrations

4. Set Alembic version_table_schema - Ensure migration tracking uses the same schema

Alternative Solutions

• Separate env vars (DATABASE_PATH, DATABASE_SCHEMA) - More config to manage; URL is more portable
• Rely on asyncpg to fix this - Open issues exist ([BUG] Gemini CLI fails to discover tools: can't resolve reference #/$defs/ContextResult #438, [BUG] search_notes list parameters fail with Pydantic validation error when called via MCP #656) but maintainers consider server_settings the correct approach

Additional Context

• SQLite: three slashes = relative, four = absolute (SQLAlchemy convention)
• Postgres: search_path supports multiple schemas (myschema,public)
• Backwards compatible - no config = current behavior
• Cloud mode unaffected (uses remote API)
• Markdown remains source of truth; index rebuilds if path changes

Impact

Enables project-scoped databases without requiring dedicated Postgres instances or polluting user-level state. Particularly useful for teams using git worktrees or developers working across multiple projects.

I'm planning to prepare a PR for this and would appreciate feedback on the approach.