Banco de dados recomendado

O contrato obrigatório é a API. Este esquema MySQL/MariaDB é apenas uma referência para quem está criando o sistema comercial.

Planos

CREATE TABLE plans (
  id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  users INT NOT NULL DEFAULT 1,
  users_connected INT NOT NULL DEFAULT 1,
  channels INT NOT NULL DEFAULT 1,
  campaign_messages INT NOT NULL DEFAULT 0,
  price DECIMAL(10,2) NOT NULL DEFAULT 0,
  additional_user_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  additional_channel_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  additional_space_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  trial INT NOT NULL DEFAULT 0,
  trial_days INT NOT NULL DEFAULT 0,
  recurrence VARCHAR(50) NOT NULL DEFAULT 'MENSAL',
  activeia BOOLEAN NOT NULL DEFAULT false,
  ia_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  activecampaign BOOLEAN NOT NULL DEFAULT false,
  campaign_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  activecampaignwaba BOOLEAN NOT NULL DEFAULT false,
  campaign_waba_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  activecampaignsms BOOLEAN NOT NULL DEFAULT false,
  campaign_sms_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  activetypebot BOOLEAN NOT NULL DEFAULT false,
  typebot_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  activescheduler BOOLEAN NOT NULL DEFAULT false,
  scheduler_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  activekanban BOOLEAN NOT NULL DEFAULT false,
  kanban_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  activecampaigndigicalls BOOLEAN NOT NULL DEFAULT false,
  campaign_digicalls_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  activeinternalchat BOOLEAN NOT NULL DEFAULT false,
  internal_chat_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  value_credit DECIMAL(10,2) NOT NULL DEFAULT 0,
  contracted_space DECIMAL(10,2) NOT NULL DEFAULT 0,
  max_contacts INT NULL,
  is_hidden BOOLEAN NOT NULL DEFAULT false,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
);

Migração de uma tabela anterior

ALTER TABLE plans
  ADD COLUMN additional_user_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD COLUMN additional_channel_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD COLUMN additional_space_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD COLUMN ia_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD COLUMN campaign_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD COLUMN campaign_waba_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD COLUMN campaign_sms_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD COLUMN typebot_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD COLUMN scheduler_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD COLUMN kanban_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD COLUMN activecampaigndigicalls BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN campaign_digicalls_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  ADD COLUMN activeinternalchat BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN internal_chat_price DECIMAL(10,2) NOT NULL DEFAULT 0;

Adicionar a coluna sem retorná-la no endpoint de planos não produz efeito no DigitalSac.

Servidores

CREATE TABLE servers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  is_default BOOLEAN NOT NULL DEFAULT false,
  ip VARCHAR(500) NOT NULL,
  url VARCHAR(500) NOT NULL,
  token VARCHAR(500) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
);

Garanta pelo menos um servidor ativo e exatamente um padrão.

Leads

CREATE TABLE digitalsac_leads (
  id INT PRIMARY KEY AUTO_INCREMENT,
  plan_id VARCHAR(50) NOT NULL,
  server_id VARCHAR(50) NOT NULL,
  partner_id VARCHAR(50) NULL,
  name VARCHAR(255) NOT NULL,
  phone VARCHAR(30) NOT NULL,
  email VARCHAR(255) NOT NULL,
  document VARCHAR(30) NOT NULL,
  password_hash VARCHAR(255) NULL,
  locale VARCHAR(20) DEFAULT 'pt-BR',
  status VARCHAR(50) NOT NULL DEFAULT 'pending',
  external_tenant_id VARCHAR(100) NULL,
  access_url VARCHAR(500) NULL,
  payload_json JSON NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL
);

Nunca armazene a senha recebida em texto puro. Use hash adequado e proteja payload_json contra persistência acidental de segredos.