Database Schema

The service uses PostgreSQL for persistent data and Redis for temporary/high-performance data.

PostgreSQL Schema Overview

erDiagram
    USERS_AUTH ||--o{ DEVICES : "possesses"
    USERS_AUTH ||--o{ PREKEYS : "possesses"
    USERS_AUTH ||--o{ SIGNED_PREKEYS : "possesses"
    USERS_AUTH ||--o{ IDENTITY_KEYS : "possesses"
    USERS_AUTH ||--o{ BACKUP_CODES : "possesses"
    USERS_AUTH ||--o{ LOGIN_HISTORY : "possesses"
    DEVICES ||--o{ LOGIN_HISTORY : "used during"
    
    USERS_AUTH {
        uuid id PK
        string phoneNumber UK
        string twoFactorSecret
        boolean twoFactorEnabled
        timestamp lastAuthenticatedAt
        timestamp createdAt
        timestamp updatedAt
    }
    
    DEVICES {
        uuid id PK
        uuid userId FK
        string deviceName
        string deviceType
        string deviceFingerprint UK
        string publicKey
        timestamp lastActive
        boolean isVerified
        boolean isActive
    }
    
    PREKEYS {
        uuid id PK
        uuid userId FK
        int keyId
        string publicKey
        boolean isOneTime
        boolean isUsed
    }
    
    SIGNED_PREKEYS {
        uuid id PK
        uuid userId FK
        int keyId
        string publicKey
        string signature
        timestamp expiresAt
    }
    
    IDENTITY_KEYS {
        uuid id PK
        uuid userId FK
        string publicKey
        string privateKeyEncrypted
    }
    
    BACKUP_CODES {
        uuid id PK
        uuid userId FK
        string codeHash
        boolean used
    }
    
    LOGIN_HISTORY {
        uuid id PK
        uuid userId FK
        uuid deviceId FK
        string ipAddress
        timestamp createdAt
        string status
    }

Table Descriptions

USERS_AUTH

The core table for user identity.

• phoneNumber: Unique E.164 identifier.
• twoFactorSecret: Encrypted TOTP secret.

DEVICES

Tracks all hardware/browsers associated with a user.

• deviceFingerprint: Unique identifier generated by the client.
• publicKey: The Signal Protocol public key for this specific device.

Cryptographic Keys (Signal Protocol)

• PREKEYS: One-time use keys for asynchronous messaging.
• SIGNED_PREKEYS: Semi-persistent keys signed by the Identity Key.
• IDENTITY_KEYS: Long-term keys identifying the user.

Redis Structures

Redis is used for high-availability temporary data with short TTLs:

• Verification Codes: verification:{id} - SMS codes (TTL: 15m).
• Active Sessions: session:{id} - JWT metadata for revocation (TTL: variable).
• QR Challenges: qr_challenge:{id} - Temporary challenges for QR login (TTL: 5m).
• Rate Limits: rate_limit:{type}:{id} - Brute-force protection counters.