synapse/synapse/storage/schema/full_schemas/54/full.sql.sqlite

CREATE TABLE application_services(
  id BIGINT PRIMARY KEY,
  url TEXT,
  token TEXT,
  hs_token TEXT,
  sender TEXT,
  UNIQUE(token)
);
CREATE TABLE application_services_regex(
  id BIGINT PRIMARY KEY,
  as_id BIGINT NOT NULL,
  namespace INTEGER,
  regex TEXT,
  FOREIGN KEY(as_id) REFERENCES application_services(id)
);
CREATE TABLE application_services_state(
  as_id TEXT PRIMARY KEY,
  state VARCHAR(5),
  last_txn INTEGER
);
CREATE TABLE application_services_txns(
  as_id TEXT NOT NULL,
  txn_id INTEGER NOT NULL,
  event_ids TEXT NOT NULL,
  UNIQUE(as_id, txn_id)
);
CREATE INDEX application_services_txns_id ON application_services_txns(as_id);
CREATE TABLE presence(
  user_id TEXT NOT NULL,
  state VARCHAR(20),
  status_msg TEXT,
  mtime BIGINT,
  UNIQUE(user_id)
);
CREATE TABLE presence_allow_inbound(
  observed_user_id TEXT NOT NULL,
  observer_user_id TEXT NOT NULL,
  UNIQUE(observed_user_id, observer_user_id)
);
CREATE TABLE presence_list(
  user_id TEXT NOT NULL,
  observed_user_id TEXT NOT NULL,
  accepted BOOLEAN NOT NULL,
  UNIQUE(user_id, observed_user_id)
);
CREATE INDEX presence_list_user_id ON presence_list(user_id);
CREATE TABLE users(
  name TEXT,
  password_hash TEXT,
  creation_ts BIGINT,
  admin SMALLINT DEFAULT 0 NOT NULL,
  upgrade_ts BIGINT,
  is_guest SMALLINT DEFAULT 0 NOT NULL,
  appservice_id TEXT,
  consent_version TEXT,
  consent_server_notice_sent TEXT,
  user_type TEXT DEFAULT NULL,
  UNIQUE(name)
);
CREATE TABLE access_tokens(
  id BIGINT PRIMARY KEY,
  user_id TEXT NOT NULL,
  device_id TEXT,
  token TEXT NOT NULL,
  last_used BIGINT,
  UNIQUE(token)
);
CREATE TABLE user_ips(
  user_id TEXT NOT NULL,
  access_token TEXT NOT NULL,
  device_id TEXT,
  ip TEXT NOT NULL,
  user_agent TEXT NOT NULL,
  last_seen BIGINT NOT NULL
);
CREATE INDEX user_ips_user_ip ON user_ips(user_id, access_token, ip);
CREATE TABLE profiles(
  user_id TEXT NOT NULL,
  displayname TEXT,
  avatar_url TEXT,
  UNIQUE(user_id)
);
CREATE TABLE received_transactions(
  transaction_id TEXT,
  origin TEXT,
  ts BIGINT,
  response_code INTEGER,
  response_json bytea,
  has_been_referenced smallint default 0,
  UNIQUE(transaction_id, origin)
);
CREATE TABLE transaction_id_to_pdu(
  transaction_id INTEGER,
  destination TEXT,
  pdu_id TEXT,
  pdu_origin TEXT,
  UNIQUE(transaction_id, destination)
);
CREATE INDEX transaction_id_to_pdu_dest ON transaction_id_to_pdu(destination);
CREATE TABLE destinations(
  destination TEXT PRIMARY KEY,
  retry_last_ts BIGINT,
  retry_interval INTEGER
);
CREATE TABLE events(
  stream_ordering INTEGER PRIMARY KEY,
  topological_ordering BIGINT NOT NULL,
  event_id TEXT NOT NULL,
  type TEXT NOT NULL,
  room_id TEXT NOT NULL,
  content TEXT,
  unrecognized_keys TEXT,
  processed BOOL NOT NULL,
  outlier BOOL NOT NULL,
  depth BIGINT DEFAULT 0 NOT NULL,
  origin_server_ts BIGINT,
  received_ts BIGINT,
  sender TEXT,
  contains_url BOOLEAN,
  UNIQUE(event_id)
);
CREATE INDEX events_order_room ON events(
  room_id,
  topological_ordering,
  stream_ordering
);
CREATE TABLE event_json(
  event_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  internal_metadata TEXT NOT NULL,
  json TEXT NOT NULL,
  UNIQUE(event_id)
);
CREATE INDEX event_json_room_id ON event_json(room_id);
CREATE TABLE state_events(
  event_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  type TEXT NOT NULL,
  state_key TEXT NOT NULL,
  prev_state TEXT,
  UNIQUE(event_id)
);
CREATE TABLE current_state_events(
  event_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  type TEXT NOT NULL,
  state_key TEXT NOT NULL,
  UNIQUE(event_id),
  UNIQUE(room_id, type, state_key)
);
CREATE TABLE room_memberships(
  event_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  sender TEXT NOT NULL,
  room_id TEXT NOT NULL,
  membership TEXT NOT NULL,
  forgotten INTEGER DEFAULT 0,
  display_name TEXT,
  avatar_url TEXT,
  UNIQUE(event_id)
);
CREATE INDEX room_memberships_room_id ON room_memberships(room_id);
CREATE INDEX room_memberships_user_id ON room_memberships(user_id);
CREATE TABLE feedback(
  event_id TEXT NOT NULL,
  feedback_type TEXT,
  target_event_id TEXT,
  sender TEXT,
  room_id TEXT,
  UNIQUE(event_id)
);
CREATE TABLE topics(
  event_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  topic TEXT NOT NULL,
  UNIQUE(event_id)
);
CREATE INDEX topics_room_id ON topics(room_id);
CREATE TABLE room_names(
  event_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  name TEXT NOT NULL,
  UNIQUE(event_id)
);
CREATE INDEX room_names_room_id ON room_names(room_id);
CREATE TABLE rooms(
  room_id TEXT PRIMARY KEY NOT NULL,
  is_public BOOL,
  creator TEXT
);
CREATE TABLE room_hosts(
  room_id TEXT NOT NULL,
  host TEXT NOT NULL,
  UNIQUE(room_id, host)
);
CREATE TABLE server_tls_certificates(
  server_name TEXT,
  fingerprint TEXT,
  from_server TEXT,
  ts_added_ms BIGINT,
  tls_certificate bytea,
  UNIQUE(server_name, fingerprint)
);
CREATE TABLE server_signature_keys(
  server_name TEXT,
  key_id TEXT,
  from_server TEXT,
  ts_added_ms BIGINT,
  verify_key bytea,
  UNIQUE(server_name, key_id)
);
CREATE TABLE rejections(
  event_id TEXT NOT NULL,
  reason TEXT NOT NULL,
  last_check TEXT NOT NULL,
  UNIQUE(event_id)
);
CREATE TABLE push_rules(
  id BIGINT PRIMARY KEY,
  user_name TEXT NOT NULL,
  rule_id TEXT NOT NULL,
  priority_class SMALLINT NOT NULL,
  priority INTEGER NOT NULL DEFAULT 0,
  conditions TEXT NOT NULL,
  actions TEXT NOT NULL,
  UNIQUE(user_name, rule_id)
);
CREATE INDEX push_rules_user_name on push_rules(user_name);
CREATE TABLE user_filters(user_id TEXT, filter_id BIGINT, filter_json bytea);
CREATE INDEX user_filters_by_user_id_filter_id ON user_filters(
  user_id,
  filter_id
);
CREATE TABLE push_rules_enable(
  id BIGINT PRIMARY KEY,
  user_name TEXT NOT NULL,
  rule_id TEXT NOT NULL,
  enabled SMALLINT,
  UNIQUE(user_name, rule_id)
);
CREATE INDEX push_rules_enable_user_name on push_rules_enable(user_name);
CREATE TABLE event_forward_extremities(
  event_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  UNIQUE(event_id, room_id)
);
CREATE INDEX ev_extrem_room ON event_forward_extremities(room_id);
CREATE INDEX ev_extrem_id ON event_forward_extremities(event_id);
CREATE TABLE event_backward_extremities(
  event_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  UNIQUE(event_id, room_id)
);
CREATE INDEX ev_b_extrem_room ON event_backward_extremities(room_id);
CREATE INDEX ev_b_extrem_id ON event_backward_extremities(event_id);
CREATE TABLE event_edges(
  event_id TEXT NOT NULL,
  prev_event_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  is_state BOOL NOT NULL,
  UNIQUE(event_id, prev_event_id, room_id, is_state)
);
CREATE INDEX ev_edges_id ON event_edges(event_id);
CREATE INDEX ev_edges_prev_id ON event_edges(prev_event_id);
CREATE TABLE room_depth(
  room_id TEXT NOT NULL,
  min_depth INTEGER NOT NULL,
  UNIQUE(room_id)
);
CREATE INDEX room_depth_room ON room_depth(room_id);
CREATE TABLE event_destinations(
  event_id TEXT NOT NULL,
  destination TEXT NOT NULL,
  delivered_ts BIGINT DEFAULT 0,
  UNIQUE(event_id, destination)
);
CREATE TABLE state_forward_extremities(
  event_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  type TEXT NOT NULL,
  state_key TEXT NOT NULL,
  UNIQUE(event_id, room_id)
);
CREATE INDEX st_extrem_keys ON state_forward_extremities(
  room_id,
  type,
  state_key
);
CREATE TABLE state_groups(
  id BIGINT PRIMARY KEY,
  room_id TEXT NOT NULL,
  event_id TEXT NOT NULL
);
CREATE TABLE state_groups_state(
  state_group BIGINT NOT NULL,
  room_id TEXT NOT NULL,
  type TEXT NOT NULL,
  state_key TEXT NOT NULL,
  event_id TEXT NOT NULL
);
CREATE TABLE event_to_state_groups(
  event_id TEXT NOT NULL,
  state_group BIGINT NOT NULL,
  UNIQUE(event_id)
);
CREATE TABLE local_media_repository(
  media_id TEXT,
  media_type TEXT,
  media_length INTEGER,
  created_ts BIGINT,
  upload_name TEXT,
  user_id TEXT,
  quarantined_by TEXT,
  url_cache TEXT,
  last_access_ts BIGINT,
  UNIQUE(media_id)
);
CREATE TABLE local_media_repository_thumbnails(
  media_id TEXT,
  thumbnail_width INTEGER,
  thumbnail_height INTEGER,
  thumbnail_type TEXT,
  thumbnail_method TEXT,
  thumbnail_length INTEGER,
  UNIQUE(media_id, thumbnail_width, thumbnail_height, thumbnail_type)
);
CREATE INDEX local_media_repository_thumbnails_media_id ON local_media_repository_thumbnails(
  media_id
);
CREATE TABLE remote_media_cache(
  media_origin TEXT,
  media_id TEXT,
  media_type TEXT,
  created_ts BIGINT,
  upload_name TEXT,
  media_length INTEGER,
  filesystem_id TEXT,
  last_access_ts BIGINT,
  quarantined_by TEXT,
  UNIQUE(media_origin, media_id)
);
CREATE TABLE remote_media_cache_thumbnails(
  media_origin TEXT,
  media_id TEXT,
  thumbnail_width INTEGER,
  thumbnail_height INTEGER,
  thumbnail_method TEXT,
  thumbnail_type TEXT,
  thumbnail_length INTEGER,
  filesystem_id TEXT,
  UNIQUE(media_origin, media_id, thumbnail_width, thumbnail_height, thumbnail_type)
);
CREATE TABLE redactions(
  event_id TEXT NOT NULL,
  redacts TEXT NOT NULL,
  UNIQUE(event_id)
);
CREATE INDEX redactions_redacts ON redactions(redacts);
CREATE TABLE room_aliases(
  room_alias TEXT NOT NULL,
  room_id TEXT NOT NULL,
  creator TEXT,
  UNIQUE(room_alias)
);
CREATE INDEX room_aliases_id ON room_aliases(room_id);
CREATE TABLE room_alias_servers(
  room_alias TEXT NOT NULL,
  server TEXT NOT NULL
);
CREATE INDEX room_alias_servers_alias ON room_alias_servers(room_alias);
CREATE TABLE event_content_hashes(
  event_id TEXT,
  algorithm TEXT,
  hash bytea,
  UNIQUE(event_id, algorithm)
);
CREATE TABLE event_reference_hashes(
  event_id TEXT,
  algorithm TEXT,
  hash bytea,
  UNIQUE(event_id, algorithm)
);
CREATE INDEX event_reference_hashes_id ON event_reference_hashes(event_id);
CREATE TABLE event_signatures(
  event_id TEXT,
  signature_name TEXT,
  key_id TEXT,
  signature bytea,
  UNIQUE(event_id, signature_name, key_id)
);
CREATE TABLE event_edge_hashes(
  event_id TEXT,
  prev_event_id TEXT,
  algorithm TEXT,
  hash bytea,
  UNIQUE(event_id, prev_event_id, algorithm)
);
CREATE TABLE IF NOT EXISTS "server_keys_json"(
  server_name TEXT NOT NULL,
  key_id TEXT NOT NULL,
  from_server TEXT NOT NULL,
  ts_added_ms BIGINT NOT NULL,
  ts_valid_until_ms BIGINT NOT NULL,
  key_json bytea NOT NULL,
  CONSTRAINT server_keys_json_uniqueness UNIQUE(server_name, key_id, from_server)
);
CREATE TABLE e2e_device_keys_json(
  user_id TEXT NOT NULL,
  device_id TEXT NOT NULL,
  ts_added_ms BIGINT NOT NULL,
  key_json TEXT NOT NULL,
  CONSTRAINT e2e_device_keys_json_uniqueness UNIQUE(user_id, device_id)
);
CREATE TABLE e2e_one_time_keys_json(
  user_id TEXT NOT NULL,
  device_id TEXT NOT NULL,
  algorithm TEXT NOT NULL,
  key_id TEXT NOT NULL,
  ts_added_ms BIGINT NOT NULL,
  key_json TEXT NOT NULL,
  CONSTRAINT e2e_one_time_keys_json_uniqueness UNIQUE(user_id, device_id, algorithm, key_id)
);
CREATE TABLE receipts_graph(
  room_id TEXT NOT NULL,
  receipt_type TEXT NOT NULL,
  user_id TEXT NOT NULL,
  event_ids TEXT NOT NULL,
  data TEXT NOT NULL,
  CONSTRAINT receipts_graph_uniqueness UNIQUE(room_id, receipt_type, user_id)
);
CREATE TABLE receipts_linearized(
  stream_id BIGINT NOT NULL,
  room_id TEXT NOT NULL,
  receipt_type TEXT NOT NULL,
  user_id TEXT NOT NULL,
  event_id TEXT NOT NULL,
  data TEXT NOT NULL,
  CONSTRAINT receipts_linearized_uniqueness UNIQUE(room_id, receipt_type, user_id)
);
CREATE INDEX receipts_linearized_id ON receipts_linearized(stream_id);
CREATE INDEX receipts_linearized_room_stream ON receipts_linearized(
  room_id,
  stream_id
);
CREATE TABLE IF NOT EXISTS "user_threepids"(
  user_id TEXT NOT NULL,
  medium TEXT NOT NULL,
  address TEXT NOT NULL,
  validated_at BIGINT NOT NULL,
  added_at BIGINT NOT NULL,
  CONSTRAINT medium_address UNIQUE(medium, address)
);
CREATE INDEX user_threepids_user_id ON user_threepids(user_id);
CREATE TABLE stats_reporting(
  reported_stream_token INTEGER,
  reported_time BIGINT
);
CREATE TABLE background_updates(
  update_name TEXT NOT NULL,
  progress_json TEXT NOT NULL,
  depends_on TEXT,
  CONSTRAINT background_updates_uniqueness UNIQUE(update_name)
);
CREATE VIRTUAL TABLE event_search USING fts4(
  event_id,
  room_id,
  sender,
  key,
  value
)
/* event_search(
  event_id,
  room_id,
  sender,
  "key",
  value
) */;
CREATE TABLE IF NOT EXISTS 'event_search_content'(
  docid INTEGER PRIMARY KEY,
  'c0event_id',
  'c1room_id',
  'c2sender',
  'c3key',
  'c4value'
);
CREATE TABLE IF NOT EXISTS 'event_search_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'event_search_segdir'(
  level INTEGER,
  idx INTEGER,
  start_block INTEGER,
  leaves_end_block INTEGER,
  end_block INTEGER,
  root BLOB,
  PRIMARY KEY(level, idx)
);
CREATE TABLE IF NOT EXISTS 'event_search_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
CREATE TABLE IF NOT EXISTS 'event_search_stat'(id INTEGER PRIMARY KEY, value BLOB);
CREATE TABLE guest_access(
  event_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  guest_access TEXT NOT NULL,
  UNIQUE(event_id)
);
CREATE TABLE history_visibility(
  event_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  history_visibility TEXT NOT NULL,
  UNIQUE(event_id)
);
CREATE TABLE room_tags(
  user_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  tag TEXT NOT NULL,
  content TEXT NOT NULL,
  CONSTRAINT room_tag_uniqueness UNIQUE(user_id, room_id, tag)
);
CREATE TABLE room_tags_revisions(
  user_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  stream_id BIGINT NOT NULL,
  CONSTRAINT room_tag_revisions_uniqueness UNIQUE(user_id, room_id)
);
CREATE TABLE IF NOT EXISTS "account_data_max_stream_id"(
  Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE,
  stream_id BIGINT NOT NULL,
  CHECK(Lock='X')
);
CREATE TABLE account_data(
  user_id TEXT NOT NULL,
  account_data_type TEXT NOT NULL,
  stream_id BIGINT NOT NULL,
  content TEXT NOT NULL,
  CONSTRAINT account_data_uniqueness UNIQUE(user_id, account_data_type)
);
CREATE TABLE room_account_data(
  user_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  account_data_type TEXT NOT NULL,
  stream_id BIGINT NOT NULL,
  content TEXT NOT NULL,
  CONSTRAINT room_account_data_uniqueness UNIQUE(user_id, room_id, account_data_type)
);
CREATE INDEX account_data_stream_id on account_data(user_id, stream_id);
CREATE INDEX room_account_data_stream_id on room_account_data(
  user_id,
  stream_id
);
CREATE INDEX events_ts ON events(origin_server_ts, stream_ordering);
CREATE TABLE event_push_actions(
  room_id TEXT NOT NULL,
  event_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  profile_tag VARCHAR(32),
  actions TEXT NOT NULL,
  topological_ordering BIGINT,
  stream_ordering BIGINT,
  notif SMALLINT,
  highlight SMALLINT,
  CONSTRAINT event_id_user_id_profile_tag_uniqueness UNIQUE(room_id, event_id, user_id, profile_tag)
);
CREATE INDEX event_push_actions_room_id_user_id on event_push_actions(
  room_id,
  user_id
);
CREATE INDEX events_room_stream on events(room_id, stream_ordering);
CREATE INDEX public_room_index on rooms(is_public);
CREATE INDEX receipts_linearized_user ON receipts_linearized(user_id);
CREATE INDEX event_push_actions_rm_tokens on event_push_actions(
  user_id,
  room_id,
  topological_ordering,
  stream_ordering
);
CREATE TABLE presence_stream(
  stream_id BIGINT,
  user_id TEXT,
  state TEXT,
  last_active_ts BIGINT,
  last_federation_update_ts BIGINT,
  last_user_sync_ts BIGINT,
  status_msg TEXT,
  currently_active BOOLEAN
);
CREATE INDEX presence_stream_id ON presence_stream(stream_id, user_id);
CREATE INDEX presence_stream_user_id ON presence_stream(user_id);
CREATE TABLE push_rules_stream(
  stream_id BIGINT NOT NULL,
  event_stream_ordering BIGINT NOT NULL,
  user_id TEXT NOT NULL,
  rule_id TEXT NOT NULL,
  op TEXT NOT NULL,
  priority_class SMALLINT,
  priority INTEGER,
  conditions TEXT,
  actions TEXT
);
CREATE INDEX push_rules_stream_id ON push_rules_stream(stream_id);
CREATE INDEX push_rules_stream_user_stream_id on push_rules_stream(
  user_id,
  stream_id
);
CREATE TABLE current_state_resets(
  event_stream_ordering BIGINT PRIMARY KEY NOT NULL
);
CREATE TABLE ex_outlier_stream(
  event_stream_ordering BIGINT PRIMARY KEY NOT NULL,
  event_id TEXT NOT NULL,
  state_group BIGINT NOT NULL
);
CREATE TABLE threepid_guest_access_tokens(
  medium TEXT,
  address TEXT,
  guest_access_token TEXT,
  first_inviter TEXT
);
CREATE UNIQUE INDEX threepid_guest_access_tokens_index ON threepid_guest_access_tokens(
  medium,
  address
);
CREATE TABLE local_invites(
  stream_id BIGINT NOT NULL,
  inviter TEXT NOT NULL,
  invitee TEXT NOT NULL,
  event_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  locally_rejected TEXT,
  replaced_by TEXT
);
CREATE INDEX local_invites_id ON local_invites(stream_id);
CREATE INDEX local_invites_for_user_idx ON local_invites(
  invitee,
  locally_rejected,
  replaced_by,
  room_id
);
CREATE INDEX event_push_actions_stream_ordering on event_push_actions(
  stream_ordering,
  user_id
);
CREATE TABLE open_id_tokens(
  token TEXT NOT NULL PRIMARY KEY,
  ts_valid_until_ms bigint NOT NULL,
  user_id TEXT NOT NULL,
  UNIQUE(token)
);
CREATE INDEX open_id_tokens_ts_valid_until_ms ON open_id_tokens(
  ts_valid_until_ms
);
CREATE TABLE pusher_throttle(
  pusher BIGINT NOT NULL,
  room_id TEXT NOT NULL,
  last_sent_ts BIGINT,
  throttle_ms BIGINT,
  PRIMARY KEY(pusher, room_id)
);
CREATE TABLE event_reports(
  id BIGINT NOT NULL PRIMARY KEY,
  received_ts BIGINT NOT NULL,
  room_id TEXT NOT NULL,
  event_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  reason TEXT,
  content TEXT
);
CREATE TABLE devices(
  user_id TEXT NOT NULL,
  device_id TEXT NOT NULL,
  display_name TEXT,
  CONSTRAINT device_uniqueness UNIQUE(user_id, device_id)
);
CREATE TABLE appservice_stream_position(
  Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE,
  stream_ordering BIGINT,
  CHECK(Lock='X')
);
CREATE TABLE device_inbox(
  user_id TEXT NOT NULL,
  device_id TEXT NOT NULL,
  stream_id BIGINT NOT NULL,
  message_json TEXT NOT NULL
);
CREATE INDEX device_inbox_user_stream_id ON device_inbox(
  user_id,
  device_id,
  stream_id
);
CREATE INDEX received_transactions_ts ON received_transactions(ts);
CREATE TABLE device_federation_outbox(
  destination TEXT NOT NULL,
  stream_id BIGINT NOT NULL,
  queued_ts BIGINT NOT NULL,
  messages_json TEXT NOT NULL
);
CREATE INDEX device_federation_outbox_destination_id ON device_federation_outbox(
  destination,
  stream_id
);
CREATE TABLE device_federation_inbox(
  origin TEXT NOT NULL,
  message_id TEXT NOT NULL,
  received_ts BIGINT NOT NULL
);
CREATE INDEX device_federation_inbox_sender_id ON device_federation_inbox(
  origin,
  message_id
);
CREATE TABLE device_max_stream_id(stream_id BIGINT NOT NULL);
CREATE TABLE public_room_list_stream(
  stream_id BIGINT NOT NULL,
  room_id TEXT NOT NULL,
  visibility BOOLEAN NOT NULL ,
  appservice_id TEXT,
  network_id TEXT
);
CREATE INDEX public_room_list_stream_idx on public_room_list_stream(stream_id);
CREATE INDEX public_room_list_stream_rm_idx on public_room_list_stream(
  room_id,
  stream_id
);
CREATE TABLE state_group_edges(
  state_group BIGINT NOT NULL,
  prev_state_group BIGINT NOT NULL
);
CREATE INDEX state_group_edges_idx ON state_group_edges(state_group);
CREATE INDEX state_group_edges_prev_idx ON state_group_edges(prev_state_group);
CREATE TABLE stream_ordering_to_exterm(
  stream_ordering BIGINT NOT NULL,
  room_id TEXT NOT NULL,
  event_id TEXT NOT NULL
);
CREATE INDEX stream_ordering_to_exterm_idx on stream_ordering_to_exterm(
  stream_ordering
);
CREATE INDEX stream_ordering_to_exterm_rm_idx on stream_ordering_to_exterm(
  room_id,
  stream_ordering
);
CREATE TABLE IF NOT EXISTS "event_auth"(
  event_id TEXT NOT NULL,
  auth_id TEXT NOT NULL,
  room_id TEXT NOT NULL
);
CREATE INDEX evauth_edges_id ON event_auth(event_id);
CREATE INDEX user_threepids_medium_address on user_threepids(medium, address);
CREATE TABLE appservice_room_list(
  appservice_id TEXT NOT NULL,
  network_id TEXT NOT NULL,
  room_id TEXT NOT NULL
);
CREATE UNIQUE INDEX appservice_room_list_idx ON appservice_room_list(
  appservice_id,
  network_id,
  room_id
);
CREATE INDEX device_federation_outbox_id ON device_federation_outbox(
  stream_id
);
CREATE TABLE federation_stream_position(
  type TEXT NOT NULL,
  stream_id INTEGER NOT NULL
);
CREATE TABLE device_lists_remote_cache(
  user_id TEXT NOT NULL,
  device_id TEXT NOT NULL,
  content TEXT NOT NULL
);
CREATE TABLE device_lists_remote_extremeties(
  user_id TEXT NOT NULL,
  stream_id TEXT NOT NULL
);
CREATE TABLE device_lists_stream(
  stream_id BIGINT NOT NULL,
  user_id TEXT NOT NULL,
  device_id TEXT NOT NULL
);
CREATE INDEX device_lists_stream_id ON device_lists_stream(stream_id, user_id);
CREATE TABLE device_lists_outbound_pokes(
  destination TEXT NOT NULL,
  stream_id BIGINT NOT NULL,
  user_id TEXT NOT NULL,
  device_id TEXT NOT NULL,
  sent BOOLEAN NOT NULL,
  ts BIGINT NOT NULL
);
CREATE INDEX device_lists_outbound_pokes_id ON device_lists_outbound_pokes(
  destination,
  stream_id
);
CREATE INDEX device_lists_outbound_pokes_user ON device_lists_outbound_pokes(
  destination,
  user_id
);
CREATE TABLE event_push_summary(
  user_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  notif_count BIGINT NOT NULL,
  stream_ordering BIGINT NOT NULL
);
CREATE INDEX event_push_summary_user_rm ON event_push_summary(
  user_id,
  room_id
);
CREATE TABLE event_push_summary_stream_ordering(
  Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE,
  stream_ordering BIGINT NOT NULL,
  CHECK(Lock='X')
);
CREATE TABLE IF NOT EXISTS "pushers"(
  id BIGINT PRIMARY KEY,
  user_name TEXT NOT NULL,
  access_token BIGINT DEFAULT NULL,
  profile_tag TEXT NOT NULL,
  kind TEXT NOT NULL,
  app_id TEXT NOT NULL,
  app_display_name TEXT NOT NULL,
  device_display_name TEXT NOT NULL,
  pushkey TEXT NOT NULL,
  ts BIGINT NOT NULL,
  lang TEXT,
  data TEXT,
  last_stream_ordering INTEGER,
  last_success BIGINT,
  failing_since BIGINT,
  UNIQUE(app_id, pushkey, user_name)
);
CREATE INDEX device_lists_outbound_pokes_stream ON device_lists_outbound_pokes(
  stream_id
);
CREATE TABLE ratelimit_override(
  user_id TEXT NOT NULL,
  messages_per_second BIGINT,
  burst_count BIGINT
);
CREATE UNIQUE INDEX ratelimit_override_idx ON ratelimit_override(user_id);
CREATE TABLE current_state_delta_stream(
  stream_id BIGINT NOT NULL,
  room_id TEXT NOT NULL,
  type TEXT NOT NULL,
  state_key TEXT NOT NULL,
  event_id TEXT,
  prev_event_id TEXT
);
CREATE INDEX current_state_delta_stream_idx ON current_state_delta_stream(
  stream_id
);
CREATE TABLE device_lists_outbound_last_success(
  destination TEXT NOT NULL,
  user_id TEXT NOT NULL,
  stream_id BIGINT NOT NULL
);
CREATE INDEX device_lists_outbound_last_success_idx ON device_lists_outbound_last_success(
  destination,
  user_id,
  stream_id
);
CREATE TABLE user_directory_stream_pos(
  Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE,
  stream_id BIGINT,
  CHECK(Lock='X')
);
CREATE TABLE IF NOT EXISTS "users_in_public_rooms"(
  user_id TEXT NOT NULL,
  room_id TEXT NOT NULL
);
CREATE VIRTUAL TABLE user_directory_search USING fts4(
  user_id,
  value
)
/* user_directory_search(
  user_id,
  value
) */;
CREATE TABLE IF NOT EXISTS 'user_directory_search_content'(
  docid INTEGER PRIMARY KEY,
  'c0user_id',
  'c1value'
);
CREATE TABLE IF NOT EXISTS 'user_directory_search_segments'(
  blockid INTEGER PRIMARY KEY,
  block BLOB
);
CREATE TABLE IF NOT EXISTS 'user_directory_search_segdir'(
  level INTEGER,
  idx INTEGER,
  start_block INTEGER,
  leaves_end_block INTEGER,
  end_block INTEGER,
  root BLOB,
  PRIMARY KEY(level, idx)
);
CREATE TABLE IF NOT EXISTS 'user_directory_search_docsize'(
  docid INTEGER PRIMARY KEY,
  size BLOB
);
CREATE TABLE IF NOT EXISTS 'user_directory_search_stat'(id INTEGER PRIMARY KEY, value BLOB);
CREATE TABLE blocked_rooms(room_id TEXT NOT NULL, user_id TEXT NOT NULL);
CREATE UNIQUE INDEX blocked_rooms_idx ON blocked_rooms(room_id);
CREATE TABLE users_who_share_rooms(
  user_id TEXT NOT NULL,
  other_user_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  share_private BOOLEAN NOT NULL
);
CREATE UNIQUE INDEX users_who_share_rooms_u_idx ON users_who_share_rooms(
  user_id,
  other_user_id
);
CREATE INDEX users_who_share_rooms_r_idx ON users_who_share_rooms(room_id);
CREATE INDEX users_who_share_rooms_o_idx ON users_who_share_rooms(
  other_user_id
);
CREATE TABLE IF NOT EXISTS "local_media_repository_url_cache"(
  url TEXT,
  response_code INTEGER,
  etag TEXT,
  expires_ts BIGINT,
  og TEXT,
  media_id TEXT,
  download_ts BIGINT
);
CREATE INDEX local_media_repository_url_cache_expires_idx ON local_media_repository_url_cache(
  expires_ts
);
CREATE INDEX local_media_repository_url_cache_by_url_download_ts ON local_media_repository_url_cache(
  url,
  download_ts
);
CREATE INDEX local_media_repository_url_cache_media_idx ON local_media_repository_url_cache(
  media_id
);
CREATE TABLE group_users(
  group_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  is_admin BOOLEAN NOT NULL,
  is_public BOOLEAN NOT NULL
);
CREATE TABLE group_invites(group_id TEXT NOT NULL, user_id TEXT NOT NULL);
CREATE TABLE group_rooms(
  group_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  is_public BOOLEAN NOT NULL
);
CREATE TABLE group_summary_rooms(
  group_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  category_id TEXT NOT NULL,
  room_order BIGINT NOT NULL,
  is_public BOOLEAN NOT NULL,
  UNIQUE(group_id, category_id, room_id, room_order),
  CHECK(room_order > 0)
);
CREATE UNIQUE INDEX group_summary_rooms_g_idx ON group_summary_rooms(
  group_id,
  room_id,
  category_id
);
CREATE TABLE group_summary_room_categories(
  group_id TEXT NOT NULL,
  category_id TEXT NOT NULL,
  cat_order BIGINT NOT NULL,
  UNIQUE(group_id, category_id, cat_order),
  CHECK(cat_order > 0)
);
CREATE TABLE group_room_categories(
  group_id TEXT NOT NULL,
  category_id TEXT NOT NULL,
  profile TEXT NOT NULL,
  is_public BOOLEAN NOT NULL,
  UNIQUE(group_id, category_id)
);
CREATE TABLE group_summary_users(
  group_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  role_id TEXT NOT NULL,
  user_order BIGINT NOT NULL,
  is_public BOOLEAN NOT NULL
);
CREATE INDEX group_summary_users_g_idx ON group_summary_users(group_id);
CREATE TABLE group_summary_roles(
  group_id TEXT NOT NULL,
  role_id TEXT NOT NULL,
  role_order BIGINT NOT NULL,
  UNIQUE(group_id, role_id, role_order),
  CHECK(role_order > 0)
);
CREATE TABLE group_roles(
  group_id TEXT NOT NULL,
  role_id TEXT NOT NULL,
  profile TEXT NOT NULL,
  is_public BOOLEAN NOT NULL,
  UNIQUE(group_id, role_id)
);
CREATE TABLE group_attestations_renewals(
  group_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  valid_until_ms BIGINT NOT NULL
);
CREATE INDEX group_attestations_renewals_g_idx ON group_attestations_renewals(
  group_id,
  user_id
);
CREATE INDEX group_attestations_renewals_u_idx ON group_attestations_renewals(
  user_id
);
CREATE INDEX group_attestations_renewals_v_idx ON group_attestations_renewals(
  valid_until_ms
);
CREATE TABLE group_attestations_remote(
  group_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  valid_until_ms BIGINT NOT NULL,
  attestation_json TEXT NOT NULL
);
CREATE INDEX group_attestations_remote_g_idx ON group_attestations_remote(
  group_id,
  user_id
);
CREATE INDEX group_attestations_remote_u_idx ON group_attestations_remote(
  user_id
);
CREATE INDEX group_attestations_remote_v_idx ON group_attestations_remote(
  valid_until_ms
);
CREATE TABLE local_group_membership(
  group_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  is_admin BOOLEAN NOT NULL,
  membership TEXT NOT NULL,
  is_publicised BOOLEAN NOT NULL,
  content TEXT NOT NULL
);
CREATE INDEX local_group_membership_u_idx ON local_group_membership(
  user_id,
  group_id
);
CREATE INDEX local_group_membership_g_idx ON local_group_membership(group_id);
CREATE TABLE local_group_updates(
  stream_id BIGINT NOT NULL,
  group_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  type TEXT NOT NULL,
  content TEXT NOT NULL
);
CREATE TABLE remote_profile_cache(
  user_id TEXT NOT NULL,
  displayname TEXT,
  avatar_url TEXT,
  last_check BIGINT NOT NULL
);
CREATE UNIQUE INDEX remote_profile_cache_user_id ON remote_profile_cache(
  user_id
);
CREATE INDEX remote_profile_cache_time ON remote_profile_cache(last_check);
CREATE TABLE IF NOT EXISTS "deleted_pushers"(
  stream_id BIGINT NOT NULL,
  app_id TEXT NOT NULL,
  pushkey TEXT NOT NULL,
  user_id TEXT NOT NULL
);
CREATE INDEX deleted_pushers_stream_id ON deleted_pushers(stream_id);
CREATE TABLE IF NOT EXISTS "groups"(
  group_id TEXT NOT NULL,
  name TEXT,
  avatar_url TEXT,
  short_description TEXT,
  long_description TEXT,
  is_public BOOL NOT NULL ,
  join_policy TEXT NOT NULL DEFAULT 'invite'
);
CREATE UNIQUE INDEX groups_idx ON groups(group_id);
CREATE TABLE IF NOT EXISTS "user_directory"(
  user_id TEXT NOT NULL,
  room_id TEXT,
  display_name TEXT,
  avatar_url TEXT
);
CREATE INDEX user_directory_room_idx ON user_directory(room_id);
CREATE UNIQUE INDEX user_directory_user_idx ON user_directory(user_id);
CREATE INDEX users_in_public_rooms_room_idx ON users_in_public_rooms(room_id);
CREATE UNIQUE INDEX users_in_public_rooms_user_idx ON users_in_public_rooms(
  user_id
);
CREATE TABLE event_push_actions_staging(
  event_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  actions TEXT NOT NULL,
  notif SMALLINT NOT NULL,
  highlight SMALLINT NOT NULL
);
CREATE INDEX event_push_actions_staging_id ON event_push_actions_staging(
  event_id
);
CREATE TABLE users_pending_deactivation(user_id TEXT NOT NULL);
CREATE UNIQUE INDEX group_invites_g_idx ON group_invites(group_id, user_id);
CREATE UNIQUE INDEX group_users_g_idx ON group_users(group_id, user_id);
CREATE INDEX group_users_u_idx ON group_users(user_id);
CREATE INDEX group_invites_u_idx ON group_invites(user_id);
CREATE UNIQUE INDEX group_rooms_g_idx ON group_rooms(group_id, room_id);
CREATE INDEX group_rooms_r_idx ON group_rooms(room_id);
CREATE TABLE user_daily_visits(
  user_id TEXT NOT NULL,
  device_id TEXT,
  timestamp BIGINT NOT NULL
);
CREATE INDEX user_daily_visits_uts_idx ON user_daily_visits(
  user_id,
  timestamp
);
CREATE INDEX user_daily_visits_ts_idx ON user_daily_visits(timestamp);
CREATE TABLE erased_users(user_id TEXT NOT NULL);
CREATE UNIQUE INDEX erased_users_user ON erased_users(user_id);
CREATE TABLE monthly_active_users(
  user_id TEXT NOT NULL,
  timestamp BIGINT NOT NULL
);
CREATE UNIQUE INDEX monthly_active_users_users ON monthly_active_users(
  user_id
);
CREATE INDEX monthly_active_users_time_stamp ON monthly_active_users(
  timestamp
);
CREATE TABLE IF NOT EXISTS "e2e_room_keys_versions"(
  user_id TEXT NOT NULL,
  version BIGINT NOT NULL,
  algorithm TEXT NOT NULL,
  auth_data TEXT NOT NULL,
  deleted SMALLINT DEFAULT 0 NOT NULL
);
CREATE UNIQUE INDEX e2e_room_keys_versions_idx ON e2e_room_keys_versions(
  user_id,
  version
);
CREATE TABLE IF NOT EXISTS "e2e_room_keys"(
  user_id TEXT NOT NULL,
  room_id TEXT NOT NULL,
  session_id TEXT NOT NULL,
  version BIGINT NOT NULL,
  first_message_index INT,
  forwarded_count INT,
  is_verified BOOLEAN,
  session_data TEXT NOT NULL
);
CREATE UNIQUE INDEX e2e_room_keys_idx ON e2e_room_keys(
  user_id,
  room_id,
  session_id
);
CREATE INDEX access_tokens_device_id ON access_tokens(user_id, device_id);
CREATE INDEX user_ips_device_id ON user_ips(user_id, device_id, last_seen);
CREATE INDEX event_contains_url_index ON events(
  room_id,
  topological_ordering,
  stream_ordering
);
CREATE INDEX event_push_actions_u_highlight ON event_push_actions(
  user_id,
  stream_ordering
);
CREATE INDEX event_push_actions_highlights_index ON event_push_actions(
  user_id,
  room_id,
  topological_ordering,
  stream_ordering
);
CREATE INDEX current_state_events_member_index ON current_state_events(
  state_key
);
CREATE INDEX device_inbox_stream_id_user_id ON device_inbox(
  stream_id,
  user_id
);
CREATE INDEX device_lists_stream_user_id ON device_lists_stream(
  user_id,
  device_id
);
CREATE INDEX local_media_repository_url_idx ON local_media_repository(
  created_ts
);
CREATE INDEX user_ips_last_seen ON user_ips(user_id, last_seen);
CREATE INDEX user_ips_last_seen_only ON user_ips(last_seen);
CREATE INDEX users_creation_ts ON users(creation_ts);
CREATE INDEX event_to_state_groups_sg_index ON event_to_state_groups(
  state_group
);
CREATE UNIQUE INDEX device_lists_remote_cache_unique_id ON device_lists_remote_cache(
  user_id,
  device_id
);
CREATE UNIQUE INDEX user_ips_device_unique_id ON user_ips(
  user_id,
  access_token,
  ip,
  user_agent,
  device_id
);
CREATE INDEX state_groups_state_type_idx ON state_groups_state(
  state_group,
  type,
  state_key
);
CREATE UNIQUE INDEX device_lists_remote_extremeties_unique_idx ON device_lists_remote_extremeties(
  user_id
);