oxify_storage/
soft_delete.rs

1//! Soft delete utilities for marking records as deleted without physical removal
2//!
3//! This module provides standardized patterns for implementing soft deletes,
4//! which mark records as deleted without removing them from the database.
5//!
6//! # Benefits of Soft Deletes
7//!
8//! - **Data Recovery**: Accidentally deleted records can be restored
9//! - **Audit Trail**: Maintain complete history of all operations
10//! - **Referential Integrity**: Foreign key constraints remain valid
11//! - **Performance**: Faster than cascading deletes
12//!
13//! # Usage Patterns
14//!
15//! ## Basic Soft Delete
16//! ```ignore
17//! use oxify_storage::soft_delete::SoftDeleteBuilder;
18//!
19//! SoftDeleteBuilder::new("workflows")
20//!     .where_clause("id = $1")
21//!     .mark_deleted(&pool, &[&workflow_id])
22//!     .await?;
23//! ```
24//!
25//! ## Soft Delete with Metadata
26//! ```ignore
27//! use oxify_storage::soft_delete::SoftDeleteMetadata;
28//!
29//! let metadata = SoftDeleteMetadata::new(user_id, Some("User requested deletion"));
30//! SoftDeleteBuilder::new("workflows")
31//!     .where_clause("id = $1")
32//!     .with_metadata(metadata)
33//!     .mark_deleted(&pool, &[&workflow_id])
34//!     .await?;
35//! ```
36
37use chrono::{DateTime, Utc};
38use serde::{Deserialize, Serialize};
39use uuid::Uuid;
40
41use crate::{Result, StorageError};
42
43/// Metadata for soft delete operations
44#[derive(Debug, Clone, Serialize, Deserialize)]
45pub struct SoftDeleteMetadata {
46    /// User who performed the deletion
47    pub deleted_by: Option<Uuid>,
48    /// Timestamp of deletion
49    pub deleted_at: DateTime<Utc>,
50    /// Reason for deletion
51    pub deletion_reason: Option<String>,
52}
53
54impl SoftDeleteMetadata {
55    /// Create new soft delete metadata
56    ///
57    /// # Examples
58    /// ```
59    /// # use oxify_storage::soft_delete::SoftDeleteMetadata;
60    /// # use uuid::Uuid;
61    /// let user_id = Uuid::new_v4();
62    /// let metadata = SoftDeleteMetadata::new(user_id, Some("User requested"));
63    /// ```
64    pub fn new(deleted_by: Uuid, deletion_reason: Option<&str>) -> Self {
65        Self {
66            deleted_by: Some(deleted_by),
67            deleted_at: Utc::now(),
68            deletion_reason: deletion_reason.map(|s| s.to_string()),
69        }
70    }
71
72    /// Create soft delete metadata without user tracking
73    pub fn anonymous(deletion_reason: Option<&str>) -> Self {
74        Self {
75            deleted_by: None,
76            deleted_at: Utc::now(),
77            deletion_reason: deletion_reason.map(|s| s.to_string()),
78        }
79    }
80}
81
82/// Builder for constructing soft delete queries
83///
84/// This builder helps construct safe, parameterized soft delete queries.
85pub struct SoftDeleteBuilder {
86    table: String,
87    where_clause: Option<String>,
88    deleted_at_column: String,
89    deleted_by_column: Option<String>,
90    deletion_reason_column: Option<String>,
91    metadata: Option<SoftDeleteMetadata>,
92}
93
94impl SoftDeleteBuilder {
95    /// Create a new soft delete builder
96    ///
97    /// # Examples
98    /// ```
99    /// # use oxify_storage::soft_delete::SoftDeleteBuilder;
100    /// let builder = SoftDeleteBuilder::new("workflows");
101    /// ```
102    pub fn new(table: &str) -> Self {
103        Self {
104            table: table.to_string(),
105            where_clause: None,
106            deleted_at_column: "deleted_at".to_string(),
107            deleted_by_column: None,
108            deletion_reason_column: None,
109            metadata: None,
110        }
111    }
112
113    /// Set the WHERE clause for the soft delete
114    ///
115    /// # Examples
116    /// ```
117    /// # use oxify_storage::soft_delete::SoftDeleteBuilder;
118    /// let builder = SoftDeleteBuilder::new("workflows")
119    ///     .where_clause("id = $1");
120    /// ```
121    pub fn where_clause(mut self, clause: &str) -> Self {
122        self.where_clause = Some(clause.to_string());
123        self
124    }
125
126    /// Set custom column name for deleted_at timestamp
127    pub fn deleted_at_column(mut self, column: &str) -> Self {
128        self.deleted_at_column = column.to_string();
129        self
130    }
131
132    /// Enable tracking of who deleted the record
133    pub fn with_deleted_by_column(mut self, column: &str) -> Self {
134        self.deleted_by_column = Some(column.to_string());
135        self
136    }
137
138    /// Enable tracking of deletion reason
139    pub fn with_deletion_reason_column(mut self, column: &str) -> Self {
140        self.deletion_reason_column = Some(column.to_string());
141        self
142    }
143
144    /// Set metadata for the soft delete operation
145    pub fn with_metadata(mut self, metadata: SoftDeleteMetadata) -> Self {
146        self.metadata = Some(metadata);
147        self
148    }
149
150    /// Build the UPDATE query SQL
151    fn build_query(&self) -> Result<String> {
152        if self.where_clause.is_none() {
153            return Err(StorageError::ValidationError(
154                "WHERE clause is required for soft delete".to_string(),
155            ));
156        }
157
158        let mut updates = vec![format!("{} = NOW()", self.deleted_at_column)];
159
160        if let Some(ref metadata) = self.metadata {
161            if let Some(ref col) = self.deleted_by_column {
162                if let Some(user_id) = metadata.deleted_by {
163                    updates.push(format!("{col} = '{user_id}'"));
164                }
165            }
166
167            if let Some(ref col) = self.deletion_reason_column {
168                if let Some(ref reason) = metadata.deletion_reason {
169                    // Note: This should use parameterized queries in production
170                    let escaped_reason = reason.replace('\'', "''");
171                    updates.push(format!("{col} = '{escaped_reason}'"));
172                }
173            }
174        }
175
176        let update_clause = updates.join(", ");
177        let where_clause = self.where_clause.as_ref().unwrap();
178
179        Ok(format!(
180            "UPDATE {} SET {} WHERE {} AND {} IS NULL",
181            self.table, update_clause, where_clause, self.deleted_at_column
182        ))
183    }
184
185    /// Get the SQL query for soft delete
186    ///
187    /// Use this to execute the soft delete with your own parameters.
188    ///
189    /// # Examples
190    /// ```ignore
191    /// let builder = SoftDeleteBuilder::new("workflows").where_clause("id = $1");
192    /// let query = builder.soft_delete_query()?;
193    /// let result = sqlx::query(&query)
194    ///     .bind(workflow_id)
195    ///     .execute(pool)
196    ///     .await?;
197    /// ```
198    pub fn soft_delete_query(&self) -> Result<String> {
199        self.build_query()
200    }
201}
202
203/// Helper for querying only non-deleted records
204pub struct SoftDeleteFilter {
205    deleted_at_column: String,
206}
207
208impl SoftDeleteFilter {
209    /// Create a new soft delete filter
210    ///
211    /// # Examples
212    /// ```
213    /// # use oxify_storage::soft_delete::SoftDeleteFilter;
214    /// let filter = SoftDeleteFilter::new("deleted_at");
215    /// assert_eq!(filter.not_deleted_clause(), "deleted_at IS NULL");
216    /// ```
217    pub fn new(deleted_at_column: &str) -> Self {
218        Self {
219            deleted_at_column: deleted_at_column.to_string(),
220        }
221    }
222
223    /// Get SQL clause for filtering non-deleted records
224    ///
225    /// # Examples
226    /// ```
227    /// # use oxify_storage::soft_delete::SoftDeleteFilter;
228    /// let filter = SoftDeleteFilter::default();
229    /// let clause = filter.not_deleted_clause();
230    /// assert_eq!(clause, "deleted_at IS NULL");
231    /// ```
232    pub fn not_deleted_clause(&self) -> String {
233        format!("{} IS NULL", self.deleted_at_column)
234    }
235
236    /// Get SQL clause for filtering deleted records
237    ///
238    /// # Examples
239    /// ```
240    /// # use oxify_storage::soft_delete::SoftDeleteFilter;
241    /// let filter = SoftDeleteFilter::default();
242    /// let clause = filter.deleted_clause();
243    /// assert_eq!(clause, "deleted_at IS NOT NULL");
244    /// ```
245    pub fn deleted_clause(&self) -> String {
246        format!("{} IS NOT NULL", self.deleted_at_column)
247    }
248
249    /// Get SQL clause for filtering by deletion time range
250    pub fn deleted_between_clause(&self, start: DateTime<Utc>, end: DateTime<Utc>) -> String {
251        format!(
252            "{} BETWEEN '{}' AND '{}'",
253            self.deleted_at_column,
254            start.to_rfc3339(),
255            end.to_rfc3339()
256        )
257    }
258}
259
260impl Default for SoftDeleteFilter {
261    fn default() -> Self {
262        Self::new("deleted_at")
263    }
264}
265
266/// Helper for restoring soft-deleted records
267pub struct SoftDeleteRestorer {
268    table: String,
269    where_clause: Option<String>,
270    deleted_at_column: String,
271    deleted_by_column: Option<String>,
272    deletion_reason_column: Option<String>,
273}
274
275impl SoftDeleteRestorer {
276    /// Create a new soft delete restorer
277    ///
278    /// # Examples
279    /// ```
280    /// # use oxify_storage::soft_delete::SoftDeleteRestorer;
281    /// let restorer = SoftDeleteRestorer::new("workflows");
282    /// ```
283    pub fn new(table: &str) -> Self {
284        Self {
285            table: table.to_string(),
286            where_clause: None,
287            deleted_at_column: "deleted_at".to_string(),
288            deleted_by_column: None,
289            deletion_reason_column: None,
290        }
291    }
292
293    /// Set the WHERE clause for the restore
294    pub fn where_clause(mut self, clause: &str) -> Self {
295        self.where_clause = Some(clause.to_string());
296        self
297    }
298
299    /// Set custom column name for deleted_at timestamp
300    pub fn deleted_at_column(mut self, column: &str) -> Self {
301        self.deleted_at_column = column.to_string();
302        self
303    }
304
305    /// Clear deleted_by column on restore
306    pub fn clear_deleted_by_column(mut self, column: &str) -> Self {
307        self.deleted_by_column = Some(column.to_string());
308        self
309    }
310
311    /// Clear deletion_reason column on restore
312    pub fn clear_deletion_reason_column(mut self, column: &str) -> Self {
313        self.deletion_reason_column = Some(column.to_string());
314        self
315    }
316
317    /// Build the restore query SQL
318    fn build_query(&self) -> Result<String> {
319        if self.where_clause.is_none() {
320            return Err(StorageError::ValidationError(
321                "WHERE clause is required for restore".to_string(),
322            ));
323        }
324
325        let mut updates = vec![format!("{} = NULL", self.deleted_at_column)];
326
327        if let Some(ref col) = self.deleted_by_column {
328            updates.push(format!("{col} = NULL"));
329        }
330
331        if let Some(ref col) = self.deletion_reason_column {
332            updates.push(format!("{col} = NULL"));
333        }
334
335        let update_clause = updates.join(", ");
336        let where_clause = self.where_clause.as_ref().unwrap();
337
338        Ok(format!(
339            "UPDATE {} SET {} WHERE {} AND {} IS NOT NULL",
340            self.table, update_clause, where_clause, self.deleted_at_column
341        ))
342    }
343
344    /// Get the SQL query for restore
345    ///
346    /// Use this to execute the restore with your own parameters.
347    ///
348    /// # Examples
349    /// ```ignore
350    /// let restorer = SoftDeleteRestorer::new("workflows").where_clause("id = $1");
351    /// let query = restorer.restore_query()?;
352    /// let result = sqlx::query(&query)
353    ///     .bind(workflow_id)
354    ///     .execute(pool)
355    ///     .await?;
356    /// ```
357    pub fn restore_query(&self) -> Result<String> {
358        self.build_query()
359    }
360}
361
362#[cfg(test)]
363mod tests {
364    use super::*;
365
366    #[test]
367    fn test_soft_delete_metadata_new() {
368        let user_id = Uuid::new_v4();
369        let metadata = SoftDeleteMetadata::new(user_id, Some("Test reason"));
370
371        assert_eq!(metadata.deleted_by, Some(user_id));
372        assert_eq!(metadata.deletion_reason, Some("Test reason".to_string()));
373    }
374
375    #[test]
376    fn test_soft_delete_metadata_anonymous() {
377        let metadata = SoftDeleteMetadata::anonymous(Some("System cleanup"));
378
379        assert_eq!(metadata.deleted_by, None);
380        assert_eq!(metadata.deletion_reason, Some("System cleanup".to_string()));
381    }
382
383    #[test]
384    fn test_soft_delete_builder_basic_query() {
385        let builder = SoftDeleteBuilder::new("workflows").where_clause("id = $1");
386
387        let query = builder.build_query().unwrap();
388        assert!(query.contains("UPDATE workflows"));
389        assert!(query.contains("SET deleted_at = NOW()"));
390        assert!(query.contains("WHERE id = $1"));
391        assert!(query.contains("AND deleted_at IS NULL"));
392    }
393
394    #[test]
395    fn test_soft_delete_builder_no_where_clause() {
396        let builder = SoftDeleteBuilder::new("workflows");
397
398        let result = builder.build_query();
399        assert!(result.is_err());
400        match result {
401            Err(StorageError::ValidationError(msg)) => {
402                assert!(msg.contains("WHERE clause is required"));
403            }
404            _ => panic!("Expected ValidationError"),
405        }
406    }
407
408    #[test]
409    fn test_soft_delete_builder_custom_columns() {
410        let builder = SoftDeleteBuilder::new("workflows")
411            .where_clause("id = $1")
412            .deleted_at_column("removed_at")
413            .with_deleted_by_column("removed_by")
414            .with_deletion_reason_column("removal_reason");
415
416        let query = builder.build_query().unwrap();
417        assert!(query.contains("removed_at = NOW()"));
418        assert!(query.contains("AND removed_at IS NULL"));
419    }
420
421    #[test]
422    fn test_soft_delete_filter_default() {
423        let filter = SoftDeleteFilter::default();
424        assert_eq!(filter.not_deleted_clause(), "deleted_at IS NULL");
425        assert_eq!(filter.deleted_clause(), "deleted_at IS NOT NULL");
426    }
427
428    #[test]
429    fn test_soft_delete_filter_custom_column() {
430        let filter = SoftDeleteFilter::new("removed_at");
431        assert_eq!(filter.not_deleted_clause(), "removed_at IS NULL");
432        assert_eq!(filter.deleted_clause(), "removed_at IS NOT NULL");
433    }
434
435    #[test]
436    fn test_soft_delete_filter_time_range() {
437        let filter = SoftDeleteFilter::default();
438        let start = DateTime::parse_from_rfc3339("2026-01-01T00:00:00Z")
439            .unwrap()
440            .with_timezone(&Utc);
441        let end = DateTime::parse_from_rfc3339("2026-12-31T23:59:59Z")
442            .unwrap()
443            .with_timezone(&Utc);
444
445        let clause = filter.deleted_between_clause(start, end);
446        assert!(clause.contains("deleted_at BETWEEN"));
447        assert!(clause.contains("2026-01-01"));
448        assert!(clause.contains("2026-12-31"));
449    }
450
451    #[test]
452    fn test_soft_delete_restorer_basic_query() {
453        let restorer = SoftDeleteRestorer::new("workflows").where_clause("id = $1");
454
455        let query = restorer.build_query().unwrap();
456        assert!(query.contains("UPDATE workflows"));
457        assert!(query.contains("SET deleted_at = NULL"));
458        assert!(query.contains("WHERE id = $1"));
459        assert!(query.contains("AND deleted_at IS NOT NULL"));
460    }
461
462    #[test]
463    fn test_soft_delete_restorer_with_metadata_columns() {
464        let restorer = SoftDeleteRestorer::new("workflows")
465            .where_clause("id = $1")
466            .clear_deleted_by_column("deleted_by")
467            .clear_deletion_reason_column("deletion_reason");
468
469        let query = restorer.build_query().unwrap();
470        assert!(query.contains("deleted_at = NULL"));
471        assert!(query.contains("deleted_by = NULL"));
472        assert!(query.contains("deletion_reason = NULL"));
473    }
474
475    #[test]
476    fn test_soft_delete_restorer_no_where_clause() {
477        let restorer = SoftDeleteRestorer::new("workflows");
478
479        let result = restorer.build_query();
480        assert!(result.is_err());
481        match result {
482            Err(StorageError::ValidationError(msg)) => {
483                assert!(msg.contains("WHERE clause is required"));
484            }
485            _ => panic!("Expected ValidationError"),
486        }
487    }
488}
oxify_storage/soft_delete.rs

oxify_storage/
soft_delete.rs
