Pular para conteúdo

Modelo de Dados e Banco de Dados

O PostgreSQL merchants-db guarda os dados duráveis da plataforma EcomSmart Agents. Ele não guarda o estado temporário das conversas, que pertence ao Redis, nem imagens ou vetores de catálogo, que pertencem ao MinIO e ao Weaviate.

Esta página mostra o modelo em nível de tabela. Ela é uma referência para entender dependências e limites de dados; mudanças de schema continuam sendo feitas por migrations do Alembic.

Schemas e Responsabilidades

O banco possui dois schemas de aplicação:

Schema Responsabilidade Exemplos
platform Operação da plataforma, catálogo, conversas, créditos, Flows e marketing. merchants, messages, credit_movements, campanhas
genai Ledger de consumo e custo de IA. ai_usage_events, ai_usage_events_orphaned

Marketing não é um schema PostgreSQL

O domínio de marketing usa tabelas do schema platform. Os nomes como campanhas, rfm_snapshot e market_basket_regras não pertencem a um schema separado chamado marketing.

Mapa do Modelo Operacional

O diagrama cobre as relações físicas mais relevantes. A seta representa uma chave estrangeira; o rótulo indica a regra usada na exclusão quando ela muda a retenção dos dados.

flowchart TB
    subgraph platform["schema platform"]
        registry["merchant_registry<br/>identidade histórica da loja"]
        merchants["merchants<br/>configuração e credenciais ativas"]
        plans["plans<br/>catálogo de planos"]
        credits["credit_movements<br/>ledger de créditos"]

        users["admin_users"]
        audit["admin_user_audit"]
        config["platform_config<br/>configuração central singleton"]

        identities["conversation_identities<br/>PII do contato"]
        sessions["conversation_sessions<br/>fato da conversa"]
        messages["messages<br/>histórico B2C e B2B"]

        fields["product_field_definitions"]
        types["product_types"]
        typeFields["product_type_fields"]
        images["product_image_sync"]

        flowConfig["flow_text_configs"]
        flowVersions["flow_text_versions"]
        flowJobs["flow_publication_jobs"]
        flowItems["flow_publication_items"]

        marketingMap["marketing_empresa_mapping"]
        marketingData["clientes, produtos, itens_pedido<br/>campanhas e snapshots analíticos"]
    end

    subgraph genai["schema genai"]
        usage["ai_usage_events<br/>uso e custo de IA"]
        orphaned["ai_usage_events_orphaned<br/>arquivo de registros sem loja válida"]
    end

    merchants -->|"mesmo id; RESTRICT"| registry
    merchants -->|"plan_id; SET NULL"| plans
    credits -->|"RESTRICT"| registry
    sessions -->|"RESTRICT"| registry
    usage -->|"RESTRICT"| registry

    users -->|"CASCADE"| merchants
    audit -->|"actor_user_id; SET NULL"| users
    identities -->|"CASCADE"| merchants
    sessions -->|"identity_id; SET NULL"| identities
    messages -->|"CASCADE"| merchants
    messages -->|"session_id; SET NULL"| sessions

    fields -->|"CASCADE"| merchants
    types -->|"CASCADE"| merchants
    typeFields -->|"CASCADE"| types
    typeFields -->|"field_key; CASCADE"| fields
    images -->|"CASCADE"| merchants

    flowConfig -->|"escopo da loja; CASCADE"| merchants
    flowVersions -->|"CASCADE"| flowConfig
    flowItems -->|"CASCADE"| flowJobs
    flowJobs -->|"SET NULL"| flowConfig
    flowItems -->|"SET NULL"| flowConfig

    marketingMap -->|"CASCADE"| registry
    marketingData -. "empresa_id lógico" .-> marketingMap

Leituras Importantes do Diagrama

Como ler a referência de campos

As seções abaixo não repetem os campos operacionais previsíveis, como id, created_at e updated_at, a menos que tenham uma regra importante. Elas destacam os campos que definem o comportamento do domínio.

Marca Significado
PK Chave primária: identifica uma linha.
FK Chave estrangeira: aponta para uma linha em outra tabela.
UNIQUE Valor ou conjunto de valores que não pode se repetir.
JSONB Estrutura flexível em JSON; seu formato é um contrato da aplicação, não uma tabela separada.

Uma relação com CASCADE remove os dados filhos quando o registro pai é removido. SET NULL preserva o filho, mas remove a referência. RESTRICT impede a remoção do pai enquanto houver histórico associado.

Identidade, loja ativa e retenção

platform.merchant_registry é a âncora histórica da loja. platform.merchants usa o mesmo identificador para concentrar a configuração ativa, credenciais e dados operacionais da loja.

Fatos que precisam continuar auditáveis, mesmo após a remoção operacional de uma loja, apontam para merchant_registry com RESTRICT: sessões de conversa, movimentos de crédito e eventos de IA. Já dados operacionais e PII, como mensagens, contatos e configurações de catálogo, apontam para merchants e podem ser removidos em cascata.

Não troque a âncora por conveniência

Ao criar uma nova tabela, escolha explicitamente entre retenção histórica (merchant_registry) e remoção com a loja (merchants). Não deixe essa decisão implícita nem substitua uma chave estrangeira por um UUID sem constraint.

Identidade, configuração e acesso
Tabela Campos de domínio Chaves e relacionamentos
merchant_registry id, store_name, created_at, deleted_at PK id. Registro histórico da loja; não guarda credenciais.
merchants id, store_name, email, status, identidade e token do WhatsApp, credenciais do WooCommerce, agent_settings (JSONB), plan_id, credit_balance, used_credits_this_month, plan_expires_at PK/FK id -> merchant_registry.id com RESTRICT. plan_id -> plans.id com SET NULL. email, número e ID de telefone do WhatsApp são únicos.
plans id, name, tag, tag_name, monthly_price_cents, active_users, monthly_credits PK id; tag é única. O catálogo atual permite bronze, silver e gold.
admin_users id, name, email, password_hash, merchant_id, role, status, last_login_at merchant_id -> merchants.id com CASCADE. Usuário de papel merchant precisa de loja; papéis centrais não podem ter uma.
admin_user_audit actor_user_id, target_user_id, action, details (JSONB) actor_user_id -> admin_users.id com SET NULL; a trilha permanece mesmo se o autor for removido.
platform_config Credenciais e identificadores centrais de WhatsApp, e-mail, flow_ids e ai_models (JSONB) Singleton: id = true. É configuração da plataforma, não da loja. ai_models mantém os modelos habilitados e os valores que o painel usa para estimar custo.

Créditos e consumo de IA

platform.plans define o catálogo de planos. A loja pode ter um plano ativo em merchants.plan_id; os saldos operacionais também ficam nessa tabela.

platform.credit_movements é o ledger imutável de créditos. Cada consumo de IA pode originar um movimento com source e external_event_id, que evita lançar o mesmo evento duas vezes.

genai.ai_usage_events registra endpoint, modelo, tokens de entrada, saída, raciocínio, cache e ferramentas, além dos custos em USD. Ele possui FK para merchant_registry. Registros históricos que não puderam ser associados a uma loja válida são preservados em genai.ai_usage_events_orphaned, sem FK, para não esconder custos já incorridos.

Créditos, precificação e uso de IA
Tabela Campos de domínio Chaves e relacionamentos
credit_pricing credit_price_cents_per_1000, updated_at Singleton (id = true) que define o valor de referência de mil créditos.
credit_movements merchant_id, event_type, description, source, category, amount, status, external_event_id, metadata (JSONB), occurred_at merchant_id -> merchant_registry.id com RESTRICT. UNIQUE (source, external_event_id) evita duplicar um mesmo lançamento externo. amount positivo adiciona e negativo consome créditos.
ai_usage_events merchant_id, endpoint, step, provider, model, input_tokens, output_tokens, reasoning_tokens, cached_input_tokens, cache_write_input_tokens, tool_input_tokens, provider_tool_calls, custos por componente e cost_usd merchant_id -> platform.merchant_registry.id com RESTRICT. É o ledger técnico de cada chamada ao provedor, não o saldo da loja.
ai_usage_events_orphaned Mesma estrutura de ai_usage_events Arquivo de custos que não puderam receber uma loja válida durante a migração. Não tem FK intencionalmente.

O saldo atual em merchants acelera a operação. credit_movements e ai_usage_events são a fonte para auditoria e reconciliação; não devem ser sobrescritos para "corrigir" saldo.

Configuração de campanhas

platform.marketing_campaign_settings guarda apenas diferenças em relação aos padrões do motor de campanhas. Assim, uma loja pode personalizar textos e regras sem alterar as demais; remover a configuração restaura o comportamento padrão.

Tabela Campos de domínio Chaves e relacionamentos
marketing_campaign_settings empresa_id, settings (JSONB) PK empresa_id -> marketing_empresa_mapping.empresa_id com CASCADE. Um registro por empresa de marketing; ausência do registro significa uso integral dos padrões.

Conversas e privacidade

platform.conversation_sessions é o registro histórico de uma conversa B2C ou B2B. O campo scope diferencia os dois contextos. Ele não contém telefone diretamente.

O telefone fica em platform.conversation_identities, junto do merchant_id e do escopo. Essa separação permite preservar métricas de sessão e remover dados pessoais quando a loja for excluída. platform.messages concentra o histórico de mensagens dos dois domínios e pode apontar opcionalmente para uma sessão.

Conversas, mensagens e PII
Tabela Campos de domínio Chaves e relacionamentos
conversation_identities id, merchant_id, scope, counterpart_phone merchant_id -> merchants.id com CASCADE. UNIQUE (merchant_id, scope, counterpart_phone) garante uma identidade por contato e contexto. Contém PII.
conversation_sessions id, merchant_id, identity_id, scope, state, outcome_status, furthest_stage, wc_order_id, woocommerce_status, feedback_rating, cart_items (JSONB), marcos de início, interação, fechamento e pagamento merchant_id -> merchant_registry.id com RESTRICT; identity_id -> conversation_identities.id com SET NULL. Há no máximo uma sessão aberta por identidade e um pedido WooCommerce não pode aparecer duas vezes para a mesma loja.
messages merchant_id, session_id, scope, counterpart_phone, direction, sender_type, message_type, content, media_url, status, raw_payload (JSONB) merchant_id -> merchants.id com CASCADE; session_id -> conversation_sessions.id com SET NULL. Guarda B2C e B2B na mesma tabela; o telefone aqui faz parte do histórico e acompanha a exclusão da loja.

scope vale b2c ou b2b. Em mensagens, sender_type vale counterpart, bot ou human_agent; não misture essa semântica com o escopo.

Catálogo gerenciável

O catálogo que orienta os Flows B2B é formado por:

Tabela Papel
product_field_definitions Campos nativos ou específicos da loja.
product_types Tipos/categorias, incluindo a referência de categoria do WooCommerce quando disponível.
product_type_fields Associação entre tipo e campo, com visibilidade, obrigatoriedade, ordem e geração por IA.
product_image_sync Estado de sincronização da imagem do produto entre WooCommerce e MinIO.

Linhas com merchant_id nulo em campos e tipos são o template global. Linhas com merchant_id preenchido pertencem a uma loja. O importador do WooCommerce usa source em product_types para distinguir template, categoria importada e categoria criada manualmente.

Catálogo, campos e imagens
Tabela Campos de domínio Chaves e relacionamentos
product_field_definitions key, merchant_id, label, input_type, native, wc_key, ui_role, max_length, position PK key; merchant_id -> merchants.id com CASCADE. Define o campo reutilizável e como ele aparece no Flow.
product_types id, merchant_id, code, title, position, wc_category_id, wc_parent_id, source merchant_id -> merchants.id com CASCADE. UNIQUE (merchant_id, code) e unicidade parcial de categoria WooCommerce por loja.
product_type_fields product_type_id, field_key, show, generate_by_ai, is_variation, required, position PK (product_type_id, field_key). Liga tipo e campo; ambas as FKs usam CASCADE.
product_image_sync merchant_id, product_id, source_url, source_version, minio_object_key, public_url, content_hash, status, last_error PK (merchant_id, product_id); merchant_id -> merchants.id com CASCADE. status é ready, placeholder ou failed.

Textos de Flow e configuração central

platform.platform_config é um singleton para a identidade central do WhatsApp, configurações globais e modelos de IA permitidos. Não deve ser usado para configuração por loja.

As personalizações de texto de Flow usam flow_text_configs, suas versões em flow_text_versions e a trilha de publicação em flow_publication_jobs e flow_publication_items. Um flow_text_configs com escopo b2c_merchant pertence a uma loja; os escopos globais não possuem merchant_id.

Editor e publicação de textos de Flow
Tabela Campos de domínio Chaves e relacionamentos
flow_text_configs id, scope, merchant_id, flow_key, base_fingerprint, draft_overrides (JSONB), published_overrides (JSONB), revisões de rascunho e publicação, updated_by merchant_id -> merchants.id com CASCADE. UNIQUE (scope, merchant_id, flow_key). Só b2c_merchant recebe loja; b2c_default e b2b_central são globais.
flow_text_versions config_id, version, overrides (JSONB), base_fingerprint, change_summary, created_by config_id -> flow_text_configs.id com CASCADE. UNIQUE (config_id, version) preserva o histórico de publicação.
flow_publication_jobs scope, flow_key, config_id, status, contadores total, succeeded, failed, solicitante e marcos de execução config_id -> flow_text_configs.id com SET NULL. Registra uma tentativa de publicar.
flow_publication_items job_id, merchant_id, config_id, draft_revision, overrides_snapshot (JSONB), status, error job_id -> flow_publication_jobs.id com CASCADE; merchant_id -> merchants.id com CASCADE; config_id -> flow_text_configs.id com SET NULL. Mostra o resultado por loja.

Marketing: Relacionamentos Lógicos

platform.marketing_empresa_mapping cria a equivalência física entre merchant_registry.id e empresa_id, usado pelo domínio de marketing. A partir dela, o domínio mantém os seguintes grupos:

  • Dados importados: clientes, produtos e itens_pedido.
  • Execução e calendário: marketing_pipeline_execucoes e calendario.
  • Ações comerciais: campanhas e campanhas_sugeridas.
  • Análises: rfm_snapshot, rfm_segmentos, fuzzy_rfm_snapshot, fuzzy_cmeans_snapshot, fuzzy_cmeans_centros, fuzzy_segmento_snapshot, prioridade_ofertas_snapshot, ofertas_snapshot e market_basket_regras.

Muitas dessas tabelas armazenam empresa_id sem uma FK declarada. A relação existe no domínio, mas o banco não a impõe. Código de importação, limpeza ou relatório precisa validar o mapeamento antes de usar esse identificador.

Marketing: dados, campanhas e análises
Grupo Tabelas e campos que orientam a leitura Relação
Mapeamento marketing_empresa_mapping: empresa_id, merchant_id, created_at merchant_id -> merchant_registry.id com CASCADE; é a única equivalência física para o identificador legado empresa_id.
Dados importados clientes: customer_id, dados de contato e localização; produtos: product_id, nome, categoria, preço e estoque; itens_pedido: pedido, cliente, produto, quantidade, preço e região Cada linha tem empresa_id; as chaves naturais são únicas por empresa para clientes e produtos. itens_pedido mantém referências lógicas, sem FKs para os outros dados importados.
Execução comercial marketing_pipeline_execucoes: status, contadores e erro; campanhas: público, oferta, canal, vigência, orçamento e status; campanhas_sugeridas: sugestão, cluster e possível campanha_id campanhas_sugeridas.campanha_id -> campanhas.id com SET NULL. O restante usa empresa_id como relação lógica.
Calendário calendario: nome, slug, data, componentes da data, tipo, abrangência, localidade, recorrência, empresa_id, impacto, origem e estado Possui índice por data e empresa, mas não FK para marketing_empresa_mapping. É a versão corrigida do calendário, não a estrutura inicial da migration.
Análises Snapshots RFM, fuzzy, clusters, prioridade de ofertas, ofertas e regras de cesta Todos são agrupados por empresa_id; customer_id, quando presente, referencia logicamente o cliente importado.

Cuidado com migrations de marketing

A tabela calendario já recebeu uma migration corretiva de schema. Não deduza seus campos por migrations antigas ou por um ambiente local defasado; consulte o schema aplicado antes de criar consultas ou novas migrations.

Tabelas de Suporte

Tabela Finalidade
admin_users e admin_user_audit Acesso administrativo e trilha de alterações de usuários.
healthcheck_results Último resultado de healthcheck por loja.
system_prompts Versões administráveis dos prompts usados pelos agentes.
alembic_version Revisão de schema aplicada no banco. Não é dado de negócio.

Fonte de Verdade e Evolução

O modelo atual resulta do baseline mais as migrations aplicadas:

  1. infra/db/init/01-schema.sql provisiona a estrutura inicial para um banco novo.
  2. infra/db/migrations/versions/ contém os deltas evolutivos e correções de ambientes já existentes.
  3. O serviço db-migrate executa a atualização durante a subida controlada da stack.

O baseline contém estruturas históricas que foram substituídas por migrations, como as antigas tabelas separadas de mensagens B2C e B2B. Portanto, para investigar um ambiente em execução, considere a revisão registrada em platform.alembic_version e o schema efetivamente aplicado, não apenas o arquivo de baseline.

Para criar ou alterar uma tabela:

  1. Defina o dono e a política de retenção antes de escolher a FK.
  2. Crie uma migration idempotente e compatível com bancos já provisionados.
  3. Valide a cadeia de migrations e execute o procedimento de Deploy e Migrações.
  4. Atualize esta página quando a mudança afetar uma relação, uma regra de retenção ou um domínio visível para a equipe.