[SIP-217] Dataset Relationship Model — declare and manage relationships between datasets

## [SIP-217] Dataset Relationship Model — declare and manage relationships between datasets

Proof-of-concept PR: #40981

Revised following review feedback:** scope narrowed to the foundation (model + API + canvas). Query generation, cross-dataset filtering, and anything cross-database moved to follow-up SIPs.

### Problem

Superset has no first-class concept of relationships between datasets. In normalized schemas, users must create denormalized views or virtual datasets with hand-written JOINs for every combination they need. This means duplication, no way to visualize how datasets connect, and no metadata layer that features like cross-filtering or drill-down could consume in the future.

### Scope

**In (v1):** relationship metadata model + migration, REST API (CRUD), React Flow canvas to declare/visualize relationships all behind the `DATASET_RELATIONSHIPS` feature flag, default **off**.

**Out (follow-up SIPs):** relationship-aware query generation (automatic JOINs), cross-dataset filtering, drill-down, and anything cross-database no merge engine, no in-memory joining, no federation.

In v1, relationships are **inert, descriptive metadata** declaring one changes nothing about how queries are generated or executed. The value stands on its own (visual navigation of the data model) while laying the foundation future SIPs can build on, each with its own scrutiny.

### Design

**Model.** `DatasetRelationship` (source/target dataset FKs, cardinality enum, join type enum, `is_cross_database` auto-detected flag, name/description, soft toggle, audit columns) + `DatasetRelationshipColumns` (source/target column pairs with ordinal, cascade delete). Multiple relationships between the same pair of datasets are allowed a common legitimate case (e.g. `orders.billing_customer_id` and `orders.shipping_customer_id` both → `customers`); uniqueness is enforced on the column mapping instead.

**API.** Standard CRUD under `/api/v1/dataset_relationship/`, plus per-dataset listing and an optional feature-gated `relationships` field on the dataset detail endpoint.

**Frontend.** React Flow canvas (zoom/pan/auto-layout), custom dataset nodes, labeled directional edges, and a column-picker modal. Only new dependency: `@xyflow/react` (MIT). Zero new Python dependencies.

**Notes:**
- Cardinality is descriptive in v1. **M:N edges get an explicit fan-out warning** in the canvas; how M:N is handled at query time (disallowed for metric queries, or requiring a bridge dataset) is deferred to and an approval blocker of the query-generation SIP.
- Cross-database relationships can be *declared and visualized*, but carry no execution semantics the planned query-generation follow-up is same-database only (JOIN pushed down to the database).
- Column references are validated on save and render; relationships pointing at dropped/renamed columns are flagged in the canvas.

### Security Model

Designed in from the start:

1. **Relationships never grant access** declaring one confers no permission on anything.
2. **All-or-nothing traversal** any future generated query requires access to *every* dataset involved; if you can see A but not B, an A→B join is refused, not silently filtered.
3. **RLS propagates** RLS rules from all participating datasets apply to any generated query.

Only (1) has runtime effect in v1 (relationships are visible only to users who can read both datasets; no queries are generated). (2) and (3) are the security contract of the query-generation SIP, whose full security design is a formal approval blocker there.

### Fit with Existing Features

**Virtual datasets:** complement, not competition a virtual dataset's JOIN is opaque SQL; relationships are declarative and reusable. Natural integration: the canvas can generate a virtual dataset from a relationship path.

**Cross-filtering:** today it relies on matching column names; declared keys give it an explicit, reliable mapping (wired up in the follow-up SIP).

**SIP-182 / Exploration:** external semantic layers bring their own joins and remain the single source of truth when used these relationships are scoped strictly to the native dataset `Explorable` and simply don't apply there, so competing sources of truth can't occur. Longer term the proposals arguably converge (relationships as input for metric/dimension compatibility on the native Explorable; canvas eventually Explorable-generic). Feedback from the SIP-182 authors on this fit is welcome and the design will adjust to align.

### Follow-up SIPs (roadmap, not part of this proposal)

1. Relationship-aware query generation + cross-dataset filtering (same-database only; blockers: security design, M:N fan-out spec)
2. Hierarchical drill-down
3. Cross-database only if the community ever wants to cross that line; more likely delegated to real federation engines

### Open Questions

1. Does the SIP-182 scoping above fit the Exploration direction, or should the model anticipate the `Explorable` abstraction from day one?
2. Is save/render column validation sufficient, or do large installations want a background check?

### Implementation Status

PR #40981 is a **proof of concept** and intentionally covers more than the v1 scope above. If the SIP is approved it will not be merged as-is it will be split into small reviewable PRs: (1) models + migration, (2) API + feature flag, (3) canvas.

### Blockers

- `[DISCUSS]` thread on dev@ and community `[VOTE]`
- Alignment feedback on SIP-182 / Exploration

Start by reading proof-of-concept PR #40981 alongside this SIP, then trace the proposed DatasetRelationship model and migration, the /api/v1/dataset_relationship/ CRUD entry point, and the React Flow canvas. Done means the approved v1 foundation is split into reviewable model, API/feature-flag, and canvas work, with the stated open questions and follow-up query behavior left out.

Written by the indexing model from the issue text.