Local Supabase Development

bun sb:start

bun sb:stop

bun sb:status

bun sb:reset

bun sb:new add_custom_function

bun sb:up

bun --cwd apps/database sb:validate:isolated

bun --cwd apps/database sb:validate:isolated --test supabase/tests/workspace-creator-membership.sql

bun --cwd apps/database sb:validate:isolated --typegen packages/types/src/supabase.ts --test supabase/tests/habit-tracker-write-rls.sql

bun --cwd apps/database sb:validate:isolated --resume /tmp/tuturuuu-supabase-EXAMPLE
bun --cwd apps/database sb:validate:isolated --cleanup /tmp/tuturuuu-supabase-EXAMPLE

TMPDIR=/private/tmp ttr resources run -- node --test --test-concurrency=1 apps/database/tests/programming-hosted-typegen-review/metadata.node-test.mjs

bun sb:typegen

import type { Database } from '@tuturuuu/types/supabase';

type WorkspaceMemberRow =
  Database['public']['Tables']['workspace_members']['Row'];

// Type-safe access to tables (the client already knows the row shapes)
const { data, error } = await supabase
  .from('workspace_members')
  .select('*')
  .eq('ws_id', workspaceId);

// data is typed as WorkspaceMemberRow[] | null

// Type-safe access to specific columns
const { data: workspace } = await supabase
  .from('workspaces')
  .select('id, name, handle')
  .eq('id', workspaceId)
  .single();

// TypeScript knows the structure of 'workspace' with proper types
const workspaceName: string | undefined = workspace?.name;

import type { WorkspaceCourse, WorkspaceRole } from '@tuturuuu/types/db';

// Use short-hand types directly
const { data: roles } = await supabase
  .from('workspace_roles')
  .select('*')
  .eq('ws_id', workspaceId);

// Type is now WorkspaceRole[]
roles?.forEach((role: WorkspaceRole) => {
  console.log(role.name, role.permissions);
});

// Short-hand types can also include extended properties
const course: WorkspaceCourse = {
  id: 'course-id',
  ws_id: 'workspace-id',
  name: 'Course Name',
  created_at: new Date().toISOString(),
  updated_at: new Date().toISOString(),
  href: '/courses/course-id', // Extended property not in the database
};

alter table <schema_name>.<table_name> enable row level security;

-- Allow users to select data from their organizations
create policy "Users can view data from their organizations"
on public.table_name
for select
to authenticated
using (
  exists (
    select 1
    from public.workspace_members workspace_member
    where workspace_member.ws_id = table_name.ws_id
      and workspace_member.user_id = (select auth.uid())
      and workspace_member.type = 'MEMBER'
  )
);

-- Allow members with manage_projects to insert data
create policy "Project managers can insert data"
on public.table_name
for insert
to authenticated
with check (
  exists (
    select 1
    from public.workspace_members workspace_member
    where workspace_member.ws_id = table_name.ws_id
      and workspace_member.user_id = (select auth.uid())
      and workspace_member.type = 'MEMBER'
  )
  and public.has_workspace_permission(
    table_name.ws_id,
    (select auth.uid()),
    'manage_projects'
  )
);

-- UPDATE needs USING for the existing row and WITH CHECK for the new row
create policy "Project managers can update data"
on public.table_name
for update
to authenticated
using (
  exists (
    select 1
    from public.workspace_members workspace_member
    where workspace_member.ws_id = table_name.ws_id
      and workspace_member.user_id = (select auth.uid())
      and workspace_member.type = 'MEMBER'
  )
  and public.has_workspace_permission(
    table_name.ws_id,
    (select auth.uid()),
    'manage_projects'
  )
)
with check (
  exists (
    select 1
    from public.workspace_members workspace_member
    where workspace_member.ws_id = table_name.ws_id
      and workspace_member.user_id = (select auth.uid())
      and workspace_member.type = 'MEMBER'
  )
  and public.has_workspace_permission(
    table_name.ws_id,
    (select auth.uid()),
    'manage_projects'
  )
);

-- DELETE uses USING because there is no new row to check
create policy "Project managers can delete data"
on public.table_name
for delete
to authenticated
using (
  exists (
    select 1
    from public.workspace_members workspace_member
    where workspace_member.ws_id = table_name.ws_id
      and workspace_member.user_id = (select auth.uid())
      and workspace_member.type = 'MEMBER'
  )
  and public.has_workspace_permission(
    table_name.ws_id,
    (select auth.uid()),
    'manage_projects'
  )
);

create or replace function private.can_access_workspace(p_ws_id uuid)
returns boolean
language plpgsql
security definer
stable
set search_path = ''
as $$
declare
  v_caller_id uuid := (select auth.uid());
begin
  if coalesce((select auth.jwt() ->> 'role'), '') = 'service_role' then
    return true;
  end if;

  if v_caller_id is null then
    return false;
  end if;

  return exists (
    select 1
    from public.workspace_members workspace_member
    where workspace_member.ws_id = p_ws_id
      and workspace_member.user_id = v_caller_id
      and workspace_member.type = 'MEMBER'
  );
end;
$$;

revoke execute on function private.can_access_workspace(uuid)
from public, anon, authenticated, service_role;
grant execute on function private.can_access_workspace(uuid)
to authenticated, service_role;

-- Allow full members with manage_projects to access a resource
create policy "Project managers can view workspace data"
on public.table_name
for select
to authenticated
using (
  exists (
    select 1
    from public.workspace_members workspace_member
    where workspace_member.ws_id = table_name.ws_id
      and workspace_member.user_id = (select auth.uid())
      and workspace_member.type = 'MEMBER'
  )
  and public.has_workspace_permission(
    table_name.ws_id,
    (select auth.uid()),
    'manage_projects'
  )
);

begin;

create extension if not exists pgtap with schema extensions;
set local search_path = public, extensions;

select plan(2);

-- Impersonate an authenticated user
set local role authenticated;
select set_config(
  'request.jwt.claims',
  '{"sub":"00000000-0000-0000-0000-000000000001","role":"authenticated"}',
  true
);

-- Should return data the user is allowed to see
select isnt_empty(
  $$ select 1 from public.workspaces where id = '00000000-0000-0000-0000-000000000000' $$,
  'User can see a workspace they belong to'
);

-- Should be blocked by RLS for an unrelated workspace
select is_empty(
  $$ select 1 from public.workspaces where id = '11111111-1111-1111-1111-111111111111' $$,
  'User cannot see a workspace they do not belong to'
);

select * from finish();
rollback;

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_workspace_id uuid := gen_random_uuid();
begin
  -- Create a personal workspace for the new user
  insert into public.workspaces (id, name, personal, creator_id)
  values (v_workspace_id, new.email || '''s workspace', true, new.id);

  -- Record the creator as a full member without re-querying the workspace
  insert into public.workspace_members (ws_id, user_id, type)
  values (v_workspace_id, new.id, 'MEMBER');

  return new;
end;
$$;

revoke execute on function public.handle_new_user()
from public, anon, authenticated, service_role;
grant execute on function public.handle_new_user() to supabase_auth_admin;

create trigger on_auth_user_created
  after insert on auth.users
  for each row
  execute function public.handle_new_user();

create or replace function private.audit_log_changes()
returns trigger
language plpgsql
security definer
as $$
begin
  insert into public.audit_logs (
    table_name,
    record_id,
    action,
    old_data,
    new_data,
    performed_by
  )
  values (
    TG_TABLE_NAME,
    coalesce(new.id, old.id),
    TG_OP,
    case when TG_OP = 'DELETE' or TG_OP = 'UPDATE' then row_to_json(old) else null end,
    case when TG_OP = 'INSERT' or TG_OP = 'UPDATE' then row_to_json(new) else null end,
    coalesce(auth.uid(), '00000000-0000-0000-0000-000000000000'::uuid)
  );
  return coalesce(new, old);
end;
$$;

-- Apply this trigger to a table
create trigger projects_audit_trigger
  after insert or update or delete
  on public.projects
  for each row
  execute function private.audit_log_changes();

create or replace function public.update_timestamp()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

-- Apply to a table
create trigger update_projects_timestamp
  before update
  on public.projects
  for each row
  execute function public.update_timestamp();

-- Insert a test user
insert into auth.users (id, email)
values ('test-uuid', 'test@example.com');

-- Verify the trigger created a workspace
select * from public.workspaces where creator_id = 'test-uuid';

-- Verify the creator is a full member of the workspace
select * from public.workspace_members
where user_id = 'test-uuid' and type = 'MEMBER';

-- Populate auth users
INSERT INTO
    "auth"."users" (
        "instance_id",
        "id",
        "aud",
        "role",
        "email",
        "encrypted_password",
        "email_confirmed_at",
        /* other fields... */
    )
VALUES
    (
        '00000000-0000-0000-0000-000000000000',
        '00000000-0000-0000-0000-000000000001',
        'authenticated',
        'authenticated',
        'local@tuturuuu.com',
        crypt('password123', gen_salt('bf')),
        '2023-02-18 23:31:13.017218+00',
        /* other values... */
    ),
    /* additional users... */

-- Populate workspaces
insert into
    public.workspaces (id, name, handle, creator_id)
values
    (
        '00000000-0000-0000-0000-000000000000',
        'Tuturuuu',
        'tuturuuu',
        '00000000-0000-0000-0000-000000000001'
    ),
    (
        '00000000-0000-0000-0000-000000000001',
        'Prototype All',
        'prototype-all',
        null
    ),
    /* additional workspaces... */

-- Populate workspace_members
insert into
    public.workspace_members (user_id, ws_id, role)
values
    (
        '00000000-0000-0000-0000-000000000001',
        '00000000-0000-0000-0000-000000000000',
        'OWNER'
    ),
    (
        '00000000-0000-0000-0000-000000000002',
        '00000000-0000-0000-0000-000000000000',
        'ADMIN'
    ),
    (
        '00000000-0000-0000-0000-000000000003',
        '00000000-0000-0000-0000-000000000000',
        'MEMBER'
    ),
    /* additional members... */

-- Populate workspace_secrets
insert into
    public.workspace_secrets (ws_id, name, value)
values
    (
        '00000000-0000-0000-0000-000000000000',
        'ENABLE_CHAT',
        'true'
    ),
    (
        '00000000-0000-0000-0000-000000000000',
        'ENABLE_EDUCATION',
        'true'
    ),
    /* additional features... */

-- Populate user group metrics
insert into
    public.user_group_metrics (id, ws_id, name, unit, is_weighted)
values
    (
        '00000000-0000-0000-0000-000000000001',
        '00000000-0000-0000-0000-000000000003',
        'Nhiệt độ',
        '°C',
        true
    ),
    (
        '00000000-0000-0000-0000-000000000002',
        '00000000-0000-0000-0000-000000000003',
        'Chiều cao',
        'cm',
        false
    ),
    /* additional metrics... */

-- Create a new user group
INSERT INTO public.workspace_user_groups (id, name, ws_id)
VALUES
  ('your-uuid-here', 'New Group', 'workspace-uuid-here')
ON CONFLICT (id) DO NOTHING;

-- Add a new workspace feature
INSERT INTO public.workspace_secrets (ws_id, name, value)
VALUES
  ('workspace-uuid-here', 'ENABLE_NEW_FEATURE', 'true')
ON CONFLICT (ws_id, name) DO UPDATE
SET value = EXCLUDED.value;

node apps/database/scripts/run-supabase.js db reset --db-url=postgresql://postgres:postgres@localhost:54322/postgres
psql postgresql://postgres:postgres@localhost:54322/postgres -f apps/database/supabase/my_custom_seed.sql

# Export only data (not schema) to seed.sql
node apps/database/scripts/run-supabase.js db dump --db-url=postgresql://postgres:postgres@localhost:54322/postgres --data-only > apps/database/supabase/new_seed.sql

// 1. Define the schema
import { z } from 'zod';

export const flashcardSchema = z.object({
  flashcards: z.array(
    z.object({
      front: z.string().describe('Question. Do not use emojis or links.'),
      back: z.string().describe('Answer. Do not use emojis or links.'),
    })
  ),
});

// 2. Setup API endpoint
export async function POST(req: Request) {
  const sbAdmin = await createAdminClient();
  const { wsId, context } = await req.json();

  // Validate user and workspace permissions
  const {
    data: { user },
  } = await supabase.auth.getUser();
  if (!user) return new Response('Unauthorized', { status: 401 });

  // Check workspace feature flag
  const { count, error } = await sbAdmin
    .from('workspace_secrets')
    .select('*', { count: 'exact', head: true })
    .eq('ws_id', wsId)
    .eq('name', 'ENABLE_CHAT')
    .eq('value', 'true');

  if (error) return new Response(error.message, { status: 500 });
  if (count === 0)
    return new Response('You are not allowed to use this feature.', {
      status: 401,
    });

  // 3. Generate structured data using AI SDK
  const result = streamObject({
    model: google('gemini-2.0-flash-001', {
      safetySettings: [
        // Safety settings configuration...
      ],
    }),
    prompt: `Generate 10 flashcards with the following context: ${context}`,
    schema: flashcardSchema,
  });

  // 4. Stream the response to the client
  return result.toTextStreamResponse();
}

export const models = [
  {
    value: 'gemini-2.0-flash-001',
    label: 'gemini-2.0-flash',
    provider: 'Google',
    description: 'Gemini 2.0 Flash delivers next-gen features...',
    context: 1000000,
  },
  // Add other models here...
];

export const defaultModel = models.find(
  (model) =>
    model.value === 'gemini-2.0-flash-001' && model.provider === 'Google Vertex'
);

export const myNewSchema = z.object({
  items: z.array(
    z.object({
      name: z.string().describe('Name of the item'),
      description: z.string().describe('Description of the item'),
      priority: z.enum(['high', 'medium', 'low']).describe('Priority level'),
    })
  ),
});

-- Enable AI chat features for a workspace
INSERT INTO public.workspace_secrets (ws_id, name, value)
VALUES ('your-workspace-id', 'ENABLE_CHAT', 'true');

-- Enable AI document features for a workspace
INSERT INTO public.workspace_secrets (ws_id, name, value)
VALUES ('your-workspace-id', 'ENABLE_DOCS', 'true');

try {
  // AI SDK code here
} catch (error) {
  console.log(error);
  return NextResponse.json(
    {
      message: `## Edge API Failure\nCould not complete the request. Please view the **Stack trace** below.\n\`\`\`bash\n${(error as Error)?.stack || 'No stack trace available'}`,
    },
    {
      status: 200,
    }
  );
}

ttr resources run -- node apps/database/scripts/verify-task-rpc-user-callers.mjs --root <disposable-root>