supabase_schema.sql

-- CORTEX — Supabase PostgreSQL Schema
-- Run this in the Supabase SQL editor to initialise the database.
-- Enable UUID extension (already enabled on Supabase by default)
create extension if not exists "pgcrypto";

-- -----------------------------------------------------------------------
-- CANVASES
-- -----------------------------------------------------------------------
create table if not exists canvases (
  id          uuid primary key default gen_random_uuid(),
  user_id     uuid,                          -- null = anonymous / demo
  name        text not null,
  nodes       jsonb not null default '[]',   -- React Flow nodes array
  edges       jsonb not null default '[]',   -- React Flow edges array
  is_demo     boolean default false,
  created_at  timestamptz default now(),
  updated_at  timestamptz default now()
);

create index if not exists canvases_user_id_idx on canvases(user_id);

-- -----------------------------------------------------------------------
-- EXECUTIONS
-- -----------------------------------------------------------------------
create table if not exists executions (
  id                     uuid primary key default gen_random_uuid(),
  canvas_id              uuid references canvases(id) on delete cascade,
  user_id                uuid,
  status                 text check (status in ('pending','running','complete','failed')) default 'pending',
  trigger_message        text,
  shared_memory_enabled  boolean default true,
  started_at             timestamptz,
  completed_at           timestamptz,
  -- summary: {memories_created, messages_passed, conflicts_detected, duration_ms, agents_run}
  summary                jsonb
);

create index if not exists executions_canvas_id_idx on executions(canvas_id);
create index if not exists executions_status_idx on executions(status);

-- -----------------------------------------------------------------------
-- MEMORY EVENTS  (mirrors every Mem0 SDK call for transparency)
-- -----------------------------------------------------------------------
create table if not exists memory_events (
  id              uuid primary key default gen_random_uuid(),
  execution_id    uuid references executions(id) on delete cascade,
  canvas_id       text not null,
  agent_id        text not null,
  operation       text check (operation in ('read','write','conflict','inherit')),
  mem0_memory_id  text,       -- ID returned by Mem0
  content         text,
  confidence      float,
  category        text,
  created_at      timestamptz default now()
);

create index if not exists memory_events_canvas_idx on memory_events(canvas_id);
create index if not exists memory_events_execution_idx on memory_events(execution_id);

-- -----------------------------------------------------------------------
-- CONFLICTS
-- -----------------------------------------------------------------------
create table if not exists conflicts (
  id                uuid primary key default gen_random_uuid(),
  canvas_id         text not null,
  execution_id      uuid references executions(id) on delete set null,
  memory_id_a       text not null,
  memory_id_b       text not null,
  content_a         text,
  content_b         text,
  similarity_score  float,
  source_agent_a    text,
  source_agent_b    text,
  confirmed_by_llm  boolean default false,
  status            text check (status in ('open','resolved')) default 'open',
  resolution        text check (resolution in ('kept_a','kept_b','merged','dismissed')),
  resolved_at       timestamptz,
  created_at        timestamptz default now()
);

create index if not exists conflicts_canvas_idx on conflicts(canvas_id);
create index if not exists conflicts_status_idx on conflicts(status);

-- -----------------------------------------------------------------------
-- TRANSCRIPTS
-- -----------------------------------------------------------------------
create table if not exists transcripts (
  id                   uuid primary key default gen_random_uuid(),
  user_id              uuid,
  feature_name         text not null,
  feature_description  text,
  -- content: [{role, content, mem_events, timestamp}]
  content              jsonb not null default '[]',
  created_at           timestamptz default now()
);

-- -----------------------------------------------------------------------
-- AGENT TEMPLATES  (seed data)
-- -----------------------------------------------------------------------
create table if not exists agent_templates (
  id                  uuid primary key default gen_random_uuid(),
  name                text not null,
  role                text not null,
  system_prompt       text not null,
  default_categories  text[] default '{}',
  is_builtin          boolean default false
);

insert into agent_templates (name, role, system_prompt, default_categories, is_builtin)
values
  (
    'Triage Agent',
    'Customer Intake',
    'You are a triage agent. Assess the customer issue, retrieve their history from shared memory, and route to the appropriate specialist.',
    array['customer','preference'],
    true
  ),
  (
    'Knowledge Agent',
    'Product Knowledge',
    'You are a knowledge agent. Search the product knowledge base in shared memory and provide accurate resolution options.',
    array['product','resolution'],
    true
  ),
  (
    'Resolution Agent',
    'Final Response',
    'You are a resolution agent. Using context from Triage and Knowledge agents in shared memory, generate a personalized, accurate customer response.',
    array['customer','resolution'],
    true
  )
on conflict do nothing;

-- -----------------------------------------------------------------------
-- updated_at trigger for canvases
-- -----------------------------------------------------------------------
create or replace function update_updated_at()
returns trigger language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create trigger canvases_updated_at
  before update on canvases
  for each row execute function update_updated_at();