2.3 The Schema Blueprint

-- 1. Setup: Enable UUID generation
-- This allows us to create unique IDs for every deal
create extension if not exists "uuid-ossp";
 
-- 2. Create the AGENTS table
-- This links to the built-in Supabase Auth system
create table public.agents (
  id uuid references auth.users not null primary key,
  full_name text,
  email text,
  license_number text,
 
  -- Settings for "Amy"
  amy_persona jsonb default '{"tone": "professional", "latency": "medium"}',
 
  -- Billing
  stripe_customer_id text,
  subscription_status text default 'active',
 
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);
 
-- 3. Create the DEALS table
-- The container for every transaction
create table public.deals (
  id uuid default uuid_generate_v4() primary key,
  agent_id uuid references public.agents(id) not null,
 
  -- Property Info
  property_address text not null,
  status text default 'pending_setup', -- active, pending, closed, cancelled
 
  -- The External Links (The Integrations)
  inbox_id text, -- AgentMail ID
  skyslope_email text, -- For filing
  docusign_room_id text, -- For forms
 
  -- Critical Dates (Extracted by AI)
  closing_date date,
  contingency_date date,
 
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);
 
-- 4. Create the STAKEHOLDERS table
-- The Rolodex. This is how Amy knows who is emailing her.
create table public.stakeholders (
  id uuid default uuid_generate_v4() primary key,
  deal_id uuid references public.deals(id) not null,
 
  name text,
  email text not null,
  role text not null, -- 'lender', 'buyer', 'seller_agent'
  phone text,
 
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);
 
-- 5. Create the LOGS table
-- An audit trail of everything Amy does (for liability)
create table public.activity_logs (
  id uuid default uuid_generate_v4() primary key,
  deal_id uuid references public.deals(id),
  action_type text, -- 'email_sent', 'doc_filed', 'draft_created'
  description text,
  timestamp timestamp with time zone default timezone('utc'::text, now()) not null
);
 
-- 6. Enable Row Level Security (The Safety Lock)
-- This ensures Agent A cannot read Agent B's deals
alter table public.agents enable row level security;
alter table public.deals enable row level security;
alter table public.stakeholders enable row level security;
 
-- 7. Create Security Policies
-- Policy: "Agents can view their own deals"
create policy "Agents can view their own deals"
  on public.deals for select
  using ( auth.uid() = agent_id );
 
-- Policy: "Agents can update their own deals"
create policy "Agents can update their own deals"
  on public.deals for update
  using ( auth.uid() = agent_id );
 
-- Policy: "Agents can view their own stakeholders"
create policy "Agents can view stakeholders"
  on public.stakeholders for select
  using (
    exists (
      select 1 from public.deals
      where public.deals.id = public.stakeholders.deal_id
      and public.deals.agent_id = auth.uid()
    )
  );