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:
db/migrations/NNNN_*.sql(Migrações delta de produção)db/schema.sql(Baseline para ambiente local)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"
📊 2. Estrutura das Tabelas Principais
Section titled “📊 2. Estrutura das Tabelas Principais”A. Tabela clients
Section titled “A. Tabela clients”- Chave Primária:
id(TEXT, UUID/String ID) - Campos:
name,email,phone,address,postcode,preferred_language,created_at.
B. Tabela employees
Section titled “B. Tabela employees”- Chave Primária:
id(TEXT) - Campos:
name,email,phone,hourly_rate,role,status,created_at. date_of_birth(0149, também emclients): opcional, data civilYYYY-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.
C. Tabela service_requests
Section titled “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
Section titled “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
Section titled “E. Tabela public_api_attempts”- Chave Primária:
id(TEXT) - Campos:
endpoint,client_key(marcador SHA-256; IP nunca persistido em claro) eattempted_at. - Índice:
idx_public_api_attempts_scope(endpoint, client_key, attempted_at)atende contagem e limpeza das janelas de rate limiting dechat-initiate.
F. Tabelas key_tags e key_custody_records
Section titled “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 eversionpara 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_insertekey_tags_audit_updatecriam 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/bootgeral.
G. Tabela holiday_suspensions
Section titled “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_idrelaciona 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
PENDINGpermite 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_quotespertence aleadse guarda serviço, horas, endereço, preferência, frequência, detalhes, extras, valor estimado e estadoOPEN/CONFIRMED/DECLINED.confirmed_request_idsó é 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_requestsguarda o pedido de login, redefinição ou cadastro até o visitante escrever daquele número. O código curtoPC-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_typediscriminaLEAD,CLIENTeEMPLOYEE; toda resolução dereference_iddeve usar os dois campos. A confirmação da cotação reponta contas do lead para o cliente.
I. Tabela payment_checkouts (0135)
Section titled “I. Tabela payment_checkouts (0135)”- Um pagamento iniciado num gateway (Stripe, GoCardless) ou pelo crédito, e as faturas que ele liquida (
invoice_ids_json). O gateway só conhece oid; 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 dePENDING; todas as escritas da liquidação são guardadas por ele, então reentrega do webhook não liquida duas vezes.- Índices:
provider_refeprovider_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 (PKfiscal_entity_id):realm_id(empresa QuickBooks),environment(sandbox/production),company_name,access_token_enc/refresh_token_enc(AES-GCM comGOOGLE_TOKEN_ENCRYPTION_KEY, ouJWT_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— PKinvoice_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_idcontinua sendo preenchido para as telas.
J2. Tabelas Xero (0177)
Section titled “J2. Tabelas Xero (0177)”xero_connections— uma linha por entidade fiscal conectada (PKfiscal_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(existeOUTPUT2),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— PKinvoice_id:fiscal_entity_id,tenant_id,xero_invoice_id,xero_payment_id,status(SYNCED/VOIDED/EXCLUDED/FAILED),error,synced_at.
K. Estruturas operacionais recentes
Section titled “K. Estruturas operacionais recentes”series_visit_headsmantém a última visita por série para a janela do boot.invoices.payment_requestdistingue pedidos de depósito de faturas. O recebimento aplicado fica vinculado à fatura da visita.fiscal_turnover_monthseintegrity_audit_resultsguardam as apurações diárias lidas pelas telas.accounting_inbound_eventsguarda os avisos e as leituras contábeis.accounting_unapplied_creditsespelha valores ainda não aplicados no QuickBooks.mail_app_passwordsguarda hashes de senhas de app.imap_folder_stateeimap_uidsmantêm a identidade das mensagens por pasta.mail_draftsguarda os metadados dos rascunhos privados email_revisionindica mudanças ao servidor de e-mail.