Database checks

Filter the list, then jump to a check — the detail below narrows to match.

Schema

Generic checks consume JDBC metadata enriched by supported catalogs. System/temporary schemas, migration bookkeeping, extension-owned objects and inherited child partitions are excluded where identified.

DB-SCHEMA-001 - Tables without a primary key

MEDIUM. Reports an application table with no primary-key columns in readable, complete metadata. A declared PK is useful row-identity documentation, but neither an ORM nor every replication arrangement universally requires a database PK. Review whether a natural key, surrogate key or intentional keyless relation is appropriate; absence alone does not prove unsafe updates or duplicate data.

Framework-generated one-row identifier tables are excluded once their complete column inventory is read: Hibernate's sequence emulation (a single integer next_val column, which Hibernate 6/7 creates for GenerationType.AUTO/SEQUENCE on MySQL and never gives a key) and Spring Batch's MySQL *_SEQ tables (ID plus a unique UNIQUE_KEY). Their DDL belongs to the framework, so a PK recommendation is not actionable. See Hibernate SequenceStyleGenerator.

DB-SCHEMA-002 - Foreign key columns without a supporting index

MEDIUM. Reviews physical FKs without a known ordinary leading index access path over the complete child column set. An equality lookup can use those leading columns in a different order; indexing just one column of a composite FK is not equivalent. Known trailing expressions must not erase a usable leading key.

Partial, value-prefix and specialized definitions may require evidence this check does not have, but only an index that keys a foreign-key column, an expression or nothing known can make one FK's result unknown: a GIN, partial or generic-JDBC index over unrelated columns cannot serve that lookup and does not hide a finding. Incomplete index inventories cannot prove absence. MySQL/MariaDB engines that require FK support normally create a suitable index automatically, so contradictory metadata warrants investigation rather than blind DDL. Review parent-key changes and actual query plans before adding an index; an unindexed FK is not universally invalid or slow. See PostgreSQL FK constraints, MySQL FK restrictions and Oracle concurrency.

DB-SCHEMA-003 - Duplicate/redundant indexes

LOW. Reviews exact ordinary-index definition overlap only when the relevant semantics are known. A shorter leading prefix of a longer index is not sufficient evidence of redundancy. Included payload, key order/direction, expressions, predicates, access method, collation/operator class, state and constraint ownership can make superficially similar indexes different. Unknown definitions do not prove equality. Review dependencies, hints and measured usage; BootUI does not assert that dropping an index is safe. Generic JDBC and vendor catalogs lacking the complete comparison evidence can therefore leave this check unevaluated. An index whose comparison semantics are not modelled — a PostgreSQL hash or GIN index, for instance — is reported as unknown only when it could pair with another index on the same table: same key columns, and an access method that is equal, B-tree-like on both sides, or not reported. The unknown names that index rather than its table. A readable datasource can still produce a PARTIAL report when an applicable comparison is unknown.

DB-SCHEMA-004 - Foreign key column type mismatch with the referenced column

MEDIUM. Compares each child column with the column actually named by the FK, including alternate referenced keys. Reports a known representational-domain discrepancy, not merely unequal type names. Decimal containment considers both integral and fractional capacity; unknown scale is not zero. Fixed-width UUID pairs count as fully compared, as do identical declarations whose type name, JDBC type, size and decimal digits are all reported and equal. A size or scale the driver does not report is unknown, not equal, so other date/time, boolean or vendor-type pairs remain an unknown comparison rather than a guess. Review intended value domains and vendor compatibility before aligning definitions. JDBC type-family classification alone cannot establish coercion behavior or query-plan quality. MySQL itself requires the size and sign of integer and decimal FK pairs to match; see MySQL 8.4 FK constraints.

DB-SCHEMA-005 - Redundant unique index duplicating the primary key

LOW. Reviews an additional exact unique-index definition only when the actual PK backing identity and relevant index semantics are established. The first unique index with matching columns is not assumed to be the backing index. Different included columns, access semantics or ownership prevent an equivalence conclusion. A unique index that is partial, expression- or prefix-keyed, partitioned, of a special type, reported invalid, or of a reported access method outside the ordinary set compared here (such as hash, GIN, bitmap or an Oracle reverse-key index) is excluded from the comparison rather than reported as unknown: it can never be an exact duplicate of a proven backing index. A unique index whose semantics really are unreadable is reported once per index, naming the datasource, table and index. Oracle may use a nonunique index to enforce a PK/unique constraint. Review full definitions and dependencies, never drop a guessed constraint backing index.

DB-SCHEMA-006 - Duplicate foreign key constraints

LOW. Reviews relationships with identical qualified parent identities and child-to-parent pairs, including known update/delete actions and deferrability. Reordering the same pairs does not change the relationship; swapping which parent column each child references does. Different actions or timing are not redundant. Unmodeled enforcement/match semantics require checking full constraint definitions, not unconditional removal.

DB-SCHEMA-007 - Narrow auto-generated primary key

LOW. Reviews positively identified generated single-column TINYINT/SMALLINT keys. Their finite representable domain may be intentional. Type capacity is not a lifetime row count, a count of committed inserts or an exhaustion forecast. Review the intended domain; vendor generator checks separately inspect an observed frontier. No automatic widening is recommended.

DB-SCHEMA-010 - Invisible or ignored indexes

LOW. Reports a MySQL 8.0+ invisible (information_schema.statistics.IS_VISIBLE = 'NO'), MariaDB 10.6+ ignored (IGNORED = 'YES') or Oracle invisible (ALL_INDEXES.VISIBILITY = 'INVISIBLE') index. The optimizer does not use it by default, yet every INSERT, UPDATE and DELETE still maintains it, and an invisible UNIQUE index still enforces uniqueness. Such a state is usually a staged "soft drop" or a trial: decide whether it is finished, then make the index visible again or drop it after reviewing constraints, hints and dependencies.

Unknown visibility is a coverage gap, never a finding. Constraint-backing indexes, unusable indexes (see DB-ORACLE-001) and Oracle automatic-indexing candidates (SYS_AI_ names, deliberately kept invisible in report-only mode) are excluded. Servers predating the feature (MySQL 5.7, MariaDB before 10.6) are not applicable. MySQL use_invisible_indexes and Oracle OPTIMIZER_USE_INVISIBLE_INDEXES let a session opt in; MariaDB ignored indexes cannot be re-enabled through hints. See MySQL invisible indexes, MariaDB ignored indexes and Oracle invisible indexes.

Dialect detection and catalog augmentation

The product/version/JDBC metadata distinguishes PostgreSQL, MySQL and MariaDB. Oracle-specific augmentation requires a genuine Oracle banner and Oracle 19c or later; unknown compatible products retain generic metadata support. Oracle schema-resolution failure must not silently widen the scan to all visible schemas. The confirmation read is separate from ordinary scoped ALL_* augmentation.

Queries gate features by the detected version: PostgreSQL sequence views from 10, INCLUDE key/payload distinction from 11, index-build progress from 12 and NULLS NOT DISTINCT/schema publications from 15; PostgreSQL 18 enforcement semantics are not assumed on older servers. MySQL visibility, functional keys (8.0.13+) and MariaDB ignored indexes (10.6+) have separate capabilities. A denied or unsupported catalog is not an empty successful one. Bounded detail rows must not replace a complete composite index with only its first catalog key parts.

Primary contracts: Java 17 DatabaseMetaData, PostgreSQL pg_index, MySQL STATISTICS, MariaDB STATISTICS, Oracle ALL_INDEXES.

PostgreSQL

DB-PG-001 - Invalid PostgreSQL indexes

HIGH. Reports known indisvalid/indisready/indislive problems, preserving transient-build and partition-parent exclusions where supported. Planner validity, write maintenance and uniqueness are different facts. An invalid UNIQUE index left by a failed concurrent build may continue rejecting duplicates; invalid does not mean no enforcement or a guaranteed complete uniqueness guarantee. Confirm build state and dependencies before choosing a version-supported repair. See CREATE INDEX CONCURRENTLY.

DB-PG-002 - PostgreSQL sequence nearing exhaustion

HIGH. Reviews the observed sequence frontier against direction-aware sequence bounds and a known owning-column domain. Positive and negative increments and nondefault ranges matter. pg_sequences.last_value may be null because of permissions, lack of use or standby state. A null value is treated as "never read" (0% consumed, no coverage gap) only when the role holds SELECT or USAGE on the sequence and it is not an unlogged sequence read on a standby; otherwise it is unknown consumption, not zero, and must not erase the sequence definition. A fresh development database with unused identity columns therefore scans complete. setval(seq, v, false) leaves the same never-read state, so a sequence positioned that way but not yet read is assessed as unused from its configured start until its first nextval.

The 80% threshold describes a bounded-range snapshot, not remaining time. Cached reservations are not committed identifiers. Cycling can still exceed a narrower owning column before wrapping. Review sequence and column bounds together; restarting after deleting/archiving rows is not established safe. See CREATE SEQUENCE and pg_sequences.

DB-PG-003 - PostgreSQL NOT VALID constraint never validated

MEDIUM. The retained ID reports a constraint currently not validated; the historical heading is retained for existing links, not as a claim that it was never validated or that a migration was forgotten. NOT VALID normally checks new/updated rows while leaving existing rows unverified. PostgreSQL 18 also distinguishes enforcement state. Review the intended migration stage and version-supported validation; the snapshot does not prove bad rows or how long the state has existed. See ALTER TABLE and PostgreSQL 18 pg_constraint.

DB-PG-004 - PostgreSQL table lacking usable replica identity

MEDIUM. Reviews a table in an applicable publication that publishes UPDATE or DELETE, using expanded membership and partition-root semantics. INSERT-only publications are excluded. Default identity without a PK and explicit NOTHING require review; unknown selected-index state is not assumed usable. Relevant writes can fail without waiting for a subscriber to attach. Review publication actions and choose an appropriate PK, supported identity index or FULL identity. See publications and pg_publication_tables.

DB-PG-005 - PostgreSQL unlogged tables

LOW. Reports an ordinary table or leaf partition with pg_class.relpersistence = 'u', excluding system and extension-owned tables. Unlogged tables skip write-ahead logging: PostgreSQL truncates them after a crash or immediate shutdown, does not replicate them to physical standbys (where they cannot be read), and cannot restore their contents by WAL-based point-in-time recovery. A logical pg_dump still copies their rows. Unlogged storage is an explicit DDL choice, often deliberate for caches or staging data, so this is a durability review prompt; ALTER TABLE ... SET LOGGED rewrites and WAL-logs a table that must be durable. Partitioned parents are not read: PostgreSQL 13-18 rejects unlogged partitioned tables, so their persistence flag carries no storage meaning. A denied or failed catalog read is SKIPPED, not clean. See CREATE TABLE UNLOGGED.

MySQL and MariaDB

DB-MYSQL-001 - Tables on a non-transactional storage engine

MEDIUM. Reviews known nontransactional engines while preserving intentional specialist exclusions. Rollback support, FK enforcement, crash safety and locking are different capabilities: MariaDB Aria can be crash-safe without being transactional. Review application transaction requirements and migration costs, not merely whether the engine is named InnoDB. See MariaDB Aria.

DB-MYSQL-002 - Tables/columns using the legacy utf8mb3 character set

MEDIUM. Reviews observed utf8/utf8mb3 defaults or column encodings, not every non-utf8mb4 choice. Three-byte encoding cannot represent the full Unicode range. Changing a table default is distinct from converting existing columns. Review supported character sets, index lengths and required comparison semantics before migration; a different collation can change uniqueness and ordering.

MariaDB 11.4.5+ supports MySQL-compatible 0900 names as aliases to UCA1400 collations. This does not mean older MariaDB supports them or that the implementation is identical to MySQL's. See MariaDB 11.4.5.

DB-MYSQL-003 - MySQL/MariaDB AUTO_INCREMENT nearing exhaustion

HIGH. Reviews a reported next/reserved counter at or beyond the 80% threshold of the known signed/unsigned column capacity. Arithmetic accommodates BIGINT UNSIGNED; missing counter or column evidence is unknown, not zero or a clean pass.

InnoDB counter metadata can be cached/reserved/stale and is not a committed-row count. MariaDB has persistent InnoDB counters from 10.2.4, not only an in-memory counter on modern versions. Persistence does not make allocation transactional or gapless. Review column bounds and referencing columns without querying application MAX(id) or resetting counters. See MariaDB InnoDB counter handling.

Oracle

Augmentation uses scoped ALL_* dictionary reads with bound owner parameters on confirmed Oracle 19c+, without a production Oracle-driver dependency. Exact owner/object identity and independent partition metadata are necessary; an inaccessible partition catalog does not establish that all partitions are usable.

DB-ORACLE-001 - Unusable Oracle indexes

HIGH. Reports explicit UNUSABLE ordinary/partition/subpartition state. Null, unknown and N/A are not synonyms for UNUSABLE. Domain-index special semantics are excluded consistently. GENERATED='Y' describes an index's generated name, not constraint ownership. An unusable unique enforcement index can block DML even with SKIP_UNUSABLE_INDEXES enabled. Review exact index/partition type and dependencies before a suitable maintenance operation. See ALL_INDEXES.

DB-ORACLE-002 - Disabled or unvalidated Oracle constraints

HIGH. Interprets known STATUS and VALIDATED together, with enforcement, RELY and deferral kept distinct. ENABLE NOVALIDATE checks new changes without proving old rows valid; DISABLE VALIDATE has different restrictions from DISABLE NOVALIDATE. An automatically named NOT NULL check is not excluded when its actual state is problematic. Unknown strings do not establish a disabled constraint. Review the intended state and Oracle's ENABLE VALIDATE CONSTRAINT syntax and locking implications. See ALL_CONSTRAINTS and data integrity.

DB-ORACLE-003 - Oracle sequence or identity generator nearing exhaustion

HIGH. Reviews positive/negative sequence bounds and known linked identity-column precision. NUMBER(p,0) can be much narrower than the underlying sequence; not every identifier is an unconstrained NUMBER. Identity ownership is obtained from dictionary linkage, not guessed from an ISEQ name.

LAST_NUMBER includes cache reservation, not committed consumption. The 80% snapshot threshold is not a time estimate. Session/scalable/sharded definitions remain excluded from ordinary range inference, and cycling does not automatically protect a narrower column. Oracle does not expose the original starting value through ALL_SEQUENCES: the denominator is the configured directional min/max range, with both endpoints clamped to a known identity-column domain. Use identity-aware column guidance for internal identity sequences; do not blindly alter/reset their sequence. Review bounds and precision without querying application rows. See ALL_SEQUENCES, ALL_TAB_IDENTITY_COLS and NUMBER types.

Hibernate mapping

These checks compare available declarations with observed metadata, not Hibernate's effective runtime mapping. Even explicit annotation/XML names are logical names subject to a physical naming strategy. The pinned Spring Boot 4.1.1 BOM selects Hibernate 7.4.5.Final; Quarkus 3.33.3.1 selects 7.2.19.Final; both use Persistence 3.2.0. The matching 7.4 and 7.2 contracts explicitly distinguish logical names from names used in generated DML/DDL.

No persistence-unit-to-datasource or effective optimizer contract is guessed. Failed or ambiguous source inventories, unresolved inheritance/overrides, incomplete columns and unsupported association placement cannot prove missing schema objects. Relation-name resolution must not confuse a mapped view with a missing base table. Ordinary reflection also cannot distinguish an omitted annotation default from that same default written explicitly. The bridge recognizes annotation-visible converters and placement restrictions, but does not resolve auto-applied converters, XML overrides or provider-specific effective JDBC mappings.

DB-HIB-002 - Mapped entity table not found in the physical schema

MEDIUM. Reviews an explicit declared table name not observed in sufficiently complete scoped relation metadata. This is not proof that the effective Hibernate table is missing. Review naming strategy, relation type, privileges, migration and persistence-unit/datasource assignment before changing anything.

DB-HIB-003 - Declared column nullability differs from observed metadata

MEDIUM. Compares a declared @Column(nullable=false) against known physical nullability. Java type families are not JDBC mapping evidence, so this rule does not compare column types despite its historical heading. Relations reported by JDBC as VIEW or MATERIALIZED VIEW, including secondary views, are excluded: a view's reported nullable column does not establish a missing physical NOT NULL constraint. Views remain available for relation-name and column-name checks. When only view columns would be compared, this rule is SKIPPED with an informational diagnostic, not a finding or a passing check; ordinary table mismatches in the same scan are still reported. Entity annotations such as @Immutable do not exempt a physical table from the comparison. Default-valued true does not establish explicit intent. Raw Java type family no longer proves an effective JDBC type mismatch: converters, Boolean/UUID emulation and custom types are valid. Review declarations and actual column constraints, not a guessed Java-to-SQL representation.

DB-HIB-004 - Mapped column length longer than the physical column size

MEDIUM. Compares positive nondefault declared lengths with a positively bounded physical string column. LOB, conversion, native-definition and unresolved placement ambiguity are excluded. An explicit @Enumerated(EnumType.STRING) enum without an @EnumeratedValue field stores the constant's name(), so it is compared like a string. Implicit or ORDINAL enums, and other ambiguous mappings, are silently skipped on non-character columns and reported as unknown evidence only when the physical column is bounded character storage. Native MySQL/MariaDB ENUM and SET columns, which drivers report as character types sized to the longest label, are not compared. An arbitrary large length is not synonymous with an unbounded SQL type. @Column(length=...) describes schema generation, not runtime input validation; review declaration versus database definition rather than assuming the mapping accepts or validates every string of that length.

DB-HIB-005 - Mapped unique constraint has no backing physical unique index

HIGH. Reviews declared uniqueness against known physical guarantees, separately from optimizer visibility. Invisible/ignored UNIQUE indexes still enforce uniqueness. A value-prefix key may reject more values without permitting duplicate full keys. Subset coverage also depends on null semantics: Oracle UNIQUE(a) with nullable a can allow repeated (NULL, 1) rows that UNIQUE(a, b) rejects. Such Oracle subset coverage needs known NOT NULL keys or equivalent evidence; it cannot be assumed. INCLUDE payload is not a unique key. Oracle may use nonunique backing indexes for unique constraints, so a nonunique constraint-backed index (including a PostgreSQL exclusion constraint) keeps the result unknown; a unique constraint-backed index such as a primary key is judged on its own structure. Collation cannot weaken enforcement (deterministic collations compare bytes, nondeterministic ones only merge more values), but a PostgreSQL key part with a non-default operator class may redefine equality and needs review. Partial, invalid, operator-class and unknown definitions require precise evidence, but only when the index could cover the declared columns; an uncertain index on other columns does not hide a missing key. Review full constraint semantics before adding a new guarantee.

DB-HIB-006 - Mapped column not found in the physical table

MEDIUM. Reviews an explicit declared column name not observed in a resolved relation with complete column metadata. Physical naming, inherited/secondary placement and source ambiguity must be considered. Do not infer inevitable runtime SQL failure or prescribe applying a migration solely from annotation names.

DB-HIB-007 - Mapped association has no physical foreign key constraint

MEDIUM. Reviews a complete explicit association declaration against actual qualified child-to-parent pairs. Respects NO_CONSTRAINT, supported join placement and explicit target information. A single join column that omits referencedColumnName is paired with the JPA default, the target entity's @Id column, once that column is corroborated as the target table's observed single-column primary key. The @Id column is taken from an explicit @Column(name), or from a plain lowercase attribute name such as id. Composite joins with an omitted referenced column, an unestablished or non-primary-key identifier, and constraints that pair the join column with a non-primary-key target column remain unknown. A matching constraint passes only when it is known to be enforced. On PostgreSQL, a foreign key absent from the complete NOT VALID catalog read (DB-PG-003's source) is validated and enforced, because PostgreSQL 18 marks every NOT ENFORCED constraint NOT VALID; a failed or truncated read leaves enforcement unknown. JPA cascade does not imply database ON DELETE CASCADE; FK-generation annotations are not a proof of the live database's intended cascade policy. Review whether a database constraint is intended before adding one. See Jakarta Persistence 3.2.

DB-HIB-009 - IDENTITY identifier column without database-side generation

MEDIUM. Reviews an explicitly named @Id declaring @GeneratedValue(strategy = GenerationType.IDENTITY) whose resolved PostgreSQL, MySQL or MariaDB column the driver explicitly reports with IS_AUTOINCREMENT = NO, no COLUMN_DEF default and no generated column. Hibernate omits an IDENTITY key from the INSERT and reads back the database-generated value, so such inserts fail, or store a placeholder on a nullable column or in non-strict MySQL modes, unless something the metadata cannot show, such as a BEFORE INSERT trigger, assigns the key. Hibernate schema validation does not compare this.

pgjdbc reports IS_AUTOINCREMENT = YES for identity columns and nextval(...) defaults, and the MySQL/MariaDB drivers report AUTO_INCREMENT. A missing or empty value is unknown, never NO. Other drivers' semantics are not established, so other dialects are not compared. GenerationType.AUTO and an omitted strategy are provider-selected and are not treated as IDENTITY; the identifier column must be explicitly named because physical naming strategies differ between Spring Boot and Quarkus. Check for a trigger first, then make the column database-generated or change the strategy. This is a physical cross-reference, not the Hibernate advisor's HIB-ID-001/HIB-ID-006 trade-offs.

DB-HIB-010 - Declared numeric precision or scale exceeds the observed column

MEDIUM. Compares a positive @Column(precision = p, scale = s) on a BigDecimal/BigInteger attribute with a bounded physical DECIMAL/NUMERIC column. With a positive precision, Hibernate 7.2/7.4 applies the annotation's scale as written, including its default zero, so both are compared. A narrower physical scale silently rounds written values; fewer physical integer digits (precision minus scale) reject large values, or clamp them in non-strict MySQL/MariaDB modes. Converters, native column definitions, @Lob and non-decimal attributes are not compared. Unconstrained columns (PostgreSQL numeric without a type modifier, Oracle NUMBER without precision), negative scales and a scale larger than the precision are unknown rather than guessed. This complements the Hibernate advisor's HIB-MAP-014, which reports a missing declaration. @Column(precision/scale) is schema-generation metadata, not input validation. See PostgreSQL numeric types and MySQL 8.4 fixed-point types.

Runtime SQL

DB-RUNTIME-001 - SQL text variations with predicate literals

LOW. Describes distinct retained SQL texts sharing a normalized shape that contains predicate literals, within a bounded SQL Trace observation window. It issues no new query and reports bounded counts and an opaque shape identifier, not captured literal values or allegedly guaranteed literal-free SQL text.

Changed comments, whitespace, projection constants or legitimate framework discriminator literals can produce variation. This does not establish that predicate values changed, that code concatenated input, that a plan cache is inefficient or that an injection vulnerability exists. A larger variant count is not “high confidence” in any of those claims. Inspect the existing SQL Trace evidence and call site before deciding whether parameterization is relevant. An absent capture window is SKIPPED.

Retired rules

These IDs remain reserved so old dismissals stay harmless and are never applied to unrelated future checks.

DB-SCHEMA-008 - Composite foreign key with partially nullable columns

Retired: mixed nullability can correctly model a required tenant and an optional relationship. MATCH SIMPLE and MATCH FULL differ; uniform nullability does not establish business intent. Making every column nullable can weaken integrity. See PostgreSQL foreign keys.

DB-SCHEMA-009 - Composite unique index with partially nullable columns

Retired: NULLS DISTINCT can be intentional, and Oracle rejects equal non-null portions of partially null composite unique keys, contrary to the previous generalized explanation. See Oracle integrity semantics.

DB-HIB-001 - Mapped foreign key column has no physical index

Retired: without a physical FK, the residual mapped association does not establish a child-side index need. Traversing an owning to-one association loads through the parent's referenced key. Physical FK access-path review remains DB-SCHEMA-002; workload-specific child lookup tuning is not inferred.

DB-HIB-008 - Hibernate sequence allocationSize does not match the physical sequence's INCREMENT BY

Retired: annotation allocation size alone lacks effective optimizer, mismatch strategy, qualified sequence and persistence-unit provenance. Hibernate's documented FIX strategy can override the mapping from the database. Compare effective generator contracts when diagnosing a real mismatch, not coincidentally equal bare names. See matching Hibernate 7.4 and 7.2 APIs.

Deliberately not checked

No application row counts, index cardinality/usage, bloat, cache-size tuning, sequence gaps, blanket NOT NULL policy, automatic sequence restart, arbitrary query plans or production workload predictions. PostgreSQL data-type preferences from the community "Don't Do This" list (money, json versus jsonb, char(n), timetz) were reviewed and not added: each has intentional uses, and one rule-level dismissal would silence unrelated types. Catalog snapshots can change concurrently and depend on driver coverage and role visibility. Live MariaDB documentation is version-sensitive; Oracle 19c documentation can include later patch syntax. An unsupported or unverified capability stays unknown rather than being silently assumed absent.