Documentation source

Hierarchical Entities — make parent_id a first-class primitive

Turn the underused entities.parent_id column into a fully surfaced tree primitive (breadcrumbs, children panel, create-child, detach-on-delete, agent tools, container view, tree nav) with a single server-action safety gate across UI, agents, MCP, API-key, and Inngest callers. Graph stays in entity_relations. Type-level inheritance deferred.

# Hierarchical Entities

> **Work folder:** `documents/work/2026-04-21-hierarchical-entities/` — spec, plan, decisions, review, notes, follow-ups.
> **Revision 2** incorporates the multi-model review at `documents/reviews/2026-04-21-hierarchical-entities.md` — 4 CRITICAL + 8 HIGH consensus findings addressed; deferred items in `followups.md`.

## Problem

`entities.parent_id` has existed since `20260328000007_remote_schema` — self-referencing FK, `ON DELETE CASCADE`, composite indexes, server action, hook, API path. None of it is visible in the product.

- **Ember** can't model "Company as container."
- **DOC'S** can't model `Patient → Protocol → Modality → Visit` cleanly.
- **Agents** don't see hierarchy — child-entity work has no parent context.
- **Views + nav** can't leverage hierarchy (no container surface, no tree nav, no breadcrumbs).

## Solution

Make `entities.parent_id` a first-class, UI-visible, agent-accessible tree primitive. Single atomic detach-or-cascade delete, ancestor/descendant queries, minimal agent tool surface, container surface that reuses the children panel, sidebar tree nav source, and **server-action-layer gating** on destructive operations so UI / agents / MCP / API-key / Inngest all hit the same safety path. Graph relations stay in `entity_relations`. Type-level schema inheritance deferred.

## Knowledge-graph principles honored

See `documents/work/2026-04-21-hierarchical-entities/notes-kg-best-practices.md` for full rationale; key revised principles post-review:

1. Tree and graph separate (`parent_id` vs `entity_relations`).
2. Adjacency list + recursive CTE.
3. Typed edges with metadata — Cypher-compatible.
4. **Cycle prevention with serialization** — DB trigger + advisory-lock + app walk.
5. Depth caps on every recursive query.
6. **Explicit-GUC tenant filter** in base + recursive step (not `auth.uid()`/RLS — fails for service-role callers).
7. **Server-action gate on destructive operations** — `deleteEntity` helper requires `cascadeConfirmed` token; UI, agents, MCP, API-keys all route through it.
8. **Atomic multi-step operations** — detach+delete is one SQL function.
9. **Reparent audit trail** — `session_event` + activity row.
10. **Ancestor context preserves prompt caching** — injected as separate `user`-role message at history start, not in the `system` string.
11. Bidirectional graph traversal preserved.
12. Temporal graph via `entity_responses` + `session_events` (now covering reparents).
13. Semantic layer left open for pgvector.

## Design

### Ownership boundaries

| Concern | Owner |
|---|---|
| Tree / containment / breadcrumbs | `entities.parent_id` |
| Destructive cascade safety | `deleteEntity` server action (single gate) |
| Arbitrary typed references | `entity_relations` |
| Type-level containment constraint | `entity_types.config.allowedChildTypes` |
| Type-level ancestor-context flag | `entity_types.config.inheritAncestorContext` |
| Type-level delete default (Phase 2) | `entity_types.config.cascadeDefault` |
| Type-level schema inheritance | **DEFERRED** |

### Data model

**No new tables.** Migration adds:

- **Trigger** `BEFORE INSERT OR UPDATE OF parent_id ON entities` — takes `pg_advisory_xact_lock(hashtext(tenant_id))`, walks ancestors via recursive CTE with tenant filter in BOTH base and recursive step, rejects self-parent, cycles, or depth > 10.
- **RPC functions** (SECURITY INVOKER, explicit `p_tenant_id` arg): `entity_ancestors`, `entity_descendants`, `entity_descendant_counts_by_type`, `entity_nearest_ancestor_of_type` (returns `SETOF entities` — null-safe).
- **Atomic delete function** `delete_entity_with_mode(p_entity_id, p_tenant_id, p_mode)` — takes the advisory lock + `FOR UPDATE` on the parent, optionally detaches children, deletes. Eliminates the detach-then-delete TOCTOU race.

Canonical recursive CTE pattern (required in every RPC):

```sql
WITH RECURSIVE chain AS (
  SELECT e.id, e.parent_id, 1 AS depth
  FROM entities e
  WHERE e.id = $base AND e.tenant_id = $tenant
  UNION ALL
  SELECT e.id, e.parent_id, c.depth + 1
  FROM entities e
  JOIN chain c ON e.id = c.parent_id AND e.tenant_id = $tenant
  WHERE c.depth < LEAST($max_depth, 10)
)
SELECT * FROM chain;
```

### `deleteEntity` — the single safety gate

`features/entities/server/delete-entity.ts` is the only file that calls `delete_entity_with_mode`. Every caller (UI, agent tool, API route, MCP, Inngest) routes through it.

Steps:
1. `requireAuth()` + tenant + `requirePermission('entities.*.delete')`.
2. Load descendant counts.
3. If descendants exist AND `mode !== 'detach-children'` AND `cascadeConfirmed !== true` → reject `CASCADE_NOT_CONFIRMED`.
4. If cascade: verify permission on every descendant type (rejects `PARTIAL_PERMISSION_CASCADE`).
5. Call atomic SQL function.
6. Fire `entity.deleted` + `entity.children_detached` analytics.
7. `revalidateTag('entity-{id}')` + parent's children tag.

### Reparent

Server action validates cycle + allowedChildTypes + permission on entity AND destination parent (`entities.own.update` on both — not `team.update`). Fires `entity.reparented` session event + activity + analytics; invalidates caches.

### Agent tools — reduced surface

- **New:** `getDescendants`.
- **Extend:** `getEntity` (`includeAncestors`), `searchEntities` (`parentId`, `ancestorId`), `updateEntity` (`parentId`).
- **Harden:** `deleteEntity` requires `cascadeConfirmed` + subtree permission check.

### Ancestor context — prompt-cache-safe

Injected as a separate `user`-role message at the START of conversation history, not concatenated into the `system` string. System prompt stays entity-independent so the cached prefix is reusable across entities. Content filtered by per-type `ancestorContextFields` (stable-field whitelist) and `excludeFromAncestorContext` (PHI-safe denylist). Acceptance: `cache_read_input_tokens > 0` on turn 2 after an ancestor field edit on turn 1.

### UI

- **Breadcrumbs** on entity detail with inline Move button (icon-only, `aria-label`) — discoverability on the trail, not hidden in a kebab.
- **Children panel** is the single source of truth for children rendering; Phase 2 container surface mounts this same component.
- **Create-child** via extended inline form.
- **Delete dialog** is one atomic call — "Detach children, then delete" (autofocused in Phase 1) vs "Delete everything," each one REST call with `cascadeConfirmed=true`.
- **Realtime** — entity-level Supabase Realtime channel includes `parent_id`; subscribers invalidate ancestors/children on change.
- **Container surface (Phase 2)** — auto-mounts for types with `allowedChildTypes`; renders parent hero + `<EntityChildrenPanel>`.
- **Entity-tree nav source (Phase 2)** — WAI-ARIA tree pattern from day one (`role="tree"`, `role="treeitem"`, `aria-expanded`, `aria-level`, arrow-key keyboard nav). Lazy-expand. Tenant-scoped realtime subscription.
- **Admin Children section (Phase 1)** — `allowedChildTypes` (server-validates every slug), `inheritAncestorContext`, `ancestorContextFields`, `excludeFromAncestorContext`. `cascadeDefault` in Phase 2.

### Cache + realtime

- `reparentEntity` + `deleteEntity` call `revalidateTag('entity-{id}')` + both parents' children tags.
- React Query mutation invalidates `entity-ancestors`, `child-entities`, `entity-descendant-counts`.
- Supabase Realtime entity channel includes `parent_id`; nav sources subscribe at tenant scope.

### Analytics

New events in `features/analytics/events.ts`: `entity.reparented`, `entity.child_created`, `entity.children_detached`, `entity.tree_nav_expanded`. Zod-validated.

### Persona callouts

- **DOC'S provider** — full protocol tree on detail page; ancestor context injected into child-entity chat; inline Move for stray Visits.
- **Ember account manager** — Company container view (Phase 2 auto-mount); single-click atomic detach+delete on churn.
- **PE analyst** — secondary beneficiary via sidebar tree nav for portfolio browsing.
- **Agent** — reads ancestor context from history start; uses `getDescendants` for subtree walks; reparents via `updateEntity({parentId})` under the same gate as humans.

## Trade-offs

- **ON DELETE CASCADE kept at DB; every caller gated at the server-action layer.** Moves the "UI is the only path" assumption to a real server-action contract that all callers honor.
- **Advisory lock serializes within-tenant reparents.** Slight write contention for data-integrity guarantee.
- **Ancestor context as separate user-role message.** Less intuitive in raw prompt dumps; preserves prompt caching.
- **Container surface mounts the children panel** — one rendering path, no drift.
- **1 new agent tool + 3 extensions.** Narrower surface.
- **Admin Children section in Phase 1.** Makes `allowedChildTypes` actually settable; `cascadeDefault` stays in Phase 2 because it's polish, not essential.

## Acceptance criteria

- [ ] pgTAP: self-parent, direct + indirect cycle, depth 11, null parent, concurrent reparent race, service-role cross-tenant, atomic delete TOCTOU — all covered.
- [ ] Every RPC + trigger filters `tenant_id` via explicit GUC in base + recursive step.
- [ ] `deleteEntity` server action rejects cascade without `cascadeConfirmed`; rejects partial-permission cascade; atomic detach+delete verified.
- [ ] `reparentEntity` validates cycle + `allowedChildTypes` + permission on entity AND destination; audit log + session_event + analytics + cache revalidation all fire.
- [ ] Breadcrumbs + inline Move button render; picker filtered by destination's `allowedChildTypes`.
- [ ] Children panel renders when children or `allowedChildTypes` set; grouped by type; type-filtered add.
- [ ] Delete dialog single-call flow; detach autofocused Phase 1.
- [ ] Admin Children section — `allowedChildTypes` server-validates; unknown slug rejected; ancestor-context fields saved; cache tag invalidated.
- [ ] Agent tools: `getDescendants` + 3 extensions tested + permission-gated; `deleteEntity` cascade gate works via tool call.
- [ ] Ancestor context: separate user-role message; system stays stable; `cache_read_input_tokens > 0` verified on turn 2.
- [ ] Cache + realtime: reparent/delete invalidate both RSC cache and React Query; realtime event propagates `parent_id` changes across tabs.
- [ ] Analytics events fire + Zod-validated.
- [ ] Tree nav (Phase 2) passes WAI-ARIA tree pattern via axe + manual keyboard test.
- [ ] All components ≤ 200 lines.
- [ ] Visual QA PASS desktop + mobile.
- [ ] Docs updated: `entity-system.mdx`, `navigation.mdx`, `view-system.mdx`; changelog entry.
- [ ] Thin-harness clean — no product slugs in platform.

## Non-goals

- Type-level schema inheritance
- Multiple parents per entity
- Ordered children / drag-to-reorder (drag-to-reparent is a follow-up)
- Subtree-scoped permissions
- Graph algorithms
- Subtree clone / export
- Auto-reparent of children when parent's type changes
- Soft-delete / tombstones (follow-up; important for DOC'S long-term)
- Per-field `excludeFromAncestorContext: true` (Phase 1 supports per-type list; per-field flag follow-up)
- FTS ancestor-path enrichment (follow-up)
- `ltree` / GiST alternative (follow-up; benchmark before Phase 2 scales)
- `cascadeDefault` per-type (Phase 2 only)

# Hierarchical Entities > **Work folder:** `documents/work/2026-04-21-hierarchical-entities/` — spec, plan, decisions, review, notes, follow-ups. > **Revision 2** incorporates the multi-model review at `documents/reviews/2026-04-21-hierarchical-entities.md` — 4 CRITICAL + 8 HIGH consensus findings addressed; deferred items in `followups.md`. ## Problem `entities.parent_id` has existed since `20260328000007_remote_schema` — self-referencing FK, `ON DELETE CASCADE`, composite indexes, server action, hook, API path. None of it is visible in the product. - **Ember** can't model "Company as container." - **DOC'S** can't model `Patient → Protocol → Modality → Visit` cleanly. - **Agents** don't see hierarchy — child-entity work has no parent context. - **Views + nav** can't leverage hierarchy (no container surface, no tree nav, no breadcrumbs). ## Solution Make `entities.parent_id` a first-class, UI-visible, agent-accessible tree primitive. Single atomic detach-or-cascade delete, ancestor/descendant queries, minimal agent tool surface, container surface that reuses the children panel, sidebar tree nav source, and **server-action-layer gating** on destructive operations so UI / agents / MCP / API-key / Inngest all hit the same safety path. Graph relations stay in `entity_relations`. Type-level schema inheritance deferred. ## Knowledge-graph principles honored See `documents/work/2026-04-21-hierarchical-entities/notes-kg-best-practices.md` for full rationale; key revised principles post-review: 1. Tree and graph separate (`parent_id` vs `entity_relations`). 2. Adjacency list + recursive CTE. 3. Typed edges with metadata — Cypher-compatible. 4. **Cycle prevention with serialization** — DB trigger + advisory-lock + app walk. 5. Depth caps on every recursive query. 6. **Explicit-GUC tenant filter** in base + recursive step (not `auth.uid()`/RLS — fails for service-role callers). 7. **Server-action gate on destructive operations** — `deleteEntity` helper requires `cascadeConfirmed` token; UI, agents, MCP, API-keys all route through it. 8. **Atomic multi-step operations** — detach+delete is one SQL function. 9. **Reparent audit trail** — `session_event` + activity row. 10. **Ancestor context preserves prompt caching** — injected as separate `user`-role message at history start, not in the `system` string. 11. Bidirectional graph traversal preserved. 12. Temporal graph via `entity_responses` + `session_events` (now covering reparents). 13. Semantic layer left open for pgvector. ## Design ### Ownership boundaries | Concern | Owner | |---|---| | Tree / containment / breadcrumbs | `entities.parent_id` | | Destructive cascade safety | `deleteEntity` server action (single gate) | | Arbitrary typed references | `entity_relations` | | Type-level containment constraint | `entity_types.config.allowedChildTypes` | | Type-level ancestor-context flag | `entity_types.config.inheritAncestorContext` | | Type-level delete default (Phase 2) | `entity_types.config.cascadeDefault` | | Type-level schema inheritance | **DEFERRED** | ### Data model **No new tables.** Migration adds: - **Trigger** `BEFORE INSERT OR UPDATE OF parent_id ON entities` — takes `pg_advisory_xact_lock(hashtext(tenant_id))`, walks ancestors via recursive CTE with tenant filter in BOTH base and recursive step, rejects self-parent, cycles, or depth > 10. - **RPC functions** (SECURITY INVOKER, explicit `p_tenant_id` arg): `entity_ancestors`, `entity_descendants`, `entity_descendant_counts_by_type`, `entity_nearest_ancestor_of_type` (returns `SETOF entities` — null-safe). - **Atomic delete function** `delete_entity_with_mode(p_entity_id, p_tenant_id, p_mode)` — takes the advisory lock + `FOR UPDATE` on the parent, optionally detaches children, deletes. Eliminates the detach-then-delete TOCTOU race. Canonical recursive CTE pattern (required in every RPC): ```sql WITH RECURSIVE chain AS ( SELECT e.id, e.parent_id, 1 AS depth FROM entities e WHERE e.id = $base AND e.tenant_id = $tenant UNION ALL SELECT e.id, e.parent_id, c.depth + 1 FROM entities e JOIN chain c ON e.id = c.parent_id AND e.tenant_id = $tenant WHERE c.depth < LEAST($max_depth, 10) ) SELECT * FROM chain; ``` ### `deleteEntity` — the single safety gate `features/entities/server/delete-entity.ts` is the only file that calls `delete_entity_with_mode`. Every caller (UI, agent tool, API route, MCP, Inngest) routes through it. Steps: 1. `requireAuth()` + tenant + `requirePermission('entities.*.delete')`. 2. Load descendant counts. 3. If descendants exist AND `mode !== 'detach-children'` AND `cascadeConfirmed !== true` → reject `CASCADE_NOT_CONFIRMED`. 4. If cascade: verify permission on every descendant type (rejects `PARTIAL_PERMISSION_CASCADE`). 5. Call atomic SQL function. 6. Fire `entity.deleted` + `entity.children_detached` analytics. 7. `revalidateTag('entity-{id}')` + parent's children tag. ### Reparent Server action validates cycle + allowedChildTypes + permission on entity AND destination parent (`entities.own.update` on both — not `team.update`). Fires `entity.reparented` session event + activity + analytics; invalidates caches. ### Agent tools — reduced surface - **New:** `getDescendants`. - **Extend:** `getEntity` (`includeAncestors`), `searchEntities` (`parentId`, `ancestorId`), `updateEntity` (`parentId`). - **Harden:** `deleteEntity` requires `cascadeConfirmed` + subtree permission check. ### Ancestor context — prompt-cache-safe Injected as a separate `user`-role message at the START of conversation history, not concatenated into the `system` string. System prompt stays entity-independent so the cached prefix is reusable across entities. Content filtered by per-type `ancestorContextFields` (stable-field whitelist) and `excludeFromAncestorContext` (PHI-safe denylist). Acceptance: `cache_read_input_tokens > 0` on turn 2 after an ancestor field edit on turn 1. ### UI - **Breadcrumbs** on entity detail with inline Move button (icon-only, `aria-label`) — discoverability on the trail, not hidden in a kebab. - **Children panel** is the single source of truth for children rendering; Phase 2 container surface mounts this same component. - **Create-child** via extended inline form. - **Delete dialog** is one atomic call — "Detach children, then delete" (autofocused in Phase 1) vs "Delete everything," each one REST call with `cascadeConfirmed=true`. - **Realtime** — entity-level Supabase Realtime channel includes `parent_id`; subscribers invalidate ancestors/children on change. - **Container surface (Phase 2)** — auto-mounts for types with `allowedChildTypes`; renders parent hero + `<EntityChildrenPanel>`. - **Entity-tree nav source (Phase 2)** — WAI-ARIA tree pattern from day one (`role="tree"`, `role="treeitem"`, `aria-expanded`, `aria-level`, arrow-key keyboard nav). Lazy-expand. Tenant-scoped realtime subscription. - **Admin Children section (Phase 1)** — `allowedChildTypes` (server-validates every slug), `inheritAncestorContext`, `ancestorContextFields`, `excludeFromAncestorContext`. `cascadeDefault` in Phase 2. ### Cache + realtime - `reparentEntity` + `deleteEntity` call `revalidateTag('entity-{id}')` + both parents' children tags. - React Query mutation invalidates `entity-ancestors`, `child-entities`, `entity-descendant-counts`. - Supabase Realtime entity channel includes `parent_id`; nav sources subscribe at tenant scope. ### Analytics New events in `features/analytics/events.ts`: `entity.reparented`, `entity.child_created`, `entity.children_detached`, `entity.tree_nav_expanded`. Zod-validated. ### Persona callouts - **DOC'S provider** — full protocol tree on detail page; ancestor context injected into child-entity chat; inline Move for stray Visits. - **Ember account manager** — Company container view (Phase 2 auto-mount); single-click atomic detach+delete on churn. - **PE analyst** — secondary beneficiary via sidebar tree nav for portfolio browsing. - **Agent** — reads ancestor context from history start; uses `getDescendants` for subtree walks; reparents via `updateEntity({parentId})` under the same gate as humans. ## Trade-offs - **ON DELETE CASCADE kept at DB; every caller gated at the server-action layer.** Moves the "UI is the only path" assumption to a real server-action contract that all callers honor. - **Advisory lock serializes within-tenant reparents.** Slight write contention for data-integrity guarantee. - **Ancestor context as separate user-role message.** Less intuitive in raw prompt dumps; preserves prompt caching. - **Container surface mounts the children panel** — one rendering path, no drift. - **1 new agent tool + 3 extensions.** Narrower surface. - **Admin Children section in Phase 1.** Makes `allowedChildTypes` actually settable; `cascadeDefault` stays in Phase 2 because it's polish, not essential. ## Acceptance criteria - [ ] pgTAP: self-parent, direct + indirect cycle, depth 11, null parent, concurrent reparent race, service-role cross-tenant, atomic delete TOCTOU — all covered. - [ ] Every RPC + trigger filters `tenant_id` via explicit GUC in base + recursive step. - [ ] `deleteEntity` server action rejects cascade without `cascadeConfirmed`; rejects partial-permission cascade; atomic detach+delete verified. - [ ] `reparentEntity` validates cycle + `allowedChildTypes` + permission on entity AND destination; audit log + session_event + analytics + cache revalidation all fire. - [ ] Breadcrumbs + inline Move button render; picker filtered by destination's `allowedChildTypes`. - [ ] Children panel renders when children or `allowedChildTypes` set; grouped by type; type-filtered add. - [ ] Delete dialog single-call flow; detach autofocused Phase 1. - [ ] Admin Children section — `allowedChildTypes` server-validates; unknown slug rejected; ancestor-context fields saved; cache tag invalidated. - [ ] Agent tools: `getDescendants` + 3 extensions tested + permission-gated; `deleteEntity` cascade gate works via tool call. - [ ] Ancestor context: separate user-role message; system stays stable; `cache_read_input_tokens > 0` verified on turn 2. - [ ] Cache + realtime: reparent/delete invalidate both RSC cache and React Query; realtime event propagates `parent_id` changes across tabs. - [ ] Analytics events fire + Zod-validated. - [ ] Tree nav (Phase 2) passes WAI-ARIA tree pattern via axe + manual keyboard test. - [ ] All components ≤ 200 lines. - [ ] Visual QA PASS desktop + mobile. - [ ] Docs updated: `entity-system.mdx`, `navigation.mdx`, `view-system.mdx`; changelog entry. - [ ] Thin-harness clean — no product slugs in platform. ## Non-goals - Type-level schema inheritance - Multiple parents per entity - Ordered children / drag-to-reorder (drag-to-reparent is a follow-up) - Subtree-scoped permissions - Graph algorithms - Subtree clone / export - Auto-reparent of children when parent's type changes - Soft-delete / tombstones (follow-up; important for DOC'S long-term) - Per-field `excludeFromAncestorContext: true` (Phase 1 supports per-type list; per-field flag follow-up) - FTS ancestor-path enrichment (follow-up) - `ltree` / GiST alternative (follow-up; benchmark before Phase 2 scales) - `cascadeDefault` per-type (Phase 2 only)