[Database] Implement robust async SQLite session factory + connection pooling in shared layer

[saviornt]

Description

Create a clean, type-safe, async-capable SQLite session manager to serve as the foundation for all database access in both Appliance and Assistant.
This is the first critical piece of the shared persistence layer.

Type

• Task

Focus Area (pick one)

• Shared Utils & Models

Priority

• High

Acceptance Criteria

• File created or updated: shared/src/database/session.py
• Exports get_db() (FastAPI-style dependency) and async_session_factory()
• Uses sqlalchemy for true async operation
• Database file path loaded from Pydantic Settings (app.core.settings.Settings)
• Connection opened in WAL mode (PRAGMA journal_mode=WAL;)
• Configurable connection pool size (default 4–8, sensible for Raspberry Pi)
• Proper resource cleanup on application shutdown (lifespan / atexit handler)
• Includes basic health check helper function (for /health/db endpoint)
• Full Google-style docstrings on all public functions/classes
• Complete type annotations (mypy strict mode clean)
• Unit tests with pytest + pytest-asyncio covering:
  • open / close cycle
  • transaction rollback on exception
  • concurrent access safety
• No blocking synchronous calls anywhere in the module

Blocker / Dependencies

• None (this is foundational)

Notes / Links

• Related files: shared/src/core/settings.py, shared/src/database/__init__.py
• Reference: SQLAlchemy 2.0 async + sqlalchemy docs
• Related decision: Use single SQLite file for both Appliance & Assistant (mirrored via MQTT dumps later)