This article applies to Aiwyn Practice.
Use this reference to find where fields from the Projects screens are stored in the external data warehouse. It is intended for anyone building reports against project, task or project-billing data.
Screens: Projects → All Projects → Create New Project (Details, Tasks and Billing tabs)
Tables: job, job_service_line, task, task_section, billing_instructions, fee_structure_v4
Details tab: basic information
| UI element | Table | Column | Notes |
|---|---|---|---|
| Template | job |
job_template_id |
The chosen template is recorded on the created project. |
| Client | job |
client_id |
|
| Project Name | job |
name, name_template
|
The typed text is saved as name; the template it was generated from is kept in name_template. |
|
Service Line |
job → job_service_line → service_line
|
id → job_id, service_line_id → name
|
Read-only. Filled automatically from the chosen template and written as one row per service line, which is why a project can carry more than one. |
| Compare to prior project | job |
overridden_previous_job_id |
Selecting the checkbox reveals a project picker; the selected project is stored here. |
Details tab: client-facing information
| UI element | Table | Column | Notes |
|---|---|---|---|
| Project Description (optional) | job |
service_description |
Appears on engagement letters and invoices. When blank, the template name is used instead. |
| Additional Details (optional) | job |
extended_service_description |
Rich-text summary shown under the engagement's list of services. |
Details tab: project team and dates
| UI element | Table | Column | Notes |
|---|---|---|---|
| Partner | job |
partner_staff_id |
|
| Manager | job |
manager_staff_id |
|
| Target Start Date | job |
target_start_date |
|
| Target Due Date | job |
target_end_date |
The field reads Due, but the column is target_end_date. |
Tasks tab
| UI element | Table | Column | Notes |
|---|---|---|---|
| Stage name, for example Stage 1 | task_section |
name |
Rendered as a section header above task rows, not as a task table column. |
| Task | task |
override_name |
There is no name column; a query joining on one returns nothing. |
| Work Code | task |
task_type_id |
A work code, despite the column name. A time entry inherits this value when the task is selected on the timesheet. |
| Assignee | task |
assignee_staff_id |
Always an individual person, never a staff role. |
| Budget (Hrs) | task |
estimated_hours |
|
| Target Start Date | task |
target_start_date |
|
| Target Due Date | task |
target_due_date |
Note the column is target_due_date here, compared with target_end_date on job. |
Billing tab: pricing structure, Fixed Fee
The Billing tab stays disabled until a Client and Template are selected on the Details tab.
| UI element | Table | Column | Notes |
|---|---|---|---|
|
Fixed Fee/ Hourly |
billing_instructions |
wip_allocation_strategy |
FIXED for Fixed Fee; REALIZATION for Hourly. |
|
Total Price |
billing_instructions → fee_structure_v4
|
fee_structure_id → amount
|
Stored on the linked fee_structure_v4 row. |
|
Pricing Notes |
billing_instructions → fee_structure_v4
|
fee_structure_id → pricing_notes
|
|
|
Manual/ Recurring/ Custom Schedule |
billing_instructions |
billing_schedule_id |
Foreign key to a billing_schedule row. That table is not currently in the warehouse and must be added before its values can be reported. |
Billing tab: pricing structure, Hourly
| UI element | Table | Column | Notes |
|---|---|---|---|
|
Minimum Price |
billing_instructions → fee_structure_v4
|
fee_structure_id → estimate_min
|
The same estimate_min column written by the Custom Fees dialog. |
|
Maximum Price |
billing_instructions → fee_structure_v4
|
fee_structure_id → estimate_max
|
Billing tab: WIP allocation and notes
| UI element | Table | Column | Notes |
|---|---|---|---|
|
WIP allocation |
billing_instructions |
include_wip, include_wip_if_final
|
Three options—each invoice, final invoice only or do not allocate—set this pair of booleans. Shown only for Fixed Fee with a Recurring or Custom schedule. |
| Notes |
billing_instructions → fee_structure_v4
|
fee_structure_id → notes
|
Distinct from Pricing Notes; fee_structure_v4 carries both notes and pricing_notes. |
Not in the external data warehouse
| UI element | Screen | Why |
|---|---|---|
| Recurring | Details tab | Marked Coming soon and disabled; nothing is stored. |
| First invoice | Billing tab, Recurring |
billing_instructions.billing_schedule_id points to the schedule, but the schedule itself is not exposed. |
| Frequency | Billing tab, Recurring | Part of the billing schedule, which is not exposed. |
| Installments | Billing tab, Recurring | Part of the billing schedule. For Fixed Fee it is derived from the number of scheduled payments rather than stored. |
| Per-payment bill date and amount rows, Add Payment | Billing tab, Custom Schedule | Individual scheduled payments are part of the billing schedule, which is not exposed. |
Related
- Data warehouse field reference: Settings
- Data warehouse field reference: Timesheet
- Data warehouse field mapping:
automatic_payment_plan / scheduled_payment - Billing settings reference
- Projects screen reference
- Project, task and request status reference