---
# System prepended metadata

title: OpenTeams Schema Documentation

---

# OpenTeams Schema Documentation

## List of Available Tables
* [affiliate](#affiliate)
* [affiliation](#affiliation)
* [allocation](#allocation)
* [client](#client)
* [employee_info](#employee_info)
* [entity](#entity)
* [mapping](#mapping)
* [organization](#organization)
* [person](#person)
* [project](#project)
* [project_task](#project_task)
* [task](#task)
* [time_entry](#time_entry)

### affiliate
<a id="affiliate"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| affiliate_id     | integer     | autogenerated id for the affiliate - used for reference in other tables     | Primary Key     |
| title     | text     | The title of the affiliate     |      |
| entity_id     | integer     | autogenerated id for the entity - used for reference in other tables     | Foreign Key     |

### affiliation
<a id="affiliation"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| affiliation_id     | integer     | autogenerated id for the affiliation     | Primary Key     |
| affiliate_id     | integer     | id from the affiliate table     | Foreign Key     |
| ot_id     | integer     | newly made id in the openteams schema from the mapping process     |      |
| ot_type     | text     | the classification the data falls under (normally name of the table)     |      |

### allocation
<a id="allocation"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| start_date     | date     | date of which the allocation begins     |      |
| user_entity_id     | integer     | the id from the entity table for the user whom the allocation is given to     |      |
| project_id     | integer     | id of the project receiving allocation from the project table     | Foreign Key     |
| allocation     | numeric     | time in hours of allocation to a project     |      |
| end_date     | date     | date of when the alloted hours ends     |      |
| flexible_hours     | boolean     | determines whether the allocation can vary depending on the need     |      |
| allocation_id     | integer     | autogenerated allocation id - used for reference in other tables     | Primary Key     |


### client
<a id="client"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| entity_id     | integer     | autogenerated from the entity table (also the client_id in the project table)     | Foreign Key     |
| is_active     | boolean     | True if the client is active     |      |
| currency     | text     | Type of currency used     |      |

### employee_info
<a id="empolyee_info"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| employee_id     | integer     | autogenerated employee id - used for reference in other tables     | Primary Key     |
| entity_id     | integer     | autogenerated from the entity table     |      |
| start_date     | date     | date on which the employee starts with the company     |      |
| terination_date     | date     | date on which the employee is terminated from the company     |      |
| organization     | text     | the organization the employee works for     |      |
| pay_rate     | numeric     | the amount the employee makes     |      |
| division     | text     | the part of the organization the employee is in     |      |
| is_active     | boolean     | True if the employee is currently active     |      |
| pay_per     | text     | the frquency in which the employee is paid     |      |
| country     | text     | the country the employee resides in     |      |
| currency_code     | text     | the currency the employee uses and is paid in     |      |
| weekly_hours     | integer     | the amount of hours the employee works per week     |      |

### entity
<a id="entity"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| entity_type     | text     | the classification the entity falls under (normally the name of the table)     |      |
| entity_id     | integer     | autogenerated id for the entity used for reference in other tables     | Primary Key     |

### mapping
<a id="mapping"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| mapping_id     | integer     | autogenerated id for the mapping     | Primary Key     |
| ot_id     | integer     | newly made id in the openteams schema     |      |
| source_id     | integer     | original id from the source data     |      |
| source_table     | text     | original table which the data comes from     |      |
| ot_type     | text     | the classification the data falls under (normally name of the table)     |      |

### organization
<a id="organization"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| entity_id     | integer     | id from the entity table     | Foreign Key     |
| name     | text     | name of the organization     |      |

### person
<a id="person"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| entity_id     | integer     | id from the entity table     | Foreign Key     |
| first_name     | text     | first name of the employee     |      |
| last_name     | text     | last name of the emplyees      |      |
| preferred_name     | text     | preferred name of the employee     |      |

### project
<a id="project"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| project_id     | integer     | auto generated id used for reference in other tables     | Primary Key     |
| code     | text     | the projects identification code from harvest     |      |
| is_active     | boolean     | True if the project is active     |      |
| bill_by     | text     | The frequency/date in which the project needs to be billed     |      |
| hourly_rate     | numeric     | the amount the project costs per hour of work     |      |
| is_billable     | boolean     | True if the project hours are billed to the client, False if it is covered internally      |      |
| start_date     | date     | the date on which the project began     |      |
| end_date     | date     | the date on which work for the project has ended     |      |
| name     | text     | the name of the project     |      |
| client_id     | integer     | the entity_id from the client table     |      |

### project_task
<a id="project_task"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| project_id     | integer     | id from the project table     | Foreign Key     |
| task_id     | integer     | autogenerated id from the task table     | Foreign Key     |
| billable     | boolean     | True if the project hours are billed to the client, False if it is covered internally     |      |
| project_task_id     | integer     | autogenerated id for the project task - user for reference in other tables     | Primary Key     |

### task
<a id="task"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| name     | text     | the task's name      |      |
| is_active     | boolean     | True if the task is active     |      |
| task_id     | integer     | autogenerated id for the task - used for reference in other tables     | Primary Key     |

### time_entry
<a id="time_entry"></a>
| FieldName | DataType | Description | Key |
| -------- | ------- | -------- | -------- |
| hourly_rate     | numeric     | the rate at which the employee costs per hour of work     |      |
| project_task_id     | integer     | id from the project task table     | Foreign Key     |
| time_spent     | integer     | the number of hours worked for this entry     |      |
| billable_rate     | numeric     | the rate which is billied to the cient     |      |
| spent_date     | date     | date which the time entry is created for     |      |
| billable     | boolean     | True if the project hours are billed to the client, False if it is covered internally     |      |
| is_closed     | boolean     | True if the entry is not alterable anymore, False if it still can be altered (in Harvest)     |      |
| employee_id     | integer     | id of the employee who the time entry belongs to     | Foreign Key     |
| time_entry_id     | integer     | autogenerated id for the time entry - used for reference in other tables     | Primary Key     |