Trignis: Change Tracking That Finishes the Job

SQL Server has had native CHANGE_TRACKING since 2008. The hard part is the plumbing around it: persisting the last-seen version across restarts, doing a full initial sync before switching to incremental, batching large payloads, routing to multiple destinations, and handling delivery failures. Most implementations start as a scheduled job querying CHANGETABLE and posting to an endpoint. Then a restart loses the version and downstream systems get an unexpected full resync.

Trignis handles that plumbing. You supply the stored procedure; Trignis takes care of state persistence, batching, routing, and failure handling.

How it works

The stored procedure contract

Trignis communicates with SQL Server through a stored procedure you define. The only requirement is a consistent JSON output shape. The procedure receives the last-processed version and returns either a full dataset or a diff, plus metadata Trignis uses to advance its state.

-- Trignis calls this on every poll cycle
CREATE OR ALTER PROCEDURE web.get_itemssync
    @json NVARCHAR(MAX)
AS
BEGIN
    DECLARE @fromVersion INT = JSON_VALUE(@json, '$.fromVersion');
    DECLARE @curVer  INT = CHANGE_TRACKING_CURRENT_VERSION();
    DECLARE @minVer  INT = CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.Items'));

    -- Fall back to full sync if version is too old
    IF @fromVersion > 0 AND @fromVersion < @minVer SET @fromVersion = 0;

    IF @fromVersion = 0
        SELECT @curVer AS 'Metadata.Sync.Version', 'Full' AS 'Metadata.Sync.Type',
               JSON_QUERY((SELECT ItemCode, Description, Assortment FROM dbo.Items FOR JSON AUTO)) AS Data
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
    ELSE
        SELECT @curVer AS 'Metadata.Sync.Version', 'Diff' AS 'Metadata.Sync.Type',
               JSON_QUERY((
                   SELECT ct.SYS_CHANGE_OPERATION AS '$operation', ct.ItemCode, i.Description, i.Assortment
                   FROM dbo.Items AS i
                   RIGHT JOIN CHANGETABLE(CHANGES dbo.Items, @fromVersion) AS ct ON ct.ItemCode = i.ItemCode
                   FOR JSON PATH
               )) AS Data
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
END

Trignis stores that version number in its local SQLite state database after each successful delivery. On the next poll, it passes the stored version back in. The procedure decides whether a diff is valid or whether the version has fallen outside the retention window and a full resync is needed. That fallback logic lives in SQL, not in the service.

Full vs. Diff

Where changes go

Each environment configuration lists one or more endpoints, and Trignis delivers to all of them on each poll cycle.

Authentication is configured per endpoint; supported types are Bearer, Basic, ApiKey, and OAuth2ClientCredentials. Trignis handles token acquisition and refresh for OAuth2 automatically. Connection strings and tokens are encrypted at rest on first startup.

{
  "Key": "production_webhook",
  "Url": "https://api.company.com/webhooks/changes",
  "Auth": { "Type": "Bearer", "Token": "<token>" },
  "CustomHeaders": {
    "X-Correlation-Id": "{guid}",
    "X-Source": "trignis-{database}",
    "X-Environment": "{environment}"
  },
  "EnableCompression": true
}

State and failure handling

Trignis persists the last successfully processed version in a local SQLite database, so restarts resume from where processing stopped. If the version has expired, the procedure returns a full sync and Trignis resets its state. Each environment runs on its own thread, so a failing destination in one environment does not block others.

Failed deliveries go to a dead letter store (sinkhole.db). Trignis retries them on a widening backoff, five times by default, then holds them for manual replay or discard from the dashboard. Dead letters are also queryable via the health endpoint.

# Service health and database connectivity
GET http://localhost:2455/health

# Tracking versions per environment
GET http://localhost:2455/health/state/production

# Dead letter count and details
GET http://localhost:2455/health/deadletters