AIS Data warehouse tables V2.0

Last updated on December 17, 2024

Overview

With over 200 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.

This page provides a complete list of AIS data warehouse tables.

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
namespace_status_t_pk	{id}	Unique
namespace_status_t_namespace_id_start_ts_uk	{"namespace_id, start_ts"}	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, i.e., ENABLED, DELETED, DISABLED, etc.

For example, ENABLED means the namespace is live within the AGS 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
namespace_status_type_t_pk	{id}	Unique
namespace_status_type_t_name_uk	{name}	Unique

Relationship diagram

Show ER Diagram of `namespace_status_type_t`

Referenced by

---

Table

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	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.
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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
environment	text	AGS environment value of the namespace
product	text	AGS product type of the namespace
namespace_type	text	Type of the namespace
client_name	text	AGS client name of the namespace

Indexes

Index name	Indexed columns	Type
namespace_t_pk	{id}	Unique
namespace_t_uk	{"name, environment, client_name"}	Unique

Relationship diagram

Show ER Diagram of `namespace_t`

Referenced by

---

Group: User

Table: test_user_t

This table identifies test users. For example, a given user is recorded as an internal loadtest user at a given date.

Analytics usage: finding and distinguishing 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.
start_ts	Timestamp without time zone	Start of the period for which this test user was given.
email	character varying	User email address.	Unique Constraint
namespace_id	bigint	Relates to namespace_t table.	Foreign Key, Unique Constraint
uid	character varying	AGS User ID
is_test_user	boolean	Flag to identify user is test user or not
end_ts	Timestamp without time zone	End of the period for which this test user was given.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
test_user_t_pk	{id}	Unique
test_user_t_uk	{"email, namespace_id"}	Unique
test_user_t_uid_email_idx	{"uid, email"}	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: showing a legend for test user 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
thirdparty_account_t_platform_user_id_uk	{"platform_id, thirdparty_uid"}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
namespace_id	bigint	Identifier of the namespace, e.g., game project.	Foreign Key

Indexes

Index name	Indexed columns	Type
user_auth_t_pk	{id}	Unique
user_auth_t_client_id_i	{"client_id, auth_ts"}	Non-Unique
user_auth_t_user_id_auth_ts_uk	{"user_id, auth_ts"}	Unique
user_auth_t_namespace_id_i	{namespace_id}	Non-Unique
user_auth_t_auth_ts_namespace_id_i	{"auth_ts, namespace_id"}	Non-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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_ban_t_pk	{id}	Unique
user_ban_t_user_id_ban_id_uk	{"user_id, ban_id, start_ts"}	Unique

Relationship diagram

Show ER Diagram of user_ban_t

Referencing to

---

Table: user_country_t

This table shows a user's 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_country_t_pk	{id}	Unique
user_country_t_country_id_i	{country_id}	Non-Unique
user_country_t_user_id_country_id_start_ts_uk	{"user_id, country_id, start_ts"}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_date_of_birth_t_pk	{id}	Unique
user_date_of_birth_t_user_id_date_of_birth_start_ts_uk	{"user_id, start_ts"}	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.
is_unique	boolean	Indicates if the display name is unique within its environment in premium or studio level in starter
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_display_name_t_pk	{id}	Unique
user_display_name_t_user_id_display_name_start_ts_uk	{"user_id, start_ts"}	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
email	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_email_t_pk	{id}	Unique
user_email_t_uk	{"user_id, start_ts"}	Unique
user_email_t_email_ix	{email}	Non-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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_language_t_pk	{id}	Unique
user_language_t_language_id_i	{language_id}	Non-Unique
user_language_t_user_id_language_id_start_ts_uk	{"user_id, start_ts"}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_marketing_preference_t_pk	{id}	Unique
user_marketing_preference_t_user_id_uk	{"user_id, start_ts"}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_status_t_pk	{id}	Unique
user_status_t_uk	{"user_id, start_ts"}	Unique

Relationship diagram

Show ER Diagram of user_status_t

Referencing to

---

Table: user_t

This table contains namespace level accounts. The uid must be unique on a per-namespace basis. This contains namespace, e.g., game, unique user identifier and optionally parent user.

For example, a record for a particular user in a specific game.

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 (AGS). 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 AGS 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_t_namespace_id_i	{namespace_id}	Non-Unique
user_t_parent_user_id_i	{parent_user_id}	Non-Unique
user_t_uid_namespace_id_id_uid_i	{"uid, namespace_id, id"}	Unique
user_t_pk	{id}	Unique
user_t_uid_namespace_id_uk	{"uid, namespace_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 AGS 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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_pk	{id}	Unique

Relationship diagram

Show ER Diagram of user_thirdparty_link_t

Referencing to

---

Table: user_thirdparty_t

This table shows the linking of a user to a third-party account 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_thirdparty_t_pk	{id}	Unique
user_thirdparty_t_thirdparty_user_id_i	{thirdparty_user_id}	Non-Unique
user_thirdparty_t_user_id_thirdparty_user_id_start_ts_uk	{"user_id, thirdparty_user_id"}	Unique

Relationship diagram

Show ER Diagram of `user_thirdparty_t`

Referencing to

Referenced by

---

Table: user_thirdparty_display_name_t

This table contains the possibly changing display name of third-party users.

For example, a specific user sets their third party display name to Melvin at a given date.

Analytics usage: to show third party display names.

Column name	Data type	Description	Constraints
id	bigint	Identifier for user third party display name generated by analytics.	Primary Key
user_thirdparty_id	bigint	Relates to user_thirdparty_t.	Foreign Key, Unique Constraint
display_name	text	Third party display name of the user.	Unique Constraint
start_ts	Timestamp without time zone	Start of the period for which this display name was given.	Unique Constraint
end_ts	Timestamp without time zone	End of the period for which this display name was given.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_thirdparty_display_name_t_pkey	{id}	Unique
user_thirdparty_display_name_t_user_thirdparty_id_uk	{"user_thirdparty_id, display_name, start_ts"}	Unique
user_thirdparty_display_name_t_user_thirdparty_id_i	{user_thirdparty_id}	Non-Unique

Relationship diagram

Show ER Diagram of user_thirdparty_display_name_t

Referencing to

---

Table: user_username_t

This table contains the possibly changing username of a user.

For example, a specific user sets their username to geoffrey456 at a given date.

Analytics usage: to show usernames.

Column name	Data type	Description	Constraints
id	bigint	Identifier for user username generated by analytics.	Primary Key
user_id	bigint	Relates to user_t.	Foreign Key, Unique Constraint
username	text	Username of the user.	Unique Constraint
start_ts	Timestamp without time zone	Start of the period for which this username was given.	Unique Constraint
end_ts	Timestamp without time zone	End of the period for which this username was given.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_username_t_pkey	{id}	Unique
user_username_t_user_id_uk	{"user_id, username, start_ts"}	Unique
user_username_t_user_id_i	{user_id}	Non-Unique

Relationship diagram

Show ER Diagram of user_username_t

Referencing to

---

Group: User Cohort

Table: user_cohort_status_t

This table contains the status constant for user cohort. The status values are not expected to change frequently, if at all.

For example, one of EMPTY, POPULATING, AGGREGATING, READY, ARCHIVED.

Analytics usage: display current status of a user cohort.

Column name	Data type	Description	Constraints
id	bigint	Identifier generated for user cohort status	Primary Key
status	character varying	The status value of the user cohort	Unique Constraint

Indexes

Index name	Indexed columns	Type
user_cohort_status_t_pkey	{id}	Unique
user_cohort_status_t_status_key	{status}	Unique
user_cohort_status_t_pkey	{id}	Unique
user_cohort_status_t_status_key	{status}	Unique

Relationship diagram

Show ER Diagram of user_cohort_status_t

Referenced by

---

Table: user_cohort_t

This table contains the user cohort details. The information stored includes display name, description, status, and user count.

For example, a user cohort of 1000 players who are under 18s has READY status

Analytics usage: displaying data at cohort level.

Column name	Data type	Description	Constraints
id	bigint	Identifier generated for user cohort	Primary Key
namespace_id	bigint	The namespace identifier of the user cohort	Foreign Key
cohort_status_id	bigint	The user cohort status identifier of the user cohort	Foreign Key
name	character varying	The user cohort display name
description	character varying	The user cohort description
user_count	bigint	The user cohort user list count
ctime	Timestamp without time zone	The user cohort creation time
mtime	Timestamp without time zone	The user cohort last modified time

Indexes

Index name	Indexed columns	Type
user_cohort_t_pkey	{id}	Unique
user_cohort_t_uk	{"namespace_id, name, ctime"}	Unique
user_cohort_t_pk	{id}	Unique
user_cohort_uk	{"namespace_id, name, ctime"}	Unique
user_cohort_slot_ix	{"namespace_id, slot"}	Unique

Relationship diagram

Show ER Diagram of user_cohort_t

Referencing to

Referenced by

---

Table: user_cohort_user_t

This table contains the normalized user list for user cohorts.

For example, a given user is defined as being part of the under 18s cohort.

Analytics usage: linking cohort to its users.

Column name	Data type	Description	Constraints
id	bigint	Identifier generated for user cohort user	Primary Key
user_cohort_id	bigint	The user cohort identifier	Foreign Key
user_id	bigint	The user identifier	Foreign Key

Indexes

Index name	Indexed columns	Type
user_cohort_user_t_pkey	{id}	Unique
user_cohort_user_t_uk	{"user_cohort_id, user_id"}	Unique
user_cohort_user_uk	{"user_cohort_id, user_id"}	Unique
user_cohort_user_t_pk	{id}	Unique

Relationship diagram

Show ER Diagram of user_cohort_user_t

Referencing to

---

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: analyzing 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 eg 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
oauth_client_property_t_pk	{id}	Unique
oauth_client_property_t_client_id_start_ts_uk	{"client_id, start_ts"}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
oauth_client_status_t_pk	{id}	Unique
oauth_client_status_t_namespace_id_start_ts_uk	{"client_id, start_ts"}	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, e.g., ENABLED and DELETED.

For example, ENABLED means 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
oauth_client_status_type_t_pk	{id}	Unique
oauth_client_status_type_t_name_uk	{name}	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 AGS systems.

note

In this case, a client 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: analyzing 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
client_t_pk	{id}	Unique
client_t_namespace_id_client_number_uk	{"namespace_id, client_number"}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
oauth_client_type_t_pk	{id}	Unique
oauth_client_type_t_name_uk	{name}	Unique

---

Group: User Banning

Table: ban_feature_t

This table contains a list of AGS 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 eg 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_ban_feature_t_name_uk	{name}	Unique
user_ban_feature_t_pk	{id}	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 AGS 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 eg 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
user_ban_reason_t_name_uk	{name}	Unique
user_ban_reason_t_pk	{id}	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	AGS 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
ban_t_pk	{id}	Unique
ban_t_ban_id_uk	{"ban_number, start_ts"}	Unique

Relationship diagram

Show ER Diagram of ban_t

Referencing to

Referenced by

---

Group: Telemetry

Table: hll_telemetry_event_t

This table contains distinct users of telemetry events.

The distinct count interval is on a per-hour basis.

Analytics usage: distinct count telemetry users.

Column name	Data type	Description	Constraints
id	bigint	Identifier generated for this table.	Primary Key
hour_ts	Timestamp without time zone	Start of the hour in which the event is sent.	Unique Constraint
namespace_id	bigint	Relates to namespace_t where the namespace of the event is sent.	Foreign Key, Unique Constraint
telemetry_event_id	bigint	Relates to telemetry_event_t to identify the telemetry event name.	Foreign Key, Unique Constraint
users	HLL	This is used by the postgresql-hll extension to count unique users.
ctime	Timestamp without time zone	When this record was created in this database.
mtime	Timestamp without time zone	When this record was last updated in this database.

Indexes

Index name	Indexed columns	Type
hll_telemetry_event_t_pk	{id}	Unique
hll_telemetry_event_t_uk	{"hour_ts, namespace_id, telemetry_event_id"}	Unique
hll_telemetry_event_t_namespace_id_hour_ts_i	{"namespace_id, hour_ts"}	Non-Unique
hll_telemetry_event_t_telemetry_event_id_i	{telemetry_event_id}	Non-Unique

Relationship diagram

Show ER Diagram of hll_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: Analyzing 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
telemetry_presence_game_state_t_pk	{id}	Unique
telemetry_presence_game_state_t_name_uk	{name}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
telemetry_presence_t_pk	{id}	Unique
telemetry_presence_t_flight_id_presence_ts_game_state_id_uk	{"flight_id, presence_ts, game_state_id"}	Unique
telemetry_presence_t_user_id_i	{user_id}	Non-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.	Foreign Key
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	jsonb	A JSON object containing data about the event.
flight_id	bigint	Relates to flight_t.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
device_type	text	Device type of the user

Indexes

Index name	Indexed columns	Type
telemetry_t_event_ts_namespace_id_i	{"event_ts, namespace_id"}	Non-Unique
telemetry_t_telemetry_event_id_event_ts_i	{"telemetry_event_id, event_ts DESC"}	Non-Unique
telemetry_t_pk	{id}	Unique
telemetry_t_namespace_id_event_number_uk	{"namespace_id, event_number"}	Unique
telemetry_t_flight_id_i	{flight_id}	Non-Unique
telemetry_t_telemetry_event_id_i	{telemetry_event_id}	Non-Unique
telemetry_t_payload_json_i	{payload_json}	Non-Unique
telemetry_t_user_id_i	{user_id}	Non-Unique

Relationship diagram

Show ER Diagram of telemetry_t

Referencing to

---

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
telemetry_event_t_pk	{id}	Unique
telemetry_event_t_namespace_id_name_uk	{"namespace_id, name"}	Unique

Relationship diagram

Show ER Diagram of telemetry_event_t

Referencing to

Referenced by

---

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 AGS for flight.
job_run_id	bigint	ID of the analytics job run via which this row was imported.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
flight_t_pk	{id}	Unique
flight_t_namespace_id_flight_number_uk	{"namespace_id, flight_number"}	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 AGS 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
lobby_disconnected_code_t_pk	{id}	Unique
lobby_disconnected_code_t_name_uk	{name}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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: analyzing 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
lobby_platform_t_pk	{id}	Unique
lobby_platform_t_lobby_session_id_platform_id_uk	{"lobby_session_id, platform_id"}	Unique
lobby_platform_t_platform_id_i	{platform_id}	Non-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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
connection_id	text	This is used to indicate a unique connection to lobby.\nUsed to relate the connect with relevant disconnect.
reconnect_from	bigint	This contains the information if the connection made is a reconnect or not withing the userConnected Events.

Indexes

Index name	Indexed columns	Type
lobby_session_connected_t_pk	{id}	Unique
lobby_session_connected_t_lobby_session_id_connected_ts_uk	{"lobby_session_id, connected_ts"}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
connection_id	text	This is used to indicate a unique connection to lobby.\nUsed to relate the connect with relevant disconnect.

Indexes

Index name	Indexed columns	Type
lobby_session_disconnected_t_pk	{id}	Unique
lobby_session_disconnected_t_disconnected_code_id_i	{disconnected_code_id}	Non-Unique
lobby_session_disconnected_t_lobby_session_id_disconnected__uk	{"lobby_session_id, disconnected_ts"}	Unique
lobby_session_disconnected_t_reason_id_i	{reason_id}	Non-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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
namespace_id	bigint	Identifier of the namespace, e.g., game project.	Foreign Key

Indexes

Index name	Indexed columns	Type
game_lobby_session_t_uk	{"user_id, srv_session_id"}	Unique
lobby_session_t_pk	{id}	Unique
lobby_session_t_user_id_i	{user_id}	Non-Unique
lobby_session_t_namespace_id_i	{namespace_id}	Non-Unique

Relationship diagram

Show ER Diagram of lobby_session_t

Referencing to

Referenced by

---

Group: Matchmaking

::: note 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
match_party_user_t_pk	{id}	Unique
match_party_user_t_matchparty_id_user_id_uk	{"matchparty_id, user_id"}	Unique
match_party_user_t_user_id_i	{user_id}	Non-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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
match_structure_team_t_pk	{id}	Unique
match_structure_team_t_matchstructure_id_matchteam_id_uk	{"matchstructure_id, matchteam_id"}	Unique
match_structure_team_t_matchteam_id_i	{matchteam_id}	Non-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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
match_team_party_t_pk	{id}	Unique
match_team_party_t_matchparty_id_i	{matchparty_id}	Non-Unique
match_team_party_t_matchteam_id_matchparty_id_uk	{"matchteam_id, matchparty_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: analyzing 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, eg 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchmaking_mainticket_matchsession_t_pk	{id}	Unique
matchmaking_mainticket_matchs_mainticket_id_matchsession_id_uk	{"mainticket_id, matchsession_id"}	Unique
matchmaking_mainticket_matchsession_t_matchsession_id_i	{matchsession_id}	Non-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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchmaking_mainticket_t_pk	{id}	Unique
matchmaking_mainticket_t_channel_id_i	{channel_id}	Non-Unique
matchmaking_mainticket_t_party_id_first_created_at_uk	{"party_id, channel_id, first_created_at"}	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: analyzing 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchmaking_ticket_cancelled_t_pk	{id}	Unique
matchmaking_ticket_cancelled_t_ticket_id_uk	{ticket_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: analyzing 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchmaking_ticket_matched_pk	{id}	Unique
matchmaking_ticket_matched_ticket_id_uk	{ticket_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: analyzing 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 this event occurred.
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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchmaking_ticket_t_pk	{id}	Unique
matchmaking_ticket_t_channel_id_i	{channel_id}	Non-Unique
matchmaking_ticket_t_mainticket_id_i	{mainticket_id}	Non-Unique
matchmaking_ticket_t_party_id_channel_id_queued_at_uk	{"party_id, channel_id, created_at"}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchmaking_ticket_timedout_t_pk	{id}	Unique
matchmaking_ticket_timedout_t_ticket_id_uk	{ticket_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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchparty_t_pk	{id}	Unique
matchparty_t_origin_party_id_user_list_uk	{"origin_party_id, user_list"}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchsession_activity_t_matchsession_event_id_i	{matchsession_event_id}	Non-Unique
matchsession_activity_t_matchsession_status_id_i	{matchsession_status_id}	Non-Unique
matchsession_activity_t_pk	{id}	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_matchstructure_id_i	{matchstructure_id}	Non-Unique
matchsession_activity_t_start_ts_i	{start_ts}	Non-Unique

Relationship diagram

Show ER Diagram of matchsession_activity_t

Referencing to

Referenced by

---

Table: matchsession_activity_user_t

This table contains denormalised matchmaking activity data for ease and speed of querying. It shows user activities and their times and namespaces.

For example, a user in a given game at a certain time joined a match party.

Analytics usage: counting distinct users performing matchmaking activities.

Column name	Data type	Description	Constraints
id	bigint	Identifier generated by analytics.	Primary Key
matchsession_activity_id	bigint	Relates to matchsession_activity_t. The max ID by the key in the hour.	Foreign Key
namespace_id	bigint	Relates to namespace_t.	Foreign Key
hour_ts	Timestamp without time zone	The hour in which the activity occurred. Note that more than one activity with the same user, namespace and hour will result in a single record.
user_id	bigint	Relates to user_t.	Foreign Key

Indexes

Index name	Indexed columns	Type
matchsession_activity_user_t_pkey	{id}	Unique
matchsession_activity_t_uk	{"namespace_id, hour_ts, user_id"}	Unique
matchsession_activity_t_user_id_i	{user_id}	Non-Unique
matchsession_activity_t_hour_ts_i	{"hour_ts, user_id"}	Non-Unique

Relationship diagram

Show ER Diagram of matchsession_activity_user_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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchsession_dedicatedserver_t_pk	{id}	Unique
matchsession_dedicatedserver_t_uk	{"matchsession_id, dedicatedserver_id"}	Unique
matchsession_dedicatedserver_t_dedicatedserver_id_i	{dedicatedserver_id}	Non-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: analyzing 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchsession_ended_t_pk	{id}	Unique
matchsession_ended_t_matchsesion_id_uk	{matchsession_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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchsession_event_type_t_pk	{id}	Unique
matchsession_event_type_t_name_uk	{name}	Unique

Relationship diagram

Show ER Diagram of matchsession_event_type_t

Referenced by

---

Table: matchsession_member_status_t

This table contains the historical statusV2 data for a member in a match session.

The statusV2 value is a changing dimension indicated by start and end time.

Analytics usage: stores the historical match session member statusV2.

Column name	Data type	Description	Constraints
id	bigint	Identifier generated for matchsession member statusV2 data	Primary Key
matchsession_member_id	bigint	The match session member identifier value	Foreign Key, Unique Constraint
matchsession_member_status_type_id	bigint	The match session member statusV2 identifier value	Foreign Key, Unique Constraint
start_ts	Timestamp without time zone	The start of when the status was in effect for this member in the match session.	Unique Constraint
end_ts	Timestamp without time zone	The end of when the status was in effect for this member in the match session.
job_run_id	bigint	ID of the analytics job run via which this row was imported.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchsession_member_status_t_pkey	{id}	Unique
matchsession_member_status_t_uk	{"matchsession_member_id, matchsession_member_status_type_id, start_ts"}	Unique
matchsession_member_status_t_matchsession_member_id_i	{matchsession_member_id}	Non-Unique

Relationship diagram

Show ER Diagram of matchsession_member_status_t

Referencing to

---

Table: matchsession_member_status_type_t

This table contains the statusV2 values for a member in a match session.

The statusV2 values are not expected to change often, if any.

Analytics usage: stores the normalized match session member statusV2 values.

Column name	Data type	Description	Constraints
id	bigint	Identifier generated for the statusV2 value	Primary Key
status	character varying	The statusV2 value of the member in the match session	Unique Constraint
job_run_id	bigint	ID of the analytics job run via which this row was imported.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchsession_member_status_type_t_status_key	{status}	Unique
matchsession_member_status_type_t_pkey	{id}	Unique

Relationship diagram

Show ER Diagram of matchsession_member_status_type_t

Referenced by

---

Table: matchsession_member_t

This table contains the list of user (member) in a match session.

The values are not expected to change often, if any.

Analytics usage: stores the historical match session user list.

Column name	Data type	Description	Constraints
id	bigint	Identifier generated for the member value	Primary Key
matchsession_id	bigint	The match session identifier value	Unique Constraint
user_id	bigint	The user identifier value	Unique Constraint
job_run_id	bigint	ID of the analytics job run via which this row was imported.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchsession_member_t_pkey	{id}	Unique
matchsession_member_t_uk	{"matchsession_id, user_id"}	Unique
matchsession_member_t_matchsession_id_i	{matchsession_id}	Non-Unique

Relationship diagram

Show ER Diagram of matchsession_member_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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchsession_status_type_t_pk	{id}	Unique
matchsession_status_type_t_name_uk	{name}	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 AGSIDand 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 AGS 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	AGS 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchsession_t_pk	{id}	Unique
matchsession_t_uk	{"namespace_id, match_id"}	Unique
matchsession_t_job_run_id_i	{job_run_id}	Non-Unique
matchsession_t_namespace_id_id_i	{"namespace_id, 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 two teams. The list of the two 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchstructure_t_team_list_uk	{team_list}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
matchteam_t_pk	{id}	Unique
matchteam_t_party_list_uk	{party_list}	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	UniqueIDin AGS 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
party_t_pk	{id}	Unique
party_t_namespace_id_i	{namespace_id}	Non-Unique
party_t_party_number_uk	{"party_number, namespace_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 localizationIDfor 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
namespace_id	bigint	Identifier of the namespace, e.g., game project.	Foreign Key

Indexes

Index name	Indexed columns	Type
agreement_t_pk	{id}	Unique
agreement_t_policy_id_i	{policy_id}	Non-Unique
agreement_t_policy_localization_id_i	{policy_localization_id}	Non-Unique
agreement_t_policy_version_id_i	{policy_version_id}	Non-Unique
agreement_t_uk	{"user_id, policy_id, policy_version_id, policy_localization_id, start_ts"}	Unique
agreement_t_namespace_id_i	{namespace_id}	Non-Unique

Relationship diagram

Show ER Diagram of agreement_t

Referencing to

---

Table: policy_localization_t

This table contains holds an AGS identifier for a localized policy version

For example, a policy localization identifier with its owning policy.

Analytics usage: supplying Identifier of a policy to look up in AGS.

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
policy_t_pk	{id}	Unique
policy_t_uk	{"policy_number, namespace_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 AGS identifier for the version

For example, a version identifier and its owning policy.

Analytics usage: supplying Identifier of a policy to look up in AGS.

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
policy_version_t_pk	{id}	Unique
policy_version_t_uk	{"policy_version_number, policy_id"}	Unique

Relationship diagram

Show ER Diagram of policy_version_t

Referencing to

Referenced by

---

Table: studio_t

This table is to be deprecated. Please ignore it.

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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
studio_t_pk	{id}	Unique
studio_t_name_uk	{name}	Unique

---

Group: Dedicated 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	AGS 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
dedicatedserver_allocation_t_pk	{id}	Unique
dedicatedserver_allocation_t_status_t_uk	{"dedicatedserver_id, start_ts"}	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	AGS 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 eg 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 eg 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
dedicatedserver_detail_t_pk	{id}	Unique
dedicatedserver_detail_t_uk	{dedicatedserver_id}	Unique
dedicatedserver_detail_t_dsprovider_id_i	{dsprovider_id}	Non-Unique
dedicatedserver_detail_t_dsregion_id_i	{dsregion_id}	Non-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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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 (eg 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
dedicatedserver_region_t_pk	{id}	Unique
dedicatedserver_region_t_uk	{name}	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	AGS 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
dedicatedserver_status_type_id	bigint	Identifier of statusV2 value of the dedicated server	Foreign Key

Indexes

Index name	Indexed columns	Type
dedicatedserver_status_t_pk	{id}	Unique
dedicatedserver_status_t_uk	{"dedicatedserver_id, start_ts"}	Unique

Relationship diagram

Show ER Diagram of dedicatedserver_status_t

Referencing to

---

Table: dedicatedserver_status_type_t

This table contains the statusV2 values for a dedicated server.

The statusV2 values are not expected to change often, if any.

Analytics usage: stores the normalized dedicated server statusV2 values.

Column name	Data type	Description	Constraints
id	bigint	Identifier generated for match party user status	Primary Key
status	character varying	The statusV2 value of the user in the match session	Unique Constraint
job_run_id	bigint	ID of the analytics job run via which this row was imported.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
dedicatedserver_status_type_t_pkey	{id}	Unique
dedicatedserver_status_type_t_status_key	{status}	Unique

Relationship diagram

Show ER Diagram of dedicatedserver_status_type_t

Referenced by

---

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
dedicatedserver_t_pk	{id}	Unique
dedicatedserver_t_uk	{pod_name}	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	AGS 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
entitlement_usage_t_pk	{id}	Unique
entitlement_activity_t_source_id_i	{source_id}	Non-Unique
entitlement_usage_t_entitlement_id_change_count_source_star_uk	{"entitlement_id, change_count, source_id, start_ts"}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
entitlement_class_type_t_pk	{id}	Unique
entitlement_class_type_t_name_uk	{name}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
entitlement_item_t_pk	{id}	Unique
entitlement_item_t_entitlement_id_uk	{entitlement_id}	Unique
entitlement_item_t_item_id_i	{item_id}	Non-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 this event occurred.
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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
entitlement_property_t_pk	{id}	Unique
entitlement_property_t_class_id_i	{class_id}	Non-Unique
entitlement_property_t_entitlement_id_uk	{entitlement_id}	Unique
entitlement_property_t_entitlement_type_id_i	{entitlement_type_id}	Non-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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
entitlement_source_type_t_pk	{id}	Unique
entitlement_source_type_t_name_uk	{name}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
entitlement_status_t_uk	{"entitlement_id, status_id, start_ts"}	Unique
entitlement_status_t_pk	{id}	Unique
entitlement_status_t_status_id_i	{status_id}	Non-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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
entitlement_status_type_t_pk	{id}	Unique
entitlement_status_type_t_name_uk	{name}	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	AGS 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
namespace_id	bigint	Identifier of the namespace, e.g., game project.	Foreign Key

Indexes

Index name	Indexed columns	Type
entitlement_t_pk	{id}	Unique
entitlement_t_entitlement_number_entitlement_namespace_uk	{"entitlement_number, user_id"}	Unique
entitlement_t_user_id_i	{user_id}	Non-Unique
entitlement_t_namespace_id_i	{namespace_id}	Non-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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
entitlement_type_t_pk	{id}	Unique
entitlement_type_t_name_uk	{name}	Unique

Relationship diagram

Show ER Diagram of entitlement_type_t

Referenced by

---

Group: Item

Table: item_bundle_t

This table contains an item can be an bundle of items in which case its bundleIDand 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
item_bundle_t_pk	{id}	Unique
item_bundle_t_item_id_bundle_item_id_start_ts_uk	{"item_id, bundle_item_id, start_ts"}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
item_clone_t_pk	{id}	Unique
item_clone_t_item_id_target_item_id_start_ts_uk	{"item_id, target_item_id, start_ts"}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
item_coin_t_pk	{id}	Unique
item_coin_t_item_id_start_ts_uk	{"item_id, start_ts"}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
item_image_t_pk	{id}	Unique
item_image_t_item_id_name_start_ts_uk	{"item_id, name, start_ts"}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
item_localization_t_pk	{id}	Unique
item_localization_t_item_id_locale_id_uk	{"item_id, locale_id, start_ts"}	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 itemIDif 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
item_property_t_pk	{id}	Unique
item_property_t_item_id_start_ts_uk	{"item_id, start_ts"}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
item_region_price_t_pk	{id}	Unique
item_region_price_t_item_id_country_id_start_ts_uk	{"item_id, country_id, start_ts"}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
item_status_t_pk	{id}	Unique
item_status_t_item_id_start_ts_uk	{"item_id, start_ts"}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
item_status_type_t_pk	{id}	Unique
item_status_type_t_name_uk	{name}	Unique

---

Table: item_t

This table contains the list of items with their AGS 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	AGS Item Id.	Unique Constraint
job_run_id	bigint	ID of the analytics job run via which this row was imported.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
game_item_t_pk	{id}	Unique
item_t_item_number_uk	{item_number}	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 eg 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
item_tag_t_pk	{id}	Unique
item_tag_t_item_id_tag_start_ts_uk	{"item_id, tag, start_ts"}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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 eg 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
order_item_t_pk	{id}	Unique
order_item_t_item_id_i	{item_id}	Non-Unique
order_item_t_order_id_item_id_uk	{"order_id, item_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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
order_payment_t_pk	{id}	Unique
order_payment_t_order_id_payment_id_uk	{"order_id, payment_id"}	Unique
order_payment_t_payment_id_i	{payment_id}	Non-Unique

Relationship diagram

Show ER Diagram of order_payment_t

Referencing to

---

Table: order_property_t

This table contains order properties eg 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
order_property_t_pk	{id}	Unique
order_property_t_currency_id_i	{currency_id}	Non-Unique
order_property_t_order_id_start_ts_uk	{"order_id, start_ts"}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
order_status_t_pk	{id}	Unique
order_status_t_order_id_order_status_id_start_ts_uk	{"order_id, order_status_id, start_ts"}	Unique
order_status_t_order_status_id_i	{order_status_id}	Non-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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
game_order_status_t_name_uk	{name}	Unique
game_order_status_t_pk	{id}	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 AGS orderIDand user, which implies namespace. Created when a user orders an item.

For example, a user placing an order with orderIDO423102251203926022.

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
namespace_id	bigint	Identifier of the namespace, e.g., game project.	Foreign Key

Indexes

Index name	Indexed columns	Type
order_t_pk	{id}	Unique
order_t_uk	{"order_number, user_id"}	Unique
order_t_user_id_i	{user_id}	Non-Unique
order_t_namespace_id_i	{namespace_id}	Non-Unique

Relationship diagram

Show ER Diagram of order_t

Referencing to

Referenced by

---

Table: fact_activity_daily_order_t

This aggregate table will be deprecated as it is no longer updated.

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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
fact_activity_daily_order_t_pk	{id}	Unique
namespace_date_uk	{"namespace_id, activity_date"}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
realmoney_transaction_item_t_pk	{id}	Unique
realmoney_transaction_item_t_uk	{realmoney_transaction_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 this event occurred.
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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
realmoney_transaction_t_pk	{id}	Unique
realmoney_transaction_t_uk	{"namespace_id, event_number"}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
realmoney_transaction_type_t_pk	{id}	Unique
realmoney_transaction_type_t_uk	{name}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
payment_property_t_region_id_i	{region_id}	Non-Unique
payment_property_t_pk	{id}	Unique
payment_property_t_channel_id_i	{channel_id}	Non-Unique
payment_property_t_currency_id_i	{currency_id}	Non-Unique
payment_property_t_language_id_i	{language_id}	Non-Unique
payment_property_t_payment_id_start_ts_uk	{"payment_id, start_ts"}	Unique
payment_property_t_payment_provider_id_i	{payment_provider_id}	Non-Unique
payment_property_t_payment_status_id_i	{payment_status_id}	Non-Unique
payment_property_t_payment_status_reason_id_i	{payment_status_reason_id}	Non-Unique
payment_property_t_target_user_id_i	{target_user_id}	Non-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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
payment_provider_t_pk	{id}	Unique
payment_provider_t_name_uk	{name}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
payment_status_reason_t_pk	{id}	Unique
payment_status_reason_t_name_uk	{name}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
namespace_id	bigint	Identifier of the namespace, e.g., game project.	Foreign Key

Indexes

Index name	Indexed columns	Type
payment_t_pk	{id}	Unique
payment_t_user_id_payment_number_uk	{"user_id, payment_number"}	Unique
payment_t_namespace_id_i	{namespace_id}	Non-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	AGS currency ID.	Foreign Key, Unique Constraint
currency_symbol	character varying	Symbol used with the currency eg $.
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 eg VIRTUAL.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
currency_detail_t_pk	{id}	Unique
currency_detail_t_uk	{"currency_id, start_ts"}	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	AGS 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
currency_t_pk	{id}	Unique
currency_t_uk	{"currency_code, namespace_id"}	Unique
currency_t_namespace_currency_code_uk	{"namespace_id, currency_code"}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
currency_type_t_pk	{id}	Unique
currency_type_t_uk	{name}	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	AGS 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
fulfillment_code_t_pk	{id}	Unique
fulfillment_code_t_fulfillment_id_code_id_uk	{"fulfillment_id, code_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, eg 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
fulfillment_entitlement_t_pk	{id}	Unique
fulfillment_entitlement_t_entitlement_id_i	{entitlement_id}	Non-Unique
fulfillment_entitlement_t_fulfillment_id_entitlement_id_uk	{"fulfillment_id, entitlement_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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
fulfillment_item_t_pk	{id}	Unique
fulfillment_item_t_fulfillment_id_item_variant_id_uk	{"fulfillment_id, item_variant_id"}	Unique
fulfillment_item_t_item_variant_id_i	{item_variant_id}	Non-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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
fulfillment_order_t_pk	{id}	Unique
fulfillment_order_t_fulfillment_id_order_id_uk	{"fulfillment_id, order_id"}	Unique
fulfillment_order_t_order_id_i	{order_id}	Non-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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
fulfillment_status_type_t_pk	{id}	Unique
fulfillment_status_type_t_name_uk	{name}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
namespace_id	bigint	Identifier of the namespace, e.g., game project.	Foreign Key

Indexes

Index name	Indexed columns	Type
fulfillment2_t_pk	{id}	Unique
fulfillment_t_fulfillment_number_uk	{fulfillment_number}	Unique
fulfillment_t_fulfillment_error_id_i	{fulfillment_error_id}	Non-Unique
fulfillment_t_status_id_i	{status_id}	Non-Unique
fulfillment_t_user_id_i	{user_id}	Non-Unique
fulfillment_t_namespace_id_i	{namespace_id}	Non-Unique
fulfillment_t_start_ts_i	{start_ts}	Non-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 uniqueIDsuch as g4a2cJbszsKnUVqMPHJf.

Analytics usage: getting the analytics numericIDfor 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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
redemption_code_t_pk	{id}	Unique
redemption_code_t_code_uk	{code}	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 eg us-west-1.
lname	character varying	Long name describing the region eg us west us west (n. California).
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
aws_region_id_uk	{id}	Unique
aws_region_t_name_uk	{name}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
continent_t_pkey	{id}	Unique
continent_t_uk	{code}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
sname	text	Short country name used for compact display

Indexes

Index name	Indexed columns	Type
country_t_name_key_uk	{name}	Unique
country_t_pk	{id}	Unique
country_t_name_id_uk	{"name, id"}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
locale_t_pk	{id}	Unique
locale_t_name_uk	{name}	Unique

Relationship diagram

Show ER Diagram of locale_t

Referenced by

---

Table: language_t

This table contains languages used in AGS localization for users, for instance to customize payment related text.

For example, en-US for American English.

Analytics usage: defining the languages shown in AGS.

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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
language_t_name_uk	{name}	Unique
language_t_pk	{id}	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 AGS 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
bd_activity_type_t_pk	{id}	Unique
bd_activity_type_t_uk	{activity_type}	Unique

---

Table: bd_calendar_month_t

This table contains derived calendar month counts of distinct users: MAU. They are categorized by namespace, e.g., game and month.

For example, Monthly Active Users for a specific game in January 2024.

Analytics usage: charting active users per calendar month rather than the alternative 30 day rolling MAU.

Column name	Data type	Description	Constraints
id	bigint		Primary Key
namespace_id	bigint	Identifier of the namespace, e.g., game project.
month_start_ts	Timestamp without time zone	The start of the month for which we count users.
mau	bigint	Monthly active user count. For the current month this will be a partial count.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
bd_calendar_month_t_pkey	{id}	Unique
bd_daily_counts_t_uk	{"namespace_id, month_start_ts"}	Unique
bd_calendar_month_t_month_start_ts_i	{"month_start_ts, namespace_id"}	Non-Unique
bd_bd_calendar_month_t_uk	{"namespace_id, month_start_ts"}	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, e.g., 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
bd_daily_counts_t_pk	{"namespace_id, activity_type_id, day_ts"}	Unique
bd_daily_count_t_day_ts_i	{"day_ts, namespace_id, activity_type_id"}	Non-Unique

---

Table: bd_dimension_t

This table contains dimensions of the distinct counts: namespace, activity type and hour.

For example, an hour timestamp, activity type eg Agreement and a namespace for a game.

Analytics usage: as a utility table for creating visualisations if all possible dimension combinations are needed.

Column name	Data type	Description	Constraints
id	uuid	Identifier for dimension generated by analytics.	Primary Key
namespace_id	bigint	Identifier of the namespace, e.g., game project.
activity_type	text	The type of the activity as listed in bd_activity_type.
hour_ts	Timestamp without time zone	UTC timestamp.
is_day_start	boolean	Indicates if the hour in this record is the start of a day.
is_hourly_t_loaded	boolean	A field for internal use, checking data load processes.

Indexes

Index name	Indexed columns	Type
bd_dimensions_t_pk	{id}	Unique
bd_dimensions_day_i	{"namespace_id, activity_type, is_day_start, hour_ts"}	Non-Unique
bd_dimensions_uk	{"namespace_id, activity_type, hour_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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
bd_etl_auth_time_t_pk	{id}	Unique
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

---

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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
bd_etl_catchup_log_t_pk	{id}	Unique
bd_etl_catchup_log_t_uk	{"namespace_id, lookback_hours, window_end_ts, latest_day_ts"}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
bd_etl_source_detail_t_id_namespace_hr_uk	{"bd_etl_source_log_id, namespace_id, ts_hr"}	Unique
bd_etl_source_detail_t_pk	{id}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
bd_etl_source_log_t_pk	{id}	Unique
bd_etl_source_log_t_table_hr_uk	{"source_table, start_ts"}	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, e.g., 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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_pk	{"ts_hr, activity_type_id, user_id"}	Unique
bd_fact_user_activity_t_country_id_i	{country_id}	Non-Unique
bd_fact_user_activity_t_date_i	{"ts_date, activity_type_id, user_id"}	Non-Unique

---

Table: bd_hourly_count_t

This table contains derived daily counts of distinct user activity: HAU. They are categorized by namespace, e.g., 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
bd_hourly_counts_t_pk	{"namespace_id, activity_type_id, hour_ts"}	Unique
bd_hourly_count_t_hour_ts_i	{"hour_ts, namespace_id, activity_type_id"}	Non-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, e.g., 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
non_unique_player_count	bigint	The non-unique count of users in match sessions.

Indexes

Index name	Indexed columns	Type
bd_session_daily_t_namespace_id_ts_hr_uk	{"namespace_id, day_ts"}	Unique
bd_session_daily_t_pk	{id}	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, e.g., 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
bd_session_hourly_t_namespace_id_ts_hr_uk	{"namespace_id, ts_hr"}	Unique
bd_session_hourly_t_pk	{id}	Unique

---

Group: GDPR

Table: gdpr_account_anonymized_t

This table holds records of users anonymized in accordance with GDPR.

For example, a given user was anonymized on a certain date.

Analytics Usage: for proof of compliance.

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
namespace_id	bigint	Identifier of the namespace, e.g., game project.	Foreign Key

Indexes

Index name	Indexed columns	Type
gdpr_account_anonymized_t_namespace_id_i	{namespace_id}	Non-Unique
gdpr_account_anonymized_t_pk	{id}	Unique
gdpr_account_anonymized_t_uk	{user_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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
namespace_id	bigint	Identifier of the namespace, e.g., game project.	Foreign Key

Indexes

Index name	Indexed columns	Type
gdpr_account_deleted_t_pk	{id}	Unique
gdpr_account_deleted_t_user_id_uk	{"user_id, start_ts, status"}	Unique
gdpr_account_deleted_t_namespace_id_i	{namespace_id}	Non-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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database
namespace_id	bigint	Identifier of the namespace, e.g., game project.	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_namespace_id_i	{namespace_id}	Non-Unique
gdpr_activity_t_requester_user_id_i	{requester_user_id}	Non-Unique
gdpr_activity_t_pk	{id}	Unique
gdpr_activity_t_target_user_id_i	{target_user_id}	Non-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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
gdpr_activity_type_t_pk	{id}	Unique
gdpr_activity_type_t_name_uk	{name}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
analytics_heartbeat_t_pk	{id}	Unique
analytics_heartbeat_t_namespace_id_uk	{namespace_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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
job_program_t_pkey	{id}	Unique
job_program_t_name_idx	{name}	Unique
job_program_t_name_uk	{name}	Unique
job_program_t_pk	{id}	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_pkey	{id}	Unique
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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
d_date_id_pk	{id}	Unique
d_date_date_actual_i	{date_actual}	Non-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	AGS Service name.
lname	character varying	AGS Service long name.
job_run_id	bigint	ID of the analytics job run via which this row was imported.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
http_justice_service_t_pk	{id}	Unique
http_justice_service_t_name_uk	{name}	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
http_request_t_pk	{id}	Unique
http_request_t_uk	{trace_id}	Unique

Relationship diagram

Show ER Diagram of http_request_t

Referencing to

---

Table: job_execution_run_t

This table relates a job run with its execution ID. This table is for internal use. You can ignore this table.

Column name	Data type	Description	Constraints
id	bigint	Identifier for the job execution run.	Primary Key
job_execution_id	bigint	Job execution ID.	Foreign Key
job_run_id	bigint	Job run ID	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
job_execution_run_t_pkey	{id}	Unique
job_execution_run_t_job_execution_id_i	{job_execution_id}	Non-Unique
job_execution_run_t_job_execution_id_job_run_id_uk	{"job_execution_id, job_run_id"}	Unique
job_execution_run_t_job_run_id_i	{job_run_id}	Non-Unique
job_execution_run_t_job_execution_id_i	{job_execution_id}	Non-Unique
job_execution_run_t_job_run_id_i	{job_run_id}	Non-Unique
job_execution_run_t_job_execution_id_job_run_id_uk	{"job_execution_id, job_run_id"}	Unique
job_execution_run_t_pkey	{id}	Unique

Relationship diagram

Show ER Diagram of job_execution_run_t

Referencing to

---

Table: job_execution_t

This table logs of ETL job executions. This table is for internal use. You can ignore this table.

Column name	Data type	Description	Constraints
id	bigint	Identifier for the job execution	Primary Key
job_execution_name	text	Name of the job execution, unique for each execution.
parameter	text	Parameter of the execution
start_ts	Timestamp without time zone	Starting timestamp of job execution
finish_ts	Timestamp without time zone	End timestamp of job execution

Indexes

Index name	Indexed columns	Type
job_execution_t_pkey	{id}	Unique
job_execution_t_job_execution_name_uk	{job_execution_name}	Unique
job_execution_t_pkey	{id}	Unique
job_execution_t_job_execution_name_uk	{job_execution_name}	Unique

Relationship diagram

Show ER Diagram of job_execution_t

Referenced by

---

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 eg 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
device_platform_t_pk	{id}	Unique
device_platform_t_name_uk	{name}	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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
thirdparty_t_name_uk	{name}	Unique
thirdparty_t_pk	{id}	Unique

Relationship diagram

Show ER Diagram of platform_t

Referenced by

---

Group: ETL Utility

Table: etl_file_path_t

This table contains the ETL file path for internal use. This table can be ignored.

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
etl_file_path_t_pkey	{id}	Unique
etl_file_path_t_file_topic_id_i	{file_topic_id}	Non-Unique
etl_file_path_t_filename_uk	{filename}	Unique
etl_file_path_t_bucket_id_i	{bucket_id}	Non-Unique
path_created_ts_i	{path_created_ts}	Non-Unique

Relationship diagram

Show ER Diagram of etl_file_path_t

Referencing to

Referenced by

---

Table: etl_file_topic_t

This table contains the ETL topics path for internal use. This table can be ignored.

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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
etl_file_topic_t_pkey	{id}	Unique
etl_file_topic_t_topic_path_uk	{topic_path}	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, which is sometimes used to derive names of namespaces.

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, e.g., 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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
etl_justice_s3_t_bucket_name_aws_region_id_namespace_i	{"bucket_name, aws_region_id, namespace"}	Non-Unique

Relationship diagram

Show ER Diagram of etl_justice_s3_t

Referencing to

---

Table: etl_justice_url_t

This table contains the ETL domain name for internal use. This table can be ignored.

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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
etl_alb_s3_t_pk	{id}	Unique
etl_alb_s3_t_uk	{"bucket_name, aws_region_id, domain_name"}	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.

For describing how many telemetry events were processed in a given ETL process.

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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
etl_processed_event_t_pkey	{id}	Unique
etl_processed_event_t_etl_file_path_id_i	{file_path_id}	Non-Unique
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

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
etl_reject_t_pkey	{id}	Unique
etl_reject_t_job_run_id_idx	{job_run_id}	Non-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 used on for internal use. This table can be ignored.

Column name	Data type	Description	Constraints
id	bigint	System identifier for the given bucket name	Primary Key
bucket_name	text	The bucket name the ETL ran on
job_run_id	bigint	ID of the analytics job run for this ETL process	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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
etl_s3_bucket_t_pkey	{id}	Unique
etl_s3_bucket_t_bucket_name_uk	{bucket_name}	Unique

Relationship diagram

Show ER Diagram of etl_s3_bucket_t

Referencing to

Referenced by

---

Table: etl_schema_discovery_t

This table contains list of AGS event schemas.agreement_t.

For example, to define a schema for the topic chat and the event userRateLimited with example values.

Analytics usage: Documentation about the various AGS Event Schemas.

Column name	Data type	Description	Constraints
id	bigint	Clarity identifier of the given schema	Primary Key
topic	text	Topic of the schema
event_name	text	Event name of the schema
schema_md5	text	Md5 of schema, used as identifier
schema	text	Concatenated list of the fields in a schema
example	text	JSON example of the schema
is_alerted	boolean	Whether this schema has fired an alert
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
etl_schema_discovery_t_pkey	{id}	Unique
etl_schema_discovery_t_topic_event_name_schema_md5_uk	{"topic, event_name, schema_md5"}	Unique

Relationship diagram

Show ER Diagram of etl_schema_discovery_t

Referenced by

---

Table: etl_schema_dynamic_field_t

This table contains fields of events where they are dynamic fields. Internal use. Generated by ab-schema-dynamic-field module or manually. This table can be ignored.

Column name	Data type	Description	Constraints
id	bigint	Clarity identifier of the dynamic field	Primary Key
topic	text	Event name where the dynamic field is found
event_name	text	Event name where the dynamic field is found
field_md5	text	Md5 of the dynamic field
field	text	The dynamic field
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
etl_schema_dynamic_field_t_pkey	{id}	Unique
etl_schema_dynamic_field_t_topic_event_name_field_md5_uk	{"topic, event_name, field_md5"}	Unique

---

Table: etl_schema_time_t

This table contains timestamp of when the schema is found for each bucket. For internal use, this table can be ignored.

Column name	Data type	Description	Constraints
id	bigint	Clarity identifier of the given row	Primary Key
schema_id	bigint	Clarity identifier of the schema, referring to ID in etl_schema_discovery_t	Foreign Key
bucketname	text	Bucket name of where the schema is found
first_found_ts	Timestamp without time zone	Timestamp of when the schema was first time found in a specific bucket
last_found_ts	Timestamp without time zone	Timestamp of when the schema was last time found in a specific bucket
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
etl_schema_time_t_pkey	{id}	Unique
etl_schema_time_t_schema_id_i	{schema_id}	Non-Unique
etl_schema_time_t_schema_id_bucketname_uk	{"schema_id, bucketname"}	Unique

Relationship diagram

Show ER Diagram of etl_schema_time_t

Referencing to

---

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

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, this table can be ignored.

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
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
foundations_studio_t_pk	{id}	Unique
foundations_studio_t_namespace_uk	{namespace_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.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
alb_logs_adt_t_pk	{id}	Unique

---

Group: DLC

Table: dlc_product_t

This table contains product identifiers for the DLC. It can be used to track which third party the product comes from.

For example, a record of a special edition DLC with third-party ID spec_edition on Steam

Analytics usage: Reporting about DLCs by platform showing their third-party information.

Column name	Data type	Description	Constraints
id	bigint	Identifier for DLC product generated by Clarity.	Primary Key
namespace_id	bigint	Identifier of the namespace.	Foreign Key, Unique Constraint
platform_id	bigint	Identifier of which platform sells this product.	Foreign Key, Unique Constraint
dlc_id	text	Identifier of the DLC product.	Unique Constraint
third_party_id	text	Identifier of the DLC third party.	Unique Constraint
job_run_id	bigint	ID of the analytics job run via which this row was imported.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
dlc_product_t_pk	{id}	Unique
dlc_product_t_uk	{"namespace_id, platform_id, dlc_id, third_party_id"}	Unique
dlc_product_t_namespace_id_i	{namespace_id}	Non-Unique
dlc_product_t_dlc_id_i	{dlc_id}	Non-Unique
dlc_product_t_third_party_id_i	{third_party_id}	Non-Unique
dlc_product_t_platform_id_i	{platform_id}	Non-Unique

Relationship diagram

Show ER Diagram of dlc_product_t

Referencing to

Referenced by

---

Table: dlc_status_t

This table stores DLC current state.

For example, showing a given DLC got status FULFILLED on 2024-11-19 16:56:29.151

Analytics usage: to filter or categorize DLCs by status in a visualization.

Column name	Data type	Description	Constraints
id	bigint	Identifier for DLC status generated by Clarity.	Primary Key
dlc_synced_id	bigint	Identifier of the DLC synced.	Foreign Key, Unique Constraint
dlc_status	text	Status of the DLC	Unique Constraint
start_ts	Timestamp without time zone	When the purchase happened.	Unique Constraint
end_ts	Timestamp without time zone	When the revoke happened.
job_run_id	bigint	ID of the analytics job run via which this row was imported.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
dlc_status_t_pk	{id}	Unique
dlc_status_t_uk	{"dlc_synced_id, dlc_status, start_ts"}	Unique
dlc_status_t_dlc_synced_id_i	{dlc_synced_id}	Non-Unique

Relationship diagram

Show ER Diagram of dlc_status_t

Referencing to

---

Table: dlc_synced_t

This table contains the core records of completed DLC transactions.

For example, a record of a transaction for a product by a given user for a given game.

Analytics usage: to track DLC history for a user.

Column name	Data type	Description	Constraints
id	bigint	Identifier for DLC transaction generated by Clarity	Primary Key
namespace_id	bigint	Identifier of the namespace.	Foreign Key, Unique Constraint
user_id	bigint	Identifier of the user.	Foreign Key, Unique Constraint
product_id	bigint	Identifier of the DLC product.	Foreign Key, Unique Constraint
transaction_id	text	Identifier of the transaction.	Unique Constraint
job_run_id	bigint	ID of the analytics job run via which this row was imported.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
dlc_synced_t_pk	{id}	Unique
dlc_synced_t_uk	{"namespace_id, user_id, product_id, transaction_id"}	Unique
dlc_synced_t_namespace_id_i	{namespace_id}	Non-Unique
dlc_synced_t_user_id_i	{user_id}	Non-Unique
dlc_synced_t_product_id_i	{product_id}	Non-Unique
dlc_synced_t_transaction_id_i	{transaction_id}	Non-Unique

Relationship diagram

Show ER Diagram of dlc_synced_t

Referencing to

Referenced by

---

Group: Extend Usage

Table: extend_usage_app_pod_t

This table contains the Extend app usage pod.

For example, a pod ID used by event-handler app in a report.

Analytics usage: internal use to list all the pods used by an app in the report.

Column name	Data type	Description	Constraints
id	bigint	Identifier for Extend app pod name generated by Clarity	Primary Key
extend_usage_app_id	bigint	ID of the Extend app usage of the pod	Foreign Key
pod_name	text	Name of the pod in from Extend app usage report
job_run_id	bigint		Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
extend_usage_app_pod_t_pkey	{id}	Unique
extend_usage_app_pod_t_extend_usage_app_id_i	{extend_usage_app_id}	Non-Unique

Relationship diagram

Show ER Diagram of extend_usage_app_pod_t

Referencing to

---

Table: extend_usage_app_t

This table contains the Extend app usage record and details.

For example, a record of event-handler app usage, scenario and the pods used.

Analytics usage: analysis of Extend app usage

Column name	Data type	Description	Constraints
id	bigint	Identifier for Extend app usage generated by Clarity	Primary Key
extend_usage_id	bigint	ID of the extend usage report	Foreign Key, Unique Constraint
app_id	text	ID of the app in the extend usage report	Unique Constraint
app_name	text	Name of the app in the extend usage report	Unique Constraint
scenario	text	Extend app usage scenario name	Unique Constraint
job_run_id	bigint	ID of the analytics job run via which this row was imported.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
extend_usage_app_t_pkey	{id}	Unique
extend_usage_app_t_extend_usage_id_i	{extend_usage_id}	Non-Unique
extend_usage_app_t_uk	{"extend_usage_id, app_id, app_name, scenario"}	Unique

Relationship diagram

Show ER Diagram of extend_usage_app_t

Referencing to

Referenced by

---

Table: extend_usage_infra_t

This table contains the extend infra usage record and details.

For example, a record of ECR usage amount in bytes in us-west-2.

Analytics usage: analysis of extend infra usage by type and region.

Column name	Data type	Description	Constraints
id	bigint	Identifier for extend infra network usage generated by Clarity	Primary Key
extend_usage_id	bigint	ID of the extend usage report	Foreign Key, Unique Constraint
extend_usage_infra_type_id	bigint	ID of the infra type	Foreign Key, Unique Constraint
amount	double precision	Extend infra usage amount
unit	text	Extend infra usage unit of measurement
region	text	Extend infra usage region identifier
job_run_id	bigint	ID of the analytics job run via which this row was imported.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
extend_usage_infra_t_pkey	{id}	Unique
extend_usage_infra_t_extend_usage_id_i	{extend_usage_id}	Non-Unique
extend_usage_infra_t_extend_usage_infra_type_id_i	{extend_usage_infra_type_id}	Non-Unique
extend_usage_infra_t_uk	{"extend_usage_id, extend_usage_infra_type_id"}	Unique

Relationship diagram

Show ER Diagram of extend_usage_infra_t

Referencing to

---

Table: extend_usage_infra_type_t

This table contains the extend infra type.

For example, elastic container registry (ECR).

Analytics usage: segregating extend infra usage by infra type.

Column name	Data type	Description	Constraints
id	bigint	Identifier for extend infra network type generated by Clarity	Primary Key
name	text	Network type used in the extend infra usage report	Unique Constraint
lname	text	Extended name of the network type	Unique Constraint
job_run_id	bigint	ID of the analytics job run via which this row was imported.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
extend_usage_infra_type_t_pkey	{id}	Unique
extend_usage_infra_type_t_uk	{"name, lname"}	Unique

Relationship diagram

Show ER Diagram of extend_usage_infra_type_t

Referencing to

Referenced by

---

Table: extend_usage_t

This table contains the extend usage report for either app usage or infra usage.

For example, a record of app usage on a pod and its duration.

Analytics usage: analysis of extend usage for both app usage and infra usage.

Column name	Data type	Description	Constraints
id	bigint	Identifier for extend generated by Clarity	Primary Key
usage_id	text	The ID of the extend usage report event	Unique Constraint
namespace_id	bigint	Identifier of the namespace, e.g., game project.	Foreign Key, Unique Constraint
start_time	Timestamp without time zone	Extend usage report start time
end_time	Timestamp without time zone	Extend usage report end time
job_run_id	bigint	ID of the analytics job run via which this row was imported.	Foreign Key
environment	text	Indicates the environment of the extend usage report
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
extend_usage_t_pkey	{id}	Unique
extend_usage_t_namespace_id_i	{namespace_id}	Non-Unique
extend_usage_t_uk	{"usage_id, namespace_id"}	Unique

Relationship diagram

Show ER Diagram of extend_usage_t

Referencing to

Referenced by

---

Group: Grafana Usage

Table: grafana_usage_t

This table contains the Grafana usage report for the users who was logged in, including details about the namespace, environment, customer.

For example, it can be used to log when a specific Grafana instance within a customer's namespace is accessed.

Analytics usage: tracking usage patterns or billing based on the frequency of access.

Column name	Data type	Description	Constraints
id	bigint	Identifier for Grafana usage generated by Clarity.	Primary Key
namespace_id	bigint	Identifier of the namespace.	Foreign Key, Unique Constraint
usage_id	text	The ID of the Grafana usage report event.	Unique Constraint
stack	text	Stack of the Grafana usage report.
environment	text	Environment of the Grafana usage report.
customer	text	Your studio name or accelbyte.
job_run_id	bigint	ID of the analytics job run via which this row was imported.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
grafana_usage_t_pk	{id}	Unique
grafana_usage_t_uk	{"namespace_id, usage_id"}	Unique
grafana_usage_t_namespace_id_i	{namespace_id}	Non-Unique

Relationship diagram

Show ER Diagram of grafana_usage_t

Referencing to

Referenced by

---

Table: grafana_user_activity_t

This table records Grafana usage activity and details for users, such as login times and namespaces.

For example, can be utilized to count the HAU.

Analytics usage: tracking user activity by user and role.

Column name	Data type	Description	Constraints
id	bigint	Identifier for Grafana usage activity generated by Clarity	Primary Key
namespace_id	bigint	Relates to namespace_t.	Foreign Key, Unique Constraint
email	text	Grafana user email	Unique Constraint
grafana_usage_id	bigint	ID of the Grafana usage report	Foreign Key
grafana_user_role_id	bigint	ID of the Grafana user role	Foreign Key, Unique Constraint
last_seen_at	Timestamp without time zone	Grafana user last timestamp activity	Unique Constraint
job_run_id	bigint	ID of the analytics job run via which this row was imported.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
grafana_user_activity_t_pk	{id}	Unique
grafana_user_activity_t_uk	{"namespace_id, last_seen_at, email, grafana_user_role_id"}	Unique
grafana_user_activity_t_namespace_id_i	{namespace_id}	Non-Unique
grafana_user_activity_t_grafana_usage_id_i	{grafana_usage_id}	Non-Unique
grafana_user_activity_t_namespace_id_email_i	{"namespace_id, email"}	Non-Unique

Relationship diagram

Show ER Diagram of grafana_user_activity_t

Referencing to

---

Table: grafana_user_role_t

This table contains the Grafana user roles, such as Admin and Viewer.

For example, showing that a given user is an Admin.

Analytics usage: to track the activity of each role.

Column name	Data type	Description	Constraints
id	bigint	Identifier for Grafana user role generated by Clarity	Primary Key
name	text	User role used in the Grafana usage report	Unique Constraint
job_run_id	bigint	ID of the analytics job run via which this row was imported.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
grafana_user_role_t_pk	{id}	Unique
grafana_user_role_t_uk	{name}	Unique

Relationship diagram

Show ER Diagram of grafana_user_role_t

Referencing to

Referenced by

---

Group: Game Standard Events

Table: gse_entity_dead_t

This table contains GSE events triggered when the entity dies or destroyed.

For example, when a player is killed or an building is destroyed in a game.

Analytics usage: understand the frequency and circumstances under which entities (players and important NPCs) die or are destroyed.

Column name	Data type	Description	Constraints
gse_event_id	bigint	Relates to gse_event_t table. Identifier for gse events generated by analytics.	Foreign Key, Primary Key
entity_id	text	Unique identifier of the entity. Empty if the entity is not related to items.
entity_type	text	The type of entity can be player, item, etc.
dead_ts	Timestamp without time zone	Timestamp when entity is die.
death_day	integer	In-game days that the death occurred on. Useful for survival-crafter games with day-night cycles.
death_location	text	For categorization. It can be a map, a named area of a map, etc.
death_type	text	For categorization. e.g., self, player, environment.
death_source	text	Can either be 'Player' for PVP gameplay, or the name of a game entity.
user_id	bigint	Relates to user_t table. Player identifier. Empty if entity is not related to players.	Foreign Key
namespace_id	bigint	Relates to namespace_t table. Identifier of the namespace, e.g., game project.	Foreign Key
job_run_id	bigint	Relates to job_run_t. ID of the analytics job run via which this row was inserted.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database.
mtime	Timestamp without time zone	When this record was last updated in this database.

Indexes

Index name	Indexed columns	Type
gse_entity_dead_t_pk	{gse_event_id}	Unique
gse_entity_dead_t_entity_id_i	{entity_id}	Non-Unique
gse_entity_dead_t_entity_type_i	{entity_type}	Non-Unique
gse_entity_dead_t_user_i	{user_id}	Non-Unique
gse_entity_dead_t_namespace_id_i	{"namespace_id, dead_ts"}	Non-Unique
gse_entity_dead_t_dead_ts_i	{dead_ts}	Non-Unique

Relationship diagram

Show ER Diagram of gse_entity_dead_t

Referencing to

---

Table: gse_entity_leveled_t

This table contains GSE events to track the progression level.

For example, a player level, entity level that belongs to the player or levels of important NPCs and bosses.

Analytics usage: understand the rate of progression and progress at any point in time.

Column name	Data type	Description	Constraints
gse_event_id	bigint	Relates to gse_event_t table. Identifier for gse events generated by analytics.	Foreign Key, Primary Key
entity_id	text	Unique identifier of the entity. Empty if the entity is not related to items.
entity_type	text	The type of entity can be player, item, etc.
point_ts	Timestamp without time zone	Timestamp when the entity being applied.
level_stat	text	The stat being increased.
level_change	text	The increase in level.
level_current	text	The level after the change in increase in level has been applied.
user_id	bigint	Relates to user_t table. Player identifier. Empty if entity is not related to players.	Foreign Key
namespace_id	bigint	Relates to namespace_t table. Identifier of the namespace, e.g., game project.	Foreign Key
job_run_id	bigint	Relates to job_run_t. ID of the analytics job run via which this row was inserted.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database.
mtime	Timestamp without time zone	When this record was last updated in this database.

Indexes

Index name	Indexed columns	Type
gse_entity_leveled_t_pk	{gse_event_id}	Unique
gse_entity_leveled_t_entity_id_i	{entity_id}	Non-Unique
gse_entity_leveled_t_entity_type_i	{entity_type}	Non-Unique
gse_entity_leveled_t_point_ts_i	{point_ts}	Non-Unique
gse_entity_leveled_t_user_id_i	{user_id}	Non-Unique
gse_entity_leveled_t_namespace_id_i	{"namespace_id, point_ts"}	Non-Unique

Relationship diagram

Show ER Diagram of gse_entity_leveled_t

Referencing to

---

Table: gse_event_t

This table contains common fields for these events in the master telemetry payload. It's been used to store Game Standard Events only.

For example, a record of popup appearance in a game played by a specific user at a given time.

Analytics usage: GSE analysis, for instance to resource flow of a game.

Column name	Data type	Description	Constraints
id	bigint	Identifier for telemetry events generated by analytics.	Primary Key
gse_event_name	text	Name of the particular GSE event.
event_ts	Timestamp without time zone	When the event occurred.
event_number	text	Unique identifier for an event.
device_type	text	Device type of the user.
user_id	bigint	Relates to user_t. Non-null if this event is associated with a particular user.	Foreign Key
flight_id	bigint	Relates to flight_t. ID of the request to track user activities across services.	Foreign Key
namespace_id	bigint	Relates to namespace_t. Identifier of the namespace, e.g., game project.	Foreign Key
job_run_id	bigint	Relates to job_run_t. ID of the analytics job run via which this row was inserted.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database.
mtime	Timestamp without time zone	When this record was last updated in this database.

Indexes

Index name	Indexed columns	Type
gse_event_t_pk	{id}	Unique
gse_event_t_event_number_uk	{event_number}	Unique
gse_event_t_flight_id_i	{flight_id}	Non-Unique
gse_event_t_namespace_id_i	{"namespace_id, event_ts"}	Non-Unique
gse_event_t_user_id_i	{user_id}	Non-Unique

Relationship diagram

Show ER Diagram of gse_event_t

Referencing to

Referenced by

---

Table: gse_matchinfo_ended_t

This table contains GSE events triggered when match gameplay ends, and any global results.

For example, solo game ended with victory result.

Analytics usage: analyze match results.

Column name	Data type	Description	Constraints
gse_event_id	bigint	Relates to gse_event_t table. Identifier for gse events generated by analytics.	Foreign Key, Primary Key
matchinfo_id	text	Unique identifier shared between all matchinfo events describing the same match.
match_id	text	If this match is a match-session created as a result of the matchmaking system, this field should mirror match_id from matchsession_t. Otherwise, it's empty.
end_reason	text	The reason that the match ended e.g., victory, failure, abandoned.
winner	text	The winning team.
ended_ts	Timestamp without time zone	Timestamp when the mission ended.
namespace_id	bigint	Relates to namespace_t table. Identifier of the namespace, e.g., game project.	Foreign Key
job_run_id	bigint	Relates to job_run_t. ID of the analytics job run via which this row was inserted.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database.
mtime	Timestamp without time zone	When this record was last updated in this database.

Indexes

Index name	Indexed columns	Type
gse_matchinfo_ended_t_pk	{gse_event_id}	Unique
gse_matchinfo_ended_t_matchinfo_id_i	{matchinfo_id}	Non-Unique
gse_matchinfo_ended_t_ended_ts_i	{ended_ts}	Non-Unique
gse_matchinfo_ended_t_namespace_id_i	{"namespace_id, ended_ts"}	Non-Unique

Relationship diagram

Show ER Diagram of gse_matchinfo_ended_t

Referencing to

---

Table: gse_matchinfo_player_t

This table contains GSE events about an individual player details participating in a match.

For example, their rank, class, role, and team.

Analytics usage: gaining match player stats.

Column name	Data type	Description	Constraints
gse_event_id	bigint	Relates to gse_event_t table. Identifier for gse events generated by analytics.	Foreign Key, Primary Key
matchinfo_id	text	Unique identifier shared between all matchinfo events describing the same match.
match_id	text	If this match is a match-session created as a result of the matchmaking system, this field should mirror match_id from matchsession_t. Otherwise, it's empty.
player_team	text	In team-based game modes, used to identify which players are on the same team. E.g., A, B, attacker, defender.
player_class	text	In game modes where players have a fixed character class per match, used to identify which the player has opted to play for this match.
player_rank	text	This value can be used to segment data by rank in game modes where players have a rank.
user_id	bigint	Relates to user_t table. Player identifier.	Foreign Key
namespace_id	bigint	Relates to namespace_t table. Identifier of the namespace, e.g., game project.	Foreign Key
job_run_id	bigint	Relates to job_run_t. ID of the analytics job run via which this row was inserted.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database.
mtime	Timestamp without time zone	When this record was last updated in this database.

Indexes

Index name	Indexed columns	Type
gse_matchinfo_player_t_pk	{gse_event_id}	Unique
gse_matchinfo_player_t_matchinfo_id_i	{matchinfo_id}	Non-Unique
gse_matchinfo_player_t_user_id_i	{user_id}	Non-Unique
gse_matchinfo_player_t_namespace_id_i	{"namespace_id, user_id"}	Non-Unique

Relationship diagram

Show ER Diagram of gse_matchinfo_player_t

Referencing to

---

Table: gse_matchinfo_t

This table contains GSE events describes the start time when gameplay begins and any parameters about the match.

For example, match parameters game mode and difficulty.

Analytics usage: track game-specific parameters.

Column name	Data type	Description	Constraints
gse_event_id	bigint	Relates to gse_event_t table. Identifier for gse events generated by analytics.	Foreign Key, Primary Key
matchinfo_id	text	Unique identifier shared between all matchinfo events describing the same match.
match_id	text	If this match is a match-session created as a result of the matchmaking system, this field should mirror match_id from matchsession_t. Otherwise, it's empty.
match_difficulty	text	Optional category to the level of match difficulty.
game_mode	text	Game mode category, e.g, PVP, Solo, Story.
started_ts	Timestamp without time zone	Timestamp when the gameplay begins.
namespace_id	bigint	Relates to namespace_t table. Identifier of the namespace, e.g., game project.	Foreign Key
job_run_id	bigint	Relates to job_run_t. ID of the analytics job run via which this row was inserted.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database.
mtime	Timestamp without time zone	When this record was last updated in this database.

Indexes

Index name	Indexed columns	Type
gse_matchinfo_t_pk	{gse_event_id}	Unique
gse_matchinfo_t_matchinfo_id_i	{matchinfo_id}	Non-Unique
gse_matchinfo_t_started_ts_i	{started_ts}	Non-Unique
gse_matchinfo_t_namespace_id_i	{"namespace_id, started_ts"}	Non-Unique

Relationship diagram

Show ER Diagram of gse_matchinfo_t

Referencing to

---

Table: gse_mission_ended_t

This table contains GSE events triggered when a mission reaches an end state.

For example, when the mission has been completed and delivered to the mission giver, or the tutorial mission has ended.

Analytics usage: learning the time it takes to finish the mission and the success or failure rate of it.

Column name	Data type	Description	Constraints
gse_event_id	bigint	Relates to gse_event_t table. Identifier for gse events generated by analytics.	Foreign Key, Primary Key
mission_id	text	Unique ID of the mission, such as its game asset ID.
mission_instance_id	text	Reusable ID to distinguish repeatable missions (e.g., dailies) and reattempts of missions with failure or abandon states.
mission_success	boolean	Boolean value whether the mission ended successfully or not.
mission_outcome	text	Optional subcategories of success and failures.
ended_ts	Timestamp without time zone	Timestamp when the mission ended.
user_id	bigint	Relates to user_t table. Player identifier.	Foreign Key
namespace_id	bigint	Relates to namespace_t table. Identifier of the namespace, e.g., game project.	Foreign Key
job_run_id	bigint	Relates to job_run_t. ID of the analytics job run via which this row was inserted.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database.
mtime	Timestamp without time zone	When this record was last updated in this database.

Indexes

Index name	Indexed columns	Type
gse_mission_ended_t_pk	{gse_event_id}	Unique
gse_mission_ended_t_mission_id_mission_instance_id_i	{"mission_id, mission_instance_id"}	Non-Unique
gse_mission_ended_t_ended_ts_i	{ended_ts}	Non-Unique
gse_mission_ended_t_user_i	{user_id}	Non-Unique
gse_mission_ended_t_namespace_id_i	{"namespace_id, ended_ts"}	Non-Unique

Relationship diagram

Show ER Diagram of gse_mission_ended_t

Referencing to

---

Table: gse_mission_started_t

This table contains GSE events triggered when the player obtains the task or the tutorial is started.

For example, when the user start the solo mission.

Analytics usage: track the start of a mission and its details.

Column name	Data type	Description	Constraints
gse_event_id	bigint	Relates to gse_event_t table. Identifier for gse events generated by analytics.	Foreign Key, Primary Key
mission_id	text	Unique ID of the mission, such as its game asset ID.
mission_instance_id	text	Reusable ID to distinguish repeatable missions (e.g., dailies) and reattempts of missions with failure or abandon states.
mission_name	text	Human readable or non-localized player facing name to make dashboards more user friendly.
mission_type	text	For categorization purposes. E.g., solo, group, pvp, raid, story, tutorial, etc.
mission_location	text	To categorize the mission. Could be the ID or name of a map, zone in an open-world game, or a chapter.
mission_difficulty	text	Optional category to the level of mission difficulty.
started_ts	Timestamp without time zone	Timestamp when the mission started.
user_id	bigint	Relates to user_t table. Player identifier.	Foreign Key
namespace_id	bigint	Relates to namespace_t table. Identifier of the namespace, e.g., game project.	Foreign Key
job_run_id	bigint	Relates to job_run_t. ID of the analytics job run via which this row was inserted.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database.
mtime	Timestamp without time zone	When this record was last updated in this database.

Indexes

Index name	Indexed columns	Type
gse_mission_started_t_pk	{gse_event_id}	Unique
gse_mission_started_t_mission_id_mission_instance_id_i	{"mission_id, mission_instance_id"}	Non-Unique
gse_mission_started_t_started_ts_i	{started_ts}	Non-Unique
gse_mission_started_t_user_id_i	{user_id}	Non-Unique
gse_mission_started_t_namespace_id_i	{"namespace_id, started_ts"}	Non-Unique

Relationship diagram

Show ER Diagram of gse_mission_started_t

Referencing to

---

Table: gse_mission_step_ended_t

This table contains GSE events to track important milestones in lengthy missions with multiple sequential steps.

For example, multi-step story missions and tutorials.

Analytics usage: learning the time it takes to progress and where attrition occurs.

Column name	Data type	Description	Constraints
gse_event_id	bigint	Relates to gse_event_t table. Identifier for gse events generated by analytics.	Foreign Key, Primary Key
mission_id	text	Unique ID of the mission, such as its game asset ID.
mission_instance_id	text	Reusable ID to distinguish repeatable missions (e.g., dailies) and reattempts of missions with failure or abandon states.
mission_step	integer	Step number starting at 1 and increments. For identifying the order in the sequence.
mission_step_name	text	An optional name to help understand which step we are referring to.
ended_ts	Timestamp without time zone	Timestamp when the mission step ended.
user_id	bigint	Relates to user_t table. Player identifier.	Foreign Key
namespace_id	bigint	Relates to namespace_t table. Identifier of the namespace, e.g., game project.	Foreign Key
job_run_id	bigint	Relates to job_run_t. ID of the analytics job run via which this row was inserted.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database.
mtime	Timestamp without time zone	When this record was last updated in this database.

Indexes

Index name	Indexed columns	Type
gse_mission_step_ended_t_pk	{gse_event_id}	Unique
gse_mission_step_ended_t_mission_id_mission_instance_id_i	{"mission_id, mission_instance_id"}	Non-Unique
gse_mission_step_ended_t_ended_ts_i	{ended_ts}	Non-Unique
gse_mission_step_ended_t_namespace_id_i	{"namespace_id, ended_ts"}	Non-Unique
gse_mission_step_ended_t_user_id_i	{user_id}	Non-Unique

Relationship diagram

Show ER Diagram of gse_mission_step_ended_t

Referencing to

---

Table: gse_popup_appear_t

This table contains GSE events triggered when the appearance of popups.

For example, context-sensitive tutorials or store offers popups.

Analytics usage: understand the flow of users and their exposure to features across the game.

Column name	Data type	Description	Constraints
gse_event_id	bigint	Relates to gse_event_t table. Identifier for gse events generated by analytics.	Foreign Key, Primary Key
popup_id	text	Unique ID of the popup.
popup_type	text	For categorization e.g., tutorial, offer.
popup_name	text	Human readable, non-localised player facing name to make dashboards more user friendly.
appear_ts	Timestamp without time zone	Timestamp when the popup appeared.
user_id	bigint	Relates to user_t table. Player identifier.	Foreign Key
namespace_id	bigint	Relates to namespace_t table. Identifier of the namespace, e.g., game project.	Foreign Key
job_run_id	bigint	Relates to job_run_t. ID of the analytics job run via which this row was inserted.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database.
mtime	Timestamp without time zone	When this record was last updated in this database.

Indexes

Index name	Indexed columns	Type
gse_popup_appear_t_pk	{gse_event_id}	Unique
gse_popup_appear_t_popup_id_i	{popup_id}	Non-Unique
gse_popup_appear_t_appear_ts_i	{appear_ts}	Non-Unique
gse_popup_appear_t_user_id_i	{user_id}	Non-Unique
gse_popup_appear_t_namespace_id_i	{"namespace_id, appear_ts"}	Non-Unique

Relationship diagram

Show ER Diagram of gse_popup_appear_t

Referencing to

---

Table: gse_resource_flow_t

This table contains GSE events to track changes to gameplay resources and items.

For example, item granted or coin transaction.

Analytics usage: understand the gameplay resource and item transaction flow.

Column name	Data type	Description	Constraints
gse_event_id	bigint	Relates to gse_event_t table. Identifier for gse events generated by analytics.	Foreign Key, Primary Key
flow_ts	Timestamp without time zone	Timestamp when the flow is happening.
flow_type	text	Type of flow. Can be sink or source.
transaction_id	text	Unique guid shared between all resource flows that are part of the same transaction.
transaction_type	text	Type of transaction e.g., traded, consumed, etc.
resource_name	text	Name of item or currency being transacted.
amount	text	The amount of resources transacted.
end_balance	text	Resource remaining after the amount of resources transacted.
user_id	bigint	Relates to user_t table. Player identifier.	Foreign Key
namespace_id	bigint	Relates to namespace_t table. Identifier of the namespace, e.g., game project.	Foreign Key
job_run_id	bigint	Relates to job_run_t. ID of the analytics job run via which this row was inserted.	Foreign Key
ctime	Timestamp without time zone	When this record was first created in this database.
mtime	Timestamp without time zone	When this record was last updated in this database.

Indexes

Index name	Indexed columns	Type
gse_resource_flow_t_pk	{gse_event_id}	Unique
gse_resource_flow_t_flow_ts_i	{flow_ts}	Non-Unique
gse_resource_flow_t_transaction_id_i	{transaction_id}	Non-Unique
gse_resource_flow_t_user_id_i	{user_id}	Non-Unique
gse_resource_flow_t_namespace_id_i	{"namespace_id, flow_ts"}	Non-Unique

Relationship diagram

Show ER Diagram of gse_resource_flow_t

Referencing to

---

Group: HLL Aggregates

Table: hll_new_user_t

This table contains the record per combination of dimensions including user first activity date that can be used for querying unique user counts.

For example, unique users from a row for a given hour, namespace and test user flag.

Analytics usage: counting new users.

Column name	Data type	Description	Constraints
id	bigint	Identifier for agreement generated by analytics.	Primary Key
namespace_id	bigint	Relates to namespace_t, namespace of user carrying out the Activity.
hour_ts	Timestamp without time zone	Start of the hour in which the activity occurred.
platform_id	bigint	Relates to platform_t, Platform eg XBox of the User. -1 if unknown.
start_date	date	Date of first user activity.
is_test_user	boolean	Is the user a test user? eg for load testing. If true the user will be in the test_user_t table.
users	HLL	This is used by the postgresql-hll extension to count unique users.
ctime	Timestamp without time zone	When this record was created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
hll_new_user_t_start_date_ix	{"namespace_id, hour_ts, start_date"}	Non-Unique
hll_new_user_t_is_test_user_ix	{"namespace_id, hour_ts, is_test_user"}	Non-Unique
hll_new_user_t_uk	{"namespace_id, hour_ts, platform_id, start_date, is_test_user"}	Unique
hll_new_user_t_pk	{id}	Unique

---

Table: hll_standard_segment_t

This table contains the record per combination of dimensions that can be used for querying unique user counts.

For example, a row for a given date, namespace, the activity of fulfilment, in the USA, on XBox.

Analytics usage: getting HAUs for a given game.

Column name	Data type	Description	Constraints
id	bigint	Date of first user activity of the player based.	Primary Key
namespace_id	bigint	Relates to namespace_t, namespace of user carrying out the Activity.
hour_ts	Timestamp without time zone	Start of the hour in which the activity occurred.
country_id	bigint	Relates to country_t, country of user. -1 if unknown.
platform_id	bigint	Relates to platform_t, Platform eg XBox of the User. -1 if unknown.
device_id	bigint	Relates to device_platform_t, showing device of the User. -1 if unknown.
player_age	smallint	Age in years of the player based on user_date_of_birth_t. -1 if unknown.
is_test_user	boolean	Is the user a test user? eg for load testing. If true the user will be in the test_user_t table.
users	HLL	This is used by the postgresql-hll extension to count unique users.
ctime	Timestamp without time zone	When this record was created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
hll_standard_segment_t_pk	{id}	Unique
hll_standard_segment_t_platform_id_ix	{"namespace_id, hour_ts, platform_id"}	Non-Unique
hll_standard_segment_t_uk	{"namespace_id, hour_ts, country_id, platform_id, device_id, player_age, is_test_user"}	Unique
hll_standard_segment_t_device_id_ix	{"namespace_id, hour_ts, device_id"}	Non-Unique
hll_standard_segment_t_player_age_ix	{"namespace_id, hour_ts, player_age"}	Non-Unique
hll_standard_segment_t_is_test_user_ix	{"namespace_id, hour_ts, is_test_user"}	Non-Unique

---

Group: IAP

Table: iap_processed_t

This table contains the core records of completed in-app purchase transactions with their full details (user, product, quantity, etc).

For example, details of an IAP by a given user including its product and transaction details.

Analytics usage: to track successful purchase history for each user.

Column name	Data type	Description	Constraints
id	bigint	Identifier for in-app purchase generated by Clarity	Primary Key
namespace_id	bigint	Identifier of the namespace.	Foreign Key, Unique Constraint
user_id	bigint	Identifier of the user.	Foreign Key, Unique Constraint
product_id	bigint	Identifier of the product.	Foreign Key, Unique Constraint
transaction_id	text	Identifier of the transaction.	Unique Constraint
order_id	text	Identifier of the order.	Unique Constraint
quantity	bigint	How many items were bought.
currency	text	Which currency was used (USD, EUR, etc.).
sandbox	boolean	A flag indicates whether the user has sandbox permission.
region	text	Country value from ISO countries.
job_run_id	bigint	ID of the analytics job run via which this row was imported.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
iap_processed_t_pk	{id}	Unique
iap_processed_t_uk	{"namespace_id, user_id, product_id, transaction_id, order_id"}	Unique
iap_processed_t_namespace_id_i	{namespace_id}	Non-Unique
iap_processed_t_user_id_i	{user_id}	Non-Unique
iap_processed_t_product_id_i	{product_id}	Non-Unique
iap_processed_t_transaction_id_i	{transaction_id}	Non-Unique
iap_processed_t_order_id_i	{order_id}	Non-Unique

Relationship diagram

Show ER Diagram of iap_processed_t

Referencing to

Referenced by

---

Table: iap_product_t

This table contains platform-specific product identifiers for in-app purchases.

For example, a skin sold on Xbox for a given game.

Analytics usage: to track which platform the product comes from.

Column name	Data type	Description	Constraints
id	bigint	Identifier for IAP product generated by Clarity.	Primary Key
namespace_id	bigint	Identifier of the namespace.	Foreign Key, Unique Constraint
platform_id	bigint	Identifier of which platform sells this product.	Foreign Key, Unique Constraint
product_id	text	Store-specific product code.	Unique Constraint
job_run_id	bigint	ID of the analytics job run via which this row was imported.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
iap_product_t_pk	{id}	Unique
iap_product_t_uk	{"namespace_id, platform_id, product_id"}	Unique
iap_product_t_namespace_id_i	{namespace_id}	Non-Unique
iap_product_t_product_id_i	{product_id}	Non-Unique
iap_product_t_platform_id_i	{platform_id}	Non-Unique

Relationship diagram

Show ER Diagram of iap_product_t

Referencing to

Referenced by

---

Table: iap_status_t

This table stores product IAP current state.

For example, an IAP has the FULLFILLED status for a given time range.

Analytics usage: to track the status of the IAP.

Column name	Data type	Description	Constraints
id	bigint	Identifier for IAP status generated by Clarity.	Primary Key
iap_processed_id	bigint	Identifier of the IAP processed.	Foreign Key, Unique Constraint
iap_status	text	Status of the IAP.	Unique Constraint
start_ts	Timestamp without time zone	When the purchase happened.	Unique Constraint
end_ts	Timestamp without time zone	When the purchase happened.
job_run_id	bigint	ID of the analytics job run via which this row was imported.
ctime	Timestamp without time zone	When this record was first created in this database
mtime	Timestamp without time zone	When this record was last updated in this database

Indexes

Index name	Indexed columns	Type
iap_status_t_pk	{id}	Unique
iap_status_t_uk	{"iap_processed_id, iap_status, start_ts"}	Unique
iap_status_t_iap_processed_id_i	{iap_processed_id}	Non-Unique

Relationship diagram

Show ER Diagram of iap_status_t

Referencing to

---