Spec: Versioned Resource Tree Structure

Core to the data platform is the requirement to store "resources" (data entities) that are versioned and can express hierarchies.
We model this via Slowly Changing Dimension Type 2 (SCD2) tables: every change appends a new row with explicit validity windows.

Logical Model

• A resource represents a logical entity (patient, trial, imaging study, …).
• Each resource has a payload stored in a dedicated SQLModel table to preserve schema guarantees.
• Parent/child relationships form a tree (parent_identifier column). No parent change cascades are materialised; history is reconstructed via timestamps.

Table Schemas

resources

field	type	comment
identifier	string	natural key for the resource
created_at	ts	creation time of the resource

resource_versions

field	type	comment
resource_identifier	string	FK → resources.identifier, part of PK
valid_from	timestamp	start of validity window, part of PK
valid_to	timestamp	nullable; NULL marks the active row
payload_table	string	table name of the payload
payload_uid	string	identifier inside the payload table
parent_identifier	string	parent resource identifier (nullable for roots)
is_deleted	boolean	soft delete flag
created_at	timestamp	when this particular row was inserted

Payload tables keep a uid/id column so we can re-hydrate payloads by payload_uid.

Operations

The API supports the following write operations (see vxp_schemas):

• ResourceCreateRequest
• ResourcePayloadChangeRequest
• ResourceParentChangeRequest
• ResourceDeleteRequest
• ResourceUndeleteRequest

All writes follow the same pattern:

1. Close the currently active row (valid_to = now()) if it exists.
2. Append a new row with valid_from = now() and the updated fields.
3. Parents are not rewritten when children change; consumers derive historical children by querying resource_versions with matching parent_identifier and overlapping validity windows.

Reading Data

• Current state: filter resource_versions by valid_to IS NULL and is_deleted = false.
• Historical snapshot: filter by valid_from <= :as_of and (valid_to IS NULL OR valid_to > :as_of).
• Children at :t: same filter with an additional parent_identifier = :id.

Design Notes

• No JSON blobs inside resource_versions; payload schemas remain normalised.
• No transaction manager or integer version counters. The SCD2 window is the single source of truth.
• Soft deletes simply close the active window without inserting a follow-up row.
• Hard deletes are still an open question and would require out-of-band cleanup of payload rows.