Database

PostgreSQL 17. Migrations live in backend/migrations, and migrate() applies them in version order at server startup. The applied history is kept in umul_internal.schema_migrations as version, name, and SHA-256 checksum. If the content of an already-applied file changes, startup aborts with MIGRATION_CHECKSUM_MISMATCH.

What is never stored

• Ordinary message plaintext
• XMTP decryption keys and wallet private keys
• Session token and login nonce plaintext (only SHA-256 hashes are stored)
• Evidence archive plaintext and content keys (only client-locked ciphertext and wrapped keys are kept, and the server can open neither)

001_auth.sql

wallet_login_nonces

Column	Description
id	uuid primary key
address	Lowercase wallet address. CHECK (address = lower(address))
nonce_hash	SHA-256 of the nonce. UNIQUE
expires_at	5 minutes after issuance
consumed_at	Time of use. The basis for single-use guarantees

sessions

Column	Description
id	uuid primary key
address	Lowercase wallet address
token_hash	SHA-256 of the session token. UNIQUE
expires_at	7 days after issuance
revoked_at	Sign-out time

Consuming a nonce and creating a session happen atomically in a single CTE statement. Concurrent requests consume it exactly once.

002_evidence.sql

evidence_requests

Column	Description
id	uuid primary key
idempotency_key	UNIQUE. Blocks duplicate creation
evidence_id	32 bytes. UNIQUE
requester_address	Lowercase address
content_hash, conversation_id_hash, participants_hash	32 bytes each
manifest_version	Hash rule version
message_count	At least 1
conversation_started_at, conversation_ended_at	End time must be at or after start
status	CHECK allowing only the 8 states
transaction_hash	32 bytes, UNIQUE, nullable
block_number	Non-negative, nullable
rejected_at	Rejection time, nullable
expires_at	1 hour after creation. CHECK (expires_at > created_at)
created_at	Creation time

evidence_participants

Composite primary key (request_id, address). signature is 65 bytes, and a CHECK requires signature and signed_at to be either both NULL or both set. There is an index for address lookups.

003_evidence_metadata.sql

Adds the evidence name and the IDs of the messages being evidenced. Both are off-chain metadata that never reach the chain. The contract accepts only contentHash, so renaming leaves the on-chain record and the signatures unaffected.

Column	Table	Description
name	evidence_requests	Evidence name. Nullable. CHECK (length BETWEEN 1 AND 100)
message_refs	evidence_requests	Array of XMTP message IDs (text[]). Carries no message body
saved_at	evidence_participants	Saved (favourited). Per-participant state, so one person saving does not affect anyone else's list

message_refs holds the same values delivered to participants in the evidence proposal control message. The detail screen uses these IDs to find the bodies in each participant's own local MLS DB.

004_evidence_metadata_integrity.sql

Closes the consistency gap 003 left behind. message_count is recorded on the contract and feeds the signed digest, while message_refs is what the screen uses to retrieve bodies. If the two disagree, the screen says "12 messages" while showing 3.

Constraint / index	Content
evidence_requests_message_refs_match_count	cardinality(message_refs) = message_count
evidence_requests_message_refs_not_empty	cardinality(message_refs) > 0
evidence_requests_created_at_idx	(created_at DESC). The collected view reads "saved first, then newest"

Rows predating 003 have a NULL message_refs and are exempt from both CHECKs.

005_evidence_archive.sql

evidence_archives

Client-side ciphertext of the evidenced plaintext (AES-256-GCM, iv 12 + body + tag 16). request_id is the primary key, so there is one row per evidence, written exactly once. A CHECK bounds the length between 29 bytes and 2 MB.

evidence_archive_keys

The wrapped content key per signing participant. The (request_id, address) primary key carries a composite foreign key to evidence_participants, which blocks rows for addresses that are not participants. A CHECK requires wrapped_key to be exactly 60 bytes (iv 12 + key 32 + tag 16). The application never creates rows for participants who did not sign.

006_evidence_content.sql

No code uses the objects this migration creates yet. They exist in the schema so they are documented here, but actual evidence-plaintext storage goes through 005's evidence_archives / evidence_archive_keys. Whether this is meant to replace 005 or serve a separate purpose is undecided.

evidence_contents

Column	Description
request_id	Primary key referencing evidence_requests. ON DELETE CASCADE
ciphertext	Result of the client's AES-256-GCM lock. Between 1 byte and 1 MB
iv	Exactly 12 bytes
algorithm	Only 'AES-GCM-256' allowed

Exceeding 1 MB means a whole conversation was included, so the ceiling signals that the evidence range should be narrowed.

evidence_participants extension

Participants upload their share of the key when they sign. A participant who did not sign has no key and cannot open the body.

Column	Description
wrapped_key	Exactly 48 bytes (CEK 32 + GCM tag 16)
wrapped_key_iv	Exactly 12 bytes

A CHECK requires both to be NULL together or set together. Unlike 005's evidence_archive_keys.wrapped_key, which is 60 bytes with the iv inline, this splits the iv into its own column.

Key derivation does not use the EIP-712 multi-signature (evidence_participants.signature). That value is stored here and is also public as on-chain calldata, so deriving a key from it would let the server decrypt. Derivation uses a separate phrase that is never transmitted.

Concurrency

Every state transition goes through PgEvidenceRepository.updateLocked. Inside a transaction it locks the row with SELECT ... FOR UPDATE, lets the aggregate decide the transition, then saves. If the aggregate rejects the transition, the whole transaction rolls back so no partial write remains.

Two participants signing simultaneously both succeed and the final state is READY. The same participant signing twice simultaneously succeeds once; the other fails with EVIDENCE_SIGNATURE_ALREADY_SUBMITTED.

Test isolation

Integration tests reset the schema, so only databases named umul_test or prefixed umul_test_ are permitted. Any other name aborts with UNSAFE_TEST_DATABASE, and even on abort every open connection pool is closed. The development database umul and the test database umul_test run in separate containers (5432 / 5433).