`UNIQUE constraint failed: resources.key` - likely cross-project key collision in `dataframer.py`

[teslajoy]

Encountered the following error during g3t push --overwrite --skip_validate inside the fhir_import_export job:

sqlite3.IntegrityError: UNIQUE constraint failed: resources.key

in log

/root/venv/lib/python3.12/site-packages/gen3_tracker/meta/dataframer.py\\\", line 161, in bulk_insert_data\\n    new_cursor = self.cursor.executemany(sql, _iterate(_resources=resources))\\n                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\\nsqlite3.IntegrityError: UNIQUE constraint failed: resources.key\\n\"]}"}

This likely stems from how resources.key is constructed in dataframer.py, currently as:

key = f"{resourceType}/{id}"

I double-checked the mint-ids and they aren't duplicates on my side, so it seems like the issue comes from the same resourceType/id showing up across different program/projects? this is surprising to me. Since the key doesn’t include program or project context, they end up colliding in the shared SQLite resources table.

Recommended fix:

Update the function signature and logic in bulk_insert_data() to namespace keys:

def bulk_insert_data(self, resources, program="", project=""):
    rows = []
    for res in resources:
        key = f"{program}/{project}/{res.get('resourceType')}/{res.get('id')}" <- HERE 
        res['key'] = key  # or append to rows for bulk insert
        rows.append((key, json.dumps(res)))

This should help prevent collisions across programs or projects and keep the resource keys properly isolated. I didn’t want to open a pull request before checking in - happy to contribute code here. If there’s already a re-seeding or namespacing step in place that I’ve missed/overlooked, feel free to point it out!

[teslajoy]

testing local db:

import os
import json
import sqlite3
from collections import Counter
from glob import glob

meta_dir = "META"  
sqlite_path = ".g3t/work/local_fhir.db"

def load_resources(meta_dir):
    resources = []
    for file_path in glob(os.path.join(meta_dir, "*.ndjson")):
        with open(file_path) as f:
            for line in f:
                try:
                    res = json.loads(line.strip())
                    resources.append(res)
                except json.JSONDecodeError:
                    print(f"could not parse line in {file_path}")
    return resources

def check_keys(resources):
    keys = []
    missing = []
    for r in resources:
        if "resourceType" in r and "id" in r:
            keys.append(f"{r['resourceType']}/{r['id']}")
        else:
            missing.append(r)
    counts = Counter(keys)
    dupes = [k for k, v in counts.items() if v > 1]

    print(f"total resources loaded from ndjson: {len(resources)}")
    if dupes:
        print("duplicates in resource list:")
        for d in dupes:
            print(f"  {d}")
    else:
        print("no duplicates in resource list")

    if missing:
        print(f"{len(missing)} resources missing resourceType or id")
    return keys

def check_sqlite_db(sqlite_path):
    if not os.path.exists(sqlite_path):
        print(f"sqlite file not found: {sqlite_path}")
        return []

    conn = sqlite3.connect(sqlite_path)
    cursor = conn.cursor()
    cursor.execute("SELECT key FROM resources")
    db_keys = [row[0] for row in cursor.fetchall()]
    conn.close()

    print(f"total keys in local db: {len(db_keys)}")
    db_counts = Counter(db_keys)
    db_dupes = [k for k, v in db_counts.items() if v > 1]
    if db_dupes:
        print("duplicates in local db:")
        for d in db_dupes:
            print(f"  {d}")
    else:
        print("no duplicates in local db")
    return db_keys

if __name__ == "__main__":
    resources = load_resources(meta_dir)
    ndjson_keys = check_keys(resources)
    db_keys = check_sqlite_db(sqlite_path)

I get:

total resources loaded from ndjson: 622
no duplicates in resource list
total keys in local db: 622
no duplicates in local db

I don't see any None either... ¯_(ツ)_/¯

[quinnwai]

I think you're onto something about duplicate IDs loaded into the database, but I don't think it's duplicate IDs across projects, as this bulk_insert_data function only loads the existing project into the LocalFHIRDatabase ( fhir_import_export.py code). What it seems to be is that a set of data already exists in the GRIP database for this project has duplicate IDs.

To confirm this is the case, could you try a g3t projects empty --project_id cbds-HTAN_BForePC --confirm empty to remove all existing GRIP data and then re-push?