# Collaborative Editing Chatbot for Persistent Chabot feature

CREATE TABLE chatbots (
   id UUID PRIMARY KEY,
   data JSONB,
   version_number SERIAL,
);

-- only one version of chatbot can be published
CREATE TABLE publish_chatbots (
   id UUID PRIMARY KEY,
   chatbot_id UUID REFERENCES users(id),
   version_number SERIAL,
);

CHANGES
- computer 1
- version = 1
- made a changes
- saved to db
- version = 2
- success because the current version in db is version=1
- computer 2
- version = 1
- made a changes
- saved to db
- version = 1
- rejected because version out of date
- discard (local changes deleted and replaced by new version)
- create new copy ( local changes deleted and make a copy and then replace the current chatbot from db)

LOCAL STORAGE
- made changes
- refetch from db
- new version from db
- local changes out of dates
- show out of date message
- discard ( local changes deleted and replaced by new version)
- create new copy ( local changes deleted and make a copy and then replace the current chatbot from db )

SWITCH VERSION:
- fetch all version history
- when click one of them fetch into local storage
- if selected version is click published
- replace the publish_chatbot table for that chatbot
- keep the logic for that out of date version chatbot

Meeting notes:
- add the owner to chatbot, only the owner can edit and the ownership can be transfered
- add versioning to chabot changes

Notes:
---

Library for working with json to handle merge conflict, crdt etc:
- https://github.com/angus-c/just#just-diff-apply
- https://jsonjoy.com/libs/json-joy-js/json-crdt-patch

Example json diff changes
---

const chatbot = {
    ...,
  id: "b963c210-0790-4dbd-9f53-66be1932b051",
  type: "chatbot",
  images: [],
  version: 2,
};

const user1 = { ...chatbot, temperature: 0.8, version: 1 };
const user2 = { ...chatbot, temperature: 0.9, images: ["a"], version: 1 };
const user3 = { ...chatbot, temperature: 0.9, version: 2 };


const diffUser1 = diff(chatbot, user1);
console.log(diffUser1);

const diffUser2 = diff(chatbot, user2);
console.log(diffUser2);

const diffUser3 = diff(chatbot, user3);
console.log(diffUser3);

Example log outputs:

diffUser1 [
  { op: 'replace', path: [ 'temperature' ], value: 0.8 },
  { op: 'replace', path: [ 'version' ], value: 1 }
]

diffUser2 [
  { op: 'replace', path: [ 'temperature' ], value: 0.9 },
  { op: 'replace', path: [ 'version' ], value: 1 },
  { op: 'add', path: [ 'images', 0 ], value: 'a' }
]

diffUser3 
[ { op: 'replace', path: [ 'temperature' ], value: 0.9 } ]

Questions:
---

The challenge to solve multiple user editing the same chatbot.

1. **How do we know if the current user has the latest updated chatbot data since we are work offline first?**
Using versioning system, we can add field `version` in chatbot table it will be `Auto-incrementing version number`.

1. **How do we know which field is edited by user when their version number is behind from db?**
Check the version between user data and chatbot. If the version number is behind curent updated version number, then merged the latest version from db to users local storage data.

1. **How do we make sure that if we merged chatbot data from db user doesn't lose their local changes?**
Hemmm,,, 🤔.

1. **How do we merge if there are conflict?**
First, how to detect conflict?
I was thinking about branching strategy just like how git works. And how was it 😁? TBD.

# Possible solution

Versioning
---

Versioning Approach for handling concurrent updates to the JSON data in the "chatbot" table.

1. **Table Structure:**
Modify the "chatbot" table structure to include versioning information. The `version_number` column will keep track of the version number for each data update:

   CREATE TABLE chatbots (
       id UUID PRIMARY KEY,
       data JSONB,
       version_number SERIAL,
       owner_id UUID REFERENCES users(id),
   );

2. **Updating Data:**
When a user wants to update the JSON data, follow these steps:

- Retrieve the latest version of the JSON data for the chatbot.
- Allow the user to edit the data and create a new version.
- Store the new version of the JSON data along with the user who edited it and the incremented version number.

pseudocode:

   current_version = SELECT version_number FROM chatbot WHERE id = chatbot_id;
   new_data = user_edited_data;
   new_version_number = current_version + 1;

   INSERT INTO chatbot (id, data, version_number, edited_by)
   VALUES (chatbot_id, new_data, new_version_number, user_id);

3. **Conflict Resolution:**
In situations where concurrent updates occur and conflict, implement strategies to handle the conflicts. This could involve notifying users, displaying version differences, and providing options to merge or select specific changes.

4. **Retrieving Versions:**
To retrieve specific versions of the JSON data, we can query the "chatbot" table based on the `id` and `version_number` columns. This allows users to access historical versions and track changes over time.

5. **Audit Trail:**
Implement an audit trail to track the history of changes. We can create a separate table to store metadata about each version update, including the user who edited it, the timestamp, and any relevant notes.

   CREATE TABLE chatbot_version_history (
       id UUID REFERENCES chatbot(id),
       version_number INT,
       edited_by UUID REFERENCES users(id),
       timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       old JSONB,
       current
       PRIMARY KEY (id, version_number)
   );

6. **User Interface:**
Design a user interface that displays the history of versions, highlights differences between versions, and offers options for conflict resolution and merging changes.

Optimistic Concurrency Control (OCC)
---

The alternative strategies with Optimistic Concurrency Control (OCC). OCC is a technique that allows multiple users to edit the same data concurrently, and conflicts are detected and resolved when users attempt to commit their changes.

1. **Timestamp-Based Concurrency:**
Add a `last_edited_at` column of type `timestamptz` (timestamp with time zone) to the "chatbot" table. This column will store the timestamp of the last edit to the JSON data.

   ALTER TABLE chatbot
   ADD COLUMN last_edited_at timestamptz;

2. **Updating Data:**
When a user wants to update the JSON data, follow these steps:

- Retrieve the current `last_edited_at` timestamp for the chatbot.
- Allow the user to edit the data and create a new version.
- Include the retrieved `last_edited_at` timestamp in the update query's WHERE clause.
- Use the `RETURNING` clause to retrieve the number of rows affected by the update.

Here's an example using pseudocode:

   current_last_edited_at = SELECT last_edited_at FROM chatbot WHERE id = chatbot_id;

   IF current_last_edited_at == user_edited_last_edited_at:
       new_data = user_edited_data;
       updated_rows = UPDATE chatbot
       SET data = new_data,
           last_edited_at = now() -- Update timestamp to the current time
       WHERE id = chatbot_id AND last_edited_at = user_edited_last_edited_at
       RETURNING *;
   ELSE:
       -- Notify user about the conflict and request retry or merge

3. **Conflict Detection and Resolution:**
If the `UPDATE` query doesn't update any rows (i.e., `updated_rows` is zero), it indicates a conflict. In this case, we can notify the user about the conflict and provide options for retrying the update, merging changes, or resolving the conflict manually.

OCC relies on timestamps to detect conflicts. If two users edit the same data concurrently, the one who commits first will succeed, and the second will be detected as a conflict. The second user can then be notified and prompted to resolve the conflict.

More options to explore:
---

1. **Optimistic Concurrency Control (OCC):**
Use a timestamp or version number to detect conflicts during updates, as previously discussed. This step helps identify when concurrent updates have occurred.

2. **Operational Transformation (OT):**
Implement an OT mechanism that tracks individual changes made by different users to the JSON data. Instead of directly editing the JSON data, users apply operations (e.g., insert, delete, update) that represent their changes.

3. **Conflict Resolution and Merging:**
When concurrent updates are detected, use OT algorithms to merge the conflicting changes. OT can intelligently combine individual operations to create a coherent result that incorporates the changes made by different users.

4. **User Interface and Collaboration:**
Design a user interface that allows users to see the changes made by others and provides options for resolving conflicts. This could involve visualizing the changes, offering merging suggestions, and allowing users to manually intervene when necessary.

5. **Applying Merged Operations:**
After resolving conflicts, apply the merged operations to the JSON data to create the updated version.