Skip to content

Esquema Completo do Banco D1 & Migrações SQL

Esquema Completo do Banco D1 & Migrações SQL

Section titled “Esquema Completo do Banco D1 & Migrações SQL”

O Cloudflare D1 (SQLite de borda) armazena todos os dados persistentes da aplicação. A definição das tabelas é mantida em sincronia entre três arquivos de verdade:

  1. db/migrations/NNNN_*.sql (Migrações delta de produção)
  2. db/schema.sql (Baseline para ambiente local)
  3. functions/api/db/schema.ts (Mapeamento de tipos no Drizzle ORM)

🗄️ 1. Diagrama de Entidade e Relacionamento Completo (ERD)

Section titled “🗄️ 1. Diagrama de Entidade e Relacionamento Completo (ERD)”
erDiagram
    clients ||--o{ service_requests : "possui agendamentos"
    clients ||--o{ invoices : "possui faturas"
    clients ||--o{ chat_channels : "possui canais"
    
    employees ||--o{ service_requests : "executa serviços"
    employees ||--o{ payroll_entries : "recebe repasses"
    clients ||--o{ key_tags : "possui chaves"
    key_tags ||--o{ key_custody_records : "gera auditoria"

    service_requests ||--o{ invoices : "origina fatura"
    services ||--o{ service_requests : "define tipo de serviço"
    
    chat_channels ||--o{ chat_messages : "contém mensagens"
    chat_channels ||--o{ channel_participants : "tem participantes"

    leads ||--o{ chat_channels : "gera conversa"
    leads ||--o{ lead_quotes : "solicita cotação"
    lead_quotes }o--|| services : "usa tipo de serviço"
    lead_quotes }o--o| service_requests : "materializa ao confirmar"
    auth_accounts }o--|| leads : "reference_type LEAD"
    auth_accounts }o--|| clients : "reference_type CLIENT"

  • Chave Primária: id (TEXT, UUID/String ID)
  • Campos: name, email, phone, address, postcode, preferred_language, created_at.
  • Chave Primária: id (TEXT)
  • Campos: name, email, phone, hourly_rate, role, status, created_at.
  • date_of_birth (0149, também em clients): opcional, data civil YYYY-MM-DD (nunca instante). Editada no perfil da equipe e do cliente; o faxineiro não recebe a do cliente (redactClientForViewer). As listas mostram o aniversário e destacam os dos próximos 7 dias (src/shared/birthday.ts, “hoje” é o de Londres; 29/02 cai em 28/02 nos anos comuns). Payload sem o campo mantém o valor; vazio apaga.
  • Chave Primária: id (TEXT)
  • Campos: client_id, service_id, employee_id, status, scheduled_date, total_price, telemetry_json, created_at.
  • Chave Primária: id (TEXT)
  • Campos: channel_id, sender_id, sender_role, content, translations_json, reactions_json, created_at.
  • Chave Primária: id (TEXT)
  • Campos: endpoint, client_key (marcador SHA-256; IP nunca persistido em claro) e attempted_at.
  • Índice: idx_public_api_attempts_scope(endpoint, client_key, attempted_at) atende contagem e limpeza das janelas de rate limiting de chat-initiate.
  • key_tags é o inventário físico por cliente, incluindo tag, localização segura, posse atual, estado e version para concorrência otimista.
  • key_custody_records é a trilha imutável de cadastro, retirada e devolução, com snapshots do responsável e identidade/papel do operador.
  • Os triggers key_tags_audit_insert e key_tags_audit_update criam o evento no mesmo statement SQLite da mudança. Assim uma transferência não pode persistir sem auditoria correspondente.
  • Índices por cliente, estado e chave/horário atendem o painel operacional sem incluir esses dados sensíveis no /api/boot geral.
  • Guarda uma intenção idempotente de pausa por cliente e intervalo, o operador, a lista final de visitas e o estado PENDING/COMPLETED.
  • service_requests.holiday_suspension_id relaciona cada visita afetada ao resultado retomável e possui índice próprio.
  • A atualização de todas as visitas elegíveis é um único statement; o registro PENDING permite retomar com segurança caso a resposta se perca antes da consolidação do envelope.

H. Tabelas lead_quotes e whatsapp_access_requests

Section titled “H. Tabelas lead_quotes e whatsapp_access_requests”
  • lead_quotes pertence a leads e guarda serviço, horas, endereço, preferência, frequência, detalhes, extras, valor estimado e estado OPEN/CONFIRMED/DECLINED. confirmed_request_id só é preenchido quando a confirmação materializa o atendimento.
  • A aplicação mantém no máximo uma cotação aberta por lead por regra de serviço; reenvio atualiza essa linha e preserva decisões anteriores como histórico.
  • whatsapp_access_requests guarda o pedido de login, redefinição ou cadastro até o visitante escrever daquele número. O código curto PC-xxxx é opcional no texto; a posse vem do remetente inbound. Depois disso o sistema responde com magic link (ou link de redefinição).
  • auth_accounts.reference_type discrimina LEAD, CLIENT e EMPLOYEE; toda resolução de reference_id deve usar os dois campos. A confirmação da cotação reponta contas do lead para o cliente.
  • Um pagamento iniciado num gateway (Stripe, GoCardless) ou pelo crédito, e as faturas que ele liquida (invoice_ids_json). O gateway só conhece o id; o webhook assinado relê as faturas daqui.
  • Campos: provider, fiscal_entity_id, client_id, amount (com taxa), fee, status (PENDING/PAID/FAILED), provider_ref (sessão Stripe, billing request ou pagamento GoCardless), provider_payment_ref, settle_token, created_at, completed_at.
  • settle_token é gravado pela única execução que move a linha de PENDING; todas as escritas da liquidação são guardadas por ele, então reentrega do webhook não liquida duas vezes.
  • Índices: provider_ref e provider_payment_ref.

J. Tabelas QuickBooks (desde a migração 0136)

Section titled “J. Tabelas QuickBooks (desde a migração 0136)”
  • quickbooks_connections — uma linha por entidade fiscal conectada (PK fiscal_entity_id): realm_id (empresa QuickBooks), environment (sandbox/production), company_name, access_token_enc / refresh_token_enc (AES-GCM com GOOGLE_TOKEN_ENCRYPTION_KEY, ou JWT_SECRET), access_expires_at, refresh_expires_at, item_id (item de serviço), tax_code_standard_id (20% S), tax_code_exempt_id, connected_by, connected_at, last_sync_at, last_error.
  • quickbooks_customers — PK (fiscal_entity_id, client_id, realm_id) → qbo_customer_id. O mesmo cliente é um customer diferente em cada empresa; reconectar a mesma empresa reaproveita o mapa.
  • quickbooks_invoice_sync — PK invoice_id: fiscal_entity_id, realm_id, qbo_invoice_id, qbo_payment_id, status (SYNCED/VOIDED/EXCLUDED/FAILED), error, synced_at. Índice (fiscal_entity_id, status) para contar falhas por entidade. invoices.quickbooks_id continua sendo preenchido para as telas.
  • xero_connections — uma linha por entidade fiscal conectada (PK fiscal_entity_id): tenant_id/tenant_name (organização), tokens criptografados (access_token_enc, access_expires_at, refresh_token_enc), sales_account_code, bank_account_id/bank_account_name (onde entram os pagamentos), vat_ready (existe OUTPUT2), connected_by, connected_at, last_sync_at, last_error.
  • xero_contacts — PK (fiscal_entity_id, client_id, tenant_id) → xero_contact_id. Reconectar outra organização não reaproveita contatos.
  • xero_invoice_sync — PK invoice_id: fiscal_entity_id, tenant_id, xero_invoice_id, xero_payment_id, status (SYNCED/VOIDED/EXCLUDED/FAILED), error, synced_at.
  • series_visit_heads mantém a última visita por série para a janela do boot.
  • invoices.payment_request distingue pedidos de depósito de faturas. O recebimento aplicado fica vinculado à fatura da visita.
  • fiscal_turnover_months e integrity_audit_results guardam as apurações diárias lidas pelas telas.
  • accounting_inbound_events guarda os avisos e as leituras contábeis. accounting_unapplied_credits espelha valores ainda não aplicados no QuickBooks.
  • mail_app_passwords guarda hashes de senhas de app. imap_folder_state e imap_uids mantêm a identidade das mensagens por pasta. mail_drafts guarda os metadados dos rascunhos privados e mail_revision indica mudanças ao servidor de e-mail.