|
Note - Every table includes four system fields: – 'tenant_id', 'partition_date', and 'partition_hour' are for internal use and can be ignored – 'system_start_time' reflects the last_modified timestamp for each record |
Contents:
Other Resources:
- Getting Started with Data Hub
- How to View a Visual Relational Schema of Data Hub
- How to Set Up Scheduled Refreshes for Data Hub
BigTime Tables
client | |
| Description | Represents the customer organizations that a BigTime user quotes, bills, or delivers services to. |
| Key Fields | client_sid, client_name, client_code, email, postal_code, address_line_1 |
| Relationships | Linked to project, invoice, client_contact, and time_entry_submitted via client_sid. |
| Usage Notes | Use for revenue tracking, project grouping, and client-specific reporting. |
client_contact | |
| Description | Represents named individuals associated with a client account. Commonly used for billing, project, or administrative communications. |
| Key Fields | client_contact_id, client_sid, first_name, last_name, client_contact_type, email |
| Relationships | Linked to the client table via client_sid (1 client → many contacts). |
| Usage Notes | Use this table to determine who to contact for billing, approvals, or project updates. Contact type is user-defined and may vary by tenant. |
cost_center_holiday | |
| Description | Stores holiday dates specific to each cost center, allowing teams in different regions to account for local public holidays in planning and reporting. |
| Key Fields | holiday_sid, cost_center_id, date, holiday_name |
| Relationships | Linked to a cost center via cost_center_id. Each tenant can define unique holidays. |
| Usage Notes | Use in capacity/utilization reporting to normalize week-to-week trends and account for expected dips in work hours during local holidays. |
expense | |
| Description | Contains individual expense entries submitted by staffers. Each record includes the expense details, metadata, and billing attributes. |
| Key Fields | expense_sid, staffer_sid, project_sid, transaction_date, amount, category, is_billable |
| Relationships | Linked to staffer, project, and expense_report_approval via expense_sid. |
| Usage Notes | Use to track and report on expense submissions, regardless of payment source. |
expense_report_approval | |
| Description | Captures each approval action taken on submitted expense reports, including multi-level workflows defined by the tenant. |
| Key Fields | expense_report_sid, expense_sid, approver_staffer_sid, review_status_id, approval_date, approval_level_sid, approver_name |
| Relationships | Links to expense and staffer via expense_sid and approver_staffer_sid. |
| Usage Notes | Use to track approval flow, identify approvers, and audit timing of multi-level review processes. |
invoice | |
| Description | Stores high-level billing information for a client or project, representing the header record for each invoice issued. |
| Key Fields | invoice_sid, client_sid, project_sid, invoice_number, invoice_period_start_date, invoice_period_end_date, amount, status, due_date |
| Relationships | Links to client and project via respective SIDs. Line-level details reside in invoice_line_item. |
| Usage Notes | Use for revenue and AR reporting. Combine with payments and line items for full invoice lifecycle tracking. |
invoice_line_item | |
| Description | Contains itemized entries that make up the total on an invoice. Each record may represent billed time, expenses, or manually added fixed-fee items. |
| Key Fields | invoice_line_item_sid, invoice_sid, invoice_line_item_name, rate, amount, invoice_line_item_type, invoice_line_item_category |
| Relationships | Linked to the invoice table via invoice_sid. |
| Usage Notes | Use for detailed invoice reporting and reconciliation against time/expense records. |
payment | |
| Description | Stores payments received from clients and their allocations to specific invoices. |
| Key Fields | payment_id, invoice_sid, payment_amount, applied_amount, payment_date |
| Relationships | Links to invoice via invoice_sid. |
| Usage Notes | Use for cash flow and accounts receivable reporting. |
project | |
| Description | Represents the top-level definition of a customer-facing or internal project. Includes status, timing, billing setup, and cost center tagging. |
| Key Fields | project_sid, client_sid, project_name, start_date, end_date, is_billable, project_status, billing_status |
| Relationships | Linked to client via client_sid. Child records include project_task, time_entry_submitted, invoice, and project_team. |
| Usage Notes | Use this table to roll up billing, time, and task data at the project level. |
project_custom_bill_rate | |
| Description | Stores overrides to standard billing rates for specific projects. These rates may apply to staffers, roles, labor categories, or combinations thereof. |
| Key Fields | custom_rate_sid, project_sid, staffer_sid, category_sid, rate_value |
| Relationships | Links to project, staffer, and rate category references. |
| Usage Notes | Used to support non-standard pricing agreements or rate changes on a per-project basis. |
project_custom_cost_rate | |
| Description | Overrides default cost rates for staffers on specific projects, used for internal margin calculations. |
| Key Fields | custom_rate_sid, project_sid, staffer_sid, rate, effective_date |
| Relationships | Connects to project and staffer via respective SIDs. |
| Usage Notes | Use for cost forecasting or post-project profitability analysis. |
project_custom_expense_rate | |
| Description | Defines project-specific overrides for expense billing and markup percentages. |
| Key Fields | custom_rate_sid, project_sid, cost_rate, bill_rate, markup_percentage |
| Relationships | Joins with project and expense categories. |
| Usage Notes | Use to analyze expense pass-through and margin at the project level. |
project_task | |
| Description | Represents individual tasks that together define the scope of a project. Tasks may be billable or non-billable and can carry financial targets, time budgets, and scheduling data. |
| Key Fields | project_task_id, project_sid, task_name, start_date, end_date, estimated_hours, is_billable, percent_complete |
| Relationships | Linked to project, and extended by project_task_allocation and project_task_assignment to manage resourcing and planning. |
| Usage Notes | Use to break down work by deliverable or phase. |
project_task_allocation | |
| Description | Represents the allocation of hours to staffers for specific project tasks over defined time periods. This table is central to resource planning, enabling firms to forecast workload, manage capacity, and align staffing with project timelines. |
| Key Fields | allocation_id, project_sid, project_task_id, staffer_sid, period_start_date, period_end_date, estimated_hours, weekly_allocations_json, monthly_allocations_json |
| Relationships | Linked to project via project_sid, project_task via project_task_id, and staffer via staffer_sid. |
| Usage Notes | Use for weekly or monthly allocation planning, capacity forecasting, and staffing visualization. Adjust allocations as project scopes evolve to maintain optimal resource utilization. |
project_task_assignment | |
| Description | Represents direct assignments of staffers to project tasks. |
| Key Fields | project_task_sid, project_sid, staff_sid |
| Relationships | Connects staffers to tasks within a project. |
| Usage Notes | Use to confirm who is responsible for completing each task. |
project_team | |
| Description | Maps staffers to projects, including role, team, and leadership status. |
| Key Fields | project_sid, staffer_sid, team_id, team_name, role_name, is_team_lead |
| Relationships | Connects to project and staffer via their SIDs. |
| Usage Notes | Use to identify who is staffed on each project, their role, and team structure. |
role_skill | |
| Description | Defines the skills associated with specific roles within the organization. |
| Key Fields | role_skill_id, role_sid, skill_sid, role_name, skill_name |
| Relationships | Join table linking role definitions to skill definitions. |
| Usage Notes | Use to benchmark staff against role requirements or plan for upskilling. |
staffer | |
| Description | Represents individual users on your team who can track time, submit expenses, and be assigned to client work. |
| Key Fields | staffer_sid, first_name, last_name, email, monthly_capacity, default_role, cost_rate, bill_rate_a through bill_rate_e |
| Relationships | Connects to project_team, project_task_assignment, time_entry_submitted, and expense. |
| Usage Notes | Core to all planning, tracking, and billing. Use to manage utilization, margin, and role-based resourcing. |
staffer_department | |
| Description | Associates staffers with internal departments or cost centers. |
| Key Fields | staffer_sid, organization_id, department |
| Relationships | Connects to staffer via staffer_sid. |
| Usage Notes | Use for reporting, cost analysis, or permission grouping. |
staffer_skill | |
| Description | Tracks skills or certifications that individual staffers have. |
| Key Fields | staff_skill_sid, staffer_sid, skill_sid, skill_rating, skill_added_at, skill_expires_at, is_active |
| Relationships | Connects to staffer and skill definitions. |
| Usage Notes | Use to identify staff qualifications and match them to project needs. |
staffer_team | |
| Description | Maps individual staffers to internal teams for reporting or planning purposes. |
| Key Fields | staffer_sid, team_sid, team_name |
| Relationships | Connects staffers to reusable team definitions. |
| Usage Notes | Use to group and report on teams or pods within your org. |
time_entry_submitted | |
| Description | Tracks individual time entries that have been submitted for approval. Includes billing details, project/task linkage, and approval state. |
| Key Fields | time_entry_submitted_sid, staffer_sid, project_sid, task_sid, date, hours_input, hours_billable, approval_status, hourly_bill_rate |
| Relationships | Links to staffer, project, task, and optionally invoice. Related approvals live in time_entry_submitted_approval. |
| Usage Notes | Core to time tracking workflows. Enables timesheet, billability, and utilization analysis. |
time_entry_submitted_approval | |
| Description | Records each approval action on submitted time entries. Supports multi-level approval chains with timestamps and approver identity. |
| Key Fields | time_entry_submitted_sid, approver_staffer_sid, approval_date, approval_level_sid, approval_status |
| Relationships | Joins to time_entry_submitted via time_entry_submitted_sid, and to staffer via approver_staffer_sid. |
| Usage Notes | Use for audit trails and compliance workflows. Enables reporting on review timing and bottlenecks. |
time_entry_unsubmitted | |
| Description | Captures time entries that have been saved by staffers but not yet submitted for approval. |
| Key Fields | time_entry_unsubmitted_sid, staffer_sid, project_sid, task_sid, date, hours_input, is_billable |
| Relationships | Mirrors time_entry_submitted structure, linking to staffer, project, and task. |
| Usage Notes | Useful for draft timesheet monitoring or identifying users with incomplete time reporting. |
vendor | |
| Description | Contains metadata about external vendors or subcontractors used in your delivery. |
| Key Fields | vendor_sid, vendor_name, status, postal_code |
| Relationships | Used in expense reports, vendor bills, and reporting. |
| Usage Notes | Use to analyze vendor usage and financial obligations. |
vendor_bill | |
| Description | Tracks bills from vendors related to projects or operational expenses. |
| Key Fields | vendor_bill_id, vendor_sid, amount, date, status |
| Relationships | Links to vendor table via vendor_sid. |
| Usage Notes | Use for managing vendor payments, accounts payable, and reimbursements. |
Resource Management Tables
staffer_seniority | |||
Staffers in company, including data available in Resource Management: note, seniority. | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
staffer_sid |
sid |
Sid of staffer |
|
note |
text |
Staffers text note |
— |
seniority_uuid |
uuid |
One-to-Many relationship between staffer and seniority with seniority level, null=True |
seniority |
time_off_manager | |||
Time off managers assigned to staffers, join table (many-to-many), staffer can have multiple time off managers and vice versa. | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
staffer_sid |
sid |
Sid of staffer (One-to-Many relationship), null=False |
staffer |
timeoff_manager_sid |
sid |
Sid of manager (One-to-Many relationship) (staffer_sid), null=False |
staffer |
finance_manager | |||
Finance managers assigned to staffers, join table (many-to-many), staffer can have multiple time off managers and vice versa. | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
staffer_sid |
sid |
Sid of staffer (One-to-Many), null=False |
staffer |
finance_manager_sid |
sid |
Sif of manager (One-to-Many) (staffer_sid), null-False |
staffer |
staffer_certificate | |||
Certificates assigned to staffers, with certificate details such as issuer, url, issue_date and a FK to certificate (organization object). Staffer can have multiple certificate records assigned. Join table serving many-to-many relationship between staffers and certificates with details. | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
staffer_sid |
sid |
Sid of staffer (One-to-Many), null=False |
staffer |
certificate_uuid |
uuid |
Certificate UUID (One-to-Many), null=False |
certificate |
issuer |
varchar(512) |
Issuer name |
— |
url |
varchar(512) |
Certificate url |
— |
issue_date |
date |
Issue date |
— |
staffer_skill_resource_management | |||
Skills with skill level from 0 to 4 assigned to staffers. Staffers can have multiple skills assigned. Join table (many-to-many). | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
staffer_sid |
sid |
Sid of staffer, null=False |
staffer |
skill_uuid |
uuid |
Skill UUID, null=False |
skill |
level |
int [0, 4] |
Skill level (int value from 0 to 4) |
— |
staffer_skill | |||
Skill details | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
skill_uuid |
uuid |
Skill UUID |
— |
path |
text |
Text with all skill parents in form: |
— |
name |
varchar(200) |
Skill name |
— |
parent_uuid |
uuid |
Paren skill UUID (One-to-many), root skills - Technical skills, Languages, Soft skills don’t have parent_uuid assigned |
skill |
seniority | |||
Seniority table with name and level | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
seniority_uuid |
uuid |
Seniority UUID |
|
name |
text |
Seniority name |
— |
level |
int |
Seniority level, greater than 0 |
seniority |
experience | |||
Staffer can have multiple experiences. Position, project, industry, region and detail are text fields provided by the user. | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
experience_uuid |
uuid |
Experience UUID |
— |
staffer_sid |
sid |
Sid of staffer (one-to-Many relationship), null=False |
staffer |
position |
varchar(200) |
Position associated with experience |
— |
project |
text |
Project name or description |
— |
industry |
text |
Industry name or description |
— |
region |
text |
Region |
— |
start_date |
date |
Start date |
— |
end_date |
date |
End date |
— |
details |
text |
details |
— |
experience_skill | |||
Join table linking experiences and skills, experience can have multiple skills assigned | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
experience_uuid |
uuid |
(One-to-Many, null=False) |
experience |
skill_uuid |
uuid |
(One-to-Many, null=False) |
skill |
demand | |||
Demand table with all details and related objects | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
demand_uuid |
uuid |
Demand UUID |
— |
project_sid |
sid |
Sid of project |
— |
seniority_uuid |
uuid |
Seniority UUID (One-to-Many) |
seniority |
role_sid |
sid |
Sid of role (One-to-Many) |
— |
demand_minutes |
int |
Total minutes scheduled |
— |
cost_rate |
numeric(20,2) |
Demand cost rate |
— |
bill_rate |
numeric(20,4) |
Demand bill rate |
— |
status |
Int [0, 1, 2] |
Status always set to 2 (status active) |
— |
start_date |
Timestamp with time zone |
Start date |
— |
end_date |
Timestamp with time zone |
End date |
— |
billable |
boolean |
Billable set to True by default |
— |
details |
text |
Demand note |
— |
cost_center_1_sid |
sid |
One-to-many, null=True |
— |
cost_center_2_sid |
sid |
One-to-many, null=True |
— |
cost_center_3_sid |
sid |
One-to-many, null=True |
— |
department_sid |
sid |
One-to-many, null=True |
— |
project_task_sid |
sid |
One-to-many, null=True |
— |
demand_skill | |||
Demand can have multiple skills assigned | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
demand_uuid |
uuid |
Demand UUID one-to-many |
demand |
skill_uuid |
uuid |
Skill UUID one-to-many |
skill |
level |
Int [0, 4] |
Skill level (from 0 to 4) |
— |
demand_daily_data | |||
Daily demand data | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
demand_uuid |
uuid |
Demand UUID |
demand |
date |
date |
date |
— |
scheduled_minutes |
int |
Scheduled minutes for date |
— |
cost_rate |
numeric(20,6) |
Cost rate |
— |
bill_rate |
numeric(20,6) |
Bill rate |
— |
demand_custom_property | |||
Custom attributes assigned to demand, only one value field will be filled | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
demand_uuid |
uuid |
Demand UUID |
demand |
name |
varchar(256) |
Custom attribute name |
— |
slug_name |
varchar(256) |
Slugified CA name |
— |
datatype |
varchar |
Possible values: |
— |
value_serialized |
text |
Custom attribute value dependant on datatype casted to text |
— |
value_integer |
int |
|
— |
value_decimal |
numeric(10,4) |
|
— |
value_text |
text |
|
— |
value_date |
date |
|
— |
value_time |
Time without time zone |
|
— |
value_datetime |
Timestamp with time zone |
|
— |
value_bool |
boolean |
|
— |
value_url |
varchar(1024) |
|
— |
value_manager |
sid |
Sid of staffer, null=True |
staffer |
certificate | |||
Certificates table | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
certificate_uuid |
uuid |
Certificate UUID |
— |
text |
text |
Certificate name |
— |
demand_certificate | |||
Certificates assigned to demand, join table, demand can have multiple certificates assigned | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
certificate_uuid |
uuid |
Certificate UUID |
certificate |
demand_uuid |
uuid |
Demand UUID |
demand |
assignment | |||
Assignments table | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
assignment_uuid |
uuid |
Assignment UUID |
— |
staffer_sid |
sid |
Sid of staffer (one-to-many, null=False) |
staffer |
project_sid |
sid |
Sid of project |
— |
role_sid |
sid |
Role of assigned staffer |
— |
fulfillment_of_uuid |
uuid |
Demand UUID (one-to-many, null=True) if assignment is connected to demand |
demand |
scheduled_minutes |
int |
Total minutes scheduled |
— |
scheduled_cost_rate |
numeric(20,2) |
Scheduled cost rate |
— |
scheduled_bill_rate |
numeric(20,4) |
Scheduled bill rate |
— |
status |
int |
0 - Draft, 1 - Reserved, 2 - Active |
— |
label |
varchar(200) |
Assignment label |
— |
start_date |
Timestamp with time zone |
Start date |
— |
end_date |
Timestamp with time zone |
End date |
— |
billable |
boolean |
Is assignment billable |
— |
details |
text |
Assignment text details |
— |
assignment_time_entry | |||
Assignment time entries | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
assignment_uuid |
uuid |
Assignment UUID |
assignment |
date |
date |
Date |
— |
actual_minutes |
int |
Tracked minutes for date |
— |
billable |
bool |
Is time entry billable |
— |
time_entry_submitted_sid |
sid |
Relation to a submitted time entry |
time_entry_submitted |
time_entry_unsubmitted_sid |
sid |
Relation to an unsubmitted time entry |
time_entry_unsubmitted |
assignment_daily_data | |||
Assignment daily data | |||
Key Fields |
Key Field Type |
Key Field Description |
Related to Foreign Table |
assignment_uuid |
uuid |
Assignment UUID |
assignment |
date |
date |
date |
— |
scheduled_minutes |
int |
Minutes scheduled for date |
— |
scheduled_cost_rate |
numeric(20,6) |
Scheduled cost rate |
— |
scheduled_bill_rate |
numeric(20,6) |
Scheduled bill rate |
— |