120 lines
4.7 KiB
SQL
120 lines
4.7 KiB
SQL
-- Migration manual: construtos que o Prisma DSL não expressa nativamente.
|
|
-- Fonte de verdade: Etapas/seconectai-vendas-schema.sql
|
|
-- Contém: extensão pgcrypto, CHECK constraint, índices parciais, views de
|
|
-- dashboard e Row Level Security (ENABLE + CREATE POLICY) para isolamento
|
|
-- entre tenants.
|
|
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
-- ============================================================
|
|
-- CHECK constraints
|
|
-- ============================================================
|
|
|
|
ALTER TABLE "tenants"
|
|
ADD CONSTRAINT "tenants_dia_vencimento_mensalidade_check"
|
|
CHECK ("dia_vencimento_mensalidade" BETWEEN 1 AND 28);
|
|
|
|
-- ============================================================
|
|
-- Índices parciais
|
|
-- ============================================================
|
|
|
|
CREATE INDEX "idx_tenant_faturas_venc"
|
|
ON "tenant_faturas"("vencimento")
|
|
WHERE "status" IN ('pendente', 'atrasado');
|
|
|
|
CREATE INDEX "idx_sale_payments_venc"
|
|
ON "sale_payments"("vencimento")
|
|
WHERE "status" IN ('pendente', 'atrasado');
|
|
|
|
-- ============================================================
|
|
-- Views de dashboard (painel do tenant)
|
|
-- ============================================================
|
|
|
|
CREATE VIEW "vw_resumo_vendas_semanal" AS
|
|
SELECT
|
|
s.tenant_id,
|
|
date_trunc('week', s.data_venda)::date AS semana,
|
|
COUNT(*) AS qtd_vendas,
|
|
SUM(s.valor_total) AS total_vendido
|
|
FROM sales s
|
|
WHERE s.status IN ('confirmada', 'concluida')
|
|
GROUP BY s.tenant_id, date_trunc('week', s.data_venda);
|
|
|
|
CREATE VIEW "vw_resumo_vendas_mensal" AS
|
|
SELECT
|
|
s.tenant_id,
|
|
date_trunc('month', s.data_venda)::date AS mes,
|
|
COUNT(*) AS qtd_vendas,
|
|
SUM(s.valor_total) AS total_vendido
|
|
FROM sales s
|
|
WHERE s.status IN ('confirmada', 'concluida')
|
|
GROUP BY s.tenant_id, date_trunc('month', s.data_venda);
|
|
|
|
CREATE VIEW "vw_recebimentos" AS
|
|
SELECT
|
|
sp.id,
|
|
s.tenant_id,
|
|
s.customer_id,
|
|
sp.valor,
|
|
sp.vencimento,
|
|
sp.status,
|
|
sp.pago_em
|
|
FROM sale_payments sp
|
|
JOIN sales s ON s.id = sp.sale_id;
|
|
|
|
-- ============================================================
|
|
-- Row Level Security — isolamento entre tenants
|
|
-- ============================================================
|
|
-- A aplicação deve definir, por conexão/transação:
|
|
-- SET app.current_tenant_id = '<uuid-do-tenant-logado>';
|
|
-- Usuários master (que enxergam todos os tenants) usam uma role de banco
|
|
-- separada (ex: app_master) que faz BYPASS RLS, ou uma policy adicional
|
|
-- que libera quando app.is_master = 'true'. Implementado na Etapa 2.
|
|
|
|
ALTER TABLE "customers" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "customer_contacts" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "products_services" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "sales" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "sale_items" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "sale_payments" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "tenant_users" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "tenant_whatsapp_instances" ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE "tenant_email_config" ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "tenant_isolation_customers" ON "customers"
|
|
USING ("tenant_id" = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY "tenant_isolation_products" ON "products_services"
|
|
USING ("tenant_id" = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY "tenant_isolation_sales" ON "sales"
|
|
USING ("tenant_id" = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY "tenant_isolation_tenant_users" ON "tenant_users"
|
|
USING ("tenant_id" = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY "tenant_isolation_whatsapp" ON "tenant_whatsapp_instances"
|
|
USING ("tenant_id" = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY "tenant_isolation_email_config" ON "tenant_email_config"
|
|
USING ("tenant_id" = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
-- sale_items e customer_contacts não têm tenant_id direto: isolar via join
|
|
CREATE POLICY "tenant_isolation_sale_items" ON "sale_items"
|
|
USING ("sale_id" IN (
|
|
SELECT id FROM sales
|
|
WHERE tenant_id = current_setting('app.current_tenant_id', true)::uuid
|
|
));
|
|
|
|
CREATE POLICY "tenant_isolation_sale_payments" ON "sale_payments"
|
|
USING ("sale_id" IN (
|
|
SELECT id FROM sales
|
|
WHERE tenant_id = current_setting('app.current_tenant_id', true)::uuid
|
|
));
|
|
|
|
CREATE POLICY "tenant_isolation_customer_contacts" ON "customer_contacts"
|
|
USING ("customer_id" IN (
|
|
SELECT id FROM customers
|
|
WHERE tenant_id = current_setting('app.current_tenant_id', true)::uuid
|
|
));
|