Pinned Data Storage

Table Schema

-- Process_NodePinnedData
CREATE TABLE Process_NodePinnedData (
    Id          BIGINT          IDENTITY PRIMARY KEY,
    TenantId    NVARCHAR(64)    NOT NULL,
    ProcessId   INT             NOT NULL,
    NodeId      NVARCHAR(256)   NOT NULL,
    DataJson    NVARCHAR(MAX)   NOT NULL,   -- JSON-serialized pinnedData object
    CreatedAt   DATETIMEOFFSET  NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt   DATETIMEOFFSET  NOT NULL DEFAULT SYSUTCDATETIME(),
    ExpiresAt   DATETIMEOFFSET  NULL,       -- NULL = never expires
    CONSTRAINT UQ_NodePinnedData UNIQUE (TenantId, ProcessId, NodeId)
);

PinnedDataService — Save and Upsert

// PinnedDataService.cs
public async Task SaveAsync(
    int processId,
    string nodeId,
    object data,
    CancellationToken ct = default)
{
    var json     = JsonSerializer.Serialize(data);
    var tenantId = _tenantContext.TenantId;

    // Upsert — INSERT if first time, UPDATE if already exists
    await _db.ExecuteAsync(@"
        MERGE Process_NodePinnedData AS target
        USING (SELECT @TenantId, @ProcessId, @NodeId) AS source (TenantId, ProcessId, NodeId)
        ON target.TenantId = source.TenantId
            AND target.ProcessId = source.ProcessId
            AND target.NodeId    = source.NodeId
        WHEN MATCHED THEN
            UPDATE SET DataJson = @Json, UpdatedAt = SYSUTCDATETIME()
        WHEN NOT MATCHED THEN
            INSERT (TenantId, ProcessId, NodeId, DataJson, CreatedAt, UpdatedAt)
            VALUES (@TenantId, @ProcessId, @NodeId, @Json, SYSUTCDATETIME(), SYSUTCDATETIME());",
        new { TenantId = tenantId, ProcessId = processId, NodeId = nodeId, Json = json },
        cancellationToken: ct);
}

Key Semantics

Read Path

// PinnedDataService.cs
public async Task<object?> GetAsync(
    int processId,
    string nodeId,
    CancellationToken ct = default)
{
    var tenantId = _tenantContext.TenantId;

    var json = await _db.QuerySingleOrDefaultAsync<string>(@"
        SELECT DataJson
        FROM   Process_NodePinnedData
        WHERE  TenantId  = @TenantId
          AND  ProcessId = @ProcessId
          AND  NodeId    = @NodeId
          AND  (ExpiresAt IS NULL OR ExpiresAt > SYSUTCDATETIME())",
        new { TenantId = tenantId, ProcessId = processId, NodeId = nodeId },
        cancellationToken: ct);

    return json is null ? null : JsonSerializer.Deserialize<object>(json);
}