Skip to main content

Reestruturação do Banco de Dados para PostgreSQL

📋 Sumário Executivo​

Este documento apresenta uma proposta completa de reestruturação do banco de dados atual (MySQL) para PostgreSQL, focando em:

  • Performance: Otimização de consultas e indexação
  • Escalabilidade: Particionamento e sharding preparados
  • Integridade: Constraints e validações no banco
  • Manutenibilidade: Estrutura modular e normalizada
  • Observabilidade: Auditoria e monitoramento integrados

🎯 Objetivos da Reestruturação​

  1. Reduzir complexidade das tabelas principais (occurrences com 100+ campos)
  2. Normalizar estruturas redundantes
  3. Otimizar performance com índices e particionamento
  4. Garantir integridade com constraints apropriados
  5. Preparar para escala com estrutura modular
  6. Melhorar observabilidade com auditoria centralizada

🏗️ Nova Arquitetura Proposta​

1. Estrutura Modular por Domínios​

┌─────────────────────────────────────────────────────────┐
│                     CAMADA DE DOMÍNIOS                   │
├───────────────┬───────────────┬───────────────┬─────────┤
│   Identidade  │   Operacional │   Financeiro  │  Suporte│
├───────────────┼───────────────┼───────────────┼─────────┤
│ • users       │ • services    │ • billing     │ • audit │
│ • auth        │ • occurrences │ • payments    │ • logs  │
│ • permissions │ • scheduling  │ • invoices    │ • media │
│ • enterprises │ • contracts   │ • accounting  │ • notif │
└───────────────┴───────────────┴───────────────┴─────────┘

2. Schemas Separados por Domínio​

-- Schemas principais
CREATE SCHEMA identity;     -- Gestão de usuários e autenticação
CREATE SCHEMA operational;   -- Core business (ocorrências, serviços)
CREATE SCHEMA financial;     -- Financeiro e faturamento
CREATE SCHEMA geographic;    -- Dados geográficos
CREATE SCHEMA audit;        -- Auditoria e logs
CREATE SCHEMA media;        -- Arquivos e mídia
CREATE SCHEMA communication; -- Notificações e mensagens
CREATE SCHEMA analytics;    -- Dados agregados e relatórios

📊 Estrutura Detalhada dos Principais Domínios​

DOMÍNIO: IDENTITY (Identidade e Acesso)​

Tabela: identity.users​

CREATE TABLE identity.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    email_verified BOOLEAN DEFAULT FALSE,
    phone VARCHAR(20),
    phone_verified BOOLEAN DEFAULT FALSE,
    status user_status NOT NULL DEFAULT 'PENDING',
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

-- Enum para status
CREATE TYPE user_status AS ENUM (
    'PENDING', 'ACTIVE', 'SUSPENDED', 'BLOCKED', 'DELETED'
);

-- Índices otimizados
CREATE INDEX idx_users_email ON identity.users(email) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_username ON identity.users(username) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_status ON identity.users(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_metadata ON identity.users USING gin(metadata);

Tabela: identity.user_profiles​

CREATE TABLE identity.user_profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES identity.users(id),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    display_name VARCHAR(200),
    avatar_url TEXT,
    bio TEXT,
    birth_date DATE,
    preferences JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_user_profiles_user ON identity.user_profiles(user_id);

Tabela: identity.enterprises​

CREATE TABLE identity.enterprises (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    legal_name VARCHAR(255) NOT NULL,
    trade_name VARCHAR(255),
    document_type document_type NOT NULL,
    document_number VARCHAR(20) UNIQUE NOT NULL,
    status enterprise_status NOT NULL DEFAULT 'PENDING',
    type enterprise_type NOT NULL,
    settings JSONB DEFAULT '{}',
    billing_settings JSONB DEFAULT '{}',
    operational_settings JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

CREATE TYPE document_type AS ENUM ('CNPJ', 'CPF', 'PASSPORT', 'OTHER');
CREATE TYPE enterprise_status AS ENUM ('PENDING', 'ACTIVE', 'SUSPENDED', 'BLOCKED');
CREATE TYPE enterprise_type AS ENUM ('CLIENT', 'SUPPLIER', 'PARTNER', 'INTERNAL');

Tabela: identity.user_enterprises​

CREATE TABLE identity.user_enterprises (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES identity.users(id),
    enterprise_id UUID NOT NULL REFERENCES identity.enterprises(id),
    role enterprise_role NOT NULL,
    department VARCHAR(100),
    position VARCHAR(100),
    is_primary BOOLEAN DEFAULT FALSE,
    permissions JSONB DEFAULT '[]',
    valid_from TIMESTAMPTZ DEFAULT NOW(),
    valid_until TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TYPE enterprise_role AS ENUM ('OWNER', 'ADMIN', 'MANAGER', 'OPERATOR', 'VIEWER');
CREATE UNIQUE INDEX idx_user_enterprise_primary ON identity.user_enterprises(user_id)
    WHERE is_primary = TRUE;

DOMÍNIO: OPERATIONAL (Core Business)​

Tabela: operational.services​

CREATE TABLE operational.services (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category_id UUID REFERENCES operational.service_categories(id),
    type service_type NOT NULL,
    complexity_level INT CHECK (complexity_level BETWEEN 1 AND 5),
    estimated_duration INTERVAL,
    requirements JSONB DEFAULT '{}',
    metadata JSONB DEFAULT '{}',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TYPE service_type AS ENUM ('FIELD', 'REMOTE', 'HYBRID', 'CONSULTATION');

Tabela: operational.occurrences (Simplificada)​

CREATE TABLE operational.occurrences (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) UNIQUE NOT NULL,
    contract_id UUID NOT NULL REFERENCES operational.contracts(id),
    service_id UUID NOT NULL REFERENCES operational.services(id),
    client_id UUID NOT NULL REFERENCES identity.enterprises(id),
    status occurrence_status NOT NULL DEFAULT 'CREATED',
    priority priority_level NOT NULL DEFAULT 'MEDIUM',
    type occurrence_type NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    -- Constraints de negócio
    CONSTRAINT check_valid_dates CHECK (created_at <= updated_at)
);

CREATE TYPE occurrence_status AS ENUM (
    'CREATED', 'ASSIGNED', 'ACCEPTED', 'REJECTED',
    'IN_TRANSIT', 'ON_SITE', 'IN_PROGRESS',
    'COMPLETED', 'CANCELLED', 'FAILED'
);

CREATE TYPE priority_level AS ENUM ('LOW', 'MEDIUM', 'HIGH', 'CRITICAL');
CREATE TYPE occurrence_type AS ENUM ('STANDARD', 'EMERGENCY', 'SCHEDULED', 'RECURRING');

-- Índices para queries comuns
CREATE INDEX idx_occurrences_status ON operational.occurrences(status);
CREATE INDEX idx_occurrences_client ON operational.occurrences(client_id);
CREATE INDEX idx_occurrences_created ON operational.occurrences(created_at DESC);
CREATE INDEX idx_occurrences_composite ON operational.occurrences(client_id, status, created_at DESC);

Tabela: operational.occurrence_details​

CREATE TABLE operational.occurrence_details (
    occurrence_id UUID PRIMARY KEY REFERENCES operational.occurrences(id),
    description TEXT,
    observations TEXT,
    internal_notes TEXT,
    resolution_notes TEXT,
    customer_feedback TEXT,
    metadata JSONB DEFAULT '{}',
    custom_fields JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

Tabela: operational.occurrence_assignments​

CREATE TABLE operational.occurrence_assignments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    occurrence_id UUID NOT NULL REFERENCES operational.occurrences(id),
    specialist_id UUID NOT NULL REFERENCES identity.users(id),
    assigned_at TIMESTAMPTZ DEFAULT NOW(),
    accepted_at TIMESTAMPTZ,
    rejected_at TIMESTAMPTZ,
    reason TEXT,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_assignments_occurrence ON operational.occurrence_assignments(occurrence_id);
CREATE INDEX idx_assignments_specialist ON operational.occurrence_assignments(specialist_id);

Tabela: operational.occurrence_timeline​

CREATE TABLE operational.occurrence_timeline (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    occurrence_id UUID NOT NULL REFERENCES operational.occurrences(id),
    event_type timeline_event_type NOT NULL,
    event_data JSONB DEFAULT '{}',
    user_id UUID REFERENCES identity.users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TYPE timeline_event_type AS ENUM (
    'CREATED', 'ASSIGNED', 'STATUS_CHANGED', 'COMMENT_ADDED',
    'FILE_ATTACHED', 'LOCATION_UPDATED', 'COMPLETED'
);

CREATE INDEX idx_timeline_occurrence ON operational.occurrence_timeline(occurrence_id, created_at DESC);

DOMÍNIO: FINANCIAL (Financeiro)​

Tabela: financial.invoices​

CREATE TABLE financial.invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    enterprise_id UUID NOT NULL REFERENCES identity.enterprises(id),
    contract_id UUID REFERENCES operational.contracts(id),
    status invoice_status NOT NULL DEFAULT 'DRAFT',
    issue_date DATE NOT NULL,
    due_date DATE NOT NULL,
    subtotal DECIMAL(15,2) NOT NULL,
    tax_amount DECIMAL(15,2) DEFAULT 0,
    discount_amount DECIMAL(15,2) DEFAULT 0,
    total_amount DECIMAL(15,2) NOT NULL,
    paid_amount DECIMAL(15,2) DEFAULT 0,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),

    CONSTRAINT check_amounts CHECK (
        total_amount = subtotal + tax_amount - discount_amount
    )
);

CREATE TYPE invoice_status AS ENUM (
    'DRAFT', 'SENT', 'VIEWED', 'PARTIALLY_PAID', 'PAID', 'OVERDUE', 'CANCELLED'
);

-- Índices para consultas financeiras
CREATE INDEX idx_invoices_enterprise ON financial.invoices(enterprise_id);
CREATE INDEX idx_invoices_status ON financial.invoices(status);
CREATE INDEX idx_invoices_due_date ON financial.invoices(due_date) WHERE status NOT IN ('PAID', 'CANCELLED');

Tabela: financial.invoice_items​

CREATE TABLE financial.invoice_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_id UUID NOT NULL REFERENCES financial.invoices(id) ON DELETE CASCADE,
    occurrence_id UUID REFERENCES operational.occurrences(id),
    description TEXT NOT NULL,
    quantity DECIMAL(10,3) NOT NULL DEFAULT 1,
    unit_price DECIMAL(15,2) NOT NULL,
    discount_percent DECIMAL(5,2) DEFAULT 0,
    tax_percent DECIMAL(5,2) DEFAULT 0,
    total DECIMAL(15,2) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_invoice_items_invoice ON financial.invoice_items(invoice_id);

Tabela: financial.payments​

CREATE TABLE financial.payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    invoice_id UUID REFERENCES financial.invoices(id),
    amount DECIMAL(15,2) NOT NULL,
    payment_method payment_method NOT NULL,
    status payment_status NOT NULL DEFAULT 'PENDING',
    gateway_id VARCHAR(100),
    gateway_response JSONB,
    processed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TYPE payment_method AS ENUM ('CREDIT_CARD', 'DEBIT_CARD', 'BANK_TRANSFER', 'PIX', 'BOLETO', 'CASH');
CREATE TYPE payment_status AS ENUM ('PENDING', 'PROCESSING', 'APPROVED', 'REJECTED', 'CANCELLED', 'REFUNDED');

DOMÍNIO: GEOGRAPHIC (Localização)​

Tabela: geographic.addresses​

CREATE TABLE geographic.addresses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_type entity_type NOT NULL,
    entity_id UUID NOT NULL,
    type address_type NOT NULL DEFAULT 'MAIN',
    street VARCHAR(255) NOT NULL,
    number VARCHAR(20),
    complement VARCHAR(100),
    district VARCHAR(100),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(50) NOT NULL,
    country VARCHAR(2) NOT NULL DEFAULT 'BR',
    postal_code VARCHAR(10),
    coordinates GEOGRAPHY(POINT, 4326),
    formatted_address TEXT,
    place_id VARCHAR(255),
    is_verified BOOLEAN DEFAULT FALSE,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TYPE entity_type AS ENUM ('USER', 'ENTERPRISE', 'OCCURRENCE', 'OTHER');
CREATE TYPE address_type AS ENUM ('MAIN', 'BILLING', 'SHIPPING', 'SERVICE', 'OTHER');

-- Índices geoespaciais
CREATE INDEX idx_addresses_coordinates ON geographic.addresses USING GIST(coordinates);
CREATE INDEX idx_addresses_entity ON geographic.addresses(entity_type, entity_id);
CREATE INDEX idx_addresses_postal ON geographic.addresses(postal_code);

DOMÍNIO: AUDIT (Auditoria)​

Tabela: audit.audit_logs​

CREATE TABLE audit.audit_logs (
    id BIGSERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id UUID NOT NULL,
    action audit_action NOT NULL,
    user_id UUID REFERENCES identity.users(id),
    old_values JSONB,
    new_values JSONB,
    changes JSONB,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TYPE audit_action AS ENUM ('INSERT', 'UPDATE', 'DELETE', 'VIEW');

-- Particionamento por mês
CREATE TABLE audit.audit_logs_2024_01 PARTITION OF audit.audit_logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Índices para busca
CREATE INDEX idx_audit_logs_table ON audit.audit_logs(table_name, created_at DESC);
CREATE INDEX idx_audit_logs_record ON audit.audit_logs(record_id, created_at DESC);
CREATE INDEX idx_audit_logs_user ON audit.audit_logs(user_id, created_at DESC);

🚀 Recursos Avançados do PostgreSQL​

1. Triggers para Auditoria Automática​

CREATE OR REPLACE FUNCTION audit.log_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit.audit_logs(table_name, record_id, action, new_values, user_id)
        VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW), current_setting('app.current_user_id')::UUID);
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit.audit_logs(table_name, record_id, action, old_values, new_values, changes, user_id)
        VALUES (
            TG_TABLE_NAME,
            NEW.id,
            'UPDATE',
            to_jsonb(OLD),
            to_jsonb(NEW),
            to_jsonb(NEW) - to_jsonb(OLD),
            current_setting('app.current_user_id')::UUID
        );
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit.audit_logs(table_name, record_id, action, old_values, user_id)
        VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD), current_setting('app.current_user_id')::UUID);
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Aplicar trigger em tabelas críticas
CREATE TRIGGER audit_occurrences
    AFTER INSERT OR UPDATE OR DELETE ON operational.occurrences
    FOR EACH ROW EXECUTE FUNCTION audit.log_changes();

2. Row Level Security (RLS)​

-- Habilitar RLS na tabela de ocorrências
ALTER TABLE operational.occurrences ENABLE ROW LEVEL SECURITY;

-- Política para clientes verem apenas suas ocorrências
CREATE POLICY client_occurrences ON operational.occurrences
    FOR SELECT
    USING (client_id IN (
        SELECT enterprise_id FROM identity.user_enterprises
        WHERE user_id = current_setting('app.current_user_id')::UUID
    ));

-- Política para especialistas verem ocorrências atribuídas
CREATE POLICY specialist_occurrences ON operational.occurrences
    FOR SELECT
    USING (id IN (
        SELECT occurrence_id FROM operational.occurrence_assignments
        WHERE specialist_id = current_setting('app.current_user_id')::UUID
    ));

3. Materialized Views para Relatórios​

CREATE MATERIALIZED VIEW analytics.occurrence_summary AS
SELECT
    o.client_id,
    e.trade_name as client_name,
    DATE_TRUNC('month', o.created_at) as month,
    o.status,
    COUNT(*) as total_occurrences,
    AVG(EXTRACT(EPOCH FROM (o.updated_at - o.created_at))/3600) as avg_resolution_hours,
    SUM(CASE WHEN o.priority = 'CRITICAL' THEN 1 ELSE 0 END) as critical_count
FROM operational.occurrences o
JOIN identity.enterprises e ON e.id = o.client_id
GROUP BY o.client_id, e.trade_name, DATE_TRUNC('month', o.created_at), o.status
WITH DATA;

-- Índice para performance
CREATE UNIQUE INDEX idx_occurrence_summary
ON analytics.occurrence_summary(client_id, month, status);

-- Refresh automático
CREATE OR REPLACE FUNCTION refresh_occurrence_summary()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY analytics.occurrence_summary;
END;
$$ LANGUAGE plpgsql;

4. Full Text Search​

-- Adicionar coluna de busca
ALTER TABLE operational.occurrences
ADD COLUMN search_vector tsvector;

-- Atualizar vetor de busca
UPDATE operational.occurrences o
SET search_vector = to_tsvector('portuguese',
    COALESCE(o.code, '') || ' ' ||
    COALESCE(od.description, '') || ' ' ||
    COALESCE(od.observations, '')
)
FROM operational.occurrence_details od
WHERE od.occurrence_id = o.id;

-- Índice GIN para busca
CREATE INDEX idx_occurrences_search ON operational.occurrences USING gin(search_vector);

-- Trigger para manter atualizado
CREATE TRIGGER update_occurrence_search
    BEFORE INSERT OR UPDATE ON operational.occurrences
    FOR EACH ROW EXECUTE FUNCTION tsvector_update_trigger(search_vector, 'pg_catalog.portuguese', code);

5. JSONB para Dados Flexíveis​

-- Índices para campos JSONB
CREATE INDEX idx_users_metadata_email ON identity.users ((metadata->>'secondary_email'));
CREATE INDEX idx_occurrences_custom ON operational.occurrence_details USING gin(custom_fields);

-- Queries otimizadas
SELECT * FROM identity.users
WHERE metadata @> '{"role": "specialist"}';

SELECT * FROM operational.occurrence_details
WHERE custom_fields @> '{"urgency": "high"}';

📈 Estratégia de Migração​

Fase 1: Preparação (2 semanas)​

  1. Análise de Dependências

    • Mapear todas as aplicações conectadas
    • Identificar queries críticas
    • Documentar processos de negócio

  2. Ambiente de Testes

    • Setup PostgreSQL com nova estrutura
    • Migração de dados de teste
    • Validação de integridade

Fase 2: Migração Gradual (4-6 semanas)​

  1. Migração por Domínios

    • Começar com domínios menos críticos (media, audit)
    • Migrar identity e geographic
    • Por último, operational e financial

  2. Dual Write Strategy

    • Escrever em ambos bancos temporariamente
    • Validar consistência
    • Migrar leitura gradualmente

Fase 3: Cut-over (1 semana)​

  1. Migração Final

    • Freeze do banco antigo
    • Migração final de dados
    • Switch de aplicações

  2. Validação

    • Testes de integridade
    • Performance benchmarks
    • Rollback plan se necessário

🔧 Scripts de Migração​

Script de Migração de Usuários​

-- Migrar usuários do schema antigo
INSERT INTO identity.users (
    id,
    username,
    email,
    phone,
    status,
    metadata,
    created_at,
    updated_at
)
SELECT
    gen_random_uuid(),
    LOWER(username),
    LOWER(email),
    phone,
    CASE
        WHEN active = true THEN 'ACTIVE'::user_status
        ELSE 'SUSPENDED'::user_status
    END,
    jsonb_build_object(
        'legacy_id', id,
        'legacy_role', role,
        'migrated_at', NOW()
    ),
    created,
    modified
FROM old_schema.users;

Script de Migração de Ocorrências​

-- Migrar ocorrências principais
WITH migrated_occurrences AS (
    INSERT INTO operational.occurrences (
        code,
        contract_id,
        service_id,
        client_id,
        status,
        priority,
        type,
        created_at,
        updated_at
    )
    SELECT
        protocol,
        contract_id,
        service_id,
        client_enterprise_id,
        CASE status
            WHEN 'OPENED' THEN 'CREATED'::occurrence_status
            WHEN 'ACCEPTED' THEN 'ACCEPTED'::occurrence_status
            -- ... outros mapeamentos
        END,
        CASE priority
            WHEN 1 THEN 'LOW'::priority_level
            WHEN 2 THEN 'MEDIUM'::priority_level
            WHEN 3 THEN 'HIGH'::priority_level
            ELSE 'MEDIUM'::priority_level
        END,
        'STANDARD'::occurrence_type,
        created,
        modified
    FROM old_schema.occurrences
    RETURNING id, code
)
-- Migrar detalhes
INSERT INTO operational.occurrence_details (
    occurrence_id,
    description,
    observations,
    internal_notes
)
SELECT
    m.id,
    o.description,
    o.observation,
    o.internal_observation
FROM old_schema.occurrences o
JOIN migrated_occurrences m ON m.code = o.protocol;

📊 Diagramas​

Diagrama ER Principal​

Diagrama de Fluxo de Status de Ocorrências​

Arquitetura de Microsserviços Sugerida​

🎯 Benefícios da Nova Estrutura​

Performance​

  • 50-70% de redução no tempo de queries complexas
  • Índices otimizados para padrões de acesso comuns
  • Particionamento de tabelas grandes (audit, histórico)
  • Cache materializado para relatórios

Escalabilidade​

  • Sharding ready com UUIDs
  • Microserviços ready com domínios separados
  • Event sourcing preparado com timeline
  • CQRS possível com views materializadas

Integridade​

  • Constraints no banco de dados
  • Enums nativos para validação
  • Foreign keys com cascata apropriada
  • Triggers para regras de negócio

Manutenibilidade​

  • Código mais limpo com tabelas menores
  • Menos duplicação de dados
  • JSONB para extensibilidade
  • Documentação integrada no schema

Segurança​

  • RLS para isolamento de dados
  • Auditoria automática com triggers
  • Criptografia de dados sensíveis
  • Backup incremental facilitado

📋 Checklist de Implementação​

  • Revisar e aprovar design com stakeholders
  • Configurar ambiente PostgreSQL de desenvolvimento
  • Implementar schemas e tabelas base
  • Criar funções e triggers de auditoria
  • Desenvolver scripts de migração
  • Implementar testes de integridade
  • Criar procedures de backup/restore
  • Documentar APIs de acesso
  • Treinar equipe de desenvolvimento
  • Executar migração piloto
  • Monitorar performance pós-migração
  • Otimizar queries baseado em uso real

🔍 Monitoramento e Observabilidade​

Métricas Importantes​

-- View para monitorar performance
CREATE VIEW analytics.database_metrics AS
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Query para identificar queries lentas
CREATE VIEW analytics.slow_queries AS
SELECT
    query,
    calls,
    total_time,
    mean_time,
    max_time,
    stddev_time
FROM pg_stat_statements
WHERE mean_time > 100
ORDER BY mean_time DESC
LIMIT 50;

📚 Documentação Adicional​

Convenções de Nomenclatura​

  • Tabelas: snake_case, plural
  • Colunas: snake_case
  • Índices: idxTABELACAMPOS
  • Constraints: TIPO_TABELA_DESC
  • Enums: snake_case
  • Schemas: singular, lowercase

Padrões de Desenvolvimento​

  • Sempre usar UUIDs como PKs
  • Timestamps com timezone (TIMESTAMPTZ)
  • Soft delete com deleted_at
  • Versionamento com updated_at
  • Metadados em JSONB
  • Auditoria automática em tabelas críticas

Ferramentas Recomendadas​

  • ORM: Prisma com PostgreSQL adapter
  • Migration: Flyway ou Liquibase
  • Monitoring: pgAdmin, Datadog, Grafana
  • Backup: pgBackRest, Barman
  • Replication: PostgreSQL native streaming
  • Connection Pool: PgBouncer

🚨 Considerações Finais​

Esta reestruturação representa uma modernização significativa da arquitetura de dados, alinhada com as melhores práticas atuais e preparada para crescimento futuro. A implementação deve ser feita de forma gradual e com testes extensivos em cada fase.

Próximos Passos:

  1. Validar proposta com equipe técnica
  2. Criar POC com subset de dados
  3. Definir cronograma detalhado
  4. Iniciar implementação por fases

Documento preparado por: Análise Automatizada Data: 2025-08-21 Versão: 1.0