add user models

1 files changed, 66 insertions, 0 deletions

diff --git a/src/guff/migrations.cr b/src/guff/migrations.cr
index c32e671..495b3dd 100644
--- a/src/guff/migrations.cr
+++ b/src/guff/migrations.cr
@@ -145,5 +145,71 @@ module Guff
     }, %{
       CREATE INDEX in_sessions_sid on sessions(session_id)
     }],
+  }, {
+    id: "6-roles",
+
+    sql: [%{
+      CREATE TABLE roles (
+        -- unique identifier
+        role_id   INTEGER PRIMARY KEY,
+
+        -- user-visible name of role
+        role_name TEXT UNIQUE NOT NULL,
+
+        -- brief description of role
+        role_desc TEXT NOT NULL
+                  CHECK (LENGTH(role_desc) > 0)
+      )
+    }, %{
+      INSERT INTO roles(role_id, role_name, role_desc) VALUES
+        (0, 'guest',  'Guest account, no login.'),
+        (1, 'viewer', 'Login and read-only access.'),
+        (2, 'editor', 'Can create and edit posts.'),
+        (3, 'admin',  'Can create and edit posts and modify site.')
+    }],
+  }, {
+    id: "7-users",
+
+    sql: [%{
+      CREATE TABLE users (
+        user_id         INTEGER PRIMARY KEY,
+
+        -- when was this user created?
+        created_at      TIMESTAMP WITH TIME ZONE
+                        NOT NULL DEFAULT CURRENT_TIMESTAMP,
+
+        -- is this user active?
+        is_active       BOOLEAN NOT NULL DEFAULT false,
+
+        -- role of this user
+        role_id         INTEGER NOT NULL DEFAULT 0
+                        REFERENCES roles(role_id),
+
+        -- display name (not the same as their login)
+        user_name       TEXT UNIQUE NOT NULL
+                        CHECK (LENGTH(user_name) > 0)
+      )
+    }, %{
+      INSERT INTO users(user_id, is_active, role_id, user_name) VALUES
+        (0, 1, (SELECT role_id FROM roles WHERE role_name = 'guest'), 'Guest'),
+        (1, 1, (SELECT role_id FROM roles WHERE role_name = 'admin'), 'Admin')
+    }],
+  }, {
+    id: "8-user-logins",
+    sql: [%{
+      CREATE TABLE user_logins (
+        user_id         INTEGER UNIQUE NOT NULL
+                        REFERENCES users(user_id),
+
+        -- email address of user
+        email           TEXT UNIQUE NOT NULL
+                        CHECK (email LIKE '%_@_%'),
+
+        -- bcrypt hash of password
+        pass_hash       TEXT NOT NULL
+      )
+    }, %{
+      CREATE INDEX in_user_logins_email ON user_logins(email)
+    }],
   }]
 end