====== 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_reference_check_script]].//

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

----

===== Management summary =====

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.

----

===== User guide =====

==== Where to find it ====

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).

==== Parameters ====

^ Parameter (code) ^ Label in the UI ^ Type ^ Default ^ Meaning ^
| ''checkDirect'' | Direct references | boolean | ''true'' | Check direct references: ''@ManyToOne'' / ''@OneToOne'' and JOINED-inheritance links. |
| ''checkPolymorphic'' | Polymorphic references | boolean | ''true'' | Check polymorphic references: a ''*_id'' column paired with a sibling ''*_type'' column (e.g. ''owner_id'' + ''owner_type''). |
| ''included'' | Searched entities | textarea | //empty = all// | Whitelist. Comma- or newline-separated entity names (e.g. ''IdmIdentityContract'') or table names. Empty means every entity. |
| ''ignored'' | Ignored entities | textarea | ''IdmAudit'' | Blacklist, subtracted from the searched set. |
| ''deleteOrphans'' | Delete orphans (not null) | boolean | ''false'' | Allow the //delete// action (used when the reference column is NOT NULL). |
| ''setNullOrphans'' | Null orphans (nullable) | boolean | ''false'' | Allow the //set null// action (used when the reference column is nullable). |
| ''previewOnly'' | Preview only | boolean | ''true'' | Detect and report only; perform no change. **The task changes nothing until this is turned off.** |
| ''sampleSize'' | Sample id count | number | ''5'' | How many source record ids are listed per reference in the XLSX report. Item result messages always show at most 5. |

==== How the cleanup action is decided ====

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:

^ Model nullability ^ Database nullability ^ Action ^ Enabled by ^
| nullable | nullable | **set null** – clear the reference column | ''setNullOrphans'' |
| NOT NULL | NOT NULL | **delete** – delete the whole orphaned record | ''deleteOrphans'' |
| //any// | //differs from model// | **none** – item ends as EXCEPTION (mismatch) | – |

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.

==== Recommended workflow ====

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

==== Examples ====

**Example 1 – full preview (safest, recommended first run).**
<code>
checkDirect       = true
checkPolymorphic  = true
included          = (empty)
ignored           = IdmAudit
deleteOrphans     = false
setNullOrphans    = false
previewOnly       = true
</code>
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.**
<code>
included          = IdmIdentityContract
deleteOrphans     = false
setNullOrphans    = true
previewOnly       = false
</code>
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.**
<code>
deleteOrphans     = true
setNullOrphans    = true
previewOnly       = false
</code>
Performs both actions everywhere: nullable orphans are nulled, NOT-NULL orphans (whole records) are deleted. Use only after a reviewed preview and a backup.

==== Reading the results ====

  * **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.

----

===== Technical description =====

==== Reference discovery (from the JPA metamodel) ====

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.

==== SQL used ====

Orphans are detected with a storage-agnostic **anti-join** (no literal ids, no casts on the join):
<code sql>
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>);
</code>
Cleanup actions:
<code sql>
DELETE FROM <table> s WHERE <same where>;                       -- both NOT NULL
UPDATE <table> s SET <column> = NULL [, <type_column> = NULL]   -- both nullable
 WHERE <same where>;
</code>
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.

==== Transactions and side effects ====

  * 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//.

==== Result codes and item states ====

^ Result code (''CoreResultCode'') ^ State ^ When ^
| // (no model) // | EXECUTED | reference is clean (0 orphans) |
| ''LONG_RUNNING_TASK_ORPHANED_REFERENCES_RESOLVED'' | EXECUTED | action performed (preview off) |
| ''LONG_RUNNING_TASK_ORPHANED_REFERENCES_WOULD_RESOLVE'' | NOT_EXECUTED | preview – action would run |
| ''LONG_RUNNING_TASK_ORPHANED_REFERENCES_BLOCKED'' | BLOCKED | orphans found, action disabled |
| ''LONG_RUNNING_TASK_ORPHANED_REFERENCES_MISMATCH'' | EXCEPTION | model vs database nullability mismatch |
| ''LONG_RUNNING_TASK_ORPHANED_REFERENCES_CHECK_FAILED'' | EXCEPTION | the check/action threw an error |
| ''LONG_RUNNING_TASK_ORPHANED_REFERENCES_SUMMARY'' | (task result, 206) | final summary + report download link |

==== Limitations ====

  * 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).

==== Extending ====

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_reference_check_script]] – the standalone Groovy script that provides the same detection as a downloadable SQL preview and runs on IdM versions without this task.//