CREATE TABLE public.roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(50) NOT NULL UNIQUE,
  description TEXT,
  is_system BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE public.permissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(100) NOT NULL UNIQUE,
  description TEXT,
  resource VARCHAR(50) NOT NULL,
  action VARCHAR(20) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

SELECT public.user_has_permission('user-uuid', 'orders.read');
-- Returns: true/false

SELECT * FROM public.get_user_roles('user-uuid');
-- Returns: table of role_name

SELECT * FROM public.get_user_permissions('user-uuid');
-- Returns: table of permission_name

SELECT public.user_has_any_permission(
  'user-uuid',
  ARRAY['orders.read', 'orders.write']
);
-- Returns: true/false

import { defineMiddlewares, authenticate } from "@switchyard/framework/http"
import { authorize } from "../middlewares/authorize-middleware"

export default defineMiddlewares({
  routes: [
    {
      method: ["GET", "POST"],
      matcher: "/admin/orders",
      middlewares: [
        authenticate("user", ["session", "bearer", "api-key"]),
      ],
    },
    {
      method: ["DELETE"],
      matcher: "/admin/orders/:id",
      middlewares: [
        authenticate("user", ["session", "bearer", "api-key"]),
        authorize("orders.delete"),
      ],
    },
  ],
})

const authContext = req.auth_context
const permissions = authContext?.auth_identity?.user_metadata?.permissions || []

if (!permissions.includes("inventory.write")) {
  return res.status(403).json({ message: "Permission denied" })
}

-- Assign role to user
INSERT INTO public.user_roles (user_id, role_id)
SELECT 
  'supabase-user-uuid',
  id 
FROM public.roles 
WHERE name = 'manager';