Merge branch 'private_messaging' into dev

22 files changed, 681 insertions, 2 deletions

diff --git a/server/migrations/2020-01-21-001001_create_private_message/down.sql b/server/migrations/2020-01-21-001001_create_private_message/down.sql
new file mode 100644
index 00000000..0d951e3e
--- /dev/null
+++ b/server/migrations/2020-01-21-001001_create_private_message/down.sql
@@ -0,0 +1,34 @@
+-- Drop the triggers
+drop trigger refresh_private_message on private_message;
+drop function refresh_private_message();
+
+-- Drop the view and table
+drop view private_message_view cascade;
+drop table private_message;
+
+-- Rebuild the old views
+drop view user_view cascade;
+create view user_view as 
+select 
+u.id,
+u.name,
+u.avatar,
+u.email,
+u.fedi_name,
+u.admin,
+u.banned,
+u.show_avatars,
+u.send_notifications_to_email,
+u.published,
+(select count(*) from post p where p.creator_id = u.id) as number_of_posts,
+(select coalesce(sum(score), 0) from post p, post_like pl where u.id = p.creator_id and p.id = pl.post_id) as post_score,
+(select count(*) from comment c where c.creator_id = u.id) as number_of_comments,
+(select coalesce(sum(score), 0) from comment c, comment_like cl where u.id = c.creator_id and c.id = cl.comment_id) as comment_score
+from user_ u;
+
+create materialized view user_mview as select * from user_view;
+
+create unique index idx_user_mview_id on user_mview (id);
+
+-- Drop the columns
+alter table user_ drop column matrix_user_id;
diff --git a/server/migrations/2020-01-21-001001_create_private_message/up.sql b/server/migrations/2020-01-21-001001_create_private_message/up.sql
new file mode 100644
index 00000000..48e16dd8
--- /dev/null
+++ b/server/migrations/2020-01-21-001001_create_private_message/up.sql
@@ -0,0 +1,90 @@
+-- Creating private message
+create table private_message (
+  id serial primary key,
+  creator_id int references user_ on update cascade on delete cascade not null,
+  recipient_id int references user_ on update cascade on delete cascade not null,
+  content text not null,
+  deleted boolean default false not null,
+  read boolean default false not null,
+  published timestamp not null default now(),
+  updated timestamp
+);
+
+-- Create the view and materialized view which has the avatar and creator name
+create view private_message_view as 
+select        
+pm.*,
+u.name as creator_name,
+u.avatar as creator_avatar,
+u2.name as recipient_name,
+u2.avatar as recipient_avatar
+from private_message pm
+inner join user_ u on u.id = pm.creator_id
+inner join user_ u2 on u2.id = pm.recipient_id;
+
+create materialized view private_message_mview as select * from private_message_view;
+
+create unique index idx_private_message_mview_id on private_message_mview (id);
+
+-- Create the triggers
+create or replace function refresh_private_message()
+returns trigger language plpgsql
+as $$
+begin
+  refresh materialized view concurrently private_message_mview;
+  return null;
+end $$;
+
+create trigger refresh_private_message
+after insert or update or delete or truncate
+on private_message
+for each statement
+execute procedure refresh_private_message();
+
+-- Update user to include matrix id
+alter table user_ add column matrix_user_id text unique;
+
+drop view user_view cascade;
+create view user_view as 
+select 
+u.id,
+u.name,
+u.avatar,
+u.email,
+u.matrix_user_id,
+u.fedi_name,
+u.admin,
+u.banned,
+u.show_avatars,
+u.send_notifications_to_email,
+u.published,
+(select count(*) from post p where p.creator_id = u.id) as number_of_posts,
+(select coalesce(sum(score), 0) from post p, post_like pl where u.id = p.creator_id and p.id = pl.post_id) as post_score,
+(select count(*) from comment c where c.creator_id = u.id) as number_of_comments,
+(select coalesce(sum(score), 0) from comment c, comment_like cl where u.id = c.creator_id and c.id = cl.comment_id) as comment_score
+from user_ u;
+
+create materialized view user_mview as select * from user_view;
+
+create unique index idx_user_mview_id on user_mview (id);
+
+-- This is what a group pm table would look like
+-- Not going to do it now because of the complications
+-- 
+-- create table private_message (
+--   id serial primary key,
+--   creator_id int references user_ on update cascade on delete cascade not null,
+--   content text not null,
+--   deleted boolean default false not null,
+--   published timestamp not null default now(),
+--   updated timestamp
+-- );
+-- 
+-- create table private_message_recipient (
+--   id serial primary key,
+--   private_message_id int references private_message on update cascade on delete cascade not null,
+--   recipient_id int references user_ on update cascade on delete cascade not null,
+--   read boolean default false not null,
+--   published timestamp not null default now(),
+--   unique(private_message_id, recipient_id)
+-- )
diff --git a/server/src/api/comment.rs b/server/src/api/comment.rs
index 61cc9506..382afb5b 100644
--- a/server/src/api/comment.rs
+++ b/server/src/api/comment.rs
@@ -7,7 +7,7 @@ use diesel::PgConnection;
 pub struct CreateComment {
   content: String,
   parent_id: Option<i32>,
-  edit_id: Option<i32>,
+  edit_id: Option<i32>, // TODO this isn't used
   pub post_id: i32,
   auth: String,
 }
@@ -15,7 +15,7 @@ pub struct CreateComment {
 #[derive(Serialize, Deserialize)]
 pub struct EditComment {
   content: String,
-  parent_id: Option<i32>,
+  parent_id: Option<i32>, // TODO why are the parent_id, creator_id, post_id, etc fields required? They aren't going to change
   edit_id: i32,
   creator_id: i32,
   pub post_id: i32,
diff --git a/server/src/api/mod.rs b/server/src/api/mod.rs
index e3580447..3b2466ac 100644
--- a/server/src/api/mod.rs
+++ b/server/src/api/mod.rs
@@ -8,6 +8,8 @@ use crate::db::moderator_views::*;
 use crate::db::password_reset_request::*;
 use crate::db::post::*;
 use crate::db::post_view::*;
+use crate::db::private_message::*;
+use crate::db::private_message_view::*;
 use crate::db::site::*;
 use crate::db::site_view::*;
 use crate::db::user::*;
@@ -67,6 +69,9 @@ pub enum UserOperation {
   DeleteAccount,
   PasswordReset,
   PasswordChange,
+  CreatePrivateMessage,
+  EditPrivateMessage,
+  GetPrivateMessages,
 }
 
 #[derive(Fail, Debug)]
diff --git a/server/src/api/user.rs b/server/src/api/user.rs
index ac700aca..046da6fb 100644
--- a/server/src/api/user.rs
+++ b/server/src/api/user.rs
@@ -30,6 +30,7 @@ pub struct SaveUserSettings {
   lang: String,
   avatar: Option<String>,
   email: Option<String>,
+  matrix_user_id: Option<String>,
   new_password: Option<String>,
   new_password_verify: Option<String>,
   old_password: Option<String>,
@@ -167,6 +168,42 @@ pub struct PasswordChange {
   password_verify: String,
 }
 
+#[derive(Serialize, Deserialize)]
+pub struct CreatePrivateMessage {
+  content: String,
+  recipient_id: i32,
+  auth: String,
+}
+
+#[derive(Serialize, Deserialize)]
+pub struct EditPrivateMessage {
+  edit_id: i32,
+  content: Option<String>,
+  deleted: Option<bool>,
+  read: Option<bool>,
+  auth: String,
+}
+
+#[derive(Serialize, Deserialize)]
+pub struct GetPrivateMessages {
+  unread_only: bool,
+  page: Option<i64>,
+  limit: Option<i64>,
+  auth: String,
+}
+
+#[derive(Serialize, Deserialize, Clone)]
+pub struct PrivateMessagesResponse {
+  op: String,
+  messages: Vec<PrivateMessageView>,
+}
+
+#[derive(Serialize, Deserialize, Clone)]
+pub struct PrivateMessageResponse {
+  op: String,
+  message: PrivateMessageView,
+}
+
 impl Perform<LoginResponse> for Oper<Login> {
   fn perform(&self, conn: &PgConnection) -> Result<LoginResponse, Error> {
     let data: &Login = &self.data;
@@ -221,6 +258,7 @@ impl Perform<LoginResponse> for Oper<Register> {
       name: data.username.to_owned(),
       fedi_name: Settings::get().hostname.to_owned(),
       email: data.email.to_owned(),
+      matrix_user_id: None,
       avatar: None,
       password_encrypted: data.password.to_owned(),
       preferred_username: None,
@@ -357,6 +395,7 @@ impl Perform<LoginResponse> for Oper<SaveUserSettings> {
       name: read_user.name,
       fedi_name: read_user.fedi_name,
       email,
+      matrix_user_id: data.matrix_user_id.to_owned(),
       avatar: data.avatar.to_owned(),
       password_encrypted,
       preferred_username: read_user.preferred_username,
@@ -504,10 +543,12 @@ impl Perform<AddAdminResponse> for Oper<AddAdmin> {
 
     let read_user = User_::read(&conn, data.user_id)?;
 
+    // TODO make addadmin easier
     let user_form = UserForm {
       name: read_user.name,
       fedi_name: read_user.fedi_name,
       email: read_user.email,
+      matrix_user_id: read_user.matrix_user_id,
       avatar: read_user.avatar,
       password_encrypted: read_user.password_encrypted,
       preferred_username: read_user.preferred_username,
@@ -568,10 +609,12 @@ impl Perform<BanUserResponse> for Oper<BanUser> {
 
     let read_user = User_::read(&conn, data.user_id)?;
 
+    // TODO make bans and addadmins easier
     let user_form = UserForm {
       name: read_user.name,
       fedi_name: read_user.fedi_name,
       email: read_user.email,
+      matrix_user_id: read_user.matrix_user_id,
       avatar: read_user.avatar,
       password_encrypted: read_user.password_encrypted,
       preferred_username: read_user.preferred_username,
@@ -762,6 +805,30 @@ impl Perform<GetRepliesResponse> for Oper<MarkAllAsRead> {
         };
     }
 
+    // messages
+    let messages = PrivateMessageQueryBuilder::create(&conn, user_id)
+      .page(1)
+      .limit(999)
+      .unread_only(true)
+      .list()?;
+
+    for message in &messages {
+      let private_message_form = PrivateMessageForm {
+        content: None,
+        creator_id: message.to_owned().creator_id,
+        recipient_id: message.to_owned().recipient_id,
+        deleted: None,
+        read: Some(true),
+        updated: None,
+      };
+
+      let _updated_message = match PrivateMessage::update(&conn, message.id, &private_message_form)
+      {
+        Ok(message) => message,
+        Err(_e) => return Err(APIError::err(&self.op, "couldnt_update_private_message").into()),
+      };
+    }
+
     Ok(GetRepliesResponse {
       op: self.op.to_string(),
       replies: vec![],
@@ -905,3 +972,150 @@ impl Perform<LoginResponse> for Oper<PasswordChange> {
     })
   }
 }
+
+impl Perform<PrivateMessageResponse> for Oper<CreatePrivateMessage> {
+  fn perform(&self, conn: &PgConnection) -> Result<PrivateMessageResponse, Error> {
+    let data: &CreatePrivateMessage = &self.data;
+
+    let claims = match Claims::decode(&data.auth) {
+      Ok(claims) => claims.claims,
+      Err(_e) => return Err(APIError::err(&self.op, "not_logged_in").into()),
+    };
+
+    let user_id = claims.id;
+
+    let hostname = &format!("https://{}", Settings::get().hostname);
+
+    // Check for a site ban
+    if UserView::read(&conn, user_id)?.banned {
+      return Err(APIError::err(&self.op, "site_ban").into());
+    }
+
+    let content_slurs_removed = remove_slurs(&data.content.to_owned());
+
+    let private_message_form = PrivateMessageForm {
+      content: Some(content_slurs_removed.to_owned()),
+      creator_id: user_id,
+      recipient_id: data.recipient_id,
+      deleted: None,
+      read: None,
+      updated: None,
+    };
+
+    let inserted_private_message = match PrivateMessage::create(&conn, &private_message_form) {
+      Ok(private_message) => private_message,
+      Err(_e) => {
+        return Err(APIError::err(&self.op, "couldnt_create_private_message").into());
+      }
+    };
+
+    // Send notifications to the recipient
+    let recipient_user = User_::read(&conn, data.recipient_id)?;
+    if recipient_user.send_notifications_to_email {
+      if let Some(email) = recipient_user.email {
+        let subject = &format!(
+          "{} - Private Message from {}",
+          Settings::get().hostname,
+          claims.username
+        );
+        let html = &format!(
+          "<h1>Private Message</h1><br><div>{} - {}</div><br><a href={}/inbox>inbox</a>",
+          claims.username, &content_slurs_removed, hostname
+        );
+        match send_email(subject, &email, &recipient_user.name, html) {
+          Ok(_o) => _o,
+          Err(e) => eprintln!("{}", e),
+        };
+      }
+    }
+
+    let private_message_view = PrivateMessageView::read(&conn, inserted_private_message.id)?;
+
+    Ok(PrivateMessageResponse {
+      op: self.op.to_string(),
+      message: private_message_view,
+    })
+  }
+}
+
+impl Perform<PrivateMessageResponse> for Oper<EditPrivateMessage> {
+  fn perform(&self, conn: &PgConnection) -> Result<PrivateMessageResponse, Error> {
+    let data: &EditPrivateMessage = &self.data;
+
+    let claims = match Claims::decode(&data.auth) {
+      Ok(claims) => claims.claims,
+      Err(_e) => return Err(APIError::err(&self.op, "not_logged_in").into()),
+    };
+
+    let user_id = claims.id;
+
+    let orig_private_message = PrivateMessage::read(&conn, data.edit_id)?;
+
+    // Check for a site ban
+    if UserView::read(&conn, user_id)?.banned {
+      return Err(APIError::err(&self.op, "site_ban").into());
+    }
+
+    // Check to make sure they are the creator (or the recipient marking as read
+    if !(data.read.is_some() && orig_private_message.recipient_id.eq(&user_id)
+      || orig_private_message.creator_id.eq(&user_id))
+    {
+      return Err(APIError::err(&self.op, "no_private_message_edit_allowed").into());
+    }
+
+    let content_slurs_removed = match &data.content {
+      Some(content) => Some(remove_slurs(content)),
+      None => None,
+    };
+
+    let private_message_form = PrivateMessageForm {
+      content: content_slurs_removed,
+      creator_id: orig_private_message.creator_id,
+      recipient_id: orig_private_message.recipient_id,
+      deleted: data.deleted.to_owned(),
+      read: data.read.to_owned(),
+      updated: if data.read.is_some() {
+        orig_private_message.updated
+      } else {
+        Some(naive_now())
+      },
+    };
+
+    let _updated_private_message =
+      match PrivateMessage::update(&conn, data.edit_id, &private_message_form) {
+        Ok(private_message) => private_message,
+        Err(_e) => return Err(APIError::err(&self.op, "couldnt_update_private_message").into()),
+      };
+
+    let private_message_view = PrivateMessageView::read(&conn, data.edit_id)?;
+
+    Ok(PrivateMessageResponse {
+      op: self.op.to_string(),
+      message: private_message_view,
+    })
+  }
+}
+
+impl Perform<PrivateMessagesResponse> for Oper<GetPrivateMessages> {
+  fn perform(&self, conn: &PgConnection) -> Result<PrivateMessagesResponse, Error> {
+    let data: &GetPrivateMessages = &self.data;
+
+    let claims = match Claims::decode(&data.auth) {
+      Ok(claims) => claims.claims,
+      Err(_e) => return Err(APIError::err(&self.op, "not_logged_in").into()),
+    };
+
+    let user_id = claims.id;
+
+    let messages = PrivateMessageQueryBuilder::create(&conn, user_id)
+      .page(data.page)
+      .limit(data.limit)
+      .unread_only(data.unread_only)
+      .list()?;
+
+    Ok(PrivateMessagesResponse {
+      op: self.op.to_string(),
+      messages,
+    })
+  }
+}
diff --git a/server/src/apub/mod.rs b/server/src/apub/mod.rs
index 2d2e5ad3..c5a0b2f0 100644
--- a/server/src/apub/mod.rs
+++ b/server/src/apub/mod.rs
@@ -22,6 +22,7 @@ mod tests {
       preferred_username: None,
       password_encrypted: "here".into(),
       email: None,
+      matrix_user_id: None,
       avatar: None,
       published: naive_now(),
       admin: false,
diff --git a/server/src/db/comment.rs b/server/src/db/comment.rs
index a9c7d81d..efba07a5 100644
--- a/server/src/db/comment.rs
+++ b/server/src/db/comment.rs
@@ -174,6 +174,7 @@ mod tests {
       preferred_username: None,
       password_encrypted: "nope".into(),
       email: None,
+      matrix_user_id: None,
       avatar: None,
       admin: false,
       banned: false,
diff --git a/server/src/db/comment_view.rs b/server/src/db/comment_view.rs
index 3b06e8e3..d4a65c9a 100644
--- a/server/src/db/comment_view.rs
+++ b/server/src/db/comment_view.rs
@@ -398,6 +398,7 @@ mod tests {
       preferred_username: None,
       password_encrypted: "nope".into(),
       email: None,
+      matrix_user_id: None,
       avatar: None,
       admin: false,
       banned: false,
diff --git a/server/src/db/community.rs b/server/src/db/community.rs
index b482ca4a..63500963 100644
--- a/server/src/db/community.rs
+++ b/server/src/db/community.rs
@@ -220,6 +220,7 @@ mod tests {
       preferred_username: None,
       password_encrypted: "nope".into(),
       email: None,
+      matrix_user_id: None,
       avatar: None,
       admin: false,
       banned: false,
diff --git a/server/src/db/mod.rs b/server/src/db/mod.rs
index fef3ffce..dacdb6f6 100644
--- a/server/src/db/mod.rs
+++ b/server/src/db/mod.rs
@@ -15,6 +15,8 @@ pub mod moderator_views;
 pub mod password_reset_request;
 pub mod post;
 pub mod post_view;
+pub mod private_message;
+pub mod private_message_view;
 pub mod site;
 pub mod site_view;
 pub mod user;
diff --git a/server/src/db/moderator.rs b/server/src/db/moderator.rs
index 3c6233cb..4fd532af 100644
--- a/server/src/db/moderator.rs
+++ b/server/src/db/moderator.rs
@@ -442,6 +442,7 @@ mod tests {
       preferred_username: None,
       password_encrypted: "nope".into(),
       email: None,
+      matrix_user_id: None,
       avatar: None,
       admin: false,
       banned: false,
@@ -463,6 +464,7 @@ mod tests {
       preferred_username: None,
       password_encrypted: "nope".into(),
       email: None,
+      matrix_user_id: None,
       avatar: None,
       admin: false,
       banned: false,
diff --git a/server/src/db/password_reset_request.rs b/server/src/db/password_reset_request.rs
index fa060a59..6951fd39 100644
--- a/server/src/db/password_reset_request.rs
+++ b/server/src/db/password_reset_request.rs
@@ -92,6 +92,7 @@ mod tests {
       preferred_username: None,
       password_encrypted: "nope".into(),
       email: None,
+      matrix_user_id: None,
       avatar: None,
       admin: false,
       banned: false,
diff --git a/server/src/db/post.rs b/server/src/db/post.rs
index d3fba4da..9e7a4341 100644
--- a/server/src/db/post.rs
+++ b/server/src/db/post.rs
@@ -187,6 +187,7 @@ mod tests {
       preferred_username: None,
       password_encrypted: "nope".into(),
       email: None,
+      matrix_user_id: None,
       avatar: None,
       admin: false,
       banned: false,
diff --git a/server/src/db/post_view.rs b/server/src/db/post_view.rs
index f6cc274f..c80d1696 100644
--- a/server/src/db/post_view.rs
+++ b/server/src/db/post_view.rs
@@ -339,6 +339,7 @@ mod tests {
       preferred_username: None,
       password_encrypted: "nope".into(),
       email: None,
+      matrix_user_id: None,
       avatar: None,
       updated: None,
       admin: false,
diff --git a/server/src/db/private_message.rs b/server/src/db/private_message.rs
new file mode 100644
index 00000000..cc073b59
--- /dev/null
+++ b/server/src/db/private_message.rs
@@ -0,0 +1,144 @@
+use super::*;
+use crate::schema::private_message;
+
+#[derive(Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize)]
+#[table_name = "private_message"]
+pub struct PrivateMessage {
+  pub id: i32,
+  pub creator_id: i32,
+  pub recipient_id: i32,
+  pub content: String,
+  pub deleted: bool,
+  pub read: bool,
+  pub published: chrono::NaiveDateTime,
+  pub updated: Option<chrono::NaiveDateTime>,
+}
+
+#[derive(Insertable, AsChangeset, Clone)]
+#[table_name = "private_message"]
+pub struct PrivateMessageForm {
+  pub creator_id: i32,
+  pub recipient_id: i32,
+  pub content: Option<String>,
+  pub deleted: Option<bool>,
+  pub read: Option<bool>,
+  pub updated: Option<chrono::NaiveDateTime>,
+}
+
+impl Crud<PrivateMessageForm> for PrivateMessage {
+  fn read(conn: &PgConnection, private_message_id: i32) -> Result<Self, Error> {
+    use crate::schema::private_message::dsl::*;
+    private_message.find(private_message_id).first::<Self>(conn)
+  }
+
+  fn delete(conn: &PgConnection, private_message_id: i32) -> Result<usize, Error> {
+    use crate::schema::private_message::dsl::*;
+    diesel::delete(private_message.find(private_message_id)).execute(conn)
+  }
+
+  fn create(conn: &PgConnection, private_message_form: &PrivateMessageForm) -> Result<Self, Error> {
+    use crate::schema::private_message::dsl::*;
+    insert_into(private_message)
+      .values(private_message_form)
+      .get_result::<Self>(conn)
+  }
+
+  fn update(
+    conn: &PgConnection,
+    private_message_id: i32,
+    private_message_form: &PrivateMessageForm,
+  ) -> Result<Self, Error> {
+    use crate::schema::private_message::dsl::*;
+    diesel::update(private_message.find(private_message_id))
+      .set(private_message_form)
+      .get_result::<Self>(conn)
+  }
+}
+
+#[cfg(test)]
+mod tests {
+  use super::super::user::*;
+  use super::*;
+  #[test]
+  fn test_crud() {
+    let conn = establish_unpooled_connection();
+
+    let creator_form = UserForm {
+      name: "creator_pm".into(),
+      fedi_name: "rrf".into(),
+      preferred_username: None,
+      password_encrypted: "nope".into(),
+      email: None,
+      matrix_user_id: None,
+      avatar: None,
+      admin: false,
+      banned: false,
+      updated: None,
+      show_nsfw: false,
+      theme: "darkly".into(),
+      default_sort_type: SortType::Hot as i16,
+      default_listing_type: ListingType::Subscribed as i16,
+      lang: "browser".into(),
+      show_avatars: true,
+      send_notifications_to_email: false,
+    };
+
+    let inserted_creator = User_::create(&conn, &creator_form).unwrap();
+
+    let recipient_form = UserForm {
+      name: "recipient_pm".into(),
+      fedi_name: "rrf".into(),
+      preferred_username: None,
+      password_encrypted: "nope".into(),
+      email: None,
+      matrix_user_id: None,
+      avatar: None,
+      admin: false,
+      banned: false,
+      updated: None,
+      show_nsfw: false,
+      theme: "darkly".into(),
+      default_sort_type: SortType::Hot as i16,
+      default_listing_type: ListingType::Subscribed as i16,
+      lang: "browser".into(),
+      show_avatars: true,
+      send_notifications_to_email: false,
+    };
+
+    let inserted_recipient = User_::create(&conn, &recipient_form).unwrap();
+
+    let private_message_form = PrivateMessageForm {
+      content: Some("A test private message".into()),
+      creator_id: inserted_creator.id,
+      recipient_id: inserted_recipient.id,
+      deleted: None,
+      read: None,
+      updated: None,
+    };
+
+    let inserted_private_message = PrivateMessage::create(&conn, &private_message_form).unwrap();
+
+    let expected_private_message = PrivateMessage {
+      id: inserted_private_message.id,
+      content: "A test private message".into(),
+      creator_id: inserted_creator.id,
+      recipient_id: inserted_recipient.id,
+      deleted: false,
+      read: false,
+      updated: None,
+      published: inserted_private_message.published,
+    };
+
+    let read_private_message = PrivateMessage::read(&conn, inserted_private_message.id).unwrap();
+    let updated_private_message =
+      PrivateMessage::update(&conn, inserted_private_message.id, &private_message_form).unwrap();
+    let num_deleted = PrivateMessage::delete(&conn, inserted_private_message.id).unwrap();
+    User_::delete(&conn, inserted_creator.id).unwrap();
+    User_::delete(&conn, inserted_recipient.id).unwrap();
+
+    assert_eq!(expected_private_message, read_private_message);
+    assert_eq!(expected_private_message, updated_private_message);
+    assert_eq!(expected_private_message, inserted_private_message);
+    assert_eq!(1, num_deleted);
+  }
+}
diff --git a/server/src/db/private_message_view.rs b/server/src/db/private_message_view.rs
new file mode 100644
index 00000000..59a573f4
--- /dev/null
+++ b/server/src/db/private_message_view.rs
@@ -0,0 +1,140 @@
+use super::*;
+use diesel::pg::Pg;
+
+// The faked schema since diesel doesn't do views
+table! {
+  private_message_view (id) {
+    id -> Int4,
+    creator_id -> Int4,
+    recipient_id -> Int4,
+    content -> Text,
+    deleted -> Bool,
+    read -> Bool,
+    published -> Timestamp,
+    updated -> Nullable<Timestamp>,
+    creator_name -> Varchar,
+    creator_avatar -> Nullable<Text>,
+    recipient_name -> Varchar,
+    recipient_avatar -> Nullable<Text>,
+  }
+}
+
+table! {
+  private_message_mview (id) {
+    id -> Int4,
+    creator_id -> Int4,
+    recipient_id -> Int4,
+    content -> Text,
+    deleted -> Bool,
+    read -> Bool,
+    published -> Timestamp,
+    updated -> Nullable<Timestamp>,
+    creator_name -> Varchar,
+    creator_avatar -> Nullable<Text>,
+    recipient_name -> Varchar,
+    recipient_avatar -> Nullable<Text>,
+  }
+}
+
+#[derive(
+  Queryable, Identifiable, PartialEq, Debug, Serialize, Deserialize, QueryableByName, Clone,
+)]
+#[table_name = "private_message_view"]
+pub struct PrivateMessageView {
+  pub id: i32,
+  pub creator_id: i32,
+  pub recipient_id: i32,
+  pub content: String,
+  pub deleted: bool,
+  pub read: bool,
+  pub published: chrono::NaiveDateTime,
+  pub updated: Option<chrono::NaiveDateTime>,
+  pub creator_name: String,
+  pub creator_avatar: Option<String>,
+  pub recipient_name: String,
+  pub recipient_avatar: Option<String>,
+}
+
+pub struct PrivateMessageQueryBuilder<'a> {
+  conn: &'a PgConnection,
+  query: super::private_message_view::private_message_mview::BoxedQuery<'a, Pg>,
+  for_recipient_id: i32,
+  unread_only: bool,
+  page: Option<i64>,
+  limit: Option<i64>,
+}
+
+impl<'a> PrivateMessageQueryBuilder<'a> {
+  pub fn create(conn: &'a PgConnection, for_recipient_id: i32) -> Self {
+    use super::private_message_view::private_message_mview::dsl::*;
+
+    let query = private_message_mview.into_boxed();
+
+    PrivateMessageQueryBuilder {
+      conn,
+      query,
+      for_recipient_id,
+      unread_only: false,
+      page: None,
+      limit: None,
+    }
+  }
+
+  pub fn unread_only(mut self, unread_only: bool) -> Self {
+    self.unread_only = unread_only;
+    self
+  }
+
+  pub fn page<T: MaybeOptional<i64>>(mut self, page: T) -> Self {
+    self.page = page.get_optional();
+    self
+  }
+
+  pub fn limit<T: MaybeOptional<i64>>(mut self, limit: T) -> Self {
+    self.limit = limit.get_optional();
+    self
+  }
+
+  pub fn list(self) -> Result<Vec<PrivateMessageView>, Error> {
+    use super::private_message_view::private_message_mview::dsl::*;
+
+    let mut query = self.query;
+
+    // If its unread, I only want the ones to me
+    if self.unread_only {
+      query = query
+        .filter(read.eq(false))
+        .filter(recipient_id.eq(self.for_recipient_id));
+    }
+    // Otherwise, I want the ALL view to show both sent and received
+    else {
+      query = query.filter(
+        recipient_id
+          .eq(self.for_recipient_id)
+          .or(creator_id.eq(self.for_recipient_id)),
+      )
+    }
+
+    let (limit, offset) = limit_and_offset(self.page, self.limit);
+
+    query
+      .limit(limit)
+      .offset(offset)
+      .order_by(published.desc())
+      .load::<PrivateMessageView>(self.conn)
+  }
+}
+
+impl PrivateMessageView {
+  pub fn read(conn: &PgConnection, from_private_message_id: i32) -> Result<Self, Error> {
+    use super::private_message_view::private_message_view::dsl::*;
+
+    let mut query = private_message_view.into_boxed();
+
+    query = query
+      .filter(id.eq(from_private_message_id))
+      .order_by(published.desc());
+
+    query.first::<Self>(conn)
+  }
+}
diff --git a/server/src/db/user.rs b/server/src/db/user.rs
index 71b63d74..b36c07be 100644
--- a/server/src/db/user.rs
+++ b/server/src/db/user.rs
@@ -26,6 +26,7 @@ pub struct User_ {
   pub lang: String,
   pub show_avatars: bool,
   pub send_notifications_to_email: bool,
+  pub matrix_user_id: Option<String>,
 }<