Phlogiston/Data Model

Vocabulary
Category: A grouping of tasks within Phlogiston. Categories are defined by _recategorization.csv.

Phlogiston Project: A set of tasks that are analyzed as a group, under the assumption that they are the same body of tasks that one team of people work on. A Phlogiston project usually contains all tasks from multiple Phabricator projects.

Phabricator Project: A tag for grouping and labeling tasks in Phabricator.

Source: synonym to Phlogiston Project.

Status: The Phabricator status field. Simplified by generic_make_history.sql.

category
Each row is one category for one Phlogiston scope.

maniphest_edge
One row for each edge (relationship) between a task and a project. Notably not partitioned by scope.

maniphest_task
One row for each task. If a task belongs to multiple Phlogiston scopes, it will appear multiple times (I think).

maniphest_transaction
One row for each transaction in Phabricator.

open_backlog_size
Possibly no longer in use and deleteable.

phab_parent_category_edge
One row for

phabricator_column
One row for each projectcolumn.

phabricator_project
One row for each Phabricator project.

recently_closed
One row per ?. Statistics on recently closed tasks, in the aggregate.

recently_closed_task
One row per recently closed task per scope.

task_on_date
One row for each task for each day. Partitioned by scope.

task_on_date_recategorized
Like task_on_date, but after recategorization.

task_on_date_agg
Like task_on_date, but aggregated to one row for each unique group of date, category, status, and scope.

velocity
One row for each category and each of the last weeks, months, and quarters, going back typically 2 quarters I think, partitioned by scope. Holds all of the velocity, history, and forecasting data.

calculate_velocities(scope_prefix)
Calculate historical velocities; calculate retrocasts and forecasts for past and future, generate all data necessary for charting.

convert_blocked_phid_to_id_sql
Insert a row into maniphest_blocked for each row in maniphest_blocked_phid, converting phid to id in the process.

find_descendents(root_id, run_date)
Returns a list ids of all tasks that are "children" (aka Blockers in Phabricator), as of the specified date.

Limitation: if Task A is a parent of Task B, and Task B is a parent of Task C, and Task A and Task C are tagged with Phabricator projects that are included in Phlogiston Project X, but Task B is not, Task C will not be included in the list.

get_backlog(scope_prefix, status_input, cutoff_range, show_hidden)
Return a scope's backlog aggregate data, based on specified parameters, combining aggregate data and category info.

populate_recently_closed_task(scope_prefix)
[incomplete list]

Data Model during Phlogiston execution
The following tables are updated in this order:

Load

 * 1) phabricator_project
 * 2) phabricator_column
 * 3) maniphest_task
 * 4) maniphest_blocked_phid
 * 5) maniphest_transaction
 * 6) maniphest_blocked

Reconstruct

 * 1) maniphest_edge (add project membership many to many table)
 * 2) task_history (add one row per task per day)
 * 3) task_milestone (add task ancestor milestone many to many table)
 * 4) task_history (update with milestone strings)
 * 5) task_history (update status for special cases involving bad import data)

Report

 * 1) task_history_recat (add one row per task per day with consolidated category)
 * 2) category_list
 * 3) tall_backlog (add grouped data)
 * 4) other tables...