# OpenKM 8.2 — Database Schema Reference # Compact reference for LLM consumption # Version: 8.2.5 # Source: JPA entities in com.openkm.db.bean + official Database description doc --- ## Overview OpenKM uses a joined-table inheritance hierarchy for repository nodes. All node types (Document, Folder, Mail, Record) share a common base table (OKM_NODE_BASE) identified by NBS_UUID. Each type extends it with a type-specific table joined on that same UUID. Node type discriminator values stored in NBS_TYPE: 1 = Folder, 2 = Document, 3 = Mail, 4 = Record, 5 = Link --- ## Category 1: Repository Node Tables ### OKM_NODE_BASE Entity: NodeBase | Base table for every node in the repository. | Column | Java field | Type | Notes | |---------------------------|------------------------|------------|----------------------------------------------------| | NBS_UUID (PK) | uuid | VARCHAR(64)| Unique node ID; FK to all child node tables | | NBS_PARENT | parent | VARCHAR(64)| Parent node UUID (NBS_UUID of parent) | | NBS_NAME | name | VARCHAR(256)| Node name (filename or folder name) | | NBS_CONTEXT | context | VARCHAR(32)| Repository context (e.g. /okm:root, /okm:trash) | | NBS_TENANT | tenant | BIGINT | Tenant ID (multi-tenant scoping) | | NBS_SHARD | shard | BIGINT | Shard ID for distributed storage | | NBS_AUTHOR | author | VARCHAR(64)| Username who created the node | | NBS_CREATED | created | TIMESTAMP | Creation date/time | | NBS_LAST_MODIFIED | lastModified | TIMESTAMP | Last modification date/time | | NBS_TITLE | title | VARCHAR(255)| Optional display title | | NBS_DESCRIPTION | description | VARCHAR(1024)| Optional description | | NBS_NOTES | notes | VARCHAR(2048)| Internal notes | | NBS_TYPE | (discriminator) | INTEGER | 1=Folder, 2=Document, 3=Mail, 4=Record, 5=Link | | NBS_LOCKED | locked | CHAR(1) | T/F — whether node is locked | | NBS_HAS_RELATIONS | hasRelations | CHAR(1) | T/F — whether node has relations | | NBS_PROMOTED_AS_RECORD | promotedAsRecord | CHAR(1) | T/F — document promoted to record | | NBS_MOVED_TO_TRASH | movedToTrash | TIMESTAMP | Date moved to trash, NULL if not in trash | | NBS_PARENT_TO_RESTORE | parentToRestore | VARCHAR(64)| Original parent UUID (for trash restore) | | NBS_TEMPLATE | template | VARCHAR(64)| Template UUID if node was created from a template | | NBS_FORCE_INDEX_REBUILD | forceIndexRebuild | CHAR(1) | T/F — force Lucene/ES reindex on next run | | NBS_NODE_CLASS | nodeClass | BIGINT | Node class ID (classification) | | NBS_DISPOSITION_CURRENT_STAGE | dispositionCurrentStage | BIGINT | Records management disposition stage | | NLK_OWNER | lock.owner | VARCHAR(64)| Embedded lock: username holding the lock | | NLK_CREATED | lock.created | TIMESTAMP | Embedded lock: when the lock was acquired | Indexes: (NBS_PARENT, NBS_NAME) unique, (NBS_TYPE, NBS_CONTEXT), (NBS_TENANT, NBS_UUID), (NBS_PARENT) --- ### OKM_NODE_PARENT Entity: NodeParent | Intermediate join table for container nodes (Folder, Mail, Record). No additional columns beyond the PK join on NBS_UUID from OKM_NODE_BASE. --- ### OKM_NODE_DOCUMENT Entity: NodeDocument | Document-specific fields. Joined to OKM_NODE_BASE on NBS_UUID. | Column | Java field | Type | Notes | |----------------------|------------------|-------------|----------------------------------------------------| | NBS_UUID (PK/FK) | uuid | VARCHAR(64) | FK to OKM_NODE_BASE.NBS_UUID | | NDC_LANGUAGE | language | VARCHAR(8) | ISO language code of the document | | NDC_MIME_TYPE | mimeType | VARCHAR(128)| MIME type (e.g. application/pdf) | | NDC_CHECKED_OUT | checkedOut | CHAR(1) | T/F — document is checked out for editing | | NDC_ENCRYPTION | encryption | CHAR(1) | T/F — document content is encrypted | | NDC_CIPHER_NAME | cipherName | VARCHAR | Cipher algorithm name when encryption=T | | NDC_SIGNED | signed | CHAR(1) | T/F — document has a digital signature | | NDC_TEXT_EXTRACTED | textExtracted | CHAR(1) | T=text extracted/indexed, F=pending extraction | | NDC_INDEXABLE | indexable | CHAR(1) | T/F — whether document should be indexed | | NDC_ANTIVIRUS_CHECKED| antivirusChecked | CHAR(1) | T/F — document has been scanned by antivirus | To reindex all documents: `UPDATE OKM_NODE_DOCUMENT SET NDC_TEXT_EXTRACTED='F'` Query all documents: `SELECT * FROM OKM_NODE_BASE nb INNER JOIN OKM_NODE_DOCUMENT nd ON nd.nbs_uuid = nb.nbs_uuid` --- ### OKM_NODE_FOLDER Entity: NodeFolder | Folder-specific table. Joined to OKM_NODE_BASE and OKM_NODE_PARENT. No additional columns beyond the inherited base fields. --- ### OKM_NODE_RECORD Entity: NodeRecord | Record-specific table. Joined to OKM_NODE_BASE and OKM_NODE_PARENT. No additional columns beyond the inherited base fields. --- ### OKM_NODE_MAIL Entity: NodeMail | Mail message node. Joined to OKM_NODE_BASE and OKM_NODE_PARENT. | Column | Java field | Type | Notes | |----------------------|-----------------|--------------|----------------------------------------------------| | NBS_UUID (PK/FK) | uuid | VARCHAR(64) | FK to OKM_NODE_BASE.NBS_UUID | | NML_SIZE | size | BIGINT | Message size in bytes | | NML_FROM | from | VARCHAR(255) | Sender email address | | NML_SENT_DATE | sentDate | TIMESTAMP | Date/time the message was sent | | NML_RECEIVED_DATE | receivedDate | TIMESTAMP | Date/time the message was received | | NML_SUBJECT | subject | VARCHAR(256) | Email subject line | | NML_MIME_TYPE | mimeType | VARCHAR(64) | MIME type of the message body | | NML_ORIGIN | origin | VARCHAR(8) | Import origin identifier | | NML_CHECKSUM | checksum | VARCHAR(64) | Content checksum for deduplication | | NML_ATTACHMENT | hasAttachments | CHAR(1) | T/F — message has attachments | | NML_TEXT_EXTRACTED | textExtracted | CHAR(1) | T/F — text has been extracted for full-text search | Related collection tables (one-to-many from OKM_NODE_MAIL): - OKM_NODE_MAIL_TO (NMT_NODE FK, NMT_TO VARCHAR) — recipient addresses - OKM_NODE_MAIL_CC (NMC_NODE FK, NMC_CC VARCHAR) — CC addresses - OKM_NODE_MAIL_BCC (NMB_NODE FK, NMB_BCC VARCHAR) — BCC addresses - OKM_NODE_MAIL_REPLY (NMR_NODE FK, NMR_REPLY VARCHAR) — reply-to addresses --- ## Category 2: Security Tables ### OKM_NODE_USER_PERMISSION Collection table from NodeBase | Stores per-user access permissions on nodes. | Column | Notes | |---------------|--------------------------------------------------------------------| | NUP_NODE (FK) | References OKM_NODE_BASE.NBS_UUID | | NUP_USER | VARCHAR(64) — username | | NUP_PERMISSION| INTEGER — bitmask: 1=Read, 2=Write, 4=Delete, 8=Security, 16=Download | Query children accessible by user 'okmAdmin' with role 'ROLE_USER' under a given parent: ```sql SELECT NB.NBS_UUID FROM OKM_NODE_BASE NB LEFT OUTER JOIN OKM_NODE_USER_PERMISSION UP ON NB.NBS_UUID=UP.NUP_NODE LEFT OUTER JOIN OKM_NODE_ROLE_PERMISSION RP ON NB.NBS_UUID=RP.NRP_NODE WHERE NB.NBS_PARENT='' AND ((UP.NUP_USER='okmAdmin' AND (UP.NUP_PERMISSION & 1)=1) OR ((RP.NRP_ROLE IN ('ROLE_USER')) AND (RP.NRP_PERMISSION & 1)=1)) GROUP BY NB.NBS_UUID ``` --- ### OKM_NODE_ROLE_PERMISSION Collection table from NodeBase | Stores per-role access permissions on nodes. | Column | Notes | |---------------|--------------------------------------------------------------------| | NRP_NODE (FK) | References OKM_NODE_BASE.NBS_UUID | | NRP_ROLE | VARCHAR(64) — role name (e.g. ROLE_USER, ROLE_ADMIN) | | NRP_PERMISSION| INTEGER — bitmask (same as NUP_PERMISSION above) | --- ## Category 3: Versioning ### OKM_NODE_VERSION Entity: NodeVersion | One row per version of a node. All version history is stored here. | Column | Java field | Type | Notes | |-------------|-------------|--------------|----------------------------------------------------------| | NV_ID (PK) | id | BIGINT | Sequence-generated surrogate key | | NV_PARENT | parent | VARCHAR(64) | FK to OKM_NODE_BASE.NBS_UUID (the document UUID) | | NV_NAME | name | VARCHAR(64) | Version label (e.g. "1.0", "1.1") | | NV_DOCVER | docVersion | VARCHAR(64) | Content version UUID — used to locate file in datastore | | NV_PREVIOUS | previous | BIGINT | ID of the previous version (NV_ID of prior row) | | NV_CREATED | created | TIMESTAMP | When this version was created | | NV_CURRENT | current | CHAR(1) | T/F — whether this is the current active version | | NV_AUTHOR | author | VARCHAR(64) | Username who created this version | | NV_SIZE | size | BIGINT | File size in bytes for this version | | NV_CHECKSUM | checksum | VARCHAR(64) | Content checksum | | NV_ACTION | action | VARCHAR(64) | Change type — see values below | | NV_PARAMS | params | VARCHAR(64) | Additional action parameters | | NV_COMMENT | comment | VARCHAR(2048)| Version comment entered by the user | | NV_ANNOTATION | annotation| CLOB | Rich annotation text for this version | NV_ACTION values: DOCUMENT_CREATED, FOLDER_CREATED, MAIL_CREATED, RECORD_CREATED, DOCUMENT_CHANGED, DOCUMENT_IMPORTED, FOLDER_IMPORTED, RECORD_IMPORTED, MAIL_IMPORTED, PROPERTY_GROUP_ADDED, PROPERTY_GROUP_REMOVED, PROPERTY_GROUP_CHANGED Datastore path from NV_DOCVER: given UUID f0d7f0a1-b337-4917-b592-56e163b81cde, file is at: /repository/datastore/f0/d7/f0/a1/f0d7f0a1-b337-4917-b592-56e163b81cde Query all versions of 'test.pdf': ```sql SELECT nv.* FROM OKM_NODE_DOCUMENT nd INNER JOIN OKM_NODE_VERSION nv ON nv.nv_parent = nd.nbs_uuid INNER JOIN OKM_NODE_BASE nb ON nd.nbs_uuid = nb.nbs_uuid WHERE nb.nbs_name = 'test.pdf' ``` --- ## Category 4: Node Annotations and Subscriptions ### OKM_NODE_NOTE Entity: NodeNote | User-written notes attached to any node. | Column | Java field | Type | Notes | |--------------|------------|-------------|------------------------------------------| | NNT_UUID (PK)| uuid | VARCHAR(64) | Note UUID | | NNT_PARENT | parent | VARCHAR(64) | FK to OKM_NODE_BASE.NBS_UUID | | NNT_AUTHOR | author | VARCHAR(64) | Username who wrote the note | | NNT_CREATED | created | TIMESTAMP | When the note was created | | NNT_TEXT | text | CLOB | Note content | --- ### OKM_NODE_SUBSCRIPTOR Collection table from NodeBase | Tracks which users are subscribed to change notifications for a node. | Column | Notes | |---------------|------------------------------------------------| | NSB_NODE (FK) | References OKM_NODE_BASE.NBS_UUID | | NSB_SUBSCRIPTOR | VARCHAR — username subscribed to this node | --- ### OKM_NODE_KEYWORD Collection table from NodeBase | Keywords (tags) assigned to nodes. | Column | Notes | |---------------|------------------------------------------------| | NKW_NODE (FK) | References OKM_NODE_BASE.NBS_UUID | | NKW_KEYWORD | VARCHAR — keyword value | Query keywords for 'test.pdf': `SELECT nk.* FROM OKM_NODE_BASE nb INNER JOIN OKM_NODE_KEYWORD nk ON nk.nkw_node = nb.nbs_uuid WHERE nb.nbs_name = 'test.pdf'` --- ### OKM_NODE_CATEGORY Collection table from NodeBase | Categories assigned to nodes (categories are themselves nodes in the repository). | Column | Notes | |-----------------|---------------------------------------------------------| | NCT_NODE (FK) | References OKM_NODE_BASE.NBS_UUID — the categorized node| | NCT_CATEGORY | VARCHAR — UUID of the category node | --- ### OKM_NODE_CLASS_CHILDREN Collection table from NodeBase | Tracks which node classes a node belongs to (classification hierarchy). | Column | Notes | |--------------|--------------------------------------------------| | NCC_NODE (FK)| References OKM_NODE_BASE.NBS_UUID | | NCC_CHILDREN | BIGINT — node class ID | --- ## Category 5: Node Relations ### OKM_NODE_RELATION_TYPE Entity: NodeRelationType | Defines typed relationships between nodes. | Column | Java field | Type | Notes | |-------------------|-------------|--------------|-------------------------------------------------| | NRT_ID (PK) | id | BIGINT | Sequence-generated | | NRT_TITLE | title | VARCHAR(255) | Display name of the relation type | | NRT_TITLE_A_TO_B | titleAToB | VARCHAR(255) | Label for direction A→B | | NRT_TITLE_B_TO_A | titleBToA | VARCHAR(255) | Label for direction B→A | | NRT_TYPE | type | VARCHAR(255) | Semantics: bidirectional, parent-child, many-to-many | | NRT_TENANT | tenant | BIGINT | Tenant scoping | --- ### OKM_NODE_RELATION Entity: NodeRelation | Instances of typed relationships between two nodes. | Column | Java field | Type | Notes | |--------------|------------|-------------|----------------------------------------------------| | NR_ID (PK) | id | BIGINT | Sequence-generated | | NR_RELATION | relation | BIGINT (FK) | FK to OKM_NODE_RELATION_TYPE.NRT_ID | | NR_NODE_A | nodeA | VARCHAR(64) | UUID of the first node (references OKM_NODE_BASE) | | NR_TYPE_A | typeA | VARCHAR(64) | Node type descriptor for node A | | NR_NODE_B | nodeB | VARCHAR(64) | UUID of the second node (references OKM_NODE_BASE) | | NR_TYPE_B | typeB | VARCHAR(64) | Node type descriptor for node B | | NR_AUTHOR | author | VARCHAR(64) | Username who created the relation | --- ## Category 6: Metadata (Property Groups) ### OKM_REG_PROPERTY_GROUP Entity: RegisteredPropertyGroup | Registry of active metadata group schemas per tenant. | Column | Java field | Type | Notes | |--------------|------------|-------------|---------------------------------------------------| | RPG_ID (PK) | id | BIGINT | Sequence-generated | | RPG_NAME | name | VARCHAR(64) | Group name as defined in XML (e.g. okg:consulting)| | RPG_TENANT | tenant | BIGINT | Tenant scoping | | RPG_PGID | pgDef | BIGINT | Reference to property group definition | --- ### OKM_REG_PROPERTY Collection table from RegisteredPropertyGroup | Maps property names to their types within a group. | Column | Notes | |---------------|-----------------------------------------------------------| | RPR_GROUP (FK)| References OKM_REG_PROPERTY_GROUP.RPG_ID | | RPR_NAME | VARCHAR(128) — property name (e.g. okp:consulting.input1) | | RPR_TYPE | VARCHAR(64) — property type (e.g. input, select, date) | --- ### OKM_PGRP_CUR_ Dynamic table | Stores current metadata values for a specific property group. Table name suffix is derived from the group name (e.g. OKM_PGRP_CUR_CONSULTING). | Column | Notes | |---------------------|----------------------------------------------------------| | RGT_UUID | FK to OKM_NODE_BASE.NBS_UUID — the node with metadata | | RGT_PRO_ | Value column per property (e.g. RGT_PRO_INPUT1) | --- ### OKM_PGRP_HIS_ Dynamic table | Stores historical metadata values (one row per version) for a property group. Table name suffix matches the current table (e.g. OKM_PGRP_HIS_CONSULTING). | Column | Notes | |---------------------|------------------------------------------------------------------| | RGT_UUID | FK to OKM_NODE_BASE.NBS_UUID | | RGT_VERSION | FK to OKM_NODE_VERSION.NV_ID — version this snapshot belongs to | | RGT_PRO_ | Value column per property | Query metadata history for a node UUID: `SELECT * FROM OKM_PGRP_HIS_CONSULTING WHERE RGT_UUID='' ORDER BY RGT_VERSION DESC` The row with the largest RGT_VERSION is the most recent metadata snapshot. --- ## Category 7: Users and Roles ### OKM_USER Entity: DbUser | Application users. | Column | Java field | Type | Notes | |----------------------|-----------------|-------------|-----------------------------------------| | USR_ID (PK) | id | VARCHAR(64) | Username (login ID) | | USR_NAME | name | VARCHAR(64) | Display name | | USR_PASSWORD | password | VARCHAR(64) | Hashed password | | USR_EMAIL | email | VARCHAR(64) | Email address | | USR_ACTIVE | active | CHAR(1) | T/F — account enabled | | USR_SECRET | secret | VARCHAR(64) | TOTP secret for 2FA | | USR_PASSWORD_CHANGED | passwordChanged | TIMESTAMP | Date password was last changed | | USR_CREATED | created | TIMESTAMP | Account creation date | | USR_LAST_LOGIN | lastLogin | TIMESTAMP | Last successful login date | --- ### OKM_ROLE Entity: DbRole | Application roles. | Column | Java field | Type | Notes | |-------------|------------|-------------|-------------------------------| | ROL_ID (PK) | id | VARCHAR(64) | Role name (e.g. ROLE_ADMIN) | | ROL_ACTIVE | active | CHAR(1) | T/F — role enabled | --- ### OKM_USER_ROLE Join table (DbUser <-> DbRole) | Associates users with roles. | Column | Notes | |------------|----------------------------------------| | UR_USER | FK to OKM_USER.USR_ID | | UR_ROLE | FK to OKM_ROLE.ROL_ID | --- ## Category 8: Activity Log ### OKM_ACTIVITY Entity: Activity | Audit trail of every user action in the system. | Column | Java field | Type | Notes | |-------------|------------|---------------|------------------------------------------------| | ACT_ID (PK) | id | BIGINT | Sequence-generated | | ACT_TENANT | tenant | BIGINT | Tenant scoping | | ACT_DATE | date | TIMESTAMP | Date/time of the action | | ACT_USER | user | VARCHAR(64) | Username who performed the action | | ACT_ACTION | action | VARCHAR(127) | Action code (e.g. GET_DOCUMENT, CREATE_FOLDER) | | ACT_ITEM | item | VARCHAR(64) | UUID of the affected node | | ACT_PATH | path | CLOB | Full path of the affected node | | ACT_PARAMS | params | CLOB | Additional action parameters (JSON/text) | | ACT_IP | ip | VARCHAR(64) | Client IP address | Indexes: (ACT_TENANT, ACT_DATE, ACT_ACTION), (ACT_TENANT, ACT_USER, ACT_ACTION), (ACT_TENANT), (ACT_DATE) --- ## Key Join Patterns Query a document by UUID: ```sql SELECT * FROM OKM_NODE_BASE nb INNER JOIN OKM_NODE_DOCUMENT nd ON nd.nbs_uuid = nb.nbs_uuid WHERE nb.nbs_uuid = '' ``` Query records whose name starts with 'test': ```sql SELECT * FROM OKM_NODE_BASE nb INNER JOIN OKM_NODE_RECORD nr ON nr.nbs_uuid = nb.nbs_uuid WHERE nb.nbs_name LIKE 'test%' ``` Query all versions of a document when its binary changed: ```sql SELECT nv.* FROM OKM_NODE_DOCUMENT nd INNER JOIN OKM_NODE_VERSION nv ON nv.nv_parent = nd.nbs_uuid INNER JOIN OKM_NODE_BASE nb ON nd.nbs_uuid = nb.nbs_uuid WHERE nb.nbs_name = 'test.pdf' AND nv.nv_action = 'DOCUMENT_CHANGED' ``` Query metadata changes for a document: ```sql SELECT nv.* FROM OKM_NODE_DOCUMENT nd INNER JOIN OKM_NODE_VERSION nv ON nv.nv_parent = nd.nbs_uuid INNER JOIN OKM_NODE_BASE nb ON nd.nbs_uuid = nb.nbs_uuid WHERE nb.nbs_name = 'test.pdf' AND nv.nv_action = 'PROPERTY_GROUP_CHANGED' ```