v3.6

Process Tracking & COMPARE

Tag operations with business process IDs, trace them later, and compare table state across time.

Back to specs

Why process tracking matters

Business operations rarely consist of a single SQL statement. An order might involve inserts into Orders, OrderLines, and Payments, plus updates to Inventory. Without tracking, these related operations are invisible once committed. The IDENTIFIER clause lets you tag all related DML with a single process ID — like 'order-batch-42' — so you can trace the full lifecycle of a business process through the database.

IDENTIFIER and METADATA clauses

Append IDENTIFIER to any INSERT, UPDATE, or DELETE to create an entry in System.ProcessLog. Optionally add METADATA with JSON context. The clause order is fixed: BY → IDENTIFIER → METADATA.

-- Tag an insert with a business process ID
INSERT INTO Orders (Id, Customer, Status)
VALUES (1, 'Acme Corp', 'New')
IDENTIFIER 'order-batch-42'

-- Combine user attribution, process ID, and metadata
UPDATE Orders SET Status = 'Shipped'
WHERE Id = 1
BY 'admin'
IDENTIFIER 'order-batch-42'
METADATA '{"source": "api", "priority": "high"}'

TRACE command

Query the process log to see every INSERT, UPDATE, and DELETE associated with a business process. Optionally scope to a specific table.

-- All operations for a process
TRACE 'order-batch-42'

-- Scoped to a single table
TRACE TABLE Orders PROCESS 'order-batch-42'

Returns columns: CreatedAtUtc, TableName, Operation, RowsAffected, ChangedBy, Metadata, RequestId, TransactionId.

COMPARE TABLE

Compare the state of a temporal table between two points in time. The result shows which rows were ADDED, DELETED, or MODIFIED. Requires the table to be created with WITH HISTORY.

-- Compare between two timestamps
COMPARE TABLE Orders AT '2025-01-01T00:00:00' WITH '2025-06-01T00:00:00'

-- Compare a past snapshot with the current state
COMPARE TABLE Orders AT '2025-01-01T00:00:00' WITH CURRENT

Returns a DiffType column (ADDED, DELETED, MODIFIED) followed by all table columns. MODIFIED rows show the newer values. Useful for auditing, migration validation, and understanding data drift over time.

System.ProcessLog

The 8th system table with 10 columns, automatically populated when DML includes an IDENTIFIER clause:

Id — auto-incremented primary key
ProcessId — the business process identifier
RequestId — from REQUEST clause, if present
TransactionId — engine transaction ID
TableName — target table of the DML
Operation — INSERT, UPDATE, or DELETE
RowsAffected — number of rows changed
Metadata — optional JSON from METADATA clause
ChangedBy — user from BY clause
CreatedAtUtc — timestamp of the operation

API access

ADO.NET (Kamiina.Data):

// Per-command process ID
cmd.ProcessId = "order-batch-42";
cmd.ProcessMetadata = "{\"source\": \"api\"}";

// Connection-level (applies to all commands)
connection.SetProcessId("order-batch-42");
connection.ClearProcessId();

// Query the process log
connection.GetProcessLog();
connection.GetProcessLog("order-batch-42");

EF Core (Kamiina.EntityFrameworkCore):

context.SetProcessId("order-batch-42");
context.SaveChanges(); // tagged automatically

context.GetProcessLog("order-batch-42");
context.ClearProcessId();

Use cases

Order processing: Trace every database operation for an order across multiple tables.
Batch imports: Tag bulk inserts with a batch ID, then verify the complete import via TRACE.
Regulatory compliance: Demonstrate which operations belong to which business process.
Migration verification: Use COMPARE to validate data before and after a migration.
Production debugging: Narrow down which process caused unexpected data changes.

Schema Documentation All specifications