Knowledge Graph Architecture

Why Hardcoded Logic Is Brittle

Your current Movement Edge Function works. It correctly fetches Oura data, classifies workouts, computes metrics, runs a two-pass intervention selection algorithm, and feeds a precisely engineered prompt to GPT-5 mini. This is excellent engineering.

The problem is architectural, not functional. Everything that makes the app intelligent — the intervention thresholds, the evidence briefs, the activity classifications, the voice rules — lives inside a single TypeScript file. This creates five compounding risks as OptimizeHER scales:

What Each Column Means

The Excel template you received is a Proto-Graph — a human-readable representation of your Knowledge Graph that can be directly seeded into Supabase as a CSV. Every row is one node in the graph. Every column is a property of that node. Here is exactly what each column does and why it exists.

Translating Excel to Database

The Excel template seeds directly into a set of Supabase (PostgreSQL) tables. Here is the complete schema for the Knowledge Graph layer — three core tables that together replace all hardcoded logic in the current Edge Function.

Table 1: kg_interventions

This is the master interventions registry. It replaces the INTERVENTIONS array and the EVIDENCE_BRIEFS object in a single table.

CREATE TABLE kg_interventions (
  id                    TEXT PRIMARY KEY,        -- 'movement_resistance'
  pillar                TEXT NOT NULL,            -- 'movement', 'vitality', 'sleep', 'nutrition'
  priority              INTEGER NOT NULL,         -- 1 = highest clinical priority
  theme                 TEXT NOT NULL,            -- 'Build Your Strength Foundation'
  primary_objective     TEXT NOT NULL,
  threshold_optimal     JSONB,                    -- {"resistance_sessions_per_week": ">= 3"}
  threshold_acceptable  JSONB,                    -- {"resistance_sessions_per_week": ">= 1"}
  threshold_necessary   JSONB,                    -- {"resistance_sessions_per_week": "< 1"}
  willingness_keywords  TEXT[],                   -- ['strength', 'weight_training']
  evidence_summary      TEXT,                     -- full evidence brief → fed to GPT
  women_specific_angle  TEXT,                     -- female-specific physiology context
  evidence_grade        TEXT,                     -- 'apex', 'high', 'moderate', 'emerging'
  is_active             BOOLEAN DEFAULT true,
  created_at            TIMESTAMPTZ DEFAULT now(),
  updated_at            TIMESTAMPTZ DEFAULT now()
);

Table 2: kg_activity_classifications

This replaces the hardcoded ACTIVITY_KEYWORDS object. To add "barre" as a mobility activity, you insert one row. Zero code changes.

CREATE TABLE kg_activity_classifications (
  keyword               TEXT PRIMARY KEY,         -- 'crossfit', 'barre', 'swimming'
  category              TEXT NOT NULL,            -- 'resistance', 'cardio', 'mobility', 'hiit'
  pillar                TEXT DEFAULT 'movement',
  created_at            TIMESTAMPTZ DEFAULT now()
);

-- Seed data (replaces ACTIVITY_KEYWORDS object):
INSERT INTO kg_activity_classifications VALUES
  ('workout', 'resistance', 'movement'),
  ('strength', 'resistance', 'movement'),
  ('weight_training', 'resistance', 'movement'),
  ('running', 'cardio', 'movement'),
  ('cycling', 'cardio', 'movement'),
  ('swimming', 'cardio', 'movement'),
  ('yoga', 'mobility', 'movement'),
  ('pilates', 'mobility', 'movement'),
  ('hiit', 'hiit', 'movement'),
  ('walking', 'non_exercise', 'movement');

Table 3: kg_triples

This is the graph layer that makes OptimizeHER a Knowledge Graph and not just a lookup table. Every causal or relational claim in your clinical logic gets stored here as a Subject → Predicate → Object triple. This is your IP audit trail and your compounding moat.

CREATE TABLE kg_triples (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  subject_entity        TEXT NOT NULL,    -- 'cycle_phase:luteal'
  predicate             TEXT NOT NULL,    -- 'increases_demand_for'
  object_entity         TEXT NOT NULL,    -- 'entity:magnesium'
  intervention_id       TEXT REFERENCES kg_interventions(id),
  pillar                TEXT,
  evidence_grade        TEXT,
  source_citation       TEXT,             -- 'Balban et al., Cell Reports Medicine 2023'
  confidence_score      NUMERIC DEFAULT 1.0,  -- updated by outcome data over time
  is_active             BOOLEAN DEFAULT true,
  created_at            TIMESTAMPTZ DEFAULT now()
);

-- Example triples for movement pillar:
INSERT INTO kg_triples 
  (subject_entity, predicate, object_entity, intervention_id, evidence_grade, source_citation)
VALUES
  ('cycle_phase:follicular', 'optimizes', 'protocol:resistance_training', 
   'movement_resistance', 'High', 'Sung et al. 2014'),
  ('cycle_phase:luteal', 'reduces_tolerance_for', 'protocol:hiit', 
   'movement_hiit', 'Moderate', 'Markofski & Braun 2014'),
  ('metric:stress_high_minutes', 'is_reduced_by', 'protocol:movement_active_minutes', 
   'movement_active_minutes', 'High', 'Caplin et al. 2021');

Your North Star Metric

The Composite Wellness Index (CWI) is OptimizeHER's proprietary score. It is not Oura's Readiness score, Resilience score, or Sleep score — it is your synthesis of those signals, weighted specifically for professional women and designed to be device-agnostic from day one.

Why device-agnostic matters: Oura measures HRV, sleep staging, and skin temperature. Apple Watch measures heart rate, activity, and blood oxygen. Garmin measures stress and VO2max. The variables are different, but the underlying constructs are the same: recovery quality, stress load, movement sufficiency, and sleep quality. By building your CWI on universal constructs — not device-specific API fields — you ensure that adding Apple Watch or Garmin support in V2 requires a new data adapter, not a new scoring model.

The Four Constructs

The Calculation

-- Supabase function: calculate_cwi(user_id, date)
CREATE OR REPLACE FUNCTION calculate_cwi(p_user_id UUID, p_date DATE)
RETURNS NUMERIC AS $$
DECLARE
  v_recovery_quality  NUMERIC;
  v_stress_load       NUMERIC;
  v_movement          NUMERIC;
  v_adherence         NUMERIC;
  v_cwi               NUMERIC;
  v_oura              RECORD;
  v_checkin           RECORD;
BEGIN
  -- Fetch Oura metrics for this date
  SELECT * INTO v_oura
  FROM daily_oura_metrics
  WHERE user_id = p_user_id AND date = p_date;

  -- Fetch self-reported check-in
  SELECT * INTO v_checkin
  FROM daily_checkins
  WHERE user_id = p_user_id AND date = p_date;

  -- CONSTRUCT 1: Recovery Quality (0-100)
  -- Uses resilience_score + sleep_score, normalized to 0-1
  v_recovery_quality := (
    COALESCE(v_oura.resilience_score, 50) * 0.6 +
    COALESCE(v_oura.sleep_score, 50) * 0.4
  );

  -- CONSTRUCT 2: Stress Load (0-100, inverted — higher stress = lower score)
  -- stress_ratio = recovery_high / (stress_high + recovery_high + 1)
  v_stress_load := (
    COALESCE(v_oura.recovery_high_minutes, 30) /
    NULLIF(COALESCE(v_oura.stress_high_minutes, 60) +
           COALESCE(v_oura.recovery_high_minutes, 30), 0)
  ) * 100;

  -- CONSTRUCT 3: Movement Sufficiency (0-100)
  -- Steps normalized to 10,000 target (100 = optimal)
  v_movement := LEAST(
    COALESCE(v_oura.steps, 0) / 100.0,   -- 10,000 steps = 100
    100
  );

  -- CONSTRUCT 4: Protocol Adherence (0 or 100 — binary for now)
  v_adherence := CASE
    WHEN COALESCE(v_checkin.protocol_completed, false) THEN 100
    ELSE 0
  END;

  -- COMPOSITE WELLNESS INDEX (weighted sum)
  v_cwi := (
    v_recovery_quality * 0.30 +
    v_stress_load      * 0.25 +
    v_movement         * 0.25 +
    v_adherence        * 0.20
  );

  RETURN ROUND(v_cwi, 1);
END;
$$ LANGUAGE plpgsql;

How the Graph Learns

Every intervention the rules engine selects is a hypothesis: "Given this user's current state, this protocol should improve her CWI." Outcome capture is the mechanism that tests each hypothesis against reality.

The Outcome Table

CREATE TABLE kg_outcomes (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id               UUID REFERENCES profiles(id),
  intervention_id       TEXT REFERENCES kg_interventions(id),
  trigger_date          DATE,
  -- Full behavioral state at trigger (the confounders)
  trigger_state         JSONB,  
  -- e.g., {"cycle_phase": "luteal", "stress_level": "high",
  --        "movement_mins": 0, "sleep_score": 71}
  
  -- CWI trajectory
  cwi_at_trigger        NUMERIC,   -- CWI on day intervention was selected
  cwi_day_3             NUMERIC,   -- CWI 3 days later
  cwi_day_7             NUMERIC,   -- CWI 7 days later
  cwi_day_14            NUMERIC,   -- CWI 14 days later
  
  -- Pillar-specific outcome metrics
  oura_resilience_before NUMERIC,
  oura_resilience_after  NUMERIC,  -- captured at day 7
  oura_stress_before     NUMERIC,
  oura_stress_after      NUMERIC,
  
  -- Self-reported signals
  user_completed        BOOLEAN DEFAULT false,
  user_rating           INTEGER,   -- 1-5 evening check-in
  
  created_at            TIMESTAMPTZ DEFAULT now()
);

The Learning Query

Once you have enough outcome rows, this query answers: "Which interventions, in which behavioral states, most reliably improve CWI?"

-- Pattern detection query — run weekly
SELECT
  intervention_id,
  trigger_state->>'cycle_phase'                    AS cycle_phase,
  CASE 
    WHEN (trigger_state->>'movement_mins')::INT > 30 
    THEN 'active' ELSE 'sedentary' 
  END                                              AS movement_day,
  AVG(cwi_day_7 - cwi_at_trigger)                  AS avg_cwi_delta,
  AVG(oura_resilience_after - oura_resilience_before) AS avg_resilience_delta,
  COUNT(*)                                          AS observations
FROM kg_outcomes
WHERE user_completed = true
GROUP BY 1, 2, 3
HAVING COUNT(*) > 50
ORDER BY avg_cwi_delta DESC;

This query produces a ranked table of intervention effectiveness by behavioral context. When a pattern emerges — say, movement_resistance in the Follicular phase with active movement produces 3x the CWI delta versus Luteal + sedentary — you update the kg_triples table with a new triple:

INSERT INTO kg_triples 
  (subject_entity, predicate, object_entity, intervention_id, 
   confidence_score, source_citation)
VALUES
  ('behavioral_state:follicular_active', 
   'amplifies_effect_of', 
   'protocol:movement_resistance',
   'movement_resistance',
   0.87,   -- empirical confidence score from your outcome data
   'OptimizeHER Outcome Data, n=312, 2026');

Before vs. After

Here is the exact structural change to your Movement Edge Function. The logic is identical — only the source of the data changes from hardcoded TypeScript to database queries.

Section 2: Activity Classification — Before vs. After

// BEFORE: Hardcoded in TypeScript
const ACTIVITY_KEYWORDS = {
  resistance: ['workout', 'strength', 'weight_training', ...],
  cardio: ['running', 'cycling', 'swimming', ...],
}

function classifyActivity(activityType: string): string {
  const lower = (activityType || '').toLowerCase()
  for (const [category, keywords] of Object.entries(ACTIVITY_KEYWORDS)) {
    if (keywords.some(k => lower.includes(k))) return category
  }
  return 'other'
}

// AFTER: Query the graph
const { data: classifications } = await supabase
  .from('kg_activity_classifications')
  .select('keyword, category')

// Build lookup map from DB result
const classificationMap = Object.fromEntries(
  (classifications || []).map(c => [c.keyword, c.category])
)

function classifyActivity(activityType: string): string {
  const lower = (activityType || '').toLowerCase()
  for (const [keyword, category] of Object.entries(classificationMap)) {
    if (lower.includes(keyword)) return category
  }
  return 'other'
}

Section 3: Rules Engine — Before vs. After

// BEFORE: Hardcoded INTERVENTIONS array with evaluate() functions
const INTERVENTIONS = [
  {
    id: 'resistance_training',
    priority: 1,
    evaluate: (data) => {
      if (data.resistanceSessionsPerWeek >= 3) return 'optimal'
      if (data.resistanceSessionsPerWeek >= 1) return 'acceptable'
      return 'necessary'
    },
    ...
  }
]

// AFTER: Fetch from graph, evaluate against thresholds
const { data: interventions } = await supabase
  .from('kg_interventions')
  .select('*')
  .eq('pillar', 'movement')
  .eq('is_active', true)
  .not('id', 'in', `(${excluded_interventions.map(i => `'${i}'`).join(',')})`)
  .order('priority', { ascending: true })

function evaluateLevel(intervention, movementData): 'optimal' | 'acceptable' | 'necessary' {
  // Parse threshold_necessary JSONB and compare to movementData
  // Returns the current assessment level
  const necessary = intervention.threshold_necessary || {}
  const acceptable = intervention.threshold_acceptable || {}
  // Dynamic threshold comparison replaces hardcoded evaluate() functions
  // Full implementation provided in companion TypeScript snippet
  return 'necessary' // simplified for brevity
}

Section 4: Evidence Brief — Before vs. After

// BEFORE: Hardcoded EVIDENCE_BRIEFS object (500+ lines)
const EVIDENCE_BRIEFS = {
  resistance_training: `EVIDENCE BRIEF: Resistance Training...`,
  weekly_exercise_minutes: `EVIDENCE BRIEF: Weekly Exercise Minutes...`,
  // ... 5 more massive hardcoded strings
}

// AFTER: Evidence brief comes from the graph row already fetched
const evidenceBrief = selectedIntervention.evidence_summary
const womenAngle = selectedIntervention.women_specific_angle

const userMessage = `${evidenceBrief}

WOMEN-SPECIFIC ANGLE:
${womenAngle}

ABOUT THIS USER:
${dataContext}
...`

Why This Becomes Defensible at Scale

When you present OptimizeHER at Demo Day on April 22nd, this architecture gives you four defensible claims that no competitor in the room can match: