Architecture

Design principles

database-audits-core carries no dependency-injection annotations. Every class takes its dependencies as constructor arguments, so any container (Spring, Guice, plain new) can wire it. The Spring integration lives in database-audits-spring-boot, which wires everything with explicit @Bean methods.

Audits return findings; callers assert. Every audit() method returns a List<Finding> — empty when clean. Each Finding (io.github.databaseaudits.audit.finding) is a record carrying the structured facts behind the violation plus a description() that is the human-readable line; the calling test makes the assertions (typically with AssertJ, via Finding::description). This means AssertJ is test-scoped only, never a compile or runtime dependency of the library.

Never pass vacuously. A run that could not actually check anything throws rather than returning a clean-looking empty list. Runtime audits throw IllegalStateException on an empty SQL capture and on a wholly-unexplainable run; plan audits throw UnsupportedOperationException on a non-PostgreSQL platform. Both errors indicate a configuration problem to fix, not a clean schema.

`DatabasePlatform` — the hub

DatabasePlatform is an enum of the four supported products: H2, MARIADB, MYSQL, POSTGRESQL. Each constant holds a CatalogDialect — the source of the catalog audits' per-engine SQL — reached through platform.catalogDialect():

return platform.catalogDialect().foreignKeysSql();

CatalogDialect declares an abstract method for each query whose SQL genuinely diverges between engines (indexCatalogSql, foreignKeysSql, foreignKeyColumnTypesSql — PostgreSQL’s pg_catalog, MySQL’s information_schema.statistics/key_column_usage, H2’s information_schema) and a default method for each query whose standard information_schema SQL every engine shares (tablesWithoutPrimaryKeySql, nullableForeignKeyColumnSql). PostgresqlCatalogDialect, MysqlCatalogDialect, and H2CatalogDialect supply the divergent SQL; MARIADB reuses MysqlCatalogDialect. Adding an engine means adding an enum constant with a dialect: the constant’s constructor requires one, and a divergent dialect will not compile until it implements every abstract method — the same compile-time completeness the old exhaustive switch`es gave, while the shared SQL is written once. Detect the platform once at startup with `DatabasePlatform.fromDataSource(DataSource), which opens one connection, reads DatabaseMetaData.getDatabaseProductName(), and closes the connection.

Catalog family (all platforms)

Eight audits that query database metadata — information_schema or pg_catalog — over plain JDBC and compare it against rules. Results are deterministic regardless of test data; no SQL execution is analyzed.

CatalogQueries is the minimal JDBC-to-list-of-maps layer. It prepares one parameterized statement per call and returns rows as Map<String, Object> with case-insensitive key lookup — necessary because PostgreSQL lower-cases unquoted aliases while H2 upper-cases them.

IndexCatalog reads every index of a schema as IndexDefinition records (key columns in order, excluding INCLUDE columns and full-text/spatial indexes). It is the shared building block for ForeignKeyIndexAudit (leading-prefix coverage) and RedundantIndexAudit (prefix containment).

IndexDefinition is a value record holding a table name, index name, ordered column list, and flags for unique, primary, partial, and expression/prefix columns.

ForeignKeyCatalog reads every foreign key of a schema as ForeignKeyDefinition records (columns and referenced columns paired in constraint order), reusing CatalogDialect.foreignKeyColumnTypesSql() rather than a dedicated query. It is the shared building block for DuplicateForeignKeyAudit (relationship grouping).

Runtime family

Seven audits that inspect the real SQL Hibernate executed during the test run, via SqlCapturingStatementInspector. Four are Query plan analysis (PostgreSQL 16+ only) (PostgreSQL 16+ only, via EXPLAIN); three are Capture-scan audits (every platform) (every platform, token-scanning the captured SQL text).

SQL capture

SqlCapturingStatementInspector implements Hibernate’s StatementInspector, recording every statement with JDBC ? placeholders (no bind values) — exactly what EXPLAIN (GENERIC_PLAN) needs. The same instance must be both Hibernate’s inspector and the one the runtime audits read; see Usage — Runtime audits for wiring.

Query plan analysis (PostgreSQL 16+ only)

QueryPlanExplainer runs EXPLAIN (GENERIC_PLAN, FORMAT JSON) with chosen planner-penalty settings (for example SET enable_seqscan = off). A surviving node of the penalized kind proves no index can serve that access path, without needing real bind values or test data. Requires preferQueryMode=simple on the JDBC URL — generic-plan EXPLAIN only works over PostgreSQL’s simple query protocol.

Template method

CapturedSqlPlanAuditTemplate is the base class for three of the four EXPLAIN-based audits (WhereClauseIndexAudit, OrderByIndexAudit, JoinIndexAudit). The fixed algorithm de-duplicates captured SQL by normalized statement shape, plans each candidate with penalties applied, and collects offending nodes. Both vacuous-run guards — empty capture and wholly-unexplainable run — live here. Subclasses supply which statements to EXPLAIN, which GUCs to penalize, and how to recognize an offending node.

UnusedIndexAudit is the fourth plan-based audit, and deliberately does not extend the template: the template emits one finding per offending statement, but this audit needs the union of index usage across every statement, then a diff against the catalog — the inverse shape. It plans each candidate via the natural generic plan (no penalties), collects every Index Name the plan mentions, and reports each catalog index (from IndexCatalog) that is neither justified by a primary key, a unique constraint, being partial, appearing in that usage, nor covering a foreign key (via ForeignKeyCatalog).

Capture-scan audits (every platform)

These three need no EXPLAIN and no PostgreSQL-specific technique — they token-scan the normalized captured SQL text directly. UnconditionalMutationAudit scans for UPDATE/DELETE without WHERE. OffsetPaginationAudit scans for OFFSET-based pagination (or MySQL/MariaDB’s comma LIMIT <offset>, <count> form), which scales linearly with page depth. RepeatedStatementAudit reads SqlCapturingStatementInspector.executionCounts() (a per-statement tally the capturer keeps alongside the capture set) to catch N+1 statement bursts — a SELECT shape captured at least a given threshold number of times.

JPA family

SchemaEntityValidationAudit validates Hibernate’s entity mappings against the live schema, reporting every missing table, missing column, and incompatible column type in one run (using Hibernate’s own type-compatibility rule). It runs under ddl-auto=none — not Hibernate’s fail-fast ddl-auto=validate — obtaining the boot mapping model from a MappingMetadataIntegrator captured during bootstrap, plus a DataSource to read the live schema.

MissingVersionAttributeAudit walks the same boot mapping model for mutable root entities with no @Version attribute — a lost-update risk under concurrent transactions. It needs no DataSource: only the mapping model, never the live schema.

EagerCollectionFetchAudit walks the boot mapping model’s collection bindings (org.hibernate.mapping.Collection, covering both entity associations and @ElementCollection`s) for any fetched eagerly. Also needs no `DataSource.

UnmappedDatabaseObjectAudit is SchemaEntityValidationAudit’s mirror image: instead of checking every mapping exists in the schema, it checks the schema holds only what is mapped. It builds the mapped table/column sets from `Metadata.collectTableMappings() (which includes Hibernate-generated join/collection tables, not just each entity’s primary table) and diffs them against DatabaseMetaData.getTables()/getColumns(), scanning only schemas that contain at least one mapped table.

Exclusions

Every audit accepts exclusion parameters so consumers suppress known-intentional violations rather than making the audit guess. Exclusion types vary by audit — table names, constraint names, table.column identifiers, index names, relation names, SQL fragments, or normalized statement strings. See Audits for each audit’s exclusion type.