tables.sql

-- Supabase tables and functions

CREATE TABLE IF NOT EXISTS brands (
    id bigint generated by default as identity,
    name text not null,
    description text null,
    domain text null,
    industry jsonb not null,
    size text not null,
    keywords text not null,
    social_media jsonb null,
    user_id text not null,
    org_id text null default ''::text,
    created_at timestamp with time zone not null default now(),
    slug text null,
    updated_at timestamp with time zone null default (now() at time zone 'utc'::text),
    constraint brands_pkey primary key (id),
    constraint brands_description_check check ((length(description) >= 30)),
    constraint brands_domain_check check (
      (
        domain ~* 'https?:\/\/(www\.)?[-a-zA-Z0-9@:%._\+~#=]{2,255}\.[a-z]{2,9}\y([-a-zA-Z0-9@:%_\+.~#?&//=]*)$'::text
      )
    ),
    constraint brands_name_check check ((length(name) <= 100)),
    constraint brands_slug_check check ((length(slug) > 3))
);

CREATE TABLE IF NOT EXISTS knowledge_base (
    id bigint generated by default as identity,
    name text not null,
    type text not null,
    status text not null default 'loading'::text,
    bucket_url text null,
    brand_id bigint not null,
    created_at timestamp with time zone not null default (now() at time zone 'utc'::text),
    user_id text not null,
    org_id text null,
    updated_at timestamp with time zone null default (now() at time zone 'utc'::text),
    constraint knowledgeBase_pkey primary key (id),
    constraint knowledge_base_brand_id_fkey foreign key (brand_id) references brands (id) on delete cascade
);

CREATE TABLE IF NOT EXISTS brand_documents (
    id bigserial,
    content text null,
    metadata jsonb null,
    embedding extensions.vector null,
    created_at timestamp with time zone null default (now() at time zone 'utc'::text),
    updated_at timestamp with time zone null default (now() at time zone 'utc'::text),
    constraint brand_documents_pkey primary key (id)
);

CREATE FUNCTION match_brand_documents(
query_embedding vector (1536),
  filter jsonb default '{}'
) returns table (
  id uuid,
  content text,
  metadata jsonb,
  similarity float
) language plpgsql as $$
#variable_conflict use_column
begin
  return query
  select
    id,
    content,
    metadata,
    1 - (brand_documents.embedding <=> query_embedding) as similarity
  from brand_documents
  where metadata @> filter
  order by brand_documents.embedding <=> query_embedding
  limit match_count;
end;
$$;

create or replace function requesting_user_id()
returns text 
language sql stable
as $$
  select nullif(current_setting('request.jwt.claims', true)::json->>'sub', '')::text;
$$;

create or replace function requesting_org_id()
returns text 
language sql stable
as $$
  select nullif(current_setting('request.jwt.claims', true)::json->>'orgID', '')::text;
$$;