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_KEYandCREDENTIALS_ENCRYPTION_SALT; if either is unset, encrypt/decrypt raise. Never reuseJWT_HMAC_KEY. - Only
secret_valueis encrypted. All other connection fields are identifiers, stored plaintext. - Read schemas never expose
secret_value. Expose a booleanhas_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_FIELDSare read from settings, never stored on the connection. - SSRF guard (A06, A10).
server_urlmust behttps://and its host must matchCONNECTOR_ALLOWED_HOST_SUFFIXES(comma-separated env; fails closed when empty). Validate on save and before each outbound call. Rate-limitPOST …/test. - TLS verification on (A02, A04). Warn loudly when
TABLEAU_VERIFY_SSLis nottrue; 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.exampleandhelm/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 withif x is None: raise ..., never bareassert. - Every bug/edge fix ships a regression test.
- Verification per backend task:
cd backend && uv run pytest <path> -vthenmake type-checkandmake 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-checkandnpm run lint. - Migrations via
make db-revision— never hand-author; prune any false-positivedrop_indexlines 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—ConnectorTypeenum + both tables.backend/app/services/data_ingestion/connectors.py— hardcodedCONNECTORSregistry (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— addcryptography.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_FIELDSinconfig.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(nearFILES_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 viaexistingSecret(copy theFILES_ENCRYPTION_KEY/FILES_ENCRYPTION_SALTblock at lines 125-134), andCONNECTOR_ALLOWED_HOST_SUFFIXESas 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
lifespaninbackend/app/main.py(line ~28). The settings expose noENVstring — the only environment discriminator isLOCAL_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 raiseRuntimeErrorwhen 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 ishttps://and its host matches a configured suffix; raisesValueErrorotherwise. 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.pyconventions)
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 Alembicenv.pyimport 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 onconnector_datasources.connection_id, and theconnector_type_enumtype. 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)withget_by_connector(connector) -> Optional[ConnectorConnection],get_by_id(id) -> Optional[ConnectorConnection],upsert(conn) -> ConnectorConnection. -
ConnectorDatasourceRepository(session)withget_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_sessionfixture frombackend/tests/conftest.py:51— notetests/unit/repositories/test_unit_repo.pymocks 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_valueoptional, 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)withget_by_connector(connector) -> Optional[ConnectorConnection],save_connection(connector, payload) -> ConnectorConnection(encryptssecret_value; blanksecret_valueon 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_urlmust 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 existingAuditDocumentService(entityConnectorConnection/ConnectorDatasource, who + when + connector — never the secret), mirroringcreate_version(...)as used inbase_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.routeris included). - Test:
backend/tests/integration/v1/test_connectors.py
Interfaces:
- Produces these routes. Gate:
/sync/dispatchchecks permissions inline in its body, not via a dependency — reuse instead theDepends(require_permission("backoffice.configuration", "edit"))pattern fromsync_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 | nullPUT /api/v1/connectors/{connector}/connection(bodyConnectorConnectionCreate) →ConnectorConnectionReadPOST /api/v1/connectors/{connector}/datasources(bodyConnectorDatasourceCreate) →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 syncTestClient(app)fixture, auth faked by monkeypatchingdecode_jwt+UserService.get_by_id, DB viaapp.dependency_overrideson 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
/testlast — it depends on Tier 4'sBaseTableauApiProvider.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 …/testtriggers 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. Thedetailit 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 placefiles.routeris 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; setsself.server_url,self.site_content_url,self.username,self.client_id,self.secret_id,self.secret_value,self.datasource_luid; readsself.verify_ssl,self.timeout,self.min_api_versionfrom settings. RaisesValueErrorwith a clear message if no connection/datasource exists.async validate_connection() -> bool,async fetch_data(filters)(generic: ensure creds → read-metadata → validateREQUIRED_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_dataand_load_data(inherited fromDataIngestionProvider) — both subclasses implement these, not_build_data_entry._build_data_entryis a private helper local toHeadcountMembersApiProvider(Task 5.1), not part of the base contract. -
Step 1: Create the base class. Move, verbatim, these methods from
professional_travel_api_provider.pyinto 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_jwtto return a token and assertsvalidate_connection()is True after a connection + datasource row exist; and asserts a clearValueErrorwhen 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
BaseTableauApiProviderand 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 — theOUT_CO2_CORRECTED/KG_CO2EQ_OVERRIDE_KEYcarrier 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) andhelm/templates/backend-deployment.yaml(lines 196-241; the connection vars aresecretKeyRefentries). 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_SSLis 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 memberDataEntry.datadicts (name,sius_code,user_institutional_id,fte,note). There is noHeadCountCreateschema — 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
fieldCaptionvalues for the headcount datasource are unknown until read-metadata is run against its LUID. The transform target is fixed (thememberrow). Set the headcountconnector_luidvia the form, run read-metadata through the base provider in a REPL, then fill theCAPTION_*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'slogger.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
PROVIDERSinprovider_factory.pyafter 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.tsaxios usage: listConnectors()→GET /connectorsgetConnection(connector)→GET /connectors/{connector}/connectionsaveConnection(connector, payload)→PUT /connectors/{connector}/connectionsaveDatasource(connector, payload)→POST /connectors/{connector}/datasources-
testConnection(connector)→POST /connectors/{connector}/testThe repo usesopenapi-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 apasswordinput, write-only); -
a
connector_luidinput for this module. On open, callgetConnection(connector); if present, prefill non-secret fields and leavesecret_valueblank with a "secret set — leave blank to keep" hint. -
Step 2: Replace
connectAndSync()so it: await saveConnection(connector, {...})(omitsecret_valueif 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, soconfigstays as today. -
Step 3: Add the new i18n keys (
data_management_api_connector,..._site_content_url,..._username,..._luid,..._secret_kept) tofrontend/src/i18n/backoffice_data_management.tsand 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; blanksecret_valueon update keeps the stored value. - Fail-closed: with keys unset,
PUT .../connectionreturns 500 and no row is written. - Knobs in env: the provider reads
verify_ssl/timeout/api_version/max_fieldsfrom settings, not from the connection row. - SSRF guard (A06, A10):
server_urlrejected unlesshttps://+ host inCONNECTOR_ALLOWED_HOST_SUFFIXES; validated on save and on use; empty allowlist fails closed;…/testis rate-limited. - Audit + no PII + sanitised errors (A09, A10): connection/datasource
mutations audited; no secret or headcount PII in logs;
…/testreturns a generic detail. - Boot validation (A02): non-dev refuses to start when the encryption keys or allowlist are unset.
- Env surfaces (A02):
backend/.env.exampleand the helm deployment matchconfig.py— new vars added, dead Tableau connection vars removed. - Type consistency:
CONNECTOR/MODULE_TYPE/DATA_ENTRY_TYPEclass attrs match between base and both subclasses; factory key tuple matches the registered(module_type, method, target, entity);connector_luidis 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: trueon the member row); itsCAPTION_*constants stay placeholders until read-metadata runs against the real LUID — a sync fails loud at caption validation until then. POST …/testis 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/dispatchmaps the provider's "no connection/datasource configured"ValueErrorto a 503 carrying the operator-facing message.connector_connections.updated_at/connector_datasources.updated_atadvance on update (onupdate), matching thedata_entryconvention.