github.com/icyphox/x@v0.0.355-0.20220311094250-029bd783e8b8/popx/stub/migrations/legacy/20200519101057_create_recovery_addresses.postgres.up.sql (about)

     1  CREATE TABLE "identity_recovery_addresses" (
     2  "id" UUID NOT NULL,
     3  PRIMARY 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,
     9  FOREIGN KEY ("identity_id") REFERENCES "identities" ("id") ON DELETE cascade
    10  );
    11  CREATE UNIQUE INDEX "identity_recovery_addresses_status_via_uq_idx" ON "identity_recovery_addresses" (via, value);
    12  CREATE INDEX "identity_recovery_addresses_status_via_idx" ON "identity_recovery_addresses" (via, value);
    13  CREATE TABLE "selfservice_recovery_requests" (
    14  "id" UUID NOT NULL,
    15  PRIMARY 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,
    26  FOREIGN KEY ("recovered_identity_id") REFERENCES "identities" ("id") ON DELETE cascade
    27  );
    28  CREATE TABLE "selfservice_recovery_request_methods" (
    29  "id" UUID NOT NULL,
    30  PRIMARY 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,
    36  FOREIGN KEY ("selfservice_recovery_request_id") REFERENCES "selfservice_recovery_requests" ("id") ON DELETE cascade
    37  );
    38  CREATE TABLE "identity_recovery_tokens" (
    39  "id" UUID NOT NULL,
    40  PRIMARY 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,
    48  FOREIGN KEY ("identity_recovery_address_id") REFERENCES "identity_recovery_addresses" ("id") ON DELETE cascade,
    49  FOREIGN KEY ("selfservice_recovery_request_id") REFERENCES "selfservice_recovery_requests" ("id") ON DELETE cascade
    50  );
    51  CREATE UNIQUE INDEX "identity_recovery_addresses_code_uq_idx" ON "identity_recovery_tokens" (token);
    52  CREATE INDEX "identity_recovery_addresses_code_idx" ON "identity_recovery_tokens" (token);