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

2 files changed, 67 insertions, 1 deletions

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)
+);