404 lines
17 KiB
Markdown
404 lines
17 KiB
Markdown
# 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
|
|
|
|
```sql
|
|
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
|
|
|
|
```sql
|
|
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',
|
|
quiet_hours int4range, -- janela de silêncio, ex.: [22,7)
|
|
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_hours` alimenta 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.
|
|
|
|
## 3. Pessoas e dispositivos
|
|
|
|
```sql
|
|
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
|
|
|
|
```sql
|
|
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
|
|
|
|
```sql
|
|
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
|
|
|
|
```sql
|
|
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
|
|
|
|
```sql
|
|
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
|
|
|
|
```sql
|
|
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 text, -- 6 dígitos, conferência humana
|
|
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
|
|
|
|
```sql
|
|
-- 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:
|
|
|
|
```sql
|
|
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
|
|
|
|
```sql
|
|
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.
|
|
|
|
## 9. Row Level Security — preparada, desativada
|
|
|
|
```sql
|
|
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`.
|