Internal Data Dictionary

This appendix describes the tables and columns in Posit Workbench’s internal database. Note that this database is intended for internal product usage only; the schema and columns may change between releases of Posit Workbench.

Note

The fields which have the Integer/Boolean field are Integer type if SQLite is used and Boolean type if PostgreSQL is used, due to differences in the two database systems.

licensed_users

The licensed_users table tracks the last sign in time of every user of Posit Workbench. It also keeps track of whether a particular user has administrator privileges, and whether a user is locked from the system.

Column Name Type Can be Null Description
user_name Text No The username associated with the user.
locked Integer/Boolean No Whether or not the user is locked (disabled from signing in).
last_sign_in† Text No The date and time (UTC) when the user last signed in.
is_admin Integer/Boolean No Indicates if the user is an admin.
user_id Integer No The POSIX/Operating-System-Level ID of the user
aws_role_arn Text Yes One or more AWS roles associated with the user, if any.
aws_role_session_name Text Yes An AWS role session name for the user, if any.
id_token Text Yes An OAuth security token for the user.
refresh_token Text Yes An optional OAuth refresh token for offline access.
token_expiry Text Yes The time after which the security token expires.
id⁕ Integer No Auto incrementing ID for each user for internal foreign key relationships.
created‡ Text Yes The date and time (UTC) when the user was created.
last_modified‡ Text Yes The date and time (UTC) when the user was last modified.
version‡ Text Yes A version field for the SCIM specification.
email‡ Text Yes The email address of the user.
display_name‡ Text Yes The display name of the user.
posix_name Text Yes The POSIX-compatible name of the user.
shadow‡ Text Yes The shadow file entry for the user.
homedir‡ Text Yes The home directory of the user.
active Integer/Boolean No Status of user account in Identity Provider. Used only when users are managed with System for Cross-domain Identity Management (SCIM).
guid Text Yes Identifier that can be obtained without auth to refer to the user.
migration_version Text Yes Tracks the migration status of user-specific data.

⁕ Primary Key   † Indexed Column   ‡ User provisioning column

licensed_users_metadata

The licensed_users_metadata table stores metadata associated with the current licensed users, used for quickly looking up known users.

Column Name Type Can be Null Description
uid Text No Randomly generated unique ID associated with the current locked users.

oauth2_state

The oauth2_state table tracks OAuth2 code flow state parameters and their corresponding codes that allow Workbench to serve as a redirect URL for OAuth2 applications.

Column Name Type Can be Null Description
state⁕ Text No The unique OAuth2 state parameter.
code Text No The OAuth2 authorization code.
created Text No The date and time (UTC) when the state was created.

⁕ Primary Key

login_state

The login_state table stores login state with “state” keys. Used by OpenID Connect and SAML-based auth to mitigate XSRF attacks.

Column Name Type Can be Null Description
state_key⁕ Text No A generated state key.
uri Text No The URI.
stay_signed_in Text No Whether to stay signed in or not.
expiration Text No The date and time (UTC) after which the state key should be considered invalid.

⁕ Primary Key

user_service_tokens

The user_service_tokens table stores authentication tokens generated for accessing both the Workbench API and the SCIM API of the user service.

Column Name Type Can be Null Description
key⁕ Text No The hashed token key.
name Text No The name of the token.
created Text No The date and time (UTC) when the token was created.
expires Text No The date and time (UTC) when the token expires.
last_used Text No The date and time (UTC) when the token was last used.
scope Integer No The scope of the token. (DEPRECATED)
access_level Integer No The access level of the token. A value of 0 indicates user-level access. A value of 1 indicates admin-level access.
permission Integer No The permission of the token. A value of 0 indicates read-only access. A value of 1 indicates read-write access.
api_token_type Integer No Type of API token. A value of 0 indicates User Service (SCIM). A value of 1 indicates a Super Admin. A value of 2 indicates Admin. A value of 3 indicates User.
username Text Yes Optional username for management or owner for API tokens.

⁕ Primary Key

schema_version

The schema_version table is used to keep track of the version of the database schema currently in use, for the purposes of managing changes to the database schema over time.

Column Name Type Can be Null Description
current_version Text No The schema version, which is derived from the date and time on which it was created.
release_name Text No The code name of the release to which this version of the schema belongs.

vscode_state

The vscode_state table is used to track the user state for VS Code sessions.

Column Name Type Can be Null Description
id⁕ Integer No Auto incrementing ID for each state entry for internal foreign key relationships.
user_id‡ Integer No A number representing the user the state is saved for. Corresponds (via foreign key) to id in the licensed_users table.
last_updated Timestamp No The last time this entry was updated.
db_name Text No The name of the VS Code IndexedDb.
db_contents Text No The contents of the IndexedDb.

⁕ Primary Key   ‡ Foreign Key

vscode_session_state

Column Name Type Can be Null Description
session_id⁕ text No The PWB session ID.
last_opened_folder text Yes The substring of the session HTTP request that contains the open folder.
user_last_seen text Yes The last time user activity was detected for the session.
username text Yes The username of session owner.

positron_state

The positron_state table is used to track the user state for Positron sessions.

Column Name Type Can be Null Description
id⁕ Integer No Auto incrementing ID for each state entry for internal foreign key relationships.
user_id‡ Integer No A number representing the user the state is saved for. Corresponds (via foreign key) to id in the licensed_users table.
last_updated Text No The last time this entry was updated.
db_name Text No The name of the Positron IndexedDb.
db_contents Text No The contents of the IndexedDb.

⁕ Primary Key ‡ Foreign Key

positron_session_state

Column Name Type Can be Null Description
session_id⁕ text No The PWB session ID.
last_opened_folder text Yes The substring of the session HTTP request that contains the open folder.
user_last_seen text Yes The last time user activity was detected for the session.
username text Yes The username of session owner.

active_session_metadata

The active_session_metadata table stores metadata about active sessions.

Column Name Type Can be Null Description
session_id⁕ Text No The ID of the session.
user_id‡ Integer No ID of the row in licensed_users which corresponds to the user that owns this session.
workbench Text No The name of the workbench (aka IDE) used for the session, e.g. RStudio, JupyterLab, Positron, or VS Code.
created Text No Time at which this session was first created. Milliseconds since Epoch UTC.
running_timestamp Text Yes Time at which this session entered the running state. ISO-8601.
last_used Text No Time at which this session was last used. Milliseconds since Epoch UTC.
r_version Text Yes Version of R in use, if this session’s workbench type is RStudio.
r_version_label Text Yes The (optional) display name of the version of R being used, if this session’s workbench type is RStudio.
r_version_home Text Yes The location on disk of R_HOME for the version of R being used, if this session’s workbench type is RStudio.
project Text Yes Open project, if any.
working_directory Text Yes The working directory of the session.
activity_state Text No Overall state of the session at a given moment, for the launch process: launching/resuming, pending, starting, and finally running. Also represents exit states: finished, suspended, failed, killed, and suspending for sessions that support suspension (i.e. RStudio).
label Text No The label of the session, used to identify it in the UI.
launch_parameters Text No Parameters with which this session was launched.
save_prompt_required Text No Flag indicating that quitting an RStudio session will display a prompt to save unsaved work. 1 or 0 as text. Default is ‘not_required’.
executing Text Yes The session is active and busy. 1 or 0 as text.
running Text Yes Whether the session is currently running. 1 or 0 as text.
last_state_updated Text Yes Time last session state updated. Milliseconds since Epoch UTC.
initial Text Yes Whether or not this is the initial metadata for the session. 1 or 0 as text.
last_resumed Text Yes Time the session was last resumed. ISO-8601.
blocking_suspend Text Yes Things which are blocking the suspension of the session, if any.
suspend_timestamp Text Yes Time at which the session suspended. ISO-8601.

⁕ Primary Key ‡ Foreign Key

oauth2_clients

The oauth2_clients table stores arbitrary OAuth2 clients.

Column Name Type Can be Null Description
issuer Text No The token issuer, which is a URL. Unique constraint.
token_url Text No The token endpoint for the issuer, which is also a URL.
client_id Text No The OAuth2 client ID.
client_secret Text No The OAuth2 client secret.
token_endpoint_auth_method Text No The auth method for sending the client secret to the token endpoint, either “client-secret-post” or “client-secret-basic”. Default is ‘client-secret-basic’.
uuid Text Yes Unique identifier for this OAuth2 client. Unique constraint.

oauth2_tokens

The oauth2_tokens table stores arbitrary OAuth2 tokens on behalf of users.

Column Name Type Can be Null Description
user_name Text No The user this token belongs to.
issuer Text No The token issuer, which is a URL.
access_token Text No The OAuth2 access token.
token_scope Text No The OAuth2 scope for this access token.
token_expiry Text No The time after which the access token expires.
refresh_token Text Yes An optional refresh token, for offline access.
client_uuid‡ Text Yes The UUID of the OAuth2 client this token belongs to. Corresponds to uuid in the oauth2_clients table.

Unique constraint on (user_name, issuer, token_scope).

‡ Foreign Key

external_app_roles

The external_app_roles table stores user roles for external applications.

Column Name Type Can be Null Description
id⁕ Integer No Auto-incrementing ID.
user_id‡ Integer No Foreign key relating to id column of licensed_users.
app_name Text No The name of the app the role is associated with (e.g., snowflake).
role_name Text No The name of the role (e.g., ADMIN).
instance_identifier Text No The unique identifier for the app instance the role is associated with.
is_default Integer/Boolean No Whether the role is set as the default role.

Unique constraint on (user_id, app_name, role_name, instance_identifier).

⁕ Primary Key ‡ Foreign Key

audited_jobs

The audited_jobs table stores information about audited job state.

Column Name Type Can be Null Description
id⁕ Text No UUID which is the primary key of the table.
status Text No Job status. Default is ‘pending’.
user_id‡ Integer No User identifier. Corresponds to id in the licensed_users table.
launcher_job_id Text No Job ID that was provided by launcher. Note this is not guaranteed to be unique.
launcher_data Text Yes Final JSON representation of this job according to launcher.
storage_uri Text Yes Where files associated with this job are stored.
created Text No When the job record was created. Default is CURRENT_TIMESTAMP.

⁕ Primary Key ‡ Foreign Key

groups

The groups table stores group information.

Column Name Type Can be Null Description
id⁕ Integer No Unique identifier for a group used for FK relationships.
gid Integer No Linux GID. Unique constraint.
name Text No Linux Group Name. Unique constraint.
uuid Text No Unique identifier for the group. This is the externally visible ID. Unique constraint.
created Text Yes Creation date for the group. RFC3339 datetime format with millisecond precision.
last_modified Text Yes Last update date for the group. RFC3339 datetime format with millisecond precision.
version Text Yes A version field to support the SCIM spec.
display_name Text Yes The display name for the group.

⁕ Primary Key

groups_licensed_users

The groups_licensed_users table is a linking table that defines which licensed_users belong to which groups.

Column Name Type Can be Null Description
user_id⁕‡ Integer No User ID. Corresponds to id in the licensed_users table.
group_id⁕‡ Integer No Group ID. Corresponds to id in the groups table.
display Text Yes Optional display name for the user in this group context.

⁕ Primary Key (composite) ‡ Foreign Key

cluster_runtimes

The cluster_runtimes table stores cluster runtime management information.

Column Name Type Can be Null Description
runtime_id⁕ Text No UUID primary key.
cluster_name Text No Cluster name.
resource_id Text Yes Cluster resource identifier.
language Text No Runtime language - r or python.
display_name Text No Display name for the runtime.
source Text No Source of the runtime.
path Text No Path to the runtime.
executable Text No Executable for the runtime.
created_time Text Yes When the runtime was created. ISO-8601 format.
updated_time Text Yes When the runtime was last updated. ISO-8601 format.

Unique constraint on (cluster_name, resource_id, path).

⁕ Primary Key

project_metadata

The project_metadata table stores metadata about projects.

Column Name Type Can be Null Description
project_id⁕ Text No Unique project identifier.
owner_id‡ Integer No Owner user ID. Corresponds to id in the licensed_users table.
name Text No Project name.
canonical_path Text No Canonical path to the project.
display_path Text Yes Display path (only used for the project owner).
project_paths Text Yes JSON-formatted array of project paths.
display_name Text Yes Display name for the project.
editors Text Yes JSON-formatted list of editors.
last_r_version Text Yes Last R version used with this project.
last_python_version Text Yes Last Python version used with this project.
launch_parameters Text Yes JSON-formatted launch parameters.
created Text Yes When the project was created. Default is CURRENT_TIMESTAMP.

⁕ Primary Key ‡ Foreign Key

user_projects

The user_projects table connects users to shared/recent projects and tracks visibility state.

Column Name Type Can be Null Description
user_id⁕‡ Integer No User ID. Corresponds to id in the licensed_users table.
original_project_id⁕‡ Text No Original project ID. Corresponds to project_id in the project_metadata table.
project_id Text No Project ID. For the project owner, this will always match original_project_id. Unique with user_id.
share_time Text Yes When the project was shared. Null for project owner.
last_session_id Text Yes Last session ID that accessed this project.
last_used Text Yes Timestamp when the project was last used.
last_editor Text Yes Last editor used with this project.
is_closed Integer/Boolean No Whether the project is closed. Default is 0 (false).

⁕ Primary Key (composite on user_id, original_project_id) ‡ Foreign Key

session_refresh_tokens

The session_refresh_tokens table stores session refresh tokens with self-contained metadata.

Column Name Type Can be Null Description
refresh_token_key⁕ Text No The hashed refresh token key (primary identifier).
session_id Text Yes ID of session using this token. May be null for jobs.
job_uuid Text No UUID of job that’s using this token.
user_id‡ Integer No User ID. Corresponds to id in the licensed_users table.
created_at Text No When this refresh token was created. Default is CURRENT_TIMESTAMP.
expires_at Text No When this refresh token expires.
token_status Text No Status of this refresh token: ‘active’, ‘revoked’, etc. Default is ‘active’.
revoked_at Text Yes When this refresh token was revoked/deactivated (nullable, for future use).
revocation_reason Text Yes Reason for revocation (nullable, for future use).

⁕ Primary Key ‡ Foreign Key

rpc_cookies

The rpc_cookies table tracks all RPC cookies, both active and revoked, so all of a session’s RPC cookies can be revoked when needed.

Column Name Type Can be Null Description
rpc_cookie_hash⁕ Text No Hash of the full RPC cookie value - primary key.
refresh_token_key‡ Text No Associated refresh token key. Corresponds to refresh_token_key in session_refresh_tokens.
user_id‡ Integer No User ID. Corresponds to id in the licensed_users table.
expires_at Text No When this RPC cookie expires.
status Text No Status: ‘active’ or ‘revoked’. Default is ‘active’.
revoked_at Text Yes When this RPC cookie was revoked.
revocation_reason Text Yes Reason for revocation.

⁕ Primary Key ‡ Foreign Key

PostgreSQL tables only

The cluster and node tables are only available in PostgreSQL, since SQLite cannot be used in load-balanced configurations.

cluster

The cluster table stores information about a cluster in a load-balanced configuration.

Column Name Type Can be Null Description
id⁕ Text No A unique ID representing the cluster.
key_hash Text Yes A hash of the cluster’s secure-cookie-key value. Used to ensure that all nodes in the cluster have matching keys.
protocol Text Yes The protocol used to communicate among nodes in the cluster. Must be one of http, https, or https no verify.
secure_cookie_key Text Yes The cluster’s secure cookie key used for secure communication.
launcher_pem Text Yes The cluster’s private launcher key. Used to securely communicate across launcher sessions.
launcher_pub Text Yes The cluster’s public launcher key. Used to securely communicate across launcher sessions.

⁕ Primary Key

node

The node table stores information about a single node in a load-balanced cluster.

Column Name Type Can be Null Description
id⁕ Text No A unique ID representing the node.
cluster_id‡ Text No The ID of the cluster to which the node belongs. Corresponds (via foreign key) to id in the cluster table
host Text Yes The node’s hostname, or its raw IP address.
port Text Yes The port to connect to; defaults to 443 for https and 8787 for http.
ipv4 Text Yes The resolved IPv4 address of the host, if applicable.
last_seen Text Yes The date and time (UTC) the node was last seen; the node updates this every 5 minutes while it’s healthy.
status Text Yes The node’s status (starting, failed to resolve, online, or offline)

⁕ Primary Key   ‡ Foreign Key

Back to top