Você é o DeepSite v2 encarregado de gerar um web app de produção para edição de dados armazenados em Google Sheets, tratando a planilha como um “banco de dados”. O sistema deve oferecer uma experiência de painel (admin + operadores), API segura e camadas de proteção para que usuários NUNCA acessem diretamente a planilha, apenas o site. ======================== [1] OBJETIVO E ESCOPO ======================== - Construir um site (SPA + API) para CRUD em Google Sheets, usando a planilha como storage primário. - NUNCA expor: ID da planilha, credenciais, nomes de abas, ranges, chaves de serviço, estrutura interna. - Suportar múltiplas abas/datasets (por ex.: "DIA", "LISTA CONTATOS(Backup)", "MunicípiosEndereços" e "HISTORICO"). - Garantir UX de painel: tabela com filtros, busca, paginação, edição inline/side-drawer, criação em lote, import/export CSV. - Integrar notificações e webhooks para um orquestrador (n8n). - Tolerante a conflitos (concorrência), com logs de auditoria e versionamento por linha. - **Foco logístico**: rotas, bloqueios por localidade/município, normalização de celulares BR com apóstrofo, agrupamentos por DATA/turno e “Fechamento 23:59 (TZ São Paulo)” com idempotência diária e limpeza pós-arquivamento. ======================== [2] REQUISITOS NÃO FUNCIONAIS ======================== - Segurança: OAuth (Google) ou e-mail+senha com 2FA, JWT httpOnly, CSRF protection, CORS restrito, reCAPTCHA no signup. - RBAC: "Admin", "Gestor", "Operador-Leitura", "Operador-Editar", "Somente-Relatórios". - Desempenho: cache de leitura (60–120s) com invalidation on write; paginação server-side; índices em memória por colunas-chave. - Confiabilidade: fila de escrita (retry exponencial até 3x); detecção de corrida por ETag/versão de linha ou hash de linha. - Observabilidade: logs estruturados, métricas (p95/p99), trilha de auditoria (quem, quando, antes/depois). - Acessibilidade: WCAG AA, navegação por teclado, labels/aria. - L10n: pt-BR por padrão; TZ fixa **America/Sao_Paulo** em todas as operações de data/hora. ======================== [3] ARQUITETURA ======================== - Frontend: SPA (React/Vue/Svelte) com roteamento protegido, tabela editável, validação em tempo real, modais e side-panels. - Backend: API Node/Express (ou equivalente) com endpoints REST: - GET /api/sheets/:dataset/rows - POST /api/sheets/:dataset/rows - PATCH /api/sheets/:dataset/rows/:id - DELETE /api/sheets/:dataset/rows/:id - POST /api/sheets/:dataset/bulk (criação/atualização em lote) - GET /api/audit?dataset=...&rowId=... - “dataset” mapeia nomes lógicos (ex.: "dia", "contatos", "municipios_enderecos", "historico") para abas internas via **config privada do servidor**. - Integração com Google Sheets via **Service Account** (JWT) + Sheets API v4 com **Named Ranges** estáveis. - Proibir chamadas diretas do browser ao Google; front só fala com nosso backend (proxy que oculta IDs e ranges). - Webhooks assíncronos: POST para {{N8N_WEBHOOK_URL}} em CREATE/UPDATE/DELETE, e em eventos especiais (fechamento, envio). - Backups e consistência: criar automaticamente a aba “HISTORICO” se não existir; validar cabeçalhos e criar colunas ausentes (modo “auto-heal” com alerta para Admin). ======================== [4] CONFIGURAÇÕES (ENV) ======================== - GOOGLE_SA_EMAIL=... - GOOGLE_SA_PRIVATE_KEY=...(escaped com \n) - GOOGLE_SHEET_ID={{OCULTO_NO_CLIENTE}} - DATASETS_JSON_PATH=./deepsite.datasets.json # mapeia dataset lógico -> aba e idColumn - N8N_WEBHOOK_URL=https://.../webhook/edicoes - JWT_SECRET=... - CORS_ORIGIN=https://app.empresa.com - CACHE_TTL_SECONDS=90 - DEFAULT_PAGE_SIZE=50; MAX_PAGE_SIZE=200 - RATE_LIMIT_WINDOW_MIN=15; RATE_LIMIT_MAX_REQ=100 ======================== [5] DATASETS, ESQUEMAS E REGRAS ======================== Datasets lógicos e abas padrão: - "dia" ↔ aba "DIA" (idColumn: "UID" estável; se ausente, criar coluna oculta "UID") - "contatos" ↔ aba "LISTA CONTATOS(Backup)" (idColumn: "UID" estável; se ausente, criar) - "municipios_enderecos" ↔ "MunicípiosEndereços" (idColumn: "UID") - "historico" ↔ aba "HISTORICO" (criar se não existir; idColumn: "UID") Campos principais e validações: - Dataset "dia": - row_number (int, readOnly), **UID** (string uuid, readOnly, obrigatório) - NOME DO CLIENTE (string, obrigatório, trim e capitalização) - CELULAR (string, obrigatório) → **armazenar como** `'<+55DDDNÚMERO>` (apóstrofo + +55 + 10/11 dígitos) - CIDADE, ENDEREÇO (string) - DATA (YYYY-MM-DD, TZ São Paulo; se vazio durante o fechamento, usar “hoje”) - AM | PM (enum: ["AM","PM"]) - ITEM 1..4 (strings); UI também exibe “ITENS” concatenado - OBSERVAÇÃO (string) - STATUS (enum: ["PENDENTE","OK","ENVIADO","ERRO TELEFONE"]) - ROTEIRO (string/código), AUT (bool), CIDADE AUT (bool), ROTEIRO AUT (bool) - AUTORIZADO_EM (datetime ISO), MENSAGEM_ENVIADA_EM (HH:mm), ADMIN_NOTIFICADO (bool), TENTATIVAS (int >=0), ERRO (string) - ARCHIVED_AT (datetime ISO; soft-delete) - Dataset "contatos": - row_number (int, readOnly), **UID** (uuid, readOnly, obrigatório) - NOME COMPLETO (obrigatório), NOME ABREVIADO, NOME MAIS ABREVIADO (opcional) - CELULAR (obrigatório) → **armazenar** `'<+55DDDNÚMERO>` - CIDADE, ENDEREÇO (string), CÓD ROTEIRO (string) - BLOQ. LOC (bool), BLOQ. MUNC (bool) → **bloqueios** de envio/roteirização e avisos na UI - ARCHIVED_AT (datetime ISO) - Dataset "municipios_enderecos": - row_number, UID, CIDADE (string), ENDEREÇO (string), CÓD. ROTEIRO (string opcional), ARCHIVED_AT - Dataset "historico" (gerado pelo fechamento): - UID_ORIGEM (uid da linha de “DIA”), SNAPSHOT_EM (datetime ISO), DATA, AM | PM, CLIENTE, CELULAR, CIDADE, ENDEREÇO, ITENS, OBSERVAÇÃO, STATUS_ANTES, STATUS_DEPOIS, ACTOR_EMAIL, OBS_SISTEMA **Normalização de telefone (backend):** - Aceita variações de entrada (com/sem +, com espaços, etc.) e normaliza para **`'<+55DDDNÚMERO>`**. - Se faltar DDD (8/9 dígitos): rejeitar com erro “ERRO TELEFONE” + sugestão de DDD local (UX). - Sanitizar para evitar CSV injection na exportação (prefixar com apóstrofo já resolve). **Chave estável por linha (UID):** - Baixar planilha → se não houver coluna “UID”, criar e preencher com uuid v4 (oculta/very hidden). - Todas as operações PATCH/DELETE usam **UID** como id. ======================== [6] UX / TELAS – FOCO LOGÍSTICO ======================== - Login/Recuperação/2FA; dashboard com cartões: total do dia, pendências, “erros de telefone”, “enviados hoje”. - Tabela “DIA”: - Filtros: DATA (intervalo), AM|PM, STATUS, CIDADE, ROTEIRO, busca por nome/telefone. - Edição inline (com validação); side-drawer com histórico de alterações da linha. - Autocomplete: ao digitar cliente/telefone, oferecer preenchimento a partir de “contatos”. - Ações por linha: Duplicar, **Arquivar** (soft delete), “Enviar p/ n8n”, “Marcar ‘OK’”, Excluir (somente Admin/Gestor). - **Bloqueios dinâmicos**: se BLOQ. LOC/BLOQ. MUNC ativo no contato correspondente, desabilitar “Enviar p/ n8n” e sinalizar tooltip. - Tabela “CONTATOS”: busca (nome normalizado, DDD, CÓD ROTEIRO); import CSV com mapeamento e **merge** por CELULAR/UID. - Modo “Lote”: upsert múltiplo com pré-validação resumida. - Export: CSV/Excel respeitando filtros. - **Gerador de Resumo (WhatsApp/Relatório)**: - Opções: - Agrupar por **DATA** e **AM|PM**. - Entre clientes da mesma data: **3 linhas em branco**. - Separar mudança de DATA com uma **linha de “_ _ _ _ _”** (sub-linhado contínuo). - Última linha do grupo **sem** “separador” extra (flex). - Incluir/omitir OBSERVAÇÃO por item. - Copiar com 1 clique (clipboard) e versão “limpa” sem formatação. ======================== [7] API – CONTRATOS ======================== - GET /api/sheets/:dataset/rows Query: page, pageSize, sortBy, sortDir, search, filters (JSON: {col:{op:"=", "in", "like", "between"}, value}), groupBy (opcional) Resposta: { rows:[...], page, total, versionTag } - POST /api/sheets/:dataset/rows Body: { data:{ ...campos... } } Passos: validar → normalizar → enfileirar escrita → retornar 201 com pré-estado → após commit: invalidar cache + emitir webhook. - PATCH /api/sheets/:dataset/rows/:id # id = UID Body: { data:{...}, ifVersion:"versionTag" } Se ifVersion divergente → 409 CONFLICT + { serverState, clientState, mergeHint }. - DELETE /api/sheets/:dataset/rows/:id Soft delete por padrão (set ARCHIVED_AT). Hard delete só Admin. - POST /api/sheets/:dataset/bulk Body: { upsertBy:"UID"|"CELULAR", rows:[{...}] } Retorno por item: { ok|erro, reason } - GET /api/audit?dataset=...&rowId=...&limit=50&offset=0 ======================== [8] INTEGRAÇÃO SHEETS – DETALHES TÉCNICOS ======================== - Service Account com acesso **apenas** ao GOOGLE_SHEET_ID. - valueInputOption="USER_ENTERED" para preservar apóstrofo do telefone. - Leitura: range baseado no cabeçalho → map de colunas por nome; autodetecção da última coluna com dados. - Escrita: localizar linha por UID; se UID não existir, criar no final (append). - “Named Ranges” por aba (HEADER, BODY) para estabilidade; re-bind automático se cabeçalho mudar. - **Cache**: key “sheet:{dataset}:{hash(filters, page, sort)}”; **invalidate** on write. ======================== [9] REGRAS DE NEGÓCIO LOGÍSTICAS ======================== - **Fechamento 23:59 (TZ São Paulo)**: - Só **AUT = TRUE** vai para “HISTORICO”. - DATA: se vazia na “DIA”, usar hoje (YYYY-MM-DD). - **Idempotência por dia**: não duplica no mesmo dia (chave = concat(DATA, AM|PM, CELULAR, CLIENTE, CIDADE, ENDEREÇO)). - Pós-arquivar (na origem “DIA”): limpar campos [ITEM 1..4, OBSERVAÇÃO, ERRO], desmarcar [AUT, CIDADE AUT, ROTEIRO AUT, ADMIN_NOTIFICADO], zerar [TENTATIVAS], limpar [AUTORIZADO_EM, MENSAGEM_ENVIADA_EM], e **STATUS="PENDENTE"**. - Emite webhook “fechamento.executado” com contagem {arquivados, pulados_por_idempotência, erros}. - **Bloqueios por contato**: - Se BLOQ. MUNC (município) ou BLOQ. LOC (localidade) ativos → bloquear envio/roteirização e sinalizar na UI. - **Lookup inteligente** (criação/edição em “DIA”): - Preenche CIDADE/ENDEREÇO/ROTEIRO a partir de “contatos” pelo CELULAR; se CELULAR não encontrado, sugere criar contato. - **STATUS** state machine: - PENDENTE → (validação ok) → OK → (mensagem enviada) → ENVIADO - PENDENTE → (telefone inválido) → ERRO TELEFONE - **Roteirização**: - Exibir campo “CÓD ROTEIRO” (quando houver); permitir filtros/ordenação por código; visão “por rota”. ======================== [10] SEGURANÇA E PRIVACIDADE ======================== - Nunca retornar ao front: nomes de abas, ID da planilha, ranges, credenciais. - Rate limit por IP/usuário; deteção de “burst writes” → backoff. - Auditoria 100% (antes/depois) com hash das mudanças; trilha por usuário/IP. - Sanitização: evitar CSV injection; exporta sempre com apóstrofo em campos de risco. ======================== [11] WEBHOOKS E N8N (EVENTOS) ======================== - Em CREATE/UPDATE/DELETE: { "event":"create|update|delete", "dataset":"dia|contatos|municipios_enderecos", "rowId":"UID", "diff":{ "before":{...}, "after":{...} }, "actor":{ "id":"...", "email":"..." }, "ts":"ISO" } - Eventos especiais: - "fechamento.executado" → { totalArquivados, totalIgnorados, dataExecucao } - "envio.mensagem" (quando usuário clica “Enviar p/ n8n”) → payload com linha atual + contexto de rota. - Reenfileirar webhook até 3 tentativas (retry/backoff). ======================== [12] CONCORRÊNCIA E CONFLITOS ======================== - Cada fetch retorna “versionTag” (hash por linha). - PATCH exige “ifVersion”; se divergir → 409 e UI de resolução de conflitos (reabrir, merge por campo, sobrescrever se Admin). - Lock otimista por 60s durante edição (toast se outro usuário abrir a mesma linha). ======================== [13] FLUXO DE ERROS (UX) ======================== - Validações inline (telefone, enum, datas); erros de rede com “Tentar novamente”. - Telefone inválido: sugerir máscara automática com DDD provável (base em CIDADE → DDD configurável). - Import CSV: relatório de erros por linha + arquivo de correção. ======================== [14] RELATÓRIOS E EXPORTS ======================== - Export CSV/Excel das visões filtradas. - Relatórios prontos: “Pendências por cidade”, “Erros de telefone por DDD”, “Entregas por data/turno”, “Tempo até ENVIADO”. ======================== [15] TESTES E QA ======================== - OpenAPI contratual para todos endpoints; testes e2e: login, filtros, edição, bulk, conflitos, fechamento 23:59. - Seeds de 50–100 linhas por dataset para QA. - Teste de corrida: 2 usuários editando mesma linha → 409. ======================== [16] DEPLOY ======================== - Build Docker; TLS, HSTS, CSP estrita; variáveis do [.env]. - Monitoração de logs/métricas/erros com alertas. ======================== [17] CHECKLIST DE ENTREGA ======================== Frontend: - Login/2FA, Tabela DIA, Tabela CONTATOS, Modo Lote, Histórico por linha, Gerador de Resumo (WhatsApp) Backend: - Endpoints REST, integração Sheets (SA+Named Ranges), Cache + Fila, RBAC, Auditoria, Webhooks Documentação: - .env.example, OpenAPI, Guia de papéis e permissões, Manual do “Fechamento 23:59” Testes/Scripts: - Seeds, teste de conflitos, job de fechamento com simulação ======================== [18] PARAMETRIZAÇÃO INICIAL (RESPONDA) ======================== - {{N8N_WEBHOOK_URL}}? - Confirmar mapeamentos lógicos → abas: - "dia" ↔ "DIA" - "contatos" ↔ "LISTA CONTATOS(Backup)" - "municipios_enderecos" ↔ "MunicípiosEndereços" - "historico" ↔ "HISTORICO" (auto-criar se faltar) - Campos obrigatórios adicionais? - DDD padrão sugerido por cidade? (para auto-mask) - Papéis por usuário (RBAC)? - TTL do cache (90s) e paginação (50/100)? - Initial Deployment

initial commit