---
# System prepended metadata

title: Time TrackerData Migration Documentation

---

---
title: 'Time TrackerData Migration Documentation'
---

Time Tracker Data Migrator
===


## Table of Contents

[TOC]

## Description

This repository was made to migrate all Time Tracker data from CosmosDB (TimeTracker V1 DB) to PostgreSQL (TimeTracker V2 DB).

Stack Technologies
---
* [Azure Cosmos DB](https://docs.microsoft.com/en-us/azure/cosmos-db/introduction)
* [PostgreSQL Database](https://www.postgresql.org/docs/current/)
* [Docker](https://docs.docker.com/), [docker-compose](https://docs.docker.com/compose/)
* [GPG](https://gnupg.org/)
* [Git-crypt](https://github.com/AGWA/git-crypt)
* Python libraries
    * [SQL Alchemy](https://www.sqlalchemy.org/)
    * [psycopg2](https://pypi.org/project/psycopg2/)
    * [alembic](https://alembic.sqlalchemy.org/en/latest/)
    * Python libraries for Azure:
        * [msal](https://msal-python.readthedocs.io/en/latest/)
        * [azure-storage-blob](https://pypi.org/project/azure-storage-blob/)
        * [azure-cosmos](https://https://pypi.org/project/azure-cosmos/) 

Flow
---
```sequence
Script->CosmosDB: connect()
Script->CosmosDB: get_data()
CosmosDB->Script: storage_data
Script->Postgres: connect()
Note right of Script: Check for data matches
Note right of Script: Match data from similar columns
Note right of Script: Match new ids with old ids
Script->Postgres: insert_data()

```

Migration Methodology
---
The migrator implementation includes the following tasks:
* Implementing mapping data from CosmosDB to PostgreSQL DB: the cosmos to Postgre migrator will migrate data if:
    * If cosmos data doesn't have a 'deleted' field.
    * If cosmos data does have a '_last_event_ctx' field.
    
> Additionaly, there are some items in the tables that doesn't have a value for customer_id, project_id or activity_id, therefore a default id was created for each one of this fields. This default id will be assigned to every item that has any of these fields empty.

* Special constraints for migration:
     * Each project_type item must have a customer_id field.
     * Each project item must have customer_id field.
     * Each time_entry item must have owner_id field.

:::info
This script is only runned manually by a GitHub Workflow. ***It needs to be confirmed before running it.***
:::

User instructions
---

### Local env
To execute the data migration from Cosmos DB to PostgreSQL in your local environment, you should do the following steps:
* Make sure Docker is running.
* Make sure your GPG private key is in the repository (Your Jr. Tech Lead can help with this)
* In the repository's root, run the following commands:
 ```
git-crypt unlock
make build_alembic
make build_migration_postgresql
make run_database
make update_migrations
make run_migration_postgresql
````
> Use `make stop` to stop all containers.
* Now the migration will run in your local DB.

>***Note:*** Run this script in local just for testing purposes.

### Stage/prod env
To execute the data migration from Cosmos DB to PostgreSQL in stage/production environment, you should do the following steps:
* Go to the actions section ![](https://i.imgur.com/UZ6SGEG.png)
* Select the ***Migration to Time-Tracker-V2 Pipeline - Stage*** or ***Migration to Time-Tracker-V2 Pipeline - Prod*** depending on which one you want to execute. ![](https://i.imgur.com/GDOOmF5.png)
* Select the Run workflow option.
![](https://i.imgur.com/cfv0Ow6.png)
* Confirm that you wan to run this workflow manually by selecting the ***yes*** option.
* The worklow will start.

> ***Note:*** For a period of 2 weeks, the XXXX and YYYY workflows are executed to maintain the sincronized data



