Supabase Database Architecture

Security & Compliance Requirements

SOC II Compliance

All sensitive data must be encrypted at rest
Audit logging for all data access and modifications
Role-based access control (RBAC) with principle of least privilege
Data backup and recovery procedures
Regular security assessments and monitoring

Data Protection

API keys and OAuth tokens stored with encryption
Personal data encrypted and access-logged
File storage with secure access policies
Environment variables stored securely

Database Schema

Core Tables

users

create table users (
  -- Mirror auth.users columns
  id uuid primary key references auth.users(id) on delete cascade,
  email text unique not null,
  phone text unique,
  confirmed_at timestamp with time zone,
  email_confirmed_at timestamp with time zone,
  last_sign_in_at timestamp with time zone,
  raw_app_meta_data jsonb,
  raw_user_meta_data jsonb,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now(),
  deleted_at timestamp with time zone,
  -- Extended columns
  profile jsonb default '{}',
  status text not null check (status in ('invited', 'waitlist', 'active', 'inactive', 'banned', 'archived')) default 'waitlist',
  is_super_user boolean default false
);
-- RLS Policies
alter table users enable row level security;
-- Users can read their own data
create policy "Users can read own data" on users
for select using (auth.uid() = id);
-- Super users can read all data
create policy "Super users can read all data" on users
for select using (
  auth.uid() in (
    select id from users where is_super_user = true
  )
);

-- Function to copy auth user to users table
create function sync_auth_user_to_users()
returns trigger as $$
begin
  insert into users (
    id,
    email,
    phone,
    confirmed_at,
    email_confirmed_at,
    last_sign_in_at,
    raw_app_meta_data,
    raw_user_meta_data,
    created_at,
    updated_at
  ) values (
    NEW.id,
    NEW.email,
    NEW.phone,
    NEW.confirmed_at,
    NEW.email_confirmed_at,
    NEW.last_sign_in_at,
    NEW.raw_app_meta_data,
    NEW.raw_user_meta_data,
    NEW.created_at,
    NEW.updated_at
  );
  return NEW;
end;
$$ language plpgsql security definer;

-- Trigger to sync auth user to users table
create trigger on_auth_user_created_sync
  after insert on auth.users
  for each row
  execute function sync_auth_user_to_users();

-- Function to check workspace email domain and add user
create function check_and_add_user_to_workspace()
returns trigger as $$
declare
  user_domain text;
  matching_workspace record;
begin
  -- Extract domain from email
  user_domain := split_part(NEW.email, '@', 2);
  
  -- Find matching workspace
  for matching_workspace in
    select * from workspaces
    where company_email_domains @> array[user_domain]
    and not (settings->>'is_global')::boolean = true
  loop
    -- Add user to workspace
    insert into workspace_users (
      workspace_id,
      user_id,
      role
    ) values (
      matching_workspace.id,
      NEW.id,
      'project_creator'
    );
    
    -- Update user status to active if matched
    update users
    set status = 'active'
    where id = NEW.id;
    
    return NEW;
  end loop;
  
  -- If no matching workspace and no invitation exists, keep as waitlist
  if not exists (
    select 1 from workspace_users
    where user_id = NEW.id
  ) then
    update users
    set status = 'waitlist'
    where id = NEW.id;
  end if;
  
  return NEW;
end;
$$ language plpgsql security definer;

-- Trigger to check workspace email domain and add user
create trigger on_user_created_check_domain
  after insert on users
  for each row
  execute function check_and_add_user_to_workspace();

-- Function to update user status based on workspace/project membership
create function update_user_status()
returns trigger as $$
begin
  -- Update user status to active when added to workspace or project
  update users
  set status = 'active'
  where id = NEW.user_id
  and status in ('waitlist', 'invited');
  
  return NEW;
end;
$$ language plpgsql security definer;

-- Triggers to update user status
create trigger on_workspace_user_added
  after insert on workspace_users
  for each row
  execute function update_user_status();

create trigger on_project_user_added
  after insert on project_users
  for each row
  execute function update_user_status();

auth.users (managed by Supabase)

Extended with custom profile data
Links to workspace and project roles

workspaces

create table workspaces (
id uuid primary key default uuid_generate_v4(),
created_at timestamp with time zone default now(),
updated_at timestamp with time zone default now(),
name text not null,
description text,
company_name text not null,
company_email_domains text[], -- Verified company domains
logo_url text,
color_palette jsonb,
settings jsonb default '{}',
is_active boolean default true,
deleted_at timestamp with time zone
);
-- RLS Policies
alter table workspaces enable row level security;
-- Anyone can read active workspaces they're a member of
create policy "Read workspaces for members" on workspaces
for select using (
auth.uid() in (
select user_id from workspace_users
where workspace_id = workspaces.id
)
);
-- Only workspace owners can update
create policy "Update workspace for owners" on workspaces
for update using (
auth.uid() in (
select user_id from workspace_users
where workspace_id = workspaces.id
and role = 'owner'
)
);

workspace_users

create table workspace_users (
id uuid primary key default uuid_generate_v4(),
workspace_id uuid references workspaces(id) on delete cascade,
user_id uuid references users(id) on delete cascade,
role text not null check (role in ('owner', 'admin', 'project_creator')),
created_at timestamp with time zone default now(),
invited_by uuid references users(id),
unique(workspace_id, user_id),
constraint valid_role check (role in ('owner', 'admin', 'project_creator'))
);
-- RLS Policies
alter table workspace_users enable row level security;
-- Workspace members can read other members
create policy "Read workspace members" on workspace_users
for select using (
auth.uid() in (
select user_id from workspace_users
where workspace_id = workspace_users.workspace_id
)
);
-- Only owners/admins can manage members
create policy "Manage members for admins" on workspace_users
for all using (
auth.uid() in (
select user_id from workspace_users
where workspace_id = workspace_users.workspace_id
and role in ('owner', 'admin')
)
);

projects

create table projects (
id uuid primary key default uuid_generate_v4(),
workspace_id uuid references workspaces(id) on delete cascade,
created_at timestamp with time zone default now(),
updated_at timestamp with time zone default now(),
name text not null,
description text,
logo_url text,
color_palette jsonb,
settings jsonb default '{}',
is_active boolean default true,
deleted_at timestamp with time zone
);
-- RLS Policies
alter table projects enable row level security;
-- Project members can read
create policy "Read projects for members" on projects
for select using (
auth.uid() in (
select user_id from project_users
where project_id = projects.id
)
);
-- Project managers and workspace admins can update
create policy "Update project for managers" on projects
for update using (
auth.uid() in (
select user_id from project_users
where project_id = projects.id
and role in ('owner', 'manager')
) or
auth.uid() in (
select user_id from workspace_users
where workspace_id = projects.workspace_id
and role in ('owner', 'admin')
)
);

project_users

create table project_users (
id uuid primary key default uuid_generate_v4(),
project_id uuid references projects(id) on delete cascade,
user_id uuid references users(id) on delete cascade,
role text not null,
created_at timestamp with time zone default now(),
invited_by uuid references users(id),
unique(project_id, user_id),
constraint valid_role check (
role in ('owner', 'manager', 'contributor', 'viewer')
)
);
-- RLS Policies
alter table project_users enable row level security;
-- Project members can read other members
create policy "Read project members" on project_users
for select using (
auth.uid() in (
select user_id from project_users
where project_id = project_users.project_id
)
);
-- Only project managers and workspace admins can manage members
create policy "Manage project members" on project_users
for all using (
auth.uid() in (
select user_id from project_users
where project_id = project_users.project_id
and role in ('owner', 'manager')
) or
auth.uid() in (
select user_id from workspace_users
where workspace_id = (
select workspace_id from projects
where id = project_users.project_id
)
and role in ('owner', 'admin')
)
);

API & Integration Tables

api_connections

create table api_connections (
  id uuid primary key default uuid_generate_v4(),
  workspace_id uuid references workspaces(id) on delete cascade,
  project_id uuid references projects(id) on delete cascade,
  name text not null,
  description text,
  api_type text not null,
  endpoint_url text not null,
  auth_type text not null check (auth_type in ('bearer', 'basic', 'api_key', 'none')),
  auth_token text,
  custom_headers jsonb default '{}',
  http_method text not null check (http_method in ('GET', 'POST', 'PUT', 'DELETE', 'PATCH')),
  request_body_template jsonb default '{}',
  response_path text,
  response_schema jsonb default '{}',
  credentials jsonb not null,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now(),
  created_by uuid references auth.users(id),
  is_active boolean default true,
  -- Either workspace_id or project_id must be set, not both
  constraint scope_check check (
    (workspace_id is null and project_id is not null) or
    (workspace_id is not null and project_id is null)
  )
);
-- RLS Policies
alter table api_connections enable row level security;
-- Read access for workspace/project members
create policy "Read API connections" on api_connections
for select using (
  (
    workspace_id is not null and
    auth.uid() in (
      select user_id from workspace_users
      where workspace_id = api_connections.workspace_id
    )
  ) or (
    project_id is not null and
    auth.uid() in (
      select user_id from project_users
      where project_id = api_connections.project_id
    )
  )
);
-- Only admins can manage API connections
create policy "Manage API connections" on api_connections
for all using (
  (
    workspace_id is not null and
    auth.uid() in (
      select user_id from workspace_users
      where workspace_id = api_connections.workspace_id
      and role in ('owner', 'admin')
    )
  ) or (
    project_id is not null and
    auth.uid() in (
      select user_id from project_users
      where project_id = api_connections.project_id
      and role in ('owner', 'manager')
    )
  )
);

oauth2_connections

create table oauth2_connections (
  id uuid primary key default uuid_generate_v4(),
  workspace_id uuid references workspaces(id) on delete cascade,
  project_id uuid references projects(id) on delete cascade,
  provider text not null,
  provider_config jsonb not null,
  client_id text not null,
  client_secret text not null,
  redirect_uri text not null,
  scopes text[] not null,
  additional_params jsonb default '{}',
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now(),
  created_by uuid references auth.users(id),
  is_active boolean default true,
  -- Either workspace_id or project_id must be set, not both
  constraint scope_check check (
    (workspace_id is null and project_id is not null) or
    (workspace_id is not null and project_id is null)
  )
);
-- RLS Policies
alter table oauth2_connections enable row level security;
-- Read access for workspace/project members
create policy "Read OAuth2 connections" on oauth2_connections
for select using (
  (
    workspace_id is not null and
    auth.uid() in (
      select user_id from workspace_users
      where workspace_id = oauth2_connections.workspace_id
    )
  ) or (
    project_id is not null and
    auth.uid() in (
      select user_id from project_users
      where project_id = oauth2_connections.project_id
    )
  )
);
-- Only admins can manage OAuth2 connections
create policy "Manage OAuth2 connections" on oauth2_connections
for all using (
  (
    workspace_id is not null and
    auth.uid() in (
      select user_id from workspace_users
      where workspace_id = oauth2_connections.workspace_id
      and role in ('owner', 'admin')
    )
  ) or (
    project_id is not null and
    auth.uid() in (
      select user_id from project_users
      where project_id = oauth2_connections.project_id
      and role in ('owner', 'manager')
    )
  )
);

oauth2_tokens

create table oauth2_tokens (
  id uuid primary key default uuid_generate_v4(),
  connection_id uuid references oauth2_connections(id) on delete cascade,
  access_token text not null,
  refresh_token text,
  token_type text not null,
  expires_at timestamp with time zone,
  scope text,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);
-- RLS Policies
alter table oauth2_tokens enable row level security;
-- Inherit connection access permissions
create policy "Access OAuth2 tokens" on oauth2_tokens
for all using (
  exists (
    select 1 from oauth2_connections
    where id = oauth2_tokens.connection_id
    and (
      (
        workspace_id is not null and
        auth.uid() in (
          select user_id from workspace_users
          where workspace_id = oauth2_connections.workspace_id
        )
      ) or (
        project_id is not null and
        auth.uid() in (
          select user_id from project_users
          where project_id = oauth2_connections.project_id
        )
      )
    )
  )
);

Chat & Messages Tables

chat_sessions

create table chat_sessions (
  id uuid primary key default uuid_generate_v4(),
  project_id uuid references projects(id) on delete cascade,
  title text not null,
  description text,
  settings jsonb default '{}',
  is_active boolean default true,
  created_at timestamp with time zone default now(),
  created_by uuid references auth.users(id),
  updated_at timestamp with time zone default now(),
  updated_by uuid references auth.users(id)
);
-- RLS Policies
alter table chat_sessions enable row level security;
-- Project members can read chat sessions
create policy "Read chat sessions" on chat_sessions
for select using (
  auth.uid() in (
    select user_id from project_users
    where project_id = chat_sessions.project_id
  )
);
-- Project contributors and up can manage chat sessions
create policy "Manage chat sessions" on chat_sessions
for all using (
  auth.uid() in (
    select user_id from project_users
    where project_id = chat_sessions.project_id
    and role in ('owner', 'manager', 'contributor')
  )
);

chat_messages

create table chat_messages (
  id uuid primary key default uuid_generate_v4(),
  chat_id uuid references chat_sessions(id) on delete cascade,
  content jsonb not null,
  message_type text not null check (message_type in ('user', 'ai', 'system')),
  agent_type text check (agent_type in ('orchestrator', 'requirements', 'architect', 'designer', 'coder', 'quality', 'search', 'scanning', 'data-validation')),
  metadata jsonb default '{}',
  created_at timestamp with time zone default now(),
  created_by uuid references auth.users(id),
  updated_at timestamp with time zone default now()
);
-- RLS Policies
alter table chat_messages enable row level security;
-- Inherit chat session access
create policy "Access chat messages" on chat_messages
for all using (
  exists (
    select 1 from chat_sessions
    where id = chat_messages.chat_id
    and auth.uid() in (
      select user_id from project_users
      where project_id = chat_sessions.project_id
    )
  )
);

chat_participants

create table chat_participants (
  id uuid primary key default uuid_generate_v4(),
  chat_id uuid references chat_sessions(id) on delete cascade,
  user_id uuid references auth.users(id) on delete cascade,
  joined_at timestamp with time zone default now(),
  last_read_at timestamp with time zone default now(),
  is_active boolean default true,
  unique(chat_id, user_id)
);
-- RLS Policies
alter table chat_participants enable row level security;
-- Users can read participant info for their chats
create policy "Read chat participants" on chat_participants
for select using (
  exists (
    select 1 from chat_sessions
    where id = chat_participants.chat_id
    and auth.uid() in (
      select user_id from project_users
      where project_id = chat_sessions.project_id
    )
  )
);
-- Users can manage their own participant status
create policy "Manage own participant status" on chat_participants
for all using (
  auth.uid() = user_id
);

Artifacts & Documents

artifacts

create table artifacts (
id uuid primary key default uuid_generate_v4(),
project_id uuid references projects(id) on delete cascade,
chat_id uuid references chat_sessions(id),
title text not null,
description text,
artifact_type text not null,
status text not null default 'pending',
progress integer default 0,
created_at timestamp with time zone default now(),
created_by uuid references auth.users(id),
updated_at timestamp with time zone default now(),
metadata jsonb default '{}',
is_public boolean default false
);
-- RLS Policies
alter table artifacts enable row level security;
-- Project members can read artifacts
create policy "Read artifacts" on artifacts
for select using (
auth.uid() in (
select user_id from project_users
where project_id = artifacts.project_id
) or
(is_public = true)
);
-- Project contributors and up can create/update artifacts
create policy "Manage artifacts" on artifacts
for all using (
auth.uid() in (
select user_id from project_users
where project_id = artifacts.project_id
and role in ('owner', 'manager', 'contributor')
)
);

artifact_files

create table artifact_files (
id uuid primary key default uuid_generate_v4(),
artifact_id uuid references artifacts(id) on delete cascade,
file_name text not null,
file_type text not null,
file_size integer not null,
file_path text not null, -- Path for building/rendering the app
is_main_file boolean default false, -- Flag for main file in artifact
storage_path text not null,
created_at timestamp with time zone default now(),
created_by uuid references auth.users(id),
updated_at timestamp with time zone default now(),
updated_by uuid references auth.users(id),
metadata jsonb default '{}'
);
-- RLS Policies
alter table artifact_files enable row level security;
-- Inherit artifact access permissions
create policy "Access artifact files" on artifact_files
for all using (
exists (
select 1 from artifacts
where id = artifact_files.artifact_id
and (
auth.uid() in (
select user_id from project_users
where project_id = artifacts.project_id
) or
artifacts.is_public = true
)
)
);

-- Add constraint to ensure only one main file per artifact
alter table artifact_files
add constraint one_main_file_per_artifact
unique (artifact_id, is_main_file)
where is_main_file = true;

Storage Buckets

-- Create secure storage buckets
insert into storage.buckets (id, name, public) values
('workspace-files', 'workspace-files', false),
('project-files', 'project-files', false),
('artifact-files', 'artifact-files', false);
-- Storage policies for workspace files
create policy "Workspace file access" on storage.objects
for all using (
bucket_id = 'workspace-files' and
auth.uid() in (
select user_id from workspace_users
where workspace_id = ( -- Extract workspace_id from path
(storage.foldername(name)):uuid
)
)
);
-- Storage policies for project files
create policy "Project file access" on storage.objects
for all using (
bucket_id = 'project-files' and
auth.uid() in (
select user_id from project_users
where project_id = ( -- Extract project_id from path
(storage.foldername(name)):uuid
)
)
);
-- Storage policies for artifact files
create policy "Artifact file access" on storage.objects
for all using (
bucket_id = 'artifact-files' and
exists (
select 1 from artifacts
where id = ( -- Extract artifact_id from path
(storage.foldername(name)):uuid
)
and (
auth.uid() in (
select user_id from project_users
where project_id = artifacts.project_id
) or
artifacts.is_public = true
)
)
);

Indexes

-- Performance indexes
create index workspace_users_user_id_idx on workspace_users(user_id);
create index project_users_user_id_idx on project_users(user_id);
create index chat_messages_chat_id_created_at_idx on chat_messages(chat_id, created_at);
create index artifacts_project_id_idx on artifacts(project_id);
create index artifact_files_artifact_id_idx on artifact_files(artifact_id);
-- Search indexes
create index workspaces_name_idx on workspaces using gin(to_tsvector('english', name));
create index projects_name_idx on projects using gin(to_tsvector('english', name));
create index artifacts_title_idx on artifacts using gin(to_tsvector('english', title));

Functions & Triggers

-- Update timestamp trigger
create function update_updated_at()
returns trigger as $$
begin
new.updated_at = now();
return new;
end;
$$ language plpgsql;
-- Apply to relevant tables
create trigger update_workspaces_updated_at
before update on workspaces
for each row execute function update_updated_at();
-- (Add similar triggers for other tables)
-- Audit logging function
create function audit_log_changes()
returns trigger as $$
begin
insert into audit_logs (
table_name,
record_id,
action,
old_data,
new_data,
changed_by
) values (
TG_TABLE_NAME,
case
when TG_OP = 'DELETE' then old.id
else new.id
end,
TG_OP,
case when TG_OP = 'INSERT' then null else row_to_json(old) end,
case when TG_OP = 'DELETE' then null else row_to_json(new) end,
auth.uid()
);
return null;
end;
$$ language plpgsql;
-- Apply audit logging to sensitive tables
create trigger audit_workspaces
after insert or update or delete on workspaces
for each row execute function audit_log_changes();
-- (Add similar triggers for other sensitive tables)

Security Functions

-- Encrypt sensitive data
create function encrypt_sensitive_data(data jsonb)
returns text as $$
begin
-- Implementation using pgcrypto
return encode(
encrypt(
data::text::bytea,
current_setting('app.encryption_key'),
'aes'
),
'base64'
);
end;
$$ language plpgsql security definer;
-- Decrypt sensitive data
create function decrypt_sensitive_data(encrypted_data text)
returns jsonb as $$
begin
return decrypt(
decode(encrypted_data, 'base64'),
current_setting('app.encryption_key'),
'aes'
)::text::jsonb;
end;
$$ language plpgsql security definer;

Backup & Recovery

-- Create backup function
create function create_backup()
returns void as $$
begin
-- Implementation for scheduled backups
end;
$$ language plpgsql;
-- Schedule daily backups
select cron.schedule(
'daily-backup',
'0 0 ',
'select create_backup()'
);

Billing & Usage Tables

workspace_subscriptions

create table workspace_subscriptions (
  id uuid primary key default uuid_generate_v4(),
  workspace_id uuid references workspaces(id) on delete cascade,
  stripe_customer_id text not null,
  stripe_subscription_id text not null,
  plan_type text not null check (plan_type in ('free', 'hobby', 'pro', 'enterprise')),
  base_token_limit bigint not null,
  additional_tokens bigint default 0,
  status text not null check (status in ('active', 'past_due', 'canceled', 'incomplete')),
  current_period_start timestamp with time zone,
  current_period_end timestamp with time zone,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);
-- RLS Policies
alter table workspace_subscriptions enable row level security;
-- Only workspace owners and admins can read subscription data
create policy "Read subscription data" on workspace_subscriptions
for select using (
  auth.uid() in (
    select user_id from workspace_users
    where workspace_id = workspace_subscriptions.workspace_id
    and role in ('owner', 'admin')
  )
);

token_purchases

create table token_purchases (
  id uuid primary key default uuid_generate_v4(),
  workspace_id uuid references workspaces(id) on delete cascade,
  stripe_payment_intent_id text not null,
  token_amount bigint not null,
  price_paid decimal(10,2) not null,
  status text not null check (status in ('pending', 'completed', 'failed')),
  created_at timestamp with time zone default now(),
  created_by uuid references auth.users(id)
);
-- RLS Policies
alter table token_purchases enable row level security;
-- Workspace owners and admins can read token purchases
create policy "Read token purchases" on token_purchases
for select using (
  auth.uid() in (
    select user_id from workspace_users
    where workspace_id = token_purchases.workspace_id
    and role in ('owner', 'admin')
  )
);

token_usage

create table token_usage (
  id uuid primary key default uuid_generate_v4(),
  workspace_id uuid references workspaces(id) on delete cascade,
  project_id uuid references projects(id) on delete cascade,
  chat_id uuid references chat_sessions(id) on delete cascade,
  tokens_used bigint not null,
  usage_type text not null check (usage_type in ('chat', 'artifact_generation', 'search')),
  created_at timestamp with time zone default now(),
  created_by uuid references auth.users(id)
);
-- RLS Policies
alter table token_usage enable row level security;
-- Users can read token usage for workspaces/projects they have access to
create policy "Read token usage" on token_usage
for select using (
  auth.uid() in (
    select user_id from workspace_users
    where workspace_id = token_usage.workspace_id
  ) or
  auth.uid() in (
    select user_id from project_users
    where project_id = token_usage.project_id
  )
);

Statistics Tables

workspace_stats

create table workspace_stats (
  id uuid primary key default uuid_generate_v4(),
  workspace_id uuid references workspaces(id) on delete cascade,
  total_projects integer not null default 0,
  total_users integer not null default 0,
  total_chats integer not null default 0,
  total_artifacts integer not null default 0,
  total_files integer not null default 0,
  total_tokens_used bigint not null default 0,
  tokens_remaining bigint not null default 0,
  stats_date date not null default current_date,
  created_at timestamp with time zone default now()
);
-- RLS Policies
alter table workspace_stats enable row level security;
-- Workspace members can read stats
create policy "Read workspace stats" on workspace_stats
for select using (
  auth.uid() in (
    select user_id from workspace_users
    where workspace_id = workspace_stats.workspace_id
  )
);

project_stats

create table project_stats (
  id uuid primary key default uuid_generate_v4(),
  project_id uuid references projects(id) on delete cascade,
  workspace_id uuid references workspaces(id) on delete cascade,
  total_users integer not null default 0,
  total_chats integer not null default 0,
  total_artifacts integer not null default 0,
  total_files integer not null default 0,
  total_tokens_used bigint not null default 0,
  tokens_remaining bigint not null default 0,
  stats_date date not null default current_date,
  created_at timestamp with time zone default now()
);
-- RLS Policies
alter table project_stats enable row level security;
-- Project members can read stats
create policy "Read project stats" on project_stats
for select using (
  auth.uid() in (
    select user_id from project_users
    where project_id = project_stats.project_id
  )
);

Audit Logging Tables

audit_logs

create table audit_logs (
  id uuid primary key default uuid_generate_v4(),
  workspace_id uuid references workspaces(id) on delete cascade,
  project_id uuid references projects(id) on delete cascade,
  table_name text not null,
  record_id uuid not null,
  action text not null check (action in ('INSERT', 'UPDATE', 'DELETE')),
  old_data jsonb,
  new_data jsonb,
  changed_by uuid references auth.users(id),
  ip_address text,
  user_agent text,
  created_at timestamp with time zone default now()
);
-- RLS Policies
alter table audit_logs enable row level security;
-- Workspace owners and admins can read audit logs
create policy "Read workspace audit logs" on audit_logs
for select using (
  auth.uid() in (
    select user_id from workspace_users
    where workspace_id = audit_logs.workspace_id
    and role in ('owner', 'admin')
  )
);
-- Project managers can read project audit logs
create policy "Read project audit logs" on audit_logs
for select using (
  auth.uid() in (
    select user_id from project_users
    where project_id = audit_logs.project_id
    and role in ('owner', 'manager')
  )
);

-- Add audit logging triggers to relevant tables
create trigger audit_workspace_subscriptions
after insert or update or delete on workspace_subscriptions
for each row execute function audit_log_changes();

create trigger audit_token_purchases
after insert or update or delete on token_purchases
for each row execute function audit_log_changes();

create trigger audit_token_usage
after insert or update or delete on token_usage
for each row execute function audit_log_changes();

-- Add audit logging trigger
create trigger audit_api_connections
after insert or update or delete on api_connections
for each row execute function audit_log_changes();

Global Workspace & Personal Project Functions

-- Function to create the default global workspace
create function create_default_global_workspace()
returns uuid as $$
declare
  workspace_id uuid;
begin
  insert into workspaces (
    name,
    description,
    company_name,
    company_email_domains,
    settings,
    is_active
  ) values (
    'Global Workspace',
    'Default global workspace for all users',
    'System',
    array['*'], -- Accepts all email domains
    jsonb_build_object(
      'is_global', true,
      'managed_by_super_user', true
    ),
    true
  ) returning id into workspace_id;

  -- Create free subscription for global workspace
  insert into workspace_subscriptions (
    workspace_id,
    stripe_customer_id,
    stripe_subscription_id,
    plan_type,
    base_token_limit,
    status
  ) values (
    workspace_id,
    'global_workspace',
    'global_subscription',
    'free',
    1000000, -- 1 million tokens
    'active'
  );

  return workspace_id;
end;
$$ language plpgsql security definer;

-- Function to add user to global workspace
create function add_user_to_global_workspace()
returns trigger as $$
declare
  global_workspace_id uuid;
begin
  -- Get the global workspace ID
  select id into global_workspace_id
  from workspaces
  where (settings->>'is_global')::boolean = true
  limit 1;

  -- Add user to global workspace
  insert into workspace_users (
    workspace_id,
    user_id,
    role
  ) values (
    global_workspace_id,
    NEW.id,
    'project_creator'
  );

  -- Create personal project for the user
  insert into projects (
    workspace_id,
    name,
    description,
    settings
  ) values (
    global_workspace_id,
    NEW.email || '''s Personal Project',
    'Personal project space',
    jsonb_build_object(
      'is_personal', true,
      'owner_id', NEW.id
    )
  );

  return NEW;
end;
$$ language plpgsql security definer;

-- Trigger to add new users to global workspace and create personal project
create trigger on_auth_user_created
  after insert on auth.users
  for each row
  execute function add_user_to_global_workspace();

-- Function to archive personal project on user deletion
create function archive_personal_project()
returns trigger as $$
begin
  -- Archive the personal project instead of deleting
  update projects
  set 
    is_active = false,
    deleted_at = now(),
    settings = settings || jsonb_build_object('archived_reason', 'user_deleted')
  where 
    workspace_id = (
      select id from workspaces 
      where (settings->>'is_global')::boolean = true 
      limit 1
    )
    and (settings->>'is_personal')::boolean = true
    and (settings->>'owner_id')::uuid = OLD.id;

  return OLD;
end;
$$ language plpgsql security definer;

-- Trigger to archive personal project when user is deleted
create trigger on_auth_user_deleted
  before delete on auth.users
  for each row
  execute function archive_personal_project();

-- Modify projects table RLS policies to handle personal projects
create policy "Read personal project"
  on projects
  for select
  using (
    auth.uid() = (settings->>'owner_id')::uuid
    and (settings->>'is_personal')::boolean = true
  );

create policy "Manage personal project"
  on projects
  for all
  using (
    auth.uid() = (settings->>'owner_id')::uuid
    and (settings->>'is_personal')::boolean = true
  );

-- Initialize global workspace on database creation
do $$
begin
  perform create_default_global_workspace();
end $$;

Project Context Tables

project_requirements

create table project_requirements (
  id uuid primary key default uuid_generate_v4(),
  project_id uuid references projects(id) on delete cascade,
  title text not null,
  content jsonb not null,
  version integer not null default 1,
  status text not null check (status in ('draft', 'review', 'approved', 'archived')),
  created_at timestamp with time zone default now(),
  created_by uuid references auth.users(id),
  updated_at timestamp with time zone default now(),
  updated_by uuid references auth.users(id)
);
-- RLS Policies
alter table project_requirements enable row level security;
-- Project members can read requirements
create policy "Read project requirements" on project_requirements
for select using (
  auth.uid() in (
    select user_id from project_users
    where project_id = project_requirements.project_id
  )
);
-- Project managers and up can manage requirements
create policy "Manage project requirements" on project_requirements
for all using (
  auth.uid() in (
    select user_id from project_users
    where project_id = project_requirements.project_id
    and role in ('owner', 'manager')
  )
);

technical_architecture

create table technical_architecture (
  id uuid primary key default uuid_generate_v4(),
  project_id uuid references projects(id) on delete cascade,
  title text not null,
  content jsonb not null,
  version integer not null default 1,
  status text not null check (status in ('draft', 'review', 'approved', 'archived')),
  apis_used jsonb default '[]',
  data_schemas jsonb default '[]',
  security_requirements jsonb default '[]',
  created_at timestamp with time zone default now(),
  created_by uuid references auth.users(id),
  updated_at timestamp with time zone default now(),
  updated_by uuid references auth.users(id)
);
-- RLS Policies
alter table technical_architecture enable row level security;
-- Project members can read architecture
create policy "Read technical architecture" on technical_architecture
for select using (
  auth.uid() in (
    select user_id from project_users
    where project_id = technical_architecture.project_id
  )
);
-- Project managers and up can manage architecture
create policy "Manage technical architecture" on technical_architecture
for all using (
  auth.uid() in (
    select user_id from project_users
    where project_id = technical_architecture.project_id
    and role in ('owner', 'manager')
  )
);

ui_design

create table ui_design (
  id uuid primary key default uuid_generate_v4(),
  project_id uuid references projects(id) on delete cascade,
  title text not null,
  content jsonb not null,
  version integer not null default 1,
  status text not null check (status in ('draft', 'review', 'approved', 'archived')),
  color_palette jsonb,
  ui_framework text,
  components jsonb default '[]',
  created_at timestamp with time zone default now(),
  created_by uuid references auth.users(id),
  updated_at timestamp with time zone default now(),
  updated_by uuid references auth.users(id)
);
-- RLS Policies
alter table ui_design enable row level security;
-- Project members can read UI design
create policy "Read UI design" on ui_design
for select using (
  auth.uid() in (
    select user_id from project_users
    where project_id = ui_design.project_id
  )
);
-- Project managers and up can manage UI design
create policy "Manage UI design" on ui_design
for all using (
  auth.uid() in (
    select user_id from project_users
    where project_id = ui_design.project_id
    and role in ('owner', 'manager')
  )
);

project_context

create table project_context (
  id uuid primary key default uuid_generate_v4(),
  project_id uuid references projects(id) on delete cascade,
  context_type text not null check (context_type in ('search', 'data', 'quality')),
  title text not null,
  content jsonb not null,
  metadata jsonb default '{}',
  created_at timestamp with time zone default now(),
  created_by uuid references auth.users(id),
  updated_at timestamp with time zone default now(),
  updated_by uuid references auth.users(id)
);
-- RLS Policies
alter table project_context enable row level security;
-- Project members can read context
create policy "Read project context" on project_context
for select using (
  auth.uid() in (
    select user_id from project_users
    where project_id = project_context.project_id
  )
);
-- Project managers and up can manage context
create policy "Manage project context" on project_context
for all using (
  auth.uid() in (
    select user_id from project_users
    where project_id = project_context.project_id
    and role in ('owner', 'manager')
  )
);

amcclosky/supabase.md

Supabase Database Architecture

Security & Compliance Requirements

SOC II Compliance

Data Protection

Database Schema

Core Tables

users

auth.users (managed by Supabase)

workspaces

workspace_users

projects

project_users

API & Integration Tables

api_connections

oauth2_connections

oauth2_tokens

Chat & Messages Tables

chat_sessions

chat_messages

chat_participants

Artifacts & Documents

artifacts

artifact_files

Storage Buckets

Indexes

Functions & Triggers

Security Functions

Backup & Recovery

Billing & Usage Tables

workspace_subscriptions

token_purchases

token_usage

Statistics Tables

workspace_stats

project_stats

Audit Logging Tables

audit_logs

Global Workspace & Personal Project Functions

Project Context Tables

project_requirements

technical_architecture

ui_design

project_context