Database Patterns — Software Complexo

Migration Safety Protocol

Nunca aplicar migration sem este protocolo:

# 1. Snapshot — estado antes
pg_dump $DATABASE_URL --schema-only > backups/schema-$(date +%Y%m%d-%H%M%S).sql

# 2. Dry-run — verificar sem commitar (BEGIN + ROLLBACK)
psql $DATABASE_URL <<'EOF'
BEGIN;
\i migrations/001_add_users_table.sql
-- Verificar resultado sem commitar
SELECT COUNT(*) FROM users;
\d users
ROLLBACK;  -- Desfaz tudo — apenas verificação
EOF

# 3. Apply — executar de verdade
psql $DATABASE_URL -f migrations/001_add_users_table.sql

# 4. Smoke test — verificar integridade após apply real
psql $DATABASE_URL -c "SELECT COUNT(*) FROM users;"
psql $DATABASE_URL -c "\d users"

# 5. Rollback disponível (executar se smoke test falhar)
psql $DATABASE_URL -f migrations/001_add_users_table.rollback.sql

Nota: PostgreSQL não tem flag --dry-run nativo. O dry-run correto é sempre via BEGIN/ROLLBACK — executa a migration em transação e faz rollback sem commitar.

Migration Structure

Cada migration tem arquivo de rollback correspondente:

migrations/
├── 001_create_users.sql
├── 001_create_users.rollback.sql
├── 002_add_user_roles.sql
└── 002_add_user_roles.rollback.sql

Template de migration

-- migrations/001_create_users.sql
-- Migration: create users table
-- Author: dev-data-engineer
-- Date: 2026-04-21

BEGIN;

CREATE TABLE users (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       VARCHAR(255) NOT NULL UNIQUE,
  name        VARCHAR(100) NOT NULL,
  role        VARCHAR(50) NOT NULL DEFAULT 'member',
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at  TIMESTAMPTZ  -- soft delete
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role) WHERE deleted_at IS NULL;

COMMENT ON TABLE users IS 'Core user accounts';

COMMIT;

-- migrations/001_create_users.rollback.sql
BEGIN;
DROP TABLE IF EXISTS users;
COMMIT;

Indexing Strategy

-- Index para lookup mais comum
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Index composto para queries frequentes
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Index parcial — apenas registros ativos (muito mais eficiente)
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;

-- Index para full-text search
CREATE INDEX idx_products_search ON products
  USING GIN(to_tsvector('english', name || ' ' || description));

Regras de indexing:

• Todo campo usado em WHERE, JOIN ON, ORDER BY frequentemente deve ter index
• Índices compostos: ordem importa (campo mais seletivo primeiro)
• Índices parciais para tabelas com flag de soft delete
• Executar EXPLAIN ANALYZE antes de criar index em tabela grande

N+1 Detection e Prevenção

// ❌ N+1 — um query por usuário
const users = await db.user.findMany()
for (const user of users) {
  const orders = await db.order.findMany({ where: { userId: user.id } })
  // N queries para N usuários = N+1 total
}

// ✅ Sem N+1 — include para eager loading
const users = await db.user.findMany({
  include: { orders: true }
})

// ✅ Para casos complexos — query única com JOIN
const usersWithOrders = await db.$queryRaw`
  SELECT u.*, json_agg(o.*) as orders
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  GROUP BY u.id
`

Detectar N+1: Logar queries em desenvolvimento com prisma.$on('query', ...) ou usar pg_stat_statements.

Soft Deletes

-- Coluna de soft delete
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- Deletar (soft)
UPDATE users SET deleted_at = NOW() WHERE id = $1;

-- Queries devem filtrar deletados
SELECT * FROM users WHERE deleted_at IS NULL;

-- View para simplificar
CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;

// Prisma — middleware para soft delete automático
prisma.$use(async (params, next) => {
  if (params.model === 'User') {
    if (params.action === 'delete') {
      params.action = 'update'
      params.args.data = { deletedAt: new Date() }
    }
    if (['findMany', 'findFirst', 'count'].includes(params.action)) {
      params.args.where = { ...params.args.where, deletedAt: null }
    }
  }
  return next(params)
})

Connection Pooling

// Supabase / Postgres — configurar pool adequadamente
const db = new PrismaClient({
  datasources: {
    db: { url: process.env.DATABASE_URL }
  },
  log: process.env.NODE_ENV === 'development' ? ['query'] : ['error'],
})

Para Supabase:

• Transaction pooler (porta 6543) → serverless functions (sem estado de sessão)
• Session pooler (porta 5432) → aplicações persistentes (com estado de sessão)
• pool_max=10 para produção, pool_min=2 para manter conexões aquecidas

RLS com Supabase

-- Habilitar RLS em todas as tabelas
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Política básica de isolamento
CREATE POLICY "users_own_data" ON users
  FOR ALL USING (auth.uid() = id);

-- Política com role
CREATE POLICY "admin_full_access" ON users
  FOR ALL USING (auth.jwt() ->> 'role' = 'admin');

-- Política de insert com check
CREATE POLICY "users_insert_own" ON orders
  FOR INSERT WITH CHECK (auth.uid() = user_id);

Testar RLS:

-- Simular como usuário específico
SET LOCAL role = authenticated;
SET LOCAL request.jwt.claims = '{"sub": "user-uuid-here"}';
SELECT * FROM orders;  -- Deve retornar apenas os pedidos do usuário

Query Performance

-- Analisar query lenta
EXPLAIN ANALYZE
SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.deleted_at IS NULL
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 20;

Interpretar:

• Seq Scan em tabela grande → precisa de index
• Hash Join → geralmente ok
• Nested Loop com muitas iterações → possível N+1

Regras absolutas

• Nunca DROP sem backup confirmado
• Nunca migration sem rollback correspondente
• Dry-run via BEGIN/ROLLBACK — PostgreSQL não tem --dry-run nativo
• Nunca SELECT * em produção — selecionar colunas necessárias
• Nunca joins sem index nas colunas de join
• Sempre RLS em tabelas com dados de usuário
• Migrations são imutáveis após aplicação — criar nova migration para corrigir