Supabase RLS Policy & Schema Architect

-- Supabase PostgreSQL Row Level Security (RLS) Migration
-- Generated by Supabase RLS & Schema Policy Architect

-- 1. Helper security definer function for tenant membership caching
CREATE OR REPLACE FUNCTION auth.is_org_member(lookup_org_id UUID)
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1 FROM organization_members
    WHERE org_id = lookup_org_id
      AND user_id = auth.uid()
  );
$$;

-- Table: organizations
CREATE TABLE IF NOT EXISTS organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT,
    slug TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "org_members_select" ON organizations;
CREATE POLICY "org_members_select" ON organizations
    FOR SELECT TO authenticated
    USING (auth.is_org_member(id));

DROP POLICY IF EXISTS "org_creator_insert" ON organizations;
CREATE POLICY "org_creator_insert" ON organizations
    FOR INSERT TO authenticated
    USING (true)
    WITH CHECK (auth.uid() IS NOT NULL);

-- Table: projects
CREATE TABLE IF NOT EXISTS projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    title TEXT,
    status TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_projects_org_id ON projects(org_id);
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "projects_org_select" ON projects;
CREATE POLICY "projects_org_select" ON projects
    FOR SELECT TO authenticated
    USING (auth.is_org_member(org_id));

DROP POLICY IF EXISTS "projects_org_insert" ON projects;
CREATE POLICY "projects_org_insert" ON projects
    FOR INSERT TO authenticated
    USING (auth.is_org_member(org_id))
    WITH CHECK (auth.is_org_member(org_id));

DROP POLICY IF EXISTS "projects_org_update" ON projects;
CREATE POLICY "projects_org_update" ON projects
    FOR UPDATE TO authenticated
    USING (auth.is_org_member(org_id))
    WITH CHECK (auth.is_org_member(org_id));

DROP POLICY IF EXISTS "projects_org_delete" ON projects;
CREATE POLICY "projects_org_delete" ON projects
    FOR DELETE TO authenticated
    USING (auth.is_org_member(org_id));