Check Orphaned References (Long Running Task)

Applies to: IdM 16.0 and newer (backported to 15). For IdM versions that do not ship this task, use the standalone script variant – see Orphaned References Check (Standalone Script).

Task name (code): core-check-orphaned-references-long-running-task
Bean / class: eu.bcvsolutions.idm.core.scheduler.task.impl.CheckOrphanedReferencesTaskExecutor

An orphaned reference is a value in one record that points to another record which no longer exists – for example a contract whose identity_id refers to an identity that was deleted directly in the database, or a provisioning archive that points to a removed system.

In CzechIdM this situation is possible by design. The product deliberately does not create business foreign keys in the database (relations are mapped with @ForeignKey(ConstraintMode.NO_CONSTRAINT)), so referential integrity is enforced by the application layer (processors, events), not by the database engine. Whenever data is changed outside the application – manual SQL fixes, failed/partial imports, restores from a partial backup, an interrupted delete – dangling references can be left behind.

The Check Orphaned References task finds these dangling references automatically and, when explicitly allowed, cleans them up. It:

• discovers references dynamically from the JPA metamodel – every entity relation (current and future) is covered without changing the task,
• is safe by default – it runs in Preview only mode and changes nothing until an operator turns preview off and enables a cleanup action,
• produces a downloadable XLSX report and a per-reference audit trail (task items) of exactly what was found and done.

Typical uses: periodic data-quality checks, diagnosing “record not found” errors at load time, and cleaning data before an upgrade or a migration that will introduce real foreign keys.

Risk note. Cleanup uses native SQL and bypasses the application layer (processors, events, and the Envers audit trail). It is a low-level maintenance tool. Always run a preview first, review the report, and take a database backup before performing real changes.

Administration → Scheduled tasks → Add (or Run task), then pick Check orphaned references. It can be run once on demand or scheduled (e.g. a monthly preview whose report is e-mailed to administrators).

When orphaned records are found for a reference, the action is derived from the column nullability, and the JPA model and the database must agree:

For a polymorphic reference the set null action also clears the sibling *_type column (when it is nullable), so the row is left as a fully empty reference rather than a type without an id.

1. Preview. Run with defaults (previewOnly = true). Nothing is changed. Optionally narrow the scope with included.
2. Review. Open the task result, read the item states and download the XLSX report. Verify that the proposed deletes/nulls are what you expect.
3. Back up. Take a database backup (cleanup bypasses audit, so it is not reversible from within IdM).
4. Execute. Run again with previewOnly = false and enable deleteOrphans and/or setNullOrphans as needed.

Example 1 – full preview (safest, recommended first run).

checkDirect       = true
checkPolymorphic  = true
included          = (empty)
ignored           = IdmAudit
deleteOrphans     = false
setNullOrphans    = false
previewOnly       = true

Detects every orphaned reference in the whole model and writes the report; changes nothing. Items appear as EXECUTED (clean), NOT_EXECUTED (would be resolved) or BLOCKED (found, but no action enabled).

Example 2 – resolve nullable orphans on one entity.

included          = IdmIdentityContract
deleteOrphans     = false
setNullOrphans    = true
previewOnly       = false

Only IdmIdentityContract references are checked. Nullable orphaned columns are set to NULL; NOT-NULL orphans are reported as BLOCKED (delete is not enabled) so nothing is deleted.

Example 3 – delete NOT-NULL orphans across the model.

deleteOrphans     = true
setNullOrphans    = true
previewOnly       = false

Performs both actions everywhere: nullable orphans are nulled, NOT-NULL orphans (whole records) are deleted. Use only after a reviewed preview and a backup.

• Task items – one item per reference (a polymorphic reference produces one item per distinct *_type value found in the data). Item state:
  • EXECUTED – the reference is clean, or (with preview off) the action was performed,
  • NOT_EXECUTED – preview: the action would be performed,
  • BLOCKED – orphans found but the matching action is disabled,
  • EXCEPTION – model/database nullability mismatch, or the check failed.
• XLSX report – attached to the task result and downloadable from it (orphaned-references-report.xlsx). Columns: Entity, Attribute, Referenced entity, Action, State, Orphan count, Sample IDs. The report is produced only when at least one orphan or one failure was found.
• Summary – the task result message reports totals: references with orphans, deleted, nulled, blocked, mismatch, failed.

Discovery is implemented by DefaultOrphanedReferenceService (bean orphanedReferenceService). It scans EntityManager.getMetamodel() once and caches the result (the metamodel is fixed for the application runtime). For every entity with a physical @Table(name) it collects:

• Direct associations – singular @ManyToOne / @OneToOne attributes declared on the entity and on its @MappedSuperclass ancestors (those columns live in the entity table). The join column is taken from @JoinColumn(name) or, if absent, the JPA default (<attribute>_id, snake-cased).
• JOINED inheritance – when an entity’s supertype is itself an entity (JOINED strategy), a synthetic reference subtable.id → parenttable.id is added.
• Polymorphic pairs – a basic UUID column named *_id that has a sibling basic String column *_type with the same prefix. The concrete target table is resolved per distinct *_type value at processing time.

References are deduplicated by (table, column) (e.g. thin entities map the same table). The following are intentionally skipped (logged at debug/warn): attributes with @Formula (no physical column), inverse @OneToOne(mappedBy=…) sides, reversed @JoinColumn(referencedColumnName=…) mappings (e.g. the forest-index id link), targets without @Table(name), and any table/column that is not a plain SQL identifier.

Orphans are detected with a storage-agnostic anti-join (no literal ids, no casts on the join):

SELECT COUNT(*) FROM <table> s
 WHERE s.<column> IS NOT NULL
   [ AND s.<type_column> = :TYPE ]           -- polymorphic only
   AND NOT EXISTS (SELECT 1 FROM <target> t WHERE t.id = s.<column>);

Cleanup actions:

DELETE FROM <table> s WHERE <same where>;                       -- both NOT NULL
UPDATE <table> s SET <column> = NULL [, <type_column> = NULL]   -- both nullable
 WHERE <same where>;

Column nullability is read once from information_schema.columns for current_schema(). Sample ids for the report are aggregated with string_agg; UUID ids (stored as bytea) are rendered via encode(id,'hex') and formatted back to UUID, other id types (e.g. a bigint forest index) via CAST(id AS varchar).

• Database: PostgreSQL. The task uses PostgreSQL-specific SQL (information_schema, current_schema(), string_agg, encode). From IdM 16 PostgreSQL is the only supported database.

• Actions run through native SQL and therefore bypass processors, entity events and the Envers audit. The audit trail of the operation is the task’s item log plus the XLSX report.
• Each cleanup action runs in its own REQUIRES_NEW transaction, so that for a polymorphic reference with several types the failure of one type does not roll back the already-resolved sibling types.
• supportsDryRun() returns false on purpose – the built-in dry run would skip the per-item detection. The dedicated previewOnly parameter is used instead (and is on by default).
• continueOnException() is true – one failing reference does not stop the whole run; it is logged as an EXCEPTION item and counted in failed.

• PostgreSQL only (see above).
• Cleanup is not audited and not reversible from within IdM – back up first.
• Only hard references mapped in the JPA model (or polymorphic *_id/*_type pairs) are discovered. References held purely in text/JSON or in configuration values are not covered.
• A reference whose model and database nullability disagree is never auto-resolved – it is reported as a mismatch for manual inspection (usually a missing/oudated Flyway migration).

Because discovery is driven by the metamodel, new entities and relations are covered automatically. No task change is needed when a new @ManyToOne or a new polymorphic *_id/*_type pair is added, as long as the entity has a physical @Table(name) and plain SQL identifiers.

See also: Orphaned References Check (Standalone Script) – the standalone Groovy script that provides the same detection as a downloadable SQL preview and runs on IdM versions without this task.