From abbbc9b1fbaae6a9513033c4ed8baca913c357b2 Mon Sep 17 00:00:00 2001
From: Dave Arter <davea@mysociety.org>
Date: Wed, 1 May 2019 09:41:49 +0100
Subject: Add user roles tables

---
 db/schema.sql | 16 ++++++++++++++++
 1 file changed, 16 insertions(+)

(limited to 'db/schema.sql')

diff --git a/db/schema.sql b/db/schema.sql
index 98005028c..93d73ab00 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -73,6 +73,22 @@ create unique index body_areas_body_id_area_id_idx on body_areas(body_id, area_i
 ALTER TABLE users ADD CONSTRAINT users_from_body_fkey
     FOREIGN KEY (from_body) REFERENCES body(id);
 
+-- roles table
+create table roles (
+    id              serial  not null primary key,
+    body_id         integer not null references body(id) ON DELETE CASCADE,
+    name            text,
+    permissions     text ARRAY,
+    unique(body_id, name)
+);
+
+-- Record which role(s) each user holds
+create table user_roles (
+    id              serial  not null primary key,
+    role_id         integer not null references roles(id) ON DELETE CASCADE,
+    user_id         integer not null references users(id) ON DELETE CASCADE
+);
+
 -- The contact for a category within a particular body
 create table contacts (
     id serial primary key,
-- 
cgit v1.2.3

