Skip to content

ADR-001: Unified Integrations Table Architecture

Status: Accepted Date: 2025-11-10 Deciders: root (with AI assistant) Technical Story: docs/specs/spec_threema_integration_v2.md

Context

We are adding Threema as a new notification channel to Swisper Signals. The current implementation stores integration fields directly in the user table:

class User(UserBase, table=True):
    # Telegram integration (notification channel)
    telegram_verification_code: str | None
    telegram_chat_id: str | None
    telegram_connected_at: datetime | None

    # WealthOS integration (data source)
    wealthos_api_key: str | None
    wealthos_connected_at: datetime | None

Problem: This pattern doesn't scale: - Each new integration (Threema, WhatsApp, Signal, Slack, Zapier) requires adding 3-5 fields to User table - Violates separation of concerns (user identity ≠ integrations) - Cannot support multiple integrations of same type per user - Difficult to query "all users with integration X" without scanning entire user table - Migration complexity grows with each integration - Mixes different integration types (notification channels + data sources) with no structure

Decision

For Threema and all future integrations (notification channels AND data sources), we will use a unified integrations table:

class IntegrationType(str, Enum):
    """All integration types in one place"""
    # Notification channels
    THREEMA = "threema"
    # Future: WHATSAPP, SIGNAL, DISCORD, SLACK, etc.

    # Data integrations (when migrated)
    # TELEGRAM, WEALTHOS, GMAIL, OFFICE365, etc.

class UserIntegration(SQLModel, table=True):
    """
    Unified table for ALL user integrations
    One-to-many: User -> UserIntegrations
    """
    __tablename__ = "integrations"

    id: uuid.UUID = Field(default_factory=uuid.uuid4, primary_key=True)
    user_id: uuid.UUID = Field(foreign_key="user.id", index=True)

    integration_type: IntegrationType = Field(index=True)

    # Integration-specific configuration (flexible JSON)
    # Examples:
    # - Threema: {"threema_id": "ABCD1234", "registration_token": "..."}
    # - WealthOS: {"api_key": "...", "last_sync_at": "..."}
    # - Telegram: {"chat_id": "123", "verification_code": "1234"}
    config: dict = Field(default_factory=dict, sa_column=Column(JSON))

    # Connection metadata (common to all integrations)
    is_active: bool = Field(default=True)
    connected_at: datetime
    disconnected_at: datetime | None
    last_used_at: datetime | None

Existing integrations remain unchanged (Telegram, WealthOS in User table) to avoid breaking existing functionality. They will be migrated incrementally (see TD-001).

Rationale

Why Unified Integrations Table?

  1. Ultimate Scalability: Add unlimited integrations (notification channels + data sources) without User table changes
  2. Separation of Concerns: User identity separate from integrations
  3. Unified Management: All integrations managed the same way (consistent API)
  4. Flexibility: Support multiple integrations of same type per user (future: personal + work accounts)
  5. Performance: Indexed queries for "all users with integration X" without full table scan
  6. Soft Delete: Preserve connection history for analytics and audit
  7. Flexible Schema: JSON config field allows per-integration customization without schema changes
  8. Type Safety: Pydantic schemas validate config structure in service layer

Why JSON Config Field?

Pros: - ✅ No schema changes when adding new integration types - ✅ Each integration can have unique fields (Threema ID vs. API key vs. chat ID) - ✅ Easy to add optional fields per integration - ✅ JSONB in PostgreSQL supports indexes and queries when needed

Cons: - ⚠️ No database-level schema enforcement (handled in service layer with Pydantic) - ⚠️ Typos in JSON keys won't be caught by database (caught by validation)

Mitigation: - Use Pydantic schemas for each integration type (e.g., ThreemaConfig, WealthOSConfig) - Validate on save and load in service layer - Type hints in code maintain type safety

Why Keep Telegram/WealthOS in User Table (For Now)?

  1. Lower Risk: Don't break existing Telegram/WealthOS users
  2. Faster Delivery: Focus on Threema implementation
  3. Proof of Concept: Demonstrates unified pattern works before migrating existing integrations
  4. Clear Migration Path: Telegram/WealthOS refactor becomes separate, well-scoped task (TD-001)

Alternatives Considered

Option 1: Keep adding fields to User table - ❌ Doesn't scale - ❌ Poor separation of concerns - ❌ Rejected

Option 2: Separate notification_channels table (only for notifications) - ✅ Clearer purpose (just notifications) - ❌ Still need separate table for data integrations (WealthOS, etc.) - ❌ Creates two new tables instead of one unified table - ❌ Rejected (too narrow scope)

Option 3: Refactor all integrations first, then add Threema - ✅ Consistent architecture from start - ❌ Higher risk (might break Telegram, WealthOS) - ❌ Much slower delivery (3-4 weeks before Threema) - ❌ Rejected (prefer pragmatic incremental approach)

Option 4: Unified integrations table for new integrations, migrate existing later (CHOSEN) - ✅ Lower risk (don't touch working code) - ✅ Faster Threema delivery - ✅ Best pattern demonstrated (unified table for all integration types) - ✅ Clear migration path for existing integrations (TD-001) - ⚠️ Temporary inconsistency (acceptable trade-off)

Consequences

Positive

  • ✅ Ultimate Scalability: Add ANY integration (notifications, data sources, webhooks) without User table changes
  • ✅ Unified Management: All integrations managed the same way (consistent API, predictable patterns)
  • ✅ Flexible Schema: JSON config supports integration-specific needs without migrations
  • ✅ Performance: Efficient indexed queries for integration-specific operations
  • ✅ Audit Trail: Soft deletes preserve connection/disconnection history
  • ✅ Multi-Integration: Can support 2+ integrations of same type per user (future: personal + work accounts)
  • ✅ Type Safety: Pydantic validation in service layer ensures config structure correctness

Negative

  • ⚠️ Inconsistency: Three patterns in codebase (Telegram in User, WealthOS in User, Threema in integrations)
  • ⚠️ Technical Debt: Telegram + WealthOS refactoring postponed (see TD-001)
  • ⚠️ Query Complexity: Need JOIN for "user with Threema" vs. direct field check for Telegram/WealthOS
  • ⚠️ Validation Burden: Must validate JSON config in code (not enforced by database schema)

Risks

  • ⚠️ Developer Confusion: New developers might not understand why three patterns exist

  • Mitigation: Clear code comments referencing this ADR and TD-001 in all relevant files

  • ⚠️ Forgotten Refactor: Telegram/WealthOS might stay in User table indefinitely

  • Mitigation: TD-001 tracked in backlog with Medium priority, revisit when adding 3rd integration

  • ⚠️ Config Validation Bugs: Typos in JSON config keys won't be caught by database

  • Mitigation: Strict Pydantic validation in service layer, comprehensive tests

Implementation

Database Schema

New table: 

CREATE TABLE integrations (
    id UUID PRIMARY KEY,
    user_id UUID REFERENCES "user"(id) ON DELETE CASCADE,
    integration_type VARCHAR(50) NOT NULL,
    config JSONB DEFAULT '{}'::jsonb NOT NULL,
    is_active BOOLEAN DEFAULT TRUE NOT NULL,
    connected_at TIMESTAMP WITH TIME ZONE NOT NULL,
    disconnected_at TIMESTAMP WITH TIME ZONE,
    last_used_at TIMESTAMP WITH TIME ZONE
);

-- Performance indexes
CREATE INDEX ix_integrations_user_id ON integrations(user_id);
CREATE INDEX ix_integrations_integration_type ON integrations(integration_type);
CREATE INDEX ix_integrations_user_type ON integrations(user_id, integration_type);

-- Future: GIN index for JSON queries if needed
-- CREATE INDEX ix_integrations_config ON integrations USING GIN(config);

Code Structure

  • Model: backend/app/models.py - UserIntegration, IntegrationType
  • Migration: backend/app/alembic/versions/[timestamp]_create_integrations_table.py
  • Services: Use UserIntegration for Threema and all future integrations
  • Validation: Pydantic schemas for each integration type's config (in service layer)
  • Documentation: Code comments reference this ADR in models.py and services

Example: Threema Config Validation

# In service layer (e.g., threema_registration_service.py)
from pydantic import BaseModel

class ThreemaConfig(BaseModel):
    """Type-safe config structure for Threema integration"""
    threema_id: str | None = None
    registration_token: str | None = None
    registration_token_expires_at: datetime | None = None

# Usage
def get_threema_config(integration: UserIntegration) -> ThreemaConfig:
    """Get validated Threema config"""
    return ThreemaConfig(**integration.config)
  • Technical Debt: TD-001 - Telegram + WealthOS Migration to integrations table
  • Spec: docs/specs/spec_threema_integration_v2.md
  • Plan: docs/plans/plan_threema_integration_v1.md
  • Code:
  • backend/app/models.py lines ~84-169 (UserIntegration, IntegrationType)
  • Threema services will reference UserIntegration model
  • Tests: backend/tests/api/test_notification_channels_foundation.py (will be renamed to test_integrations_foundation.py)

Scope Note

Initial Implementation (Threema): - Only IntegrationType.THREEMA supported - Proves unified integrations pattern works

Future Scope (incremental migration): - Migrate Telegram to integrations table (TD-001) - Migrate WealthOS to integrations table (TD-001) - Add WhatsApp, Signal, Discord as needed - Add Slack, Zapier, make.com as needed - Consider migrating Gmail/Office365 (separate evaluation)

Key Insight: This ADR establishes the long-term pattern for ALL user integrations, but implementation is incremental to minimize risk.

Status Updates:

  • 2025-11-10: Decision made and documented - unified integrations table for all integration types
  • Future: When all integrations migrated, update this ADR with "Migration Complete" note