--- a/sat/memory/sqla_mapping.py	Fri Jun 17 14:15:23 2022 +0200
+++ b/sat/memory/sqla_mapping.py	Fri Jun 17 14:15:23 2022 +0200
@@ -16,19 +16,32 @@
 # You should have received a copy of the GNU Affero General Public License
 # along with this program.  If not, see <http://www.gnu.org/licenses/>.
 
-import pickle
-import json
+from typing import Dict, Any
 from datetime import datetime
+import enum
+import json
+import pickle
 import time
-import enum
+
 from sqlalchemy import (
-    MetaData, Column, Integer, Text, Float, Boolean, DateTime, Enum, JSON, ForeignKey,
-    UniqueConstraint, Index, DDL, event
+    Boolean,
+    Column,
+    DDL,
+    DateTime,
+    Enum,
+    Float,
+    ForeignKey,
+    Index,
+    Integer,
+    JSON,
+    MetaData,
+    Text,
+    UniqueConstraint,
+    event,
 )
-
 from sqlalchemy.orm import declarative_base, relationship
+from sqlalchemy.sql.functions import now
 from sqlalchemy.types import TypeDecorator
-from sqlalchemy.sql.functions import now
 from twisted.words.protocols.jabber import jid
 from wokkel import generic
 
@@ -46,7 +59,7 @@
 )
 # keys which are in message data extra but not stored in extra field this is
 # because those values are stored in separate fields
-NOT_IN_EXTRA = ('stanza_id', 'received_timestamp', 'update_uid')
+NOT_IN_EXTRA = ('origin_id', 'stanza_id', 'received_timestamp', 'update_uid')
 
 
 class SyncState(enum.Enum):
@@ -184,6 +197,7 @@
     __tablename__ = "history"
     __table_args__ = (
         UniqueConstraint("profile_id", "stanza_id", "source", "dest"),
+        UniqueConstraint("profile_id", "origin_id", "source", name="uq_origin_id"),
         Index("history__profile_id_timestamp", "profile_id", "timestamp"),
         Index(
             "history__profile_id_received_timestamp", "profile_id", "received_timestamp"
@@ -191,6 +205,7 @@
     )
 
     uid = Column(Text, primary_key=True)
+    origin_id = Column(Text)
     stanza_id = Column(Text)
     update_uid = Column(Text)
     profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"))
@@ -259,6 +274,8 @@
 
     def serialise(self):
         extra = self.extra
+        if self.origin_id is not None:
+            extra["origin_id"] = self.origin_id
         if self.stanza_id is not None:
             extra["stanza_id"] = self.stanza_id
         if self.update_uid is not None:
@@ -319,10 +336,18 @@
         Integer,
         primary_key=True,
     )
-    history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"))
-    message = Column(Text)
+    history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"), nullable=False)
+    message = Column(Text, nullable=False)
     language = Column(Text)
 
+    def serialise(self) -> Dict[str, Any]:
+        s = {}
+        if self.message:
+            s["message"] = str(self.message)
+        if self.language:
+            s["language"] = str(self.language)
+        return s
+
     def __repr__(self):
         lang_str = f"[{self.language}]" if self.language else ""
         msg = f"{self.message[:20]}…" if len(self.message)>20 else self.message
@@ -340,10 +365,18 @@
         Integer,
         primary_key=True,
     )
-    history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"))
-    subject = Column(Text)
+    history_uid = Column(ForeignKey("history.uid", ondelete="CASCADE"), nullable=False)
+    subject = Column(Text, nullable=False)
     language = Column(Text)
 
+    def serialise(self) -> Dict[str, Any]:
+        s = {}
+        if self.subject:
+            s["subject"] = str(self.subject)
+        if self.language:
+            s["language"] = str(self.language)
+        return s
+
     def __repr__(self):
         lang_str = f"[{self.language}]" if self.language else ""
         msg = f"{self.subject[:20]}…" if len(self.subject)>20 else self.subject