The relationship graph — ontology map
What the derived relationship graph is built from, what it can and cannot know, how its weights and quadrants are grained, and the counting traps that make a graph query wrong.
Layer id: graph.* (a per-studio Postgres schema) · Role: a derived layer, not a source — it has no vendor, no credentials, no connector row · Freshness: as of the last build, not the live second
Every other map on this shelf describes a vendor: what Mindbody exposes, what GymMaster withholds. This one describes something we build ourselves. The relationship graph is computed entirely from the canonical tables the connectors already fill — it invents nothing, reaches no external system, and knows only what attendance, sessions, locations and plans imply.
That makes its failure mode different, and worse. A source map stops you claiming data you never received. This map stops you claiming meaning the graph never computed: reading an edge weight as a visit count, a quadrant as a churn prediction, or an empty rollup as "this member has no relationships" when the real answer is "the graph hasn't been built for this org yet".
This is the coverage-and-honesty map. For the tool signatures, parameters and purpose semantics, see Graph & relationship tools. For the tables themselves, the schema reference.
What this layer is
Three things live under graph.*, and conflating them is the single most common mistake:
The graph
Who is connected to whom, and how strongly — five classes of weighted, decayed edge
graph.node, graph.edge
The rollups
Precomputed answers over those edges — routing, concentration, resilience, quadrants
graph.member_affinity, graph.staff_concentration, graph.member_resilience, graph.location_resilience, graph.connection_score
The sense layer
Derived facts that a member may need attention now
graph.risk_signal
And one more distinction the naming makes deliberately awkward, because getting it wrong produces nonsense:
graph.signal— the raw, append-only observation stream (a class attended, a sale, a payment, an appointment). An input.graph.risk_signal— the at-risk trigger queue. An output.
They are not variants of each other. get_open_signals reads the second.
The build is one transaction: upsert nodes → build MS/MM/ML/SL/SS edges → classify → score → tier → rebuild every rollup → extract signals → detect risk signals. It runs nightly per org, and again after an import or restate that changes its inputs. Everything below is therefore derived state with a computed_at, never an event log.
Coverage at a glance — what feeds the graph
bookings.attendance (booking_status = 'attended') + class_session.instructor_staff_id
MS edge attended (member↔staff)
one per (member, staff) pair
MS window, MS half-life
No MS edges at all — no affinity, no concentration, no resilience denominator
the same, self-joined on the session
MM edge co_attended (member↔member)
one per unordered member pair
MM window, MS/MM half-life, × α_peer
No peer anchor — every member's resilience falls
bookings.attendance → class_session.location_id → people.location
ML edge frequents (member↔location)
one per (member, location)
ML window, ML half-life
No ML edge ⇒ no member_resilience row ⇒ no quadrant
bookings.facility_entry (granted entries, non-exit, access_category <> 'class_attendance') → door's parent club
the same ML edge, summed in
one per (member, club)
FL window, FL half-life, × α_facility
Location attachment is class-only (fine for studios; understates a 24/7 gym)
class_session instructor + secondary staff → location
SL edge staff_of (staff↔location)
one per (staff, location)
SL window, SL half-life
No location_resilience row for that location
co-instruction on one session
SS edge co_teaches (staff↔staff)
one per unordered staff pair
SS window, SS half-life
Every teacher reads as a single point of failure
commerce.plan (category / billing interval), falling back to people.member.membership_type
contract commitment C in member_resilience
one per member
current plan state
C degrades to its fallback — resilience skews
bookings.attendance, commerce.sale, commerce.payment, bookings.appointment
graph.signal rows only — activity counters on graph.node
one per source row
none (full history)
The node's activity counters read as zero
people.member_status_event (non-baseline rows)
graph.signal rows only, kinds status_changed / plan_started / plan_cancelled
one per transition
none, but the log itself is live-forward
No lifecycle signals — the graph can't see that a member went inactive
Nodes exist for exactly three entity types: member, staff, location — one per canonical row (people.member / people.staff where canonical_*_id IS NULL, and every people.location). The entity_type CHECK also admits company, org_user, class_session, plan and product; nothing creates those nodes today.
What it produces
graph.edge
one per (from, to, kind); edge_class ∈ MS/ML/SL/MM/SS
get_entity_edges
graph.connection_score
one per MS pair — score, 30-day delta, trend
get_staff_relationship_review
graph.member_affinity
one per member — primary coach, top-3, concentration %
get_member_affinity, get_member_context
graph.staff_concentration
one per staff — primary_member_count, total member edges
get_staff_concentration, get_staff_context
graph.member_resilience
one per (member, location) — R, A, C, R̃, vuln_quadrant
get_member_context, list_quadrant_members
graph.location_resilience
one per location — ops_resilience, cover depth, single points of failure
get_location_context
graph.risk_signal
one per open trigger
get_open_signals
What we cannot get
Money never becomes a relationship. Sales and payments land in graph.signal and move a node's activity counters — they contribute zero edge weight. The only route commerce takes into a score is contract commitment (C), a per-member switching-cost term. So "who are my most valuable relationships" is not a graph question; it is a commerce.payment_attribution question joined to graph output afterwards.
1:1 and PT relationships are invisible. MS edges are built from class attendance only. Appointments produce an appointment_attended signal but never an edge — and in any case bookings.appointment is empty for every connected source today (Mindbody doesn't support it, GymMaster has no such concept, Wix's is unmapped). A studio whose strongest coach bonds are one-to-one has a graph that cannot see them.
Declared or social relationships. graph.edge.kind documents referred_by, mentors, family_of, introduces, messages, follows, covers_for, contracts_with — none of them are ever written. Five kinds exist in practice: attended, co_attended, frequents, staff_of, co_teaches. The MM edge was renamed from workout_buddy to co_attended precisely because the old name claimed a friendship the data never asserts: two people were in the same small room.
Anything from messages or marketing. comms.* and marketing.* feed nothing here.
Relationships formed in big classes. MM edges only come from classes at or below a size cap — above it, co-attendance is treated as coincidence, not connection. A 40-person spin studio will have a sparse peer graph by design.
A relationship that predates the window. Each edge class ages out on its own horizon. An edge older than its window is lapsed or gone; there is no lifetime-total view, and the graph deliberately cannot answer "who used to be close to whom".
History of its own scores. The graph is a rebuilt snapshot. There is no time series of past weights, past quadrants, or past resilience — the only built-in comparison is connection_score.score_delta_30d and its trend. "Show me how this member's resilience moved over six months" cannot be answered.
Note the one exception, and keep it straight: member status history does exist, in people.member_status_event — when someone went inactive, was suspended, or lost their plan, and why where that is knowable. That is a canonical fact the graph merely mirrors as status_changed / plan_started / plan_cancelled signals; it is not graph state, it survives a rebuild, and it says nothing about how any score moved. It is also live-forward only — no source system records status history, so the log begins when Kula started synthesising it, and each member's oldest row per dimension is an is_baseline first observation rather than a change.
A churn prediction. Resilience and the quadrants are structural: how exposed a member is to losing their coach. They are not a forecast, and specialist_nomad does not mean "leaving". Behavioural lapse is list_at_risk_members and graph.risk_signal — a different layer entirely.
Cluster labels, edge rationales, confidence. graph.node.cluster_tags, graph.edge.rationale and graph.edge.confidence are schema surface with no producer: tags are always empty, rationale always NULL, confidence always 1. Don't filter on them.
The tuning values. Half-lives, windows, α_peer, the class-size cap, β and every risk threshold are injected at deploy and are not published. The tools return the resulting scores and labels, never the parameters — and an untuned deployment silently uses neutral placeholders, so absolute weights are not comparable between deployments.
Anything cross-org. The graph is built inside one studio's database from that studio's rows. There is no shared or global graph.
Identity and join keys
Node
graph.node.id (UUID)
(entity_type, entity_id) is a loose FK
Member node
entity_id
people.member.id — the canonical row
Staff node
entity_id
people.staff.id — the canonical row
Location node
entity_id
people.location.id
Rollups
person_id, staff_id, location_id
the same people.*.id values
Three consequences that cause most wrong graph queries:
entity_id is an internal UUID, not a vendor id. AI clients routinely hold a source_external_id — most often the instructor_staff_id printed on a class session. The graph-context tools resolve either form, but raw SQL will not. Use entity_lookup to get the canonical id first.
Identity-resolved duplicates have no node. A studio on two sources has two people.member rows for one human; only the canonical one becomes a node, and edges are keyed on COALESCE(canonical_member_id, id). That is what makes the graph the deduplicated view of a person — but it also means a node count and a naive people.member count won't agree. Count humans with people.member_distinct.
Loose FKs are not enforced. Nothing stops a rollup row outliving the people.* row it points at between builds. Join, don't assume.
Counting traps
A weight is not a count. Edge weight is a decayed, windowed sum — a member who came fifty times two years ago can weigh less than one who came four times last week. If you want visits, count bookings.attendance.
Weights are not comparable across edge classes. Each class uses its own half-life, MM is additionally scaled by α_peer, and the facility leg of ML is scaled by α_facility. "This member's ML weight is bigger than their MS weight" says nothing on its own — that comparison is exactly what the resilience formula exists to do properly.
Backfill twins can double-count. The same logical attendance row can exist as both a live and a backfill copy. The signal extractor applies a live-wins filter; the edge builders read the base tables and do not. On an org carrying both copies, that attendance contributes twice to MS/MM/ML weight, and inflates the computed class size that the MM cap is checked against. Check before trusting absolute weights:
member_resilience is per (member, location). A member attending two locations has two rows with two different quadrants. Never average them; the member's exposure is the minimum R̃ — their most brittle attachment, which is what at_risk_threshold fires on.
primary_member_count counts primaries, not students. A teacher with 200 members but only 12 who rank them first has primary_member_count = 12 and total_member_edges = 200. The first number is defection exposure; the second is reach. Reporting one as the other misstates the risk in both directions.
High staff_concentration_pct is not a compliment or a complaint. It is one number wearing two hats: a strong single-coach bond, and single-coach dependence. Say which one you mean.
Empty ≠ no relationships. Every rollup is DELETE-and-rebuilt, so an org that has never had a graph build returns empty from every graph tool. That is "not computed", not "no connections". Check first:
A computed_at older than ~26 hours means the nightly sweep didn't run — get_system_status says so in plain language.
Tier without a trend degrades quietly. connection_score exists for MS pairs only, so ML/SL/SS edges can never be warming or cooling — they resolve to strong/cold/lapsed by recency alone. An ML edge that is "strong" has not been shown to be improving.
Quadrants are not the at-risk list. Said once more because it is the mistake that keeps recurring: use list_at_risk_members for who is lapsing. list_quadrant_members answers "who is structurally exposed", which is a different set of people and a different conversation. And a third, now that it exists: list_member_status_changes answers "who has already changed state" — someone the sweep flipped to inactive has left the at-risk board (it filters status = 'active') and shows up here instead.
A status change is not activity. Lifecycle signals carry weight = 0 and are excluded from graph.node.activity_count_30d/_90d and last_activity_at. Counting them would make a member going inactive read as more active on the day they left. If you aggregate graph.signal yourself, exclude status_changed, plan_started and plan_cancelled or you will reproduce that paradox.
Baselines are not changes. Every member has one is_baseline = true row per dimension in people.member_status_event recording what Kula first observed. They are deliberately not projected into graph.signal, but a direct query over the table that forgets NOT is_baseline reports the entire membership as having changed on the day the feature shipped.
Questions this layer can and can't answer
Can answer well
Who is a member's primary coach, and how concentrated they are on them
Which coaches carry the most member relationships — and who leaves with them
Which members would lose their strongest connection if a given coach left a location
Where a teacher's relationships are decaying, bucketed by tier with a 30-day trend
Whether a location can absorb a departure operationally (cover depth, single points of failure)
How members at a location distribute across the four connection-strength quadrants
Which members are structurally exposed — narrow routine, no contract, one coach
The open attention queue: drift, softening cadence, missed second visit, stale pause, milestone
Who changed membership state recently, and whether that was the vendor's own claim or a Kula derivation (live-forward only — see the note on score history)
Cannot answer
Anything about revenue, LTV or spend (money makes signals, never edges)
Anything about 1:1 / PT relationships (no source fills
bookings.appointment)Who referred whom, who is related to whom, who mentors whom (never written)
Whether two members are actually friends (co-attendance is the claim; friendship is not)
How any score has moved over time (no score history beyond the 30-day delta — member status history is a separate, canonical thing that does exist; see above)
Who is about to churn (structural exposure ≠ prediction — that's the at-risk layer)
Anything for a studio with no instructor on its sessions, or no location rows
How to phrase the gap. "Your graph is built from class attendance, so it can see coach relationships but not your PT work — no connected system gives us appointment data." Not: "your relationship data is incomplete."
Recipes — what works well
One member's relationships → get_member_context
Core facts, per-location resilience with its A and C components and quadrant, plus the affinity rollup, all scope- and purpose-filtered in one call. Prefer it to three separate table reads.
Who leaves with a coach → simulate_departure
Takes staff_id + location_id, returns the members who lean on that coach, weakest remaining connection first. Reads precomputed weights — no write, no recompute. Pair it with get_location_context for whether the location can cover the classes.
A teacher review → get_staff_relationship_review
The only tool that exposes the tier distribution and per-member trend. The summary field carries the full distribution even when the member list is limit-capped — quote the summary, not the truncated list.
The exposed tail at a location → get_location_context → list_quadrant_members
Aggregate first, then drill in with quadrant: 'specialist_nomad' and a max_resilience ceiling. Under analysis purposes members come back anonymised; use action_board only when someone will actually act.
The attention queue → get_open_signals
Open rows only (resolved_at IS NULL), most severe first. Six types, and they partition deliberately: a paused member fires pause_drift, never drift, so nobody gets chased twice for the same silence.
Raw edges → get_entity_edges, then SQL
An inventory with no MM rows means the class-size cap is excluding everything; no ML means sessions carry no location_id; no MS means sessions carry no instructor. All three are input problems, not graph bugs.
What doesn't work
get_retention_curve, get_member_payments and get_cac_by_cohort are not graph tools — they read commerce and marketing, and the graph has no opinion about them. And on an org whose only connector is a payment system (Stripe-only, say), every graph tool correctly returns empty: no attendance means no edges, and no edges means no relationships to score. That is the right answer, not a fault to investigate.
Where this lives in the code
Member status/plan transition log (table + trigger)
services/intelligence/migrations/postgres/org/100_people/011_member_status_event.sql
Build orchestration + MS/MM edges
services/intelligence/internal/graph/builder.go
ML/SL/SS edges, classification, resilience rollups
services/intelligence/internal/graph/builder_tripartite.go
Connection scores + edge tiers
services/intelligence/internal/graph/builder_decay.go
Signal extraction + node activity
services/intelligence/internal/graph/builder_signals.go
Risk-signal detectors
services/intelligence/internal/graph/signals.go
Tuning parameters (shape only — values injected)
services/intelligence/internal/graph/params.go
Tables
services/intelligence/migrations/postgres/org/800_graph/
Purpose-scoped AI tools
services/intelligence/internal/tools/graph_context.go, graph.go
Freshness + build runner
services/intelligence/internal/admin/graphbuild/
Operator console explorer
services/intelligence/internal/admin/httpapi/stats_graph_network.go
Tool reference for AI clients
Last updated
Was this helpful?