Skip to main content

Banco de Dados

📋 Visão Geral​

O Portal FindUP utiliza PostgreSQL via Supabase, implementando um schema robusto com Row Level Security (RLS), triggers automáticos e funções customizadas para garantir integridade, segurança e performance.

🗄️ Schema do Banco​

Diagrama de Relacionamentos​

📊 Tabelas Principais​

auth.users (Supabase Auth)​

Sistema de autenticação nativo do Supabase.

-- Gerenciado pelo Supabase Auth
-- Contém: id, email, encrypted_password, etc.

profiles​

Perfis estendidos dos usuários.

CREATE TABLE public.profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID UNIQUE REFERENCES auth.users(id) ON DELETE CASCADE,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT,
  document_number TEXT, -- CPF/CNPJ
  company_name TEXT,
  commission_rate DECIMAL(5,4) DEFAULT 0.10,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

user_roles​

Sistema de roles hierárquico.

CREATE TYPE public.app_role AS ENUM ('admin', 'technician', 'user');

CREATE TABLE public.user_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role app_role NOT NULL,
  UNIQUE (user_id, role)
);

products​

Catálogo de produtos e serviços.

CREATE TYPE public.product_type AS ENUM (
  'google_workspace',
  'microsoft_365',
  'other'
);

CREATE TABLE public.products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  description TEXT,
  product_type product_type NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  monthly_price DECIMAL(10,2), -- Para assinaturas
  commission_percentage DECIMAL(5,4) DEFAULT 0.10,
  is_active BOOLEAN DEFAULT true,
  image_url TEXT,
  features TEXT[], -- Array de recursos
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

customers​

Base de clientes dos técnicos.

CREATE TABLE public.customers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  technician_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE,
  company_name TEXT NOT NULL,
  contact_name TEXT NOT NULL,
  email TEXT NOT NULL,
  phone TEXT NOT NULL,
  cnpj TEXT,
  address TEXT,
  city TEXT,
  state TEXT,
  zip_code TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

proposals​

Propostas comerciais.

CREATE TYPE public.proposal_status AS ENUM (
  'pending',
  'sent',
  'activated',
  'email_sent',
  'approved',
  'rejected'
);

CREATE TABLE public.proposals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID REFERENCES public.customers(id) ON DELETE CASCADE,
  technician_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE,
  product_id UUID REFERENCES public.products(id) ON DELETE CASCADE,
  monthly_value DECIMAL(10,2) NOT NULL,
  contract_months INTEGER DEFAULT 12,
  total_value DECIMAL(10,2) NOT NULL,
  status proposal_status DEFAULT 'pending',
  proposal_pdf_url TEXT,
  email_sent_at TIMESTAMPTZ,
  approval_token TEXT UNIQUE,
  approved_at TIMESTAMPTZ,
  approved_by_email TEXT,
  activated_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

orders​

Pedidos gerados a partir das propostas.

CREATE TYPE public.order_status AS ENUM (
  'pending',
  'processing',
  'completed',
  'cancelled'
);

CREATE TABLE public.orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  proposal_id UUID REFERENCES public.proposals(id),
  customer_id UUID REFERENCES public.customers(id),
  technician_id UUID REFERENCES public.profiles(id),
  product_id UUID REFERENCES public.products(id),
  quantity INTEGER DEFAULT 1,
  unit_price DECIMAL(10,2) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  status order_status DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

commissions​

Comissões dos técnicos.

CREATE TABLE public.commissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID REFERENCES public.orders(id),
  technician_id UUID REFERENCES public.profiles(id),
  amount DECIMAL(10,2) NOT NULL,
  commission_rate DECIMAL(5,4) NOT NULL,
  vivo_payment_date DATE,
  vivo_payment_received BOOLEAN DEFAULT false,
  scheduled_payment_date DATE,
  payment_status TEXT DEFAULT 'pending'
    CHECK (payment_status IN ('pending', 'scheduled', 'paid')),
  notes TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

payment_calendar​

Calendário de repasses.

CREATE TABLE public.payment_calendar (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  month_year TEXT UNIQUE NOT NULL,
  vivo_payment_deadline DATE NOT NULL,
  findup_payment_deadline DATE NOT NULL,
  total_commissions_due DECIMAL(10,2) DEFAULT 0,
  total_commissions_paid DECIMAL(10,2) DEFAULT 0,
  status TEXT DEFAULT 'open'
    CHECK (status IN ('open', 'processing', 'completed')),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

audit_logs​

Logs de auditoria.

CREATE TABLE public.audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id),
  action TEXT NOT NULL,
  table_name TEXT,
  record_id UUID,
  old_values JSONB,
  new_values JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

🔐 Row Level Security (RLS)​

Políticas por Tabela​

profiles​

-- Usuários podem ver apenas seu próprio perfil
CREATE POLICY "Users can view own profile"
ON profiles FOR SELECT
USING (auth.uid() = user_id);

-- Admins podem ver todos os perfis
CREATE POLICY "Admins can view all profiles"
ON profiles FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid()
    AND role IN ('admin', 'super_admin')
  )
);

customers​

-- Técnicos veem apenas seus clientes
CREATE POLICY "Technicians view own customers"
ON customers FOR SELECT
USING (
  technician_id IN (
    SELECT id FROM profiles WHERE user_id = auth.uid()
  ) OR
  EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid() AND role IN ('admin', 'super_admin')
  )
);

proposals​

-- Técnicos veem apenas suas propostas
CREATE POLICY "Technicians view own proposals"
ON proposals FOR SELECT
USING (
  technician_id IN (
    SELECT id FROM profiles WHERE user_id = auth.uid()
  ) OR
  EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid() AND role IN ('admin', 'super_admin')
  )
);

-- Acesso público via token para aprovação
CREATE POLICY "Public access via approval token"
ON proposals FOR SELECT
TO anon
USING (approval_token IS NOT NULL);

commissions​

-- Técnicos veem apenas suas comissões
CREATE POLICY "Technicians view own commissions"
ON commissions FOR SELECT
USING (
  technician_id IN (
    SELECT id FROM profiles WHERE user_id = auth.uid()
  ) OR
  EXISTS (
    SELECT 1 FROM user_roles
    WHERE user_id = auth.uid() AND role IN ('admin', 'super_admin')
  )
);

⚡ Funções Customizadas​

has_role(user_id, role)​

Verifica se um usuário tem determinado role.

CREATE OR REPLACE FUNCTION public.has_role(_user_id uuid, _role public.app_role)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.user_roles
    WHERE user_id = _user_id AND role = _role
  )
$$;

calculate_payment_dates(sale_date)​

Calcula datas de pagamento da Vivo e FindUP.

CREATE OR REPLACE FUNCTION public.calculate_payment_dates(sale_date DATE)
RETURNS TABLE (vivo_deadline DATE, findup_deadline DATE)
AS $$
DECLARE
  next_month_start DATE;
  vivo_date DATE;
  findup_date DATE;
  business_days INTEGER := 0;
  current_date_calc DATE;
BEGIN
  -- Primeiro dia do próximo mês
  next_month_start := DATE_TRUNC('month', sale_date + INTERVAL '1 month');
  current_date_calc := next_month_start;

  -- Contar 15 dias úteis
  WHILE business_days < 15 LOOP
    IF EXTRACT(DOW FROM current_date_calc) NOT IN (0, 6) THEN
      business_days := business_days + 1;
    END IF;

    IF business_days < 15 THEN
      current_date_calc := current_date_calc + INTERVAL '1 day';
    END IF;
  END LOOP;

  vivo_date := current_date_calc;
  findup_date := vivo_date + INTERVAL '10 days';

  RETURN QUERY SELECT vivo_date, findup_date;
END;
$$ LANGUAGE plpgsql;

generate_approval_token()​

Gera token seguro para aprovação de propostas.

CREATE OR REPLACE FUNCTION public.generate_approval_token()
RETURNS TEXT
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    RETURN encode(gen_random_bytes(32), 'hex');
END;
$$;

🔄 Triggers​

Fluxo de Triggers Automáticos​

Atualização de updated_at​

CREATE OR REPLACE FUNCTION public.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Aplicar em todas as tabelas relevantes
CREATE TRIGGER update_profiles_updated_at
  BEFORE UPDATE ON public.profiles
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();

Geração automática de tokens​

CREATE OR REPLACE FUNCTION public.set_approval_token()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    IF NEW.approval_token IS NULL THEN
        NEW.approval_token = public.generate_approval_token();
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER set_approval_token_trigger
    BEFORE INSERT ON public.proposals
    FOR EACH ROW
    EXECUTE FUNCTION public.set_approval_token();

Cálculo automático de comissões​

CREATE OR REPLACE FUNCTION public.handle_proposal_activation()
RETURNS TRIGGER AS $$
DECLARE
  month_key TEXT;
  payment_dates RECORD;
  commission_value DECIMAL(10,2);
BEGIN
  IF NEW.status = 'activated' AND OLD.status != 'activated' THEN
    -- Calcular datas de pagamento
    SELECT * INTO payment_dates
    FROM public.calculate_payment_dates(NEW.activated_at::DATE);

    -- Chave do mês/ano
    month_key := TO_CHAR(NEW.activated_at, 'YYYY-MM');

    -- Calcular comissão: (mensalidade × 3) ÷ 2
    commission_value := (NEW.monthly_value * 3) / 2;

    -- Inserir/atualizar calendário de pagamento
    INSERT INTO public.payment_calendar (
      month_year,
      vivo_payment_deadline,
      findup_payment_deadline,
      total_commissions_due
    )
    VALUES (
      month_key,
      payment_dates.vivo_deadline,
      payment_dates.findup_deadline,
      commission_value
    )
    ON CONFLICT (month_year)
    DO UPDATE SET
      total_commissions_due = payment_calendar.total_commissions_due + commission_value;

    -- Atualizar comissão com as datas
    UPDATE public.commissions
    SET
      scheduled_payment_date = payment_dates.findup_deadline,
      amount = commission_value
    WHERE order_id IN (
      SELECT id FROM public.orders WHERE proposal_id = NEW.id
    );
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_proposal_activation
  AFTER UPDATE ON public.proposals
  FOR EACH ROW EXECUTE FUNCTION public.handle_proposal_activation();

📈 Índices para Performance​

-- Índices principais
CREATE INDEX idx_customers_technician_id ON public.customers(technician_id);
CREATE INDEX idx_proposals_customer_id ON public.proposals(customer_id);
CREATE INDEX idx_proposals_technician_id ON public.proposals(technician_id);
CREATE INDEX idx_proposals_status ON public.proposals(status);
CREATE INDEX idx_commissions_technician_id ON public.commissions(technician_id);
CREATE INDEX idx_payment_calendar_month_year ON public.payment_calendar(month_year);
CREATE INDEX idx_audit_logs_user_id ON public.audit_logs(user_id);
CREATE INDEX idx_audit_logs_created_at ON public.audit_logs(created_at);

-- Índices compostos
CREATE INDEX idx_proposals_tech_status ON public.proposals(technician_id, status);
CREATE INDEX idx_commissions_tech_status ON public.commissions(technician_id, payment_status);

🔒 Backup e Recuperação​

Backup Automático (Supabase)​

O Supabase realiza backups automáticos diários.

Backup Manual​

# Backup via CLI do Supabase
supabase db dump --file backup.sql

# Restaurar backup
supabase db reset --from-file backup.sql

Exportação de Dados​

-- Exportar dados específicos
COPY (SELECT * FROM proposals WHERE created_at >= '2025-01-01')
TO '/tmp/proposals_2025.csv' DELIMITER ',' CSV HEADER;

🔍 Monitoramento​

Queries de Performance​

-- Queries mais lentas
SELECT query, mean_time, calls, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Tamanho das tabelas
SELECT
  tablename,
  pg_size_pretty(pg_total_relation_size(tablename::regclass)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

Logs de Auditoria​

-- Ações recentes por usuário
SELECT
  al.action,
  al.table_name,
  p.full_name,
  al.created_at
FROM audit_logs al
JOIN profiles p ON p.user_id = al.user_id
ORDER BY al.created_at DESC
LIMIT 50;

🧹 Manutenção​

Limpeza de Logs Antigos​

-- Limpar logs de auditoria com mais de 1 ano
DELETE FROM audit_logs
WHERE created_at < now() - INTERVAL '1 year';

Otimização​

-- Analisar estatísticas das tabelas
ANALYZE;

-- Reindexar se necessário
REINDEX TABLE proposals;

📊 Relatórios SQL​

Vendas por Técnico​

SELECT
  p.full_name as tecnico,
  COUNT(pr.id) as total_propostas,
  COUNT(CASE WHEN pr.status = 'activated' THEN 1 END) as ativadas,
  SUM(CASE WHEN pr.status = 'activated' THEN pr.total_value ELSE 0 END) as valor_total
FROM profiles p
LEFT JOIN proposals pr ON pr.technician_id = p.id
WHERE p.user_id IN (
  SELECT user_id FROM user_roles WHERE role = 'technician'
)
GROUP BY p.id, p.full_name
ORDER BY valor_total DESC;

Comissões por Período​

SELECT
  DATE_TRUNC('month', c.created_at) as mes,
  COUNT(*) as total_comissoes,
  SUM(c.amount) as valor_total,
  AVG(c.amount) as valor_medio
FROM commissions c
WHERE c.created_at >= '2025-01-01'
GROUP BY DATE_TRUNC('month', c.created_at)
ORDER BY mes;