SMSPort PostgreSQL Database Schema Reference

This reference document outlines the core relational database schema, tables, and entity relationships for SMSPort, persisted via PostgreSQL and TypeORM.

Entity Relationship Overview

erDiagram
    TENANT ||--o{ WORKSPACE : "owns"
    WORKSPACE ||--o{ USER : "has members"
    WORKSPACE ||--o{ CONTACT : "stores"
    WORKSPACE ||--o{ CONVERSATION : "contains"
    CONVERSATION ||--o{ MESSAGE : "records"
    WORKSPACE ||--o{ BOT_GRAPH : "configures"
    WORKSPACE ||--o{ CAMPAIGN : "schedules"
    WORKSPACE ||--o{ INTEGRATION : "connects"

    WORKSPACE {
        uuid id PK
        string name
        string slug
        string waba_id
        string phone_number_id
        timestamp created_at
    }

    CONVERSATION {
        uuid id PK
        uuid workspace_id FK
        uuid contact_id FK
        string status
        string assigned_operator_id
        timestamp last_message_at
    }

    MESSAGE {
        uuid id PK
        uuid conversation_id FK
        string direction
        string type
        text body
        string wamid
        string delivery_status
    }

    BOT_GRAPH {
        uuid id PK
        uuid workspace_id FK
        string name
        string trigger_type
        jsonb graph_json
        boolean is_active
    }

Core Entities & Tables

1. `workspaces`

Multi-tenant isolation root for each organization.

• id (UUID, Primary Key)
• name (varchar)
• slug (varchar, Unique)
• waba_id (varchar, Meta WhatsApp Business Account ID)
• phone_number_id (varchar, Meta Phone ID)
• settings (jsonb)

2. `conversations`

Unified WhatsApp chat thread with an end customer.

• id (UUID, Primary Key)
• workspace_id (UUID, Foreign Key)
• contact_id (UUID, Foreign Key)
• status (open | closed | snoozed | bot_active)
• presence_lock (varchar, operator ID holding lock)

3. `messages`

Individual message payload (inbound & outbound).

• id (UUID, Primary Key)
• conversation_id (UUID, Foreign Key)
• direction (inbound | outbound)
• type (text | image | document | template | interactive_flow)
• wamid (varchar, Meta WhatsApp Message ID)
• delivery_status (sent | delivered | read | failed)