Skip to content

Instantly share code, notes, and snippets.

@kyleledbetter

kyleledbetter/supabase.md

Created December 26, 2024 23:28
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kyleledbetter/e4229d4163a1ea88b50dfcf3f7ea4a2a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kyleledbetter/e4229d4163a1ea88b50dfcf3f7ea4a2a to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. kyleledbetter created this gist Dec 26, 2024.
    1,352 changes: 1,352 additions & 0 deletions supabase.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,1352 @@
    # 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

    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    -- 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
    sql
    -- 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
    sql
    -- 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
    sql
    -- 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
    sql
    -- 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
    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    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
    sql
    -- 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

    sql
    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

    sql
    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

    sql
    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

    sql
    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')
    )
    );