# 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 |