Elsa 3.3 Secret Management - SQL Server DataType does not support secret expiry greater than 24 hours

[OFurnell]

Description

I've been trying out the new secret management within Elsa but I've hit an issue that seems to be specific to the SQL Server implementation. By default, Entity Framework maps the TimeSpan type to time(7), but this type only supports 24 hours, so the options from Elsa Studio can't be

Steps to Reproduce

Create a new project and add the required dependencies for SQL Server secret persistence, for example:

dotnet add package Elsa
dotnet add package Elsa.EntityFrameworkCore
dotnet add package Elsa.EntityFrameworkCore.SqlServer
dotnet add package Elsa.Secrets.Api
dotnet add package Elsa.Secrets.Persistence.EntityFrameworkCore.SqlServer
dotnet add package Elsa.Workflows.Api
dotnet add package Elsa.Workflows.Core
dotnet add package Elsa.Workflows.Management

Enable the secrets management and API

using Elsa.EntityFrameworkCore.Extensions;
using Elsa.EntityFrameworkCore.Modules.Runtime;
using Elsa.Extensions;
using Elsa.Secrets.Extensions;
using Elsa.Secrets.Persistence;

var builder = WebApplication.CreateBuilder(args);

var connectionString = "Server=(localdb)\\MSSQLLocalDB;Initial Catalog=elsa-secrets;";

builder.Services.AddDataProtection();
builder.Services.AddElsa(elsa => 
    elsa.UseWorkflowsApi()
        .UseSecrets()
        .UseSecretsManagement(management => management.UseEntityFrameworkCore(a => a.UseSqlServer(connectionString)))
        .UseSecretsApi()
        .UseIdentity(identity =>
        {
            identity.TokenOptions = options => options.SigningKey = "sufficiently-large-secret-signing-key";
            identity.UseAdminUserProvider();
        })
        .UseDefaultAuthentication(auth => auth.UseAdminApiKey())
        .AddSwagger()
);

var app = builder.Build();
app.UseWorkflowsApi();
app.UseSwaggerUI();
app.Run();

Run the project and attempt to create a new secret, passing in an expiresIn of a day or greater

curl --location 'http://localhost:5000/elsa/api/secrets' \
--header 'Authorization: ApiKey 00000000-0000-0000-0000-000000000000' \
--header 'Content-Type: application/json' \
--data '{"name":"Example Expiry","scope":"General","value":"Example Value","description":"Test to demonstrate expiry","expiresIn":"7.00:00:00"}'

Expected Behavior

Secret is created within the [Elsa].[Secrets] table with [ExpiresIn] and [ExpiresAt] both populated

Actual Behavior

The operation fails with the error Value '1.00:00:00' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999

Log Output

info: Microsoft.AspNetCore.Routing.EndpointMiddleware[0]
      Executing endpoint 'HTTP: POST /elsa/api/secrets'
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (0ms) [Parameters=[@p0='?' (Size = 450), @p1='?' (DbType = DateTimeOffset), @p2='?' (Size = 4000), @p3='?' (Size = 4000), @p4='?' (DbType = DateTimeOffset), @p5='?' (DbType = Time), @p6='?' (DbType = Boolean), @p7='?' (DbType = DateTimeOffset), @p8='?' (Size = 450), @p9='?' (Size = 4000), @p10='?' (Size = 450), @p11='?' (Size = 4000), @p12='?' (DbType = Int32), @p13='?' (Size = 450), @p14='?' (DbType = DateTimeOffset), @p15='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      INSERT INTO [Elsa].[Secrets] ([Id], [CreatedAt], [Description], [EncryptedValue], [ExpiresAt], [ExpiresIn], [IsLatest], [LastAccessedAt], [Name], [Owner], [Scope], [SecretId], [Status], [TenantId], [UpdatedAt], [Version])
      VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15);
fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'Elsa.Secrets.Persistence.EntityFrameworkCore.SecretsDbContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
       ---> System.OverflowException: SqlDbType.Time overflow.  Value '1.00:00:00' is out of range.  Must be between 00:00:00.0000000 and 23:59:59.9999999.
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__211_0(Task`1 result)
         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
      --- End of stack trace from previous location ---
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
      --- End of stack trace from previous location ---
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         --- End of inner exception stack trace ---
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
       ---> System.OverflowException: SqlDbType.Time overflow.  Value '1.00:00:00' is out of range.  Must be between 00:00:00.0000000 and 23:59:59.9999999.
         at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__211_0(Task`1 result)
         at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
      --- End of stack trace from previous location ---
         at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
         at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
      --- End of stack trace from previous location ---
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         --- End of inner exception stack trace ---
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.SqlServer.Update.Internal.SqlServerModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)