Introduced org_id in tt_monthly_quotas table.

[timetracker.git] / mysql.sql

diff --git a/mysql.sql b/mysql.sql
index a4fa84c84d7b308eb5221cf7fac88e1e480c2569..3652c5288d620c696a4355a7661a0b1090f0e063 100644 (file)
--- a/mysql.sql
+++ b/mysql.sql
@@ -3,57 +3,65 @@
 # 2) Then, execute this script from command prompt with a command like this:
 # mysql -h host -u user -p -D db_name < mysql.sql
 
 # 2) Then, execute this script from command prompt with a command like this:
 # mysql -h host -u user -p -D db_name < mysql.sql
 

-# create database timetracker character set = 'utf8';
+# create database timetracker character set = 'utf8mb4';

 
 # use timetracker;
 
 
 #
 
 # use timetracker;
 
 
 #

-# Structure for table tt_teams. A team is a group of users for whom we are tracking work time.
-# This table stores settings common to all team members such as language, week start day, etc.
-#
-CREATE TABLE `tt_teams` (
-  `id` int(11) NOT NULL auto_increment,                      # team id
-  `timestamp` timestamp default CURRENT_TIMESTAMP,           # modification timestamp
-  `name` varchar(80) default NULL,                           # team name
-  `currency` varchar(7) default NULL,                        # team currency symbol
-  `decimal_mark` char(1) NOT NULL default '.',               # separator in decimals
-  `lang` varchar(10) NOT NULL default 'en',                  # language
-  `date_format` varchar(20) NOT NULL default '%Y-%m-%d',     # date format
-  `time_format` varchar(20) NOT NULL default '%H:%M',        # time format
-  `week_start` smallint(2) NOT NULL default 0,               # Week start day, 0 == Sunday.
-  `tracking_mode` smallint(2) NOT NULL default 1,            # tracking mode ("time", "projects" or "projects and tasks")
-  `project_required` smallint(2) NOT NULL default 0,         # whether a project selection is required or optional
-  `task_required` smallint(2) NOT NULL default 0,            # whether a task selection is required or optional
-  `record_type` smallint(2) NOT NULL default 0,              # time record type ("start and finish", "duration", or both)
-  `bcc_email` varchar(100) default NULL,                     # bcc email to copy all reports to
-  `plugins` varchar(255) default NULL,                       # a list of enabled plugins for team
-  `lock_spec` varchar(255) default NULL,                     # Cron specification for record locking,
-                                                             # for example: "0 10 * * 1" for "weekly on Mon at 10:00".
-  `workday_minutes` smallint(4) default 480,                 # number of work minutes in a regular working day
-  `custom_logo` tinyint(4) default 0,                        # whether to use a custom logo or not
-  `config` text default NULL,                                # miscellaneous team configuration settings
-  `status` tinyint(4) default 1,                             # team status
+# Structure for table tt_groups. A group is a unit of users for whom we are tracking work time.
+# This table stores settings common to all group members such as language, week start day, etc.
+#
+CREATE TABLE `tt_groups` (
+  `id` int(11) NOT NULL auto_increment,                  # group id
+  `parent_id` int(11) default NULL,                      # parent group id
+  `org_id` int(11) default NULL,                         # organization id (id of top group)
+  `name` varchar(80) default NULL,                       # group name
+  `currency` varchar(7) default NULL,                    # currency symbol
+  `decimal_mark` char(1) NOT NULL default '.',           # separator in decimals
+  `lang` varchar(10) NOT NULL default 'en',              # language
+  `date_format` varchar(20) NOT NULL default '%Y-%m-%d', # date format
+  `time_format` varchar(20) NOT NULL default '%H:%M',    # time format
+  `week_start` smallint(2) NOT NULL default 0,           # Week start day, 0 == Sunday.
+  `tracking_mode` smallint(2) NOT NULL default 1,        # tracking mode ("time", "projects" or "projects and tasks")
+  `project_required` smallint(2) NOT NULL default 0,     # whether a project selection is required or optional
+  `task_required` smallint(2) NOT NULL default 0,        # whether a task selection is required or optional
+  `record_type` smallint(2) NOT NULL default 0,          # time record type ("start and finish", "duration", or both)
+  `bcc_email` varchar(100) default NULL,                 # bcc email to copy all reports to
+  `allow_ip` varchar(255) default NULL,                  # specification from where users are allowed access
+  `password_complexity` varchar(64) default NULL,        # password example that defines required complexity
+  `plugins` varchar(255) default NULL,                   # a list of enabled plugins for group
+  `lock_spec` varchar(255) default NULL,                 # Cron specification for record locking,
+                                                         # for example: "0 10 * * 1" for "weekly on Mon at 10:00".
+  `workday_minutes` smallint(4) default 480,             # number of work minutes in a regular working day
+  `custom_logo` tinyint(4) default 0,                    # whether to use a custom logo or not
+  `config` text default NULL,                            # miscellaneous group configuration settings
+  `created` datetime default NULL,                       # creation timestamp
+  `created_ip` varchar(45) default NULL,                 # creator ip
+  `created_by` int(11) default NULL,                     # creator user_id
+  `modified` datetime default NULL,                      # modification timestamp
+  `modified_ip` varchar(45) default NULL,                # modifier ip
+  `modified_by` int(11) default NULL,                    # modifier user_id
+  `status` tinyint(4) default 1,                         # group status

   PRIMARY KEY (`id`)
 );
 
 
 #
   PRIMARY KEY (`id`)
 );
 
 
 #

-# Structure for table tt_roles. This table stores customized team roles.
+# Structure for table tt_roles. This table stores group roles.

 #
 CREATE TABLE `tt_roles` (
   `id` int(11) NOT NULL auto_increment,    # Role id. Identifies roles for all groups on the server.
 #
 CREATE TABLE `tt_roles` (
   `id` int(11) NOT NULL auto_increment,    # Role id. Identifies roles for all groups on the server.

-  `team_id` int(11) NOT NULL,              # Team id the role is defined for.
+  `group_id` int(11) NOT NULL,             # Group id the role is defined for.
+  `org_id` int(11) default NULL,           # Organization id.

   `name` varchar(80) default NULL,         # Role name - custom role name. In case we are editing a
                                            # predefined role (USER, etc.), we can rename the role here.
   `description` varchar(255) default NULL, # Role description.
   `name` varchar(80) default NULL,         # Role name - custom role name. In case we are editing a
                                            # predefined role (USER, etc.), we can rename the role here.
   `description` varchar(255) default NULL, # Role description.

-  `rank` int(11) default 0,                # Role rank, an integer value between 0-324. Predefined role ranks:
-                                           # USER - 4, CLIENT - 16, COMANAGER - 68, MANAGER - 324.
+  `rank` int(11) default 0,                # Role rank, an integer value between 0-512. Predefined role ranks:
+                                           # User - 4, Supervisor - 12, Client - 16,
+                                           # Co-manager - 68, Manager - 324, Top manager - 512.

                                            # Rank is used to determine what "lesser roles" are in each group
                                            # Rank is used to determine what "lesser roles" are in each group

-                                           # for sutuations such as "manage_users".
-                                           # It also identifies a role within a team (by its "rank").
-                                           # Value of rank is to be used in role field in tt_users table,
-                                           # just like standard roles now.
+                                           # for situations such as "manage_users".

   `rights` text default NULL,              # Comma-separated list of rights assigned to a role.
                                            # NULL here for predefined roles (4, 16, 68, 324 - manager)
                                            # means a hard-coded set of default access rights.
   `rights` text default NULL,              # Comma-separated list of rights assigned to a role.
                                            # NULL here for predefined roles (4, 16, 68, 324 - manager)
                                            # means a hard-coded set of default access rights.


@@ -62,11 +70,11 @@ CREATE TABLE `tt_roles` (
 );
 
 # Create an index that guarantees unique active and inactive role ranks in each group.
 );
 
 # Create an index that guarantees unique active and inactive role ranks in each group.

-create unique index role_idx on tt_roles(team_id, rank, status);
+create unique index role_idx on tt_roles(group_id, rank, status);

 
 # Insert site-wide roles - site administrator and top manager.
 
 # Insert site-wide roles - site administrator and top manager.

-INSERT INTO `tt_roles` (`team_id`, `name`, `rank`, `rights`) VALUES (0, 'Site administrator', 1024, 'administer_site');
-INSERT INTO `tt_roles` (`team_id`, `name`, `rank`, `rights`) VALUES (0, 'Top manager', 512, 'track_own_time,track_own_expenses,view_own_reports,view_own_charts,view_own_invoices,manage_own_settings,view_users,track_time,track_expenses,view_reports,view_charts,override_punch_mode,override_own_punch_mode,override_date_lock,swap_roles,approve_timesheets,manage_users,manage_projects,manage_tasks,manage_custom_fields,manage_clients,manage_invoices,manage_features,manage_basic_settings,manage_advanced_settings,manage_roles,export_data,manage_subgroups');
+INSERT INTO `tt_roles` (`group_id`, `name`, `rank`, `rights`) VALUES (0, 'Site administrator', 1024, 'administer_site');
+INSERT INTO `tt_roles` (`group_id`, `name`, `rank`, `rights`) VALUES (0, 'Top manager', 512, 'track_own_time,track_own_expenses,view_own_reports,view_own_charts,view_own_invoices,view_own_projects,view_own_tasks,manage_own_settings,view_users,track_time,track_expenses,view_reports,view_charts,view_own_clients,override_punch_mode,override_own_punch_mode,override_date_lock,override_own_date_lock,swap_roles,approve_timesheets,manage_own_account,manage_users,manage_projects,manage_tasks,manage_custom_fields,manage_clients,manage_invoices,override_allow_ip,manage_basic_settings,view_all_reports,manage_features,manage_advanced_settings,manage_roles,export_data,manage_subgroups,delete_group');

 
 
 #
 
 
 #


@@ -74,16 +82,23 @@ INSERT INTO `tt_roles` (`team_id`, `name`, `rank`, `rights`) VALUES (0, 'Top man
 #
 CREATE TABLE `tt_users` (
   `id` int(11) NOT NULL auto_increment,            # user id
 #
 CREATE TABLE `tt_users` (
   `id` int(11) NOT NULL auto_increment,            # user id

-  `timestamp` timestamp default CURRENT_TIMESTAMP, # modification timestamp
-  `login` varchar(50) COLLATE utf8_bin NOT NULL,   # user login
+  `login` varchar(50) COLLATE utf8mb4_bin NOT NULL,# user login

   `password` varchar(50) default NULL,             # password hash
   `name` varchar(100) default NULL,                # user name
   `password` varchar(50) default NULL,             # password hash
   `name` varchar(100) default NULL,                # user name

-  `team_id` int(11) NOT NULL,                      # team id
-  `role` int(11) default 4,                        # user role ("manager", "co-manager", "client", or "user")
-  `role_id` int(11) default NULL,                  # future replacement of role
+  `group_id` int(11) NOT NULL,                     # group id
+  `org_id` int(11) default NULL,                   # organization id
+  `role_id` int(11) default NULL,                  # role id

   `client_id` int(11) default NULL,                # client id for "client" user role
   `rate` float(6,2) NOT NULL default '0.00',       # default hourly rate
   `email` varchar(100) default NULL,               # user email
   `client_id` int(11) default NULL,                # client id for "client" user role
   `rate` float(6,2) NOT NULL default '0.00',       # default hourly rate
   `email` varchar(100) default NULL,               # user email

+  `created` datetime default NULL,                 # creation timestamp
+  `created_ip` varchar(45) default NULL,           # creator ip
+  `created_by` int(11) default NULL,               # creator user_id (null for self)
+  `modified` datetime default NULL,                # modification timestamp
+  `modified_ip` varchar(45) default NULL,          # modifier ip
+  `modified_by` int(11) default NULL,              # modifier user_id
+  `accessed` datetime default NULL,                # last access timestamp
+  `accessed_ip` varchar(45) default NULL,          # last access ip

   `status` tinyint(4) default 1,                   # user status
   PRIMARY KEY (`id`)
 );
   `status` tinyint(4) default 1,                   # user status
   PRIMARY KEY (`id`)
 );


@@ -91,42 +106,44 @@ CREATE TABLE `tt_users` (
 # Create an index that guarantees unique active and inactive logins.
 create unique index login_idx on tt_users(login, status);
 
 # Create an index that guarantees unique active and inactive logins.
 create unique index login_idx on tt_users(login, status);
 

-# Create admin account with password 'secret'. Admin is a superuser, who can create teams.
+# Create admin account with password 'secret'. Admin is a superuser who can create groups.

 DELETE from `tt_users` WHERE login = 'admin';
 DELETE from `tt_users` WHERE login = 'admin';

-INSERT INTO `tt_users` (`login`, `password`, `name`, `team_id`, `role`, `role_id`) VALUES ('admin', md5('secret'), 'Admin', '0', '1024', (select id from tt_roles where rank = 1024));
+INSERT INTO `tt_users` (`login`, `password`, `name`, `group_id`, `role_id`) VALUES ('admin', md5('secret'), 'Admin', '0', (select id from tt_roles where rank = 1024));

 
 
 #
 # Structure for table tt_projects.
 #
 CREATE TABLE `tt_projects` (
 
 
 #
 # Structure for table tt_projects.
 #
 CREATE TABLE `tt_projects` (

-  `id` int(11) NOT NULL auto_increment,         # project id
-  `team_id` int(11) NOT NULL,                   # team id
-  `name` varchar(80) COLLATE utf8_bin NOT NULL, # project name
-  `description` varchar(255) default NULL,      # project description
-  `tasks` text default NULL,                    # comma-separated list of task ids associated with this project
-  `status` tinyint(4) default 1,                # project status
+  `id` int(11) NOT NULL auto_increment,            # project id
+  `group_id` int(11) NOT NULL,                     # group id
+  `org_id` int(11) default NULL,                   # organization id
+  `name` varchar(80) COLLATE utf8mb4_bin NOT NULL, # project name
+  `description` varchar(255) default NULL,         # project description
+  `tasks` text default NULL,                       # comma-separated list of task ids associated with this project
+  `status` tinyint(4) default 1,                   # project status

   PRIMARY KEY (`id`)
 );
 
   PRIMARY KEY (`id`)
 );
 

-# Create an index that guarantees unique active and inactive projects per team.
-create unique index project_idx on tt_projects(team_id, name, status);
+# Create an index that guarantees unique active and inactive projects per group.
+create unique index project_idx on tt_projects(group_id, name, status);

 
 
 #
 # Structure for table tt_tasks.
 #
 CREATE TABLE `tt_tasks` (
 
 
 #
 # Structure for table tt_tasks.
 #
 CREATE TABLE `tt_tasks` (

-  `id` int(11) NOT NULL auto_increment,         # task id
-  `team_id` int(11) NOT NULL,                   # team id
-  `name` varchar(80) COLLATE utf8_bin NOT NULL, # task name
-  `description` varchar(255) default NULL,      # task description
-  `status` tinyint(4) default 1,                # task status
+  `id` int(11) NOT NULL auto_increment,            # task id
+  `group_id` int(11) NOT NULL,                     # group id
+  `org_id` int(11) default NULL,                   # organization id
+  `name` varchar(80) COLLATE utf8mb4_bin NOT NULL, # task name
+  `description` varchar(255) default NULL,         # task description
+  `status` tinyint(4) default 1,                   # task status

   PRIMARY KEY (`id`)
 );
 
   PRIMARY KEY (`id`)
 );
 

-# Create an index that guarantees unique active and inactive tasks per team.
-create unique index task_idx on tt_tasks(team_id, name, status);
+# Create an index that guarantees unique active and inactive tasks per group.
+create unique index task_idx on tt_tasks(group_id, name, status);

 
 
 #
 
 
 #


@@ -136,6 +153,8 @@ CREATE TABLE `tt_user_project_binds` (
   `id` int(11) NOT NULL auto_increment, # bind id
   `user_id` int(11) NOT NULL,           # user id
   `project_id` int(11) NOT NULL,        # project id
   `id` int(11) NOT NULL auto_increment, # bind id
   `user_id` int(11) NOT NULL,           # user id
   `project_id` int(11) NOT NULL,        # project id

+  `group_id` int(11) default NULL,      # group id
+  `org_id` int(11) default NULL,        # organization id

   `rate` float(6,2) default '0.00',     # rate for this user when working on this project
   `status` tinyint(4) default 1,        # bind status
   PRIMARY KEY (`id`)
   `rate` float(6,2) default '0.00',     # rate for this user when working on this project
   `status` tinyint(4) default 1,        # bind status
   PRIMARY KEY (`id`)


@@ -149,8 +168,10 @@ create unique index bind_idx on tt_user_project_binds(user_id, project_id);
 # Structure for table tt_project_task_binds. This table maps projects to assigned tasks.
 #
 CREATE TABLE `tt_project_task_binds` (
 # Structure for table tt_project_task_binds. This table maps projects to assigned tasks.
 #
 CREATE TABLE `tt_project_task_binds` (

-  `project_id` int(11) NOT NULL, # project id
-  `task_id` int(11) NOT NULL     # task id
+  `project_id` int(11) NOT NULL,        # project id
+  `task_id` int(11) NOT NULL,           # task id
+  `group_id` int(11) default NULL,      # group id
+  `org_id` int(11) default NULL         # organization id

 );
 
 # Indexes for tt_project_task_binds.
 );
 
 # Indexes for tt_project_task_binds.


@@ -164,8 +185,9 @@ create index task_idx on tt_project_task_binds(task_id);
 #
 CREATE TABLE `tt_log` (
   `id` bigint NOT NULL auto_increment,             # time record id
 #
 CREATE TABLE `tt_log` (
   `id` bigint NOT NULL auto_increment,             # time record id

-  `timestamp` timestamp default CURRENT_TIMESTAMP, # modification timestamp

   `user_id` int(11) NOT NULL,                      # user id
   `user_id` int(11) NOT NULL,                      # user id

+  `group_id` int(11) default NULL,                 # group id
+  `org_id` int(11) default NULL,                   # organization id

   `date` date NOT NULL,                            # date the record is for
   `start` time default NULL,                       # record start time (for example, 09:00)
   `duration` time default NULL,                    # record duration (for example, 1 hour)
   `date` date NOT NULL,                            # date the record is for
   `start` time default NULL,                       # record start time (for example, 09:00)
   `duration` time default NULL,                    # record duration (for example, 1 hour)


@@ -176,6 +198,12 @@ CREATE TABLE `tt_log` (
   `comment` text,                                  # user provided comment for time record
   `billable` tinyint(4) default 0,                 # whether the record is billable or not
   `paid` tinyint(4) default 0,                     # whether the record is paid
   `comment` text,                                  # user provided comment for time record
   `billable` tinyint(4) default 0,                 # whether the record is billable or not
   `paid` tinyint(4) default 0,                     # whether the record is paid

+  `created` datetime default NULL,                 # creation timestamp
+  `created_ip` varchar(45) default NULL,           # creator ip
+  `created_by` int(11) default NULL,               # creator user_id
+  `modified` datetime default NULL,                # modification timestamp
+  `modified_ip` varchar(45) default NULL,          # modifier ip
+  `modified_by` int(11) default NULL,              # modifier user_id

   `status` tinyint(4) default 1,                   # time record status
   PRIMARY KEY (`id`)
 );
   `status` tinyint(4) default 1,                   # time record status
   PRIMARY KEY (`id`)
 );


@@ -183,6 +211,7 @@ CREATE TABLE `tt_log` (
 # Create indexes on tt_log for performance.
 create index date_idx on tt_log(date);
 create index user_idx on tt_log(user_id);
 # Create indexes on tt_log for performance.
 create index date_idx on tt_log(date);
 create index user_idx on tt_log(user_id);

+create index group_idx on tt_log(group_id);

 create index client_idx on tt_log(client_id);
 create index invoice_idx on tt_log(invoice_id);
 create index project_idx on tt_log(project_id);
 create index client_idx on tt_log(client_id);
 create index invoice_idx on tt_log(invoice_id);
 create index project_idx on tt_log(project_id);


@@ -193,24 +222,25 @@ create index task_idx on tt_log(task_id);
 # Structure for table tt_invoices. Invoices are issued to clients for billable work.
 #
 CREATE TABLE `tt_invoices` (
 # Structure for table tt_invoices. Invoices are issued to clients for billable work.
 #
 CREATE TABLE `tt_invoices` (

-  `id` int(11) NOT NULL auto_increment,         # invoice id
-  `team_id` int(11) NOT NULL,                   # team id
-  `name` varchar(80) COLLATE utf8_bin NOT NULL, # invoice name
-  `date` date NOT NULL,                         # invoice date
-  `client_id` int(11) NOT NULL,                 # client id
-  `status` tinyint(4) default 1,                # invoice status
+  `id` int(11) NOT NULL auto_increment,            # invoice id
+  `group_id` int(11) NOT NULL,                     # group id
+  `org_id` int(11) default NULL,                   # organization id
+  `name` varchar(80) COLLATE utf8mb4_bin NOT NULL, # invoice name
+  `date` date NOT NULL,                            # invoice date
+  `client_id` int(11) NOT NULL,                    # client id
+  `status` tinyint(4) default 1,                   # invoice status

   PRIMARY KEY (`id`)
 );
 
   PRIMARY KEY (`id`)
 );
 

-# Create an index that guarantees unique invoice names per team.
-create unique index name_idx on tt_invoices(team_id, name, status);
+# Create an index that guarantees unique invoice names per group.
+create unique index name_idx on tt_invoices(group_id, name, status);

 
 
 #
 # Structure for table tt_tmp_refs. Used for reset password mechanism.
 #
 CREATE TABLE `tt_tmp_refs` (
 
 
 #
 # Structure for table tt_tmp_refs. Used for reset password mechanism.
 #
 CREATE TABLE `tt_tmp_refs` (

-  `timestamp` timestamp default CURRENT_TIMESTAMP, # creation timestamp
+  `created` datetime default NULL,                 # creation timestamp

   `ref` char(32) NOT NULL default '',              # unique reference for user, used in urls
   `user_id` int(11) NOT NULL                       # user id
 );
   `ref` char(32) NOT NULL default '',              # unique reference for user, used in urls
   `user_id` int(11) NOT NULL                       # user id
 );


@@ -238,6 +268,7 @@ CREATE TABLE `tt_fav_reports` (
   `show_client` tinyint(4) NOT NULL default 0,           # whether to show client column
   `show_invoice` tinyint(4) NOT NULL default 0,          # whether to show invoice column
   `show_paid` tinyint(4) NOT NULL default 0,             # whether to show paid column
   `show_client` tinyint(4) NOT NULL default 0,           # whether to show client column
   `show_invoice` tinyint(4) NOT NULL default 0,          # whether to show invoice column
   `show_paid` tinyint(4) NOT NULL default 0,             # whether to show paid column

+  `show_ip` tinyint(4) NOT NULL default 0,               # whether to show ip column

   `show_project` tinyint(4) NOT NULL default 0,          # whether to show project column
   `show_start` tinyint(4) NOT NULL default 0,            # whether to show start field
   `show_duration` tinyint(4) NOT NULL default 0,         # whether to show duration field
   `show_project` tinyint(4) NOT NULL default 0,          # whether to show project column
   `show_start` tinyint(4) NOT NULL default 0,            # whether to show start field
   `show_duration` tinyint(4) NOT NULL default 0,         # whether to show duration field


@@ -246,8 +277,11 @@ CREATE TABLE `tt_fav_reports` (
   `show_end` tinyint(4) NOT NULL default 0,              # whether to show end field
   `show_note` tinyint(4) NOT NULL default 0,             # whether to show note column
   `show_custom_field_1` tinyint(4) NOT NULL default 0,   # whether to show custom field 1
   `show_end` tinyint(4) NOT NULL default 0,              # whether to show end field
   `show_note` tinyint(4) NOT NULL default 0,             # whether to show note column
   `show_custom_field_1` tinyint(4) NOT NULL default 0,   # whether to show custom field 1

+  `show_work_units` tinyint(4) NOT NULL default 0,       # whether to show work units

   `show_totals_only` tinyint(4) NOT NULL default 0,      # whether to show totals only
   `show_totals_only` tinyint(4) NOT NULL default 0,      # whether to show totals only

-  `group_by` varchar(20) default NULL,                   # group by field
+  `group_by1` varchar(20) default NULL,                  # group by field 1
+  `group_by2` varchar(20) default NULL,                  # group by field 2
+  `group_by3` varchar(20) default NULL,                  # group by field 3

   `status` tinyint(4) default 1,                         # favorite report status
   PRIMARY KEY (`id`)
 );
   `status` tinyint(4) default 1,                         # favorite report status
   PRIMARY KEY (`id`)
 );


@@ -258,7 +292,7 @@ CREATE TABLE `tt_fav_reports` (
 #
 CREATE TABLE `tt_cron` (
   `id` int(11) NOT NULL auto_increment,         # entry id
 #
 CREATE TABLE `tt_cron` (
   `id` int(11) NOT NULL auto_increment,         # entry id

-  `team_id` int(11) NOT NULL,                   # team id
+  `group_id` int(11) NOT NULL,                  # group id

   `cron_spec` varchar(255) NOT NULL,            # cron specification, "0 1 * * *" for "daily at 01:00"
   `last` int(11) default NULL,                  # UNIX timestamp of when job was last run
   `next` int(11) default NULL,                  # UNIX timestamp of when to run next job
   `cron_spec` varchar(255) NOT NULL,            # cron specification, "0 1 * * *" for "daily at 01:00"
   `last` int(11) default NULL,                  # UNIX timestamp of when job was last run
   `next` int(11) default NULL,                  # UNIX timestamp of when to run next job


@@ -276,18 +310,19 @@ CREATE TABLE `tt_cron` (
 # Structure for table tt_clients. A client is an entity for whom work is performed and who may be invoiced.
 #
 CREATE TABLE `tt_clients` (
 # Structure for table tt_clients. A client is an entity for whom work is performed and who may be invoiced.
 #
 CREATE TABLE `tt_clients` (

-  `id` int(11) NOT NULL AUTO_INCREMENT,         # client id
-  `team_id` int(11) NOT NULL,                   # team id
-  `name` varchar(80) COLLATE utf8_bin NOT NULL, # client name
-  `address` varchar(255) default NULL,          # client address
-  `tax` float(6,2) default '0.00',              # applicable tax for this client
-  `projects` text default NULL,                 # comma-separated list of project ids assigned to this client
-  `status` tinyint(4) default 1,                # client status
+  `id` int(11) NOT NULL AUTO_INCREMENT,            # client id
+  `group_id` int(11) NOT NULL,                     # group id
+  `org_id` int(11) default NULL,                   # organization id
+  `name` varchar(80) COLLATE utf8mb4_bin NOT NULL, # client name
+  `address` varchar(255) default NULL,             # client address
+  `tax` float(6,2) default '0.00',                 # applicable tax for this client
+  `projects` text default NULL,                    # comma-separated list of project ids assigned to this client
+  `status` tinyint(4) default 1,                   # client status

   PRIMARY KEY (`id`)
 );
 
   PRIMARY KEY (`id`)
 );
 

-# Create an index that guarantees unique active and inactive clients per team.
-create unique index client_name_idx on tt_clients(team_id, name, status);
+# Create an index that guarantees unique active and inactive clients per group.
+create unique index client_name_idx on tt_clients(group_id, name, status);

 
 
 #
 
 
 #


@@ -324,7 +359,7 @@ create unique index param_idx on tt_config(user_id, param_name);
 #
 CREATE TABLE `tt_custom_fields` (
   `id` int(11) NOT NULL auto_increment,    # custom field id
 #
 CREATE TABLE `tt_custom_fields` (
   `id` int(11) NOT NULL auto_increment,    # custom field id

-  `team_id` int(11) NOT NULL,              # team id
+  `group_id` int(11) NOT NULL,             # group id

   `type` tinyint(4) NOT NULL default 0,    # custom field type (text or dropdown)
   `label` varchar(32) NOT NULL default '', # custom field label
   `required` tinyint(4) default 0,         # whether this custom field is mandatory for time records
   `type` tinyint(4) NOT NULL default 0,    # custom field type (text or dropdown)
   `label` varchar(32) NOT NULL default '', # custom field label
   `required` tinyint(4) default 0,         # whether this custom field is mandatory for time records


@@ -358,28 +393,38 @@ CREATE TABLE `tt_custom_field_log` (
   PRIMARY KEY  (`id`)
 );
 
   PRIMARY KEY  (`id`)
 );
 

+create index log_idx on tt_custom_field_log(log_id);
+

 
 #
 # Structure for table tt_expense_items.
 # This table lists expense items.
 #
 CREATE TABLE `tt_expense_items` (
 
 #
 # Structure for table tt_expense_items.
 # This table lists expense items.
 #
 CREATE TABLE `tt_expense_items` (

-  `id` bigint NOT NULL auto_increment, # expense item id
-  `date` date NOT NULL,                # date the record is for
-  `user_id` int(11) NOT NULL,          # user id the expense item is reported by
-  `client_id` int(11) default NULL,    # client id
-  `project_id` int(11) default NULL,   # project id
-  `name` text NOT NULL,                # expense item name (what is an expense for)   
-  `cost` decimal(10,2) default '0.00', # item cost (including taxes, etc.)
-  `invoice_id` int(11) default NULL,   # invoice id
-  `paid` tinyint(4) default 0,         # whether the item is paid
-  `status` tinyint(4) default 1,       # item status
+  `id` bigint NOT NULL auto_increment,    # expense item id
+  `date` date NOT NULL,                   # date the record is for
+  `user_id` int(11) NOT NULL,             # user id the expense item is reported by
+  `group_id` int(11) default NULL,        # group id
+  `client_id` int(11) default NULL,       # client id
+  `project_id` int(11) default NULL,      # project id
+  `name` text NOT NULL,                   # expense item name (what is an expense for)
+  `cost` decimal(10,2) default '0.00',    # item cost (including taxes, etc.)
+  `invoice_id` int(11) default NULL,      # invoice id
+  `paid` tinyint(4) default 0,            # whether the item is paid
+  `created` datetime default NULL,        # creation timestamp
+  `created_ip` varchar(45) default NULL,  # creator ip
+  `created_by` int(11) default NULL,      # creator user_id
+  `modified` datetime default NULL,       # modification timestamp
+  `modified_ip` varchar(45) default NULL, # modifier ip
+  `modified_by` int(11) default NULL,     # modifier user_id
+  `status` tinyint(4) default 1,          # item status

   PRIMARY KEY  (`id`)
 );
 
 # Create indexes on tt_expense_items for performance.
 create index date_idx on tt_expense_items(date);
 create index user_idx on tt_expense_items(user_id);
   PRIMARY KEY  (`id`)
 );
 
 # Create indexes on tt_expense_items for performance.
 create index date_idx on tt_expense_items(date);
 create index user_idx on tt_expense_items(user_id);

+create index group_idx on tt_expense_items(group_id);

 create index client_idx on tt_expense_items(client_id);
 create index project_idx on tt_expense_items(project_id);
 create index invoice_idx on tt_expense_items(invoice_id);
 create index client_idx on tt_expense_items(client_id);
 create index project_idx on tt_expense_items(project_id);
 create index invoice_idx on tt_expense_items(invoice_id);


@@ -391,7 +436,7 @@ create index invoice_idx on tt_expense_items(invoice_id);
 #
 CREATE TABLE `tt_predefined_expenses` (
   `id` int(11) NOT NULL auto_increment, # predefined expense id
 #
 CREATE TABLE `tt_predefined_expenses` (
   `id` int(11) NOT NULL auto_increment, # predefined expense id

-  `team_id` int(11) NOT NULL,           # team id
+  `group_id` int(11) NOT NULL,          # group id

   `name` varchar(255) NOT NULL,         # predefined expense name, such as mileage
   `cost` decimal(10,2) default '0.00',  # cost for one unit
   PRIMARY KEY  (`id`)
   `name` varchar(255) NOT NULL,         # predefined expense name, such as mileage
   `cost` decimal(10,2) default '0.00',  # cost for one unit
   PRIMARY KEY  (`id`)


@@ -400,19 +445,17 @@ CREATE TABLE `tt_predefined_expenses` (
 
 #
 # Structure for table tt_monthly_quotas.
 
 #
 # Structure for table tt_monthly_quotas.

-# This table keeps monthly work hour quotas for teams.
+# This table keeps monthly work hour quotas for groups.

 #
 CREATE TABLE `tt_monthly_quotas` (
 #
 CREATE TABLE `tt_monthly_quotas` (

-  `team_id` int(11) NOT NULL,             # team id
+  `group_id` int(11) NOT NULL,            # group id
+  `org_id` int(11) default NULL           # organization id

   `year` smallint(5) UNSIGNED NOT NULL,   # quota year
   `month` tinyint(3) UNSIGNED NOT NULL,   # quota month
   `minutes` int(11) default NULL,         # quota in minutes in specified month and year
   `year` smallint(5) UNSIGNED NOT NULL,   # quota year
   `month` tinyint(3) UNSIGNED NOT NULL,   # quota month
   `minutes` int(11) default NULL,         # quota in minutes in specified month and year

-  PRIMARY KEY (`team_id`,`year`,`month`)
+  PRIMARY KEY (`group_id`,`year`,`month`)

 );
 
 );
 

-ALTER TABLE `tt_monthly_quotas`
-  ADD CONSTRAINT `FK_TT_TEAM_CONSTRAING` FOREIGN KEY (`team_id`) REFERENCES `tt_teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-

 
 #
 # Structure for table tt_site_config. This table stores configuration data
 
 #
 # Structure for table tt_site_config. This table stores configuration data


@@ -422,9 +465,9 @@ ALTER TABLE `tt_monthly_quotas`
 CREATE TABLE `tt_site_config` (
   `param_name` varchar(32) NOT NULL, # parameter name
   `param_value` text default NULL,   # parameter value
 CREATE TABLE `tt_site_config` (
   `param_name` varchar(32) NOT NULL, # parameter name
   `param_value` text default NULL,   # parameter value

-  `created` datetime default NULL,   # creation time
-  `updated` datetime default NULL,   # modification time
+  `created` datetime default NULL,   # creation timestamp
+  `modified` datetime default NULL,  # modification timestamp

   PRIMARY KEY  (`param_name`)
 );
 
   PRIMARY KEY  (`param_name`)
 );
 

-INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.17.48', now()); # TODO: change when structure changes.
+INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.18.15', now()); # TODO: change when structure changes.