= Advanced Application Development =

== Transactions ==

=== User Registration ===

{{{
public async Task<RegisterResponse> Handle(RegisterRequest request, CancellationToken cancellationToken)
{
    var existing = await _userRepository.GetByEmailAsync(request.Email, cancellationToken);
    if (existing != null)
        throw new InvalidOperationException("Email already in use.");

    await using var transaction = await _context.BeginTransactionAsync(cancellationToken);
    try
    {
        var user = new Domain.Entities.User
        {
            Username = request.Username,
            Email = request.Email,
            Name = request.Name,
            Surname = request.Surname,
            Password = BCrypt.Net.BCrypt.HashPassword(request.Password),
            CreatedAt = DateTime.UtcNow,
            UpdatedAt = DateTime.UtcNow
        };

        await _userRepository.AddAsync(user, cancellationToken);

        var writer = new Domain.Entities.Writer { Id = user.Id };
        await _writerRepository.AddAsync(writer, cancellationToken);

        await transaction.CommitAsync(cancellationToken);
        return new RegisterResponse(user.Id, user.Username, user.Email);
    }
    catch
    {
        await transaction.RollbackAsync(cancellationToken);
        throw;
    }
}
}}}

=== Publishing a Story ===

{{{
public async Task<AddResponse> Handle(AddRequest request, CancellationToken cancellationToken)
{
    await using var transaction = await _context.BeginTransactionAsync(cancellationToken);
    try
    {
        var story = new Domain.Entities.Story
        {
            MatureContent = request.MatureContent,
            ShortDescription = request.ShortDescription,
            Image = request.Image,
            Content = request.Content,
            UserId = request.UserId,
            CreatedAt = DateTime.UtcNow,
            UpdatedAt = DateTime.UtcNow
        };

        await _storyRepository.AddAsync(story, cancellationToken);

        foreach (var genreName in request.Genres ?? [])
        {
            var genre = await _genreRepository.GetByNameAsync(genreName, cancellationToken);
            if (genre == null) continue;
            await _hasGenreRepository.AddAsync(new Domain.Entities.HasGenre { StoryId = story.Id, GenreId = genre.Id }, cancellationToken);
        }

        await transaction.CommitAsync(cancellationToken);
        return new AddResponse(story.Id);
    }
    catch
    {
        await transaction.RollbackAsync(cancellationToken);
        throw;
    }
}
}}}

== Database Connection Pooling ==

Because we use .NET 9 with EF Core and Npgsql for the backend, we do not manage database connections manually. Connection pooling in Npgsql is handled automatically by the driver.

Npgsql is a transitive dependency of `Npgsql.EntityFrameworkCore.PostgreSQL`, so adding the following to the project is sufficient:

{{{
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="9.0.4" />
}}}

The EF Core context is registered using `AddDbContextPool` instead of `AddDbContext`, which enables context instance pooling on top of Npgsql's own connection pool:

{{{
services.AddDbContextPool<ApplicationDbContext>(options =>
    options.UseNpgsql(connectionString));
}}}

The pool parameters are read from `appsettings.json` at startup and appended to the Npgsql connection string:

{{{
"ConnectionPool": {
  "MinPoolSize": 1,
  "MaxPoolSize": 20,
  "ConnectionIdleLifetime": 300,
  "ConnectionPruningInterval": 10,
  "CommandTimeout": 30,
  "Timeout": 15
}
}}}

These values can be changed as needed. The established connections can be observed in the SSH tunnel logs when the application starts:

{{{
debug1: Connection to port 9999 forwarding to localhost port 5432 requested.
debug1: channel 2: new direct-tcpip [direct-tcpip] (inactive timeout: 0)
debug1: Connection to port 9999 forwarding to localhost port 5432 requested.
debug1: channel 3: new direct-tcpip [direct-tcpip] (inactive timeout: 0)
debug1: Connection to port 9999 forwarding to localhost port 5432 requested.
debug1: channel 4: new direct-tcpip [direct-tcpip] (inactive timeout: 0)
debug1: Connection to port 9999 forwarding to localhost port 5432 requested.
debug1: channel 5: new direct-tcpip [direct-tcpip] (inactive timeout: 0)
debug1: Connection to port 9999 forwarding to localhost port 5432 requested.
debug1: channel 6: new direct-tcpip [direct-tcpip] (inactive timeout: 0)
debug1: Connection to port 9999 forwarding to localhost port 5432 requested.
debug1: channel 7: new direct-tcpip [direct-tcpip] (inactive timeout: 0)
debug1: Connection to port 9999 forwarding to localhost port 5432 requested.
debug1: channel 8: new direct-tcpip [direct-tcpip] (inactive timeout: 0)
debug1: Connection to port 9999 forwarding to localhost port 5432 requested.
debug1: channel 9: new direct-tcpip [direct-tcpip] (inactive timeout: 0)
debug1: Connection to port 9999 forwarding to localhost port 5432 requested.
debug1: channel 10: new direct-tcpip [direct-tcpip] (inactive timeout: 0)
debug1: Connection to port 9999 forwarding to localhost port 5432 requested.
debug1: channel 11: new direct-tcpip [direct-tcpip] (inactive timeout: 0)
}}}