CarePath Flow

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  owner_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE user_profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE NOT NULL,
  first_name TEXT,
  last_name TEXT,
  role TEXT DEFAULT 'staff' NOT NULL, -- e.g., 'admin', 'staff'
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE patients (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE NOT NULL,
  email TEXT UNIQUE NOT NULL,
  first_name TEXT,
  last_name TEXT,
  phone_number TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE care_flows (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'draft' NOT NULL, -- 'draft', 'published'
  created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE care_flow_steps (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  flow_id UUID REFERENCES care_flows(id) ON DELETE CASCADE NOT NULL,
  step_order INTEGER NOT NULL,
  title TEXT NOT NULL,
  content JSONB NOT NULL, -- e.g., {type: 'text', value: '...', media: 'url'}, {type: 'question', question: '...', options: [...]}
  step_type TEXT NOT NULL, -- 'info', 'question', 'action'
  next_step_id UUID REFERENCES care_flow_steps(id) ON DELETE SET NULL, -- Default next step
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE care_flow_conditions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  flow_id UUID REFERENCES care_flows(id) ON DELETE CASCADE NOT NULL,
  source_step_id UUID REFERENCES care_flow_steps(id) ON DELETE CASCADE NOT NULL,
  target_step_id UUID REFERENCES care_flow_steps(id) ON DELETE CASCADE NOT NULL,
  condition_logic JSONB NOT NULL, -- e.g., {type: 'response_value', step_id: '...', operator: 'equals', value: 'Yes'}
  priority INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE patient_care_flows (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id UUID REFERENCES patients(id) ON DELETE CASCADE NOT NULL,
  flow_id UUID REFERENCES care_flows(id) ON DELETE CASCADE NOT NULL,
  unique_link TEXT UNIQUE NOT NULL,
  assigned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  completed_at TIMESTAMP WITH TIME ZONE,
  current_step_id UUID REFERENCES care_flow_steps(id) ON DELETE SET NULL,
  status TEXT DEFAULT 'assigned' NOT NULL, -- 'assigned', 'in_progress', 'completed'
  last_accessed_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE patient_care_flow_responses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_care_flow_id UUID REFERENCES patient_care_flows(id) ON DELETE CASCADE NOT NULL,
  step_id UUID REFERENCES care_flow_steps(id) ON DELETE CASCADE NOT NULL,
  response JSONB NOT NULL, -- e.g., {question_id: '...', answer: '...'}, {action_confirmed: true}
  responded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID UNIQUE REFERENCES organizations(id) ON DELETE CASCADE NOT NULL,
  stripe_customer_id TEXT UNIQUE NOT NULL,
  stripe_subscription_id TEXT UNIQUE NOT NULL,
  status TEXT NOT NULL, -- e.g., 'active', 'past_due', 'canceled'
  plan TEXT NOT NULL, -- e.g., 'basic', 'pro'
  current_period_end TIMESTAMP WITH TIME ZONE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_organizations_owner_id ON organizations(owner_id);
CREATE INDEX idx_user_profiles_organization_id ON user_profiles(organization_id);
CREATE INDEX idx_patients_organization_id ON patients(organization_id);
CREATE INDEX idx_care_flows_organization_id ON care_flows(organization_id);
CREATE INDEX idx_care_flow_steps_flow_id ON care_flow_steps(flow_id);
CREATE INDEX idx_care_flow_conditions_flow_id ON care_flow_conditions(flow_id);
CREATE INDEX idx_patient_care_flows_patient_id ON patient_care_flows(patient_id);
CREATE INDEX idx_patient_care_flows_flow_id ON patient_care_flows(flow_id);
CREATE INDEX idx_patient_care_flow_responses_patient_care_flow_id ON patient_care_flow_responses(patient_care_flow_id);
CREATE INDEX idx_subscriptions_organization_id ON subscriptions(organization_id);