forked from lumpinif/deepcrawl
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathsupabase-schema.sql
More file actions
154 lines (146 loc) · 5.84 KB
/
Copy pathsupabase-schema.sql
File metadata and controls
154 lines (146 loc) · 5.84 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
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
-- Deepcrawl Auth Database Schema
-- Run this in Supabase SQL Editor
-- Migration 0000: Core tables
CREATE TABLE "account" (
"id" text PRIMARY KEY NOT NULL,
"account_id" text NOT NULL,
"provider_id" text NOT NULL,
"user_id" text NOT NULL,
"access_token" text,
"refresh_token" text,
"id_token" text,
"access_token_expires_at" timestamp,
"refresh_token_expires_at" timestamp,
"scope" text,
"password" text,
"created_at" timestamp NOT NULL,
"updated_at" timestamp NOT NULL
);
CREATE TABLE "session" (
"id" text PRIMARY KEY NOT NULL,
"expires_at" timestamp NOT NULL,
"token" text NOT NULL,
"created_at" timestamp NOT NULL,
"updated_at" timestamp NOT NULL,
"ip_address" text,
"user_agent" text,
"user_id" text NOT NULL,
CONSTRAINT "session_token_unique" UNIQUE("token")
);
CREATE TABLE "user" (
"id" text PRIMARY KEY NOT NULL,
"name" text NOT NULL,
"email" text NOT NULL,
"email_verified" boolean NOT NULL,
"image" text,
"created_at" timestamp NOT NULL,
"updated_at" timestamp NOT NULL,
CONSTRAINT "user_email_unique" UNIQUE("email")
);
CREATE TABLE "verification" (
"id" text PRIMARY KEY NOT NULL,
"identifier" text NOT NULL,
"value" text NOT NULL,
"expires_at" timestamp NOT NULL,
"created_at" timestamp,
"updated_at" timestamp
);
ALTER TABLE "account" ADD CONSTRAINT "account_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "session" ADD CONSTRAINT "session_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;
-- Migration 0001: User extensions
ALTER TABLE "session" ADD COLUMN "impersonated_by" text;
ALTER TABLE "user" ADD COLUMN "role" text;
ALTER TABLE "user" ADD COLUMN "banned" boolean;
ALTER TABLE "user" ADD COLUMN "ban_reason" text;
ALTER TABLE "user" ADD COLUMN "ban_expires" timestamp;
-- Migration 0002: API Keys
CREATE TABLE "apikey" (
"id" text PRIMARY KEY NOT NULL,
"name" text,
"start" text,
"prefix" text,
"key" text NOT NULL,
"user_id" text NOT NULL,
"refill_interval" integer,
"refill_amount" integer,
"last_refill_at" timestamp,
"enabled" boolean DEFAULT true,
"rate_limit_enabled" boolean DEFAULT true,
"rate_limit_time_window" integer DEFAULT 86400000,
"rate_limit_max" integer DEFAULT 10,
"request_count" integer,
"remaining" integer,
"last_request" timestamp,
"expires_at" timestamp,
"created_at" timestamp NOT NULL,
"updated_at" timestamp NOT NULL,
"permissions" text,
"metadata" text
);
ALTER TABLE "apikey" ADD CONSTRAINT "apikey_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;
-- Migration 0003: Organizations
CREATE TABLE "invitation" (
"id" text PRIMARY KEY NOT NULL,
"organization_id" text NOT NULL,
"email" text NOT NULL,
"role" text,
"status" text DEFAULT 'pending' NOT NULL,
"expires_at" timestamp NOT NULL,
"inviter_id" text NOT NULL
);
CREATE TABLE "member" (
"id" text PRIMARY KEY NOT NULL,
"organization_id" text NOT NULL,
"user_id" text NOT NULL,
"role" text DEFAULT 'member' NOT NULL,
"created_at" timestamp NOT NULL
);
CREATE TABLE "organization" (
"id" text PRIMARY KEY NOT NULL,
"name" text NOT NULL,
"slug" text,
"logo" text,
"created_at" timestamp NOT NULL,
"metadata" text,
CONSTRAINT "organization_slug_unique" UNIQUE("slug")
);
CREATE TABLE "passkey" (
"id" text PRIMARY KEY NOT NULL,
"name" text,
"public_key" text NOT NULL,
"user_id" text NOT NULL,
"credential_i_d" text NOT NULL,
"counter" integer NOT NULL,
"device_type" text NOT NULL,
"backed_up" boolean NOT NULL,
"transports" text,
"created_at" timestamp
);
ALTER TABLE "session" ADD COLUMN "active_organization_id" text;
ALTER TABLE "invitation" ADD CONSTRAINT "invitation_organization_id_organization_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organization"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "invitation" ADD CONSTRAINT "invitation_inviter_id_user_id_fk" FOREIGN KEY ("inviter_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "member" ADD CONSTRAINT "member_organization_id_organization_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organization"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "member" ADD CONSTRAINT "member_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;
ALTER TABLE "passkey" ADD CONSTRAINT "passkey_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;
-- Migration 0004: Passkey extension
ALTER TABLE "passkey" ADD COLUMN "aaguid" text;
-- Migration 0005: Rate limit defaults
ALTER TABLE "apikey" ALTER COLUMN "rate_limit_max" SET DEFAULT 100;
-- Migration 0006: User email normalization
ALTER TABLE "apikey" ALTER COLUMN "rate_limit_time_window" SET DEFAULT 60000;
ALTER TABLE "apikey" ALTER COLUMN "rate_limit_max" SET DEFAULT 20;
ALTER TABLE "user" ADD COLUMN "normalized_email" text;
ALTER TABLE "user" ADD CONSTRAINT "user_normalized_email_unique" UNIQUE("normalized_email");
-- Migration 0007: Defaults
ALTER TABLE "passkey" RENAME COLUMN "credential_i_d" TO "credential_id";
ALTER TABLE "account" ALTER COLUMN "created_at" SET DEFAULT now();
ALTER TABLE "apikey" ALTER COLUMN "request_count" SET DEFAULT 0;
ALTER TABLE "session" ALTER COLUMN "created_at" SET DEFAULT now();
ALTER TABLE "user" ALTER COLUMN "email_verified" SET DEFAULT false;
ALTER TABLE "user" ALTER COLUMN "created_at" SET DEFAULT now();
ALTER TABLE "user" ALTER COLUMN "updated_at" SET DEFAULT now();
ALTER TABLE "user" ALTER COLUMN "banned" SET DEFAULT false;
ALTER TABLE "verification" ALTER COLUMN "created_at" SET DEFAULT now();
ALTER TABLE "verification" ALTER COLUMN "created_at" SET NOT NULL;
ALTER TABLE "verification" ALTER COLUMN "updated_at" SET DEFAULT now();
ALTER TABLE "verification" ALTER COLUMN "updated_at" SET NOT NULL;