244e50c806
Implements #9, #10 - Team model with workspace membership - TeamMember model with role-based access (OWNER, ADMIN, MEMBER) - Row-Level Security policies for tenant isolation on 19 tables - Helper functions: current_user_id(), is_workspace_member(), is_workspace_admin() - Developer utilities in src/lib/db-context.ts for easy RLS integration - Comprehensive documentation in docs/design/multi-tenant-rls.md Database migrations: - 20260129220941_add_team_model: Adds Team and TeamMember tables - 20260129221004_add_rls_policies: Enables RLS and creates policies Security features: - Complete database-level tenant isolation - Automatic query filtering based on workspace membership - Defense-in-depth security with application and database layers - Performance-optimized with indexes on workspace_id
41 lines
1.4 KiB
SQL
41 lines
1.4 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "TeamMemberRole" AS ENUM ('OWNER', 'ADMIN', 'MEMBER');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "teams" (
|
|
"id" UUID NOT NULL,
|
|
"workspace_id" UUID NOT NULL,
|
|
"name" TEXT NOT NULL,
|
|
"description" TEXT,
|
|
"metadata" JSONB NOT NULL DEFAULT '{}',
|
|
"created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ NOT NULL,
|
|
|
|
CONSTRAINT "teams_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "team_members" (
|
|
"team_id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"role" "TeamMemberRole" NOT NULL DEFAULT 'MEMBER',
|
|
"joined_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "team_members_pkey" PRIMARY KEY ("team_id","user_id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "teams_workspace_id_idx" ON "teams"("workspace_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "team_members_user_id_idx" ON "team_members"("user_id");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "teams" ADD CONSTRAINT "teams_workspace_id_fkey" FOREIGN KEY ("workspace_id") REFERENCES "workspaces"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "team_members" ADD CONSTRAINT "team_members_team_id_fkey" FOREIGN KEY ("team_id") REFERENCES "teams"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "team_members" ADD CONSTRAINT "team_members_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|