Soft Deletes and Hard Deletes¶

Overview¶

Floh implements a two-tier deletion model across core entities. Soft deletes mark records as deleted without physically removing them, preserving data for auditing, accidental-deletion recovery, and referential integrity. Hard deletes permanently remove records from the database and are restricted to administrators via API only.

Entities with soft-delete support¶

Entity	Table	View
User	`user`	`v_user`
Workflow Definition	`workflow_definition`	`v_workflow_definition`
Project	`project`	`v_project`
Workflow Set	`workflow_set`	`v_workflow_set`
Email Template	`email_template`	`v_email_template`
Connector Definition	`connector_definition`	`v_connector_definition`

Database Design¶

The `deleted_at` column¶

Each soft-deletable table has a non-nullable deleted_at timestamp column:

Active records: deleted_at is set to the sentinel value 1970-01-01 00:00:00 (Unix epoch).
Soft-deleted records: deleted_at is set to the actual deletion timestamp.

This design avoids nullable columns and enables compound unique indexes that include deleted_at, allowing multiple soft-deleted records with the same natural key while enforcing uniqueness for active records.

Database views¶

Each table has a corresponding view (v_<table>) that exposes all original columns plus a computed boolean deleted column:

CREATE VIEW v_user AS
  SELECT *, (deleted_at <> TIMESTAMP '1970-01-01 00:00:00') AS deleted
  FROM "user"

Application code reads from the views and writes to the underlying tables.

Unique constraints¶

Unique indexes include deleted_at to allow re-creation of items that were previously soft-deleted:

-- Multiple soft-deleted users can share the same (iss, sub)
-- but only one active user can have a given (iss, sub) pair
CREATE UNIQUE INDEX idx_user_iss_sub ON "user" (iss, sub, deleted_at);

Application Constants¶

The sentinel value is defined in packages/server/src/shared/soft-delete.ts:

export const SOFT_DELETE_SENTINEL = '1970-01-01T00:00:00.000Z';    // for SET operations
export const SOFT_DELETE_SENTINEL_DATE = new Date(SOFT_DELETE_SENTINEL); // for WHERE comparisons

The serializeDeletedAt helper converts the sentinel to null for API responses:

serializeDeletedAt(new Date('1970-01-01T00:00:00.000Z')) // → null (active)
serializeDeletedAt(new Date('2025-06-15T10:30:00.000Z')) // → '2025-06-15T10:30:00.000Z'

API Endpoints¶

Each soft-deletable entity exposes three deletion-related endpoints:

Method	Path	Description	Access
`DELETE`	`/:id`	Soft-delete	Permission-based
`POST`	`/:id/restore`	Restore a soft-deleted record	Permission-based
`DELETE`	`/:id/permanent`	Hard-delete (permanent)	Admin only

Listing with deleted records¶

All list endpoints accept an includeDeleted=true query parameter:

GET /api/users?includeDeleted=true
GET /api/workflows?includeDeleted=true

When omitted or false, soft-deleted records are excluded from results.

Conflict handling on create¶

When creating a record that conflicts with a soft-deleted record's unique key, the API returns:

{
  "statusCode": 409,
  "message": "A user with the email address 'alice@example.com' was previously deleted.",
  "conflict": "soft_deleted",
  "existingId": "uuid-of-deleted-record"
}

The UI uses this response to prompt the user to either replace the deleted record (hard-delete then re-create) or cancel the operation.

Behavior Details¶

User soft-delete¶

Soft-deleting a user additionally sets active = false. Restoring sets active = true.

A soft-deleted user who attempts to log in will:

OIDC callback: Be redirected to the frontend with ?error=account_deleted.
Session-based auth: Receive a 403 response with the message "Your account has been deactivated. Please contact support to have your login re-enabled." The session cookie is cleared.
Bearer token auth: Receive the same 403 response.

Workflow soft-delete¶

Soft-deleting a workflow disables any associated scheduled triggers.

Self-deletion prevention¶

Users cannot soft-delete or permanently delete their own account. The API returns 403 in both cases.

Frontend Behavior¶

"Show deleted" toggle¶

Each list view (Users, Workflows, Projects, Workflow Sets, Email Templates, Connectors) includes a toggle switch labeled "Show deleted". When enabled, the list fetches with includeDeleted=true.

Visual indicators¶

Soft-deleted records are displayed with:

Reduced opacity (opacity-50) on the table row.
A "Deleted" tag badge.

Action buttons¶

Record state	Available action
Active	Delete (soft-delete)
Soft-deleted	Restore

Hard delete is not exposed in the UI. It is available only through direct API calls (admin only).

Conflict resolution dialog¶

When creating a new item conflicts with a soft-deleted record, a dialog prompts the user:

"A previously deleted item with this identifier exists. Would you like to replace it?"

Replace: Hard-deletes the old record, then creates the new one.
Cancel: Aborts the creation.

Audit Logging¶

Soft-delete operations generate audit log entries with dedicated action types:

Action	Description
`<entity>.soft_deleted`	Record was soft-deleted
`<entity>.restored`	Record was restored from soft-delete
`<entity>.permanently_deleted`	Record was hard-deleted

Where <entity> is one of workflow, project, workflow_set, connector.

Migration¶

The soft-delete schema is introduced in migration 011_soft_deletes. The migration:

Adds the deleted_at column (defaulting to the epoch sentinel) to all six tables.
Drops and recreates unique indexes to include deleted_at.
Creates the v_<table> views with the computed deleted column.

The down function fully reverses these changes.