This capability note describes the project-owned database persistence boundary, the SQLite runtime backend, the PostgreSQL/libpq backend, and remaining work for production operation.
The deployed configuration parser accepts only postgresql and sqlite.
PostgreSQL startup fails when its URI file is missing, unreadable, or empty; it
never substitutes a process-local store. DatabaseBackend::memory is an
explicit programmatic backend for tests only and is not a valid config-file
value. Test fixtures that do not exercise persistence select it through
tests/support/InMemoryDatabaseConfig.hpp.
-
Prepared statement representation.
-
Bound parameter representation with sensitivity metadata.
-
Statement-name validation.
-
Conservative SQL-shape validation.
-
Redacted bound-parameter summaries.
-
Database executor interface.
-
Validated execution helper that rejects invalid statements before they reach an executor.
-
Migration step and migration plan models.
-
Contiguous upgrade and explicit downgrade migration-plan validation; data-only migration statements (
INSERT/UPDATE/DELETE) are accepted as no-op schema-state transitions, enabling backfill and corrective migrations should a later migration need one. -
The schema has three steps.
schema::current_schema_version()is3, andmigrations/holds001_initial_schema.sql,002_oauth_tables.sqland003_delayed_events.sql. -
Version
1(initial_schema) is the frozen v1 schema. Every pre-1.0 migration (versions 2 to 26 as of 0.12.46) was collapsed into it in 0.12.47 (ADR-0224), so v1 creates all 64 tables in their final shape, every column that an earlier migration used toALTERon now being part of the table'sCREATE, plus nine indexes:event_edges_prev_event_id,idx_to_device_messages_recipient_age,idx_deferred_pdus_room_id,idx_media_pending_uploads_owner,idx_events_room_id,idx_state_groups_room_id,idx_access_tokens_user_device,idx_refresh_tokens_user_deviceandidx_refresh_tokens_predecessor(idx_events_room_idandidx_state_groups_room_idserve the per-room reload, ADR-0490;stream_orderingis deliberately unindexed) Nothing is seeded or backfilled: the old state-transition backfill and state-group seeding only ever mattered for upgrading existing databases and no longer exist. The compiled catalog insrc/database/Migration.cpphas three upgrade steps (version1,initial_schema; version2,oauth_tables; version3,delayed_events) and three downgrade steps, newest first (drop_delayed_eventsto version2,drop_oauth_tablesto version1, thendrop_initial_schemato version0, which drops every v1 table).migrations/001_initial_schema.sqlis the frozen record of v1,002_oauth_tables.sqlof version 2 and003_delayed_events.sqlof version 3; a unit test requires each file to match the build's step statement for statement. -
Version
2(oauth_tables, 1.1.0) adds the OAuth 2.0 tables (ADR-0776), and changes no existing table:oauth_clients(client_id TEXT PRIMARY KEY,metadata_json,created_at_ms,last_used_at_ms, allTEXT NOT NULL, the integers written as decimal text like every other*_mscolumn). A row is kept as first written (store_oauth_clientanswersexisting); onlylast_used_at_mschanges, at most once a day (touch_oauth_client, ADR-0777). The metadata is refused over 16 KiB and the ID over 128 bytes.oauth_sessions(user_id,device_id,client_id,scope,created_at_ms, primary key(user_id, device_id)), withidx_oauth_sessions_client_id. The scope is refused over 2048 bytes. A session row is written in the caller's transaction (prepare_oauth_session_insert, thenremember_oauth_sessionafter the commit) and deleted bydelete_devicein the transaction that deletes the device. It is also deleted in the transaction that revokes every token of its device (revoke_user_credentials,revoke_device_tokens,revoke_device_credentials), and by the retention sweep once its device holds no access or refresh token that is neither revoked nor expired (ADR-0808). No foreign keys: no v1 table declares one, and the deleting transaction removes dependent rows explicitly.- In memory:
PersistentStore::oauth(OAuthTables), hydrated on both backends, with sessions by (user, device), a per-user index, a session count per client and the sessionless clients ordered by last use, which the retention sweep reads (30 days, never a client with a session).
-
Version
3(delayed_events, 1.1.2) adds one table for MSC4140 delayed events (ADR-0824, ADR-0825), and changes no existing table:delayed_events(delay_id TEXT PRIMARY KEY, thenuser_id,device_id,room_id,event_type,is_state,state_key,content_json,txn_id,via_appservice,delay_ms,delayed_since_ms,status,finalised_at_ms,event_id,error_json, allTEXT NOT NULL: the integers are decimal text, the two booleans aretrueorfalselikeusers.deactivated,statusisscheduled,sent,cancelledorfailed, and the columns that do not apply are the empty string, or0forfinalised_at_ms), withidx_delayed_events_user_idonuser_id, which serves the delete at deactivation. No foreign key.device_idis the scheduling request's device and is not deleted with it: a delayed event belongs to the user.- Caps, enforced by
store_delayed_eventand the finalising functions (DelayedEventStore.hpp):delay_id64 bytes,content_json65,536 bytes,error_json1,024 bytes, 255 bytes foruser_id,device_id,room_id,event_type,state_key,txn_idandevent_id, anddelay_msfrom 1 to 2,678,400,000 (31 days). A row over a cap is refused, never truncated. The store does not enforce the per-user cap on scheduled rows: the caller does, because it must computeRetry-Afterfrom the user's rows. - In memory:
PersistentStore::delayed_events(DelayedEventTable), hydrated on both backends (neither loads it for the federation worker profile), with no lock of its own (runtime.mutexserialises it). It indexes the rows by delay ID, by user (the scheduled rows by due time, the finalised rows by finalised time), every scheduled row not being sent by due time (delayed_since_ms + delay_ms, then delay ID, so the earliest due is O(1)), by (user, device, room, event type, transaction ID) for idempotency, by event ID for sent rows, and every finalised row by finalised time for the age prune. A memory-only "being sent" mark hides a row from the due order while a send is in progress. No reader scans the table. A row the loader cannot read is skipped when hydrating, never repaired and never guessed: the three numeric columns must be decimal digits,statusand the two booleans exactly the texts the store writes, a scheduled row's delay within 1 to the maximum, and asentrow's event ID not already claimed by another row. The count skipped is logged once at start-up. A number read as 0 would make a scheduled row due at once, which is sending early. - Rows leave in four ways, all in one transaction: the user's cancel and the outcome of a send each evict that
user's oldest finalised rows beyond
server.delayed_events.max_finalised_per_user; the age prune deletes finalised rows older than 7 days (oldest first, at most 500 a transaction); and account deactivation deletes all of a user's rows (DELETE ... WHERE user_id = $1, always run in the database, so a skipped row does not outlive its account). Logout and device deletion delete nothing. - The one place memory changes without a commit:
record_delayed_event_outcome. The event was already sent, so memory is finalised even when the finalising commit fails (the function says so), and the database row still readsscheduled, so the next start-up sends it again (at least once, ADR-0825). Every other function changes memory only after the commit.
-
The schema registry is split by version (ADR-0778):
initial_schema_definitions()is what v1 created, and the SQLite and PostgreSQL bootstraps build exactly that and write ledger row 1; every later step then runs throughapply_pending_migrations, so a fresh database and an upgraded 1.0.x database reach the same schema by the same path.current_schema_definitions()is every version up to the current one. -
Schema change policy. Before
v1.0.0there was nothing to upgrade from: a database whoseschema_migrationsledger holds an upgrade row this build does not have (for example the rows 2 to 26 written by every build up to 0.12.46) is refused at open, before any migration or hydration (schema_ledger_is_known), and must be recreated. Sincev1.0.0the v1 migration andmigrations/001_initial_schema.sqlnever change; every schema change is a new numbered migration (v2 onwards) that keeps deployed databases compatible, and upgrade compatibility is promised from that release. -
SQLite RAII wrappers around database connections and prepared statements.
-
SQLite current-schema bootstrap for new database files.
-
SQLite row hydration for users, devices, access tokens, rooms, memberships, refresh tokens, events (with depth), current state, server signing keys (with server_name), event DAG rows, event signatures, E2EE key state, media metadata, durable media blobs, remote media metadata, account data, policy rules, audit events, and admin actions.
-
Write-through SQLite persistence behind the existing store mutation helpers.
-
Transaction-aware persistent-store commits with SQLite rollback support.
-
Atomic helpers for multi-row login, room creation, and state-event writes.
-
Persistent helpers for refresh-token rotation, global/device access-token and refresh-token revocation, device display-name updates, and device deletion.
-
set_user_account_stateupdates theuserstablesuspended/lockedcolumns and mirrors the new state into the in-memoryPersistentUser, used by the admin lock/suspend endpoints. -
database::revoke_tokens_for_user_except_device(0.12.7) revokes every access and refresh token for a user except one named device, in a singleUPDATE ... WHERE user_id = $1 AND device_id <> $2per token table — so a password change withlogout_devices: truecan revoke every other device without the caller's own tokens ever being revoked, and therefore without anything to restore afterward. This replaces the prior revoke-then-restore pair:restore_tokens_for_device, which re-activated a device's tokens by settingrevoked = false, is removed from both the header and the implementation. ThatUPDATEcould not distinguish a token it had revoked microseconds earlier from one revoked days earlier by an unrelated logout or admin action, so it resurrected all of them — handing back a token the very password change was meant to invalidate. No function in the database layer may clear arevokedflag; revocation is a one-way transition. A requirement of the shape "revoke some subset" must be expressed as a narrower revoke, never as a broad revoke followed by a restore. See ADR-0052. -
Access and refresh token rows carry an
expires_atcolumn (TEXT NOT NULL DEFAULT '', empty = no expiry / legacy) folded into the version-1 initial schema.store_access_token,store_refresh_token, andstore_device_and_access_tokenbind the epoch-millis text of the token'sexpires_at, and SQLite/PostgreSQL hydration parse it back into thePersistentAccessToken/PersistentRefreshTokenexpires_atfield (std::optional<system_clock::time_point>,nulloptfor empty). The runtime enforces expiry at the session/refresh lookup rather than at the store layer. -
Token-hash equality at the store layer uses constant-time comparison (
crypto::constant_time_equal, backed bysodium_memcmp) for the access and refresh token lookups, so a database-equivalent match does not branch on the fixed-length hash bytes. -
PersistentStore::server_signing_keyshas its own lock,server_signing_keys_mutex(0.12.13). The remote-key resolver stores and reads keys from federation relay threads and from backfill with the runtime mutex released, so after start-up hydration the vector is touched only throughstore_server_signing_key,find_server_signing_keyandsnapshot_server_signing_keys(a copy for callers that iterate). The lock is never held across the database write or a network call. -
server_signing_keys.expired_ts(TEXT NOT NULL DEFAULT '', v1 step, ADR-0425) marks a remote server's retired key, one it listed underold_verify_keys; empty means current.PersistentServerSigningKey::expired_tsis 0 for a current key. A retired row hasvalid_until_tsequal toexpired_ts, capped at the fetch time, and it is one-way:store_server_signing_keykeeps a retired row retired whatever a later write says, keeps its public key, takes the smaller expiry, and retires a current row written with an expiry at once (the SQL upsert applies the same rule, so concurrent writers cannot raise an expiry). A row that holds a secret, this server's own key, never takes an expiry: such a write returns false. Both loaders and the backend half ofrefresh_remote_signing_keysselect the column, and nothing deletes or prunes a row byvalid_until_ts. -
Media bytes are not hydrated (ADR-0119). The SQLite and PostgreSQL loaders read
media_blobswithout itsbytescolumn;prepare_media_blob_read(under the runtime mutex) andread_media_blob(without it) fetch one blob's bytes per request. A media upload writes its bytes incommit_local_media_upload; a deduplicated upload writes onlyref_count(update_media_blob_ref_count). Remote media is admitted bycommit_remote_media_admission, which in one transaction deletes the media andremote_mediarows of the cache entries the admission displaces, sets those blobs' reference counts (clearing bytes at zero), and inserts the new media, blob andremote_mediarows.remote_mediacarrieslocal_media_idandfetched_at_ms. Since 0.12.81 (ADR-0574) a SQLite download larger thandatabase::media_blob_chunk_bytes(256 KiB) is read in chunks:read_media_blob_chunkreads one chunk on a short-lived read-only connection. PostgreSQL stays buffered, because eachsubstring()of a compressed TOASTedbyteadecompresses the whole value. -
room_aliasesis held in memory asdatabase::RoomAliasTable(F-03, ADR-0581), not a vector: it owns the rows and indexes them by alias, by room ID and by creator, sofind_room_alias, a room alias listing and a creator alias count are hash lookups and a delete or reassign does not scan the table. Hydration (SQLite and PostgreSQL) inserts through the class.server.client_api.max_room_aliases_per_userandmax_room_aliases_per_roombound the rows. -
room_aliasescarriescreator_user_id(CSAZ-12, ADR-0127):store_room_aliaswrites the creating user, anddelete_room_aliasremoves a mapping, in the database and in memory, forDELETE /directory/room/{roomAlias}. -
Binary payload columns are
BLOB(#448):media_blobs.bytesholds raw media content andserver_signing_keys.secret_keyholds encrypted key material (BLOB NOT NULL DEFAULT '', empty = no secret persisted). Both were folded into the v1 schema. SQLite column reads are length-based (sqlite3_column_bytes), so payloads containing NUL or invalid UTF-8 round-trip byte-exactly.SchemaTableDefinition::columns_sqlis shared, backend-agnostic DDL text written in SQLite's dialect; PostgreSQL has noBLOBtype, soPostgresqlStore.cpp'stranslate_ddl_types_for_postgresql()rewritesBLOBtoBYTEA(whole-word, so it cannot mangle an identifier) wherever aSchemaTableDefinitionbecomes DDL for that backend — both the bootstrap path and any later migration step. -
Every SQLite connection pins
PRAGMA secure_delete = ON,PRAGMA trusted_schema = OFFandPRAGMA synchronous = FULLat open (#447), and a read-write connection also pinsPRAGMA journal_mode = DELETE, so an environment that installs global default pragmas cannot silently weaken durability. The stores also enablePRAGMA foreign_keys = ONon their connections. -
SQLite hydration fails closed when a row query cannot be prepared or stepped to completion.
-
SQLite connections use a 5000 ms busy timeout for short-lived lock contention.
-
libpqdependency review and a PostgreSQL RAII connection/result wrapper. -
PostgreSQL current-schema bootstrap, row hydration, and write-through transaction execution when a URI file is explicitly configured.
-
Durable persistent-store helpers for device keys, one-time keys, fallback keys, cross-signing keys, key signatures, key backup versions, and key backup sessions.
-
One fallback key per algorithm per device:
store_fallback_keydeletes the device's other fallback keys of the same algorithm and inserts the new one in a single transaction (spec v1.19,POST /keys/upload,fallback_keys). A key's algorithm isdatabase::fallback_key_algorithm(key_id): the key ID up to and including its first:(the whole ID when it has none). Replacement matches that algorithm exactly: a key ID with no:replaces only a held key of the same ID, never keys whose IDs merely begin with it, as the former prefix match did. The same function counts the algorithms a device holds formax_fallback_keys_per_device(ADR-0594). -
key_signatures_for_targetreads key signatures through an in-memory index keyed by target user (PersistentStore::key_signature_target_index). The index is maintained bystore_key_signature, rebuilt after SQLite and PostgreSQL hydration, copied with the store, and ignored (lookup scans) while its row count differs fromkey_signatures, so rows pushed directly never produce a missing result. Read signatures through this function, not by scanning the vector. -
Run-3 C14 (ADR-0672): three more indexes sit beside it, by (signer, target user, target key), by signer and by (target user, target key), trusted under the same rule.
store_key_signatureswrites a whole upload in one transaction;for_each_signer_key_signatureandkey_signatures_for_target_keyread through them. -
find_current_state_event_idandfor_each_room_state_rowread current state through in-memory indexes overPersistentStore::state(state_position_indexby room, type and state key;state_room_indexby room). Every writer inPersistentStore.cppkeeps them in step,reload_roomand both hydration paths rebuild them (rebuild_state_index), and a lookup scans whilestate_indexed_rowsdiffers fromstate.size()or a position no longer names the row asked for (ADR-0344). Read state by key or by room through these, not by scanning the vector; a new writer ofstatemust callnote_state_row_appendedorrebuild_state_index. Both count the rows they look at instate_rows_examined, so a test proves a reader's cost by count. -
room_state_revisioncounts the changes to a room's current state rows, in memory only and from zero at each start. A cache of something computed from those rows is valid for one revision (ADR-0624), so a writer that changes a current state row callsnote_room_state_changedafter the in-memory row is written: a revision moved only before the write lets a reader stamp the new revision on the old row (apply_store_event_with_statemoves it on apply as well as on prepare, ADR-0697). -
delete_federation_transactionsremoves a list of queued transactions in one database transaction; the dispatch worker uses it to drop what a failing destination holds past its share or its age (ADR-0621). -
Physical migration-file loading for SQL files with explicit metadata and statement names; the checked-in
migrations/directory contains001_initial_schema.sql, the frozen record of the v1 schema, and002_oauth_tables.sql.merovingian-db-migrate --plan A B --migrations DIRtakes only the files whose version is between A (exclusive) and B. -
sync_stream_watermarktable stores the highest allocatedsync_stream_idand is updated bydatabase::allocate_sync_stream_id()before the ID is used for ephemeral events such as typing notifications and read receipts. This prevents the monotonic sync stream counter from rolling back across restarts. -
database::restore_sync_stream_id()writes the highestsync_stream_idfound in durable rows (account data, to-device messages, device-list changes, presence, read receipts) into the watermark on startup, so a restart starts from the maximum persisted value rather than the table default. -
device_list_changesholds at most one row per(observer_user_id, subject_user_id).database::record_device_list_change()andrecord_device_list_changes()replace a pair's earlier row (aDELETEthen anINSERTin one transaction) so it carries the newest stream position and the latestchange_type;/syncreports only the latest change per subject, so nothing is lost, and a peer sending the same EDU repeatedly cannot grow the table. The batch form validates every change first, allocates one sync stream position for all of them and commits once. This needs no migration: the primary key stays(stream_id, observer_user_id, subject_user_id). Rows written before this rule (duplicates per pair) are harmless, are collapsed bysync::collect_device_list_delta()on read, and are replaced the next time their pair changes. -
event_stream_watermarktable stores the highest allocated timelinestream_orderingand is updated byhomeserver::allocate_stream_ordering()(viadatabase::persist_event_stream_watermark()) on every allocation. Some allocations — membership stream positions — are not backed by a persisted event row, so a counter rebuilt frommax(events.stream_ordering)alone regresses across restarts, which puts clients' persisted sliding syncpos/sincetokens ahead of the live stream and invalidates them all on every restart. Hydration takes the maximum of the persisted watermark and the highest eventstream_ordering, then persists the merged floor so the row exists even on a first start. -
stream_token_epochtable holds one row (singleton= 1): the database's sync token epoch, a random nonzero 64-bit value stored as text (ADR-0592). The main process writes one at its first start on a database (homeserver::ensure_stream_token_epoch(), throughdatabase::persist_stream_token_epoch()), keeps it across restarts, and refuses to start when it can be neither read nor written.merovingian-server --after-restorereplaces it with a different random value, so every sync token issued before a restore is foreign. Sync tokens, sliding syncposand theto_deviceextension'ssincecarry it; a token from another epoch acknowledges nothing. The federation worker never reads it. -
state_transitionstable records the previousevent_idfor every state-event tuple replacement. It is populated bydatabase::store_state()and the splitprepare_store_event_with_state()/apply_store_event_with_state()helpers. The in-memory store keeps a hash index overstate_transitionsso thatclient_event_with_id()can injectunsigned.replaces_stateinto the client event format in O(1) rather than scanning the whole vector. For federated state events the previous event ID is derived by walking the event graph (prev_eventsandauth_events) to find the predecessor state event of the same(room_id, event_type, state_key)tuple, instead of assuming the local arrival-time current state was the predecessor. -
account_threepidstable records the 3PID bindings a user has added to their account, replacing the prior in-memoryvector<AccountThreePid>which was lost on restart. Columns areuser_id,medium,address,country,id_server,added_at_ms,validated_at_ms, andbound, with the primary key(user_id, medium, address).added_at_mszero marks a row that only records a bind at an identity server and grants nothing on this server (ADR-0584); that an address is on one account only is enforced bythreepid_in_use, not by a key. -
account_threepids.client_secret/account_threepids.sidcolumns store theclient_secret+sidpair the IS issued for a binding, so a later remote unbind can replay them in the request body (IS unbind auth mode 2). Both columns areTEXT NOT NULL DEFAULT ''; they are populated only for IS-bound 3PIDs and left empty for local-only bindings. -
PersistentThreePidBindingstruct (now carrying optionalclient_secretandsid) and thestore_account_threepid/find_account_threepid/delete_account_threepidstore functions (PersistentStore.hpp) persist, look up, and remove bound 3PIDs. Implemented for both SQLite (SqliteStore.cpp) and PostgreSQL (PostgresqlStore.cpp); rows are hydrated into the in-memory store on backend open so existing bindings are visible immediately after restart. -
pusherstable records the push notification pushers a user has registered viaPOST /_matrix/client/v3/pushers/set. Columns areuser_id,app_id,pushkey,kind,app_display_name,device_display_name,profile_tag,lang,data_url,data_format(the pusher'sdatadictionary'surl/formatkeys, stored as plain columns rather than nested JSON), anddata_extra_json(a canonical-JSON-serialized object holding every OTHER member of the pusher'sdatadictionary at registration time; PR #479 review finding P1: the Push Gateway API's notify Device object is defined as "the data dictionary passed in at pusher creation minus the url key", so a custom member beyondurl/formatmust survive a restart and reach the gateway, not just be discarded), anddevice_id(the device whose access token set the pusher, empty when none did; a password change that revokes a device deletes its pushers, 0.12.60), with a primary key on(user_id, app_id, pushkey)— the spec's uniqueness rule: setting a pusher with the sameapp_idandpushkeyfor the same user replaces it in place. ThePersistentPusherstruct and thestore_pusher/find_pusher/delete_pusher/list_pushers_for_userstore functions (PersistentStore.hpp) persist, look up, list, and remove pushers, following the same pattern asPersistentThreePidBindingabove; implemented for both SQLite and PostgreSQL, hydrated on backend open.merovingian::push::PushGatewayClient(PushGatewayClient.hpp) forwards a device'sdata_extramembers verbatim into the notify request body, and — since the PR #479 follow-up —ClientServerRuntime.cpp'sparse_pusher_set_body()captures everydatamember beyondurl/formatat registration time andRoomService.cpp's pusher-to-PushGatewayDeviceconversion threads it through to a live delivery, so the gap noted above is closed end to end; seedocs/todos/capability-gaps.md. -
notificationstable records the historyGET /_matrix/client/v3/notificationsserves. Columns areuser_id,room_id,event_id,stream_ordering(the triggering event's global stream position — unique per row, since a recipient gets at most one notification per event, and doubles as this table's pagination key exactly likeevents.stream_orderingalready does forGET /messages),ts(the event'sorigin_server_ts, milliseconds),actions(the canonical-JSON actions array reconstructed from the matched push rule's resolved outcome —PushRules.hpp'sPushEvaluationResultdoes not keep the rule's raw actions array, soRoomService.cpp'spush_notification_actions_jsonrebuilds the conventional["notify", {"set_tweak": ...}, ...]shape),profile_tag(left empty: this recording happens once per(user, event)rather than once per pusher, so it cannot name one pusher's configured tag), andhighlight("true"/"false", mirroring the matched rule'sset_tweak: highlightaction, so?only=highlightcan filter without re-parsingactions), with a primary key on(user_id, event_id). ThePersistentNotificationstruct and thestore_notification/list_notifications_for_userstore functions (PersistentStore.hpp) persist and list rows, following the same pattern asPersistentPusherabove; implemented for both SQLite and PostgreSQL, hydrated on backend open.RoomService.cpp'sbuild_pending_push_deliveries()callsstore_notificationsonce per event, with a row for every local recipient whose push rule evaluation resolvesnotify: true— in one transaction, unconditionally, regardless ofserver.push.enabledor whether that recipient has a registered pusher:GET /notificationsreturns events the user "has been, or would have been, notified about" (spec), so a user with push turned off must still see their history. Only the Push Gateway delivery half of that function (building aPendingPushDeliveryand dispatching it) stays gated onpush.enabled+ a registered pusher. Thereadfield the endpoint reports is not stored on the row — it is computed at request time fromsync::read_receipt_markers()against the caller's currentm.read/m.read.privatereceipts in that room (unthreaded, or the one for the event's thread), so it always reflects the latest receipt without a write-side update on every receipt change. Retention:store_notificationsprunes, in the same transaction as the inserts, the oldest rows for each notifieduser_idbeyondserver.client_api.max_notifications_retained_per_user(default 1,000, valid range 1..100,000). The mirror changes only after the commit (ADR-0440). The same policy applies to the in-memory mirror and SQLite/PostgreSQL persistence; rows with the oldest stream ordering are removed first. It is independent ofserver.push.max_in_flight_deliveries, which bounds background tasks rather than persisted rows. Recording still occurs when push delivery is disabled. Increasing retention preserves more future history but cannot restore rows already pruned under an earlier policy. Changing the policy requires restart. -
openid_tokenstable stores the short-lived tokens minted byPOST /_matrix/client/v3/user/{userId}/openid/request_token(Matrix v1.19 CS API §OpenID) and redeemed byGET /_matrix/federation/v1/openid/userinfo(SS API §OpenID). Columns areuser_id,token_hash(primary key), andexpires_at(epoch milliseconds, encoded the same way asaccess_tokens.expires_at/refresh_tokens.expires_atviaexpires_at_text/parse_expires_at). Unlike an access token's expiry,expires_atis never optional here — every OpenID token has a finite lifetime, and a user holds at mostmax_openid_tokens_per_user(32) live rows; the in-memory rows are indexed by hash and by user (ADR-0681, ADR-0682). This is a separate table fromaccess_tokens, by design — seedocs/auth-identity.md("OpenID tokens") anddocs/threat-model.mdfor why that separation is the entire security property this feature depends on. ThePersistentOpenidTokenstruct and thestore_openid_tokenstore function (PersistentStore.hpp) persist rows, following the same hashed-token pattern asPersistentAccessToken; implemented for both SQLite and PostgreSQL, hydrated on backend open.homeserver::request_openid_token(src/ homeserver/AuthService.cpp) mints and hashes the token — reusing the same keyed-hash machinery (issue_token_hash, preferring the master-key-derived v4 HMAC) access tokens use, since the hash algorithm is not the security boundary here — andhomeserver::federation_openid_userinfois the only function that ever reads this table back; the ordinary client-server auth gate (authenticated_user) never consults it, andfederation_openid_ userinfonever consultsaccess_tokens/sessions. Retention:revoke_openid_tokens_for_userdeletes a user's rows from the table and the in-memory vector (deactivation, logout everywhere, password change withlogout_devices; returnsfalseon a write failure, ADR-0408).store_openid_tokensweeps every already-expired row (across all users, not just the one being inserted) on each insert — appropriate because an OpenID token's natural bound is its own short expiry (one hour; seedocs/auth-identity.md) rather than a per-user row count, unlikenotifications' count-based cap above. -
login_tokenstable stores the short-lived, single-use tokens minted byhomeserver::complete_sso_loginwhen an SSO authentication completes (Matrix v1.19 CS API §"Client login via SSO") and redeemed exactly once byPOST /_matrix/client/v3/loginwithtype: m.login.token(homeserver::redeem_login_token). Columns areuser_id,token_hash(primary key),expires_at(epoch milliseconds, same encoding asopenid_tokens.expires_at), andused(TEXT NOT NULL DEFAULT 'false', following therevoked-column boolean-as-text conventionaccess_tokens/refresh_tokensalready use). This is a separate table fromaccess_tokens, for the same token-confusion reasonopenid_tokensis — seedocs/auth-identity.md("SSO login") anddocs/threat-model.md("Open redirect and login-token exfiltration via SSO redirectUrl").database::store_login_tokenpersists a row (reusingissue_token_hash, the same keyed-hash machinery access tokens use) and mirrors the openid-token retention strategy: every insert sweeps every already-expired-or-used row, not just the one just inserted, since a login token's natural bound is its own ~30s expiry or single use rather than a row count.database::consume_login_tokenis the only function that ever reads this table back and is the sole redemption path: it finds an unused, unexpired row matching one of the candidate hashes (constant-time compared viacrypto::constant_time_equal), marks it used in the backend before returning the owninguser_idso a persistence failure can never leave a token silently redeemable twice from memory alone, and returnsnullopton any miss (unknown, expired, or already-used token are indistinguishable to the caller). Implemented for both SQLite and PostgreSQL, hydrated on backend open. -
appservice_txn_cursortable persists the outboundPUT /_matrix/app/v1/transactions/{txnId}delivery cursor for each registered Application Service API (Matrix v1.19) appservice, keyed onappservice_id(one row per appservice). Columns:next_txn_id(the next fresh transaction id to allocate — monotonic, never reused),delivered_stream_ordering(high-water mark: every event up to and including this position has been acknowledged),pending_txn_idandpending_stream_ordering(the currently in-flight, unacknowledged batch, if any —pending_txn_id == 0means none). Every field is stored asTEXT, matching this table'spushers/notifications/openid_tokensneighbours, and parsed back tostd::uint64_tat hydration. ThePersistentAppserviceTxnCursorstruct and thefind_appservice_txn_cursor/store_appservice_txn_cursorstore functions (PersistentStore.hpp) are implemented for both SQLite and PostgreSQL, hydrated on backend open. The cursor is the state of the one transaction in flight (pending_txn_id/pending_stream_ordering) plus the transaction-ID allocator; the queue behind it isappservice_outboxbelow. A retry of the in-flight transaction reuses the exact samepending_txn_idand re-derives the identical single event fromevents, never growing it, per the spec's "Homeservers MUST NOT alter ... events they were going to send within that transaction ID on retries." -
appservice_outboxtable (audit R01, ADR-0163) is the Application Service API transaction queue the spec requires: one row(appservice_id, stream_ordering, event_id)per event queued for one appservice, primary key(appservice_id, stream_ordering). A row is written when the event is dispatched (enqueue_appservice_event, idempotent on the key, interest decided at that moment) and deleted in the same backend transaction that clears the cursor's pending transaction and raisesdelivered_stream_ordering(complete_appservice_delivery), so a crash can never leave a delivered event queued or a queued event forgotten.stream_orderingisTEXTlike every numeric column in this schema; the in-memory mirrorPersistentStore::appservice_outboxis kept sorted numerically by(appservice_id, stream_ordering)(sort_appservice_outboxafter hydration).delivered_stream_orderingis now informational: it is not a threshold new events are compared against, because stream orderings are allocated before commit and events in other rooms commit out of order. Main process only: the table is not infederation_worker_table_allowlist. The queue of one appservice is capped atappservice.max_queued_eventsrows (default 100000, ADR-0191): at the capenqueue_appservice_deliverywrites no further row for that appservice, andcount_appservice_outbox_entries(two binary searches over the sorted mirror) is what it checks. No row is dropped for age. -
appservice_directorytable (ADR-0192) holds the rooms an appservice has published to the directory of one of its networks (protocols) withPUT /_matrix/client/v3/directory/list/appservice/{networkId}/{roomId}: one row(appservice_id, network_id, room_id), primary key all three.PersistentAppserviceDirectoryEntry,PersistentStore::appservice_directoryandset_appservice_directory_entry/appservice_directory_containsare implemented for both SQLite and PostgreSQL and hydrated on backend open; listing and unlisting are idempotent and commit before the mirror changes. It is independent ofrooms.directory_public. Main only: the table is not infederation_worker_table_allowlist. -
deferred_pdustable (R03, ADR-0165) holds the federation PDUsingest_pdu_eventcould not yet place because aprev_eventorauth_eventis unknown or aprev_eventhas no state group. Columns:event_id(primary key),room_id(indexed),origin(the server it arrived from, to backfill from),pdu_json(the envelope exactly as ingestion would have stored it, which is the redacted copy after a content-hash mismatch),received_at_ms(kept across attempts) andattempts(retries that did not place it). Every number isTEXT, like the neighbouring tables, and is parsed back at hydration.PersistentDeferredPduandupsert_deferred_pdu/remove_deferred_pdu/list_deferred_pdus/deferred_pdus_for_room(PersistentStore.hpp) are implemented for both SQLite and PostgreSQL, hydrated on backend open. The store is bounded: 100 rows per room, 1000 in total, 16 MiB ofpdu_jsonin total (max_deferred_pdus_per_room,max_deferred_pdus_total,max_deferred_pdu_json_bytes); a new row that would exceed one is refused (DeferredPduUpsert::refused_full) while an update of a held row never is. Rows older than 24 hours (max_deferred_pdu_age) or retried 10 times (max_deferred_pdu_attempts) are dropped byhomeserver::retry_deferred_pdusand audited. The next-attempt time is not stored (HomeserverRuntime::deferred_pdu_next_attempt, in memory): after a restart every retained PDU is due at once. Main only: the table is not infederation_worker_table_allowlist, because the worker relays every PDU to main and never reads or writes it. -
receiptstable (ADR-0169) holds read receipts, local and federated: one row per(room_id, receipt_type, user_id, thread_id)(the primary key), namingevent_id, itstsand thestream_idit was last set at.thread_idis''for an unthreaded receipt,mainfor the main timeline, or a thread root's event ID. Every unread count is computed from these rows, so before this table a restart marked every room unread.PersistentReceipt,PersistentStore::receiptsandstore_receipt/remove_receipt(PersistentStore.hpp) are implemented for both SQLite and PostgreSQL and hydrated on backend open;store_receiptallocates the sync stream position and commits the row before the mirror changes. At the inbound cap, it deletes the selected victim and upserts the incoming receipt in one transaction. A failed delete or upsert preserves the previous receipts in both the database and the mirror. The inbound receipt callback takesHomeserverRuntime::mutexbefore checking membership or reading/updating the receipt mirror, including on direct federation requests; worker relays can re-enter this recursive lock. Onlym.readandm.read.privateare stored here:m.fully_readis room account data. Inbound federated receipts are capped byhomeserver::max_inbound_receipt_entries; at the cap the oldest receipt of a remote user is removed, never a local user's. Main only: the table is not infederation_worker_table_allowlist. Rows are read throughreceipt_room_indexandreceipt_user_room_index(receipts_in_room,receipts_of_user_in_room, counted inreceipt_rows_examined), never by looping the vector; the indexes follow the ADR-0603 trust rule andrebuild_key_indexesrebuilds them on load. One user holds at mostmax_threaded_receipt_rows_per_user_room(256) rows with a thread root asthread_idin one room; a new one is committed with the delete of that user's own oldest (ADR-0737). The device-list change, stored filter and presence tables are read through standing indexes by observer, user and user (for_each_observer_device_list_change,for_each_user_filter,find_presence_state; ADR-0659).store_receiptscommits an inboundm.receiptEDU's accepted rows in one transaction and, pastmax_inbound_receipt_entries, deletes the oldest remote rows in it, taken from the orderedreceipt_remote_by_streamindex (ADR-0652). -
The v1 schema's media tables carry the upload names and pending uploads.
media.upload_name(TEXT NOT NULL DEFAULT '', ADR-0211) is the upload'sfilename(or the remote server'sContent-Dispositionfilename), served back inContent-Disposition.media_pending_uploads(media_idprimary key,owner_user_id,created_at_ms,expires_at_ms, indexed by owner; ADR-0212) holds themxc://URIs reserved byPOST /_matrix/media/v1/createthat have no content yet.commit_pending_media_uploadinserts one and deletes the expired ones it was handed in the same transaction;commit_local_media_uploadwithcompletes_pending_media_iddeletes the row in the transaction that stores the content. Rows are bounded bymax_pending_uploads. Main only: not infederation_worker_table_allowlist(the federation media route runs in main).
-
users.deactivatedcolumn (part of the v1 schema) is aTEXT NOT NULL DEFAULT 'false'column of theuserstable, set byPOST /_matrix/client/v3/account/deactivate(seedocs/auth-identity.md"Account deactivation"). It is deliberately distinct from the existing reversiblelocked/suspendedcolumns: a deactivated account can never log in again, and its row is retained (not deleted) so the localpart is never reissued to a new registration.set_user_deactivated(PersistentStore.hpp) updates the column and mirrors the change into the in-memoryPersistentUser, following the same pattern asset_user_account_stateabove; implemented for both SQLite and PostgreSQL, hydrated on backend open. Adding a sixth column tousersalso exposed a latent risk instore_user's insert statement: it previously used a bareINSERT INTO users VALUES (...), which binds parameters by position and would have silently mismatched had the column order or count changed elsewhere.store_usernow names every column explicitly (INSERT INTO users (user_id, password_hash, locked, suspended, admin, deactivated) VALUES (...)), so a future schema change tousersfails loudly (wrong parameter count) rather than silently binding a value to the wrong column. -
Phase A of spec-conformant PDU ingestion (ADR-0064): delta state groups, forward extremities, and event status. This is storage plumbing only — it does not change how PDUs are ingested, how local events pick
prev_events, or what clients see; that is later phases. The v1 schema holds:events.status—TEXT NOT NULL DEFAULT 'accepted', one of'accepted' | 'soft_failed' | 'rejected' | 'outlier'(spec: "Checks performed on receipt of a PDU").set_event_status/find_event_statusget/set it, validating the value and mirroring the change into the in-memoryPersistentEvent::statusfield (appended as that struct's last member, defaulting to"accepted", so no existing brace-init call site needed to change).state_groups, the root of a delta chain, withparent_state_group_id(TEXT NOT NULL DEFAULT '') anddelta_depth(TEXT NOT NULL DEFAULT '0'). Like every other nullable-in-spirit column in this schema (e.g.state_transitions.previous_event_id), the empty string is the "no parent, this is a snapshot" sentinel rather than a SQLNULL—PreparedStatement/BoundValuehas no NULL parameter representation, and this keeps the convention consistent with the rest of the schema. In C++,PersistentStateGroup::parent_state_group_idisstd::optional<std::string>(nullopt⇔ the empty-string sentinel).state_group_edgesis vestigial and unused — see "Every table classified" below for whether it should eventually be dropped.state_group_state (state_group_id, event_type, state_key, event_id)— one group's own rows: the full state for a snapshot group (parent_state_group_idempty), or just the changed entries for a delta group.event_state_groups (event_id PRIMARY KEY, state_group_id)— maps an event to the group holding the room's state immediately after it.forward_extremities (room_id, event_id)— a room's current DAG leaves.- Index:
event_edges_prev_event_id ON event_edges (prev_event_id).event_edges' primary key is(event_id, prev_event_id), which cannot serve a lookup keyed onprev_event_idalone; the ingest-time "children of this event" lookup (is this event anybody'sprev_event_id?) needs exactly that shape, and without the index it is a full table scan per probe. Confirmed byEXPLAIN QUERY PLANintests/integration/test_state_groups_flow.cpp's "event_edges has a usable index for prev_event_id lookups in the v1 schema" scenario.state_groupshas no matchingroom_idindex: every store function that reads it (find_state_group,read_state_group_full_state,create_or_reuse_state_group's parent lookup) looks up bystate_group_id, its primary key — nothing queries it byroom_id.
Store API (PersistentStore.hpp):
create_or_reuse_state_group(store, room_id, new_state_group_id, parent_state_group_id, full_state)builds the group that will holdfull_state— reusingparent_state_group_idunchanged whenfull_stateis exactly the parent's own state (read viaread_state_group_full_state, never re-derived by re-diffing the raw rows a second way), writing a full snapshot when there is no parent, the parent'sdelta_depth + 1would exceedevents::max_state_group_delta_depth(100), orfull_stateis missing a key the parent has (deltas never encode deletions — state resolution never drops a key present in any fork, so this should not happen, but a missing parent key is handled safely rather than assumed impossible), and otherwise writing only the changed entries as a delta. Fails closed (returnsnullopt) if the named parent does not resolve.read_state_group_full_state(store, state_group_id)walks parent links back to the snapshot and applies every delta on the chain, newest first; bounded tomax_state_group_delta_depth + 1hops and cycle-detected — a missing group, a cycle, or an over-long chain returnsnullopt, never a partial map.find_state_groupreturns a group's own row (not its resulting state).update_forward_extremities(store, room_id, event_id, prev_event_ids, accepted)removesprev_event_idsfrom and addsevent_idto the room's extremity set whenacceptedis true, and is a no-op when false (soft-failed/rejected events never become extremities and never remove one);find_forward_extremitiesreads the current set. All four are implemented for SQLite, PostgreSQL, and the in-memory store's write-through path (thePersistentStoreBackend::memorybackend commits trivially, so unit tests exercise the in-memory bookkeeping directly — seetests/unit/test_state_groups.cpp).Forward extremities and the resolved-state memo (ADR-0600, ADR-0602).
PersistentStore::forward_extremitiesis aForwardExtremityTable, which counts each room's tips by the server of their sender. Hydration reads the tips before the events are indexed, sorebuild_forward_extremity_serverscharges them afterwards.PersistentStore::resolved_state_memois aResolvedStateMemo, in memory only and never persisted: the last resolved state of at most 256 rooms and 131072 rows, keyed by each room's sorted distinct state groups.set_event_status,replace_event_jsonandapply_room_snapshotforget the room.PersistentStore::state_before_memois aStateBeforeMemo(ADR-0694), also in memory only: at most 1024 entries, the oldest dropped first, each naming the group that holds the state before an event for a room, a resolution context and sorted distinct prev groups. The same three writers forget the room.In-memory indexes (F-07, F-15, ADR-0325). The three tables are mirrored in memory by
StateGroupTable,StateGroupEntryTableandEventStateGroupTable(StateGroupTable.hpp,StateGroupEntryTable.hpp,EventStateGroupTable.hpp), which own the rows and keep hash indexes bystate_group_idandevent_id, sofind_event_state_group,find_state_groupandread_state_group_full_statecost the rows they return and not a pass over every room's. They are filled by hydration andreload_roomthrough the tables' own methods; no schema changed. Retained federation PDUs (deferred_pdus) are likewise indexed in memory by the event IDs they are missing (PersistentStore::deferred_pdu_awaits, rebuilt after hydration; ADR-0326).A fresh database starts with no state groups or forward extremities; they are written as rooms are created and events ingested. Nothing is seeded at bootstrap. PostgreSQL bootstrap creating these tables is covered by
tests/integration/test_state_groups_flow.cpp. -
Phase B1 of spec-conformant PDU ingestion (0.12.13, ADR-0064): the phase A tables are now written and read on the live ingest path, not just declared in the schema. New module
merovingian::homeserver::state_bookkeeping(src/homeserver/StateBookkeeping.cpp):compute_state_before(store, room_id, policy, prev_event_ids)— refuses aprev_eventheld under another room or mapped to another room's state group (find_event_in_other_room, ADR-0583), then gives the state immediately before an event: a singleprev_event's own after-state directly (find_event_state_group+read_state_group_full_state, no resolution needed); severalprev_eventsresolved viaevents::resolve_state_v2over their after-states. Fails closed (StateBeforeResult::ok = false) if anyprev_eventhas no recorded state group, or resolution itself does (ADR-0063's walk cap) — never silently substitutes current state.compute_state_after(state_before, event_id, event_type, state_key)—state_beforeplus the event itself when it is a state event.record_event_state(store, room_id, event_id, prev_event_ids, state_after)— creates or reuses the after-state group (chained offprev_event_ids.front()'s own group as the delta parent, so unchanged state reuses it percreate_or_reuse_state_group's own dedup), mapsevent_idto it, and updates forward extremities.recompute_current_state(store, room_id, policy)— current state as the resolution over the room's forward extremities (one extremity: its after-state directly; several:resolve_state_v2), diffed against the cachedcurrent_stateand written only where changed, throughdatabase::store_state— the same call every other state write already used, sostate_transitions(unsigned.replaces_state) and the existing sync wake-up path (SyncNotifier::publish, called by the ingest caller on an accepted result) stay correct with no separate "state diff" plumbing.current_stateis a cache of this result, never an independent write target, from this path onward. A key the resolution does not hold is deleted withremove_current_state_rows, in one transaction with the membership rows it corrects, unless its row's event has no state group and no tip's state names it (ADR-0615).
Wired into
ingest_pdu_event(src/homeserver/LocalHttpRouter.cpp): state-before is computed before the event is persisted (a missing prev-state fails the PDU closed with the newfederation::PduIngestionStatus::missing_prev_state— not a rejection; the transaction it arrived in still returns 200 per spec, since a delayed-but-legitimate PDU is indistinguishable from one whose history we have not fetched yet. Since R03 (ADR-0165) the PDU is also retained indeferred_pdusand the status isdeferred;missing_prev_stateis what is left when nothing could be retained); the after-state group, forward-extremity update, and current-state recomputation happen after the event is durably stored, logged-but-non-fatal on failure (the same trade-off the pre-existing membership-persistence step in the same function already makes for the same reason: the raw event is already committed, so failing the PDU here would only cause pointless retries).Phase B1 completion (same 0.12.13 branch, follow-up commits): every local and inbound-accepted event path now goes through this bookkeeping, not just
ingest_pdu_event.homeserver::store_local_event(StateBookkeeping.hpp/.cpp) is the choke point every locally created event must go through — runs the same compute-state-before / store / record-after-state / recompute sequence asingest_pdu_event, but every failure is a hard failure (the request fails), since a local event has not yet been told "success" to anyone.homeserver::forward_extremities_for_new_eventgives local events theirprev_eventsfrom the room's real forward extremities (capped at the spec's 20 per event,events::max_prev_events_per_event, highest depth kept) instead of the previous, DAG-unaware "last event pushed intostore.events" heuristic.persist_composed_event(RoomService.cpp— used bycreate_room's initial-state chain, invite/join composition, and every ordinary send/state-send/redaction) calls it instead ofdatabase::store_event_with_statedirectly. Since 0.12.48 the state before is resolved once, while the event is composed (homeserver::basis_for_new_event), and handed tostore_local_eventas aNewEventBasis; when the extremities' states do not resolve, the basis cites only the newest extremity whose state reads alone, and itsretired_extremitiesleave theforward_extremitiestable with the event (ADR-0225).LocalHttpRouter.cpp'smembership_acceptor(a remote user'ssend_join/send_leave/send_knockinto a room we are resident in — the same trust boundary asingest_pdu_event) gets the identical state-before check and after-state/current-state bookkeeping.invite_handler's invite-event store gets explicitstatus = "outlier"(it is never part of this server's own timeline).- Since 0.12.48 (ADR-0228) a local event and a federated join commit in ONE
transaction with all of their bookkeeping, through
homeserver::store_event_with_bookkeeping: the event with itscurrent_staterow and membership projection, its after-state group, the mapping, the forward-extremity change, and everycurrent_statechange the resolution of the new extremities makes. Each part has a prepared form in the store (prepare_store_event_with_state,prepare_state_group,prepare_event_state_group,prepare_forward_extremities_update,prepare_current_state) and anapply_*that mirrors it in memory once the transaction commits; the single-step functions (create_or_reuse_state_group,set_event_state_group,update_forward_extremities,store_state) are prepare, commit, apply. A refusal at any part writes nothing. Before, each part committed on its own, so a 500 could leave the event stored and the room's tip moved, or a user joined with no state group. Inbound PDUs keep their separate commits. - The federated-join flow (
join_room/perform_federated_join,RoomService.cpp) seeds the room's state from thesend_joinresponse's state (stored asstatus = "outlier"events byingest_send_join_state, which returns the full state map alongside the joined-members list): the join event's after-state, that state plus the join, is one snapshot group (until 0.12.48, a delta on a separatejoin-snapshot:group holding the before-join state, which nothing read), and the join is made the room's sole forward extremity (since 0.12.48 it also retires every extremity an earlier stay left; before, a rejoin kept them) — so the first inbound PDU after a join no longer hitsmissing_prev_state. Since FED-1 (ADR-0083) only entries whose room is the joined room are stored, andauth_chainevents are stored as outliers with nocurrent_staterow;repair_missing_state_entries(start-up) likewise only promotesacceptedevents, never an outlier or a rejected one. - A source-tree guard test (
tests/unit/test_store_event_choke_point.cpp) fails the build ifdatabase::store_event_with_state(appears anywhere insrc/outside a reviewed, counted allowlist, and since 0.12.48 the same fordatabase::prepare_store_event_with_state(. PduStateConflictContext/state_conflict_resolver(the older, never-production-reached conflict-resolution plumbing this phase was meant to replace) were left in place here and removed in phase B2 (ADR-0064).
A real bug was found and fixed along the way:
database::store_state'sstate_transitionsinsert had noON CONFLICThandling, but its primary key is(room_id, event_type, state_key, event_id)— state resolution making an event current again after it was already superseded once (the spec's "reverts to an older event" case) re-inserts that same key, which both backends reject as a duplicate row; the write is now an upsert (ON CONFLICT (...) DO UPDATE SET previous_event_id = excluded.previous_event_id), with the in-memorystate_transition_indexmirror updated to match.Tests:
tests/unit/test_state_bookkeeping.cpp,tests/integration/test_pdu_ingestion_state_groups_flow.cpp,tests/integration/test_local_event_state_bookkeeping_flow.cpp, andtests/unit/test_store_event_choke_point.cpp(all[pdu_ingestion][state_groups]) cover the module directly, end-to-end throughingest_pdu_event, end-to-end through local send/create paths, and the source-tree guard, respectively;tests/integration/test_join_room_flow.cppcovers the federated-join seeding end to end;tests/integration/test_sync_handler_flow.cppcovers a resolution-driven reactivation reaching an incremental/syncclient. -
Token rotation lineage (ADR-0074).
refresh_tokens.predecessor_hashandaccess_tokens.predecessor_refresh_hash(bothTEXT NOT NULL DEFAULT '') are part of the v1 schema. A token pair minted byPOST /refreshrecords the hash of the refresh token it replaced, which stays valid until the pair is first used (seedocs/auth-identity.md).refresh_tokens.issued_at_ms(TEXT NOT NULL DEFAULT '0', the last column, like every other millisecond column of the schema) is the wall-clock time the token was minted,0when unknown; it is persisted by every insert and loaded by both backends (F-08). The in-memoryaccess_tokensandrefresh_tokensareCredentialTables (hash, (user, device), user and predecessor indexes), and the three secondary indexes above serve the sameWHEREclauses in SQL:user_idanddevice_id(the per-device and per-user revokes, and thedevice_id <>form, whoseuser_idprefix the composite index serves) andpredecessor_hash;token_hashis the primary key. Both backends store and hydrate the columns;database::revoke_refresh_tokens_with_predecessorsupersedes an unused pair when a refresh is retried. Hydration (homeserver::hydrate_local_database) also copies each access token'sexpires_atinto its session; before 0.12.13 it did not, so expiry was not enforced after a restart. -
M05 authenticated-media storage (ADR-0068). The
mediatable'slegacy_endpoint_visiblecolumn (TEXT NOT NULL DEFAULT 'true') is part of the v1 schema. New local uploads are stored withlegacy_endpoint_visible = false(seedocs/media-repository.md); the unauthenticated/_matrix/media/v3/downloadand/thumbnailroutes treat afalsevalue as a 404, while authenticated/_matrix/client/v1/media/...routes ignore it and serve the media. Adding a column to themediatable does not affect the federation worker table classification:mediaremains inworker_never_reads_tables, so the worker allowlist needs no change. -
No client input moves the sync stream counter. A
/syncsincetoken ahead of the server's streams is served as an initial sync and leaves the counter alone (ADR-0315).database::allocate_sync_stream_id()throwsstd::overflow_erroratINT64_MAXwith the counter unchanged, so an ID is never reused. A database holding a watermark at that ceiling refuses sync-stream writes until an operator repairs it. -
Offline
merovingian-db-migrateplanning scaffold. -
Database
runtimeandmigrationrole separation. -
Runtime hydration for users, sessions, rooms, memberships, events, client device listings, and durable media repository blobs.
-
Runtime event writes persist previous-event edges, auth-event edges, and Matrix event signatures in the same transaction as the event row.
-
Event depth column persisted alongside events so depth survives restarts.
-
Server signing keys scoped by server_name with composite primary key.
-
Runtime trust-and-safety report/review paths append durable policy audit rows and admin action rows.
-
Policy rule and media blob helpers upsert durable rows and hydrate them after SQLite/PostgreSQL reopen.
-
Committed statements are not retained (AUTH-11).
commit_persistent_transactionused to append every committedPreparedStatement, bound parameters included, toPersistentStore::prepared_statements, which nothing trimmed. The store now keeps nothing unless a test callsenable_statement_capture(store, capacity); the capture isPersistentStore::captured_statements, holds at mostcapacitystatements (clamped tomax_statement_capture_capacity, 65 536), drops the oldest first, and is empty and disabled by default.sensitive_values_are_redactedreads that capture and returns false when capture is disabled instead of passing on an empty buffer. Production code never enables capture: password and token hashes must not outlive the commit in process memory. -
PersistentStore::audit_logis a bounded window (AUTH-1): the newestmax_in_memory_audit_events(1 024) rows, oldest dropped first, withaudit_log_evictedcounting what left. The table is the complete record. Append throughappend_audit_event; hydrate throughremember_audit_event. Hydration keeps the newest rows in insertion order on both backends (SQLite byrowid; PostgreSQL bycreated_at_ms, thenctidwithin one millisecond, because retention deletes rows andctidalone no longer orders them, ADR-0489). Rows older thansecurity.audit_log_retention_daysare deleted by theretention-sweepjob (delete_audit_rows_older_than, at most 500 rows per transaction;created_at_msis the column that ages them, and a row stamped0is never deleted). The same job deletes the unusableaccess_tokensandrefresh_tokensrows (collect_unusable_credentials,delete_credential_rows,forget_credentials, ADR-0488).load_audit_events_by_type_prefix(store, prefix, limit)reads older rows straight from the table (newest first,limitclamped tomax_audit_query_rows, prefix bound and matched literally); in-memory stores answer from the window.append_audit_eventcutsactor,targetandreasonto 255 bytes on a UTF-8 boundary. Seedocs/observability-audit.md, "Audit volume bounds". -
Unit coverage for statement validation, executor gating, redaction, migration planning, and schema inventory.
-
Migration-plan validation coverage uses explicit hand-built plans, while current-schema upgrade coverage separately tracks schema-version bumps.
-
Integration coverage proving SQLite users, sessions, rooms, and events survive a homeserver runtime restart.
-
Upgrade coverage (
tests/integration/test_database_upgrade_flow.cpp, ADR-0216, ADR-0224).migrate_sqlite_schema_toandmigrate_postgresql_schema_tobuild a schema at an older version and stop there: they bootstrap an empty database, apply migrations forward to exactly the target, and hydrate nothing. They refuse a version this build does not know before touching the database, and a schema already past the target, since migrations never run backwards. Today the catalog has only v1, so the scenarios cover the v1 build-and-stop seam, never going past this build, an unopenable file being refused, tables without a ledger being refused (SQLite), a fresh database having every table's final columns and all six indexes, and a pre-1.0 ledger (rows 2 to 26) being refused on both backends. The "any older version opens at current" scenario is vacuous until a v2 exists. The 1.1 series starts with a previous-release fixture,tests/fixtures/upgrade/1.0.3/: the SQLite database, PostgreSQL dump and manifest the 1.0.3 release wrote through its own HTTP API (scripts/generate_upgrade_fixture.py, ADR-0216; the 0.11.13 fixture was removed when the migrations collapsed, 0.12.47, ADR-0224). Two scenarios ([fixture], one per backend) open it, runmigrate_*_schema_to, start the runtime on it and read back the users, access tokens, rooms, events, media, account data, pusher and device keys the manifest lists, then send a new event signed with the old release's key. Opening the 1.0.3 database applies migration 002 (the OAuth 2.0 tables), so the scenarios first assert the v1 to v3 steps ran: the schema version iscurrent_schema_version(), the ledger gained theoauth_tablesanddelayed_eventsrows, andoauth_clients,oauth_sessionsanddelayed_eventsexist and take a row (the fixture held none of them before). They then assert everything the manifest lists is still served. The PostgreSQL scenario needsMEROVINGIAN_TEST_POSTGRESQL_URIand reports itself skipped without it. -
Both stores read the migration ledger in numeric order (
ORDER BY CAST(version AS INTEGER)):schema_migrations.versionisTEXT, and ordering by it alone put'10'before'9'. -
profiles.fields_jsonholds a user's extended profile fields (m.tz, custom keys) as one canonical JSON object,{}when none (ADR-0478);update_profile_fieldsrewrites it. -
The PostgreSQL store hydrates
profileslike the SQLite store. Before 0.12.42 it wrote them but never read them back, so displaynames and avatars disappeared at every restart. -
Live PostgreSQL integration coverage: a dedicated GitHub Actions workflow (
.github/workflows/postgres-integration.yml) starts a PostgreSQL 16 service, provisions amerovingian_migrationrole with DDL grants and amerovingian_runtimerole with table-level DML grants, and runs every integration scenario tagged[postgresql](ADR-0511), most of them intests/integration/test_postgresql_persistence_flow.cpp. Scenarios assert: schema is bootstrapped tocurrent_schema_version, previously persisted rows survive a close/reopen, and a runtime-role session is denied DDL. -
PostgreSQL role helpers (
set_postgresql_role,reset_postgresql_role,current_postgresql_user) in PostgresqlStore.hpp let runtime callers switch identities inside a single connection. Role names are validated against PostgreSQL identifier shape (alphanumeric plus underscore, ≤ 63 chars) before being interpolated into theSET ROLEstatement, so the API is safe to call with operator-supplied role names. -
Bootstrap table-identifier validation (issue #442): PostgreSQL's
create_table_if_missing_sqlpreviously concatenatedtable.namedirectly intoCREATE TABLE IF NOT EXISTS <name> (...)DDL with no validation or quoting, unlike the SQLite path. It now mirrorsSqliteStore.cppexactly:schema_table_is_core()gates the name to the compiled core-table set, andquote_sqlite_identifier()(generic ANSI double-quote identifier quoting, despite the name — valid for PostgreSQL too) wraps it. Current callers only ever pass hardcoded core table names, so this is defense-in-depth against a future caller passing a non-core or attacker-influenced name. -
Client transaction-idempotency dedup via the version-1
client_txn_idstable. Keyed on(user_id, device_id, room_id, event_type, txn_id): a transaction ID is scoped to one device (F-52, ADR-0380).room_idis empty string for to-device sends.device_idis the device that made the request (empty when it had none, which is a scope of its own); it also gives the sending device itsunsigned.transaction_id.created_at_msis when the record was made (the store stamps it). The in-memory rows are indexed by event ID and by the key (client_txn_event_index,client_txn_key_index), rebuilt after hydration and extended on every store, so the idempotency check and the per-event transaction-ID lookup are hash lookups. The indexes are trusted only whileclient_txn_indexed_rowsequals the row count; a caller that pushes rows directly falls back to a scan until the next rebuild. Only the to-device rows (room_idempty) are pruned: theclient-txn-prunejob deletes those older than 7 days (database::prune_to_device_client_txns, ADR-0381) from the table and the mirror and rebuilds both indexes; room-send rows stay, becauseunsigned.transaction_idis served from them (ADR-0258).event_idstores the assigned event ID for room sends and is empty for to-device entries. Both SQLite and PostgreSQL hydrate rows on startup. Handlers check the table before processing and store the result after a successful send, allowing clients to safely retryPUT /rooms/{roomId}/sendandPUT /sendToDevicerequests. -
to_device_messages.stream_idis TEXT, soORDER BY stream_idsorts "10" before "9". Both backends calldatabase::sort_to_device_messagesafter hydrating to restore numeric order, anddrain_to_device_messagesand the sliding syncto_deviceextension sort what they return, sonext_batchnever acknowledges a row that was not sent (ADR-0426). -
PUT /sendToDevicewrites its localto_device_messagesrows and itsclient_txn_idsrow in ONE backend transaction (database::enqueue_to_device_messages_with_client_txn, R02, ADR-0164). The per-recipient age and count evictions (CSAZ-10, ADR-0117) are part of that transaction:enqueue_to_device_messageand the batched function share one prepare step that builds the DELETE/INSERT statements without touching memory, and the in-memory mirror is updated only after the commit succeeds. A failed commit leaves the store unchanged and the transaction unrecorded, so the handler answers500 M_UNKNOWNand a retry with the same transaction ID is processed rather than replayed. Stream ids are allocated before the commit (allocate_sync_stream_idpersists the watermark itself); a failed commit skips them, it never reuses them. A request with no local recipient records the transaction alone through the same function. -
to_device_messagesis indexed by (user, device) and by user (to_device_recipient_index,to_device_user_index, ADR-0618). An erase moves the last row into the gap, so the vector has no meaningful order. Send and sync read only the recipient's rows, and the per-user limits evict a user's oldest row first, whichever device holds it. -
A federated join persists the resident's answer in few transactions: the state array in one (
ingest_send_join_state), the auth chain in one (prepare_outlier_events_batch, committed bycommit_prepared_outlier_batchwithruntime.mutexreleased, then applied) and the member rows in one (store_memberships_batch), counted bycommitted_transactions(C03, ADR-0714). The state-array and member commits keep the lock: they writecurrent_stateand membership rows. -
server_signing_keysholds this server's keys and every cached remote key;server_signing_key_indexgroups the rows by server name, and a reader asks for one server's rows (snapshot_server_signing_keys_of) instead of copying the table (C27, ADR-0713). -
invitesis indexed by (room, user), by what a record is charged to and by origin (invite_key_index,invite_quota_index,invite_origin_index, ADR-0619).invite_is_over_store_capsis asked by every writer before it builds a statement. The runtime rebuilds the indexes once it has setlocal_server_name, which hydration runs before. The same writers keep a byte ledger of the remote invites, per sending server and in all (invite_origin_bytes,remote_invite_bytes_total; limitsmax_remote_invite_bytes_per_origin64 MiB andmax_remote_invite_bytes_total256 MiB, ADR-0707), rebuilt byrebuild_invite_indexesand trusted only whileinvite_bytes_rowsequals the row count. -
An inbound
m.direct_to_deviceEDU writes all of itsto_device_messagesrows in ONE backend transaction (database::enqueue_to_device_messages, ADR-0166), through the same prepare/apply steps and evictions as the single-message and/sendToDevicewriters. A failed commit stores none of the EDU, and the sink records its(origin, sender, message_id)in the in-memory replay window only after the commit, so the origin's resend is delivered in full. -
database::reload_room(store, room_id)re-reads a single room's rows (room, membership, invites, events, state, the room's state groups with their entries and its events' state-group mappings (ADR-0064; federation reads judge an event on its recorded group and fail closed without one), the room's forward extremities (1.0.1, ADR-0733), and the event-relation tables scoped to that room's events) from the backing database and replaces this store's in-memory copy of that room. Implemented for both SQLite and PostgreSQL with parameterised, room_id-scoped queries (never a full-table re-read). A no-op (returnstrue) for thememorybackend. Its read half,load_room_snapshot, is what main runs for a federation worker'sroom_syncreload; the worker applies the answer withapply_room_snapshot(StoreReadGuard::reload_room_from) — see architecture.md, "Federation worker room staleness". -
database::fill_store_from_room_snapshots(store, snapshots)builds a store from room snapshots in one pass: each table is inserted once and each index rebuilt once, where areload_roomper room would cost rooms times rows (EventTable::insert_allmerges the stream order of everything it holds).database::load_room_ids(store)lists every room ID, sorted, from one statement, anddatabase::public_signing_keys_of_server(store, name, cap)copies a server's public key columns from memory underserver_signing_keys_mutex, never a row that holds a secret. They are what main serves a federation worker, which opens no database (1.0.1, C44, ADR-0733): the worker's store is in memory, filled at start throughRuntimeStartOptions::fill_store, andrefresh_remote_signing_keysasks main throughPersistentStore::remote_signing_key_sourcewhen that is set, taking only the named server's rows without a secret from the answer. -
database::reconstruct_event_relations(store)re-derives everyPersistentEvent::prev_event_ids/auth_event_ids/signaturesfrom the flatevent_edges/event_auth/event_signaturestables. Those fields are only populated directly when an event is stored fresh within a process's own lifetime (store_event_with_state); hydrating a store from disk previously left them silently empty. Called at the end of bothopen_sqlite_persistent_store/open_postgresql_persistent_storeand as part ofreload_room's snapshot merge. Idempotent. -
database::store_event_with_state()is split intoprepare_store_event_with_state(),commit_persistent_transaction(), andapply_store_event_with_state(). ThePreparedStateUpdatestruct holds the event, optional state event, and the prepared statements.preparevalidates in-memory pre-conditions (room existence, duplicate event id) and builds the statements.commitexecutes them on the backend without holding any room lock, so different rooms can commit concurrently.applymirrors the committed rows into the in-memory vectors with an idempotent duplicate guard. Used by the per-room inbound PDU path so the global runtime lock is released before the database commit. The combined helper remains for callers that do not need the split.
The homeserver runtime can now use SQLite for local persisted state when
database.backend=sqlite is configured. Dependency-specific SQLite types remain
inside the database module and do not leak into homeserver services.
The boundary provides these guarantees:
- Application code submits named prepared-statement shapes, not ad hoc SQL execution requests.
- Invalid statement names fail before reaching the executor.
- Obvious multi-statement/comment-shaped SQL is rejected at the boundary.
- Sensitive parameter values can be summarized without leaking the value.
- Migration plans are contiguous and direction-aware.
- Core table inventory is explicit and test-covered.
- Media rows include hash algorithm, digest, quarantine state, removal state, and durable blob references before runtime media writes are accepted.
- Fresh SQLite database files are created with the current schema and recorded migration metadata.
- Existing SQLite database files apply pending project-owned migrations before
runtime state is hydrated. A database whose
schema_migrationsledger holds an upgrade row this build does not know (for example the rows 2 to 26 written by pre-1.0 builds up to 0.12.46) is refused at open, before any migration or hydration (schema_ledger_is_known), on both backends. - Auth and room mutations fail the request when required persistent writes fail. Token revocations (logout, logout-all, refresh rotation and reuse, device deletion, password change) are checked against the store after the write, and a revocation that did not persist answers 500 (DB-5, ADR-0116).
- A media upload is answered with an
mxc://URI only after its media row and blob are committed together (commit_local_media_upload, one transaction). A failed commit answers 500 andmedia::rollback_local_media_uploadremoves the record and releases the blob reference in memory, so a restart cannot lose media a client was told was stored (DB-5). Media moderation commits flags, blob references and its audit row together (commit_local_media_moderation). - Device/token, refresh-token, room/membership, and event/current-state mutations are committed before in-memory runtime state is updated.
- Signed event DAG rows are committed before the runtime room timeline is updated.
- Multi-row runtime mutations commit through one backend transaction so partial login, room, or state-event writes are rolled back.
- PostgreSQL connection strings are accepted only in explicit URI or libpq key/value form. Connection-opening diagnostics (DB-9) use a fixed redacted identity and generic parser/connection-status refusal reasons rather than reconstructing a supplied option summary or forwarding library error text. Host/database values are suppressed too because they can contain sensitive material. These are first-GREEN coverage of the existing guard, not a new production repair. Native Windows socket execution, live authentication/TLS/ready branches and general SQL-result error confidentiality are not established by these checks. See ADR-0139.
- Remote PostgreSQL TLS admission (DB-8): every effective host and host-address
entry is checked. Unless all destinations are local Unix sockets or recognized
loopback literals, parsed connection options must explicitly select
sslmodewith the valueverify-full. An omitted or empty mode cannot inherit its way through admission viaPGSSLMODE; the minimal worker environment deliberately excludes that default. Service indirection and insecure remote opt-outs remain unsupported. The opener disables preferential GSS encryption and database-name expansion; private credential readers, role handling, binary bindings and transaction semantics are unchanged. Certificate trust and sandbox accessibility must be provisioned for each process. A deterministic unit regression covers the admission rule. Live TLS/hostname/certificate refusal and worker trust-file access are not proven by admission tests, and these local compatibility checks do not establish remote TLS transport. See ADR-0138. - Runtime startup requires
database.role=runtime; offline migration planning requiresdatabase.role=migration. - No store function may clear a
revokedflag once set — revocation is a one-way transition, enforced by the store's API surface rather than by caller discipline (seerevoke_tokens_for_user_except_deviceabove and ADR-0052). - Binary (
BLOB) columns round-trip byte-exactly on both backends. The columns aremedia_blobs.bytesandserver_signing_keys.secret_key— everyBLOBinmigrations/*.sqlandSchema.cpp; a new one must follow this mechanism.BoundValuecarries abinaryflag, and a write must set it for everyBLOBparameter. SQLite binds every parameter by explicit length and reads columns bysqlite3_column_bytes, so it ignores the flag and is unchanged. On PostgreSQL,execute_prepared_statementbinds abinaryparameter in libpq's binary wire format (paramFormats= 1, explicitparamLengths, declared typebytea), so arbitrary bytes — NULs, invalid UTF-8, backslashes — arrive exactly and nothing is escaped or truncated. The read side is one generic rule rather than a per-table special case:load_result_rowsasks libpq for each result column's type (PQftype) and decodes everybyteacolumn from PostgreSQL's\xhex text form back to raw bytes, so any query that selects aBLOBcolumn gets bytes, not hex. Each connection pinsSET bytea_output = 'hex'at open so a role or database default ofescapecannot change that form; abyteavalue that is not valid hex fails the query rather than returning empty or garbled bytes, because an emptysecret_keyreads as "no signing key" and would make the server mint a new identity. Until DB-1 was fixed onlymedia_blobs.byteswas decoded, so the signing secret came back as its own hex text after a restart and the server could not load its signing key (0.12.14 audit). Existing rows hold the raw bytes intact, so no data migration was needed. - Identity-server unbind credentials (
account_threepids.client_secretand.sid) are bound as sensitive values, so they never reach a query trace or diagnostic log. They remain plaintext at rest; that is the documented residual risk indocs/threat-model.md§IS-delegated bind/unbind/requestToken. - Migration plans are serialised across processes. Each step commits in its own
transaction, so the window that needs protecting is between the
transactions: PostgreSQL takes a
pg_advisory_lockfor the whole plan under an RAII lease (a crashed migrator's session-scoped lock disappears with its connection, so recovery needs no operator action), and SQLite re-reads theschema_migrationsledger inside each step'sBEGIN IMMEDIATEand skips a step the winner of a race already applied. These are deliberately not one shared mechanism — see ADR-0059.
The v1 push_rules schema table remains unused: a user's push rules are kept as
their m.push_rules global account data, applied over the current server defaults
(ADR-0204), because that table's primary key cannot express a rule's kind. Federation queue
tables (federation_destinations, federation_transactions) are hydrated and
used by the runtime on both backends; media metadata and policy rows are also
persisted and hydrated.
merovingian-db-migrate(src/db_migrate.cpp) remains an offline planning tool: it prints a migration plan and does not connect to a database. Runtime startup applies pending schema migrations automatically.- PostgreSQL role separation is enforced in the live server path. When roles
are configured,
open_postgresql_persistent_store()assumesdatabase.migration_rolefor DDL (the fresh-database v1 bootstrap and any later pending migration), resets it, and then assumesdatabase.runtime_rolefor runtime access. The fresh-database bootstrap fails closed if the migration role cannot be assumed, so the migration role owns every table on a fresh install; a single-role deployment (no migration role) bootstraps as the login role. Live role scenarios run in thepostgres-integrationworkflow because they require PostgreSQL and the role environment variables. Seepackaging/postgresql/provision-roles.sql. - The schema's v1
push_rulestable is unused: push rules are kept asm.push_rulesaccount data (ADR-0204, see above).
The federation worker (merovingian-fed-worker) opens no database on either backend (1.0.1, run-3 finding C44,
ADR-0733). Its configuration copy
is switched to the memory backend with every database location cleared, and main fills its store over the IPC
channel: the room list, each room's snapshot (load_room_snapshot, read by main in one transaction closed before the
first page is sent), and on a key-cache miss one server's public signing keys from main's memory. A compromised
worker therefore holds no descriptor, lock, login or Landlock grant on main's database.
Until 1.0.1 a PostgreSQL worker connected with a separate, SELECT-only login (ADR-0062 part 2), configured by
federation.worker.database_uri_file and federation.worker.allow_shared_database_credentials and provisioned by a
provision-federation-worker-role.sql script. That arrangement is retired: both keys are refused at parse with an
error naming the key, main reads no worker URI file and hands the worker no credential, and the script is deleted
(ADR-0736). After upgrading, remove the two lines
and drop the worker's role (DROP ROLE) once no worker uses it.
TableLoadProfile::federation_worker and database::federation_worker_table_allowlist remain: a store opened with
that profile hydrates only the allowlisted tables and never selects server_signing_keys.secret_key. No worker opens
such a store any more; in the worker the profile only turns on asking main for a server's keys on a miss
(LocalDatabase::remote_signing_keys_read_through).
tests/unit/test_worker_table_load_profile.cpp asserts every table
migrations/*.sql creates falls into exactly one of three buckets, so a new
migration's table cannot go unclassified:
- Worker allowlist (13 tables, profile-gated reads —
database::federation_worker_table_allowlist):rooms,membership,current_state,events,event_edges,event_auth,event_signatures,room_aliases,server_signing_keys(column-restricted, see above),state_groups,state_group_state,event_state_groups,forward_extremities(the last four added in 0.12.13 by ADR-0064 phase A —state_groupswas vestigial before this and is now moved out of the never-reads list below). - Worker never reads (53 tables):
users,devices,access_tokens,refresh_tokens,federation_destinations,federation_transactions,invites,state_transitions,sync_stream_watermark,event_stream_watermark,stream_token_epoch,device_keys,one_time_keys,fallback_keys,cross_signing_keys,key_signatures,key_backup_versions,key_backup_version_counters,key_backup_sessions,media,media_blobs,remote_media,audit_log,admin_actions,policy_rules,account_data,room_account_data,to_device_messages,device_list_changes,presence_state,filters,profiles,account_threepids,client_txn_ids,pushers,notifications,openid_tokens,login_tokens,appservice_txn_cursor,appservice_outbox,deferred_pdus,receipts,appservice_directory,media_pending_uploads,oauth_clientsandoauth_sessions(migration 002; every OAuth 2.0 endpoint runs in main),delayed_events(migration 003; the federation worker sends no events and holds no signing key, so it never reads a scheduled event), plus six tablesSchema.cppdeclares but that no runtime code path (main's or the worker's) ever populates or queries —event_json,key_backups,push_rules,rate_limits,room_versions,state_group_edges— vestigial from earlier schema iterations, confirmed by grep to appear nowhere outsideSchema.cpp's own DDL declarations.state_group_edgesremains vestigial after ADR-0064 phase A: the design uses parent links onstate_groupsitself for the delta chain, not a separate edges table, so phase A deliberately left it alone. Whether to drop it is an open question for a later phase, not decided here.federation_destinations/federation_transactionsspecifically are read only byfederation::DispatchWorker, which never starts in the worker process: its construction (LocalHttpRouter.cpp, guarded inside the same lazily-wired callback block) requiresruntime.database.signing_secret_keyto hold a real 32-byte secret, and the worker's runtime never loads one (crypto_provider_overridden = true, per ADR-0015/ADR-0062 part 1) — the construction check fails and returns before the worker ever touches either table, main or not.profiles,device_keys/one_time_keys/fallback_keys/cross_signing_keys/key_signatures, anddevicesare read only byprofile_query_provider/device_keys_query_provider/one_time_keys_claim_provider/user_devices_provider— all four overridden by the worker to relay to main over IPC instead (src/federation_worker/AGENTS.mdrule 3), so their default, table-reading implementations are wired but never invoked inside the worker. - Read by every process, not profile-gated (1 table):
schema_migrations.
rooms.directory_public is TEXT NOT NULL DEFAULT 'false' in the v1 schema. It is independent of room join rules; publication is an explicit, durable choice (ADR-0099). Creation persists publication with the room and initial membership. Visibility changes commit before updating the store and runtime mirrors. PostgreSQL scoped snapshots bind one room ID and join relation tables to events, avoiding the 128-parameter ceiling for large rooms. Media moderation commits flags, removal blob state and required administrative audit rows together; mirrors change only after commit (ADR-0100).
database::MembershipProjection carries the membership row, and the invite metadata change, that an
m.room.member event implies. store_event_with_state and prepare_store_event_with_state take one and
write it in the event's own transaction (an upsert on membership, and an upsert or keyed delete on
invites), so the event, its current_state row, the row and the invite change are durable together or
not at all, on both backends. store_membership_projection writes a row and invite change with no event in
one transaction. reconcile_memberships_with_current_state runs at main's hydration (TableLoadProfile::full only; the federation worker never writes main's rows): a row is rewritten to the
current member event's membership when the row is missing, says join while the event does not, or is older
than the event; a newer row is kept. Corrected rows get a fresh stream ordering and are corrected in memory
even if the write fails. No schema change (ADR-0149).
Since 0.12.59 a knock projection may carry the invite record too: a knock on a room on another server
keeps its signed knock event and the resident's verified knock_room_state there, which /sync shows as
knock_state (ADR-0243). Its resident_server column names the server that accepted the knock, where
POST /leave sends the knock's withdrawal (ADR-0251); it is empty for an invite. Join, leave and ban end
it, and an invite answering the knock replaces it. Every reader looks the record up only for a user whose
membership is invite or knock, or deletes it; a new reader must check the membership too.
Persisted writes reuse connections from a per-store ConnectionPool instead of opening one per
transaction: at most database.pool_size (16) are in use at once, and a write waits for a free one.
PostgreSQL connections assume the runtime role once when opened; a pooled one is reused only while idle
(PQTRANS_IDLE) with nothing to read on its socket, so one the server has closed is replaced. SQLite
connections keep their pragmas and foreign_keys=ON. Reads, migrations and the worker's read-only store
still open their own connections. write_connections_opened reports how many the pool has opened
(ADR-0154). A slot is given back on every way out of an acquisition, a throwing open or liveness check
included: waiting for a slot has no timeout, so a slot that leaked would be lost for good.
An SQLite database file is created 0600 and must not be a symlink. Directories that already exist are
followed as the kernel resolves them, symlinks included (an operator's /var/lib/merovingian pointing at a
data volume); any directory the server creates is made 0700, and everything below it is opened without
following links (DB-10).
Those checks never open an existing database file: closing any descriptor of it would release every POSIX
lock the process holds on it, including another connection's write lock (ADR-0157). An existing file is
checked by name (fstatat with AT_SYMLINK_NOFOLLOW), and a read-only connection reads the header, to
refuse WAL, through SQLite's own handle before any statement runs.
A read-only connection (the worker's store and room reloads, media blob and audit reads) can be told a
journal is hot when it is not: SQLite checks that the journal exists and then that no process holds
RESERVED, and a writer that commits between the two checks has already deleted its journal and unlocked.
A read-write connection settles that under EXCLUSIVE; a read-only one fails at once with
SQLITE_READONLY_ROLLBACK. The open-time pragmas, prepare and every read's first step retry that one
result every 10 ms for up to 2 s; a journal that is really hot still fails the read, and main's next
read-write connection rolls it back (ADR-0159). A failed room snapshot logs room_snapshot.read_failed
with its stage and extended SQLite code, and a connection whose open-time pragmas fail logs
connection.rejected with the code.
A room reload (reload_room) reads the room's twelve tables (the forward extremities since 1.0.1) in one read transaction, so its rows
come from one committed state: BEGIN DEFERRED on SQLite, whose first read takes SHARED until the
end (main's commits wait for it), and REPEATABLE READ READ ONLY on PostgreSQL. The worker's
whole-store startup load stays one statement per table, so it never holds SQLite's SHARED lock for
the whole load (ADR-0161).
PersistentStore::events is a database::EventTable (include/merovingian/database/EventTable.hpp),
not a std::vector. It owns the in-memory rows of the events table and keeps five indexes over them
(the room-and-time index since 0.12.64, the per-room stream-order and relation indexes since 0.12.72), all holding positions in its row vector so they survive reallocation (ADR-0215):
- by event ID.
events.find(id)andevents.contains(id)are hash lookups, andevents.find_in_room(room_id, id)adds the room check.events.with_id(id)is a range of zero or one rows, for a loop whose body runs for the match. Use these, never afind_ifover the rows. - by stream order.
events.in_stream_order()visits every row by ascendingstream_ordering. Rows with equal stream orderings come in the order they were added. Reverse it for newest first. Iterating the table itself visits arrival order, which is not stream order: SQLite hydration has noORDER BY, PostgreSQL hydrates in event-ID order and moves a row whose status is updated, and stream orderings commit out of order across rooms. - by room and stream order (ADR-0435).
events.room_in_stream_order(room_id)visits the rows of one room, of every status, in the orderin_stream_order()visits them, at the cost of that room's rows. It is what/sync,/messages,/context,/search,initialSync,/relations, the sliding sync builder,read_markers_forandcount_unread_by_threadread a room through; neverfor (x : store.events)plus aroom_idtest on a request path.insert,insert_all,erase_if,modify(a changedroom_id) andclearkeep it current. - by room and time (ADR-0300).
events.nearest_by_time(room_id, ts, direction, accept, max_examined)seeks the timeline event of one room nearest anorigin_server_ts, ordered by (origin_server_ts,depth,stream_ordering), the boundary inclusive, stepping over rowsacceptrefuses and offering at mostmax_examined(database::max_time_search_examined, 1000) to it. It backsGET /rooms/{roomId}/timestamp_to_eventand the federation endpoint of the same name. A row is in it only while its status is notoutlier,rejectedorsoft_failedand its JSON has an integer top-levelorigin_server_ts;modifymoves a row in or out when a mutation changes that. There is no migration: the key is parsed from the JSON once, when a row enters the table. - by relation target (ADR-0430).
events.for_each_relation_child_newest_first(target_event_id, kind, visit, max_examined)visits the rows of oneRelationKind(m.replace,m.thread,m.reference) whosecontent."m.relates_to"namestarget_event_id, newest listed first;m.annotationand other types are not indexed.relation_child_count()is the size of the lists. It backs bundled aggregations (unsigned.m.relations). A row is listed whatever its status or room (the reader judges both); a target ID that is empty or over 255 bytes is not indexed.modifyre-derives the key, so a redacted child (nom.relates_toleft) leaves the list. There is no migration: the key is parsed from the JSON when a row enters the table, only for a row whose JSON contains"m.relates_to".
Rows are read-only through the table. The only ways to change it are:
| Change | Method | Callers |
|---|---|---|
| Add one event (refused if its ID is already held) | insert() / push_back() |
store_event, apply_store_event_with_state |
| Add many, sorting their stream order in one pass | insert_all() |
SQLite and PostgreSQL hydration, reload_room, room snapshots |
Change a row's fields other than event_id and stream_ordering |
modify() |
set_event_status, replace_event_json (redaction), reconstruct_event_relations |
| Remove rows, renumbering both indexes in one pass | erase_if() |
reload_room |
| Move every row out | extract() |
room snapshot loaders |
modify() puts event_id and stream_ordering back if a mutation changes them. The table has no lock
of its own: like every other PersistentStore member, it is guarded by runtime.mutex in the homeserver.
users, memberships (by room and user), devices, device_keys, cross_signing_keys, one_time_keys,
fallback_keys, key_backup_versions and key_backup_sessions each have a position index beside the vector
(RowKeyIndex, RowGroupIndex). It is trusted only while its row count equals the vector's; a writer heals a stale one,
hydration rebuilds all of them (rebuild_key_indexes), and a reader of a store edited directly scans. Deleting a one-time
key, fallback key, device key or backup session moves the last row into the gap, so those vectors have no order
(ADR-0605). See src/database/AGENTS.md.