Soft Deletes and Audit Trails in a Rust Application

Deleting data is usually a one-way door, so many applications mark rows as deleted and keep them. The mechanism is simple; the consequences are scattered through every query and every index. Everything compiles on Rust 1.90 with edition 2024, sqlx 0.8 and PostgreSQL 17.

The column and the queries

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

CREATE UNIQUE INDEX users_email_live ON users (email) WHERE deleted_at IS NULL;

CREATE VIEW live_users AS SELECT * FROM users WHERE deleted_at IS NULL;

The partial unique index is the part people forget. With a plain UNIQUE (email), a deleted user's email blocks anyone from signing up with it again. The partial form applies uniqueness only among live rows.

use sqlx::PgPool;

pub async fn soft_delete(pool: &PgPool, id: i64) -> sqlx::Result<bool> {
    let r = sqlx::query("UPDATE users SET deleted_at = now() WHERE id = $1 AND deleted_at IS NULL")
        .bind(id)
        .execute(pool)
        .await?;
    Ok(r.rows_affected() == 1)
}

pub async fn restore(pool: &PgPool, id: i64) -> sqlx::Result<bool> {
    let r = sqlx::query("UPDATE users SET deleted_at = NULL WHERE id = $1 AND deleted_at IS NOT NULL")
        .bind(id)
        .execute(pool)
        .await?;
    Ok(r.rows_affected() == 1)
}

pub async fn find_live(pool: &PgPool, id: i64) -> sqlx::Result<Option<(i64, String)>> {
    sqlx::query_as("SELECT id, email FROM live_users WHERE id = $1")
        .bind(id)
        .fetch_optional(pool)
        .await
}

Application code reads from the view (live_users) and writes to the table. Joins and foreign keys get harder: an order may reference a soft deleted customer, which is usually what you want for history, and sometimes not. Decide per relationship.

Why filters leak

Every new query is a chance to forget the condition. Two defenses: the view above, and a thin repository layer where the only way to read users adds the filter. Row-level security can enforce it in the database, at the cost of complexity. Whichever you choose, add a test that soft-deletes a row and asserts it does not appear in list, search and count endpoints.

An audit trail with a trigger

CREATE TABLE audit_log (
    id         BIGSERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    row_id     BIGINT NOT NULL,
    action     TEXT NOT NULL,
    actor      TEXT,
    old_data   JSONB,
    new_data   JSONB,
    at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE FUNCTION audit_users() RETURNS trigger AS $$
BEGIN
    INSERT INTO audit_log (table_name, row_id, action, actor, old_data, new_data)
    VALUES (
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id),
        TG_OP,
        current_setting('app.actor', true),
        CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
        CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_audit AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_users();

The trigger sees every change, including ones made in psql. It cannot know who the web user was, so the application passes it with a session setting inside the transaction:

pub async fn set_actor(tx: &mut sqlx::PgConnection, actor: &str) -> sqlx::Result<()> {
    sqlx::query("SELECT set_config('app.actor', $1, true)")
        .bind(actor)
        .execute(tx)
        .await?;
    Ok(())
}

The true makes the setting local to the transaction. Call it first in every write transaction (a middleware or a helper that begins transactions).

Application level audit

Triggers capture facts, not reasons. If you need "deleted because of a chargeback", the application writes its own event with the reason. Many systems do both: triggers for completeness, events for meaning. A related pattern, storing events as the source of truth, is in event sourcing.

The privacy catch

Soft deletes and audit logs both retain personal data. A "right to erasure" request means actually removing or anonymizing the user's data, including the audit copies, after any retention period the law requires. Plan the purge job: hard delete or overwrite fields with placeholders, and test it. Keep audit tables append-only for application roles, and partition by time so old data can be dropped cheaply. For the design that makes this easier, see schema design for Rust types.