forked from koshikraj/ottopus
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy path0000_base_schema.sql
More file actions
114 lines (114 loc) · 6.29 KB
/
Copy path0000_base_schema.sql
File metadata and controls
114 lines (114 loc) · 6.29 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
CREATE TABLE "linked_wallets" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"user_id" uuid NOT NULL,
"namespace" text DEFAULT 'eip155' NOT NULL,
"address" text NOT NULL,
"label" text,
"wallet_type" text NOT NULL,
"is_watch_only" boolean DEFAULT false NOT NULL,
"ownership_proof" jsonb,
"proved_at" timestamp with time zone,
"unlinked_at" timestamp with time zone,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "linked_wallets_address_lowercase" CHECK ("linked_wallets"."address" = lower("linked_wallets"."address")),
CONSTRAINT "linked_wallets_proof_required" CHECK ("linked_wallets"."is_watch_only" or "linked_wallets"."ownership_proof" is not null)
);
--> statement-breakpoint
CREATE TABLE "oauth_auth_codes" (
"code_hash" text PRIMARY KEY NOT NULL,
"client_id" text NOT NULL,
"user_id" uuid NOT NULL,
"scopes" text[] NOT NULL,
"code_challenge" text NOT NULL,
"code_challenge_method" text DEFAULT 'S256' NOT NULL,
"redirect_uri" text NOT NULL,
"expires_at" timestamp with time zone NOT NULL,
"consumed_at" timestamp with time zone,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "oauth_auth_codes_pkce_s256" CHECK ("oauth_auth_codes"."code_challenge_method" = 'S256')
);
--> statement-breakpoint
CREATE TABLE "oauth_clients" (
"client_id" text PRIMARY KEY NOT NULL,
"client_name" text NOT NULL,
"redirect_uris" text[] NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "oauth_tokens" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"token_hash" text NOT NULL,
"kind" text NOT NULL,
"client_id" text NOT NULL,
"user_id" uuid NOT NULL,
"scopes" text[] NOT NULL,
"expires_at" timestamp with time zone NOT NULL,
"revoked_at" timestamp with time zone,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "oauth_tokens_token_hash_unique" UNIQUE("token_hash"),
CONSTRAINT "oauth_tokens_kind" CHECK ("oauth_tokens"."kind" in ('access', 'refresh'))
);
--> statement-breakpoint
CREATE TABLE "plan_events" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"plan_id" uuid NOT NULL,
"plan_version" integer NOT NULL,
"status" text NOT NULL,
"detail" jsonb,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "plan_events_status" CHECK ("plan_events"."status" in (
'draft', 'awaiting_review', 'awaiting_signature', 'submitted',
'confirmed', 'failed', 'expired', 'blocked', 'superseded', 'cancelled'
))
);
--> statement-breakpoint
CREATE TABLE "plans" (
"id" uuid DEFAULT gen_random_uuid() NOT NULL,
"version" integer DEFAULT 1 NOT NULL,
"plan_hash" text NOT NULL,
"user_id" uuid NOT NULL,
"wallet_id" uuid,
"intent" jsonb NOT NULL,
"payload" jsonb NOT NULL,
"reason" text,
"expires_at" timestamp with time zone NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "plans_id_version_pk" PRIMARY KEY("id","version")
);
--> statement-breakpoint
CREATE TABLE "simulations" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"plan_id" uuid NOT NULL,
"plan_version" integer NOT NULL,
"provider" text NOT NULL,
"ok" boolean NOT NULL,
"asset_diff" jsonb,
"raw" jsonb,
"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "users" (
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
"privy_did" text NOT NULL,
"email" text,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "users_privy_did_unique" UNIQUE("privy_did")
);
--> statement-breakpoint
ALTER TABLE "linked_wallets" ADD CONSTRAINT "linked_wallets_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_auth_codes" ADD CONSTRAINT "oauth_auth_codes_client_id_oauth_clients_client_id_fk" FOREIGN KEY ("client_id") REFERENCES "public"."oauth_clients"("client_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_auth_codes" ADD CONSTRAINT "oauth_auth_codes_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_tokens" ADD CONSTRAINT "oauth_tokens_client_id_oauth_clients_client_id_fk" FOREIGN KEY ("client_id") REFERENCES "public"."oauth_clients"("client_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "oauth_tokens" ADD CONSTRAINT "oauth_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 "plan_events" ADD CONSTRAINT "plan_events_plan_fk" FOREIGN KEY ("plan_id","plan_version") REFERENCES "public"."plans"("id","version") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "plans" ADD CONSTRAINT "plans_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "plans" ADD CONSTRAINT "plans_wallet_id_linked_wallets_id_fk" FOREIGN KEY ("wallet_id") REFERENCES "public"."linked_wallets"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "simulations" ADD CONSTRAINT "simulations_plan_fk" FOREIGN KEY ("plan_id","plan_version") REFERENCES "public"."plans"("id","version") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "linked_wallets_user_account_idx" ON "linked_wallets" USING btree ("user_id","namespace","address") WHERE "linked_wallets"."unlinked_at" is null;--> statement-breakpoint
CREATE INDEX "linked_wallets_user_idx" ON "linked_wallets" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "oauth_auth_codes_expires_idx" ON "oauth_auth_codes" USING btree ("expires_at");--> statement-breakpoint
CREATE INDEX "oauth_tokens_user_idx" ON "oauth_tokens" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "plan_events_plan_idx" ON "plan_events" USING btree ("plan_id","plan_version");--> statement-breakpoint
CREATE UNIQUE INDEX "plans_hash_idx" ON "plans" USING btree ("plan_hash");--> statement-breakpoint
CREATE INDEX "plans_user_idx" ON "plans" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "simulations_plan_idx" ON "simulations" USING btree ("plan_id","plan_version");