Overview
This document defines the key data tables used for analytics and reporting in GUIDEcx.
It includes:
Table definitions for core entities
Field-level descriptions
Usage notes and best practices for joins, filtering, and casting
Deprecation guidance for legacy fields
Global Conventions
Display Names
Use
*_namefields (account_name, project_name, task_name, etc.) for display purposes.namefields are deprecated and retained only for backward compatibility.
Status vs Status Category
status= User-facing, customizable label shown in the UI (workspace/project/task). Use for display only.status_category= System-defined, non-editable category. Use for filtering, grouping, and KPIs.
Dates vs Timestamps
Use
*_datefields for analytics and partitioning.*_atfields are deprecated aliases of*_dateand are retained for backward compatibility, but should not be used for new reports.
Narrow / EAV Tables
Task and Project Properties are narrow entity–attribute–value (EAV) tables.
Always de-dup to the latest (
entity_id, property_name) before pivoting.Pivot only the properties needed for your report.
Account
Accounts
Field | Type | Description |
account_id | STRING | Unique account identifier (UUID as text). |
name (deprecated) | STRING | Legacy display name; use account_name. |
account_name | STRING | Preferred display name. |
company_domain | STRING | Primary web/email domain for the account. |
billable_license_limit | INTEGER | Max billable licenses allowed. |
billable_license_rate | NUMERIC | Price per billable license. |
total_member_count | INTEGER | All members in account. |
total_external_member_count | INTEGER | External/guest members. |
total_billable_member_count | INTEGER | Members consuming a license. |
workspace_limit | INTEGER | Max workspaces for account. |
workspace_count | INTEGER | Current workspaces in account. |
created_date | TIMESTAMP | Preferred creation date. |
updated_date | TIMESTAMP | Preferred last update date. |
created_at (deprecated) | TIMESTAMP | Precise legacy creation time. |
updated_at (deprecated) | TIMESTAMP | Precise legacy update time. |
Workspaces
Field | Type | Description |
workspace_id | STRING | Unique workspace ID. |
workspace_name | STRING | Preferred workspace name. |
name (deprecated) | STRING | Legacy workspace name. |
domain | STRING | Internal domain ID. |
company_domain | STRING | Public-facing company domain. |
account_id | STRING | Linked account ID. |
account_name | STRING | Linked account name. |
account_default | INTEGER | 1 if default workspace for account. |
total_member_count | INTEGER | All members in workspace. |
total_external_member_count | INTEGER | External/guest members. |
total_billable_member_count | INTEGER | Members with a license. |
created_date | TIMESTAMP | Preferred creation date. |
updated_date | TIMESTAMP | Preferred last update date. |
created_at (deprecated) | TIMESTAMP | Legacy creation time. |
updated_at (deprecated) | TIMESTAMP | Legacy update time. |
Member
Field | Type | Description |
member_id | STRING | Unique member ID. |
workspace_id | STRING | Linked workspace ID. |
workspace_name | STRING | Linked workspace name. |
primary_workspace | INTEGER | 1 if primary workspace. |
first_name | STRING | First name. |
last_name | STRING | Last name. |
full_name | STRING | Full display name. |
STRING | Email address. | |
role | STRING | Role within workspace. |
status | STRING | Current status (system-defined). |
current_sign_in_at | TIMESTAMP | Last sign-in. |
invited_date | TIMESTAMP | Preferred invite date. |
invitation_accepted_date | TIMESTAMP | Preferred acceptance date. |
deactivated_date | TIMESTAMP | Preferred deactivation date. |
invited_at (deprecated) | TIMESTAMP | Legacy invite time. |
invitation_accepted_at (deprecated) | TIMESTAMP | Legacy acceptance time. |
deactivated_at (deprecated) | TIMESTAMP | Legacy deactivation time. |
failed_login_attempts | INTEGER | Count of failed logins. |
locked_at | TIMESTAMP | Time account was locked. |
Project
Field | Type | Description |
project_id | STRING | Unique project identifier. |
workspace_id | STRING | Workspace containing the project. |
project_name | STRING | Preferred project display name. |
name (deprecated) | STRING | Legacy name; use project_name. |
start_date | DATETIME | Planned/actual start date. |
due_date | DATETIME | Planned due date. |
projected_end_date | DATETIME | Updated forecast end date. |
done_date | TIMESTAMP | Actual completion time. |
status | STRING | UI label, customizable. |
status_category | STRING | System-defined, reliable category. |
customer_id | STRING | Associated customer ID. |
customer_name | STRING | Associated customer name. |
customer_domain | STRING | Customer’s primary domain. |
project_manager_name | STRING | Project manager name. |
project_manager_email | STRING | Project manager email. |
cash_value | INTEGER | Project monetary value. |
allotted_hours | INTEGER | Labor hours allocated. |
internal_project_messages_count | INTEGER | Internal-only messages. |
public_project_messages_count | INTEGER | Public messages. |
created_date | TIMESTAMP | Preferred creation date. |
updated_date | TIMESTAMP | Preferred last update date. |
created_at (deprecated) | TIMESTAMP | Precise legacy creation time. |
updated_at (deprecated) | TIMESTAMP | Precise legacy update time. |
Project Properties (narrow/EAV)
Field | Type | Description |
workspace_id | STRING | Workspace containing project. |
project_id | STRING | Linked project ID. |
legacy_project_id | STRING | Legacy project ID. |
project_property_name | STRING | Preferred property name. |
name (deprecated) | STRING | Legacy property name. |
value | STRING | Raw property value. |
value_type | STRING | Declared data type. |
created_date | TIMESTAMP | Preferred creation date. |
updated_date | TIMESTAMP | Preferred last update date. |
created_at (deprecated) | TIMESTAMP | Legacy creation time. |
updated_at (deprecated) | TIMESTAMP | Legacy update time. |
Project Members
Field | Type | Description |
project_member_id | STRING | Unique project-member ID. |
workspace_id | STRING | Workspace ID. |
project_id | STRING | Linked project ID. |
legacy_project_id | STRING | This is for a potential future of migrating 1.0 to 2.0. Avoid using. Today this is only an alias of project_id |
member_id | STRING | Linked member ID. |
first_name | STRING | Member’s first name. |
last_name | STRING | Member’s last name. |
STRING | Member’s email. | |
workspace_role | STRING | Workspace-level role. |
project_role | STRING | UI label for role. |
project_role_type | STRING | System-defined role category. |
project_view | STRING | Assigned/preferred project view. |
invited_date | TIMESTAMP | Preferred invite date. |
invited_at (deprecated) | TIMESTAMP | Legacy invite time. |
current_sign_in_at | TIMESTAMP | Last sign-in time. |
Tasks
Field | Type | Description |
task_id | STRING | Unique task identifier. |
workspace_id | STRING | Workspace containing task. |
task_name | STRING | Preferred task display name. |
name (deprecated) | STRING | Legacy task name. |
start_date | TIMESTAMP | Planned start date/time. |
due_date | TIMESTAMP | Planned due date/time. |
start_at | TIMESTAMP | Actual precise start time. |
due_at | TIMESTAMP | Actual precise due time. |
duration | INTEGER | Task duration in minutes. |
status | STRING | UI label, customizable. |
status_category | STRING | System-defined category. |
responsibility | STRING | Responsible party type. |
visibility | STRING | Visibility (Public/Internal). |
has_description | INTEGER | 1 if has description. |
assignee_name | STRING | Assigned person’s name. |
assignee_email | STRING | Assigned person’s email. |
created_date | TIMESTAMP | Preferred creation date. |
created_at (deprecated) | TIMESTAMP | Legacy creation time. |
updated_date | TIMESTAMP | Preferred last update date. |
updated_at (deprecated) | TIMESTAMP | Legacy update time. |
completed_date | TIMESTAMP | Preferred completion date. |
completed_at (deprecated) | TIMESTAMP | Legacy completion time. |
estimated_points | INTEGER | Estimated effort points. |
estimated_hours | FLOAT | Estimated effort hours. |
actual_hours | FLOAT | Actual hours worked. |
billable_hours | FLOAT | Billable hours. |
non_billable_hours | FLOAT | Non-billable hours. |
public_asset_count | INTEGER | Public files. |
internal_asset_count | INTEGER | Internal files. |
public_task_messages_count | INTEGER | Public task messages. |
internal_task_messages_count | INTEGER | Internal task messages. |
milestone_id | STRING | Linked milestone ID. |
milestone_name | STRING | Linked milestone name. |
phase_id | STRING | Linked phase ID. |
phase_name | STRING | Linked phase name. |
project_id | STRING | Parent project ID. |
project_name | STRING | Parent project name. |
Task Properties (narrow/EAV)
Field | Type | Description |
workspace_id | STRING | Workspace containing task. |
task_id | STRING | Linked task ID. |
legacy_task_id | STRING | Legacy task ID. |
task_property_name | STRING | Preferred property name. |
name (deprecated) | STRING | Legacy property name. |
value | STRING | Raw property value. |
value_type | STRING | Declared data type. |
created_date | TIMESTAMP | Preferred creation date. |
updated_date | TIMESTAMP | Preferred last update date. |
created_at (deprecated) | TIMESTAMP | Legacy creation time. |
updated_at (deprecated) | TIMESTAMP | Legacy update time. |
Usage: Always de-dup by latest (task_id, task_property_name) before pivoting.