Add integration_type enum to database and registry
epic-external-system-integration-configuration-foundation-task-011 — Create a PostgreSQL enum type for supported integration types (xledger, dynamics, cornerstone, consio, bufdir) and apply it to the organization_integrations table. Synchronize the enum values with the Dart IntegrationTypeRegistry constants to ensure type safety across the stack. Write migration with forward and rollback scripts.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 2 - 518 tasks
Can start after Tier 1 completes
Handles integration between different epics or system components. Requires coordination across multiple development streams.
Implementation Notes
Create the migration file using the Supabase CLI: 'supabase migration new add_integration_type_enum'. Use this SQL pattern for safety: first CREATE TYPE IF NOT EXISTS integration_type AS ENUM (...), then ALTER TABLE organization_integrations ALTER COLUMN integration_type TYPE integration_type USING integration_type::integration_type. Add a rollback file with: ALTER TABLE organization_integrations ALTER COLUMN integration_type TYPE text, then DROP TYPE integration_type. The critical cross-stack coupling is between PostgreSQL enum values and Dart enum: add a CI-friendly Dart test that asserts exact value list to catch drift.
Document the process for adding a new integration type in the migration comment (requires: new PG migration to ADD VALUE, new Dart enum value, new registry entry).
Testing Requirements
Test the migration in a local Supabase development environment. Verify: (1) forward migration runs without errors on empty table; (2) forward migration runs without errors with existing rows containing valid enum values; (3) forward migration fails (or pre-check catches) if rows contain invalid enum values; (4) rollback migration reverts column to text and drops enum without error; (5) after migration, inserting a row with an invalid integration_type value is rejected by PostgreSQL with a type error. Also write a Dart unit test that compares IntegrationTypeRegistry.supportedTypes with a hardcoded list of the 5 expected values — this test acts as a cross-stack sync check.
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.
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.
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.