fix(db): Audit and prevent unmanaged implicit connection creation in DbConnectionFactory/DotConnect

Summary

DotConnect.executeQuery() (no-arg) silently creates a new thread-local connection via DbConnectionFactory.getConnection() when none exists, with no knowledge of who will close it. This is the root mechanism behind multiple connection leak bugs (#34926, #34831, #34920, #34489). Rather than continuing to chase individual leak instances, we should address the systemic problem: unmanaged implicit connection creation should be detectable, and ideally preventable.

The Problem

How connections are supposed to work

dotCMS uses a thread-local connection model with three layers of lifecycle management:

1. Annotations (@CloseDBIfOpened, @WrapInTransaction) — CDI/ByteBuddy interceptors that open a connection, run the method, and close the connection in a finally block. These use the connectionExists() check to avoid closing a connection they didn't create.

2. DbConnectionFactory.wrapConnection() — Programmatic equivalent of the annotations. Records whether a connection already existed, runs the delegate, and closes only if it created one.

3. CMSFilter — HTTP request filter that defensively calls closeSilently() at the end of every request. Acts as a safety net for any thread-local connections that weren't closed by annotations.

Where it breaks down

All three mechanisms are caller-side — they depend on the code that initiates the work knowing that DB connections need lifecycle management. The actual connection creation in DotConnect is silent and implicit:

// DotConnect.executeQuery() — no-arg version
private void executeQuery() throws SQLException {
    Connection conn = DbConnectionFactory.getConnection();  // creates if not exists, silently
    executeQuery(conn);
}

// DbConnectionFactory.getConnection()
public static Connection getConnection() {
    // ... ThreadLocal lookup ...
    if (connection == null || connection.isClosed()) {
        DataSource db = getDataSource();
        connection = db.getConnection();                    // new connection, stored in ThreadLocal
        connectionsList.put(DATABASE_DEFAULT_DATASOURCE, connection);
    }
    return connection;
}

There is no warning, no error, and no tracking when getConnection() implicitly creates a new connection. It silently assumes someone higher in the call stack will handle cleanup. This assumption fails in several known patterns:

Pattern	Why cleanup fails	Examples
Background threads (executors, scheduled tasks, event listeners)	No CMSFilter, no annotations in the call stack	#34926 (MetricStatsCollector), #34831 (experiments), #34920 (embeddings)
CDI annotation bypass	@CloseDBIfOpened / @WrapInTransaction silently ineffective when object is new'd instead of CDI-proxied	#34482, AsyncEmbeddingsCallStrategy → new EmbeddingsRunner()
ThreadLocal poisoning	An earlier caller opens a connection without cleanup; subsequent wrapConnection() calls see isNewConnection=false and skip their close	#34926 (CountOfSitesWithThumbnailsMetricType poisoning telemetry thread)
try-with-resources anti-pattern	try (Connection c = DbConnectionFactory.getConnection()) closes the thread-local connection out from under other code that holds a reference	#34489

Why individual fixes are insufficient

Each leak instance gets its own fix (add wrapConnection() here, add @CloseDBIfOpened there), but:

• New code continues to be written that calls DotConnect on background threads without connection management
• There is no compile-time or runtime signal that a connection was implicitly created without a responsible closer
• The leak only manifests in production under specific timing/load conditions, making it hard to catch in testing
• The catch(Exception e) pattern (common in metric collectors, listeners, and async code) swallows errors without cleanup

Proposed Approach

Phase 1: Detection — Warn on unmanaged implicit connection creation

Add a runtime check in DbConnectionFactory.getConnection() that logs a warning when a new connection is created outside of a known-safe context. A "known-safe context" means one of:

• Inside a wrapConnection() call (trackable via a ThreadLocal boolean flag)
• Inside a @CloseDBIfOpened / @WrapInTransaction interceptor (trackable via the same or similar flag)
• On a thread with an active CMSFilter request (already trackable) (although I think we should usually find the issue before this point)

// Sketch — DbConnectionFactory.getConnection()
if (connection == null || connection.isClosed()) {
    connection = db.getConnection();
    connectionsList.put(DATABASE_DEFAULT_DATASOURCE, connection);

    if (!isInManagedContext()) {
        Logger.warn(DbConnectionFactory.class,
            "New DB connection created outside managed context on thread '"
            + Thread.currentThread().getName() + "'. "
            + "This connection has no guaranteed cleanup path. "
            + "Call stack: " + getCallerInfo());
    }
}

This surfaces every unmanaged connection creation in logs at the point of creation, making leak sources immediately visible rather than requiring pg_stat_activity forensics in production.

Phase 2: Audit — Identify and fix all unmanaged callers

Using the Phase 1 warnings in a test/staging environment:

1. Run the full telemetry collection cycle and capture all warnings
2. Run content publish/archive operations and capture warnings
3. Run AI embedding operations and capture warnings
4. Catalog all call sites that create unmanaged connections
5. Fix each one: add wrapConnection(), convert to DBMetricType, or pass explicit connections

Phase 3: Harden — Make unmanaged creation an error

Once all known callers are fixed:

1. Elevate the warning to an error log level
2. Consider throwing an exception in development/test mode (controlled by a config flag like DB_STRICT_CONNECTION_MANAGEMENT=true)
3. Add a unit test that runs common background-thread code paths and asserts no unmanaged connections are created

Phase 4: Structural prevention

Longer-term options to prevent the class of bug entirely:

• Make DotConnect require an explicit connection parameter — deprecate the no-arg loadObjectResults() / executeQuery() in favor of versions that take a Connection parameter. This makes connection ownership explicit at the call site.
• Provide a safe DotConnect.withManagedConnection() helper that wraps the query in wrapConnection() automatically, for cases where callers don't want to manage connections manually.
• CDI interceptor reliability (#34482) — ensure @CloseDBIfOpened works regardless of instantiation pattern, eliminating the CDI proxy bypass class of bugs.

Relationship to other issues

This is a systemic/architectural issue that encompasses the root cause behind multiple specific leak bugs:

Issue	How this audit relates
#34926	CountOfSitesWithThumbnailsMetricType — would be caught by Phase 1 warning on telemetry executor thread
#34831	ExperimentsAPIImpl.listActive() — would be caught by Phase 1 warning on content event threads
#34920	EmbeddingsFactory init paths — would be caught by Phase 1 warning on AI embedding threads
#34489	try-with-resources anti-pattern — Phase 4 deprecation of implicit connection creation eliminates this pattern
#34482	CDI interceptor bypass — Phase 3 strict mode would catch cases where annotations silently fail
#34837	Parent epic — this issue belongs in Tier 3 (systemic/architectural) as it addresses the root architectural issue that makes all the Tier 1-2 leaks possible

Acceptance Criteria

• DbConnectionFactory.getConnection() logs a warning when creating a new connection outside a managed context
• "Managed context" flag is set by wrapConnection(), @CloseDBIfOpened/@WrapInTransaction interceptors, and CMSFilter
• Warning includes thread name and caller info for easy triage
• All warnings from a full telemetry cycle in staging are triaged and fixed or documented as known-safe
• Config flag (DB_STRICT_CONNECTION_MANAGEMENT) available to elevate warnings to errors in test environments
• No-arg DotConnect.loadObjectResults() and executeQuery() are documented as requiring a managed context

Start with DbConnectionFactory.getConnection() and trace how wrapConnection(), the @CloseDBIfOpened/@WrapInTransaction interceptors, and CMSFilter establish connection-management context. Review DotConnect.executeQuery() and loadObjectResults() for implicit calls. Done means unmanaged creation is detected with thread and caller information, the managed-context behavior and strict-mode configuration are covered, and the listed acceptance criteria are addressed.