A customer maintenance form in Uniface 10, part 11 - finding duplicates, merging customers and answering a GDPR request

Every customer database collects duplicates. The duplicate check from part 2 warns when a new customer looks like an existing one, and the import from part 10 skips such lines, but old data, typos and "Müller" vs. "Mueller" still get through. Sooner or later someone asks: "these two are the same company - can you make one out of them?".

The second request in this part comes from outside: a customer asks what data you store about them (GDPR Art. 15), or asks you to delete it (Art. 17).

Both have the same technical core. A customer is no longer one row, it is a row plus addresses, contact persons, notes, categories, history entries and follow-ups. Merging moves all of them; erasure removes all of them. Forget one table and you have either orphaned rows or personal data you believe is gone.

Step 1: find the problems

QUALITY_SVC.QUALITY_REPORT writes an HTML page with everything that looks wrong. It is one SQL statement, assembled from UNION ALL parts, each returning the same three columns: ID, name, finding.

SELECT c.CUSTOMER_ID, c.LAST_NAME || ', ' || c.FIRST_NAME, 'No billing address'
FROM CUSTOMER c
WHERE COALESCE(c.IS_ACTIVE, 1) = 1
  AND NOT EXISTS (SELECT 1 FROM CUSTOMER_ADDRESS a
                  WHERE a.CUSTOMER_ID = c.CUSTOMER_ID AND a.ADDRESS_TYPE = 'BILLING')
UNION ALL
SELECT c.CUSTOMER_ID, c.LAST_NAME || ', ' || c.FIRST_NAME, 'Neither e-mail nor phone'
FROM CUSTOMER c
WHERE COALESCE(c.IS_ACTIVE, 1) = 1
  AND TRIM(COALESCE(c.EMAIL, '')) = '' AND TRIM(COALESCE(c.PHONE, '')) = ''
UNION ALL
SELECT c.CUSTOMER_ID, c.LAST_NAME || ', ' || c.FIRST_NAME, 'Invalid German postal code ' || a.POSTAL_CODE
FROM CUSTOMER c JOIN CUSTOMER_ADDRESS a ON a.CUSTOMER_ID = c.CUSTOMER_ID
WHERE COALESCE(c.IS_ACTIVE, 1) = 1
  AND UPPER(COALESCE(a.COUNTRY, 'DE')) = 'DE'
  AND (LENGTH(a.POSTAL_CODE) <> 5 OR a.POSTAL_CODE GLOB '*[^0-9]*')
UNION ALL
SELECT c.CUSTOMER_ID, c.LAST_NAME || ', ' || c.FIRST_NAME, 'Same name as customer ' || d.CUSTOMER_ID
FROM CUSTOMER c JOIN CUSTOMER d
  ON d.CUSTOMER_ID <> c.CUSTOMER_ID
 AND LOWER(TRIM(d.LAST_NAME)) = LOWER(TRIM(c.LAST_NAME))
 AND LOWER(TRIM(d.FIRST_NAME)) = LOWER(TRIM(c.FIRST_NAME))
WHERE COALESCE(c.IS_ACTIVE, 1) = 1 AND COALESCE(d.IS_ACTIVE, 1) = 1
UNION ALL
SELECT ... 'Same e-mail as customer ' || d.CUSTOMER_ID ...

• GLOB '*[^0-9]*' is SQLite's way to say "contains a non-digit". There is no REGEXP without an extension.
• The self-join finds pairs. Each pair appears twice (A→B and B→A). For a report that is fine - each customer's row names the other one.
• Inactive customers are ignored. The soft delete from part 5 means "not relevant any more", and reporting problems in data nobody uses only creates noise.

In ProcScript the statement is built in pieces of at most five $concat arguments - see part 8 for why - and the result goes through the same HTML escaping as every other report.

The report does not fix anything. It gives a list of IDs to look at, and for the duplicates the next step is a merge.

Step 2: merge two customers

Preview first

Merging is not reversible, so there is a separate, read-only operation that says what will happen:

public operation MERGE_PREVIEW
params
    numeric pKeepId : IN
    numeric pDropId : IN
    string pText : OUT
    string pError : OUT
endparams
...
    pText = "Customer %%(pDropId) will be deleted. %%(vAddr) address(es), %%(vCont) contact person(s) and %%(vNotes) note(s) move to customer %%(pKeepId). Continue?"

The UI shows this text in an askmess and only calls MERGE_CUSTOMERS on "Yes". The service does not ask questions itself - services have no UI.

What MERGE_CUSTOMERS does, in order

1. Validate: both IDs given, not the same, both exist. The name of the customer that will disappear is read now, while it still exists, for the history.
2. Count what will move, for the summary.
3. Resolve the one conflict: only one contact person per customer may be the main contact (the contact service enforces that when a contact is saved). If the kept customer already has one, the moving contacts lose their flag first.
4. Move the child rows.
5. Merge categories without duplicates.
6. Fill gaps in the kept customer from the dropped one.
7. Delete the dropped customer and log the merge.

Steps 4 to 7 as code:

    call RUN_SQL("UPDATE CUSTOMER_ADDRESS SET CUSTOMER_ID = %%(pKeepId) WHERE CUSTOMER_ID = %%(pDropId)", pError)
    if ($status < 0)
        return -1
    endif
    call RUN_SQL("UPDATE CUSTOMER_CONTACT SET CUSTOMER_ID = %%(pKeepId) WHERE CUSTOMER_ID = %%(pDropId)", pError)
    ...
    call RUN_SQL("UPDATE CUSTOMER_NOTE SET CUSTOMER_ID = %%(pKeepId) WHERE CUSTOMER_ID = %%(pDropId)", pError)
    ...
    call RUN_SQL("UPDATE CUSTOMER_HISTORY SET CUSTOMER_ID = %%(pKeepId) WHERE CUSTOMER_ID = %%(pDropId)", pError)
    ...
    call RUN_SQL("UPDATE CUSTOMER_FOLLOWUP SET CUSTOMER_ID = %%(pKeepId) WHERE CUSTOMER_ID = %%(pDropId)", pError)
    ...
    call RUN_SQL("INSERT OR IGNORE INTO CUSTOMER_CATEGORY (CUSTOMER_ID, CATEGORY_CODE) SELECT %%(pKeepId), CATEGORY_CODE FROM CUSTOMER_CATEGORY WHERE CUSTOMER_ID = %%(pDropId)", pError)
    ...
    call RUN_SQL("DELETE FROM CUSTOMER_CATEGORY WHERE CUSTOMER_ID = %%(pDropId)", pError)
    ...
    call RUN_SQL("UPDATE CUSTOMER SET EMAIL = (SELECT d.EMAIL FROM CUSTOMER d WHERE d.CUSTOMER_ID = %%(pDropId)) WHERE CUSTOMER_ID = %%(pKeepId) AND TRIM(COALESCE(EMAIL, '')) = ''", pError)
    ...
    call RUN_SQL("DELETE FROM CUSTOMER WHERE CUSTOMER_ID = %%(pDropId)", pError)
    ...
    activate "HISTORY_SVC".LOG_EVENT(pKeepId, "MERGED", vDropName, vText, pUser, pError)

Remarks:

• Categories have a composite primary key (CUSTOMER_ID, CATEGORY_CODE). A plain UPDATE would fail with a constraint error when both customers are "VIP". INSERT OR IGNORE ... SELECT copies what is missing, then the old assignments are deleted.
• E-mail and phone are only taken over if the kept customer has none. The service never overwrites data that exists; the user chose which customer to keep for a reason.
• The history moves too. After the merge, the kept customer's history shows everything that ever happened to either of them, plus a MERGED entry naming the dropped customer ("17 Anna Müller").
• Follow-ups were added in a later stage. The merge was extended when the table appeared, and there is a test for it ("FOLLOWUP entries move when customers are merged"). This is exactly the kind of place where a new table silently breaks an old feature.
• RUN_SQL is a local entry that runs the statement and turns a negative status into a readable message. The whole merge runs in one transaction; the caller commits on success and rolls back on the first error.

The summary returned to the user:

Moved: 1 address(es), 1 contact person(s), 1 note(s), 1 history entries, 1 new category assignment(s).

Step 3: GDPR access - "what do you store about me?"

PRIVACY_SVC.EXPORT_PERSONAL_DATA produces one HTML page with everything about a customer. It does not build that page from scratch. The customer data sheet from REPORT_SVC already shows master data, addresses, contact persons, notes and categories - so the service writes that sheet, reads it back, cuts off the closing tags, appends the history and closes the page again:

    activate "REPORT_SVC".CUSTOMER_SHEET(pCustomerId, pFileName, pError)
    ...
    fileload pFileName, vHtml, "UTF-8"
    ...
    vBom = $string("")
    if (vHtml[1:1] = vBom)
        vLen = $length(vHtml) - 1
        vHtml = vHtml[2:vLen]
    endif
    vEnd = "</body></html>"
    vPos = $scan(vHtml, vEnd)
    if (vPos > 1)
        vCnt = vPos - 1
        vHtml = vHtml[1:vCnt]
    endif
    activate "HISTORY_SVC".LOAD_FOR_CUSTOMER(pCustomerId, vData, pError)
    ...
    vHtml = $concat(vHtml, "<p class='meta'>Personal data export according to Art. 15 GDPR.</p>", vNl, "</body></html>")
    lfiledump vHtml, pFileName, "UTF-8"

Reusing the sheet means that when a new section is added to the data sheet, the GDPR export gets it automatically. The BOM appears again here: fileload keeps it, and without removing it the rewritten file would start with two. The test "one complete HTML page" checks that the closing </body></html> comes after the appended GDPR note, i.e. that the cut-and-append really produced a single page. It does not count BOMs - that would be a cheap extra assertion.

The history matters for Art. 15: it contains old values - a previous address, a previous e-mail. Those are personal data too, and a sheet with only the current state would be incomplete.

Step 4: erasure - anonymize, don't delete

ANONYMIZE_CUSTOMER does not delete the customer row. It deletes everything that belongs to the person and overwrites what identifies them:

    call RUN_SQL("DELETE FROM CUSTOMER_ADDRESS WHERE CUSTOMER_ID = %%(pCustomerId)", pError)
    ...
    call RUN_SQL("DELETE FROM CUSTOMER_CONTACT WHERE CUSTOMER_ID = %%(pCustomerId)", pError)
    ...
    call RUN_SQL("DELETE FROM CUSTOMER_NOTE WHERE CUSTOMER_ID = %%(pCustomerId)", pError)
    ...
    call RUN_SQL("DELETE FROM CUSTOMER_FOLLOWUP WHERE CUSTOMER_ID = %%(pCustomerId)", pError)
    ...
    call RUN_SQL("DELETE FROM CUSTOMER_CATEGORY WHERE CUSTOMER_ID = %%(pCustomerId)", pError)
    ...
    call RUN_SQL("DELETE FROM CUSTOMER_HISTORY WHERE CUSTOMER_ID = %%(pCustomerId)", pError)
    ...
    call RUN_SQL("UPDATE CUSTOMER SET LAST_NAME = 'Anonymized', FIRST_NAME = '-', EMAIL = NULL, PHONE = NULL, IS_ACTIVE = 0, CHANGED_AT = datetime('now', 'localtime'), CHANGED_BY = %%(vUserLit), VERSION_NO = COALESCE(VERSION_NO, 0) + 1 WHERE CUSTOMER_ID = %%(pCustomerId)", pError)
    ...
    activate "HISTORY_SVC".LOG_EVENT(pCustomerId, "ANONYMIZED", vText, vText, pUser, pError)

Why keep the row?

• Other systems - invoices, orders, an ERP - may still refer to customer number 17. A hard delete would turn those references into dangling numbers; an anonymized row keeps them valid without identifying anyone.
• The history keeps exactly one entry: ANONYMIZED, with who did it and when. That is the evidence that the request was handled, and it contains no personal data of the customer.
• IS_ACTIVE = 0 takes the row out of every list, export and report, because all of them already filter on it.

The history is deleted before the ANONYMIZED event is written, not after - otherwise the evidence would be deleted too. The test "history keeps only the anonymization" checks that order.

What anonymization here does not reach

This is the section a reader with GDPR experience will look for, so here it is:

• Backups. The daily backups from part 10 still contain the customer until they rotate out. That is usually acceptable if it is documented and backups are not restored casually, but it has to be written down.
• Exported files. Every CSV, vCard, JSON or HTML file that was ever written to the export folder is outside the database. The app does not know where copies went.
• The customer number and CREATED_AT remain. On their own they do not identify a person; combined with an external system that still has the name, they do.
• Free text elsewhere. If someone wrote "see Anna Müller" into a note of a different customer, that note is not touched. Only the customer's own rows are cleaned.
• Other tables added later. Every new customer-related table has to be added to ANONYMIZE_CUSTOMER, MERGE_CUSTOMERS and the export. Follow-ups were the first case. A test per table is the only protection I have found so far.

Whether "anonymized row with the old number" satisfies Art. 17 in a specific situation is a legal question, not a technical one. The technical part is making sure that what you say is gone is gone.

Tests

PASS: MERGE preview names what moves
PASS: MERGE with itself is rejected
PASS: MERGE with an unknown customer is rejected
PASS: MERGE succeeds
PASS: MERGE deletes the second customer
PASS: MERGE moves the addresses
PASS: MERGE keeps only one main contact
PASS: MERGE moves the contact persons
PASS: MERGE moves the notes
PASS: MERGE unites the categories without duplicates
PASS: MERGE fills empty e-mail and phone
PASS: MERGE writes a history entry
PASS: MERGE moves the old history
PASS: QUALITY report is written
PASS: QUALITY missing billing address and contact data
PASS: QUALITY invalid German postal code
PASS: QUALITY same name and same e-mail
PASS: QUALITY ignores inactive customers
PASS: PRIVACY export contains data and history
PASS: PRIVACY export is one complete HTML page
PASS: PRIVACY export for an unknown customer is reported
PASS: PRIVACY anonymize succeeds
PASS: PRIVACY name, e-mail and phone are removed
PASS: PRIVACY addresses, notes and categories are deleted
PASS: PRIVACY history keeps only the anonymization
PASS: PRIVACY anonymize an unknown customer is reported
PASS: FOLLOWUP entries move when customers are merged
PASS: FOLLOWUP entries are removed when anonymizing

The merge test builds two customers who both have a main contact and both have the category VIP, plus one category only the dropped customer has. That single setup covers the two conflicts (main contact, composite key) and the normal case (one new category) at once.

Known weak spots

• No optimistic locking in the merge. Part 4 protects the form against overwriting someone else's change via VERSION_NO. The merge does not check versions; if someone edits the dropped customer while the merge runs, that edit is lost with the row. For a single-user or small-team desktop app I accept it; it should be a version check.
• The merge cannot be undone. The history says what happened, but there is no "unmerge". The preview and the backup are the safety net.
• The duplicate report is exact-match only. "Müller" and "Mueller", or "Anna" and "Anne", are not found. Fuzzy matching (normalizing umlauts, Levenshtein distance) is possible in ProcScript, but not in one SQL statement on SQLite without extensions.

Takeaways

List every table that hangs off a customer, in one place. Merge, erasure and export all need the same list. When a new table arrives, all three must change, and a test per table is what tells you when you forgot one.

Move history on merge, delete it on erasure, and write the event afterwards. The order decides whether the evidence survives.

Anonymize instead of delete when other systems hold your IDs, and write down what anonymization does not reach: backups, exported files, free text.

Reuse your reports. The GDPR export is the data sheet plus the history, not a second implementation that drifts.

Next part: 252 tests in one run, a query that hung the test process, and a tools form painted in the IDE.