Skip to content

Self-improving AI Employees for everyone

19.5 KB
Raw Blame
"""Separate encrypted database for API keys and OAuth tokens.

This module provides a dedicated SQLite database for storing sensitive credentials
with field-level Fernet encryption. Credentials are encrypted before storage and
decrypted on retrieval.

The database is separate from the main application database (workflow.db) to:
1. Isolate sensitive data from application data
2. Allow different backup/security policies
3. Enable easier credential rotation and management
"""

import hashlib
import logging
import asyncio
from contextlib import asynccontextmanager

from core.session_teardown import teardown_session
from datetime import datetime, timezone
from typing import Any, Dict, List, Optional

from sqlalchemy import Column, JSON
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker
from sqlmodel import Field, SQLModel, select

from core.encryption import EncryptionService

logger = logging.getLogger(__name__)


# --- SQLModel Definitions ---


class CredentialsMetadata(SQLModel, table=True):
    """Stores encryption salt and other metadata."""

    __tablename__ = "credentials_metadata"

    key: str = Field(primary_key=True, max_length=50)
    value: str = Field(max_length=500)


class EncryptedAPIKey(SQLModel, table=True):
    """API keys with encrypted storage."""

    __tablename__ = "encrypted_api_keys"

    id: str = Field(primary_key=True, max_length=255)  # {session_id}_{provider}
    provider: str = Field(max_length=50, index=True)
    session_id: str = Field(default="default", max_length=255)
    key_encrypted: str = Field(max_length=2000)  # Fernet token
    key_hash: str = Field(max_length=64, index=True)  # SHA256[:16] for lookup
    models: Optional[Dict[str, Any]] = Field(default=None, sa_column=Column(JSON))
    is_valid: bool = Field(default=True)
    last_validated: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))
    created_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))
    updated_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))


class EncryptedOAuthToken(SQLModel, table=True):
    """OAuth tokens with encrypted storage."""

    __tablename__ = "oauth_tokens"

    id: Optional[int] = Field(default=None, primary_key=True)
    provider: str = Field(max_length=50, index=True)  # 'google', 'twitter'
    customer_id: str = Field(default="owner", max_length=255, index=True)
    email: Optional[str] = Field(default=None, max_length=255)
    name: Optional[str] = Field(default=None, max_length=255)
    access_token_encrypted: str = Field(max_length=4000)
    refresh_token_encrypted: str = Field(max_length=4000)
    token_expiry: Optional[datetime] = Field(default=None)
    scopes: Optional[str] = Field(default=None, max_length=2000)
    created_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))
    updated_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))


# --- Database Class ---


class CredentialsDatabase:
    """
    Async SQLite database with field-level Fernet encryption.

    Provides secure storage for API keys and OAuth tokens in a separate
    database file from the main application data.

    Usage:
        encryption = EncryptionService()
        credentials_db = CredentialsDatabase("credentials.db", encryption)

        # On app startup
        salt = await credentials_db.initialize()

        # On user login
        encryption.initialize(password, salt)

        # Store/retrieve credentials
        await credentials_db.save_api_key("openai", "sk-xxx", ["gpt-4"])
        api_key = await credentials_db.get_api_key("openai")
    """

    def __init__(self, db_path: str, encryption: EncryptionService):
        """
        Initialize credentials database.

        Args:
            db_path: Path to SQLite database file (e.g., "credentials.db")
            encryption: EncryptionService instance for encrypt/decrypt operations
        """
        self.db_path = db_path
        self.encryption = encryption
        self.engine = create_async_engine(
            f"sqlite+aiosqlite:///{db_path}",
            echo=False,
        )
        self._session_factory = sessionmaker(
            self.engine,
            class_=AsyncSession,
            expire_on_commit=False,
        )
        self._salt: Optional[bytes] = None

    async def initialize(self) -> bytes:
        """
        Initialize database tables and return encryption salt.

        Creates tables if they don't exist. Generates and stores a new salt
        on first run, or retrieves existing salt on subsequent runs.

        Returns:
            Salt bytes for PBKDF2 key derivation
        """
        async with self.engine.begin() as conn:
            await conn.run_sync(SQLModel.metadata.create_all)

        # Get or create salt
        salt_hex = await self._get_metadata("encryption_salt")
        if not salt_hex:
            new_salt = EncryptionService.generate_salt()
            await self._set_metadata("encryption_salt", new_salt.hex())
            self._salt = new_salt
            logger.info("Generated new encryption salt for credentials database")
        else:
            self._salt = bytes.fromhex(salt_hex)
            logger.debug("Loaded existing encryption salt")

        return self._salt

    def get_salt(self) -> Optional[bytes]:
        """Get cached salt (available after initialize())."""
        return self._salt

    @asynccontextmanager
    async def get_session(self):
        """Get async database session.

        Same cancellation-safe teardown as ``core.database.Database``:
        rollback + close run under ``asyncio.shield`` so a cancelled
        caller (a WebSocket client disconnecting during the connect-time
        credential probes) cannot interrupt the pool's reset rollback.
        """
        session = self._session_factory()
        try:
            yield session
        except asyncio.CancelledError:
            await asyncio.shield(teardown_session(session, rollback=True))
            raise
        except Exception:
            await asyncio.shield(teardown_session(session, rollback=True))
            raise
        else:
            await asyncio.shield(teardown_session(session, rollback=False))

    # --- Metadata Operations ---

    async def _get_metadata(self, key: str) -> Optional[str]:
        """Get metadata value by key."""
        async with self.get_session() as session:
            result = await session.execute(select(CredentialsMetadata).where(CredentialsMetadata.key == key))
            row = result.scalars().first()
            return row.value if row else None

    async def _set_metadata(self, key: str, value: str) -> None:
        """Set metadata value."""
        async with self.get_session() as session:
            existing = await session.execute(select(CredentialsMetadata).where(CredentialsMetadata.key == key))
            row = existing.scalars().first()
            if row:
                row.value = value
            else:
                session.add(CredentialsMetadata(key=key, value=value))
            await session.commit()

    # --- API Key Operations ---

    async def save_api_key(
        self,
        provider: str,
        api_key: str,
        models: Optional[List[str]] = None,
        session_id: str = "default",
        model_params: Optional[Dict[str, Dict[str, Any]]] = None,
    ) -> None:
        """
        Save encrypted API key.

        Args:
            provider: Provider name (e.g., "openai", "anthropic")
            api_key: Plaintext API key to encrypt and store
            models: List of available models for this key
            session_id: Session identifier (default: "default")
            model_params: Optional per-model parameters keyed by model id, e.g.
                ``{"qwen2.5-7b": {"context_length": 8192}}``. Used by local
                providers (Ollama, LM Studio) where the context window depends
                on what the user has loaded — the value is fetched from the
                official SDK during validation. Stored alongside the model
                list under the same JSON column (``model_params`` subkey) so
                ``model_registry.get_context_length()`` can read the real
                ctx instead of a JSON default. Cloud providers leave this
                empty — their per-model params live in ``model_registry.json``
                from OpenRouter.
        """
        key_id = f"{session_id}_{provider}"
        encrypted = self.encryption.encrypt(api_key)
        key_hash = hashlib.sha256(api_key.encode()).hexdigest()[:16]
        models_blob: Dict[str, Any] = {"models": models or []}
        if model_params:
            models_blob["model_params"] = model_params

        async with self.get_session() as session:
            existing = await session.get(EncryptedAPIKey, key_id)
            now = datetime.now(timezone.utc)

            if existing:
                existing.key_encrypted = encrypted
                existing.key_hash = key_hash
                existing.models = models_blob
                existing.is_valid = True
                existing.last_validated = now
                existing.updated_at = now
            else:
                session.add(
                    EncryptedAPIKey(
                        id=key_id,
                        provider=provider,
                        session_id=session_id,
                        key_encrypted=encrypted,
                        key_hash=key_hash,
                        models=models_blob,
                        is_valid=True,
                        last_validated=now,
                    )
                )
            await session.commit()
            logger.debug(f"Saved encrypted API key for provider: {provider}")

    async def get_api_key_model_params(self, provider: str, session_id: str = "default") -> Dict[str, Dict[str, Any]]:
        """Return the per-model param map stored alongside the model list.

        Empty dict if the provider has no entry, no params were stored,
        or the row predates the ``model_params`` column. The runtime
        path (``model_registry.get_context_length`` etc.) consults this
        before falling back to JSON defaults so local-LLM context comes
        from the actual loaded model, not a guess.
        """
        key_id = f"{session_id}_{provider}"
        async with self.get_session() as session:
            row = await session.get(EncryptedAPIKey, key_id)
            if not row or not row.models:
                return {}
            params = row.models.get("model_params") or {}
            return params if isinstance(params, dict) else {}

    async def get_api_key(self, provider: str, session_id: str = "default") -> Optional[str]:
        """
        Get decrypted API key.

        Args:
            provider: Provider name
            session_id: Session identifier

        Returns:
            Decrypted API key or None if not found
        """
        key_id = f"{session_id}_{provider}"
        async with self.get_session() as session:
            row = await session.get(EncryptedAPIKey, key_id)
            if not row:
                return None
            try:
                return self.encryption.decrypt(row.key_encrypted)
            except ValueError as e:
                logger.error(f"Failed to decrypt API key for {provider}: {e}")
                return None

    async def get_api_key_info(self, provider: str, session_id: str = "default") -> Optional[Dict[str, Any]]:
        """
        Get API key metadata (without decrypting the key).

        Args:
            provider: Provider name
            session_id: Session identifier

        Returns:
            Dict with models, is_valid, last_validated, or None if not found
        """
        key_id = f"{session_id}_{provider}"
        async with self.get_session() as session:
            row = await session.get(EncryptedAPIKey, key_id)
            if not row:
                return None
            return {
                "provider": row.provider,
                "models": row.models.get("models", []) if row.models else [],
                "is_valid": row.is_valid,
                "last_validated": row.last_validated,
            }

    async def delete_api_key(self, provider: str, session_id: str = "default") -> bool:
        """
        Delete API key.

        Args:
            provider: Provider name
            session_id: Session identifier

        Returns:
            True if key was deleted, False if not found
        """
        key_id = f"{session_id}_{provider}"
        async with self.get_session() as session:
            row = await session.get(EncryptedAPIKey, key_id)
            if row:
                await session.delete(row)
                await session.commit()
                logger.debug(f"Deleted API key for provider: {provider}")
                return True
            return False

    async def list_api_keys(self, session_id: str = "default") -> List[str]:
        """
        List all stored provider names for a session.

        Args:
            session_id: Session identifier

        Returns:
            List of provider names
        """
        async with self.get_session() as session:
            result = await session.execute(select(EncryptedAPIKey.provider).where(EncryptedAPIKey.session_id == session_id))
            return [row[0] for row in result.all()]

    async def list_key_scopes(self, provider: str) -> List[str]:
        """
        List every session_id that has a key stored for one provider.

        The inverse of :meth:`list_api_keys`, which walks the other axis of
        the ``{session_id}_{provider}`` key. Used by plugins that hold
        several accounts for the same provider and need to enumerate them.

        Args:
            provider: Provider name

        Returns:
            Sorted list of session identifiers
        """
        async with self.get_session() as session:
            result = await session.execute(
                select(EncryptedAPIKey.session_id).where(EncryptedAPIKey.provider == provider).distinct()
            )
            return sorted(row[0] for row in result.all())

    async def get_api_key_models(self, provider: str, session_id: str = "default") -> List[str]:
        """
        Get stored models for an API key.

        Args:
            provider: Provider name
            session_id: Session identifier

        Returns:
            List of model names or empty list if not found
        """
        key_id = f"{session_id}_{provider}"
        async with self.get_session() as session:
            row = await session.get(EncryptedAPIKey, key_id)
            if not row or not row.models:
                return []
            return row.models.get("models", [])

    # --- OAuth Token Operations ---

    async def save_oauth_tokens(
        self,
        provider: str,
        access_token: str,
        refresh_token: str,
        email: Optional[str] = None,
        name: Optional[str] = None,
        expiry: Optional[datetime] = None,
        scopes: Optional[str] = None,
        customer_id: str = "owner",
    ) -> None:
        """
        Save encrypted OAuth tokens.

        Args:
            provider: OAuth provider (e.g., "google", "twitter")
            access_token: OAuth access token
            refresh_token: OAuth refresh token
            email: User email from OAuth provider
            name: User display name
            expiry: Token expiration datetime
            scopes: Comma-separated granted scopes
            customer_id: Customer identifier (default: "owner")
        """
        encrypted_access = self.encryption.encrypt(access_token)
        encrypted_refresh = self.encryption.encrypt(refresh_token)

        async with self.get_session() as session:
            existing = await session.execute(
                select(EncryptedOAuthToken).where(
                    EncryptedOAuthToken.provider == provider,
                    EncryptedOAuthToken.customer_id == customer_id,
                )
            )
            row = existing.scalars().first()
            now = datetime.now(timezone.utc)

            if row:
                row.access_token_encrypted = encrypted_access
                row.refresh_token_encrypted = encrypted_refresh
                row.email = email
                row.name = name
                row.token_expiry = expiry
                row.scopes = scopes
                row.updated_at = now
            else:
                session.add(
                    EncryptedOAuthToken(
                        provider=provider,
                        customer_id=customer_id,
                        email=email,
                        name=name,
                        access_token_encrypted=encrypted_access,
                        refresh_token_encrypted=encrypted_refresh,
                        token_expiry=expiry,
                        scopes=scopes,
                    )
                )
            await session.commit()
            logger.debug(f"Saved encrypted OAuth tokens for provider: {provider}")

    async def get_oauth_tokens(self, provider: str, customer_id: str = "owner") -> Optional[Dict[str, Any]]:
        """
        Get decrypted OAuth tokens.

        Args:
            provider: OAuth provider
            customer_id: Customer identifier

        Returns:
            Dict with access_token, refresh_token, email, name, etc.
            or None if not found
        """
        async with self.get_session() as session:
            result = await session.execute(
                select(EncryptedOAuthToken).where(
                    EncryptedOAuthToken.provider == provider,
                    EncryptedOAuthToken.customer_id == customer_id,
                )
            )
            row = result.scalars().first()
            if not row:
                return None

            try:
                return {
                    "access_token": self.encryption.decrypt(row.access_token_encrypted),
                    "refresh_token": self.encryption.decrypt(row.refresh_token_encrypted),
                    "email": row.email,
                    "name": row.name,
                    "token_expiry": row.token_expiry,
                    "scopes": row.scopes,
                }
            except ValueError as e:
                logger.error(f"Failed to decrypt OAuth tokens for {provider}: {e}")
                return None

    async def delete_oauth_tokens(self, provider: str, customer_id: str = "owner") -> bool:
        """
        Delete OAuth tokens.

        Args:
            provider: OAuth provider
            customer_id: Customer identifier

        Returns:
            True if tokens were deleted, False if not found
        """
        async with self.get_session() as session:
            result = await session.execute(
                select(EncryptedOAuthToken).where(
                    EncryptedOAuthToken.provider == provider,
                    EncryptedOAuthToken.customer_id == customer_id,
                )
            )
            row = result.scalars().first()
            if row:
                await session.delete(row)
                await session.commit()
                logger.debug(f"Deleted OAuth tokens for provider: {provider}")
                return True
            return False

    async def list_oauth_providers(self, customer_id: str = "owner") -> List[str]:
        """
        List all OAuth providers with stored tokens.

        Args:
            customer_id: Customer identifier

        Returns:
            List of provider names
        """
        async with self.get_session() as session:
            result = await session.execute(select(EncryptedOAuthToken.provider).where(EncryptedOAuthToken.customer_id == customer_id))
            return [row[0] for row in result.all()]