> For the complete documentation index, see [llms.txt](https://docs.openiam.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.openiam.com/administration-guide/reference/certification-tables.md). # Certification database tables Every table in the certification database and every field in the ephemeral preview store, so a report or a break-glass query can be written against the right one. ## What this is, and when you need it The twenty-three data tables of the separate `certification` database, plus `OPENIAM_VERSION`, grouped by the three layers they belong to; the three OpenSearch preview indexes and two Redis key families that hold a preview; and the migration that created each. Scan it when you are writing a report, a dashboard query or a break-glass diagnosis against certification data. All of these tables are created in 2026.6.1.0 unless noted, and every child table cascades on delete from its parent — which is why deleting a `CAMPAIGN` row destroys its whole evidence trail. What the objects mean is [The certification model](/administration-guide/governance/certification/certification-model.md); running the service is [Operate certification services](/administration-guide/governance/certification/operate-certification-services.md). Two reporting rules follow from this inventory. First, an external report over `ACCESS_REVIEW_ITEM` alone reports only the *reviewed* slice — the excluded slice is `CAMPAIGN_PREVIEW_EXCLUSION` and it is a different table. Second, you rarely need to join out to `openiam`: the user and access snapshot columns on `ACCESS_REVIEW_ITEM` are exactly there so that a historical campaign reads correctly without one. ## Definition side | Table | Key | Holds | | ----------------------------------- | ------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `CERTIFICATION` | `ID` | The reusable definition: `NAME`, `STATUS`, `CERTIFICATION_TYPE`, `SCHEDULE_TYPE`, `START_DATE`, `USER_QUERYING_MODE`, `EXTENDED_SEARCH_ID`, `MAIL_TEMPLATE_ID`, `ALLOW_EXPIRING`, `ALLOW_EXPIRING_EXTENSION`, `MAX_EXTENSION_EXP_DAYS`, `ON_EXPIRE_ACTION`, `MIN_MEMBERSHIP_RISK`, `AUTO_CERTIFY_ENABLED` / `_MAX_SCORE` / `_VISIBILITY`, `ALLOW_BULK_APPROVAL`, `BETA_FEATURES_ENABLED` | | `CERTIFICATION_TARGET` | `(CERTIFICATION_ID, ENTITY_ID)` | The selected scope entity ids for a user certification | | `CERTIFICATION_GROUP` | `(CERTIFICATION_ID, GRP_ID)` | Groups selected into scope | | `CERTIFICATION_ROLE` | `(CERTIFICATION_ID, ROLE_ID)` | Roles selected into scope | | `CERTIFICATION_APPLICATION` | `(CERTIFICATION_ID, APPLICATION_ID)` | Applications selected into scope | | `CERTIFICATION_ROLE_EXCLUDE` | `(CERTIFICATION_ID, ROLE_ID)` | Roles excluded — the `ROLE_EXCLUDED` exclusion reason | | `CERTIFICATION_GROUP_EXCLUDE` | `(CERTIFICATION_ID, GRP_ID)` | Groups excluded — `GROUP_EXCLUDED` | | `CERTIFICATION_MANAGER` | `(CERTIFICATION_ID, USER_ID)` | **Manager of Access Review** — the recipients of the scope and complete reports | | `CERTIFICATION_TAG` | `(CERTIFICATION_ID, TAG_ID)` | Membership tags to exclude — `ENTITLEMENT_EXCLUDED_TAG` | | `CERTIFICATION_REVIEWER` | `ID` | One reviewer step: `REVIEWER_LEVEL`, `REVIEWER_ENTITY_TYPE`, `REVIEWER_ENTITY_ID`, `IS_MANDATORY`, `DAYS_BEFORE_REMINDER`, `NUMBER_OF_REMINDERS`, `DAYS_TO_COMPLETE`, `MAIL_TEMPLATE_ID` | | `CERTIFICATION_REVIEWER_ESCALATION` | `ID`, FK `REVIEWER_ID` | The escalation chain of one step: `ESCALATION_LEVEL`, `ESCALATION_ENTITY_TYPE`, `ESCALATION_ENTITY_ID`, `ESCALATION_DISPLAY_NAME`, plus the same four timing columns | ## Run side | Table | Key | Holds | | ---------------------------- | --------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `CAMPAIGN` | `ID`, FK `CERTIFICATION_ID` | One execution: `STATUS`, `EXECUTION_DATE`, `EXPECTED_COMPLETED_DATE`, `ACTUAL_COMPLETED_DATE`, `TOTAL_COUNT`, `COMPLETED_COUNT`, `NOT_STARTED_COUNT`, `WAS_EXTENDED`, `EXTENDABLE`, the per-run copies (`ALLOW_BULK_APPROVAL`, `AUTO_CERTIFY_*`, `BETA_FEATURES_ENABLED`), the launch snapshot (`PREVIEW_ID`, `LAUNCHED_BY`, `LAUNCHED_FROM_PREVIEW_AT`, `EXCLUDED_USER_COUNT`, `EXCLUDED_ACCESS_COUNT`, `REVIEWERS_NOT_RESOLVED_COUNT`) and `SIGNED_OFF_BY` / `SIGN_OFF_DATE` | | `CAMPAIGN_USER` | `(CAMPAIGN_ID, USER_ID)` | Users that participated in the run | | `CAMPAIGN_GROUP` | `(CAMPAIGN_ID, GRP_ID)` | Groups that participated | | `CAMPAIGN_ROLE` | `(CAMPAIGN_ID, ROLE_ID)` | Roles that participated | | `CAMPAIGN_MANAGER` | `(CAMPAIGN_ID, USER_ID)` | The run's campaign managers — report recipients | | `CAMPAIGN_STEP` | `ID`, FK `CAMPAIGN_ID` | One step of one run: `STEP_LEVEL`, `REVIEWER_NAME`, `REVIEWER_TYPE`, `TOTAL_COUNT`, `COMPLETED_COUNT`, `ADDITIONAL_INFO` | | `CAMPAIGN_REVIEWERS` | `(STEP_ID, REVIEWER_ID)` | The **resolved** human reviewers per step. The durable "who reviewed this" record; indexed by `IDX_CAMPAIGN_REV_STEP (STEP_ID)` and `IDX_CAMP_REV_REVIEWER (REVIEWER_ID)` | | `CAMPAIGN_PREVIEW_EXCLUSION` | `ID`, FK `CAMPAIGN_ID` | One row per launch exclusion: `GRAIN`, `CATEGORY`, `USER_ID`, `ENTITLEMENT_ID`, `ENTITLEMENT_TYPE`, `EXCLUSION_REASON`, `OFFENDING_ENTITY_ID` / `_TYPE` / `_NAME`, `DETAIL`. Added in 2026.6.2.0 | ## Item side | Table | Key | Holds | | ---------------------------------- | ------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `ACCESS_REVIEW_ITEM` | `ID`, FK `CAMPAIGN_ID` | One user by entitlement decision. `ROLE_ID` or `GRP_ID`; the user snapshot (`USER_FIRST_NAME`, `USER_LAST_NAME`, `USER_PRINCIPAL`, `USER_SUPERVISOR_ID` plus names, `USER_STATUS`, `USER_SECONDARY_STATUS`, `USER_MD_TYPE_ID`); the access snapshot (`ACCESS_NAME`, `ACCESS_DESCRIPTION`, `ACCESS_TYPE_ID`, `ACCESS_MANAGED_SYS_ID`, `ACCESS_TAG_IDS`, `ACCESS_RIGHT_IDS`, `XREF_DESCRIPTION`, `ACCESS_START_DATE`, `ACCESS_END_DATE_BEFORE`, `ACCESS_END_DATE_AFTER`); the decision (`ACCESS_REVOKED`, `ACCESS_REVOKED_BY`, `DECISION_DATE`, `REVIEW_OUTCOME`, `AUTO_CERTIFIED`, `HIDDEN_FROM_REVIEWER`, `ACCESS_COMMENT`, `REQUEST_COMMENT`); workflow position (`CURRENT_PROC_INST_ID`, `CURRENT_STEP_ID`, `CURRENT_STEP_REVIEWED`, `REVIEW_ROUTE`); risk (`RISK_SCORE`, `RISK_BAND`, `RISK_JUSTIFICATION`, `RISK_SUBJECT_TYPE`, `RISK_SUBJECT_ID`, `RISK_SCORE_AT_LAUNCH`, `RISK_BAND_AT_LAUNCH`, `RISK_CONTRIBUTIONS`, `RISK_CONTRIBUTIONS_AT_LAUNCH`, `RISK_LAST_COMPUTED`, `IDENTITY_RISK_SCORE_AT_LAUNCH`, `IDENTITY_RISK_BAND_AT_LAUNCH`); and `VERSION` | | `ACCESS_REVIEW_ITEM_CUR_REV` | `(ACCESS_REVIEW_ITEM_ID, CURRENT_REVIEWER)` | The element collection behind the item's current-reviewer set | | `ACCESS_REVIEW_ITEM_AUDIT` | `ID`, FK `ACCESS_REVIEW_ITEM_ID` | One row per reviewer action: `AUDIT_SPECIFICATION`, `REVIEWER_ID`, `REVIEWER_NAME`, `REVIEWER_EMAIL`, `REVIEWER_TYPE`, `REVIEW_DATE`, `REVIEW_NOTE`, `CAMPAIGN_STEP_ID`, `DECISION_SOURCE` | | `ACCESS_REVIEW_ITEM_SOD_VIOLATION` | `ID`, FK `ACCESS_REVIEW_ITEM_ID` | The SoD conflict shown to the reviewer: `SOD_POLICY_ID`, `SOD_POLICY_NAME`, `SOD_SEVERITY`, `RISK_NAME`, `CONFLICTING_ENTITLEMENT_ID` / `_NAME` / `_TYPE`, `POLICY_THRESHOLD`, `SEGMENT_ID`, `SEGMENT_NAME`, `SEGMENT_THRESHOLD`, `DIRECT_VIOLATION`, `IS_RESOLVED` — all `NOT NULL`, all snapshotted at build time | `CERTIFICATION_RISK_TYPES` existed in 2026.6.1.0 and was **dropped in 2026.7.1.0**. Its replacement `RISK_SCORE_THRESHOLD` (and `MIN_IDENTITY_RISK` on `CAMPAIGN`) was in turn **dropped in 2026.9.1.0**, when certifications stopped filtering by identity risk. Read `CERTIFICATION.MIN_MEMBERSHIP_RISK` instead — see [Access certification and risk](/administration-guide/risk/certification.md). ## The migration sequence The `certification` database is migrated independently of `openiam`, on its own version sequence, and it has its **own** `OPENIAM_VERSION` table, separate from the one in `openiam`. | Release | What it added | | ---------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 2026.6.1.0 | Creates the whole schema | | 2026.6.2.0 | `EXTENDED_SEARCH_ID`; `ALLOW_BULK_APPROVAL` and `ACCESS_REVIEW_ITEM_AUDIT.DECISION_SOURCE`; `CAMPAIGN_PREVIEW_EXCLUSION` and the launch-snapshot columns on `CAMPAIGN` | | 2026.7.1.0 | `RISK_SCORE_THRESHOLD` (dropped again in 2026.9.1.0); the `AUTO_CERTIFY_*` trio; `SIGNED_OFF_BY` / `SIGN_OFF_DATE`; the item risk snapshot; **drops `CERTIFICATION_RISK_TYPES`**; `BETA_FEATURES_ENABLED` | | 2026.7.2.0 | Performance indexes, `ACCESS_REVIEW_ITEM.VERSION`, and further risk columns | | 2026.8.1.0 | Version marker only | The 2026.7.2.0 migration builds four secondary indexes on `ACCESS_REVIEW_ITEM`, `ACCESS_REVIEW_ITEM_AUDIT` and `CAMPAIGN_REVIEWERS`, and drops the unused `IDX_CAMPAIGN_REVIEWER`. The index builds are online — they do not lock the tables — but on a large `ACCESS_REVIEW_ITEM` they take time and temporary disk proportional to table size. {% hint style="warning" %} **Break-glass: checking the certification schema version.** No Webconsole screen reports the `certification` schema's version, so the only way to answer the question is to read the table directly: {% code overflow="wrap" %} ```sql SELECT ID, VERSION FROM certification.OPENIAM_VERSION ORDER BY VERSION; ``` {% endcode %} A fully migrated 2026.8.1 install has rows through `2026.8.1`. This is outside the supported change path, but it is a read, so it risks nothing beyond bypassing the product's own access control and audit — no record of the query will appear anywhere. There is no supported alternative; if you cannot reach the database, ask your deployment owner to run it or raise it with support. {% endhint %} ## The preview store A preview writes nothing to the certification database. It lives in three OpenSearch indexes and two Redis keys, and all five are disposable — excluding them from your snapshots is correct. | Index | Holds | | -------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------- | | `certification_preview_metadata` | One document per preview: status, phase, counts, `startedAt`, `completedAt`, `validityHours`. Read **by id**, so it is realtime | | `certification_preview_user` | One document per previewed user | | `certification_preview_item` | One document per previewed **row** — both the included user-by-entitlement rows and the excluded rows, discriminated by `docType` (`INCLUDED` / `EXCLUDED`) | `certification_preview_item` is the one to know. Its keyword fields are `previewId`, `userId`, `entitlementId`, `entitlementType`, `docType`, `exclusionReasons`, `issueCategory` (`ACCESS_DROP` / `REVIEWER` / `USER_LEVEL`), `issueType` (an exclusion reason name, the same values the preview's exclusion tiles show), `offendingEntityType` / `_Id` / `_Name` and `detail`. Included rows additionally carry the per-entitlement snapshot that promotion copies straight onto `ACCESS_REVIEW_ITEM` (`accessDescription`, `accessMdTypeId`, `accessManagedSysId`, `tagsIds`, `accessRightIds`, `xrefDescription`, `accessStartDate`, `accessEndDateBefore`, `accessEndDateAfter`) plus `reviewerSteps` and `firstStepReviewerIds`. That is why launch is a read then a write with no recompute. | Redis key prefix | Holds | TTL | | ----------------------------------------------------------------------------------------------------------------- | ---------------------------------------------- | ----------------------------------------------------- | | `CERT_PREVIEW_ACTIVE:` | The single active preview id per certification | — (cleared explicitly) | | `CERT_PREVIEW_PROGRESS:` (prefix overridable with `org.openiam.certification.preview.progress.prefix`) | The live progress map and status | **48 h** safety net, re-applied on every status write | | `CERT_PREVIEW_LAUNCH:` | The launch-progress pointer | **48 h** safety net | {% hint style="info" %} **None of the three preview indexes appears on `/webconsole/reindex`**, and none can be rebuilt from there — see [Search indexes and the authorization graph](/administration-guide/reference/search-indexes.md) for the indexes that screen does list. OpenIAM creates each preview index on first write, with an explicit mapping. Deleting them is safe: the next preview recreates them. Losing them costs you the previews in flight, nothing else. {% endhint %} ## The legacy rename map Before certification moved to its own database the same data lived in `openiam`. The map matters if you are reconciling old exports against new ones. | Legacy table (`openiam`) | New table (`certification`) | | ------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `ACCESS_CERTIFICATION` | `CERTIFICATION` | | `ACCESS_CERTIFICATION_TARGET` / `_GROUP` / `_ROLE` / `_APPLICATION` / `_MANAGER` / `_TAG` / `_ROLE_EXCLUDE` / `_GROUP_EXCLUDE` | `CERTIFICATION_TARGET` / `_GROUP` / `_ROLE` / `_APPLICATION` / `_MANAGER` / `_TAG` / `_ROLE_EXCLUDE` / `_GROUP_EXCLUDE`, with `ACCESS_CERTIFICATION_ID` renamed to `CERTIFICATION_ID` | | `APPROVER_ASSOC` where `ASSOCIATION_TYPE = 'CERTIFICATION'` | `CERTIFICATION_REVIEWER`, with `APPROVER_*` renamed to `REVIEWER_*` and `ASSOCIATION_ENTITY_ID` to `CERTIFICATION_ID` | | `ESCALATION` (certification-typed parents only) | `CERTIFICATION_REVIEWER_ESCALATION`, with `ASSOCIATION_ID` renamed to `REVIEWER_ID` | | `ACCESS_CERTIFICATION_TAG` | `CERTIFICATION_TAG` | The user-facing snapshot columns on `ACCESS_REVIEW_ITEM` do not exist in the legacy schema and stay `NULL` for migrated rows, so a report over migrated campaigns shows blank user names. Fall back to the live `USERS` row for those campaigns, and accept that it reflects the user as they are now. ## Related * [The certification model](/administration-guide/governance/certification/certification-model.md) — what these objects mean, and how a campaign reads end to end * [Operate certification services](/administration-guide/governance/certification/operate-certification-services.md) — the service, the datasource and the retention position * [Diagnose a stalled campaign](/administration-guide/governance/certification/diagnose-a-stalled-campaign.md) — which of these tables to read when nothing is moving * [Search indexes and the authorization graph](/administration-guide/reference/search-indexes.md) — the reindexable indexes, which exclude the preview store *Applies to OpenIAM 2026.8.1. Last reviewed 4 September 2026.* --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://docs.openiam.com/administration-guide/reference/certification-tables.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.