1CREATE TABLE "identity_recovery_addresses" (
2"id" UUID NOT NULL,
3PRIMARY KEY("id"),
4"via" VARCHAR (16) NOT NULL,
5"value" VARCHAR (400) NOT NULL,
6"identity_id" UUID NOT NULL,
7"created_at" timestamp NOT NULL,
8"updated_at" timestamp NOT NULL,
9FOREIGN KEY ("identity_id") REFERENCES "identities" ("id") ON DELETE cascade
10);
11CREATE UNIQUE INDEX "identity_recovery_addresses_status_via_uq_idx" ON "identity_recovery_addresses" (via, value);
12CREATE INDEX "identity_recovery_addresses_status_via_idx" ON "identity_recovery_addresses" (via, value);
13CREATE TABLE "selfservice_recovery_requests" (
14"id" UUID NOT NULL,
15PRIMARY KEY("id"),
16"request_url" VARCHAR (2048) NOT NULL,
17"issued_at" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
18"expires_at" timestamp NOT NULL,
19"messages" jsonb,
20"active_method" VARCHAR (32),
21"csrf_token" VARCHAR (255) NOT NULL,
22"state" VARCHAR (32) NOT NULL,
23"recovered_identity_id" UUID,
24"created_at" timestamp NOT NULL,
25"updated_at" timestamp NOT NULL,
26FOREIGN KEY ("recovered_identity_id") REFERENCES "identities" ("id") ON DELETE cascade
27);
28CREATE TABLE "selfservice_recovery_request_methods" (
29"id" UUID NOT NULL,
30PRIMARY KEY("id"),
31"method" VARCHAR (32) NOT NULL,
32"config" jsonb NOT NULL,
33"selfservice_recovery_request_id" UUID NOT NULL,
34"created_at" timestamp NOT NULL,
35"updated_at" timestamp NOT NULL,
36FOREIGN KEY ("selfservice_recovery_request_id") REFERENCES "selfservice_recovery_requests" ("id") ON DELETE cascade
37);
38CREATE TABLE "identity_recovery_tokens" (
39"id" UUID NOT NULL,
40PRIMARY KEY("id"),
41"token" VARCHAR (64) NOT NULL,
42"used" bool NOT NULL DEFAULT 'false',
43"used_at" timestamp,
44"identity_recovery_address_id" UUID NOT NULL,
45"selfservice_recovery_request_id" UUID NOT NULL,
46"created_at" timestamp NOT NULL,
47"updated_at" timestamp NOT NULL,
48FOREIGN KEY ("identity_recovery_address_id") REFERENCES "identity_recovery_addresses" ("id") ON DELETE cascade,
49FOREIGN KEY ("selfservice_recovery_request_id") REFERENCES "selfservice_recovery_requests" ("id") ON DELETE cascade
50);
51CREATE UNIQUE INDEX "identity_recovery_addresses_code_uq_idx" ON "identity_recovery_tokens" (token);
52CREATE INDEX "identity_recovery_addresses_code_idx" ON "identity_recovery_tokens" (token);View as plain text