Change logging (audit)

Any entity can capture an append-only change log of every insert, update and delete. Because the log is written by a database trigger, it captures every mutation path — form submit, row delete, LINQ, import, even raw SQL — inside the same transaction as the change itself. Nothing that touches the table escapes it.

Each change is written straight into the [Audit].[AuditLog] ledger inside the mutating transaction — synchronous, no staging table or background worker. See How it’s stored below.

Enabling it

Set Logging="Enabled" on the entity. See Entities → Logging.

<Entity Name="Invoice" SchemaName="Sales" PluralName="Invoices" Logging="Enabled">
  <Fields> … </Fields>
</Entity>

On the next model migration, AuditLogSqlHandlerService installs a per-entity AFTER INSERT, UPDATE, DELETE trigger. Setting Logging="Disabled" (or removing the attribute) drops the trigger on the next migration, so capture stops cleanly. The operation is idempotent and dual-dialect (SQL Server + PostgreSQL).

What gets written

Each committed change becomes a row in [Audit].[AuditLog] with these fields:

Column	Meaning
SchemaName, TableName, RowId	which record changed
Operation	INS, UPD, or DEL
ChangedById	the actor — taken straight off the row’s own audit columns (CreatedById / UpdatedById), which the app stamps from @SessionUserId
CompanyId	the tenant, from the row
Id	monotonic identity key — what warehousing reads forward from
ChangedAt	the row’s own audit timestamp (CreatedAt / UpdatedAt), falling back to the database clock (SYSDATETIMEOFFSET() / now()) when the row carries none
Payload	the JSON image of the row this change produced

• Payload is the row after the change on insert and update, and the row that was removed on delete. One image, not a before/after pair: the previous state of a row is the payload of the change before it, so storing both doubled the widest column in the product to answer a question the ledger already answers by reading one row back.
• On update, only rows whose image actually changed are logged — the trigger still computes the before-image to compare against, it simply no longer stores it — so a no-op save writes nothing.

ChangedAt is an application clock, not a database one

The triggers prefer the row’s own CreatedAt/UpdatedAt, which the app stamps in C#, and fall back to the database clock only when the row carries none. So ChangedAt can skew between API instances, and an import that backdates CreatedAt writes an audit row dated in the past. Do not use it as a watermark — that is what the Id column is for.

• The row’s own trait timestamps (CreatedAt / UpdatedAt / DeletedAt) are normalized to UTC when saved through GenieContext, so audit images always carry a +00:00 offset regardless of the host’s time zone. The instant is unchanged — only the offset is rewritten. See Timezone handling → never DateTime.Now.

How it’s stored

Each change is written directly into [Audit].[AuditLog] by the per-entity trigger, inside the mutating transaction — no staging table, no background worker. AuditLog is:

• EF-mapped — created by the host’s migration like any other engine table, in a dedicated Audit schema alongside your data.
• Keyed on a monotonic identity Id. The table went without one for a while to avoid the insert-tail hotspot; it is back because it is what makes the ledger replicable at all. A database-assigned monotonic key is a watermark with no clock problems, and reading forward from one costs this table nothing — no pending flag, no trigger, no UPDATE. The alternative would have added a write per row to the highest-volume table in the deployment. Rows are only inserted (by the trigger) and read (LINQ/query), neither of which needs a key.
• UNLOGGED on PostgreSQL — the table skips WAL for cheaper writes. Trade-off: an UNLOGGED table is truncated on an unclean PostgreSQL shutdown, so you can lose recent audit rows on a crash. On SQL Server it’s an ordinary logged (durable) table.

Because capture is synchronous, an audit row exists if and only if the change committed — no eventual consistency, no worker to run, no config to tune.

Offloading to another store

The engine ships no drain/sink abstraction. To move audit rows to an external store (e.g. ClickHouse), maintain your own mover in the host project — read from [Audit].[AuditLog] on whatever schedule you like.

Production tuning

AuditLog is expected to be write-heavy, and its two read indexes are maintained per insert inside the user’s transaction. So tune it for your workload in production: cluster on (TableName, ChangedAt) and range-partition on ChangedAt (e.g. monthly) for fast queries via partition elimination and cheap retention (partition switch/drop). If write throughput matters more than read speed, drop the read indexes and add them only where you actually query.

The IsSyncable exclusion

The application-only IsSyncable replication flag (added by warehousing, or by the search feature) is stripped from both the logged JSON image and from change detection. That single exclusion is what lets the audit trigger coexist with the IsSyncable syncable trigger without conflict — a change that only flips IsSyncable produces no audit row, so there are no phantom entries and the two triggers never fight.

The framework-only RowVersion concurrency counter is excluded on the same basis, so a version bump never logs on its own.

Framework-internal writes are excluded

Some framework features complete a row after it’s inserted. The most common is an auto-generated <Sequence> column (e.g. a SKU): the sequence trigger fills the real value with an internal UPDATE once the row exists. That internal write is not a user change, so it’s excluded from audit capture (as well as from the concurrency version bump and the search re-index flag).

The sequence trigger is ordered to run first, and the change-logging trigger records each new row’s committed image read back from the table (a primary-key seek) — so the single I entry already carries the finalized value (SKU-01), not the raw token, and there is no I-followed-by-phantom-U. This also keeps bulk imports fast: without the exclusion, generating sequence values on a large batch produced a second full wave of audit work per row.

Note

Actor attribution relies on the row’s audit columns being stamped from the session, which the engine does on its normal write paths. A raw SQL write that bypasses those columns is still logged, but its ChangedById reflects whatever the row carried.