Skip to content

cis-api persistence audit — 2026-11-01

Result and scope

The compilation blockers are fixed. The persistence audit covers the full cis-api/src/main/java/com/dw/ngms/cis/persistence tree, rather than a user-repository sample. Findings below are proposed work. The subsequent mapper constructor-injection migration changes dependency wiring and preserves the query/mapping behavior identified here; the persistence optimizations remain proposed work. Source line references below identify the audit snapshot before that migration.

Package Java files Screening performed
domains 193 Associations, explicit/default eager loading, collection fetches, batching, identifiers, named native queries and result mappings
repository 170 Query annotations, derived methods, entity graphs, pagination/counts, scalar/entity returns, modifying queries, custom SQL implementation
projection 168 Getter/alias candidates, nested entity references, scalar read-model suitability
rowmapper 43 Repository access, duplicate column reads, nullable numbers/timestamps, producer SQL aliases
views 5 Association exposure, identity and mutability risks
Total 579 25,246 source lines; 389 repository @Query declarations

The complete inventory lists every file, source size, structural signals and repository query declarations. All files received structural screening; the concrete findings received targeted source and consumer review. An inventory entry without a finding is not a claim of runtime correctness. This is not a line-by-line business-rule certification of all 579 files.

Repeated application repository calls are confirmed from source. Actual SQL counts, row amplification, latency, database constraints/indexes, view uniqueness and Oracle execution plans were not measured. First-level identity reuse and query caches can affect SQL counts, so repository call counts below must not be reported as measured SQL counts.

Current build files select Java 25, Spring Boot 4.1.2 and Gradle 9.8.2. Older skill/version notes are not the authority for this snapshot.

Compilation fixes implemented

SearchDocumentMapperDecorator, SgDataParcelsDecorator and WorkflowNotesDecorator each had a constructor accepting the delegate plus another dependency. MapStruct's generated decorator subclass could not call those constructors. Simply adding an empty constructor would leave those dependencies uninitialized.

The three mappers now use constructor-injected collaborator components with @AfterMapping hooks. Existing enrichment is retained: document URL/image prefixes, parcel status captions and workflow usernames. The helper classes retain their existing names. Their repository lookup behavior is intentionally still visible in the audit below; changing the hook mechanism does not fix N+1 by itself.

Request models, response DTOs, controller signatures and repository query semantics were not changed. Generated implementations were inspected, not edited. The generated mapping handles null sources before running hooks; this also avoids the old document decorator's null dereference for a null source.

Validation:

  • ./gradlew.bat :cis-api:compileJava — passed, with warnings.
  • ./gradlew.bat compileJava compileTestJava — passed across all six root modules; some tasks were up to date.
  • ./gradlew.bat :cis-search:test --tests '*MappingEnrichmentTest' :cis-api:test --tests '*WorkflowNotesMappingTest' — passed: three tests, zero failures/errors. These are isolated Spring wiring/mapping tests without Oracle.
  • Tests exercise forward mapping, enrichment, selected reverse mappings and null-source handling. They do not constitute full HTTP/JSON parity or a full application-context startup test.

High-priority findings and fixes

P01 — User lists perform per-user and per-role repository lookups

Evidence: UserService:121, UserMapperDecorator:134, User:91, Roles:52.

The page mapper queries status/type/title/province, organization/sector and each role's reference data. User roles are eager; metadata is a default-eager one-to-one. Loading roles can also load their eager collection. UserRoleMapperDecorator independently performs role/province/section lookups when used directly; do not assume it currently runs inside UserMapper (see P14).

Fix: load the page or a scalar page projection; batch roles/metadata and distinct reference IDs for those users; assemble lookup maps; map with no database calls. Use lightweight role/name projections to avoid pulling unrelated role data. Keep Page.map/page metadata and every existing nested DTO field. Chunk large IN requests for the deployed Oracle limits. Test multiple users with distinct and shared roles, missing lookup rows and empty metadata. Compare 1-user and 20-user cold-session statement counts.

P02 — Assistant listing adds another query per result

Evidence: UserService:139.

Each assistant is fully mapped, then findByUserIdAndAssistantId is called for that row.

Fix: query all relationships for the professional once, index by assistant ID, and combine with the batched user assembler from P01. Retain assistantId, assistantStatusId and current ordering. Test multiple assistants, duplicate/absent relationships and status values.

P03 — Lodgement and reservation list projections are followed by per-row entity reads

Evidence: LodgementDraftService:157, LodgementDraftDecorator:59, ReservationDraftDecorator:59.

The short draft projection is followed by a province and a full-user lookup per row. Full users bring P01's eager associations even when only a name is needed.

Fix: select the existing display fields in the projection using left joins, or batch name/province projections after paging. Keep full-name versus username behavior separate: the lodgement entity mapper assembles a full name, whereas its short-projection mapper uses userName. Preserve existing empty-string fallbacks. Test first/last pages, missing users/provinces and leading/trailing spaces.

P04 — Lodgement steps nest per-step lookups and per-building section loads

Evidence: LodgementDraftStepsDecorator:74, SchemeSchemeDecorator:30.

Each step looks up captions, counts documents and fetches a scheme. Each scheme loads all buildings and then loads each building's sections to calculate counts.

Fix: collect step and scheme IDs, batch schemes/captions, query document counts grouped by step, building counts grouped by scheme and section counts grouped by scheme. Use separate aggregates or COUNT(DISTINCT ...) to avoid multiplying counts across joins. Do not load section entities to call .size(). Preserve the current empty SchemeSchemeDto fallback and zero counts. Test two buildings with several sections and schemes without buildings.

P05 — Detail entity graphs fetch several sibling collections

Evidence: LodgementDraftRepository:20, ReservationDraftRepository:20.

These graphs include annexures/payments/steps/requests/documents or documents/steps/requests/outcomes. They can produce amplified result sets or substantial secondary loading. This is a fetch-plan risk, not a measured Cartesian product. Set de-duplication does not itself reduce transferred rows.

Fix: load the root once and fetch the required child groups in separate queries or controlled batches inside a read transaction. Keep detail responses complete, including empty collections and ordering. Avoid introducing multi-collection fetch joins into paged list queries. Measure returned SQL rows as well as statement counts; fewer statements can still be slower.

P06 — Leave pagination declares non-scalar count queries

Evidence: UserLeaveCalenderRepository:25.

Both explicit countQuery strings select * instead of a count. Pages requiring a count can fail or produce invalid totals; small result sets can mask this by avoiding the count execution.

Fix: replace only the count selections with COUNT(*), preserving each existing filter/subquery. Verify the reporting-user relationships separately before changing those predicates. Oracle integration tests must include more rows than the page size and verify totals and last-page headers.

P07 — Help auditing uses the globally latest login instead of the acting user

Evidence: UserLoginHistoryRepository:14, HelpService:32.

The query selects the user from the globally greatest login timestamp. Every help row repeats this lookup and inserts an audit row. Concurrent logins can attribute another user's help activity; tied timestamps can return multiple rows.

Fix: derive the actor once from the authenticated principal at service entry, then retain the existing per-help-row audit semantics while batching writes where appropriate. Remove the global-latest-login dependency from this consumer. Test simultaneous sessions, same-timestamp logins and empty login history. This changes incorrect audit attribution, not the response format.

P08 — Read-then-increment numbering is unsafe under concurrency

Evidence: LocationRepository:24, ArcPacketRepository:18, PacketService:77, UserMapperDecorator:57.

Location IDs use MAX+1; packet numbers read a maximum before incrementing; user codes read the last user. Packet maximum accepts provinceId but does not use it. The intended global/province/year number scope must be established before fixing that predicate.

Fix: use an approved Oracle sequence for technical IDs or a locked counter row for business numbers scoped by the actual numbering rule, with a matching unique constraint. Keep the existing formatted codes and response types. Handle empty tables. Concurrency tests must issue simultaneous creates. Inspect current sequence increments and ownership before any DDL; do not change applied migrations or broadly increase allocation sizes.

P09 — Data viewer executes raw SQL and materializes all results

Evidence: DataViewerCustomImpl:37, DataViewerService:97, DataViewerResource:76.

The object name is concatenated into a count query; the supplied query is executed directly, using deprecated setResultTransformer, and all rows are materialized. The count measures the whole object rather than the filtered query. The web request reaches this implementation. This audit does not establish the complete effective authorization or database-privilege boundary.

Fix: resolve approved objects server-side, enforce a defined read-only SQL/query feature contract with a database read-only principal, bind filter values, and use setTupleTransformer or a typed JDBC extractor preserving map keys/types. Stream background CSV exports with explicit resource/transaction lifetimes. Define whether totalCount means object rows or filtered results before changing it. Adding row caps or restricting supported query grammar needs a product/API behavior decision; it is not silently contract-neutral.

Medium-priority findings and fixes

P10 — Eager loading extends beyond users

Evidence: Cart:40, CartStage:40, MaintenanceDraft:79, MaintenanceDraftService:158.

Explicit eager associations occur in 19 domain files, including lodgement batch/request/document/step, scheme child collections, roles, province, sitemap and users. Default-eager to-one mappings add more; explicit FetchType.EAGER searches alone are insufficient. Maintenance list retrieval returns entities with user/province/list-item references, even though child collections use batching and lazy-aware mapping conditions.

Fix: use list projections or a deliberate to-one fetch plan, then examine child loads by endpoint. Preserve LazyLoadingAwareMapper conditions: switching fetch plans can change which response collections are populated. With open-in-view: false, mapping must finish inside the read-service transaction. Do not globally change associations to LAZY without endpoint parity and missing-row tests.

P11 — Notes, captions, report labels and fee conversion repeat lookups

Evidence: DocketNotesDecorator:28, WorkflowNotesDecorator, TemplateMapperDecorator:26, BusinessReport:74, CartJsonToCartItemConvertor:380.

Similar patterns occur in examination stamping/caveats, parcel figures, lodgement document/payment/annexure decorators and reservation step captions. Fee conversion queries by a multi-field pricing key per item.

Fix: batch lightweight user-name and reference projections per operation; for prices, batch or memoize by the complete pricing key within the request. Avoid shared mutable caches unless invalidation and fee effective-date behavior are defined. Keep username/full-name differences, missing-row handling and invoice rounding unchanged.

P12 — Productivity generation queries repeatedly for every action and day

Evidence: ProductivityReportGenerator:60.

The all-history path loads all action projections, deletes per action, then checks holiday/office/leave data and writes productivity rows in a day loop. This can grow with actions multiplied by days; it is an application query amplification problem rather than association traversal alone.

Fix: process stable action-ID chunks, preload calendars/office intervals/leave for each chunk's user/province/date range, bulk-delete the selected action IDs, and batch replacements. Retain interval precedence, open-action time handling and transaction/rollback behavior. Use a fixed clock and multi-day fixtures, including holidays overlapping leave.

P13 — Plan Safe draft query compares to null with equality

Evidence: PlanSafeDocTrackingRepository:47, consumer PlanSafeDocTrackingService.getAllPlanSafeDocTrackings.

t.workflowId=null is not the correct JPQL null predicate.

Fix: use t.workflowId IS NULL, leaving projection fields and joins unchanged. Verify with one unsubmitted and one submitted record. This deliberately repairs result membership; it does not alter the DTO format.

P14 — Mapper collaborators are declared as imports and response/write directions are coupled

Evidence: UserMapper:15, generated UserMapperImpl_ inspected during this audit.

imports makes types available to generated expressions; it does not delegate nested mapping. The current generated user mapper creates its own nested conversion methods. Replacing imports with uses can activate decorated mappings and change fields/query counts. Broad ReportingPolicy.IGNORE also hides missing target handling.

Fix: first lock down current DTO fixtures, then introduce pure read mappers with explicit uses, a shared @MapperConfig, constructor injection and unmappedTargetPolicy = ERROR for migrated mappers. Supply preloaded enrichment via explicit inputs or immutable @Context. Keep repository access out of new mapping helpers. Separate create/update mapping; load entities in services and use narrowly configured @MappingTarget updates. Preserve existing null/empty behavior rather than setting a global null-ignore policy. Registration password encoding, timestamps and identity generation belong in a service/factory. Do not use enriched response DTOs as automatic inverse write definitions.

P15 — Projection fields and selected aliases do not always match

Evidence: InvoicePerformaRepository:31, InvoicePerformaProjection:17.

getPerformaInvoiceByDraftId omits perItemCost, while the same projection's parent-history query selects it. Additional candidate omissions occur in dashboard monthly distributions, shared workflow/application projections, scheme history and document summaries. Shared projection supersets may intentionally leave some properties null; a textual alias mismatch is not automatically a defect.

Fix: verify each consumer before adding values; use smaller internal projections per query and map into the existing external response shape. Add native-query alias/type tests against Oracle. Preserve intentional nulls; filling a previously null field is a behavior change even with identical JSON keys.

P16 — Examination report row mapper reads the wrong column

Evidence: row mapper:23, producer SQL:95.

The SQL aliases ERVEN as survitudePlan; the mapper reads SURVEYRECORD for both the servitude and survey-record fields. This duplicates the wrong value.

Fix: read the existing SURVITUDEPLAN alias for that property. Separately confirm whether the business source should be ERVEN; do not rename the public property/CSV column during this fix. Test with different servitude and survey-record values.

P17 — JDBC row mapping loses null information and can dereference null timestamps

Evidence: ReservationOutcomeRowMapper:22, SgdataParcelsRowMapper.

getLong/getInt yield zero for SQL NULL unless wasNull is checked. The reservation mapper also calls toLocalDateTime() on issue/expiry timestamps without checking null. Whether affected columns can actually be null needs database confirmation. No repository access was found in the 43 row mappers themselves, so these are correctness/compatibility risks rather than direct N+1 generators.

Fix: use typed nullable getObject(..., Long.class) or wasNull, plus null-safe timestamp conversion, only where the contract expects null. Do not silently change established zero/default report values. Test null and non-null JDBC fixtures and compare serialized output.

P18 — Date predicates and month sorting need query-specific correction

Evidence: UserLeaveCalenderRepository:46, VwWorkflowProductivityRepository:128; similar monthly ordering in archive/examination dashboards.

Date-to-string-to-date predicates depend on formatting and can inhibit ordinary index access. Sorting by numeric MMYY puts January 2026 before December 2025 in an ascending cross-year series.

Fix: express date-only comparisons as equivalent bound ranges after confirming DATE/TIMESTAMP/timezone semantics; obtain Oracle plans before adding indexes. Sort monthly series by TRUNC(date,'MM') while retaining the existing display month and response aliases. Do not change inclusive end-date semantics casually. Test year boundaries, leap dates, midnight and end-of-day values.

P19 — Cart writes force a flush per child and map the same DTO twice

Evidence: CartService:132.

addToFinalCart maps twice, flushes the root, then saves and flushes every child before querying children again. This prevents effective batching and repeats mapping work.

Fix: map once and persist the aggregate in one service transaction, or explicitly save child batches after the parent ID is available. Preserve current association ownership, cart response content and invoice generation. Establish whether the intermediate flush is needed for constraints before removing it. saveAll alone does not guarantee JDBC batching. Test rollback after a child/invoice failure.

Package-wide follow-up controls

  • Views: all five files are scalar entity mappings. Verify that each @Id uniquely identifies a view row using live duplicate counts, especially role-expanded user views. Use read-only query transactions/projections; consider @Immutable only after establishing that no consumer writes through the view. Missing immutability is not proof of an existing erroneous write.
  • Bulk updates/deletes: screen every @Modifying consumer for a service transaction and for reading already-managed entities afterwards. Refresh/clear at an appropriate boundary only where needed; blanket clearAutomatically can detach pending work. Prefer grouped operations to per-row calls while preserving cascade/audit semantics.
  • Concurrency: no @Version or @Lock declarations were found in this persistence tree. Assess actual competing workflow/status writers and database-side safeguards before adding optimistic locking columns or changing write predicates.
  • Unbounded collections: several user searches, task queues, reports and exports return collections. First batch enrichments and process background workloads in chunks. Keep existing collection response contracts; adding a new page envelope, silently capping results or dropping fields requires a separate compatibility decision. Returning all rows still has output-size costs even after query optimization.
  • Maintenance: retain the existing targeted entity graphs, @BatchSize annotations and lazy-aware mapping. Their presence is a useful baseline, not proof that every caller has a suitable fetch plan.
  • Scalar repositories/projections: dispatch/document/error/fee/list-management/system and many examination/archive/workflow reads do not have association-based N+1 merely because they use repositories or projections. Repeated calls in a consumer, broad entity reads, indexes and native-query execution plans must be assessed separately. Do not add fetch joins to scalar projection queries.
  • Fetch batching: hibernate.jdbc.batch_size=100 batches compatible writes, not arbitrary read lookups. Association batching can help navigational reads but cannot replace P01/P04's explicit per-row repository calls.
  • Indexes: obtain current DDL, row counts and DBMS_XPLAN for the selected slow query before proposing a concrete index. Candidate families are parent foreign keys and combined workflow/user/status predicates; index order and selectivity require live evidence. No migrations or data changes were made.

Current persistence compiler warnings

A forced offline compilation of cis-api and its main-source dependencies with -Xlint:deprecation,unchecked -Xmaxwarns 10000 passed after the fixes. The persistence warning inventory records nine distinct file/line/message diagnostics emitted within the persistence tree:

Location Distinct diagnostics Fix approach
DataViewerCustomImpl 6 Replace raw query/result types with explicit typed boundaries; replace deprecated Hibernate result transformation while preserving column aliases, value types and response maps (P09).
SiteMapRepository / SiteRoleRepository 3 These still reference deprecated SiteRole. Trace active callers and its replacement before migration; retain compatibility while callers remain. Do not delete or suppress merely to obtain zero warnings.

This is a compiler-emission inventory for those flags, not every IDE inspection or -Xlint:all category. Remaining MapStruct warnings are in mapper packages outside persistence; review generated IDs/backreferences separately from actual response-field omissions. The existing broader compiler-warning-audit.md describes an older snapshot and was not overwritten.

Contract-preserving implementation order

  1. Keep the compilation fixes and their three focused tests as the first reviewable slice.
  2. Add golden response fixtures for user lists, assistant lists, draft lists/details and scheme counts. Cover null versus empty, order, enum/date values, envelopes and pagination headers.
  3. Instrument representative Oracle-backed reads using the existing JPA_GENERATE_STATISTICS switch. Measure prepared statements, collection fetches, transferred rows and elapsed time with cold persistence contexts and production-like cache settings. Avoid logging credential bind values.
  4. Fix scalar correctness separately: P06/P13/P16, with native-query/JDBC fixtures. Confirm intended result changes in P07/P08/P15/P18.
  5. Introduce the batched read assembler and pure MapStruct context for P01/P02/P03/P11. Assert mapping alone performs zero repository calls and total read counts grow by batches, not one lookup per result.
  6. Replace scheme/document entity counting with grouped aggregates (P04), then split detail fetch plans (P05/P10). Recheck JSON and row amplification after each change.
  7. Optimize background/calendar/cart workloads (P12/P19), then address numbering and data-viewer constraints with their database/feature contracts explicitly defined.

For query-count regressions, compare one item with twenty items, shared and distinct references, empty pages, missing optional data and multiple children. Account for legitimate count queries and batch boundaries rather than prescribing one universal SQL count. Oracle startup/integration tests remain necessary: compilation does not validate native SQL, repository initialization, schema mappings or production performance.

Framework references

  • MapStruct 1.6.3 reference: uses, mapping hooks, context, update/null semantics and constructor injection.
  • Hibernate ORM guide: fetch plans, association batching, JDBC batching and query/result transformation. Check the resolved Hibernate version before adopting version-specific APIs.

Coverage by persistence area

Counts include nested folders; historical Lodgement/lodgment spellings are grouped here. These are screening coverage and follow-up directions, not runtime pass statuses.

Area Domains Repositories Projections Row mappers Views Follow-up
(root/shared) 16 21 13 11 5 User/role/province loading, assistant lookups, help attribution, view identity (P01/P02/P07/P10).
archive 11 12 15 8 0 Packet numbering, Plan Safe predicate, scalar DTO and named-native result mappings (P08/P13).
cart 23 7 1 0 0 Eager children, repeated mapping/flushing and fee enrichment (P10/P11/P19).
dashboard 0 4 36 0 0 Scalar aggregates/projections; month ordering and alias consistency (P15/P18).
dataviewer 3 5 1 0 0 Custom SQL, whole-object counts, raw result types and export materialization (P09).
dispatch 1 1 0 0 0 Scalar domain/repository screening; inspect caller result sizes before changing fetch behavior.
document 1 1 0 0 0 Scalar domain/repository screening; no association-fetch remedy inferred.
error 2 1 0 0 0 Scalar reference data screening; no association-fetch remedy inferred.
examination 27 25 10 6 0 Notes/caption enrichment, projection aliases and JDBC report columns (P11/P15/P16).
fee 5 5 1 0 0 Scalar pricing repositories; optimize repeated complete pricing keys in consumers (P11).
invoice 1 1 0 0 0 To-one loading and omitted unit-cost projection field (P10/P15).
listmanagement 4 4 0 0 0 Batch reference captions; preserve administrator-managed values (P01/P11).
lodgement 29 31 26 6 0 Draft/step graphs, scheme counts, user/province lookups and nullable outcome mapping (P03-P05/P17).
logo 1 0 0 0 0 Scalar model screening; no association-fetch remedy inferred.
maintenance 9 9 3 5 0 Targeted graphs/batching already exist; default-eager references and lazy-aware response parity (P10).
notification 1 0 0 0 0 Scalar model screening; bound background consumption rather than changing response format.
prepackage 4 4 6 0 0 MAX+1 boundary allocation; scalar subscription/query result-size review (P08).
province 2 2 0 0 0 Reference/address loading and repeated province lookup batching (P10/P11).
report 6 3 42 0 0 Per-province labels, per-action/day queries, scalar report projections (P11/P12).
reservation 16 11 6 7 0 Draft graphs, short-list enrichment, JDBC nullable outcomes and numbering scope (P03/P05/P08/P17).
sitemap 3 0 0 0 0 Eager role links and deprecated SiteRole references (P10; compiler inventory).
system 2 2 0 0 0 Scalar configuration/log screening; no association-fetch remedy inferred.
user 12 11 0 0 0 Leave count queries, role/metadata loading, global latest login and null/date semantics (P01/P06/P07/P18).
workflow 14 10 8 0 0 Scalar native projection counts/aliases, notes enrichment and productivity consumers (P11/P12/P15).