Automatic audit trails and soft delete in Delphi with Trysil

Most ORMs handle INSERT, UPDATE, and DELETE well. But when you need to track who changed a record and when, or keep deleted records around instead of erasing them, you're usually on your own — writing triggers, adding boilerplate to every repository method, or bolting on an external audit library.

In Trysil (an open-source ORM for Delphi), I wanted these features to be declarative: add an attribute, and the framework handles the rest. No base class to inherit, no interface to implement, no code to write beyond the attribute itself.

This article shows how it works.

The problem

Consider a typical entity:

[TTable('Invoices')]
[TSequence('InvoicesID')]
TInvoice = class
strict private
  [TPrimaryKey]
  [TColumn('ID')]
  FID: TTPrimaryKey;

  [TColumn('InvoiceNumber')]
  FInvoiceNumber: String;

  [TColumn('Amount')]
  FAmount: Double;

  [TVersionColumn]
  [TColumn('VersionID')]
  FVersionID: TTVersion;
public
  property ID: TTPrimaryKey read FID;
  property InvoiceNumber: String read FInvoiceNumber write FInvoiceNumber;
  property Amount: Double read FAmount write FAmount;
end;

Now you get a new requirement: "We need to know who created each invoice and when. And when invoices are deleted, don't actually remove them — mark them as deleted so we can audit them later."

In most ORMs this means: add columns to the table, add fields to the entity, then manually set CreatedAt := Now in every insert method, UpdatedAt := Now in every update method, and replace every DELETE FROM with an UPDATE ... SET DeletedAt = Now. And don't forget to add WHERE DeletedAt IS NULL to every single query.

The solution: six attributes

Trysil solves this with six attributes that you place on entity fields:

Attribute	Populated on	Field type
[TCreatedAt]	INSERT	TTNullable<TDateTime>
[TCreatedBy]	INSERT	String
[TUpdatedAt]	UPDATE	TTNullable<TDateTime>
[TUpdatedBy]	UPDATE	String
[TDeletedAt]	DELETE (soft)	TTNullable<TDateTime>
[TDeletedBy]	DELETE (soft)	String

Here's the same invoice entity with full change tracking and soft delete:

[TTable('Invoices')]
[TSequence('InvoicesID')]
TInvoice = class
strict private
  [TPrimaryKey]
  [TColumn('ID')]
  FID: TTPrimaryKey;

  [TColumn('InvoiceNumber')]
  FInvoiceNumber: String;

  [TColumn('Amount')]
  FAmount: Double;

  [TCreatedAt]
  [TColumn('CreatedAt')]
  FCreatedAt: TTNullable<TDateTime>;

  [TCreatedBy]
  [TColumn('CreatedBy')]
  FCreatedBy: String;

  [TUpdatedAt]
  [TColumn('UpdatedAt')]
  FUpdatedAt: TTNullable<TDateTime>;

  [TUpdatedBy]
  [TColumn('UpdatedBy')]
  FUpdatedBy: String;

  [TDeletedAt]
  [TColumn('DeletedAt')]
  FDeletedAt: TTNullable<TDateTime>;

  [TDeletedBy]
  [TColumn('DeletedBy')]
  FDeletedBy: String;

  [TVersionColumn]
  [TColumn('VersionID')]
  FVersionID: TTVersion;
public
  property ID: TTPrimaryKey read FID;
  property InvoiceNumber: String read FInvoiceNumber write FInvoiceNumber;
  property Amount: Double read FAmount write FAmount;
  property CreatedAt: TTNullable<TDateTime> read FCreatedAt;
  property CreatedBy: String read FCreatedBy;
  property UpdatedAt: TTNullable<TDateTime> read FUpdatedAt;
  property UpdatedBy: String read FUpdatedBy;
  property DeletedAt: TTNullable<TDateTime> read FDeletedAt;
  property DeletedBy: String read FDeletedBy;
end;

That's it. No other code changes. The ORM does the rest.

What happens at runtime

Insert

LInvoice := LContext.CreateEntity<TInvoice>();
LInvoice.InvoiceNumber := 'INV-2026-001';
LInvoice.Amount := 1500.00;
LContext.Insert<TInvoice>(LInvoice);

Before executing the INSERT, the resolver automatically sets CreatedAt to the current timestamp and CreatedBy to the current user. You never touch these fields yourself.

The generated SQL looks like:

INSERT INTO Invoices (InvoiceNumber, Amount, CreatedAt, CreatedBy, VersionID)
VALUES (:p1, :p2, :p3, :p4, 0)

Update

LInvoice.Amount := 1750.00;
LContext.Update<TInvoice>(LInvoice);

The resolver sets UpdatedAt and UpdatedBy automatically:

UPDATE Invoices
SET InvoiceNumber = :p1, Amount = :p2, UpdatedAt = :p3, UpdatedBy = :p4,
    VersionID = VersionID + 1
WHERE ID = :pk AND VersionID = :ver

Note the optimistic locking in the WHERE clause — [TVersionColumn] works alongside change tracking.

Delete (soft)

LContext.Delete<TInvoice>(LInvoice);

Because the entity has a [TDeletedAt] field, this does not execute a DELETE FROM. Instead it runs:

UPDATE Invoices
SET DeletedAt = :p1, DeletedBy = :p2, VersionID = VersionID + 1
WHERE ID = :pk AND VersionID = :ver

The record stays in the database. It's just marked as deleted.

Queries automatically exclude soft-deleted records

Every SELECT generated by Trysil checks for the presence of [TDeletedAt] on the entity. If found, it appends DeletedAt IS NULL to the WHERE clause:

LContext.SelectAll<TInvoice>(LInvoices);
// Generated: SELECT ... FROM Invoices WHERE DeletedAt IS NULL

This happens transparently. You don't add any filter — the ORM knows that if you declared [TDeletedAt], you want soft-deleted records hidden by default.

When you need to see deleted records

Use IncludeDeleted on the filter builder:

LFilter := LContext.CreateFilterBuilder<TInvoice>()
  .IncludeDeleted
  .Build;
LContext.Select<TInvoice>(LInvoices, LFilter);
// Generated: SELECT ... FROM Invoices (no DeletedAt filter)

This is useful for admin panels, audit views, or data recovery.

Providing the current user

The *By attributes need to know who the current user is. Trysil doesn't impose any authentication mechanism — instead, you provide a callback:

LContext.OnGetCurrentUser :=
  function: String
  begin
    result := GetCurrentSessionUser(); // your logic here
  end;

This callback is invoked by the resolver right before executing INSERT, UPDATE, or soft DELETE. If not assigned, an empty string is written — so the *By fields are optional. You can use only the *At attributes if you just need timestamps.

Mix and match

You don't have to use all six attributes. Common combinations:

Timestamps only (no user tracking):

[TCreatedAt]
[TColumn('CreatedAt')]
FCreatedAt: TTNullable<TDateTime>;

[TUpdatedAt]
[TColumn('UpdatedAt')]
FUpdatedAt: TTNullable<TDateTime>;

Soft delete only (no creation/update tracking):

[TDeletedAt]
[TColumn('DeletedAt')]
FDeletedAt: TTNullable<TDateTime>;

Full audit trail — all six, as shown in the invoice example above.

Each entity can have its own combination. There's no global setting to toggle.

How it works internally

The implementation touches three layers:

1. Mapping — When Trysil first encounters an entity class, it scans its RTTI for the six attributes and builds a TTChangeTrackingMap for each phase (created, updated, deleted). Each map holds references to the ChangedAt and ChangedBy column mappings. This scan happens once and is cached.

2. Resolver — Before executing any command, the resolver calls ApplyChangeTracking with the appropriate map. This sets the timestamp via TTNullable<TDateTime>.Create(Now) and the user string via the OnGetCurrentUser callback.

3. SQL generation — When [TDeletedAt] is present, the resolver switches from CreateDeleteCommand (which generates DELETE FROM) to CreateSoftDeleteCommand (which generates an UPDATE targeting only the deleted tracking columns and the version column). On the SELECT side, AddSoftDeleteWhere injects the DeletedAt IS NULL condition unless IncludeDeleted is set.

The soft delete command also skips relation checks (CheckRelations), since the record isn't actually being removed — foreign key constraints don't apply.

Database table

The corresponding table just needs the extra columns. For example, with PostgreSQL:

CREATE TABLE Invoices (
  ID SERIAL PRIMARY KEY,
  InvoiceNumber VARCHAR(50) NOT NULL,
  Amount DOUBLE PRECISION NOT NULL,
  CreatedAt TIMESTAMP,
  CreatedBy VARCHAR(100),
  UpdatedAt TIMESTAMP,
  UpdatedBy VARCHAR(100),
  DeletedAt TIMESTAMP,
  DeletedBy VARCHAR(100),
  VersionID INTEGER NOT NULL DEFAULT 0
);

No triggers, no stored procedures. The ORM handles everything at the application level.

Wrapping up

Adding audit trails and soft delete to a Delphi application usually means writing repetitive code in every data access method. With Trysil's attribute-based approach, you declare the behavior once on the entity and the framework enforces it consistently across all operations.

The feature works with all four supported databases (SQLite, PostgreSQL, SQL Server, Firebird) and composes naturally with the rest of the framework — optimistic locking, identity map, fluent query builder, JSON serialization.

Links:

• GitHub: github.com/davidlastrucci/Trysil
• Documentation: davidlastrucci.github.io/trysil
• Install: available on GetIt and via boss install davidlastrucci/Trysil

If you have questions or feedback, feel free to open an issue on GitHub.