Database Schema

Estrutura do banco de dados PostgreSQL.

Entity Relationship Diagram

erDiagram
    users ||--o{ orders : places
    users {
        uuid id PK
        string email UK
        string name
        string password_hash
        string role
        boolean is_active
        timestamp created_at
        timestamp updated_at
    }

    orders ||--|{ order_items : contains
    orders {
        uuid id PK
        uuid user_id FK
        decimal total
        string status
        json metadata
        timestamp created_at
        timestamp updated_at
    }

    products ||--o{ order_items : ""
    products {
        uuid id PK
        string name
        text description
        decimal price
        integer stock
        json attributes
        boolean is_active
        timestamp created_at
        timestamp updated_at
    }

    order_items {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        integer quantity
        decimal unit_price
        decimal subtotal
    }

    users ||--o{ sessions : has
    sessions {
        uuid id PK
        uuid user_id FK
        string token_hash UK
        timestamp expires_at
        timestamp created_at
    }

Tabelas Principais

users

Armazena dados de usuários do sistema.

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL DEFAULT 'user',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_is_active ON users(is_active);

products

Catálogo de produtos.

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    attributes JSONB,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_products_is_active ON products(is_active);
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);

orders

Pedidos de clientes.

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    total DECIMAL(10, 2) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Convenções

Naming

Tabelas: plural, snake_case (users, order_items)
Colunas: snake_case (created_at, user_id)
Primary keys: sempre id
Foreign keys: {table}_id (user_id, product_id)
Boolean: prefixo is_ ou has_ (is_active, has_permission)

Types

IDs: UUID (mais seguro que integers sequenciais)
Timestamps: TIMESTAMP WITH TIME ZONE
Money: DECIMAL(10, 2)
JSON: JSONB (indexável e rápido)
Enums: VARCHAR com CHECK constraint

Constraints

NOT NULL: Sempre que possível
UNIQUE: Para campos únicos (email, etc.)
CHECK: Para validações (price >= 0)
Foreign Keys: Com ON DELETE apropriado

Migrations (Alembic)

Ver guia completo de migrations →

Exemplo de Migration

"""add user email column

Revision ID: abc123def456
Revises: xyz789abc012
Create Date: 2026-01-20 10:00:00

"""
from alembic import op
import sqlalchemy as sa


def upgrade():
    """Add email column to users."""
    op.add_column('users',
        sa.Column('email', sa.String(255), nullable=True)
    )

    # Popular dados existentes
    op.execute("""
        UPDATE users 
        SET email = username || '@temp.com' 
        WHERE email IS NULL
    """)

    # Tornar NOT NULL
    op.alter_column('users', 'email', nullable=False)

    # Adicionar índice
    op.create_index('idx_users_email', 'users', ['email'])

    # Adicionar constraint UNIQUE
    op.create_unique_constraint('uq_users_email', 'users', ['email'])


def downgrade():
    """Remove email column."""
    op.drop_constraint('uq_users_email', 'users')
    op.drop_index('idx_users_email')
    op.drop_column('users', 'email')

Indexes

Quando Criar Índices

✅ Criar índice: - Colunas em WHERE frequentemente - Colunas em JOIN - Foreign keys - Colunas em ORDER BY

❌ Evitar índice: - Tabelas muito pequenas (< 1000 rows) - Colunas que mudam frequentemente - Colunas com baixa cardinali dade (ex: boolean)

Exemplo

-- Consulta frequente
SELECT * FROM orders WHERE user_id = ? AND status = 'pending';

-- Índice composto
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Performance

Query Optimization

# ❌ Ruim: N+1 queries
users = await session.execute(select(User))
for user in users:
    orders = await session.execute(
        select(Order).where(Order.user_id == user.id)
    )  # Query por usuário!

# ✅ Bom: Join ou eager loading
users_with_orders = await session.execute(
    select(User).options(selectinload(User.orders))
)

Connection Pooling

from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    DATABASE_URL,
    pool_size=10,        # Connections no pool
    max_overflow=20,     # Max connections extras
    pool_pre_ping=True,  # Test connection before use
    pool_recycle=3600,   # Recycle after 1h
)

Backup e Restore

Backups automáticos diários (RDS)
Point-in-time recovery habilitado
Snapshot manual antes de migrations grandes
Ver procedimentos →

Documentação Automática

O schema é documentado automaticamente:

# Gerar ER diagram
python scripts/generate_er_diagram.py

# Gerar documentação de tabelas
python scripts/document_schema.py