# database-layer Specification ## Purpose TBD - created by archiving change add-graphql-member-api. Update Purpose after archive. ## Requirements ### Requirement: SQLAlchemy Member model with async support The system MUST define a Member SQLAlchemy model with async session support for persisting member data with name, address, and contact information. **Model fields:** - `id`: Integer primary key (auto-increment) - `first_name`: String (max 100 chars, required) - `last_name`: String (max 100 chars, optional) - `street`: String (max 200 chars, optional) - `apartment_number`: String (max 20 chars, optional) - `zip`: String (max 20 chars, optional) - `city`: String (max 100 chars, optional) - `country`: String (max 100 chars, optional) - `email`: String (max 255 chars, optional) - `phone`: String (max 50 chars, optional) - `created_at`: DateTime (auto-set on creation) - `updated_at`: DateTime (auto-update on modification) #### Scenario: Member model is defined with proper constraints **Given** src/models/member.py exists **When** the Member class is inspected **Then** it inherits from SQLAlchemy Base **And** tablename is "members" **And** first_name has nullable=False **And** all other data fields (last_name, email, phone, address fields) have nullable=True **And** string fields have length constraints via String(N) #### Scenario: Timestamps are automatically managed **Given** a new Member instance is created **When** the instance is added to session and committed **Then** created_at is set to current UTC timestamp **And** updated_at is set to current UTC timestamp **When** the instance is later modified and committed **Then** updated_at is updated to new UTC timestamp **And** created_at remains unchanged #### Scenario: Model supports async operations **Given** Member model is defined **When** async database session is used **Then** CRUD operations execute without blocking event loop **And** SQLAlchemy async patterns are followed (select, add, commit) ### Requirement: Database connection and session management The system MUST provide async database connection factory and session management with proper lifecycle handling. **Connection configuration:** - Async engine using aiosqlite for SQLite - Connection pooling disabled for SQLite (single-writer) - Echo mode configurable via DEBUG setting #### Scenario: Async engine is created on application startup **Given** src/database.py defines engine initialization **When** FastAPI app starts **Then** async engine is created with database_url from config **And** engine is configured for SQLite with aiosqlite driver **And** SQL echo is enabled if DEBUG=True #### Scenario: Session factory provides isolated sessions **Given** async_session_maker is defined **When** resolver requests database session **Then** new AsyncSession is created from factory **And** session is isolated from other concurrent requests **And** session is properly closed after request completes #### Scenario: Session lifecycle is managed via dependency injection **Given** get_db_session() dependency is defined **When** FastAPI resolver depends on db_session **Then** session is yielded for resolver use **And** session is committed if no exceptions occur **And** session is rolled back if exceptions occur **And** session is closed in finally block ### Requirement: Alembic database migration setup The system MUST use Alembic for version-controlled schema migrations with async support for SQLite database. **Alembic configuration:** - Migrations stored in `migrations/versions/` - Environment configured for async operations - Migration template includes docstring and revision metadata #### Scenario: Alembic is initialized with project structure **Given** alembic init migrations was run **When** migrations/ directory is inspected **Then** migrations/env.py exists with async configuration **And** migrations/versions/ directory exists for migration files **And** alembic.ini contains database connection template #### Scenario: Initial migration creates members table **Given** Alembic is configured **When** developer runs `alembic revision --autogenerate -m "create members table"` **Then** new migration file is generated in migrations/versions/ **And** upgrade() function contains CREATE TABLE for members **And** downgrade() function contains DROP TABLE for members **And** all Member model columns are included #### Scenario: Migrations are applied to database **Given** migration files exist in migrations/versions/ **When** developer runs `alembic upgrade head` **Then** all pending migrations are executed in order **And** members table is created in database **And** alembic_version table tracks current revision **And** command exits with success code #### Scenario: Migrations are reversible **Given** database is at current migration head **When** developer runs `alembic downgrade -1` **Then** most recent migration is reversed **And** members table is dropped (for initial migration) **And** alembic_version is updated to previous revision ### Requirement: Database initialization on application startup The system MUST verify database connectivity and schema readiness when FastAPI application starts. #### Scenario: Application startup checks database connection **Given** FastAPI app has startup event handler **When** application starts **Then** database engine connection is tested **And** exception is raised if database is unreachable **And** startup log message confirms database ready #### Scenario: Database file is created if missing **Given** SQLite database file does not exist **When** application starts **Then** database file is created automatically **And** schema tables exist after migrations run **And** application continues startup normally ### Requirement: Test database fixtures with isolation The system MUST provide pytest fixtures for database testing with transaction rollback to ensure test isolation. #### Scenario: Test database session fixture is available **Given** tests/conftest.py defines db_session fixture **When** test function requests db_session parameter **Then** isolated AsyncSession is provided **And** session uses in-memory SQLite database (`:memory:`) **And** schema is created before test runs **And** all changes are rolled back after test completes #### Scenario: Test database is independent from development database **Given** tests use db_session fixture **When** tests create or modify data **Then** changes are not visible in development database **And** test data does not persist between test runs **And** tests can run in parallel without interference ### Requirement: Database query helpers for common operations The system MUST provide reusable query patterns for fetching, creating, updating, and deleting members. #### Scenario: Get member by ID query **Given** member exists in database with id=1 **When** query executes `select(Member).where(Member.id == 1)` **Then** Member instance is returned **When** query executes with non-existent id **Then** None is returned (not exception) #### Scenario: List all members query **Given** multiple members exist in database **When** query executes `select(Member).order_by(Member.last_name.nulls_last(), Member.first_name)` **Then** all members are returned as list **And** members are sorted by last name (nulls last), then first name **And** empty list is returned if no members exist #### Scenario: Update member fields **Given** member exists with id=1 **When** member attributes are modified and session commits **Then** database record is updated **And** updated_at timestamp is refreshed **And** created_at timestamp is unchanged #### Scenario: Delete member from database **Given** member exists with id=1 **When** session executes delete(member) and commits **Then** member is removed from database **And** subsequent queries for that id return None