AIS データウェアハウステーブル V1.5
Overview
With over 150 normalized SQL tables, AccelByte Intelligence Service (AIS) provides a comprehensive data warehouse with full data relationships that allows you to query data across games, studios, and AccelByte Gaming Services (AGS).
AIS follows GDPR standards, making sure that all data is secure and reliable. In addition, AIS data warehouse can be tailored to satisfy each user's specific data needs.
Ask your account manager or technical producer about AIS today!
You will find all the normalized SQL tables in the AIS data warehouse below.
Group: Namespace
Table: namespace_status_t
This table contains record of the status changes of a namespace over time.
For example a record of a namespace becoming enabled at 2023-02-07 05:47:30.000.
Analytics usage: reporting on namespaces depending on status. Usually only those which are enabled would be reported upon.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for namespace status generated by analytics. | Primary Key |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Foreign Key |
status_id | bigint | Relates to namespace_status_type_t. | Foreign Key |
start_ts | timestamp without time zone | The start of when the status was in effect for this namespace. | |
end_ts | timestamp without time zone | The end of when the status was in effect for this namespace. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
namespace_status_t_namespace_id_start_ts_uk | {"namespace_id, start_ts"} | Unique |
namespace_status_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of namespace_status_t
- Referencing to
Table: namespace_status_type_t
This table contains a list of possible types of statuses for namespace. One of ENABLED, DELETED, DISABLED.
For example ENABLED meaning the namespace is live within AccelByte systems.
Analytics usage: displaying namespace status.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for namespace status type generated by analytics. | Primary Key |
name | character varying | Name of the namespace status type. | |
lname | character varying | Long name of the namespace status type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
namespace_status_type_t_name_uk | {name} | Unique |
namespace_status_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of namespace_status_type_t
- Referenced by
Table: namespace_t
This table contains a namespace shows who or what owns data in other tables. It usually represents a game but could also be AccelByte, a project, a team within a studio or a studio.
For example a record of a namespace for a specific game in development, its parent project namespace and the studio owning the game.
Analytics usage: obtaining namespace details, for instance to view data for all namespaces under a certain studio.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | The Identifier for namespace. | Primary Key |
parent_id | bigint | Relates back to namespace_t to identify parent of this namespace. If null then there is no parent and the namespace is a Publisher. | |
studio_id | bigint | Relates to studio_t. | Foreign Key |
name | character varying | Name of the namespace. | |
lname | character varying | Long name of the namespace. | |
is_working_title | boolean | If the namespace is for a game and the game is a working title this is true. | |
effective_dt | date | The date at which data for this namespace is valid. This could be, for example, the initial release date of the game. | |
is_production | boolean | Indicates whether the namespace is in production or not. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
namespace_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of namespace_t
- Referencing to
- Referenced by
Group: User
Table: test_user_t
This table contains test users data.
Analytics usage: finding and distinguish test users in a namespace.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user generated by analytics. | Primary Key |
user_type_id | bigint | Relates to test_user_type_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was loaded. | |
received_ts | timestamp without time zone | When the data was received. | |
character varying | User email address. | Unique Constraint | |
namespace_id | bigint | Relates to namespace_t table. | Foreign Key, Unique Constraint |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
test_user_t_uk | {"email, namespace_id"} | Unique |
test_user_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of test_user_t
- Referencing to
Table: test_user_type_t
This table contains a list of test data types. The test data type is what test event source. For example sanity-test, loadtest-internal, loadtest-external, etc.
Analytics usage: displaying test data type.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for test type generated by analytics. | Primary Key |
name | character varying | Name of the test type. | Unique Constraint |
lname | character varying | Long name of the test type. | |
job_run_id | bigint | Id of the analytics job run via which this row was loaded. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
test_type_t_pk | {id} | Unique |
test_type_t_name_uk | {name} | Unique |
Relationship Diagram:
Show ER Diagram of test_user_type_t
- Referenced by
Table: thirdparty_account_t
This table contains third party accounts held by users. This shows the platform identifier for those accounts and the platform they are using.
For example an Xbox platform's account with its Xbox account id.
Analytics usage: viewing third party accounts and their platform.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for third party account generated by analytics. | Primary Key |
platform_id | bigint | Platform id. | Foreign Key |
thirdparty_uid | character varying | User id / gamer tag from each third party. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
thirdparty_account_t_platform_user_id_uk | {"platform_id, thirdparty_uid"} | Unique |
thirdparty_account_t_job_run_id_i | {job_run_id} | Non-Unique |
thirdparty_account_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of thirdparty_account_t
- Referencing to
- Referenced by
Table: user_auth_t
This table contains record of the user authentication user activity. When they authenticate into a game, a website or other client.
For example a record of a user authenticating in a game's website at 2021-12-08 17:01:30.000 vai that website's authentication system.
Analytics usage: analysis of user authentications and on which platforms they happen over time.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user auth generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key |
client_id | bigint | Relates to oauth_client_t. | Foreign Key |
auth_ts | timestamp without time zone | When the authorization occurred. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
is_third_party_auth | boolean | Was this authorization done by a third party? | |
platform_id | bigint | Relates to plaform_t. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_auth_t_user_id_auth_ts_uk | {"user_id, auth_ts"} | Unique |
user_auth_t_job_run_id_i | {job_run_id} | Non-Unique |
user_auth_t_client_id_i | {"client_id, auth_ts"} | Non-Unique |
user_auth_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_auth_t
- Referencing to
Table: user_ban_t
This table contains record of the user ban user activity. When a user is banned from a game feature.
For example a user is banned from chat for a week starting 2023-03-11 17:06:20.000.
Analytics usage: analysis of banned user counts and frequency of bans.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user ban generated by analytics. | Primary Key |
is_enabled | boolean | Is this instance of a ban is enabled | |
user_id | bigint | Relates to user_t. | Foreign Key |
start_ts | timestamp without time zone | Start time of the user ban activity. | |
ban_id | bigint | Clarity ban id. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | UTC timestamp for the end of the activity. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_ban_t_user_id_ban_id_uk | {"user_id, ban_id, start_ts"} | Unique |
user_ban_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_ban_t
- Referencing to
Table: user_country_t
This table contains shows a user country that they connect from and the applicable time period
For example a record for a user connecting from the USA at 2021-08-16 04:21:28.000, then another record for that user connecting from France at 2021-08-17 05:03:19.000.
Analytics usage: showing visualisation by country.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user country generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key |
country_id | bigint | Clarity country id. | Foreign Key |
start_ts | timestamp without time zone | Start of the period when the player was connecting from this country. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | End of the period when the player was connecting from this country. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_country_t_user_id_country_id_start_ts_uk | {"user_id, country_id, start_ts"} | Unique |
user_country_t_job_run_id_i | {job_run_id} | Non-Unique |
user_country_t_country_id_i | {country_id} | Non-Unique |
user_country_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_country_t
- Referencing to
Table: user_date_of_birth_t
This table contains the dates of birth input by a user. This can be amended over time for the same user.
For example a record of a user changing their date of birth to 1980-04-05 00:00:00.000 at 2021-08-10 15:38:37.000.
Analytics usage: analysis of user activity by age range.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user date of birth generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key |
date_of_birth | timestamp without time zone | Date of Birth of the user. | |
start_ts | timestamp without time zone | Start of the period for which this date of birth was given. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | End of the period for which this date of birth was given. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_date_of_birth_t_user_id_date_of_birth_start_ts_uk | {"user_id, start_ts"} | Unique |
user_date_of_birth_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_date_of_birth_t
- Referencing to
Table: user_display_name_t
This table contains the display names chosen by a user. This can be amended over time for the same user.
For example a record of a user setting their display name to “Gandalf” at 2021-07-17 23:28:35.000.
Analytics usage: looking up a user by display name.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user display name generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key |
display_name | character varying | Display name of the user. | |
start_ts | timestamp without time zone | Start of the period for which this display name was given. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | End of the period for which this display name was given. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_display_name_t_user_id_display_name_start_ts_uk | {"user_id, start_ts"} | Unique |
user_display_name_t_job_run_id_i | {job_run_id} | Non-Unique |
user_display_name_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_display_name_t
- Referencing to
Table: user_email_t
This table contains the email addresses chosen by a user. This can be amended over time for the same user.
For example a record of a user setting their email to “jose79@gmail.com” at 2021-08-09 12:07:05.000.
Analytics usage: looking up a user by email or finding the currently active email for a user.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user email generated by analytics. | Primary Key |
character varying | Email of the user. | ||
user_id | bigint | Relates to user_t. | Foreign Key, Unique Constraint |
start_ts | timestamp without time zone | Start of the period for which this email was given. | Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | End of the period for which this email was given. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_email_t_job_run_id_i | {job_run_id} | Non-Unique |
user_email_t_uk | {"user_id, start_ts"} | Unique |
user_email_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_email_t
- Referencing to
Table: user_enabled_t
This table contains a record of whether a user is enabled or not and over which time period. When a user is enabled they can log in to clients in the namespace, when disabled they are blocked from the whole namespace. Disabled is like a global ban.
For example a record for a user that became enabled at 2021-08-10 11:31:34.000 and was disabled at 2021-09-10 11:32:17.000.
Analytics usage: reporting on number of users enabled or disabled over time.
column name | data type | description | constraints |
---|---|---|---|
id | integer | Identifier for user enabled generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key, Unique Constraint |
start_ts | timestamp without time zone | Start of the period for which this user was enabled or not. | Unique Constraint |
is_enabled | boolean | Whether the user is enabled to use a namespace. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | End of the period for which this user was enabled or not. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_enabled_t_job_run_id_i | {job_run_id} | Non-Unique |
user_enabled_t_uk | {"user_id, start_ts"} | Unique |
user_enabled_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_enabled_t
- Referencing to
Table: user_language_t
This table contains the interface language chosen by a user. This can be amended over time for the same user.
For example a record of a user picking French as their primary language at 2021-08-19 07:37:57.000.
Analytics usage: analysis of languages chosen by users.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user language generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key |
language_id | bigint | Relates to language_t. | Foreign Key |
start_ts | timestamp without time zone | Start of the period for which this user was associated with this language. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | End of the period for which this user was associated with this language. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_language_t_user_id_language_id_start_ts_uk | {"user_id, start_ts"} | Unique |
user_language_t_language_id_i | {language_id} | Non-Unique |
user_language_t_job_run_id_i | {job_run_id} | Non-Unique |
user_language_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_language_t
- Referencing to
Table: user_marketing_preference_t
This table contains a record of whether a user has ticked subscribe when presented with a marketing preference choice or not and over which time period. This can be true or false at a given time for a user. There are not multiple marketing preference types, it is only one setting per user.
For example a particular user subscribes to receive marketing materials from a studio at 2022-11-09 06:59:34.000.
Analytics usage: analysis of marketing preference uptake.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user marketing preference generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key |
is_subscribed | boolean | Whether the user is subscribed to marketing or not. | |
start_ts | timestamp without time zone | Start of the period for which this user was subscribed or not to marketing. | |
end_ts | timestamp without time zone | End of the period for which this user was subscribed or not to marketing. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_marketing_preference_t_user_id_uk | {"user_id, start_ts"} | Unique |
user_marketing_preference_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_marketing_preference_t
- Referencing to
Table: user_status_t
This table contains user status, including whether they are deleted or verified. A user may choose to delete their account. There can be more than one record per user as the statuses may change over time.
For example A record of a user becoming verified at 2021-08-10 11:32:17.000.
Analytics usage: analysis of the number of users deleted or verified over time.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user status generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key, Unique Constraint |
start_ts | timestamp without time zone | Start of the period for which this user has this status. | Unique Constraint |
is_deleted | boolean | Whether user is deleted from its namespace. | |
is_verified | boolean | Whether user is verified in its namespace. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | End of the period for which this user has this status. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_status_t_uk | {"user_id, start_ts"} | Unique |
user_status_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_status_t
- Referencing to
Table: user_t
This table contains this table holds namespace level accounts. The uid must be unique on a per-namespace basis. This contains namespace eg. game, unique user identifier and optionally parent user.
For example a record for a particular user in a specific game with a unique user uid 1e1bbe017a014b2ebdb504fb99005b55.
Analytics usage: finding users in a namespace.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user generated by analytics. | Primary Key |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Foreign Key, Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
uid | character varying | User id as reflected in AccelByte Gaming Services. This should not be confused with any of the analytics user ids. Typically a hex string. This is the same user id that is found in the AccelByte Gaming Services raw events. | Unique Constraint |
parent_user_id | bigint | Relates back to user_t to identify parent of this user. | |
min_event_date | date | Earliest activity date for the User |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_t_namespace_id_i | {namespace_id} | Non-Unique |
user_t_job_run_id_i | {job_run_id} | Non-Unique |
user_t_uid_namespace_id_uk | {"uid, namespace_id"} | Unique |
user_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_t
- Referencing to
- Referenced by
Table: user_thirdparty_link_t
This table contains relates user to their third party accounts, recorded when the link between user and the third party is made or removed.
For example a record of a user linking their third party account to their in-game account on 2022-05-31 07:24:54.000. Another record of them unlinking it at 2022-06-11 07:24:54.000.
Analytics usage: relating users to third party accounts.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user third party link generated by analytics. | Primary Key |
user_thirdparty_id | bigint | Relates to user_thirdparty_t. | Foreign Key |
is_linked | boolean | Is there association between the AccelByte Gaming Services user and the Third Party user? | |
start_ts | timestamp without time zone | Start of the period for which these user accounts are linked or not. | |
end_ts | timestamp without time zone | End of the period for which these user accounts are linked or not. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_thirdparty_link_t_user_thirdparty_id_start_ts_uk | {"user_thirdparty_id, start_ts"} | Unique |
user_thirdparty_link_t_job_run_id_i | {job_run_id} | Non-Unique |
user_thirdparty_link_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_thirdparty_link_t
- Referencing to
Table: user_thirdparty_t
This table contains shows the linking of user to third party accounts during any time period.
For example a user id and their third party user id.
Analytics usage: relating users to third party accounts.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for user third party generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key |
thirdparty_user_id | bigint | Relates to thirdparty_account_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_thirdparty_t_user_id_thirdparty_user_id_start_ts_uk | {"user_id, thirdparty_user_id"} | Unique |
user_thirdparty_t_thirdparty_user_id_i | {thirdparty_user_id} | Non-Unique |
user_thirdparty_t_job_run_id_i | {job_run_id} | Non-Unique |
user_thirdparty_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of user_thirdparty_t
- Referencing to
- Referenced by
Group: User Oauth
Table: oauth_client_property_t
This table contains properties of an OAuth client such as platform, url, name.
For example a default game client running at localhost which is public starting at 2023-06-12 02:58:02.000.
Analytics usage: analysing client usage by attribute.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for oauth client properties generated by analytics. | Primary Key |
client_id | bigint | Relates to oauth_client_t. | Foreign Key |
name | character varying | Name of the oauth client properties. | |
redirect_uri | character varying | The redirect URI of the oAuth Client. | |
client_type | bigint | Relates to oauth_client_type_t. | |
base_uri | character varying | The base URI for the oAuth Client. | |
client_platform | character varying | The client platform e.g.,Playstation. | |
start_ts | timestamp without time zone | The start of the lifetime of these oAuth Client properties. | |
end_ts | timestamp without time zone | The end of the lifetime of these oAuth Client properties. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
oauth_client_property_t_client_id_start_ts_uk | {"client_id, start_ts"} | Unique |
oauth_client_property_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of oauth_client_property_t
- Referencing to
Table: oauth_client_status_t
This table contains the status of an OAuth client over time.
For example the status for a specific client changed to DELETED at 2023-06-12 02:58:02.000.
Analytics usage: selecting OAuth clients for reporting based on status.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for oauth client status generated by analytics. | Primary Key |
client_id | bigint | Relates to oauth_client_t. | Foreign Key |
status_id | bigint | Relates to oauth_client_status_type_t. | Foreign Key |
start_ts | timestamp without time zone | The start of when the status was in effect. | |
end_ts | timestamp without time zone | The end of when the status was in effect. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
oauth_client_status_t_namespace_id_start_ts_uk | {"client_id, start_ts"} | Unique |
oauth_client_status_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of oauth_client_status_t
- Referencing to
Table: oauth_client_status_type_t
This table contains the possible OAuth client status types. One of ENABLED, DELETED
For example ENABLED meaning the client is currently live.
Analytics usage: displaying OAuth client status.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for oauth client status type generated by analytics. | Primary Key |
name | character varying | Name of the oauth client status type. | |
lname | character varying | Long name of the oauth client status type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
oauth_client_status_type_t_name_uk | {name} | Unique |
oauth_client_status_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of oauth_client_status_type_t
- Referenced by
Table: oauth_client_t
This table contains a record of OAuth clients of AccelByte systems. Note that a client in this case is not a user, rather an authentication protocol.
For example a record containing a client number, which is a unique identifier relating to the application that a user logged in from, in a particular namespace.
Analytics usage: analysing usage of various clients by users.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for oauth client generated by analytics. | Primary Key |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Foreign Key |
client_number | character varying | An identifier for oAuth Client. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
client_t_namespace_id_client_number_uk | {"namespace_id, client_number"} | Unique |
client_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of oauth_client_t
- Referencing to
- Referenced by
Table: oauth_client_type_t
This table contains a list of OAuth client types. One of Public, Confidential.
For example Confidential, meaning used during development and not open to the public.
Analytics usage: displaying OAuth client type.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for oauth client type generated by analytics. | Primary Key |
name | character varying | Name of the oauth client type. | |
lname | character varying | Long name of the oauth client type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
oauth_client_type_t_name_uk | {name} | Unique |
oauth_client_type_t_pk | {id} | Unique |
Group: User Banning
Table: ban_feature_t
This table contains a list of AccelByte Gaming Services features within a game from which a user can be banned.
For example matchmaking.
Analytics usage: showing the name of the feature from which the user has been banned when reporting on bans.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for ban feature generated by analytics. | Primary Key |
name | character varying | Name of the game feature e.g.,matchmaking. | Unique Constraint |
lname | character varying | Long name of the ban feature. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_ban_feature_t_pk | {id} | Unique |
user_ban_feature_t_name_uk | {name} | Unique |
Relationship Diagram:
Show ER Diagram of ban_feature_t
- Referenced by
Table: ban_reason_t
This table contains list of reasons for a user to be banned from an AccelByte Gaming Services feature within a game.
For example cheating.
Analytics usage: showing the reason why the user was banned reporting on bans.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for ban reason generated by analytics. | Primary Key |
name | character varying | Name of the reason e.g.,cheating. | Unique Constraint |
lname | character varying | Long name of the ban reason. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
user_ban_reason_t_pk | {id} | Unique |
user_ban_reason_t_name_uk | {name} | Unique |
Relationship Diagram:
Show ER Diagram of ban_reason_t
- Referenced by
Table: ban_t
This table contains the record of the ban user activity; this is the event when they get banned within a game with the reason for the ban and feature they were banned from. It can include a freeform comment for further explanation of the ban.
For example a user being banned on 2023-01-01 from chat features because of harassment.
Analytics usage: analysis of users' ban activities.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for ban generated by analytics. | Primary Key |
ban_number | character varying | AccelByte Gaming Services ban id. | |
feature_id | bigint | Banned from the feature with this id. | Foreign Key |
reason_id | bigint | Banned for the reason with this id. | Foreign Key |
comment | character varying | Freeform comment about the ban. | |
ban_end_date | timestamp without time zone | Timestamp of when the ban ends. | |
start_ts | timestamp without time zone | Start time of the ban activity. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | |
end_ts | timestamp without time zone | UTC timestamp for the end of the activity. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
ban_t_ban_id_uk | {"ban_number, start_ts"} | Unique |
ban_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of ban_t
- Referencing to
- Referenced by
Group: Telemetry
Table: telemetry_event_t
This table contains a list of possible telemetry event types within a namespace. Hence namespaces may have varying types of event types.
For example CPU load for a particular game.
Analytics usage: showing telemetry event name.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for telemetry event type generated by analytics. | Primary Key |
name | character varying | Name of the telemetry event type. | |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
telemetry_event_t_namespace_id_name_uk | {"namespace_id, name"} | Unique |
telemetry_event_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of telemetry_event_t
- Referencing to
Table: telemetry_presence_game_state_t
This table contains the possible game states used in telemetry presence records.
For example IN_GAMEPLAY.
Analytics usage: Analysing and displaying events by game state.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for telemetry game state generated by analytics. | Primary Key |
name | character varying | Name of the telemetry presence game state. | |
lname | character varying | Long name of the telemetry presence game state. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
telemetry_presence_game_state_t_name_uk | {name} | Unique |
telemetry_presence_game_state_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of telemetry_presence_game_state_t
- Referenced by
Table: telemetry_presence_t
This table contains a subset of telemetry event instances which indicate a user presence in a game and their state at that time. This is unlike telemetry_t which is more flexible having any kind of content in a JSON payload.
For example a record of a .
Analytics usage: telemetry analysis, for instance to locate high resource usage during a stage of a game.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for telemetry presence event generated by analytics. | Primary Key |
flight_id | bigint | Relates to telemetry_presence_flight_t. | Foreign Key |
presence_ts | timestamp without time zone | Timestamp of when the telemetry presence event occurred. | |
game_state_id | bigint | Relates to telemetry_presence_game_state_t. | Foreign Key |
game_context | character varying | Describes the context of the event eg. pvp. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | |
device_platform_id | bigint | Relates to device_platform_t | Foreign Key |
user_id | bigint | Relates to user_t. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
telemetry_presence_t_user_id_i | {user_id} | Non-Unique |
telemetry_presence_t_flight_id_presence_ts_game_state_id_uk | {"flight_id, presence_ts, game_state_id"} | Unique |
telemetry_presence_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of telemetry_presence_t
- Referencing to
Table: telemetry_t
This table contains telemetry event instances and their JSON payloads. They have a namespace, may have an associated user, event type and time.
For example a record of CPU load in a game played by a specific user at a given time.
Analytics usage: telemetry analysis, for instance to locate high resource usage during a stage of a game.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for telemetry generated by analytics. | Primary Key |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Foreign Key |
user_id | bigint | Relates to user_t. Non-null if this telemetry event is associated with a particular user. | Foreign Key |
telemetry_event_id | bigint | Relates to telemetry_event_t. | |
event_ts | timestamp without time zone | When the event occurred. | |
event_number | character varying | An identifier for the event. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
payload_json | character varying | A JSON object containing data about the event. | |
flight_id | bigint | Relates to flight_t. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
telemetry_t_namespace_id_event_number_uk | {"namespace_id, event_number"} | Unique |
telemetry_t_flight_id_i | {flight_id} | Non-Unique |
telemetry_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of telemetry_t
- Referencing to
Table: flight_t
This table contains the flight records. A flight is like a game session. It has a namespace, platform and its own identifier.
For example a new session on Windows for a specific game namespace.
Analytics usage: Analysis grouped by flight.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for flight generated by analytics. | Primary Key |
namespace_id | bigint | Relates to namespace_t. | Foreign Key |
flight_number | character varying | Unique Id in AccelByte Gaming Services for flight. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
flight_t_namespace_id_flight_number_uk | {"namespace_id, flight_number"} | Unique |
flight_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of flight_t
- Referencing to
- Referenced by
Group: Lobby
Table: lobby_disconnected_code_t
This table contains a list of AccelByte Gaming Services lobby disconnection codes.
For example Code 4002.
Analytics usage: separating lobby session disconnections by code in reports. It can be more meaningful to use the lobby disconnected reason.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for lobby disconnected code generated by analytics. | Primary Key |
name | character varying | Name of the lobby disconnected code. | |
lname | character varying | Long name of the lobby disconnected code. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
lobby_disconnected_code_t_name_uk | {name} | Unique |
lobby_disconnected_code_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of lobby_disconnected_code_t
- Referenced by
Table: lobby_disconnected_reason_t
This table contains a list of reasons for lobby session disconnection.
For example server closed the connection.
Analytics usage: separating lobby session disconnections by reason in reports.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for lobby disconnected reason generated by analytics. | Primary Key |
name | character varying | Name of the lobby disconnected reason. | |
lname | character varying | Long name of the lobby disconnected reason. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
lobby_disconnected_reason_t_name_uk | {name} | Unique |
lobby_disconnected_reason_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of lobby_disconnected_reason_t
- Referenced by
Table: lobby_platform_t
This table contains if known, this specifies what the user is running the game on.
For example a particular user's lobby session is via Xbox.
Analytics usage: analysing lobby sessions by users' platform used.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for lobby platform generated by analytics. | Primary Key |
lobby_session_id | bigint | Relates to lobby_session_t. | Foreign Key |
platform_id | bigint | Platform Id eg. Windows. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
lobby_platform_t_platform_id_i | {platform_id} | Non-Unique |
lobby_platform_t_lobby_session_id_platform_id_uk | {"lobby_session_id, platform_id"} | Unique |
lobby_platform_t_job_run_id_i | {job_run_id} | Non-Unique |
lobby_platform_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of lobby_platform_t
- Referencing to
Table: lobby_session_connected_t
This table contains the start of a lobby session and its time
For example a specific lobby session started at 2022-11-01 10:40:16.000.
Analytics usage: analysis of lobby session start times, for instance to make a heat-map showing the busiest times of day.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for lobby session connected generated by analytics. | Primary Key |
lobby_session_id | bigint | Relates to lobby_session_t. | Foreign Key |
connected_ts | timestamp without time zone | UTC timestamp of the activity. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
lobby_session_connected_t_lobby_session_id_connected_ts_uk | {"lobby_session_id, connected_ts"} | Unique |
lobby_session_connected_t_job_run_id_i | {job_run_id} | Non-Unique |
lobby_session_connected_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of lobby_session_connected_t
- Referencing to
Table: lobby_session_disconnected_t
This table contains the end of a lobby session with the disconnection status code and reason
For example a specific lobby session ended at 2022-11-01 10:40:16.000 with the code 4002 and reason “server closed the connection”.
Analytics usage: analysis of the number of lobby disconnections and their reasons over time.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for lobby session disconnected generated by analytics. | Primary Key |
lobby_session_id | bigint | Relates to lobby_session_t. | Foreign Key |
disconnected_ts | timestamp without time zone | When the lobby session was disconnected. | |
reason_id | bigint | Why the lobby session was disconnected. | Foreign Key |
disconnected_code_id | bigint | Relates to lobby_disconnected_code_t. | Foreign Key |
is_forced | boolean | Was the disconnection forced?. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
lobby_session_disconnected_t_reason_id_i | {reason_id} | Non-Unique |
lobby_session_disconnected_t_lobby_session_id_disconnected__uk | {"lobby_session_id, disconnected_ts"} | Unique |
lobby_session_disconnected_t_job_run_id_i | {job_run_id} | Non-Unique |
lobby_session_disconnected_t_disconnected_code_id_i | {disconnected_code_id} | Non-Unique |
lobby_session_disconnected_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of lobby_session_disconnected_t
- Referencing to
Table: lobby_session_t
This table contains record of the lobby session user activity. This is when a user enters a game lobby.
For example a particular user creates a lobby session, the id of the user and the session will be recorded.
Analytics usage: links user to lobby session.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for lobby session generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key, Unique Constraint |
srv_session_id | character varying | Server session Id for this lobby session. | Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
lobby_session_t_user_id_i | {user_id} | Non-Unique |
lobby_session_t_pk | {id} | Unique |
game_lobby_session_t_uk | {"user_id, srv_session_id"} | Unique |
Relationship Diagram:
Show ER Diagram of lobby_session_t
- Referencing to
- Referenced by
Group: Matchmaking
Current version of schema supports both Matchmaking (Legacy) and the new Matchmaking service.
Table: match_party_user_t
This table contains the users within a match party. Duplicated data can be found in the user list in a match party, but this is provided for easier querying.
For example a specific user is in a specific match party.
Analytics usage: joining match party to its users.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for match party user relation generated by analytics. | Primary Key |
matchparty_id | bigint | Analytics identifier of a match party, a subgroup of a team. | Foreign Key |
user_id | bigint | Relates to user_t for the user who is in the match party. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
match_party_user_t_user_id_i | {user_id} | Non-Unique |
match_party_user_t_matchparty_id_user_id_uk | {"matchparty_id, user_id"} | Unique |
match_party_user_t_job_run_id_i | {job_run_id} | Non-Unique |
match_party_user_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of match_party_user_t
- Referencing to
Table: match_structure_team_t
This table contains the teams in a match structure. This is duplicated in the matchstructure_t table yet differs in that it is normalized for ease of querying.
For example a given match structure contains team 1 and 2, so this table would contain a row for team 1 and team 2 both for the same match structure.
Analytics usage: relating team to match structure.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for match structure team generated by analytics. | Primary Key |
matchstructure_id | bigint | Analytics identifier of the match structure which is a match in progress in a game. The team is in that match. | Foreign Key |
matchteam_id | bigint | Analytics identifier for the team within the match. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
match_structure_team_t_matchteam_id_i | {matchteam_id} | Non-Unique |
match_structure_team_t_matchstructure_id_matchteam_id_uk | {"matchstructure_id, matchteam_id"} | Unique |
match_structure_team_t_job_run_id_i | {job_run_id} | Non-Unique |
match_structure_team_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of match_structure_team_t
- Referencing to
Table: match_team_party_t
This table contains a utility table to relate match team to its match parties. This data is duplicated in a list of match parties inside the matchteam_t table, but this is provided for easier querying
For example a match team containing 3 match parties. This would show as 3 records, one per party.
Analytics usage: relating match team to match party.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for match party team relation generated by analytics. | Primary Key |
matchteam_id | bigint | Analytics identifier for match team, a group of players in a team. | Foreign Key |
matchparty_id | bigint | Analytics identifier for match party, a subgroup of players. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
match_team_party_t_matchteam_id_matchparty_id_uk | {"matchteam_id, matchparty_id"} | Unique |
match_team_party_t_matchparty_id_i | {matchparty_id} | Non-Unique |
match_team_party_t_job_run_id_i | {job_run_id} | Non-Unique |
match_team_party_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of match_team_party_t
- Referencing to
Table: matchmaking_channel_t
This table contains describes a match mode
For example PVP in Europe.
Analytics usage: analysing matchmaking by channel.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchmaking channel generated by analytics. | Primary Key |
channel_name | character varying | A codename for the type of match aka match mode, e.g.,ranked1v1. | |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchamking_channel_t_channel_name_namespace_id_uk | {"channel_name, namespace_id"} | Unique |
matchamking_channel_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchmaking_channel_t
- Referencing to
- Referenced by
Table: matchmaking_mainticket_matchsession_t
This table contains relates main tickets to match sessions. Normally a match session would have 0 or 1 main tickets but this rule may not hold, for instance due to test data.
For example a specific session has a specific main ticket.
Analytics usage: relating session to main ticket.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchmaking mainticket matchsession generated by analytics. | Primary Key |
mainticket_id | bigint | Relates to matchmaking_ticket_t for the main ticket. | Foreign Key |
matchsession_id | bigint | Relates to matchmaking_session_t for the main ticket. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchmaking_mainticket_matchsession_t_matchsession_id_i | {matchsession_id} | Non-Unique |
matchmaking_mainticket_matchsession_t_job_run_id_i | {job_run_id} | Non-Unique |
matchmaking_mainticket_matchs_mainticket_id_matchsession_id_uk | {"mainticket_id, matchsession_id"} | Unique |
matchmaking_mainticket_matchsession_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchmaking_mainticket_matchsession_t
- Referencing to
Table: matchmaking_mainticket_t
This table contains the first ticket in a series of tickets for the same party when matchmaking is initiated. There may be subsequent tickets added to this series by this party, and these will not be main tickets. This contains data about the main ticket including party, channel and time.
For example a main ticket that was created at 2023-01-11 07:35:11.000 with a user on the Xbox platform and in a specific party.
Analytics usage: defining the main ticket in a matchmaking process. Useful for investigating the users that start parties and how long it takes for them to make a successful match from the first time they enter a matchmaking queue.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchmaking mainticket generated by analytics. | Primary Key |
party_id | bigint | Relates to party_t. | Foreign Key |
channel_id | bigint | Relates to matchmaking_channel_t. | Foreign Key |
first_created_at | timestamp without time zone | When the main ticket was created. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchmaking_mainticket_t_party_id_first_created_at_uk | {"party_id, channel_id, first_created_at"} | Unique |
matchmaking_mainticket_t_job_run_id_i | {job_run_id} | Non-Unique |
matchmaking_mainticket_t_channel_id_i | {channel_id} | Non-Unique |
matchmaking_mainticket_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchmaking_mainticket_t
- Referencing to
- Referenced by
Table: matchmaking_ticket_cancelled_t
This table contains a record of when a ticket is cancelled, for instance when a party decides to exit matchmaking.
For example a ticket that is cancelled at 2022-10-28 01:27:56.110.
Analytics usage: analysing the times and counts of ticket cancellations in match sessions
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchmaking ticket cancelled generated by analytics. | Primary Key |
ticket_id | bigint | Relates to matchmaking_ticket_t for the cancelled ticket. | Foreign Key |
cancelled_at | timestamp without time zone | Timestamp of when the matchmaking ticket was cancelled. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchmaking_ticket_cancelled_t_ticket_id_uk | {ticket_id} | Unique |
matchmaking_ticket_cancelled_t_job_run_id_i | {job_run_id} | Non-Unique |
matchmaking_ticket_cancelled_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchmaking_ticket_cancelled_t
- Referencing to
Table: matchmaking_ticket_matched_t
This table contains a record of when a ticket fulfills the requirements of a match, creating a match session.
For example a specific ticket is matched at 2022-10-01 07:00:02.444 and creates a joinable session, i.e. others can join it with their tickets.
Analytics usage: analysing when tickets create match sessions.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchmaking ticket matched generated by analytics. | Primary Key |
ticket_id | bigint | Relates to matchmaking_ticket_t for the matched ticket. | Foreign Key |
matched_at | timestamp without time zone | When the ticket was matched. | |
is_joinable_session | boolean | True if the matchmaking session can be joined by players. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchmaking_ticket_matched_ticket_id_uk | {ticket_id} | Unique |
matchmaking_ticket_matched_t_job_run_id_i | {job_run_id} | Non-Unique |
matchmaking_ticket_matched_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchmaking_ticket_matched_t
- Referencing to
Table: matchmaking_ticket_t
This table contains a ticket which may enter a match, a request for matchmaking from a party.
For example a particular party requests to join a match in a game's PVP ranked channel at 2022-10-01 07:00:02.444.
Analytics usage: analysing when parties request to join matches.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchmaking ticket generated by analytics. | Primary Key |
party_id | bigint | Relates to party_t. | Foreign Key |
channel_id | bigint | Relates to matchmaking_channel_t. | Foreign Key |
created_at | timestamp without time zone | When the matchmaking ticket was created. | |
mainticket_id | bigint | Relates to matchmaking_mainticket_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchmaking_ticket_t_party_id_channel_id_queued_at_uk | {"party_id, channel_id, created_at"} | Unique |
matchmaking_ticket_t_mainticket_id_i | {mainticket_id} | Non-Unique |
matchmaking_ticket_t_job_run_id_i | {job_run_id} | Non-Unique |
matchmaking_ticket_t_channel_id_i | {channel_id} | Non-Unique |
matchmaking_ticket_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchmaking_ticket_t
- Referencing to
- Referenced by
Table: matchmaking_ticket_timedout_t
This table contains a record of when a ticket in a match times out. For instance when a party is waiting too long to become full.
For example a given ticket timed out at 2021-11-07 15:30:21.564.
Analytics usage: analysis of ticket time-outs. A game studio may wish to monitor these in order to reduce their number and increase player satisfaction.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchmaking ticket timedout generated by analytics. | Primary Key |
ticket_id | bigint | Relates to matchmaking_ticket_t | Foreign Key |
timedout_at | timestamp without time zone | When the ticket timed out | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchmaking_ticket_timedout_t_ticket_id_uk | {ticket_id} | Unique |
matchmaking_ticket_timedout_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchmaking_ticket_timedout_t
- Referencing to
Table: matchparty_t
This table contains contains a list of users in a match party. A match party is a group of players that have associated themselves with each other. Often a match party is one player alone, in which case its user list would contain only one user id. As players enter and leave a match party a new record is created. Note that match_party_user_t contains the same data in normalised format which may be easier to query.
For example a record could hold a match party with a list of 3 users in that party.
Analytics usage: linking match party to users within it.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for match party generated by analytics. | Primary Key |
origin_party_id | bigint | Refers to party_t.id, the party record of the user who created the match party. | Foreign Key |
user_list | character varying | Comma delimited list of analytics user ids for users currently in the match party. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchparty_t_origin_party_id_user_list_uk | {"origin_party_id, user_list"} | Unique |
matchparty_t_job_run_id_i | {job_run_id} | Non-Unique |
matchparty_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchparty_t
- Referencing to
- Referenced by
Table: matchsession_activity_t
This table contains record of the match session user activity, the session events and statuses contained within a match structure.
For example an event occurs of type “sessionWriteResult” resulting in the session status “matched” at 2022-12-11 02:01:59.695. The event is now associated with a match structure containing the newly matched user.
Analytics usage: analysis of the occurrences of the various types of match session related events.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchsession activity generated by analytics. | Primary Key |
matchsession_id | bigint | Relates to matchsession_t. | Foreign Key |
matchstructure_id | bigint | Relates to matchstructure_t. | Foreign Key |
matchsession_event_id | bigint | Relates to matchsession_event_type_t. | Foreign Key |
matchsession_status_id | bigint | Relates to matchsession_status_type_t. | Foreign Key |
start_ts | timestamp without time zone | Start time of the matchsession activity. | |
end_ts | timestamp without time zone | UTC timestamp for the end of the activity. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchsession_activity_t_start_ts_i | {start_ts} | Non-Unique |
matchsession_activity_t_matchstructure_id_i | {matchstructure_id} | Non-Unique |
matchsession_activity_t_matchsession_status_id_i | {matchsession_status_id} | Non-Unique |
matchsession_activity_t_matchsession_id_matchstructure_id_m_uk | {"matchsession_id, matchstructure_id, matchsession_event_id, matchsession_status_id, start_ts"} | Unique |
matchsession_activity_t_matchsession_event_id_i | {matchsession_event_id} | Non-Unique |
matchsession_activity_t_job_run_id_i | {job_run_id} | Non-Unique |
matchsession_activity_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchsession_activity_t
- Referencing to
Table: matchsession_dedicatedserver_t
This table contains relates match session to dedicated servers. This may be many-to-many.
For example a dedicated server containing a list of several match sessions running on it.
Analytics usage: analysis of dedicated server usage via the match sessions running on them.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchsession dedicatedserver generated by analytics. | Primary Key |
matchsession_id | bigint | Relates to matchsession_t. | Foreign Key, Unique Constraint |
dedicatedserver_id | bigint | Relates to dedicatedserver_t. | Foreign Key, Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchsession_dedicatedserver_t_job_run_id_i | {job_run_id} | Non-Unique |
matchsession_dedicatedserver_t_dedicatedserver_id_i | {dedicatedserver_id} | Non-Unique |
matchsession_dedicatedserver_t_uk | {"matchsession_id, dedicatedserver_id"} | Unique |
matchsession_dedicatedserver_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchsession_dedicatedserver_t
- Referencing to
Table: matchsession_ended_t
This table contains record of when match sessions ended. One record may represent many sessions ending at the same time.
For example a match session that ended at 2021-08-19 15:29:49.000.
Analytics usage: analysing the end of match sessions, perhaps useful in conjunction with other data about the start in order to monitor match session duration.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchsession ended generated by analytics. | Primary Key |
matchsession_id | bigint | Relates to matchsession_t. | Foreign Key |
ended_at | timestamp without time zone | When the match session ended. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchsession_ended_t_matchsession_id_uk | {matchsession_id} | Unique |
matchsession_ended_t_job_run_id_i | {job_run_id} | Non-Unique |
matchsession_ended_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchsession_ended_t
- Referencing to
Table: matchsession_event_type_t
This table contains a list of match session event type codenames One of sessionWriteResult, sessionRemoveUserID, SessionQueued, SessionDeleted, SessionAddUserID, SessionDequeued, startMatchSessionCreated
For example sessionRemoveUserID which means a user leaves a session.
Analytics usage: displaying the match session event type, for instance in a legend.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchsession event type generated by analytics. | Primary Key |
name | character varying | Name of the match session event type. | |
lname | character varying | Long name of the match session event type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchsession_event_type_t_name_uk | {name} | Unique |
matchsession_event_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchsession_event_type_t
- Referenced by
Table: matchsession_status_type_t
This table contains a list of match session status type codenames. One of matched, SessionFull, SessionInQueue, SessionTimeout, done.
For example SessionFull meaning the session has the maximum number of players allowed.
Analytics usage: displaying the match session status, for instance in a legend.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchsession status type generated by analytics. | Primary Key |
name | character varying | Name of the match session status type. | |
lname | character varying | Long name of the match session status type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchsession_status_type_t_name_uk | {name} | Unique |
matchsession_status_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchsession_status_type_t
- Referenced by
Table: matchsession_t
This table contains identifies a match session with its AccelByte Gaming Services id and specifies which namespace it is in. This is created when a game starts using parties in the match.
For example a match session that is created with the AccelByte Gaming Services match session id of bc58881ed21a4df2ac00f9a36c9e9a11 in a particular game namespace.
Analytics usage: to find the match sessions in a namespace.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for matchsession generated by analytics. | Primary Key |
match_id | character varying | AccelByte Gaming Services Match Id. | Unique Constraint |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Foreign Key, Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchsession_t_job_run_id_i | {job_run_id} | Non-Unique |
matchsession_t_uk | {"namespace_id, match_id"} | Unique |
matchsession_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchsession_t
- Referencing to
- Referenced by
Table: matchstructure_t
This table contains this a match in progress in a game, the highest level entity in the team composition system. It contains match teams which in turn contain match parties. Note that match_structure_team_t contains equivalent data which is easier to query due to being normalised.
For example a specific match structure that contains 2 teams. The list of the 2 ids of the teams are contained in this record.
Analytics usage: relating the top level match structure to the teams within it.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for match structure generated by analytics. | Primary Key |
team_list | character varying | A comma delimited list of match team analytics ids for the teams in this match structure. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchstructure_t_team_list_uk | {team_list} | Unique |
matchstructure_t_job_run_id_i | {job_run_id} | Non-Unique |
matchstructure_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchstructure_t
- Referencing to
- Referenced by
Table: matchteam_t
This table contains a match team contains one or more match parties. These teams are the groups that appear in game as groups who compete or cooperate. Drilling down, a team may contain one to many users.
For example a team containing 3 parties intent on joining the same game.
Analytics usage: relating team to parties.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for match team generated by analytics. | Primary Key |
party_list | character varying | Comma delimited list of matchparty_t.id, a list of match parties in this team. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
matchteam_t_party_list_uk | {party_list} | Unique |
matchteam_t_job_run_id_i | {job_run_id} | Non-Unique |
matchteam_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of matchteam_t
- Referencing to
- Referenced by
Table: party_t
This table contains A Party, also known as a lobby party. Used with matchparty_t to find the users in the party.
For example a new party created within a particular game.
Analytics usage: relating party to namespace.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for party generated by analytics. | Primary Key |
party_number | character varying | Unique id in AccelByte Gaming Services for party. | |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
party_t_party_number_uk | {"party_number, namespace_id"} | Unique |
party_t_namespace_id_i | {namespace_id} | Non-Unique |
party_t_job_run_id_i | {job_run_id} | Non-Unique |
party_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of party_t
- Referencing to
- Referenced by
Group: Policy
Table: agreement_t
This table contains the record of agreements when a user signs up in the player portal.
For example a legal agreement.
Analytics usage: analysis of users' agreement activities.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for agreement generated by analytics. | Primary Key |
policy_id | bigint | Agreement policy id. | Foreign Key |
policy_version_id | bigint | Agreement policy version id. | Foreign Key |
policy_localization_id | bigint | Agreement policy localization id for multi language use. | Foreign Key |
user_id | bigint | Relates to user_t for the user who made the agreement. | Foreign Key |
is_accepted | boolean | Was the agreement accepted?. | |
start_ts | timestamp without time zone | Start time of the agreement activity. | |
end_ts | timestamp without time zone | UTC timestamp for the end of the activity. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
agreement_t_uk | {"user_id, policy_id, policy_version_id, policy_localization_id, start_ts"} | Unique |
agreement_t_policy_version_id_i | {policy_version_id} | Non-Unique |
agreement_t_policy_localization_id_i | {policy_localization_id} | Non-Unique |
agreement_t_policy_id_i | {policy_id} | Non-Unique |
agreement_t_job_run_id_i | {job_run_id} | Non-Unique |
agreement_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of agreement_t
- Referencing to
Table: policy_localization_t
This table contains holds an AccelByte Gaming Services identifier for a localized policy version
For example a policy localization identifier with its owning policy.
Analytics usage: supplying the identifier of a policy to look up in AccelByte Gaming Services.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for policy localization generated by analytics. | Primary Key |
policy_localization_number | character varying | An identifier for localized policy. | Unique Constraint |
policy_version_id | bigint | The version of the localized policy. | Foreign Key, Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
policy_localization_t_uk | {"policy_localization_number, policy_version_id"} | Unique |
policy_localization_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of policy_localization_t
- Referencing to
- Referenced by
Table: policy_t
This table contains a policy defined by a namespace
For example terms of service for a particular game.
Analytics usage: relating policy to namespace.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for policy generated by analytics. | Primary Key |
policy_number | character varying | An identifier for policy. | Unique Constraint |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Foreign Key, Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
policy_t_uk | {"policy_number, namespace_id"} | Unique |
policy_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of policy_t
- Referencing to
- Referenced by
Table: policy_version_t
This table contains versions of a policy, holds an AccelByte Gaming Services identifier for the version
For example a version identifier and its owning policy.
Analytics usage: supplying the identifier of a policy to look up in AccelByte Gaming Services.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for policy version generated by analytics. | Primary Key |
policy_version_number | character varying | The version of the policy. | Unique Constraint |
policy_id | bigint | Relates to policy_t. | Foreign Key, Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
policy_version_t_uk | {"policy_version_number, policy_id"} | Unique |
policy_version_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of policy_version_t
- Referencing to
- Referenced by
Table: studio_t
This table contains game studios and their names. Note that namespace_t contains a studio_id which allows studios to contain namespaces.
For example a studio with its system name and readable formatted name.
Analytics usage: reporting by studio, usually via their namespaces.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for studio generated by analytics. | Primary Key |
name | character varying | Name of the studio. | Unique Constraint |
lname | character varying | Long name of the studio. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
studio_t_pk | {id} | Unique |
studio_t_name_uk | {name} | Unique |
Relationship Diagram:
Show ER Diagram of studio_t
- Referenced by
Group: Dedication Server
Table: dedicatedserver_allocation_t
This table contains a log of the processes running on dedicated servers. These servers run multiplayer game matches.
For example the “Started” event for a dedicated server running a new match starting at 2022-11-11 11:00.
Analytics usage: analysis of dedicated server usage.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
dedicatedserver_id | bigint | AccelByte Gaming Services Server Id. | Foreign Key, Unique Constraint |
status | character varying | Server status eg. Started. | |
description | character varying | Description of server status. | |
start_ts | timestamp without time zone | Start of the allocation. | Unique Constraint |
end_ts | timestamp without time zone | End of the allocation. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
dedicatedserver_allocation_t_job_run_id_i | {job_run_id} | Non-Unique |
dedicatedserver_allocation_t_status_t_uk | {"dedicatedserver_id, start_ts"} | Unique |
dedicatedserver_allocation_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of dedicatedserver_allocation_t
- Referencing to
Table: dedicatedserver_detail_t
This table contains metadata attributes for dedicated servers
For example a given data server's capacity in terms of CPU memory.
Analytics usage: reporting on dedicated server numbers split by configuration.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
dedicatedserver_id | bigint | AccelByte Gaming Services Server Id. | Foreign Key, Unique Constraint |
dsprovider_id | bigint | Id of the provider in dedicatedserver_provider_t. | Foreign Key |
dsregion_id | bigint | Id of the region in dedicated_server_region_t. | Foreign Key |
deployment | character varying | Describes the server deployment e.g.,CustomGame. | |
image_version | character varying | Server Image container Id. | |
game_version | character varying | Version of the game running on the server. | |
ip_address | character varying | Server IP Address. | |
port_main | integer | Server main port. | |
protocol | character varying | Server protocol e.g.,UDP. | |
cpu_limit | integer | Max CPU for the server. | |
memory_limit | integer | Max RAM for the server. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
dedicatedserver_detail_t_job_run_id_i | {job_run_id} | Non-Unique |
dedicatedserver_detail_t_dsregion_id_i | {dsregion_id} | Non-Unique |
dedicatedserver_detail_t_dsprovider_id_i | {dsprovider_id} | Non-Unique |
dedicatedserver_detail_t_uk | {dedicatedserver_id} | Unique |
dedicatedserver_detail_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of dedicatedserver_detail_t
- Referencing to
Table: dedicatedserver_provider_t
This table contains a list of possible cloud services providers
For example AWS for Amazon Web Services.
Analytics usage: comparing costs from each cloud provider.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
name | character varying | Name of the dedicated server provider. | Unique Constraint |
lname | character varying | Long name of the dedicated server provider. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
dedicatedserver_provider_t_uk | {name} | Unique |
dedicatedserver_provider_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of dedicatedserver_provider_t
- Referenced by
Table: dedicatedserver_region_t
This table contains a list cloud provider (e.g., AWS) regions used for the dedicated servers. Not always defined for a dedicated server.
For example AWS us-west-2 region which is used by a particular server.
Analytics usage: reporting on dedicated server numbers split by region.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
name | character varying | Name of the dedicated server region. | Unique Constraint |
lname | character varying | Long name of the dedicated server region. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
dedicatedserver_region_t_uk | {name} | Unique |
dedicatedserver_region_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of dedicatedserver_region_t
- Referenced by
Table: dedicatedserver_status_t
This table contains state of the dedicated servers eg. Ready, waiting, busy, creating, removing and the times at which those statuses occurred
For example a particular server entered the READY status at 2022-08-29 14:52:50.449.
Analytics usage: analysis of dedicated server activity and performance, for instance boot up time.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
dedicatedserver_id | bigint | AccelByte Gaming Services Dedicated Server Id. | Foreign Key, Unique Constraint |
status | character varying | Server Status eg. BUSY. | |
start_ts | timestamp without time zone | Start of the status being in effect. | Unique Constraint |
end_ts | timestamp without time zone | End of the status being in effect. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
dedicatedserver_status_t_job_run_id_i | {job_run_id} | Non-Unique |
dedicatedserver_status_t_uk | {"dedicatedserver_id, start_ts"} | Unique |
dedicatedserver_status_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of dedicatedserver_status_t
- Referencing to
Table: dedicatedserver_t
This table contains when a match is started a dedicated server is instantiated for that match. This is a list of those occurrences and the ids of those servers.
For example A record of the dedicated server ds-accelbytetesting-us-west-2-test1-v0.0.1-armada-2f4f-4349-bf27-9c45117cd2e7.
Analytics usage: naming the dedicate servers.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
pod_name | character varying | Deployment Identifier. | Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
dedicatedserver_t_job_run_id_i | {job_run_id} | Non-Unique |
dedicatedserver_t_uk | {pod_name} | Unique |
dedicatedserver_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of dedicatedserver_t
- Referencing to
- Referenced by
Group: Entitlement
Table: entitlement_activity_t
This table contains record of the entitlement user activity. When an entitlement, the offer of an item to a user, is given to or consumed by a user.
For example The purchase of an entitlement to a lootbox by a user that changed their count of this entitlement from 0 to 1.
Analytics usage: analysis of users' entitlement activities.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for entitlement activity generated by analytics. | Primary Key |
entitlement_id | bigint | AccelByte Gaming Services Entitlement Id. | Foreign Key |
current_count | integer | Current count of available Entitlements. | |
change_count | integer | Change in count of Entitlements. | |
source_id | bigint | Relates to entitlement_source_type_t. | Foreign Key |
start_ts | timestamp without time zone | Start time of the entitlement activity. | |
end_ts | timestamp without time zone | UTC timestamp for the end of the activity. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
entitlement_usage_t_entitlement_id_change_count_source_star_uk | {"entitlement_id, change_count, source_id, start_ts"} | Unique |
entitlement_activity_t_source_id_i | {source_id} | Non-Unique |
entitlement_activity_t_job_run_id_i | {job_run_id} | Non-Unique |
entitlement_usage_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of entitlement_activity_t
- Referencing to
Table: entitlement_class_type_t
This table contains a list of classes of entitlement.
For example LOOTBOX class.
Analytics usage: reporting by entitlement class.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
name | character varying | Name of the entitlement class type. | |
lname | character varying | Long name of the entitlement class type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
entitlement_class_type_t_name_uk | {name} | Unique |
entitlement_class_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of entitlement_class_type_t
- Referenced by
Table: entitlement_item_t
This table contains relates an entitlement, an offer of an item, to its item.
For example the offer of an entitlement of 10 in-game platinum coins to a player
Analytics usage: joining entitlement to item.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
entitlement_id | bigint | Relates to entitlement_t. | Foreign Key |
item_id | bigint | Relates to item_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
entitlement_item_t_job_run_id_i | {job_run_id} | Non-Unique |
entitlement_item_t_item_id_i | {item_id} | Non-Unique |
entitlement_item_t_entitlement_id_uk | {entitlement_id} | Unique |
entitlement_item_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of entitlement_item_t
- Referencing to
Table: entitlement_property_t
This table contains attributes of the entitlements
For example specifications of an entitlement to a Tuxedo cosmetic on offer, that it is of a durable type and class of entitlement.
Analytics usage: analysis of entitlement activities by class, type or name.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
entitlement_id | bigint | Relates to entitlement_t. | Foreign Key |
class_id | bigint | Relates to entitlement_class_type_t. | Foreign Key |
entitlement_type_id | bigint | Relates to entitlement_type_t. | Foreign Key |
created_at | timestamp without time zone | When these properties were recorded. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
name | character varying | Name of the entitlement properties. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
entitlement_property_t_job_run_id_i | {job_run_id} | Non-Unique |
entitlement_property_t_entitlement_type_id_i | {entitlement_type_id} | Non-Unique |
entitlement_property_t_entitlement_id_uk | {entitlement_id} | Unique |
entitlement_property_t_class_id_i | {class_id} | Non-Unique |
entitlement_property_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of entitlement_property_t
- Referencing to
Table: entitlement_source_type_t
This table contains a list of methods via which a user can gain an item.
For example IAP for in-app-purchase.
Analytics usage: analysis of entitlement activities by source, for example to compare the number of IAPs to the number of rewards.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
name | character varying | Name of the entitlement source type. | |
lname | character varying | Long name of the entitlement source type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
entitlement_source_type_t_name_uk | {name} | Unique |
entitlement_source_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of entitlement_source_type_t
- Referenced by
Table: entitlement_status_t
This table contains state of entitlements. This could change over time for a specific entitlement.
For example a particular entitlement became active at 2022-01-07 18:43:37.433
Analytics usage: analysis of the time periods between entitlement status changes, for instance to find out the average lifetime of an entitlement on offer.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
entitlement_id | bigint | Relates to entitlement_t. | Foreign Key |
status_id | bigint | Relates to entitlement_status_t. | Foreign Key |
start_ts | timestamp without time zone | Start of the status being in effect. | |
end_ts | timestamp without time zone | End of the status being in effect. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
entitlement_status_t_status_id_i | {status_id} | Non-Unique |
entitlement_status_t_job_run_id_i | {job_run_id} | Non-Unique |
entitlement_status_t_entitlement_id_uk | {entitlement_id} | Unique |
entitlement_status_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of entitlement_status_t
- Referencing to
Table: entitlement_status_type_t
This table contains a list of possible statuses for an entitlement: active, revoked, inactive
For example ACTIVE meaning available to users.
Analytics usage: reporting by entitlement status.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
name | character varying | Name of the entitlement status type. | |
lname | character varying | Long name of the entitlement status type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
entitlement_status_type_t_name_uk | {name} | Unique |
entitlement_status_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of entitlement_status_type_t
- Referenced by
Table: entitlement_t
This table contains an entitlement is when an item is offered to a user. When is is delivered a fulfillment is created for an entitlement
For example the id of a specific entitlement and the id of the user it is offered to.
Analytics usage: joining the various entitlement tables to user.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for entitlement generated by analytics. | Primary Key |
entitlement_number | character varying | AccelByte Gaming Services Entitlement Id. | |
user_id | bigint | Relates to user_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
entitlement_t_user_id_i | {user_id} | Non-Unique |
entitlement_t_job_run_id_i | {job_run_id} | Non-Unique |
entitlement_t_entitlement_number_entitlement_namespace_uk | {"entitlement_number, user_id"} | Unique |
entitlement_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of entitlement_t
- Referencing to
- Referenced by
Table: entitlement_type_t
This table contains the possible types of entitlement: durable, consumable
For example DURABLE meaning persistent unless revoked. It cannot be reduced in-game like, for instance, mana which would be a CONSUMABLE.
Analytics usage: separating reports into durable and consumable entitlement types.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
name | character varying | Name of the entitlement type. | |
lname | character varying | Long name of the entitlement type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
entitlement_type_t_name_uk | {name} | Unique |
entitlement_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of entitlement_type_t
- Referenced by
Table: entitlementitem_t
Internal use
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for entitlement item generated by analytics. | Primary Key |
entitlement_item_number | character varying | Entitlement Item real world identifier | |
entitlement_namespace_id | bigint | Namespace in which this entitlement item is | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
entitlement_ingameitem_t_item_number_game_namespace_id_uk | {"entitlement_item_number, entitlement_namespace_id"} | Unique |
entitlement_ingameitem_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of entitlementitem_t
- Referencing to
Group: Item
Table: item_bundle_t
This table contains an item can be an bundle of items in which case its bundle id and creation time are shown here.
For example a bundle on offer as an item created at 2023-02-20 12:53:03.000.
Analytics usage: linking item to bundle.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item bundle generated by analytics. | Primary Key |
item_id | bigint | Relates to item_t. | Foreign Key |
bundle_item_id | bigint | Relates to item_t for the Bundle item containing this item. | Foreign Key |
start_ts | timestamp without time zone | Start of the item bundle lifetime. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | End of the item bundle lifetime. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
item_bundle_t_item_id_bundle_item_id_start_ts_uk | {"item_id, bundle_item_id, start_ts"} | Unique |
item_bundle_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of item_bundle_t
- Referencing to
Table: item_clone_t
This table contains an item can be cloned from a game namespace to its publisher namespace, this is a log of that cloning. Used for instance when a publisher wants to sell an in game item on their website.
For example item A is cloned to its publisher namespace as item B at 2023-02-20 12:53:03.000.
Analytics usage: analysis of the cloning of items.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item clone generated by analytics. | Primary Key |
item_id | bigint | Relates to item_t. | Foreign Key |
target_item_id | bigint | Relates to item_t for the target item of the cloning. | Foreign Key |
start_ts | timestamp without time zone | Start of the item clone lifetime. | |
end_ts | timestamp without time zone | End of the item clone lifetime. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
item_clone_t_item_id_target_item_id_start_ts_uk | {"item_id, target_item_id, start_ts"} | Unique |
item_clone_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of item_clone_t
- Referencing to
Table: item_coin_t
This table contains an item can be a coin otherwise known as a currency in which case its details are shown here.
For example an item which is some in-game currency for a particular game.
Analytics usage: linking item to game-specific currency.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item coin generated by analytics. | Primary Key |
item_id | bigint | Relates to item_t. | Foreign Key |
target_currency_code_id | bigint | Relates to currency_t. | Foreign Key |
start_ts | timestamp without time zone | Start of the item coin lifetime. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
item_coin_t_item_id_start_ts_uk | {"item_id, start_ts"} | Unique |
item_coin_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of item_coin_t
- Referencing to
Table: item_image_t
This table contains details of an image related to an item. This can apply to any type of item.
For example an item image with the name “Swimwear” and caption “Summer Swimwear” located at the URI https://cdn.gamename.com/files/6015237f64f2331.png
Analytics usage: displaying images for an item when looking at drill-down data for that specific item.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item image generated by analytics. | Primary Key |
item_id | bigint | Relates to item_t. | Foreign Key |
name | character varying | Name of the item image. | |
caption | character varying | Caption describing the Image. | |
url | character varying | URL of the Image. | |
small_url | character varying | Short URL of the Image. | |
start_ts | timestamp without time zone | Start of the Image lifetime. | |
end_ts | timestamp without time zone | End of the Image lifetime. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
item_image_t_item_id_name_start_ts_uk | {"item_id, name, start_ts"} | Unique |
item_image_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of item_image_t
- Referencing to
Table: item_localization_t
This table contains localized text for items, the title, description and long description for different locales.
For example a lootbox item with its Spanish language details such as title “caja de botín especial”
Analytics usage: user interface customization to fit the viewer's language.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item localization generated by analytics. | Primary Key |
item_id | bigint | Relates to item_t. | Foreign Key |
locale_id | bigint | Relates to locale_t which identifies locale. | Foreign Key |
title | character varying | Localized item title. | |
description | character varying | Localized item description. | |
long_description | character varying | Localized item long description. | |
start_ts | timestamp without time zone | Start of the item localization lifetime. | |
end_ts | timestamp without time zone | End of the item localization lifetime. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
item_localization_t_item_id_locale_id_uk | {"item_id, locale_id, start_ts"} | Unique |
item_localization_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of item_localization_t
- Referencing to
Table: item_property_t
This table contains metadata for items, versioned rows per item showing item count, catalog category and namespace and other details.
For example the currently active definition for a specific item including that its entitlement type is consumable, it can be used 100 times but only 5 times per user.
Analytics usage: detailed analysis of items and their attributes.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item properties generated by analytics. | Primary Key |
item_id | bigint | Relates to item_t. | Foreign Key |
base_app_id | bigint | Relates to item_t for the base app item id if the item is an app. | Foreign Key |
entitlement_type_id | bigint | Relates to entitlement_type_t. | Foreign Key |
use_count | integer | Count of uses of this entitlement. | |
is_stackable | boolean | Is the entitlement stackable?. | |
category_path_id | bigint | Relates to catalog_category_t. | Foreign Key |
thumbnail_url | character varying | URL of item thumbnail image. | |
max_count_per_user | integer | Maximum number of this item for one user. | |
max_count | integer | Maximum number of this item overall. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
start_ts | timestamp without time zone | Start of the lifetime of the item properties. | |
end_ts | timestamp without time zone | End of the lifetime of the item properties. | |
target_namespace_id | bigint | Namespace for the item. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
item_property_t_item_id_start_ts_uk | {"item_id, start_ts"} | Unique |
item_property_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of item_property_t
- Referencing to
Table: item_region_price_t
This table contains pricing for items is stored here. They and pricing and discounts can change over time.
For example a specific item was assigned the price of $1.99 in USD when sold in the USA between 2022-01-14 02:03:21.000 and 2023-01-11 07:35:11.000.
Analytics usage: financial calculations related to item purchases.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item region price generated by analytics. | Primary Key |
item_id | bigint | Relates to item_t. | Foreign Key |
country_id | bigint | Relates to country_t. | Foreign Key |
price | integer | Price of the item in this region. | |
discount_percentage | integer | Percentage discount of this item in this region. | |
discount_amount | integer | Discount amount of this item in this region. | |
discounted_price | integer | Discounted price of this item in this region. | |
currency_id | bigint | Relates to currency_t. | Foreign Key |
purchase_at | timestamp without time zone | When the item can be purchased. | |
expire_at | timestamp without time zone | When the possibility to purchase ends. | |
discount_purchase_at | timestamp without time zone | When the item can be purchased at discount. | |
discount_expire_at | timestamp without time zone | When the possibility to purchase at discount ends. | |
start_ts | timestamp without time zone | Start of the regional price effectivity. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | End of the regional price effectivity. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
item_region_price_t_item_id_country_id_start_ts_uk | {"item_id, country_id, start_ts"} | Unique |
item_region_price_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of item_region_price_t
- Referencing to
Table: item_status_t
This table contains status for items. The status type can change over time.
For example a specific item was deleted at 2022-01-14 02:03:21.000.
Analytics usage: analysis of the lifecycle of items.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item status generated by analytics. | Primary Key |
item_id | bigint | Relates to item_t. | Foreign Key |
status_id | bigint | Relates to item_status_type_t. | |
start_ts | timestamp without time zone | Start of the item status lifetime. | |
end_ts | timestamp without time zone | End of the item status lifetime. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
item_status_t_item_id_start_ts_uk | {"item_id, start_ts"} | Unique |
item_status_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of item_status_t
- Referencing to
Table: item_status_type_t
This table contains list of statuses for items: ACTIVE, INACTIVE, DELETED
For example ACTIVE meaning the item is available to users. It may be set to inactive if, for instance, if an item had to be removed from the market due to copyright issues.
Analytics usage: displaying item status, for instance in a legend.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item status type generated by analytics. | Primary Key |
name | character varying | Name of the item status type. | |
lname | character varying | Long name of the item status type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
item_status_type_t_name_uk | {name} | Unique |
item_status_type_t_pk | {id} | Unique |
Table: item_t
This table contains the list of items with their AccelByte Gaming Services id. Items can be of various types and distributed via entitlements. An item can go to many users.
For example a golden sword within a fantasy game namespace.
Analytics usage: linking item to namespace, for instance to count the number of items in a namespace over all time.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item generated by analytics. | Primary Key |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Foreign Key |
item_number | character varying | AccelByte Gaming Services Item Id. | Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
item_t_item_number_uk | {item_number} | Unique |
game_item_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of item_t
- Referencing to
- Referenced by
Table: item_tag_t
This table contains freeform tagging of items to describe them
For example the tag 'Uncommon' to indicate the rarity of an in-game asset item.
Analytics usage: describing items. A tag cloud could be created with size based on tag occurrence.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item tags generated by analytics. | Primary Key |
item_id | bigint | Relates to item_t. | Foreign Key |
tag | character varying | Descriptive tag e.g.,Featured. | |
start_ts | timestamp without time zone | Start of the tag lifetime. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | End of the tag lifetime. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
item_tag_t_item_id_tag_start_ts_uk | {"item_id, tag, start_ts"} | Unique |
item_tag_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of item_tag_t
- Referencing to
Table: item_type_t
This table contains a list of item types eg. APP, COINS, SUBSCRIPTION
For example COINS meaning in-game or real currency.
Analytics usage: displaying type of item.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item type generated by analytics. | Primary Key |
name | character varying | Name of the item type. | Unique Constraint |
lname | character varying | Long name of the item type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
game_item_type_t_pk | {id} | Unique |
game_item_type_t_name_uk | {name} | Unique |
Relationship Diagram:
Show ER Diagram of item_type_t
- Referencing to
- Referenced by
Table: item_variant_t
This table contains an item can have multiples SKUs and names, these are shown here
For example the same item could have multiple records each with different SKUs and slightly different names that are updated over time if the administrator decided to rename the item.
Analytics usage: showing low level details about an item.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for item variant generated by analytics. | Primary Key |
item_id | bigint | Relates to item_t. | Foreign Key, Unique Constraint |
name | character varying | Name of the item variant. | Unique Constraint |
item_type_id | bigint | Relates to item_type_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
sku | character varying | Real world product identifier for the item. | Unique Constraint |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
item_variant_t_uk | {"item_id, name, sku"} | Unique |
item_variant_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of item_variant_t
- Referencing to
- Referenced by
Group: Ecommerce
Table: ecommerce_platform_t
This tables contains the list of eCommerce platforms in use, with unique names.
For example Steam.
Analytics usage: eCommerce Platform labels.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
name | character varying | eCommerce platform name. | Unique Constraint |
lname | character varying | Long name. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
ecommerce_platform_t_uk | {name} | Unique |
ecommerce_platform_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of ecommerce_platform_t
- Referenced by
Table: catalog_category_t
This table contains a list of categories of items. Items are things that could be sold e.g.,in game items, bundles, virtual currencies, game giveaways. The path of the catalog category is unique within a namespace.
For example a catalog category with the path “/coins” for a specific game.
Analytics usage: defining and naming the possible catalog categories.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for catalog category generated by analytics. | Primary Key |
path | character varying | The unique path of the Category within the Namespace. | |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
catalog_category_t_namespace_path_uk | {"namespace_id, path"} | Unique |
catalog_category_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of catalog_category_t
- Referencing to
- Referenced by
Table: order_item_t
This table contains shows the items and their quantities that an order contains.
For example an order containing 2 items would result in two rows with the same order_id each having one of the relevant item_ids.
Analytics usage: relating orders to their items.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for order item generated by analytics. | Primary Key |
order_id | bigint | Relates to order_t. | Foreign Key |
qty | integer | Quantity of the Item in the Order. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
item_id | bigint | Relates to item_t. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
order_item_t_order_id_item_id_uk | {"order_id, item_id"} | Unique |
order_item_t_job_run_id_i | {job_run_id} | Non-Unique |
order_item_t_item_id_i | {item_id} | Non-Unique |
order_item_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of order_item_t
- Referencing to
Table: order_payment_t
This table contains relates an order to a payment. There can be 0 or 1 payments per order.
For example a specific order that was paid.
Analytics usage: relating orders to payments.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for order payment generated by analytics. | Primary Key |
order_id | bigint | Relates to order_t. | Foreign Key |
payment_id | bigint | Relates to payment_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
order_payment_t_payment_id_i | {payment_id} | Non-Unique |
order_payment_t_order_id_payment_id_uk | {"order_id, payment_id"} | Unique |
order_payment_t_job_run_id_i | {job_run_id} | Non-Unique |
order_payment_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of order_payment_t
- Referencing to
Table: order_property_t
This table contains order properties e.g.,price and the time period for which they are valid.
For example an order that cost $2 USD, created at 2021-10-21 12:31:36.000 and due to expire at 2021-11-21 12:31:36.000.
Analytics usage: including order details in analyses such as those that look at order costs to the users.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for order properties generated by analytics. | Primary Key |
order_id | bigint | Relates to order_t. | Foreign Key |
price | integer | Price of the Order. | |
discounted_price | integer | Discounted price of the Order. | |
start_ts | timestamp without time zone | Start of the lifetime of the order properties. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | End of the lifetime of the order properties. | |
currency_id | bigint | Relates to currency_t. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
order_property_t_order_id_start_ts_uk | {"order_id, start_ts"} | Unique |
order_property_t_job_run_id_i | {job_run_id} | Non-Unique |
order_property_t_currency_id_i | {currency_id} | Non-Unique |
order_property_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of order_property_t
- Referencing to
Table: order_status_t
This table contains the status of an order. This changes for an order over time.
For example one record for an order having charged status at 2021-11-21 12:31:36.000 and another record for the same order with status fulfilled at the later time of 2021-11-21 15:31:36.000.
Analytics usage: tracking order status changes over time.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for order status generated by analytics. | Primary Key |
order_id | bigint | Relates to order_t. | Foreign Key |
order_status_id | bigint | Relates to order_status_type_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
start_ts | timestamp without time zone | The start of when the status was in effect. | |
end_ts | timestamp without time zone | The end of when the status was in effect. | |
status_reason | character varying | Describes why this status occurred. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
order_status_t_order_status_id_i | {order_status_id} | Non-Unique |
order_status_t_order_id_order_status_id_start_ts_uk | {"order_id, order_status_id, start_ts"} | Unique |
order_status_t_job_run_id_i | {job_run_id} | Non-Unique |
order_status_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of order_status_t
- Referencing to
Table: order_status_type_t
This table contains a list of statuses that an order can have. One of SUCCESS, FAIL, CHARGED, FULFILLED, FULFILL_FAILED, INIT, REFUNDING, REFUNDED, CLOSED.
For example REFUNDED meaning the user got reimbursed for the order.
Analytics usage: displaying order status.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for order status type generated by analytics. | Primary Key |
name | character varying | Name of the order status type. | Unique Constraint |
lname | character varying | Long name of the order status type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
game_order_status_t_pk | {id} | Unique |
game_order_status_t_name_uk | {name} | Unique |
Relationship Diagram:
Show ER Diagram of order_status_type_t
- Referenced by
Table: order_t
This table contains a record of the order user activity with AccelByte Gaming Services order id and user, which implies namespace. Created when a user orders an item.
For example a user placing an order with order id O423102251203926022.
Analytics usage: relating order to user.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for order generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key, Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
order_number | character varying | The real world identifier for order. | Unique Constraint |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
order_t_user_id_i | {user_id} | Non-Unique |
order_t_job_run_id_i | {job_run_id} | Non-Unique |
order_t_uk | {"order_number, user_id"} | Unique |
order_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of order_t
- Referencing to
- Referenced by
Table: fact_activity_daily_order_t
Internal use
column name | data type | description | constraints |
---|---|---|---|
id | integer | Identifier for fact activity daily orders generated by analytics | Primary Key |
namespace_id | bigint | Identifier of the namespace e.g.,game project | Unique Constraint |
activity_date | date | Date of the daily order activity | Unique Constraint |
users_ordering_count | bigint | Number of users ordering on the day | |
order_count | bigint | Number of orders on the day | |
total_price | bigint | Total price of orders on the day | |
total_discounted_price | bigint | Total discounted price of orders on the day | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
namespace_date_uk | {"namespace_id, activity_date"} | Unique |
fact_activity_daily_order_t_pk | {id} | Unique |
Group: Transaction
Table: realmoney_currency_t
This table contains the 3-digit of ISO-4217 currency code.
For example USD.
Analytics usage: Currency labels.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Analytics Id for realmoney currency. | Primary Key |
currency_code | character varying | ISO Currency Code. | Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
realmoney_currency_t_uk | {currency_code} | Unique |
realmoney_currency_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of realmoney_currency_t
- Referenced by
Table: realmoney_transaction_item_t
This table contains the the item of a transaction (if it exists).
For example a item within a transaction on an eCommerce platform.
Analytics usage: analysis focusing on specific items transacted.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Analytics Id for realmoney transaction item. | Primary Key |
items | jsonb | A JSON list of a real-money transaction items. | |
realmoney_transaction_id | bigint | Relates to realmoney_transaction_t. | Foreign Key, Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
realmoney_transaction_item_t_uk | {realmoney_transaction_id} | Unique |
realmoney_transaction_item_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of realmoney_transaction_item_t
- Referencing to
Table: realmoney_transaction_t
This table contains the instances of real money transactions.
For example a transaction for a certain amount within a namespace on a given eCommerce platform.
Analytics usage: Analysis of eCommerce transactions.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Analytics Id for realmoney transaction. | Primary Key |
created_ts | timestamp without time zone | When the transaction was created. | |
amount | double precision | The amount of currency transacted. | |
namespace_id | bigint | The namespace for the Transaction. | Foreign Key, Unique Constraint |
event_number | character varying | A unique identifier of a real-money transaction event. | Unique Constraint |
currency_id | bigint | Relates to realmoney_currency_t. | Foreign Key |
platform_uid | character varying | A unique user identifier of eCommerce platform. | |
ecommerce_platform_id | bigint | Analytics ID of eCommerce platform related to ecommerce_platform_t table. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
transaction_type_id | bigint | Relates to realmoney_transaction_type_t. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
realmoney_transaction_t_uk | {"namespace_id, event_number"} | Unique |
realmoney_transaction_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of realmoney_transaction_t
- Referencing to
- Referenced by
Table: realmoney_transaction_type_t
This table will contain the type of transaction.
For example purchase, refund.
Analytics usage: labelling transactions by type.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Analytics Id for realmoney transaction type. | Primary Key |
name | character varying | Name of the transaction type. | Unique Constraint |
lname | character varying | Long name of the transaction type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
realmoney_transaction_type_t_uk | {name} | Unique |
realmoney_transaction_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of realmoney_transaction_type_t
- Referenced by
Table: payment_channel_t
This table contains the possible payment channels: INTERNAL, EXTERNAL
For example INTERNAL.
Analytics usage: displaying payment channel names.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for payment channel generated by analytics. | Primary Key |
name | character varying | Name of the payment channel. | |
lname | character varying | Long name of the payment channel. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
payment_channel_t_name_uk | {name} | Unique |
payment_channel_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of payment_channel_t
- Referenced by
Table: payment_property_t
This table contains properties of a payment user activity.
For example details of a payment for $1 USD via external payment channel, with its external order number, language used, with payment status charged.
Analytics usage: analysis of payment properties and their occurrences. For instance to find average payment amount per month.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for payment properties generated by analytics. | Primary Key |
payment_id | bigint | Relates to payment_t. | Foreign Key |
target_user_id | bigint | Relates to user_t for the target user, if any. | Foreign Key |
external_order_number | character varying | The real world identifier for order. | |
currency_id | bigint | Relates to currency_t. | Foreign Key |
language_id | bigint | Relates to language_t. | Foreign Key |
region_id | bigint | Relates to country_t. | Foreign Key |
is_sandbox | boolean | Indicates whether the payment was made in a sandbox. | |
price | integer | Amount of the payment. | |
payment_status_id | bigint | Relates to payment_status_type_t. | Foreign Key |
payment_status_reason_id | bigint | Relates to payment_status_reason_t. | Foreign Key |
start_ts | timestamp without time zone | Start of the lifetime of the payment properties. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
end_ts | timestamp without time zone | End of the lifetime of the payment properties. | |
external_user_number | character varying | Real world user identifier. | |
payment_provider_id | bigint | Relates to payment_provider_t. | Foreign Key |
channel_id | bigint | Relates to payment_channel_t. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
payment_property_t_target_user_id_i | {target_user_id} | Non-Unique |
payment_property_t_region_id_i | {region_id} | Non-Unique |
payment_property_t_payment_status_reason_id_i | {payment_status_reason_id} | Non-Unique |
payment_property_t_payment_status_id_i | {payment_status_id} | Non-Unique |
payment_property_t_payment_provider_id_i | {payment_provider_id} | Non-Unique |
payment_property_t_payment_id_start_ts_uk | {"payment_id, start_ts"} | Unique |
payment_property_t_language_id_i | {language_id} | Non-Unique |
payment_property_t_job_run_id_i | {job_run_id} | Non-Unique |
payment_property_t_currency_id_i | {currency_id} | Non-Unique |
payment_property_t_channel_id_i | {channel_id} | Non-Unique |
payment_property_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of payment_property_t
- Referencing to
Table: payment_provider_t
This table contains a list of possible payment providers for orders. One of WALLET, ADYEN, XSOLLA, STRIPE, CHECKOUT.
For example STRIPE meaning that online payment gateway.
Analytics usage: displaying payment provider name.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for payment provider generated by analytics. | Primary Key |
name | character varying | Name of the payment provider. | |
lname | character varying | Long name of the payment provider. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
payment_provider_t_name_uk | {name} | Unique |
payment_provider_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of payment_provider_t
- Referenced by
Table: payment_status_reason_t
This table contains a list of possible reasons for payment statuses.
For example ORDER_EXPIRED meaning, you guessed it, the order related to a payment expired.
Analytics usage: displaying payment status reason name.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for payment status reason generated by analytics. | Primary Key |
name | character varying | Name of the payment status reason. | |
lname | character varying | Long name of the payment status reason. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
payment_status_reason_t_name_uk | {name} | Unique |
payment_status_reason_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of payment_status_reason_t
- Referenced by
Table: payment_status_type_t
This table contains a list of payment statuses. One of INIT, CHARGED, REFUNDING, REFUNDED, CHARGE_FAILED.
For example CHARGED meaning the payment was successful.
Analytics usage: displaying payment status names.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for payment status type generated by analytics. | Primary Key |
name | character varying | Name of the payment status type. | |
lname | character varying | Long name of the payment status type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
payment_status_type_t_name_uk | {name} | Unique |
payment_status_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of payment_status_type_t
- Referenced by
Table: payment_t
This table contains record of the payment user activity. A user can make an order which contains items for which a payment is needed.
For example a record containing a user id and one of their payment ids.
Analytics usage: relating user to payment.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for payment generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key |
payment_number | character varying | Real world payment identifier. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
payment_t_user_id_payment_number_uk | {"user_id, payment_number"} | Unique |
payment_t_job_run_id_i | {job_run_id} | Non-Unique |
payment_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of payment_t
- Referencing to
- Referenced by
Group: Currency
Table: currency_detail_t
This table contains metadata attributes for currencies.
For example A game uses Rupiah which has the symbol Rp and 2 decimal places.
Analytics usage: formatting currency data correctly.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
currency_id | bigint | AccelByte Gaming Services currency id. | Foreign Key, Unique Constraint |
currency_symbol | character varying | Symbol used with the currency e.g.,$. | |
decimals | integer | Number of decimal places used for this currency. | |
is_deleted | boolean | A flag to show whether the currency was deleted from the System. | |
start_ts | timestamp without time zone | Start of the lifetime of the currency. | Unique Constraint |
end_ts | timestamp without time zone | End of the lifetime of the currency. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
currency_type_id | bigint | Indicates currency type e.g.,VIRTUAL. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
currency_detail_t_uk | {"currency_id, start_ts"} | Unique |
currency_detail_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of currency_detail_t
- Referencing to
Table: currency_localization_t
This table contains localization data for currencies eg. Description
For example In the language locale en-US for a given game the description of that currency is “Your virtual Coins”.
Analytics usage: creating user interfaces and reports tailored to the reader's language
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
currency_id | bigint | AccelByte Gaming Services currency id. | Foreign Key, Unique Constraint |
description | character varying | Localized currency description. | |
start_ts | timestamp without time zone | Start of the localized information lifetime. | Unique Constraint |
end_ts | timestamp without time zone | End of the localized information lifetime. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
locale_id | bigint | Relates to locale_t which identifies locale. | Foreign Key, Unique Constraint |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
currency_localization_t_uk | {"currency_id, start_ts, locale_id"} | Unique |
currency_localization_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of currency_localization_t
- Referencing to
Table: currency_t
This table contains currency and namespace combinations. A namespace may have more than one currency. The list of currency codes is unique within each namespace.
For example a given game has the currency USD.
Analytics usage: giving the possible currencies for a selected game.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
currency_code | character varying | Currency code, in most cases ISO Currency Code. | Unique Constraint |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Foreign Key, Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
currency_t_namespace_currency_code_uk | {"namespace_id, currency_code"} | Unique |
currency_t_uk | {"currency_code, namespace_id"} | Unique |
currency_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of currency_t
- Referencing to
- Referenced by
Table: currency_type_t
This table contains types of currency: virtual or real are the current choices.
For example REAL, meaning a real world currency like the US dollar.
Analytics usage: treating real and virtual currencies separately for accounting purposes.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
name | character varying | Name of the currency type. | Unique Constraint |
lname | character varying | Long name of the currency type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
currency_type_t_uk | {name} | Unique |
currency_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of currency_type_t
- Referenced by
Group: Fulfillment
Table: fulfillment_coderedeem_t
This table contains the link between redemption code and its fulfillments. The same redemption code may be used for many fulfillments.
For example redemption code uHrrYSaQFnNiqXFcUkFF was used by a user to successfully redeem a specific fulfillment.
Analytics usage: joining used redemption codes to fulfillments.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for fulfillment code redeem generated by analytics. | Primary Key |
fulfillment_id | bigint | AccelByte Gaming Services Fulfillment Id. | Foreign Key |
code_id | bigint | Relates to redemption_code_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
fulfillment_code_t_fulfillment_id_code_id_uk | {"fulfillment_id, code_id"} | Unique |
fulfillment_code_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of fulfillment_coderedeem_t
- Referencing to
Table: fulfillment_entitlement_t
This table contains entitlements for the fulfillments. Note there could be many entitlements in one fulfillment
For example a record of a specific fulfillment and its entitlement, e.g.,a lootbox item offered to a specific user. The user has received the item since it has been fulfilled.
Analytics usage: joining fulfillment to entitlements.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for fulfillment entitlement generated by analytics. | Primary Key |
fulfillment_id | bigint | Relates to fulfillment_t. | Foreign Key |
entitlement_id | bigint | Relates to entitlement_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
fulfillment_entitlement_t_job_run_id_i | {job_run_id} | Non-Unique |
fulfillment_entitlement_t_fulfillment_id_entitlement_id_uk | {"fulfillment_id, entitlement_id"} | Unique |
fulfillment_entitlement_t_entitlement_id_i | {entitlement_id} | Non-Unique |
fulfillment_entitlement_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of fulfillment_entitlement_t
- Referencing to
Table: fulfillment_error_t
This table contains possible types of error that might cause a fulfilment to fail
For example HTTP Status error 500: Internal Server Error
Analytics usage: reporting of errors in fulfillments.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for fulfillment error generated by analytics. | Primary Key |
http_status | integer | HTTP Status code eg. 500. | |
code | integer | Code for this error eg. 20000. | |
message | character varying | Message for this error eg. internal server error. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
fulfillment_error_message_t_http_status_code_message_uk | {"http_status, code, message"} | Unique |
fulfillment_error_message_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of fulfillment_error_t
- Referenced by
Table: fulfillment_item_t
This table contains the item variants that a fulfillment contains
For example a fulfillment containing 3 units of the item variant 50-cal-ammo-tracer.
Analytics usage: relating fulfillment to item and its variant, and reporting the quantities of those items.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for fulfillment item generated by analytics. | Primary Key |
fulfillment_id | bigint | Relates to fulfillment_t. | |
item_variant_id | bigint | Relates to item_variant_t. | Foreign Key |
quantity | integer | Quantity of item variants in this fulfillment. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
fulfillment_item_t_job_run_id_i | {job_run_id} | Non-Unique |
fulfillment_item_t_item_variant_id_i | {item_variant_id} | Non-Unique |
fulfillment_item_t_fulfillment_id_item_variant_id_uk | {"fulfillment_id, item_variant_id"} | Unique |
fulfillment_item_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of fulfillment_item_t
- Referencing to
Table: fulfillment_order_t
This table contains related fulfillment to order. A user can order items and when that order is fulfilled the fulfillment is successful.
For example a fulfillment containing an order for 3 units of the item variant 50-cal-ammo-tracer.
Analytics usage: relating fulfillment to order.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for fulfillment order generated by analytics. | Primary Key |
fulfillment_id | bigint | Relates to fulfillment_t. | Foreign Key |
order_id | bigint | Relates to order_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
fulfillment_order_t_order_id_i | {order_id} | Non-Unique |
fulfillment_order_t_job_run_id_i | {job_run_id} | Non-Unique |
fulfillment_order_t_fulfillment_id_order_id_uk | {"fulfillment_id, order_id"} | Unique |
fulfillment_order_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of fulfillment_order_t
- Referencing to
Table: fulfillment_status_type_t
This table contains possible statuses for a fulfillment: success, fail. Relates to its delivery or failure.
For example FAIL meaning for some reason the fulfillment was not executed. The current sole reason available is an internal server error.
Analytics usage: showing the possible fulfillment statuses, for instance in a legend.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for fulfillment status type generated by analytics. | Primary Key |
name | character varying | Name of the fulfilment status type. | |
lname | character varying | Long name of the fulfilment status type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
fulfillment_status_type_t_name_uk | {name} | Unique |
fulfillment_status_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of fulfillment_status_type_t
- Referenced by
Table: fulfillment_t
This table contains record of the fulfillment user activity. A fulfillment is one or more entitlements delivered to a user. It could be via an order or redemption. Note that there are two kinds of fulfillment: an order or a code redeem.
For example a successful fulfillment to a specific user executed at 2022-04-14 01:52:57.000.
Analytics usage: analysis of users' fulfillment activities, joining user and time to fulfillment.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for fulfillment generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key |
start_ts | timestamp without time zone | Start time of the fulfillment activity. | |
end_ts | timestamp without time zone | UTC timestamp for the end of the activity. | |
status_id | bigint | Relates to fulfillment_status_type_t. | Foreign Key |
fulfillment_error_id | bigint | Relates to fulfillment_error_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
fulfillment_number | character varying | We use order number as fulfillment number, but in clarity, to get the related order, use fulfillment_order_t instead. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
fulfillment_t_user_id_i | {user_id} | Non-Unique |
fulfillment_t_status_id_i | {status_id} | Non-Unique |
fulfillment_t_job_run_id_i | {job_run_id} | Non-Unique |
fulfillment_t_fulfillment_number_uk | {fulfillment_number} | Unique |
fulfillment_t_fulfillment_error_id_i | {fulfillment_error_id} | Non-Unique |
fulfillment2_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of fulfillment_t
- Referencing to
- Referenced by
Table: redemption_code_t
This table contains redemption codes which can be used to receive items via a fulfillment.
For example an issued redemption code with a unique id such as g4a2cJbszsKnUVqMPHJf.
Analytics usage: getting the analytics numeric id for an alphanumeric redemption code.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for redemption code generated by analytics. | Primary Key |
code | character varying | The real world redemption code which may be visible to the player. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
redemption_code_t_code_uk | {code} | Unique |
redemption_code_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of redemption_code_t
- Referencing to
- Referenced by
Group: Language and Geographic
Table: aws_region_t
This table contains a list of AWS regions, where AWS resources are located.
For example “us-west-2” Oregon.
Analytics usage: reporting AWS resource usage per region, for example.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
name | character varying | AWS region id e.g.,us-west-1. | |
lname | character varying | Long name describing the region e.g.,us west us west (n. California). |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
aws_region_t_name_uk | {name} | Unique |
aws_region_id_uk | {id} | Unique |
aws_region_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of aws_region_t
- Referenced by
Table: continent_t
This table contains all continents and their ISO codes
For example AF: Africa.
Analytics usage: creating reports split by continent.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Incrementing identifier. | Primary Key |
code | character | ISO Continent Code. | |
name | text | Name of the continent. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
continent_t_uk | {code} | Unique |
continent_t_pkey | {id} | Unique |
Table: country_t
This table contains all countries and their ISO Country Codes
For example US: United States of America, including its other ISO format identifiers and that it is in the continent NA: North America.
Analytics usage: creating reports split by country.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
name | character varying | Name of the country. | Unique Constraint |
lname | character varying | Long name of the country. | |
iso_3char | text | ISO Country Code. | |
iso_number | text | ISO Country Number. | |
iso_continent | text | ISO Continent Code. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
country_t_pk | {id} | Unique |
country_t_name_key_uk | {name} | Unique |
Relationship Diagram:
Show ER Diagram of country_t
- Referenced by
Table: locale_t
This table contains A list of languages used in connection with items and currencies.
For example de for German.
Analytics usage: displaying language locale for items.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for locale generated by analytics. | Primary Key |
name | character varying | Name of the locale. | |
lname | character varying | Long name of the locale. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
locale_t_name_uk | {name} | Unique |
locale_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of locale_t
- Referenced by
Table: language_t
This table contains languages used in AccelByte Gaming Services localization for users, for instance to customize payment related text.
For example en-US for American English.
Analytics usage: defining the languages shown in AccelByte Gaming Services.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for language generated by analytics. | Primary Key |
name | character varying | Name of the language. | Unique Constraint |
lname | character varying | Long name of the language. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
language_t_pk | {id} | Unique |
language_t_name_uk | {name} | Unique |
Relationship Diagram:
Show ER Diagram of language_t
- Referenced by
Group: Aggregator Utility
Table: bd_activity_type_t
This table contains a list of types of AccelByte Gaming Services user activity. Note there is a catch-all activity 'All Events' which represents all activities combined.
For example 'Auth' being user authentication.
Analytics usage: segregating user activity distinct counts by type of activity eg. calculating HAU for only the payments.
column name | data type | description | constraints |
---|---|---|---|
id | smallint | Identifier for activity type generated by analytics. | Primary Key |
activity_type | text | Descriptive name of the activity type, eg. Agreement. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
bd_activity_type_t_uk | {activity_type} | Unique |
bd_activity_type_t_pk | {id} | Unique |
Table: bd_daily_count_t
This table contains derived daily counts of distinct user activity: DAU, WAU and MAU, AHAU (average over the day) and PHAU (top HAU in the day). They are categorized by namespace eg. game, day and activity type.
For example Daily Active Users who made a purchase in a given game for a given day.
Analytics usage: charting active users per day, either overall with the “All Events” activity type or for a specific type of activity.
column name | data type | description | constraints |
---|---|---|---|
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Primary Key |
activity_type_id | smallint | The id of the activity as listed in bd_activity_type. | Primary Key |
activity_type | text | The type of the activity as listed in bd_activity_type. | |
day_ts | date | The date on which the activity occurred. | Primary Key |
dau | bigint | Daily active user count. | |
wau | bigint | Weekly active user count. | |
mau | bigint | Monthly active user count. | |
ahau | numeric | Average HAU. | |
phau | bigint | Highest HAU over the day. | |
phau_hours | ARRAY | The hours which scored the highest HAU over the day. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
bd_daily_count_t_day_ts_i | {"day_ts, namespace_id, activity_type_id"} | Non-Unique |
bd_daily_counts_t_pk | {"namespace_id, activity_type_id, day_ts"} | Unique |
Table: bd_etl_auth_time_t
This table contains Data not used for analytics visualisations but required for aggregation processes. This table may be ignored.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Internal use for checking data completeness, can be ignored. | Primary Key |
namespace_id | bigint | Internal use for checking data completeness, can be ignored. | |
auth_ts | timestamp without time zone | Internal use for checking data completeness, can be ignored. | |
latest_prior_ts | timestamp without time zone | Internal use for checking data completeness, can be ignored. | |
latest_day_ts | timestamp without time zone | Internal use for checking data completeness, can be ignored. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
bd_etl_auth_time_t_uk | {"id, namespace_id, auth_ts DESC"} | Unique |
bd_etl_auth_time_t_latest_prior_ts_i | {"((latest_prior_ts IS NULL"} | Non-Unique |
bd_etl_auth_time_t_pk | {id} | Unique |
Table: bd_etl_catchup_log_t
This table contains Data not used for analytics visualisations but required for aggregation processes. This table may be ignored.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Internal use for processing late incoming data, can be ignored. | Primary Key |
namespace_id | bigint | Internal use for processing late incoming data, can be ignored. | |
lookback_hours | bigint | Internal use for processing late incoming data, can be ignored. | |
window_end_ts | timestamp without time zone | Internal use for processing late incoming data, can be ignored. | |
latest_day_ts | timestamp without time zone | Internal use for processing late incoming data, can be ignored. | |
is_processed | boolean | Internal use for processing late incoming data, can be ignored. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
bd_etl_catchup_log_t_uk | {"namespace_id, lookback_hours, window_end_ts, latest_day_ts"} | Unique |
bd_etl_catchup_log_t_pk | {id} | Unique |
Table: bd_etl_process_log_t
This table contains Data not used for analytics visualisations but required for aggregation processes. This table may be ignored.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Internal use for recording ETLs, can be ignored. | Primary Key |
start_ts | timestamp without time zone | Internal use for recording ETLs, can be ignored. | |
end_ts | timestamp without time zone | Internal use for recording ETLs, can be ignored. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
bd_etl_process_log_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of bd_etl_process_log_t
- Referenced by
Table: bd_etl_source_detail_t
This table contains Data not used for analytics visualisations but required for aggregation processes. This table may be ignored.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Internal use for recording ETLs, can be ignored. | Primary Key |
bd_etl_source_log_id | bigint | Internal use for recording ETLs, can be ignored. | Foreign Key, Unique Constraint |
namespace_id | bigint | Internal use for recording ETLs, can be ignored. | Unique Constraint |
ts_hr | timestamp without time zone | Internal use for recording ETLs, can be ignored. | Unique Constraint |
source_rowcount | bigint | Internal use for recording ETLs, can be ignored. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
bd_etl_source_detail_t_pk | {id} | Unique |
bd_etl_source_detail_t_id_namespace_hr_uk | {"bd_etl_source_log_id, namespace_id, ts_hr"} | Unique |
Relationship Diagram:
Show ER Diagram of bd_etl_source_detail_t
- Referencing to
Table: bd_etl_source_log_t
This table contains Data not used for analytics visualisations but required for aggregation processes. This table may be ignored.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Internal use for recording ETLs, can be ignored. | Primary Key |
source_table | text | Internal use for recording ETLs, can be ignored. | Unique Constraint |
source_group | text | Internal use for recording ETLs, can be ignored. | |
start_id | bigint | Internal use for recording ETLs, can be ignored. | |
end_id | bigint | Internal use for recording ETLs, can be ignored. | |
start_ts | timestamp without time zone | Internal use for recording ETLs, can be ignored. | Unique Constraint |
bd_etl_process_log_id | bigint | Internal use for recording ETLs, can be ignored. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
bd_etl_source_log_t_table_hr_uk | {"source_table, start_ts"} | Unique |
bd_etl_source_log_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of bd_etl_source_log_t
- Referencing to
- Referenced by
Table: bd_fact_user_activity_t
This table contains all the distinct users, hour timestamps and activity types executed by the user. Note that namespace eg. game can be derived from the user_id by joining to the user_t table.
For example a record of a user joining a game with the 'New User' activity type during the hour 2022-11-11 11:00.
Analytics usage: queries about user activity. For instance this is used to create the content of the bd_hourly_count_t table which gives DAU and similar distinct user counts.
column name | data type | description | constraints |
---|---|---|---|
ts_hr | timestamp without time zone | The timestamp of the start of the hour in which the activity occurred. | Primary Key |
activity_type_id | smallint | The id of the activity as listed in bd_activity_type. | Primary Key |
user_id | bigint | Relates to user_t. | Primary Key |
ts_date | date | The day in which the activity occurred. | |
country_id | bigint | The country in which the activity occurred, not always populated. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
bd_fact_user_activity_t_user_uk | {"user_id, ts_hr, activity_type_id"} | Unique |
bd_fact_user_activity_t_user_id_ts_date_i | {"user_id, ts_date"} | Non-Unique |
bd_fact_user_activity_t_date_i | {"ts_date, activity_type_id, user_id"} | Non-Unique |
bd_fact_user_activity_t_country_id_i | {country_id} | Non-Unique |
bd_fact_user_activity_t_pk | {"ts_hr, activity_type_id, user_id"} | Unique |
Table: bd_hourly_count_t
This table contains derived daily counts of distinct user activity: HAU. They are categorized by namespace eg. game, hour and activity type.
For example Hourly Active Users who made a purchase in a given game in a given hour.
Analytics usage: charting active users per hour, either overall with the 'All Events' activity type or for a specific type of activity. This can answer questions such as “which is the busiest hour of the day?”.
column name | data type | description | constraints |
---|---|---|---|
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Primary Key |
activity_type_id | smallint | The id of the activity as listed in bd_activity_type. | Primary Key |
activity_type | text | The type of the activity as listed in bd_activity_type. | |
hour_ts | timestamp without time zone | UTC timestamp. | Primary Key |
hau | bigint | Hourly Active Users. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
bd_hourly_count_t_hour_ts_i | {"hour_ts, namespace_id, activity_type_id"} | Non-Unique |
bd_hourly_counts_t_pk | {"namespace_id, activity_type_id, hour_ts"} | Unique |
Table: bd_session_daily_t
This table contains derived daily counts of distinct match sessions and lobby sessions. They are categorized by day and namespace eg. game. Note that a single user may have many sessions in an day.
For example 1023 match sessions and 3301 lobby sessions for a given game on 2023-07-05.
Analytics usage: charting session counts for a namespace per day.
column name | data type | description | constraints |
---|---|---|---|
id | integer | Identifier for metric daily generated by analytics. | Primary Key |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Unique Constraint |
day_ts | date | The day in which the session occurred. | Unique Constraint |
match_session_count | bigint | The count of distinct match sessions. | |
lobby_session_count | bigint | The count of distinct lobby sessions. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
bd_session_daily_t_pk | {id} | Unique |
bd_session_daily_t_namespace_id_ts_hr_uk | {"namespace_id, day_ts"} | Unique |
Table: bd_session_hourly_t
This table contains derived hourly counts of distinct match sessions and lobby sessions. They are categorized by hour and namespace eg. game. Note that a single user may have many sessions in an hour.
For example 244 match sessions and 453 lobby sessions for a given game during the hour 2022-11-11 11:00.
Analytics usage: charting session counts for a namespace per hour.
column name | data type | description | constraints |
---|---|---|---|
id | integer | Identifier for metric hourly generated by analytics. | Primary Key |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Unique Constraint |
ts_hr | timestamp without time zone | The timestamp of the start of the hour in which the session occurred. | Unique Constraint |
match_session_count | bigint | The count of distinct match sessions. | |
lobby_session_count | bigint | The count of distinct lobby sessions. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
bd_session_hourly_t_pk | {id} | Unique |
bd_session_hourly_t_namespace_id_ts_hr_uk | {"namespace_id, ts_hr"} | Unique |
Group: GDPR
Table: gdpr_account_anonymized_t
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for GDPR account anonymized generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key, Unique Constraint |
finished_ts | timestamp without time zone | When the anonymization finished. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
gdpr_account_anonymized_t_uk | {user_id} | Unique |
gdpr_account_anonymized_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of gdpr_account_anonymized_t
- Referencing to
Table: gdpr_account_deleted_t
This table contains a log of status changes for user accounts having GDPR activities
For example a status change to DELETED for a given user at 2023-02-24 00:49:00.000.
Analytics usage: analysis of GDPR user status changes.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for GDPR account deleted generated by analytics. | Primary Key |
user_id | bigint | Relates to user_t. | Foreign Key |
start_ts | timestamp without time zone | Start time of the GDPR account deleted activity. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
status | bigint | Relates to GDPR_account_deletion_status_type_t. | |
end_ts | timestamp without time zone | End of the deletion activity. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
gdpr_account_deleted_t_user_id_uk | {"user_id, start_ts, status"} | Unique |
gdpr_account_deleted_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of gdpr_account_deleted_t
- Referencing to
Table: gdpr_account_deletion_status_type_t
This table contains list of GDPR activity statuses: REQUESTED_TO_IAM, DELETED
For example DELETED, meaning that the user's data has been deleted from the system as requested to comply with GDPR.
Analytics usage: showing the GDPR status type, for instance in a legend.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for GDPR account deletion statusype generated by analytics. | Primary Key |
name | character varying | Name of the GDPR account deletion status type. | |
lname | character varying | Long name of the GDPR account deletion status type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
gdpr_account_deletion_status_type_t_name_uk | {name} | Unique |
gdpr_account_deletion_status_type_t_pk | {id} | Unique |
Table: gdpr_activity_t
This table contains a log of GDPR activities requested by a user for themselves or another user.
For example An administrative user requests the GDPR covered data on behalf of a player user at 2023-02-20 12:53:03.000.
Analytics usage: analysis of GDPR activities, for instance to count the requests for personal data per month.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for GDPR activity generated by analytics. | Primary Key |
requester_user_id | bigint | Requester in user_t. | Foreign Key |
target_user_id | bigint | Target user in user_t. | Foreign Key |
activity_type_id | bigint | The id of the activity as listed in bd_activity_type. | Foreign Key |
activity_ts | timestamp without time zone | UTC timestamp of the activity. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
gdpr_activity_t_target_user_id_activity_type_id_activity_ts_uk | {"target_user_id, activity_type_id, activity_ts"} | Unique |
gdpr_activity_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of gdpr_activity_t
- Referencing to
Table: gdpr_activity_type_t
This table contains list of GDPR related activity types. One of PERSONAL_DATA_REQUESTED, PERSONAL_DATA_DOWNLOADED, ACCOUNT_DELETION_REQUESTED, ACCOUNT_DELETION_REQUEST_CANCELLED, PERSONAL_DATA_REQUEST_CANCELLED.
For example PERSONAL_DATA_REQUESTED when a user requests that we supply all the GDPR covered data about themselves.
Analytics usage: showing the type of GDPR activity.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for GDPR activity type generated by analytics. | Primary Key |
name | character varying | Name of the GDPR activity type. | |
lname | character varying | Long name of the GDPR activity type. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
gdpr_activity_type_t_name_uk | {name} | Unique |
gdpr_activity_type_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of gdpr_activity_type_t
- Referenced by
Group: General Utility
Table: analytics_heartbeat_t
Internal use
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for analytics heartbeat generated by analytics | Primary Key |
namespace_id | bigint | Identifier of the namespace e.g.,game project | Foreign Key |
latest_ts | timestamp without time zone | ? | |
event_id | character varying | ? | |
job_run_id | bigint | Id of the analytics job run via which this row was imported | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
analytics_heartbeat_t_namespace_id_uk | {namespace_id} | Unique |
analytics_heartbeat_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of analytics_heartbeat_t
- Referencing to
Table: schema_version_t
This table contains a record of versions of this schema. The row with the highest id and version_number is th current in-use version.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | System identifier for schema version. | Primary Key |
version_number | numeric | Version number that increases chronologically. | |
implemented_date | date | Date of implementation of this new version of the Schema. | |
change_description | text | A brief description of changes to the schema compared with the previous version. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
schema_version_t_pkey | {id} | Unique |
Table: job_program_t
This table contains data for internal use. This table can be ignored.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for job program generated by analytics. | Primary Key |
name | character varying | Name of the job program. | Unique Constraint |
lname | character varying | Long name of the job program. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
job_program_t_pk | {id} | Unique |
job_program_t_name_uk | {name} | Unique |
Relationship Diagram:
Show ER Diagram of job_program_t
- Referenced by
Table: job_run_t
This table contains a log of ETL job runs processing incoming data for internal use. This table can be ignored.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for job run generated by analytics. | Primary Key |
program_id | bigint | Internal program id for this job. | Foreign Key |
start_ts | timestamp without time zone | Start of the job run. | |
finish_ts | timestamp without time zone | End of the job run. | |
hostname | character varying | Where the job ran. | |
username | character varying | User invoking the Job. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
job_run_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of job_run_t
- Referencing to
- Referenced by
Table: dim_date_t
This table contains date dimensions, covering dates up to 2050. This includes a lot of information about each date for instance day_of_month.
For example 2023-01-03 and its information such as suffix, epoch, whether it is the first day of the month and so on.
Analytics usage: creating reports using this as a utility for formatting and selecting dates.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
date_actual | date | Dimension date. | |
epoch | bigint | Seconds since 1970-01-01. | |
day_suffix | character varying | eg. 1st. | |
day_name | character varying | eg. Thursday. | |
day_of_week | integer | eg. 1 for Monday. | |
day_of_month | integer | eg. 23. | |
day_of_quarter | integer | eg. 75. | |
day_of_year | integer | eg. 214. | |
week_of_month | integer | eg. 2. | |
week_of_year | integer | eg. 34. | |
week_of_year_iso | character | eg. 2024-W37-5. | |
month_actual | integer | eg. 9. | |
month_name | character varying | eg. July. | |
month_name_abbreviated | character | eg. Sep. | |
quarter_actual | integer | eg. 3. | |
quarter_name | character varying | eg. Third. | |
year_actual | integer | eg. 2024. | |
first_day_of_week | date | eg. 2024-09-09. | |
last_day_of_week | date | eg. 2024-09-15. | |
first_day_of_month | date | eg. 2024-09-01. | |
last_day_of_month | date | eg. 2024-09-30. | |
first_day_of_quarter | date | eg. 2024-07-01. | |
last_day_of_quarter | date | eg. 2024-09-30. | |
first_day_of_year | date | eg. 2024-01-01. | |
last_day_of_year | date | eg. 2024-12-31. | |
mmyyyy | character | eg. 092024. | |
mmddyyyy | character | eg. 09142024 . | |
is_weekend_indr | boolean | True if the date is on a weekend. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
d_date_date_actual_i | {date_actual} | Non-Unique |
d_date_id_pk | {id} | Unique |
Table: internal_job_run_detail_t
This table contains data for internal use. This table can be ignored.
column name | data type | description | constraints |
---|---|---|---|
id | integer | Identifier for internal job run detail generated by analytics. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Primary Key |
namespace_id | bigint | Identifier of the namespace e.g.,game project. | Primary Key |
activity_type | text | The type of the activity as listed in bd_activity_type. | Primary Key |
min_ts_hr | timestamp without time zone | Earliest time of the period of data processed by this job. | |
max_ts_hr | timestamp without time zone | Latest time of the period of data processed by this job. | |
user_id_count | bigint | Count of users in the data processed by this job. | |
row_count | bigint | Count of rows in the data processed by this job. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
internal_job_run_detail_t_pk | {"job_run_id, namespace_id, activity_type"} | Unique |
Table: http_justice_service_t
This table contains data for internal use. This table can be ignored.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
name | character varying | AccelByte Gaming Services Service name. | |
lname | character varying | AccelByte Gaming Services Service long name. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
http_justice_service_t_name_uk | {name} | Unique |
http_justice_service_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of http_justice_service_t
- Referenced by
Table: http_request_t
This table contains data for internal use. This table can be ignored.
column name | data type | description | constraints |
---|---|---|---|
type | character varying | The type of request or connection. | |
time | timestamp without time zone | The time when the load balancer generated a response to the client, in ISO 8601 format. For WebSockets, this is the time when the connection is closed. | |
elb | character varying | The resource ID of the load balancer. If you are parsing access log entries, note that resources IDs can contain forward slashes. | |
client_ip | character varying | The IP address of the requesting client. If there is a proxy in front of the load balancer, this field contains the IP address of the proxy. | |
client_port | integer | The port of the requesting client. | |
target_ip | character varying | The IP address of the target that processed this request. | |
target_port | integer | The port of the target that processed this request. | |
request_processing_time | double precision | The total time elapsed (in seconds, with millisecond precision) from the time the load balancer received the request until the time it sent the request to a target. | |
target_processing_time | double precision | The total time elapsed (in seconds, with millisecond precision) from the time the load balancer sent the request to a target until the target started to send the response headers. | |
response_processing_time | double precision | The total time elapsed (in seconds, with millisecond precision) from the time the load balancer received the response header from the target until it started to send the response to the client. This includes both the queuing time at the load balancer and the connection acquisition time from the load balancer to the client. | |
elb_status_code | integer | The status code of the response from the load balancer. | |
target_status_code | character varying | The status code of the response from the target. This value is recorded only if a connection was established to the target and the target sent a response. Otherwise, it is set to -. | |
received_bytes | bigint | The size of the request, in bytes, received from the client (requester). For HTTP requests, this includes the headers. For WebSockets, this is the total number of bytes received from the client on the connection. | |
sent_bytes | bigint | The size of the response, in bytes, sent to the client (requester). For HTTP requests, this includes the headers. For WebSockets, this is the total number of bytes sent to the client on the connection. | |
request_verb | character varying | HTTP method . | |
request_url | character varying | host:port/uri. | |
request_proto | character varying | protocol. | |
user_agent | character varying | A User-Agent string that identifies the client that originated the request, enclosed in double quotes. The string consists of one or more product identifiers, product[/version]. If the string is longer than 8 KB, it is truncated. | |
ssl_cipher | character varying | [HTTPS listener] The SSL cipher. This value is set to - if the listener is not an HTTPS listener. | |
ssl_protocol | character varying | [HTTPS listener] The SSL protocol. This value is set to - if the listener is not an HTTPS listener. | |
target_group_arn | character varying | The Amazon Resource Name (ARN) of the target group. | |
trace_id | character varying | The contents of the X-Amzn-Trace-Id header, enclosed in double quotes. | Unique Constraint |
domain_name | character varying | [HTTPS listener] The SNI domain provided by the client during the TLS handshake, enclosed in double quotes. This value is set to - if the client does not support SNI or the domain does not match a certificate and the default certificate is presented to the client. | |
chosen_cert_arn | character varying | [HTTPS listener] The ARN of the certificate presented to the client, enclosed in double quotes. This value is set to session-reused if the session is reused. This value is set to - if the listener is not an HTTPS listener. | |
matched_rule_priority | character varying | The priority value of the rule that matched the request. If a rule matched, this is a value from 1 to 50,000. If no rule matched and the default action was taken, this value is set to 0. If an error occurs during rules evaluation, it is set to -1. For any other error, it is set to -. | |
request_creation_time | character varying | The time when the load balancer received the request from the client, in ISO 8601 format. | |
actions_executed | character varying | The actions taken when processing the request, enclosed in double quotes. This value is a comma-separated list that can include the values described in Actions taken. If no action was taken, such as for a malformed request, this value is set to -. | |
redirect_url | character varying | The URL of the redirect target for the location header of the HTTP response, enclosed in double quotes. If no redirect actions were taken, this value is set to -. | |
lambda_error_reason | character varying | The error reason code, enclosed in double quotes. If the request failed, this is one of the error codes described in Error reason codes. If the actions taken do not include an authenticate action or the target is not a Lambda function, this value is set to -. | |
target_port_list | character varying | A space-delimited list of IP addresses and ports for the targets that processed this request, enclosed in double quotes. Currently, this list can contain one item and it matches the target:port field. | |
target_status_code_list | character varying | A space-delimited list of status codes from the responses of the targets, enclosed in double quotes. Currently, this list can contain one item and it matches the target_status_code field. | |
classification | character varying | The classification for desync mitigation, enclosed in double quotes. If the request does not comply with RFC 7230, the possible values are Acceptable, Ambiguous, and Severe. . | |
classification_reason | character varying | The classification reason code, enclosed in double quotes. If the request does not comply with RFC 7230, this is one of the classification codes described in Classification reasons. If the request complies with RFC 7230, this value is set to -. | |
id | bigint | Identifier for http request generated by analytics. | Primary Key |
domain_url_id | bigint | Relates to etl_justice_url_t. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
http_justice_service_id | integer | Relates to http_justice_service_t. | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
http_request_t_uk | {trace_id} | Unique |
http_request_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of http_request_t
- Referencing to
Group: Platform
Table: device_platform_t
This table contains the list of possible device platforms. Used for instance in telemetry presence.
For example Windows.
Analytics usage: Displaying the platforms in a legend on a chart.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for device platform generated by analytics. | Primary Key |
name | character varying | Name of the device platform e.g.,Windows. | |
lname | character varying | Long name of the device platform. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
device_platform_t_name_uk | {name} | Unique |
device_platform_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of device_platform_t
- Referenced by
Table: platform_t
This table contains a list of hardware or software platforms on which a game can run.
For example steam representing that online games platform.
Analytics usage: displaying platform name.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for platform generated by analytics. | Primary Key |
name | character varying | Name of the platform. | Unique Constraint |
lname | character varying | Long name of the platform. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
thirdparty_t_pk | {id} | Unique |
thirdparty_t_name_uk | {name} | Unique |
Relationship Diagram:
Show ER Diagram of platform_t
- Referenced by
Group: ETL Utility
Table: etl_file_path_t
This table contains the file path.
Analytics usage: finds the file path of the processed event.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for file path generated by analytics service. | Primary Key |
file_topic_id | bigint | Relates to etl_file_topic_t. | Foreign Key |
path_created_ts | timestamp without time zone | The path time partition in the S3. | |
filename | text | The object filename in the S3. | |
job_run_id | bigint | Id of the analytics job run via which this row was loaded. | |
has_error | boolean | A flag on whether the file has at least one line of error during ETL or not | |
bucket_id | bigint | S3 bucket where the file resides in, relates to etl_s3_bucket_t | Foreign Key |
last_etl_ts | timestamp without time zone | The timestamp of when was the last time the file had been processed by the ETL |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
path_created_ts_i | {path_created_ts} | Non-Unique |
etl_file_path_t_filename_uk | {filename} | Unique |
etl_file_path_t_file_topic_id_i | {file_topic_id} | Non-Unique |
etl_file_path_t_bucket_id_i | {bucket_id} | Non-Unique |
etl_file_path_t_pkey | {id} | Unique |
Relationship Diagram:
Show ER Diagram of etl_file_path_t
- Referencing to
- Referenced by
Table: etl_file_topic_t
This table contains the topics path.
Analytics usage: finds the topic path of the processed event.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for topic path generated by analytics service. | Primary Key |
topic_path | text | The topic path in the S3. | |
job_run_id | bigint | Id of the analytics job run via which this row was loaded. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
etl_file_topic_t_topic_path_uk | {topic_path} | Unique |
etl_file_topic_t_pkey | {id} | Unique |
Relationship Diagram:
Show ER Diagram of etl_file_topic_t
- Referenced by
Table: etl_justice_s3_t
This table contains a log of data imports for AGS events. Sometimes used to derive namespace name.
For example a data import job ran with a bucket name bucket123-analytics-kafka in us-west-2 region for the namespace dragon-strategy-game.
Analytics usage: internal use for deriving namespace name from namespace id. Not necessary to refer to since namespace name is already in namespace_t.
column name | data type | description | constraints |
---|---|---|---|
id | integer | Identifier for the ETL job to load data from s3 generated by analytics. | |
bucket_name | character varying | AWS s3 bucket name of the source data. | |
aws_region_id | bigint | Relates to aws_region_t. | Foreign Key |
namespace | character varying | Namespace eg. for a game. | |
namespace_id | bigint | If this value is not null the row refers to a game namespace. | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Relationship Diagram:
Show ER Diagram of etl_justice_s3_t
- Referencing to
Table: etl_justice_url_t
Internal use
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier | Primary Key |
bucket_name | character varying | AWS s3 bucket name of the access load balancer log | Unique Constraint |
aws_region_id | bigint | AWS Region in which the ETL took place | Foreign Key, Unique Constraint |
domain_name | character varying | Domain name for the ETL | Unique Constraint |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
etl_alb_s3_t_uk | {"bucket_name, aws_region_id, domain_name"} | Unique |
etl_alb_s3_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of etl_justice_url_t
- Referencing to
- Referenced by
Table: etl_processed_event_t
This table contains the number of events processed by etl. They are categorized by namespace and event source.
Analytics usage: charting the number of events processed by etl.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for the processed event. | Primary Key |
file_path_id | bigint | Relates to etl_file_path_t. | Foreign Key |
namespace_id | bigint | Relates to namespace_t. | Foreign Key |
event_source | text | The event source from event, whether it is justice-event (AGS event) or game telemetry (custom events). | Check Constraint |
event_count | bigint | The number of processed events. | |
event_size | bigint | The size of processed events. | |
job_run_id | bigint | Id of the analytics job run via which this row was loaded. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
etl_processed_event_t_namespace_id_i | {namespace_id} | Non-Unique |
etl_processed_event_t_file_path_id_uk | {"file_path_id, namespace_id, event_source"} | Unique |
etl_processed_event_t_etl_file_path_id_i | {file_path_id} | Non-Unique |
etl_processed_event_t_pkey | {id} | Unique |
Relationship Diagram:
Show ER Diagram of etl_processed_event_t
- Referencing to
Table: etl_reject_t
This table contains a log of rejected rows of incoming data and their errors for internal use. This table can be ignored.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported. | Foreign Key |
line | character varying | The line which was rejected. | |
error_text | character varying | Error related to this rejected record. | |
error_retry | integer | Error retry number. | |
attempt_no | character varying | Number of retries for this attempt. | |
is_fatal | boolean | Was the reject a fatal error. | |
reason | character varying | Reason for rejecting this record. | |
etl_ts | timestamp without time zone | The timestamp of when the error happened |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
etl_reject_t_job_run_id_i | {job_run_id} | Non-Unique |
etl_reject_t_id_uk | {id} | Unique |
etl_reject_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of etl_reject_t
- Referencing to
Table: etl_s3_bucket_t
This table contains bucket names the ETL ran on
column name | data type | description | constraints |
---|---|---|---|
id | bigint | dw0 identifier for the given bucket name | Primary Key |
bucket_name | text | The bucket name the ETL ran on | |
job_run_id | bigint | Foreign Key | |
customer_name | text | Customer name, defaults to null, should be manually updated | |
environment_name | text | Environment name (e.g. dev/prod/int), defaults to null, should be manually updated |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
etl_s3_bucket_t_bucket_name_uk | {bucket_name} | Unique |
etl_s3_bucket_t_pkey | {id} | Unique |
Relationship Diagram:
Show ER Diagram of etl_s3_bucket_t
- Referencing to
- Referenced by
Group: Foundation
Table: foundations_shared_oauthclient_t
Internal use
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier | Primary Key |
oauth_client_number | character varying | identifier for oauthclient |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
foundations_shared_oauthclient_t_oauth_client_number_uk | {oauth_client_number} | Unique |
foundations_shared_oauthclient_t_pk | {id} | Unique |
Table: foundations_studio_t
Internal use
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier for foundations studio generated by analytics | Primary Key |
namespace_id | bigint | Identifier of the namespace e.g.,game project | Foreign Key |
job_run_id | bigint | Id of the analytics job run via which this row was imported | Foreign Key |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
foundations_studio_t_namespace_uk | {namespace_id} | Unique |
foundations_studio_t_pk | {id} | Unique |
Relationship Diagram:
Show ER Diagram of foundations_studio_t
- Referencing to
Group: AccelByte Development Tools
Table: alb_logs_adt_t
This table contains application load balancer logs from AccelByte Development Tools.
For example records of requests made to retrieve the AccelByte Development Tools pages of the developer website.
Analytics usage: ALB log analysis, for instance finding web app latency or identifying groups of error status codes and their frequency.
column name | data type | description | constraints |
---|---|---|---|
id | bigint | Identifier. | Primary Key |
namespace | text | AccelByte defined namespace tag. | |
request_date | timestamp without time zone | The time of the request to the ALB. | |
request_processing_time | numeric | The total time elapsed (in seconds, with millisecond precision) from the time the load balancer received the request until the time it sent the request to a target. | |
target_processing_time | numeric | The total time elapsed (in seconds, with millisecond precision) from the time the load balancer sent the request to a target until the target started to send the response headers. | |
response_processing_time | numeric | The total time elapsed (in seconds, with millisecond precision) from the time the load balancer received the response header from the target until it started to send the response to the client. | |
request_count | integer | Total number of completed requests that were received and routed to the registered instances. | |
gb_in | integer | Incoming gb to load balancer. | |
gb_out | integer | Outgoing gb from load balancer. | |
client_ip_count | integer | Count of distinct client ips. | |
max_latency | numeric | Maximum latency, the time elapsed after the request leaves the load balancer until a response is received. | |
job_run_id | bigint | Id of the analytics job run via which this row was imported. |
Indexes:
Index name | Indexed columns | Type |
---|---|---|
alb_logs_adt_t_pk | {id} | Unique |