Requirements:

1. Be able to assign an SQL DataSource Table as the primary source of truth (source of most up-to-date TGNG data)

   1. Update the Jira CUSTOMFIELDTABLE DB value according to the DB Table value (On Issue load and API access event)

      1. check if JQL search and Jira Issue Export trigger any of our methods (pull data periodically to keep the Jira version fresh?)

2. The feature should be an extension of the Data Mirror functionality

   1. Add a button to allow users to pick whether they want the Data Mirror table to be the source of truth

   2. Add a checkbox labelled “Treat DataSource value as primary during sync (overwrites Jira value)” (or other wording that would sound better)

Use Case (Data Flow):

1. The user defines an SQL data source

2. In the Grid config, the user sets up Data Mirror and checks the box to make it the primary storage

   1. An info panel with table handling restrictions is displayed (Do not change the table schema or it will break)

   2. A checkbox is presented labelled something like “Treat DataSource value as primary during sync (overwrites Jira value)”

3. Any pre-existing data is mirrored to the DB Table (as in Data Mirror)

4. Any changes in the DB Table after that are reflected on Issue View and when REST API is called

   1. If an error occurs because of DB Table data type incompatibility (or any other SQL-related data), the error message should include that information

5. Any changes by the user (on issue or via REST/Java API) are first reflected in the table and then saved to the default Jira storage

   1. If the save is not possible due to the change in the DB Table, an error should be displayed detailing the cause

6. The JQL search is using the most up-to-date values that are taken from the DB Table

Use-case clarifications

...

Usecase

...

Description

...

Questions to Clarify

...

Answers

...

Connect to the datasource

...

Users configure the connection from the app to the external datasource.

...

1. What type of datasource is it? (SQL, Posgres, Oracle, MSSQL)

2. Which source will be the primary? Database or App?

...

1. Support all of them

2. Suggest “Database” to be the main source

...

Display data from the datasource

...

Data from the datasource is displayed in tables within the app.

...

1. Is there a limit to the number of records displayed?

2. How to handle with big data from database?

3. JQL search will still work as expected?

...

1. No

2. Still run, just take more time to synchonize.

3. Yes

...

Update data in the app

...

Users can update data in the app, and the changes are reflected in the external datasource.

...

1. How should conflicts between app and datasource data be resolved?

2. How can we detect the data as conflict? Should users be notified about unsynced data? Should a warning display for stale data?

3. What happen when the connection is lost? Can still user be able to use that grid?

4. How should conflicts be handled if datasource changes during editing?

5. If the data is add from UI but the database is offline, will the data from app be save or throw error?

...

1. If the data is not the same, the grid will still show the data (which is saved from Jira database). But in this case, user will not know if the data is not synchonize.

2. Status
   colour Blue title TBD

   We need to find a way to let user know if the data is not the same…

3. When the connection is lost, the user can still use the grid, but the data will no longer sync with the database.

4. The action that is saved last will determine the final data value.

5. Throw error → tested on Data Mirror TGNG

...

Update data in the datasource

...

Updates made in the datasource are synchronized back to the app.

...

1. Is real-time synchronization required, or will periodic updates suffice, or when user reload the grid?

2. How will the app handle partial updates or missing fields?

3. What happens if a record is updated in the app and datasource simultaneously?

4. Should updates from the datasource queue when the user is offline and apply later?

...

1. Realtime is needed, and user can synchonize again when click on a button “Re-synchonize”.

2. Follow TGE, with the column is not the same type or missing, it will be ignored.

3. The action that is saved last will determine the final data value.

4. Status
   colour Blue title TBD

   A background function to run the synchonize is an option, but not sure if we should support this or not…

...

Synchronize datasource schema

...

Automatically detect and adapt to the structure (schema) of the datasource for compatibility.

...

1. Should the schema be fetched dynamically or provided manually?

2. How will changes in the datasource schema be handled?

3. Should users be notified if new columns or tables are added to the datasource?

4. How to handle if app and database have different restriction? Like allow null value, different in data type?

...

1. No, refect to TGE

2. User will need to synchonize manually

3. Status
   colour Blue title TBD

4. Showing error when user saves data in the app

...

Error handling and notifications

...

Users are informed of connection issues, synchronization errors, or invalid data.

...

1. What types of errors should trigger notifications?

2. Should there be retry mechanisms for failed synchonize or connections?

3. What level of detail should be provided in error messages?

...

1. Status
   colour Blue title TBD

   Errors related to the table structure not the same, synchonize action failed

2. Yes, we should provide a button for users to proactively synchronize. However, we will notify them that all current data will be overwritten by the data from the database. Since the synchronization process might take some time, we need to warn users not to interact with the grid during this period. Additionally, consider providing a progress indicator to enhance the user experience.

3. Status
   colour Blue title TBD

   Show them clearly which column type is preventing user from saving the data.

...

Security and access control

...

Ensure that only authorized users can access and update data.

...

1. What roles/permissions are needed for accessing and updating data?

2. Is encryption required for data in transit and at rest?

3. Should users' actions (e.g., edits, deletions) be restricted based on their roles?

...

1. User that can edit grid can do this, or admin can use the Grid Behaviour to restrict

2. No, reflect on TGE

3. The same as #1 answer

...

Logging and audit trail

...

Log all actions related to data synchronization for troubleshooting and auditing purposes.

...

1. What details should be logged (e.g., timestamp, user, action)?

2. Who should have access to the logs?

3. Should there be a UI for viewing logs within the app?

...

1. Status
   colour Blue title TBD

   Something the same as Grid History should be logged.

2. Admin

3. Yes, but in the Grid Configuration → Data Mirror section for only admin

...

Data transformation and mapping

...

Define how the datasource data is mapped to the app's data structure.

...

1. What if the column type is being changed from one side, what should be happen?

2. How will unsupported or invalid data be handled?

...

1. With TGE, it does not support changing column types and reflecting those changes in the database and vice versa, if the changes is being processed, the data in table will be broken and does not display the data. TGNG should have the same restriction.

2. Ignore and only work with correct data

Plan of action:

1. Check how TGE handles a similar functionality

2. Implement a naїve version of the functionality (always overwrites on sync, doesn’t handle getCustomfieldValue, etc.)

3. Validate whether all of the points in the Questions are relevant to the actual behaviour of the Grid in that basic implementation version.

4. Write potential answers/suggestions to the questions with some detail on the implementation.

5. Get approval on the clarifications from Questions

6. Agree and document the timescale of the full implementation, and add it to the overall roadmap.

7. Implement a more thorough version of the functionality

Questions:

...

Question

...

Hung: To simplify the process, we can display a warning to notify users when the data is not synchronized correctly. Additionally, we can provide a "Re-synchronize" button, which, when clicked, will fetch all the data from the database and replace the current data in the UI grid. This approach ensures that the database acts as the primary source for synchronization.

...

Does this functionality need to be present on Cloud as well as on Server

...

Hung: There is no restriction so I think we can do for both

...

How the TGE manages the getCustomfieldValue call from Jira’s own API? Do we provide the data pulled from the table on that call? Is it a Service that TGE exports?

...

Hung: We keep auto_increment ID on both side, and use another field “UUID” to manage the data to synchonize.

With the data save from FE, we can use library like uuidv4

WIth the data save from database, we can use function UUID() from mysql

CREATE TABLE MyTable (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL DEFAULT (UUID()),
    name VARCHAR(255),
    age INT
);

...

If the DB Table sourced rows contain non-standard Row IDs, do they get transformed and re-inserted? Can we make the correct format of the Row ID be populated on the SQL side?

...

Hung: the same answer as above

...

Should we use a different format for Row IDs that are primarily stored in a remote DB?

...

Hung: UUID is an option that I suggest

...

Should the case of initialising TGNG from a pre-existing table be supported?

...

Hung: Yes, that is also a case that Francis suggest when we talk about bulk import

...

Do we handle Attachment columns by just storing the Attachment ID?

...

HUng: Tested on current Data Mirror function, it will save the Attachment ID with the value property

...

Do wrong/non-existing Attachment ID values get ignored or do we add a custom error? Does the same behaviour apply to wrong/non-existing values on User/Multi/Single-select?

...

Info
This document outlines the bidirectional synchronization feature in TGNG, explaining its purpose, functionality, and key considerations for users.

Table of Contents
minLevel 1 maxLevel 6 outline false style none type list printable true

Overview

The Bidirectional Data Synchronization feature enables seamless two-way data exchange between Table Grid Next Generation (TGNG) and an external database. This allows users to maintain up-to-date information across both systems efficiently.

Key Benefits

• Automatic Data Synchronization: Keep your grid data and database in sync without manual intervention.

• Customizable Sync Rules: Choose whether database values should overwrite grid values.

• Improved Data Integrity: Minimize discrepancies by ensuring data consistency.

How It Works

1. Enable Bidirectional Synchronization in the grid settings.

2. Configure the Data Source to specify which database table will sync with the grid.

3. Select Sync Mode:

   • Grid as Primary: Changes in the grid update the database.

   • Database as Primary: Database updates override grid values.

4. Automatic or Manual Sync: Choose between real-time updates or scheduled batch synchronization.

Flowcharts

1. Synchronize Bidirectionally Data Mirror (ODBC)

Drawio
mVer 2 zoom 1 simple 0 inComment 0 custContentId 293634077 pageId 291340289 lbox 1 diagramDisplayName Untitled Diagram-1736765866887.drawio contentVer 2 revision 2 baseUrl https://tablegrid.atlassian.net/wiki diagramName Untitled Diagram-1736765866887.drawio pCenter 0 width 601 links tbstyle height 561

2. Disconnect Synchronize Bidirectionally Data Mirror (ODBC) → Data Mirror function as normally

Drawio
mVer 2 zoom 1 simple 0 inComment 0 custContentId 292848080 pageId 291340289 lbox 1 diagramDisplayName Untitled Diagram-1736766899314.drawio contentVer 1 revision 1 baseUrl https://tablegrid.atlassian.net/wiki diagramName Untitled Diagram-1736766899314.drawio pCenter 0 width 601.0000000000003 links tbstyle height 440.99999999999994

Configuration Steps

1. Navigate to Grid Settings → Data Mirror.

2. Enable Bidirectional Sync and select the preferred sync mode.

Image Added

3. Define Sync Rules:

   • Map grid columns to database fields.

4. Save Settings and start synchronization.

Common Questions

1. How is it different from the current Data Mirror function?

The current Data Mirror function only allows saving grid data to an external database for viewing purposes.

With Data Mirror Synchronization, you can edit data directly in the external database, and the changes will be reflected in the Grid UI.

2. What databases are supported?

Most major SQL databases (PostgreSQL, MySQL, SQL Server, Oracle.) are supported.

3. What are the limitations?

• Currently, if data is saved from the Grid UI and later deleted from the external database, it will not be automatically removed from the Grid.

• Each time you update data in the external database, you must reload the grid in the UI to see the latest changes.

---

With this feature, users will gain enhanced flexibility in managing data synchronization between Jira and external databases. By ensuring data integrity and providing a configurable source of truth, TGNG aims to streamline workflows and improve efficiency. Future updates will refine and expand upon this functionality based on user feedback and evolving needs.

For further assistance, refer to our Support Portal.