schema.sql

-- Tempest Webs Supabase PostgreSQL Schema Migration
-- Designed for real-time multi-agent execution, state persistence, and vector memory.

-- Enable pgvector extension for long-term semantic memory
create extension if not exists vector;

-- 1. WORKSPACES
create table if not exists workspaces (
    id uuid primary key default gen_random_uuid(),
    name text not null,
    description text,
    created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Enable Realtime for workspaces
alter table workspaces replica identity full;

-- 2. AGENTS
create table if not exists agents (
    id uuid primary key default gen_random_uuid(),
    workspace_id uuid references workspaces(id) on delete cascade not null,
    name text not null,
    role text not null,
    system_prompt text not null,
    model_provider text not null check (model_provider in ('openai', 'gemini', 'ollama', 'anthropic')),
    model_name text not null,
    temperature float default 0.2 not null,
    tools jsonb default '[]'::jsonb not null, -- Array of enabled tool names/schemas
    created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- 3. WORKFLOWS
create table if not exists workflows (
    id uuid primary key default gen_random_uuid(),
    workspace_id uuid references workspaces(id) on delete cascade not null,
    name text not null,
    nodes jsonb not null default '[]'::jsonb, -- Node coordinates, config and state metadata
    edges jsonb not null default '[]'::jsonb, -- Connections and routing rules
    created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Enable Realtime for workflows
alter table workflows replica identity full;

-- 4. EXECUTIONS (Agent run sessions)
create table if not exists executions (
    id uuid primary key default gen_random_uuid(),
    workflow_id uuid references workflows(id) on delete cascade not null,
    status text not null check (status in ('pending', 'running', 'completed', 'failed', 'paused')),
    current_node_id text,
    context_data jsonb default '{}'::jsonb not null, -- Shared execution variables / state graph data
    created_at timestamp with time zone default timezone('utc'::text, now()) not null,
    updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Enable Realtime for executions
alter table executions replica identity full;

-- 5. AGENT_LOGS (Visual agent step terminal output)
create table if not exists agent_logs (
    id uuid primary key default gen_random_uuid(),
    execution_id uuid references executions(id) on delete cascade not null,
    agent_id uuid references agents(id) on delete set null,
    level text not null check (level in ('debug', 'info', 'warn', 'error')),
    message text not null,
    created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Enable Realtime for agent logs to support live terminal streaming
alter table agent_logs replica identity full;

-- 6. EPISODIC_MESSAGES (Detailed history of chat, tool execution outputs, and agent interactions)
create table if not exists episodic_messages (
    id uuid primary key default gen_random_uuid(),
    execution_id uuid references executions(id) on delete cascade not null,
    sender_id uuid references agents(id) on delete set null,
    recipient_id uuid references agents(id) on delete set null,
    role text not null check (role in ('system', 'user', 'agent', 'tool')),
    content text not null,
    tool_calls jsonb default null, -- Details of tool calls if role is 'agent' or 'tool'
    created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Enable Realtime for episodic messages
alter table episodic_messages replica identity full;

-- 7. SEMANTIC_MEMORY (Vector database for similarity searches)
create table if not exists semantic_memory (
    id uuid primary key default gen_random_uuid(),
    workspace_id uuid references workspaces(id) on delete cascade not null,
    agent_id uuid references agents(id) on delete cascade, -- Optional: null means shared workspace-wide knowledge
    content text not null,
    embedding vector(1536), -- Designed for Gemini/OpenAI 1536-dimensional embeddings
    metadata jsonb default '{}'::jsonb not null,
    created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Create HNSW index for super fast similarity searches (cosine distance)
create index if not exists semantic_memory_hnsw_idx 
on semantic_memory using hnsw (embedding vector_cosine_ops);

-- 8. HELPER FUNCTION FOR VECTOR SIMILARITY SEARCH
-- Allows agents or API to search memory using cosine similarity
create or replace function match_semantic_memory (
  query_embedding vector(1536),
  match_threshold float,
  match_count int,
  filter_workspace_id uuid,
  filter_agent_id uuid default null
)
returns table (
  id uuid,
  content text,
  metadata jsonb,
  similarity float
)
language plpgsql
as $$
begin
  return query
  select
    sm.id,
    sm.content,
    sm.metadata,
    1 - (sm.embedding <=> query_embedding) as similarity
  from semantic_memory sm
  where sm.workspace_id = filter_workspace_id
    and (filter_agent_id is null or sm.agent_id = filter_agent_id)
    and 1 - (sm.embedding <=> query_embedding) > match_threshold
  order by sm.embedding <=> query_embedding
  limit match_count;
end;
$$;