Implement Supabase Vault wrapper for credential storage

epic-external-system-integration-configuration-foundation-task-004 — Create the vault client layer that wraps Supabase Vault pgsodium functions for storing, retrieving, and rotating API keys. Implement store_credential(), retrieve_credential(), rotate_credential(), and delete_credential() SQL functions. Ensure plaintext is never written to the main database tables and all vault references use opaque vault secret IDs.

critical priority high complexity database pending database specialist Tier 2

Acceptance Criteria

store_credential(p_name TEXT, p_secret TEXT, p_description TEXT DEFAULT '') RETURNS UUID function exists in the vault schema, inserts into vault.secrets via pgsodium, and returns the opaque secret ID

retrieve_credential(p_secret_id UUID) RETURNS TEXT function exists, decrypts and returns the secret value, and can only be called by service_role or a designated trusted function — not by authenticated end users

rotate_credential(p_secret_id UUID, p_new_secret TEXT) RETURNS VOID function updates the vault secret in place without changing the secret ID, so existing references in organization_integrations remain valid

delete_credential(p_secret_id UUID) RETURNS VOID function removes the vault secret and returns cleanly if the ID does not exist (idempotent)

A plaintext audit check constraint or trigger exists to reject any INSERT or UPDATE on organization_integrations that sets a column value matching a common API key pattern (basic heuristic) — defense-in-depth measure

All four functions are created in a numbered migration file and are security definer with explicit search_path set to prevent search path injection

Calling retrieve_credential() as an authenticated (non-service) role returns a permission denied error

store_credential() called twice with the same name creates two independent secrets (names are not unique keys in Vault) — callers must store the returned UUID

All functions handle NULL inputs gracefully with descriptive RAISE EXCEPTION messages

Technical Requirements

frameworks

Supabase

PostgreSQL

pgsodium

apis

Supabase Vault API

pgsodium extension

data models

IntegrationCredentialVault

VaultSecret

performance requirements

store_credential() must complete within 200ms under normal load — pgsodium encryption is in-process and should be fast

retrieve_credential() is only called server-side (Edge Functions) during integration execution, not on every API request — document this access pattern explicitly

security requirements

SECURITY DEFINER must be set on all vault functions with SET search_path = vault, pg_temp to prevent privilege escalation

retrieve_credential() must be REVOKE ALL from PUBLIC and GRANT EXECUTE only to service_role

store_credential() may be granted to authenticated role only if called through a trusted RPC path — otherwise restrict to service_role as well

Secrets must be encrypted at rest by pgsodium using the Supabase-managed key — no custom key management required

Audit log every call to retrieve_credential() including secret_id and calling session — write to a vault_access_log table

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.

View Full Execution Plan

Implementation Notes

Supabase Vault uses the vault schema and vault.secrets table backed by pgsodium. The core insert is: INSERT INTO vault.secrets (name, secret, description) VALUES (p_name, p_secret, p_description) RETURNING id. For retrieval, use: SELECT decrypted_secret FROM vault.decrypted_secrets WHERE id = p_secret_id. The decrypted_secrets view handles the pgsodium decryption transparently.

For rotate_credential(), use: UPDATE vault.secrets SET secret = pgsodium.crypto_aead_det_encrypt(convert_to(p_new_secret, 'utf8'), ...) — check Supabase Vault docs for the exact update mechanism as it may differ from direct pgsodium calls. Create a vault_access_log table (id, secret_id, accessed_at, session_user) and INSERT into it inside retrieve_credential() for auditability. Set a short COMMENT ON FUNCTION to document the security model for each function. Coordinate with task-005 which adds the vault_secret_id column to organization_integrations — this task creates the underlying Vault functions that task-005 relies on.

Testing Requirements

Integration tests against Supabase local environment with pgsodium enabled (required for Vault). Test cases: (1) store_credential() returns a valid UUID and the plaintext is not readable from vault.secrets directly by an authenticated user. (2) retrieve_credential() with the returned UUID returns the original plaintext when called as service_role. (3) retrieve_credential() called as authenticated role throws permission denied.

(4) rotate_credential() with a new value: subsequent retrieve_credential() returns new value, UUID unchanged. (5) delete_credential() removes the secret: subsequent retrieve_credential() throws not-found error. (6) delete_credential() on non-existent UUID returns cleanly without error. (7) store_credential() with NULL secret raises exception.

Wrap all tests in transactions that are rolled back to keep test environment clean.

Component

Integration Credential Vault

data high

Dependencies (1)

Design and write the Supabase migration for the organization_integrations table including all columns: id, organization_id (FK), integration_type (enum referencing registry), status, field_mappings (JSONB), sync_schedule, excluded_features (JSONB array), created_at, updated_at. Include all necessary indexes for org-scoped queries. epic-external-system-integration-configuration-foundation-task-002

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