Versioning with History Tables
Some applications require preserving previous versions of data when updates or changes occur. Using Tailor DB CDC, you can capture and log data modifications in real-time, storing prior record versions in a dedicated history table. This approach preserves historical data and enables change monitoring and analysis over time.
How to enable Data Versioning
- Create a history table
- Enable
PublishRecordEvents
settings - Add an event based trigger to
executor.cue
manifest
Example
1. Create a history table
Let's create a history table for the StockSummary
type to log its data changes.
<span><span style="color: var(--shiki-token-keyword)">package</span><span style="color: var(--shiki-color-text)"> master</span></span>
<span></span>
<span><span style="color: var(--shiki-token-keyword)">import</span><span style="color: var(--shiki-color-text)"> (</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">github.com/tailor-platform/tailorctl/schema/v2/tailordb</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">tailor.build/template/services/tailordb</span><span style="color: var(--shiki-color-text)">:permissions</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">tailor.build/template/services/tailordb</span><span style="color: var(--shiki-color-text)">:commonType</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)">)</span></span>
<span></span>
<span><span style="color: var(--shiki-color-text)">StockSummaryHistory: commonType.#CommonType </span><span style="color: var(--shiki-token-keyword)">&</span><span style="color: var(--shiki-color-text)"> {</span></span>
<span><span style="color: var(--shiki-color-text)"> Name: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">StockSummaryHistory</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Description: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">StockSummaryHistory model</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Fields: {</span></span>
<span><span style="color: var(--shiki-color-text)"> variantID: {</span></span>
<span><span style="color: var(--shiki-color-text)"> Type: tailordb.#TypeUUID</span></span>
<span><span style="color: var(--shiki-color-text)"> Description: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">Variant ID</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Required: </span><span style="color: var(--shiki-token-constant)">true</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> variant: {</span></span>
<span><span style="color: var(--shiki-color-text)"> Type: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">ProductVariant</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Description: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">Variant</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> SourceId: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">variantID</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> onHoldQuantity: {</span></span>
<span><span style="color: var(--shiki-color-text)"> Type: tailordb.#TypeFloat</span></span>
<span><span style="color: var(--shiki-color-text)"> Description: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">onHoldQuantity</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Required: </span><span style="color: var(--shiki-token-constant)">true</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> availableQuantity: {</span></span>
<span><span style="color: var(--shiki-color-text)"> Type: tailordb.#TypeFloat</span></span>
<span><span style="color: var(--shiki-color-text)"> Description: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">availableQuantity</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Required: </span><span style="color: var(--shiki-token-constant)">true</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> inStockQuantity: {</span></span>
<span><span style="color: var(--shiki-color-text)"> Type: tailordb.#TypeFloat</span></span>
<span><span style="color: var(--shiki-color-text)"> Description: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">DO NOT UPDATE FROM THE FRONT END. The quantity of the product in stock.</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Hooks: {</span></span>
<span><span style="color: var(--shiki-color-text)"> CreateExpr: </span><span style="color: var(--shiki-color-text)">"""</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> decimal(_value.onHoldQuantity) + decimal(_value.availableQuantity)</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> </span><span style="color: var(--shiki-color-text)">"""</span></span>
<span><span style="color: var(--shiki-color-text)"> UpdateExpr: CreateExpr</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> totalCost: {</span></span>
<span><span style="color: var(--shiki-color-text)"> Type: tailordb.#TypeFloat</span></span>
<span><span style="color: var(--shiki-color-text)"> Description: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">totalCost</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Required: </span><span style="color: var(--shiki-token-constant)">true</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> averageCost: {</span></span>
<span><span style="color: var(--shiki-color-text)"> Type: tailordb.#TypeFloat</span></span>
<span><span style="color: var(--shiki-color-text)"> Description: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">averageCost</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Hooks: {</span></span>
<span><span style="color: var(--shiki-color-text)"> CreateExpr: </span><span style="color: var(--shiki-color-text)">"""</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> (decimal(_value.onHoldQuantity) + decimal(_value.availableQuantity)) != decimal(0.0) ? </span></span>
<span><span style="color: var(--shiki-token-string-expression)"> decimal(_value.totalCost) / (decimal(_value.onHoldQuantity) + decimal(_value.availableQuantity)) : </span></span>
<span><span style="color: var(--shiki-token-string-expression)"> decimal(0.0)</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> </span><span style="color: var(--shiki-color-text)">"""</span></span>
<span><span style="color: var(--shiki-color-text)"> UpdateExpr: CreateExpr</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> TypePermission: permissions.adminAccess</span></span>
<span><span style="color: var(--shiki-color-text)">}</span></span>
<span></span>
2. Enable PublishRecordEvents
settings
By enabling PublishRecordEvents
in the StockSummary
settings, you can create an event-based trigger that executes on every StockSummary
record update.
<span><span style="color: var(--shiki-token-keyword)">package</span><span style="color: var(--shiki-color-text)"> transaction</span></span>
<span></span>
<span><span style="color: var(--shiki-token-keyword)">import</span><span style="color: var(--shiki-color-text)"> (</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">github.com/tailor-platform/tailorctl/schema/v2/tailordb</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">tailor.build/template/services/tailordb</span><span style="color: var(--shiki-color-text)">:permissions</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)">)</span></span>
<span></span>
<span><span style="color: var(--shiki-color-text)">StockSummary: tailordb.#Type </span><span style="color: var(--shiki-token-keyword)">&</span><span style="color: var(--shiki-color-text)"> {</span></span>
<span><span style="color: var(--shiki-color-text)"> Name: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">StockSummary</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Description: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">StockSummary model.</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Settings: {</span></span>
<span><span style="color: var(--shiki-color-text)"> PublishRecordEvents: </span><span style="color: var(--shiki-token-constant)">true</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> Fields: {</span></span>
<span><span style="color: var(--shiki-color-text)"> ...</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span></span>
<span><span style="color: var(--shiki-color-text)"> TypePermission: permissions.adminAccess</span></span>
<span><span style="color: var(--shiki-color-text)">}</span></span>
<span></span>
3. Add an event based trigger to executor.cue
manifest
The stockSummaryHistoryWithEvent
trigger executes on each StockSummary
record update.
It logs the old record by creating an entry in the history table.
<span><span style="color: var(--shiki-token-keyword)">package</span><span style="color: var(--shiki-color-text)"> executor</span></span>
<span></span>
<span><span style="color: var(--shiki-token-keyword)">import</span><span style="color: var(--shiki-color-text)"> (</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">github.com/tailor-platform/tailorctl/schema/v2/executor</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">github.com/tailor-platform/tailorctl/schema/v2/common</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">tailor.build/template/services/pipeline</span><span style="color: var(--shiki-color-text)">:settings</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">tailor.build/template/environment</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)">)</span></span>
<span></span>
<span><span style="color: var(--shiki-color-text)">executor.#Spec </span><span style="color: var(--shiki-token-keyword)">&</span><span style="color: var(--shiki-color-text)"> {</span></span>
<span><span style="color: var(--shiki-color-text)"> Executors: [</span></span>
<span><span style="color: var(--shiki-color-text)"> ...</span></span>
<span><span style="color: var(--shiki-color-text)"> #stockSummaryHistoryWithEvent</span></span>
<span><span style="color: var(--shiki-color-text)"> ]</span></span>
<span><span style="color: var(--shiki-color-text)">}</span></span>
<span></span>
<span><span style="color: var(--shiki-color-text)">#stockSummaryHistoryWithEvent: executor.#Executor </span><span style="color: var(--shiki-token-keyword)">&</span><span style="color: var(--shiki-color-text)"> {</span></span>
<span><span style="color: var(--shiki-color-text)"> Name: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">eventbased-executor-stocksummary</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Description: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">Create a data based on the event</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Trigger: executor.#TriggerEvent </span><span style="color: var(--shiki-token-keyword)">&</span><span style="color: var(--shiki-color-text)"> {</span></span>
<span><span style="color: var(--shiki-color-text)"> EventType: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">tailordb.type_record.updated</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Condition: common.#Script </span><span style="color: var(--shiki-token-keyword)">&</span><span style="color: var(--shiki-color-text)"> {</span></span>
<span><span style="color: var(--shiki-color-text)"> Expr: </span><span style="color: var(--shiki-color-text)">"""</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> args.namespaceName == "my-tailordb" && args.typeName == "StockSummary"</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> </span><span style="color: var(--shiki-color-text)">"""</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> Target: executor.#TargetTailorGraphql </span><span style="color: var(--shiki-token-keyword)">&</span><span style="color: var(--shiki-color-text)"> {</span></span>
<span><span style="color: var(--shiki-color-text)"> AppName: </span><span style="color: var(--shiki-color-text)">"</span><span style="color: var(--shiki-token-string-expression)">my-app</span><span style="color: var(--shiki-color-text)">"</span></span>
<span><span style="color: var(--shiki-color-text)"> Query: </span><span style="color: var(--shiki-color-text)">"""</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> mutation ($input: StockSummaryHistoryCreateInput!) {</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> createStockSummaryHistory(input: $input) {</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> id</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> }</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> }</span><span style="color: var(--shiki-color-text)">"""</span></span>
<span><span style="color: var(--shiki-color-text)"> Variables: common.#Script </span><span style="color: var(--shiki-token-keyword)">&</span><span style="color: var(--shiki-color-text)"> {</span></span>
<span><span style="color: var(--shiki-color-text)"> Expr: </span><span style="color: var(--shiki-color-text)">"""</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> ({</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> "input": {</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> "variantID": args.oldRecord.variantID,</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> "onHoldQuantity": args.oldRecord.onHoldQuantity,</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> "availableQuantity": args.oldRecord.availableQuantity,</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> "totalCost": args.oldRecord.totalCost</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> }</span></span>
<span><span style="color: var(--shiki-token-string-expression)"> })</span><span style="color: var(--shiki-color-text)">"""</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)">}</span></span>
<span></span>
Refer to the Event based trigger tutorial for detailed steps on setting up an event-based trigger. Once the trigger is configured, proceed with the following steps to confirm it is working correctly.
- Create a record in
StockSummary
table.
<span><span style="color: var(--shiki-token-keyword)">mutation</span><span style="color: var(--shiki-color-text)">{</span></span>
<span><span style="color: var(--shiki-color-text)"> createStockSummary(input: { </span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-token-string)">variantID</span><span style="color: var(--shiki-color-text)">: </span><span style="color: var(--shiki-token-string-expression)">"fc9349f5-2398-5aa6-a6a7-478c8db009ec"</span><span style="color: var(--shiki-color-text)">, </span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-token-string)">onHoldQuantity</span><span style="color: var(--shiki-color-text)">: </span><span style="color: var(--shiki-token-constant)">20</span><span style="color: var(--shiki-color-text)">,</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-token-string)">availableQuantity</span><span style="color: var(--shiki-color-text)">: </span><span style="color: var(--shiki-token-constant)">20</span><span style="color: var(--shiki-color-text)">,</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-token-string)">totalCost</span><span style="color: var(--shiki-color-text)">:</span><span style="color: var(--shiki-token-constant)">800</span><span style="color: var(--shiki-color-text)">,</span></span>
<span><span style="color: var(--shiki-color-text)"> }) {</span></span>
<span><span style="color: var(--shiki-color-text)"> id</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)">}</span></span>
<span></span>
- Update the record in
StockSummary
table.
<span><span style="color: var(--shiki-token-keyword)">mutation</span><span style="color: var(--shiki-color-text)">{</span></span>
<span><span style="color: var(--shiki-color-text)"> updateStockSummary(id:</span><span style="color: var(--shiki-token-comment)">"f701637c-12b1-4dab-ab45-3e952d8ff0b6"</span><span style="color: var(--shiki-color-text)"> input: { </span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-token-string)">availableQuantity</span><span style="color: var(--shiki-color-text)">: </span><span style="color: var(--shiki-token-constant)">60</span><span style="color: var(--shiki-color-text)">,</span></span>
<span><span style="color: var(--shiki-color-text)"> }) {</span></span>
<span><span style="color: var(--shiki-color-text)"> id</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)">}</span></span>
<span></span>
Execute the following query to view the update history of the record in the history table.
<span><span style="color: var(--shiki-color-text)">{</span></span>
<span><span style="color: var(--shiki-color-text)"> stockSummaryHistories(query: { </span><span style="color: var(--shiki-token-string)">variantID</span><span style="color: var(--shiki-color-text)">: { </span><span style="color: var(--shiki-token-string)">eq</span><span style="color: var(--shiki-color-text)">: </span><span style="color: var(--shiki-token-string-expression)">"fc9349f5-2398-5aa6-a6a7-478c8db009ec"</span><span style="color: var(--shiki-color-text)"> } }) {</span></span>
<span><span style="color: var(--shiki-color-text)"> collection {</span></span>
<span><span style="color: var(--shiki-color-text)"> id</span></span>
<span><span style="color: var(--shiki-color-text)"> variantID</span></span>
<span><span style="color: var(--shiki-color-text)"> availableQuantity</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)">}</span></span>
<span></span>
<span><span style="color: var(--shiki-color-text)">{</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-token-comment)">"data"</span><span style="color: var(--shiki-color-text)">: {</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-token-comment)">"stockSummaryHistories"</span><span style="color: var(--shiki-color-text)">: {</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-token-comment)">"collection"</span><span style="color: var(--shiki-color-text)">: [</span></span>
<span><span style="color: var(--shiki-color-text)"> {</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-token-comment)">"id"</span><span style="color: var(--shiki-color-text)">: </span><span style="color: var(--shiki-token-comment)">"323c9cff-a75f-45a5-aea5-33a367b4e0ce"</span><span style="color: var(--shiki-color-text)">,</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-token-comment)">"variantID"</span><span style="color: var(--shiki-color-text)">: </span><span style="color: var(--shiki-token-comment)">"fc9349f5-2398-5aa6-a6a7-478c8db009ec"</span><span style="color: var(--shiki-color-text)">,</span></span>
<span><span style="color: var(--shiki-color-text)"> </span><span style="color: var(--shiki-token-comment)">"availableQuantity"</span><span style="color: var(--shiki-color-text)">: 20</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> ]</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)"> }</span></span>
<span><span style="color: var(--shiki-color-text)">}</span></span>
<span></span>