Status: Accepted Date: 2025-08-07 Context: PM-034 Foundation Repair - Database Integration Architecture Decision Maker: Lead Developer (Code Agent) Stakeholders: Chief Architect, Infrastructure Team
Unify database session management across Piper Morgan services by standardizing on AsyncSessionFactory pattern and eliminating competing session creation approaches that cause transaction conflicts and integration failures.
Root cause analysis revealed critical database integration failures:
Two Competing Patterns:
AsyncSessionFactory.session_scope()RepositoryFactory + db.get_session()Transaction Boundary Conflicts:
# services/database/repositories.py:49-115
if self.session.in_transaction():
# Already in transaction, just add the entity
else:
# Start new transaction - CREATES NESTED CONFLICTS
async with self.session.begin():
Integration Health Impact:
Files with AsyncSessionFactory (Modern Pattern):
services/database/session_factory.py - Core implementationservices/intent_service/llm_classifier_factory.pyservices/repositories/todo_repository.pyservices/orchestration/engine.pyservices/api/health/staging_health.pyFiles with Legacy Patterns:
services/database/repositories.py - BaseRepository (594 lines)services/database/connection.py - Global db instanceAdopt AsyncSessionFactory as the single database session management pattern across all Piper Morgan services.
Session Creation:
# STANDARD PATTERN (Required)
async with AsyncSessionFactory.session_scope() as session:
repository = SomeRepository(session)
await repository.operation()
# Automatic commit and cleanup
Repository Pattern Update:
# ELIMINATE transaction detection logic
# SIMPLIFY to single-responsibility operations
async def create(self, **kwargs) -> Any:
entity = self.model(**kwargs)
self.session.add(entity)
await self.session.flush()
return entity
Service Integration:
# STANDARD SERVICE PATTERN
class UnifiedService:
async def perform_operation(self, data):
async with AsyncSessionFactory.session_scope() as session:
repository = SomeRepository(session)
return await repository.operation(data)
Elimination of Session Conflicts:
Simplified Repository Pattern:
Improved Integration Reliability:
Developer Experience:
Immediate Actions:
db.get_session() global patternService Updates Required:
Testing Strategy:
Mitigation: Incremental migration with backward compatibility during transition
Mitigation: Performance testing at each migration step, optimization if needed
Mitigation: UnifiedSessionManager for services requiring transaction coordination
Technical Metrics:
Integration Health:
Code Quality:
Implementation Priority: P0 - Foundational (blocks PM-034 Phase 3) Estimated Effort: 3-5 points Dependencies: None Follow-up ADRs: Circuit breaker patterns, Slack integration simplification