clubber/openspec/changes/archive/2025-11-20-add-graphql-member-api/specs/member-crud/spec.md

# Spec: Member CRUD Operations

**Capability:** member-crud
**Status:** Draft
**Last Updated:** 2025-11-20

## Overview

This capability defines the business logic for creating, reading, updating, and deleting member records with validation, error handling, and sample data seeding.

## ADDED Requirements

### Requirement: Create member with validation

The system MUST allow creating new members with only firstName required, optionally validating email and phone format when provided.

**Validation rules:**
- firstName: Required field, must be non-empty string
- Email (when provided): Must match regex pattern `^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$`
- Phone (when provided): Must match E.164 format `^\+?[1-9]\d{1,14}$`
- All other fields (lastName, address fields, email, phone) are optional
- Email and phone do NOT require uniqueness checking
- Format validation only applies when field is provided (not null/empty)

#### Scenario: Create member with valid data

**Given** client provides valid member data:
```python
{
    "first_name": "John",
    "last_name": "Doe",
    "street": "123 Main Street",
    "apartment_number": "Apt 4B",
    "zip": "12345",
    "city": "Springfield",
    "country": "USA",
    "email": "john.doe@example.com",
    "phone": "+15551234567"
}
```
**When** createMember mutation executes
**Then** new Member record is created in database
**And** member.id is auto-generated
**And** member.created_at is set to current timestamp
**And** member.updated_at is set to current timestamp
**And** member object is returned to client

#### Scenario: Create member with invalid email format

**Given** client provides email "invalid-email"
**When** createMember mutation executes
**Then** validation error is raised
**And** error message is "Invalid email format: invalid-email"
**And** no database record is created

#### Scenario: Create member with invalid phone format

**Given** client provides phone "123-456"
**When** createMember mutation executes
**Then** validation error is raised
**And** error message is "Invalid phone format: 123-456"
**And** no database record is created

#### Scenario: Create member with duplicate email (allowed)

**Given** member exists with email "john@example.com"
**When** client creates another member with email "john@example.com"
**Then** new member is created successfully
**And** both members coexist in database
**And** no uniqueness constraint error occurs

#### Scenario: Create member without apartment number

**Given** client omits apartment_number field
**When** createMember mutation executes
**Then** member is created with apartment_number=None
**And** all other fields are populated correctly

#### Scenario: Create member with only firstName (minimal data)

**Given** client provides only first_name="Jane"
**When** createMember mutation executes
**Then** new Member record is created in database
**And** member.first_name is "Jane"
**And** member.last_name is None
**And** member.email is None
**And** member.phone is None
**And** all address fields (street, zip, city, country, apartment_number) are None
**And** created_at and updated_at are populated

#### Scenario: Create member with optional email (valid format)

**Given** client provides first_name="John" and email="john@example.com"
**When** createMember mutation executes
**Then** email format is validated
**And** member is created with validated email
**And** other optional fields are None

#### Scenario: Create member with optional email (invalid format)

**Given** client provides first_name="John" and email="invalid"
**When** createMember mutation executes
**Then** validation error is raised
**And** error message is "Invalid email format: invalid"
**And** no database record is created

### Requirement: Read member by ID

The system MUST allow fetching a single member by unique identifier with all fields populated.

#### Scenario: Get existing member by ID

**Given** member exists with id=1
**When** member(id=1) query executes
**Then** Member object is returned
**And** all fields match database record
**And** timestamps are in ISO 8601 format

#### Scenario: Get member with non-existent ID

**Given** no member exists with id=999
**When** member(id=999) query executes
**Then** None is returned
**And** no exception is raised

### Requirement: List all members with sorting

The system MUST allow fetching all members sorted alphabetically by last name (with nulls last), then first name.

#### Scenario: List members in sorted order

**Given** members exist:
  - id=1: firstName="Alice", lastName="Smith"
  - id=2: firstName="Bob", lastName="Jones"
  - id=3: firstName="Charlie", lastName="Smith"
  - id=4: firstName="Diana", lastName=None
**When** members query executes
**Then** members are returned in order: Jones, Smith (Alice), Smith (Charlie), Diana (null lastName)
**And** sorting is case-insensitive
**And** members with null lastName appear at the end

#### Scenario: List members when database is empty

**Given** no members exist in database
**When** members query executes
**Then** empty list is returned
**And** no error is raised

#### Scenario: List members returns all fields

**Given** members exist in database
**When** members query requests all fields
**Then** each member includes id, name, address, contact, timestamps
**And** no fields are null except apartmentNumber (if not provided)

### Requirement: Update member with partial field changes

The system MUST allow updating specific member fields while preserving unchanged fields and refreshing the updated_at timestamp.

#### Scenario: Update member email

**Given** member exists with id=1, email="old@example.com"
**When** updateMember mutation executes with:
```python
{"id": 1, "email": "new@example.com"}
```
**Then** member.email is updated to "new@example.com"
**And** member.updated_at is refreshed to current timestamp
**And** member.created_at remains unchanged
**And** all other fields remain unchanged

#### Scenario: Update multiple fields simultaneously

**Given** member exists with id=1
**When** updateMember mutation provides phone, street, and city
**Then** all three fields are updated
**And** other fields remain unchanged
**And** updated_at is refreshed

#### Scenario: Update member with invalid email

**Given** member exists with id=1
**When** updateMember mutation provides email="invalid"
**Then** validation error is raised
**And** database record is not modified
**And** error message indicates invalid email format

#### Scenario: Update non-existent member

**Given** no member exists with id=999
**When** updateMember(id=999) mutation executes
**Then** error is raised
**And** error message is "Member with ID 999 not found"

#### Scenario: Update with no field changes

**Given** member exists with id=1
**When** updateMember mutation provides only id (no other fields)
**Then** no fields are modified
**And** updated_at is NOT refreshed (no actual changes)

### Requirement: Delete member from system

The system MUST allow permanent deletion of member records by ID.

#### Scenario: Delete existing member

**Given** member exists with id=1
**When** deleteMember(id=1) mutation executes
**Then** member is removed from database
**And** mutation returns true
**When** subsequent query for id=1 executes
**Then** None is returned

#### Scenario: Delete non-existent member

**Given** no member exists with id=999
**When** deleteMember(id=999) mutation executes
**Then** error is raised
**And** error message is "Member with ID 999 not found"
**And** mutation returns false or raises exception

### Requirement: Seed database with sample member

The system MUST provide an idempotent script to populate database with one sample member for development and testing.

**Sample member data:**
- first_name: "Jane"
- last_name: "Doe"
- street: None (demonstrates optional address fields)
- apartment_number: None
- zip: None
- city: None
- country: None
- email: "jane.doe@example.com"
- phone: None (demonstrates optional phone)

#### Scenario: Seed script creates sample member

**Given** database schema exists (migrations applied)
**And** no member exists with email "jane.doe@example.com"
**When** seed script executes
**Then** sample member is created in database
**And** script prints "Sample member created successfully"
**And** script exits with code 0

#### Scenario: Seed script is idempotent

**Given** sample member already exists with email "jane.doe@example.com"
**When** seed script executes again
**Then** no new member is created
**And** existing member is not modified
**And** script prints "Sample member already exists"
**And** script exits with code 0

#### Scenario: Seed script verifies sample member

**Given** seed script has completed
**When** query executes for email "jane.doe@example.com"
**Then** member exists with all sample data fields populated
**And** created_at and updated_at timestamps are valid

### Requirement: Input validation with clear error messages

The system MUST validate all inputs and provide actionable error messages identifying the validation failure.

#### Scenario: Empty required field error

**Given** createMember mutation has firstName=""
**When** mutation executes
**Then** validation error is raised
**And** error message is "firstName cannot be empty"

#### Scenario: Field length exceeds maximum

**Given** createMember mutation has firstName with 150 characters
**When** mutation executes
**Then** validation error is raised
**And** error message indicates maximum length of 100 characters

#### Scenario: Multiple validation errors

**Given** createMember mutation has invalid email AND invalid phone
**When** mutation executes
**Then** validation errors for both fields are returned
**And** error messages clearly identify each invalid field

## MODIFIED Requirements

None (new capability)

## REMOVED Requirements

None (new capability)

## Cross-References

### Depends On
- **project-setup** - Requires Python environment and testing infrastructure
- **database-layer** - Requires Member model and database sessions
- **graphql-api** - Requires GraphQL resolvers for API interface

### Enables
None (this is a terminal capability - enables end-user features)

### Related To
None

## Implementation Notes

1. **Validation location:** Implement in resolvers before database operations
2. **Regex compilation:** Compile email/phone patterns once at module level
3. **Error types:** Use custom exception classes (MemberNotFoundError, ValidationError)
4. **Transaction handling:** Database session auto-commits on success, rolls back on exception
5. **Testing:** Use pytest fixtures for database setup, test each scenario independently
6. **Seed script:** Use asyncio.run() to execute async database operations

## Validation Patterns (Python)

```python
import re
from typing import Optional

EMAIL_PATTERN = re.compile(r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$')
PHONE_PATTERN = re.compile(r'^\+?[1-9]\d{1,14}$')

def validate_email(email: Optional[str]) -> None:
    """Validate email format only if email is provided (not None or empty)."""
    if email is not None and email != "":
        if not EMAIL_PATTERN.match(email):
            raise ValidationError(f"Invalid email format: {email}")

def validate_phone(phone: Optional[str]) -> None:
    """Validate phone format only if phone is provided (not None or empty)."""
    if phone is not None and phone != "":
        if not PHONE_PATTERN.match(phone):
            raise ValidationError(f"Invalid phone format: {phone}")

def validate_first_name(first_name: str) -> None:
    """Ensure firstName is provided and non-empty."""
    if not first_name or first_name.strip() == "":
        raise ValidationError("firstName cannot be empty")
```

## Validation Checklist

- [ ] Create member with valid data succeeds
- [ ] Create member with only firstName succeeds (all other fields null)
- [ ] Invalid email format raises ValidationError (when provided)
- [ ] Invalid phone format raises ValidationError (when provided)
- [ ] Email/phone validation skipped when fields are null/empty
- [ ] Duplicate emails are allowed (no uniqueness constraint)
- [ ] Get member by ID returns correct data
- [ ] Get non-existent member returns None
- [ ] List members returns sorted results (nulls last for lastName)
- [ ] Update member modifies only specified fields
- [ ] Update refreshes updated_at timestamp
- [ ] Update non-existent member raises error
- [ ] Delete member removes record from database
- [ ] Delete non-existent member raises error
- [ ] Seed script creates sample member with some null fields
- [ ] Seed script is idempotent (safe to run multiple times)
- [ ] All error messages are clear and actionable