PulsePattern AI

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email TEXT UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'practitioner', -- 'practitioner' or 'client'
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE practitioners (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  first_name TEXT,
  last_name TEXT,
  organization_id UUID, -- For future team features
  stripe_customer_id TEXT UNIQUE, -- Stripe customer ID
  subscription_status TEXT DEFAULT 'trial' -- 'trial', 'active', 'inactive'
);

CREATE TABLE clients (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  practitioner_id UUID REFERENCES practitioners(id) ON DELETE CASCADE,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL, -- Client's email for portal access
  user_id UUID REFERENCES users(id) ON DELETE SET NULL, -- Link to user if client registers
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE data_templates (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  practitioner_id UUID REFERENCES practitioners(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  template_json JSONB NOT NULL, -- JSON schema for the form fields
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE client_data_entries (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
  template_id UUID REFERENCES data_templates(id) ON DELETE SET NULL,
  entry_date DATE NOT NULL DEFAULT CURRENT_DATE,
  entry_data JSONB NOT NULL, -- Actual data submitted by the client
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE ai_summaries (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  client_id UUID REFERENCES clients(id) ON DELETE CASCADE,
  summary_date DATE NOT NULL,
  summary_text TEXT NOT NULL,
  analysis_data JSONB, -- Optional: structured analysis from AI
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_practitioners_user_id ON practitioners (user_id);
CREATE INDEX idx_clients_practitioner_id ON clients (practitioner_id);
CREATE INDEX idx_clients_email ON clients (email);
CREATE INDEX idx_data_templates_practitioner_id ON data_templates (practitioner_id);
CREATE INDEX idx_client_data_entries_client_id ON client_data_entries (client_id);
CREATE INDEX idx_client_data_entries_entry_date ON client_data_entries (entry_date);
CREATE INDEX idx_ai_summaries_client_id_date ON ai_summaries (client_id, summary_date);