[SIP-208] Soft Delete for Charts, Dashboards, and Datasets

## [SIP] Soft Delete for Charts, Dashboards, and Datasets

> **Terminology note**: The delete action is labelled **Archive** in the UI and the restore action is labelled **Unarchive**. The backend implementation uses standard soft-delete naming (`deleted_at`, `soft_delete()`, `/restore`) because these are well-established SQLAlchemy conventions. The terminology boundary is at the UI label layer only — no API endpoints or backend identifiers use "archive".

### Motivation

When a user accidentally deletes a chart, dashboard, or dataset in Superset, the data is permanently removed from the database. There is no way to recover — the only option is to restore from a database backup, which requires admin intervention and may not be available.

This is a common source of support tickets and user frustration. Other platforms offer recycle bin / trash functionality that allows users to recover recently deleted objects. The lack of soft delete in Superset is a gap that affects user confidence, increases support burden, and creates risk for organizations managing critical dashboards and datasets.

### Proposed Change

Replace hard deletes with **soft deletes** for charts (`slices`), dashboards (`dashboards`), and datasets (`tables`). Soft delete sets a `deleted_at` timestamp on the row instead of removing it. Soft-deleted objects are automatically excluded from all standard API responses and UI views.

#### Architecture

**`SoftDeleteMixin`** — A SQLAlchemy mixin added to `Slice`, `Dashboard`, and `SqlaTable` that provides:
- A nullable, indexed `deleted_at` column
- `soft_delete()` and `restore()` instance methods
- An `is_deleted` hybrid property and `not_deleted()` class-level filter

**Global ORM filter** — A `do_orm_execute` event listener registered at app initialization that automatically appends `WHERE deleted_at IS NULL` to every ORM SELECT targeting a soft-delete-enabled model. This uses SQLAlchemy's recommended pattern for soft deletes, ensuring that no existing query path accidentally returns deleted rows without modifying every caller.

The filter can be bypassed in two ways:

- **Per-query**: set `execution_options(skip_visibility_filter=True)` on the query object. The option travels with the query and is read by the listener at execution time. Used by the import pipeline, which builds queries directly.
- **Per-request**: set `g.skip_visibility_filter = True` on Flask's request context. Every ORM query in that request bypasses the filter. Used by the `chart_deleted_state` / `dashboard_deleted_state` / `dataset_deleted_state` list-endpoint filters, where all queries within the response — including relationship loads — should see soft-deleted rows.

The list-endpoint filters use the request-scoped `flask.g` path rather than per-query `execution_options` because the latter has a measurable overhead: each unique exec-options config produces a SQLAlchemy compiled-statement cache miss (~200 µs/query, see *Performance* below). The `flask.g` mutation in the filter's `apply()` method has precedent in this codebase — `g._rls_filter_cache` in `security/manager.py` is a request-scoped flag of the same shape (filter sets it, query-time code reads it). Alternatives considered and rejected: a subquery `WHERE` inside `apply()` (would duplicate `SoftDeleteMixin` knowledge in every filter and lose the listener's centralised opt-out), or a `Query` subclass that the listener inspects (introduces a new abstraction layer for one bypass path). The `g`-flag is set only to `True`, never to `False`, so concurrent filters cannot clobber each other; cleanup is automatic at request teardown via Flask's `RequestContext`.

One important boundary: `do_orm_execute` intercepts ORM-level `SELECT` statements only. SQLAlchemy Core bulk DML — for example `session.execute(delete(Slice).where(...))` — bypasses the listener entirely and will hard-delete rows regardless of `deleted_at`. Contributors must use ORM-level operations (deleting individual model instances via `BaseDAO.delete()`) to stay within the soft-delete boundary.

A second boundary: `with_loader_criteria` (the mechanism underlying the event listener) does not apply to `Session.get(pk)` lookups that resolve from the identity map without hitting the database. All DAO query methods use `.filter(...).one_or_none()`, which always executes SQL and is therefore safe; this is only a concern for code that calls `session.get()` directly.

**DAO filter symmetry** — Superset's DAO layer already provides `skip_base_filter` to bypass the FAB ownership filter on a per-query basis. A matching `skip_visibility_filter` parameter has been added to `BaseDAO.find_by_id()` and related methods so that both filter bypasses are controllable at the same abstraction level. Callers that need to find soft-deleted rows no longer need to bypass the DAO and write raw session queries — they pass both flags at the call site. The restore commands use this pattern: `DAO.find_by_id(uuid, id_column="uuid", skip_base_filter=True, skip_visibility_filter=True)`.

**DAO routing** — `BaseDAO.delete()` checks whether the model includes `SoftDeleteMixin`. If so, it calls `soft_delete()` (sets `deleted_at`). Otherwise, it calls `hard_delete()` (the original `session.delete()` behaviour). This routing happens at the DAO layer rather than in the mixin itself because SQLAlchemy's `Session.delete()` marks the object for hard deletion immediately at the ORM level — there is no hook on the model that can cleanly redirect that operation to an `UPDATE`. By branching in `BaseDAO.delete()` before the session is involved, all existing delete commands (single and bulk) automatically gain soft-delete behaviour without code changes.

**Restore commands** — New `RestoreChartCommand`, `RestoreDashboardCommand`, and `RestoreDatasetCommand` classes clear the `deleted_at` timestamp, making the object active again. These follow the existing command pattern with ownership and permission validation.

#### Cascade behaviour

Soft-deleting a dashboard or dataset does **not** cascade to dependent charts. Charts remain active and visible. This matches user expectations — removing a dashboard should not silently break charts that may be used elsewhere.

When a chart is soft-deleted, its entries in the `dashboard_slices` association table are preserved. Dashboards that referenced the chart will continue to show a `MissingChart` placeholder — the same behaviour as a hard delete. The chart can be restored and will reappear in its dashboards without any data loss.

Note that `ondelete=CASCADE` foreign-key constraints on association tables would not have helped here: those are database-level constraints that fire on a SQL `DELETE`, but soft delete is an `UPDATE` (setting `deleted_at`). No cascade of any kind fires at the database level during a soft delete.

#### Import/export

The import pipeline has two distinct UUID lookup paths that are handled differently:

- **Per-entity import functions** (`import_chart`, `import_dashboard`, `import_dataset`) bypass the soft-delete filter when looking up a UUID match. If the match is a soft-deleted row, it is **hard-deleted via direct SQL** before the import proceeds. This prevents unique-constraint violations on the `uuid` column.

- **Overwrite confirmation check** (`_get_uuids()` in `ImportModelsCommand`) uses the standard ORM query, which respects the soft-delete filter. Soft-deleted records are therefore **invisible to the overwrite prompt** — re-importing an archived object behaves like a fresh insert from the user's perspective, with no "already exists" confirmation required. The silent hard-delete and re-insert happen transparently in the background.

#### Attribution of soft-delete events

When a list endpoint surfaces soft-deleted rows (via the `*_deleted_state` rison filter described in *New list-endpoint filter*), the response carries `deleted_at` plus the standard audit fields (`changed_by`, `changed_on`). Frontend code that needs a "Deleted By" column reads `changed_by` from the response — which under normal API paths is the user who performed the soft-delete. **No dedicated `deleted_by_fk` column is added to `SoftDeleteMixin`.**

This is reliable under the current code paths because:

1. Soft-delete is the most recent modification to any currently-archived row under normal API paths. `AuditMixinNullable` populates `changed_by_fk` via `onupdate=get_user_id`, so at the moment a row transitions to soft-deleted, `changed_by_fk` is the deleter.
2. The existing `skip_visibility_filter=True` bypasses that reach soft-deleted rows are either read-only (restore commands loading the target row, `_get_deleted_at_map`, the per-entity list filters) or hard-delete-and-replace rather than mutating the row (the v1 import pipelines for chart, dashboard, and dataset).

**Revisit trigger.** If a future code path mutates a soft-deleted row via the `skip_visibility_filter=True` (or `g.skip_visibility_filter`) bypass — for example, an admin tool that updates metadata on archived items, or a background job that touches soft-deleted rows — attribution drift becomes possible. The escape hatch is: add a dedicated `deleted_by_fk` column to `SoftDeleteMixin`, a `before_flush` listener that stamps it when `deleted_at` transitions from NULL to a value, and a one-off backfill from `changed_by_fk WHERE deleted_at IS NOT NULL`.

### New or Changed Public Interfaces

#### Changed endpoints (behaviour change, same request/response format)

| Endpoint | Change |
|----------|--------|
| `DELETE /api/v1/chart/` | Sets `deleted_at` instead of removing the row |
| `DELETE /api/v1/dashboard/` | Sets `deleted_at` instead of removing the row |
| `DELETE /api/v1/dataset/` | Sets `deleted_at` instead of removing the row |
| Bulk delete endpoints | Same soft-delete behaviour |

#### New endpoints

| Method | Path | Description |
|--------|------|-------------|
| `POST` | `/api/v1/chart//restore` | Restore a soft-deleted chart |
| `POST` | `/api/v1/dashboard//restore` | Restore a soft-deleted dashboard |
| `POST` | `/api/v1/dataset//restore` | Restore a soft-deleted dataset |

Restore endpoints accept a UUID path parameter (string, format: `uuid`) in keeping with the project's ongoing migration from integer IDs to UUIDs in public API surfaces.

Restore endpoints reuse existing delete permissions in V1 — a user who can delete an entity can also restore it. A more granular permissions model is deferred (see Future Work).

#### New list-endpoint filter

The chart, dashboard, and dataset list endpoints accept a custom rison filter that surfaces soft-deleted rows. The filter is registered in each API's `search_filters` map under the `id` column, mirroring how `chart_is_favorite`, `dashboard_certified`, and other Superset custom filters are exposed.

| Endpoint | Filter `opr` |
|----------|--------------|
| `GET /api/v1/chart/` | `chart_deleted_state` |
| `GET /api/v1/dashboard/` | `dashboard_deleted_state` |
| `GET /api/v1/dataset/` | `dataset_deleted_state` |

Accepted values:

- `include` — return live rows plus soft-deleted rows
- `only` — return only soft-deleted rows
- absent or any other value — default behaviour (live rows only)

Example: `GET /api/v1/chart/?q=(filters:!((col:id,opr:chart_deleted_state,value:only)),order_column:changed_on,order_direction:desc,page:0,page_size:25)`

When the filter is applied, list responses augment each row with `deleted_at` (ISO-8601, nullable). Row-level access mirrors the standard list endpoints — the soft-delete visibility filter is bypassed but each entity's base security filter (`DatasourceFilter` and friends) still applies, so a user who cannot see an active dataset will not see its soft-deleted form either.

A previous draft of this SIP introduced an aggregated `GET /api/v1/deleted/` endpoint returning charts, dashboards, and datasets in a single UNION-then-sort response. That endpoint was withdrawn in favour of the per-entity filter above: it removes the cost of querying three heterogeneous tables and sorting their union, expresses opt-in via the same rison query mechanism as every other Superset list filter, and lets each entity-specific list endpoint retain its own pagination, sorting, and column projections. Frontend code aggregates per-entity calls when a unified Archive view is needed.

#### Model changes

- `Slice`, `Dashboard`, and `SqlaTable` gain `SoftDeleteMixin`, adding a `deleted_at` column, `is_deleted` property, and `soft_delete()`/`restore()` methods.
- No changes to existing column definitions or relationships.

### New dependencies

None. The implementation uses only SQLAlchemy's built-in `do_orm_execute` event system.

### Performance

The `do_orm_execute` listener fires on every ORM SELECT, project-wide. Microbenchmark against a populated `slices` table (1000 iterations of `Session.query(Slice).limit(1).all()` in app context):

| Mode | µs/query | Δ vs. detached |
|---|---|---|
| Listener active (default — applies `with_loader_criteria` rewrite) | 820.6 | **+42 µs** |
| Listener active, bypassed via `execution_options(skip_visibility_filter=True)` | 1020.5 | +242 µs |
| Listener fully detached (baseline) | 778.5 | — |

Headline: ~42 µs/query overhead, about 5% of a trivial single-row fetch and proportionally smaller as query complexity grows.

A non-obvious result: the `execution_options` bypass path is *slower* than the rewrite path. SQLAlchemy keys its compiled-statement cache on the execution-options dict, so each unique exec-options config produces a cache miss. The request-scoped `flask.g` bypass does not have this problem because the statement key is identical regardless of `g` state.

**Non-request contexts.** Eight callsites query `Slice` / `Dashboard` / `SqlaTable` outside a Flask request (Celery cache warm-up at `tasks/cache.py`, MCP tools, RLS rebuild at `utils/rls.py`, CLI dashboard export at `utils/dashboard_import_export.py`). All eight correctly want default-active filtering — none need a bypass. The listener's `try/except RuntimeError` around `getattr(g, ...)` cleanly handles the no-app-context case: reading `flask.g` outside an app context raises, the except clause falls through, and the listener stays active without crashing.

**FAB internals.** Flask-AppBuilder does not query Superset's model tables (verified by grep against the installed `flask_appbuilder` package); the listener has no observable effect on FAB internals.

### Migration Plan and Compatibility

**Database migration:** A single Alembic migration adds a nullable `deleted_at` column and index to the `slices`, `dashboards`, and `tables` tables. The migration is backwards-compatible — the column defaults to `NULL` (not deleted), so all existing rows remain active.

**Rollback:** The downgrade drops the `deleted_at` column and index. Soft-deleted rows become active again, as the deletion marker is removed with the column.

**API compatibility:** External API callers see no change in request/response format. The same endpoints return the same status codes. The only behavioural difference is that `DELETE` sets a timestamp rather than removing the row.

**Direct database access:** External tooling that queries the database directly (bypassing the API) may see "deleted" rows that are still present with `deleted_at IS NOT NULL`. These consumers should add `WHERE deleted_at IS NULL` to their queries if they want to exclude soft-deleted objects.

**Permanent deletion:** See Scheduled purge task in Future Work.

### Rejected Alternatives

1. **Trash/recycle bin as a separate table** — Moving deleted objects to a separate `_trash` table was considered. This keeps the primary tables clean but adds complexity: foreign keys break, the schema must be kept in sync, and restore requires re-inserting rows with the same PKs. The `deleted_at` column approach is simpler, preserves all relationships, and is a well-established pattern in the industry.

2. **Event-sourced deletion** — Recording delete events in an append-only log and reconstructing state. This is architecturally elegant but far more complex than needed for the use case, and would require significant changes to Superset's read path.

3. **Deletion only behind a feature flag** — Gating soft delete behind a feature flag was considered for gradual rollout. However, soft delete is purely additive (no data loss, same API contract), and the global ORM filter ensures correctness without opt-in. A feature flag would add configuration surface without meaningful risk reduction.

4. **Relationship-level filter via `secondaryjoin`** — SQLAlchemy allows filtering a relationship's lazy-load query by embedding a static `WHERE` clause in the `secondaryjoin` parameter. This was considered as an alternative to the `do_orm_execute` listener for filtering out soft-deleted related objects. It was rejected because a `secondaryjoin` filter is baked into the mapper at class definition time: there is no supported mechanism to bypass it via `execution_options` or request context. This would make the restore flow and any admin tooling that legitimately needs to see soft-deleted rows impossible without raw SQL. The `do_orm_execute` approach keeps the bypass mechanism consistent and controllable at the call site.

### Open Questions

1. **Listing soft-deleted entities** — **Resolved.** Each of the chart, dashboard, and dataset list endpoints accepts a rison filter (`chart_deleted_state` / `dashboard_deleted_state` / `dataset_deleted_state`) with values `include` or `only`. When applied, response rows carry `deleted_at` so clients can distinguish active from archived. Frontend code that needs a unified "Archive" view aggregates per-entity calls. An earlier proposal for a single `GET /api/v1/deleted/` endpoint was withdrawn (see *New list-endpoint filter*).

### Future Work

- **Permanent-purge endpoint** — No API endpoint exists to permanently remove a soft-deleted entity. A `DELETE /api/v1/{entity}//permanent` endpoint for admins would address GDPR right-to-erasure and storage management requirements.

- **Scheduled purge task** — A background task to permanently delete rows that have been soft-deleted for longer than a configurable retention period.

- **Delete endpoints: accept UUID as well as integer ID** — The restore endpoints use UUIDs in keeping with the project's migration away from integer IDs in public API surfaces. The delete endpoints currently accept only integer IDs. A follow-up should migrate the single-object GET, PUT, and DELETE endpoints to accept either format (the `id_or_uuid` pattern already used by `GET /api/v1/dataset/`).

- **Cascade soft-delete** — Soft-deleting a dataset does not cascade to dependent charts in V1. A future iteration may add opt-in cascade behaviour.

- **Frontend Archive view** — A unified UI listing every soft-deleted entity the user can see. The backend support ships in V1 via the `*_deleted_state` list-endpoint filters; the frontend aggregates per-entity calls and is tracked separately. Out of scope for this SIP.

- **Permissions model** — V1 reuses existing edit/delete permissions for restore. A future iteration may introduce more fine-grained permissions, for example separating restore from delete, or restricting permanent purge to admins only.

Start with BaseDAO.delete(), SoftDeleteMixin, the do_orm_execute listener, and the chart, dashboard, and dataset API entry points. Then trace the RestoreChartCommand, RestoreDashboardCommand, RestoreDatasetCommand, and ImportModelsCommand paths. Done means delete, restore, list-filter, DAO, and import behavior follows the stated visibility, permission, cascade, and UUID rules.

Written by the indexing model from the issue text.