starter.sql

create table
  public.documents (
    id bigserial,
    content text null,
    metadata jsonb null,
    embedding vector(1536) null,
    constraint documents_pkey primary key (id)
  ) tablespace pg_default;

  create table
  public.user_npc (
    id bigint generated by default as identity,
    name character varying null default ''::character varying,
    race character varying null default ''::character varying,
    character_class character varying null default ''::character varying,
    traits character varying null default ''::character varying,
    personality character varying null default ''::character varying,
    accent character varying null default ''::character varying,
    conversational_style character varying null default ''::character varying,
    alignment character varying null default ''::character varying,
    game_setting character varying null default ''::character varying,
    background_history text null default ''::text,
    user_id uuid null,
    created_at timestamp with time zone null default now(),
    updated_at timestamp with time zone null default now(),
    additional_info text null default ''::text,
    attitude character varying null default ''::character varying,
    gender character varying null default ''::character varying,
    created_by uuid null,
    picture_url character varying null,
    game character varying null default ''::character varying,
    constraint user_npc_pkey primary key (id),
    constraint user_npc_created_by_fkey foreign key (created_by) references auth.users (id) on delete restrict
  ) tablespace pg_default;

create index if not exists idx_user_npc_user_id on public.user_npc using btree (user_id) tablespace pg_default;

create table
  public.profiles (
    id uuid not null,
    updated_at timestamp with time zone null,
    username text null,
    full_name text null,
    avatar_url text null,
    website text null,
    constraint profiles_pkey primary key (id),
    constraint profiles_username_key unique (username),
    constraint profiles_id_fkey foreign key (id) references auth.users (id) on delete cascade,
    constraint username_length check ((char_length(username) >= 3))
  ) tablespace pg_default;




CREATE OR REPLACE FUNCTION public.match_documents(query_embedding vector, match_count integer DEFAULT NULL::integer, filter jsonb DEFAULT '{}'::jsonb)
 RETURNS TABLE(id bigint, content text, metadata jsonb, similarity double precision)
 LANGUAGE plpgsql
AS $function$
#variable_conflict use_column
begin
  return query
  select
    id,
    content,
    metadata,
    1 - (documents.embedding <=> query_embedding) as similarity
  from documents
  where metadata @> filter
  order by documents.embedding <=> query_embedding
  limit match_count;
end;
$function$