canvas/migrations/001_create_tables.sql

-- @blinksgg/canvas Database Schema
-- Run these migrations in your Supabase project

-- ============================================================
-- Graphs Table
-- ============================================================
create table if not exists public.graphs (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid references auth.users(id) on delete cascade,
  name text not null default 'Untitled',
  description text,
  -- Customization data (style guide, settings, etc.)
  data jsonb default '{}',
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null
);

-- RLS policies for graphs
alter table public.graphs enable row level security;

create policy "Users can view their own graphs"
  on public.graphs for select
  using (auth.uid() = owner_id);

create policy "Users can create their own graphs"
  on public.graphs for insert
  with check (auth.uid() = owner_id);

create policy "Users can update their own graphs"
  on public.graphs for update
  using (auth.uid() = owner_id);

create policy "Users can delete their own graphs"
  on public.graphs for delete
  using (auth.uid() = owner_id);

-- ============================================================
-- Nodes Table
-- ============================================================
create table if not exists public.nodes (
  id uuid primary key default gen_random_uuid(),
  graph_id uuid not null references public.graphs(id) on delete cascade,
  label text,
  node_type text,
  -- Configuration for the node type (inputs, settings, etc.)
  configuration jsonb default '{}',
  -- UI properties (position, size, color, zIndex, etc.)
  ui_properties jsonb default '{}',
  -- Customization data (app-specific data)
  data jsonb default '{}',
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null
);

-- Index for fast lookup by graph
create index if not exists idx_nodes_graph_id on public.nodes(graph_id);

-- RLS policies for nodes
alter table public.nodes enable row level security;

create policy "Users can view nodes in their graphs"
  on public.nodes for select
  using (
    exists (
      select 1 from public.graphs
      where graphs.id = nodes.graph_id
      and graphs.owner_id = auth.uid()
    )
  );

create policy "Users can create nodes in their graphs"
  on public.nodes for insert
  with check (
    exists (
      select 1 from public.graphs
      where graphs.id = graph_id
      and graphs.owner_id = auth.uid()
    )
  );

create policy "Users can update nodes in their graphs"
  on public.nodes for update
  using (
    exists (
      select 1 from public.graphs
      where graphs.id = nodes.graph_id
      and graphs.owner_id = auth.uid()
    )
  );

create policy "Users can delete nodes in their graphs"
  on public.nodes for delete
  using (
    exists (
      select 1 from public.graphs
      where graphs.id = nodes.graph_id
      and graphs.owner_id = auth.uid()
    )
  );

-- ============================================================
-- Edges Table
-- ============================================================
create table if not exists public.edges (
  id uuid primary key default gen_random_uuid(),
  graph_id uuid not null references public.graphs(id) on delete cascade,
  source_node_id uuid not null references public.nodes(id) on delete cascade,
  target_node_id uuid not null references public.nodes(id) on delete cascade,
  edge_type text,
  -- Filter/transform condition for data flow
  filter_condition jsonb,
  -- UI properties (style, color, weight, etc.)
  ui_properties jsonb default '{}',
  -- Customization data (app-specific data)
  data jsonb default '{}',
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null
);

-- Index for fast lookup by graph and source/target
create index if not exists idx_edges_graph_id on public.edges(graph_id);
create index if not exists idx_edges_source_node_id on public.edges(source_node_id);
create index if not exists idx_edges_target_node_id on public.edges(target_node_id);

-- RLS policies for edges
alter table public.edges enable row level security;

create policy "Users can view edges in their graphs"
  on public.edges for select
  using (
    exists (
      select 1 from public.graphs
      where graphs.id = edges.graph_id
      and graphs.owner_id = auth.uid()
    )
  );

create policy "Users can create edges in their graphs"
  on public.edges for insert
  with check (
    exists (
      select 1 from public.graphs
      where graphs.id = graph_id
      and graphs.owner_id = auth.uid()
    )
  );

create policy "Users can update edges in their graphs"
  on public.edges for update
  using (
    exists (
      select 1 from public.graphs
      where graphs.id = edges.graph_id
      and graphs.owner_id = auth.uid()
    )
  );

create policy "Users can delete edges in their graphs"
  on public.edges for delete
  using (
    exists (
      select 1 from public.graphs
      where graphs.id = edges.graph_id
      and graphs.owner_id = auth.uid()
    )
  );

-- ============================================================
-- Updated At Trigger
-- ============================================================
create or replace function public.handle_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

create trigger set_graphs_updated_at
  before update on public.graphs
  for each row execute function public.handle_updated_at();

create trigger set_nodes_updated_at
  before update on public.nodes
  for each row execute function public.handle_updated_at();

create trigger set_edges_updated_at
  before update on public.edges
  for each row execute function public.handle_updated_at();