mirror of
https://github.com/freedbygrace/DynamoDNS.git
synced 2026-08-05 15:57:43 +00:00
alphaeusmote
4720a91269
Replit-Commit-Author: Agent Replit-Commit-Session-Id: 9111ef36-26c8-4085-84ca-a35dc1fec1b5 Replit-Commit-Screenshot-Url: https://storage.googleapis.com/screenshot-production-us-central1/7083d608-d6d3-4a6a-9a27-6286c5109627/9eca506b-31ec-4590-94ae-d7f471d7a0f7.jpg
173 lines
8.4 KiB
SQL
173 lines
8.4 KiB
SQL
CREATE TABLE "api_tokens" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"token" text NOT NULL,
|
|
"organization_id" uuid NOT NULL,
|
|
"permissions" text[],
|
|
"created_by" uuid NOT NULL,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"expires_at" timestamp,
|
|
CONSTRAINT "api_tokens_token_unique" UNIQUE("token")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "custom_roles" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"description" text,
|
|
"permissions" text[] NOT NULL,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"created_by" uuid NOT NULL,
|
|
CONSTRAINT "custom_roles_name_unique" UNIQUE("name")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "dns_history" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"record_id" uuid NOT NULL,
|
|
"action" text NOT NULL,
|
|
"previous_value" text,
|
|
"new_value" text,
|
|
"user_id" uuid,
|
|
"timestamp" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "dns_metrics" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"domain_id" uuid,
|
|
"record_id" uuid,
|
|
"metric_type" text NOT NULL,
|
|
"value" jsonb NOT NULL,
|
|
"source" text,
|
|
"tags" text[],
|
|
"timestamp" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "dns_records" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"domain_id" uuid NOT NULL,
|
|
"name" text NOT NULL,
|
|
"type" text NOT NULL,
|
|
"content" text NOT NULL,
|
|
"ttl" integer DEFAULT 3600,
|
|
"proxied" boolean DEFAULT false,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
"is_auto_ip" boolean DEFAULT false NOT NULL,
|
|
"notes" text,
|
|
"last_updated" timestamp,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "domains" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"organization_id" uuid NOT NULL,
|
|
"provider_id" uuid NOT NULL,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
"last_updated" timestamp,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "group_members" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"group_id" uuid NOT NULL,
|
|
"member_type" text NOT NULL,
|
|
"member_id" uuid NOT NULL,
|
|
"added_at" timestamp DEFAULT now() NOT NULL,
|
|
"added_by" uuid NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "group_roles" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"group_id" uuid NOT NULL,
|
|
"role_id" text NOT NULL,
|
|
"is_system_role" boolean NOT NULL,
|
|
"assigned_at" timestamp DEFAULT now() NOT NULL,
|
|
"assigned_by" uuid NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "groups" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"description" text,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"created_by" uuid NOT NULL,
|
|
"parent_group_id" uuid
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "organizations" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "providers" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"type" text NOT NULL,
|
|
"credentials" jsonb,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "users" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"username" text NOT NULL,
|
|
"password" text NOT NULL,
|
|
"email" text NOT NULL,
|
|
"full_name" text,
|
|
"role" text DEFAULT 'user' NOT NULL,
|
|
"organization_id" uuid,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "users_username_unique" UNIQUE("username"),
|
|
CONSTRAINT "users_email_unique" UNIQUE("email")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "webhook_delivery_logs" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"webhook_id" uuid NOT NULL,
|
|
"event" text NOT NULL,
|
|
"payload" jsonb NOT NULL,
|
|
"signature" text,
|
|
"status" boolean NOT NULL,
|
|
"status_code" integer,
|
|
"message" text NOT NULL,
|
|
"response_body" text,
|
|
"retry_count" integer DEFAULT 0 NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "webhooks" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"url" text NOT NULL,
|
|
"organization_id" uuid NOT NULL,
|
|
"secret" text,
|
|
"events" text[] NOT NULL,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
"last_triggered" timestamp,
|
|
"created_by" uuid NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "api_tokens" ADD CONSTRAINT "api_tokens_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "api_tokens" ADD CONSTRAINT "api_tokens_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "custom_roles" ADD CONSTRAINT "custom_roles_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "dns_history" ADD CONSTRAINT "dns_history_record_id_dns_records_id_fk" FOREIGN KEY ("record_id") REFERENCES "public"."dns_records"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "dns_history" ADD CONSTRAINT "dns_history_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "dns_metrics" ADD CONSTRAINT "dns_metrics_domain_id_domains_id_fk" FOREIGN KEY ("domain_id") REFERENCES "public"."domains"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "dns_metrics" ADD CONSTRAINT "dns_metrics_record_id_dns_records_id_fk" FOREIGN KEY ("record_id") REFERENCES "public"."dns_records"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "dns_records" ADD CONSTRAINT "dns_records_domain_id_domains_id_fk" FOREIGN KEY ("domain_id") REFERENCES "public"."domains"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "domains" ADD CONSTRAINT "domains_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "domains" ADD CONSTRAINT "domains_provider_id_providers_id_fk" FOREIGN KEY ("provider_id") REFERENCES "public"."providers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "group_members" ADD CONSTRAINT "group_members_group_id_groups_id_fk" FOREIGN KEY ("group_id") REFERENCES "public"."groups"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "group_members" ADD CONSTRAINT "group_members_added_by_users_id_fk" FOREIGN KEY ("added_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "group_roles" ADD CONSTRAINT "group_roles_group_id_groups_id_fk" FOREIGN KEY ("group_id") REFERENCES "public"."groups"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "group_roles" ADD CONSTRAINT "group_roles_assigned_by_users_id_fk" FOREIGN KEY ("assigned_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "groups" ADD CONSTRAINT "groups_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "users" ADD CONSTRAINT "users_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "webhook_delivery_logs" ADD CONSTRAINT "webhook_delivery_logs_webhook_id_webhooks_id_fk" FOREIGN KEY ("webhook_id") REFERENCES "public"."webhooks"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "webhooks" ADD CONSTRAINT "webhooks_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "webhooks" ADD CONSTRAINT "webhooks_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action; |