critical priority medium complexity database pending database specialist Tier 3

Acceptance Criteria

Migration adds vault_secret_id UUID column to organization_integrations, initially NULLABLE to allow rows without credentials during setup phase
A CHECK constraint named chk_active_integration_has_vault_ref enforces: (status != 'active') OR (vault_secret_id IS NOT NULL) — active integrations must have a vault reference
No column named credential, api_key, secret, password, token, or any similar plaintext credential identifier exists on the table after this migration
A database comment (COMMENT ON COLUMN) is added to vault_secret_id documenting that this is an opaque Vault reference ID, not a credential value
Existing RLS SELECT policy is updated (or a new policy added) to include vault_secret_id in readable columns for admin users of the same organization
vault_secret_id is explicitly EXCLUDED from the SELECT policy for coordinator role — coordinators can see integration status but not the vault reference ID
Migration rollback drops the vault_secret_id column and its constraint cleanly
A trigger or constraint prevents vault_secret_id from being set to a non-existent UUID (FK to vault.secrets.id if Supabase allows cross-schema FK, otherwise enforce in application layer with documented note)
Migration is idempotent: ALTER TABLE ... ADD COLUMN IF NOT EXISTS pattern used

Technical Requirements

frameworks
Supabase
PostgreSQL
apis
Supabase Vault API
data models
OrganizationIntegration
IntegrationCredentialVault
performance requirements
No additional indexes required for vault_secret_id as lookups will be by organization_id first
CHECK constraint evaluation is negligible overhead
security requirements
vault_secret_id must never appear in API responses to the Flutter client — the repository layer must explicitly exclude it from SELECT projections returned to the app
The column stores only a UUID reference — the actual decryption never happens in the Flutter app, only in Edge Functions
Audit log any access to vault_secret_id column via the retrieve_credential() function from task-004
Ensure the RLS policy update does not accidentally expand coordinator access to vault_secret_id

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.

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).

Component
Integration Credential Vault
data high
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.