continuwuity/docs/DATABASE_RELATIONSHIPS.md

Continuwuity Database Column Relationships

This document analyzes how the 89 database columns in Continuwuity relate to each other, showing the data flow and dependencies between tables.

Core Identity Mapping System

Event ID Management

The system uses a sophisticated event ID mapping system to optimize storage:

eventid_shorteventid ←→ shorteventid_eventid
         ↓                      ↓
   eventid_pduid         shorteventid_authchain
         ↓                      ↓
     pduid_pdu         shorteventid_shortstatehash
         ↓
eventid_outlierpdu

Relationships:

• eventid_shorteventid + shorteventid_eventid: Bidirectional mapping between full Matrix event IDs (48 bytes) and compact short IDs (8 bytes)
• eventid_pduid: Maps event IDs to PDU IDs (16-byte internal identifiers)
• pduid_pdu: Main event storage using PDU IDs as keys
• eventid_outlierpdu: Stores events not yet part of the timeline (outliers)
• shorteventid_authchain: Authorization chains using short event IDs
• shorteventid_shortstatehash: Links events to room state using short IDs

Room ID Management

Similar optimization for room identifiers:

roomid_shortroomid
       ↓
   (used in keys for room-related tables)

State Management

Complex state tracking with compression:

statekey_shortstatekey ←→ shortstatekey_statekey
         ↓
statehash_shortstatehash
         ↓
shortstatehash_statediff
         ↓
roomid_shortstatehash

Relationships:

• statekey_shortstatekey + shortstatekey_statekey: Bidirectional mapping for state keys
• statehash_shortstatehash: Maps full state hashes to 8-byte compressed versions
• shortstatehash_statediff: Stores state differences between versions
• roomid_shortstatehash: Current state hash for each room

User and Authentication Flow

User Authentication Chain

userid_password → token_userdeviceid ←→ userdeviceid_token
                         ↓
                 userdeviceid_metadata
                         ↓
              userdevicesessionid_uiaainfo

Relationships:

• userid_password: Stores user password hashes
• token_userdeviceid + userdeviceid_token: Bidirectional mapping between access tokens and devices
• userdeviceid_metadata: Device information (name, type, etc.)
• userdevicesessionid_uiaainfo: User-Interactive Authentication session data

User Profile Data

userid_displayname
userid_avatarurl → userid_blurhash
useridprofilekey_value

Relationships:

• Profile data is stored separately per attribute
• userid_blurhash complements userid_avatarurl for progressive loading

Token Management

openidtoken_expiresatuserid
logintoken_expiresatuserid  
tokenids

Relationships:

• Separate token types have separate expiration tracking
• tokenids manages token ID allocation

Room Membership System

Membership State Tracking

roomuserid_joined ←→ userroomid_joined
roomuserid_invitecount ←→ userroomid_invitestate
roomuserid_leftcount ←→ userroomid_leftstate
roomuserid_knockedcount ←→ userroomid_knockedstate

Relationships:

• Bidirectional indexes: room→user and user→room perspectives
• Count tables track membership transitions
• State tables store membership event data

Room Counts and Metadata

roomid_joinedcount ← roomuserid_joined
roomid_invitedcount ← roomuserid_invitecount
roomuseroncejoinedids (historical tracking)

Relationships:

• Count tables are derived from individual membership records
• Historical tracking for users who ever joined

Federation Integration

roomserverids ←→ serverroomids
roomid_inviteviaservers

Relationships:

• Bidirectional tracking of which servers participate in which rooms
• Via servers for invitation routing

Cryptography and Security

Device Key Management

userid_devicelistversion
         ↓
keyid_key ← userid_masterkeyid
         ↓     userid_selfsigningkeyid
keychangeid_userid ← userid_usersigningkeyid
         ↓
onetimekeyid_onetimekeys
         ↓
userid_lastonetimekeyupdate

Relationships:

• Device list versions track changes requiring key updates
• Different key types stored separately with references from user records
• Key changes trigger notifications
• One-time keys managed with update timestamps

Key Backup System

backupid_algorithm
backupid_etag → backupkeyid_backup

Relationships:

• Backup metadata (algorithm, versioning) linked to actual backed-up keys

Push Notifications and Read Tracking

Push Infrastructure

senderkey_pusher ←→ pushkey_deviceid

Relationships:

• Bidirectional mapping between push keys and devices

Read Receipt System

readreceiptid_readreceipt
roomuserid_privateread
roomuserid_lastprivatereadupdate
userroomid_highlightcount
userroomid_notificationcount

Relationships:

• Public read receipts vs private read markers
• Highlight/notification counts per user-room pair
• Update tracking for private reads

Media and Content

Media Storage

mediaid_file ←→ mediaid_user
url_previews

Relationships:

• File metadata linked to uploader tracking
• URL previews cached separately

Sync and Timeline

Sync Token Management

roomsynctoken_shortstatehash
lazyloadedids

Relationships:

• Sync tokens map to room state for efficient delta computation
• Lazy loading tracking for member events

Event Relations

tofrom_relation
threadid_userids
referencedevents
softfailedeventids

Relationships:

• Event relations track replies, edits, reactions
• Thread participant tracking
• Referenced events and soft failures

Federation and Server Management

Server Discovery and Communication

servername_destination (cached)
servername_override (cached)
server_signingkeys
servername_educount
servercurrentevent_data
servernameevent_data

Relationships:

• Destination resolution with caching
• Server signing keys for federation
• EDU (Ephemeral Data Unit) counting
• Current and historical server events

Account Data and Presence

Account Data Storage

roomusertype_roomuserdataid → roomuserdataid_accountdata
userid_presenceid → presenceid_presence

Relationships:

• Account data indexed by room+user+type, pointing to actual data
• Presence data separated from user records with ID mapping

Global Configuration

Application Services

id_appserviceregistrations

Global Settings

global
publicroomids
bannedroomids
disabledroomids

Relationships:

• Global server configuration
• Room access control lists

Performance Optimizations

Shared Cache Relationships

• eventid_outlierpdu and pduid_pdu share cache because they both store PDU data
• Related tables are grouped for memory efficiency

Transaction Management

userdevicetxnid_response
todeviceid_events

Relationships:

• Transaction ID response caching
• To-device message queuing

Data Flow Examples

Sending a Message

1. pduid_pdu ← stores the PDU
2. eventid_pduid ← maps event ID to PDU ID
3. eventid_shorteventid ← creates short ID mapping
4. shorteventid_shortstatehash ← links to room state
5. userroomid_notificationcount ← updates notification counts
6. readreceiptid_readreceipt ← processes read receipts

User Login

1. userid_password ← validates credentials
2. userdeviceid_token ← creates device token
3. token_userdeviceid ← creates reverse mapping
4. userdeviceid_metadata ← stores device info

Room Join

1. roomuserid_joined ← records membership
2. userroomid_joined ← creates reverse index
3. roomid_joinedcount ← updates room count
4. roomuseroncejoinedids ← historical tracking
5. roomserverids ← federation tracking

This relational structure allows Continuwuity to efficiently handle Matrix protocol operations while maintaining data consistency and enabling fast lookups from multiple perspectives.