Esquema Completo do Banco D1 & Migrações SQL

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)

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"

---

📊 2. Estrutura das Tabelas Principais

A. Tabela clients

• Chave Primária: id (TEXT, UUID/String ID)
• Campos: name, email, phone, address, postcode, preferred_language, created_at.

B. Tabela employees

• Chave Primária: id (TEXT)
• Campos: name, email, phone, hourly_rate, role, status, created_at.

C. Tabela service_requests

• Chave Primária: id (TEXT)
• Campos: client_id, service_id, employee_id, status, scheduled_date, total_price, telemetry_json, created_at.

D. Tabela chat_messages

• Chave Primária: id (TEXT)
• Campos: channel_id, sender_id, sender_role, content, translations_json, reactions_json, created_at.

E. Tabela public_api_attempts

• 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 e check-number.

F. Tabelas key_tags e key_custody_records

• 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.

G. Tabela holiday_suspensions

• 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 phone_verifications

• 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.
• phone_verifications guarda somente o hash do código, expiração, contagem de tentativas e horário de criação. O código em claro não é persistido.
• 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.