WRD history does not record deletes or cascaded effects, and changesets have no commit order

[RichardHoekstra]

With keephistorydays set on a type, updates and closes are recorded in wrd.changes, but two things are missing:

1. Deleting an entity (WRDType.delete / DeleteEntities) leaves no trace. The entity simply disappears from wrd.entities, and later reads of other entities' history can no longer reconstruct references to it (test_wrd_history.ts already documents this as "can't reconstruct after a delete").

2. The effects PostgreSQL cascades from a delete are not recorded either: attachments and links to the deleted entity are deleted (leftentity/rightentity ON DELETE CASCADE) and settings of other entities referring to it are dropped (entity_settings.setting ON DELETE CASCADE). A type that keeps history therefore silently loses data without a history row.

Separately, wrd.changesets has no commit-ordered position. changesets.id is a sequence value allocated when the changeset is created and creationdate is the time of the first change, so neither tells a reader "everything up to here has been committed": a lower id can still appear after a higher one. That makes it impossible for exports, synchronisations or dashboards to reliably resume from a known point.

Proposal (patch with tests ready)

• Record a delete change before an entity is deleted, carrying the full old entity, and record a change for every cascaded effect on a type that keeps history. A new wrd.changes.cause column refers to the recorded deletion that caused the effect. GetChanges() reports changetype delete and the cause.
• Number changesets in commit order: wrd.schemas.historyhead advances by one per changeset just before commit (the row lock is held only during the commit) and the number is stored in wrd.changesets.historyseqnr. A rollback leaves nothing behind. WRDSchemaType.getHistoryHead() and ListChangesets().historyseqnr expose it.
• No new cost when no type in a schema keeps history.

A second, smaller patch fills two fields that already exist but that the JS API leaves empty: wrd.changesets.entity/userdata (the acting user, as the HareScript API already records) and wrd.changes.source, with a setChangeSource() / SetWRDChangeSource() to describe what caused the changes in a work (an import, a task, a request). GetChanges() then returns source as its documentation already says it does.

Happy to split or rename things; I mainly want to check this is in scope before opening the PR.

Patch on a branch, not yet a PR: master...RichardHoekstra:platform:wrd-history-observability

[unilynx]

glanced through it because I was mostly curious about how the historyhead was managed, added a comment about that. no need to address that yet

about deleteEntity/closeEntity - my opinion was always, if you want to preserve history, use closeEntity and set up automatic deletion in the wrdschema.xml after X days.

I'm not opposed in principle to having deleteEntity record the entity into history.

@blexrob any thoughts about whether we should start recording deletions too ?

if we go down this route we shouldn't forget to update the history view dialog too to properly show deletions

[RichardHoekstra]

Thanks — real bug, fixed.

Ordered locking. onBeforeCommit advanced the heads in changeset creation order, so
two works touching the same schemas could take those row locks in opposite orders and
deadlock exactly as you describe. Both the TypeScript and the HareScript side now sort by
schema id.

Test. Two CodeContexts, each writing to both schemas but visiting them in opposite
order, with a barrier so both are in the commit phase before either finishes; it then
checks that each head advanced by one per changeset and that whoever committed first comes
first in both schemas. 40P01 on 3 of 3 runs without the sorting, 3 of 3 green with it.

It also caught a second bug of mine: I wrote actionBy from the audit context straight
into changesets.entity, which is a foreign key, so a context still naming a deleted
entity aborted the whole transaction at COMMIT. It's looked up in the insert now.

close vs delete. Fair position, and I'm not trying to change the advice. One thing for
that discussion: the purge deleteClosedAfter performs is itself an unrecorded delete with
the same cascade, so the history of other entities ends up pointing at a guid that
resolves to nothing either way, just X days later. Which makes me wonder whether the
cleanup task is the better place to fix this rather than delete(). I left it out of this
patch on purpose, along with DeleteAllEntities.

Worth noting the two halves are independent in the code, so if the delete question needs
time I can offer the commit-ordered head as its own PR first. That's the part I'd defend
regardless: it's what lets a sync, export or search index resume, where today it either
re-compares everything or listens to wrd:type.* events and misses whatever happened while
it was down. (Background, since you may wonder: I'm after a change API where you propose,
inspect the effects, apply, and then verify from the database what actually happened.
Agents are my interest, but importers and bulk edits have the same shape.)

History dialog. Covered — GetChanges() reports changetype: "delete" and the changes
screen shows those rows with the trash icon.

The fixes are a commit on top of the two you looked at, so your comment stays anchored.

[unilynx]

During tests I'll sometimes wipe content for a type using this route:

Should that now also generate history or not? Or should it be an option ?

(when I personally use it, I want all history gone too and the operation to just finish as fast as possible. not sure if anyone else using this option would ever want to preserve history?)

[RichardHoekstra]

Good questions, and I don't fully have the answer. I find it hard to judge when you'd want to store the full entity being deleted, and when you only want the minimal fact ("type wiped at this time by this user").

And a delete that doesn't really delete anything gets you into trouble as soon as you're supposed to remove information (GDPR and the like).

I think there are at least three flavours of delete, with different expectations:

• A domain event. The world changed: who, when, why?
• A retention purge. deleteClosedAfter, a GDPR erasure request. It defeats the purpose if personal data stays behind in the history.
• An administrative wipe. Testing, major DB changes.

For your case, the wipe, I'd say: no history, and taking the type's existing history with it is what I'd expect from a reset too. At most a single marker ("type wiped at t by u"), so a sync resuming from the history head knows it has to start over. So I wouldn't make it an option.

The harder question is the first two. Would it make sense to keep this PR to what's uncontroversial (the commit-ordered history head, and recording the fact of a delete) and leave storing the deleted entity's content for a separate discussion?