high priority medium complexity testing pending testing specialist Tier 4

Acceptance Criteria

DeclarationTemplateRepository: fetching a template by (templateId, version) returns the correct versioned record
DeclarationTemplateRepository: fetching a non-existent version returns null without throwing
DriverAssignmentRepository: create, read, update, and delete operations complete successfully against the test Supabase project
DriverAssignmentRepository: soft-delete sets deleted_at and is_deleted flags without removing the row from the database
DriverAssignmentRepository: querying after soft-delete does not return the deleted record in normal list queries
DeclarationRepository: all valid lifecycle transitions (pending → sent → acknowledged, pending → expired) succeed
DeclarationRepository: invalid lifecycle transitions (e.g., acknowledged → pending) are rejected with a descriptive error
DeclarationAuditLogger: every supported event type (created, sent, acknowledged, expired, deleted) inserts a new row in the audit log table
DeclarationAuditLogger: attempting to UPDATE or DELETE an existing audit log row throws an RLS or trigger error (insert-only enforcement verified)
RLS cross-org test: authenticating as org_A and querying data seeded for org_B returns zero rows for all four repositories
All tests pass consistently (no flakiness) when run in CI against the dedicated test Supabase project
Test setup and teardown scripts seed and clean up test data reliably, leaving the test database in a clean state

Technical Requirements

frameworks
Flutter
flutter_test
Dart
apis
Supabase PostgREST — declaration_templates (SELECT versioned)
Supabase PostgREST — driver_assignments (CRUD + soft-delete)
Supabase PostgREST — declarations (lifecycle state transitions)
Supabase PostgREST — declaration_audit_log (INSERT only)
data models
DeclarationTemplate (template_id, version, content)
DriverAssignment (assignment_id, org_id, driver_id, is_deleted, deleted_at)
Declaration (declaration_id, org_id, status, created_at, updated_at)
DeclarationAuditLog (log_id, declaration_id, event_type, created_at, actor_id)
performance requirements
Each integration test must complete within 5 seconds to keep the CI suite fast
Test setup (seed + auth) must not exceed 3 seconds per test group
security requirements
Tests must authenticate with two distinct Supabase test user accounts (org_A_user, org_B_user) to verify RLS cross-org isolation
Test credentials must be stored in environment variables, never hard-coded
Audit log insert-only enforcement must be verified by attempting a direct UPDATE via the Supabase client and asserting a PostgrestException is thrown

Execution Context

Execution Tier
Tier 4

Tier 4 - 323 tasks

Can start after Tier 3 completes

Implementation Notes

Create a test helper class SupabaseTestHelper that wraps client initialization, row seeding, and cleanup to reduce boilerplate across test files. For soft-delete tests, use two queries: one with the default filter (should exclude deleted) and one with includeDeleted: true (should include) to verify both paths. For lifecycle transition tests, seed a declaration in the starting state, call the repository transition method, then re-fetch and assert the new status. For audit log insert-only enforcement, catch the PostgrestException and assert its message or code indicates a policy violation — do not rely on HTTP status codes alone as they can vary.

Document which Supabase project is used for integration tests in a README inside test/integration/.

Testing Requirements

These ARE the tests. Use flutter_test with a test/integration/ directory. Each repository gets its own test file (declaration_template_repository_test.dart, driver_assignment_repository_test.dart, declaration_repository_test.dart, declaration_audit_logger_test.dart). Use setUp/tearDown to insert and delete test rows using a privileged service-role Supabase client.

For RLS tests, initialize a second SupabaseClient with org_B credentials and assert that queries return empty lists. The test Supabase project URL and anon/service-role keys must be injected via --dart-define or a .env file excluded from version control. Tag integration tests with @Tags(['integration']) so they can be excluded from fast unit test runs.

Epic Risks (3)
high impact medium prob security

Row-level security policies for driver assignments and declarations must correctly scope data to the coordinator's chapter without leaking records across organizations. An incorrect RLS predicate could silently return empty result sets or, worse, expose cross-org data, both of which are difficult to detect in unit tests.

Mitigation & Contingency

Mitigation: Write dedicated RLS integration test scenarios with multiple org fixtures asserting both data isolation and correct data visibility. Use Supabase's built-in policy testing utilities and review policies with a second developer.

Contingency: If RLS policies prove too complex to get right quickly, implement application-layer org scoping as a temporary guard while RLS is fixed in a follow-up, with an explicit security review gate before production deployment.

high impact medium prob security

The declaration audit logger must produce tamper-evident records. If the database allows updates or deletes on audit rows, the compliance guarantee is broken. Supabase does not natively prevent row deletion by default.

Mitigation & Contingency

Mitigation: Implement an insert-only RLS policy on the audit table that denies UPDATE and DELETE for all roles including the service role. Add a database trigger that rejects mutation attempts and logs the attempt itself.

Contingency: If immutability cannot be enforced at the database level within the sprint, store audit entries in an append-only Supabase Edge Function log stream as a temporary alternative, with a migration plan to the proper table once constraints are implemented.

medium impact low prob technical

The org-feature-flag-service caches flag values to avoid repeated database reads. If the cache is not invalidated promptly after an admin toggles the flag, coordinators may see stale UI state — either seeing driver features when they should not, or not seeing them when they should.

Mitigation & Contingency

Mitigation: Use a Supabase Realtime subscription to listen for changes on the driver_feature_flag_config table and invalidate the in-memory cache immediately on change. Set a short TTL (60 seconds) as a safety net.

Contingency: If Realtime subscription proves unreliable, expose a manual cache-bust endpoint accessible from the admin toggle action, ensuring the cache is cleared synchronously on every flag change.