Page Comparison

DB design: 

 CREATE TABLE dedup (
catchment_id varchar,
health_id1 varchar,
health_id2 varchar,
rule_name varchar,
rule_value varchar,
created_at timeuuid,
PRIMARY KEY (catchment_id, health_id1, health_id2, rule_name, rule_value));

Example:

Feed - p1 (create), p2 (create), p3 (create), p1 (updated occupation), p1 (updated nid2), p2 (voided, merged-with p3), h4 (create), p5 (create). pn represents HID.
All have same NID, nid1 but different catchments, c1, c2, c3, c4, c5. Only, h4, h5 have same phone #. c1 - 101112, c2 - 202122, c3 - 303133, so on.
Rules: same nid and same phone # are dedups. Dedups cannot be resolved for just one rule, but for all.

...

Searches all patients by phone # ph1ph2. No matching patient. No action taken.

...

 Searches all patients by phone # ph3. No matching patient. No action taken.

Scenario 4: 

#1 select hid2 from dedup where catchment in (1011, 101112) and hid1 = h1. 

...

#4 Dedup db looks same as in scenario 3.

Note: If catchment is updated, it will be similar to scenario 3.

Scenario 5: 

Similar to scenario 4. 

Dedup db:

Scenario 6:

Similar to scenario 4. Dedup table becomes empty.

Scenario 8:

Similar to scenario 2. 

Dedup table:

Scenario 9:

Similar to scenario 2. 

Dedup table:

Scenario 10:

p3 and p4 are no more dupes. 

#1. select catchment from patient where hid in (h3, h4)

#2. delete from dedup where catchment in (..) and hid1 in (h3, h4) and hid2 in (h3, h4) [in won't work. so run separate queries in batch]

Dedup table:

Find duplicates by catchment:

select * from dedup where catchment = 102030.

Then sort the result by timeuuid (time component only) in-memory and return.