Testing Strategy: Integration tests, DB environments, and CI pipeline

[teetangh]

Context

The backend currently has no automated integration tests. Testing is done manually via curl against a shared Supabase dev database with seeded mock data. This works for development but doesn't scale — mock data changes between sessions, tests aren't reproducible, and there's no CI verification.

Recommended Enterprise Testing Strategy

Three-Layer Test Pyramid

Layer 1: Unit Tests (fast, no DB)

• What: Test business logic in services and utilities
• How: Mock repositories with mocktail, use Freezed models as test data
• Where: backend/test/unit/
• When: Every commit, pre-push hooks
• Coverage target: All service methods, validation logic, auth flows

// Example: test auth service
test('signUpWithEmail creates user + profile + account', () async {
  when(() => mockDb.findUserByEmail(any())).thenAnswer((_) async => null);
  when(() => mockDb.createUser(...)).thenAnswer((_) async => testUser);
  
  final result = await authService.signUpWithEmail('test@example.com', 'pass');
  
  expect(result['user']['email'], 'test@example.com');
  verify(() => mockDb.createConsulteeProfile(...)).called(1);
});

Layer 2: Integration Tests (with DB)

• What: Test full request→DB→response flow
• How: Start dart_frog server, hit endpoints with HTTP client, verify JSON responses
• Where: backend/test/integration/
• DB: Dedicated test database (NOT the shared dev Supabase)
• When: PR checks, nightly builds

// Example: integration test
test('POST /api/feedback creates feedback with auto-generated UUID', () async {
  final response = await http.post(
    Uri.parse('http://localhost:8080/api/feedback'),
    headers: {'Authorization': 'Bearer $testToken', 'Content-Type': 'application/json'},
    body: jsonEncode({'title': 'Test', 'description': 'Integration test', 'rating': 5}),
  );
  
  expect(response.statusCode, 201);
  final body = jsonDecode(response.body);
  expect(body['id'], isNotEmpty); // UUID auto-generated
  expect(body['status'], 'PENDING'); // Enum correct case
  expect(DateTime.tryParse(body['createdAt']), isNotNull); // DateTime valid
});

Layer 3: E2E Tests (full stack)

• What: Flutter app → API → DB → response → UI verification
• How: Flutter integration_test package
• Where: test/integration/
• When: Before releases

Database Environments

Environment	Database	Data	Purpose
Test (CI)	Docker PostgreSQL per run	Deterministic seed	Automated tests — reproducible, isolated
Dev	Supabase dev project	Seeded mock data	Manual development and debugging
Staging	Supabase staging project	Sanitized prod copy	Pre-release verification
Production	Supabase prod project	Real user data	Live

Deterministic Test Seeding

Create backend/test/fixtures/seed.dart:

/// Deterministic test data — same IDs every run
class TestFixtures {
  static const consultantUserId = 'test-consultant-001';
  static const consulteeUserId = 'test-consultee-001';
  static const domainId = 'test-domain-001';
  static const planId = 'test-plan-001';
  
  static Future<void> seed(DatabaseClient db) async {
    await db.executeRaw('DELETE FROM "Feedback" WHERE "userId" LIKE \'test-%\'');
    // ... seed known test records with fixed IDs
  }
  
  static Future<void> teardown(DatabaseClient db) async {
    await db.executeRaw('DELETE FROM "Feedback" WHERE "userId" LIKE \'test-%\'');
  }
}

CI Pipeline Integration

# .github/workflows/backend-tests.yml
name: Backend Tests
on: [push, pull_request]

jobs:
  unit:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: dart-lang/setup-dart@v1
      - run: cd backend && dart pub get
      - run: cd backend && dart test test/unit/

  integration:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: test_familiarise
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
        ports: ['5432:5432']
    steps:
      - uses: actions/checkout@v4
      - uses: dart-lang/setup-dart@v1
      - run: cd backend && dart pub get
      - run: npx prisma migrate deploy  # Apply schema
      - run: dart run test/fixtures/seed.dart  # Seed test data
      - run: dart run build_runner build --delete-conflicting-outputs
      - run: dart pub global run dart_frog_cli:dart_frog build
      - run: dart build/bin/server.dart &
      - run: sleep 5 && dart test test/integration/

Existing Test Infrastructure

The project already has:

• backend/tool/gen_test_token.dart — JWT generation for test users
• backend/tool/gen_token_for.dart — JWT for specific user IDs
• backend/test/repositories/ — Some unit tests with mocks
• Supabase dev DB with seeded data (test_intg_cbj_cnt, test_intg_cbj_cte users)

Implementation Priority

1. Phase 1: Automate the manual curl tests as integration tests (we already run 16 curl tests manually — convert to dart test)
2. Phase 2: Add unit tests for auth service, payment logic, booking conflict detection
3. Phase 3: Docker PostgreSQL for CI — isolated, reproducible
4. Phase 4: E2E Flutter integration tests

Key Principles

• Never test against production — always use test/dev environments
• Deterministic data — fixed IDs, predictable state, no random data in assertions
• Isolated per run — each CI run gets its own DB, seeds fresh, tears down after
• Fast feedback — unit tests run in seconds, integration tests in under a minute