Modelagem de Dados com casos especiais: histórico, temporalidade e auditoria

Por que “casos especiais” exigem modelagem temporal

Em muitos domínios, não basta armazenar apenas o valor atual de um dado. É comum precisar responder perguntas como: “qual era o status naquele dia?”, “quem alterou e quando?”, “qual preço estava vigente no momento da venda?”. Esses cenários exigem modelagem para mudanças ao longo do tempo, normalmente combinando três necessidades:

• Temporalidade (vigência): o dado tem um período em que é válido (início/fim).
• Histórico (linha do tempo): manter versões anteriores para consulta e relatórios.
• Auditoria: registrar metadados de criação/alteração e, às vezes, o detalhe do que mudou.

Estado atual vs. linha do tempo

Estado atual

O “estado atual” é o retrato do dado agora. Ex.: tabela cliente com a coluna status contendo apenas o status vigente. É simples e eficiente para operações do dia a dia, mas não responde perguntas históricas sem mecanismos adicionais.

Linha do tempo

A “linha do tempo” registra as mudanças e permite reconstruir o estado em qualquer instante. Ex.: tabela de histórico ou tabela com vigência (valid_from/valid_to) para cada versão do status. É essencial para relatórios retroativos, auditorias e reconciliações.

Risco comum em relatórios

Um erro frequente é gerar relatórios de períodos passados usando o estado atual. Ex.: “vendas de clientes ativos em janeiro” calculadas com o status atual do cliente (hoje) em vez do status vigente em janeiro. Isso distorce indicadores e pode gerar inconsistências contábeis e regulatórias.

Auditoria: trilha mínima e trilha detalhada

Trilha mínima (colunas de auditoria)

Um padrão simples e muito útil é adicionar colunas de auditoria nas tabelas principais. Elas ajudam a responder “quando” e “por quem” algo foi criado/alterado, mas não guardam versões antigas do conteúdo.

Continue em nosso aplicativo e ...

• Ouça o áudio com a tela desligada
• Ganhe Certificado após a conclusão
• + de 5000 cursos para você explorar!

ou continue lendo abaixo...

Baixar o aplicativo

• created_at: data/hora de criação do registro
• updated_at: data/hora da última atualização
• created_by: identificador do usuário/sistema que criou
• updated_by: identificador do usuário/sistema que atualizou por último

Cuidados:

• Fuso horário: padronize (ex.: UTC) e documente.
• Origem: created_by/updated_by podem ser usuário humano, serviço, integração; defina um domínio consistente.
• Atualização automática: defina regra clara (trigger, camada de aplicação, etc.) para não depender de disciplina manual.

Trilha detalhada (log de alterações)

Quando é necessário saber o que mudou (campo anterior vs. novo), use uma entidade/tabela de auditoria de eventos. Ela registra cada alteração como um evento, com metadados e, opcionalmente, o “antes/depois”.

Cuidados:

• Volume: logs detalhados crescem rápido; defina retenção e estratégia de arquivamento.
• Privacidade: evite registrar dados sensíveis sem necessidade; aplique mascaramento/criptografia quando aplicável.
• Consultas: auditoria detalhada é ótima para rastreabilidade, mas nem sempre é a melhor fonte para relatórios analíticos.

Padrão 1: tabela de histórico (snapshot por versão)

Use quando você precisa consultar versões anteriores de um registro (ou parte dele) e reconstruir o estado em um momento.

Estrutura típica

Você mantém:

• Uma tabela “atual” (opcional, mas comum) com o estado vigente.
• Uma tabela de histórico com múltiplas versões por entidade.

cliente (estado atual)                 cliente_hist (linha do tempo por versão)  - cliente_id (PK)                     - hist_id (PK)  - nome                                 - cliente_id (FK)  - status                               - nome  - created_at                            - status  - updated_at                            - valid_from  - created_by                            - valid_to  - updated_by                            - changed_at  - ...                                  - changed_by

Como funciona

• cliente guarda o valor vigente (rápido para consultas operacionais).
• cliente_hist guarda cada versão com período de vigência.

Passo a passo prático

1. Defina o que precisa de histórico: status? endereço? limite de crédito? Nem tudo precisa versionar.
2. Escolha o “gatilho” de versionamento: toda atualização gera uma nova versão ou apenas mudanças em campos específicos.
3. Defina colunas de vigência: valid_from e valid_to.
4. Regra de fechamento de versão: ao inserir uma nova versão, feche a anterior ajustando valid_to.
5. Garanta não sobreposição: para o mesmo cliente_id, períodos não podem se sobrepor.
6. Defina como consultar “vigente”: por valid_to nulo ou por maior valid_from.

Consulta típica: estado em uma data

Para obter a versão vigente em uma data :t:

SELECT * FROM cliente_hist WHERE cliente_id = :id   AND valid_from <= :t   AND (valid_to IS NULL OR valid_to > :t);

Observação: o uso de valid_to > :t (intervalo semiaberto) evita ambiguidade em limites exatos de tempo.

Padrão 2: validade início/fim na própria tabela (bitemporal simplificado)

Em alguns casos, você pode armazenar múltiplas linhas na própria tabela “principal”, cada uma com vigência. Isso elimina a separação “atual vs histórico”, mas exige que todas as consultas operacionais filtrem pela vigência.

Exemplo: preço de produto por período

produto_preco  - produto_id (FK)  - valid_from  - valid_to  - preco  - created_at  - created_by

Regras essenciais:

• Para um mesmo produto_id, períodos não podem se sobrepor.
• Deve existir no máximo um preço vigente por instante.
• Relatórios devem usar a data do evento (ex.: data da venda) para buscar o preço vigente naquele momento.

Passo a passo prático

1. Escolha a granularidade: data (dia) ou timestamp (data/hora). Para preço, muitas vezes timestamp é necessário.
2. Defina o intervalo: recomende intervalo semiaberto ([valid_from, valid_to)).
3. Crie regra de inserção: ao cadastrar novo preço, feche o anterior (valid_to = novo.valid_from).
4. Padronize consulta: sempre filtrar por data de referência.

Status ao longo do tempo: quando é atributo, quando vira entidade temporal

Status pode ser um simples atributo no estado atual, mas vira um caso temporal quando você precisa responder “desde quando está assim?” ou “qual era o status em uma data?”.

Padrão: histórico de status

cliente_status_hist  - cliente_id (FK)  - status  - valid_from  - valid_to  - changed_at  - changed_by

Boas práticas:

• Não duplicar sem necessidade: se apenas o status precisa de histórico, não versione o cliente inteiro; crie histórico apenas do status.
• Motivo da mudança (quando relevante): change_reason pode ser importante para auditoria e suporte.

Cuidados com relatórios e integrações

Relatórios “as of” (como era em…)

Relatórios retroativos devem usar uma data de referência (ex.: data do pedido, data de competência) e buscar a versão vigente naquele instante. Isso vale para status, preço, endereço fiscal, regras de comissão etc.

Relatórios “current” (como está agora)

Relatórios operacionais (fila do dia, carteira atual) normalmente usam apenas o estado vigente. Misturar “current” com métricas históricas é uma fonte comum de divergência.

Integrações e reprocessamentos

Se eventos podem chegar atrasados (ex.: integração que envia alterações com delay), o modelo temporal precisa suportar inserção de versões “no passado”. Isso aumenta a complexidade: pode exigir recalcular valid_to de versões intermediárias e revalidar sobreposições.

Critérios para decidir o nível de detalhe temporal

Nem todo dado precisa de linha do tempo completa. Use critérios objetivos para decidir:

• Exigência legal/regulatória: obriga retenção e rastreabilidade? Por quanto tempo?
• Impacto financeiro: preço, impostos, comissões, limites e contratos tendem a exigir vigência.
• Frequência de mudança: quanto mais muda, maior o custo de armazenar e consultar histórico detalhado.
• Necessidade de reconstituir decisões: se decisões dependem do valor no momento (ex.: aprovação de crédito), registre a versão usada.
• Granularidade necessária: dia vs. minuto vs. segundo. Evite precisão maior do que o necessário.
• Consumo por relatórios: se relatórios “as of” são frequentes, modele vigência de forma explícita e indexável.

Regras de integridade típicas em modelos temporais

1) Não sobreposição de períodos

Para uma mesma entidade (ex.: mesmo cliente_id), não pode haver duas versões com intervalos que se cruzam.

2) Continuidade (opcional)

Alguns domínios exigem que não existam “buracos” entre versões (ex.: sempre deve haver um preço). Outros permitem ausência (ex.: status indefinido).

3) Uma versão vigente por vez

Se você usa valid_to IS NULL para indicar vigente, garanta que exista no máximo uma linha com valid_to nulo por entidade.

4) Semântica do tempo

Defina se valid_from/valid_to representam:

• Tempo de vigência do negócio (quando “vale” no mundo real), ou
• Tempo de registro (quando foi gravado no sistema).

Em muitos casos, você precisa de ambos: valid_from/valid_to (negócio) e created_at/updated_at (registro/auditoria).