clubber/openspec/specs/database-layer/spec.md

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