Mock: Internal Business Platform on Next.js + Supabase/Vercel

-- CI check: bảng public nào chưa bật RLS?
select relname from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where n.nspname = 'public' and c.relkind = 'r' and not c.relrowsecurity;

create policy "members update own tenant rows" on projects
  for update to authenticated
  using (private.is_member(tenant_id))
  with check (private.is_member(tenant_id));

create function private.has_role(t uuid, roles text[])
returns boolean language sql stable security definer set search_path = '' as $$
  select exists (
    select 1 from public.memberships m
    where m.user_id = (select auth.uid()) and m.tenant_id = t and m.role = any(roles)
  );
$$;

alter table projects enable row level security;
create policy "read" on projects for select to authenticated
  using (private.has_role(tenant_id, '{owner,admin,member,viewer}'));
create policy "write" on projects for insert to authenticated
  with check (private.has_role(tenant_id, '{owner,admin}'));
create policy "update" on projects for update to authenticated
  using (private.has_role(tenant_id, '{owner,admin}'))
  with check (private.has_role(tenant_id, '{owner,admin}'));

create policy "tenant read" on orders for select to authenticated
  using (
    tenant_id in (
      select tenant_id from memberships where user_id = auth.uid()
    )
  );
-- orders has no index on tenant_id
-- app query: select * from orders order by created_at desc limit 50;

create view public.order_totals with (security_invoker = true) as
  select tenant_id, date_trunc('day', created_at) d, sum(total) s
  from orders group by 1, 2;

'use server'
import { z } from 'zod'
import { requireMember } from '@/server/auth'

const Input = z.object({ projectId: z.uuid(), name: z.string().min(1).max(120) })

export async function renameProject(raw: unknown) {
  const input = Input.parse(raw)
  const { supabase } = await requireMember({ permission: 'project:update' })
  const { error } = await supabase.from('projects')
    .update({ name: input.name }).eq('id', input.projectId)
  if (error) throw error
}

begin;
select plan(2);
set local role authenticated;
select set_config('request.jwt.claims', '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}', true);
select is_empty($$ select * from invoices where tenant_id = 'tenant-b-uuid' $$, 'A cannot read B');
select throws_ok($$ insert into invoices(tenant_id, total) values ('tenant-b-uuid', 10) $$, '42501');
select * from finish();
rollback;

-- 20261001_add_external_ref.sql
alter table tasks add column external_ref uuid not null default gen_random_uuid();
create index tasks_external_ref_idx on tasks (external_ref);
alter table tasks add constraint tasks_project_fk
  foreign key (tenant_id, project_id) references projects (tenant_id, id);

create extension if not exists btree_gist;
alter table shifts add constraint no_overlap exclude using gist (
  tenant_id with =, employee_id with =,
  tstzrange(starts_at, ends_at, '[)') with &&
);

// lib/stats.ts (server)
let cached: { at: number; data: Stats } | null = null

export async function getStats(tenantId: string) {
  if (cached && Date.now() - cached.at < 60_000) return cached.data
  const data = await db.query('select ... where tenant_id = $1', [tenantId])
  cached = { at: Date.now(), data }
  return data
}

export async function POST(req: Request) {
  const body = (await req.json()) as { tenantId: string; role: 'member' | 'admin'; email: string }
  const admin = createAdminClient() // service_role
  await admin.from('memberships').insert({
    tenant_id: body.tenantId, role: body.role, email: body.email,
  })
  return Response.json({ ok: true })
}

export async function approve(requestId: string) {
  const { data: req } = await supabase.from('expense_requests')
    .select('*').eq('id', requestId).single()
  if (req.status !== 'pending') throw new Error('Already processed')
  await supabase.from('expense_requests').update({ status: 'approved' }).eq('id', requestId)
  await createPayout(req) // inserts into payouts
}