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.