# ApiService PostgreSQL Test Stability - Implementation Tasks

## Overview

Investigation and remediation plan for unstable execution of `Kds.Ai.ApiService.Tests` caused by PostgreSQL connection pool exhaustion during per-test schema bootstrap.

This task plan is derived from analysis of the current test infrastructure in:

- `backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Infrastructure/PostgresDatabase.cs`
- `backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Infrastructure/TestsBase.cs`
- `backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Infrastructure/AppFactory.cs`

It is also grounded in the current TUnit guidance for ASP.NET integration tests and parallel execution:

- `https://tunit.dev/docs/intro`
- `https://tunit.dev/docs/guides/performance`

It is also derived from repeated local executions of:

- `dotnet test --project "backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests.csproj"`

Observed failure signature:

- `Npgsql.NpgsqlException: The connection pool has been exhausted, either raise 'Max Pool Size' (currently 20) or 'Timeout' (currently 15 seconds) in your connection string.`
- failure location: `Kds.Ai.ApiService.Tests.Infrastructure.TestsBase.InitializeAsync(...)`
- failure phase: `Before(Test)` hook, before individual test logic runs

The current test harness uses one shared PostgreSQL testcontainer per test session, creates a fresh schema per test, and performs schema creation plus seed data setup in parallel across many tests. The shared connection string explicitly caps `MaxPoolSize` at `20`, which is too small for the current concurrency level and bootstrap pattern.

## Source Documents

- repository analysis performed during this session
- failing local test output from repeated `Kds.Ai.ApiService.Tests` runs
- current TUnit integration test harness for API service

## Spec Name

The canonical implementation spec should be referred to as **ApiService PostgreSQL Test Stability**.

## Fixed Decisions

- This is a test infrastructure problem, not an application runtime bug.
- The goal is stable repeated execution of the full `Kds.Ai.ApiService.Tests` project.
- The fix must target the test harness and its PostgreSQL usage pattern, not production connection settings.
- The final state must support repeated green runs of the full project without relying on lucky scheduling.
- The solution should prefer deterministic test infrastructure changes over simply retrying failed runs.

## Scope

This task includes:

- documenting the exact failure mode and reproduction path,
- identifying why per-test schema bootstrap exhausts the shared Npgsql pool,
- reviewing TUnit parallelism and the current per-test setup pattern,
- choosing and implementing a stability fix in the API test harness,
- validating the fix by re-running the full API test project multiple times.

In short, this slice ends when `Kds.Ai.ApiService.Tests` can run reliably as a full suite without intermittent `Npgsql` pool exhaustion during `Before(Test)` initialization.

## Out of Scope

The following items are explicitly excluded from this task:

- application feature changes,
- warehouse or Redshift runtime behavior,
- changes to production PostgreSQL connection strings,
- unrelated test projects unless they need the same harness correction,
- masking the issue by documenting retries as the expected workflow.

---

## Problem Statement

### What Fails

The full API integration test project is unstable:

- some runs succeed completely,
- other runs fail before test bodies execute,
- failures happen in `TestsBase.InitializeAsync(...)` while opening PostgreSQL connections.

### Current Harness Behavior

- `PostgresDatabase` provisions one shared PostgreSQL container per test session.
- `PostgresDatabase.ConnectionString` forces `MaxPoolSize = 20`.
- every test gets its own schema via `TestsBase.InitializeAsync(...)`.
- `InitializeAsync(...)` opens a raw `NpgsqlConnection`, creates a schema, creates EF tables, and seeds data.
- `DisposeAsync()` later opens another raw connection and drops the schema.
- TUnit runs tests in parallel by default.

### Why It Is Unstable

The current design combines:

- aggressive per-test setup,
- shared container and shared connection pool,
- explicit low pool limit,
- parallel test execution.

That means many tests compete for the same `Npgsql` pool at the exact same phase, and once enough tests are initializing simultaneously, connection acquisition starts timing out.

### Observable Evidence

- repeated failures show the same exception family and same hook location,
- failing tests vary between runs,
- the failures are distributed across unrelated endpoint test classes,
- stack traces point to pool rent/open rather than SQL command failures,
- some full runs have succeeded earlier in the same workspace, which confirms flakiness rather than deterministic functional failure.

---

## Reproduction

Primary reproduction command:

```bash
dotnet test --project "backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests.csproj"
```

Typical failing message:

```text
Npgsql.NpgsqlException: The connection pool has been exhausted, either raise 'Max Pool Size' (currently 20) or 'Timeout' (currently 15 seconds) in your connection string.
```

Typical stack anchor:

```text
Kds.Ai.ApiService.Tests.Infrastructure.TestsBase.InitializeAsync(CancellationToken cancellationToken)
```

Latest validation run in this workspace:

- command: `dotnet test --project "backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests.csproj"`
- result: `92` total, `52` passed, `40` failed, duration about `49s`
- all observed failures still originate from `Before(Test)` in `TestsBase.InitializeAsync(...)`

---

## Technical Analysis

### Shared Pool Bottleneck

`PostgresDatabase.ConnectionString` returns the container connection string with:

```csharp
var builder = new NpgsqlConnectionStringBuilder(Container.GetConnectionString())
{
    MaxPoolSize = 20,
};
```

This hard cap is low relative to:

- 92 tests in the project,
- parallel `Before(Test)` execution,
- additional connections opened by EF `DbContext` and WebApplicationFactory startup.

### Hot Path In Every Test

`TestsBase.InitializeAsync(...)` does all of the following per test:

1. open raw Npgsql connection,
2. create schema,
3. construct EF `KdsAiDbContext`,
4. create all tables,
5. seed base rows,
6. initialize app factory lazily for later test use.

The teardown path opens another raw connection to drop the schema.

### Failure Characteristics

Because the exception happens in `Before(Test)`, the impacted tests are not semantically broken. They are simply unable to acquire a connection in time.

This is why failing test names vary from run to run and do not cluster around one business area.

### TUnit-Specific Interpretation

According to the TUnit performance guidance, parallel execution should be explicitly limited when tests share constrained infrastructure such as a database. The current test project does not define any `ParallelLimiter`, so the engine is free to schedule more bootstrap work than the shared PostgreSQL container and connection pool can sustain.

The same guidance also matters for choosing the correct primitive:

- `ParallelLimiter` is the right tool for capping concurrent integration tests against a shared database,
- `ParallelGroup` is not sufficient because tests inside a group still run concurrently,
- `NotInParallel` would over-correct by serializing too much of the suite.

That makes this primarily a missing concurrency-governor problem, amplified by an artificially low `MaxPoolSize`.

---

## Recommended Solution

### Decision

The recommended implementation direction is **Option D: combine reduced TUnit parallelism with removal of the test-only low pool cap**.

This should be treated as the primary fix for the API integration harness.

### Why This Is The Best Fit

- It directly addresses the root cause: too many concurrent `Before(Test)` database bootstraps.
- It follows the TUnit performance guidance for shared database resources.
- It keeps per-test schema isolation intact.
- It avoids the worst tradeoff of fully serial execution.
- It avoids relying on a bigger pool alone, which would only move the failure threshold.

### Concrete Changes To Make

1. Add a TUnit `ParallelLimiter` for `Kds.Ai.ApiService.Tests`.

Recommended shape:

```csharp
using TUnit.Core;
using TUnit.Core.Interfaces;

[assembly: ParallelLimiter<ApiServicePostgresParallelLimit>]

public sealed class ApiServicePostgresParallelLimit : IParallelLimit
{
    public int Limit => 6;
}
```

Rationale for starting at `6`:

- each test performs schema bootstrap in `Before(Test)`,
- each bootstrap can consume more than one connection when raw `NpgsqlConnection`, EF Core table creation, and app startup overlap,
- `6` is low enough to stop thundering-herd pressure but still preserves useful parallelism.

This value should be treated as the starting point, not a magic constant. If one validation pass still shows pressure, reduce to `4` or `5` before considering any larger redesign.

2. Remove the explicit `MaxPoolSize = 20` override from `Infrastructure/PostgresDatabase.cs`.

Recommended change:

- stop forcing a test-only pool of `20`,
- use the provider default pool settings from `Container.GetConnectionString()` unless a later measurement shows a clear reason to tune them.

Rationale:

- the current cap is much lower than the effective workload generated by the suite,
- it is not representative of the intended integration-test capacity,
- keeping the hard cap while only adding a limiter leaves unnecessarily little headroom for EF Core and host startup.

3. Keep the existing per-test schema model.

Do not replace schema isolation as part of the immediate fix. The schema-per-test pattern is not the core bug; the unbounded concurrency against that pattern is.

4. Do not use `[NotInParallel]` as the main project-wide fix.

That would likely stabilize the suite, but at a disproportionate runtime cost. TUnit's limiter is the more appropriate control.

### Optional Hardening After Stabilization

These are secondary improvements, not the primary fix:

- move `TestsBase` toward TUnit's `WebApplicationTest` lifecycle model (`SetupAsync`, `ConfigureTestConfiguration`) so per-test setup is expressed in the framework's intended extension points,
- reduce bootstrap cost by consolidating repeated seeding logic,
- if runtime is still too high, consider a more advanced pattern such as pre-created template state or transaction-based isolation for tests that do not need schema-level independence.

---

## Task Checklist

### Phase 1: Diagnosis Lock-In

- [x] Confirm the minimal, reproducible failure mode for full `Kds.Ai.ApiService.Tests`
- [x] Capture the exact concurrency and connection-pool bottleneck in the current harness
- [x] Decide whether the primary fix is pool-size increase, reduced parallelism, bootstrap redesign, or a combination

### Phase 2: Harness Stabilization

- [ ] Update `PostgresDatabase` / `TestsBase` infrastructure to remove the pool exhaustion failure mode
- [ ] Ensure the chosen fix is deterministic and does not rely on manual retries
- [ ] Keep test isolation guarantees intact while improving stability

### Phase 3: Verification

- [ ] Run the full `Kds.Ai.ApiService.Tests` project repeatedly
- [ ] Verify that the suite becomes stable across repeated runs
- [ ] Document the final reasoning and any remaining tradeoffs

---

## Candidate Fix Directions

### Option A: Increase Pool Size

Raise `MaxPoolSize` in test-only infrastructure.

Pros:

- smallest code change,
- likely addresses immediate bottleneck.

Cons:

- may only move the threshold,
- does not reduce bootstrap pressure.

### Option B: Reduce Test Parallelism For This Project Or Base Class

Constrain concurrency for tests using shared per-test schema bootstrap.

Pros:

- directly addresses contention,
- deterministic.

Cons:

- increases execution time.

### Option C: Reduce Connection Churn During Setup

Refactor initialization so fewer connections are opened during `Before(Test)` and `After(Test)`.

Pros:

- better long-term fix,
- lowers infrastructure pressure.

Cons:

- more intrusive change.

### Option D: Combine A + B Or A + C

Most likely final direction if one isolated change is insufficient.

### Recommended Selection

Choose **Option D**, specifically:

- use **B** as the controlling fix by adding a TUnit `ParallelLimiter`,
- combine it with a small **A** correction by removing the artificial `MaxPoolSize = 20` bottleneck,
- keep **C** as a follow-up optimization only if stabilization succeeds but execution time remains unattractive.

This gives the suite a deterministic concurrency ceiling and removes the most obvious self-inflicted pool restriction without forcing a larger harness rewrite in the same change.

---

## Success Criteria

- `dotnet test --project "backend/tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests/Kds.Ai.ApiService.Tests.csproj"` completes green repeatedly
- no `Npgsql` pool exhaustion errors occur in `TestsBase.InitializeAsync(...)`
- test isolation by schema remains intact
- the solution is documented as a test harness stabilization, not a production setting change