Soft Delete, Audit Columns and History Tables

Part 3 refused to delete a patient with history, and left the question open: then how does a patient leave? This part answers it three ways. Audit columns that record who did what and when, kept by the database so no code path can forget them; a soft delete that keeps the row and hides it, with the two costs that come with it; and a history of every version of a row, built in on SQL Server and hand-built on PostgreSQL. Then the case where a real DELETE plus an audit row is the better answer.

Who, and when

Section 1 adds five columns to the patients table:

CREATE TABLE patients (
    id         bigint GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_patients PRIMARY KEY,
    full_name  varchar(200) NOT NULL,
    phone      varchar(30)  NOT NULL CONSTRAINT uq_patients_phone UNIQUE,
    created_at timestamptz  NOT NULL DEFAULT now(),
    created_by varchar(100) NOT NULL DEFAULT current_setting('app.staff_user', true),
    updated_at timestamptz  NULL,
    updated_by varchar(100) NULL,
    deleted_at timestamptz  NULL
);

The "who" is the interesting one. Every request from the app uses the same database login, so the login is useless as a name; instead the app declares the signed-in staff member on the connection at the start of each request, SET app.staff_user = 'reception.front' in PostgreSQL and sp_set_session_context in SQL Server, and the default reads it back. Section 2 shows the consequence of forgetting: the first insert fails with null value in column "created_by" ... violates not-null constraint, which is the right failure, because a row nobody claims should not exist. Section 3 keeps updated_at and updated_by with a trigger, so that an UPDATE from any code path fills them:

CREATE FUNCTION set_updated_columns() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    NEW.updated_by := current_setting('app.staff_user', true);
    RETURN NEW;
END
$$;
CREATE TRIGGER tr_patients_set_updated BEFORE UPDATE ON patients
    FOR EACH ROW EXECUTE FUNCTION set_updated_columns();

A PostgreSQL trigger runs a function once per row and a BEFORE trigger edits the row on its way in. SQL Server's trigger runs once per statement, after it, and has to update the affected rows a second time from the inserted pseudo-table; the script has that version. After Dr. Rossi corrects Aisha Khan's name, the row reads updated_by = dr.rossi and the UPDATE statement never mentioned either column.

Soft delete, and the phone that comes back

A soft delete is an UPDATE that sets deleted_at. David Chen leaves the clinic, his row stays, and section 4 asks what happens when a new patient, Tomás Silva, arrives with David's old phone number. The plain unique constraint still counts David:

ERROR:  duplicate key value violates unique constraint "uq_patients_phone"
DETAIL:  Key (phone)=(+00-555-0102) already exists.

The rule the clinic means is "unique among patients who are here", which is a filter on the index:

ALTER TABLE patients DROP CONSTRAINT uq_patients_phone;
CREATE UNIQUE INDEX uq_patients_phone_active ON patients (phone) WHERE deleted_at IS NULL;

The same statement works on SQL Server, where it is a filtered index and needs QUOTED_IDENTIFIER ON in every session that writes the table, which sqlcmd does not set by default. Tomás is accepted; Liam O'Brien with the same number is refused by uq_patients_phone_active; and section 4d shows the first cost of soft delete: undeleting David, an UPDATE that clears deleted_at, now collides with Tomás. A soft-deleted row is still a row, and everything unique about it is still in the way.

The second cost is in section 5. Five rows, four active, and a query that forgets the filter returns both David and Tomás for that phone number. Every query on the table must remember WHERE deleted_at IS NULL forever, and the practical defence is a view that remembers it once:

CREATE VIEW active_patients AS
SELECT id, full_name, phone, created_at, created_by, updated_at, updated_by
FROM patients
WHERE deleted_at IS NULL;

Through the view the phone number finds only Tomás. In EF Core the same thing is a global query filter on the entity; either way, the raw table is for administrators and the filtered shape is what the app reads.

History: every version of a row

An appointment goes Booked, CheckedIn, InProgress, Done, and the clinic wants to know what it was at 10:03 when the complaint came in. SQL Server has this built in. Two period columns and one clause turn a table into a system-versioned temporal table:

CREATE TABLE appointments (
    id         BIGINT IDENTITY(1,1) NOT NULL CONSTRAINT pk_appointments PRIMARY KEY,
    patient_id BIGINT        NOT NULL CONSTRAINT fk_appointments_patients REFERENCES patients (id),
    starts_at  DATETIME2(3)  NOT NULL,
    status     NVARCHAR(20)  NOT NULL,
    updated_by NVARCHAR(100) NOT NULL,
    -- Full precision here: two changes inside one millisecond would give a zero-length
    -- history row, which FOR SYSTEM_TIME queries leave out.
    valid_from DATETIME2(7) GENERATED ALWAYS AS ROW START NOT NULL,
    valid_to   DATETIME2(7) GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.appointments_history));

From then on every UPDATE and DELETE copies the old version into the history table with its validity period, the history table refuses direct deletes (Msg 13560, Cannot delete rows from a temporal history table), and the question is one clause: SELECT ... FROM appointments FOR SYSTEM_TIME AS OF @as_of, which answered CheckedIn reception.front for a moment captured between the second and third update. The comment about precision is a real finding: with DATETIME2(3), an insert and an update inside the same millisecond produced a history row whose period was zero long, and FOR SYSTEM_TIME ALL left it out.

PostgreSQL 18 has no system versioning, so section 6 builds it: a history table with the same columns plus valid_from, valid_to and an operation letter, and a trigger that copies the old row on every UPDATE and DELETE:

CREATE FUNCTION appointments_keep_history() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO appointments_history
        (id, patient_id, starts_at, status, updated_by, valid_from, valid_to, operation)
    VALUES
        (OLD.id, OLD.patient_id, OLD.starts_at, OLD.status, OLD.updated_by, OLD.valid_from, now(),
         left(TG_OP, 1));
    IF TG_OP = 'UPDATE' THEN
        NEW.valid_from := now();
        RETURN NEW;
    END IF;
    RETURN OLD;
END
$$;
CREATE TRIGGER tr_appointments_keep_history BEFORE UPDATE OR DELETE ON appointments
    FOR EACH ROW EXECUTE FUNCTION appointments_keep_history();

After Maria's appointment has gone through its four statuses, the history table holds:

     what      | id |   status   |   updated_by    |          valid_from           |           valid_to            | operation
---------------+----+------------+-----------------+-------------------------------+-------------------------------+-----------
 history table |  1 | Booked     | reception.front | 2026-09-25 05:19:53.597007+00 | 2026-09-25 05:19:54.601966+00 | U
 history table |  1 | CheckedIn  | reception.front | 2026-09-25 05:19:54.601966+00 | 2026-09-25 05:19:56.615156+00 | U
 history table |  1 | InProgress | dr.rossi        | 2026-09-25 05:19:56.615156+00 | 2026-09-25 05:19:57.625726+00 | U

The "as of" query is written by hand, a UNION of the current row if it was already valid and the history row whose period contains the moment, and it gave the same answer, CheckedIn. What the hand-built version lacks is the protection: nothing stops a DELETE on the history table except permissions. Both versions record when, not who, which is why updated_by stays an ordinary column that each UPDATE sets, so it travels into the history rows.

When a real delete is right

Yuki Tanaka was registered by mistake, has no appointments, and should not exist as a hidden row forever. Section 7 deletes her for real, and an AFTER DELETE trigger writes the old row into an audit log as JSON, to_jsonb(OLD) here and FOR JSON PATH on SQL Server, with who did it and when:

 id | table_name | row_id | action |                                          old_data                                          |  changed_by
----+------------+--------+--------+--------------------------------------------------------------------------------------------+--------------
  1 | patients   |      5 | DELETE | {"id": 5, "phone": "+00-555-0104", "full_name": "Yuki Tanaka", "created_at": ...}         | admin.office

Then section 7b tries the same on Maria, who has an appointment, and the foreign key from Part 3 refuses: violates foreign key constraint "fk_appointments_patients". That is the rule of thumb in one run. A row that nothing references and that was wrong can be deleted with an audit row. A row that history hangs off is soft-deleted, so the history keeps its meaning. A row whose own changes matter gets a history table. Most clinics need all three, on different tables.

Frequently asked

How do I implement soft delete in SQL?

Add a nullable deleted_at column, set it instead of deleting, and move every unique constraint to a filtered or partial unique index WHERE deleted_at IS NULL so a departed patient's phone number can be reused. Give the app a view or a global query filter so no query forgets the filter.

What are the disadvantages of soft delete?

Every query must filter the deleted rows or it returns them. Unique values of a deleted row are still taken unless the index is filtered, and undeleting can collide with a newer row. Tables grow forever. Use it where history hangs off the row, not for rows that were simply wrong.

How do I keep a history of changes to a table?

In SQL Server, make it a system-versioned temporal table with two period columns and SYSTEM_VERSIONING = ON, then query with FOR SYSTEM_TIME AS OF. In PostgreSQL, add a history table and a trigger that copies the old row on every update and delete, and write the as-of query as a union of current and history rows.

Next: Part 11, schema migrations, where the columns this part added are added to a table with 200,000 live rows, and timed.