From 382a782c16a998776176de37037dd029fab4e3fe Mon Sep 17 00:00:00 2001
From: Hakim Cassimally <hakim@mysociety.org>
Date: Tue, 27 May 2014 16:08:50 +0000
Subject: Report moderation

    - redaction marked with [...]
    - of report and comments
    - stores original data
    - uses a single form, on the report/_main view
    - requires additional permissions (user_body_permissions)
    - Hide report functionality
    - Moderation notification/contact form
    - Moderation writes to admin_log
---
 db/schema.sql                 | 32 +++++++++++++++++++++++++++++++-
 db/schema_0032-moderation.sql | 36 ++++++++++++++++++++++++++++++++++++
 2 files changed, 67 insertions(+), 1 deletion(-)
 create mode 100644 db/schema_0032-moderation.sql

(limited to 'db')

diff --git a/db/schema.sql b/db/schema.sql
index 6ddb7bae6..5bac02bce 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -450,5 +450,35 @@ create table admin_log (
     ),
     object_id integer not null,
     action text not null,
-    whenedited timestamp not null default ms_current_timestamp()
+    whenedited timestamp not null default ms_current_timestamp(),
+    user_id int references users(id) null,
+    reason text not null default ''
 ); 
+
+create table moderation_original_data (
+    id serial not null primary key,
+
+    -- Problem details
+    problem_id int references problem(id) ON DELETE CASCADE not null,
+    comment_id int references comment(id) ON DELETE CASCADE unique,
+
+    title text null,
+    detail text null, -- or text for comment
+    photo bytea,
+    anonymous bool not null,
+
+    -- Metadata
+    created timestamp not null default ms_current_timestamp()
+);
+
+create table user_body_permissions (
+    id serial not null primary key,
+    user_id int references users(id) not null,
+    body_id int references body(id) not null,
+    permission_type text not null check(
+        permission_type='moderate' or
+        -- for future expansion --
+        permission_type='admin'
+    ),
+    unique(user_id, body_id, permission_type)
+);
diff --git a/db/schema_0032-moderation.sql b/db/schema_0032-moderation.sql
new file mode 100644
index 000000000..b3caded1e
--- /dev/null
+++ b/db/schema_0032-moderation.sql
@@ -0,0 +1,36 @@
+-- was created in previous versions of this branch
+DROP TABLE IF EXISTS moderation_log;
+
+alter table admin_log add column
+    user_id int references users(id) null;
+
+alter table admin_log add column
+    reason text not null default '';
+
+create table moderation_original_data (
+    id serial not null primary key,
+
+    -- Problem details
+    problem_id int references problem(id) ON DELETE CASCADE not null,
+    comment_id int references comment(id) ON DELETE CASCADE unique,
+
+    title text null,
+    detail text null, -- or text for comment
+    photo bytea,
+    anonymous bool not null,
+
+    -- Metadata
+    created timestamp not null default ms_current_timestamp()
+);
+
+create table user_body_permissions (
+    id serial not null primary key,
+    user_id int references users(id) not null,
+    body_id int references body(id) not null,
+    permission_type text not null check(
+        permission_type='moderate' or
+        -- for future expansion --
+        permission_type='admin'
+    ),
+    unique(user_id, body_id, permission_type)
+);
-- 
cgit v1.2.3