00-latest-revision.sql 6.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206
  1. -- v0 -> v56 (compatible with v45+): Latest revision
  2. CREATE TABLE "user" (
  3. mxid TEXT PRIMARY KEY,
  4. username TEXT UNIQUE,
  5. agent SMALLINT,
  6. device SMALLINT,
  7. management_room TEXT,
  8. space_room TEXT,
  9. phone_last_seen BIGINT,
  10. phone_last_pinged BIGINT,
  11. timezone TEXT
  12. );
  13. CREATE TABLE portal (
  14. jid TEXT,
  15. receiver TEXT,
  16. mxid TEXT UNIQUE,
  17. name TEXT NOT NULL,
  18. name_set BOOLEAN NOT NULL DEFAULT false,
  19. topic TEXT NOT NULL,
  20. topic_set BOOLEAN NOT NULL DEFAULT false,
  21. avatar TEXT NOT NULL,
  22. avatar_url TEXT,
  23. avatar_set BOOLEAN NOT NULL DEFAULT false,
  24. encrypted BOOLEAN NOT NULL DEFAULT false,
  25. last_sync BIGINT NOT NULL DEFAULT 0,
  26. is_parent BOOLEAN NOT NULL DEFAULT false,
  27. parent_group TEXT,
  28. in_space BOOLEAN NOT NULL DEFAULT false,
  29. first_event_id TEXT,
  30. next_batch_id TEXT,
  31. relay_user_id TEXT,
  32. expiration_time BIGINT NOT NULL DEFAULT 0 CHECK (expiration_time >= 0 AND expiration_time < 4294967296),
  33. PRIMARY KEY (jid, receiver)
  34. );
  35. CREATE INDEX portal_parent_group_idx ON portal(parent_group);
  36. CREATE TABLE puppet (
  37. username TEXT PRIMARY KEY,
  38. displayname TEXT,
  39. name_quality SMALLINT,
  40. avatar TEXT,
  41. avatar_url TEXT,
  42. name_set BOOLEAN NOT NULL DEFAULT false,
  43. avatar_set BOOLEAN NOT NULL DEFAULT false,
  44. contact_info_set BOOLEAN NOT NULL DEFAULT false,
  45. last_sync BIGINT NOT NULL DEFAULT 0,
  46. custom_mxid TEXT,
  47. access_token TEXT,
  48. next_batch TEXT,
  49. enable_presence BOOLEAN NOT NULL DEFAULT true,
  50. enable_receipts BOOLEAN NOT NULL DEFAULT true
  51. );
  52. -- only: postgres
  53. CREATE TYPE error_type AS ENUM ('', 'decryption_failed', 'media_not_found');
  54. CREATE TABLE message (
  55. chat_jid TEXT,
  56. chat_receiver TEXT,
  57. jid TEXT,
  58. mxid TEXT UNIQUE,
  59. sender TEXT,
  60. sender_mxid TEXT NOT NULL DEFAULT '',
  61. timestamp BIGINT,
  62. sent BOOLEAN,
  63. error error_type,
  64. type TEXT,
  65. broadcast_list_jid TEXT,
  66. PRIMARY KEY (chat_jid, chat_receiver, jid),
  67. FOREIGN KEY (chat_jid, chat_receiver) REFERENCES portal(jid, receiver) ON DELETE CASCADE
  68. );
  69. CREATE TABLE poll_option_id (
  70. msg_mxid TEXT,
  71. opt_id TEXT,
  72. opt_hash bytea CHECK ( length(opt_hash) = 32 ),
  73. PRIMARY KEY (msg_mxid, opt_id),
  74. CONSTRAINT poll_option_unique_hash UNIQUE (msg_mxid, opt_hash),
  75. CONSTRAINT message_mxid_fkey FOREIGN KEY (msg_mxid) REFERENCES message(mxid) ON DELETE CASCADE ON UPDATE CASCADE
  76. );
  77. CREATE TABLE reaction (
  78. chat_jid TEXT,
  79. chat_receiver TEXT,
  80. target_jid TEXT,
  81. sender TEXT,
  82. mxid TEXT NOT NULL,
  83. jid TEXT NOT NULL,
  84. PRIMARY KEY (chat_jid, chat_receiver, target_jid, sender),
  85. FOREIGN KEY (chat_jid, chat_receiver, target_jid) REFERENCES message(chat_jid, chat_receiver, jid)
  86. ON DELETE CASCADE ON UPDATE CASCADE
  87. );
  88. CREATE TABLE disappearing_message (
  89. room_id TEXT,
  90. event_id TEXT,
  91. expire_in BIGINT NOT NULL,
  92. expire_at BIGINT,
  93. PRIMARY KEY (room_id, event_id)
  94. );
  95. CREATE TABLE user_portal (
  96. user_mxid TEXT,
  97. portal_jid TEXT,
  98. portal_receiver TEXT,
  99. last_read_ts BIGINT NOT NULL DEFAULT 0,
  100. in_space BOOLEAN NOT NULL DEFAULT false,
  101. PRIMARY KEY (user_mxid, portal_jid, portal_receiver),
  102. FOREIGN KEY (user_mxid) REFERENCES "user"(mxid) ON UPDATE CASCADE ON DELETE CASCADE,
  103. FOREIGN KEY (portal_jid, portal_receiver) REFERENCES portal(jid, receiver) ON UPDATE CASCADE ON DELETE CASCADE
  104. );
  105. CREATE TABLE backfill_queue (
  106. queue_id INTEGER PRIMARY KEY
  107. -- only: postgres
  108. GENERATED ALWAYS AS IDENTITY
  109. ,
  110. user_mxid TEXT,
  111. type INTEGER NOT NULL,
  112. priority INTEGER NOT NULL,
  113. portal_jid TEXT,
  114. portal_receiver TEXT,
  115. time_start TIMESTAMP,
  116. dispatch_time TIMESTAMP,
  117. completed_at TIMESTAMP,
  118. batch_delay INTEGER,
  119. max_batch_events INTEGER NOT NULL,
  120. max_total_events INTEGER,
  121. FOREIGN KEY (user_mxid) REFERENCES "user" (mxid) ON DELETE CASCADE ON UPDATE CASCADE,
  122. FOREIGN KEY (portal_jid, portal_receiver) REFERENCES portal(jid, receiver) ON DELETE CASCADE
  123. );
  124. CREATE TABLE backfill_state (
  125. user_mxid TEXT,
  126. portal_jid TEXT,
  127. portal_receiver TEXT,
  128. processing_batch BOOLEAN,
  129. backfill_complete BOOLEAN,
  130. first_expected_ts BIGINT,
  131. PRIMARY KEY (user_mxid, portal_jid, portal_receiver),
  132. FOREIGN KEY (user_mxid) REFERENCES "user" (mxid) ON DELETE CASCADE ON UPDATE CASCADE,
  133. FOREIGN KEY (portal_jid, portal_receiver) REFERENCES portal (jid, receiver) ON DELETE CASCADE
  134. );
  135. CREATE TABLE media_backfill_requests (
  136. user_mxid TEXT,
  137. portal_jid TEXT,
  138. portal_receiver TEXT,
  139. event_id TEXT,
  140. media_key bytea,
  141. status INTEGER,
  142. error TEXT,
  143. PRIMARY KEY (user_mxid, portal_jid, portal_receiver, event_id),
  144. FOREIGN KEY (user_mxid) REFERENCES "user"(mxid) ON UPDATE CASCADE ON DELETE CASCADE,
  145. FOREIGN KEY (portal_jid, portal_receiver) REFERENCES portal(jid, receiver) ON UPDATE CASCADE ON DELETE CASCADE
  146. );
  147. CREATE TABLE history_sync_conversation (
  148. user_mxid TEXT,
  149. conversation_id TEXT,
  150. portal_jid TEXT,
  151. portal_receiver TEXT,
  152. last_message_timestamp TIMESTAMP,
  153. archived BOOLEAN,
  154. pinned INTEGER,
  155. mute_end_time TIMESTAMP,
  156. disappearing_mode INTEGER,
  157. end_of_history_transfer_type INTEGER,
  158. ephemeral_Expiration INTEGER,
  159. marked_as_unread BOOLEAN,
  160. unread_count INTEGER,
  161. PRIMARY KEY (user_mxid, conversation_id),
  162. FOREIGN KEY (user_mxid) REFERENCES "user"(mxid) ON UPDATE CASCADE ON DELETE CASCADE,
  163. FOREIGN KEY (portal_jid, portal_receiver) REFERENCES portal(jid, receiver) ON UPDATE CASCADE ON DELETE CASCADE
  164. );
  165. CREATE TABLE history_sync_message (
  166. user_mxid TEXT,
  167. conversation_id TEXT,
  168. message_id TEXT,
  169. timestamp TIMESTAMP,
  170. data bytea,
  171. inserted_time TIMESTAMP,
  172. PRIMARY KEY (user_mxid, conversation_id, message_id),
  173. FOREIGN KEY (user_mxid) REFERENCES "user"(mxid) ON UPDATE CASCADE ON DELETE CASCADE,
  174. FOREIGN KEY (user_mxid, conversation_id) REFERENCES history_sync_conversation(user_mxid, conversation_id) ON DELETE CASCADE
  175. );