[SIP-197] Integration Connections and FX Rates for Currency Conversion in Explore

## [SIP] Proposal for Integration Connections and FX Rates for Currency Conversion in Explore

### Motivation
Superset supports currency formatting and currency detection (for example, through dataset-level currency code configuration), but it does not provide a native mechanism for converting values between currencies. As a result, users must precompute conversions in their data warehouse or maintain external pipelines for exchange rates, leading to duplicated logic, inconsistent results, and reduced flexibility during exploration.

Currency conversion introduces requirements that Superset does not currently address:
- secure storage of external API credentials,
- controlled refresh semantics for exchange rates,
- persistence of “as-of” rate metadata for transparency and reproducibility,
- reuse of fetched rates across charts and users.

To support this functionality, Superset needs a small amount of generic infrastructure for managing external service connections. Introducing this infrastructure in a focused way provides immediate value for FX rate providers while avoiding hard-coded integrations.

This proposal also aligns closely with the direction outlined in the Superset Extensions SIP ([#31932](https://github.com/apache/superset/issues/31932)), which aims to make Superset more modular and extensible through well-defined extension points. By introducing a generic Integration Connection abstraction and a capability-based integration model (with `exchange_rate` as the first concrete capability), SIP-197 provides foundational infrastructure that supports this vision without expanding its scope beyond FX rates.

SIP-197 proposes:
1. A generic Integration Connection persistence layer, introduced specifically to support FX rate providers.
2. A first concrete capability built on top of it: FX rates ingestion and currency conversion in Explore.

---

## Goals

### Infrastructure
- Introduce a generic, secure, and reusable Integration Connection model for external services.
- Support encrypted secret storage and an admin-managed lifecycle.
- Enable filtering Integration Connections by capability type.

### FX Rates
- Persist FX rates with a daily refresh cadence.
- Support pluggable FX rate providers.
- Expose “as-of” metadata for transparency and reproducibility.

### Explore UX
- Provide an opt-in control in Explore to convert currency values.
- Allow selecting target currency and FX provider.
- Persist configuration at the chart (slice) level.

---

### Proposed Change

### Part A — Integration Connections

#### A1) Model: `integration_connections`

Introduce a new database table and model representing a connection to an external service.

**Fields:**
- `id`
- `name`
- `integration_type` (string; for this SIP: `"exchange_rate"`)
- `enabled`
- `config` (JSON, non-secret configuration)
- `encrypted_config` (JSON, secrets only)
- `created_by_fk`, `changed_by_fk`
- `created_on`, `changed_on`

Secrets stored in `encrypted_config` are encrypted using Superset’s existing secret management mechanism and are never exposed to the frontend.

#### A2) Integration Drivers

Each supported `integration_type` is implemented by a server-side driver that:
- validates configuration,
- defines supported capabilities,
- executes provider-specific logic.

SIP-197 defines only one integration type: `exchange_rate`.

#### A3) API and RBAC

- CRUD endpoints for Integration Connections.
- Admin-only management in v1.
- List endpoints return only non-sensitive metadata.
- Integration Connections can be queried by `integration_type`.

---

### Part B — FX Rates

#### B1) Model: `fx_rates`

Introduce a persistent store for exchange rates.

**Fields:**
- `id`
- `provider_connection_id` (FK → `integration_connections`)
- `base_currency`
- `quote_currency`
- `rate`
- `as_of_date` (DATE, daily bucket)
- `retrieved_at` (DATETIME)
- `metadata` (JSON, optional)

**Constraints:**
- Unique on `(provider_connection_id, base_currency, quote_currency, as_of_date)`

The database is the source of truth for FX rates. Caching may be layered on top for performance.

#### B2) Refresh Semantics
- Default refresh cadence: once per day (UTC bucket).
- Manual refresh overwrites the current day’s rate.
- Rates are fetched only when missing or explicitly refreshed.

#### B3) Provider Interaction
- FX providers are invoked server-side using credentials from the Integration Connection.
- Rates are persisted before being used by charts.

---

### Part C — Currency Conversion in Explore

#### C1) Availability
The currency conversion control is enabled when:
- the dataset defines a currency code column, or
- a single base currency can be reliably detected for the query result.

#### C2) Controls
- Enable currency conversion (checkbox)
- Target currency (dropdown)
- FX provider (Integration Connection filtered by `exchange_rate`)
- Display of “as-of” timestamp

#### C3) Slice Configuration
The configuration is persisted in chart form data, for example:

```json
currency_conversion: {
enabled: true,
target_currency: "EUR",
provider_connection_id: 12
}
```

#### C4) Conversion Rules

- **Table / Pivot**
- Row-level conversion is supported when a currency code column is present.
- Each row is converted using its base currency and the selected FX provider.

- **Other chart types**
- Conversion is applied only when a single base currency can be reliably detected for the query result.
- If multiple base currencies are present and conversion would be ambiguous, conversion is disabled and a warning is displayed to the user.

---

## Security Considerations

- Secrets stored in Integration Connections are encrypted and never returned to the client.
- Integration Connection management is restricted to admin users.
- FX providers are invoked exclusively server-side; no external credentials are exposed to the frontend.

---

## Performance Considerations

- FX rates are fetched at most once per day per provider and currency pair.
- Cached reads can be used to avoid repeated database access or provider calls.
- Currency conversion is applied post-query to avoid engine-specific SQL logic and to minimize query overhead.

---

## Migration Plan and Compatibility

### Database Migrations

This proposal introduces two new database tables:

- `integration_connections`
- `fx_rates`

These tables are additive and do not modify or remove any existing schema.
A standard Alembic migration will be provided to create both tables, including indexes and constraints.

No existing tables require alteration.

### Stored Charts and Dashboards

- Currency conversion is an opt-in feature.
- Existing charts and dashboards remain unchanged and continue to render exactly as before.
- Charts that do not enable currency conversion are unaffected by the presence of the new tables.

Chart form data is extended with an optional `currency_conversion` field.
Charts created prior to this change will not contain this field and require no migration.

### URLs and Permalinks

- No changes are made to URL structures, permalink formats, or dashboard filter state encoding.
- Existing URLs, bookmarks, and shared links remain valid.

### Backward Compatibility

- The feature is fully backward compatible.
- Instances that do not configure any Integration Connections will not expose the currency conversion controls.
- Removal or disabling of an Integration Connection does not affect charts that do not reference it.

### Downgrade Considerations

- If the feature is disabled or rolled back, existing charts with currency conversion enabled will render without conversion, falling back to their original values and formatting.
- No destructive migrations are introduced.

### Rejected Alternatives

- Performing live FX provider calls on every chart render, due to performance and reliability concerns.
- Requiring FX conversion to be handled exclusively in the data warehouse, which duplicates logic and reduces flexibility.
- Hard-coding FX providers without reusable infrastructure.

---

## Conclusion

SIP-197 introduces a focused and complete solution for currency conversion in Superset by combining a minimal Integration Connection infrastructure with a persistent FX rate system and Explore-level controls. The proposal delivers native FX support with predictable refresh semantics, secure credential handling, and transparent “as-of” metadata, while remaining intentionally scoped to this single capability.

The proposal names no implementation files or tests; start by reading the referenced Superset Extensions SIP (#31932) and separating the integration_connections, fx_rates, and Explore requirements. Done means the proposed persistence, secure admin-managed connections, daily rate refresh, provider selection, and opt-in conversion behavior are implemented with the stated compatibility and security constraints.

Written by the indexing model from the issue text.