Schema do Banco de Dados

Schema completo do PostgreSQL (Supabase), incluindo tabelas, índices, RLS policies, funções e triggers.

Visão Geral das Tabelas

auth.users (gerenciado pelo Supabase Auth)
    │
    ├── profiles (1:1 via trigger)
    │       ├── pricing_config (updated_by → profiles)
    │       ├── deliveries (driver_id, created_by → profiles)
    │       │       └── driver_locations (delivery_id → deliveries)
    │       ├── wallets (1:1 via trigger)
    │       │       └── transactions (driver_id → profiles)
    │       ├── withdrawals (driver_id → profiles)
    │       └── push_subscriptions (driver_id → profiles)
    └── driver_locations (driver_id → profiles)

Tabela: `profiles`

Perfil de usuário — espelha auth.users com dados extras.

CREATE TABLE IF NOT EXISTS public.profiles (
  id           uuid PRIMARY KEY REFERENCES auth.users ON DELETE CASCADE,
  role         text NOT NULL CHECK (role IN ('admin', 'driver')),
  full_name    text NOT NULL,
  phone        text,
  avatar_url   text,
  is_active    boolean NOT NULL DEFAULT true,
  created_at   timestamptz NOT NULL DEFAULT now()
);

Coluna	Tipo	Descrição
`id`	uuid (PK)	Mesmo UUID do `auth.users`
`role`	text	`'admin'` ou `'driver'`
`full_name`	text	Nome completo
`phone`	text	Telefone (opcional)
`avatar_url`	text	URL do avatar (opcional)
`is_active`	boolean	Se o usuário pode operar
`created_at`	timestamptz	Data de criação

RLS Policies:

-- Usuário vê/edita apenas seu próprio perfil
CREATE POLICY "profiles_own" ON public.profiles
  FOR ALL USING (auth.uid() = id);

-- Admin pode ler todos os perfis
CREATE POLICY "admin_read_profiles" ON public.profiles
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.profiles p WHERE p.id = auth.uid() AND p.role = 'admin')
  );

Tabela: `pricing_config`

Configuração de preço vigente. Cada PUT /pricing insere uma nova linha (histórico).

CREATE TABLE IF NOT EXISTS public.pricing_config (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  base_fare     numeric(10,2) NOT NULL CHECK (base_fare > 0),
  price_per_km  numeric(10,2) NOT NULL CHECK (price_per_km > 0),
  min_km        numeric(5,2)  NOT NULL CHECK (min_km > 0),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  updated_by    uuid REFERENCES public.profiles
);

Coluna	Tipo	Descrição
`id`	uuid (PK)	Gerado automaticamente
`base_fare`	numeric(10,2)	Tarifa base (inclui `min_km`)
`price_per_km`	numeric(10,2)	Valor por km acima do mínimo
`min_km`	numeric(5,2)	Quilômetros incluídos no base_fare
`updated_at`	timestamptz	Timestamp da configuração
`updated_by`	uuid (FK)	Admin que fez a alteração

Configuração padrão inserida no schema:

INSERT INTO public.pricing_config (base_fare, price_per_km, min_km)
VALUES (15.00, 2.00, 5.00);

RLS Policies:

CREATE POLICY "admin_all_pricing" ON public.pricing_config
  FOR ALL USING (
    EXISTS (SELECT 1 FROM public.profiles p WHERE p.id = auth.uid() AND p.role = 'admin')
  );

CREATE POLICY "driver_read_pricing" ON public.pricing_config
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.profiles p WHERE p.id = auth.uid() AND p.role = 'driver')
  );

Tabela: `deliveries`

Entrega com todas as informações de rota, preço e status.

CREATE TABLE IF NOT EXISTS public.deliveries (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title               text NOT NULL,
  description         text,
  origin_address      text NOT NULL,
  origin_lat          numeric(10,7) NOT NULL,
  origin_lng          numeric(10,7) NOT NULL,
  destination_address text NOT NULL,
  destination_lat     numeric(10,7) NOT NULL,
  destination_lng     numeric(10,7) NOT NULL,
  distance_km         numeric(8,2),
  route_polyline      text,
  base_fare           numeric(10,2) NOT NULL,
  price_per_km        numeric(10,2) NOT NULL,
  min_km              numeric(5,2)  NOT NULL,
  driver_earning      numeric(10,2) NOT NULL,
  client_value        numeric(10,2),
  company_profit      numeric(10,2),
  status              text NOT NULL DEFAULT 'pending'
                      CHECK (status IN ('pending','assigned','in_progress','completed','cancelled')),
  driver_id           uuid REFERENCES public.profiles,
  created_by          uuid NOT NULL REFERENCES public.profiles,
  created_at          timestamptz NOT NULL DEFAULT now(),
  wait_since          timestamptz NOT NULL DEFAULT now(),
  assigned_at         timestamptz,
  started_at          timestamptz,
  completed_at        timestamptz,
  notes               text,
  asaas_transfer_id   text
);

Coluna	Tipo	Descrição
`id`	uuid (PK)	Identificador único
`title`	text	Título da entrega
`origin_*`	text/numeric	Endereço e coordenadas de origem
`destination_*`	text/numeric	Endereço e coordenadas de destino
`distance_km`	numeric(8,2)	Distância calculada pelo ORS
`route_polyline`	text	Geometria encodada da rota
`base_fare`	numeric(10,2)	Tarifa base no momento da criação
`price_per_km`	numeric(10,2)	Preço/km no momento da criação
`min_km`	numeric(5,2)	Mínimo km no momento da criação
`driver_earning`	numeric(10,2)	Ganho calculado do motorista
`client_value`	numeric(10,2)	Valor cobrado do cliente (opcional)
`company_profit`	numeric(10,2)	`client_value - driver_earning`
`status`	text	Estado atual da entrega
`driver_id`	uuid (FK)	Motorista responsável (null se pending)
`created_by`	uuid (FK)	Admin que criou
`wait_since`	timestamptz	Início da espera na fila
`assigned_at`	timestamptz	Quando motorista aceitou
`started_at`	timestamptz	Quando coleta iniciou
`completed_at`	timestamptz	Quando entrega foi concluída
`asaas_transfer_id`	text	ID da transferência ASAAS

RLS Policies:

-- Driver pode ver pending (disponíveis) ou suas próprias entregas
CREATE POLICY "driver_read_deliveries" ON public.deliveries
  FOR SELECT USING (status = 'pending' OR driver_id = auth.uid());

-- Admin tem acesso total
CREATE POLICY "admin_all_deliveries" ON public.deliveries
  FOR ALL USING (
    EXISTS (SELECT 1 FROM public.profiles p WHERE p.id = auth.uid() AND p.role = 'admin')
  );

Tabela: `driver_locations`

Localização GPS atual de cada motorista (upsert a cada 10 segundos).

CREATE TABLE IF NOT EXISTS public.driver_locations (
  driver_id     uuid PRIMARY KEY REFERENCES public.profiles ON DELETE CASCADE,
  lat           numeric(10,7) NOT NULL,
  lng           numeric(10,7) NOT NULL,
  heading       numeric(5,2),
  speed         numeric(6,2),
  accuracy      numeric(6,2),
  delivery_id   uuid REFERENCES public.deliveries,
  is_online     boolean NOT NULL DEFAULT false,
  updated_at    timestamptz NOT NULL DEFAULT now()
);

Coluna	Tipo	Descrição
`driver_id`	uuid (PK/FK)	1 linha por motorista
`lat`, `lng`	numeric(10,7)	Coordenadas GPS (7 casas ≈ 11mm precisão)
`heading`	numeric(5,2)	Direção em graus (0-360)
`speed`	numeric(6,2)	Velocidade em km/h
`accuracy`	numeric(6,2)	Precisão em metros
`delivery_id`	uuid (FK)	Entrega atual (se houver)
`is_online`	boolean	Se o motorista está ativo
`updated_at`	timestamptz	Última atualização GPS

RLS Policies:

-- Driver gerencia sua própria localização
CREATE POLICY "driver_write_location" ON public.driver_locations
  FOR ALL USING (driver_id = auth.uid());

-- Admin lê todos os drivers online
CREATE POLICY "admin_read_locations" ON public.driver_locations
  FOR SELECT USING (
    EXISTS (SELECT 1 FROM public.profiles p WHERE p.id = auth.uid() AND p.role = 'admin')
  );

Tabela: `wallets`

Carteira financeira de cada motorista (1:1 com profiles).

CREATE TABLE IF NOT EXISTS public.wallets (
  driver_id         uuid PRIMARY KEY REFERENCES public.profiles ON DELETE CASCADE,
  balance           numeric(12,2) NOT NULL DEFAULT 0 CHECK (balance >= 0),
  asaas_account_id  text,
  asaas_wallet_id   text,
  updated_at        timestamptz NOT NULL DEFAULT now()
);

Coluna	Tipo	Descrição
`driver_id`	uuid (PK/FK)	1 carteira por motorista
`balance`	numeric(12,2)	Saldo atual (não pode ser negativo)
`asaas_account_id`	text	ID da subconta ASAAS
`asaas_wallet_id`	text	ID da wallet ASAAS para transferência

RLS Policy:

CREATE POLICY "driver_read_wallet" ON public.wallets
  FOR SELECT USING (driver_id = auth.uid());

Tabela: `transactions`

Histórico de todas as movimentações financeiras.

CREATE TABLE IF NOT EXISTS public.transactions (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  driver_id         uuid NOT NULL REFERENCES public.profiles,
  delivery_id       uuid REFERENCES public.deliveries,
  type              text NOT NULL CHECK (type IN ('earning','withdrawal','adjustment')),
  amount            numeric(12,2) NOT NULL CHECK (amount > 0),
  direction         text NOT NULL CHECK (direction IN ('credit','debit')),
  status            text NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending','completed','failed')),
  asaas_transfer_id text,
  description       text,
  created_at        timestamptz NOT NULL DEFAULT now()
);

Tipo (`type`)	Direção (`direction`)	Descrição
`earning`	`credit`	Ganho por entrega completada
`withdrawal`	`debit`	Saque via Pix
`adjustment`	`credit`/`debit`	Ajuste manual pelo admin

RLS Policy:

CREATE POLICY "driver_read_transactions" ON public.transactions
  FOR SELECT USING (driver_id = auth.uid());

Tabela: `withdrawals`

Solicitações de saque via Pix.

CREATE TABLE IF NOT EXISTS public.withdrawals (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  driver_id         uuid NOT NULL REFERENCES public.profiles,
  amount            numeric(12,2) NOT NULL CHECK (amount > 0),
  pix_key           text NOT NULL,
  pix_key_type      text NOT NULL CHECK (pix_key_type IN ('CPF','CNPJ','EMAIL','PHONE','EVP')),
  status            text NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending','processing','completed','failed')),
  asaas_transfer_id text,
  requested_at      timestamptz NOT NULL DEFAULT now(),
  completed_at      timestamptz,
  error_message     text
);

Status	Descrição
`pending`	Criado, aguardando processamento
`processing`	Chamada ASAAS feita, aguardando confirmação
`completed`	Pix enviado e confirmado via webhook
`failed`	Falha na transferência ASAAS

RLS Policies:

CREATE POLICY "driver_read_withdrawals" ON public.withdrawals
  FOR SELECT USING (driver_id = auth.uid());

CREATE POLICY "driver_create_withdrawal" ON public.withdrawals
  FOR INSERT WITH CHECK (driver_id = auth.uid());

Tabela: `push_subscriptions`

Assinaturas de Web Push para notificações aos drivers.

CREATE TABLE IF NOT EXISTS public.push_subscriptions (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  driver_id   uuid NOT NULL REFERENCES public.profiles ON DELETE CASCADE,
  endpoint    text NOT NULL UNIQUE,
  p256dh      text NOT NULL,
  auth        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

RLS Policy:

CREATE POLICY "driver_manage_push" ON public.push_subscriptions
  FOR ALL USING (driver_id = auth.uid());

Índices

CREATE INDEX idx_deliveries_status      ON public.deliveries(status);
CREATE INDEX idx_deliveries_driver      ON public.deliveries(driver_id);
CREATE INDEX idx_deliveries_created     ON public.deliveries(created_at DESC);
CREATE INDEX idx_deliveries_wait        ON public.deliveries(wait_since) WHERE status = 'pending';
CREATE INDEX idx_transactions_driver    ON public.transactions(driver_id, created_at DESC);
CREATE INDEX idx_withdrawals_driver     ON public.withdrawals(driver_id, requested_at DESC);
CREATE INDEX idx_withdrawals_asaas      ON public.withdrawals(asaas_transfer_id) WHERE asaas_transfer_id IS NOT NULL;
CREATE INDEX idx_driver_locations_online ON public.driver_locations(is_online) WHERE is_online = true;
CREATE INDEX idx_push_subs_driver       ON public.push_subscriptions(driver_id);

Os índices parciais (WHERE status = 'pending' e WHERE is_online = true) são especialmente eficientes pois indexam apenas as linhas relevantes.

Funções PostgreSQL

`increment_wallet_balance(p_driver_id, p_amount)`

Chamada pelo backend via supabase.rpc() ao completar uma entrega.

CREATE OR REPLACE FUNCTION public.increment_wallet_balance(
  p_driver_id uuid,
  p_amount    numeric
) RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  UPDATE public.wallets
  SET balance = balance + p_amount,
      updated_at = now()
  WHERE driver_id = p_driver_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Wallet not found for driver %', p_driver_id;
  END IF;
END;
$$;

`decrement_wallet_balance(p_driver_id, p_amount)`

Usada para saques confirmados (proteção contra saldo negativo embutida).

CREATE OR REPLACE FUNCTION public.decrement_wallet_balance(
  p_driver_id uuid,
  p_amount    numeric
) RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  UPDATE public.wallets
  SET balance = balance - p_amount,
      updated_at = now()
  WHERE driver_id = p_driver_id AND balance >= p_amount;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Insufficient balance or wallet not found for driver %', p_driver_id;
  END IF;
END;
$$;

Triggers

`on_auth_user_created` → `handle_new_user()`

Cria automaticamente o registro em profiles ao cadastrar usuário no Supabase Auth.

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  INSERT INTO public.profiles (id, role, full_name, phone)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'role', 'driver'),
    COALESCE(NEW.raw_user_meta_data->>'full_name', ''),
    NEW.raw_user_meta_data->>'phone'
  );
  RETURN NEW;
END;
$$;

`on_profile_created` → `create_driver_wallet()`

Cria automaticamente a carteira ao criar um profile com role = 'driver'.

CREATE OR REPLACE FUNCTION public.create_driver_wallet()
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  IF NEW.role = 'driver' THEN
    INSERT INTO public.wallets(driver_id) VALUES (NEW.id)
    ON CONFLICT (driver_id) DO NOTHING;
  END IF;
  RETURN NEW;
END;
$$;

Supabase Realtime

Tabelas com Realtime habilitado no dashboard (Database > Replication):

Tabela	Eventos	Consumidor
`driver_locations`	UPDATE	Admin map (GPS markers)
`deliveries`	INSERT, UPDATE	Driver app (fila de entregas)
`wallets`	UPDATE	Driver app (saldo)

Schema do Banco de Dados

Visão Geral das Tabelas

Tabela: profiles

Tabela: pricing_config

Tabela: deliveries

Tabela: driver_locations

Tabela: wallets

Tabela: transactions

Tabela: withdrawals

Tabela: push_subscriptions

Índices

Funções PostgreSQL

increment_wallet_balance(p_driver_id, p_amount)

decrement_wallet_balance(p_driver_id, p_amount)

Triggers

on_auth_user_created → handle_new_user()

on_profile_created → create_driver_wallet()

Supabase Realtime

Tabela: `profiles`

Tabela: `pricing_config`

Tabela: `deliveries`

Tabela: `driver_locations`

Tabela: `wallets`

Tabela: `transactions`

Tabela: `withdrawals`

Tabela: `push_subscriptions`

`increment_wallet_balance(p_driver_id, p_amount)`

`decrement_wallet_balance(p_driver_id, p_amount)`

`on_auth_user_created` → `handle_new_user()`

`on_profile_created` → `create_driver_wallet()`