- 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
- API keys and OAuth tokens stored with encryption
- Personal data encrypted and access-logged
- File storage with secure access policies
- Environment variables stored securely
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();
- Extended with custom profile data
- Links to workspace and project roles
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' ) );
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') ) );
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') ) );
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') ) );
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') ) ) );
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') ) ) );
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 ) ) ) ) );
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') ) );
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 ) ) );
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 );
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') ) );
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;
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 ) ) );
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));
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)
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;
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()' );
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') ) );
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') ) );
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 ) );
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 ) );
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 ) );
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();
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 $$;
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') ) );
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') ) );
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') ) );
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') ) );