SQLize Online / PHPize Online  /  SQLtest Online

CREATE TYPE "subscriptionType" AS ENUM ( 'Basic', 'Pro', 'Enterprise' ); CREATE TYPE "userStatus" AS ENUM ( 'Active', 'Disabled' ); CREATE TYPE "roleType" AS ENUM ( 'Admin', 'User' ); CREATE TYPE "permission" AS ENUM ( 'Read', 'Write' ); CREATE TYPE "serviceType" AS ENUM ( 'Application', 'Infrastructure', 'Library', 'Database' ); CREATE TABLE "organizations" ( "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "name" varchar, "tenant" varchar, "website" varchar, "created_at" timestamp, "updated_at" timestamp, "deleted_at" timestamp ); CREATE TABLE "settings" ( "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "organization_id" int, "created_at" timestamp, "updated_at" timestamp, "deleted_at" timestamp ); CREATE TABLE "subscriptions" ( "id" int PRIMARY KEY, "name" varchar, "description" varchar, "subscription_type" subscriptionType, "settings_id" int, "monthly_price" number, "created_at" timestamp, "updated_at" timestamp, "deleted_at" timestamp ); CREATE TABLE "teams" ( "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "name" varchar, "description" varchar, "color" varchar, "organization_id" int, "created_at" timestamp, "updated_at" timestamp, "deleted_at" timestamp ); CREATE TABLE "users" ( "id" varchar UNIQUE PRIMARY KEY, "name" varchar, "lastname" varchar, "email" varchar, "avatar" varchar, "status" userStatus, "role" int, "organization_id" int, "created_at" timestamp, "updated_at" timestamp, "deleted_at" timestamp ); CREATE TABLE "roles" ( "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "name" varchar, "description" varchar, "type" roleTypev, "permissions" permission[], "created_at" timestamp, "updated_at" timestamp, "deleted_at" timestamp ); CREATE TABLE "user_teams" ( "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "user_id" int, "team_id" int, "organization_id" int, "created_at" timestamp, "updated_at" timestamp, "deleted_at" timestamp ); CREATE TABLE "systems" ( "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "name" varchar, "description" varchar, "team_id" int, "created_at" timestamp, "updated_at" timestamp, "deleted_at" timestamp ); CREATE TABLE "services" ( "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "name" varchar, "description" varchar, "system_id" int, "source_url" varchar, "dependencies" int[], "type" serviceType, "opsdocs" int[], "tags" varchar[], "created_at" timestamp, "updated_at" timestamp, "deleted_at" timestamp ); CREATE TABLE "dependencies" ( "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "name" varchar, "description" varchar, "source_url" varchar UNIQUE, "opsdocs" int[], "tags" varchar[], "created_at" timestamp, "updated_at" timestamp, "deleted_at" timestamp ); CREATE TABLE "opsdocs" ( "id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "name" varchar, "description" varchar, "source_url" varchar UNIQUE, "tags" varchar[], "created_at" timestamp, "updated_at" timestamp, "deleted_at" timestamp ); ALTER TABLE "teams" ADD FOREIGN KEY ("organization_id") REFERENCES "organizations" ("id"); ALTER TABLE "user_teams" ADD FOREIGN KEY ("team_id") REFERENCES "teams" ("id"); ALTER TABLE "user_teams" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id"); ALTER TABLE "settings" ADD FOREIGN KEY ("organization_id") REFERENCES "organizations" ("id"); ALTER TABLE "subscriptions" ADD FOREIGN KEY ("settings_id") REFERENCES "settings" ("id"); ALTER TABLE "systems" ADD FOREIGN KEY ("team_id") REFERENCES "teams" ("id"); ALTER TABLE "services" ADD FOREIGN KEY ("system_id") REFERENCES "systems" ("id"); CREATE TABLE "services_dependencies" ( "services_dependencies" int[], "dependencies_id" int, PRIMARY KEY ("services_dependencies", "dependencies_id") ); ALTER TABLE "services_dependencies" ADD FOREIGN KEY ("services_dependencies") REFERENCES "services" ("dependencies"); ALTER TABLE "services_dependencies" ADD FOREIGN KEY ("dependencies_id") REFERENCES "dependencies" ("id"); CREATE TABLE "dependencies_opsdocs" ( "dependencies_opsdocs" int[], "opsdocs_id" int, PRIMARY KEY ("dependencies_opsdocs", "opsdocs_id") ); ALTER TABLE "dependencies_opsdocs" ADD FOREIGN KEY ("dependencies_opsdocs") REFERENCES "dependencies" ("opsdocs"); ALTER TABLE "dependencies_opsdocs" ADD FOREIGN KEY ("opsdocs_id") REFERENCES "opsdocs" ("id"); CREATE TABLE "services_opsdocs" ( "services_opsdocs" int[], "opsdocs_id" int, PRIMARY KEY ("services_opsdocs", "opsdocs_id") ); ALTER TABLE "services_opsdocs" ADD FOREIGN KEY ("services_opsdocs") REFERENCES "services" ("opsdocs"); ALTER TABLE "services_opsdocs" ADD FOREIGN KEY ("opsdocs_id") REFERENCES "opsdocs" ("id"); ALTER TABLE "user_teams" ADD FOREIGN KEY ("organization_id") REFERENCES "organizations" ("id"); ALTER TABLE "roles" ADD FOREIGN KEY ("id") REFERENCES "users" ("role");