Skip to content

Latest commit

 

History

History
339 lines (292 loc) · 8.1 KB

README.md

File metadata and controls

339 lines (292 loc) · 8.1 KB

Supabase Data Storage Configuration

This section will guide you through configuring the NEXT_PUBLIC_STORAGE_MODE variable and setting up the necessary SQL configurations for storing chat data either locally or in the cloud using Supabase.

Storage Mode

You can configure how your chat data is stored by setting the NEXT_PUBLIC_STORAGE_MODE environment variable in your .env file:

  • local: This mode saves chat data directly in your browser's local storage.
  • cloud: This mode syncs chat data to Supabase, a cloud-based PostgreSQL database.

Example:

To use Supabase for cloud storage, change the mode to "cloud":

NEXT_PUBLIC_STORAGE_MODE="cloud"

Setting Up Supabase

To store chat data in Supabase, follow these steps:

  1. Create a Supabase account and set up a new project by following this guide.

  2. Update environment variables in your .env file with the credentials provided by Supabase:

NEXT_PUBLIC_SUPABASE_URL="your_supabase_url"
SUPABASE_SERVICE_ROLE_KEY="******"

SQL Configuration

Execute the following SQL commands in the Supabase SQL editor to set up the necessary database schema, tables, and functions:

1. Create Schema and Set Permissions

-- Create chat_dataset schema
CREATE SCHEMA chat_dataset;

GRANT USAGE ON SCHEMA chat_dataset TO anon, authenticated, service_role;
GRANT ALL ON ALL TABLES IN SCHEMA chat_dataset TO anon, authenticated, service_role;
GRANT ALL ON ALL ROUTINES IN SCHEMA chat_dataset TO anon, authenticated, service_role;
GRANT ALL ON ALL SEQUENCES IN SCHEMA chat_dataset TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA chat_dataset GRANT ALL ON TABLES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA chat_dataset GRANT ALL ON ROUTINES TO anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA chat_dataset GRANT ALL ON SEQUENCES TO anon, authenticated, service_role;

2. Create Chats Table

CREATE TABLE IF NOT EXISTS
  chat_dataset.chats (
    id bigint generated always as identity,
    chat_id text not null,
    user_id uuid not null,
    title text null,
    path text null,
    created_at bigint null default (
      extract(
        epoch
        from
          current_timestamp
      ) * (1000)::numeric
    ),
    messages jsonb not null,
    share_path text null,
    current_model_name VARCHAR(50),
    updated_at bigint null default (
      extract(
        epoch
        from
          current_timestamp
      ) * (1000)::numeric
    ),
    constraint chats_pkey primary key (id),
    constraint chats_chat_id_key unique (chat_id),
    constraint chats_user_id_fkey foreign key (user_id) references next_auth.users (id)
  ) tablespace pg_default;

3. Create Functions

  • Function to Get Chat Data
CREATE OR REPLACE FUNCTION chat_dataset.get_chat_data(p_user_id uuid, p_chat_id text)
RETURNS TABLE (
  id bigint,
  chat_id text,
  user_id uuid,
  title text,
  path text,
  created_at bigint,
  current_model_name VARCHAR(50),
  messages jsonb,
  share_path text,
  updated_at bigint
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    c.id,
    c.chat_id,
    c.user_id,
    c.title,
    c.path,
    c.created_at,
    c.current_model_name,
    c.messages,
    c.share_path,
    c.updated_at
  FROM chat_dataset.chats c
  WHERE c.user_id = p_user_id AND c.chat_id = p_chat_id;
END;
$$;
  • Function to Upsert Chat Data
CREATE OR REPLACE FUNCTION chat_dataset.upsert_chat(
  p_chat_id text,
  p_title text,
  p_user_id uuid,
  p_created_at bigint,
  p_path text,
  p_messages jsonb,
  p_share_path text,
  p_current_model_name VARCHAR(50)
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO chat_dataset.chats (
    chat_id, title, user_id, created_at, path, messages, share_path, current_model_name
  )
  VALUES (
    p_chat_id, p_title, p_user_id, p_created_at, p_path, p_messages, p_share_path, p_current_model_name
  )
  ON CONFLICT (chat_id) DO UPDATE
  SET 
    title = EXCLUDED.title,
    user_id = EXCLUDED.user_id,
    created_at = EXCLUDED.created_at,
    path = EXCLUDED.path,
    messages = EXCLUDED.messages,
    share_path = EXCLUDED.share_path,
    current_model_name = EXCLUDED.current_model_name;
END;
$$;
  • Function to delete user chat
CREATE OR REPLACE FUNCTION chat_dataset.delete_chat(p_user_id UUID, p_chat_id text)
RETURNS TABLE (deleted_chat chat_dataset.chats) AS $$
BEGIN
    RETURN QUERY
    DELETE FROM chat_dataset.chats
    WHERE user_id = p_user_id AND chat_id = p_chat_id
    RETURNING *;
END;
$$ LANGUAGE plpgsql;
  • Function to get all Chat history
-- Execute the following SQL statement in the Supabase SQL Editor to create the function
CREATE OR REPLACE FUNCTION chat_dataset.get_user_chats(p_user_id uuid)
RETURNS TABLE(
  id bigint, 
  chat_id text, 
  user_id uuid, 
  title text, 
  path text, 
  created_at bigint, 
  messages jsonb, 
  share_path text,
  current_model_name VARCHAR(50),
  updated_at bigint
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    c.id, 
    c.chat_id, 
    c.user_id, 
    c.title, 
    c.path, 
    c.created_at, 
    c.messages, 
    c.share_path, 
    c.current_model_name,
    c.updated_at
  FROM chat_dataset.chats c
  WHERE c.user_id = p_user_id
  ORDER BY c.updated_at DESC;
END;
$$ LANGUAGE plpgsql;
  • Function to Delete all Chat data
CREATE OR REPLACE FUNCTION chat_dataset.delete_user_chats(p_user_id UUID)
RETURNS VOID AS $$
BEGIN
  DELETE FROM chat_dataset.chats
  WHERE user_id = p_user_id;
END;
$$ LANGUAGE plpgsql;
  • Function to get system prompt data
CREATE OR REPLACE FUNCTION chat_dataset.get_prompt_by_user_id(_user_id UUID)
RETURNS TABLE (
  id UUID,
  user_id UUID,
  system_prompt TEXT,
  user_prompts JSON,
  created_at BIGINT,
  updated_at BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    s.id,
    s.user_id,
    s.prompt AS system_prompt,
    s.user_prompts,
    s.created_at,
    s.updated_at
  FROM
    chat_dataset.settings s
  WHERE
    s.user_id = _user_id;
END;
$$ LANGUAGE plpgsql;
  • Function to upsert system prompt data
CREATE OR REPLACE FUNCTION chat_dataset.upsert_prompt(
  _user_id UUID,
  _prompt TEXT DEFAULT NULL,
  _user_prompts jsonb DEFAULT NULL
)
RETURNS VOID
LANGUAGE plpgsql AS $$
BEGIN
  -- Try to update the existing record
  UPDATE chat_dataset.settings
  SET
    updated_at = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) * 1000
  WHERE
    user_id = _user_id;

  -- If prompt is provided, update the prompt field
  IF _prompt IS NOT NULL THEN
    UPDATE chat_dataset.settings
    SET
      prompt = _prompt
    WHERE
      user_id = _user_id;
  END IF;

  -- If user_prompts is provided, update the user_prompts field
  IF _user_prompts IS NOT NULL THEN
    UPDATE chat_dataset.settings
    SET
      user_prompts = _user_prompts
    WHERE
      user_id = _user_id;
  END IF;

  -- If no rows were updated, insert a new record
  IF NOT FOUND THEN
    INSERT INTO chat_dataset.settings (
      user_id, prompt, user_prompts, created_at, updated_at
    )
    VALUES (
      _user_id,
      COALESCE(_prompt, '')
      _user_prompts,
      EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) * 1000,
      EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) * 1000
    );
  END IF;
END;
$$;
  • Function to get shared chat data
CREATE OR REPLACE FUNCTION chat_dataset.get_shared_chat(chat_id UUID)
RETURNS TABLE (chat_record chat_dataset.chats) AS $$
BEGIN
    RETURN QUERY
    SELECT * 
    FROM chat_dataset.chats
    WHERE chat_id = chat_id;
END;
$$ LANGUAGE plpgsql;
  • Function to update shared chat data
CREATE OR REPLACE FUNCTION chat_dataset.update_share_path(a_chat_id TEXT, a_user_id UUID, a_share_path TEXT)
RETURNS SETOF chat_dataset.chats AS $$
BEGIN
    RETURN QUERY
    UPDATE chat_dataset.chats
    SET share_path = a_share_path
    WHERE chat_dataset.chats.chat_id = a_chat_id AND chat_dataset.chats.user_id = a_user_id
    RETURNING *;
END;
$$ LANGUAGE plpgsql;

By following these steps, you can configure your AI chatbot to store chat data either locally in the browser or in the cloud using Supabase.