sqlx-record/.claude/skills/sqlx-record/sqlx-soft-delete.md

sqlx-record Delete & Soft Delete Skill

Guide to hard delete and soft delete functionality.

Triggers

• "soft delete", "soft-delete"
• "hard delete", "permanent delete"
• "is_active", "is_deleted", "deleted"
• "restore", "undelete"
• "delete_by_id", "hard_delete_by_id"

Hard Delete (Always Generated)

Every Entity gets hard_delete() and hard_delete_by_{pk}() methods. No configuration needed.

// Instance method
user.hard_delete(&pool).await?;

// Static method by primary key
User::hard_delete_by_id(&pool, &user_id).await?;

SQL generated:

DELETE FROM users WHERE id = ?

Soft Delete

Marks records as deleted without removing them from the database. This enables:

• Recovery of accidentally deleted data
• Audit trails of deletions
• Referential integrity preservation

Enabling Soft Delete

Preferred: is_active convention (auto-detected, no attribute needed):

use sqlx_record::prelude::*;

#[derive(Entity, FromRow)]
#[table_name = "users"]
struct User {
    #[primary_key]
    id: Uuid,
    name: String,
    is_active: bool,  // Auto-detected: FALSE = deleted, TRUE = active
}

Alternative: #[soft_delete] attribute on any bool field:

#[derive(Entity, FromRow)]
#[table_name = "users"]
struct User {
    #[primary_key]
    id: Uuid,
    name: String,

    #[soft_delete]       // Field will be FALSE when deleted
    is_active: bool,
}

Legacy: is_deleted/deleted fields are also auto-detected (TRUE = deleted).

Detection Priority

1. Field with #[soft_delete] attribute (FALSE = deleted)
2. Field named is_active with bool type (FALSE = deleted)
3. Field named is_deleted or deleted with bool type (TRUE = deleted)

Generated Methods

soft_delete() / soft_delete_by_{pk}()

Marks the record as deleted:

// Instance method
user.soft_delete(&pool).await?;

// Static method by primary key
User::soft_delete_by_id(&pool, &user_id).await?;

SQL generated (is_active convention):

UPDATE users SET is_active = FALSE WHERE id = ?

restore() / restore_by_{pk}()

Restores a soft-deleted record:

// Instance method
user.restore(&pool).await?;

// Static method by primary key
User::restore_by_id(&pool, &user_id).await?;

SQL generated (is_active convention):

UPDATE users SET is_active = TRUE WHERE id = ?

soft_delete_field()

Returns the field name:

let field = User::soft_delete_field();  // "is_active"

Filtering Deleted Records

Soft delete does NOT automatically filter find() queries. You must add the filter manually:

// Include only active (non-deleted)
let users = User::find(&pool, filters![("is_active", true)], None).await?;

// Include only deleted (trash view)
let deleted = User::find(&pool, filters![("is_active", false)], None).await?;

// Include all records
let all = User::find(&pool, filters![], None).await?;

Helper Pattern

Create a helper function for consistent filtering:

impl User {
    pub async fn find_active(
        pool: &Pool,
        mut filters: Vec<Filter<'_>>,
        index: Option<&str>
    ) -> Result<Vec<Self>, sqlx::Error> {
        filters.push(Filter::Equal("is_active", true.into()));
        Self::find(pool, filters, index).await
    }
}

// Usage
let users = User::find_active(&pool, filters![("role", "admin")], None).await?;

Usage Examples

Basic Flow

// Create user
let user = User {
    id: new_uuid(),
    name: "Alice".into(),
    is_active: true,
};
user.insert(&pool).await?;

// Soft delete
user.soft_delete(&pool).await?;
// user still exists in DB with is_active = false

// Find won't return deleted users (with proper filter)
let users = User::find(&pool, filters![("is_active", true)], None).await?;
// Alice not in results

// Restore
User::restore_by_id(&pool, &user.id).await?;
// user.is_active = true again

// Hard delete (permanent)
User::hard_delete_by_id(&pool, &user.id).await?;
// Row completely removed from database

With Audit Trail

use sqlx_record::{transaction, prelude::*};

async fn soft_delete_with_audit(
    pool: &Pool,
    user_id: &Uuid,
    actor_id: &Uuid
) -> Result<(), sqlx::Error> {
    transaction!(&pool, |tx| {
        // Soft delete the user
        User::soft_delete_by_id(&mut *tx, user_id).await?;

        // Record the deletion
        let change = EntityChange {
            id: new_uuid(),
            entity_id: *user_id,
            action: "soft_delete".into(),
            changed_at: chrono::Utc::now().timestamp_millis(),
            actor_id: *actor_id,
            session_id: Uuid::nil(),
            change_set_id: Uuid::nil(),
            new_value: None,
        };
        create_entity_change(&mut *tx, "entity_changes_users", &change).await?;

        Ok::<_, sqlx::Error>(())
    }).await
}

Cascade Soft Delete

async fn delete_user_cascade(pool: &Pool, user_id: &Uuid) -> Result<(), sqlx::Error> {
    transaction!(&pool, |tx| {
        // Soft delete user's orders
        let orders = Order::find(&mut *tx, filters![("user_id", user_id)], None).await?;
        for order in orders {
            order.soft_delete(&mut *tx).await?;
        }

        // Soft delete user
        User::soft_delete_by_id(&mut *tx, user_id).await?;

        Ok::<_, sqlx::Error>(())
    }).await
}

Database Schema

Recommended column definition:

-- MySQL
is_active BOOLEAN NOT NULL DEFAULT TRUE

-- PostgreSQL
is_active BOOLEAN NOT NULL DEFAULT TRUE

-- SQLite
is_active INTEGER NOT NULL DEFAULT 1  -- 1=true, 0=false

Add an index for efficient filtering:

CREATE INDEX idx_users_is_active ON users (is_active);

-- Or composite index for common queries
CREATE INDEX idx_users_active_name ON users (is_active, name);

Notes

• Soft delete field must be bool type
• The field is included in UpdateForm (can be manually toggled)
• hard_delete() / hard_delete_by_{pk}() are always available, even on entities with soft delete
• Consider adding deleted_at: Option<i64> for deletion timestamps
• For complex filtering, consider database views