Create credential vault reference column migration
epic-external-system-integration-configuration-foundation-task-005 — Add vault_secret_id column to the organization_integrations table to store the opaque Vault reference ID instead of plaintext credentials. Write migration ensuring the column is non-nullable for active integrations and that no credential fields exist outside of Vault. Update existing RLS policies to cover this column.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 3 - 413 tasks
Can start after Tier 2 completes
Handles integration between different epics or system components. Requires coordination across multiple development streams.
Implementation Notes
This migration must run after both task-003 (RLS policies exist) and task-004 (Vault functions exist). In the migration SQL, add the column with ALTER TABLE organization_integrations ADD COLUMN IF NOT EXISTS vault_secret_id UUID. The CHECK constraint for active integrations should use: ALTER TABLE organization_integrations ADD CONSTRAINT chk_active_integration_has_vault_ref CHECK (status::text != 'active' OR vault_secret_id IS NOT NULL). Regarding cross-schema FK to vault.secrets: Supabase Vault does not officially support foreign key constraints into vault.secrets because it is a protected schema.
Document this limitation explicitly and enforce referential integrity at the application layer in the OrganizationIntegrationRepository Dart class — before inserting a vault_secret_id, verify the secret exists via a retrieve_credential() probe call. In the Dart repository, always fetch organization_integrations rows with explicit column projection (never SELECT *) to ensure vault_secret_id is never accidentally returned to the client.
Testing Requirements
Migration validation tests in Supabase local environment. Test cases: (1) Migration applies cleanly on top of task-002 and task-003 migrations. (2) INSERT of an 'active' status row without vault_secret_id is rejected by the CHECK constraint. (3) INSERT of an 'inactive' status row without vault_secret_id succeeds.
(4) UPDATE of a row changing status to 'active' without setting vault_secret_id is rejected. (5) Rollback migration removes the column and constraint without error. (6) Coordinator-role RLS policy cannot retrieve vault_secret_id (query returns NULL or column is absent from result set). (7) Admin-role RLS policy can retrieve vault_secret_id for their own organization's rows.
Run all tests as part of the full migration suite (supabase db reset).
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.