04 - Mapeamento de Banco de Dados v1.0

Logs e Auditoria — Mapeamento de Banco de Dados (v1.0)

Documentos desta funcionalidade: 01 - Definição (v1.0) · 02 - Especificação (v1.0) · 03 - Protótipo (v1.0, código em 03 - Protótipo v1.0.html) · 04 - Mapeamento de Banco de Dados (v1.0) · Cenários de Teste (v1.0) · 05 - Registro de Decisões (v1.0)

Fonte primária da estrutura: Biblioteca de Schema (DDL) (IpSeguranca.sql, Gescom.sql, catalog.json, framework_pattern.md). O log de sistema não usa banco relacional (vai para Loki e Prometheus); este mapeamento trata da trilha de auditoria. Tudo que é tabela ou coluna nova está marcado [PROPOSTA].

Convenções: instantes em UTC (datetime2(3) no SQL Server, timestamptz no PostgreSQL). O banco final pode mudar de SQL Server para PostgreSQL (decisão em aberto), por isso há modelo lógico neutro e um mapeamento para cada motor (4.4).

---

4.1 Tabelas existentes utilizadas

Tabela	Base	Uso
Paciente (PK IdPaciente int)	GescomClienteAlfa	Identificador opaco do paciente na trilha (RN-LOG-015). NumProntuario, CPF e nome não entram.
Clinica (CNES, IdChClinica)	GescomClienteAlfa	CNES na identificação da exportação (NGS1.07.08)
SegEmpresa (SegurancaClienteEmpresaId)	GescomClienteAlfa	Liga a clínica local ao ClienteEmpresa global
ClienteEmpresa (Id, Nome, CNPJ, ClienteBDId)	IPSeguranca	Nome e CNPJ da instituição na exportação; clínica na trilha
ClienteBD (Id, NomeDatabase)	IPSeguranca	Tenant (ClienteBDId)
Usuario (Id guid, TentativasLogin, DHBloqueio)	IPSeguranca	Usuário (GUID global)
UsuarioCliente (UsuarioId, ClienteBDId, Situacao)	IPSeguranca	Usuário por cliente
ClientePerfilAcesso (Id, Nome, ClienteBDId)	IPSeguranca	Perfil “Auditor” por cliente (seed 4.6)
Processo, DireitoProcesso, ProcessoDireito	IPSeguranca	Processo “Auditoria” e direito “Visualizar auditoria” (seed 4.6)
Log, LogComplemento	IPSeguranca	Não reutilizadas. Finalidade não confirmada, campos insuficientes (sem categoria, registro afetado, hash) e sem proteção de somente inclusão. Destino a decidir em P-LOG-07.

4.2 Outras bases

IpTerminologia: sem uso. GescomZeradoPadronizacao (legado): a trilha não é criada no legado; o legado tem PEPLog (com NovoValor), que não é migrado para a trilha nova (ver P-LOG-01).

4.3 Estados e domínios

Domínio	Valores	Origem
Categoria	1 = Clínica, 2 = Operacional	RN-LOG-011 [PROPOSTA]
TipoEvento	smallint, numeração do catálogo AuditEventType (AUD-RES-001 = 1001, AUD-USR-001 = 2001, AUD-OPE-001 = 3001; sequência dentro da faixa)	Especificação 2.3.3 [PROPOSTA]
Resultado	1 = sucesso, 2 = negado, 3 = falha	[PROPOSTA]

A numeração do tipo é estável e nunca reaproveitada; o nome vem do catálogo em código e do dicionário i18n, não do banco.

4.4 Modelo lógico e mapeamento por banco

4.4.1 Modelo lógico (neutro) — entidade auditoria.Evento [PROPOSTA]

Existe em dois lugares com a mesma estrutura: IpSeguranca (repositório global) e GescomClienteAlfa (repositório do cliente). A coluna PacienteId só é usada no repositório do cliente.

Campo lógico	Tipo lógico	Obrig.	Descrição	Requisito
Id	inteiro 64 bits, sequencial	sim	Identificador único	NGS1.07.05
OcorridoEm	instante UTC	sim	Data e hora	NGS1.07.05
TipoEvento	inteiro 16 bits	sim	Código do catálogo	NGS1.07.05
Categoria	inteiro 8 bits	sim	Clínica ou operacional	RN-LOG-011
UsuarioId	GUID	sim (nulo em login de usuário desconhecido: grava o login informado em Detalhe, sem senha)	Usuário global	NGS1.07.05
ClienteBDId	GUID	sim	Tenant	RN-LOG-017
ClinicaId	GUID	não	ClienteEmpresa.Id	RN-LOG-002
PacienteId	inteiro 32 bits	não	Paciente.IdPaciente, identificador do registro afetado clínico	NGS1.07.07c
Recurso	texto 60	não	Tipo do registro afetado (ex.: Prontuario, Prescricao, Usuario)	NGS1.07.05
RecursoId	texto 64	não	Id do recurso afetado	NGS1.07.05
Componente	texto 60	sim	Serviço/componente	NGS1.07.05
Ip	texto 45	não	IP de origem	NGS1.07.05
TraceId	texto 32	não	Liga ao log de sistema	RN-LOG-002
Resultado	inteiro 8 bits	sim	Sucesso, negado, falha	[PROPOSTA]
Detalhe	JSON	não	Informação complementar: nomes de campos alterados; sem valores clínicos	NGS1.07.06
HashLinha	32 bytes	sim	SHA-256 dos campos canônicos	RN-LOG-025

Hash canônico: SHA-256 sobre a concatenação UTF-8 de OcorridoEm (RFC 3339), TipoEvento, Categoria, UsuarioId, ClienteBDId, ClinicaId, PacienteId, Recurso, RecursoId, Componente, Ip, TraceId, Resultado e Detalhe, na ordem fixa, separados por |, nulo como texto vazio. Calculado pela aplicação, igual nos dois bancos.

Exceção ao padrão de 16 campos: a tabela não leva o carimbo de última ação (CriadoEm, AtualizadoEm, RemovidoEm, etc.), porque o registro nunca é alterado nem removido; o próprio registro é o carimbo. A detecção de “tabelas sem o padrão” da Biblioteca de Schema deve tratar auditoria.Evento como exceção declarada.

4.4.2 SQL Server [PROPOSTA]

CREATE PARTITION FUNCTION pf_auditoria_mes (datetime2(3))
  AS RANGE RIGHT FOR VALUES ('2026-12-01','2027-01-01' /* ... criadas por rotina */);
CREATE PARTITION SCHEME ps_auditoria_mes
  AS PARTITION pf_auditoria_mes ALL TO ([PRIMARY]);
 
CREATE TABLE auditoria.Evento (
  Id          bigint IDENTITY(1,1) NOT NULL,
  OcorridoEm  datetime2(3)     NOT NULL,
  TipoEvento  smallint         NOT NULL,
  Categoria   tinyint          NOT NULL,
  UsuarioId   uniqueidentifier NULL,
  ClienteBDId uniqueidentifier NOT NULL,
  ClinicaId   uniqueidentifier NULL,
  PacienteId  int              NULL,
  Recurso     varchar(60)      NULL,
  RecursoId   varchar(64)      NULL,
  Componente  varchar(60)      NOT NULL,
  Ip          varchar(45)      NULL,
  TraceId     char(32)         NULL,
  Resultado   tinyint          NOT NULL,
  Detalhe     nvarchar(max)    NULL,
  HashLinha   binary(32)       NOT NULL,
  CONSTRAINT Evento_PK PRIMARY KEY CLUSTERED (OcorridoEm, Id)
    WITH (DATA_COMPRESSION = PAGE) ON ps_auditoria_mes (OcorridoEm),
  CONSTRAINT Evento_Detalhe_CK CHECK (Detalhe IS NULL OR ISJSON(Detalhe) = 1)
);
CREATE INDEX Evento_Usuario_IX  ON auditoria.Evento (UsuarioId,  OcorridoEm) ON ps_auditoria_mes (OcorridoEm);
CREATE INDEX Evento_Paciente_IX ON auditoria.Evento (PacienteId, OcorridoEm) WHERE PacienteId IS NOT NULL ON ps_auditoria_mes (OcorridoEm);
CREATE INDEX Evento_Tipo_IX     ON auditoria.Evento (TipoEvento, OcorridoEm) ON ps_auditoria_mes (OcorridoEm);

Compressão PAGE por partição (partições antigas podem ser comprimidas mais tarde). Tabela ledger só existe no SQL Server 2022 ou superior e não é adotada (RN-LOG-025). Permissões: o papel da aplicação recebe INSERT e SELECT; DENY UPDATE, DELETE sobre o schema auditoria; o dono do schema é outro papel.

4.4.3 PostgreSQL [PROPOSTA]

CREATE TABLE auditoria.evento (
  id           bigint GENERATED ALWAYS AS IDENTITY,
  ocorrido_em  timestamptz NOT NULL,
  tipo_evento  smallint    NOT NULL,
  categoria    smallint    NOT NULL,
  usuario_id   uuid,
  cliente_bd_id uuid       NOT NULL,
  clinica_id   uuid,
  paciente_id  int,
  recurso      varchar(60),
  recurso_id   varchar(64),
  componente   varchar(60) NOT NULL,
  ip           varchar(45),
  trace_id     char(32),
  resultado    smallint    NOT NULL,
  detalhe      jsonb,
  hash_linha   bytea       NOT NULL,
  PRIMARY KEY (ocorrido_em, id)
) PARTITION BY RANGE (ocorrido_em);
CREATE INDEX ON auditoria.evento (usuario_id, ocorrido_em);
CREATE INDEX ON auditoria.evento (paciente_id, ocorrido_em) WHERE paciente_id IS NOT NULL;
CREATE INDEX ON auditoria.evento USING brin (ocorrido_em);
REVOKE UPDATE, DELETE, TRUNCATE ON auditoria.evento FROM gemed_app;

Partições mensais criadas e rotacionadas por pg_partman (ou rotina própria). A chave primária precisa conter a chave de partição, por isso (ocorrido_em, id). Sem compressão de página nativa: as opções são sistema de arquivos com compressão (ZFS), TimescaleDB ou Citus colunar; decidir após medir o volume (P-LOG-03). Partição antiga pode sair com DETACH PARTITION [CONCURRENTLY] para arquivamento, desde que continue visível na tela (NGS1.07.07b). Gatilho opcional que recusa UPDATE e DELETE como segunda barreira.

4.4.4 Equivalência de pacientes (cliente) [PROPOSTA]

auditoria.PacienteEquivalencia (PacienteIdOrigem int, PacienteIdDestino int, UnificadoEm datetime2(3), UsuarioId uniqueidentifier). Usada só se houver unificação de pacientes (EX-LOG-09); a consulta por IdPaciente inclui os de origem. Criar quando a unificação existir (P-LOG-08).

4.4.5 Log de sistema

Sem tabelas. Loki (rótulos service, environment, level; campos estruturados para o resto) e Prometheus. Retenção e armazenamento de objetos (MinIO/S3) a definir após medir o volume (P-LOG-02).

4.5 Lacunas e propostas

ID	Lacuna	Proposta
L-01	Não há tabela de trilha real em nenhuma das 4 bases	Criar auditoria.Evento em IpSeguranca e em cada banco de cliente
L-02	Não há perfil “Auditor” nem direito de visualização de auditoria em IpSeguranca	Seeds em 4.6
L-03	Log/LogComplemento sem finalidade confirmada	Manter como estão; decidir em P-LOG-07
L-04	Sem equivalência de paciente	4.4.4, quando necessário
L-05	Sem rotina de criação de partições	Rotina agendada que cria as partições dos próximos 3 meses [PROPOSTA]
L-06	Sem papel de banco só de inserção para a aplicação	Criar papéis aplicação e dono, separados

4.6 Seeds da Funcionalidade

Seed	Tabela(s)	Por cliente ou global?	Status	Detalhe
Processo “Auditoria” (rota da tela, contexto Segurança)	IpSeguranca.Processo	Global	[PROPOSTA]	Processo, Nome, Prefixo, Sistema, Contexto, Rota a seguir o padrão dos processos existentes (conferir na Fase 4)
Direito “Visualizar auditoria”	IpSeguranca.DireitoProcesso	Global	[PROPOSTA]	Direito, Nome, Tipo, domínio conforme os direitos existentes
Ligação processo–direito	IpSeguranca.ProcessoDireito	Global	[PROPOSTA]	Acao conforme o padrão existente
Perfil “Auditor”	IpSeguranca.ClientePerfilAcesso	Por cliente (ClienteBDId)	[PROPOSTA]	Criado por cliente na implantação; sem usuários por padrão
Permissão do perfil “Auditor” ao processo	tabela de ligação perfil–processo (ClientePerfilAcessoProcesso, conferir nome exato no DDL)	Por cliente	[PROPOSTA]	Concede “Visualizar auditoria”
Papéis de banco da aplicação (inserção e consulta) e do dono	auditoria nos dois bancos	Por banco	[PROPOSTA]	Scripts de implantação
Partições iniciais	auditoria.Evento	Por banco	[PROPOSTA]	Mês corrente e 3 seguintes

O administrador do cliente já poderá ser vinculado ao direito pela gestão de perfis existente.

4.7 Migração

Nada é migrado para a trilha nova. Os bancos de clientes em produção no legado não ganham a trilha; ela nasce com o Gemed 2.1 (GescomClienteAlfa). A troca de SQL Server para PostgreSQL, se ocorrer, copia a trilha por partição (mantendo Id e HashLinha, que independem do motor).