UNO Data Lake

Este repositório contém os modelos de dados utilizados no Uno Data Lake, um sistema para gestão e armazenamento de dados de franquias e seus negócios relacionados. Esta documentação explica a estrutura de cada tabela e seus campos.

Bem-vindo(a) à documentação do UNO Data Lake. Este ambiente concentra os dados operacionais de cada franqueadora e suas unidades (franquias/empresas) em um banco PostgreSQL, permitindo a construção de dashboards, relatórios e análises de BI sobre uma base unificada.

Esta documentação descreve o que cada tabela representa, quais colunas existem, como elas se relacionam e como consultá-las corretamente. Foi escrita para um público misto: analistas de BI (com SQL básico) e engenheiros de dados.

Índice

• UNO Data Lake
  • Índice
  • Visão geral
  • Como acessar o Data Lake
  • Convenções importantes
    • Multi-tenant: a coluna schema e o franchisor_id
    • Sempre filtre porschema.
    • Regra de ouro dos JOINs
    • Chaves: não existe id técnico
    • Índices e desempenho no Postgres
    • Datas, fuso horário e tipos
    • Dinheiro e números
    • Textos truncados
    • Nomes mascarados
    • Atualização e frescor dos dados
    • Exclusões e regras de inclusão
  • Mapa de relacionamentos
  • Catálogo de tabelas
  • Tabelas — Estrutura organizacional
    • franchises
    • companies
    • employees
    • customers
  • Tabelas — CRM e funil comercial
    • deals
    • budgets
  • Tabelas — Vendas (orders e itens)
    • orders
    • order_services
    • order_products
    • order_plans
    • order_courses
    • order_lessons
  • Tabelas — Financeiro
    • payments
    • accounts_payable
  • Tabelas — Catálogo (produtos, serviços, planos, cursos)
    • services
    • products
    • plans
    • courses
    • classes
  • Tabelas — Operação clínica/atendimento
    • appointments
    • rooms
    • room_time_slots
    • measures
    • slimming_goals
  • Tabelas — Metas e desempenho
    • goals
    • employee_goals
  • Tabelas — Royalties e taxas da franqueadora
    • franchise_royalties
    • franchise_royalty_periods
    • franchise_advertising_fees
    • franchise_advertising_fee_periods
    • franchise_categories
    • franchise_category_types
  • Tabelas — Marketing (Facebook Ads)
    • facebook_accounts
    • facebook_campaign_data
  • Tabelas — Sistema
    • metadata
  • Enums e dicionários de valores
    • franchises.status
    • companies.status
    • orders.status
    • budgets.status
    • payments.payment_status_id
    • appointments.status_id
  • Exemplos de queries para BI
    • 1. Faturamento mensal (vendas faturadas)
    • 2. Recebíveis pendentes (a vencer e vencidos)
    • 3. Ranking de vendedores no mês
    • 4. Conversão do funil de CRM
    • 5. Vendas com seus pagamentos (uso correto do JOIN)
    • 6. Ocupação de agenda por profissional
    • 7. Custo por lead (Facebook Ads × Deals)
    • 8. Consolidado da rede (todas as unidades da franqueadora)
    • 9. Frescor dos dados por unidade
  • Boas práticas de desempenho no Postgres

Visão geral

O Data Lake recebe réplicas dos dados transacionais das aplicações operacionais (Vendas, CRM, Atendimento, Financeiro, Marketing) e os organiza em tabelas analíticas amplas, denormalizadas (com nomes já materializados em colunas *_name) para facilitar a leitura por ferramentas de BI.

Características principais:

• Banco: PostgreSQL (Amazon RDS), com SSL obrigatório.
• Um schema por franqueadora: os dados de cada rede ficam em um schema próprio, chamado frq<ID> (ex.: frq4163). O seu login enxerga apenas o schema da sua franqueadora; não há como consultar outra rede.
• Modelo: estrela "achatada" — cada tabela já contém o nome (denormalizado) das principais entidades relacionadas, evitando a maior parte dos JOINs em consultas típicas.
• Multi-tenant dentro do schema: todas as tabelas operacionais possuem a coluna schema, que identifica a unidade (franquia/empresa) dona do registro. Toda consulta deve filtrar por schema.
• Carga direta: as tabelas são projetadas diretamente da base de origem, sem camada intermediária. As tabelas de vendas são atualizadas a cada 30 minutos; as demais, diariamente (ver Atualização e frescor).
• Histórico: as tabelas refletem o estado atual da operação. Para análises temporais, use as colunas de data (created_at, updated_at, due_date, liquidation_date, etc.).

Como acessar o Data Lake

O acesso é feito com um login individual (por pessoa ou por ferramenta de BI), criado pelo operador do Data Lake no painel de operação, na aba Acesso BI da franqueadora.

Exemplo de string de conexão (host, porta e nome do banco são informados pelo operador ao criar o login):

postgres://bi_frq<ID>_<login>:<senha>@<host>:5432/<database>?sslmode=require

Se uma ferramenta exigir o schema explicitamente, informe frq<ID>.

Convenções importantes

Multi-tenant: a coluna schema e o franchisor_id

Quase todas as tabelas possuem duas colunas de contexto:

Por que isso importa? Os IDs de negócio (como order_id, customer_id, appointment_id) são gerados por unidade — ou seja, não são únicos dentro do schema da franqueadora. Duas unidades diferentes podem ter um order_id = 1234 apontando para vendas completamente distintas.

Regra de ouro dos JOINs

Toda relação entre tabelas deve ser feita pela combinação schema + <id_de_negócio> — nunca apenas pelo ID.

Exemplos corretos:

-- ✅ CORRETO: JOIN por schema + order_id
SELECT *
FROM orders o
JOIN payments p
  ON p.schema   = o.schema
 AND p.order_id = o.order_id
WHERE o.schema = 'minha_unidade';

-- ❌ INCORRETO: JOIN apenas por order_id
SELECT *
FROM orders o
JOIN payments p ON p.order_id = o.order_id;  -- vai cruzar dados de unidades diferentes!

Para análises da rede inteira (todas as unidades da franqueadora), basta não filtrar schema no WHERE — mas mantenha o schema em todos os JOINs:

SELECT o.schema, SUM(p.value) AS recebido
FROM orders o
JOIN payments p
  ON p.schema   = o.schema
 AND p.order_id = o.order_id
GROUP BY o.schema;

Chaves: não existe id técnico

A chave primária de cada tabela é composta: (schema, <entidade>_id) — por exemplo (schema, order_id) em orders. Não há coluna id técnica.

Use sempre as colunas com sufixo _id (order_id, customer_id, appointment_id, …) — são essas que vêm dos sistemas operacionais e que devem ser usadas em filtros e JOINs (sempre combinadas com schema).

Única exceção: franchise_category_types tem uma coluna id (bigint) gerada pelo Data Lake, porque a origem não tem identificador próprio para essa junção.

Índices e desempenho no Postgres

Toda tabela tem a chave primária (schema, <id>) e, quando a consulta típica pede, índices adicionais que também começam por schema:

Implicação prática: uma consulta que não filtra por schema não consegue usar nenhum desses índices e varre a tabela inteira. Com o filtro, um recorte por período (created_at, due_date, start_date, date) ou por cliente usa o índice e responde rápido.

Datas, fuso horário e tipos

• Todas as colunas de data/hora usam timestamptz (UTC). Converta para o fuso local da empresa (a coluna companies.timezone indica o fuso, ex.: America/Sao_Paulo) quando for exibir para o usuário final: created_at AT TIME ZONE 'America/Sao_Paulo'.
• facebook_campaign_data.date é do tipo date (dia da métrica, sem hora).
• Textos são text (sem limite de tamanho declarado); inteiros são integer; flags são boolean.
• A moeda padrão de cada empresa está em companies.currency.

Dinheiro e números

• Valores monetários são numeric(14,2) — exatos, sem erro de arredondamento de ponto flutuante. SUM/AVG sobre eles devolvem numeric.
• Percentuais e taxas ficam em numeric(14,4): facebook_campaign_data.frequency, franchise_royalties.percentage, franchise_advertising_fees.percentage.
• Número de parcela é numeric(6,1) em payments.installment e accounts_payable.installment.
• Guarda de faixa: se um valor na origem for impossível (não cabe em 14 dígitos, infinito ou inválido), a coluna chega como NULL — nunca como um número inventado. Nas colunas de total de orders, budgets e dos itens de venda, que são NOT NULL DEFAULT 0, esse caso vira 0.
• A mesma guarda vale para colunas inteiras vindas de campos numéricos da origem (ex.: room_time_slots.start_time, goals.indication): fora da faixa, NULL.

Textos truncados

Alguns nomes e observações são cortados na carga para manter compatibilidade com dashboards existentes:

Nomes mascarados

O banco é compartilhado entre franqueadoras, então nomes de empresas e franquias nunca chegam ao Data Lake:

• companies.name e companies.legal_name trazem o alias cadastrado no painel para a unidade (padrão frq<ID>-u<company_id>). Uma unidade sem alias cadastrado não aparece em companies.
• franchises.name traz o alias da franquia ou, na ausência dele, franquia-<franchise_id>.
• As demais tabelas (customers, employees, deals, …) não são mascaradas.

Se precisar exibir o nome comercial da unidade num dashboard, mantenha um de-para schema → nome na própria ferramenta de BI.

Atualização e frescor dos dados

As tabelas são carregadas em cadências (grupos), configuradas por franqueadora no painel de operação. Valores padrão:

A carga é incremental: só as linhas cujas tabelas de origem mudaram são recalculadas. A coluna updated_at de cada linha é o maior updated_at entre as tabelas de origem que a compõem — por exemplo, orders.updated_at avança quando um pagamento da venda é liquidado, mesmo sem alteração no pedido.

Para saber até quando os dados de uma unidade estão atualizados, consulte a view metadata: ela informa, por unidade e tabela, o dado mais novo já carregado. Atenção: isso não é atraso do pipeline — uma unidade sem venda nova terá last_updated antigo em orders e estará em dia.

Exclusões e regras de inclusão

• Uma linha apagada na origem (ou que deixou de atender à regra de inclusão da tabela) é removida do Data Lake pela verificação de exclusões (diária) ou por uma carga completa. Entre uma coisa e outra, ela pode aparecer por até 24 h.
• Regras de inclusão aplicadas na carga:

Mapa de relacionamentos

                    franchises (1) ──< companies (N)
                         │
                         └──< franchise_royalties ──< franchise_royalty_periods
                         └──< franchise_advertising_fees ──< franchise_advertising_fee_periods
                         └──< franchise_category_types >── franchise_categories

  customers ──┐
              ├──< deals
              ├──< budgets ──> orders
              ├──< orders ──< order_services ──> services
              │                ──< order_products ──> products
              │                ──< order_plans    ──> plans
              │                ──< order_courses  ──> courses ──< classes
              │                ──< order_lessons
              │                ──< payments
              ├──< appointments ──> rooms ──< room_time_slots
              ├──< measures
              └──< slimming_goals

  employees ──< employee_goals >── goals
  accounts_payable (financeiro – contas a pagar)
  facebook_accounts ──< facebook_campaign_data

Todos os JOINs implícitos acima usam schema + <id>.

Catálogo de tabelas

Tabelas — Estrutura organizacional

franchises

Franquias (unidades operacionais) cadastradas. Carregada a partir da unidade franqueadora; o schema aqui é o da própria franqueadora. Não possui franchisor_id.

Chave primária: (schema, franchise_id).

companies

Empresas (CNPJs) operadas pelas unidades. Existe uma company por schema; a empresa-franqueadora não entra. Os nomes são mascarados.

Chave primária: (schema, company_id).

employees

Funcionários das empresas (vendedores, atendentes, profissionais, etc.).

Chave primária: (schema, employee_id).

customers

Clientes finais (PF e PJ).

Chave primária: (schema, customer_id). Índice: (schema, created_at).

Tabelas — CRM e funil comercial

deals

Negociações/oportunidades do funil comercial. Um deal pode (ou não) ser convertido em uma order. Quando o deal tem mais de um agendamento, a linha reflete o mais recente (customer_appointment_*).

Chave primária: (schema, deal_id). Índice: (schema, created_at).

Para taxa de conversão: deals com converted_at IS NOT NULL ou com order_id preenchido.

budgets

Orçamentos elaborados para clientes. Só entram orçamentos com total_price > 0.

Chave primária: (schema, budget_id). Índice: (schema, created_at).

Tabelas — Vendas (orders e itens)

orders

Cabeçalho da venda. Os itens estão nas tabelas order_services, order_products, order_plans, order_courses. Só entram vendas com total_price > 0.

Chave primária: (schema, order_id). Índices: (schema, created_at), (schema, customer_id), (schema, status).

order_services

Itens de serviço de uma venda.

Chave primária: (schema, order_service_id). Índice: (schema, order_id).

order_products

Itens de produto de uma venda.

Chave primária: (schema, order_product_id). Índice: (schema, order_id).

order_plans

Planos contratados em uma venda.

Chave primária: (schema, order_plan_id). Índice: (schema, order_id).

order_courses

Cursos contratados em uma venda.

Chave primária: (schema, order_course_id). Índice: (schema, order_id).

order_lessons

Aulas individuais associadas a order_courses (controle de presença).

Chave primária: (schema, order_lesson_id). Índice: (schema, customer_id).

Tabelas — Financeiro

payments

Recebíveis (parcelas a receber) gerados pelas vendas. Parcelas excluídas na origem não entram.

Chave primária: (schema, payment_id). Índices: (schema, order_id), (schema, created_at), (schema, liquidation_date).

accounts_payable

Contas a pagar da empresa (despesas, fornecedores, etc.). Contas excluídas na origem não entram.

Chave primária: (schema, account_payable_id). Índice: (schema, due_date).

Tabelas — Catálogo (produtos, serviços, planos, cursos)

Todas as tabelas de catálogo seguem o mesmo padrão: schema, franchisor_id, <item>_id (integer, não nulo), franchise_identifier (integer — id no escopo da franquia), name (text), price (numeric(14,2)), active (boolean), created_at, updated_at (timestamptz). Chave primária (schema, <item>_id). Todas as colunas além de schema e do id são nullable.

services

Serviços oferecidos pela unidade.

Chave: service_id.

products

Produtos comercializados. Sem colunas adicionais além do padrão. Chave: product_id.

plans

Planos comercializados. Sem colunas adicionais além do padrão. Chave: plan_id.

courses

Cursos oferecidos. Sem colunas adicionais além do padrão. Chave: course_id.

classes

Turmas de cursos.

Chave primária: (schema, class_id).

Tabelas — Operação clínica/atendimento

appointments

Agendamentos de serviços para clientes. Não entram agendamentos excluídos, sem cliente ou com status_id = 7 (Bloqueio de agenda).

Chave primária: (schema, appointment_id). Índices: (schema, start_date), (schema, customer_id).

rooms

Salas de atendimento.

Chave primária: (schema, room_id).

room_time_slots

Janelas de funcionamento das salas (por dia da semana).

Chave primária: (schema, room_time_slot_id).

measures

Medidas corporais coletadas (típico de redes de estética/saúde). Medidas fora da faixa numérica chegam como NULL.

Chave primária: (schema, measure_id). Índice: (schema, customer_id).

slimming_goals

Metas de emagrecimento por cliente.

Chave primária: (schema, slimming_goal_id).

Tabelas — Metas e desempenho

goals

Metas globais da empresa por período.

Chave primária: (schema, goal_id).

employee_goals

Metas individuais por funcionário.

Chave primária: (schema, employee_goal_id).

Tabelas — Royalties e taxas da franqueadora

As tabelas deste grupo vêm da unidade franqueadora: o schema é o da própria franqueadora e não há coluna franchisor_id.

franchise_royalties

Regras de cobrança de royalties por franquia.

Chave primária: (schema, franchise_royalty_id).

franchise_royalty_periods

Valores apurados de royalties por período.

Chave primária: (schema, franchise_royalty_period_id).

franchise_advertising_fees

Regras de taxa de publicidade. Mesma estrutura de franchise_royalties, trocando franchise_royalty_id por franchise_advertising_fee_id. Chave primária: (schema, franchise_advertising_fee_id).

franchise_advertising_fee_periods

Valores apurados de taxa de publicidade. Mesma estrutura de franchise_royalty_periods, com franchise_advertising_fee_period_id (chave) e franchise_advertising_fee_id (regra associada).

franchise_categories

Categorias para classificar franquias.

Chave primária: (schema, franchise_category_id).

franchise_category_types

Tabela de junção entre franquia e categoria.

Chave primária: (schema, id).

Tabelas — Marketing (Facebook Ads)

facebook_accounts

Contas de anúncio do Facebook integradas.

Chave primária: (schema, facebook_account_id).

facebook_campaign_data

Métricas diárias por anúncio.

Chave primária: (schema, facebook_campaign_data_id). Índice: (schema, date).

Para atribuir leads/vendas a campanhas, cruze com deals.facebook_source_id ou com deals.ad_campaign_name / ad_set_name / ad_name.

Tabelas — Sistema

metadata

View (somente leitura) que informa, para cada par (schema, table), o dado mais novo já carregado no Data Lake.

Use para verificar até quando os dados de uma unidade estão atualizados:

SELECT "table", last_updated
FROM metadata
WHERE schema = 'minha_unidade'
ORDER BY "table";

last_updated é a idade do dado, não do pipeline: uma unidade sem movimentação recente terá valores antigos e estará em dia. A verificação de exclusões não altera esta view.

Enums e dicionários de valores

franchises.status

companies.status

orders.status

budgets.status

payments.payment_status_id

appointments.status_id

Exemplos de queries para BI

Em todos os exemplos, troque 'minha_unidade' pelo schema desejado.

1. Faturamento mensal (vendas faturadas)

SELECT date_trunc('month', created_at) AS mes,
       SUM(total_price)               AS faturamento,
       COUNT(*)                       AS qtd_vendas
FROM orders
WHERE schema = 'minha_unidade'
  AND status = 'BILLED'
  AND created_at >= '2025-01-01'
GROUP BY 1
ORDER BY 1;

2. Recebíveis pendentes (a vencer e vencidos)

SELECT payment_status_name,
       COUNT(*)        AS qtd,
       SUM(value)      AS total
FROM payments
WHERE schema = 'minha_unidade'
  AND payment_status_id IN (1, 3) -- Pendente, Vencido
GROUP BY 1;

3. Ranking de vendedores no mês

SELECT seller_id,
       seller_name,
       COUNT(*)            AS qtd_vendas,
       SUM(total_price)    AS receita,
       SUM(total_paid)     AS recebido
FROM orders
WHERE schema = 'minha_unidade'
  AND status = 'BILLED'
  AND created_at >= date_trunc('month', current_date)
GROUP BY 1, 2
ORDER BY receita DESC;

4. Conversão do funil de CRM

SELECT COUNT(*)                                                 AS leads,
       COUNT(converted_at)                                      AS convertidos,
       COUNT(discarded_at)                                      AS descartados,
       ROUND(100.0 * COUNT(converted_at) / NULLIF(COUNT(*),0), 2) AS taxa_conversao_pct
FROM deals
WHERE schema = 'minha_unidade'
  AND created_at >= current_date - INTERVAL '90 days';

5. Vendas com seus pagamentos (uso correto do JOIN)

SELECT o.order_id,
       o.customer_name,
       o.total_price,
       o.total_paid,
       p.payment_id,
       p.value,
       p.due_date,
       p.payment_status_name
FROM orders o
JOIN payments p
  ON p.schema   = o.schema    -- ✅ obrigatório
 AND p.order_id = o.order_id
WHERE o.schema = 'minha_unidade'
  AND o.created_at >= current_date - INTERVAL '30 days';

6. Ocupação de agenda por profissional

SELECT employee_id,
       employee_name,
       COUNT(*) FILTER (WHERE status_id = 5) AS realizados,
       COUNT(*) FILTER (WHERE status_id = 6) AS faltas,
       COUNT(*)                              AS total_agendamentos
FROM appointments
WHERE schema = 'minha_unidade'
  AND start_date >= date_trunc('month', current_date)
GROUP BY 1, 2
ORDER BY total_agendamentos DESC;

7. Custo por lead (Facebook Ads × Deals)

WITH gasto AS (
  SELECT date, SUM(total_spent) AS spent
  FROM facebook_campaign_data
  WHERE schema = 'minha_unidade'
    AND date >= current_date - INTERVAL '30 days'
  GROUP BY 1
),
leads AS (
  SELECT created_at::date AS date, COUNT(*) AS qtd
  FROM deals
  WHERE schema = 'minha_unidade'
    AND facebook_source_id IS NOT NULL
    AND created_at >= current_date - INTERVAL '30 days'
  GROUP BY 1
)
SELECT g.date,
       g.spent,
       l.qtd                                AS leads,
       ROUND(g.spent / NULLIF(l.qtd, 0), 2) AS cpl
FROM gasto g
LEFT JOIN leads l ON l.date = g.date
ORDER BY g.date;

8. Consolidado da rede (todas as unidades da franqueadora)

Como o schema Postgres já é o da franqueadora, basta agrupar por schema sem filtrá-lo. Para exibir o nome da unidade, cruze com companies (o name é o alias cadastrado no painel).

SELECT o.schema,
       c.name           AS unidade,
       SUM(o.total_price) AS faturamento
FROM orders o
LEFT JOIN companies c
  ON c.schema = o.schema
WHERE o.status = 'BILLED'
  AND o.created_at >= date_trunc('month', current_date)
GROUP BY o.schema, c.name
ORDER BY faturamento DESC;

9. Frescor dos dados por unidade

SELECT schema,
       MAX(last_updated) FILTER (WHERE "table" = 'orders')   AS ultima_venda_vista,
       MAX(last_updated) FILTER (WHERE "table" = 'payments') AS ultimo_pagamento_visto
FROM metadata
GROUP BY schema
ORDER BY schema;

Boas práticas de desempenho no Postgres

1. Sempre filtre por schema. Todo índice começa por essa coluna; sem o filtro, a consulta varre a tabela inteira.
2. Faça JOINs por schema + <id>. Além de correto, é o que permite usar a chave primária composta dos dois lados.
3. Use os campos *_name denormalizados sempre que possível, em vez de fazer JOIN com tabelas-mestras (customers, services, etc.). Eles existem justamente para evitar JOINs em queries comuns de BI.
4. Filtre por colunas indexadas junto com schema: created_at (vendas, pagamentos, clientes, orçamentos, deals), due_date (contas a pagar), start_date (agendamentos), date (Facebook Ads), liquidation_date (pagamentos), customer_id, order_id, status (vendas).
5. Janelas curtas de data (ex.: últimos 90 dias) reduzem muito o volume lido.
6. Selecione só as colunas necessárias — menos dados trafegando para a ferramenta de BI.
7. Prefira agregações GROUP BY em colunas de baixa cardinalidade (status, employee_id, schema) e dimensões temporais (date_trunc(...)).
8. Use WITH (CTEs) para deixar a query legível; o planejador do Postgres as otimiza normalmente.
9. Confira com EXPLAIN se a consulta usa índice (Index Scan / Bitmap Index Scan) em vez de Seq Scan quando ela estiver lenta.
10. Lembre dos limites do login: 15 minutos por consulta e 10 conexões simultâneas. Configure pool e cache na ferramenta de BI em vez de abrir uma conexão por painel.
11. Para saber até onde o Data Lake está atualizado, consulte a view metadata.

Em caso de dúvidas sobre o significado de um campo ou enum específico, consulte o time de dados — esta documentação é viva e será atualizada conforme novas tabelas e colunas forem adicionadas ao Data Lake.