# Keycloak Entities

> Use for any work touching Keycloak's database — writing JPQL or SQL against Keycloak entities, designing migrations, building extensions, debugging FK/cascade issues, or mapping Keycloak's domain model to its schema. Covers the Keycloak schema, ER diagram, entity classes, and migrations across ~90 tables including USER_ENTITY, KEYCLOAK_ROLE, KEYCLOAK_GROUP, CLIENT, USER_ROLE_MAPPING, USER_GROUP_M…

- **Type:** Skill
- **Install:** `agentstack add skill-tide-foundation-keycloak-skills-keycloak-entities`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [tide-foundation](https://agentstack.voostack.com/s/tide-foundation)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [tide-foundation](https://github.com/tide-foundation)
- **Source:** https://github.com/tide-foundation/keycloak-skills/tree/main/skills/keycloak-entities

## Install

```sh
agentstack add skill-tide-foundation-keycloak-skills-keycloak-entities
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# Keycloak Entities — Reference Skill

Expert reference for Keycloak's JPA data model. The skill is structured so this file alone covers most needs (~80% of questions); read the reference files for deep dives.

Verified against Keycloak 26.5.5.

---

## Upstream Source (github.com/keycloak/keycloak @ 26.5.5)

Entity classes live in the upstream Keycloak repository on GitHub. The links below are **remote URLs**, not files in this working directory — fetch with `WebFetch` if you need to read them.

| Upstream location | What's there |
|---|---|
| [Core entities package](https://github.com/keycloak/keycloak/tree/26.5.5/model/jpa/src/main/java/org/keycloak/models/jpa/entities) | realm, user, client, role, group, scopes, mappings |
| [Federated user entities](https://github.com/keycloak/keycloak/tree/26.5.5/model/jpa/src/main/java/org/keycloak/storage/jpa/entity) | `FederatedUser*` classes |
| [Authorization Services entities](https://github.com/keycloak/keycloak/tree/26.5.5/model/jpa/src/main/java/org/keycloak/authorization/jpa/entities) | UMA entities |
| [Event/audit log entities](https://github.com/keycloak/keycloak/tree/26.5.5/model/jpa/src/main/java/org/keycloak/events/jpa) | event/audit entities |
| [`MigrationModelEntity`](https://github.com/keycloak/keycloak/blob/26.5.5/model/jpa/src/main/java/org/keycloak/models/jpa/entities/MigrationModelEntity.java) | Schema migration tracker |
| [Organization entities](https://github.com/keycloak/keycloak/tree/26.5.5/model/jpa/src/main/java/org/keycloak/models/jpa/entities) — `OrganizationEntity`, `OrganizationDomainEntity`, `OrganizationInvitationEntity` | Organizations (KC 25+). [Adapters and provider](https://github.com/keycloak/keycloak/tree/26.5.5/model/jpa/src/main/java/org/keycloak/organization/jpa) live separately. |

Schema migrations: [Liquibase changelogs in `META-INF`](https://github.com/keycloak/keycloak/tree/26.5.5/model/jpa/src/main/resources/META-INF). Entity classes show JPA mapping; changelogs are authoritative for DB-side constraints/indexes.

Many tables are list-collection mappings (no dedicated entity class) — they back `@ElementCollection` fields on parent entities.

---

## Mental Model

1. **Realm is the container.** Almost every realm-scoped table FKs to `realm.id`.
2. **Three entity families**:
   - **Identity**: users, groups, credentials, federated_identity
   - **Authorization**: roles, composite_roles, scopes, Authorization Services
   - **Applications**: clients, scopes, protocol mappers, IDPs, auth flows
3. **Relationship tables are composite-PK and DON'T carry realm.** Always JOIN through the parent.
4. **Attributes are universal extensibility.** Every major entity has a sibling attribute table.
5. **Federation parallels mirror standard tables** with `Fed*` prefix and no FKs.
6. **Sessions are mostly NOT in DB anymore** — Infinispan only in 26+.
7. **Customize behavior** via JPA `*Provider` and `*Adapter` extension. Customize schema via your own `JpaEntityProvider` + Liquibase.
8. **Querying**: prefer named queries on entity classes; otherwise JPQL via `EntityManager`.
9. **Cascades are aggressive**: deleting a parent often nukes a tree of children.
10. **JPA field names ≠ FK column names** — `e.user.id` not `e.userId`.

---

## Schema at a Glance

```
Realm core ─────┬─ realm + realm_attribute + realm_smtp_config
                └─ realm-level lists (events listeners, supported locales, default groups, localizations)

Users ──────────┬─ user_entity + user_attribute + user_required_action
                ├─ user_role_mapping (→ keycloak_role)
                ├─ user_group_membership (→ keycloak_group)
                ├─ user_consent + user_consent_client_scope
                └─ credential

Roles ──────────┬─ keycloak_role (realm OR client-scoped)
                ├─ role_attribute (multi-valued)
                └─ composite_role (self-join: parent → child)

Groups ─────────┬─ keycloak_group (hierarchical via parent_group)
                ├─ group_attribute (multi-valued)
                └─ group_role_mapping

Clients ────────┬─ client + client_attributes
                ├─ list-tables: redirect_uris, web_origins, client_node_registrations
                └─ client_initial_access, client_auth_flow_bindings

Client scopes ──┬─ client_scope + client_scope_attributes
                ├─ client_scope_client (binding to clients)
                ├─ client_scope_role_mapping (allow-list)
                └─ default_client_scope (realm-level defaults)

Protocol mappers ── protocol_mapper (client OR scope) + protocol_mapper_config

IDPs ───────────┬─ identity_provider + identity_provider_config
                ├─ identity_provider_mapper + idp_mapper_config
                └─ federated_identity (link to users)

Auth flows ─────┬─ authentication_flow + authentication_execution
                ├─ authenticator_config + authenticator_config_entry
                └─ required_action_provider + required_action_config

Components ────── component + component_config (KeyProviders, UserStorage, etc.)

Federated users ─ fed_user_attribute, fed_user_credential, fed_user_*_mapping (NO FKs)

Authz Services ── (separate sub-model — see references/authorization-services.md)

Events ─────────── event_entity + admin_event_entity (optional, append-only)

Sessions ───────── offline_user_session + offline_client_session + revoked_token

Organizations ──── org + org_domain (KC 25+); org_invitation (KC 26.5+)

System ─────────── migration_model + databasechangelog + databasechangeloglock
```

---

## Schema Patterns

Keycloak's data model uses 7 recurring patterns. Recognizing them speeds up reading the schema.

### Pattern 1: Realm-scoped entity
- Has `realm_id` column FK to `realm.id`
- Examples: `user_entity`, `client`, `keycloak_role`, `keycloak_group`, `client_scope`, `component`, `identity_provider`

### Pattern 2: Composite-PK relationship table
- Two FKs as composite PK
- **Doesn't carry `realm_id`** — must JOIN through parent for realm filtering
- Uses JPA `@IdClass` (each entity has a public static `Key` inner class)
- Examples: `user_role_mapping`, `user_group_membership`, `group_role_mapping`, `composite_role`, `client_scope_role_mapping`, `client_scope_client`

### Pattern 3a: Multi-valued attribute (simple-ID PK)
- One `(parent, name)` can have multiple value rows
- Examples: `user_attribute`, `group_attribute`, `role_attribute`, `resource_attribute`

### Pattern 3b: Single-valued attribute (composite PK)
- One value per `(parent, name)`
- Examples: `client_attributes`, `client_scope_attributes`, `realm_attribute`

### Pattern 4: ElementCollection list-table
- No dedicated entity class
- Backs a `Set` field on a parent entity
- Examples: `redirect_uris`, `web_origins`, `realm_supported_locales`, `realm_default_groups`, `protocol_mapper_config`, `identity_provider_config`

### Pattern 5: Hierarchical via self-FK
- A `parent_*` column references the same table's `id`
- `keycloak_group.parent_group` (top = `' '` single space, NOT null)
- `component.parent_id`
- `authentication_execution.flow_id` → `authentication_flow.id` (the parent flow); when `authenticator_flow = true`, also `authentication_execution.auth_flow_id` → the invoked sub-flow

### Pattern 6: Federated mirror (no FK)
- Tables prefixed `fed_*` for users in external storage
- Each row carries `user_id + realm_id + storage_provider_id` (no FKs)
- Mirrors of: user_attribute, credential, consent, group_membership, role_mapping, required_action

### Pattern 7: Provider with config
- Main row in entity table + multi-row config table
- `identity_provider` + `identity_provider_config`
- `protocol_mapper` + `protocol_mapper_config`
- `authenticator_config` + `authenticator_config_entry`
- `component` + `component_config`

---

## Common Gotchas

### 1. Inconsistent `realm_id` widths
Most tables: `VARCHAR(36)`. Some (`keycloak_role`, `event_entity`): `VARCHAR(255)`. Check the changelog for manual SQL.

### 2. Top-level groups have `parent_group = ' '`, not NULL
`GroupEntity.TOP_PARENT_ID = " "` (single space). Top-level group queries must use `WHERE parent_group = ' '`. NULL won't match.

### 3. Composite roles compose to arbitrary depth
Granting one composite role grants all transitively-composed children. Always use:
```java
RoleUtils.expandCompositeRoles(roles)
RoleUtils.getDeepUserRoleMappings(user)
```

### 4. Realm vs client roles share `keycloak_role`
Boolean `client_role` distinguishes:
- Realm role: `client_role = false`, `realm_id` set, `client` null
- Client role: `client_role = true`, `client` set, `realm_id` set
- `client_realm_constraint` powers the unique-name-per-scope constraint

### 5. `protocol_mapper` has TWO possible parents
`client_id` set OR `client_scope_id` set, never both. To list all mappers for a token request: (mappers with client_id = X) UNION (mappers from each scope the client uses). The model API handles this.

### 6. Multi-valued vs single-valued attributes

| Table | Multi-valued? |
|---|---|
| `user_attribute`, `group_attribute`, `role_attribute`, `resource_attribute` | YES (multiple rows per name) |
| `client_attributes`, `client_scope_attributes`, `realm_attribute` | NO (one value per name) |

Setter behavior:
- Multi-valued: `setAttribute(name, List)` writes N rows; `setSingleAttribute(name, val)` deletes existing then writes 1
- Single-valued: `setAttribute(name, val)` does INSERT or UPDATE

### 7. Realm config lives in 2 places
Some realm settings are columns on `realm` (name, enabled, password_policy, ssl_required). Others are in `realm_attribute` rows. Model API `realm.getAttribute(name)` reads from the attribute table; column-backed settings have dedicated getters.

### 8. The `default-roles-` role
Every realm has an auto-generated composite role containing all realm-default + client-default roles. Granted to all users by default. Composite expansion delivers the defaults.

**The realm name is lowercased** in the role name: `Constants.DEFAULT_ROLES_ROLE_PREFIX + "-" + realm.getName().toLowerCase()`. So a realm `"MyRealm"` has role `default-roles-myrealm`, not `default-roles-MyRealm`. Querying by the raw realm name will miss.

### 9. Service-account users
When `client.service_accounts_enabled = true`, Keycloak creates a system user with `username = "service-account-"` and `service_account_client_link = `. They behave like regular users for role-mapping.

### 10. Sessions are NOT in the database (KC 22+, fully removed in 26.0)
`user_session`, `client_session`, etc. are now in **Infinispan**. Only `offline_user_session`, `offline_client_session`, `revoked_token` are persisted in DB.

### 11. `event_entity` and `admin_event_entity` are append-only
Optional — only populated if event listeners enabled. No FKs to other domain tables (events survive entity deletion for audit). Heavy realms produce huge volumes; configure retention.

### 12. JPA `@ManyToOne` field names ≠ FK column names
`UserAttributeEntity.user` (JPA field, type `UserEntity`) maps to `USER_ID` column. JPQL is `e.user.id` not `e.userId`. Common JPQL writer trap.

```java
// CORRECT
"SELECT e FROM UserAttributeEntity e WHERE e.user.id = :uid"

// WRONG — field 'userId' doesn't exist
"SELECT e FROM UserAttributeEntity e WHERE e.userId = :uid"
```

### 13. `LONG_VALUE` and the hash index columns on user/fed user attributes (KC 24+)
`user_attribute.value` is `VARCHAR(255)`. Longer values spill into `LONG_VALUE` (NCLOB, added 24.0.0). Querying just `VALUE` will miss long attribute values. Use the entity's `getValue()` getter or join with `LONG_VALUE`.

KC 24.0.0 also added two hash index columns to support indexed search of long values:
- `LONG_VALUE_HASH BINARY(64)` — SHA-256 of the long value, case-sensitive
- `LONG_VALUE_HASH_LOWER_CASE BINARY(64)` — SHA-256 of the lowercased long value, for case-insensitive search

Custom JPQL searching by long-value content should compute the hash on the input and match the appropriate hash column rather than full-scanning the NCLOB. The same three columns exist on `FED_USER_ATTRIBUTE`.

### 14. `fed_user_*` tables have no FKs
Federated users live outside Keycloak's `user_entity`. Tables denormalize with `user_id + realm_id + storage_provider_id` columns. No referential integrity to the external store.

### 15. Brute-force tracking is no longer in the database (KC 26.1+)
The `username_login_failure` table was **dropped in 26.1.0** (`` in `jpa-changelog-26.1.0.xml`). Brute-force/login-failure state is now Infinispan-only — the same migration applied to live sessions in 26.0. The previous table was keyed by `(realm_id, username)` so tracking worked even before a matching user existed (defeating username enumeration); the in-memory replacement preserves that semantic but means raw SQL/JPQL can no longer read or mutate the state. Use `BruteForceProtector` (SPI) or the admin REST `/admin/realms/{realm}/attack-detection/brute-force/users/{user-id}` endpoints.

### 16. Composite PKs are `@IdClass`, not `@EmbeddedId`
Each composite-PK entity has a public static inner `Key` class. **The Key constructor signatures take entity references, not raw IDs**, mirroring the `@ManyToOne` field types on the entity itself. For example, `UserRoleMappingEntity.Key(UserEntity user, String roleId)` — the first arg is a `UserEntity`, not a `String userId`.

```java
UserEntity userRef = em.getReference(UserEntity.class, userId);
em.find(UserRoleMappingEntity.class, new UserRoleMappingEntity.Key(userRef, roleId));
```

### 17. Relationship tables don't carry `realm_id`
`user_role_mapping`, `group_role_mapping`, `composite_role`, `client_scope_role_mapping`, `client_scope_client` — none have a direct realm column. Filter by JOIN through the parent:

```sql
SELECT urm.* FROM user_role_mapping urm
JOIN user_entity u ON urm.user_id = u.id
WHERE u.realm_id = ?
```

This is a top source of bugs.

### 18. `master` realm is special
The realm named `master` has admin authority over all other realms. Many extensions skip it:
```java
if ("master".equals(realm.getName())) return;
```

### 19. Not every entity has `@NamedQuery` annotations
`UserEntity`, `RoleEntity`, `GroupEntity`, `ClientEntity`, `CredentialEntity`, and `ClientScopeEntity` define named queries. **`ComponentEntity` and `IdentityProviderEntity` do not** — they have zero `@NamedQuery` declarations on the class. Calling `em.createNamedQuery("getComponents", ComponentEntity.class)` throws `IllegalArgumentException` at runtime.

For those entities, use the model API (`realm.getComponentsStream(...)`, `realm.getIdentityProvidersStream()`, etc.) or write inline JPQL / Criteria against the entity directly. See [references/entities.md](./references/entities.md) for the authoritative list of named queries per entity.

### 20. `keycloak_group.type` and `user_group_membership.membership_type` separate regular groups from organizations (KC 26.0+)
Since 26.0.0, two related columns were added to support organizations:

**`KEYCLOAK_GROUP.TYPE` (INT NOT NULL, default 0)** — values from `GroupModel.Type`:
- `0` = `REALM` — regular group
- `1` = `ORGANIZATION` — backs an organization (referenced from `org.group_id`)

**`USER_GROUP_MEMBERSHIP.MEMBERSHIP_TYPE` (VARCHAR)** — values from `org.keycloak.representations.idm.MembershipType`:
- `UNMANAGED` — member can exist without the group/org (default for regular groups)
- `MANAGED` — member cannot exist without the group/org (typical for org memberships)

A bare `SELECT ... FROM keycloak_group WHERE realm_id = ?` returns **both kinds** of groups, and org-backed memberships appear in `user_group_membership` alongside regular memberships. Naive queries silently include organizations.

Filter on `keycloak_group.type = 0` for regular groups, or use `JpaRealmProvider.getGroupsStream(realm)` which adds the type predicate automatically. The named queries `getGroupsByMember` / `getGroupsByFederatedMember` filter on `g.type = 1` to enumerate a user's organizations. For membership semantics, `MANAGED` rows

…

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [tide-foundation](https://github.com/tide-foundation)
- **Source:** [tide-foundation/keycloak-skills](https://github.com/tide-foundation/keycloak-skills)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-tide-foundation-keycloak-skills-keycloak-entities
- Seller: https://agentstack.voostack.com/s/tide-foundation
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.