129 lines
4.7 KiB
SQL
129 lines
4.7 KiB
SQL
DO $$ BEGIN
|
|
CREATE TYPE "role" AS ENUM('viewer', 'initiator', 'approver', 'admin');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS "approvals" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"proposal_id" uuid NOT NULL,
|
|
"signer" text NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS "audit_logs" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"organization_id" uuid NOT NULL,
|
|
"treasury_id" uuid,
|
|
"action" text NOT NULL,
|
|
"actor" text NOT NULL,
|
|
"details" text,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS "memberships" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"organization_id" uuid NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"role" "role" NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS "organizations" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"name" text NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS "sub_accounts" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"treasury_id" uuid NOT NULL,
|
|
"address" text NOT NULL,
|
|
"label" text,
|
|
"metadata_hash" text,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "sub_accounts_address_unique" UNIQUE("address")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS "transaction_proposals" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"treasury_id" uuid NOT NULL,
|
|
"proposal_id" integer NOT NULL,
|
|
"wallet_address" text NOT NULL,
|
|
"to" text NOT NULL,
|
|
"value" text NOT NULL,
|
|
"token" text,
|
|
"data" text,
|
|
"status" text DEFAULT 'pending' NOT NULL,
|
|
"proposer" text NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"executed_at" timestamp
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS "treasuries" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"organization_id" uuid NOT NULL,
|
|
"chain_id" integer NOT NULL,
|
|
"main_wallet" text NOT NULL,
|
|
"label" text,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
"updated_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "treasuries_main_wallet_unique" UNIQUE("main_wallet")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE IF NOT EXISTS "users" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"address" text NOT NULL,
|
|
"created_at" timestamp DEFAULT now() NOT NULL,
|
|
CONSTRAINT "users_address_unique" UNIQUE("address")
|
|
);
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "approvals" ADD CONSTRAINT "approvals_proposal_id_transaction_proposals_id_fk" FOREIGN KEY ("proposal_id") REFERENCES "transaction_proposals"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "organizations"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_treasury_id_treasuries_id_fk" FOREIGN KEY ("treasury_id") REFERENCES "treasuries"("id") ON DELETE set null ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "memberships" ADD CONSTRAINT "memberships_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "organizations"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "memberships" ADD CONSTRAINT "memberships_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "sub_accounts" ADD CONSTRAINT "sub_accounts_treasury_id_treasuries_id_fk" FOREIGN KEY ("treasury_id") REFERENCES "treasuries"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "transaction_proposals" ADD CONSTRAINT "transaction_proposals_treasury_id_treasuries_id_fk" FOREIGN KEY ("treasury_id") REFERENCES "treasuries"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
--> statement-breakpoint
|
|
DO $$ BEGIN
|
|
ALTER TABLE "treasuries" ADD CONSTRAINT "treasuries_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "organizations"("id") ON DELETE cascade ON UPDATE no action;
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|