_memory
| type | name | tbl_name | rootpage | sql |
|---|---|---|---|---|
| table | schema_version | schema_version | 2 | CREATE TABLE schema_version( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, -- Makes sure this table only has one row. version INTEGER NOT NULL, upgraded BOOL NOT NULL, -- Whether we reached this version from an upgrade or an initial schema. CHECK (Lock='X') ) |
| table | schema_compat_version | schema_compat_version | 4 | CREATE TABLE schema_compat_version( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, -- Makes sure this table only has one row. -- The SCHEMA_VERSION of the oldest synapse this database can be used with compat_version INTEGER NOT NULL, CHECK (Lock='X') ) |
| table | applied_schema_deltas | applied_schema_deltas | 6 | CREATE TABLE applied_schema_deltas( version INTEGER NOT NULL, file TEXT NOT NULL, UNIQUE(version, file) ) |
| table | applied_module_schemas | applied_module_schemas | 8 | CREATE TABLE applied_module_schemas( module_name TEXT NOT NULL, file TEXT NOT NULL, UNIQUE(module_name, file) ) |
| table | background_updates | background_updates | 10 | CREATE TABLE background_updates ( update_name text NOT NULL, progress_json text NOT NULL, depends_on text, ordering INT NOT NULL DEFAULT 0, CONSTRAINT background_updates_uniqueness UNIQUE (update_name) ) |
| table | application_services_txns | application_services_txns | 12 | 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) ) |
| table | users | users | 17 | 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, deactivated SMALLINT DEFAULT 0 NOT NULL, shadow_banned BOOLEAN, consent_ts bigint, approved BOOLEAN, locked BOOLEAN DEFAULT FALSE NOT NULL, suspended BOOLEAN DEFAULT FALSE NOT NULL, UNIQUE(name) ) |
| table | user_ips | user_ips | 19 | 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 ) |
| table | profiles | profiles | 20 | CREATE TABLE profiles( user_id TEXT NOT NULL, displayname TEXT, avatar_url TEXT, full_user_id TEXT, fields JSONB, UNIQUE(user_id) ) |
| table | received_transactions | received_transactions | 22 | 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) ) |
| table | destinations | destinations | 24 | CREATE TABLE destinations( destination TEXT PRIMARY KEY, retry_last_ts BIGINT, retry_interval INTEGER , failure_ts BIGINT, last_successful_stream_ordering BIGINT) |
| table | events | events | 26 | 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, instance_name TEXT, state_key TEXT DEFAULT NULL, rejection_reason TEXT DEFAULT NULL, UNIQUE (event_id) ) |
| table | event_json | event_json | 31 | CREATE TABLE event_json( event_id TEXT NOT NULL, room_id TEXT NOT NULL, internal_metadata TEXT NOT NULL, json TEXT NOT NULL, format_version INTEGER, UNIQUE (event_id) ) |
| table | state_events | state_events | 33 | 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) ) |
| table | current_state_events | current_state_events | 35 | CREATE TABLE current_state_events( event_id TEXT NOT NULL, room_id TEXT NOT NULL, type TEXT NOT NULL, state_key TEXT NOT NULL, membership TEXT, event_stream_ordering BIGINT REFERENCES events(stream_ordering), UNIQUE (event_id), UNIQUE (room_id, type, state_key) ) |
| table | room_memberships | room_memberships | 38 | 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, event_stream_ordering BIGINT REFERENCES events(stream_ordering), participant BOOLEAN DEFAULT FALSE, UNIQUE (event_id) ) |
| table | rooms | rooms | 42 | CREATE TABLE rooms( room_id TEXT PRIMARY KEY NOT NULL, is_public BOOL, creator TEXT , room_version TEXT, has_auth_chain_index BOOLEAN) |
| table | server_signature_keys | server_signature_keys | 44 | CREATE TABLE server_signature_keys( server_name TEXT, key_id TEXT, from_server TEXT, ts_added_ms BIGINT, verify_key bytea, ts_valid_until_ms BIGINT, UNIQUE (server_name, key_id) ) |
| table | rejections | rejections | 46 | CREATE TABLE rejections( event_id TEXT NOT NULL, reason TEXT NOT NULL, last_check TEXT NOT NULL, UNIQUE (event_id) ) |
| table | push_rules | push_rules | 48 | 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) ) |
| table | push_rules_enable | push_rules_enable | 52 | 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) ) |
| table | event_backward_extremities | event_backward_extremities | 61 | CREATE TABLE event_backward_extremities( event_id TEXT NOT NULL, room_id TEXT NOT NULL, UNIQUE (event_id, room_id) ) |
| table | room_depth | room_depth | 65 | CREATE TABLE room_depth( room_id TEXT NOT NULL, min_depth INTEGER NOT NULL, UNIQUE (room_id) ) |
| table | event_to_state_groups | event_to_state_groups | 67 | CREATE TABLE event_to_state_groups( event_id TEXT NOT NULL, state_group BIGINT NOT NULL, UNIQUE (event_id) ) |
| table | local_media_repository | local_media_repository | 69 | 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, safe_from_quarantine BOOLEAN NOT NULL DEFAULT 0, authenticated BOOLEAN DEFAULT FALSE NOT NULL, sha256 TEXT, UNIQUE (media_id) ) |
| table | remote_media_cache | remote_media_cache | 71 | 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, authenticated BOOLEAN DEFAULT FALSE NOT NULL, sha256 TEXT, UNIQUE (media_origin, media_id) ) |
| table | redactions | redactions | 73 | CREATE TABLE redactions ( event_id TEXT NOT NULL, redacts TEXT NOT NULL, have_censored BOOL NOT NULL DEFAULT false, received_ts BIGINT, recheck boolean NOT NULL DEFAULT true, UNIQUE (event_id) ) |
| table | room_aliases | room_aliases | 76 | CREATE TABLE room_aliases( room_alias TEXT NOT NULL, room_id TEXT NOT NULL, creator TEXT, UNIQUE (room_alias) ) |
| table | room_alias_servers | room_alias_servers | 79 | CREATE TABLE room_alias_servers( room_alias TEXT NOT NULL, server TEXT NOT NULL ) |
| table | server_keys_json | server_keys_json | 81 | CREATE TABLE "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) ) |
| table | e2e_device_keys_json | e2e_device_keys_json | 83 | 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) ) |
| table | e2e_one_time_keys_json | e2e_one_time_keys_json | 85 | 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) ) |
| table | user_threepids | user_threepids | 88 | CREATE TABLE "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) ) |
| table | room_tags | room_tags | 97 | 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) ) |
| table | room_tags_revisions | room_tags_revisions | 99 | CREATE TABLE room_tags_revisions ( user_id TEXT NOT NULL, room_id TEXT NOT NULL, stream_id BIGINT NOT NULL, instance_name TEXT, CONSTRAINT room_tag_revisions_uniqueness UNIQUE (user_id, room_id) ) |
| table | account_data | account_data | 101 | CREATE TABLE account_data( user_id TEXT NOT NULL, account_data_type TEXT NOT NULL, stream_id BIGINT NOT NULL, content TEXT NOT NULL, instance_name TEXT, CONSTRAINT account_data_uniqueness UNIQUE (user_id, account_data_type) ) |
| table | room_account_data | room_account_data | 103 | 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, instance_name TEXT, CONSTRAINT room_account_data_uniqueness UNIQUE (user_id, room_id, account_data_type) ) |
| table | presence_stream | presence_stream | 115 | 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 , instance_name TEXT) |
| table | push_rules_stream | push_rules_stream | 118 | 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 , instance_name TEXT) |
| table | ex_outlier_stream | ex_outlier_stream | 121 | CREATE TABLE ex_outlier_stream( event_stream_ordering BIGINT PRIMARY KEY NOT NULL, event_id TEXT NOT NULL, state_group BIGINT NOT NULL , instance_name TEXT) |
| table | threepid_guest_access_tokens | threepid_guest_access_tokens | 123 | CREATE TABLE threepid_guest_access_tokens( medium TEXT, address TEXT, guest_access_token TEXT, first_inviter TEXT ) |
| table | open_id_tokens | open_id_tokens | 126 | 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) ) |
| table | pusher_throttle | pusher_throttle | 129 | 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) ) |
| table | event_reports | event_reports | 131 | 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 ) |
| table | appservice_stream_position | appservice_stream_position | 133 | CREATE TABLE appservice_stream_position( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, stream_ordering BIGINT, CHECK (Lock='X') ) |
| table | device_inbox | device_inbox | 136 | CREATE TABLE device_inbox ( user_id TEXT NOT NULL, device_id TEXT NOT NULL, stream_id BIGINT NOT NULL, message_json TEXT NOT NULL , instance_name TEXT) |
| table | device_federation_outbox | device_federation_outbox | 139 | CREATE TABLE device_federation_outbox ( destination TEXT NOT NULL, stream_id BIGINT NOT NULL, queued_ts BIGINT NOT NULL, messages_json TEXT NOT NULL , instance_name TEXT) |
| table | device_federation_inbox | device_federation_inbox | 141 | CREATE TABLE device_federation_inbox ( origin TEXT NOT NULL, message_id TEXT NOT NULL, received_ts BIGINT NOT NULL , instance_name TEXT) |
| table | stream_ordering_to_exterm | stream_ordering_to_exterm | 143 | CREATE TABLE stream_ordering_to_exterm ( stream_ordering BIGINT NOT NULL, room_id TEXT NOT NULL, event_id TEXT NOT NULL ) |
| table | event_auth | event_auth | 146 | CREATE TABLE "event_auth"( event_id TEXT NOT NULL, auth_id TEXT NOT NULL, room_id TEXT NOT NULL ) |
| table | appservice_room_list | appservice_room_list | 149 | CREATE TABLE appservice_room_list( appservice_id TEXT NOT NULL, network_id TEXT NOT NULL, room_id TEXT NOT NULL ) |
| table | federation_stream_position | federation_stream_position | 152 | CREATE TABLE federation_stream_position( type TEXT NOT NULL, stream_id INTEGER NOT NULL , instance_name TEXT NOT NULL DEFAULT 'master') |
| table | device_lists_remote_cache | device_lists_remote_cache | 153 | CREATE TABLE device_lists_remote_cache ( user_id TEXT NOT NULL, device_id TEXT NOT NULL, content TEXT NOT NULL ) |
| table | device_lists_remote_extremeties | device_lists_remote_extremeties | 154 | CREATE TABLE device_lists_remote_extremeties ( user_id TEXT NOT NULL, stream_id TEXT NOT NULL ) |
| table | device_lists_stream | device_lists_stream | 155 | CREATE TABLE device_lists_stream ( stream_id BIGINT NOT NULL, user_id TEXT NOT NULL, device_id TEXT NOT NULL , instance_name TEXT) |
| table | device_lists_outbound_pokes | device_lists_outbound_pokes | 157 | 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 , opentracing_context TEXT, instance_name TEXT) |
| table | event_push_summary_stream_ordering | event_push_summary_stream_ordering | 162 | CREATE TABLE event_push_summary_stream_ordering ( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, stream_ordering BIGINT NOT NULL, CHECK (Lock='X') ) |
| table | pushers | pushers | 164 | CREATE TABLE "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, enabled BOOLEAN, device_id TEXT, instance_name TEXT, UNIQUE (app_id, pushkey, user_name) ) |
| table | ratelimit_override | ratelimit_override | 168 | CREATE TABLE ratelimit_override ( user_id TEXT NOT NULL, messages_per_second BIGINT, burst_count BIGINT ) |
| table | current_state_delta_stream | current_state_delta_stream | 170 | 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 , instance_name TEXT) |
| table | user_directory_stream_pos | user_directory_stream_pos | 172 | CREATE TABLE user_directory_stream_pos ( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, stream_id BIGINT, CHECK (Lock='X') ) |
| table | user_directory_search | user_directory_search | 0 | CREATE VIRTUAL TABLE user_directory_search USING fts4 ( user_id, value ) |
| table | user_directory_search_content | user_directory_search_content | 174 | CREATE TABLE 'user_directory_search_content'(docid INTEGER PRIMARY KEY, 'c0user_id', 'c1value') |
| table | user_directory_search_segments | user_directory_search_segments | 175 | CREATE TABLE 'user_directory_search_segments'(blockid INTEGER PRIMARY KEY, block BLOB) |
| table | user_directory_search_segdir | user_directory_search_segdir | 176 | CREATE TABLE 'user_directory_search_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx)) |
| table | user_directory_search_docsize | user_directory_search_docsize | 178 | CREATE TABLE 'user_directory_search_docsize'(docid INTEGER PRIMARY KEY, size BLOB) |
| table | user_directory_search_stat | user_directory_search_stat | 179 | CREATE TABLE 'user_directory_search_stat'(id INTEGER PRIMARY KEY, value BLOB) |
| table | blocked_rooms | blocked_rooms | 181 | CREATE TABLE blocked_rooms ( room_id TEXT NOT NULL, user_id TEXT NOT NULL ) |
| table | local_media_repository_url_cache | local_media_repository_url_cache | 183 | CREATE TABLE "local_media_repository_url_cache"( url TEXT, response_code INTEGER, etag TEXT, expires_ts BIGINT, og TEXT, media_id TEXT, download_ts BIGINT ) |
| table | deleted_pushers | deleted_pushers | 187 | CREATE TABLE "deleted_pushers" ( stream_id BIGINT NOT NULL, app_id TEXT NOT NULL, pushkey TEXT NOT NULL, user_id TEXT NOT NULL , instance_name TEXT) |
| table | user_directory | user_directory | 189 | CREATE TABLE "user_directory" ( user_id TEXT NOT NULL, room_id TEXT, display_name TEXT, avatar_url TEXT ) |
| table | users_pending_deactivation | users_pending_deactivation | 194 | CREATE TABLE users_pending_deactivation ( user_id TEXT NOT NULL ) |
| table | user_daily_visits | user_daily_visits | 195 | CREATE TABLE user_daily_visits ( user_id TEXT NOT NULL, device_id TEXT, timestamp BIGINT NOT NULL , user_agent TEXT) |
| table | erased_users | erased_users | 198 | CREATE TABLE erased_users ( user_id TEXT NOT NULL ) |
| table | monthly_active_users | monthly_active_users | 200 | CREATE TABLE monthly_active_users ( user_id TEXT NOT NULL, timestamp BIGINT NOT NULL ) |
| table | e2e_room_keys_versions | e2e_room_keys_versions | 203 | CREATE TABLE "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 , etag BIGINT) |
| table | e2e_room_keys | e2e_room_keys | 206 | CREATE TABLE "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 ) |
| table | users_who_share_private_rooms | users_who_share_private_rooms | 207 | CREATE TABLE users_who_share_private_rooms ( user_id TEXT NOT NULL, other_user_id TEXT NOT NULL, room_id TEXT NOT NULL ) |
| table | user_threepid_id_server | user_threepid_id_server | 211 | CREATE TABLE user_threepid_id_server ( user_id TEXT NOT NULL, medium TEXT NOT NULL, address TEXT NOT NULL, id_server TEXT NOT NULL ) |
| table | users_in_public_rooms | users_in_public_rooms | 213 | CREATE TABLE users_in_public_rooms ( user_id TEXT NOT NULL, room_id TEXT NOT NULL ) |
| table | account_validity | account_validity | 215 | CREATE TABLE account_validity ( user_id TEXT PRIMARY KEY, expiration_ts_ms BIGINT NOT NULL, email_sent BOOLEAN NOT NULL, renewal_token TEXT , token_used_ts_ms BIGINT) |
| table | event_relations | event_relations | 217 | CREATE TABLE event_relations ( event_id TEXT NOT NULL, relates_to_id TEXT NOT NULL, relation_type TEXT NOT NULL, aggregation_key TEXT ) |
| table | room_stats_earliest_token | room_stats_earliest_token | 220 | CREATE TABLE room_stats_earliest_token ( room_id TEXT NOT NULL, token BIGINT NOT NULL ) |
| table | threepid_validation_session | threepid_validation_session | 234 | CREATE TABLE threepid_validation_session ( session_id TEXT PRIMARY KEY, medium TEXT NOT NULL, address TEXT NOT NULL, client_secret TEXT NOT NULL, last_send_attempt BIGINT NOT NULL, validated_at BIGINT ) |
| table | threepid_validation_token | threepid_validation_token | 236 | CREATE TABLE threepid_validation_token ( token TEXT PRIMARY KEY, session_id TEXT NOT NULL, next_link TEXT, expires BIGINT NOT NULL ) |
| table | event_expiry | event_expiry | 239 | CREATE TABLE event_expiry ( event_id TEXT PRIMARY KEY, expiry_ts BIGINT NOT NULL ) |
| table | event_labels | event_labels | 242 | CREATE TABLE event_labels ( event_id TEXT, label TEXT, room_id TEXT NOT NULL, topological_ordering BIGINT NOT NULL, PRIMARY KEY(event_id, label) ) |
| table | devices | devices | 246 | CREATE TABLE "devices" ( user_id TEXT NOT NULL, device_id TEXT NOT NULL, display_name TEXT, last_seen BIGINT, ip TEXT, user_agent TEXT, hidden BOOLEAN DEFAULT 0, CONSTRAINT device_uniqueness UNIQUE (user_id, device_id) ) |
| table | room_retention | room_retention | 248 | CREATE TABLE room_retention( room_id TEXT, event_id TEXT, min_lifetime BIGINT, max_lifetime BIGINT, PRIMARY KEY(room_id, event_id) ) |
| table | e2e_cross_signing_keys | e2e_cross_signing_keys | 251 | CREATE TABLE e2e_cross_signing_keys ( user_id TEXT NOT NULL, -- the type of cross-signing key (master, user_signing, or self_signing) keytype TEXT NOT NULL, -- the full key information, as a json-encoded dict keydata TEXT NOT NULL, -- for keeping the keys in order, so that we can fetch the latest one stream_id BIGINT NOT NULL , updatable_without_uia_before_ms bigint DEFAULT NULL, instance_name TEXT) |
| table | e2e_cross_signing_signatures | e2e_cross_signing_signatures | 254 | CREATE TABLE e2e_cross_signing_signatures ( -- user who did the signing user_id TEXT NOT NULL, -- key used to sign key_id TEXT NOT NULL, -- user who was signed target_user_id TEXT NOT NULL, -- device/key that was signed target_device_id TEXT NOT NULL, -- the actual signature signature TEXT NOT NULL ) |
| table | user_signature_stream | user_signature_stream | 255 | CREATE TABLE user_signature_stream ( -- uses the same stream ID as device list stream stream_id BIGINT NOT NULL, -- user who did the signing from_user_id TEXT NOT NULL, -- list of users who were signed, as a JSON array user_ids TEXT NOT NULL , instance_name TEXT) |
| table | stats_incremental_position | stats_incremental_position | 258 | CREATE TABLE stats_incremental_position ( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, -- Makes sure this table only has one row. stream_id BIGINT NOT NULL, CHECK (Lock='X') ) |
| table | room_stats_current | room_stats_current | 260 | CREATE TABLE room_stats_current ( room_id TEXT NOT NULL PRIMARY KEY, -- These are absolute counts current_state_events INT NOT NULL, joined_members INT NOT NULL, invited_members INT NOT NULL, left_members INT NOT NULL, banned_members INT NOT NULL, local_users_in_room INT NOT NULL, -- The maximum delta stream position that this row takes into account. completed_delta_stream_id BIGINT NOT NULL , knocked_members INT) |
| table | user_stats_current | user_stats_current | 262 | CREATE TABLE user_stats_current ( user_id TEXT NOT NULL PRIMARY KEY, joined_rooms BIGINT NOT NULL, -- The maximum delta stream position that this row takes into account. completed_delta_stream_id BIGINT NOT NULL ) |
| table | room_stats_state | room_stats_state | 264 | CREATE TABLE room_stats_state ( room_id TEXT NOT NULL, name TEXT, canonical_alias TEXT, join_rules TEXT, history_visibility TEXT, encryption TEXT, avatar TEXT, guest_access TEXT, is_federatable BOOLEAN, topic TEXT , room_type TEXT) |
| table | user_filters | user_filters | 266 | CREATE TABLE "user_filters" ( user_id TEXT NOT NULL, filter_id BIGINT NOT NULL, filter_json BYTEA NOT NULL , full_user_id TEXT) |
| table | user_external_ids | user_external_ids | 268 | CREATE TABLE user_external_ids ( auth_provider TEXT NOT NULL, external_id TEXT NOT NULL, user_id TEXT NOT NULL, UNIQUE (auth_provider, external_id) ) |
| table | device_lists_remote_resync | device_lists_remote_resync | 272 | CREATE TABLE device_lists_remote_resync ( user_id TEXT NOT NULL, added_ts BIGINT NOT NULL ) |
| table | local_current_membership | local_current_membership | 275 | CREATE TABLE local_current_membership ( room_id TEXT NOT NULL, user_id TEXT NOT NULL, event_id TEXT NOT NULL, membership TEXT NOT NULL , event_stream_ordering BIGINT REFERENCES events(stream_ordering)) |
| table | ui_auth_sessions | ui_auth_sessions | 278 | CREATE TABLE ui_auth_sessions( session_id TEXT NOT NULL, -- The session ID passed to the client. creation_time BIGINT NOT NULL, -- The time this session was created (epoch time in milliseconds). serverdict TEXT NOT NULL, -- A JSON dictionary of arbitrary data added by Synapse. clientdict TEXT NOT NULL, -- A JSON dictionary of arbitrary data from the client. uri TEXT NOT NULL, -- The URI the UI authentication session is using. method TEXT NOT NULL, -- The HTTP method the UI authentication session is using. -- The clientdict, uri, and method make up an tuple that must be immutable -- throughout the lifetime of the UI Auth session. description TEXT NOT NULL, -- A human readable description of the operation which caused the UI Auth flow to occur. UNIQUE (session_id) ) |
| table | ui_auth_sessions_credentials | ui_auth_sessions_credentials | 280 | CREATE TABLE ui_auth_sessions_credentials( session_id TEXT NOT NULL, -- The corresponding UI Auth session. stage_type TEXT NOT NULL, -- The stage type. result TEXT NOT NULL, -- The result of the stage verification, stored as JSON. UNIQUE (session_id, stage_type), FOREIGN KEY (session_id) REFERENCES ui_auth_sessions (session_id) ) |
| table | device_lists_outbound_last_success | device_lists_outbound_last_success | 282 | CREATE TABLE "device_lists_outbound_last_success" ( destination TEXT NOT NULL, user_id TEXT NOT NULL, stream_id BIGINT NOT NULL ) |
| table | local_media_repository_thumbnails | local_media_repository_thumbnails | 284 | 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, thumbnail_method ) ) |
| table | remote_media_cache_thumbnails | remote_media_cache_thumbnails | 288 | 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, thumbnail_method ) ) |
| table | ui_auth_sessions_ips | ui_auth_sessions_ips | 290 | CREATE TABLE ui_auth_sessions_ips( session_id TEXT NOT NULL, ip TEXT NOT NULL, user_agent TEXT NOT NULL, UNIQUE (session_id, ip, user_agent), FOREIGN KEY (session_id) REFERENCES ui_auth_sessions (session_id) ) |
| table | dehydrated_devices | dehydrated_devices | 293 | CREATE TABLE dehydrated_devices( user_id TEXT NOT NULL PRIMARY KEY, device_id TEXT NOT NULL, device_data TEXT NOT NULL -- JSON-encoded client-defined data ) |
| table | e2e_fallback_keys_json | e2e_fallback_keys_json | 295 | CREATE TABLE e2e_fallback_keys_json ( user_id TEXT NOT NULL, -- The user this fallback key is for. device_id TEXT NOT NULL, -- The device this fallback key is for. algorithm TEXT NOT NULL, -- Which algorithm this fallback key is for. key_id TEXT NOT NULL, -- An id for suppressing duplicate uploads. key_json TEXT NOT NULL, -- The key as a JSON blob. used BOOLEAN NOT NULL DEFAULT FALSE, -- Whether the key has been used or not. CONSTRAINT e2e_fallback_keys_json_uniqueness UNIQUE (user_id, device_id, algorithm) ) |
| table | destination_rooms | destination_rooms | 297 | CREATE TABLE destination_rooms ( -- the destination in question. destination TEXT NOT NULL REFERENCES destinations (destination), -- the ID of the room in question room_id TEXT NOT NULL REFERENCES rooms (room_id), -- the stream_ordering of the event stream_ordering BIGINT NOT NULL, PRIMARY KEY (destination, room_id) -- We don't declare a foreign key on stream_ordering here because that'd mean -- we'd need to either maintain an index (expensive) or do a table scan of -- destination_rooms whenever we delete an event (also potentially expensive). -- In addition to that, a foreign key on stream_ordering would be redundant -- as this row doesn't need to refer to a specific event; if the event gets -- deleted then it doesn't affect the validity of the stream_ordering here. ) |
| table | stream_positions | stream_positions | 300 | CREATE TABLE stream_positions ( stream_name TEXT NOT NULL, instance_name TEXT NOT NULL, stream_id BIGINT NOT NULL ) |
| table | access_tokens | access_tokens | 303 | CREATE TABLE "access_tokens" ( id BIGINT PRIMARY KEY, user_id TEXT NOT NULL, device_id TEXT, token TEXT NOT NULL, valid_until_ms BIGINT, puppets_user_id TEXT, last_validated BIGINT, refresh_token_id BIGINT REFERENCES refresh_tokens (id) ON DELETE CASCADE, used BOOLEAN, UNIQUE(token) ) |
| table | ignored_users | ignored_users | 311 | CREATE TABLE ignored_users( ignorer_user_id TEXT NOT NULL, ignored_user_id TEXT NOT NULL ) |
| table | event_auth_chains | event_auth_chains | 314 | CREATE TABLE event_auth_chains ( event_id TEXT PRIMARY KEY, chain_id BIGINT NOT NULL, sequence_number BIGINT NOT NULL ) |
| table | event_auth_chain_links | event_auth_chain_links | 317 | CREATE TABLE event_auth_chain_links ( origin_chain_id BIGINT NOT NULL, origin_sequence_number BIGINT NOT NULL, target_chain_id BIGINT NOT NULL, target_sequence_number BIGINT NOT NULL ) |
| table | event_auth_chain_to_calculate | event_auth_chain_to_calculate | 319 | CREATE TABLE event_auth_chain_to_calculate ( event_id TEXT PRIMARY KEY, room_id TEXT NOT NULL, type TEXT NOT NULL, state_key TEXT NOT NULL ) |
| table | users_to_send_full_presence_to | users_to_send_full_presence_to | 322 | CREATE TABLE users_to_send_full_presence_to( -- The user ID to send full presence to. user_id TEXT PRIMARY KEY, -- A presence stream ID token - the current presence stream token when the row was last upserted. -- If a user calls /sync and this token is part of the update they're to receive, we also include -- full user presence in the response. -- This allows multiple devices for a user to receive full presence whenever they next call /sync. presence_stream_id BIGINT, FOREIGN KEY (user_id) REFERENCES users (name) ) |
| table | refresh_tokens | refresh_tokens | 325 | CREATE TABLE refresh_tokens ( id BIGINT PRIMARY KEY, user_id TEXT NOT NULL, device_id TEXT NOT NULL, token TEXT NOT NULL, -- When consumed, a new refresh token is generated, which is tracked by -- this foreign key next_token_id BIGINT REFERENCES refresh_tokens (id) ON DELETE CASCADE, expiry_ts BIGINT DEFAULT NULL, ultimate_session_expiry_ts BIGINT DEFAULT NULL, UNIQUE(token) ) |
| table | worker_locks | worker_locks | 328 | CREATE TABLE worker_locks ( lock_name TEXT NOT NULL, lock_key TEXT NOT NULL, -- We write the instance name to ease manual debugging, we don't ever read -- from it. -- Note: instance names aren't guarenteed to be unique. instance_name TEXT NOT NULL, -- A random string generated each time an instance takes out a lock. Used by -- the instance to tell whether the lock is still held by it (e.g. in the -- case where the process stalls for a long time the lock may time out and -- be taken out by another instance, at which point the original instance -- can tell it no longer holds the lock as the tokens no longer match). token TEXT NOT NULL, last_renewed_ts BIGINT NOT NULL ) |
| table | federation_inbound_events_staging | federation_inbound_events_staging | 330 | CREATE TABLE federation_inbound_events_staging ( origin TEXT NOT NULL, room_id TEXT NOT NULL, event_id TEXT NOT NULL, received_ts BIGINT NOT NULL, event_json TEXT NOT NULL, internal_metadata TEXT NOT NULL ) |
| table | registration_tokens | registration_tokens | 339 | CREATE TABLE registration_tokens( token TEXT NOT NULL, -- The token that can be used for authentication. uses_allowed INT, -- The total number of times this token can be used. NULL if no limit. pending INT NOT NULL, -- The number of in progress registrations using this token. completed INT NOT NULL, -- The number of times this token has been used to complete a registration. expiry_time BIGINT, -- The latest time this token will be valid (epoch time in milliseconds). NULL if token doesn't expire. UNIQUE (token) ) |
| table | sessions | sessions | 342 | CREATE TABLE sessions( session_type TEXT NOT NULL, -- The unique key for this type of session. session_id TEXT NOT NULL, -- The session ID passed to the client. value TEXT NOT NULL, -- A JSON dictionary to persist. expiry_time_ms BIGINT NOT NULL, -- The time this session will expire (epoch time in milliseconds). UNIQUE (session_type, session_id) ) |
| table | device_auth_providers | device_auth_providers | 351 | CREATE TABLE device_auth_providers ( user_id TEXT NOT NULL, device_id TEXT NOT NULL, auth_provider_id TEXT NOT NULL, auth_provider_session_id TEXT NOT NULL ) |
| table | partial_state_rooms | partial_state_rooms | 355 | CREATE TABLE partial_state_rooms ( room_id TEXT PRIMARY KEY, device_lists_stream_id BIGINT NOT NULL DEFAULT 0, join_event_id TEXT REFERENCES events(event_id), joined_via TEXT, FOREIGN KEY(room_id) REFERENCES rooms(room_id) ) |
| table | partial_state_rooms_servers | partial_state_rooms_servers | 357 | CREATE TABLE partial_state_rooms_servers ( room_id TEXT NOT NULL REFERENCES partial_state_rooms(room_id), server_name TEXT NOT NULL, UNIQUE(room_id, server_name) ) |
| table | partial_state_events | partial_state_events | 359 | CREATE TABLE partial_state_events ( -- the room_id is denormalised for efficient indexing (the canonical source is `events`) room_id TEXT NOT NULL REFERENCES partial_state_rooms(room_id), event_id TEXT NOT NULL REFERENCES events(event_id), UNIQUE(event_id) ) |
| table | device_lists_changes_in_room | device_lists_changes_in_room | 363 | CREATE TABLE device_lists_changes_in_room ( user_id TEXT NOT NULL, device_id TEXT NOT NULL, room_id TEXT NOT NULL, -- This initially matches `device_lists_stream.stream_id`. Note that we -- delete older values from `device_lists_stream`, so we can't use a foreign -- constraint here. -- -- The table will contain rows with the same `stream_id` but different -- `room_id`, as for each device update we store a row per room the user is -- joined to. Therefore `(stream_id, room_id)` gives a unique index. stream_id BIGINT NOT NULL, -- We have a background process which goes through this table and converts -- entries into rows in `device_lists_outbound_pokes`. Once we have processed -- a row, we mark it as such by setting `converted_to_destinations=TRUE`. converted_to_destinations BOOLEAN NOT NULL, opentracing_context TEXT , instance_name TEXT, inserted_ts BIGINT) |
| table | event_edges | event_edges | 366 | CREATE TABLE "event_edges" ( event_id TEXT NOT NULL, prev_event_id TEXT NOT NULL, room_id TEXT NULL, is_state BOOL NOT NULL DEFAULT 0, FOREIGN KEY(event_id) REFERENCES events(event_id) ) |
| table | event_push_summary_last_receipt_stream_id | event_push_summary_last_receipt_stream_id | 369 | CREATE TABLE event_push_summary_last_receipt_stream_id ( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, -- Makes sure this table only has one row. stream_id BIGINT NOT NULL, CHECK (Lock='X') ) |
| table | application_services_state | application_services_state | 371 | CREATE TABLE "application_services_state" ( as_id TEXT PRIMARY KEY NOT NULL, state VARCHAR(5), read_receipt_stream_id BIGINT, presence_stream_id BIGINT, to_device_stream_id BIGINT, device_list_stream_id BIGINT ) |
| table | state_groups | state_groups | 399 | CREATE TABLE state_groups ( id BIGINT PRIMARY KEY, room_id TEXT NOT NULL, event_id TEXT NOT NULL ) |
| table | state_groups_state | state_groups_state | 401 | 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 ) |
| table | state_group_edges | state_group_edges | 402 | CREATE TABLE state_group_edges ( state_group BIGINT NOT NULL, prev_state_group BIGINT NOT NULL ) |
| table | event_failed_pull_attempts | event_failed_pull_attempts | 407 | CREATE TABLE event_failed_pull_attempts( room_id TEXT NOT NULL REFERENCES rooms (room_id), event_id TEXT NOT NULL, num_attempts INT NOT NULL, last_attempt_ts BIGINT NOT NULL, last_cause TEXT NOT NULL, PRIMARY KEY (room_id, event_id) ) |
| table | device_lists_remote_pending | device_lists_remote_pending | 412 | CREATE TABLE device_lists_remote_pending( stream_id BIGINT PRIMARY KEY, user_id TEXT NOT NULL, device_id TEXT NOT NULL , instance_name TEXT) |
| table | receipts_linearized | receipts_linearized | 389 | 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, thread_id TEXT, event_stream_ordering BIGINT, data TEXT NOT NULL, instance_name TEXT, CONSTRAINT receipts_linearized_uniqueness_thread UNIQUE (room_id, receipt_type, user_id, thread_id) ) |
| table | receipts_graph | receipts_graph | 416 | CREATE TABLE "receipts_graph" ( room_id TEXT NOT NULL, receipt_type TEXT NOT NULL, user_id TEXT NOT NULL, event_ids TEXT NOT NULL, thread_id TEXT, data TEXT NOT NULL, CONSTRAINT receipts_graph_uniqueness_thread UNIQUE (room_id, receipt_type, user_id, thread_id) ) |
| table | threads | threads | 376 | CREATE TABLE threads ( room_id TEXT NOT NULL, -- The event ID of the root event in the thread. thread_id TEXT NOT NULL, -- The latest event ID and corresponding topo / stream ordering. latest_event_id TEXT NOT NULL, topological_ordering BIGINT NOT NULL, stream_ordering BIGINT NOT NULL, CONSTRAINT threads_uniqueness UNIQUE (room_id, thread_id) ) |
| table | event_search | event_search | 0 | CREATE VIRTUAL TABLE event_search USING fts4 (tokenize=porter, event_id, room_id, sender, key, value ) |
| table | event_search_content | event_search_content | 91 | CREATE TABLE 'event_search_content'(docid INTEGER PRIMARY KEY, 'c0event_id', 'c1room_id', 'c2sender', 'c3key', 'c4value') |
| table | event_search_segments | event_search_segments | 92 | CREATE TABLE 'event_search_segments'(blockid INTEGER PRIMARY KEY, block BLOB) |
| table | event_search_segdir | event_search_segdir | 93 | CREATE TABLE 'event_search_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx)) |
| table | event_search_docsize | event_search_docsize | 95 | CREATE TABLE 'event_search_docsize'(docid INTEGER PRIMARY KEY, size BLOB) |
| table | event_search_stat | event_search_stat | 96 | CREATE TABLE 'event_search_stat'(id INTEGER PRIMARY KEY, value BLOB) |
| table | login_tokens | login_tokens | 381 | CREATE TABLE login_tokens ( token TEXT PRIMARY KEY, user_id TEXT NOT NULL, expiry_ts BIGINT NOT NULL, used_ts BIGINT, auth_provider_id TEXT, auth_provider_session_id TEXT ) |
| table | device_lists_changes_converted_stream_position | device_lists_changes_converted_stream_position | 418 | CREATE TABLE device_lists_changes_converted_stream_position( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, -- Makes sure this table only has one row. -- The (stream id, room id) of the last row in `device_lists_changes_in_room` that -- has been converted to `device_lists_outbound_pokes`. Rows with a strictly larger -- (stream id, room id) where `converted_to_destinations` is `FALSE` have not been -- converted. stream_id BIGINT NOT NULL, -- `room_id` may be an empty string, which compares less than all valid room IDs. room_id TEXT NOT NULL, instance_name TEXT, CHECK (Lock='X') ) |
| table | un_partial_stated_room_stream | un_partial_stated_room_stream | 420 | CREATE TABLE un_partial_stated_room_stream( -- Position in the stream stream_id BIGINT PRIMARY KEY NOT NULL, -- Which instance wrote this entry. instance_name TEXT NOT NULL, -- Which room has been un-partial-stated. room_id TEXT NOT NULL REFERENCES rooms(room_id) ON DELETE CASCADE ) |
| table | un_partial_stated_event_stream | un_partial_stated_event_stream | 423 | CREATE TABLE un_partial_stated_event_stream( -- Position in the stream stream_id BIGINT PRIMARY KEY NOT NULL, -- Which instance wrote this entry. instance_name TEXT NOT NULL, -- Which event has been un-partial-stated. event_id TEXT NOT NULL REFERENCES events(event_id) ON DELETE CASCADE, -- true iff the `rejected` status of the event changed when it became -- un-partial-stated. rejection_status_changed BOOLEAN NOT NULL ) |
| table | user_directory_stale_remote_users | user_directory_stale_remote_users | 15 | CREATE TABLE user_directory_stale_remote_users ( -- The User ID of the remote user whose profile may be stale. user_id TEXT NOT NULL PRIMARY KEY, -- The server name of the user. user_server_name TEXT NOT NULL, -- The timestamp (in ms) after which we should next try to request the user's -- latest profile. next_try_at_ts BIGINT NOT NULL, -- The number of retries so far. -- 0 means we have not yet attempted to refresh the profile. -- Used for calculating exponential backoff. retry_counter INTEGER NOT NULL ) |
| table | event_txn_id_device_id | event_txn_id_device_id | 429 | CREATE TABLE event_txn_id_device_id ( event_id TEXT NOT NULL, room_id TEXT NOT NULL, user_id TEXT NOT NULL, device_id TEXT NOT NULL, txn_id TEXT NOT NULL, inserted_ts BIGINT NOT NULL, FOREIGN KEY (event_id) REFERENCES events (event_id) ON DELETE CASCADE, FOREIGN KEY (user_id, device_id) REFERENCES devices (user_id, device_id) ON DELETE CASCADE ) |
| table | per_user_experimental_features | per_user_experimental_features | 433 | CREATE TABLE per_user_experimental_features ( -- The User ID to check/set the feature for user_id TEXT NOT NULL, -- Contains features to be enabled/disabled feature TEXT NOT NULL, -- whether the feature is enabled/disabled for a given user, defaults to disabled enabled BOOLEAN DEFAULT FALSE, FOREIGN KEY (user_id) REFERENCES users(name), PRIMARY KEY (user_id, feature) ) |
| table | room_forgetter_stream_pos | room_forgetter_stream_pos | 436 | CREATE TABLE room_forgetter_stream_pos ( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, -- Makes sure this table only has one row. stream_id BIGINT NOT NULL, CHECK (Lock='X') ) |
| table | event_push_actions_staging | event_push_actions_staging | 438 | 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, unread SMALLINT, thread_id TEXT, inserted_ts BIGINT, CONSTRAINT event_push_actions_staging_thread_id CHECK (thread_id is NOT NULL) ) |
| table | event_push_actions | event_push_actions | 439 | 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, unread SMALLINT, thread_id TEXT, CONSTRAINT event_id_user_id_profile_tag_uniqueness UNIQUE (room_id, event_id, user_id, profile_tag), CONSTRAINT event_push_actions_thread_id CHECK (thread_id is NOT NULL) ) |
| table | event_push_summary | event_push_summary | 441 | 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, unread_count BIGINT, last_receipt_stream_ordering BIGINT, thread_id TEXT, CONSTRAINT event_push_summary_thread_id CHECK (thread_id is NOT NULL) ) |
| table | event_forward_extremities | event_forward_extremities | 223 | CREATE TABLE "event_forward_extremities"( event_id TEXT NOT NULL, room_id TEXT NOT NULL, UNIQUE (event_id, room_id), CONSTRAINT event_forward_extremities_event_id FOREIGN KEY (event_id) REFERENCES events (event_id) DEFERRABLE INITIALLY DEFERRED ) |
| table | worker_read_write_locks_mode | worker_read_write_locks_mode | 60 | CREATE TABLE worker_read_write_locks_mode ( lock_name TEXT NOT NULL, lock_key TEXT NOT NULL, -- Whether this lock is in read (false) or write (true) mode write_lock BOOLEAN NOT NULL, -- A token that has currently acquired the lock. We need this so that we can -- add a foreign constraint from this table to `worker_read_write_locks`. token TEXT NOT NULL, -- Add a foreign key constraint to ensure that if a lock is in -- `worker_read_write_locks_mode` then there must be a corresponding row in -- `worker_read_write_locks` (i.e. we don't accidentally end up with a row in -- `worker_read_write_locks_mode` when the lock is not currently acquired). FOREIGN KEY (lock_name, lock_key, token) REFERENCES worker_read_write_locks(lock_name, lock_key, token) DEFERRABLE INITIALLY DEFERRED ) |
| table | worker_read_write_locks | worker_read_write_locks | 442 | CREATE TABLE worker_read_write_locks ( lock_name TEXT NOT NULL, lock_key TEXT NOT NULL, -- We write the instance name to ease manual debugging, we don't ever read -- from it. -- Note: instance names aren't guarenteed to be unique. instance_name TEXT NOT NULL, -- Whether the process has taken out a "read" or a "write" lock. write_lock BOOLEAN NOT NULL, -- A random string generated each time an instance takes out a lock. Used by -- the instance to tell whether the lock is still held by it (e.g. in the -- case where the process stalls for a long time the lock may time out and -- be taken out by another instance, at which point the original instance -- can tell it no longer holds the lock as the tokens no longer match). token TEXT NOT NULL, last_renewed_ts BIGINT NOT NULL, -- This constraint ensures that a given lock has only been acquired in read -- xor write mode, but not both. FOREIGN KEY (lock_name, lock_key, write_lock) REFERENCES worker_read_write_locks_mode (lock_name, lock_key, write_lock) ) |
| table | scheduled_tasks | scheduled_tasks | 446 | CREATE TABLE scheduled_tasks( id TEXT PRIMARY KEY, action TEXT NOT NULL, status TEXT NOT NULL, timestamp BIGINT NOT NULL, resource_id TEXT, params TEXT, result TEXT, error TEXT ) |
| table | timeline_gaps | timeline_gaps | 450 | CREATE TABLE timeline_gaps ( room_id TEXT NOT NULL, instance_name TEXT NOT NULL, stream_ordering BIGINT NOT NULL ) |
| table | room_reports | room_reports | 307 | CREATE TABLE room_reports ( id BIGINT NOT NULL PRIMARY KEY, received_ts BIGINT NOT NULL, room_id TEXT NOT NULL, user_id TEXT NOT NULL, reason TEXT NOT NULL ) |
| table | sliding_sync_joined_rooms_to_recalculate | sliding_sync_joined_rooms_to_recalculate | 309 | CREATE TABLE sliding_sync_joined_rooms_to_recalculate( room_id TEXT NOT NULL REFERENCES rooms(room_id), PRIMARY KEY (room_id) ) |
| table | sliding_sync_joined_rooms | sliding_sync_joined_rooms | 334 | CREATE TABLE sliding_sync_joined_rooms( room_id TEXT NOT NULL REFERENCES rooms(room_id), -- The `stream_ordering` of the most-recent/latest event in the room event_stream_ordering BIGINT NOT NULL REFERENCES events(stream_ordering), -- The `stream_ordering` of the last event according to the `bump_event_types` bump_stamp BIGINT, -- `m.room.create` -> `content.type` (current state) -- -- Useful for the `spaces`/`not_spaces` filter in the Sliding Sync API room_type TEXT, -- `m.room.name` -> `content.name` (current state) -- -- Useful for the room meta data and `room_name_like` filter in the Sliding Sync API room_name TEXT, -- `m.room.encryption` -> `content.algorithm` (current state) -- -- Useful for the `is_encrypted` filter in the Sliding Sync API is_encrypted BOOLEAN DEFAULT FALSE NOT NULL, -- `m.room.tombstone` -> `content.replacement_room` (according to the current state at the -- time of the membership). -- -- Useful for the `include_old_rooms` functionality in the Sliding Sync API tombstone_successor_room_id TEXT, PRIMARY KEY (room_id) ) |
| table | sliding_sync_membership_snapshots | sliding_sync_membership_snapshots | 337 | CREATE TABLE sliding_sync_membership_snapshots( room_id TEXT NOT NULL REFERENCES rooms(room_id), user_id TEXT NOT NULL, -- Useful to be able to tell leaves from kicks (where the `user_id` is different from the `sender`) sender TEXT NOT NULL, membership_event_id TEXT NOT NULL REFERENCES events(event_id), membership TEXT NOT NULL, -- This is an integer just to match `room_memberships` and also means we don't need -- to do any casting. forgotten INTEGER DEFAULT 0 NOT NULL, -- `stream_ordering` of the `membership_event_id` event_stream_ordering BIGINT NOT NULL REFERENCES events(stream_ordering), -- `instance_name` of the worker that persisted the `membership_event_id`. -- Useful for crafting `PersistedEventPosition(...)` event_instance_name TEXT NOT NULL, -- For remote invites/knocks that don't include any stripped state, we want to be -- able to distinguish between a room with `None` as valid value for some state and -- room where the state is completely unknown. Basically, this should be True unless -- no stripped state was provided for a remote invite/knock (False). has_known_state BOOLEAN DEFAULT FALSE NOT NULL, -- `m.room.create` -> `content.type` (according to the current state at the time of -- the membership). -- -- Useful for the `spaces`/`not_spaces` filter in the Sliding Sync API room_type TEXT, -- `m.room.name` -> `content.name` (according to the current state at the time of -- the membership). -- -- Useful for the room meta data and `room_name_like` filter in the Sliding Sync API room_name TEXT, -- `m.room.encryption` -> `content.algorithm` (according to the current state at the -- time of the membership). -- -- Useful for the `is_encrypted` filter in the Sliding Sync API is_encrypted BOOLEAN DEFAULT FALSE NOT NULL, -- `m.room.tombstone` -> `content.replacement_room` (according to the current state at the -- time of the membership). -- -- Useful for the `… |
| table | sliding_sync_connections | sliding_sync_connections | 346 | CREATE TABLE sliding_sync_connections( connection_key INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, -- Generally the device ID, but may be something else for e.g. puppeted accounts. effective_device_id TEXT NOT NULL, conn_id TEXT NOT NULL, created_ts BIGINT NOT NULL , last_used_ts BIGINT) |
| table | sqlite_sequence | sqlite_sequence | 347 | CREATE TABLE sqlite_sequence(name,seq) |
| table | sliding_sync_connection_positions | sliding_sync_connection_positions | 350 | CREATE TABLE sliding_sync_connection_positions( connection_position INTEGER PRIMARY KEY AUTOINCREMENT, connection_key BIGINT NOT NULL REFERENCES sliding_sync_connections(connection_key) ON DELETE CASCADE, created_ts BIGINT NOT NULL ) |
| table | sliding_sync_connection_required_state | sliding_sync_connection_required_state | 453 | CREATE TABLE sliding_sync_connection_required_state( required_state_id INTEGER PRIMARY KEY AUTOINCREMENT, connection_key BIGINT NOT NULL REFERENCES sliding_sync_connections(connection_key) ON DELETE CASCADE, required_state TEXT NOT NULL -- We store this as a json list of event type / state key tuples. ) |
| table | sliding_sync_connection_room_configs | sliding_sync_connection_room_configs | 455 | CREATE TABLE sliding_sync_connection_room_configs( connection_position BIGINT NOT NULL REFERENCES sliding_sync_connection_positions(connection_position) ON DELETE CASCADE, room_id TEXT NOT NULL, timeline_limit BIGINT NOT NULL, required_state_id BIGINT NOT NULL REFERENCES sliding_sync_connection_required_state(required_state_id) ) |
| table | sliding_sync_connection_streams | sliding_sync_connection_streams | 457 | CREATE TABLE sliding_sync_connection_streams( connection_position BIGINT NOT NULL REFERENCES sliding_sync_connection_positions(connection_position) ON DELETE CASCADE, stream TEXT NOT NULL, -- e.g. "events" or "receipts" room_id TEXT NOT NULL, room_status TEXT NOT NULL, -- "live" or "previously", i.e. the `HaveSentRoomFlag` value last_token TEXT -- For "previously" the token for the stream we have sent up to. ) |
| table | delayed_events | delayed_events | 459 | CREATE TABLE delayed_events ( delay_id TEXT NOT NULL, user_localpart TEXT NOT NULL, device_id TEXT, delay BIGINT NOT NULL, send_ts BIGINT NOT NULL, room_id TEXT NOT NULL, event_type TEXT NOT NULL, state_key TEXT, origin_server_ts BIGINT, content TEXT NOT NULL, is_processed BOOLEAN NOT NULL DEFAULT FALSE, sticky_duration_ms BIGINT, PRIMARY KEY (user_localpart, delay_id) ) |
| table | delayed_events_stream_pos | delayed_events_stream_pos | 465 | CREATE TABLE delayed_events_stream_pos ( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, -- Makes sure this table only has one row. stream_id BIGINT NOT NULL, CHECK (Lock='X') ) |
| table | state_groups_pending_deletion | state_groups_pending_deletion | 467 | CREATE TABLE state_groups_pending_deletion ( sequence_number INTEGER PRIMARY KEY AUTOINCREMENT, state_group BIGINT NOT NULL, insertion_ts BIGINT NOT NULL ) |
| table | state_groups_persisting | state_groups_persisting | 470 | CREATE TABLE state_groups_persisting ( state_group BIGINT NOT NULL, instance_name TEXT NOT NULL, PRIMARY KEY (state_group, instance_name) ) |
| table | thread_subscriptions | thread_subscriptions | 473 | CREATE TABLE thread_subscriptions ( stream_id INTEGER NOT NULL PRIMARY KEY, instance_name TEXT NOT NULL, room_id TEXT NOT NULL, event_id TEXT NOT NULL, user_id TEXT NOT NULL, subscribed BOOLEAN NOT NULL, automatic BOOLEAN NOT NULL, unsubscribed_at_stream_ordering BIGINT, unsubscribed_at_topological_ordering BIGINT, CONSTRAINT thread_subscriptions_fk_users FOREIGN KEY (user_id) REFERENCES users(name), CONSTRAINT thread_subscriptions_fk_rooms FOREIGN KEY (room_id) -- When we delete a room, we should already have deleted all the events in that room -- and so there shouldn't be any subscriptions left in that room. -- So the `ON DELETE CASCADE` should be optional, but included anyway for good measure. REFERENCES rooms(room_id) ON DELETE CASCADE, CONSTRAINT thread_subscriptions_fk_events FOREIGN KEY (event_id) REFERENCES events(event_id) ON DELETE CASCADE, -- This order provides a useful index for: -- 1. foreign key constraint on (room_id) -- 2. foreign key constraint on (room_id, event_id) -- 3. finding the user's settings for a specific thread (as well as enforcing uniqueness) UNIQUE (room_id, event_id, user_id) ) |
| table | user_reports | user_reports | 479 | CREATE TABLE user_reports ( id BIGINT NOT NULL PRIMARY KEY, received_ts BIGINT NOT NULL, target_user_id TEXT NOT NULL, user_id TEXT NOT NULL, reason TEXT NOT NULL ) |
| table | room_ban_redactions | room_ban_redactions | 483 | CREATE TABLE room_ban_redactions( room_id text NOT NULL, user_id text NOT NULL, redacting_event_id text NOT NULL, redact_end_ordering bigint DEFAULT NULL, -- stream ordering after which redactions are not applied CONSTRAINT room_ban_redaction_uniqueness UNIQUE (room_id, user_id) ) |
| table | sticky_events | sticky_events | 487 | CREATE TABLE sticky_events ( -- Position in the sticky events stream stream_id INTEGER NOT NULL PRIMARY KEY, -- Name of the worker sending this. (This makes the stream compatible with multiple writers.) instance_name TEXT NOT NULL, -- The event ID of the sticky event itself. event_id TEXT NOT NULL, -- The room ID that the sticky event is in. -- Denormalised for performance. (Safe as it's an immutable property of the event.) room_id TEXT NOT NULL, -- The stream_ordering of the event. -- Denormalised for performance since we will want to sort these by stream_ordering -- when fetching them. (Safe as it's an immutable property of the event.) event_stream_ordering INTEGER NOT NULL UNIQUE, -- Sender of the sticky event. -- Denormalised for performance so we can query only for sticky events originating -- from our homeserver. (Safe as it's an immutable property of the event.) sender TEXT NOT NULL, -- When the sticky event expires, in milliseconds since the Unix epoch. expires_at BIGINT NOT NULL ) |
| table | sliding_sync_connection_lazy_members | sliding_sync_connection_lazy_members | 490 | CREATE TABLE sliding_sync_connection_lazy_members ( connection_key BIGINT NOT NULL REFERENCES sliding_sync_connections(connection_key) ON DELETE CASCADE, connection_position BIGINT REFERENCES sliding_sync_connection_positions(connection_position) ON DELETE CASCADE, room_id TEXT NOT NULL, user_id TEXT NOT NULL, last_seen_ts BIGINT NOT NULL ) |
| table | quarantined_media_changes | quarantined_media_changes | 495 | CREATE TABLE quarantined_media_changes ( -- Position in the quarantined media stream stream_id INTEGER NOT NULL PRIMARY KEY, -- Name of the worker sending this (makes us compatible with multiple writers) instance_name TEXT NOT NULL, -- Media origin. NULL if local media. -- We store the origin and media_id as media is scoped to the origin and are uniquely identified by (origin, media_id). origin TEXT NULL, -- Media ID at the origin. media_id TEXT NOT NULL, -- True if quarantined at this position, false otherwise. quarantined BOOLEAN NOT NULL ) |
| table | msc4242_state_dag_forward_extremities | msc4242_state_dag_forward_extremities | 497 | CREATE TABLE msc4242_state_dag_forward_extremities( -- we always expect the room to exist. If it gets removed, delete fwd extremities. room_id TEXT NOT NULL REFERENCES rooms(room_id) ON DELETE CASCADE, event_id TEXT NOT NULL REFERENCES events(event_id) ON DELETE CASCADE, -- it doesn't make sense to reference the same event multiple times, and this uniqueness -- index is also used to delete events once they are no longer forward extremities. UNIQUE (event_id) ) |
| table | msc4242_state_dag_edges | msc4242_state_dag_edges | 500 | CREATE TABLE msc4242_state_dag_edges( -- Deleting the room deletes the state DAG. room_id TEXT NOT NULL REFERENCES rooms(room_id) ON DELETE CASCADE, -- the event IDs being referenced must exist (hence REFERENCES) and we do not want to accidentally delete -- the event and create a hole in the state DAG. It is not possible for a state -- DAG room to function with an holey DAG, so these events _cannot_ be purged. To purge them, the -- entire room would need to be deleted. event_id TEXT NOT NULL REFERENCES events(event_id), -- one of the `prev_state_events` for this event ID. We must have it since we must have the entire state DAG. -- can be NULL for the create event. prev_state_event_id TEXT REFERENCES events(event_id) ) |
| table | device_lists_changes_in_room_max_pruned_stream_id | device_lists_changes_in_room_max_pruned_stream_id | 502 | CREATE TABLE device_lists_changes_in_room_max_pruned_stream_id ( Lock CHAR(1) NOT NULL DEFAULT 'X' UNIQUE, stream_id BIGINT NOT NULL ) |
| table | profile_updates | profile_updates | 12484 | CREATE TABLE profile_updates ( stream_id BIGINT NOT NULL PRIMARY KEY, instance_name TEXT NOT NULL, -- The full user ID user_id TEXT NOT NULL, -- Profile action that has happened, see ProfileUpdateAction enum. action TEXT NOT NULL, -- JSON array of the profile field names that have been -- added, updated or removed in this update. -- See https://spec.matrix.org/unstable/client-server-api/#profiles -- This is only present if `action` is `update`. -- -- We support multiple field updates at once because it is easy to foresee features -- involving multiple fields (where getting the illusion of a torn write might be harmful), -- as well as synchronisation over federation being likely to lead to multiple field changes -- at once. affected_fields JSONB NULL, -- Unix timestamp (milliseconds) for debugging purposes inserted_ts BIGINT NOT NULL ) |
| table | profile_updates_per_user | profile_updates_per_user | 12645 | CREATE TABLE profile_updates_per_user ( -- Stream ID reference to `profile_updates` stream_id BIGINT NOT NULL REFERENCES profile_updates (stream_id), -- The full user ID of the local user that should receive the profile update. user_id TEXT NOT NULL, -- Unix timestamp (milliseconds). Used to determine when to prune rows (to prevent the table -- from growing indefinitely). inserted_ts BIGINT NOT NULL, PRIMARY KEY (user_id, stream_id) ) |
| table | message_fts | message_fts | 0 | CREATE VIRTUAL TABLE message_fts USING fts5( event_id UNINDEXED, body, tokenize='trigram' ) |
| table | message_fts_data | message_fts_data | 21382 | CREATE TABLE 'message_fts_data'(id INTEGER PRIMARY KEY, block BLOB) |
| table | message_fts_idx | message_fts_idx | 21383 | CREATE TABLE 'message_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID |
| table | message_fts_content | message_fts_content | 21384 | CREATE TABLE 'message_fts_content'(id INTEGER PRIMARY KEY, c0, c1) |
| table | message_fts_docsize | message_fts_docsize | 21385 | CREATE TABLE 'message_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB) |
| table | message_fts_config | message_fts_config | 21387 | CREATE TABLE 'message_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID |