PrimaryKeyPresenceAudit

Every base table in the schema must have a PRIMARY KEY. Pass table names to skip as excludedTables. A constant LIQUIBASE_BOOKKEEPING_TABLES is provided for the common case of excluding Liquibase’s own tables.

List<Finding> findings = new PrimaryKeyPresenceAudit(queries, platform)
        .audit("my_schema", PrimaryKeyPresenceAudit.LIQUIBASE_BOOKKEEPING_TABLES);

Finding format: the table name — for example orders.

Fix: Add a PRIMARY KEY to the table, or pass its name as an exclusion.

ForeignKeyIndexAudit

Every foreign key constraint must be backed by an index whose leading columns are the FK columns. An unindexed FK causes slow child→parent lookups and lock-heavy parent DELETE/UPDATE (sequential child scan under a strong lock). Pass constraint names to skip as excludedConstraints.

Note: PostgreSQL and H2 do not auto-create an index for a foreign key. MySQL/MariaDB InnoDB does, so this audit usually passes there, though it still catches an index dropped after the fact on MariaDB (permitted while foreign_key_checks is suspended; MySQL refuses such drops outright).

List<Finding> findings = new ForeignKeyIndexAudit(queries, indexes, platform)
        .audit("my_schema", Set.of("fk_legacy_unindexed_constraint"));

Finding format:

orders.fk_orders_customer  ->  FOREIGN KEY (customer_id) REFERENCES customers

Fix: Add an index whose leading column(s) are the FK column(s), or exclude the constraint name.

ForeignKeyNotNullAudit

Every foreign key column should be NOT NULL unless the relationship is genuinely optional. Pass column identifiers in table.column format to skip as excludedColumns. Composite FKs are reported per column.

List<Finding> findings = new ForeignKeyNotNullAudit(queries, platform)
        .audit("my_schema", Set.of("orders.optional_partner_id"));

Finding format:

orders.customer_id (fk_orders_customer) is nullable

Fix: Make the column NOT NULL, or exclude it if the optional relationship is intentional.

ForeignKeyTypeMatchAudit

Every foreign key column must have exactly the same declared type as the column it references. A type mismatch — an integer referencing a bigint, differing varchar lengths — forces implicit conversions in joins and FK checks, can defeat index use, and caps the child at the narrower range. Pass column identifiers in table.column format to skip as excludedColumns.

List<Finding> findings = new ForeignKeyTypeMatchAudit(queries, platform)
        .audit("my_schema", Set.of());

Finding format:

orders.customer_id is integer but references customers.id which is bigint (fk_orders_customer)

Fix: Align the FK column’s type with its referenced column’s, or exclude the column if the mismatch is deliberate.

DuplicateForeignKeyAudit

No relationship should be enforced by more than one foreign key constraint. A duplicate FK constraint — a Liquibase changeset applied twice, a hand-written constraint duplicating a generated one — doubles constraint-check work on every child write and confuses schema tooling. Two constraints on the same table are duplicates when they declare the same FK columns to the same referenced table and columns, regardless of column declaration order. Pass constraint names to skip as excludedConstraints; excluding one constraint of a duplicate pair drops that relationship below two members, which suppresses the finding entirely.

List<Finding> findings = new DuplicateForeignKeyAudit(foreignKeys)
        .audit("my_schema", Set.of());

Finding format:

orders: FOREIGN KEY (customer_id) REFERENCES customers (id) is duplicated by constraints [fk_orders_customer, fk_orders_customer_legacy]

Fix: Drop all but one of the duplicate constraints, or exclude one to keep the duplication deliberately.

PrimaryKeyTypeAudit

Every primary key should be at least bigint wide. A 32-bit (or narrower) integer primary key overflows at roughly 2.1 billion rows — trivial to fix at design time, brutal to fix live, since widening a primary key in place typically means rewriting the table and every referencing foreign key. Advisory for genuinely small, bounded tables. Pass column identifiers in table.column format to skip as excludedColumns.

List<Finding> findings = new PrimaryKeyTypeAudit(queries, platform)
        .audit("my_schema", Set.of());

Finding format:

orders.id primary key type integer is narrower than bigint — risks key exhaustion

Fix: Migrate the column to BIGINT — widening any referencing foreign key columns in the same change, since ForeignKeyTypeMatchAudit will flag stragglers — or exclude the column if the table is genuinely bounded.

RedundantIndexAudit

No non-unique index should be made redundant by another index whose leading columns are identical. A redundant index wastes write throughput and storage — the wider index already serves the same lookups. Ignores primary key, unique, partial, and expression indexes. Pass index names to skip as excludedIndexes.

List<Finding> findings = new RedundantIndexAudit(indexes)
        .audit("my_schema", Set.of("idx_orders_customer_legacy"));

Finding format:

orders.idx_orders_customer is covered by idx_orders_customer_status

Fix: Drop the narrower index (the wider one already serves its lookups), or exclude the index name if the redundancy is intentional (for example, a collation-specific index).

UniqueIndexNotNullAudit

No UNIQUE index should include a nullable column. On every supported platform a UNIQUE index or constraint permits any number of rows whose key contains a NULL — so a "unique business key" declared on a nullable column silently admits duplicate rows for whichever rows are missing the key. Partial and expression indexes are skipped rather than flagged, since partial uniqueness is often deliberate. Pass index names to skip as excludedIndexes.

List<Finding> findings = new UniqueIndexNotNullAudit(queries, indexes, platform)
        .audit("my_schema", Set.of());

Finding format:

customers.uq_customers_email is UNIQUE over nullable column(s) [email] — rows with NULLs bypass uniqueness

Fix: Make the nullable column NOT NULL, use PostgreSQL 15+ NULLS NOT DISTINCT, or exclude the index if the partial uniqueness is deliberate.

Plan-based runtime audits (PostgreSQL 16+ only)

These require preferQueryMode=simple on the JDBC URL. They throw UnsupportedOperationException on any other platform and IllegalStateException when the SQL capture is empty.

List<Finding> findings = new WhereClauseIndexAudit(explainer, inspector)
        .audit(
            Set.of("audit_log"),                              // skip this table
            List.of("where status = 'LEGACY'")               // skip statements containing this
        );

Seq Scan on 'orders' filtering (status = $1)
      select o1_0.id, o1_0.status from orders o1_0 where o1_0.status = ?

List<Finding> findings = new OrderByIndexAudit(explainer, inspector)
        .audit(
            Set.of("lookup_values"),                          // skip this small static table
            List.of("group by")                              // skip aggregate queries
        );

Sort under 'orders' by (created_at DESC)
      select o1_0.id, o1_0.created_at from orders o1_0 order by o1_0.created_at desc

List<Finding> findings = new JoinIndexAudit(explainer, inspector)
        .audit(
            Set.of("lookup_values"),                         // skip this small static table
            List.of()
        );

Hash Join on 'orders' joining (order_items.order_id = orders.id)
      select oi1_0.id, o1_0.status from order_items oi1_0 join orders o1_0 on oi1_0.order_id = o1_0.id

Merge Join on 'orders' joining (order_items.order_id = orders.id)
      select oi1_0.id, o1_0.status from order_items oi1_0 join orders o1_0 on oi1_0.order_id = o1_0.id

Nested Loop with inner Seq Scan on 'orders' joining (order_items.order_id = orders.id)
      select oi1_0.id, o1_0.status from order_items oi1_0 join orders o1_0 on oi1_0.order_id = o1_0.id

List<Finding> findings = new UnusedIndexAudit(explainer, inspector, indexes, foreignKeys)
        .audit("my_schema", Set.of("idx_kept_for_admin_report"));

orders.idx_orders_legacy_status is used by no captured statement's plan

Capture-scan runtime audits (all platforms)

These scan the captured SQL text directly — no EXPLAIN, no platform restriction. They throw IllegalStateException when the SQL capture is empty.

List<Finding> findings = new UnconditionalMutationAudit(inspector)
        .audit(Set.of("delete from schema_version"));

delete from session_tokens

List<Finding> findings = new OffsetPaginationAudit(inspector)
        .audit(List.of("from lookup_values"));

select o1_0.id, o1_0.status from orders o1_0 order by o1_0.created_at desc limit ? offset ?

List<Finding> findings = new RepeatedStatementAudit(inspector)
        .audit(50, List.of());

executed 137 times: select i1_0.id, i1_0.order_id from order_items i1_0 where i1_0.order_id = ?

SchemaEntityValidationAudit

The JPA entity mappings must match the live database schema. This audit walks Hibernate’s fully resolved boot mapping model and, for each mapped table, confirms against the live database metadata that the table exists, every mapped column exists, and each column’s type is compatible — reporting all mismatches in one run. It uses Hibernate’s own type-compatibility rule, so a mapping Hibernate accepts is never flagged.

Run with ddl-auto=none. The audit deliberately does not rely on Hibernate’s ddl-auto=validate startup check, which fails fast on the first mismatch and aborts the EntityManagerFactory build. The boot mapping model is captured during bootstrap by a MappingMetadataIntegrator (auto-registered via META-INF/services).

List<Finding> findings =
    SchemaEntityValidationAudit.forEntityManagerFactory(entityManagerFactory, dataSource).audit();

Finding: e.g. missing column [quantity] in table [public.parent], or wrong column type in column [name] in table [public.parent]; found [integer], but expecting [varchar(255)].

Fix: Reconcile the entity mappings with the Liquibase-built schema (whichever drifted), or pass a known, acceptable mismatch as an excluded relation (a table name or table.column, optionally schema-qualified as schema.table / schema.table.column) to audit(Set<String>).

MissingVersionAttributeAudit

Every mutable root entity should carry a @Version attribute. A mutable entity with no optimistic-locking version lets concurrent transactions silently overwrite each other’s changes (a lost update). Versioning is declared on the inheritance hierarchy’s root, so only root entities are checked; an @Immutable entity is skipped, since it cannot lose an update it never receives. Advisory: append-only or single-writer entities are legitimate exclusions. Pass entities to skip as excludedEntities — fully-qualified name, simple name, or physical table name, matched case-insensitively.

List<Finding> findings = MissingVersionAttributeAudit
        .forEntityManagerFactory(entityManagerFactory)
        .audit(Set.of("ReferenceData"));

Finding: e.g. com.acme.Order (table orders) has no @Version attribute — concurrent updates can silently overwrite each other.

Fix: Add a @Version attribute, or exclude the entity if it is genuinely append-only or single-writer.

EagerCollectionFetchAudit

Every mapped collection should be fetched lazily. @OneToMany/@ManyToMany(fetch = EAGER) (also the JPA default for @ElementCollection) loads the collection on every load of its owning entity — multiplying rows through a join or firing an extra query per owner, compounding across every entity that reaches the collection. Pass a deliberately eager collection’s Hibernate role (e.g. com.acme.Order.items) to skip as excludedRoles.

List<Finding> findings = EagerCollectionFetchAudit
        .forEntityManagerFactory(entityManagerFactory)
        .audit(Set.of());

Finding: e.g. com.acme.Order.items (collection table order_items) is fetched eagerly — loaded on every owner load.

Fix: Switch to FetchType.LAZY, and fetch eagerly only where a specific query genuinely needs the collection, via a fetch join or @EntityGraph.

UnmappedDatabaseObjectAudit

Every physical base table and column in the live schema should be mapped by a JPA entity — the reverse direction of SchemaEntityValidationAudit, which proves every mapping exists in the schema but nothing proves the schema holds only what is mapped. A leftover table keeps accumulating unowned data; a NOT NULL column with no default that no entity maps makes every entity INSERT against that table fail at runtime. Only schemas containing at least one mapped table are scanned, and only base tables (views are ignored). A Hibernate-generated join/collection table (@JoinTable, @ElementCollection’s `@CollectionTable) counts as mapped. Pass a known, acceptable unmapped relation (a table name or table.column, optionally schema-qualified) as excludedRelations — for example migration-tool bookkeeping tables.

List<Finding> findings = UnmappedDatabaseObjectAudit
        .forEntityManagerFactory(entityManagerFactory, dataSource)
        .audit(PrimaryKeyPresenceAudit.LIQUIBASE_BOOKKEEPING_TABLES);

Finding: e.g. unmapped table [public.orphan_table] exists in the schema but no entity maps it, or unmapped column [tenant_id] in table [public.orders] — NOT NULL with no default breaks entity inserts.

Fix: Map the object, drop it via a migration, or exclude it (migration bookkeeping tables, deliberate denormalizations).