Leadsmart
  1. Docs
Leadsmart
  • API Reference
  • Docs
    • LeadSmart API Documentation
    • Services
    • Middleware
    • Integrations
    • Database
    • Architecture
  • Authentication
    • Register
      POST
    • Update User
      PATCH
    • Login
      POST
    • Logout
      POST
    • Reset Password
      POST
    • Change Email
      POST
    • Request Password Reset
      POST
    • Request Email Change
      POST
    • Verify Email
      GET
  • Companies
    • Create Company
      POST
    • Join Company
      POST
    • Get All User Company
      GET
    • Get user Company
      GET
    • Update Company
      PATCH
    • Deactivate Company
      PATCH
    • Activate Company
      PATCH
    • Reset Invite Code
      PATCH
  • Members
    • Create a Member
      POST
    • Get All Member
      GET
    • Get Single Member
      GET
    • Update Member
      PATCH
    • Deactivate Member
      PATCH
    • Activate Member
      PATCH
  • Agendas
    • Create Company Agenda
    • Get All Agendas
    • Get Single Agenda
    • Get Agenda with Appoint
    • Get Available Slots
    • Update Agenda
    • Delete Agenda
    • Activate Agenda
    • Create Agenda Integration
    • Delete Agenda Integration
  • Appointments
    • Create Appointment
    • Get All Appointments
    • Get Appointment
    • Add Members
    • Delete Members
    • Update Appointment
    • Update Date
  • Plans
    • Get Single Plan
    • Get All Plans
    • Create Plan
    • Delete Plan
    • Update Plan
  • Plans Duration
    • Get Single Plan Duration
    • Create Plan Duration
    • Delete Plan Duration
    • Update Plan Duration
  • Company Roles
    • Get Single Company Role
    • Get All Company Roles
    • Create Company Role
    • Delete Company Role
    • Update Company Role
  • Clients
    • Get All Clients
    • Create Client
    • Get Client By Id
    • Update Client
    • Delete Client
  • Client Threads
    • Get All By Client
    • Get All By Instance
    • Create Client Thread
    • Get Client By Id
    • Update Client
    • Delete Client Thread
  • Admin
    • Get All Plans Duration
    • Get All Plans
    • Create Plan
    • Get All Members
    • Get All Companies
  • Chatwoot Accounts
    • Get Chatwoot Account
    • Update Chatwoot Account
    • Delete Chatwoot Account
    • Create Chatwoot Account
  • Integrations
    • Tokens
      • Fetch All Tokens
      • Fetch Token
      • Update Token
      • Remove Token
    • Google
      • Create Auth URL
      • Regenerate Auth URL
      • Renew Google Webhook
    • External Calendar
      • Create External Event
      • Update External Event
      • Delete External Event
      • Validate Calendar Id
      • Fetch External Calendars
  • Messaging
    • Instances
      • Create Instance
      • Get All Instance
      • Get Instance
      • Update Instance
      • Connect Instance
      • Disconnect Instance
      • Activate Instance
      • Deactivate Instance
    • Message
      • Send Message
  • AI Keys
    • Get All AI Keys
    • Get AI Keys By ID
    • Create AI Key
    • Update AI Key
    • Delete AI Key
  • Agent Prompts
    • Get All Agent Prompts
    • Get Agent Prompt By ID
    • Create Agent Prompt
    • Update Agent Prompt
    • Delete Agent Prompt
  • Agent Configuration
    • Get All Agent Configurations
    • Get Agent Configuration By ID
    • Create Agent Configuration
    • Update Agent Configuration
    • Delete Configuration Prompt
  • Agent Prompt Versions
    • Get All Agent Prompt Versions
    • Get Agent Prompt Version By ID
    • Create Agent Prompt Version
    • Update Agent Prompt Version
    • Delete Agent Prompt Version
  1. Docs

Database

LeadSmart API uses PostgreSQL as the database and Sequelize as the ORM (Object-Relational Mapping) tool. This document outlines the database structure, models, and relationships.

Database Configuration#

The database configuration is managed through Sequelize and defined in the .sequelizerc file and src/config/sequelize.config.ts.

Entities and Relationships#

Core Entities#

User#

The User model represents system users.
Key fields:
id: UUID primary key
name: User's full name
email: User's email address (unique)
password: Hashed password
role: User role (admin, user)
active: User account status
Relationships:
One-to-many with Member (user can be a member of multiple companies)
One-to-many with RefreshToken (user can have multiple refresh tokens)
One-to-many with PendingUser (for invitations)

Company#

The Company model represents organizations in the system.
Key fields:
id: UUID primary key
name: Company name
description: Company description
website: Company website
email: Company email
phone: Company phone
logo: Logo URL
active: Company status
Relationships:
One-to-many with Member (company can have multiple members)
One-to-many with Agenda (company can have multiple agendas)
One-to-many with Client (company can have multiple clients)
One-to-many with CompanyRole (company can define multiple roles)

Member#

The Member model represents a user's membership in a company.
Key fields:
id: UUID primary key
userId: Reference to User
companyId: Reference to Company
companyRoleId: Reference to CompanyRole
active: Membership status
Relationships:
Many-to-one with User (member belongs to a user)
Many-to-one with Company (member belongs to a company)
Many-to-one with CompanyRole (member has a role in the company)
One-to-many with Appointment (member can have multiple appointments)

Client#

The Client model represents clients of a company.
Key fields:
id: UUID primary key
name: Client name
email: Client email
phone: Client phone number
companyId: Reference to Company
notes: Client notes
active: Client status
Relationships:
Many-to-one with Company (client belongs to a company)
One-to-many with ClientThread (client can have multiple conversation threads)
One-to-many with Appointment (client can have multiple appointments)

Appointment Management#

Agenda#

The Agenda model represents appointment calendars.
Key fields:
id: UUID primary key
name: Agenda name
description: Agenda description
companyId: Reference to Company
googleCalendarId: Optional Google Calendar ID
color: Calendar color code
active: Agenda status
Relationships:
Many-to-one with Company (agenda belongs to a company)
One-to-many with Appointment (agenda can have multiple appointments)

Appointment#

The Appointment model represents scheduled meetings.
Key fields:
id: UUID primary key
title: Appointment title
description: Appointment description
startDate: Start date and time
endDate: End date and time
status: Appointment status (scheduled, completed, cancelled)
agendaId: Reference to Agenda
clientId: Reference to Client
googleEventId: Optional Google Calendar event ID
Relationships:
Many-to-one with Agenda (appointment belongs to an agenda)
Many-to-one with Client (appointment is with a client)
Many-to-many with Member through AppointmentMember (appointment has multiple members)

Messaging#

MessagingInstance#

The MessagingInstance model represents WhatsApp messaging instances.
Key fields:
id: UUID primary key
name: Instance name
companyId: Reference to Company
phone: Phone number
status: Connection status
active: Instance status
Relationships:
Many-to-one with Company (messaging instance belongs to a company)

ClientThread#

The ClientThread model represents messaging threads with clients.
Key fields:
id: UUID primary key
clientId: Reference to Client
messagingInstanceId: Reference to MessagingInstance
lastMessage: Last message content
lastMessageDate: Last message timestamp
unreadCount: Number of unread messages
status: Thread status
Relationships:
Many-to-one with Client (thread is with a client)
Many-to-one with MessagingInstance (thread uses a messaging instance)

Plans and Subscriptions#

PlanType#

The PlanType model represents subscription plan types.
Key fields:
id: UUID primary key
name: Plan name
description: Plan description
features: Plan features
price: Plan price
active: Plan status
Relationships:
One-to-many with PlanDuration (plan type can have multiple durations)
Many-to-many with Company (companies can subscribe to plans)

PlanDuration#

The PlanDuration model represents subscription duration options.
Key fields:
id: UUID primary key
planTypeId: Reference to PlanType
months: Duration in months
discount: Discount percentage
active: Duration status
Relationships:
Many-to-one with PlanType (duration belongs to a plan type)

Integration Models#

IntegrationToken#

The IntegrationToken model stores tokens for external service integrations.
Key fields:
id: UUID primary key
companyId: Reference to Company
service: Service name (google, chatwoot, etc.)
accessToken: Access token
refreshToken: Refresh token
expiry: Token expiration date
active: Token status
Relationships:
Many-to-one with Company (token belongs to a company)

GoogleWebhook#

The GoogleWebhook model tracks Google Calendar webhook subscriptions.
Key fields:
id: UUID primary key
companyId: Reference to Company
calendarId: Google Calendar ID
resourceId: Google resource ID
channelId: Google channel ID
expiration: Webhook expiration date
active: Webhook status
Relationships:
Many-to-one with Company (webhook belongs to a company)

OpenAICred#

The OpenAICred model stores OpenAI API credentials.
Key fields:
id: UUID primary key
companyId: Reference to Company
apiKey: OpenAI API key
organization: OpenAI organization ID
active: Credential status
Relationships:
Many-to-one with Company (credentials belong to a company)
One-to-many with OpenAIBots (credentials can be used by multiple bots)

OpenAIBots#

The OpenAIBots model represents OpenAI-powered chatbot configurations.
Key fields:
id: UUID primary key
companyId: Reference to Company
openaiCredId: Reference to OpenAICred
name: Bot name
model: OpenAI model name
temperature: Model temperature setting
systemPrompt: System prompt for the bot
active: Bot status
Relationships:
Many-to-one with Company (bot belongs to a company)
Many-to-one with OpenAICred (bot uses OpenAI credentials)

Database Schema Diagram#

The database schema follows a relational design with normalized tables and proper foreign key relationships. The main entity clusters are:
1.
User Management: User, Member, CompanyRole
2.
Company Management: Company, Client
3.
Appointment Management: Agenda, Appointment, AppointmentMember
4.
Messaging: MessagingInstance, ClientThread
5.
Plans and Billing: PlanType, PlanDuration
6.
Integrations: IntegrationToken, GoogleWebhook, OpenAICred, OpenAIBots
Modificado em 2025-04-24 22:32:03
Página anterior
Integrations
Próxima página
Architecture
Built with