stack/apps/api/prisma/migrations/20260207_add_user_credentials/migration.sql

-- User Credentials Storage with RLS Policies
-- This migration adds the user_credentials table for secure storage of user API keys,
-- OAuth tokens, and other credentials with encryption and RLS enforcement.
--
-- Related: #355 - Create UserCredential Prisma model with RLS policies
-- Design: docs/design/credential-security.md (Phase 3a)

-- =============================================================================
-- CREATE ENUMS
-- =============================================================================

-- CredentialType enum: Types of credentials that can be stored
CREATE TYPE "CredentialType" AS ENUM ('API_KEY', 'OAUTH_TOKEN', 'ACCESS_TOKEN', 'SECRET', 'PASSWORD', 'CUSTOM');

-- CredentialScope enum: Access scope for credentials
CREATE TYPE "CredentialScope" AS ENUM ('USER', 'WORKSPACE', 'SYSTEM');

-- =============================================================================
-- EXTEND EXISTING ENUMS
-- =============================================================================

-- Add CREDENTIAL to EntityType for activity logging
ALTER TYPE "EntityType" ADD VALUE 'CREDENTIAL';

-- Add credential-related actions to ActivityAction
ALTER TYPE "ActivityAction" ADD VALUE 'CREDENTIAL_CREATED';
ALTER TYPE "ActivityAction" ADD VALUE 'CREDENTIAL_ACCESSED';
ALTER TYPE "ActivityAction" ADD VALUE 'CREDENTIAL_ROTATED';
ALTER TYPE "ActivityAction" ADD VALUE 'CREDENTIAL_REVOKED';

-- =============================================================================
-- CREATE USER_CREDENTIALS TABLE
-- =============================================================================

CREATE TABLE "user_credentials" (
    "id" UUID NOT NULL DEFAULT uuid_generate_v4(),
    "user_id" UUID NOT NULL,
    "workspace_id" UUID,

    -- Identity
    "name" VARCHAR(255) NOT NULL,
    "provider" VARCHAR(100) NOT NULL,
    "type" "CredentialType" NOT NULL,
    "scope" "CredentialScope" NOT NULL DEFAULT 'USER',

    -- Encrypted storage
    "encrypted_value" TEXT NOT NULL,
    "masked_value" VARCHAR(20),

    -- Metadata
    "description" TEXT,
    "expires_at" TIMESTAMPTZ,
    "last_used_at" TIMESTAMPTZ,
    "metadata" JSONB NOT NULL DEFAULT '{}',

    -- Status
    "is_active" BOOLEAN NOT NULL DEFAULT true,
    "rotated_at" TIMESTAMPTZ,

    -- Audit
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "updated_at" TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT "user_credentials_pkey" PRIMARY KEY ("id")
);

-- =============================================================================
-- CREATE FOREIGN KEY CONSTRAINTS
-- =============================================================================

ALTER TABLE "user_credentials" ADD CONSTRAINT "user_credentials_user_id_fkey"
    FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "user_credentials" ADD CONSTRAINT "user_credentials_workspace_id_fkey"
    FOREIGN KEY ("workspace_id") REFERENCES "workspaces"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- =============================================================================
-- CREATE INDEXES
-- =============================================================================

-- Index for user lookups
CREATE INDEX "user_credentials_user_id_idx" ON "user_credentials"("user_id");

-- Index for workspace lookups
CREATE INDEX "user_credentials_workspace_id_idx" ON "user_credentials"("workspace_id");

-- Index for user + scope queries
CREATE INDEX "user_credentials_user_id_scope_idx" ON "user_credentials"("user_id", "scope");

-- Index for workspace + scope queries
CREATE INDEX "user_credentials_workspace_id_scope_idx" ON "user_credentials"("workspace_id", "scope");

-- Index for scope + active status queries
CREATE INDEX "user_credentials_scope_is_active_idx" ON "user_credentials"("scope", "is_active");

-- =============================================================================
-- CREATE UNIQUE CONSTRAINT
-- =============================================================================

-- Prevent duplicate credentials per user/workspace/provider/name
CREATE UNIQUE INDEX "user_credentials_user_id_workspace_id_provider_name_key"
    ON "user_credentials"("user_id", "workspace_id", "provider", "name");

-- =============================================================================
-- ENABLE FORCE ROW LEVEL SECURITY
-- =============================================================================
-- FORCE means the table owner (mosaic) is also subject to RLS policies.
-- This prevents Prisma (connecting as owner) from bypassing policies.

ALTER TABLE user_credentials ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_credentials FORCE ROW LEVEL SECURITY;

-- =============================================================================
-- RLS POLICIES
-- =============================================================================

-- Owner bypass policy: Allow access to all rows ONLY when no RLS context is set
-- This is required for:
-- 1. Prisma migrations that run without RLS context
-- 2. Database maintenance operations
-- When RLS context IS set (current_user_id() returns non-NULL), this policy does not apply
--
-- NOTE: If connecting as a PostgreSQL superuser (like the default 'mosaic' role),
-- RLS policies are bypassed entirely. For full RLS enforcement, the application
-- should connect as a non-superuser role. See docs/design/credential-security.md
CREATE POLICY user_credentials_owner_bypass ON user_credentials
  FOR ALL
  USING (current_user_id() IS NULL);

-- User access policy: USER-scoped credentials visible only to owner
-- Uses current_user_id() helper from migration 20260129221004_add_rls_policies
CREATE POLICY user_credentials_user_access ON user_credentials
  FOR ALL
  USING (
    scope = 'USER' AND user_id = current_user_id()
  );

-- Workspace admin access policy: WORKSPACE-scoped credentials visible to workspace admins
-- Uses is_workspace_admin() helper from migration 20260129221004_add_rls_policies
CREATE POLICY user_credentials_workspace_access ON user_credentials
  FOR ALL
  USING (
    scope = 'WORKSPACE'
    AND workspace_id IS NOT NULL
    AND is_workspace_admin(workspace_id, current_user_id())
  );

-- SYSTEM-scoped credentials are only accessible via owner bypass policy
-- (when current_user_id() IS NULL, which happens for admin operations)

-- =============================================================================
-- AUDIT TRIGGER
-- =============================================================================

-- Update updated_at timestamp on row changes
CREATE OR REPLACE FUNCTION update_user_credentials_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_credentials_updated_at
    BEFORE UPDATE ON user_credentials
    FOR EACH ROW
    EXECUTE FUNCTION update_user_credentials_updated_at();

-- =============================================================================
-- NOTES
-- =============================================================================
-- This migration creates the foundation for secure credential storage.
-- The encrypted_value column stores ciphertext in one of two formats:
--
-- 1. OpenBao Transit format (preferred): vault:v1:base64data
-- 2. AES-256-GCM fallback format: iv:authTag:encrypted
--
-- The VaultService (issue #353) handles encryption/decryption with automatic
-- fallback to CryptoService when OpenBao is unavailable.
--
-- RLS enforcement ensures:
-- - USER scope: Only the credential owner can access
-- - WORKSPACE scope: Only workspace admins can access
-- - SYSTEM scope: Only accessible via admin/migration bypass