Add 'verified' database columns for email/phone.

These are so we can state whether a user's email address or phone number
have been verified by confirmation email/text.

3 files changed, 27 insertions, 1 deletions

diff --git a/db/downgrade_0056---0055.sql b/db/downgrade_0056---0055.sql
new file mode 100644
index 000000000..75b69dd4c
--- /dev/null
+++ b/db/downgrade_0056---0055.sql
@@ -0,0 +1,10 @@
+BEGIN;
+
+ALTER TABLE users DROP email_verified;
+ALTER TABLE users DROP phone_verified;
+
+DELETE FROM users WHERE email IS NULL;
+ALTER TABLE users ALTER email SET NOT NULL;
+ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);
+
+COMMIT;
diff --git a/db/schema.sql b/db/schema.sql
index f428ff59d..f2197dc52 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -21,9 +21,11 @@ create table sessions (
 -- users table
 create table users (
     id              serial  not null primary key,
-    email           text    not null unique,
+    email           text,
+    email_verified  boolean not null default 'f',
     name            text,
     phone           text,
+    phone_verified  boolean not null default 'f',
     password        text    not null default '',
     from_body       integer,
     flagged         boolean not null default 'f',
@@ -34,6 +36,8 @@ create table users (
     area_id         integer,
     extra           text
 );
+CREATE UNIQUE INDEX users_email_verified_unique ON users (email) WHERE email_verified;
+CREATE UNIQUE INDEX users_phone_verified_unique ON users (phone) WHERE phone_verified;
 
 -- Record details of reporting bodies, including open311 configuration details
 create table body (
diff --git a/db/schema_0056-phone-login.sql b/db/schema_0056-phone-login.sql
new file mode 100644
index 000000000..f5e0b07e4
--- /dev/null
+++ b/db/schema_0056-phone-login.sql
@@ -0,0 +1,12 @@
+BEGIN;
+
+ALTER TABLE users ADD email_verified boolean not null default 'f';
+UPDATE USERS set email_verified = 't';
+ALTER TABLE users ADD phone_verified boolean not null default 'f';
+
+ALTER TABLE users ALTER email DROP NOT NULL;
+ALTER TABLE users DROP CONSTRAINT users_email_key;
+CREATE UNIQUE INDEX users_email_verified_unique ON users (email) WHERE email_verified;
+CREATE UNIQUE INDEX users_phone_verified_unique ON users (phone) WHERE phone_verified;
+
+COMMIT;