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