Implement OrganizationIntegrationRepository CRUD operations

epic-external-system-integration-configuration-foundation-task-008 — Implement the full Dart repository class for CRUD operations on organization_integrations via Supabase client. Methods: getIntegrationsByOrg(), getIntegrationById(), createIntegration(), updateIntegration(), deleteIntegration(). All methods must handle Supabase exceptions, map to domain errors, and serialize/deserialize JSONB field_mappings correctly.

critical priority medium complexity backend pending backend specialist Tier 3

Acceptance Criteria

OrganizationIntegrationRepository is an abstract interface class with a SupabaseOrganizationIntegrationRepository implementation

getIntegrationsByOrg(String orgId) returns Future<List<OrganizationIntegration>> filtered by organization_id, ordered by created_at ascending

getIntegrationById(String id) returns Future<OrganizationIntegration?> — returns null (not throws) when record not found

createIntegration(OrganizationIntegration integration) inserts a new row and returns the created OrganizationIntegration with server-generated id, created_at, updated_at

updateIntegration(OrganizationIntegration integration) updates only mutable fields (displayName, isEnabled, fieldMappings, syncSchedule) and returns updated model

deleteIntegration(String id) soft-deletes or hard-deletes based on schema design, returns void, throws IntegrationNotFoundException if id does not exist

All Supabase PostgrestException errors are caught and rethrown as typed domain exceptions (IntegrationNotFoundException, IntegrationPermissionException, IntegrationRepositoryException)

JSONB field_mappings is correctly serialized to/from Supabase in all CRUD operations

Repository does not expose Supabase types in its public interface — all return types are domain models

Integration with Supabase RLS: operations automatically scoped to authenticated user's organization via RLS policies (no manual org filtering needed beyond getIntegrationsByOrg)

Technical Requirements

frameworks

Dart

Flutter

Riverpod

apis

Supabase Dart client (.from('organization_integrations').select/insert/update/delete)

data models

OrganizationIntegration

FieldMapping

IntegrationRepositoryException

IntegrationNotFoundException

performance requirements

getIntegrationsByOrg must complete in under 2 seconds on typical Supabase network latency

Do not fetch all columns when only a subset is needed — use .select() with explicit column list where appropriate

security requirements

Never log credential_vault_id or any field containing 'secret', 'key', or 'password'

All Supabase queries rely on RLS for authorization — do not implement authorization logic in the repository layer

Validate that organizationId in the domain model matches authenticated user's organization before insert

Execution Context

Execution Tier

Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

Integration Task

Handles integration between different epics or system components. Requires coordination across multiple development streams.

View Full Execution Plan

Implementation Notes

Define abstract class OrganizationIntegrationRepository with method signatures in lib/domain/integrations/. Place the Supabase implementation in lib/infrastructure/integrations/supabase_organization_integration_repository.dart. Register the repository as a Riverpod provider (Provider) so BLoC/Cubit can inject it. Exception hierarchy: create IntegrationRepositoryException as a base sealed class with subtypes IntegrationNotFoundException, IntegrationPermissionException, IntegrationConflictException.

For updateIntegration, use Supabase .update() with .eq('id', id).select() to return the updated row in a single round trip. Avoid exposing raw Supabase response maps outside this file.

Testing Requirements

Write unit and integration tests using flutter_test. Unit tests: mock the Supabase client and test each method's happy path and error paths (PostgrestException → domain exception mapping). Integration tests (optional, if test Supabase project available): test against a real Supabase test instance with known fixture data. Test scenarios: (1) getIntegrationsByOrg returns empty list for org with no integrations; (2) getIntegrationById returns null for non-existent ID; (3) createIntegration returns model with server-generated fields populated; (4) updateIntegration only changes specified mutable fields; (5) deleteIntegration throws IntegrationNotFoundException for unknown ID; (6) PostgrestException with code 42501 (RLS) maps to IntegrationPermissionException.

Component

Organization Integration Repository

data medium

Dependencies (2)

Create fully typed Dart data model classes for OrganizationIntegration including fromJson/toJson serialization, JSONB field_mappings deserialization into typed FieldMapping objects, and sync schedule representation. Include copyWith, equality, and toString. Models must align exactly with the database schema columns. epic-external-system-integration-configuration-foundation-task-007 Write and apply Row Level Security policies on the organization_integrations table ensuring coordinators and admins can only read/write their own organization's integration records. Define separate policies for SELECT, INSERT, UPDATE, DELETE. Ensure service-role bypass for edge function access. epic-external-system-integration-configuration-foundation-task-003

Epic Risks (3)

high impact medium prob technical

Supabase Vault API has limited documentation for Dart/Flutter clients; wrapping it correctly for credential rotation and secret reference management may require significant trial and error, delaying the vault component and blocking all downstream credential-dependent work.

Mitigation & Contingency

Mitigation: Spike the Vault integration in the first sprint using a minimal proof-of-concept (store, retrieve, rotate one secret). Document the API surface before building the full vault client. Identify any missing Dart SDK bindings early.

Contingency: If Supabase Vault is too complex, fall back to Supabase's encrypted column approach (pgcrypto) for credential storage as a temporary measure, with a planned migration path to Vault once the API is understood.

high impact low prob security

Incorrect RLS policy configuration on organization_integrations could allow org admins of one organization to read or modify another organization's integration credentials, creating a serious data breach and compliance violation.

Mitigation & Contingency

Mitigation: Write integration tests that explicitly attempt cross-org data access using different JWT tokens and assert 0 rows returned. Include RLS policy review in PR checklist. Use Supabase's local development stack for policy validation before deployment.

Contingency: If a breach is discovered post-deployment, immediately revoke all integration credentials, rotate vault secrets, notify affected organizations, and apply emergency RLS patches.

medium impact medium prob technical

JSONB columns for field_mappings and sync_schedule lack database-level schema enforcement; AI-generated or malformed JSON could silently corrupt integration configurations, causing export failures that are hard to diagnose.

Mitigation & Contingency

Mitigation: Define TypeScript/Dart model classes with strict deserialization and validation. Add database check constraints or triggers that validate JSONB structure at write time. Version the JSONB schema to enable forward-compatible migrations.

Contingency: Build a repair script that scans organization_integrations for invalid JSONB and resets corrupted records to a safe default state, alerting the admin of the affected organization.

Quick Links

All Tasks Execution Plan