Core Data Models

​

LO Core: Leadership Operating System Data Model

​

Tables

​

Key Design Decisions

1. User References: lo_seat_assignments.profile_id references pf_profiles.id (NOT hr_employees.id)
   • Reason: Not all users in seats are employees; allows external board members, advisors, etc.
2. RLS Pattern: All tables use lo_has_org_access() SECURITY DEFINER function
   • Prevents infinite recursion when checking pf_user_role_assignments
3. Vision-to-Role Alignment: lo_role_definitions.vision_alignment and strategic_goal_ids[]
   • Links roles to strategic goals for accountability
4. Validation Triggers: Use triggers instead of CHECK constraints
   • lo_validate_meeting() - meeting_type and status validation
   • lo_validate_meeting_attendee() - attendee status validation
   • lo_validate_issue() - issue priority and status validation
   • lo_validate_one_on_one() - 1-on-1 status validation
   • lo_validate_feedback() - feedback type and GWC category validation
   • lo_validate_assessment() - assessment type validation
   • lo_validate_assessment_schedule() - schedule status and scope validation
   • lo_validate_assessment_response() - response status validation

​

RLS Security Functions

-- SECURITY DEFINER function used by all LO RLS policies
CREATE OR REPLACE FUNCTION lo_has_org_access(org_id UUID, user_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM pf_user_role_assignments
    WHERE pf_user_role_assignments.organization_id = org_id
      AND pf_user_role_assignments.user_id = user_id
  );
END;
$$;

-- Admin access function for elevated operations
CREATE OR REPLACE FUNCTION lo_has_admin_access(org_id UUID, user_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM pf_user_role_assignments
    WHERE pf_user_role_assignments.organization_id = org_id
      AND pf_user_role_assignments.user_id = user_id
      AND pf_user_role_assignments.system_role >= 'org_admin'
  );
END;
$$;

​

Integration Points

• pf_profiles - User identity and display names
• pf_organizations - Multi-tenant context
• pf_user_role_assignments - RBAC (via SECURITY DEFINER function)
• fw_forms - Assessment forms (LO-09)

• rock_completed - When rock is completed ✅
• todo_created - When task is created ✅
• todo_completed - When task is completed ✅
• todo_overdue - When task becomes overdue ✅
• scorecard_updated - When scorecard is updated ✅
• metric_threshold_breached - When metric breaches threshold ✅
• meeting_scheduled - When meeting is scheduled ✅
• meeting_completed - When meeting is completed ✅
• action_item_created - When action item created ✅
• issue_identified - When issue is identified ✅
• issue_resolved - When issue is resolved ✅
• assessment_distributed - When assessment is distributed ✅
• assessment_completed - When assessment is completed ✅

• LO-03: Rocks assigned to roles
• LO-04: To-dos from action items
• LO-05: Scorecards owned by roles
• LO-06: Meetings with agenda items
• LO-07: Issues linked to meetings
• LO-08: Feedback linked to roles

• one_on_one_scheduled - When 1-on-1 is scheduled
• feedback_submitted - When feedback is provided
• vision_updated - When vision is updated
• strategic_goal_created - When strategic goal is created
• rock_created - When rock is created
• role_assigned - When person assigned to role
• accountability_chart_updated - When accountability chart structure changes
• knowledge_article_created - When knowledge article is created
• process_updated - When process is updated

​

Related Documentation

• Platform Integration Layers - Platform layer integrations
• Event Contracts - Event-based integrations
• API Contracts - API-based integrations
• Cross-Core Integrations - Integration matrix