Plandora Database Model


additional_field
This table contains the values of Meta Felds related to a specific entitty table (task, request, project, risk, etc)
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
planning_id VARCHAR(10) PK NN        
meta_field_id VARCHAR(10) PK NN        
value TEXT   NN        
date_value TIMESTAMP            
numeric_value INTEGER     UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY planning_id
meta_field_id


additional_table
This table contain the values of Meta Field but when the meta field is defined to a grid object. Because of this, this table must contain the fields line and col in order to store information in a "grid" format;
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
planning_id VARCHAR(10) PK NN        
meta_field_id VARCHAR(10) PK NN        
line INTEGER(6) PK NN        
col INTEGER(6) PK NN        
value TEXT   NN        
date_value TIMESTAMP            
IndexName IndexType Columns
PRIMARY PRIMARY planning_id
meta_field_id
line
col


area
This table contains all company areas used by system to categorize an user department area.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
description VARCHAR(100)            
IndexName IndexType Columns
PRIMARY PRIMARY id


artifact
This table is a index of all artifacts that was created into the system related to a specifica entity (task, request, risk, etc) and the artifact content table.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
repository_file_id VARCHAR(10)   NN        
artifact_template_type TEXT            
last_update TIMESTAMP   NN   CURRENT_TIMESTAMP    
project_id VARCHAR(10)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


artifact_template
This table contains the artifacts templates to be optionally used by project members. A template is a piece of content pre-formated that can be used to create for example, a specification, a project document, etc
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10)   NN        
name VARCHAR(70)   NN        
description VARCHAR(255)            
header_html TEXT            
body_html TEXT   NN        
footer_html TEXT            
profile_view VARCHAR(1)            
category_id VARCHAR(10)   NN        


attachment
Contain a lista of all attachment files related to the system entities (tasks, request, projects, etc). Each entity ID will be defined by planning_id and the payload of file (binary data will be stored at binary_file column)
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
planning_id VARCHAR(10)            
name VARCHAR(50)            
visibility VARCHAR(2)   NN        
type VARCHAR(40)   NN        
content_type VARCHAR(255)   NN        
comment TEXT   NN        
binary_file MEDIUMBLOB            
status_ VARCHAR(2)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id
PRIMARY Index id


attachment_history
This table contain the life-cycle history of each attachment file.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
status VARCHAR(2) PK NN        
creation_date TIMESTAMP PK NN   CURRENT_TIMESTAMP    
user_id VARCHAR(10) PK NN        
attachment_id VARCHAR(10) PK NN        
history TEXT            
IndexName IndexType Columns
PRIMARY PRIMARY status
creation_date
user_id
attachment_id


category
This table contains a list of all system categories. A category could be used to give more qualification for requests, tasks, risks, etc of a specific project.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
project_id VARCHAR(10)            
name VARCHAR(50)            
description VARCHAR(100)            
type_ INTEGER(1)         if it is 1 the category is applicable to the requirement, if it is 0=task; 2=Report; 3=KPI; 4=Risk; 5=Workflow; 6=Discussion; 7=Invoice; 9=Cost and 10=Occurrence  
billable INTEGER(1)         define if category is related with a billable task of non-billable task. Applyable only for tasks.  
disable_view INTEGER(1)         if 1, the category will be hidden at GUI  
is_defect INTEGER(1)         define if the category is related to a defect task. Applyable only for tasks.  
is_testing INTEGER(1)         define if the category is related to a Testing Task. Applyable only for tasks.  
is_developing INTEGER(1)         define if the category is related to a development task. Applyable only for tasks.  
category_order INTEGER(2)         define the order of category apperance at at GUI  
IndexName IndexType Columns
PRIMARY PRIMARY id


company
This table contains all companies that will be used to link with the project members.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(25)   NN        
full_name VARCHAR(255)            
company_number VARCHAR(255)            
address VARCHAR(255)            
city VARCHAR(255)            
state_province VARCHAR(50)            
country VARCHAR(100)            
IndexName IndexType Columns
PRIMARY PRIMARY id


cost
This table contains the cost specification for all projects. The cost value will be defined at installment_cost table. Even the expenses (created by project member to refound) are costs. In this case, the field expense_id will be defined with the ID of the expense created.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(30)   NN        
project_id VARCHAR(10)   NN        
category_id VARCHAR(10)   NN        
account_code VARCHAR(30)   NN        
expense_id VARCHAR(10)            
IndexName IndexType Columns
PRIMARY PRIMARY id


cost_history
Each time a cost information is changed, it is possible to restore historically the life-cicle of a project cost.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
cost_id VARCHAR(10) PK NN        
installment_num INTEGER(4) PK NN        
creation_date TIMESTAMP PK NN   0000-00-00 00:00:00    
name VARCHAR(30)   NN        
project_id VARCHAR(10)   NN        
category_id VARCHAR(10)   NN        
account_code VARCHAR(30)            
expense_id VARCHAR(10)            
due_date TIMESTAMP   NN   0000-00-00 00:00:00    
cost_status_id VARCHAR(10)   NN        
value INTEGER(8)   NN        
user_id VARCHAR(10)            
IndexName IndexType Columns
PRIMARY PRIMARY cost_id
installment_num
creation_date


cost_installment
Contain the values of a cost record. Actually, it is possible to divide a cost in 1 or N installments. Each installment could be approved by a project member that have permission to deny or permit the inclusion of this cost into the project balance.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
cost_id VARCHAR(10) PK NN        
installment_num INTEGER(4) PK NN        
due_date TIMESTAMP   NN   CURRENT_TIMESTAMP    
cost_status_id VARCHAR(10)   NN        
approver VARCHAR(10)            
value INTEGER(8)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY cost_id
installment_num


cost_status
List of status of each cost. The state machine order contain a sequence that define the cost life-cycle.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
description VARCHAR(100)            
state_machine_order BIGINT(4)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


custom_form
This table contains a list of IDs of each mata form. Each line of this table represents a record that could be shown and edited into a specific meta form created previously (see table meta_form).
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
meta_form_id VARCHAR(10)   NN        
IndexName IndexType Columns
PRIMARY Index id


custom_node_template
This table contains the match between a workflow node and the information that will be used to create a new task (when the workflow node was instantiated). After task creation, the column related_task_id will contain the ID of the task created.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
node_template_id VARCHAR(10) PK NN        
instance_id VARCHAR(10) PK NN        
template_id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
description TEXT            
planning_id VARCHAR(10)            
project_id VARCHAR(10)            
category_id VARCHAR(10)            
related_task_id VARCHAR(10)            
resource_list TEXT            
question_content TEXT            
is_parent_task INTEGER(11)            
decision_answer TEXT            
iteration VARCHAR(10)            
IndexName IndexType Columns
PRIMARY PRIMARY node_template_id
instance_id
template_id


customer
This table contains all customers of all projects. Each customer ID must be included into the user table, in order to follow the hierarcal architecture where a Customer entity IS A User entity.

Besides, the customer entity contains a number of attributes of the relationship between the specific customer and the project (exemple: enable status, pre approving permission, etc). Due to a Resource and Leader entities are automatically a Customer entity, all the attibutes defined into the customer table will be inherited by Resource and Leader entities.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
project_id VARCHAR(10) PK NN        
id VARCHAR(10) PK NN        
is_disable INTEGER(1)         if it is '1' the current customer is disabled to perform actions for the related project  
is_req_acceptable INTEGER(1)            
can_see_tech_comment INTEGER(1)         if it is '1' the current customer is allowed to view the technical comments that was written by resources into the tasks.  
pre_approve_req INTEGER(1)            
can_see_discussion INTEGER(1)            
function_id VARCHAR(10)            
can_see_other_reqs INTEGER(1)            
can_open_otherowner_reqs INTEGER(1)            
IndexName IndexType Columns
PRIMARY PRIMARY project_id
id


customer_function
This table contains a list of functions of each project resource (project members). Into the system, the members can assume a specific profile into the project ('leader', 'resource' or "customer") but can assume a number of roles that is defined at 'function' table (for example: developer, DBA, project management, quality assurance, etc).

The profile is related with the permission level into the system. The roles are more abstract and are related with the works that the members can do into the project.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
project_id VARCHAR(10) PK NN        
customer_id VARCHAR(10) PK NN        
function_id VARCHAR(10) PK NN        
creation_date TIMESTAMP            
IndexName IndexType Columns
PRIMARY PRIMARY project_id
customer_id
function_id


db_repository_item
Plandora are able to integrate with SVN repository, but also contain a internal repository. This table contains the repository items when the project repository defined but a project is "DB Repository'.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
repository_file_path TEXT   NN        
repository_file_name TEXT   NN        
project_id VARCHAR(10)   NN        
is_directory INTEGER(1)            
parent_id VARCHAR(10)            
IndexName IndexType Columns
PRIMARY PRIMARY id


db_repository_sequence
This table is used to control the increment of each repository item version. For each project that was defined with "Repository DB" this table contains the last version repository item.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id INTEGER(10) PK NN        
project_id VARCHAR(10) PK NN        
IndexName IndexType Columns
PRIMARY PRIMARY id
project_id


db_repository_version
This table contains the payload of the repository file. The "version" column is part of PK. It means that this table can contain a lot of versions for the same repository item. Plandora is not able to optimize the disk space of the file versions. Each version will be stored wholly.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
version INTEGER(10) PK NN        
repository_item_id VARCHAR(10) PK NN        
content_type VARCHAR(40)   NN        
creation_date TIMESTAMP   NN   CURRENT_TIMESTAMP    
comment TEXT            
user_id VARCHAR(10)   NN        
file_size INTEGER(13)   NN        
binary_file MEDIUMBLOB            
IndexName IndexType Columns
PRIMARY PRIMARY version
repository_item_id


decision_node_template
This table contains the workflow decision nodes representation. The administrator user is able to create workflows, and the nodes will be stored at 'decision_node_template' and 'step_node_template'.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
question_content TEXT            
next_node_id_if_false VARCHAR(10)            
IndexName IndexType Columns
PRIMARY PRIMARY id


department
This table contains all departments of all companies. It can be used to categorize an user or used into reports.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)            
description VARCHAR(100)            
IndexName IndexType Columns
PRIMARY PRIMARY id


discussion
This table is related a feature under construction (until version 1.13.0)
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(255)   NN        
owner VARCHAR(10)            
is_blocked INTEGER(1)            
category_id VARCHAR(10)   NN        
project_id VARCHAR(10)            
IndexName IndexType Columns
PRIMARY PRIMARY id


discussion_topic
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
id_2 VARCHAR(10)   NN        
planning_id VARCHAR(10)   NN        
content TEXT            
creation_date TIMESTAMP   NN   CURRENT_TIMESTAMP    
parent_topic VARCHAR(10)            
user_id VARCHAR(10)            
IndexName IndexType Columns
PRIMARY PRIMARY id
planning_disc_topic Index planning_id
PRIMARY Index id


event_log
This table contains the events generated by system. The events are stored into data base and could be used for audit purposes. The Logger Agent saves an event information into this table as well. The event table contains the reference (username) to the user that has included the event but it doesn't has any FK to tool_user table.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
summary VARCHAR(10)   NN        
description TEXT   NN        
creation_date TIMESTAMP   NN   CURRENT_TIMESTAMP    
username VARCHAR(30)   NN        


expense
This table contains all expenses generated by project members. Tipically, the members applies expense values to be refauld by the project budget. This expenses must be aproved by project leaders.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
project_id VARCHAR(10)   NN        
user_id VARCHAR(10)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


function
This table contains all company functions (for HR purposes). It can be used to categorize an user and into reports.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
description VARCHAR(100)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


invoice
This table contains all project invoices. It can be used to input all bills generated by project efford.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
project_id VARCHAR(10)   NN        
category_id VARCHAR(10)   NN        
due_date TIMESTAMP   NN   CURRENT_TIMESTAMP    
invoice_status_id VARCHAR(10)   NN        
invoice_date TIMESTAMP            
invoice_number VARCHAR(40)            
purchase_order VARCHAR(40)            
contact VARCHAR(70)            
IndexName IndexType Columns
PRIMARY PRIMARY id


invoice_history
This table contains the life-cycle of an invoice. Each record contains the information of an invoice in a specific moment.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
creation_date TIMESTAMP PK NN   CURRENT_TIMESTAMP    
invoice_id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
category_id VARCHAR(10)   NN        
invoice_status_id VARCHAR(10)   NN        
due_date TIMESTAMP   NN   0000-00-00 00:00:00    
invoice_date TIMESTAMP   NN        
invoice_number VARCHAR(40)   NN        
purchase_order VARCHAR(40)   NN        
contact VARCHAR(70)   NN        
description TEXT   NN        
total_price BIGINT(20)   NN        
user_id VARCHAR(10)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY creation_date
invoice_id


invoice_item
This table contains the parts of the invoice. Each record contributes with a part of total value (debit or credit).
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
invoice_item_id VARCHAR(10) PK NN        
invoice_id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
type INTEGER(1)   NN     1-Service (credit); 2-Product (credit); 3-Taxes (debit); 4-Discount (debit)  
price BIGINT(20)   NN     value in cents  
amount INTEGER(3)   NN        
type_index INTEGER(2)   NN     "1"=credit; "-1"=debit  
IndexName IndexType Columns
PRIMARY PRIMARY invoice_item_id
invoice_id


invoice_status
This table contains all possible status of invoices. The field 'state_machine_order' defines the order of invoice status at "state machine". The value "1" represents a initial state and the values "100" and "101" represents the final state.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
description VARCHAR(100)   NN        
state_machine_order BIGINT(4)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


leader
This table contain the IDs of all project leaders. Each leader ID should be into the resource table, to respect the architecture hierarchy where a Leader entity IS A Resource entity.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
project_id VARCHAR(10) PK NN        
id VARCHAR(10) PK NN        
IndexName IndexType Columns
PRIMARY PRIMARY project_id
id


meta_field
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
type INTEGER(1)   NN        
apply_to INTEGER(1)   NN        
domain TEXT   NN        
final_date TIMESTAMP   NN        
meta_form_id VARCHAR(10)   NN        
help_content VARCHAR(300)   NN        
IndexName IndexType Columns
PRIMARY Index id


meta_form
This table contain a lista of all meta forms of the system. A meta form is a specific purpose table created by the system administrator (root) and contains a list of meta fields and a list of users of tool that can edit it (see the form Users). It is possible to setup a Javascript snip that will be executed by tool after / before saving a record or after load the form.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(30)   NN        
viewable_cols TEXT            
grid_row_num INTEGER(11)            
filter_col_id VARCHAR(10)            
js_before_save TEXT            
js_after_save TEXT            
js_after_load TEXT            
IndexName IndexType Columns
PRIMARY PRIMARY id


node_template
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
description TEXT   NN        
node_type VARCHAR(2)   NN        
planning_id VARCHAR(10)   NN        
project_id VARCHAR(10)   NN        
next_node_id VARCHAR(10)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


notification
This table contain the notification/alerts agents os system. A notification is an alert that starts in a determined moment and send a customized message for a specific channel.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
description VARCHAR(100)   NN        
notification_class VARCHAR(100)   NN     he class signature (package structure plus class name) that implement the channel of notification (email, http, or some customized implementation)  
sql_text TEXT   NN     the SQL that contain the condition of notiication. In other words, if the SQL return some data from DB means that something should be sent by the notificator.  
retry_number INTEGER(2)   NN     number of times that the notification engine will try to send the alert.  
next_notification TIMESTAMP   NN   CURRENT_TIMESTAMP Date/time provisioned by system to send the next alert.  
final_date TIMESTAMP   NN     If null, the current alert is active. If the alert is disabled, this field contain the date/time of disabling action.  
last_check TIMESTAMP   NN   0000-00-00 00:00:00    
period_minute INTEGER(3)   NN     The minute that the notification should starts.  
period_hour INTEGER(3)   NN     The hour that the notification should starts.  
periodicity INTEGER(2)   NN     The periodicity of alert: 1-Yearly; 2-Monthly; 3-Weekly; 4-Daily; 5-Eventually  
IndexName IndexType Columns
PRIMARY PRIMARY id


notification_field
This table contain the fields used by the notification agent in order to provide the parameters used for notification engine. For example: the native email notification of tool knows how to send an e-mail but it need some information about that SMPT server, user, etc, that changes in each case. Thus, the records of this tables will be "translated" in customized fields of the notification GUI.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
notification_id VARCHAR(10) PK NN        
name VARCHAR(100) PK NN        
value VARCHAR(100)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY notification_id
name


occurrence
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
source VARCHAR(100)   NN        
name VARCHAR(255)   NN        
visible VARCHAR(1)   NN        
loc VARCHAR(7)   NN        
occurrence_status VARCHAR(10)   NN        
occurrence_status_label VARCHAR(60)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


occurrence_dependency
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
occurrence_id VARCHAR(10) PK NN        
planning_id VARCHAR(10) PK NN        
IndexName IndexType Columns
PRIMARY PRIMARY occurrence_id
planning_id
occur_depend_planning Index planning_id


occurrence_field
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
field VARCHAR(10) PK NN        
occurrence_id VARCHAR(10) PK NN        
value TEXT   NN        
date_value TIMESTAMP   NN        
IndexName IndexType Columns
PRIMARY PRIMARY field
occurrence_id


occurrence_history
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
occurrence_id VARCHAR(10) PK NN        
occurrence_status VARCHAR(10) PK NN        
creation_date TIMESTAMP PK NN   CURRENT_TIMESTAMP    
user_id VARCHAR(10) PK NN        
occurrence_status_label VARCHAR(60)   NN        
history TEXT   NN        
IndexName IndexType Columns
PRIMARY PRIMARY occurrence_id
occurrence_status
creation_date
user_id


occurrence_kpi
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
report_id VARCHAR(10) PK NN        
occurrence_id VARCHAR(10) PK NN        
creation_date TIMESTAMP   NN        
weight INTEGER(1)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY report_id
occurrence_id
occur_kpi_occur Index occurrence_id


p_sequence
This table contain the sequence used by system to increment the ids of the most of tables. The architecture of system was desing to avoid using features of specific data bases. A "sequence" feature and "auto-increment ID" feature are available in many commercial data bases, but each data base has a specific rule to create and increment the sequence. Then, Plandora controls the "ID sequence" storing the next ID available in this table.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id BIGINT(9) PK NN     Unique Sequential ID  
IndexName IndexType Columns
PRIMARY PRIMARY id


plan_relation
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
planning_id VARCHAR(10) PK NN        
plan_related_id VARCHAR(10) PK NN        
plan_type VARCHAR(2)   NN        
plan_related_type VARCHAR(2)   NN        
relation_type VARCHAR(2)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY planning_id
plan_related_id


planning
This entity is an abstraction of the main planning entities of system, i.e, Task, Requeriment , Risks, Occurrences, Invoices, Costs, Project, etc.
This "super-entity" contain the common fields of all planning entities.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
description TEXT   NN        
creation_date TIMESTAMP   NN   CURRENT_TIMESTAMP    
final_date TIMESTAMP   NN        
iteration VARCHAR(10)   NN        
rich_text_desc TEXT   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


preference
This table contain the system preferences of each user.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(50) PK NN        
user_id VARCHAR(10) PK NN        
value TEXT   NN        
IndexName IndexType Columns
PRIMARY Index id
user_id


project
This table contain all projects of system.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(30)   NN        
parent_id VARCHAR(10)   NN        
can_alloc VARCHAR(1)   NN        
project_status_id VARCHAR(10)   NN        
repository_url VARCHAR(200)   NN        
repository_class VARCHAR(200)   NN        
repository_user VARCHAR(40)   NN        
repository_pass VARCHAR(40)   NN        
estimated_closure_date TIMESTAMP   NN        
allow_billable INTEGER(1)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


project_history
This table contain the life-cycle of a project. Each state transition of project is stored here.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
project_id VARCHAR(10) PK NN        
project_status_id VARCHAR(10) PK NN        
creation_date TIMESTAMP PK NN   CURRENT_TIMESTAMP    
IndexName IndexType Columns
PRIMARY PRIMARY project_id
project_status_id
creation_date


project_report
This table contain a list of projects where a KPI is applied. The KPI generation engine could consider a list of projects for the same indicator and generating different values (report_result) in the same time.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
project_id VARCHAR(10) PK NN       AI
report_id VARCHAR(10) PK NN        
IndexName IndexType Columns
PRIMARY PRIMARY project_id
report_id


project_status
This table contain all status of project. Each status is related to a numeric constant used into the system to define the status. This constant is following: 1 - Project Opened ; 2 - Project on-Hold ; 3 - Project Aborted/Closed, etc
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
note VARCHAR(100)   NN        
state_machine_order BIGINT(4)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


question_alternative
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
survey_id VARCHAR(10) PK NN        
question_id VARCHAR(10) PK NN        
sequence INTEGER(11) PK NN        
content TEXT   NN        
IndexName IndexType Columns
PRIMARY PRIMARY survey_id
question_id
sequence


question_answer
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
survey_id VARCHAR(10) PK NN        
question_id VARCHAR(10) PK NN        
answer_date TIMESTAMP PK NN   CURRENT_TIMESTAMP    
user_id VARCHAR(10)   NN        
value TEXT   NN        
IndexName IndexType Columns
PRIMARY PRIMARY survey_id
question_id
answer_date


report
This table contain all Report or KPI records of system. Each Report or KPI contain a SQL statement. A Report uses the SQL statement to populate data into the Jasper report layout. In the other hand the system timer uses the SQL statement from a KPI record to generate an indicator value from data base and set the new value into the report_result table.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
category_id VARCHAR(10)   NN        
report_id VARCHAR(10)   NN     Project id related with the metric  
name VARCHAR(100)   NN        
type INTEGER(2)   NN     Metric Type (1=daily, etc)  
report_perspective_id VARCHAR(10)   NN     If it is a KPI, define the BSC perspective, otherwise is NULL  
sql_text TEXT   NN     SQL statement that implement the metric business rule.  
execution_hour INTEGER(2)   NN     Execution time of metric (hour that timer should wake up to run the SQL)  
last_execution TIMESTAMP   NN     Last execution date/time  
final_date TIMESTAMP   NN     Final date of metric. If NULL the metric is alive, otherwise, contain the date/time of exclusion.  
data_type INTEGER(2)   NN     Type of metric data (1=date, 0=float, etc)  
file_name VARCHAR(100)   NN     If the current record is a KPI this field is null, otherwise, contain the path of Jasper file used by report engine.  
profile_view VARCHAR(1)   NN        
goal VARCHAR(25)   NN        
tolerance VARCHAR(25)   NN        
tolerance_type VARCHAR(2)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


report_result
his table contain the values (metrics) generated by timer when it wake up and run a KPI's SQL clausule.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
last_execution TIMESTAMP PK NN   0000-00-00 00:00:00    
project_id VARCHAR(10) PK NN        
report_id VARCHAR(10) PK NN        
value VARCHAR(25)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY last_execution
project_id
report_id


repository_file
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
repository_file_path TEXT   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


repository_file_plan
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
repository_file_id VARCHAR(10) PK NN        
planning_id VARCHAR(10) PK NN        
IndexName IndexType Columns
PRIMARY PRIMARY repository_file_id
planning_id
rep_file_plan_plan Index planning_id


repository_file_project
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
repository_file_id VARCHAR(10) PK NN        
project_id VARCHAR(10) PK NN        
is_disable INTEGER(1)   NN        
last_update TIMESTAMP   NN   CURRENT_TIMESTAMP    
is_indexable INTEGER(1)            
is_downloadable INTEGER(1)     UNSIGNED      
IndexName IndexType Columns
PRIMARY PRIMARY repository_file_id
project_id


repository_policy
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
type_policy VARCHAR(30) PK NN        
project_id VARCHAR(10) PK NN        
value TEXT   NN        
IndexName IndexType Columns
PRIMARY PRIMARY type_policy
project_id


requeriment
This table contain all requests of system (bugs, support requests, system requirement, etc).
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
category_id VARCHAR(10)   NN        
project_id VARCHAR(10)   NN        
suggested_date TIMESTAMP   NN        
deadline_date TIMESTAMP   NN        
requeriment_status_id VARCHAR(10)   NN        
priority INTEGER(1)   NN        
is_acceptance INTEGER(1)   NN        
reopening BIGINT(6)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


requeriment_history
Each request has the own life cycle, and this table contain the information about the historical occurrence of the all requests.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
creation_date TIMESTAMP PK NN   CURRENT_TIMESTAMP    
requeriment_id INTEGER PK NN UNSIGNED      
requeriment_status_id VARCHAR(10) PK NN        
user_id VARCHAR(10)   NN        
comment TEXT   NN        
iteration VARCHAR(10)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY creation_date
requeriment_id
requeriment_status_id


requeriment_status
This table contain a list of all status of the requirement. Each status is related to a numeric constant used into the system to define the status. This constant is following: 1 - Waiting Approve ; 100 - Planned ; 200 - Canceled ; 201 - Closed ; 202 - Refused ; 300 - In-Progress
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
description VARCHAR(100)   NN        
state_machine_order BIGINT(4)   NN        
accept_project_id VARCHAR(10)   NN        
parent_id VARCHAR(10)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


resource
This table contain all resources of all project. Each resource ID should be into the customer table, to respect the architecture hierarchy where a Resource entity IS A Customer entity.
Besides, the resource entity contain a number of attributes of the relationship between the specific resource and the project (example: cost, capacity, etc). Due to a Leader entity is automatically a Resource entity, all this attibutes defines into the resource table will be inherited by Leader entity.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
project_id VARCHAR(10) PK NN        
id VARCHAR(10) PK NN        
can_self_alloc INTEGER(1)   NN     If it is '1' the resource could set tasks for him self  
cost_per_hour DECIMAL(9,2)   NN        
can_see_customer INTEGER(1)   NN     If is is '1' the resource could view into the "resource task" form the name of the customer that made the request.  
capacity_per_day DECIMAL(9,2)   NN        
can_see_repository INTEGER(1)   NN        
can_see_invoice INTEGER(1)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY project_id
id


resource_capacity
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
resource_id VARCHAR(10) PK NN        
project_id VARCHAR(10) PK NN        
cap_year INTEGER(4) PK NN        
cap_month INTEGER(2) PK NN        
cap_day INTEGER(2) PK NN        
capacity INTEGER(4)   NN        
cost_per_hour INTEGER(10)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY resource_id
project_id
cap_year
cap_month
cap_day


resource_task
This table contain the allocation of resource for a specific task. Contain the general information about the status, task date and duration. However, there are another table (resource_task_alloct) that contain the time values day by day. For example, if a resource_task contain a task that should be completed in 12 hours, then, there are two related records in resource_task_alloc with value = 8 and 4.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
task_id VARCHAR(10) PK NN        
project_id VARCHAR(10) PK NN        
id VARCHAR(10)   NN        
start_date TIMESTAMP   NN   CURRENT_TIMESTAMP    
estimated_time BIGINT(6)   NN        
actual_date TIMESTAMP   NN        
actual_time BIGINT(6)   NN        
task_status_id VARCHAR(10)   NN        
is_acceptance_task INTEGER(1)   NN        
billable INTEGER(1)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY task_id
project_id


resource_task_alloc
This table contain the allocation of human resources for a specific task. An allocation is a quantity of minutes used by resource to work in the task.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
task_id VARCHAR(10) PK NN        
project_id VARCHAR(10) PK NN        
sequence BIGINT(6) PK NN        
alloc_time BIGINT(6)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY task_id
project_id
sequence


risk
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
category_id VARCHAR(10)   NN        
name VARCHAR(50)   NN        
probability VARCHAR(2)   NN        
impact VARCHAR(2)   NN        
tendency VARCHAR(2)   NN        
responsible VARCHAR(40)   NN        
risk_status_id VARCHAR(10)   NN        
strategy TEXT   NN        
contingency TEXT   NN        
impact_cost VARCHAR(1)   NN        
impact_time VARCHAR(1)   NN        
impact_quality VARCHAR(1)   NN        
impact_scope VARCHAR(1)   NN        
risk_type VARCHAR(1)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


risk_history
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
risk_id VARCHAR(10) PK NN        
risk_status_id VARCHAR(10) PK NN        
creation_date TIMESTAMP PK NN   CURRENT_TIMESTAMP    
user_id VARCHAR(10) PK NN        
risk_status_label VARCHAR(60)   NN        
history TEXT   NN        
probability VARCHAR(2)   NN        
impact VARCHAR(2)   NN        
tendency VARCHAR(2)   NN        
impact_cost VARCHAR(1)   NN        
impact_time VARCHAR(1)   NN        
impact_quality VARCHAR(1)   NN        
impact_scope VARCHAR(1)   NN        
risk_type VARCHAR(1)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY risk_id
risk_status_id
creation_date
user_id


risk_status
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
description VARCHAR(100)   NN        
status_type VARCHAR(1)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


root
This table contain only one record. It is the ID of root user and is always "3" and it is alwayed related to the project "0". The project "0" it is the root project.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
project_id VARCHAR(10) PK NN        
id VARCHAR(10) PK NN        
IndexName IndexType Columns
PRIMARY PRIMARY project_id
id


step_node_template
This table contains the workflow task nodes representation. The administrator user is able to create workflows, and the nodes will be stored at 'decision_node_template' and 'step_node_template'.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
category_id VARCHAR(10)   NN        
resource_list TEXT            
category_regex VARCHAR(255)            
iteration VARCHAR(10)            
IndexName IndexType Columns
PRIMARY PRIMARY id


survey
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
description TEXT   NN        
is_template VARCHAR(1)   NN        
is_anonymous VARCHAR(1)   NN        
creation_date TIMESTAMP   NN   CURRENT_TIMESTAMP    
final_date TIMESTAMP   NN        
date_publishing TIMESTAMP   NN   0000-00-00 00:00:00    
anonymous_key VARCHAR(70)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


survey_question
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
survey_id VARCHAR(10) PK NN        
id VARCHAR(10) PK NN        
type VARCHAR(1)   NN        
content TEXT   NN        
position INTEGER(11)   NN        
sub_title VARCHAR(50)   NN        
is_mandatory VARCHAR(1)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY survey_id
id


task
This table contain a list of all tasks of system. A task represent a work event performed by the resources of project.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
requeriment_id VARCHAR(10)   NN        
task_id VARCHAR(10)   NN     Parent Task ID. The parent task is used to join a group of tasks.  
is_parent_task INTEGER(1)   NN     If it is 0 the current task is a common task, if it is 1 the task is a joinner task (used to join a group of tasks)  
created_by VARCHAR(10)   NN        
is_unpredictable INTEGER(1)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id
PRIMARY Index id


task_history
Each task has the own life cycle, and this table contain the information about the historical occurrence of the task.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
task_id VARCHAR(10) PK NN        
resource_id VARCHAR(10) PK NN        
project_id VARCHAR(10) PK NN        
task_status_id VARCHAR(10) PK NN        
creation_date TIMESTAMP PK NN   CURRENT_TIMESTAMP    
id VARCHAR(10)   NN        
start_date TIMESTAMP   NN   0000-00-00 00:00:00    
estimated_time BIGINT(6)   NN        
actual_date TIMESTAMP   NN        
actual_time BIGINT(6)   NN        
comment TEXT   NN        
iteration VARCHAR(10)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY task_id
resource_id
project_id
task_status_id
creation_date


task_status
This table contain all status of task. Each status is related to a numeric constant used into the system to define the status. This constant is following: 1 - Task Open ; 100 - Task Closed ; 101 - Task Canceled ; 20 - Task In ProgressI ; 50 - Task on-Hold
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
description VARCHAR(100)   NN        
state_machine_order BIGINT(4)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


template
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
name VARCHAR(50)   NN        
deprecated_date TIMESTAMP   NN        
root_node_id VARCHAR(10)   NN        
category_id VARCHAR(10)   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id


tool_user
This table contain all users of system, independently of project or role.
ColumnName DataType PrimaryKey   NotNull   Flags Default Value Comment AutoInc
id VARCHAR(10) PK NN        
username VARCHAR(30)   NN        
password VARCHAR(70)   NN        
name VARCHAR(50)   NN        
email VARCHAR(70)   NN        
phone VARCHAR(20)   NN        
color VARCHAR(10)   NN     Representative color of user (used by Gantt Chart bars to identify a task assigned to user). It is a RGB hexadecimal pattern (ex: FF0000 = red, C2C2C2= gray, etc)  
area_id VARCHAR(10)   NN        
department_id VARCHAR(10)   NN        
function_id VARCHAR(10)   NN        
company_id VARCHAR(10)            
country VARCHAR(2)   NN        
language VARCHAR(2)   NN        
auth_mode VARCHAR(200)   NN        
birth DATE   NN        
permission TEXT   NN        
pic_file MEDIUMBLOB   NN        
final_date TIMESTAMP   NN        
creation_date TIMESTAMP   NN        
IndexName IndexType Columns
PRIMARY PRIMARY id