132 lines
7.4 KiB
SQL
132 lines
7.4 KiB
SQL
CREATE TYPE "public"."permission_override_effect" AS ENUM('allow', 'deny');
|
|
|
|
CREATE TABLE "permission_templates" (
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "permission_templates_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
|
"organization_id" text NOT NULL,
|
|
"code" text NOT NULL,
|
|
"name" text NOT NULL,
|
|
"description" text,
|
|
"is_system" boolean DEFAULT false NOT NULL,
|
|
"is_default" boolean DEFAULT false NOT NULL,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
"deleted_at" timestamp with time zone,
|
|
"created_by" text,
|
|
"updated_by" text,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE "permission_template_permissions" (
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "permission_template_permissions_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
|
"template_id" integer NOT NULL,
|
|
"permission" text NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE "user_permission_template_assignments" (
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "user_permission_template_assignments_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
|
"organization_id" text NOT NULL,
|
|
"user_id" text NOT NULL,
|
|
"template_id" integer NOT NULL,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
"assigned_by" text,
|
|
"assigned_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE "user_permission_overrides" (
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "user_permission_overrides_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
|
"organization_id" text NOT NULL,
|
|
"user_id" text NOT NULL,
|
|
"permission" text NOT NULL,
|
|
"effect" "permission_override_effect" NOT NULL,
|
|
"reason" text,
|
|
"expires_at" timestamp with time zone,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
"created_by" text,
|
|
"revoked_by" text,
|
|
"revoked_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
CREATE TABLE "permission_audit_logs" (
|
|
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "permission_audit_logs_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
|
|
"organization_id" text,
|
|
"actor_user_id" text,
|
|
"target_user_id" text,
|
|
"template_id" integer,
|
|
"assignment_id" integer,
|
|
"override_id" integer,
|
|
"action" text NOT NULL,
|
|
"reason" text,
|
|
"before" jsonb,
|
|
"after" jsonb,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE "permission_templates"
|
|
ADD CONSTRAINT "permission_templates_organization_id_organizations_id_fk"
|
|
FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;
|
|
ALTER TABLE "permission_templates"
|
|
ADD CONSTRAINT "permission_templates_created_by_users_id_fk"
|
|
FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
|
|
ALTER TABLE "permission_templates"
|
|
ADD CONSTRAINT "permission_templates_updated_by_users_id_fk"
|
|
FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
|
|
|
|
ALTER TABLE "permission_template_permissions"
|
|
ADD CONSTRAINT "permission_template_permissions_template_id_permission_templates_id_fk"
|
|
FOREIGN KEY ("template_id") REFERENCES "public"."permission_templates"("id") ON DELETE cascade ON UPDATE no action;
|
|
|
|
ALTER TABLE "user_permission_template_assignments"
|
|
ADD CONSTRAINT "user_permission_template_assignments_organization_id_organizations_id_fk"
|
|
FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;
|
|
ALTER TABLE "user_permission_template_assignments"
|
|
ADD CONSTRAINT "user_permission_template_assignments_user_id_users_id_fk"
|
|
FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
|
|
ALTER TABLE "user_permission_template_assignments"
|
|
ADD CONSTRAINT "user_permission_template_assignments_template_id_permission_templates_id_fk"
|
|
FOREIGN KEY ("template_id") REFERENCES "public"."permission_templates"("id") ON DELETE restrict ON UPDATE no action;
|
|
ALTER TABLE "user_permission_template_assignments"
|
|
ADD CONSTRAINT "user_permission_template_assignments_assigned_by_users_id_fk"
|
|
FOREIGN KEY ("assigned_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
|
|
|
|
ALTER TABLE "user_permission_overrides"
|
|
ADD CONSTRAINT "user_permission_overrides_organization_id_organizations_id_fk"
|
|
FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;
|
|
ALTER TABLE "user_permission_overrides"
|
|
ADD CONSTRAINT "user_permission_overrides_user_id_users_id_fk"
|
|
FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
|
|
ALTER TABLE "user_permission_overrides"
|
|
ADD CONSTRAINT "user_permission_overrides_created_by_users_id_fk"
|
|
FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
|
|
ALTER TABLE "user_permission_overrides"
|
|
ADD CONSTRAINT "user_permission_overrides_revoked_by_users_id_fk"
|
|
FOREIGN KEY ("revoked_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
|
|
|
|
ALTER TABLE "permission_audit_logs"
|
|
ADD CONSTRAINT "permission_audit_logs_organization_id_organizations_id_fk"
|
|
FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE set null ON UPDATE no action;
|
|
ALTER TABLE "permission_audit_logs"
|
|
ADD CONSTRAINT "permission_audit_logs_actor_user_id_users_id_fk"
|
|
FOREIGN KEY ("actor_user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
|
|
ALTER TABLE "permission_audit_logs"
|
|
ADD CONSTRAINT "permission_audit_logs_target_user_id_users_id_fk"
|
|
FOREIGN KEY ("target_user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;
|
|
ALTER TABLE "permission_audit_logs"
|
|
ADD CONSTRAINT "permission_audit_logs_template_id_permission_templates_id_fk"
|
|
FOREIGN KEY ("template_id") REFERENCES "public"."permission_templates"("id") ON DELETE set null ON UPDATE no action;
|
|
ALTER TABLE "permission_audit_logs"
|
|
ADD CONSTRAINT "permission_audit_logs_assignment_id_user_permission_template_assignments_id_fk"
|
|
FOREIGN KEY ("assignment_id") REFERENCES "public"."user_permission_template_assignments"("id") ON DELETE set null ON UPDATE no action;
|
|
ALTER TABLE "permission_audit_logs"
|
|
ADD CONSTRAINT "permission_audit_logs_override_id_user_permission_overrides_id_fk"
|
|
FOREIGN KEY ("override_id") REFERENCES "public"."user_permission_overrides"("id") ON DELETE set null ON UPDATE no action;
|
|
|
|
CREATE UNIQUE INDEX "permission_templates_org_code_idx" ON "permission_templates" USING btree ("organization_id", "code");
|
|
CREATE UNIQUE INDEX "permission_templates_org_name_idx" ON "permission_templates" USING btree ("organization_id", "name");
|
|
CREATE UNIQUE INDEX "permission_template_permissions_template_permission_idx" ON "permission_template_permissions" USING btree ("template_id", "permission");
|
|
CREATE UNIQUE INDEX "user_permission_template_assignments_org_user_template_idx" ON "user_permission_template_assignments" USING btree ("organization_id", "user_id", "template_id");
|