Native PostgreSQL Schema Script: postgres.sql - Infrastructure

Last modified 13 Jul 2026 09:30 UTC
Table of Contents

Back to native PostgreSQL schema overview

Back to postgres.sql documentation

This page documents schema objects parsed from config/sql/native/postgres.sql.

Region: Infrastructure

Schema, extensions, enum types, shared identifiers, repository metadata, and support routines.

Tables

m_assignment_holder

Abstract base table for assignment-holding objects, excluding shadows.

Represents AssignmentHolderType (all objects except shadows) extending m_object, but still abstract, hence the CHECK (false).

No indexes here, always add indexes and referential constraints on concrete sub-tables.

XSD type: http://midpoint.evolveum.com/xml/ns/public/common/common-3#AssignmentHolderType

Inherits from: m_object

No primary key is defined directly on this table. The table inherits from m_object.

Columns

Column Type Required / constraints Description

objectType

ObjectType

required

m_container

Abstract base table for separately persisted container values.

Purely abstract table (no entries are allowed). Represents Containerable/PrismContainerValue. Allows querying all separately persisted containers, but not necessary for the application.

No primary key is defined for this table.

Columns

Column Type Required / constraints Description

ownerOid

UUID

required

OID of the owning object row.

Default OID value is covered by INSERT triggers. No PK defined on abstract tables. Owner does not have to be the direct parent of the container.

cid

BIGINT

required

Container identifier unique within the owning object.

Container ID, unique in the scope of the whole object (owner). While this provides it for sub-tables we will repeat this for clarity, it’s part of PK.

m_object

Abstract base table containing common fields inherited by all repository object tables.

XSD type: http://midpoint.evolveum.com/xml/ns/public/common/common-3#ObjectType

No primary key is defined for this table.

Columns

Column Type Required / constraints Description

oid

UUID

required

Object identifier inherited by concrete object tables.

nameOrig

TEXT

required

Original object name as entered or displayed.

nameNorm

TEXT

required

Normalized object name used for exact case-insensitive lookup and uniqueness.

fullObject

BYTEA

Serialized full object representation, however some items are stored separately.

See Splitted full object for more information.

tenantRefTargetOid

UUID

OID of the tenant reference target object.

tenantRefTargetType

ObjectType

Object type of the tenant reference target.

tenantRefRelationId

INTEGER

Relation URI identifier of the tenant reference.

lifecycleState

TEXT

cidSeq

BIGINT

required, default 1

Next container identifier value allocated within this object.

version

INTEGER

required, default 1

Object version used for optimistic locking.

policySituations

INTEGER[]

Policy situation URI identifiers attached to the object.

subtypes

TEXT[]

Object subtype values used for subtype filtering.

fullTextInfo

TEXT

Text search helper content derived from selected object data.

ext

JSONB

Indexed extension and attribute values stored as JSON data.

Extension items are stored as JSON key:value pairs, where key is m_ext_item.id (as string) and values are stored as follows (this is internal and has no effect on how query is written):
* string and boolean are stored as-is
* any numeric type integral/float/precise is stored as NUMERIC (JSONB can store that)
* enum as toString() or name() of the Java enum instance
* date-time as Instant.toString() ISO-8601 long date-timeZ (UTC), cut to 3 fraction digits
* poly-string is stored as sub-object {"o":"orig-value","n":"norm-value"}
* reference is stored as sub-object {"o":"oid","t":"targetType","r":"relationId"}
** where targetType is ObjectType and relationId is from m_uri.id, just like for ref columns

creatorRefTargetOid

UUID

OID of the object that created this object.

creatorRefTargetType

ObjectType

Object type of the object that created this object.

creatorRefRelationId

INTEGER

Relation URI identifier for the creator reference.

createChannelId

INTEGER

URI identifier of the channel that created the object.

createTimestamp

TIMESTAMPTZ

Time when the object was created.

modifierRefTargetOid

UUID

OID of the object that last modified this object.

modifierRefTargetType

ObjectType

Object type of the object that last modified this object.

modifierRefRelationId

INTEGER

Relation URI identifier for the modifier reference.

modifyChannelId

INTEGER

URI identifier of the channel that last modified the object.

modifyTimestamp

TIMESTAMPTZ

Time when the object was last modified.

db_created

TIMESTAMPTZ

required, default current_timestamp

Database timestamp when this row was created.

db_modified

TIMESTAMPTZ

required, default current_timestamp

Database timestamp when this row was last modified.

Foreign keys

Column References

tenantRefRelationId

m_uri.id

creatorRefRelationId

m_uri.id

createChannelId

m_uri.id

modifierRefRelationId

m_uri.id

modifyChannelId

m_uri.id

m_object_oid

Provides generated object identifiers shared by all concrete object tables.

Columns

Column Type Required / constraints Description

oid

UUID

primary key, required, default gen_random_uuid ()

Generated repository object identifier referenced by concrete object rows.

m_reference

Abstract base table for object and container references.

No primary key is defined for this table.

Columns

Column Type Required / constraints Description

ownerOid

UUID

required

OID of the object that owns the reference.

ownerType

ObjectType

required

Object type of the reference owner.

targetOid

UUID

required

OID of the referenced target object.

targetType

ObjectType

required

Object type of the referenced target object.

relationId

INTEGER

required

Relation URI identifier of the reference.

Foreign keys

Column References Notes

ownerOid

m_object_oid.oid

ON DELETE CASCADE

relationId

m_uri.id

-

m_uri

Stores frequently used URI values, such as channels and relation QNames, as compact numeric identifiers.

Catalog of often used URIs, typically channels and relation Q-names. Never update values of "uri" manually to change URI for some objects (unless you really want to migrate old URI to a new one). URI can be anything, for QNames the format is based on QNameUtil ("prefix-url#localPart").

Columns

Column Type Required / constraints Description

id

SERIAL

primary key, required

Numeric URI identifier referenced by repository tables.

uri

TEXT

required

URI text value.

Enum types

Enum types define allowed database values for selected columns.

Enum type Description Values

AccessCertificationCampaignStateType

Stores access certification campaign states used by certification campaign tables.

CREATED, IN_REVIEW_STAGE, REVIEW_STAGE_DONE, IN_REMEDIATION, CLOSED

ActivationStatusType

Stores activation administrative status values used by focus, assignment, and resource data.

ENABLED, DISABLED, ARCHIVED

AdministrativeAvailabilityStatusType

Stores administrative availability values used by resource search data.

MAINTENANCE, OPERATIONAL

AvailabilityStatusType

Stores availability status values used for operational resource and connector state.

DOWN, UP, BROKEN

ContainerType

Identifies separately stored container table categories in the native repository.

ACCESS_CERTIFICATION_CASE, ACCESS_CERTIFICATION_WORK_ITEM, AFFECTED_OBJECTS, ASSIGNMENT, ASSIGNMENT_METADATA, CASE_WORK_ITEM, CLUSTER_DETECTED_PATTERN, FOCUS_IDENTITY, INDUCEMENT, LOOKUP_TABLE_ROW, OPERATION_EXECUTION, OUTLIER_PARTITION, SIMULATION_RESULT_PROCESSED_OBJECT, TRIGGER

CorrelationSituationType

Stores correlation situation values used by shadow identity matching.

UNCERTAIN, EXISTING_OWNER, NO_OWNER, ERROR

ExecutionModeType

Stores execution mode values used by tasks, simulations, and repository-side reporting.

FULL, PREVIEW, SHADOW_MANAGEMENT_PREVIEW, DRY_RUN, NONE, BUCKET_ANALYSIS

ExtItemCardinality

Identifies whether an indexed extension item stores a single value or an array of values.

SCALAR, ARRAY

ExtItemHolderType

Identifies whether an extension item belongs to object extension data or resource attributes.

EXTENSION, ATTRIBUTES

LockoutStatusType

Stores lockout status values used by focus activation data.

NORMAL, LOCKED

NodeOperationalStateType

Stores node operational state values used by node repository objects.

UP, DOWN, STARTING

ObjectType

Identifies midPoint object kinds stored in native repository object tables.

ABSTRACT_ROLE, ACCESS_CERTIFICATION_CAMPAIGN, ACCESS_CERTIFICATION_DEFINITION, APPLICATION, ARCHETYPE, ASSIGNMENT_HOLDER, CASE, CONNECTOR, CONNECTOR_DEVELOPMENT, CONNECTOR_HOST, DASHBOARD, FOCUS, FORM, FUNCTION_LIBRARY, GENERIC_OBJECT, LOOKUP_TABLE, MARK, MESSAGE_TEMPLATE, NODE, OBJECT, OBJECT_COLLECTION, OBJECT_TEMPLATE, ORG, POLICY, REPORT, REPORT_DATA, RESOURCE, ROLE, ROLE_ANALYSIS_CLUSTER, ROLE_ANALYSIS_SESSION, ROLE_ANALYSIS_OUTLIER, SCHEMA, SECURITY_POLICY, SEQUENCE, SERVICE, SHADOW, SIMULATION_RESULT, SYSTEM_CONFIGURATION, TASK, USER, VALUE_POLICY

OperationExecutionRecordTypeType

Stores operation execution record type values used by operation execution containers.

SIMPLE, COMPLEX

OperationResultStatusType

Stores operation result status values used by tasks, operation executions, and audit-related data.

SUCCESS, WARNING, PARTIAL_ERROR, FATAL_ERROR, HANDLED_ERROR, NOT_APPLICABLE, IN_PROGRESS, UNKNOWN

OrientationType

Stores orientation values for report output and presentation metadata.

PORTRAIT, LANDSCAPE

PredefinedConfigurationType

Stores predefined configuration profile values.

PRODUCTION, DEVELOPMENT

ReferenceType

Identifies reference table categories used for object and container references.

ARCHETYPE, ASSIGNMENT_CREATE_APPROVER, ASSIGNMENT_MODIFY_APPROVER, ASSIGNMENT_EFFECTIVE_MARK, ACCESS_CERT_WI_ASSIGNEE, ACCESS_CERT_WI_CANDIDATE, CASE_WI_ASSIGNEE, CASE_WI_CANDIDATE, DELEGATED, INCLUDE, OBJECT_CREATE_APPROVER, OBJECT_EFFECTIVE_MARK, OBJECT_MODIFY_APPROVER, OBJECT_PARENT_ORG, PERSONA, PROCESSED_OBJECT_EVENT_MARK, PROJECTION, RESOURCE_BUSINESS_CONFIGURATION_APPROVER, ROLE_MEMBERSHIP, TASK_AFFECTED_OBJECT

ResourceAdministrativeStateType

Stores administrative state values used by resource objects.

ENABLED, DISABLED

ShadowKindType

Stores shadow kind values used to classify resource object shadows.

ACCOUNT, ENTITLEMENT, GENERIC, ASSOCIATION, UNKNOWN

SynchronizationSituationType

Stores synchronization situation values used by shadow lifecycle and synchronization processing.

DELETED, DISPUTED, LINKED, UNLINKED, UNMATCHED

TaskAutoScalingModeType

Stores task auto-scaling mode values.

DISABLED, DEFAULT

TaskBindingType

Stores task binding values describing whether task execution is tied to a specific node.

LOOSE, TIGHT

TaskExecutionStateType

Stores task execution state values.

RUNNING, RUNNABLE, WAITING, SUSPENDED, CLOSED

TaskRecurrenceType

Stores task recurrence values for single-run and recurring tasks.

SINGLE, RECURRING

TaskSchedulingStateType

Stores task scheduling state values.

READY, WAITING, SUSPENDED, CLOSED

TaskWaitingReasonType

Stores task waiting reason values.

OTHER_TASKS, OTHER

ThreadStopActionType

Stores task thread stop action values.

RESTART, RESCHEDULE, SUSPEND, CLOSE

TimeIntervalStatusType

Stores time interval status values.

BEFORE, IN, AFTER

Functions and procedures

Functions and procedures implement repository-side database behavior.

before_update_object

Prevents object OID changes and updates database-managed modification metadata.

Kind: function

delete_object_oid

Releases the shared OID row after a concrete object row is deleted.

Kind: function

insert_object_oid

Assigns a new OID to inserted object rows or reserves an explicitly provided OID.

Kind: function

Procedural blocks

Procedural blocks run repository-side setup logic that is documented as a whole.

do-block-1

Ensures UUID generation support by enabling pgcrypto when gen_random_uuid() is not already available.

Statement type: DO block

Used for: OID generation support

do-block-2

Adds the objectType discriminator column to m_object using PostgreSQL-version-specific generated-column behavior.

Statement type: DO block

Used for: PostgreSQL compatibility for inherited object type discriminator columns

do-block-3

Adds discriminator columns to m_reference and m_container using PostgreSQL-version-specific generated-column behavior.

Statement type: DO block

Used for: PostgreSQL compatibility for inherited reference and container discriminator columns

Extensions and schemas

Extensions and schemas are database infrastructure objects required by the repository schema.

Extension fuzzystrmatch

Enables fuzzy string matching functions used by repository search features.

Extension intarray

Enables indexing support for integer array columns used by repository search tables.

Extension pg_trgm

Enables trigram indexes used for efficient text search.

Schema CURRENT_USER

Creates the schema used by the native PostgreSQL repository objects for the current database user.

Was this page helpful?
YES NO
Thanks for your feedback