SQLite - bulk insert compare

In progress, will expand the details out.

Create table with generated uniqueness row hash

DROP TABLE IF EXISTS agreements;

CREATE TABLE IF NOT EXISTS agreements (
    file_id TEXT NOT NULL,
    dag TEXT NOT NULL,
    file_code TEXT NOT NULL,
    org_name TEXT,
    uploaded datetime NOT NULL,
    expired datetime,
    version int NOT NULL DEFAULT 0,
    resources TEXT,
    compare_id TEXT GENERATED ALWAYS AS (hex(dag || file_code || uploaded || coalesce(org_name, '') || coalesce(expired, '') || coalesce(resources, ''))) STORED,
    UNIQUE(compare_id)
);

Insert all records, updates, duplicates, etc

insert into agreements (file_id, dag, file_code, uploaded, expired, org_name, version, resources)
values ('previous', 'org-1', 'abc', '2026-01-01', null, null, 0, '1, 2'),
       ('previous', 'org-2', 'abc', '2026-01-01', null, null, 0, null),
       ('previous', 'org-3', 'abc', '2026-01-01', null, null, 0, null),
       ('current', 'org-1', 'abc', '2026-01-01', null, null, 1, null),
       ('current', 'org-1', 'def', '2026-01-01', null, null, 1, null),
       ('current', 'org-2', 'abc', '2026-01-01', '2026-10-01', null, 1, null),
       ('current', 'org-4', 'def', '2026-01-01', null, null, 1, null)
ON CONFLICT DO NOTHING;

Cleanup duplicates by removing first set items

DELETE
FROM agreements
where compare_id IN (select a.compare_id FROM agreements a inner join agreements b on a.dag = b.dag and a.file_code = b.file_code where a.version < b.version);