Part 8: Persistence and State - EF Core, Migrations, and Reliability

In the last part, we looked at how expressions make your workflows dynamic. Today, we are discussing the backbone of any production-ready system: Persistence. How do we keep your workflow data, execution history, and node states safe, even if the server crashes or restarts?

EF Core: Our Reliable Partner

Vyshyvanka uses Entity Framework Core (EF Core) as the primary abstraction for database interaction. It allows us to work with our domain entities in a type-safe, object-oriented way while maintaining the flexibility to target different database backends.

For development, we default to SQLite because it makes setting up local testing environments trivial — no external services, no Docker containers, just a file. For production, we support PostgreSQL, which gives us the robustness, scalability, and feature set required for high-volume automation tasks.

The switch between backends is handled at startup via configuration and .NET Aspire service wiring, so the same application code works seamlessly against both.

The Migration Workflow

One of the most important rules in our development culture is this: Never use EnsureCreatedAsync() in production.

While it is tempting for quick prototyping, relying on auto-generation for production schemas is a recipe for disaster. We strictly use EF Core Migrations. This approach provides a version-controlled history of your database schema, ensuring that deployments are predictable and that we can roll back if something goes wrong.

Our command for adding a migration is standardized:

dotnet ef migrations add <Name> \
  --project src/Vyshyvanka.Engine \
  --startup-project src/Vyshyvanka.Api \
  --output-dir Persistence/Migrations

At startup, the application automatically applies any pending migrations with MigrateAsync(). This ensures that every deployment brings the schema up to date without manual intervention.

Separation of Concerns

Our persistence layer follows a clean separation:

This ensures that the domain layer (Core) never depends on EF Core or any specific database technology. The engine provides the implementation details.

State Separation

We persist the workflow state (the graph structure — nodes, connections, configuration) and the execution state (the runtime status of every node during a run) separately. This separation allows us to perform high-frequency updates on the execution state without modifying the workflow definition itself.

When an execution transitions states, we use database transactions to ensure atomicity:

1. The node execution result is saved.
2. The overall execution status is updated.
3. Terminal states (Completed, Failed, Cancelled) are final — no further writes allowed.

This prevents zombie executions or partially processed data from corrupting your results.

Async All the Way Down

Every repository method follows our async-first pattern with CancellationToken:

public async Task<Workflow?> GetByIdAsync(Guid id, CancellationToken ct)
{
    return await _context.Workflows
        .Include(w => w.Nodes)
        .Include(w => w.Connections)
        .FirstOrDefaultAsync(w => w.Id == id, ct);
}

This ensures that database I/O never blocks threads, keeping the engine responsive even under heavy load.

Testing Our Persistence

Because we use EF Core, we can leverage lightweight in-memory providers to write unit tests for our data access logic without needing a real database:

[Fact]
public async Task WhenWorkflowSavedThenCanBeRetrieved()
{
    var options = new DbContextOptionsBuilder<VyshyvankaDbContext>()
        .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
        .Options;

    await using var context = new VyshyvankaDbContext(options);
    var repository = new WorkflowRepository(context);

    var workflow = CreateTestWorkflow();
    await repository.SaveAsync(workflow, CancellationToken.None);
    var retrieved = await repository.GetByIdAsync(workflow.Id, CancellationToken.None);

    retrieved.Should().NotBeNull();
    retrieved!.Nodes.Should().HaveCount(workflow.Nodes.Count);
}

For integration tests, we use WebApplicationFactory with a real SQLite database to test the full API-to-database path.

Credential Storage

Credentials get special treatment in our persistence layer. The CredentialEntity stores encrypted data — never plain text. The encryption key is managed externally (environment variables or a secrets manager). Even if someone gets access to the database, the credential values remain protected by AES-256 encryption.

Persistence is the quiet backbone of Vyshyvanka. By respecting migrations, separating concerns, and testing at every level, we ensure that your workflow data is always safe, consistent, and recoverable.

In the next part, we will discuss Part 9: Security First - Credentials, Authentication, and Secrets Management. Stay tuned!

Check out the project source code here: https://github.com/homolibere/Vyshyvanka