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.

⁕ 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 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.

⁕ 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.

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