# HG changeset patch # User jbe # Date 1505691872 -7200 # Node ID 0fc78541dc154115a727792127448ab2b4d2981d # Parent 3f21631a7f6d112f586ed9185b5fd4f4cb218e49 Added "verification" table to store verification requests diff -r 3f21631a7f6d -r 0fc78541dc15 core.sql --- a/core.sql Sun Sep 17 13:19:37 2017 +0200 +++ b/core.sql Mon Sep 18 01:44:32 2017 +0200 @@ -207,6 +207,38 @@ COMMENT ON COLUMN "member_history"."until" IS 'Timestamp until the data was valid'; +CREATE TABLE "verification" ( + "id" SERIAL8 PRIMARY KEY, + "requested" TIMESTAMPTZ, + "request_origin" JSONB, + "request_data" JSONB, + "verified" TIMESTAMPTZ, + "verification_origin" JSONB, + "verification_data" JSONB, + "denied" TIMESTAMPTZ, + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, + "comment" TEXT, + CONSTRAINT "verified_and_denied_conflict" CHECK ( + "verified" ISNULL OR "denied" ISNULL ) ); +CREATE INDEX "verification_requested_idx" ON "verification" ("requested"); +CREATE INDEX "verification_open_request_idx" ON "verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL; +CREATE INDEX "verification_verified_idx" ON "verification" ("verified"); +CREATE INDEX "verification_denied_idx" ON "verification" ("denied"); +CREATE INDEX "verification_member_id_idx" ON "verification" ("member_id"); + +COMMENT ON TABLE "verification" IS 'Request to verify a participant'; + +COMMENT ON COLUMN "verification"."requested" IS 'Timestamp when request for verification has been submitted'; +COMMENT ON COLUMN "verification"."request_origin" IS 'JSON data containing information about the origin of the request (e.g. IP address or hostname)'; +COMMENT ON COLUMN "verification"."request_data" IS 'JSON data containing information about the entity to be verified (e.g. real name, address, etc.)'; +COMMENT ON COLUMN "verification"."verified" IS 'Timestamp when request for verification has been accepted by authority'; +COMMENT ON COLUMN "verification"."verification_origin" IS 'JSON data containing information about the authority or operator who accepted or denied the request'; +COMMENT ON COLUMN "verification"."verification_data" IS 'JSON data containing additional verified data, but all public information shall be copied to "member"."identification", "member"."verification" and/or "member"."name" if applicable for setup'; +COMMENT ON COLUMN "verification"."denied" IS 'Timestamp when request for verification has been denied by authority'; +COMMENT ON COLUMN "verification"."member_id" IS 'Timestamp when request for verification has been denied by authority'; +COMMENT ON COLUMN "verification"."comment" IS 'Administrative comment'; + + CREATE TABLE "member_settings" ( "member_id" INT4 PRIMARY KEY REFERENCES "member" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "settings" JSONB NOT NULL CHECK (jsonb_typeof("settings") = 'object') ); @@ -6336,6 +6368,7 @@ "password_reset_secret_expiry" = NULL, "location" = NULL; -- "text_search_data" is updated by triggers + DELETE FROM "verification"; DELETE FROM "member_settings"; DELETE FROM "member_useterms"; DELETE FROM "member_profile"; diff -r 3f21631a7f6d -r 0fc78541dc15 update/core-update.v3.2.2-v4.0.0.sql --- a/update/core-update.v3.2.2-v4.0.0.sql Sun Sep 17 13:19:37 2017 +0200 +++ b/update/core-update.v3.2.2-v4.0.0.sql Mon Sep 18 01:44:32 2017 +0200 @@ -33,6 +33,38 @@ COMMENT ON COLUMN "system_setting"."snapshot_retention" IS 'Unreferenced snapshots are retained for the given period of time after creation; set to NULL for infinite retention.'; +CREATE TABLE "verification" ( + "id" SERIAL8 PRIMARY KEY, + "requested" TIMESTAMPTZ, + "request_origin" JSONB, + "request_data" JSONB, + "verified" TIMESTAMPTZ, + "verification_origin" JSONB, + "verification_data" JSONB, + "denied" TIMESTAMPTZ, + "member_id" INT4 REFERENCES "member" ("id") ON DELETE RESTRICT ON UPDATE CASCADE, + "comment" TEXT, + CONSTRAINT "verified_and_denied_conflict" CHECK ( + "verified" ISNULL OR "denied" ISNULL ) ); +CREATE INDEX "verification_requested_idx" ON "verification" ("requested"); +CREATE INDEX "verification_open_request_idx" ON "verification" ("requested") WHERE "verified" ISNULL AND "denied" ISNULL; +CREATE INDEX "verification_verified_idx" ON "verification" ("verified"); +CREATE INDEX "verification_denied_idx" ON "verification" ("denied"); +CREATE INDEX "verification_member_id_idx" ON "verification" ("member_id"); + +COMMENT ON TABLE "verification" IS 'Request to verify a participant'; + +COMMENT ON COLUMN "verification"."requested" IS 'Timestamp when request for verification has been submitted'; +COMMENT ON COLUMN "verification"."request_origin" IS 'JSON data containing information about the origin of the request (e.g. IP address or hostname)'; +COMMENT ON COLUMN "verification"."request_data" IS 'JSON data containing information about the entity to be verified (e.g. real name, address, etc.)'; +COMMENT ON COLUMN "verification"."verified" IS 'Timestamp when request for verification has been accepted by authority'; +COMMENT ON COLUMN "verification"."verification_origin" IS 'JSON data containing information about the authority or operator who accepted or denied the request'; +COMMENT ON COLUMN "verification"."verification_data" IS 'JSON data containing additional verified data, but all public information shall be copied to "member"."identification", "member"."verification" and/or "member"."name" if applicable for setup'; +COMMENT ON COLUMN "verification"."denied" IS 'Timestamp when request for verification has been denied by authority'; +COMMENT ON COLUMN "verification"."member_id" IS 'Timestamp when request for verification has been denied by authority'; +COMMENT ON COLUMN "verification"."comment" IS 'Administrative comment'; + + ALTER TABLE "member" ADD COLUMN "deleted" TIMESTAMPTZ; ALTER TABLE "member" ADD CONSTRAINT "deleted_requires_locked" CHECK ("deleted" ISNULL OR "locked" = TRUE); @@ -3364,6 +3396,7 @@ "password_reset_secret_expiry" = NULL, "location" = NULL; -- "text_search_data" is updated by triggers + DELETE FROM "verification"; DELETE FROM "member_settings"; DELETE FROM "member_useterms"; DELETE FROM "member_profile";