Database Tables
auth_events
Table registering which events authorize which
Table "public.auth_events"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+--------+-----------+----------+---------+---------+-------------+--------------+------------------------------------------------
auth_event_nid | bigint | | not null | | plain | | | The event which authenticates some other event
event_nid | bigint | | not null | | plain | | | The event which is being authenticated
Foreign-key constraints:
"auth_events_auth_event_nid_fkey" FOREIGN KEY (auth_event_nid) REFERENCES events(nid) ON DELETE CASCADE
"auth_events_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
Access method: heap
database_import_progress
Tracks the progress of a homeserver migration import per table, so an interrupted import can be resumed.
Table "public.database_import_progress"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
table_name | text | | not null | | extended | | | The record type / table being imported (e.g. 'users').
cursor | text | | not null | | extended | | | The sort key of the last successfully imported row. Passed back to the exporter (under the query parameter it advertises in the export header's 'cursor_param') to resume the export from this point.
imported_count | bigint | | not null | 0 | plain | | | The number of rows imported so far for this table.
completed | boolean | | not null | false | plain | | | Whether the import for this table has finished (the exporter's trailer record was received).
updated_at | timestamp with time zone | | not null | now() | plain | | | When this row was last updated.
Indexes:
"database_import_progress_pkey" PRIMARY KEY, btree (table_name)
Access method: heap
devices
Local Devices
Table "public.devices"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------+---------+-----------+----------+----------------------------------------------+----------+-------------+--------------+--------------------------------------------------------------------------------------------
nid | bigint | | not null | generated by default as identity | plain | | | Surrogate key for the device
user_nid | integer | | not null | | plain | | | User to which this device belongs
device_id | text | | not null | | extended | | | ID for the device, according to the matrix spec
idp_id | text | | | | extended | | | ID for the device when talking to the identity provider
revoked_token_tag | bigint | | not null | 0 | plain | | | All token tags less than this count as revoked
stream_id | bigint | | not null | locking_nextval('user_keys_streamids'::text) | plain | | | The stream ID of the device creation. Used to compute the `device_lists` of sync requests.
Indexes:
"devices_pkey" PRIMARY KEY, btree (nid)
"devices_stream_id_key" UNIQUE CONSTRAINT, btree (stream_id)
"devices_user_nid_device_id_key" UNIQUE CONSTRAINT, btree (user_nid, device_id)
"devices_user_nid_idp_id_key" UNIQUE CONSTRAINT, btree (user_nid, idp_id)
Foreign-key constraints:
"devices_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) DEFERRABLE
Referenced by:
TABLE "event_transactions" CONSTRAINT "event_transactions_sender_device_nid_fkey" FOREIGN KEY (sender_device_nid) REFERENCES devices(nid) ON DELETE CASCADE
TABLE "fallback_keys" CONSTRAINT "fallback_keys_device_nid_fkey" FOREIGN KEY (device_nid) REFERENCES devices(nid) ON DELETE CASCADE
TABLE "filters" CONSTRAINT "filters_device_nid_fkey" FOREIGN KEY (device_nid) REFERENCES devices(nid) ON DELETE CASCADE
TABLE "one_time_keys" CONSTRAINT "one_time_keys_device_nid_fkey" FOREIGN KEY (device_nid) REFERENCES devices(nid) ON DELETE CASCADE
TABLE "syncv4_connections" CONSTRAINT "syncv4_connections_device_nid_fkey" FOREIGN KEY (device_nid) REFERENCES devices(nid) ON DELETE CASCADE
TABLE "to_device_messages" CONSTRAINT "to_device_messages_device_nid_fkey" FOREIGN KEY (device_nid) REFERENCES devices(nid) ON DELETE CASCADE
TABLE "user_keys" CONSTRAINT "user_keys_device_nid_fkey" FOREIGN KEY (device_nid) REFERENCES devices(nid) ON DELETE CASCADE
Access method: heap
event_ordering
Table which stores a linear ordering of events. See also https://bundesmessenger.gitlab.io/backend/neo/arminius/arminius/rustdoc/arminius_lib_db_room/ordering/index.html
Table "public.event_ordering"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+----------+-----------+----------+---------+----------+-------------+--------------+--------------------------------------------------------------------------------------------
event_nid | bigint | | not null | | plain | | | The event which occupies a position in our ordering
room_nid | integer | | not null | | plain | | | The room for which our event is ordered. Should always match the event
ordering | bigint[] | | not null | | extended | | | The actual ordering. Ordering of events is achieved by sorting this list lexicographically
Indexes:
"event_ordering_event_nid_key" UNIQUE CONSTRAINT, btree (event_nid)
"event_ordering_room_nid_ordering_key" UNIQUE CONSTRAINT, btree (room_nid, ordering)
"linear_ordering" btree (ordering)
Check constraints:
"event_ordering_ordering_check" CHECK (array_position(ordering, NULL::bigint) IS NULL)
Foreign-key constraints:
"event_ordering_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
"event_ordering_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
Access method: heap
event_transactions
Table that stores references to events which were sent as part of a call to PUT /_matrix/client/v3/rooms/{roomId}/send/{eventType}/{txnId}
Table "public.event_transactions"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------+------------------------+-----------+----------+---------+----------+-------------+--------------+-----------------------------------------------------------------------------------------------------------------------------------
event_nid | bigint | | not null | | plain | | | The event which was inserted as part of the transaction
sender_device_nid | bigint | | not null | | plain | | | The device which sent the message. Used for de-duplication
txn_id | character varying(255) | | not null | | extended | | | User-defined transaction ID used for de-duplication. See https://spec.matrix.org/v1.14/client-server-api/#transaction-identifiers
room_nid | integer | | not null | | plain | | | The room the event was sent to. Part of the de-duplication key
event_type | text | | not null | | extended | | | The type of the event, as in the `/send/{eventType}` path. Part of the de-duplication key
Indexes:
"event_transactions_pkey" PRIMARY KEY, btree (event_nid)
"event_transactions_dedup_key" UNIQUE, btree (sender_device_nid, room_nid, event_type, txn_id)
Foreign-key constraints:
"event_transactions_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
"event_transactions_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
"event_transactions_sender_device_nid_fkey" FOREIGN KEY (sender_device_nid) REFERENCES devices(nid) ON DELETE CASCADE
Access method: heap
events
Events table for storing all event data
Table "public.events"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+---------+-----------+----------+-----------------------------------------------------+----------+-------------+--------------+--------------------------------------------------------------------------------------------------------
nid | bigint | | not null | generated by default as identity | plain | | | Surrogate key for events
event_id | text | | not null | | extended | | | The event id per the matrix spec
room_nid | integer | | not null | | plain | | | Room to which the event belongs
event_json | json | | not null | | extended | | | The event contents, in federation format
depth | bigint | | not null | | plain | | | For the first event in a room, should be 1. For all following events, the max depth of an ancestor + 1
received_ts | bigint | | not null | (EXTRACT(epoch FROM now()) * 1000::numeric)::bigint | plain | | | Server receipt time in milliseconds
stream_id | bigint | | not null | locking_nextval('eventids'::text) | plain | | | Position in the /sync stream
Indexes:
"events_pkey" PRIMARY KEY, btree (nid)
"events_room_member_state_key" btree (room_nid, (event_json ->> 'state_key'::text)) WHERE (event_json ->> 'type'::text) = 'm.room.member'::text
"events_room_nid_event_id_key" UNIQUE CONSTRAINT, btree (room_nid, event_id)
"events_room_stream" btree (room_nid, stream_id)
"events_stream_id_key" UNIQUE CONSTRAINT, btree (stream_id)
Foreign-key constraints:
"events_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
Referenced by:
TABLE "auth_events" CONSTRAINT "auth_events_auth_event_nid_fkey" FOREIGN KEY (auth_event_nid) REFERENCES events(nid) ON DELETE CASCADE
TABLE "auth_events" CONSTRAINT "auth_events_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
TABLE "event_ordering" CONSTRAINT "event_ordering_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
TABLE "event_transactions" CONSTRAINT "event_transactions_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
TABLE "events_state_snapshots" CONSTRAINT "events_state_snapshots_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
TABLE "leaf_events" CONSTRAINT "leaf_events_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
TABLE "previous_events" CONSTRAINT "previous_events_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
TABLE "previous_events" CONSTRAINT "previous_events_previous_event_nid_fkey" FOREIGN KEY (previous_event_nid) REFERENCES events(nid) ON DELETE CASCADE
TABLE "read_receipts" CONSTRAINT "read_receipts_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
TABLE "state_snapshot_state_events" CONSTRAINT "state_snapshot_state_events_state_event_nid_fkey" FOREIGN KEY (state_event_nid) REFERENCES events(nid) ON DELETE CASCADE
TABLE "state_snapshots" CONSTRAINT "state_snapshots_history_visibility_event_nid_fkey" FOREIGN KEY (history_visibility_event_nid) REFERENCES events(nid) ON DELETE SET NULL
TABLE "state_snapshots" CONSTRAINT "state_snapshots_server_acl_event_nid_fkey" FOREIGN KEY (server_acl_event_nid) REFERENCES events(nid) ON DELETE SET NULL
TABLE "verification_metadata" CONSTRAINT "verification_metadata_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
Triggers:
require_room_lock BEFORE INSERT ON events FOR EACH ROW EXECUTE FUNCTION assert_room_locked()
Access method: heap
events_state_snapshots
Table storing relations between events, and the state snapshots which store state events that apply to them
Table "public.events_state_snapshots"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+--------+-----------+----------+---------+---------+-------------+--------------+-------------------------------------------------------
event_nid | bigint | | not null | | plain | | | The state event, to which some state snapshot applies
state_snapshot_nid | bigint | | not null | | plain | | | Some state snapshot, which applies to a state event
Indexes:
"events_state_snapshots_pkey" PRIMARY KEY, btree (event_nid)
Foreign-key constraints:
"events_state_snapshots_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
"events_state_snapshots_state_snapshot_nid_fkey" FOREIGN KEY (state_snapshot_nid) REFERENCES state_snapshots(nid) ON DELETE CASCADE
Access method: heap
fallback_keys
Fallback keys for a user
Table "public.fallback_keys"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------+---------+-----------+----------+----------------------------------+----------+-------------+--------------+------------------------------------------------------
nid | integer | | not null | generated by default as identity | plain | | | Surrogate key for the key
key_algorithm | text | | not null | | extended | | | The algorithm used in the key
key_name | text | | not null | | extended | | | A name for the key
device_nid | integer | | not null | | plain | | | The device which owns the private key
key_object | jsonb | | not null | | extended | | | The public key part of the key
used | boolean | | not null | false | plain | | | Whether or not the fallback has already been claimed
Indexes:
"fallback_keys_pkey" PRIMARY KEY, btree (nid)
"fallback_key_per_device" UNIQUE CONSTRAINT, btree (device_nid, key_algorithm)
Foreign-key constraints:
"fallback_keys_device_nid_fkey" FOREIGN KEY (device_nid) REFERENCES devices(nid) ON DELETE CASCADE
Access method: heap
federated_invites
Invites received over federation, for rooms we are not active in
Table "public.federated_invites"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+----------+-----------+----------+----------------------------------------+----------+-------------+--------------+---------------------------------------------------------------------------------------------------
nid | bigint | | not null | generated by default as identity | plain | | | Surrogate key for the invite
stream_id | bigint | | not null | locking_nextval('invited_rooms'::text) | plain | | | Stream ID for the federated_invite. See the sync docs in the external documentation for more info
user_nid | integer | | not null | | plain | | | The user that has been invited to a room
room_id | text | | not null | | extended | | | The matrix room ID of the room being invited to
state_events | bigint[] | | | | extended | | | List of state events in the stripped state, giving a preview of the room
Indexes:
"federated_invites_pkey" PRIMARY KEY, btree (nid)
"federated_invites_stream_id_key" UNIQUE CONSTRAINT, btree (stream_id)
Foreign-key constraints:
"federated_invites_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid)
Access method: heap
federated_knocks
Rooms we have knocked on over federation
Table "public.federated_knocks"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+----------+-----------+----------+----------------------------------------+----------+-------------+--------------+---------------------------------------------------------------------------------------------------
nid | bigint | | not null | generated by default as identity | plain | | | Surrogate key for the knock
stream_id | bigint | | not null | locking_nextval('invited_rooms'::text) | plain | | | Stream ID for the federated_invite. See the sync docs in the external documentation for more info
user_nid | integer | | not null | | plain | | | The user that has been invited to a room
room_id | text | | not null | | extended | | | The matrix room ID of the room being invited to
state_events | bigint[] | | | | extended | | | List of state events in the stripped state, giving a preview of the room
Indexes:
"federated_knocks_pkey" PRIMARY KEY, btree (nid)
"federated_knocks_stream_id_key" UNIQUE CONSTRAINT, btree (stream_id)
Foreign-key constraints:
"federated_knocks_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid)
Access method: heap
federated_to_device_transactions
Transaction tracker for incoming to-device EDUs
Table "public.federated_to_device_transactions"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+------+-----------+----------+---------+----------+-------------+--------------+---------------------------------------------
origin | text | | not null | | extended | | | The homeserver that sent us the transaction
txnid | text | | not null | | extended | | | The actual transaction ID.
Indexes:
"federated_to_device_transactions_origin_txnid_key" UNIQUE CONSTRAINT, btree (origin, txnid)
Access method: heap
filters
Filters for endpoints that user have uploaded.
Table "public.filters"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+----------------------+-----------+----------+-----------------------------+----------+-------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
id | uuid | | not null | | plain | | | The primary key of the filter used to retrieve the filter by the user that created it. This is stored as UUID, but it is generated and used as ULID value.
user_nid | integer | | not null | | plain | | | The user that has created the filter. Only this user should have access to the filter.
device_nid | bigint | | not null | | plain | | | The filter for the device that created it. The life time of the filter is bound to the existence of this device.
account_data | event_filter | | | | extended | | | Filters for events related to account data that are not part of the rooms to include
presence | event_filter | | | | extended | | | Filters for the updates to presence to include
room | room_filter | | | | extended | | | Filters to be applied to room data
event_format | event_format_enum | | not null | 'client'::event_format_enum | plain | | | The format for the events. "federation" should send the events in a raw event as received, while the "client" should send it in a format suitable for the clients.
event_fields | dot_separated_path[] | | | | extended | | | Address the fields of the events to include.
Indexes:
"filters_pkey" PRIMARY KEY, btree (id)
"idx_filters_dedup" UNIQUE, btree (user_nid, device_nid, generate_filter_hash(account_data, presence, room, event_format, event_fields))
Foreign-key constraints:
"filters_device_nid_fkey" FOREIGN KEY (device_nid) REFERENCES devices(nid) ON DELETE CASCADE
"filters_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) ON DELETE CASCADE
Access method: heap
forgotten_rooms
List of rooms that users have marked as “forgotten”. If all our local users have forgotten a room, we should delete it.
Table "public.forgotten_rooms"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------+---------+-----------+----------+---------+---------+-------------+--------------+-----------------------------------------------
room_nid | integer | | not null | | plain | | | The room that has been marked as forgotten
user_nid | integer | | not null | | plain | | | The user which has marked a room as forgotten
Indexes:
"forgotten_rooms_room_nid_user_nid_key" UNIQUE CONSTRAINT, btree (room_nid, user_nid)
Foreign-key constraints:
"forgotten_rooms_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
"forgotten_rooms_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) ON DELETE CASCADE
Access method: heap
global_account_data
Global account data for each user
Table "public.global_account_data"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+---------+-----------+----------+-------------------------------------------------+----------+-------------+--------------+----------------------------------------------------------------------------------------------------------------------------------
user_nid | integer | | not null | | plain | | | The user to which this account data belongs
data_type | text | | not null | | extended | | | Type of the tag. See https://spec.matrix.org/v1.14/client-server-api/#client-behaviour-17
data | jsonb | | not null | | extended | | | A JSON blob the user stores
stream_id | bigint | | not null | locking_nextval('account_data_streamids'::text) | plain | | | Stream ID of the user's account data. See https://bundesmessenger.gitlab.io/backend/neo/arminius/arminius/architecture/sync.html
Indexes:
"global_account_data_pkey" PRIMARY KEY, btree (user_nid, data_type)
"global_account_data_stream_id_key" UNIQUE CONSTRAINT, btree (stream_id)
Foreign-key constraints:
"global_account_data_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) ON DELETE CASCADE
Access method: heap
leaf_events
Leaves of the room event DAG
Table "public.leaf_events"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+---------+-----------+----------+---------+---------+-------------+--------------+--------------------------------------
room_nid | integer | | not null | | plain | | | The room to which an event is a leaf
event_nid | bigint | | not null | | plain | | | The event which is a leaf
Indexes:
"leaf_events_room_nid_event_nid_key" UNIQUE CONSTRAINT, btree (room_nid, event_nid)
Foreign-key constraints:
"leaf_events_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
"leaf_events_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
Triggers:
require_room_lock BEFORE INSERT ON leaf_events FOR EACH ROW EXECUTE FUNCTION assert_room_locked()
Access method: heap
local_signatures
Cross-signing signatures for local keys
Table "public.local_signatures"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------+----------+-----------+----------+----------------------------------------------+----------+-------------+--------------+----------------------------------------------------------------------------------------------
signing_user_nid | integer | | not null | | plain | | | Reference to the user who issued the signature
signing_key_type | key_type | | not null | | plain | | | Type of key that issued the signature. See the `KEY_TYPE` enum in the `user_keys` migration.
signing_key_id | text | | not null | | extended | | | Key ID of the key that issued the signature
signed_key_nid | bigint | | not null | | plain | | | Link to the signed key. Will delete the signature on deletion.
signature | bytea | | not null | | extended | | | The actual signature
stream_id | bigint | | not null | locking_nextval('user_keys_streamids'::text) | plain | | | The stream ID to tell when the key was inserted. This reuses the keys streamids.
Indexes:
"local_signatures_stream_id_key" UNIQUE CONSTRAINT, btree (stream_id)
"unique_signature" UNIQUE CONSTRAINT, btree (signing_user_nid, signing_key_id, signed_key_nid)
Foreign-key constraints:
"local_signatures_signed_key_nid_fkey" FOREIGN KEY (signed_key_nid) REFERENCES user_keys(nid) ON DELETE CASCADE
"local_signatures_signing_user_nid_fkey" FOREIGN KEY (signing_user_nid) REFERENCES users(nid) ON DELETE CASCADE
Access method: heap
one_time_keys
To-be-issued one time keys for a user
Table "public.one_time_keys"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------------+---------+-----------+----------+----------------------------------+----------+-------------+--------------+----------------------------------------------------------------------------------------
nid | integer | | not null | generated by default as identity | plain | | | Surrogate key for the OTK
key_algorithm | text | | not null | | extended | | | The algorithm used in the OTK
key_name | text | | not null | | extended | | | A name for the OTK
device_nid | integer | | not null | | plain | | | The device which owns the private key
key_object | jsonb | | not null | | extended | | | The public key part of the OTK
upload_revision | integer | | not null | generated always as identity | plain | | | An integer used to order the claimed key by sending back the oldest uploaded one first
Indexes:
"one_time_keys_pkey" PRIMARY KEY, btree (nid)
"user_device_key_unique" UNIQUE CONSTRAINT, btree (device_nid, key_object)
Foreign-key constraints:
"one_time_keys_device_nid_fkey" FOREIGN KEY (device_nid) REFERENCES devices(nid) ON DELETE CASCADE
Access method: heap
presence
User presence information
Table "public.presence"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+--------------------------------------------------------------------------------------------
user_nid | integer | | not null | | plain | | | The user for which we are recording presence
last_active | timestamp with time zone | | not null | now() | plain | | | The time at which the user was last active
status | character varying(255) | | | | extended | | | A custom status the user can set
manual_presence | character varying(100) | | | | extended | | | Users can set their presence manually with PUT /_matrix/client/v3/presence/{userId}/status
Indexes:
"presence_pkey" PRIMARY KEY, btree (user_nid)
Foreign-key constraints:
"presence_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid)
Access method: heap
previous_events
Table registering which events precede which
Table "public.previous_events"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+--------+-----------+----------+---------+---------+-------------+--------------+-----------------------------------------------------------------------------
previous_event_nid | bigint | | not null | | plain | | | The event which precedes the event referenced by `event_nid`
event_nid | bigint | | not null | | plain | | | The event which predecessor is the event referenced by `previous_event_nid`
Foreign-key constraints:
"previous_events_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
"previous_events_previous_event_nid_fkey" FOREIGN KEY (previous_event_nid) REFERENCES events(nid) ON DELETE CASCADE
Access method: heap
read_receipts
Users room read receipts
Table "public.read_receipts"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+--------------------------+-----------+----------+--------------------------------------------------+----------+-------------+--------------+----------------------------------------------------------------------------------------------------------------------------------
nid | integer | | not null | generated by default as identity | plain | | | Surrogate key for the read receipt
room_nid | integer | | not null | | plain | | | The room of the read receipt
user_id | text | | not null | | extended | | | The user owning the read receipt
event_nid | integer | | not null | | plain | | | The event ID the receipt acknowledges up to
receipt_type | receipt_type | | not null | | plain | | | The type of read receipt
thread | text | | | | extended | | | Optional. The thread the receipt applies to
ts | timestamp with time zone | | not null | | plain | | | The timestamp the receipt was sent at
stream_id | bigint | | not null | locking_nextval('read_receipts_streamids'::text) | plain | | | Stream ID of the users read receipts. See https://bundesmessenger.gitlab.io/backend/neo/arminius/arminius/architecture/sync.html
Indexes:
"read_receipts_pkey" PRIMARY KEY, btree (nid)
"read_receipts_room_nid_user_id_receipt_type_thread_key" UNIQUE CONSTRAINT, btree (room_nid, user_id, receipt_type, thread) NULLS NOT DISTINCT
"read_receipts_stream_id_key" UNIQUE CONSTRAINT, btree (stream_id)
Foreign-key constraints:
"read_receipts_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
"read_receipts_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
Access method: heap
room_account_data
Room specific account data for each user
Table "public.room_account_data"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+---------+-----------+----------+-------------------------------------------------+----------+-------------+--------------+----------------------------------------------------------------------------------------------------------------------------------
user_nid | integer | | not null | | plain | | | The user to which this room-specific account data belongs
room_nid | integer | | not null | | plain | | | The room to which this room-specific account data belongs
data_type | text | | not null | | extended | | | See documentation for the table `global_account_data`
data | jsonb | | not null | | extended | | | An arbitrary JSON blob, the actual uploaded data
stream_id | bigint | | not null | locking_nextval('account_data_streamids'::text) | plain | | | Stream ID of the user's account data. See https://bundesmessenger.gitlab.io/backend/neo/arminius/arminius/architecture/sync.html
Indexes:
"room_account_data_pkey" PRIMARY KEY, btree (user_nid, room_nid, data_type)
"room_account_data_stream_id_key" UNIQUE CONSTRAINT, btree (stream_id)
Foreign-key constraints:
"room_account_data_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
"room_account_data_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) ON DELETE CASCADE
Access method: heap
room_keys
Room keys stored in backups
Table "public.room_keys"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------------------+---------+-----------+----------+---------+----------+-------------+--------------+----------------------------------------
room_keys_version_nid | integer | | not null | | plain | | | Link to the backup keys are part of
room_nid | integer | | not null | | plain | | | Link to the room the keys belong to
session_id | text | | not null | | extended | | | Name of the session the keys belong to
backup_data | jsonb | | not null | | extended | | | Actual room keys backup content
Indexes:
"room_keys_room_keys_version_nid_room_nid_session_id_key" UNIQUE CONSTRAINT, btree (room_keys_version_nid, room_nid, session_id)
Foreign-key constraints:
"room_keys_room_keys_version_nid_fkey" FOREIGN KEY (room_keys_version_nid) REFERENCES room_keys_versions(nid) ON DELETE CASCADE
"room_keys_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
Access method: heap
room_keys_versions
Room keys backups
Table "public.room_keys_versions"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+---------+-----------+----------+----------------------------------+----------+-------------+--------------+-------------------------------------------------
nid | integer | | not null | generated by default as identity | plain | | | Surrogate key for room_keys_versions
user_nid | integer | | | | plain | | | User owning the room keys backup
version | bigint | | not null | | plain | | | Version of the room keys backup
auth_data | jsonb | | not null | | extended | | | Algorithm dependent data of the backup
etag | bigint | | not null | 1 | plain | | | Unique integer identifying the backup's version
Indexes:
"room_keys_versions_pkey" PRIMARY KEY, btree (nid)
"room_keys_versions_user_nid_version_key" UNIQUE CONSTRAINT, btree (user_nid, version)
Foreign-key constraints:
"room_keys_versions_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) ON DELETE CASCADE
Referenced by:
TABLE "room_keys" CONSTRAINT "room_keys_room_keys_version_nid_fkey" FOREIGN KEY (room_keys_version_nid) REFERENCES room_keys_versions(nid) ON DELETE CASCADE
Access method: heap
room_state_snapshots
The state of a room, when all the latest_events have been resolved
Table "public.room_state_snapshots"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+---------+-----------+----------+---------+---------+-------------+--------------+----------------------------------------------
room_nid | integer | | not null | | plain | | | The room for which we are storing the state
state_snapshot_nid | bigint | | not null | | plain | | | The state snapshot which applies to the room
Indexes:
"room_state_snapshots_pkey" PRIMARY KEY, btree (room_nid)
Foreign-key constraints:
"room_state_snapshots_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
"room_state_snapshots_state_snapshot_nid_fkey" FOREIGN KEY (state_snapshot_nid) REFERENCES state_snapshots(nid) ON DELETE CASCADE
Access method: heap
rooms
Rooms known to server
Table "public.rooms"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------+---------+-----------+----------+----------------------------------+----------+-------------+--------------+-----------------------------------------------------
nid | integer | | not null | generated by default as identity | plain | | | Surrogate key for the room
room_id | text | C | not null | | extended | | | Room ID, as used in the Matrix protocol
room_version | text | | not null | | extended | | | Room version, as defined in the Matrix protocol
public | boolean | | not null | false | plain | | | Whether to show the room in the published room list
Indexes:
"rooms_pkey" PRIMARY KEY, btree (nid)
"rooms_room_id_key" UNIQUE CONSTRAINT, btree (room_id)
Referenced by:
TABLE "event_ordering" CONSTRAINT "event_ordering_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
TABLE "event_transactions" CONSTRAINT "event_transactions_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
TABLE "events" CONSTRAINT "events_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
TABLE "forgotten_rooms" CONSTRAINT "forgotten_rooms_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
TABLE "leaf_events" CONSTRAINT "leaf_events_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
TABLE "read_receipts" CONSTRAINT "read_receipts_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
TABLE "room_account_data" CONSTRAINT "room_account_data_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
TABLE "room_keys" CONSTRAINT "room_keys_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
TABLE "room_state_snapshots" CONSTRAINT "room_state_snapshots_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid) ON DELETE CASCADE
TABLE "syncv4_room_streams" CONSTRAINT "syncv4_room_streams_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid)
Access method: heap
scim_sync_token
Table used to store the single sync token
Table "public.scim_sync_token"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------+--------+-----------+----------+---------+----------+-------------+--------------+----------------------------------------------------------
nid | bigint | | not null | 0 | plain | | | ID (always 0, because there is only a single sync token)
sync_token | text | | not null | | extended | | | the latest sync token returned by the SCIM server
Indexes:
"scim_sync_token_pkey" PRIMARY KEY, btree (nid)
Check constraints:
"scim_sync_token_nid_check" CHECK (nid = 0)
Access method: heap
server_key_responses
Most recent responses to key queries on other servers. Used for notary key queries
Table "public.server_key_responses"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+------+-----------+----------+---------+----------+-------------+--------------+-------------------------------------------------------
server_name | text | | not null | | extended | | | The name of the server from which we got the response
response | text | | not null | | extended | | | The actual signed canonical JSON response
Indexes:
"server_key_responses_pkey" PRIMARY KEY, btree (server_name)
Access method: heap
server_private_keys
Keys used by this homeserver
Table "public.server_private_keys"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+------------------------------------------------------------------------------
key_version | text | | not null | | extended | | | Alphanumeric string uniquely identifying this key for the homeserver
key_pair | bytea | | | | extended | | | The key pair as a DER-encoded PKCS#8 v2 document w. public key, used by ruma
verify_key | bytea | | not null | | extended | | | The public key
created_at | timestamp with time zone | | not null | now() | plain | | | The time at which the key was created
expired_at | timestamp with time zone | | | | plain | | | The time at which the key expired
Indexes:
"server_private_keys_pkey" PRIMARY KEY, btree (key_version)
Check constraints:
"nonexpired_keys_available" CHECK (expired_at IS NOT NULL OR key_pair IS NOT NULL)
Access method: heap
server_public_keys
Public keys for other homeservers
Table "public.server_public_keys"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+--------------------------------------
server_name | text | | not null | | extended | | | The server which the keys belongs to
key_algorithm | text | | not null | | extended | | | The algorithm used for the key
key_name | text | | not null | | extended | | | The name used for the key
public_key | bytea | | not null | | extended | | | The actual key
expires_at | timestamp with time zone | | not null | | plain | | | The time at which the key expires
Indexes:
"server_public_keys_pkey" PRIMARY KEY, btree (server_name, key_name, key_algorithm)
Access method: heap
state_snapshot_state_events
Table storing relations between state snapshots, and the state events that make them up
Table "public.state_snapshot_state_events"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------------------+--------+-----------+----------+---------+---------+-------------+--------------+----------------------------------------------------
state_snapshot_nid | bigint | | not null | | plain | | | The state snapshot which contains some state event
state_event_nid | bigint | | not null | | plain | | | A state event which is part of some snapshot
Indexes:
"state_snapshot_state_events_state_snapshot_nid_state_event__key" UNIQUE CONSTRAINT, btree (state_snapshot_nid, state_event_nid)
Foreign-key constraints:
"state_snapshot_state_events_state_event_nid_fkey" FOREIGN KEY (state_event_nid) REFERENCES events(nid) ON DELETE CASCADE
"state_snapshot_state_events_state_snapshot_nid_fkey" FOREIGN KEY (state_snapshot_nid) REFERENCES state_snapshots(nid) ON DELETE CASCADE
Triggers:
maintain_state_snapshot_pointers AFTER INSERT OR DELETE ON state_snapshot_state_events FOR EACH ROW EXECUTE FUNCTION maintain_state_snapshot_pointers()
Access method: heap
state_snapshots
Surrogate table to store collections of state events. Used to store state for events and rooms
Table "public.state_snapshots"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------------------+--------+-----------+----------+----------------------------------+---------+-------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
nid | bigint | | not null | generated by default as identity | plain | | | The numerical ID of a event state
server_acl_event_nid | bigint | | | | plain | | | The effective `m.room.server_acl` event in this snapshot, if any. `m.room.server_acl` always has an empty state key, as per the spec (v1.19/client-server/10.32.2)
history_visibility_event_nid | bigint | | | | plain | | | The effective `m.room.history_visibility` event in this snapshot, if any
Indexes:
"state_snapshots_pkey" PRIMARY KEY, btree (nid)
"state_snapshots_history_visibility_event_nid" btree (history_visibility_event_nid) WHERE history_visibility_event_nid IS NOT NULL
"state_snapshots_server_acl_event_nid" btree (server_acl_event_nid) WHERE server_acl_event_nid IS NOT NULL
Foreign-key constraints:
"state_snapshots_history_visibility_event_nid_fkey" FOREIGN KEY (history_visibility_event_nid) REFERENCES events(nid) ON DELETE SET NULL
"state_snapshots_server_acl_event_nid_fkey" FOREIGN KEY (server_acl_event_nid) REFERENCES events(nid) ON DELETE SET NULL
Referenced by:
TABLE "events_state_snapshots" CONSTRAINT "events_state_snapshots_state_snapshot_nid_fkey" FOREIGN KEY (state_snapshot_nid) REFERENCES state_snapshots(nid) ON DELETE CASCADE
TABLE "room_state_snapshots" CONSTRAINT "room_state_snapshots_state_snapshot_nid_fkey" FOREIGN KEY (state_snapshot_nid) REFERENCES state_snapshots(nid) ON DELETE CASCADE
TABLE "state_snapshot_state_events" CONSTRAINT "state_snapshot_state_events_state_snapshot_nid_fkey" FOREIGN KEY (state_snapshot_nid) REFERENCES state_snapshots(nid) ON DELETE CASCADE
Access method: heap
stripped_state_events
Stripped state events, which make up federated invites
Table "public.stripped_state_events"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------+--------+-----------+----------+----------------------------------+----------+-------------+--------------+---------------------------------
nid | bigint | | not null | generated by default as identity | plain | | | Surrogate key for the event
content | json | | not null | | extended | | | The actual content of the event
sender | text | | not null | | extended | | | The sender of the event
state_key | text | | not null | | extended | | | The state_key of the event
type | text | | not null | | extended | | | The event type
Indexes:
"stripped_state_events_pkey" PRIMARY KEY, btree (nid)
Access method: heap
syncv4_connections
Users sync v4 connections. Linked to (user_id, device_id, conn_id) triplets.
Table "public.syncv4_connections"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------+---------+-----------+----------+----------------------------------+----------+-------------+--------------+--------------------------------------------------------------------------
nid | bigint | | not null | generated by default as identity | plain | | | Surrogate key of the connection.
device_nid | integer | | not null | | plain | | | Link to the device owning this connection
conn_id | text | | | | extended | | | User-set connection ID. Can be null. Must be unique per-user per-device.
Indexes:
"syncv4_connections_pkey" PRIMARY KEY, btree (nid)
"syncv4_connections_device_nid_conn_id_key" UNIQUE CONSTRAINT, btree (device_nid, conn_id) NULLS NOT DISTINCT
Foreign-key constraints:
"syncv4_connections_device_nid_fkey" FOREIGN KEY (device_nid) REFERENCES devices(nid) ON DELETE CASCADE
Referenced by:
TABLE "syncv4_positions" CONSTRAINT "syncv4_positions_connection_nid_fkey" FOREIGN KEY (connection_nid) REFERENCES syncv4_connections(nid) ON DELETE CASCADE
Access method: heap
syncv4_positions
Sync v4 positions within their connections. We store one per response.
Table "public.syncv4_positions"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+--------+-----------+----------+-----------------------------------------------+---------+-------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------
position | bigint | | not null | locking_nextval('syncv4_pos_streamids'::text) | plain | | | Surrogate key of the position. This is a global stream ID (See https://bundesmessenger.gitlab.io/backend/neo/arminius/arminius/architecture/sync.html)
connection_nid | bigint | | not null | | plain | | | Link to the connection this position belongs to
prev_position | bigint | | | | plain | | | Link to the parent position within the same connection. Will be null if it's the first ever position in the connection, or if the parent got deleted.
Indexes:
"syncv4_positions_position_key" UNIQUE CONSTRAINT, btree ("position")
Foreign-key constraints:
"syncv4_positions_connection_nid_fkey" FOREIGN KEY (connection_nid) REFERENCES syncv4_connections(nid) ON DELETE CASCADE
"syncv4_positions_prev_position_fkey" FOREIGN KEY (prev_position) REFERENCES syncv4_positions("position") ON DELETE SET NULL
Referenced by:
TABLE "syncv4_positions" CONSTRAINT "syncv4_positions_prev_position_fkey" FOREIGN KEY (prev_position) REFERENCES syncv4_positions("position") ON DELETE SET NULL
TABLE "syncv4_room_streams" CONSTRAINT "syncv4_room_streams_position_fkey" FOREIGN KEY ("position") REFERENCES syncv4_positions("position") ON DELETE CASCADE
Access method: heap
syncv4_room_streams
Tracks, for each stream, for each room, what was already sent down the connection
Table "public.syncv4_room_streams"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------------+---------------+-----------+----------+---------+---------+-------------+--------------+------------------------------------------------------------------------------------------------
position | bigint | | not null | | plain | | | Link to the position that sent this stream
stream_name | stream_name | | not null | | plain | | | Name of the stream this entry applies to
room_nid | integer | | not null | | plain | | | Link to the room this entry applies to
stream_status | stream_status | | not null | | plain | | | LIVE means the room was fully caught up. PREVIOUSLY means we sent up to the `up_to` stream ID.
up_to | bigint | | | | plain | | | Set only if the `stream_status` is PREVIOUSLY. Indicates up to which `stream_id` we sent
Indexes:
"syncv4_room_streams_position_stream_name_room_nid_key" UNIQUE CONSTRAINT, btree ("position", stream_name, room_nid)
Foreign-key constraints:
"syncv4_room_streams_position_fkey" FOREIGN KEY ("position") REFERENCES syncv4_positions("position") ON DELETE CASCADE
"syncv4_room_streams_room_nid_fkey" FOREIGN KEY (room_nid) REFERENCES rooms(nid)
Access method: heap
to_device_messages
Messages sent to local devices. See https://spec.matrix.org/v1.15/client-server-api/#send-to-device-messaging
Table "public.to_device_messages"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------------------+--------+-----------+----------+---------------------------------------------------+----------+-------------+--------------+-------------------------------------------------------------------------------------------------------
to_device_message_nid | bigint | | not null | generated by default as identity | plain | | | Surrogate key for the message
device_nid | bigint | | not null | | plain | | | The intended recipient of the message
event_type | text | | not null | | extended | | | A string, representing the event type, for instance `m.new_device`
event_content | json | | not null | | extended | | | The content, an arbitrary JSON blob
sender | text | | not null | | extended | | | The user who sent the event
stream_id | bigint | | not null | locking_nextval('device_message_streamids'::text) | plain | | | Stream id. See https://bundesmessenger.gitlab.io/backend/neo/arminius/arminius/architecture/sync.html
Indexes:
"to_device_messages_pkey" PRIMARY KEY, btree (to_device_message_nid)
"to_device_messages_stream_id_key" UNIQUE CONSTRAINT, btree (stream_id)
Foreign-key constraints:
"to_device_messages_device_nid_fkey" FOREIGN KEY (device_nid) REFERENCES devices(nid) ON DELETE CASCADE
Access method: heap
user_keys
User device keys and cross-signing keys
Table "public.user_keys"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------+----------+-----------+----------+----------------------------------------------+----------+-------------+--------------+-------------------------------------------------------------------------------------------------------------------------
nid | bigint | | not null | generated by default as identity | plain | | | Surrogate key for user_keys
key_type | key_type | | not null | | plain | | | Type of the key, see the `KEY_TYPE` enum
user_nid | integer | | not null | | plain | | | ID of the user owning the key
device_nid | integer | | | | plain | | | ID of the device owning the key iff key_type=device
key_id | text | | not null | | extended | | | Matrix key ID
key_object | jsonb | | not null | | extended | | | The actual key object
stream_id | bigint | | not null | locking_nextval('user_keys_streamids'::text) | plain | | | Stream ID of the users keys. See https://bundesmessenger.gitlab.io/backend/neo/arminius/arminius/architecture/sync.html
Indexes:
"user_keys_pkey" PRIMARY KEY, btree (nid)
"user_keys_key_type_user_nid_device_nid_key" UNIQUE CONSTRAINT, btree (key_type, user_nid, device_nid) NULLS NOT DISTINCT
"user_keys_stream_id_key" UNIQUE CONSTRAINT, btree (stream_id)
Check constraints:
"user_keys_check" CHECK (key_type <> 'device'::key_type AND device_nid = NULL::integer OR key_type = 'device'::key_type AND device_nid <> NULL::integer)
Foreign-key constraints:
"user_keys_device_nid_fkey" FOREIGN KEY (device_nid) REFERENCES devices(nid) ON DELETE CASCADE
"user_keys_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) ON DELETE CASCADE
Referenced by:
TABLE "local_signatures" CONSTRAINT "local_signatures_signed_key_nid_fkey" FOREIGN KEY (signed_key_nid) REFERENCES user_keys(nid) ON DELETE CASCADE
Access method: heap
users
Local Users
Table "public.users"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-----------------+---------+-----------+----------+----------------------------------+----------+-------------+--------------+------------------------------------------------------
nid | integer | | not null | generated by default as identity | plain | | | Surrogate key for the user
user_id | text | | not null | | extended | | | Mxid for the user
idp_id | text | | | | extended | | | ID for the user with the identity provider
display_name | text | | | | extended | | | The users human-readable display name
avatar_id | text | | | | extended | | | Link to the users avatar
keys_backup_seq | bigint | | not null | 0 | plain | | | Version number of the user's latest room keys backup
Indexes:
"users_pkey" PRIMARY KEY, btree (nid)
"users_idp_id_key" UNIQUE CONSTRAINT, btree (idp_id)
"users_user_id_key" UNIQUE CONSTRAINT, btree (user_id)
Referenced by:
TABLE "devices" CONSTRAINT "devices_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) DEFERRABLE
TABLE "federated_invites" CONSTRAINT "federated_invites_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid)
TABLE "federated_knocks" CONSTRAINT "federated_knocks_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid)
TABLE "filters" CONSTRAINT "filters_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) ON DELETE CASCADE
TABLE "forgotten_rooms" CONSTRAINT "forgotten_rooms_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) ON DELETE CASCADE
TABLE "global_account_data" CONSTRAINT "global_account_data_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) ON DELETE CASCADE
TABLE "local_signatures" CONSTRAINT "local_signatures_signing_user_nid_fkey" FOREIGN KEY (signing_user_nid) REFERENCES users(nid) ON DELETE CASCADE
TABLE "presence" CONSTRAINT "presence_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid)
TABLE "room_account_data" CONSTRAINT "room_account_data_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) ON DELETE CASCADE
TABLE "room_keys_versions" CONSTRAINT "room_keys_versions_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) ON DELETE CASCADE
TABLE "user_keys" CONSTRAINT "user_keys_user_nid_fkey" FOREIGN KEY (user_nid) REFERENCES users(nid) ON DELETE CASCADE
Access method: heap
verification_metadata
Metadata recorded when attempting to verify the signature of an event
Table "public.verification_metadata"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------------+--------------------------+-----------+----------+---------+---------+-------------+--------------+-------------------------------------------------------------------------------------
event_nid | bigint | | not null | | plain | | | The ID of the event that verification was attempted for
attempted_at | timestamp with time zone | | not null | | plain | | | Timestamp when verification was attempted
signature_verification | parts_verified | | not null | | plain | | | Signature verification outcome for the event
soft_failed | boolean | | not null | | plain | | | Whether the event was soft-failed, i.e. authorization against the room state failed
Indexes:
"verification_metadata_pkey" PRIMARY KEY, btree (event_nid)
Foreign-key constraints:
"verification_metadata_event_nid_fkey" FOREIGN KEY (event_nid) REFERENCES events(nid) ON DELETE CASCADE
Access method: heap