Skip to content

API Connect: connectors and encrypted connections — Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Make the backoffice "API connect" form functional — a hardcoded, modular connector registry; one encrypted connection per connector (entered in the form); a connector_luid datasource per module; and the travel + new headcount providers driven from them via one shared base provider.

Architecture: ConnectorType + a CONNECTORS registry name the integration types (today only EPFL_TABLEAU). A connector_connections table holds the connection (secret_value Fernet-encrypted); a connector_datasources table holds one connector_luid per module. A BaseTableauApiProvider lifts all shared Tableau plumbing out of the travel provider and adds _ensure_credentials(), which loads the datasource + connection from the DB and reads runtime knobs from settings instead of reading credentials from env. The factory routes each module's API ingestion to the right subclass.

Tech Stack: FastAPI, SQLModel, async SQLAlchemy, Alembic, Pydantic v2, cryptography (Scrypt + Fernet), pytest; Vue 3 + Quasar 2 frontend.

See the PRD for context and decisions: 1552-api-connect-tableau-credentials-prd.md.

Global Constraints

  • Encryption key is dedicated and fails closed. Set both CREDENTIALS_ENCRYPTION_KEY and CREDENTIALS_ENCRYPTION_SALT; if either is unset, encrypt/decrypt raise. Never reuse JWT_HMAC_KEY.
  • Only secret_value is encrypted. All other connection fields are identifiers, stored plaintext.
  • Read schemas never expose secret_value. Expose a boolean has_secret. The form secret field is write-only; an empty value on update keeps the stored secret.
  • Runtime knobs stay in env. TABLEAU_VERIFY_SSL, TABLEAU_REQUEST_TIMEOUT_SECONDS, TABLEAU_REST_MIN_API_VERSION, TABLEAU_MAX_FIELDS are read from settings, never stored on the connection.
  • SSRF guard (A06, A10). server_url must be https:// and its host must match CONNECTOR_ALLOWED_HOST_SUFFIXES (comma-separated env; fails closed when empty). Validate on save and before each outbound call. Rate-limit POST …/test.
  • TLS verification on (A02, A04). Warn loudly when TABLEAU_VERIFY_SSL is not true; prod must keep it on.
  • Audit, no PII, sanitised errors (A09, A10). Audit connection/datasource mutations via AuditDocumentService; never log secrets or headcount PII (name, SCIPER); error responses are generic, never raw exceptions/bodies.
  • Validate secrets at boot (A02). A startup check fails the app in non-dev when the encryption keys or the allowlist are unset.
  • Env surfaces updated with config. Any new/removed setting is mirrored in backend/.env.example and helm/templates/backend-deployment.yaml.
  • No backward compat. Remove env-var credential reads (TABLEAU_SERVER_URL, …SITE_CONTENT_URL, …USERNAME, …CONNECTED_APP_*, …DS_FLIGHTS_LUID) from the travel provider once the DB path works (project is pre-v1.x — see memory).
  • No new patterns. Mirror existing model/repo/service/schema/router conventions (models/user.py, repositories/unit_repo.py, services/data_entry_service.py, schemas/user.py).
  • Backend style: functions ≤40 lines / ≤2 nesting; wrap column refs in col(); # type: ignore[code] always with a code; narrow with if x is None: raise ..., never bare assert.
  • Every bug/edge fix ships a regression test.
  • Verification per backend task: cd backend && uv run pytest <path> -v then make type-check and make lint. The user runs the full suite — stop at lint/type-check.
  • Frontend has no JS unit harness (see memory). Verify with cd frontend && make type-check and npm run lint.
  • Migrations via make db-revision — never hand-author; prune any false-positive drop_index lines after autogenerate.
  • PRs target dev. Each tier below is one PR.

File structure

New backend files:

  • backend/app/core/crypto.py — encrypt/decrypt helpers.
  • backend/app/models/connector.py — ConnectorType enum + both tables.
  • backend/app/services/data_ingestion/connectors.py — hardcoded CONNECTORS registry (ConnectorSpec).
  • backend/app/repositories/connector_repo.py — both repos.
  • backend/app/schemas/connector.py — connection + datasource + spec DTOs.
  • backend/app/services/connector_service.py — encryption-aware service.
  • backend/app/api/v1/connectors.py — router.
  • backend/app/services/data_ingestion/api_providers/base_tableau_api_provider.py — shared base provider.
  • backend/app/services/data_ingestion/api_providers/headcount_members_api_provider.py — new provider.
  • Tests under backend/tests/... mirroring each.

Modified backend files:

  • backend/app/core/config.py — new credential settings; drop dead Tableau connection env vars (Tier 4), keep the knobs.
  • backend/pyproject.toml — add cryptography.
  • backend/app/services/data_ingestion/api_providers/professional_travel_api_provider.py — subclass the base, drop env reads.
  • backend/app/services/data_ingestion/provider_factory.py — register headcount provider.
  • The v1 router aggregator — include the new router.

Frontend (Tier 6):

  • frontend/src/stores/connectors.ts (new) — API client + state.
  • frontend/src/composables/useDataEntryDialog.ts — wire submit to save the connection + datasource, then dispatch.
  • frontend/src/components/molecules/data-management/DataEntryDialogContent.vue — connector dropdown, the 6-field connection form, the per-module LUID.

Tier 1 — Encryption foundation (PR 1)

Adds the dedicated key settings, the cryptography dependency, and the encrypt/decrypt helpers. Independently testable: round-trip + fail-closed.

Task 1.1: Add cryptography dependency and config settings

Files:

  • Modify: backend/pyproject.toml
  • Modify: backend/app/core/config.py:165 (after the Tableau block)
  • Modify: backend/.env.example
  • Modify: helm/templates/backend-deployment.yaml

  • Step 1: Add the dependency

cd backend && uv add cryptography
  • Step 2: Add settings after TABLEAU_MAX_FIELDS in config.py:
    # Credential encryption (required to store connection secrets)
    CREDENTIALS_ENCRYPTION_KEY: str = Field(
        default="",
        description=(
            "URL-safe base64 secret (>=32 bytes) used to derive the Fernet "
            "key that encrypts stored connection secrets. Provide via env or "
            "a secret manager only; never commit."
        ),
    )
    CREDENTIALS_ENCRYPTION_SALT: str = Field(
        default="",
        description=(
            "URL-safe base64 salt (>=16 bytes) for credential key derivation. "
            "Keep stable for the lifetime of the encrypted data."
        ),
    )
    # SSRF guard: host suffixes a connection server_url may target.
    CONNECTOR_ALLOWED_HOST_SUFFIXES: str = Field(
        default="",
        description=(
            "Comma-separated host suffixes an API-connection server_url may "
            "target (e.g. 'epfl.ch'). SSRF guard; empty fails closed."
        ),
    )
  • Step 3: Mirror the new vars in backend/.env.example (near FILES_ENCRYPTION_*), with safe dev defaults:
CREDENTIALS_ENCRYPTION_KEY=
CREDENTIALS_ENCRYPTION_SALT=
CONNECTOR_ALLOWED_HOST_SUFFIXES=epfl.ch
  • Step 4: Mirror them in helm/templates/backend-deployment.yaml — inject the two encryption keys via existingSecret (copy the FILES_ENCRYPTION_KEY / FILES_ENCRYPTION_SALT block at lines 125-134), and CONNECTOR_ALLOWED_HOST_SUFFIXES as a plain value/config key.

  • Step 5: Add a startup validation so a misconfigured prod fails at boot, not on first save. Call it at the top of lifespan in backend/app/main.py (line ~28). The settings expose no ENV string — the only environment discriminator is LOCAL_ENVIRONMENT: bool (config.py:32); gate on it:

def assert_security_settings(settings) -> None:
    """Fail closed at boot outside local/dev when security settings are missing."""
    if settings.LOCAL_ENVIRONMENT:
        return
    missing = [
        name
        for name in (
            "CREDENTIALS_ENCRYPTION_KEY",
            "CREDENTIALS_ENCRYPTION_SALT",
            "CONNECTOR_ALLOWED_HOST_SUFFIXES",
        )
        if not getattr(settings, name)
    ]
    if missing:
        raise RuntimeError(f"Missing required security settings: {missing}")

Integration tests boot the real app via TestClient(app); make sure the test env runs with LOCAL_ENVIRONMENT=true (set it in backend/tests/conftest.py if it is not already) so the check never trips the suite.

  • Step 6: Verify it imports

Run: cd backend && uv run python -c "from app.core.config import get_settings; get_settings()" Expected: no error.

  • Step 7: Commit
git add backend/pyproject.toml backend/uv.lock backend/app/core/config.py backend/.env.example helm/templates/backend-deployment.yaml backend/app/main.py
git commit -m "feat(config): credential encryption + SSRF allowlist settings and boot check"

Task 1.2: Encrypt/decrypt helpers (TDD)

Files:

  • Modify: backend/app/core/crypto.py (exists as a docstring-only stub — replace its body)
  • Test: backend/tests/unit/core/test_crypto.py

Interfaces:

  • Produces: encrypt_secret(plaintext: str) -> str, decrypt_secret(token: str) -> str. Both raise RuntimeError when keys are unset.

  • Step 1: Write the failing tests

import pytest
from app.core import crypto


def test_round_trip(monkeypatch):
    monkeypatch.setenv("CREDENTIALS_ENCRYPTION_KEY", "dev-key-material-please-change")
    monkeypatch.setenv("CREDENTIALS_ENCRYPTION_SALT", "dev-salt-please-change")
    crypto.get_settings.cache_clear()  # settings are lru_cached
    token = crypto.encrypt_secret("super-secret-value")
    assert token != "super-secret-value"
    assert crypto.decrypt_secret(token) == "super-secret-value"


def test_fails_closed_without_key(monkeypatch):
    monkeypatch.setenv("CREDENTIALS_ENCRYPTION_KEY", "")
    monkeypatch.setenv("CREDENTIALS_ENCRYPTION_SALT", "")
    crypto.get_settings.cache_clear()
    with pytest.raises(RuntimeError):
        crypto.encrypt_secret("x")
  • Step 2: Run to verify it fails

Run: cd backend && uv run pytest tests/unit/core/test_crypto.py -v Expected: FAIL — ModuleNotFoundError: app.core.crypto.

  • Step 3: Implement crypto.py
import base64

from cryptography.fernet import Fernet
from cryptography.hazmat.primitives.kdf.scrypt import Scrypt

from app.core.config import get_settings


def _derive_fernet_key() -> bytes:
    """Derive a Fernet key from the dedicated credential key + salt.

    Fails closed: raises if either secret is unset so a connection can never
    be stored or read without configured encryption.
    """
    settings = get_settings()
    key = settings.CREDENTIALS_ENCRYPTION_KEY
    salt = settings.CREDENTIALS_ENCRYPTION_SALT
    if not key or not salt:
        raise RuntimeError(
            "CREDENTIALS_ENCRYPTION_KEY and CREDENTIALS_ENCRYPTION_SALT must "
            "be set to store or read connection secrets"
        )
    kdf = Scrypt(salt=salt.encode(), length=32, n=2**14, r=8, p=1)
    return base64.urlsafe_b64encode(kdf.derive(key.encode()))


def encrypt_secret(plaintext: str) -> str:
    """Return a Fernet token for ``plaintext`` (URL-safe base64 string)."""
    return Fernet(_derive_fernet_key()).encrypt(plaintext.encode()).decode()


def decrypt_secret(token: str) -> str:
    """Return the plaintext for a Fernet ``token`` produced by encrypt."""
    return Fernet(_derive_fernet_key()).decrypt(token.encode()).decode()
  • Step 4: Run to verify it passes

Run: cd backend && uv run pytest tests/unit/core/test_crypto.py -v Expected: PASS (2 tests).

  • Step 5: Type-check, lint, commit
cd backend && make type-check && make lint
git add backend/app/core/crypto.py backend/tests/unit/core/test_crypto.py
git commit -m "feat(crypto): Fernet encrypt/decrypt for connection secrets"

Task 1.3: SSRF URL allowlist validator (TDD)

Files:

  • Create: backend/app/core/url_safety.py
  • Test: backend/tests/unit/core/test_url_safety.py

Interfaces:

  • Produces: validate_external_url(url: str) -> str — returns the URL if it is https:// and its host matches a configured suffix; raises ValueError otherwise. Empty allowlist → raise (fail closed).

  • Step 1: Write the failing tests

import pytest
from app.core import url_safety


def _set(monkeypatch, suffixes):
    monkeypatch.setenv("CONNECTOR_ALLOWED_HOST_SUFFIXES", suffixes)
    url_safety.get_settings.cache_clear()


def test_allows_https_matching_suffix(monkeypatch):
    _set(monkeypatch, "epfl.ch")
    assert url_safety.validate_external_url("https://tableau.epfl.ch/") == (
        "https://tableau.epfl.ch/"
    )


@pytest.mark.parametrize(
    "url",
    [
        "http://tableau.epfl.ch/",        # not https
        "https://tableau.evil.com/",      # host not allowed
        "https://evil-epfl.ch/",          # suffix not on a dot boundary
        "https://169.254.169.254/",       # cloud metadata
    ],
)
def test_rejects_unsafe(monkeypatch, url):
    _set(monkeypatch, "epfl.ch")
    with pytest.raises(ValueError):
        url_safety.validate_external_url(url)


def test_fails_closed_when_allowlist_empty(monkeypatch):
    _set(monkeypatch, "")
    with pytest.raises(ValueError):
        url_safety.validate_external_url("https://tableau.epfl.ch/")
  • Step 2: Run to verify it fails

Run: cd backend && uv run pytest tests/unit/core/test_url_safety.py -v Expected: FAIL — ModuleNotFoundError: app.core.url_safety.

  • Step 3: Implement url_safety.py
from urllib.parse import urlparse

from app.core.config import get_settings


def _allowed_suffixes() -> list[str]:
    raw = get_settings().CONNECTOR_ALLOWED_HOST_SUFFIXES
    return [s.strip().lower() for s in raw.split(",") if s.strip()]


def validate_external_url(url: str) -> str:
    """Return ``url`` if it is https and its host matches the allowlist.

    SSRF guard for a form-supplied ``server_url``. Fails closed when the
    allowlist is empty. Suffix match is on a dot boundary so ``evil-epfl.ch``
    does not match ``epfl.ch``.
    """
    suffixes = _allowed_suffixes()
    if not suffixes:
        raise ValueError("CONNECTOR_ALLOWED_HOST_SUFFIXES is not configured")
    parsed = urlparse(url)
    if parsed.scheme != "https":
        raise ValueError("server_url must use https")
    host = (parsed.hostname or "").lower()
    if not any(host == s or host.endswith("." + s) for s in suffixes):
        raise ValueError(f"server_url host {host!r} is not in the allowlist")
    return url
  • Step 4: Run to verify it passes

Run: cd backend && uv run pytest tests/unit/core/test_url_safety.py -v Expected: PASS.

  • Step 5: Type-check, lint, commit
cd backend && make type-check && make lint
git add backend/app/core/url_safety.py backend/tests/unit/core/test_url_safety.py
git commit -m "feat(security): https + host-allowlist SSRF guard for server_url"

Tier 2 — Data layer: connector registry, models, migration, repos, schemas (PR 2)

Task 2.1: Connector enum, models, registry

Files:

  • Create: backend/app/models/connector.py
  • Create: backend/app/services/data_ingestion/connectors.py

Interfaces:

  • Produces: ConnectorType (str enum: EPFL_TABLEAU), ConnectorConnection(table=True), ConnectorDatasource(table=True), ConnectorSpec, CONNECTORS, list_connectors().

  • Step 1: Write the models (mirror models/user.py conventions)

from datetime import datetime, timezone
from enum import Enum
from typing import Optional

from sqlalchemy import Column, DateTime
from sqlalchemy import Enum as SAEnum
from sqlmodel import Field, SQLModel


class ConnectorType(str, Enum):
    EPFL_TABLEAU = "EPFL_TABLEAU"


def _utcnow() -> datetime:
    return datetime.now(timezone.utc)


class ConnectorConnection(SQLModel, table=True):
    """One connection per connector (where + who + connected-app creds).

    ``secret_value`` is stored encrypted (Fernet token); every other column
    is an identifier entered in the form.
    """

    __tablename__ = "connector_connections"

    id: Optional[int] = Field(default=None, primary_key=True, index=True)
    connector: ConnectorType = Field(
        sa_column=Column(
            SAEnum(ConnectorType, name="connector_type_enum", native_enum=True),
            nullable=False,
            unique=True,
        ),
    )
    label: str = Field(nullable=False)
    server_url: str = Field(nullable=False)
    site_content_url: Optional[str] = Field(default=None, nullable=True)
    username: str = Field(nullable=False)
    client_id: str = Field(nullable=False)
    secret_id: str = Field(nullable=False)
    secret_value_encrypted: str = Field(nullable=False)
    is_active: bool = Field(default=True, nullable=False)
    created_at: datetime = Field(
        default_factory=_utcnow,
        sa_column=Column(DateTime(timezone=True), nullable=False),
    )
    updated_at: datetime = Field(
        default_factory=_utcnow,
        sa_column=Column(DateTime(timezone=True), nullable=False),
    )


class ConnectorDatasource(SQLModel, table=True):
    """One datasource (LUID) for one module, owned by a connection."""

    __tablename__ = "connector_datasources"

    id: Optional[int] = Field(default=None, primary_key=True, index=True)
    connection_id: int = Field(
        foreign_key="connector_connections.id", nullable=False, index=True
    )
    module_type_id: int = Field(nullable=False, index=True)
    data_entry_type_id: Optional[int] = Field(default=None, nullable=True)
    connector_luid: str = Field(nullable=False)
    label: str = Field(nullable=False)
    is_active: bool = Field(default=True, nullable=False)
    created_at: datetime = Field(
        default_factory=_utcnow,
        sa_column=Column(DateTime(timezone=True), nullable=False),
    )
    updated_at: datetime = Field(
        default_factory=_utcnow,
        sa_column=Column(DateTime(timezone=True), nullable=False),
    )
  • Step 2: Write the hardcoded registry (connectors.py):
from dataclasses import dataclass

from app.models.connector import ConnectorType


@dataclass(frozen=True)
class ConnectorSpec:
    connector: ConnectorType
    label: str
    form_fields: tuple[str, ...]


# Modular registry: a new integration is one more entry here plus a provider
# subclass. The form renders ``form_fields`` for the chosen connector.
CONNECTORS: dict[ConnectorType, ConnectorSpec] = {
    ConnectorType.EPFL_TABLEAU: ConnectorSpec(
        connector=ConnectorType.EPFL_TABLEAU,
        label="EPFL Tableau",
        form_fields=(
            "server_url",
            "site_content_url",
            "username",
            "client_id",
            "secret_id",
            "secret_value",
        ),
    ),
}


def list_connectors() -> list[ConnectorSpec]:
    return list(CONNECTORS.values())
  • Step 3: Register models for metadata discovery. Add to app/models/__init__.py (or the Alembic env.py import surface):
from app.models.connector import (  # noqa: F401
    ConnectorConnection,
    ConnectorDatasource,
    ConnectorType,
)
  • Step 4: Verify imports

Run: cd backend && uv run python -c "from app.services.data_ingestion.connectors import list_connectors; print([c.connector for c in list_connectors()])" Expected: [<ConnectorType.EPFL_TABLEAU: 'EPFL_TABLEAU'>].

  • Step 5: Commit
git add backend/app/models/connector.py backend/app/services/data_ingestion/connectors.py backend/app/models/__init__.py
git commit -m "feat(models): connector type, connection + datasource tables, registry"

Task 2.2: Alembic migration

Files:

  • Create: backend/alembic/versions/<generated>.py

  • Step 1: Autogenerate

cd backend && make db-revision message="connector connections and datasources"
  • Step 2: Review the generated file. Confirm it creates both tables, the unique constraint on connector_connections.connector, the FK + index on connector_datasources.connection_id, and the connector_type_enum type. Add a partial unique index for one active datasource per module target:
    op.create_index(
        "uq_active_datasource_per_module",
        "connector_datasources",
        ["module_type_id", sa.text("COALESCE(data_entry_type_id, 0)")],
        unique=True,
        postgresql_where=sa.text("is_active = true"),
    )

Postgres treats each NULL as distinct, so a plain column index would not catch duplicates when data_entry_type_id is NULL (the common case); the COALESCE expression closes that gap. Mirror it in downgrade() and prune any false-positive drop_index lines on unrelated tables (see memory: Alembic autogenerate quirk).

  • Step 3: Apply and verify

Run: cd backend && make db-migrate Expected: upgrade to head with no error.

  • Step 4: Commit
git add backend/alembic/versions/
git commit -m "feat(db): migration for connector connections and datasources"

Task 2.3: Repositories (TDD)

Files:

  • Create: backend/app/repositories/connector_repo.py
  • Test: backend/tests/unit/repositories/test_connector_repo.py

Interfaces:

  • Produces: ConnectorConnectionRepository(session) with get_by_connector(connector) -> Optional[ConnectorConnection], get_by_id(id) -> Optional[ConnectorConnection], upsert(conn) -> ConnectorConnection.
  • ConnectorDatasourceRepository(session) with get_active_for_module(module_type_id, data_entry_type_id) -> Optional[ConnectorDatasource], list_for_connection(connection_id) -> list[ConnectorDatasource], upsert(ds) -> ConnectorDatasource.

  • Step 1: Write the failing test (use the real async db_session fixture from backend/tests/conftest.py:51 — note tests/unit/repositories/test_unit_repo.py mocks its session instead; do not copy that pattern here):

import pytest

from app.models.connector import ConnectorConnection, ConnectorType
from app.repositories.connector_repo import ConnectorConnectionRepository


@pytest.mark.asyncio
async def test_get_by_connector_returns_saved_connection(db_session):
    repo = ConnectorConnectionRepository(db_session)
    saved = await repo.upsert(
        ConnectorConnection(
            connector=ConnectorType.EPFL_TABLEAU,
            label="EPFL Tableau",
            server_url="https://tableau.epfl.ch/",
            site_content_url="co2fp",
            username="svc-calcco2-epfl-api",
            client_id="cid",
            secret_id="sid",
            secret_value_encrypted="enc",
        )
    )
    await db_session.commit()
    found = await repo.get_by_connector(ConnectorType.EPFL_TABLEAU)
    assert found is not None
    assert found.id == saved.id
    assert found.server_url == "https://tableau.epfl.ch/"
  • Step 2: Run to verify it fails

Run: cd backend && uv run pytest tests/unit/repositories/test_connector_repo.py -v Expected: FAIL — module not found.

  • Step 3: Implement the repos (mirror unit_repo.py):
from typing import Optional

from sqlmodel import col, select
from sqlmodel.ext.asyncio.session import AsyncSession

from app.models.connector import (
    ConnectorConnection,
    ConnectorDatasource,
    ConnectorType,
)


class ConnectorConnectionRepository:
    def __init__(self, session: AsyncSession):
        self.session = session

    async def get_by_connector(
        self, connector: ConnectorType
    ) -> Optional[ConnectorConnection]:
        result = await self.session.exec(
            select(ConnectorConnection).where(
                col(ConnectorConnection.connector) == connector
            )
        )
        return result.one_or_none()

    async def get_by_id(self, conn_id: int) -> Optional[ConnectorConnection]:
        result = await self.session.exec(
            select(ConnectorConnection).where(
                col(ConnectorConnection.id) == conn_id
            )
        )
        return result.one_or_none()

    async def upsert(self, conn: ConnectorConnection) -> ConnectorConnection:
        self.session.add(conn)
        await self.session.flush()
        await self.session.refresh(conn)
        return conn


class ConnectorDatasourceRepository:
    def __init__(self, session: AsyncSession):
        self.session = session

    async def get_active_for_module(
        self, module_type_id: int, data_entry_type_id: Optional[int] = None
    ) -> Optional[ConnectorDatasource]:
        query = select(ConnectorDatasource).where(
            col(ConnectorDatasource.module_type_id) == module_type_id,
            col(ConnectorDatasource.is_active).is_(True),
        )
        if data_entry_type_id is not None:
            query = query.where(
                col(ConnectorDatasource.data_entry_type_id) == data_entry_type_id
            )
        result = await self.session.exec(query)
        return result.first()

    async def list_for_connection(
        self, connection_id: int
    ) -> list[ConnectorDatasource]:
        result = await self.session.exec(
            select(ConnectorDatasource).where(
                col(ConnectorDatasource.connection_id) == connection_id
            )
        )
        return list(result.all())

    async def upsert(self, ds: ConnectorDatasource) -> ConnectorDatasource:
        self.session.add(ds)
        await self.session.flush()
        await self.session.refresh(ds)
        return ds
  • Step 4: Run to verify it passes

Run: cd backend && uv run pytest tests/unit/repositories/test_connector_repo.py -v Expected: PASS.

  • Step 5: Type-check, lint, commit
cd backend && make type-check && make lint
git add backend/app/repositories/connector_repo.py backend/tests/unit/repositories/test_connector_repo.py
git commit -m "feat(repo): connector connection + datasource repositories"

Task 2.4: Pydantic schemas

Files:

  • Create: backend/app/schemas/connector.py

Interfaces:

  • Produces: ConnectorSpecRead, ConnectorConnectionCreate (also doubles as the update payload — secret_value optional, blank keeps stored), ConnectorConnectionRead (no secret; has_secret: bool), ConnectorDatasourceCreate, ConnectorDatasourceRead.

  • Step 1: Write the schemas (mirror schemas/user.py):

from datetime import datetime
from typing import Optional

from pydantic import BaseModel

from app.models.connector import ConnectorType


class ConnectorSpecRead(BaseModel):
    connector: ConnectorType
    label: str
    form_fields: list[str]


class ConnectorConnectionCreate(BaseModel):
    label: str
    server_url: str
    site_content_url: Optional[str] = None
    username: str
    client_id: str
    secret_id: str
    secret_value: Optional[str] = None  # required on create; blank keeps on update


class ConnectorConnectionRead(BaseModel):
    id: int
    connector: ConnectorType
    label: str
    server_url: str
    site_content_url: Optional[str]
    username: str
    client_id: str
    secret_id: str
    has_secret: bool  # never expose the secret itself
    is_active: bool
    created_at: datetime
    updated_at: datetime


class ConnectorDatasourceCreate(BaseModel):
    module_type_id: int
    data_entry_type_id: Optional[int] = None
    connector_luid: str
    label: str


class ConnectorDatasourceRead(BaseModel):
    id: int
    connection_id: int
    module_type_id: int
    data_entry_type_id: Optional[int]
    connector_luid: str
    label: str
    is_active: bool
  • Step 2: Verify import

Run: cd backend && uv run python -c "from app.schemas.connector import ConnectorConnectionRead" Expected: no error.

  • Step 3: Commit
git add backend/app/schemas/connector.py
git commit -m "feat(schemas): connector connection + datasource DTOs (secret write-only)"

Tier 3 — Service + API (PR 3)

Task 3.1: Service (TDD)

Files:

  • Create: backend/app/services/connector_service.py
  • Test: backend/tests/unit/services/test_connector_service.py

Interfaces:

  • Produces: ConnectorConnectionService(session) with get_by_connector(connector) -> Optional[ConnectorConnection], save_connection(connector, payload) -> ConnectorConnection (encrypts secret_value; blank secret_value on an existing row keeps the stored secret), to_read(conn) -> ConnectorConnectionRead, get_decrypted_secret(conn) -> str, save_datasource(connection_id, payload) -> ConnectorDatasource.

  • Step 1: Write the failing test

import pytest

from app.core import crypto
from app.models.connector import ConnectorType
from app.schemas.connector import ConnectorConnectionCreate
from app.services.connector_service import ConnectorConnectionService


@pytest.mark.asyncio
async def test_save_encrypts_blank_keeps_and_read_hides_secret(
    db_session, monkeypatch
):
    monkeypatch.setenv("CREDENTIALS_ENCRYPTION_KEY", "dev-key-material")
    monkeypatch.setenv("CREDENTIALS_ENCRYPTION_SALT", "dev-salt")
    crypto.get_settings.cache_clear()

    service = ConnectorConnectionService(db_session)
    base = ConnectorConnectionCreate(
        label="EPFL Tableau",
        server_url="https://tableau.epfl.ch/",
        site_content_url="co2fp",
        username="svc-calcco2-epfl-api",
        client_id="cid",
        secret_id="sid",
        secret_value="the-real-secret",
    )
    conn = await service.save_connection(ConnectorType.EPFL_TABLEAU, base)
    await db_session.commit()
    assert conn.secret_value_encrypted != "the-real-secret"
    assert service.get_decrypted_secret(conn) == "the-real-secret"

    # blank secret on update keeps the stored value
    base.username = "changed"
    base.secret_value = None
    conn2 = await service.save_connection(ConnectorType.EPFL_TABLEAU, base)
    await db_session.commit()
    assert conn2.username == "changed"
    assert service.get_decrypted_secret(conn2) == "the-real-secret"

    read = service.to_read(conn2)
    assert read.has_secret is True
    assert not hasattr(read, "secret_value")
  • Step 2: Run to verify it fails

Run: cd backend && uv run pytest tests/unit/services/test_connector_service.py -v Expected: FAIL — module not found.

  • Step 3: Implement the service
from typing import Optional

from sqlmodel.ext.asyncio.session import AsyncSession

from app.core.crypto import decrypt_secret, encrypt_secret
from app.core.url_safety import validate_external_url
from app.models.connector import (
    ConnectorConnection,
    ConnectorDatasource,
    ConnectorType,
)
from app.repositories.connector_repo import (
    ConnectorConnectionRepository,
    ConnectorDatasourceRepository,
)
from app.schemas.connector import (
    ConnectorConnectionCreate,
    ConnectorConnectionRead,
    ConnectorDatasourceCreate,
)


class ConnectorConnectionService:
    def __init__(self, session: AsyncSession):
        self.session = session
        self.repo = ConnectorConnectionRepository(session)
        self.datasources = ConnectorDatasourceRepository(session)

    async def get_by_connector(
        self, connector: ConnectorType
    ) -> Optional[ConnectorConnection]:
        return await self.repo.get_by_connector(connector)

    async def save_connection(
        self, connector: ConnectorType, payload: ConnectorConnectionCreate
    ) -> ConnectorConnection:
        """Create or replace the single connection for a connector.

        A blank ``secret_value`` on an existing row keeps the stored secret;
        on a new row it is required.
        """
        validate_external_url(payload.server_url)  # SSRF guard; raises on bad host
        existing = await self.repo.get_by_connector(connector)
        if existing is None and not payload.secret_value:
            raise ValueError("secret_value is required for a new connection")
        target = existing or ConnectorConnection(
            connector=connector, secret_value_encrypted="",
            label=payload.label, server_url=payload.server_url,
            username=payload.username, client_id=payload.client_id,
            secret_id=payload.secret_id,
        )
        target.label = payload.label
        target.server_url = payload.server_url
        target.site_content_url = payload.site_content_url
        target.username = payload.username
        target.client_id = payload.client_id
        target.secret_id = payload.secret_id
        if payload.secret_value:
            target.secret_value_encrypted = encrypt_secret(payload.secret_value)
        return await self.repo.upsert(target)

    def get_decrypted_secret(self, conn: ConnectorConnection) -> str:
        return decrypt_secret(conn.secret_value_encrypted)

    def to_read(self, conn: ConnectorConnection) -> ConnectorConnectionRead:
        if conn.id is None:
            raise ValueError("connection must be persisted before read")
        return ConnectorConnectionRead(
            id=conn.id,
            connector=conn.connector,
            label=conn.label,
            server_url=conn.server_url,
            site_content_url=conn.site_content_url,
            username=conn.username,
            client_id=conn.client_id,
            secret_id=conn.secret_id,
            has_secret=bool(conn.secret_value_encrypted),
            is_active=conn.is_active,
            created_at=conn.created_at,
            updated_at=conn.updated_at,
        )

    async def save_datasource(
        self, connection_id: int, payload: ConnectorDatasourceCreate
    ) -> ConnectorDatasource:
        existing = await self.datasources.get_active_for_module(
            payload.module_type_id, payload.data_entry_type_id
        )
        target = existing or ConnectorDatasource(
            connection_id=connection_id,
            module_type_id=payload.module_type_id,
            data_entry_type_id=payload.data_entry_type_id,
            connector_luid=payload.connector_luid,
            label=payload.label,
        )
        target.connection_id = connection_id
        target.connector_luid = payload.connector_luid
        target.label = payload.label
        return await self.datasources.upsert(target)
  • Step 4: Add an SSRF-rejection test to the same file — a non-allowlisted or http:// server_url must raise before any row is written:
@pytest.mark.asyncio
async def test_save_rejects_disallowed_server_url(db_session, monkeypatch):
    monkeypatch.setenv("CREDENTIALS_ENCRYPTION_KEY", "dev-key-material")
    monkeypatch.setenv("CREDENTIALS_ENCRYPTION_SALT", "dev-salt")
    monkeypatch.setenv("CONNECTOR_ALLOWED_HOST_SUFFIXES", "epfl.ch")
    crypto.get_settings.cache_clear()
    from app.core import url_safety

    url_safety.get_settings.cache_clear()

    service = ConnectorConnectionService(db_session)
    with pytest.raises(ValueError):
        await service.save_connection(
            ConnectorType.EPFL_TABLEAU,
            ConnectorConnectionCreate(
                label="x", server_url="https://tableau.evil.com/",
                username="u", client_id="c", secret_id="s",
                secret_value="v",
            ),
        )
  • Step 5: Audit connection mutations. In upsert_connection / upsert_datasource (the router, Task 3.2) record the change via the existing AuditDocumentService (entity ConnectorConnection / ConnectorDatasource, who + when + connector — never the secret), mirroring create_version(...) as used in base_provider.create_job.

  • Step 6: Run to verify it passes

Run: cd backend && uv run pytest tests/unit/services/test_connector_service.py -v Expected: PASS.

  • Step 7: Type-check, lint, commit
cd backend && make type-check && make lint
git add backend/app/services/connector_service.py backend/tests/unit/services/test_connector_service.py
git commit -m "feat(service): encryption-aware connector service with SSRF guard"

Task 3.2: Router (TDD)

Files:

  • Create: backend/app/api/v1/connectors.py
  • Modify: the v1 router aggregator (mirror how files.router is included).
  • Test: backend/tests/integration/v1/test_connectors.py

Interfaces:

  • Produces these routes. Gate: /sync/dispatch checks permissions inline in its body, not via a dependency — reuse instead the Depends(require_permission("backoffice.configuration", "edit")) pattern from sync_module_factors (api/v1/data_sync.py:1005), the same permission the dispatch path requires for global syncs. Deny by default:
  • GET /api/v1/connectors → list[ConnectorSpecRead]
  • GET /api/v1/connectors/{connector}/connection → ConnectorConnectionRead | null
  • PUT /api/v1/connectors/{connector}/connection (body ConnectorConnectionCreate) → ConnectorConnectionRead
  • POST /api/v1/connectors/{connector}/datasources (body ConnectorDatasourceCreate) → ConnectorDatasourceRead
  • POST /api/v1/connectors/{connector}/test → { "ok": bool, "detail": str }

  • Step 1: Write the failing test. There is no shared authed-client fixture; follow tests/integration/v1/test_auth.py / test_permission_scope_e2e.py: a local sync TestClient(app) fixture, auth faked by monkeypatching decode_jwt + UserService.get_by_id, DB via app.dependency_overrides on the session dep, overrides cleared after each test. Adapt the snippet below (written async) to that sync idiom:

import pytest


@pytest.mark.asyncio
async def test_list_then_put_then_get_hides_secret(authed_client, monkeypatch):
    monkeypatch.setenv("CREDENTIALS_ENCRYPTION_KEY", "dev-key-material")
    monkeypatch.setenv("CREDENTIALS_ENCRYPTION_SALT", "dev-salt")
    from app.core import crypto

    crypto.get_settings.cache_clear()

    listed = await authed_client.get("/api/v1/connectors")
    assert listed.status_code == 200
    assert listed.json()[0]["connector"] == "EPFL_TABLEAU"

    body = {
        "label": "EPFL Tableau",
        "server_url": "https://tableau.epfl.ch/",
        "site_content_url": "co2fp",
        "username": "svc-calcco2-epfl-api",
        "client_id": "cid",
        "secret_id": "sid",
        "secret_value": "the-real-secret",
    }
    put = await authed_client.put(
        "/api/v1/connectors/EPFL_TABLEAU/connection", json=body
    )
    assert put.status_code == 200
    assert "secret_value" not in put.json()
    assert put.json()["has_secret"] is True

    got = await authed_client.get("/api/v1/connectors/EPFL_TABLEAU/connection")
    assert got.status_code == 200
    assert got.json()["client_id"] == "cid"
    assert "secret_value" not in got.json()
  • Step 2: Run to verify it fails

Run: cd backend && uv run pytest tests/integration/v1/test_connectors.py -v Expected: FAIL — 404 (routes not registered).

  • Step 3: Implement the router
from fastapi import APIRouter, Depends, HTTPException
from sqlmodel.ext.asyncio.session import AsyncSession

from app.api.deps import get_db  # same session dep the data_sync routes use (verify name)
from app.models.connector import ConnectorType
from app.schemas.connector import (
    ConnectorConnectionCreate,
    ConnectorConnectionRead,
    ConnectorDatasourceCreate,
    ConnectorDatasourceRead,
    ConnectorSpecRead,
)
from app.services.connector_service import ConnectorConnectionService
from app.services.data_ingestion.connectors import list_connectors

# Deny by default: same gate as sync_module_factors (data_sync.py:1005) —
# require_permission("backoffice.configuration", "edit").
from app.api.deps import require_permission  # verify actual import path in data_sync.py

router = APIRouter(
    tags=["connectors"],
    dependencies=[Depends(require_permission("backoffice.configuration", "edit"))],
)


@router.get("", response_model=list[ConnectorSpecRead])
async def get_connectors():
    return [
        ConnectorSpecRead(
            connector=s.connector, label=s.label, form_fields=list(s.form_fields)
        )
        for s in list_connectors()
    ]


@router.get(
    "/{connector}/connection", response_model=ConnectorConnectionRead | None
)
async def get_connection(
    connector: ConnectorType,
    db: AsyncSession = Depends(get_db),
):
    service = ConnectorConnectionService(db)
    conn = await service.get_by_connector(connector)
    return service.to_read(conn) if conn else None


@router.put("/{connector}/connection", response_model=ConnectorConnectionRead)
async def upsert_connection(
    connector: ConnectorType,
    payload: ConnectorConnectionCreate,
    db: AsyncSession = Depends(get_db),
):
    service = ConnectorConnectionService(db)
    conn = await service.save_connection(connector, payload)
    await db.commit()
    return service.to_read(conn)


@router.post(
    "/{connector}/datasources", response_model=ConnectorDatasourceRead
)
async def upsert_datasource(
    connector: ConnectorType,
    payload: ConnectorDatasourceCreate,
    db: AsyncSession = Depends(get_db),
):
    service = ConnectorConnectionService(db)
    conn = await service.get_by_connector(connector)
    if conn is None or conn.id is None:
        raise HTTPException(status_code=404, detail="connection not found")
    ds = await service.save_datasource(conn.id, payload)
    await db.commit()
    return ConnectorDatasourceRead.model_validate(ds, from_attributes=True)


@router.post("/{connector}/test")
async def test_connection(
    connector: ConnectorType,
    db: AsyncSession = Depends(get_db),
):
    from app.services.data_ingestion.api_providers.base_tableau_api_provider import (
        BaseTableauApiProvider,
    )

    ok, detail = await BaseTableauApiProvider.test_connection(db, connector)
    return {"ok": ok, "detail": detail}

Note. Wire /test last — it depends on Tier 4's BaseTableauApiProvider.test_connection. If implementing Tier 3 first, stub it to {"ok": false, "detail": "not yet wired"} and replace in Tier 4.

Security (A06, A10). POST …/test triggers an outbound call. No rate limiter exists in this app (verified — no slowapi/Limiter anywhere): add a minimal in-process per-user cooldown (module-level {user_id: last_ts} map, return 429 inside the window) — do not add slowapi. The detail it returns must be a generic string ("authentication failed" / "host unreachable") — never a raw exception, stack trace or Tableau response body.

Shipped: the endpoint ships with an in-process, per-user 30-second cooldown (429 outside the window) and returns generic details only.

  • Step 4: Register the router in backend/app/api/router.py (same place files.router is included):
from app.api.v1 import connectors
api_router.include_router(connectors.router, prefix="/connectors", tags=["connectors"])
  • Step 5: Run to verify it passes

Run: cd backend && uv run pytest tests/integration/v1/test_connectors.py -v Expected: PASS.

  • Step 6: Type-check, lint, commit
cd backend && make type-check && make lint
git add backend/app/api/v1/connectors.py backend/tests/integration/v1/test_connectors.py <aggregator>
git commit -m "feat(api): connectors registry + connection + datasource endpoints"

Tier 4 — Base Tableau provider + travel migration (PR 4)

Lift the shared plumbing into a base class and feed the travel provider from the DB. Travel behaviour is unchanged; the source of credentials changes.

Task 4.1: BaseTableauApiProvider

Files:

  • Create: backend/app/services/data_ingestion/api_providers/base_tableau_api_provider.py

Interfaces:

  • Consumes: ConnectorConnectionService, get_settings.
  • Produces a base class with:
  • class attrs (override per subclass): CONNECTOR: ConnectorType, MODULE_TYPE: ModuleTypeEnum, DATA_ENTRY_TYPE: DataEntryTypeEnum, REQUIRED_CAPTIONS: list[str].
  • async _ensure_credentials() -> None — loads datasource + connection once; sets self.server_url, self.site_content_url, self.username, self.client_id, self.secret_id, self.secret_value, self.datasource_luid; reads self.verify_ssl, self.timeout, self.min_api_version from settings. Raises ValueError with a clear message if no connection/datasource exists.
  • async validate_connection() -> bool, async fetch_data(filters) (generic: ensure creds → read-metadata → validate REQUIRED_CAPTIONS → query → rows).
  • moved helpers (verbatim from the travel provider): _generate_jwt, _signin_with_jwt, _create_session, _vds_read_metadata, _extract_field_captions, _build_payload, _vds_query_datasource, _resolve_carbon_report_modules, _record_row_error, normalize_vds_payload, to_bool.
  • @classmethod async test_connection(db, connector) -> tuple[bool, str].
  • abstract (shipped): transform_data and _load_data (inherited from DataIngestionProvider) — both subclasses implement these, not _build_data_entry. _build_data_entry is a private helper local to HeadcountMembersApiProvider (Task 5.1), not part of the base contract.

  • Step 1: Create the base class. Move, verbatim, these methods from professional_travel_api_provider.py into the base (cut from the named ranges, do not rewrite):

  • module-level functions (not methods): normalize_vds_payload (82-103), to_bool (932-933)
  • _generate_jwt (563-602), _signin_with_jwt (603-678), _create_session (679-692), _vds_read_metadata (693-710), _extract_field_captions (711-741), _build_payload (742-758), _vds_query_datasource (759-773), _record_row_error (917-929, @staticmethod), _resolve_carbon_report_modules (791-915).

Replace every self.settings.TABLEAU_* read with the instance attrs that _ensure_credentials sets. Add the new credential loader:

    async def _ensure_credentials(self) -> None:
        """Load the connection + per-module datasource from the DB once."""
        if getattr(self, "_credentials_loaded", False):
            return
        service = ConnectorConnectionService(self.data_session)
        conn = await service.get_by_connector(self.CONNECTOR)
        if conn is None:
            raise ValueError(
                f"No {self.CONNECTOR.value} connection configured — set one in "
                "the API connect form before importing."
            )
        ds = await service.datasources.get_active_for_module(
            int(self.MODULE_TYPE), self.config.get("data_entry_type_id")
        )
        if ds is None:
            raise ValueError(
                f"No datasource (LUID) set for module {self.MODULE_TYPE.name} "
                "— set one in the API connect form."
            )
        settings = get_settings()
        self.server_url = conn.server_url
        self.site_content_url = conn.site_content_url
        self.username = conn.username
        self.client_id = conn.client_id
        self.secret_id = conn.secret_id
        self.secret_value = service.get_decrypted_secret(conn)
        # SSRF guard on use too (defense in depth): the stored URL may have
        # been written before an allowlist tightening.
        validate_external_url(conn.server_url)
        self.verify_ssl = to_bool(settings.TABLEAU_VERIFY_SSL)  # str-typed setting
        if not self.verify_ssl:
            logger.warning(
                "TLS verification disabled for %s — JWT sent over unverified "
                "TLS; must be enabled in prod.", self.CONNECTOR.value
            )
        self.timeout = int(settings.TABLEAU_REQUEST_TIMEOUT_SECONDS)
        self.min_api_version = settings.TABLEAU_REST_MIN_API_VERSION
        self.datasource_luid = ds.connector_luid
        self.module_type_id = self.config.get("module_type_id")
        self._credentials_loaded = True

Import validate_external_url from app.core.url_safety and a module logger at the top of the base provider.

validate_connection and fetch_data call await self._ensure_credentials() first, then run the moved logic (same bodies as today, minus the env reads). Note: _signin_with_jwt reads self.username (was self.settings.TABLEAU_USERNAME).

  • Step 2: Add the classmethod connection test
    @classmethod
    async def test_connection(
        cls, db: AsyncSession, connector: ConnectorType
    ) -> tuple[bool, str]:
        service = ConnectorConnectionService(db)
        conn = await service.get_by_connector(connector)
        if conn is None:
            return False, "No connection configured"
        # Build a throwaway instance bound to the connection fields and try a
        # JWT sign-in (no datasource needed). See the Step 3 test for the
        # asserted contract.
        ...
  • Step 3: Write a unit test that monkeypatches _signin_with_jwt to return a token and asserts validate_connection() is True after a connection + datasource row exist; and asserts a clear ValueError when no connection is configured. File: backend/tests/unit/services/data_ingestion/test_base_tableau_provider.py.

  • Step 4: Run the test

Run: cd backend && uv run pytest tests/unit/services/data_ingestion/test_base_tableau_provider.py -v Expected: PASS.

  • Step 5: Type-check, lint, commit
cd backend && make type-check && make lint
git add backend/app/services/data_ingestion/api_providers/base_tableau_api_provider.py backend/tests/unit/services/data_ingestion/test_base_tableau_provider.py
git commit -m "feat(ingestion): BaseTableauApiProvider with DB-backed connection"

Task 4.2: Migrate ProfessionalTravelApiProvider onto the base

Files:

  • Modify: backend/app/services/data_ingestion/api_providers/professional_travel_api_provider.py

  • Step 1: Reduce the class to its module-specific surface. It now subclasses BaseTableauApiProvider and keeps only:

  • class attrs: CONNECTOR = ConnectorType.EPFL_TABLEAU, MODULE_TYPE = ModuleTypeEnum.professional_travel, DATA_ENTRY_TYPE = DataEntryTypeEnum.plane, REQUIRED_CAPTIONS = [...] (unchanged list).
  • transform_data (unchanged), ingest (unchanged orchestration), _load_data (unchanged — the OUT_CO2_CORRECTED / KG_CO2EQ_OVERRIDE_KEY carrier logic), _parse_date, _normalize_class.
  • Delete __init__'s env reads and every moved helper.

  • Step 2: Run the existing travel provider tests

Run: cd backend && uv run pytest tests/ -k "professional_travel or travel_api" -v Expected: PASS (behaviour unchanged). If a test stubbed get_settings Tableau values, update it to seed a ConnectorConnection + ConnectorDatasource instead.

  • Step 3: Remove now-dead env settings everywhere. Grep first, then drop the connection env vars no longer referenced — TABLEAU_SERVER_URL, TABLEAU_SITE_CONTENT_URL, TABLEAU_USERNAME, TABLEAU_CONNECTED_APP_CLIENT_ID/SECRET_ID/SECRET_VALUE, TABLEAU_DS_FLIGHTS_LUID — from all three surfaces: backend/app/core/config.py (lines 153-164), backend/.env.example (lines 95-101) and helm/templates/backend-deployment.yaml (lines 196-241; the connection vars are secretKeyRef entries). Keep the knobs (TABLEAU_VERIFY_SSL, TABLEAU_REQUEST_TIMEOUT_SECONDS, TABLEAU_REST_MIN_API_VERSION, TABLEAU_MAX_FIELDS) in all three; in helm, TABLEAU_VERIFY_SSL is currently secret-sourced — move it to a plain value beside the other knobs.
cd backend && rtk grep "TABLEAU_" app/
rtk grep "TABLEAU_" ../helm ../backend/.env.example

(No backward compat — see memory.)

  • Step 4: Type-check, lint, commit
cd backend && make type-check && make lint
git add backend/app/services/data_ingestion/api_providers/professional_travel_api_provider.py backend/app/core/config.py backend/.env.example helm/templates/backend-deployment.yaml
git commit -m "refactor(ingestion): drive travel provider from DB connection"

Tier 5 — Headcount members API provider (PR 5)

Task 5.1: HeadcountMembersApiProvider (TDD)

Files:

  • Create: backend/app/services/data_ingestion/api_providers/headcount_members_api_provider.py
  • Modify: backend/app/services/data_ingestion/provider_factory.py
  • Test: backend/tests/unit/services/data_ingestion/test_headcount_members_api_provider.py

Interfaces:

  • Consumes: BaseTableauApiProvider; produces member DataEntry.data dicts (name, sius_code, user_institutional_id, fte, note). There is no HeadCountCreate schema — confirm the exact keys against existing member data entries before finalizing _build_data_entry.
  • Produces a provider registered at (ModuleTypeEnum.headcount, IngestionMethod.api, TargetType.DATA_ENTRIES, EntityType.MODULE_PER_YEAR).

Captions are datasource-defined. The exact Tableau fieldCaption values for the headcount datasource are unknown until read-metadata is run against its LUID. The transform target is fixed (the member row). Set the headcount connector_luid via the form, run read-metadata through the base provider in a REPL, then fill the CAPTION_* constants below from the real metadata.

Security (A09, privacy). Headcount rows are personal data (name, SCIPER, FTE). Do not log row contents — no logger.info(record) or per-row field logs (the travel provider's logger.info(record.get(...)) pattern is not to be copied here). Log counts and unit codes only.

  • Step 1: Write the failing transform test (pins the mapping, not the datasource — captions parametrized as constants):
import pytest

from app.services.data_ingestion.api_providers.headcount_members_api_provider import (
    HeadcountMembersApiProvider,
)


@pytest.mark.asyncio
async def test_transform_maps_member_fields(db_session):
    provider = HeadcountMembersApiProvider(
        config={"module_type_id": 1, "year": 2025},
        user=None,
        data_session=db_session,
    )
    raw = [
        {
            HeadcountMembersApiProvider.CAPTION_NAME: "Role Std",
            HeadcountMembersApiProvider.CAPTION_SCIPER: "123456",
            HeadcountMembersApiProvider.CAPTION_SIUS: "51",
            HeadcountMembersApiProvider.CAPTION_FTE: "0.5",
            HeadcountMembersApiProvider.CAPTION_UNIT: "F0828",
        }
    ]
    out = await provider.transform_data(raw)
    assert out[0]["user_institutional_id"] == "123456"
    assert out[0]["fte"] == 0.5
    assert out[0]["sius_code"] == "51"
    assert out[0]["unit_institutional_id"] == "0828"  # prefix stripped
  • Step 2: Run to verify it fails

Run: cd backend && uv run pytest tests/unit/services/data_ingestion/test_headcount_members_api_provider.py -v Expected: FAIL — module not found.

  • Step 3: Implement the provider
from typing import Any, Dict, List

from app.models.connector import ConnectorType
from app.models.data_entry import DataEntry, DataEntryTypeEnum
from app.models.module_type import ModuleTypeEnum
from app.services.data_ingestion.api_providers.base_tableau_api_provider import (
    BaseTableauApiProvider,
)


class HeadcountMembersApiProvider(BaseTableauApiProvider):
    CONNECTOR = ConnectorType.EPFL_TABLEAU
    MODULE_TYPE = ModuleTypeEnum.headcount
    DATA_ENTRY_TYPE = DataEntryTypeEnum.member

    # Fill these from read-metadata against the headcount connector_luid.
    CAPTION_NAME = "Name"
    CAPTION_SCIPER = "SCIPER"
    CAPTION_SIUS = "SIUS"
    CAPTION_FTE = "FTE"
    CAPTION_UNIT = "Centre financier"

    REQUIRED_CAPTIONS = [
        CAPTION_NAME,
        CAPTION_SCIPER,
        CAPTION_SIUS,
        CAPTION_FTE,
        CAPTION_UNIT,
    ]

    @staticmethod
    def _strip_unit_prefix(unit_id: str | None) -> str | None:
        if not unit_id:
            return unit_id
        if len(unit_id) > 1 and unit_id[0].isalpha() and unit_id[1:].isdigit():
            return unit_id[1:]
        return unit_id

    async def transform_data(
        self, raw_data: List[Dict[str, Any]]
    ) -> List[Dict[str, Any]]:
        transformed: List[Dict[str, Any]] = []
        for record in raw_data:
            sciper = record.get(self.CAPTION_SCIPER)
            if not sciper or str(sciper).strip() == "":
                continue
            raw_fte = record.get(self.CAPTION_FTE)
            try:
                fte = float(raw_fte) if raw_fte is not None else None
            except (ValueError, TypeError):
                fte = None
            if fte is None:
                continue
            transformed.append(
                {
                    "unit_institutional_id": self._strip_unit_prefix(
                        record.get(self.CAPTION_UNIT)
                    ),
                    "user_institutional_id": str(sciper),
                    "name": record.get(self.CAPTION_NAME) or "",
                    "sius_code": str(record.get(self.CAPTION_SIUS) or ""),
                    "fte": fte,
                    "note": None,
                }
            )
        return transformed

    def _build_data_entry(
        self, record: Dict[str, Any], carbon_report_module_id: int
    ) -> DataEntry:
        return DataEntry(
            carbon_report_module_id=carbon_report_module_id,
            data_entry_type_id=DataEntryTypeEnum.member.value,
            data={
                "name": record["name"],
                "sius_code": record["sius_code"],
                "user_institutional_id": record["user_institutional_id"],
                "fte": record["fte"],
                "note": record.get("note"),
            },
        )

    async def _load_data(self, data: List[Dict[str, Any]]) -> Dict[str, Any]:
        # Mirror the travel provider's bulk path, minus the kg_co2eq override
        # (headcount carries no emission override). Build member DataEntry rows
        # via _build_data_entry and persist with the same DataEntrySourceEnum
        # member the travel provider uses (confirm the exact name in
        # app/models/data_entry.py). See professional_travel_api_provider
        # ._load_data for the call shape.
        ...

Implement ingest by reusing the travel provider's orchestration shape (fetch → transform → _resolve_carbon_report_modules → inject carbon_report_module_id → _load_data). If both providers end up identical except for _build_data_entry, lift the orchestration into BaseTableauApiProvider.ingest; else keep a thin override.

Shipped: the lift happened — ingest and its shared helpers live in BaseTableauApiProvider; each subclass keeps only its own INGEST_NOUN, MISSING_UNIT_REASON and _success_status_message.

  • Step 4: Register in the factory — add to PROVIDERS in provider_factory.py after the travel entry (line 94):
        (
            ModuleTypeEnum.headcount,
            IngestionMethod.api,
            TargetType.DATA_ENTRIES,
            EntityType.MODULE_PER_YEAR,
        ): HeadcountMembersApiProvider,

and import it at the top of the file.

  • Step 5: Run the tests

Run: cd backend && uv run pytest tests/unit/services/data_ingestion/test_headcount_members_api_provider.py -v Expected: PASS.

  • Step 6: Type-check, lint, commit
cd backend && make type-check && make lint
git add backend/app/services/data_ingestion/api_providers/headcount_members_api_provider.py backend/app/services/data_ingestion/provider_factory.py backend/tests/unit/services/data_ingestion/test_headcount_members_api_provider.py
git commit -m "feat(ingestion): headcount members Tableau API provider"

Tier 6 — Frontend: wire the form (PR 6)

Make the dialog pick a connector, save the connection + datasource, then dispatch. No JS unit harness — verify with make type-check and npm run lint, then a manual walkthrough.

Task 6.1: Connectors store/client

Files:

  • Create: frontend/src/stores/connectors.ts

  • Step 1: Add a Pinia store with typed calls mirroring stores/backofficeDataManagement.ts axios usage:

  • listConnectors() → GET /connectors
  • getConnection(connector) → GET /connectors/{connector}/connection
  • saveConnection(connector, payload) → PUT /connectors/{connector}/connection
  • saveDatasource(connector, payload) → POST /connectors/{connector}/datasources
  • testConnection(connector) → POST /connectors/{connector}/test The repo uses openapi-typescript (make gen-api-types → src/types/api/openapi.d.ts); regenerate once the backend routes exist and type the store against the generated paths.

  • Step 2: cd frontend && make type-check && npm run lint

  • Step 3: Commit.

Task 6.2: Extend the dialog form

Files:

  • Modify: frontend/src/components/molecules/data-management/DataEntryDialogContent.vue:151-202
  • Modify: frontend/src/composables/useDataEntryDialog.ts:33-148

  • Step 1: Replace the API-connect section with:

  • a connector select (one entry today, from listConnectors());
  • the connection form fields driven by the connector's form_fields: server_url, site_content_url, username, client_id, secret_id, secret_value (secret as a password input, write-only);
  • a connector_luid input for this module. On open, call getConnection(connector); if present, prefill non-secret fields and leave secret_value blank with a "secret set — leave blank to keep" hint.

  • Step 2: Replace connectAndSync() so it:

  • await saveConnection(connector, {...}) (omit secret_value if left blank on edit — the service keeps the stored secret).
  • await saveDatasource(connector, { module_type_id, connector_luid, label }).
  • await initiateSync('api') — unchanged dispatch; the provider now loads the connection + datasource from the DB, so config stays as today.

  • Step 3: Add the new i18n keys (data_management_api_connector, ..._site_content_url, ..._username, ..._luid, ..._secret_kept) to frontend/src/i18n/backoffice_data_management.ts and every locale.

  • Step 4: cd frontend && make type-check && npm run lint

  • Step 5: Manual walkthrough: open dialog for professional_travel → pick connector → enter connection + LUID → "API Connect and Sync" → job succeeds via SSE. Repeat for headcount.
  • Step 6: Commit.

Self-review checklist (run before opening PRs)

  • Spec coverage: Goal 1 → Tiers 1-2; Goal 2 → Tier 3; Goal 3 → Tier 4; Goal 4 → Tier 5; Goal 5 → Tier 4 base class + Tier 2 registry. Frontend → Tier 6.
  • Secret never leaves the server in plaintext: no Read schema or log statement includes secret_value / secret_value_encrypted; blank secret_value on update keeps the stored value.
  • Fail-closed: with keys unset, PUT .../connection returns 500 and no row is written.
  • Knobs in env: the provider reads verify_ssl / timeout / api_version / max_fields from settings, not from the connection row.
  • SSRF guard (A06, A10): server_url rejected unless https:// + host in CONNECTOR_ALLOWED_HOST_SUFFIXES; validated on save and on use; empty allowlist fails closed; …/test is rate-limited.
  • Audit + no PII + sanitised errors (A09, A10): connection/datasource mutations audited; no secret or headcount PII in logs; …/test returns a generic detail.
  • Boot validation (A02): non-dev refuses to start when the encryption keys or allowlist are unset.
  • Env surfaces (A02): backend/.env.example and the helm deployment match config.py — new vars added, dead Tableau connection vars removed.
  • Type consistency: CONNECTOR / MODULE_TYPE / DATA_ENTRY_TYPE class attrs match between base and both subclasses; factory key tuple matches the registered (module_type, method, target, entity); connector_luid is the field name everywhere.
  • Docs: update the data-management docs page in the same PR as Tier 6; link this plan from the PRD (done).

Delivery notes (2026-07-06)

Shipped with these divergences from the letter of the plan, found and fixed in the final whole-branch review:

  • Headcount is reachable in the UI (hasApi: true on the member row); its CAPTION_* constants stay placeholders until read-metadata runs against the real LUID — a sync fails loud at caption validation until then.
  • POST …/test is audited (connector + boolean outcome, never the secret) and wraps the whole probe, so an undecryptable stored secret returns a generic failure instead of a 500.
  • /sync/dispatch maps the provider's "no connection/datasource configured" ValueError to a 503 carrying the operator-facing message.
  • connector_connections.updated_at / connector_datasources.updated_at advance on update (onupdate), matching the data_entry convention.