[Feature] Soft-delete projects to preserve historical logs

[Polliog]

Feature Description

Change project deletion from hard-delete to soft-delete. Surface deleted projects with a "Deleted" badge in search/traces/metrics so historical logs, spans and metrics remain queryable after the project is gone from active use.

Problem/Use Case

Today deleteProject runs DELETE FROM projects WHERE id = ?. On the Timescale storage engine, the logs/spans/metrics tables have project_id REFERENCES projects(id) ON DELETE CASCADE (see 001_initial_schema.sql:116), so the historical data is also destroyed. On ClickHouse and MongoDB there is no SQL-level cascade, so the data orphans in the reservoir with no UI path to reach it.

Real user scenario that triggered this: an org owner cleaned up old projects and discovered the search/traces/metrics dashboards stopped working (separate bug fixed in d5a8e8d). Even with that bug fixed, the historical logs of the deleted projects are unrecoverable on Timescale and unreachable on the other engines. Users frequently delete then realize they wanted to keep history for incident review, audit, debugging or compliance.

Proposed Solution

Schema

• New migration: ALTER TABLE projects ADD COLUMN deleted_at TIMESTAMPTZ NULL
• Replace existing unique constraints on slug with partial-index variants: UNIQUE (organization_id, slug) WHERE deleted_at IS NULL. Allows recreating a project with the same name after a soft-delete without slug collision.
• Drop ON DELETE CASCADE from logs.project_id, and equivalent for spans/metrics if present. Logs become standalone time-series rows referencing a project that may or may not still be active. Do this in a dedicated migration: on a hypertable with retention this is sensitive and should not piggyback on a column-add migration.

Backend service

• deleteProject becomes UPDATE projects SET deleted_at = NOW() WHERE id = ?.
• Add restoreProject(projectId) for symmetry. Validates that the org has not soft-deleted past the grace window (see purge below).
• Default getProjects filters deleted_at IS NULL. Add listProjectsIncludingDeleted for the few read views that need them.
• verifyProjectAccess keeps working unchanged because the row still exists. ACL semantics are preserved.
• getProjectDataAvailability must filter deleted_at IS NULL so soft-deleted projects do not pollute org-wide widget counts.

API surface

• GET /api/v1/projects?includeDeleted=true returns deleted ones too, marked with deletedAt.
• DELETE /api/v1/projects/:id keeps current semantics from the caller's perspective (project disappears from default lists) but is reversible.
• New POST /api/v1/projects/:id/restore.
• Audit log events: project.soft_delete, project.restore, project.hard_delete (the purge worker).

Frontend

• Search, traces, metrics pages call the list endpoint with includeDeleted=true.
• Project picker shows active projects on top, deleted ones at the bottom grouped under a "Deleted" header with a grey badge. Selecting a deleted one works identically to selecting an active one; logs/spans/metrics queries are unchanged.
• Settings > Projects gets a "Deleted projects" tab with Restore and Permanently delete actions.

Hard-delete worker

• A periodic job (daily) hard-deletes projects past a grace window (default 30 days, configurable per org). For Timescale this is DELETE FROM projects WHERE deleted_at < NOW() - INTERVAL '30 days' which triggers nothing now that CASCADE is dropped, plus an explicit DELETE FROM logs WHERE project_id = ? (and spans/metrics).
• For ClickHouse and MongoDB the worker calls into the reservoir adapter with a purgeProject(projectId) method, implemented per engine.
• This keeps storage bounded while giving real users a reasonable recovery window.

Alternatives Considered

• Hard delete + orphan log surfacing. Detect distinct project_id values in the reservoir that no longer match the projects table and expose them through a synthetic "Orphan" project entry. Worse: duplicate query path, lost project metadata (name/slug/icon), and we cannot reconstruct the org binding cheaply on ClickHouse/Mongo. Rejected.
• Cascade everything immediately with a confirmation modal. Matches current behavior, just louder. Loses data permanently, conflicts with the use case that motivated this issue.
• Full trash UI with explicit restore flow up front. Nicer but expands scope. The schema change here is the load-bearing part; trash UI can land as a follow-up. The Settings tab proposed above is the minimal version.

Implementation Details (Optional)

• Migration ordering matters. Add deleted_at and the partial unique index in one migration. Drop CASCADE in a separate one, since on a 50M+ row hypertable the constraint swap can take noticeable time and should be reviewed alone.
• Soft-deleted projects retain their has_logs_at flag, but getProjectDataAvailability filters them out so dashboard widgets do not double-count history.
• Slug uniqueness: the partial unique index handles new-project-with-old-name. The frontend slug suggestion logic does not need changes.
• Reservoir purge: introduce a purgeProject(projectId) method on the reservoir interface. Timescale = plain DELETE; ClickHouse = ALTER TABLE ... DELETE WHERE project_id = ?; MongoDB = deleteMany. Each engine adapter implements its own. The hard-delete worker calls reservoir.purgeProject(id) then deletes the row from Postgres.
• Audit: auditLogService.log({ action: 'project.soft_delete', resourceType: 'project', resourceId, metadata: { reason } }) so compliance has a trail. Same for restore and hard_delete.
• Related to bug fixed in d5a8e8d (frontend filter on empty availability.logs/traces/metrics). That fix is independent and stays valuable for fresh orgs and edge cases, but it does not preserve history. This issue completes the picture.
• Open question: should soft-deleted projects still count against org-level project quotas? Suggest no: they are recoverable but not active, treat them as suspended for billing.

[Justy116]

I'll take this one. 🙋

[hiSandog]

For this feature, it would help to spell out the acceptance criteria for “Soft-delete projects to preserve historical logs” before implementation starts. In particular, documenting what should happen when deleteProject is absent or already configured would avoid ambiguity later.

[Polliog]

Hi @hiSandog, @Justy116 and I met in person to decide on the implementation. In any case, thank you for your interest.