clubber/openspec/specs/member-crud/spec.md

# member-crud Specification

## Purpose
TBD - created by archiving change add-graphql-member-api. Update Purpose after archive.
## 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