ADR 0019: Record relationships and cardinality-based controls

Status: Accepted for the local prototype
Date: 2026-11-02
Extends: ADR 0018

Storage and operations

Store each connection once in sys_relationships, with a UUID, workspace, definition identity, two object/record identities and creation metadata. sys_record_identities is populated atomically during normal record creation. Composite foreign keys prevent cross-workspace or missing registered endpoints. The registry supplements rather than replaces per-object tables; endpoint lookup also verifies the actual row. ADR 0022 subsequently adds deferred constraint triggers tying business rows and registrations together. SQL maintenance must use the application layer; direct deletion of business rows is not a supported lifecycle operation.

Undirected symmetric edges use a canonical endpoint order, have one shared control and count neighbours in either direction. Disjoint endpoint roles retain their configured orientation, labels and cardinalities. Custom definitions now have exactly one object pair under ADR 0021. Reject self-links and duplicates. Primary edges have at most one direct parent globally across the definition’s allowed pairs; reject cycles. General/custom cycles remain permitted. Current module ownership and fixed Primary roles follow ADRs 0021/0024/0025; record-link operations do not activate modules.

get_record_relationships returns applicable fields, unchanged edge references and a dossier projection. Each dossier row retains its edge ID separately from its opposite endpoint record ID. Standard Type/Status/Category IDs and selected choice labels are read from current endpoint data, batched per object type; absent standard fields return null/empty values. Historical journal snapshots remain unchanged. search_relationship_candidates searches eligible active objects with server pagination, excludes the current record and does not grant permission. create_record and update_record accept optional relationship-field replacements, each with definition identity/version, side and target identities. Omitted fields are retained. Saving fields and relationships is one transaction with one change ID. Verify the record version and every submitted definition version; reject the whole transaction on validation/journal failure. Relationship-only no-ops do not change versions or history. Changes update both endpoint records’ versions and modification metadata; a scalar-plus-link save increments its own record once.

Rule activation and validation

Custom rules begin as drafts. ADR 0021 makes General always active and gates Primary by workspace Projects activation; ADR 0024 restricts its parent roles to the supplied Contact/Project/Case definitions. activate_relationship_type requires Designer/Administrator configuration rights and preflights the current graph before atomically activating/versioning/journaling the definition. Editing an active rule preflights the proposed final state, including permitted pairs and minima/maxima. Used direction is fixed once relationship history exists. Availability and rule activation are independent; inactive definitions retain history/links and accept no new links.

Under ADR 0022, record writes validate changed endpoints and newly required connections; activation/configuration retains full-graph preflight. Replacing a mandatory single partner is atomic. For initially empty models with mutually required partners, bootstrap with optional rules, create/connect records and then tighten the rules; multi-record batch creation is not implemented. The workspace write barrier serializes mutations/preflight in that workspace while readers share locks and other workspaces can progress. Schema changes retain an exclusive global barrier; this is not a production load claim.

Content reads/writes use object, field and record permissions under ADR 0026. Designers can configure/activate rules but receive no record content from preflight errors. Relationships, Primary context and assignments grant no content rights. Inactive objects remain readable; links cannot be added to or changed on their records. Preserve retained references while saving unrelated active records.

Journal and interface

Each added/removed edge creates one central relationship journal entry with immutable before/after endpoint/definition snapshots. sys_relationship_journal_endpoints projects that same event into both records’ histories, including after unlinking. This avoids duplicated events and preserves readable historical Keywords/labels. Definition journals exclude relationship events; the Administrator instance journal includes them. Future permanent deletion must address registry/edge references and Administrator-only retained history explicitly under R-040 and ADR 0029; other authorized users receive only content-free deletion notices. No permanent record deletion/cascade is implemented here.

The Primary right side uses the configured level chain under ADR 0024. The object-first header actions described below select General or an eligible specific relationship from every record tab; applying an existing-record selection returns to Details for saving. General links and subordinate Primary records appear in the Dossier instead of duplicate Details tables. The Details relationship section contains only applicable explicitly defined custom relationships, as a labeled InputGroup for sides with at most one target, using the same control pattern as Primary association: read-only Keyword, Choose/Change, Open and Remove. Sides allowing multiple targets use their own standard Table with Keyword, object type and row actions; a Plus button adds targets. Single-value fields stay visible when empty and expose required/error state on the control. Unexpected excess retained targets remain visible in a table rather than being silently hidden. Rows open the target; Remove changes only the draft. Tables retain every existing target, use stable column widths, truncation with full text titles and shared pagination above ten targets. Each custom relationship table is a standard Collapsible. Populated or required connections start expanded; empty optional connections start collapsed. The table title retains its count, and Add remains available while collapsed. Applying a picker expands its table. Empty expanded tables keep a compact empty row. Empty custom sections are omitted. Required markers and inactive-state help replace repeated optional/cardinality prose. Read-only records and inactive definitions retain readable links without mutation actions. A separate standard dialog provides RadioGroup single selection or checkbox multiple selection, a server-side object filter/search before pagination and a Selected view spanning result pages. Only opening a picker loads candidates. Applying changes updates the record draft; persistence still requires saving the record. Closing a changed picker asks to discard its local selection, preserving other record edits. Picker drafts also participate in the existing SPA navigation guard. These are relationship projections, not duplicate scalar fields or physical business columns. Save/Reset, tab drafts, unsaved navigation guards and keyboard saving apply to both scalar fields and relationship drafts. The Dossier tab reuses RecordDataTable, RecordTableToolbar, ColumnMenu and pagination. Columns are relationship type, object type, Keyword, Type, Status and Category. An alphabetically labeled object-type filter uses stable object UUIDs and combines with localized search/sort on the full fetched dossier before paging; selection is page-scoped. A row, Keyword link or action opens the opposite record; object navigation and Copy record ID use the target identity rather than the edge ID. Opening the tab refreshes saved endpoint values while retaining any Details draft. The table has the same header, action and horizontal scrolling treatment as other record tables. Journal detail dialogs show structured endpoint snapshots. ADR 0020 subsequently adds the hierarchy tree and derived ancestor display. Object-level required Primary associations and ancestor levels are implemented under ADR 0024.

Validation

Disposable PostgreSQL integration tests cover directed/undirected links, symmetric no-ops, endpoint/workspace checks, minima, both sides of 1:1, Primary uniqueness/cycles, concurrent versions, definition conflicts, atomic replacement/creation, late-journal rollback, shared endpoint history, activation preflight and current initialization. The fictional seed applies the versioned plan documented in Demo data through common operations; repeated connection-plan application retains existing links and adds no duplicates.

Record header actions and linked creation

Saved, editable records provide Link with… / Verknüpfen mit… and New linked record / Neue Verknüpfung dropdowns. The common API/MCP list_record_link_options operation returns active, readable object types and eligible relationship sides. It evaluates source/target conditions and their field permissions, current source edit rights, Projects Primary levels and create capability. An object with only General is a direct menu item. Otherwise its submenu starts with the existing General definition label and then its eligible existing side labels. Labels/definitions are never renamed for this presentation. Objects without matching records remain available for creation; record candidates remain independently permission-filtered.

Link with… opens the shared picker restricted to the selected object type. Its selection remains a normal record draft; saving updates the chosen side and preserves other links. New linked record opens the normal target-object creation page with an explicit source context and a retained inverse-side selection. The source must be saved first. The seeded relationship is read-only until the new record has been saved; unrelated target inputs remain editable. Conditional target fields must be completed before saving. Cancel returns to the same-workspace source record without creating anything.

create_record.linkFrom carries the source object/record identity and expected record version, relationship identity/version and original side. The shared backend requires Edit on the source as well as normal target creation rights, inverts directed/disjoint or symmetric sides, merges the source reference without replacing source edges and validates the complete result with the existing graph checks. Record contents, link, registry identity, endpoint versions and property/edge journals commit together with one change ID. Stale versions, conditions, rights, cardinality failures and cross-workspace attempts roll back the entire creation. No new storage or migration is required. Source-link creation never silently replaces an occupied one-target side; the corresponding creation choice is unavailable. Existing-link selection can still explicitly replace a one-target selection.

The record-header Link with and New linked record menus omit only the supplied Organization/Contact-person assignment. That association remains available through its dedicated contact fields and the shared API/MCP relationship operations. Filtering uses its semantic relationship UUID binding, never labels, technical names or a blanket module exclusion. General, Identities, distribution membership and other associations remain available for Contacts; custom relationship types retain their exact side labels and action rights, including custom labels matching a module label. General-only targets are direct menu entries. Linked Contact creation still selects the Contact kind before opening its fixed form.