# Kantata schema ERD

Table and field reference for the Fivetran Kantata connector.

## Table & Field Reference

### story_allocation_day

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| assignment_id | Long | FK |
| story_id | Long | FK |
| created_at | Instant |  |
| current | Boolean |  |
| date | LocalDate |  |
| minutes | Int |  |
| updated_at | Instant |  |

### role

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| created_at | Instant |  |
| deleted_at | Instant |  |
| name | String |  |
| updated_at | Instant |  |

### assignment

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| assignee_id | Long | FK |
| story_id | Long | FK |
| allocated_minutes | Int |  |
| created_at | Instant |  |
| current | Boolean |  |
| estimated_minutes | Int |  |
| resource_id | Long |  |
| updated_at | Instant |  |

### workspace_invoice_preference

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| workspace_id | Long | PK, FK |
| client_invoice_address | String |  |
| client_invoice_name | String |  |
| consultant_invoice_address | String |  |
| consultant_invoice_name | String |  |
| project_code | String |  |
| purchase_order | String |  |

### time_off_entry

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| user_id | Long | FK |
| hours | Float |  |
| requested_date | LocalDate |  |
| submission_date | LocalDate |  |

### holiday

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| end_date | LocalDate |  |
| name | String |  |
| paid | Boolean |  |
| start_date | LocalDate |  |
| total_hours | Int |  |

### invoice_time_entry

| Column | Type | Keys |
| --- | --- | --- |
| invoice_id | Long | PK, FK |
| time_entry_id | Long | PK, FK |

### account_membership

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| default_role_id | Long | FK |
| manager_id | Long | FK |
| user_id | Long | FK |
| bill_rate_in_cents | Long |  |
| billability_target | Long |  |
| can_create_workspace | Boolean |  |
| can_view_reports | Boolean |  |
| cost_rate_in_cents | Long |  |
| created_at | Instant |  |
| default_read_only | Boolean |  |
| disabled_at | Instant |  |
| is_administrator | Boolean |  |
| is_backup_approver | Boolean |  |
| is_owner | Boolean |  |
| is_project_lead | Boolean |  |
| is_punch_clock_user | Boolean |  |
| manager_can_approve | Boolean |  |
| permission | String |  |
| should_show_alert_on_timesheet_submission | Boolean |  |
| updated_at | Instant |  |

### rate_card_version_history

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| updated_at | Instant | PK |
| rate_card_id | Long | FK |
| created_at | Instant |  |
| default_rate | Int |  |

### skill_membership

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| creator_id | Long | FK |
| skill_id | Long | FK |
| user_id | Long | FK |
| created_at | Instant |  |
| level | Int |  |
| updated_at | Instant |  |

### account_location

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| archived | Boolean |  |
| in_use | Boolean |  |
| name | String |  |

### workweek

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| created_at | Instant |  |
| default | Boolean |  |
| end_date | LocalDate |  |
| friday_minutes | Int |  |
| monday_minutes | Int |  |
| saturday_minutes | Int |  |
| start_date | LocalDate |  |
| sunday_minutes | Int |  |
| thursday_minutes | Int |  |
| total_minutes | Int |  |
| tuesday_minutes | Int |  |
| updated_at | Instant |  |
| wednesday_minutes | Int |  |

### estimate_scenario

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| estimate_id | Long | FK |
| rate_card_id | Long | FK |
| budget_in_cents | Long |  |
| created_at | Instant |  |
| name | String |  |
| start_date | LocalDate |  |
| target_margin_percent | Int |  |
| updated_at | Instant |  |

### skill

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| skill_category_id | Long | FK |
| created_at | Instant |  |
| description | String |  |
| max_level | String |  |
| name | String |  |
| updated_at | Instant |  |

### participation

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| workspace_id | Long | PK, FK |
| user_id | Long | FK |
| active_role_name | String |  |
| allocated_workspace_minutes | Long |  |
| can_invite | Boolean |  |
| cost_in_cents | Long |  |
| created_at | Instant |  |
| default_rate_in_cents | Long |  |
| estimated_workspace_minutes | Long |  |
| is_team_lead | Boolean |  |
| permissions | String |  |
| rate_in_cents | Long |  |
| role | String |  |
| show_as_read_only | Boolean |  |
| updated_at | Instant |  |

### workspace_resource

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| participation_id | Long | FK |
| role_id | Long | FK |
| user_id | Long | FK |
| workspace_id | Long | FK |
| created_at | Instant |  |
| display_label | String |  |
| full_name | String |  |
| label | String |  |
| photo_path | String |  |
| role_name | String |  |
| updated_at | Instant |  |

### cost_rate

| Column | Type | Keys |
| --- | --- | --- |
| currency | String | PK |
| account_membership_id | Long | PK, FK |
| amount_in_subunits | Long |  |
| created_at | Instant |  |
| in_use | Boolean |  |
| updated_at | Instant |  |

### rate_card_set_history

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| updated_at | Instant | PK |
| account_default | Boolean |  |
| active_currencies | Json |  |
| created_at | Instant |  |
| default_currencies | Json |  |
| destroyable | Boolean |  |
| title | String |  |

### rate_card_role_history

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| updated_at | Instant | PK |
| rate_card_version_id | Long | FK |
| role_id | Long | FK |
| created_at | Instant |  |
| rate | Int |  |

### user

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| role_id | Long | FK |
| account_id | Long |  |
| email_address | String |  |
| full_name | String |  |
| headline | String |  |
| photo_path | String |  |
| custom_** |  |  |

### expense

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| expense_category_id | Long | FK |
| story_id | Long | FK |
| user_id | Long | FK |
| workspace_id | Long | FK |
| amount_in_cents | Long |  |
| category | String |  |
| created_at | Instant |  |
| currency | String |  |
| currency_base_unit | Int |  |
| currency_symbol | String |  |
| date | LocalDate |  |
| is_billable | Boolean |  |
| is_invoiced | Boolean |  |
| notes | String |  |
| taxable | Boolean |  |
| updated_at | Instant |  |
| user_can_edit | Boolean |  |

### workspace_association

| Column | Type | Keys |
| --- | --- | --- |
| workspace_group_id | Long | PK, FK |
| workspace_id | Long | PK, FK |

### billable_utilizations

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| account_membership_id | Long | FK |
| creator_id | Long | FK |
| created_at | Instant |  |
| effective_date | LocalDate |  |
| target | Int |  |
| updated_at | Instant |  |

### workspace

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| account_color_id | Long | FK |
| creator_id | Long | FK |
| primary_workspace_group_id | Long | FK |
| rate_card_id | Long | FK |
| access_level | String |  |
| account_feature_has_time_entry_role_picker | Boolean |  |
| account_feature_time_trackable | Boolean |  |
| archived | Boolean |  |
| budget_used_in_cents | Long |  |
| budgeted | Boolean |  |
| can_create_line_items | Boolean |  |
| can_invite | Boolean |  |
| change_orders_enabled | Boolean |  |
| client_role_name | String |  |
| consultant_role_name | String |  |
| created_at | Instant |  |
| currency | String |  |
| currency_base_unit | Int |  |
| currency_symbol | String |  |
| default_rate | BigDecimal |  |
| description | String |  |
| due_date | LocalDate |  |
| effective_due_date | LocalDate |  |
| exclude_archived_stories_percent_complete | Boolean |  |
| expenses_in_burn_rate | Boolean |  |
| has_active_expense_report_submissions | Boolean |  |
| has_active_timesheet_submissions | Boolean |  |
| has_budget_access | Boolean |  |
| over_budget | Boolean |  |
| percentage_complete | Int |  |
| posts_require_privacy_decision | Boolean |  |
| price_in_cents | Long |  |
| primary_counterpart_id | Int |  |
| provider_lead_name | String |  |
| require_expense_approvals | Boolean |  |
| require_time_approvals | Boolean |  |
| start_date | LocalDate |  |
| status_color | String |  |
| status_key | Int |  |
| status_message | String |  |
| stories_are_fixed_fee_by_default | Boolean |  |
| target_margin | Int |  |
| tasks_default_non_billable | Boolean |  |
| title | String |  |
| total_expenses_in_cents | Long |  |
| updated_at | Instant |  |
| custom_** |  |  |
| permission_** |  |  |

### timesheet_submission

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| user_id | Long | FK |
| workspace_id | Long | FK |
| comment | String |  |
| created_at | Instant |  |
| currency | String |  |
| currency_base_unit | Int |  |
| currency_symbol | String |  |
| current_resolution_created_at_date | Instant |  |
| current_resolution_creator_id | Int |  |
| current_resolution_description | String |  |
| end_date | LocalDate |  |
| line_item_total_formatted | String |  |
| line_item_total_in_cents | Long |  |
| resolution_description | String |  |
| start_date | LocalDate |  |
| status | String |  |
| title | String |  |
| type | String |  |
| updated_at | Instant |  |
| permission_** |  |  |

### story_state_change

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| story_id | Long | FK |
| user_id | Long | FK |
| created_at | Instant |  |
| formatted_state | String |  |
| state | String |  |
| updated_at | Instant |  |

### story

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| creator_id | Long | FK |
| parent_id | Long | FK |
| root_id | Long | FK |
| workspace_id | Long | FK |
| archived | Boolean |  |
| assignment_timestamped_at | Instant |  |
| billable | Boolean |  |
| budget_estimate_in_cents | Long |  |
| budget_used_in_cents | Long |  |
| created_at | Instant |  |
| deleted_at | Instant |  |
| description | String |  |
| due_date | LocalDate |  |
| fixed_fee | Boolean |  |
| has_proofing_access | Boolean |  |
| invoiced_balance_in_cents | Long |  |
| logged_billable_time_in_minutes | Int |  |
| logged_nonbillable_time_in_minutes | Int |  |
| percentage_complete | Int |  |
| position | Int |  |
| priority | String |  |
| start_date | LocalDate |  |
| state | String |  |
| status | String |  |
| story_type | String |  |
| sub_stories_billable_time_in_minutes | Int |  |
| sub_stories_budget_estimate_in_cents | Long |  |
| sub_stories_budget_used_in_cents | Long |  |
| sub_stories_time_estimate_in_minutes | Int |  |
| sub_story_count | Int |  |
| subtree_depth | Int |  |
| time_estimate_in_minutes | Int |  |
| time_trackable | Boolean |  |
| title | String |  |
| uninvoiced_balance_in_cents | Long |  |
| updated_at | Instant |  |
| weight | Int |  |
| custom_** |  |  |

### invoice_expense

| Column | Type | Keys |
| --- | --- | --- |
| expense_id | Long | PK, FK |
| invoice_id | Long | PK, FK |

### estimate

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| creator_id | Long | FK |
| workspace_group_id | Long | FK |
| created_at | Instant |  |
| currency | String |  |
| is_locked | Boolean |  |
| name | String |  |
| updated_at | Instant |  |

### timesheet_submission_time_entry

| Column | Type | Keys |
| --- | --- | --- |
| time_entry_id | Long | PK, FK |
| timesheet_submission_id | Long | PK, FK |

### workspace_user

| Column | Type | Keys |
| --- | --- | --- |
| user_id | Long | PK, FK |
| workspace_id | Long | PK, FK |

### invoice_fixed_fee_item

| Column | Type | Keys |
| --- | --- | --- |
| fixed_fee_item_id | Long | PK, FK |
| invoice_id | Long | PK, FK |

### fixed_fee_item

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| story_id | Long | FK |
| workspace_id | Long | FK |
| amount_in_cents | Long |  |
| currency | String |  |
| notes | String |  |
| taxable | Boolean |  |

### invoice_workspace

| Column | Type | Keys |
| --- | --- | --- |
| invoice_id | Long | PK, FK |
| workspace_id | Long | PK, FK |

### workspace_status_change

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| from_color | String | FK |
| to_color | String | FK |
| user_id | Long | FK |
| workspace_id | Long | FK |
| created_at | Instant |  |
| from_message | String |  |
| from_status_key | String |  |
| to_message | String |  |
| to_status_key | String |  |
| updated_at | Instant |  |

### holiday_calendar_membership

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| holiday_calendar_id | Long | FK |
| user_id | Long | FK |
| created_at | Instant |  |
| end_date | LocalDate |  |
| in_use | Boolean |  |
| start_date | LocalDate |  |
| updated_at | Instant |  |

### holiday_calendar

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| default | Boolean |  |
| name | String |  |

### time_entry

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| location_id | Long | FK |
| story_id | Long | FK |
| user_id | Long | FK |
| workspace_id | Long | FK |
| active_submission_id | Long |  |
| approved | Boolean |  |
| billable | Boolean |  |
| cost_rate_in_cents | Long |  |
| created_at | Instant |  |
| currency | String |  |
| currency_base_unit | Int |  |
| currency_symbol | String |  |
| date_performed | LocalDate |  |
| is_invoiced | Boolean |  |
| notes | String |  |
| rate_in_cents | Long |  |
| taxable | Boolean |  |
| time_in_minutes | Int |  |
| updated_at | Instant |  |
| user_can_edit | Boolean |  |

### account_color

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| created_at | Instant |  |
| default_color | Boolean |  |
| enabled | Boolean |  |
| hex | String |  |
| name | String |  |
| updated_at | Instant |  |

### estimate_scenario_resource

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| estimate_scenario_id | Long | PK, FK |
| role_id | Long | FK |
| user_id | Long | FK |
| bill_rate | Long |  |
| cost_rate | Long |  |
| created_at | Instant |  |
| geography_id | Long |  |
| label | String |  |
| updated_at | Instant |  |

### invoice

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| recipient_id | Long | FK |
| user_id | Long | FK |
| balance_in_cents | Long |  |
| created_at | Instant |  |
| currency | String |  |
| currency_base_unit | Int |  |
| currency_symbol | String |  |
| draft | Boolean |  |
| due_date | LocalDate |  |
| invoice_date | LocalDate |  |
| message | String |  |
| payment_schedule | Int |  |
| project_code | String |  |
| purchase_order | String |  |
| status | String |  |
| tax_rate | Float |  |
| title | String |  |
| updated_at | Instant |  |
| user_invoice_number | Long |  |
| user_invoice_title | String |  |

### rate_card_history

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| updated_at | Instant | PK |
| rate_card_set_id | Long | FK |
| created_at | Instant |  |
| currency | String |  |
| uses | Int |  |

### status_reports

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| creator_id | Long | FK |
| updater_id | Long | FK |
| workspace_id | Long | FK |
| color | String |  |
| created_at | Instant |  |
| description | String |  |
| updated_at | Instant |  |

### post

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| story_id | Long | FK |
| subject_id | Long | FK |
| user_id | Long | FK |
| workspace_id | Long | FK |
| created_at | Instant |  |
| formatted_message | String |  |
| has_attachment | Boolean |  |
| has_attachments | Boolean |  |
| message | String |  |
| newest_reply_at | Instant |  |
| private | Boolean |  |
| reply | Boolean |  |
| reply_count | Long |  |
| subject_title | String |  |
| subject_type | String |  |
| updated_at | Instant |  |
| workspace_type | String |  |

### workweek_membership

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| user_id | Long | FK |
| workweek_id | Long | FK |
| created_at | Instant |  |
| end_date | LocalDate |  |
| start_date | LocalDate |  |
| updated_at | Instant |  |

### account_invitation

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| default_role_id | Long | FK |
| invitee_id | Long | FK |
| inviter_id | Long | FK |
| bill_rate_in_cents | Long |  |
| cost_rate_in_cents | Long |  |
| created_at | Instant |  |
| email_address | String |  |
| full_name | String |  |
| pending | Boolean |  |
| permission | String |  |

### checklist

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| story_id | Long | PK, FK |
| completed_by_id | Long | FK |
| completed | Boolean |  |
| completed_at | Instant |  |
| created_at | Instant |  |
| name | String |  |
| position | Int |  |
| updated_at | Instant |  |

### expense_category

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| deleted_at | Instant |  |
| name | String |  |

### holiday_calendar_association

| Column | Type | Keys |
| --- | --- | --- |
| holiday_calendar_id | Long | PK, FK |
| holiday_id | Long | PK, FK |

### workspace_allocation

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| workspace_resource_id | Long | FK |
| created_at | Instant |  |
| end_date | LocalDate |  |
| hard | Boolean |  |
| minutes | Int |  |
| notes | String |  |
| percentage | Float |  |
| start_date | LocalDate |  |
| updated_at | Instant |  |

### workspace_group

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| address | String |  |
| company | Boolean |  |
| contact_name | String |  |
| created_at | Instant |  |
| email | String |  |
| name | String |  |
| notes | String |  |
| phone_number | String |  |
| updated_at | Instant |  |
| website | String |  |
| custom_** |  |  |

### skill_category

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| name | String |  |

## Relationships

- story_allocation_day(assignment_id) → assignment(id)
- story_allocation_day(story_id) → story(id)
- assignment(story_id) → story(id)
- assignment(assignee_id) → user(id)
- workspace_invoice_preference(workspace_id) → workspace(id)
- time_off_entry(user_id) → user(id)
- invoice_time_entry(invoice_id) → invoice(id)
- invoice_time_entry(time_entry_id) → time_entry(id)
- account_membership(user_id) → user(id)
- account_membership(manager_id) → user(id)
- account_membership(default_role_id) → role(id)
- rate_card_version_history(rate_card_id) → rate_card_history(id)
- skill_membership(skill_id) → skill(id)
- skill_membership(creator_id) → user(id)
- skill_membership(user_id) → user(id)
- estimate_scenario(estimate_id) → estimate(id)
- estimate_scenario(rate_card_id) → rate_card_history(id)
- skill(skill_category_id) → skill_category(id)
- participation(workspace_id) → workspace(id)
- participation(user_id) → user(id)
- workspace_resource(workspace_id) → workspace(id)
- workspace_resource(user_id) → user(id)
- workspace_resource(role_id) → role(id)
- workspace_resource(participation_id) → participation(id)
- cost_rate(account_membership_id) → account_membership(id)
- rate_card_role_history(role_id) → role(id)
- rate_card_role_history(rate_card_version_id) → rate_card_version_history(id)
- user(role_id) → role(id)
- expense(workspace_id) → workspace(id)
- expense(user_id) → user(id)
- expense(story_id) → story(id)
- expense(expense_category_id) → expense_category(id)
- workspace_association(workspace_group_id) → workspace_group(id)
- workspace_association(workspace_id) → workspace(id)
- billable_utilizations(account_membership_id) → account_membership(id)
- billable_utilizations(creator_id) → user(id)
- workspace(creator_id) → user(id)
- workspace(primary_workspace_group_id) → workspace_group(id)
- workspace(rate_card_id) → rate_card_history(id)
- workspace(account_color_id) → account_color(id)
- timesheet_submission(workspace_id) → workspace(id)
- timesheet_submission(user_id) → user(id)
- story_state_change(user_id) → user(id)
- story_state_change(story_id) → story(id)
- story(workspace_id) → workspace(id)
- story(creator_id) → user(id)
- story(parent_id) → story(id)
- story(root_id) → story(id)
- invoice_expense(invoice_id) → invoice(id)
- invoice_expense(expense_id) → expense(id)
- estimate(creator_id) → user(id)
- estimate(workspace_group_id) → workspace_group(id)
- timesheet_submission_time_entry(timesheet_submission_id) → timesheet_submission(id)
- timesheet_submission_time_entry(time_entry_id) → time_entry(id)
- workspace_user(workspace_id) → workspace(id)
- workspace_user(user_id) → user(id)
- invoice_fixed_fee_item(invoice_id) → invoice(id)
- invoice_fixed_fee_item(fixed_fee_item_id) → fixed_fee_item(id)
- fixed_fee_item(story_id) → story(id)
- fixed_fee_item(workspace_id) → workspace(id)
- invoice_workspace(invoice_id) → invoice(id)
- invoice_workspace(workspace_id) → workspace(id)
- workspace_status_change(user_id) → user(id)
- workspace_status_change(workspace_id) → workspace(id)
- workspace_status_change(from_color) → account_color(id)
- workspace_status_change(to_color) → account_color(id)
- holiday_calendar_membership(user_id) → user(id)
- holiday_calendar_membership(holiday_calendar_id) → holiday_calendar(id)
- time_entry(location_id) → account_location(id)
- time_entry(story_id) → story(id)
- time_entry(workspace_id) → workspace(id)
- time_entry(user_id) → user(id)
- estimate_scenario_resource(estimate_scenario_id) → estimate_scenario(id)
- estimate_scenario_resource(role_id) → role(id)
- estimate_scenario_resource(user_id) → user(id)
- invoice(user_id) → user(id)
- invoice(recipient_id) → user(id)
- rate_card_history(rate_card_set_id) → rate_card_set_history(id)
- status_reports(creator_id) → user(id)
- status_reports(updater_id) → user(id)
- status_reports(workspace_id) → workspace(id)
- post(story_id) → story(id)
- post(workspace_id) → workspace(id)
- post(user_id) → user(id)
- post(subject_id) → post(id)
- workweek_membership(workweek_id) → workweek(id)
- workweek_membership(user_id) → user(id)
- account_invitation(default_role_id) → role(id)
- account_invitation(inviter_id) → user(id)
- account_invitation(invitee_id) → user(id)
- checklist(story_id) → story(id)
- checklist(completed_by_id) → user(id)
- holiday_calendar_association(holiday_id) → holiday(id)
- holiday_calendar_association(holiday_calendar_id) → holiday_calendar(id)
- workspace_allocation(workspace_resource_id) → workspace_resource(id)