/DB

Audit columns

Track when rows are created and last changed with server-side defaults and a small update hook.

updated 5 Jun 20261 min readv0.3.2View as Markdown

Most tables benefit from created_at and updated_at columns. The created timestamp is a pure database default; the updated timestamp needs a touch on each write.

Created timestamp

[Default(DbDefaults.Time.Now)] stamps the row server-side on insert, so the application never sets it:

[Default(DbDefaults.Time.Now)]
public DateTime CreatedAt { get; set; }

DbDefaults.Time.Now is portable: the generator emits the platform's UTC-now expression in the DDL. See DB constants.

Updated timestamp

Set UpdatedAt before every update. Centralize it so no call site forgets:

[Default(DbDefaults.Time.Now)]
public DateTime UpdatedAt { get; set; }

// in your service / repository
public Task<int> SaveAsync(TaskItem task) =>
    db.ExecuteAsync(d =>
    {
        task.UpdatedAt = DateTime.UtcNow;
        return d.Tasks.UpdateAsync(task);
    });

UpdateAsync writes every non-key column by default, so updated_at is included with no extra configuration.

Who changed it

To record an actor, add a column and set it from your request context:

public Guid? UpdatedBy { get; set; }
task.UpdatedAt = DateTime.UtcNow;
task.UpdatedBy = currentUserId;
await d.Tasks.UpdateAsync(task);
TIP
If you want the timestamp enforced even for writes that bypass the application, keep the C# touch for convenience and add a database trigger as the source of truth. The [Default] expression handles inserts; a BEFORE UPDATE trigger handles updates. A value convertor is the place to normalize an audit value (for example forcing UTC) on the way to the column.