Skip to content

Latest commit

 

History

History
1413 lines (1365 loc) · 103 KB

database-persistence.md

File metadata and controls

1413 lines (1365 loc) · 103 KB

Database persistence

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.

Included now

  • 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() is 3, and migrations/ holds 001_initial_schema.sql, 002_oauth_tables.sql and 003_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 to ALTER on now being part of the table's CREATE, 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_device and idx_refresh_tokens_predecessor (idx_events_room_id and idx_state_groups_room_id serve the per-room reload, ADR-0490; stream_ordering is 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 in src/database/Migration.cpp has three upgrade steps (version 1, initial_schema; version 2, oauth_tables; version 3, delayed_events) and three downgrade steps, newest first (drop_delayed_events to version 2, drop_oauth_tables to version 1, then drop_initial_schema to version 0, which drops every v1 table). migrations/001_initial_schema.sql is the frozen record of v1, 002_oauth_tables.sql of version 2 and 003_delayed_events.sql of 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, all TEXT NOT NULL, the integers written as decimal text like every other *_ms column). A row is kept as first written (store_oauth_client answers existing); only last_used_at_ms changes, 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)), with idx_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, then remember_oauth_session after the commit) and deleted by delete_device in 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, then user_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, all TEXT NOT NULL: the integers are decimal text, the two booleans are true or false like users.deactivated, status is scheduled, sent, cancelled or failed, and the columns that do not apply are the empty string, or 0 for finalised_at_ms), with idx_delayed_events_user_id on user_id, which serves the delete at deactivation. No foreign key. device_id is the scheduling request's device and is not deleted with it: a delayed event belongs to the user.
    • Caps, enforced by store_delayed_event and the finalising functions (DelayedEventStore.hpp): delay_id 64 bytes, content_json 65,536 bytes, error_json 1,024 bytes, 255 bytes for user_id, device_id, room_id, event_type, state_key, txn_id and event_id, and delay_ms from 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 compute Retry-After from 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.mutex serialises 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, status and the two booleans exactly the texts the store writes, a scheduled row's delay within 1 to the maximum, and a sent row'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 reads scheduled, 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 through apply_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.0 there was nothing to upgrade from: a database whose schema_migrations ledger 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. Since v1.0.0 the v1 migration and migrations/001_initial_schema.sql never 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_state updates the users table suspended/locked columns and mirrors the new state into the in-memory PersistentUser, 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 single UPDATE ... WHERE user_id = $1 AND device_id <> $2 per token table — so a password change with logout_devices: true can 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 setting revoked = false, is removed from both the header and the implementation. That UPDATE could 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 a revoked flag; 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_at column (TEXT NOT NULL DEFAULT '', empty = no expiry / legacy) folded into the version-1 initial schema. store_access_token, store_refresh_token, and store_device_and_access_token bind the epoch-millis text of the token's expires_at, and SQLite/PostgreSQL hydration parse it back into the PersistentAccessToken / PersistentRefreshToken expires_at field (std::optional<system_clock::time_point>, nullopt for 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 by sodium_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_keys has 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 through store_server_signing_key, find_server_signing_key and snapshot_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 under old_verify_keys; empty means current. PersistentServerSigningKey::expired_ts is 0 for a current key. A retired row has valid_until_ts equal to expired_ts, capped at the fetch time, and it is one-way: store_server_signing_key keeps 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 of refresh_remote_signing_keys select the column, and nothing deletes or prunes a row by valid_until_ts.

  • Media bytes are not hydrated (ADR-0119). The SQLite and PostgreSQL loaders read media_blobs without its bytes column; prepare_media_blob_read (under the runtime mutex) and read_media_blob (without it) fetch one blob's bytes per request. A media upload writes its bytes in commit_local_media_upload; a deduplicated upload writes only ref_count (update_media_blob_ref_count). Remote media is admitted by commit_remote_media_admission, which in one transaction deletes the media and remote_media rows of the cache entries the admission displaces, sets those blobs' reference counts (clearing bytes at zero), and inserts the new media, blob and remote_media rows. remote_media carries local_media_id and fetched_at_ms. Since 0.12.81 (ADR-0574) a SQLite download larger than database::media_blob_chunk_bytes (256 KiB) is read in chunks: read_media_blob_chunk reads one chunk on a short-lived read-only connection. PostgreSQL stays buffered, because each substring() of a compressed TOASTed bytea decompresses the whole value.

  • room_aliases is held in memory as database::RoomAliasTable (F-03, ADR-0581), not a vector: it owns the rows and indexes them by alias, by room ID and by creator, so find_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_user and max_room_aliases_per_room bound the rows.

  • room_aliases carries creator_user_id (CSAZ-12, ADR-0127): store_room_alias writes the creating user, and delete_room_alias removes a mapping, in the database and in memory, for DELETE /directory/room/{roomAlias}.

  • Binary payload columns are BLOB (#448): media_blobs.bytes holds raw media content and server_signing_keys.secret_key holds 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_sql is shared, backend-agnostic DDL text written in SQLite's dialect; PostgreSQL has no BLOB type, so PostgresqlStore.cpp's translate_ddl_types_for_postgresql() rewrites BLOB to BYTEA (whole-word, so it cannot mangle an identifier) wherever a SchemaTableDefinition becomes DDL for that backend — both the bootstrap path and any later migration step.

  • Every SQLite connection pins PRAGMA secure_delete = ON, PRAGMA trusted_schema = OFF and PRAGMA synchronous = FULL at open (#447), and a read-write connection also pins PRAGMA journal_mode = DELETE, so an environment that installs global default pragmas cannot silently weaken durability. The stores also enable PRAGMA foreign_keys = ON on 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.

  • libpq dependency 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_key deletes 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 is database::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 for max_fallback_keys_per_device (ADR-0594).

  • key_signatures_for_target reads key signatures through an in-memory index keyed by target user (PersistentStore::key_signature_target_index). The index is maintained by store_key_signature, rebuilt after SQLite and PostgreSQL hydration, copied with the store, and ignored (lookup scans) while its row count differs from key_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_signatures writes a whole upload in one transaction; for_each_signer_key_signature and key_signatures_for_target_key read through them.

  • find_current_state_event_id and for_each_room_state_row read current state through in-memory indexes over PersistentStore::state (state_position_index by room, type and state key; state_room_index by room). Every writer in PersistentStore.cpp keeps them in step, reload_room and both hydration paths rebuild them (rebuild_state_index), and a lookup scans while state_indexed_rows differs from state.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 of state must call note_state_row_appended or rebuild_state_index. Both count the rows they look at in state_rows_examined, so a test proves a reader's cost by count.

  • room_state_revision counts 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 calls note_room_state_changed after 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_state moves it on apply as well as on prepare, ADR-0697).

  • delete_federation_transactions removes 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 contains 001_initial_schema.sql, the frozen record of the v1 schema, and 002_oauth_tables.sql. merovingian-db-migrate --plan A B --migrations DIR takes only the files whose version is between A (exclusive) and B.

  • sync_stream_watermark table stores the highest allocated sync_stream_id and is updated by database::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 highest sync_stream_id found 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_changes holds at most one row per (observer_user_id, subject_user_id). database::record_device_list_change() and record_device_list_changes() replace a pair's earlier row (a DELETE then an INSERT in one transaction) so it carries the newest stream position and the latest change_type; /sync reports 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 by sync::collect_device_list_delta() on read, and are replaced the next time their pair changes.

  • event_stream_watermark table stores the highest allocated timeline stream_ordering and is updated by homeserver::allocate_stream_ordering() (via database::persist_event_stream_watermark()) on every allocation. Some allocations — membership stream positions — are not backed by a persisted event row, so a counter rebuilt from max(events.stream_ordering) alone regresses across restarts, which puts clients' persisted sliding sync pos/since tokens ahead of the live stream and invalidates them all on every restart. Hydration takes the maximum of the persisted watermark and the highest event stream_ordering, then persists the merged floor so the row exists even on a first start.

  • stream_token_epoch table 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(), through database::persist_stream_token_epoch()), keeps it across restarts, and refuses to start when it can be neither read nor written. merovingian-server --after-restore replaces it with a different random value, so every sync token issued before a restore is foreign. Sync tokens, sliding sync pos and the to_device extension's since carry it; a token from another epoch acknowledges nothing. The federation worker never reads it.

  • state_transitions table records the previous event_id for every state-event tuple replacement. It is populated by database::store_state() and the split prepare_store_event_with_state() / apply_store_event_with_state() helpers. The in-memory store keeps a hash index over state_transitions so that client_event_with_id() can inject unsigned.replaces_state into 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_events and auth_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_threepids table records the 3PID bindings a user has added to their account, replacing the prior in-memory vector<AccountThreePid> which was lost on restart. Columns are user_id, medium, address, country, id_server, added_at_ms, validated_at_ms, and bound, with the primary key (user_id, medium, address). added_at_ms zero 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 by threepid_in_use, not by a key.

  • account_threepids.client_secret / account_threepids.sid columns store the client_secret + sid pair 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 are TEXT NOT NULL DEFAULT ''; they are populated only for IS-bound 3PIDs and left empty for local-only bindings.

  • PersistentThreePidBinding struct (now carrying optional client_secret and sid) and the store_account_threepid / find_account_threepid / delete_account_threepid store 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.

  • pushers table records the push notification pushers a user has registered via POST /_matrix/client/v3/pushers/set. Columns are user_id, app_id, pushkey, kind, app_display_name, device_display_name, profile_tag, lang, data_url, data_format (the pusher's data dictionary's url/format keys, stored as plain columns rather than nested JSON), and data_extra_json (a canonical-JSON-serialized object holding every OTHER member of the pusher's data dictionary 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 beyond url/format must survive a restart and reach the gateway, not just be discarded), and device_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 same app_id and pushkey for the same user replaces it in place. The PersistentPusher struct and the store_pusher / find_pusher / delete_pusher / list_pushers_for_user store functions (PersistentStore.hpp) persist, look up, list, and remove pushers, following the same pattern as PersistentThreePidBinding above; implemented for both SQLite and PostgreSQL, hydrated on backend open. merovingian::push::PushGatewayClient (PushGatewayClient.hpp) forwards a device's data_extra members verbatim into the notify request body, and — since the PR #479 follow-up — ClientServerRuntime.cpp's parse_pusher_set_body() captures every data member beyond url/format at registration time and RoomService.cpp's pusher-to-PushGatewayDevice conversion threads it through to a live delivery, so the gap noted above is closed end to end; see docs/todos/capability-gaps.md.

  • notifications table records the history GET /_matrix/client/v3/notifications serves. Columns are user_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 like events.stream_ordering already does for GET /messages), ts (the event's origin_server_ts, milliseconds), actions (the canonical-JSON actions array reconstructed from the matched push rule's resolved outcome — PushRules.hpp's PushEvaluationResult does not keep the rule's raw actions array, so RoomService.cpp's push_notification_actions_json rebuilds 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), and highlight ("true"/"false", mirroring the matched rule's set_tweak: highlight action, so ?only=highlight can filter without re-parsing actions), with a primary key on (user_id, event_id). The PersistentNotification struct and the store_notification / list_notifications_for_user store functions (PersistentStore.hpp) persist and list rows, following the same pattern as PersistentPusher above; implemented for both SQLite and PostgreSQL, hydrated on backend open. RoomService.cpp's build_pending_push_deliveries() calls store_notifications once per event, with a row for every local recipient whose push rule evaluation resolves notify: true — in one transaction, unconditionally, regardless of server.push.enabled or whether that recipient has a registered pusher: GET /notifications returns 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 a PendingPushDelivery and dispatching it) stays gated on push.enabled + a registered pusher. The read field the endpoint reports is not stored on the row — it is computed at request time from sync::read_receipt_markers() against the caller's current m.read/ m.read.private receipts 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_notifications prunes, in the same transaction as the inserts, the oldest rows for each notified user_id beyond server.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 of server.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_tokens table stores the short-lived tokens minted by POST /_matrix/client/v3/user/{userId}/openid/request_token (Matrix v1.19 CS API §OpenID) and redeemed by GET /_matrix/federation/v1/openid/userinfo (SS API §OpenID). Columns are user_id, token_hash (primary key), and expires_at (epoch milliseconds, encoded the same way as access_tokens.expires_at / refresh_tokens.expires_at via expires_at_text/parse_expires_at). Unlike an access token's expiry, expires_at is never optional here — every OpenID token has a finite lifetime, and a user holds at most max_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 from access_tokens, by design — see docs/auth-identity.md ("OpenID tokens") and docs/threat-model.md for why that separation is the entire security property this feature depends on. The PersistentOpenidToken struct and the store_openid_token store function (PersistentStore.hpp) persist rows, following the same hashed-token pattern as PersistentAccessToken; 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 — and homeserver::federation_openid_userinfo is the only function that ever reads this table back; the ordinary client-server auth gate (authenticated_user) never consults it, and federation_openid_ userinfo never consults access_tokens/sessions. Retention: revoke_openid_tokens_for_user deletes a user's rows from the table and the in-memory vector (deactivation, logout everywhere, password change with logout_devices; returns false on a write failure, ADR-0408). store_openid_token sweeps 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; see docs/auth-identity.md) rather than a per-user row count, unlike notifications' count-based cap above.

  • login_tokens table stores the short-lived, single-use tokens minted by homeserver::complete_sso_login when an SSO authentication completes (Matrix v1.19 CS API §"Client login via SSO") and redeemed exactly once by POST /_matrix/client/v3/login with type: m.login.token (homeserver::redeem_login_token). Columns are user_id, token_hash (primary key), expires_at (epoch milliseconds, same encoding as openid_tokens.expires_at), and used (TEXT NOT NULL DEFAULT 'false', following the revoked-column boolean-as-text convention access_tokens/refresh_tokens already use). This is a separate table from access_tokens, for the same token-confusion reason openid_tokens is — see docs/auth-identity.md ("SSO login") and docs/threat-model.md ("Open redirect and login-token exfiltration via SSO redirectUrl"). database::store_login_token persists a row (reusing issue_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_token is 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 via crypto::constant_time_equal), marks it used in the backend before returning the owning user_id so a persistence failure can never leave a token silently redeemable twice from memory alone, and returns nullopt on 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_cursor table persists the outbound PUT /_matrix/app/v1/transactions/{txnId} delivery cursor for each registered Application Service API (Matrix v1.19) appservice, keyed on appservice_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_id and pending_stream_ordering (the currently in-flight, unacknowledged batch, if any — pending_txn_id == 0 means none). Every field is stored as TEXT, matching this table's pushers/notifications/openid_tokens neighbours, and parsed back to std::uint64_t at hydration. The PersistentAppserviceTxnCursor struct and the find_appservice_txn_cursor/store_appservice_txn_cursor store 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 is appservice_outbox below. A retry of the in-flight transaction reuses the exact same pending_txn_id and re-derives the identical single event from events, never growing it, per the spec's "Homeservers MUST NOT alter ... events they were going to send within that transaction ID on retries."

  • appservice_outbox table (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 raises delivered_stream_ordering (complete_appservice_delivery), so a crash can never leave a delivered event queued or a queued event forgotten. stream_ordering is TEXT like every numeric column in this schema; the in-memory mirror PersistentStore::appservice_outbox is kept sorted numerically by (appservice_id, stream_ordering) (sort_appservice_outbox after hydration). delivered_stream_ordering is 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 in federation_worker_table_allowlist. The queue of one appservice is capped at appservice.max_queued_events rows (default 100000, ADR-0191): at the cap enqueue_appservice_delivery writes no further row for that appservice, and count_appservice_outbox_entries (two binary searches over the sorted mirror) is what it checks. No row is dropped for age.

  • appservice_directory table (ADR-0192) holds the rooms an appservice has published to the directory of one of its networks (protocols) with PUT /_matrix/client/v3/directory/list/appservice/{networkId}/{roomId}: one row (appservice_id, network_id, room_id), primary key all three. PersistentAppserviceDirectoryEntry, PersistentStore::appservice_directory and set_appservice_directory_entry / appservice_directory_contains are 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 of rooms.directory_public. Main only: the table is not in federation_worker_table_allowlist.

  • deferred_pdus table (R03, ADR-0165) holds the federation PDUs ingest_pdu_event could not yet place because a prev_event or auth_event is unknown or a prev_event has 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) and attempts (retries that did not place it). Every number is TEXT, like the neighbouring tables, and is parsed back at hydration. PersistentDeferredPdu and upsert_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 of pdu_json in 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 by homeserver::retry_deferred_pdus and 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 in federation_worker_table_allowlist, because the worker relays every PDU to main and never reads or writes it.

  • receipts table (ADR-0169) holds read receipts, local and federated: one row per (room_id, receipt_type, user_id, thread_id) (the primary key), naming event_id, its ts and the stream_id it was last set at. thread_id is '' for an unthreaded receipt, main for 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::receipts and store_receipt/remove_receipt (PersistentStore.hpp) are implemented for both SQLite and PostgreSQL and hydrated on backend open; store_receipt allocates 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 takes HomeserverRuntime::mutex before checking membership or reading/updating the receipt mirror, including on direct federation requests; worker relays can re-enter this recursive lock. Only m.read and m.read.private are stored here: m.fully_read is room account data. Inbound federated receipts are capped by homeserver::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 in federation_worker_table_allowlist. Rows are read through receipt_room_index and receipt_user_room_index (receipts_in_room, receipts_of_user_in_room, counted in receipt_rows_examined), never by looping the vector; the indexes follow the ADR-0603 trust rule and rebuild_key_indexes rebuilds them on load. One user holds at most max_threaded_receipt_rows_per_user_room (256) rows with a thread root as thread_id in 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_receipts commits an inbound m.receipt EDU's accepted rows in one transaction and, past max_inbound_receipt_entries, deletes the oldest remote rows in it, taken from the ordered receipt_remote_by_stream index (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's filename (or the remote server's Content-Disposition filename), served back in Content-Disposition.
    • media_pending_uploads (media_id primary key, owner_user_id, created_at_ms, expires_at_ms, indexed by owner; ADR-0212) holds the mxc:// URIs reserved by POST /_matrix/media/v1/create that have no content yet. commit_pending_media_upload inserts one and deletes the expired ones it was handed in the same transaction; commit_local_media_upload with completes_pending_media_id deletes the row in the transaction that stores the content. Rows are bounded by max_pending_uploads. Main only: not in federation_worker_table_allowlist (the federation media route runs in main).
  • users.deactivated column (part of the v1 schema) is a TEXT NOT NULL DEFAULT 'false' column of the users table, set by POST /_matrix/client/v3/account/deactivate (see docs/auth-identity.md "Account deactivation"). It is deliberately distinct from the existing reversible locked/suspended columns: 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-memory PersistentUser, following the same pattern as set_user_account_state above; implemented for both SQLite and PostgreSQL, hydrated on backend open. Adding a sixth column to users also exposed a latent risk in store_user's insert statement: it previously used a bare INSERT INTO users VALUES (...), which binds parameters by position and would have silently mismatched had the column order or count changed elsewhere. store_user now names every column explicitly (INSERT INTO users (user_id, password_hash, locked, suspended, admin, deactivated) VALUES (...)), so a future schema change to users fails 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_status get/set it, validating the value and mirroring the change into the in-memory PersistentEvent::status field (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, with parent_state_group_id (TEXT NOT NULL DEFAULT '') and delta_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 SQL NULL — PreparedStatement/BoundValue has no NULL parameter representation, and this keeps the convention consistent with the rest of the schema. In C++, PersistentStateGroup::parent_state_group_id is std::optional<std::string> (nullopt ⇔ the empty-string sentinel). state_group_edges is 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_id empty), 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 on prev_event_id alone; the ingest-time "children of this event" lookup (is this event anybody's prev_event_id?) needs exactly that shape, and without the index it is a full table scan per probe. Confirmed by EXPLAIN QUERY PLAN in tests/integration/test_state_groups_flow.cpp's "event_edges has a usable index for prev_event_id lookups in the v1 schema" scenario. state_groups has no matching room_id index: every store function that reads it (find_state_group, read_state_group_full_state, create_or_reuse_state_group's parent lookup) looks up by state_group_id, its primary key — nothing queries it by room_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 hold full_state — reusing parent_state_group_id unchanged when full_state is exactly the parent's own state (read via read_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's delta_depth + 1 would exceed events::max_state_group_delta_depth (100), or full_state is 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 (returns nullopt) 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 to max_state_group_delta_depth + 1 hops and cycle-detected — a missing group, a cycle, or an over-long chain returns nullopt, never a partial map. find_state_group returns a group's own row (not its resulting state). update_forward_extremities(store, room_id, event_id, prev_event_ids, accepted) removes prev_event_ids from and adds event_id to the room's extremity set when accepted is true, and is a no-op when false (soft-failed/rejected events never become extremities and never remove one); find_forward_extremities reads the current set. All four are implemented for SQLite, PostgreSQL, and the in-memory store's write-through path (the PersistentStoreBackend::memory backend commits trivially, so unit tests exercise the in-memory bookkeeping directly — see tests/unit/test_state_groups.cpp).

    Forward extremities and the resolved-state memo (ADR-0600, ADR-0602). PersistentStore::forward_extremities is a ForwardExtremityTable, which counts each room's tips by the server of their sender. Hydration reads the tips before the events are indexed, so rebuild_forward_extremity_servers charges them afterwards. PersistentStore::resolved_state_memo is a ResolvedStateMemo, 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_json and apply_room_snapshot forget the room. PersistentStore::state_before_memo is a StateBeforeMemo (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, StateGroupEntryTable and EventStateGroupTable (StateGroupTable.hpp, StateGroupEntryTable.hpp, EventStateGroupTable.hpp), which own the rows and keep hash indexes by state_group_id and event_id, so find_event_state_group, find_state_group and read_state_group_full_state cost the rows they return and not a pass over every room's. They are filled by hydration and reload_room through 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 a prev_event held 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 single prev_event's own after-state directly (find_event_state_group + read_state_group_full_state, no resolution needed); several prev_events resolved via events::resolve_state_v2 over their after-states. Fails closed (StateBeforeResult::ok = false) if any prev_event has 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_before plus 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 off prev_event_ids.front()'s own group as the delta parent, so unchanged state reuses it per create_or_reuse_state_group's own dedup), maps event_id to 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 cached current_state and written only where changed, through database::store_state — the same call every other state write already used, so state_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_state is a cache of this result, never an independent write target, from this path onward. A key the resolution does not hold is deleted with remove_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 new federation::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 in deferred_pdus and the status is deferred; missing_prev_state is 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 as ingest_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_event gives local events their prev_events from 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 into store.events" heuristic. persist_composed_event (RoomService.cpp — used by create_room's initial-state chain, invite/join composition, and every ordinary send/state-send/redaction) calls it instead of database::store_event_with_state directly. Since 0.12.48 the state before is resolved once, while the event is composed (homeserver::basis_for_new_event), and handed to store_local_event as a NewEventBasis; when the extremities' states do not resolve, the basis cites only the newest extremity whose state reads alone, and its retired_extremities leave the forward_extremities table with the event (ADR-0225).
    • LocalHttpRouter.cpp's membership_acceptor (a remote user's send_join/send_leave/send_knock into a room we are resident in — the same trust boundary as ingest_pdu_event) gets the identical state-before check and after-state/current-state bookkeeping. invite_handler's invite-event store gets explicit status = "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 its current_state row and membership projection, its after-state group, the mapping, the forward-extremity change, and every current_state change 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 an apply_* 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 the send_join response's state (stored as status = "outlier" events by ingest_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 separate join-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 hits missing_prev_state. Since FED-1 (ADR-0083) only entries whose room is the joined room are stored, and auth_chain events are stored as outliers with no current_state row; repair_missing_state_entries (start-up) likewise only promotes accepted events, never an outlier or a rejected one.
    • A source-tree guard test (tests/unit/test_store_event_choke_point.cpp) fails the build if database::store_event_with_state( appears anywhere in src/ outside a reviewed, counted allowlist, and since 0.12.48 the same for database::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's state_transitions insert had no ON CONFLICT handling, 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-memory state_transition_index mirror 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, and tests/unit/test_store_event_choke_point.cpp (all [pdu_ingestion][state_groups]) cover the module directly, end-to-end through ingest_pdu_event, end-to-end through local send/create paths, and the source-tree guard, respectively; tests/integration/test_join_room_flow.cpp covers the federated-join seeding end to end; tests/integration/test_sync_handler_flow.cpp covers a resolution-driven reactivation reaching an incremental /sync client.

  • Token rotation lineage (ADR-0074). refresh_tokens.predecessor_hash and access_tokens.predecessor_refresh_hash (both TEXT NOT NULL DEFAULT '') are part of the v1 schema. A token pair minted by POST /refresh records the hash of the refresh token it replaced, which stays valid until the pair is first used (see docs/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, 0 when unknown; it is persisted by every insert and loaded by both backends (F-08). The in-memory access_tokens and refresh_tokens are CredentialTables (hash, (user, device), user and predecessor indexes), and the three secondary indexes above serve the same WHERE clauses in SQL: user_id and device_id (the per-device and per-user revokes, and the device_id <> form, whose user_id prefix the composite index serves) and predecessor_hash; token_hash is the primary key. Both backends store and hydrate the columns; database::revoke_refresh_tokens_with_predecessor supersedes an unused pair when a refresh is retried. Hydration (homeserver::hydrate_local_database) also copies each access token's expires_at into its session; before 0.12.13 it did not, so expiry was not enforced after a restart.

  • M05 authenticated-media storage (ADR-0068). The media table's legacy_endpoint_visible column (TEXT NOT NULL DEFAULT 'true') is part of the v1 schema. New local uploads are stored with legacy_endpoint_visible = false (see docs/media-repository.md); the unauthenticated /_matrix/media/v3/download and /thumbnail routes treat a false value as a 404, while authenticated /_matrix/client/v1/media/... routes ignore it and serve the media. Adding a column to the media table does not affect the federation worker table classification: media remains in worker_never_reads_tables, so the worker allowlist needs no change.

  • No client input moves the sync stream counter. A /sync since token ahead of the server's streams is served as an initial sync and leaves the counter alone (ADR-0315). database::allocate_sync_stream_id() throws std::overflow_error at INT64_MAX with 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-migrate planning scaffold.

  • Database runtime and migration role 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_transaction used to append every committed PreparedStatement, bound parameters included, to PersistentStore::prepared_statements, which nothing trimmed. The store now keeps nothing unless a test calls enable_statement_capture(store, capacity); the capture is PersistentStore::captured_statements, holds at most capacity statements (clamped to max_statement_capture_capacity, 65 536), drops the oldest first, and is empty and disabled by default. sensitive_values_are_redacted reads 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_log is a bounded window (AUTH-1): the newest max_in_memory_audit_events (1 024) rows, oldest dropped first, with audit_log_evicted counting what left. The table is the complete record. Append through append_audit_event; hydrate through remember_audit_event. Hydration keeps the newest rows in insertion order on both backends (SQLite by rowid; PostgreSQL by created_at_ms, then ctid within one millisecond, because retention deletes rows and ctid alone no longer orders them, ADR-0489). Rows older than security.audit_log_retention_days are deleted by the retention-sweep job (delete_audit_rows_older_than, at most 500 rows per transaction; created_at_ms is the column that ages them, and a row stamped 0 is never deleted). The same job deletes the unusable access_tokens and refresh_tokens rows (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, limit clamped to max_audit_query_rows, prefix bound and matched literally); in-memory stores answer from the window. append_audit_event cuts actor, target and reason to 255 bytes on a UTF-8 boundary. See docs/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_to and migrate_postgresql_schema_to build 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, run migrate_*_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 is current_schema_version(), the ledger gained the oauth_tables and delayed_events rows, and oauth_clients, oauth_sessions and delayed_events exist and take a row (the fixture held none of them before). They then assert everything the manifest lists is still served. The PostgreSQL scenario needs MEROVINGIAN_TEST_POSTGRESQL_URI and reports itself skipped without it.

  • Both stores read the migration ledger in numeric order (ORDER BY CAST(version AS INTEGER)): schema_migrations.version is TEXT, and ordering by it alone put '10' before '9'.

  • profiles.fields_json holds a user's extended profile fields (m.tz, custom keys) as one canonical JSON object, {} when none (ADR-0478); update_profile_fields rewrites it.

  • The PostgreSQL store hydrates profiles like 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 a merovingian_migration role with DDL grants and a merovingian_runtime role with table-level DML grants, and runs every integration scenario tagged [postgresql] (ADR-0511), most of them in tests/integration/test_postgresql_persistence_flow.cpp. Scenarios assert: schema is bootstrapped to current_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 the SET ROLE statement, so the API is safe to call with operator-supplied role names.

  • Bootstrap table-identifier validation (issue #442): PostgreSQL's create_table_if_missing_sql previously concatenated table.name directly into CREATE TABLE IF NOT EXISTS <name> (...) DDL with no validation or quoting, unlike the SQLite path. It now mirrors SqliteStore.cpp exactly: schema_table_is_core() gates the name to the compiled core-table set, and quote_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_ids table. 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_id is empty string for to-device sends. device_id is the device that made the request (empty when it had none, which is a scope of its own); it also gives the sending device its unsigned.transaction_id. created_at_ms is 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 while client_txn_indexed_rows equals the row count; a caller that pushes rows directly falls back to a scan until the next rebuild. Only the to-device rows (room_id empty) are pruned: the client-txn-prune job 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, because unsigned.transaction_id is served from them (ADR-0258). event_id stores 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 retry PUT /rooms/{roomId}/send and PUT /sendToDevice requests.

  • to_device_messages.stream_id is TEXT, so ORDER BY stream_id sorts "10" before "9". Both backends call database::sort_to_device_messages after hydrating to restore numeric order, and drain_to_device_messages and the sliding sync to_device extension sort what they return, so next_batch never acknowledges a row that was not sent (ADR-0426).

  • PUT /sendToDevice writes its local to_device_messages rows and its client_txn_ids row 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_message and 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 answers 500 M_UNKNOWN and a retry with the same transaction ID is processed rather than replayed. Stream ids are allocated before the commit (allocate_sync_stream_id persists 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_messages is 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 by commit_prepared_outlier_batch with runtime.mutex released, then applied) and the member rows in one (store_memberships_batch), counted by committed_transactions (C03, ADR-0714). The state-array and member commits keep the lock: they write current_state and membership rows.

  • server_signing_keys holds this server's keys and every cached remote key; server_signing_key_index groups 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).

  • invites is 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_caps is asked by every writer before it builds a statement. The runtime rebuilds the indexes once it has set local_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; limits max_remote_invite_bytes_per_origin 64 MiB and max_remote_invite_bytes_total 256 MiB, ADR-0707), rebuilt by rebuild_invite_indexes and trusted only while invite_bytes_rows equals the row count.

  • An inbound m.direct_to_device EDU writes all of its to_device_messages rows in ONE backend transaction (database::enqueue_to_device_messages, ADR-0166), through the same prepare/apply steps and evictions as the single-message and /sendToDevice writers. 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 (returns true) for the memory backend. Its read half, load_room_snapshot, is what main runs for a federation worker's room_sync reload; the worker applies the answer with apply_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 a reload_room per room would cost rooms times rows (EventTable::insert_all merges the stream order of everything it holds). database::load_room_ids(store) lists every room ID, sorted, from one statement, and database::public_signing_keys_of_server(store, name, cap) copies a server's public key columns from memory under server_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 through RuntimeStartOptions::fill_store, and refresh_remote_signing_keys asks main through PersistentStore::remote_signing_key_source when that is set, taking only the named server's rows without a secret from the answer.

  • database::reconstruct_event_relations(store) re-derives every PersistentEvent::prev_event_ids/auth_event_ids/signatures from the flat event_edges/event_auth/event_signatures tables. 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 both open_sqlite_persistent_store/open_postgresql_persistent_store and as part of reload_room's snapshot merge. Idempotent.

  • database::store_event_with_state() is split into prepare_store_event_with_state(), commit_persistent_transaction(), and apply_store_event_with_state(). The PreparedStateUpdate struct holds the event, optional state event, and the prepared statements. prepare validates in-memory pre-conditions (room existence, duplicate event id) and builds the statements. commit executes them on the backend without holding any room lock, so different rooms can commit concurrently. apply mirrors 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.

Security posture

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_migrations ledger 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 and media::rollback_local_media_upload removes 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 sslmode with the value verify-full. An omitted or empty mode cannot inherit its way through admission via PGSSLMODE; 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 requires database.role=migration.
  • No store function may clear a revoked flag once set — revocation is a one-way transition, enforced by the store's API surface rather than by caller discipline (see revoke_tokens_for_user_except_device above and ADR-0052).
  • Binary (BLOB) columns round-trip byte-exactly on both backends. The columns are media_blobs.bytes and server_signing_keys.secret_key — every BLOB in migrations/*.sql and Schema.cpp; a new one must follow this mechanism. BoundValue carries a binary flag, and a write must set it for every BLOB parameter. SQLite binds every parameter by explicit length and reads columns by sqlite3_column_bytes, so it ignores the flag and is unchanged. On PostgreSQL, execute_prepared_statement binds a binary parameter in libpq's binary wire format (paramFormats = 1, explicit paramLengths, declared type bytea), 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_rows asks libpq for each result column's type (PQftype) and decodes every bytea column from PostgreSQL's \x hex text form back to raw bytes, so any query that selects a BLOB column gets bytes, not hex. Each connection pins SET bytea_output = 'hex' at open so a role or database default of escape cannot change that form; a bytea value that is not valid hex fails the query rather than returning empty or garbled bytes, because an empty secret_key reads as "no signing key" and would make the server mint a new identity. Until DB-1 was fixed only media_blobs.bytes was 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_secret and .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 in docs/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_lock for 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 the schema_migrations ledger inside each step's BEGIN IMMEDIATE and skips a step the winner of a race already applied. These are deliberately not one shared mechanism — see ADR-0059.

Deliberately not included

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.

Next starting points

  • 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() assumes database.migration_role for DDL (the fresh-database v1 bootstrap and any later pending migration), resets it, and then assumes database.runtime_role for 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 the postgres-integration workflow because they require PostgreSQL and the role environment variables. See packaging/postgresql/provision-roles.sql.
  • The schema's v1 push_rules table is unused: push rules are kept as m.push_rules account data (ADR-0204, see above).

Federation worker database access (ADR-0733, ADR-0736)

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).

Every table classified

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_groups was 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_clients and oauth_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 tables Schema.cpp declares 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 outside Schema.cpp's own DDL declarations. state_group_edges remains vestigial after ADR-0064 phase A: the design uses parent links on state_groups itself 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_transactions specifically are read only by federation::DispatchWorker, which never starts in the worker process: its construction (LocalHttpRouter.cpp, guarded inside the same lazily-wired callback block) requires runtime.database.signing_secret_key to 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, and devices are read only by profile_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.md rule 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.

Security audit follow-up (0.12.17)

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).

Membership rows commit with their event (DB-4, 0.12.26)

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.

Write connection pool (DB-7, 0.12.26)

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).

Event table indexes (0.12.39)

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) and events.contains(id) are hash lookups, and events.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 a find_if over the rows.
  • by stream order. events.in_stream_order() visits every row by ascending stream_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 no ORDER 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 order in_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_for and count_unread_by_thread read a room through; never for (x : store.events) plus a room_id test on a request path. insert, insert_all, erase_if, modify (a changed room_id) and clear keep 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 an origin_server_ts, ordered by (origin_server_ts, depth, stream_ordering), the boundary inclusive, stepping over rows accept refuses and offering at most max_examined (database::max_time_search_examined, 1000) to it. It backs GET /rooms/{roomId}/timestamp_to_event and the federation endpoint of the same name. A row is in it only while its status is not outlier, rejected or soft_failed and its JSON has an integer top-level origin_server_ts; modify moves 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 one RelationKind (m.replace, m.thread, m.reference) whose content."m.relates_to" names target_event_id, newest listed first; m.annotation and 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. modify re-derives the key, so a redacted child (no m.relates_to left) 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.

Row position indexes for keys, users, memberships and key backup (ADR-0603, ADR-0606)

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.