moai/.planning/phases/01-foundation/01-03-PLAN.md

phase	plan	type
01-foundation	03	execute

Create database module with async session management and write model tests.

Purpose: Enable database operations and validate models work correctly. Output: Working database.py with async session factory, passing model tests.

<execution_context> ~/.claude/get-shit-done/workflows/execute-phase.md ~/.claude/get-shit-done/templates/summary.md </execution_context>

@.planning/PROJECT.md @.planning/ROADMAP.md @SPEC.md @CLAUDE.md @src/moai/core/models.py

Tech choices:

• SQLAlchemy 2.0 async (create_async_engine, AsyncSession)
• aiosqlite for async SQLite
• pytest-asyncio for async tests

From CLAUDE.md:

• Testing: pytest, target 80%+ coverage on core logic
• Database: SQLAlchemy + SQLite, upgrades to PostgreSQL in Phase 2

Task 1: Create database module with async session management src/moai/core/database.py Create src/moai/core/database.py with:

Imports: sqlalchemy.ext.asyncio (create_async_engine, AsyncSession, async_sessionmaker), contextlib

Module-level:

• DATABASE_URL: str = "sqlite+aiosqlite:///./moai.db" (default, can be overridden)
• engine: AsyncEngine = None (initialized lazily)
• async_session_factory: async_sessionmaker = None

Functions:

1. init_db(url: str | None = None) -> None:

   • Creates engine with echo=False
   • Creates async_session_factory
   • Stores in module globals
   • Use: create_async_engine(url, echo=False)

2. async create_tables() -> None:

   • Imports Base from models
   • Runs async with engine.begin() as conn: await conn.run_sync(Base.metadata.create_all)

3. @contextlib.asynccontextmanager async def get_session() -> AsyncGenerator[AsyncSession, None]:

   • Yields session from factory
   • Handles commit on success, rollback on exception
   • Pattern: async with async_session_factory() as session: yield session; await session.commit()

4. async def close_db() -> None:

   • Disposes engine: await engine.dispose()

Add module docstring explaining session management pattern. python -c "from moai.core.database import init_db, create_tables, get_session, close_db" Database module importable with all functions

Task 2: Create model tests with in-memory database tests/test_models.py Create tests/test_models.py with:

Fixtures:

• @pytest.fixture async def db_session():
  • init_db("sqlite+aiosqlite:///:memory:")
  • await create_tables()
  • async with get_session() as session: yield session
  • await close_db()

Test cases:

1. test_create_project:

   • Create Project(name="Test Project", models=["claude", "gpt"])
   • Add to session, commit
   • Assert id is set (UUID string), name correct, models correct

2. test_create_discussion_with_project:

   • Create Project, add Discussion linked to it
   • Assert discussion.project_id matches project.id
   • Assert project.discussions contains the discussion

3. test_create_full_discussion_chain:

   • Create Project -> Discussion -> Round -> Message
   • Verify all relationships work
   • Verify cascade (all linked when navigating relationships)

4. test_create_consensus:

   • Create Discussion with Consensus
   • Assert discussion.consensus is set
   • Assert consensus.discussion links back

5. test_project_cascade_delete:

   • Create Project with Discussion with Round with Message
   • Delete Project
   • Assert all children deleted (cascade)

Use pytest.mark.asyncio on all async tests. Import all models and database functions. pytest tests/test_models.py -v passes all tests 5 model tests passing, cascade behavior verified

Task 3: Add .gitignore entries and verify full test suite .gitignore Update .gitignore to add:

# Database
*.db
*.sqlite
*.sqlite3

# Python
__pycache__/
*.pyc
.pytest_cache/
.coverage
htmlcov/

# Virtual environments
.venv/
venv/

# IDE
.idea/
.vscode/
*.swp

Then run full test suite with coverage to verify everything works together. pytest --cov=moai --cov-report=term-missing shows coverage, all tests pass .gitignore updated, tests pass with coverage report

Before declaring plan complete: - [ ] `pytest tests/test_models.py -v` passes all 5 tests - [ ] `pytest --cov=moai --cov-report=term-missing` runs successfully - [ ] `ruff check src tests` passes - [ ] Database file (moai.db) is gitignored - [ ] Phase 1 complete: scaffolding, models, database all working

<success_criteria>

• All tasks completed
• All tests pass
• No linting errors
• Phase 1: Foundation complete </success_criteria>

After completion, create `.planning/phases/01-foundation/01-03-SUMMARY.md` with: - Summary of all 3 plans in Phase 1 - Final verification that foundation is complete - Ready for Phase 2: Bot Core