271 lines
15 KiB
SQL
271 lines
15 KiB
SQL
CREATE TYPE "public"."deployment_status" AS ENUM('pending', 'success', 'failed');--> statement-breakpoint
|
|
CREATE TYPE "public"."invoice_status" AS ENUM('draft', 'open', 'paid', 'void', 'uncollectible');--> statement-breakpoint
|
|
CREATE TYPE "public"."message_role" AS ENUM('user', 'assistant', 'system');--> statement-breakpoint
|
|
CREATE TYPE "public"."org_member_role" AS ENUM('owner', 'admin', 'member', 'viewer');--> statement-breakpoint
|
|
CREATE TYPE "public"."project_status" AS ENUM('draft', 'building', 'deployed', 'error');--> statement-breakpoint
|
|
CREATE TYPE "public"."subscription_status" AS ENUM('active', 'past_due', 'canceled', 'trialing');--> statement-breakpoint
|
|
CREATE TYPE "public"."subscription_tier" AS ENUM('free', 'pro', 'enterprise');--> statement-breakpoint
|
|
CREATE TYPE "public"."user_role" AS ENUM('admin', 'co_admin', 'owner', 'user');--> statement-breakpoint
|
|
CREATE TABLE "ai_models" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"display_name" varchar(255) NOT NULL,
|
|
"api_name" varchar(255) NOT NULL,
|
|
"provider_id" uuid NOT NULL,
|
|
"description" text,
|
|
"max_output_tokens" integer,
|
|
"context_window" integer,
|
|
"is_available" boolean DEFAULT true NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "ai_providers" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" varchar(255) NOT NULL,
|
|
"api_base_url" text NOT NULL,
|
|
"env_var_name" varchar(100),
|
|
"is_builtin" boolean DEFAULT true NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "audit_logs" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid,
|
|
"organization_id" uuid,
|
|
"action" varchar(255) NOT NULL,
|
|
"resource_type" varchar(100),
|
|
"resource_id" uuid,
|
|
"metadata" jsonb,
|
|
"ip_address" varchar(45),
|
|
"user_agent" text,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "chats" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"project_id" uuid NOT NULL,
|
|
"title" varchar(255),
|
|
"created_by" uuid,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "deployment_logs" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"project_id" uuid NOT NULL,
|
|
"version_id" uuid,
|
|
"status" "deployment_status" NOT NULL,
|
|
"logs" text,
|
|
"error_message" text,
|
|
"started_at" timestamp DEFAULT now() NOT NULL,
|
|
"completed_at" timestamp
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "design_systems" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"project_id" uuid NOT NULL,
|
|
"name" varchar(255) NOT NULL,
|
|
"pattern" varchar(255),
|
|
"style" varchar(255),
|
|
"color_palette" jsonb,
|
|
"typography" jsonb,
|
|
"effects" jsonb,
|
|
"anti_patterns" jsonb,
|
|
"generated_by_ai" boolean DEFAULT true NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "email_verification_tokens" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"token" varchar(255) NOT NULL,
|
|
"expires_at" timestamp NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "email_verification_tokens_token_unique" UNIQUE("token")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "invoices" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"organization_id" uuid NOT NULL,
|
|
"stripe_invoice_id" varchar(255),
|
|
"amount" numeric(10, 2) NOT NULL,
|
|
"currency" varchar(3) DEFAULT 'USD' NOT NULL,
|
|
"status" "invoice_status" NOT NULL,
|
|
"due_date" timestamp,
|
|
"paid_at" timestamp,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "invoices_stripe_invoice_id_unique" UNIQUE("stripe_invoice_id")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "messages" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"chat_id" uuid NOT NULL,
|
|
"role" "message_role" NOT NULL,
|
|
"content" text NOT NULL,
|
|
"metadata" jsonb,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "organization_members" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"organization_id" uuid NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"role" "org_member_role" NOT NULL,
|
|
"permissions" jsonb,
|
|
"invited_by" uuid,
|
|
"joined_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "organizations" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" varchar(255) NOT NULL,
|
|
"slug" varchar(255) NOT NULL,
|
|
"owner_id" uuid NOT NULL,
|
|
"stripe_customer_id" varchar(255),
|
|
"subscription_tier" "subscription_tier" DEFAULT 'free' NOT NULL,
|
|
"subscription_status" "subscription_status" DEFAULT 'active' NOT NULL,
|
|
"trial_ends_at" timestamp,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "organizations_slug_unique" UNIQUE("slug")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "password_reset_tokens" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"token" varchar(255) NOT NULL,
|
|
"expires_at" timestamp NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "password_reset_tokens_token_unique" UNIQUE("token")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "project_versions" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"project_id" uuid NOT NULL,
|
|
"version_number" varchar(50) NOT NULL,
|
|
"commit_hash" varchar(255),
|
|
"gitea_commit_id" varchar(255),
|
|
"is_current" boolean DEFAULT false NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "projects" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"organization_id" uuid NOT NULL,
|
|
"name" varchar(255) NOT NULL,
|
|
"description" text,
|
|
"slug" varchar(255) NOT NULL,
|
|
"gitea_repo_id" integer,
|
|
"gitea_repo_url" text,
|
|
"easypanel_project_id" varchar(255),
|
|
"easypanel_app_id" varchar(255),
|
|
"easypanel_database_id" varchar(255),
|
|
"deployment_url" text,
|
|
"install_command" text DEFAULT 'npm install',
|
|
"start_command" text DEFAULT 'npm start',
|
|
"build_command" text DEFAULT 'npm run build',
|
|
"environment_variables" jsonb DEFAULT '{}' NOT NULL,
|
|
"status" "project_status" DEFAULT 'draft' NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL,
|
|
"last_deployed_at" timestamp
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "prompts" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"title" varchar(255) NOT NULL,
|
|
"description" text,
|
|
"content" text NOT NULL,
|
|
"category" varchar(100),
|
|
"is_public" boolean DEFAULT false NOT NULL,
|
|
"created_by" uuid,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "sessions" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"refresh_token_hash" varchar(255) NOT NULL,
|
|
"expires_at" timestamp NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"device_info" jsonb
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "subscription_events" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"organization_id" uuid NOT NULL,
|
|
"event_type" varchar(100) NOT NULL,
|
|
"stripe_event_id" varchar(255),
|
|
"metadata" jsonb,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "user_api_keys" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"provider_id" uuid NOT NULL,
|
|
"encrypted_key" text NOT NULL,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "users" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"email" varchar(255) NOT NULL,
|
|
"password_hash" varchar(255) NOT NULL,
|
|
"full_name" varchar(255),
|
|
"role" "user_role" NOT NULL,
|
|
"avatar_url" text,
|
|
"email_verified" boolean DEFAULT false NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL,
|
|
"last_login_at" timestamp,
|
|
"is_active" boolean DEFAULT true NOT NULL,
|
|
CONSTRAINT "users_email_unique" UNIQUE("email")
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "ai_models" ADD CONSTRAINT "ai_models_provider_id_ai_providers_id_fk" FOREIGN KEY ("provider_id") REFERENCES "public"."ai_providers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_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 "audit_logs" ADD CONSTRAINT "audit_logs_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 "chats" ADD CONSTRAINT "chats_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "chats" ADD CONSTRAINT "chats_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "deployment_logs" ADD CONSTRAINT "deployment_logs_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "deployment_logs" ADD CONSTRAINT "deployment_logs_version_id_project_versions_id_fk" FOREIGN KEY ("version_id") REFERENCES "public"."project_versions"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "design_systems" ADD CONSTRAINT "design_systems_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "email_verification_tokens" ADD CONSTRAINT "email_verification_tokens_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "invoices" ADD CONSTRAINT "invoices_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "messages" ADD CONSTRAINT "messages_chat_id_chats_id_fk" FOREIGN KEY ("chat_id") REFERENCES "public"."chats"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "organization_members" ADD CONSTRAINT "organization_members_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "organization_members" ADD CONSTRAINT "organization_members_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "organization_members" ADD CONSTRAINT "organization_members_invited_by_users_id_fk" FOREIGN KEY ("invited_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "organizations" ADD CONSTRAINT "organizations_owner_id_users_id_fk" FOREIGN KEY ("owner_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "password_reset_tokens" ADD CONSTRAINT "password_reset_tokens_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "project_versions" ADD CONSTRAINT "project_versions_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "projects" ADD CONSTRAINT "projects_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "prompts" ADD CONSTRAINT "prompts_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "subscription_events" ADD CONSTRAINT "subscription_events_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "user_api_keys" ADD CONSTRAINT "user_api_keys_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "user_api_keys" ADD CONSTRAINT "user_api_keys_provider_id_ai_providers_id_fk" FOREIGN KEY ("provider_id") REFERENCES "public"."ai_providers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
CREATE INDEX "idx_audit_logs_user" ON "audit_logs" USING btree ("user_id");--> statement-breakpoint
|
|
CREATE INDEX "idx_audit_logs_org" ON "audit_logs" USING btree ("organization_id");--> statement-breakpoint
|
|
CREATE INDEX "idx_audit_logs_created" ON "audit_logs" USING btree ("created_at");--> statement-breakpoint
|
|
CREATE INDEX "idx_chats_project" ON "chats" USING btree ("project_id");--> statement-breakpoint
|
|
CREATE INDEX "idx_deployment_logs_project" ON "deployment_logs" USING btree ("project_id");--> statement-breakpoint
|
|
CREATE INDEX "idx_messages_chat" ON "messages" USING btree ("chat_id");--> statement-breakpoint
|
|
CREATE INDEX "idx_messages_created" ON "messages" USING btree ("created_at");--> statement-breakpoint
|
|
CREATE INDEX "idx_org_members_org" ON "organization_members" USING btree ("organization_id");--> statement-breakpoint
|
|
CREATE INDEX "idx_org_members_user" ON "organization_members" USING btree ("user_id");--> statement-breakpoint
|
|
CREATE INDEX "unique_org_member" ON "organization_members" USING btree ("organization_id","user_id");--> statement-breakpoint
|
|
CREATE INDEX "idx_organizations_slug" ON "organizations" USING btree ("slug");--> statement-breakpoint
|
|
CREATE INDEX "idx_organizations_owner" ON "organizations" USING btree ("owner_id");--> statement-breakpoint
|
|
CREATE INDEX "idx_project_versions_project" ON "project_versions" USING btree ("project_id");--> statement-breakpoint
|
|
CREATE INDEX "unique_project_version" ON "project_versions" USING btree ("project_id","version_number");--> statement-breakpoint
|
|
CREATE INDEX "idx_projects_org" ON "projects" USING btree ("organization_id");--> statement-breakpoint
|
|
CREATE INDEX "idx_projects_slug" ON "projects" USING btree ("organization_id","slug");--> statement-breakpoint
|
|
CREATE INDEX "unique_user_provider" ON "user_api_keys" USING btree ("user_id","provider_id");--> statement-breakpoint
|
|
CREATE INDEX "idx_users_email" ON "users" USING btree ("email"); |