myeasycms-v2/.claude/skills/postgres-expert/makerkit.md

Makerkit Database Patterns

Schema Location

All schemas are in apps/web/supabase/schemas/ with numbered prefixes for dependency ordering.

Existing Helper Functions - DO NOT Recreate

-- Account Access Control
public.has_role_on_account(account_id uuid, role_name? text)
public.has_permission(user_id uuid, account_id uuid, permission app_permissions)
public.is_account_owner(account_id uuid)
public.has_active_subscription(account_id uuid)
public.is_team_member(account_id uuid, user_id uuid)
public.can_action_account_member(target_account_id uuid, target_user_id uuid)

-- Administrative
public.is_super_admin()
public.is_aal2()
public.is_mfa_compliant()

-- Configuration
public.is_set(field_name text)

RLS Policy Patterns

Personal + Team Access

create policy "table_read" on public.table for select
  to authenticated using (
    account_id = (select auth.uid()) or
    public.has_role_on_account(account_id)
  );

Permission-Based Access

create policy "table_manage" on public.table for all
  to authenticated using (
    public.has_permission(auth.uid(), account_id, 'feature.manage'::app_permissions)
  );

Storage Bucket Policy

create policy bucket_policy on storage.objects for all using (
  bucket_id = 'bucket_name'
  and (
    kit.get_storage_filename_as_uuid(name) = auth.uid()
    or public.has_role_on_account(kit.get_storage_filename_as_uuid(name))
  )
);

Adding New Permissions

-- Add to app_permissions enum
ALTER TYPE public.app_permissions ADD VALUE 'feature.manage';
COMMIT;

Standard Table Template

create table if not exists public.feature (
  id uuid unique not null default extensions.uuid_generate_v4(),
  account_id uuid references public.accounts(id) on delete cascade not null,
  name varchar(255) 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),
  updated_by uuid references auth.users(id),
  primary key (id)
);

-- Enable RLS
alter table "public"."feature" enable row level security;

-- Revoke defaults, grant specific
revoke all on public.feature from authenticated, service_role;
grant select, insert, update, delete on table public.feature to authenticated;

-- Add triggers
create trigger set_timestamps
  before insert or update on public.feature
  for each row execute function public.trigger_set_timestamps();

create trigger set_user_tracking
  before insert or update on public.feature
  for each row execute function public.trigger_set_user_tracking();

-- Add indexes
create index ix_feature_account_id on public.feature(account_id);

Migration Workflow

# New entity: copy schema to migration
pnpm --filter web run supabase migrations new feature_name

# Modify existing: generate diff
pnpm --filter web run supabase:db:diff -f update_feature

# Apply
pnpm --filter web supabase migrations up

# Generate types
pnpm supabase:web:typegen

Security Definer Function Pattern

create or replace function public.admin_function(target_id uuid)
returns void
language plpgsql
security definer
set search_path = ''
as $$
begin
  -- ALWAYS validate permissions first
  if not public.is_account_owner(target_id) then
    raise exception 'Access denied';
  end if;

  -- Safe to proceed
end;
$$;

grant execute on function public.admin_function(uuid) to authenticated;