high priority low complexity database pending database specialist Tier 2

Acceptance Criteria

A PostgreSQL enum type named integration_type is created with values: 'xledger', 'dynamics', 'cornerstone', 'consio', 'bufdir'
The organization_integrations table's integration_type column is altered from text/varchar to the integration_type enum type
Forward migration script executes successfully on a clean Supabase project and on a project with existing rows
Rollback migration script reverts the column back to text type and drops the enum type without data loss
Migration includes a step that validates existing data rows have valid enum values before altering column type (uses a pre-check query)
The Dart IntegrationType enum values exactly match the PostgreSQL enum values (same 5 strings, same casing)
A comment in the migration file explicitly states: 'To add a new integration type, update both this migration and IntegrationTypeRegistry in Dart'
Migration is idempotent — running it twice does not cause an error (uses CREATE TYPE IF NOT EXISTS pattern)
RLS policies on organization_integrations are preserved after the column type change
Supabase generated TypeScript types (if used) reflect the new enum type after regeneration

Technical Requirements

frameworks
PostgreSQL
Supabase
apis
Supabase Migration CLI
data models
organization_integrations table
integration_type PostgreSQL enum
performance requirements
ALTER COLUMN on integration_type should complete in under 5 seconds even with 10,000 existing rows
Use ALTER TABLE ... USING integration_type::integration_type for in-place type coercion without table rewrite
security requirements
Migration must run in a transaction — wrap in BEGIN/COMMIT so partial failures are rolled back
Do not DROP the enum type in the forward migration if it already exists — use CREATE TYPE IF NOT EXISTS

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Integration Task

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.

Component
Integration Type Registry
infrastructure low
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.