Database defined UNIQUE constraint causes conflicts

[leorudczenko]

We believe we have found a bug involving MerginMaps mobile Input.

Summary

Our database/GeoPackage structure requires the use of the UNIQUE constraint on certain fields, specifically for primary/foreign keys. Adding a record to a table in Mergin Maps mobile Input that has a UNIQUE constraint on a field will cause a subsequent sync to fail with a conflict if the data on the server have been changed by QGIS in the meantime.

To Reproduce

We found that this can be replicated in a minimal way using the following steps:

• Create a new project in QGIS using the MerginMaps plugin option New basic QGIS project
• Modify the Survey_points.gpkg by adding a UNIQUE constraint to any field
• Sync the project in QGIS
• Download the project in MerginMaps Input - (At this point, QGIS and MerginMaps Input will have the same project in the same version)
• Add a new point to the Survey layer in QGIS
• Sync the new QGIS point
• Add a new point to the Survey layer in MerginMaps Input
• Sync the new MerginMaps Input point
• At this point, the conflict will have occurred within MerginMaps Input, you can sync the QGIS project to verify the conflict GeoPackage file

Example Project

We have created a public MerginMaps project with these same steps applied. In this instance, the UNIQUE constraint has been applied to the photo field in the Survey_points table. You can view the project here.

It is worth noting that this issue only occurs when syncing QGIS first, and then syncing MerginMaps Input. If you sync MerginMaps Input first, and then you sync QGIS, the conflict issue does not occur. We have also tried to replicate it between 2 QGIS instances, and 2 MerginMaps Input instances, but neither of those scenarios encountered the conflict issue either.

Software Versions

We have successfully replicated this issue on multiple devices that are using the most up-to-date versions of MerginMaps Input and the MerginMaps QGIS plugin.

• QGIS 3.32.3 for Windows
• Mergin Maps QGIS Plugin 2023.4.1
• Mergin Maps mobile Input 2.4.1 for Android

[tomasMizera]

Hello @leorudczenko, thank you for such a detailed report! This is likely happening because of how geodiff (our library for merging changes) works.
Do you mean conflict in data, like two features have the same entered value in one field despite marked UNIQUE, or conflict in terms of creating a copy of the geopackage in a separate file called edit_conflict?

[leorudczenko]

Hey @tomasMizera, thanks for the quick reply 🙂 I mean a conflict in terms of creating a copy of the geopackage in a separate file called edit_conflict. There is an example of this happening in the linked example project.

I have tried using geodiff to manually see the differences. It is treating the new data as an UPDATE rather than a new INSERT statement:

{
  "geodiff": [
    {
      "changes": [
        {
          "column": 0,
          "old": 1
        },
        {
          "column": 1,
          "new": "R1AAAREPAAABAQAAAALYHbPRNf/A+IJ2heqZWkE=",
          "old": "R1AAAREPAAABAQAAAIL3i1d8J/TAmPsdPsKUW0E="
        },
        {
          "column": 2,
          "new": "2023-12-01T13:30:38.097",
          "old": "2023-12-01T13:28:46.017"
        },
        {
          "column": 3,
          "new": "This point was made in MerginMaps Input",
          "old": "This point was made in QGIS"
        },
        {
          "column": 4,
          "new": "PXL_20231017_154620713.jpg",
          "old": "20231121_093845.jpg"
        }
      ],
      "table": "Survey_points",
      "type": "update"
    }
  ]
}

[tomasMizera]

I will forward this to our support team and we will get back to you.

[raherin]

Hi @leorudczenko, can you check if the problem persists if using QGIS 3.34? There was a bug in previous releases that caused issues when merging features with a 'unique' constraint, which has been fixed in the 3.34 update.

[leorudczenko]

Hey @raherin, I've just followed the same steps to recreate this bug in QGIS 3.34, and I'm getting the same problem as I did in QGIS 3.32 unfortunately.

[raherin]

I've tested this in the latest version of the plugin (2024.1) on QGIS 3.34.2 and v2.5.0 of the mobile app on Android and the conflict file is not created. I made an example project that is publicly available: support/unique-test

The unique constraint is set in the photo field.

[leorudczenko]

Hey @raherin, thanks for taking the time to look at this. However, I've just checked the project you've provided for your test and the UNIQUE constraint has not been applied to the GeoPackage file, it has been applied to the QGIS project. For this error to occur, the UNIQUE constraint needs to be set on a database level.

The way we did this on the example project was using these steps:

• Use sqlite3 .dump to generate an SQL file of the GeoPackage file Survey_points.gpkg
• Add the UNIQUE constraint to any of the fields in the table Survey_ponts within the newly generated SQL file
• Delete the original Survey_points.gpkg file and sync mergin (This will mean the project has no GeoPackage file)
• Use the modified SQL file to recreate the Survey_points.gpkg file with the added UNIQUE constraint
• Add the new Survey_points.gpkg file to the project folder and sync mergin (This will add back the GeoPackage file)
• Now you have the example project with a database level UNIQUE constraint

We found that you have to sync the project in-between deleting and re-creating the GeoPackage file because mergin does not recognise database constraint changes with geodiff.

Alternatively, you could just create a new mergin project from scratch and ensure the UNIQUE constraint is on any of the columns at a database level. This will also trigger the bug in the same way, but we wanted to demonstrate how this will occur on the example mergin project too.

I have tried to recreate this bug again using the versions you specified, latest version of the plugin (2024.1) on QGIS 3.34.2 and v2.5.0 of the mobile app on Android, and I can confirm the bug still exists.

[uclaros]

Hi @leorudczenko, I could still not replicate the conflict generation by following your extended instructions, unless I intentionally set a duplicate value to both the point created in QGIS and the one created in Mergin Maps for the unique field, which is the properly expected behavior.
Are you absolutely positive that you are not trying to store duplicate values on a unique field?

Please also bear in mind that changing the database schema after the project has been created is something that is currently not supported and can lead to conflicts.
https://merginmaps.com/docs/manage/synchronisation/#conflicted-copy
https://merginmaps.com/docs/manage/deploy-new-project/

[leorudczenko]

Hey @uclaros, yes I am 100% sure that I am not storing duplicate values on a unique field. I have been able to recreate this bug consistently across 40+ Mergin Maps projects of varying structures.

Thank you for the information about database schema changes.

I have created a new minimum project to replicate this issue without modifying database schemas, but the issue still persists as it has always been. But, here is the SQL script I am using to generate my fresh GeoPackage file without any schema modifications in-between:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE gpkg_spatial_ref_sys (srs_name TEXT NOT NULL,srs_id INTEGER NOT NULL PRIMARY KEY,organization TEXT NOT NULL,organization_coordsys_id INTEGER NOT NULL,definition  TEXT NOT NULL,description TEXT);
INSERT INTO gpkg_spatial_ref_sys VALUES('Undefined Cartesian SRS',-1,'NONE',-1,'undefined','undefined Cartesian coordinate reference system');
INSERT INTO gpkg_spatial_ref_sys VALUES('Undefined geographic SRS',0,'NONE',0,'undefined','undefined geographic coordinate reference system');
INSERT INTO gpkg_spatial_ref_sys VALUES('WGS 84 geodetic',4326,'EPSG',4326,'GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AXIS["Latitude",NORTH],AXIS["Longitude",EAST],AUTHORITY["EPSG","4326"]]','longitude/latitude coordinates in decimal degrees on the WGS 84 spheroid');
CREATE TABLE gpkg_contents (table_name TEXT NOT NULL PRIMARY KEY,data_type TEXT NOT NULL,identifier TEXT UNIQUE,description TEXT DEFAULT '',last_change DATETIME NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),min_x DOUBLE, min_y DOUBLE,max_x DOUBLE, max_y DOUBLE,srs_id INTEGER,CONSTRAINT fk_gc_r_srs_id FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys(srs_id));
INSERT INTO gpkg_contents VALUES('locality_point','features','locality_point','','2024-02-29T15:43:12.515Z',NULL,NULL,NULL,NULL,4326);
CREATE TABLE gpkg_ogr_contents(table_name TEXT NOT NULL PRIMARY KEY,feature_count INTEGER DEFAULT NULL);
INSERT INTO gpkg_ogr_contents VALUES('locality_point',NULL);
CREATE TABLE gpkg_geometry_columns (table_name TEXT NOT NULL,column_name TEXT NOT NULL,geometry_type_name TEXT NOT NULL,srs_id INTEGER NOT NULL,z TINYINT NOT NULL,m TINYINT NOT NULL,CONSTRAINT pk_geom_cols PRIMARY KEY (table_name, column_name),CONSTRAINT uk_gc_table_name UNIQUE (table_name),CONSTRAINT fk_gc_tn FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name),CONSTRAINT fk_gc_srs FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys (srs_id));
INSERT INTO gpkg_geometry_columns VALUES('locality_point','geometry','POINT',4326,0,0);
CREATE TABLE gpkg_tile_matrix_set (table_name TEXT NOT NULL PRIMARY KEY,srs_id INTEGER NOT NULL,min_x DOUBLE NOT NULL,min_y DOUBLE NOT NULL,max_x DOUBLE NOT NULL,max_y DOUBLE NOT NULL,CONSTRAINT fk_gtms_table_name FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name),CONSTRAINT fk_gtms_srs FOREIGN KEY (srs_id) REFERENCES gpkg_spatial_ref_sys (srs_id));
CREATE TABLE gpkg_tile_matrix (table_name TEXT NOT NULL,zoom_level INTEGER NOT NULL,matrix_width INTEGER NOT NULL,matrix_height INTEGER NOT NULL,tile_width INTEGER NOT NULL,tile_height INTEGER NOT NULL,pixel_x_size DOUBLE NOT NULL,pixel_y_size DOUBLE NOT NULL,CONSTRAINT pk_ttm PRIMARY KEY (table_name, zoom_level),CONSTRAINT fk_tmm_table_name FOREIGN KEY (table_name) REFERENCES gpkg_contents(table_name));
CREATE TABLE IF NOT EXISTS "locality_point" ( "fid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "geometry" POINT, "name" TEXT UNIQUE);
CREATE TABLE gpkg_extensions (table_name TEXT,column_name TEXT,extension_name TEXT NOT NULL,definition TEXT NOT NULL,scope TEXT NOT NULL,CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name));
INSERT INTO gpkg_extensions VALUES('locality_point','geometry','gpkg_rtree_index','http://www.geopackage.org/spec120/#extension_rtree','write-only');
PRAGMA writable_schema=ON;
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','rtree_locality_point_geometry','rtree_locality_point_geometry',0,'CREATE VIRTUAL TABLE "rtree_locality_point_geometry" USING rtree(id, minx, maxx, miny, maxy)');
CREATE TABLE IF NOT EXISTS "rtree_locality_point_geometry_rowid"(rowid INTEGER PRIMARY KEY,nodeno);
CREATE TABLE IF NOT EXISTS "rtree_locality_point_geometry_node"(nodeno INTEGER PRIMARY KEY,data);
INSERT INTO rtree_locality_point_geometry_node VALUES(1,X'00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000');
CREATE TABLE IF NOT EXISTS "rtree_locality_point_geometry_parent"(nodeno INTEGER PRIMARY KEY,parentnode);
DELETE FROM sqlite_sequence;
CREATE TRIGGER 'gpkg_tile_matrix_zoom_level_insert' BEFORE INSERT ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint: zoom_level cannot be less than 0') WHERE (NEW.zoom_level < 0); END;
CREATE TRIGGER 'gpkg_tile_matrix_zoom_level_update' BEFORE UPDATE of zoom_level ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint: zoom_level cannot be less than 0') WHERE (NEW.zoom_level < 0); END;
CREATE TRIGGER 'gpkg_tile_matrix_matrix_width_insert' BEFORE INSERT ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint: matrix_width cannot be less than 1') WHERE (NEW.matrix_width < 1); END;
CREATE TRIGGER 'gpkg_tile_matrix_matrix_width_update' BEFORE UPDATE OF matrix_width ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint: matrix_width cannot be less than 1') WHERE (NEW.matrix_width < 1); END;
CREATE TRIGGER 'gpkg_tile_matrix_matrix_height_insert' BEFORE INSERT ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint: matrix_height cannot be less than 1') WHERE (NEW.matrix_height < 1); END;
CREATE TRIGGER 'gpkg_tile_matrix_matrix_height_update' BEFORE UPDATE OF matrix_height ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint: matrix_height cannot be less than 1') WHERE (NEW.matrix_height < 1); END;
CREATE TRIGGER 'gpkg_tile_matrix_pixel_x_size_insert' BEFORE INSERT ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint: pixel_x_size must be greater than 0') WHERE NOT (NEW.pixel_x_size > 0); END;
CREATE TRIGGER 'gpkg_tile_matrix_pixel_x_size_update' BEFORE UPDATE OF pixel_x_size ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint: pixel_x_size must be greater than 0') WHERE NOT (NEW.pixel_x_size > 0); END;
CREATE TRIGGER 'gpkg_tile_matrix_pixel_y_size_insert' BEFORE INSERT ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'insert on table ''gpkg_tile_matrix'' violates constraint: pixel_y_size must be greater than 0') WHERE NOT (NEW.pixel_y_size > 0); END;
CREATE TRIGGER 'gpkg_tile_matrix_pixel_y_size_update' BEFORE UPDATE OF pixel_y_size ON 'gpkg_tile_matrix' FOR EACH ROW BEGIN SELECT RAISE(ABORT, 'update on table ''gpkg_tile_matrix'' violates constraint: pixel_y_size must be greater than 0') WHERE NOT (NEW.pixel_y_size > 0); END;
CREATE TRIGGER "rtree_locality_point_geometry_insert" AFTER INSERT ON "locality_point" WHEN (new."geometry" NOT NULL AND NOT ST_IsEmpty(NEW."geometry")) BEGIN INSERT OR REPLACE INTO "rtree_locality_point_geometry" VALUES (NEW."fid",ST_MinX(NEW."geometry"), ST_MaxX(NEW."geometry"),ST_MinY(NEW."geometry"), ST_MaxY(NEW."geometry")); END;
CREATE TRIGGER "rtree_locality_point_geometry_update1" AFTER UPDATE OF "geometry" ON "locality_point" WHEN OLD."fid" = NEW."fid" AND (NEW."geometry" NOTNULL AND NOT ST_IsEmpty(NEW."geometry")) BEGIN INSERT OR REPLACE INTO "rtree_locality_point_geometry" VALUES (NEW."fid",ST_MinX(NEW."geometry"), ST_MaxX(NEW."geometry"),ST_MinY(NEW."geometry"), ST_MaxY(NEW."geometry")); END;
CREATE TRIGGER "rtree_locality_point_geometry_update2" AFTER UPDATE OF "geometry" ON "locality_point" WHEN OLD."fid" = NEW."fid" AND (NEW."geometry" ISNULL OR ST_IsEmpty(NEW."geometry")) BEGIN DELETE FROM "rtree_locality_point_geometry" WHERE id = OLD."fid"; END;
CREATE TRIGGER "rtree_locality_point_geometry_update3" AFTER UPDATE ON "locality_point" WHEN OLD."fid" != NEW."fid" AND (NEW."geometry" NOTNULL AND NOT ST_IsEmpty(NEW."geometry")) BEGIN DELETE FROM "rtree_locality_point_geometry" WHERE id = OLD."fid"; INSERT OR REPLACE INTO "rtree_locality_point_geometry" VALUES (NEW."fid",ST_MinX(NEW."geometry"), ST_MaxX(NEW."geometry"),ST_MinY(NEW."geometry"), ST_MaxY(NEW."geometry")); END;
CREATE TRIGGER "rtree_locality_point_geometry_update4" AFTER UPDATE ON "locality_point" WHEN OLD."fid" != NEW."fid" AND (NEW."geometry" ISNULL OR ST_IsEmpty(NEW."geometry")) BEGIN DELETE FROM "rtree_locality_point_geometry" WHERE id IN (OLD."fid", NEW."fid"); END;
CREATE TRIGGER "rtree_locality_point_geometry_delete" AFTER DELETE ON "locality_point" WHEN old."geometry" NOT NULL BEGIN DELETE FROM "rtree_locality_point_geometry" WHERE id = OLD."fid"; END;
CREATE TRIGGER "trigger_insert_feature_count_locality_point" AFTER INSERT ON "locality_point" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count + 1 WHERE lower(table_name) = lower('locality_point'); END;
CREATE TRIGGER "trigger_delete_feature_count_locality_point" AFTER DELETE ON "locality_point" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count - 1 WHERE lower(table_name) = lower('locality_point'); END;
PRAGMA writable_schema=OFF;
COMMIT;

And here is the list of steps I follow exactly to recreate the bug:

• I generate a fresh GeoPackage file using the SQL script which contains a single UNIQUE constrained text field called name on the table locality_point
• I load this GeoPackage file into a fresh and empty QGIS project
• I save the project with the single layer locality_point from the GeoPackage
• I add this project to Mergin Maps using the Use current QGIS project as is option
• I sync the project in QGIS and in MerginMaps Input here so they are on the same state, a single layer with no features
• I then create a point in the QGIS project with the text field value set to leorud_qgis
• I sync the QGIS project
• I then create a point in the MerginMaps Input project BEFORE syncing with the text field value set to leorud_mergin
• If you have done this correctly, your MerginMaps Input project will currently only have the point with the name leorud_mergin. If your MerginMaps Input project also shows the point with the name leorud_qgis, then you have done this incorrectly.
• I sync the MerginMaps Input project, at which point the leorud_mergin point disappears and a conflict GeoPackage is created

When comparing the actual GeoPackage file with the conflict GeoPackage file using geodiff, it shows that it is treating both of the INSERT statements as a single UPDATE statement, which is incorrect. You can do it yourself or see the JSON output from my comparison here:

{
  "geodiff": [
    {
      "changes": [
        {
          "column": 0,
          "old": 1
        },
        {
          "column": 1,
          "new": "R1AAAeYQAAABAQAAAGYVNy2RfPK/E2w8EO98SkA=",
          "old": "R1AAAeYQAAABAQAAAD9yXJ/8+/G/HvOdojl9SkA="
        },
        {
          "column": 2,
          "new": "leo_mergin",
          "old": "leo_qgis"
        }
      ],
      "table": "locality_point",
      "type": "update"
    }
  ]
}

Therefore, this could be an issue with geodiff, but I am unsure where the issue lies, as it only happens in this very specific interaction between creating a point in QGIS, and then creating a different point in MerginMaps Input.

Here is a link to a public MerginMaps project where all of these files and examples came from: https://app.merginmaps.com/projects/SIGMALite/conflict-bug-min-2/tree

[saberraz]

Hi @leorudczenko
I am not able to replicate the issue:

• Create a new project in QGIS using the MerginMaps plugin option New basic QGIS project
• Modify the Survey_points.gpkg by adding a UNIQUE constraint to any field
• Sync the project in QGIS
• Download the project in MerginMaps Input - (At this point, QGIS and MerginMaps Input will have the same project in the same version)
  I cloned project from @raherin
  https://app.merginmaps.com/projects/saber/uq-test/tree

• Add a new point to the Survey layer in QGIS
• Sync the new QGIS point

See: https://app.merginmaps.com/projects/saber/uq-test/history?version_id=v2

• Add a new point to the Survey layer in MerginMaps Input
• Sync the new MerginMaps Input point
• At this point, the conflict will have occurred within MerginMaps Input, you can sync the QGIS project to verify the conflict GeoPackage file

See: https://app.merginmaps.com/projects/saber/uq-test/history?version_id=v3

As you can see the process did not create an edit conflict.

[leorudczenko]

Hey @saberraz, thanks for taking the time to look over this. Unfortunately, I've checked the project links you sent and it looks like the UNIQUE constraint has not been applied on a database level. I tried to clarify this important detail again here. It must be done on the database directly for the bug to occur.

I have tried to recreate this myself again using the instructions which I explained in more detail here. I can confirm that I am still able to reproduce this bug with the exact same steps. Here is a link to the public Mergin Maps project: https://app.merginmaps.com/projects/SIGMALite/conflict-bug-min-20240502/tree

These are the software versions I was using to reproduce it this time:

• QGIS 3.36.2 for Windows
• Mergin Maps QGIS Plugin 2024.1
• Mergin Maps mobile Input 2.5.0 for Android

This is the output when using geodiff when comparing the actual GeoPackage file to the conflict GeoPackage file:

{
  "geodiff": [
    {
      "changes": [
        {
          "column": 0,
          "old": 1
        },
        {
          "column": 1,
          "new": "R1AAAeYQAAABAQAAALUd/JjDfPK/hpM3rO98SkA=",
          "old": "R1AAAeYQAAABAQAAAGwT2eol9/G/teN/L3OASkA="
        },
        {
          "column": 2,
          "new": "leorud_mergin",
          "old": "leorud_qgis"
        }
      ],
      "table": "locality_point",
      "type": "update"
    }
  ]
}

As you can see, geodiff still encounters the exact same issue I explained here.

Both the .diff and .json files generated by geodiff are included in the linked Mergin Maps project.