schema.sql

-- CORTEX — Neon PostgreSQL Schema
-- -----------------------------------------------------------------------
-- SETUP INSTRUCTIONS
-- 1. Create a project at https://neon.tech (free tier works fine)
-- 2. Copy the connection string from the Neon dashboard (pooled endpoint)
--    Format: postgresql://user:password@ep-xxx.region.aws.neon.tech/neondb?sslmode=require
-- 3. Add it to cortex-api/.env as: DATABASE_URL=<your connection string>
-- 4. Run this file in the Neon SQL Editor (or psql) to create all tables
-- -----------------------------------------------------------------------

create extension if not exists "pgcrypto";

-- -----------------------------------------------------------------------
-- USERS  (auth)
-- -----------------------------------------------------------------------
create table if not exists users (
  id               text primary key,          -- UUID string
  email            text not null unique,
  hashed_password  text not null,
  display_name     text,
  created_at       timestamptz default now()
);

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

-- -----------------------------------------------------------------------
-- EXECUTIONS
-- -----------------------------------------------------------------------
create table if not exists executions (
  id                     text primary key,     -- UUID string from Python
  canvas_id              text not null,        -- references canvases.id (soft ref)
  session_id             text not null,        -- WebSocket session 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,
  memories_created       int default 0,
  conflicts_detected     int default 0,
  duration_ms            int default 0,
  agents_run             text[] default '{}'
);

create index if not exists executions_canvas_idx on executions(canvas_id);
create index if not exists executions_status_idx on executions(status);
create index if not exists executions_started_idx on executions(started_at desc);

-- -----------------------------------------------------------------------
-- MEMORY EVENTS  (every Mem0 SDK call logged for the Transcripts page)
-- -----------------------------------------------------------------------
create table if not exists memory_events (
  id              uuid primary key default gen_random_uuid(),
  execution_id    text references executions(id) on delete cascade,
  canvas_id       text not null,
  agent_id        text not null,
  event_type      text check (event_type in ('MEMORY_READ','MEMORY_WRITE')),
  content         text,
  mem0_id         text,        -- ID returned by Mem0 SDK
  category        text,
  scope           text,        -- 'shared' | 'private'
  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      text 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      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);

-- -----------------------------------------------------------------------
-- 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();

-- -----------------------------------------------------------------------
-- Seed demo canvas record
-- -----------------------------------------------------------------------
insert into canvases (id, name, is_demo) values
  ('demo-canvas-v1', 'Customer Support Demo', true)
on conflict (id) do nothing;