Projeto-Portaria/docs/02-MODELO-DE-DADOS.md

02 — Modelo de dados

PostgreSQL 17 + PostGIS. Migrations em backend/src/main/resources/db/migration/ via Flyway. Convenções: snake_case, PKs uuid (gen_random_uuid()), timestamps timestamptz, soft delete só onde há exigência de auditoria.

Regras invioláveis

1. Toda tabela de negócio tem tenant_id uuid NOT NULL — mesmo com multi-tenancy desativado na v1. Adicionar depois seria reescrever schema e queries.
2. Nada de ON DELETE CASCADE em dado auditável. Visitas, mídias e logs sobrevivem à exclusão de unidades e moradores — são prova jurídica.
3. version integer NOT NULL DEFAULT 0 em toda tabela com escrita concorrente.
4. Timestamps sempre timestamptz. Nunca timestamp.

---

1. Tenancy e planos

CREATE TABLE tenants (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name        text NOT NULL,
    document    text,                          -- CNPJ da administradora
    plan_id     uuid NOT NULL REFERENCES plans(id),
    status      text NOT NULL DEFAULT 'ATIVO', -- ATIVO | SUSPENSO | CANCELADO
    created_at  timestamptz NOT NULL DEFAULT now(),
    version     integer NOT NULL DEFAULT 0
);

CREATE TABLE plans (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    code        text NOT NULL UNIQUE,      -- AUTONOMO | ASSISTIDO_24H
    name        text NOT NULL,
    base_price  numeric(10,2),             -- referência comercial; cobrança é manual na v1
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- Módulos ligáveis por tenant. A flag manda; o plano é só o default aplicado na criação.
CREATE TABLE tenant_features (
    tenant_id    uuid NOT NULL REFERENCES tenants(id),
    feature_key  text NOT NULL,
    enabled      boolean NOT NULL DEFAULT false,
    quota        integer,                  -- NULL = ilimitado (ex.: minutos de vídeo/mês)
    updated_at   timestamptz NOT NULL DEFAULT now(),
    updated_by   uuid,
    PRIMARY KEY (tenant_id, feature_key)
);

Chaves de feature: whatsapp_notifications, operator_queue, video_recording, access_control_hardware, recurring_authorizations.

2. Estrutura física do condomínio

CREATE TABLE condominiums (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id     uuid NOT NULL REFERENCES tenants(id),
    name          text NOT NULL,
    address       text NOT NULL,
    timezone      text NOT NULL DEFAULT 'America/Sao_Paulo',
    -- janela de silêncio ("quiet_hours" nos demais docs); start > end cruza a meia-noite (22 → 7)
    quiet_start   smallint CHECK (quiet_start BETWEEN 0 AND 23),
    quiet_end     smallint CHECK (quiet_end BETWEEN 0 AND 23),
    created_at    timestamptz NOT NULL DEFAULT now(),
    version       integer NOT NULL DEFAULT 0
);

CREATE TABLE blocks (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id        uuid NOT NULL,
    condominium_id   uuid NOT NULL REFERENCES condominiums(id),
    name             text NOT NULL,             -- "A", "Torre Norte"
    UNIQUE (condominium_id, name)
);

CREATE TABLE units (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id      uuid NOT NULL,
    block_id       uuid NOT NULL REFERENCES blocks(id),
    identifier     text NOT NULL,               -- "101", "Sala 42"
    -- regra padrão de entrega quando ninguém responde em 15s
    delivery_rule  text NOT NULL DEFAULT 'DEIXAR_PORTARIA',  -- DEIXAR_PORTARIA | RECUSAR | AGUARDAR
    active         boolean NOT NULL DEFAULT true,
    version        integer NOT NULL DEFAULT 0,
    UNIQUE (block_id, identifier)
);

quiet_start/quiet_end alimentam a defesa contra "tocar em todos os apartamentos de madrugada": dentro da janela, visitas não-pré-autorizadas vão direto para a fila de operador em vez de acordar moradores. Duas colunas em vez de int4range de propósito: a janela típica cruza a meia-noite (22 → 7), e int4range(22, 7) é um range inválido no Postgres. A verificação "está na janela?" vive em shared/validation, testada nos dois sentidos.

3. Pessoas e dispositivos

CREATE TABLE persons (
    id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id    uuid NOT NULL,
    name         text NOT NULL,
    email        text,
    phone        text,                       -- E.164
    auth_subject text UNIQUE,                -- sub do OIDC; NULL até aceitar o convite
    created_at   timestamptz NOT NULL DEFAULT now(),
    version      integer NOT NULL DEFAULT 0
);

-- Uma pessoa pode estar em várias unidades (e uma unidade tem vários moradores)
CREATE TABLE unit_members (
    id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id    uuid NOT NULL,
    unit_id      uuid NOT NULL REFERENCES units(id),
    person_id    uuid NOT NULL REFERENCES persons(id),
    role         text NOT NULL,              -- PROPRIETARIO | INQUILINO | DEPENDENTE
    ring_order   smallint NOT NULL DEFAULT 0,-- ordem de escalonamento dentro da unidade
    receives_calls boolean NOT NULL DEFAULT true,
    valid_from   date NOT NULL DEFAULT CURRENT_DATE,
    valid_until  date,                       -- reconciliação com a administradora
    UNIQUE (unit_id, person_id)
);

CREATE TABLE devices (
    id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id    uuid NOT NULL,
    person_id    uuid NOT NULL REFERENCES persons(id),
    platform     text NOT NULL,              -- ANDROID | IOS
    push_token   text NOT NULL,              -- FCM token
    voip_token   text,                       -- APNs PushKit — obrigatório no iOS
    app_version  text,
    last_seen_at timestamptz,
    active       boolean NOT NULL DEFAULT true,
    UNIQUE (platform, push_token)
);

voip_token separado não é redundância. No iOS, notificação comum não faz o telefone tocar como chamada — só PushKit + CallKit fazem, e o token do PushKit é distinto do token APNs normal. Sem essa coluna, o app iOS não funciona como portaria.

ring_order define a ordem do escalonamento dentro da unidade. valid_until é o gancho da reconciliação periódica com a administradora — morador que saiu para de receber chamadas sem precisar excluir histórico.

Responsáveis pela abertura e operadores

CREATE TABLE gate_responsibles (
    id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id    uuid NOT NULL,
    gate_id      uuid NOT NULL REFERENCES gates(id),
    person_id    uuid NOT NULL REFERENCES persons(id),
    priority     smallint NOT NULL DEFAULT 0,
    active       boolean NOT NULL DEFAULT true
);

CREATE TABLE operators (
    id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id    uuid NOT NULL,
    person_id    uuid NOT NULL REFERENCES persons(id),
    status       text NOT NULL DEFAULT 'OFFLINE',  -- OFFLINE | DISPONIVEL | EM_ATENDIMENTO
    status_since timestamptz NOT NULL DEFAULT now(),
    version      integer NOT NULL DEFAULT 0
);

4. Portarias e QR

CREATE TABLE gates (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id        uuid NOT NULL,
    condominium_id   uuid NOT NULL REFERENCES condominiums(id),
    name             text NOT NULL,          -- "Portaria Social", "Garagem"
    location         geography(Point, 4326) NOT NULL,
    geofence_meters  integer NOT NULL DEFAULT 80,
    qr_secret        text NOT NULL,          -- segredo de assinatura do QR
    qr_version       integer NOT NULL DEFAULT 1,  -- incrementar reimprime o QR e invalida o antigo
    active           boolean NOT NULL DEFAULT true,
    version          integer NOT NULL DEFAULT 0
);
CREATE INDEX idx_gates_location ON gates USING GIST (location);

O QR impresso não rotaciona, então a defesa é em camadas — a validação de que o visitante está dentro de geofence_meters do portão é a mais forte delas. A permissão de localização deixa de ser só auditoria e vira controle anti-fraude.

qr_version permite invalidar QRs vazados: incrementa a versão, reimprime a placa, e os códigos antigos param de validar.

5. Visitas — o agregado central

CREATE TABLE visits (
    id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id         uuid NOT NULL,
    gate_id           uuid NOT NULL REFERENCES gates(id),
    unit_id           uuid REFERENCES units(id),   -- NULL se a unidade informada não existe (busca cega)
    unit_input        text NOT NULL,               -- o que o visitante digitou, sempre preservado
    kind              text NOT NULL,               -- VISITA | ENTREGA | PRESTADOR
    state             text NOT NULL DEFAULT 'PENDENTE',

    visitor_name      text NOT NULL,
    visitor_document  text,
    visitor_phone     text,
    visitor_location  geography(Point, 4326),
    location_accuracy real,
    inside_geofence   boolean,

    room_name         text,                        -- sala LiveKit
    answered_by       uuid REFERENCES persons(id),
    resolved_by       uuid REFERENCES persons(id),
    resolution_reason text,
    degraded_mode     text,                        -- NULL | AUDIO | FOTO_TEXTO | OPERADOR

    created_at        timestamptz NOT NULL DEFAULT now(),
    ringing_at        timestamptz,
    answered_at       timestamptz,
    resolved_at       timestamptz,
    expires_at        timestamptz NOT NULL,
    version           integer NOT NULL DEFAULT 0
);

CREATE INDEX idx_visits_unit_created ON visits (tenant_id, unit_id, created_at DESC);
CREATE INDEX idx_visits_state_active ON visits (state, expires_at)
    WHERE state NOT IN ('AUTORIZADA','NEGADA','EXPIRADA','CANCELADA','RECADO_EM_VIDEO');

unit_id nulo com unit_input preenchido é o coração da busca cega. Se o visitante digitar uma unidade inexistente, a visita é criada do mesmo jeito, entra em TOCANDO e simplesmente não é atendida — a resposta da API é idêntica à de uma unidade válida. Um atacante com o QR não consegue distinguir "apartamento não existe" de "ninguém atendeu", e portanto não consegue mapear o prédio.

answered_by e resolved_by são separados de propósito: quem atendeu a chamada pode não ser quem decidiu (operador atende, morador decide).

Tentativas de toque

CREATE TABLE visit_attempts (
    id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id    uuid NOT NULL,
    visit_id     uuid NOT NULL REFERENCES visits(id),
    person_id    uuid REFERENCES persons(id),
    target_kind  text NOT NULL,     -- MORADOR | OUTROS_MORADORES | OPERADOR | RESPONSAVEL_ABERTURA
    channel      text NOT NULL,     -- PUSH | VOIP | WHATSAPP | WEBSOCKET
    sent_at      timestamptz NOT NULL DEFAULT now(),
    delivered_at timestamptz,
    answered_at  timestamptz,
    failure      text
);

Esta tabela é o que permite responder "por que ninguém atendeu?" — push não entregue, token expirado, morador sem app. Sem ela, a falha mais comum do produto fica invisível.

6. Mídia, autorizações e consentimento

CREATE TABLE media_assets (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id     uuid NOT NULL,
    visit_id      uuid REFERENCES visits(id),
    kind          text NOT NULL,        -- FOTO_VISITANTE | FOTO_PACOTE | RECADO_VIDEO | GRAVACAO
    storage_key   text NOT NULL,        -- caminho no MinIO/S3
    content_type  text NOT NULL,
    size_bytes    bigint,
    sha256        text NOT NULL,        -- integridade: prova que a mídia não foi alterada
    created_at    timestamptz NOT NULL DEFAULT now(),
    expires_at    timestamptz NOT NULL  -- expurgo automático (LGPD)
);
CREATE INDEX idx_media_expiry ON media_assets (expires_at) WHERE expires_at IS NOT NULL;

CREATE TABLE access_grants (
    id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id      uuid NOT NULL,
    visit_id       uuid NOT NULL REFERENCES visits(id),
    gate_id        uuid NOT NULL REFERENCES gates(id),
    granted_by     uuid NOT NULL REFERENCES persons(id),
    pin_hash       text,                -- hash do PIN de 6 dígitos; o PIN em claro só existe na tela e na notificação
    valid_until    timestamptz NOT NULL,
    used_at        timestamptz,
    device_result  text,                 -- resultado do AccessControlDevice (v2)
    created_at     timestamptz NOT NULL DEFAULT now()
);

-- Registro de que o aviso de tratamento foi exibido (LGPD: transparência, não consentimento)
CREATE TABLE privacy_notices (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id     uuid NOT NULL,
    visit_id      uuid NOT NULL REFERENCES visits(id),
    notice_version text NOT NULL,        -- versão do texto exibido
    shown_at      timestamptz NOT NULL DEFAULT now(),
    ip_address    inet,
    user_agent    text
);

privacy_notices registra exibição de aviso, não consentimento — a base legal é legítimo interesse (ver 06-LGPD-E-SEGURANCA.md). O que precisa ser provável é que a informação foi dada, com a versão exata do texto.

sha256 em media_assets sustenta o valor probatório: sem hash, a foto guardada não prova nada em disputa jurídica.

7. Infraestrutura de confiabilidade

-- Padrão 1: outbox transacional
CREATE TABLE outbox_events (
    id             bigserial PRIMARY KEY,
    tenant_id      uuid NOT NULL,
    aggregate_type text NOT NULL,
    aggregate_id   uuid NOT NULL,
    event_type     text NOT NULL,
    payload        jsonb NOT NULL,
    created_at     timestamptz NOT NULL DEFAULT now(),
    published_at   timestamptz,
    attempts       integer NOT NULL DEFAULT 0,
    next_attempt_at timestamptz NOT NULL DEFAULT now(),
    last_error     text
);
CREATE INDEX idx_outbox_pending ON outbox_events (next_attempt_at)
    WHERE published_at IS NULL;

-- Padrões 2 e 6: timeouts persistidos e fila durável
CREATE TABLE scheduled_jobs (
    id            bigserial PRIMARY KEY,
    tenant_id     uuid,
    job_type      text NOT NULL,     -- VISIT_RING_TIMEOUT | VISIT_ESCALATE | MEDIA_PURGE ...
    payload       jsonb NOT NULL,
    run_at        timestamptz NOT NULL,
    locked_until  timestamptz,
    attempts      integer NOT NULL DEFAULT 0,
    completed_at  timestamptz,
    last_error    text
);
CREATE INDEX idx_jobs_due ON scheduled_jobs (run_at)
    WHERE completed_at IS NULL;

-- Padrão 4: idempotência
CREATE TABLE idempotency_keys (
    key           text PRIMARY KEY,
    tenant_id     uuid NOT NULL,
    endpoint      text NOT NULL,
    request_hash  text NOT NULL,
    response_code integer,
    response_body jsonb,
    created_at    timestamptz NOT NULL DEFAULT now(),
    expires_at    timestamptz NOT NULL DEFAULT now() + interval '24 hours'
);

Consumo da fila sempre com FOR UPDATE SKIP LOCKED, para que réplicas concorrentes não peguem o mesmo job:

SELECT * FROM scheduled_jobs
 WHERE completed_at IS NULL AND run_at <= now()
   AND (locked_until IS NULL OR locked_until < now())
 ORDER BY run_at
 LIMIT 50
 FOR UPDATE SKIP LOCKED;

8. Auditoria

CREATE TABLE audit_log (
    id          bigserial PRIMARY KEY,
    tenant_id   uuid NOT NULL,
    actor_id    uuid,
    actor_kind  text NOT NULL,        -- MORADOR | ADMIN | OPERADOR | VISITANTE | SISTEMA
    action      text NOT NULL,
    entity_type text NOT NULL,
    entity_id   uuid,
    before      jsonb,
    after       jsonb,
    ip_address  inet,
    created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_audit_entity ON audit_log (tenant_id, entity_type, entity_id, created_at DESC);

Append-only. Nenhum UPDATE ou DELETE, garantido por permissão do usuário de aplicação no banco. É a defesa jurídica em caso de autorização indevida.

Papéis de banco separados — sem isso o revoke é decorativo. Dono de tabela ignora REVOKE e RLS (salvo FORCE). Portanto: o Flyway conecta como portaria (dono do schema, roda migrations); a aplicação conecta como portaria_app, papel sem ownership, criado no init do Postgres (ver 05-INFRA-DOCKER.md §2). Se o backend conectar como dono, nem o append-only do audit_log nem as policies de RLS valem nada — e nenhum teste funcional percebe.

9. Row Level Security — preparada, desativada

CREATE POLICY tenant_isolation ON visits
  USING (tenant_id = current_setting('app.current_tenant', true)::uuid);
ALTER TABLE visits DISABLE ROW LEVEL SECURITY;   -- v1

Criada em todas as tabelas de negócio na mesma migration da tabela. Ativar multi-tenancy vira ENABLE ROW LEVEL SECURITY + SET app.current_tenant por conexão.

10. Retenção e expurgo

Dado	Retenção padrão	Mecanismo
Foto do visitante	90 dias	media_assets.expires_at + job MEDIA_PURGE
Foto de pacote	30 dias	idem
Recado em vídeo	30 dias ou até o morador resolver	idem
Gravação de chamada	90 dias (só com módulo ativo)	idem
Registro de visita (sem mídia)	5 anos	retido — obrigação de segurança patrimonial
audit_log	5 anos	retido
idempotency_keys	24 horas	job de limpeza

O job MEDIA_PURGE roda diariamente, apaga o objeto no MinIO e anula storage_key, preservando a linha — o registro de que existiu uma foto continua auditável mesmo depois de o dado pessoal ser eliminado.

11. Ordem das migrations

V1__tenancy_e_planos.sql          tenants, plans, tenant_features
V2__estrutura_condominio.sql      condominiums, blocks, units
V3__pessoas_e_dispositivos.sql    persons, unit_members, devices, operators
V4__portarias.sql                 gates (+ PostGIS), gate_responsibles
V5__visitas.sql                   visits, visit_attempts
V6__midia_e_autorizacoes.sql      media_assets, access_grants, privacy_notices
V7__confiabilidade.sql            outbox_events, scheduled_jobs, idempotency_keys
V8__auditoria.sql                 audit_log + revogação de UPDATE/DELETE
V9__rls_policies.sql              policies criadas e desativadas
V10__seed_planos.sql              AUTONOMO e ASSISTIDO_24H

CREATE EXTENSION postgis; e pgcrypto vão na V1.