--- a/sat/memory/sqla_mapping.py	Thu Jul 29 21:30:32 2021 +0200
+++ b/sat/memory/sqla_mapping.py	Thu Jul 29 21:38:37 2021 +0200
@@ -18,14 +18,19 @@
 
 import pickle
 import json
+from datetime import datetime
+import time
+import enum
 from sqlalchemy import (
-    MetaData, Column, Integer, Text, Float, Enum, ForeignKey, UniqueConstraint, Index,
+    MetaData, Column, Integer, Text, Float, Boolean, DateTime, Enum, JSON, ForeignKey,
+    UniqueConstraint, Index
 )
 
 from sqlalchemy.orm import declarative_base, relationship
 from sqlalchemy.types import TypeDecorator
+from sqlalchemy.sql.functions import now
 from twisted.words.protocols.jabber import jid
-from datetime import datetime
+from wokkel import generic
 
 
 Base = declarative_base(
@@ -44,6 +49,17 @@
 NOT_IN_EXTRA = ('stanza_id', 'received_timestamp', 'update_uid')
 
 
+class SyncState(enum.Enum):
+    #: synchronisation is currently in progress
+    IN_PROGRESS = 1
+    #: synchronisation is done
+    COMPLETED = 2
+    #: something wrong happened during synchronisation, won't sync
+    ERROR = 3
+    #: synchronisation won't be done even if a syncing analyser match
+    NO_SYNC = 4
+
+
 class LegacyPickle(TypeDecorator):
     """Handle troubles with data pickled by former version of SàT
 
@@ -97,6 +113,21 @@
         return json.loads(value)
 
 
+class Xml(TypeDecorator):
+    impl = Text
+    cache_ok = True
+
+    def process_bind_param(self, value, dialect):
+        if value is None:
+            return None
+        return value.toXml()
+
+    def process_result_value(self, value, dialect):
+        if value is None:
+            return None
+        return generic.parseXml(value.encode())
+
+
 class JID(TypeDecorator):
     """Store twisted JID in text fields"""
     impl = Text
@@ -448,3 +479,62 @@
     profile_id = Column(ForeignKey("profiles.id", ondelete="CASCADE"))
 
     profile = relationship("Profile")
+
+
+class PubsubNode(Base):
+    __tablename__ = "pubsub_nodes"
+    __table_args__ = (
+        UniqueConstraint("profile_id", "service", "name"),
+    )
+
+    id = Column(Integer, primary_key=True)
+    profile_id = Column(
+        ForeignKey("profiles.id", ondelete="CASCADE")
+    )
+    service = Column(JID)
+    name = Column(Text, nullable=False)
+    subscribed = Column(
+        Boolean(create_constraint=True, name="subscribed_bool"), nullable=False
+    )
+    analyser = Column(Text)
+    sync_state = Column(
+        Enum(
+            SyncState,
+            name="sync_state",
+            create_constraint=True,
+        ),
+        nullable=True
+    )
+    sync_state_updated = Column(
+        Float,
+        nullable=False,
+        default=time.time()
+    )
+    type_ = Column(
+        Text, name="type", nullable=True
+    )
+    subtype = Column(
+        Text, nullable=True
+    )
+    extra = Column(JSON)
+
+    items = relationship("PubsubItem", back_populates="node", passive_deletes=True)
+
+    def __str__(self):
+        return f"Pubsub node {self.name!r} at {self.service}"
+
+
+class PubsubItem(Base):
+    __tablename__ = "pubsub_items"
+    __table_args__ = (
+        UniqueConstraint("node_id", "name"),
+    )
+    id = Column(Integer, primary_key=True)
+    node_id = Column(ForeignKey("pubsub_nodes.id", ondelete="CASCADE"), nullable=False)
+    name = Column(Text, nullable=False)
+    data = Column(Xml, nullable=False)
+    created = Column(DateTime, nullable=False, server_default=now())
+    updated = Column(DateTime, nullable=False, server_default=now(), onupdate=now())
+    parsed = Column(JSON)
+
+    node = relationship("PubsubNode", back_populates="items")