# 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='<parent-uuid>'
  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:
  <tomcat_home>/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_<GROUP_NAME>
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_<PROP_NAME> | Value column per property (e.g. RGT_PRO_INPUT1)          |

---

### OKM_PGRP_HIS_<GROUP_NAME>
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_<PROP_NAME> | Value column per property                                        |

Query metadata history for a node UUID:
`SELECT * FROM OKM_PGRP_HIS_CONSULTING WHERE RGT_UUID='<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 = '<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'
```
