Design de Schema de Banco de Dados: Padrões Que Envelhecem Bem

Um schema é um contrato com o seu eu do futuro. Cada coluna que você adiciona, cada restrição que pula, cada "vamos arrumar isso depois" — tudo se acumula. Já trabalhei em sistemas onde uma única decisão ruim de schema feita três anos atrás agora custa à equipe uma sprint inteira a cada trimestre em soluções alternativas.

O banco de dados sobrevive a tudo. Seu framework de frontend vai mudar. Sua camada de API vai ser reescrita. Sua estratégia de deploy vai evoluir. Mas os dados? Os dados ficam. E o formato que você deu a eles no primeiro dia te acompanha para sempre, porque migrar uma tabela com 200 milhões de linhas não é a mesma coisa que refatorar um componente React.

Isso é o que aprendi sobre tomar decisões de schema que não te assombram. Focado em PostgreSQL, porque é o que uso e confio, mas a maioria desses padrões se aplica a qualquer banco de dados relacional.

Convenções de Nomenclatura: A Coisa Entediante Que Mais Importa#

Já vi mais discussões sobre convenções de nomenclatura do que sobre decisões reais de arquitetura. Aqui está o que adotei depois de anos alternando entre projetos:

Snake_case para tudo. Tabelas, colunas, índices, restrições. Nada de camelCase, nada de PascalCase. O PostgreSQL converte identificadores sem aspas para minúsculas de qualquer forma, então createdAt vira createdat a menos que você use aspas duplas em todo lugar. Não lute contra o banco de dados.

Nomes de tabela no plural. Uma tabela contém muitas linhas. users e não user. orders e não order. order_items e não order_item. Isso lê naturalmente em queries: SELECT * FROM users WHERE ... — você está selecionando de uma coleção.

Sufixo _id para chaves estrangeiras. user_id, order_id, tenant_id. A chave primária é apenas id. Isso é inequívoco. Quando você faz join de tabelas, users.id = orders.user_id lê como uma frase natural.

Sufixo _at para timestamps. created_at, updated_at, deleted_at, published_at, expires_at. Você sempre sabe que é um ponto no tempo.

Prefixo is_ para booleanos. is_active, is_verified, is_published. Algumas pessoas usam has_ para booleanos de posse (has_mfa_enabled), mas eu mantenho simples e uso is_ para tudo.

Sufixo _count para contadores desnormalizados. comment_count, follower_count. Deixa claro que é um número em cache, não um cálculo em tempo real.

Eis o ponto: consistência vence perfeição. Já vi equipes gastarem semanas debatendo se deveria ser email_address ou email ou email_addr. Apenas escolha um padrão e aplique em todo lugar. A pior convenção de nomenclatura é aquela que é aplicada de forma inconsistente.

sql

-- Bom: consistente, legível, sem surpresas
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL,
    display_name    TEXT NOT NULL,
    password_hash   TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    is_verified     BOOLEAN NOT NULL DEFAULT false,
    login_count     INTEGER NOT NULL DEFAULT 0,
    last_login_at   TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Ruim: casing inconsistente, convenções misturadas, nomes ambíguos
CREATE TABLE User (
    UserID          SERIAL PRIMARY KEY,
    EmailAddress    VARCHAR(255),
    name            VARCHAR(100),
    passwd          VARCHAR(255),
    active          BOOLEAN,
    verified        INT,             -- isso é 0/1? um nível de verificação?
    lastLogin       TIMESTAMP,       -- camelCase em um mundo snake_case
    created         TIMESTAMP        -- criou o quê? quando?
);

Mais uma coisa: nunca use palavras reservadas como nomes de coluna. type, order, user, group, table — todos funcionam se você usar aspas, mas vão te queimar em ORMs, query builders e toda ferramenta de geração dinâmica de SQL. Use kind em vez de type, sort_order em vez de order. Seu eu do futuro vai agradecer.

As Colunas Padrão: O Que Toda Tabela Recebe#

Toda tabela nos meus schemas começa com o mesmo esqueleto. Sem exceções.

sql

CREATE TABLE things (
    id          BIGSERIAL PRIMARY KEY,
    -- ... colunas de domínio ...
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

O Debate UUID vs BIGSERIAL#

Essa é uma daquelas decisões que gera muito mais calor do que luz. Aqui está o trade-off real:

BIGSERIAL (inteiro auto-incrementante):

8 bytes. Compacto. Rápido para indexar e fazer join.
Ordenado — você pode ordenar por id para obter a ordem de inserção.
Previsível — um usuário pode adivinhar outros IDs incrementando o seu.
Não funciona bem em sistemas distribuídos (requer coordenação).

UUID v4 (aleatório):

16 bytes. Índices maiores, joins mais lentos (mas raramente o gargalo).
Imprevisível — sem vazamento de informação.
Funciona em sistemas distribuídos sem coordenação.
Localidade de índice terrível — UUIDs aleatórios fragmentam índices B-tree.

UUID v7 (ordenado por tempo, RFC 9562):

16 bytes, mas ordenado por tempo, então a localidade B-tree é excelente.
Imprevisível o suficiente para uso externo.
Relativamente novo, mas PostgreSQL 17+ tem gen_random_uuid() e você pode usar uuid_generate_v7() com extensões.

Minha posição atual: BIGSERIAL para tabelas internas, UUID v7 para qualquer coisa exposta ao mundo externo. Se um ID aparece em uma URL, uma resposta de API ou um payload de webhook, use UUIDs. Se é uma tabela de join pura que os usuários nunca veem, BIGSERIAL é suficiente.

sql

-- Para um recurso voltado para API
CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    owner_id    UUID NOT NULL REFERENCES users(id),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Para uma tabela de mapeamento interna
CREATE TABLE project_members (
    id          BIGSERIAL PRIMARY KEY,
    project_id  UUID NOT NULL REFERENCES projects(id),
    user_id     UUID NOT NULL REFERENCES users(id),
    role        TEXT NOT NULL DEFAULT 'member',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (project_id, user_id)
);

Timestamps: Sempre TIMESTAMPTZ#

Use TIMESTAMPTZ, não TIMESTAMP. A versão "TZ" armazena o valor em UTC e converte na leitura com base no fuso horário da sessão. A versão sem TZ armazena o que você der sem contexto de fuso horário — o que significa que se dois servidores em fusos horários diferentes escreverem na mesma tabela, você tem corrupção silenciosa de dados.

sql

-- Sempre isso
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 
-- Nunca isso
created_at  TIMESTAMP NOT NULL DEFAULT NOW()

O Trigger de updated_at#

O PostgreSQL não tem o ON UPDATE CURRENT_TIMESTAMP do MySQL. Você precisa de um trigger:

sql

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Aplicar a cada tabela que tem updated_at
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
 
CREATE TRIGGER set_updated_at
    BEFORE UPDATE ON projects
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

Sim, você precisa de um trigger por tabela. Sim, é tedioso. Escreva um helper de migração que cria o trigger automaticamente quando você adiciona uma tabela. Vale a pena, porque a alternativa é lembrar de definir updated_at = NOW() em cada UPDATE query em toda a sua aplicação — e você vai esquecer.

Normalização: Quando Quebrar as Regras#

Todo curso de Ciência da Computação ensina normalização até a 3FN (Terceira Forma Normal). As regras são:

1FN: Cada coluna contém um único valor atômico. Sem arrays, sem listas separadas por vírgula.
2FN: Cada coluna não-chave depende da chave primária inteira (relevante para chaves compostas).
3FN: Sem dependências transitivas. Se a coluna A determina a coluna B, e B determina C, então C não deveria estar na mesma tabela que A.

Na prática, 3FN é o ponto ideal para tabelas transacionais. Você deve começar aí e desviar apenas quando tiver uma razão específica e mensurável.

Aqui está um sistema de pedidos devidamente normalizado:

sql

CREATE TABLE customers (
    id              BIGSERIAL PRIMARY KEY,
    email           TEXT NOT NULL UNIQUE,
    display_name    TEXT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    sku             TEXT NOT NULL UNIQUE,
    name            TEXT NOT NULL,
    price_cents     INTEGER NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE orders (
    id              BIGSERIAL PRIMARY KEY,
    customer_id     BIGINT NOT NULL REFERENCES customers(id),
    status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    total_cents     INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE order_items (
    id              BIGSERIAL PRIMARY KEY,
    order_id        BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id      BIGINT NOT NULL REFERENCES products(id),
    quantity        INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents INTEGER NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Note unit_price_cents em order_items. Isso é desnormalização intencional. Fazemos um snapshot do preço no momento do pedido, porque o preço do produto pode mudar depois. Esta é uma das razões mais comuns e corretas para desnormalizar.

Quando a Desnormalização É a Decisão Certa#

Tabelas de relatórios. Se seu dashboard de analytics precisa fazer join de 8 tabelas para renderizar, crie uma tabela de relatórios desnormalizada e popule-a com um job em background. Seu schema transacional fica limpo, e suas queries de relatórios ficam rápidas.

sql

-- Tabela de relatórios desnormalizada, populada por um cron job
CREATE TABLE order_reports (
    id                  BIGSERIAL PRIMARY KEY,
    order_id            BIGINT NOT NULL,
    customer_email      TEXT NOT NULL,
    customer_name       TEXT NOT NULL,
    product_names       TEXT[] NOT NULL,        -- array é ok aqui
    item_count          INTEGER NOT NULL,
    total_cents         INTEGER NOT NULL,
    order_status        TEXT NOT NULL,
    ordered_at          TIMESTAMPTZ NOT NULL,
    report_generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Visões materializadas. A MATERIALIZED VIEW do PostgreSQL é subestimada. É um snapshot desnormalizado que você atualiza sob demanda. Perfeito para dashboards.

sql

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(o.total_cents) AS revenue_cents,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM orders o
WHERE o.status != 'cancelled'
GROUP BY DATE_TRUNC('month', o.created_at)
ORDER BY month DESC;
 
-- Atualizar diariamente
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Colunas JSON para atributos dinâmicos. Quando diferentes linhas precisam de campos diferentes — variantes de produtos, submissões de formulários, preferências de usuário — uma coluna JSONB é frequentemente melhor que uma tabela EAV (Entity-Attribute-Value) pesadelo. Mais sobre isso adiante.

A Regra Que Eu Nunca Quebro#

Nunca desnormalize suas tabelas fonte-da-verdade. Desnormalize cópias, snapshots, relatórios e caches. Os dados canônicos ficam normalizados. Quando a cópia desnormalizada ficar desatualizada ou corrompida (e vai), você a reconstrói a partir da fonte normalizada.

Chaves Estrangeiras e Restrições: O Melhor Código Que Você Nunca Vai Escrever#

Já ouvi toda desculpa para pular chaves estrangeiras. "Elas tornam as escritas mais lentas." "Nós aplicamos na aplicação." "Precisamos de flexibilidade."

Estão todas erradas.

Chaves estrangeiras são a coisa mais impactante que você pode adicionar a um schema. Elas previnem categorias inteiras de bugs que nenhuma quantidade de código de aplicação consegue capturar — condições de corrida, falhas parciais, linhas órfãs de transações falhadas. Uma chave estrangeira é uma garantia do próprio motor do banco de dados, aplicada no nível de armazenamento. O hook beforeDelete do seu ORM é uma sugestão.

sql

-- Sempre faça isso
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- A alternativa "vamos tratar no código":
-- Esperança. Esperança não é uma estratégia.

Estratégias de ON DELETE#

É aqui que fica nuançado. O que acontece quando você deleta uma linha pai?

RESTRICT (padrão): O delete falha se existirem linhas filhas. Use isso para a maioria dos relacionamentos. Você não pode deletar um cliente que tem pedidos — isso é lógica de negócio codificada no schema.

sql

-- Cliente não pode ser deletado enquanto tiver pedidos
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;

CASCADE: Linhas filhas são automaticamente deletadas. Use com moderação e deliberadamente. Bom para relacionamentos "parte-de" onde o filho não tem significado sem o pai.

sql

-- Deletar um pedido deleta seus itens — são insignificantes sozinhos
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE;
 
-- Deletar um projeto deleta suas associações
ALTER TABLE project_members
    ADD CONSTRAINT fk_project_members_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE;

SET NULL: A coluna de chave estrangeira é definida como NULL. Use quando o relacionamento é opcional e a linha filha ainda é significativa sozinha.

sql

-- Se um gerente sai, seus subordinados ainda existem — apenas sem atribuição
ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id)
    ON DELETE SET NULL;

Minha regra geral: padrão RESTRICT, use CASCADE para relacionamentos de composição, use SET NULL para associações opcionais. Se estiver em dúvida, RESTRICT é sempre a escolha segura — é mais fácil relaxar uma restrição do que recuperar dados deletados.

Restrições Check: Seguro Barato#

Restrições check custam quase nada no momento da escrita e impedem dados lixo de entrar no seu sistema para sempre:

sql

CREATE TABLE products (
    id              BIGSERIAL PRIMARY KEY,
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    price_cents     INTEGER NOT NULL CHECK (price_cents >= 0),
    discount_pct    NUMERIC(5,2) CHECK (discount_pct BETWEEN 0 AND 100),
    weight_kg       NUMERIC(10,3) CHECK (weight_kg > 0),
    status          TEXT NOT NULL CHECK (status IN ('draft', 'active', 'archived')),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Cada restrição check que você adiciona é um bug de validação a menos que você precisará debugar em produção. O banco de dados é a última linha de defesa. Use-o.

Restrições Unique e Índices Unique Parciais#

Restrições unique são diretas para casos simples:

sql

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Mas ficam interessantes com índices parciais — restrições unique que se aplicam apenas a certas linhas:

sql

-- Apenas uma assinatura ativa por usuário (mas podem ter muitas canceladas)
CREATE UNIQUE INDEX uq_active_subscription
    ON subscriptions (user_id)
    WHERE status = 'active';
 
-- Apenas um endereço principal por usuário
CREATE UNIQUE INDEX uq_primary_address
    ON addresses (user_id)
    WHERE is_primary = true;

Este é um dos recursos matadores do PostgreSQL. Use-o.

Soft Deletes: O Padrão Que Todo Mundo Ama Odiar#

Soft deletes são simples em conceito: em vez de DELETE FROM users WHERE id = 42, você faz UPDATE users SET deleted_at = NOW() WHERE id = 42. A linha permanece no banco de dados mas é filtrada das queries normais.

sql

CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT NOT NULL,
    display_name TEXT NOT NULL,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- O índice parcial: filtra linhas deletadas eficientemente
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

Cada query na sua aplicação agora precisa de WHERE deleted_at IS NULL. Cada. Uma. Delas. ORMs ajudam com isso (Prisma tem middleware, Drizzle tem .where(isNull(deletedAt))), mas é um imposto sobre cada operação de leitura. Esqueça uma vez e você está mostrando dados "deletados" para os usuários.

O Problema da Restrição Unique#

Aqui é onde soft deletes ficam feios. Se você tem UNIQUE (email) e um usuário faz soft delete da conta, o email ainda está na tabela. Ele não pode se re-registrar com o mesmo email. Um novo usuário com esse email também não pode se cadastrar.

A solução é um índice unique parcial:

sql

-- Email deve ser único, mas apenas entre usuários não deletados
CREATE UNIQUE INDEX uq_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

Isso funciona, mas agora você precisa lembrar desse padrão para cada coluna unique em cada tabela com soft delete. É gerenciável com disciplina, mas é complexidade que você escolheu assumir.

O Problema da Chave Estrangeira#

Soft deletes interagem mal com chaves estrangeiras. Se orders.user_id referencia users.id com ON DELETE RESTRICT, e você faz soft delete de um usuário... nada acontece. A FK não dispara porque você não deletou a linha de verdade. O usuário está "ausente" na perspectiva da aplicação mas ainda muito presente no banco de dados.

Isso significa que o código da sua aplicação tem que lidar com o caso onde uma entidade referenciada foi soft-deletada. Cada join, cada lookup, cada vez que você segue uma chave estrangeira — você precisa checar deleted_at IS NULL na tabela referenciada também. Ou não checa, e sua aplicação mostra "Pedido por [usuário deletado]" o que é um bug ou uma feature dependendo de quem você perguntar.

Minha Abordagem#

Eu uso soft deletes apenas para entidades voltadas ao usuário onde a recuperação é um requisito de negócio — contas de usuário, projetos, documentos. Coisas onde um agente de suporte pode precisar restaurar uma deleção. Para todo o resto, uso hard deletes com uma trilha de auditoria (mais sobre isso a seguir).

sql

-- Soft delete: voltado ao usuário, recuperável
CREATE TABLE documents (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title       TEXT NOT NULL,
    content     TEXT NOT NULL,
    owner_id    UUID NOT NULL REFERENCES users(id),
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_documents_active ON documents (owner_id) WHERE deleted_at IS NULL;
 
-- Hard delete: interno, não recuperável pela UI (mas auditado)
CREATE TABLE api_keys (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    key_hash    TEXT NOT NULL,
    last_used_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Quando deletada, sumiu. O audit_log registra que existiu.

Trilhas de Auditoria: Saiba O Que Mudou e Quem Fez#

Toda aplicação não trivial precisa de uma resposta para "o que aconteceu com este registro?" em algum momento. Trilhas de auditoria são como você fornece essa resposta sem vasculhar logs da aplicação.

O Padrão de Tabela de Auditoria Separada#

A abordagem mais simples: uma única tabela audit_log que registra cada mudança.

sql

CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,          -- text para lidar com PKs tanto UUID quanto BIGINT
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,                  -- NULL para INSERT
    new_values      JSONB,                  -- NULL para DELETE
    changed_fields  TEXT[],                 -- quais colunas mudaram (para UPDATE)
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    ip_address      INET,
    user_agent      TEXT
);
 
CREATE INDEX idx_audit_log_table_record ON audit_log (table_name, record_id);
CREATE INDEX idx_audit_log_changed_by ON audit_log (changed_by);
CREATE INDEX idx_audit_log_changed_at ON audit_log (changed_at);

O desafio é popular essa tabela. Você pode fazer no código da aplicação (explícito, mas fácil de esquecer) ou com triggers (automático, mas mais difícil de passar contexto como changed_by).

A Abordagem Baseada em Triggers#

Triggers capturam cada mudança automaticamente, inclusive de SQL direto ou operações de administração do banco:

sql

CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    old_data JSONB;
    new_data JSONB;
    changed  TEXT[];
    col      TEXT;
BEGIN
    IF TG_OP = 'DELETE' THEN
        old_data := to_jsonb(OLD);
        INSERT INTO audit_log (table_name, record_id, operation, old_values, changed_at)
        VALUES (TG_TABLE_NAME, OLD.id::TEXT, 'DELETE', old_data, NOW());
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        old_data := to_jsonb(OLD);
        new_data := to_jsonb(NEW);
        -- Encontrar campos alterados
        FOR col IN SELECT key FROM jsonb_each(new_data)
        LOOP
            IF old_data->col IS DISTINCT FROM new_data->col THEN
                changed := array_append(changed, col);
            END IF;
        END LOOP;
        -- Só registrar se algo realmente mudou
        IF array_length(changed, 1) > 0 THEN
            INSERT INTO audit_log (table_name, record_id, operation, old_values, new_values, changed_fields, changed_at)
            VALUES (TG_TABLE_NAME, NEW.id::TEXT, 'UPDATE', old_data, new_data, changed, NOW());
        END IF;
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        new_data := to_jsonb(NEW);
        INSERT INTO audit_log (table_name, record_id, operation, new_values, changed_at)
        VALUES (TG_TABLE_NAME, NEW.id::TEXT, 'INSERT', new_data, NOW());
        RETURN NEW;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
-- Aplicar às tabelas que você quer auditar
CREATE TRIGGER audit_users
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();
 
CREATE TRIGGER audit_orders
    AFTER INSERT OR UPDATE OR DELETE ON orders
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

O lado negativo: triggers não sabem qual usuário da aplicação fez a mudança. Você pode contornar isso com variáveis de sessão:

sql

-- Na sua aplicação, antes da query:
SET LOCAL app.current_user_id = 'uuid-of-current-user';
 
-- Na função trigger:
changed_by_id := current_setting('app.current_user_id', true)::UUID;

Isso funciona mas parece frágil. Na prática, uso uma abordagem híbrida: triggers para a captura de dados, e código de aplicação para definir o contexto da sessão.

O Padrão de Tabela de Histórico#

Para tabelas onde você precisa do histórico completo de versões (não apenas "o que mudou" mas "qual era o estado no tempo T"), uma tabela de histórico dedicada é mais limpa:

sql

CREATE TABLE documents (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title           TEXT NOT NULL,
    content         TEXT NOT NULL,
    version         INTEGER NOT NULL DEFAULT 1,
    owner_id        UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE document_history (
    id              BIGSERIAL PRIMARY KEY,
    document_id     UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    title           TEXT NOT NULL,
    content         TEXT NOT NULL,
    version         INTEGER NOT NULL,
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (document_id, version)
);

Antes de cada atualização em documents, copie o estado atual para document_history e incremente a versão. Agora você pode reconstruir o documento em qualquer ponto no tempo, mostrar diffs entre versões e até restaurar versões antigas.

O trade-off é armazenamento. Se sua coluna content é grande e muda frequentemente, a tabela de histórico pode crescer rápido. Para a maioria das aplicações, isso é aceitável — armazenamento é barato e você pode arquivar versões antigas em cold storage se necessário.

Multi-Tenancy: Três Abordagens, Escolha Sua Dor#

Multi-tenancy é uma daquelas coisas que é fácil de adicionar no início e quase impossível de adicionar depois. Se há qualquer chance de sua aplicação servir múltiplas organizações, construa desde o primeiro dia.

Row-Level: tenant_id em Cada Tabela#

A abordagem mais comum. Cada tabela tem uma coluna tenant_id, e cada query filtra por ela.

sql

CREATE TABLE tenants (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    slug        TEXT NOT NULL UNIQUE,
    plan        TEXT NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TABLE tasks (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    project_id  UUID NOT NULL REFERENCES projects(id),
    title       TEXT NOT NULL,
    status      TEXT NOT NULL DEFAULT 'todo',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Cada índice deve incluir tenant_id para performance de query
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);

O risco é vazamento de dados — um WHERE tenant_id = ... esquecido e você está mostrando dados do Tenant A para o Tenant B. A Row-Level Security (RLS) do PostgreSQL elimina essa classe de bugs:

sql

-- Habilitar RLS na tabela
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- Criar uma política baseada em variável de sessão
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Forçar RLS mesmo para donos da tabela
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Agora, mesmo que o código da sua aplicação esqueça a cláusula WHERE tenant_id = ..., o PostgreSQL a adiciona automaticamente. Isso é defesa em profundidade, e é um dos argumentos mais fortes para PostgreSQL em sistemas multi-tenant.

sql

-- No middleware de conexão da sua aplicação:
SET LOCAL app.current_tenant_id = 'tenant-uuid-here';
 
-- Agora todas as queries em tabelas com RLS são filtradas automaticamente
SELECT * FROM projects;
-- PostgreSQL adiciona internamente: WHERE tenant_id = 'tenant-uuid-here'

Prós: Banco de dados único, operações simples, uso eficiente de recursos, queries cross-tenant fáceis para admin. Contras: Requer disciplina (ou RLS), cada query toca tenant_id, mais difícil dar a tenants seu próprio backup/restore.

Schema-Per-Tenant#

Cada tenant recebe seu próprio schema PostgreSQL. Todos os schemas compartilham o mesmo banco de dados, mas as tabelas são isoladas por namespace.

sql

-- Criar um schema para cada tenant
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
 
-- Tabelas vivem no schema do tenant
CREATE TABLE tenant_acme.projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Definir o search_path para o tenant atual
SET search_path TO tenant_acme, public;
 
-- Agora queries sem qualificação acessam o schema do tenant
SELECT * FROM projects;  -- consulta tenant_acme.projects

Prós: Isolamento forte, sem risco de vazamento de dados cross-tenant, backup/restore fácil por tenant, tenants podem ter variações de schema. Contras: Complexidade de migração de schema (você precisa migrar N schemas), gerenciamento de pool de conexões, PostgreSQL tem limites práticos em torno de ~10.000 schemas.

Database-Per-Tenant#

Cada tenant recebe seu próprio banco de dados. Isolamento máximo.

Prós: Isolamento completo, escala independente, backup/restore fácil, pode colocar tenants grandes em hardware dedicado. Contras: Pesadelo de gerenciamento de conexões, queries cross-tenant impossíveis, migração deve rodar N vezes, overhead operacional significativo.

Qual Escolher?#

Para a maioria das aplicações SaaS: comece com row-level + RLS. É o mais simples de operar, e RLS dá isolamento suficiente para a grande maioria dos casos de uso. Mude para schema-per-tenant apenas se tiver requisitos contratuais de isolamento (clientes enterprise, indústrias reguladas). Database-per-tenant é para quando você absolutamente deve garantir isolamento físico — e mesmo assim, considere bancos de dados gerenciados onde o peso operacional é tratado por você.

Colunas JSON/JSONB: A Saída de Emergência#

O JSONB do PostgreSQL é notável. Ele te dá flexibilidade de banco de documentos dentro de um sistema relacional. Mas como qualquer ferramenta poderosa, é fácil de abusar.

Quando JSONB É a Escolha Certa#

Atributos dinâmicos definidos pelo usuário. Uma plataforma de e-commerce onde cada categoria de produto tem atributos diferentes — sapatos têm size e color, eletrônicos têm voltage e wattage. Em vez de uma tabela EAV ou uma coluna para cada atributo possível:

sql

CREATE TABLE products (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL,
    category_id     UUID NOT NULL REFERENCES categories(id),
    price_cents     INTEGER NOT NULL CHECK (price_cents >= 0),
    attributes      JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- Dados de exemplo:
-- Sapato: {"size": "10", "color": "black", "material": "leather"}
-- Laptop: {"ram_gb": 16, "storage_gb": 512, "screen_inches": 14.0}

Configurações e preferências da aplicação. Preferências de usuário, feature flags, configurações de notificação — coisas que mudam de forma frequentemente e não precisam de integridade relacional.

sql

CREATE TABLE user_settings (
    user_id     UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    preferences JSONB NOT NULL DEFAULT '{
        "theme": "system",
        "notifications": {"email": true, "push": false},
        "locale": "en",
        "timezone": "UTC"
    }',
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Payloads de webhook, respostas de API, dados de eventos. Qualquer coisa onde o schema é controlado por um sistema externo e pode mudar sem aviso.

Índices GIN em JSONB#

Sem índices, consultar dentro de JSONB requer um scan completo da tabela. Índices GIN tornam rápido:

sql

-- Indexar a coluna JSONB inteira (bom para queries de contenção @>)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- Query: encontrar todos os produtos com color = "black"
SELECT * FROM products
WHERE attributes @> '{"color": "black"}';
 
-- Query: encontrar produtos com qualquer um desses atributos
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'size'];

Para caminhos específicos que você consulta frequentemente, um índice direcionado é mais eficiente:

sql

-- Indexar um caminho específico
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
 
-- Agora é um lookup B-tree regular
SELECT * FROM products
WHERE attributes->>'color' = 'black';

A Armadilha da Migração#

Aqui é onde JSONB morde: você não pode facilmente adicionar restrições NOT NULL ou valores padrão a campos dentro de JSON. Se você adiciona um novo campo obrigatório aos atributos do produto, precisa fazer backfill em cada linha existente. Com uma coluna regular, uma migração lida com isso atomicamente. Com JSONB, você está escrevendo um UPDATE que toca cada linha e torcendo para que o código da sua aplicação lide com campos faltantes graciosamente até o backfill completar.

sql

-- Adicionando uma nova coluna: limpo, atômico, uma declaração
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(10,3);
 
-- Adicionando um novo campo JSONB: confuso, requer backfill
UPDATE products
SET attributes = attributes || '{"weight_kg": null}'
WHERE NOT (attributes ? 'weight_kg');
-- Mais atualizar código da aplicação para lidar com weight_kg faltante
-- Mais atualizar lógica de validação
-- Mais atualizar cada resposta de API que inclui attributes

Minha regra: se você está consultando um campo JSONB em uma cláusula WHERE mais de duas vezes por semana, provavelmente deveria ser uma coluna. JSONB é uma ótima saída de emergência. É um padrão terrível.

Migrações de Schema: Não Quebre Produção às 3 da Manhã#

Migrações de schema são onde a teoria encontra a realidade. Seu schema parece ótimo no papel, mas agora você precisa alterar uma tabela com 50 milhões de linhas durante o horário comercial sem nenhum downtime.

Ferramentas de Migração#

Já usei a maioria delas. Opiniões breves:

Drizzle (TypeScript): Meu favorito atual. Schema-as-code, queries type-safe geradas a partir do schema, SQL de migração limpo. O comando push para desenvolvimento é rápido.

Prisma (TypeScript): Ótima DX para schemas simples. Tem dificuldade com recursos avançados do PostgreSQL (índices parciais, tipos customizados, RLS). O motor de migração pode tomar decisões surpreendentes.

Flyway (Java/CLI): Sólido como rocha, testado em batalha, SQL-first. Se você está escrevendo migrações SQL puras, Flyway as rastreia de forma confiável. Sem mágica, sem surpresas.

golang-migrate (Go/CLI): Similar ao Flyway mas mais leve. Ótimo para projetos Go ou quando você só quer um runner simples de migração up/down.

O Problema do Zero-Downtime#

As mudanças de schema mais perigosas são as que travam a tabela. No PostgreSQL, ALTER TABLE ... ADD COLUMN com um valor padrão costumava travar a tabela inteira durante a reescrita. Desde o PostgreSQL 11, padrões simples (constantes) são apenas metadados e instantâneos. Mas outras operações ainda travam:

sql

-- SEGURO: apenas metadados, instantâneo (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT false;
 
-- PERIGOSO: reescreve a tabela inteira, lock completo
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(500);
ALTER TABLE users ADD COLUMN score INTEGER NOT NULL DEFAULT compute_score();
 
-- PERIGOSO: escaneia a tabela inteira para validar
ALTER TABLE orders ADD CONSTRAINT check_total CHECK (total_cents >= 0);

Adicionando Colunas NOT NULL com Segurança#

Você não pode simplesmente adicionar NOT NULL a uma tabela existente com dados — falha porque linhas existentes não têm um valor. A abordagem ingênua:

sql

-- Isso trava a tabela e a reescreve. Não faça isso em uma tabela grande.
ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT 'https://example.com/default.png';

Desde o PostgreSQL 11, isso é realmente seguro para padrões constantes — é apenas metadados. Mas se seu padrão é uma função ou você precisa fazer backfill com valores calculados, use o padrão expand-contract.

O Padrão Expand-Contract#

Este é o padrão ouro para mudanças de schema com zero-downtime. Três fases:

Fase 1: Expandir — Adicione a nova coluna como nullable. Faça deploy do código da aplicação que escreve nas colunas antiga e nova.

sql

-- Migração 1: Adicionar a coluna nullable
ALTER TABLE users ADD COLUMN normalized_email TEXT;

Fase 2: Migrar — Faça backfill das linhas existentes em lotes. Sua aplicação já está escrevendo na nova coluna para dados novos.

sql

-- Migração 2: Backfill em lotes (não faça isso em uma declaração para tabelas grandes)
UPDATE users SET normalized_email = LOWER(TRIM(email))
WHERE normalized_email IS NULL
LIMIT 10000;
-- Repita até que todas as linhas estejam preenchidas

Fase 3: Contrair — Uma vez que todas as linhas estejam preenchidas, adicione a restrição NOT NULL e remova a coluna antiga (se aplicável).

sql

-- Migração 3: Adicionar restrição (use NOT VALID para evitar scan completo, depois valide separadamente)
ALTER TABLE users ADD CONSTRAINT users_normalized_email_not_null
    CHECK (normalized_email IS NOT NULL) NOT VALID;
 
-- Migração 4: Validar a restrição (usa ShareUpdateExclusiveLock, não AccessExclusiveLock)
ALTER TABLE users VALIDATE CONSTRAINT users_normalized_email_not_null;

O truque NOT VALID + VALIDATE CONSTRAINT é crucial. Adicionar uma restrição check normalmente escaneia a tabela inteira enquanto mantém um lock pesado. NOT VALID adiciona a restrição sem escanear (só se aplica a novas escritas), e VALIDATE CONSTRAINT escaneia com um lock mais leve que não bloqueia leituras nem escritas.

Criação de Índices#

Criar índices em tabelas grandes bloqueia escritas por padrão. Sempre use CONCURRENTLY:

sql

-- BLOQUEIA ESCRITAS: não faça isso em uma tabela em produção
CREATE INDEX idx_users_email ON users (email);
 
-- NÃO BLOQUEIA: use isso em vez
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLY demora mais e não pode rodar dentro de uma transação, mas não trava a tabela. O trade-off sempre vale a pena em produção.

As Decisões Das Quais Me Arrependi#

Todo schema tem arrependimentos. Aqui estão os meus.

Usar SERIAL Em Vez de UUID para IDs Externos#

No início de um projeto, usei chaves primárias SERIAL e as expus diretamente em URLs: /users/42, /orders/1337. Isso vazou informação (concorrentes podiam adivinhar nossa contagem de usuários), tornou a paginação adivinhável e quebrou quando precisamos fundir bancos de dados de duas regiões. Mudar para UUIDs para IDs voltados ao exterior exigiu uma migração de vários meses.

Lição: use UUIDs para qualquer coisa exposta fora do seu banco de dados. Use SERIAL/BIGSERIAL para tabelas de join internas se quiser, mas nunca deixe um inteiro auto-incrementante aparecer em uma URL.

Sem Restrições ("Vamos Adicionar Depois")#

Lançamos uma tabela sem restrições check porque "estamos indo rápido e vamos adicionar depois." Em duas semanas, os dados tinham preços negativos, nomes vazios e um valor de order_status de "oof" que alguém digitou durante testes e nunca limpou.

Adicionar restrições após o fato exigiu:

Encontrar todos os dados inválidos
Decidir o que fazer com eles (corrigir, deletar ou avosear)
Escrever uma migração que corrige os dados E adiciona a restrição

Isso demorou mais do que adicionar a restrição no primeiro dia teria demorado. Comece com restrições. Relaxe-as se precisar. Nunca o contrário.

Strings Separadas por Vírgula Em Vez de Arrays ou Tabelas de Join#

Uma vez armazenei tags como uma string separada por vírgula: "javascript,react,nextjs". Consultar era um pesadelo:

sql

-- É assim que você consulta valores separados por vírgula. Não faça isso.
SELECT * FROM posts WHERE tags LIKE '%react%';
-- Isso também encontra "react-native" e "preact"
 
-- O que eu deveria ter feito:
CREATE TABLE post_tags (
    post_id     UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id      UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Ou no mínimo, use o tipo array nativo do PostgreSQL:

sql

CREATE TABLE posts (
    id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title   TEXT NOT NULL,
    tags    TEXT[] NOT NULL DEFAULT '{}',
    -- ...
);
 
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
 
-- Queries limpas
SELECT * FROM posts WHERE 'react' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['react', 'nextjs'];

Arrays são aceitáveis para listas simples que não precisam de seus próprios atributos. No momento em que você precisa de metadados no relacionamento (como "quem adicionou esta tag" ou "quando foi adicionada"), você precisa de uma tabela de join.

Usar "type" como Nome de Coluna#

sql

-- Parecia bom na hora
CREATE TABLE notifications (
    id      BIGSERIAL PRIMARY KEY,
    type    TEXT NOT NULL,  -- "email", "sms", "push"
    -- ...
);
 
-- Aí você tenta consultar em literalmente qualquer ORM:
-- notification.type  ← conflita com o sistema de tipos de toda linguagem
-- "type" é uma palavra reservada na maioria dos dialetos SQL
-- Você acaba usando aspas em todo lugar: SELECT "type" FROM notifications

Use kind, category, ou notification_type. Qualquer coisa menos type. Da mesma forma, evite order (use sort_order ou position), user (use account ou prefixe) e group (use team ou group_name).

Não Adicionar created_at Desde o Início#

Uma tabela não recebeu created_at porque "não precisamos." Três meses depois, precisamos debugar um problema e não tínhamos ideia de quando os registros foram criados. Adicioná-lo retroativamente significou que todas as linhas existentes receberam o mesmo timestamp (o timestamp da migração), tornando os dados históricos inúteis.

Toda tabela recebe created_at. Sem exceções. O custo é uma coluna. O custo de não ter é incognoscível até que você precise.

Colocar Lógica de Negócio em Views do Banco de Dados#

Uma vez criei uma cadeia de views — active_users filtrava users, premium_active_users filtrava active_users, e uma view de relatórios fazia join de todas. Funcionou ótimo até alguém mudar a tabela users e todas as três views quebraram silenciosamente. O planejador de queries também teve dificuldade para otimizar através de múltiplas camadas de views.

Views são ótimas para conveniência e padrões de acesso somente leitura. São terríveis como lugar para codificar lógica de negócio que muda. Mantenha regras de negócio no código da aplicação onde são versionadas, testadas e deployáveis. Use views para atalhos de relatórios, não como blocos de construção arquiteturais.

Juntando Tudo#

Aqui está como um schema bem projetado se parece quando você combina esses padrões. Um sistema simplificado de gerenciamento de projetos:

sql

-- Extensões
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- para gen_random_uuid()
 
-- Função trigger de updated_at (reutilizável)
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Tenants
CREATE TABLE tenants (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    slug            TEXT NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9-]+$'),
    plan            TEXT NOT NULL DEFAULT 'free'
                    CHECK (plan IN ('free', 'pro', 'enterprise')),
    settings        JSONB NOT NULL DEFAULT '{}',
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON tenants
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
-- Usuários
CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    email           TEXT NOT NULL,
    display_name    TEXT NOT NULL CHECK (LENGTH(display_name) > 0),
    password_hash   TEXT NOT NULL,
    is_active       BOOLEAN NOT NULL DEFAULT true,
    is_verified     BOOLEAN NOT NULL DEFAULT false,
    last_login_at   TIMESTAMPTZ,
    deleted_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE UNIQUE INDEX uq_users_email_per_tenant
    ON users (tenant_id, email)
    WHERE deleted_at IS NULL;
 
CREATE INDEX idx_users_tenant ON users (tenant_id) WHERE deleted_at IS NULL;
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
-- Habilitar RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Projetos
CREATE TABLE projects (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    name            TEXT NOT NULL CHECK (LENGTH(name) > 0),
    description     TEXT NOT NULL DEFAULT '',
    status          TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'archived', 'deleted')),
    owner_id        UUID NOT NULL REFERENCES users(id),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_projects_owner ON projects (owner_id);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON projects
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Tarefas
CREATE TABLE tasks (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    project_id      UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title           TEXT NOT NULL CHECK (LENGTH(title) > 0),
    description     TEXT NOT NULL DEFAULT '',
    status          TEXT NOT NULL DEFAULT 'todo'
                    CHECK (status IN ('todo', 'in_progress', 'review', 'done')),
    priority        INTEGER NOT NULL DEFAULT 0 CHECK (priority BETWEEN 0 AND 4),
    assignee_id     UUID REFERENCES users(id) ON DELETE SET NULL,
    due_at          TIMESTAMPTZ,
    completed_at    TIMESTAMPTZ,
    sort_order      INTEGER NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);
CREATE INDEX idx_tasks_assignee ON tasks (assignee_id) WHERE assignee_id IS NOT NULL;
CREATE INDEX idx_tasks_status ON tasks (tenant_id, status) WHERE status != 'done';
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON tasks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Comentários de tarefas
CREATE TABLE task_comments (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    task_id         UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    author_id       UUID NOT NULL REFERENCES users(id),
    body            TEXT NOT NULL CHECK (LENGTH(body) > 0),
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_task_comments_task ON task_comments (task_id);
 
CREATE TRIGGER set_updated_at BEFORE UPDATE ON task_comments
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
 
ALTER TABLE task_comments ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON task_comments
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Log de auditoria (sem RLS — tabela apenas para admin)
CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    tenant_id       UUID NOT NULL REFERENCES tenants(id),
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,
    operation       TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values      JSONB,
    new_values      JSONB,
    changed_fields  TEXT[],
    changed_by      UUID REFERENCES users(id),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
CREATE INDEX idx_audit_log_tenant_table ON audit_log (tenant_id, table_name, record_id);
CREATE INDEX idx_audit_log_changed_at ON audit_log (changed_at);

Note os padrões:

Toda tabela tem tenant_id e RLS habilitado (exceto tenants e audit_log).
Toda tabela tem created_at e updated_at com triggers.
UUIDs para todas as chaves primárias (entidades voltadas ao exterior).
Restrições check em enums de status, comprimentos e intervalos.
Chaves estrangeiras com comportamento ON DELETE apropriado.
Índices projetados para os padrões de acesso (tenant + projeto, tenant + status).
Soft deletes apenas em users (onde recuperação de conta é necessária), arquivamento baseado em status em projects.

Considerações Finais#

Design de schema não é glamouroso. Ninguém nunca deu uma palestra em conferência intitulada "Adicionei Restrições Check em Cada Tabela e Isso Nos Salvou Seis Meses de Debugging." Mas é exatamente isso que um bom design de schema faz — previne problemas tão silenciosamente que você nunca sabe que eles teriam existido.

Os padrões neste post não são novidade. São o resultado de anos escrevendo migrações, debugando corrupção de dados e refatorando schemas sob carga de produção. Cada um deles existe porque eu, ou alguém com quem trabalhei, fez o contrário primeiro e pagou o preço.

Comece com restrições. Use chaves estrangeiras. Adicione created_at em tudo. Escolha uma convenção de nomenclatura e aplique implacavelmente. Use RLS para multi-tenancy. Seja cuidadoso com JSONB. Teste suas migrações contra dados de tamanho de produção antes de fazer deploy.

O banco de dados é a fundação. Acerte, e tudo construído em cima se torna mais simples. Erre, e nenhuma quantidade de código de aplicação inteligente pode te salvar.