Epic: Database Connection and Transaction Management

Description

dotCMS pods experience database connection leaks that can overwhelm shared RDS instances under sustained load. Investigation has uncovered a cluster of related root causes spanning three layers: application code leaking connections on background threads, a silent env var naming bug causing aggressive connection churn, and missing server-side safety nets.

This epic groups all fixes, defense-in-depth enhancements, and observability work needed to achieve stable, leak-free database connection management across all environments.

---

Priority Tiers

Tier 1 — Active leak fixes (deploy ASAP, each is one PR)

These directly stop the connection accumulation observed in production. Each is an independent, deployable unit of work.

#	Issue	What	Impact
1	#34926	MetricStatsCollector ThreadLocal poisoning	CountOfSitesWithThumbnailsMetricType poisons executor thread's ThreadLocal, defeating wrapConnection() fix (#34490). Defensive cleanup after each metric + warning log + fix the known poison pill. Primary active leak — 46 orphaned connections observed on Frankfurt.
2	#34921	DB_MAXWAIT naming bug + pool defaults	Stops 60s connection churn on ALL deployments. One-line constant rename + safe defaults.
3	#34831	Experiments listActive() leak	Wraps ExperimentsFactoryImpl.listActive() in wrapConnection(). Stops the primary daily leak from content operations.
4	#34920	EmbeddingsFactory AI/vector init leak	Wraps EmbeddingsFactory callers at API layer. Stops vector extension and async embedding leaks.
5	#34922	Server-side RDS timeouts	Apply statement_timeout, idle_in_transaction_session_timeout, idle_session_timeout per-role. Operational — no code deploy. Provides a ceiling on leaked connection lifetime.

Tier 2 — Defense-in-depth (prevent future leaks, reduce blast radius)

These harden the system against leak vectors that aren't actively causing production issues but could under different conditions.

#	Issue	What
6	#34832	Query timeouts + tcpKeepAlive + keepaliveTime — setQueryTimeout() on DotConnect, JDBC tcpKeepAlive=true, HikariCP keepaliveTime env var
7	#34839	DatabaseHealthCheck race condition — same executor+shutdownNow() pattern as pre-#34490 telemetry
8	#34840	DB_VALIDATION_QUERY semantics — deployment examples disable JDBC4 Connection.isValid(); HikariCP upgrade required for keepalive
9	#34489	try-with-resources anti-pattern — codebase sweep for DbConnectionFactory.getConnection() in try-with-resources (creates secondary leak when outer code also holds thread-local)

Tier 3 — Systemic / architectural (long-term stability)

These address root architectural issues that make connection leaks possible in the first place.

#	Issue	What
10	#34482	CDI Interceptors for @CloseDBIfOpened / @WrapInTransaction — makes annotations work reliably regardless of how objects are instantiated (fixes the new vs CDI proxy bypass)
11	#34833	ES calls inside @WrapInTransaction — Elasticsearch HTTP I/O inside DB transactions holds connections open for external I/O duration. Blocks tightening idle_in_transaction_session_timeout.
12	#34923	Connection pool sizing observability — baseline HikariCP metrics, right-size DB_MAX_TOTAL, align thread pool config. Requires Tier 1 fixes first so baseline isn't distorted.
13	#34930	Audit & prevent unmanaged implicit connection creation — Add runtime detection when DbConnectionFactory.getConnection() creates a new connection outside a managed context (wrapConnection(), @CloseDBIfOpened, CMSFilter). Surfaces leak sources at creation time instead of requiring production forensics. Long-term: deprecate implicit no-arg DotConnect methods, strict mode config flag.

Completed

#	Issue	Status
⚠️	#34480	MetricStatsCollector telemetry leak — partially fixed in 26.02.27-01 via PR #34490. wrapConnection() is correct but defeated by ThreadLocal poisoning (#34926).

---

Dependency Graph

Tier 1 (deploy in order):
  #34926 (ThreadLocal poisoning)  ← deploy first, fixes primary active leak (46 connections on Frankfurt)
  #34921 (DB_MAXWAIT naming bug)  ← deploy next, biggest bang for buck on pool churn
  #34831 (experiments leak)       ← deploy next
  #34920 (embeddings/AI leak)     ← deploy next
  #34922 (RDS timeouts)           ← operational, can apply any time

Tier 2 (after Tier 1):
  #34832 (query timeouts + keepalive) ← depends on #34921 for correct maxLifetime
  #34839 (health check fix)
  #34840 (validation query + HikariCP upgrade) ← depends on #34832 for keepalive
  #34489 (anti-pattern sweep)

Tier 3 (after Tier 2):
  #34482 (CDI interceptors)       ← systemic fix, eliminates class of bugs
  #34833 (ES in transaction)      ← blocks tightening #34922 timeouts
  #34923 (pool sizing)            ← depends on Tier 1 leak fixes for clean baseline
  #34930 (unmanaged connection detection) ← prevents future leaks at source; Phase 1 (warnings) can start early

---

Desired Outcome

• Zero connection leaks from background threads — telemetry, experiments, embeddings, health checks
• HikariCP maxLifetime correctly applied (30 min, not 60s) — DB_MAXWAIT naming bug fixed
• Server-side timeouts active as backstop — no leaked connection persists indefinitely
• @WrapInTransaction / @CloseDBIfOpened reliable regardless of instantiation pattern
• Observability baseline established — pool utilization metrics enabling informed right-sizing
• No unplanned pod restarts required to recover from connection exhaustion

Target Personas

• Platform team — code fixes in Tier 1-2
• Enablement / DevOps — RDS timeouts (#34922), k8s workarounds, observability (#34923)
• Architecture — CDI interceptor design (#34482), ES transaction pattern (#34833)

Start by reading the Tier 1 issues and the entry points named here, including CountOfSitesWithThumbnailsMetricType, ExperimentsFactoryImpl.listActive(), EmbeddingsFactory, DatabaseHealthCheck, and DbConnectionFactory. Trace how wrapConnection() and the HikariCP settings relate across the tiers; this epic is done only when the listed leak, timeout, transaction, and observability outcomes are addressed.