4-Schema Architecture & Star Schema
ARCHITECTUREData flows through 4 schemas: raw (JSONB) → staging (normalized) → analytics (star schema with dim_*/fact_*) → public API. JOINs happen in analytics for dashboard queries.
SQL JOINs → Next.js Frontend
Complex SQL with JOINs, window functions, CTEs served via Next.js API routes to React dashboard components.
Complex JOINs with aggregation served via Next.js API to frontend
// app/api/dashboard/revenue/route.ts
// Complex SQL query with JOINs → served to frontend
import { NextRequest, NextResponse } from "next/server";
import { createClient } from "@supabase/supabase-js";
const supabase = createClient(
process.env.SUPABASE_URL!,Frontend component consuming SQL JOINs via API route
// components/RevenueDashboard.tsx
"use client";
import { useState, useEffect } from "react";
interface DashboardData {
state: string;
year: number;Window functions, CTEs, pivots, running totals — ready for API routes
-- ═══════════════════════════════════════════ -- Advanced SQL for Next.js Dashboard APIs -- All queries read from analytics.* schema -- ═══════════════════════════════════════════ -- 1. Year-over-Year Comparison with Growth % -- Used in: /api/dashboard/yoy WITH current_year AS (
Fundamentos — DDL & Tipos
Criacao de tabelas, tipos de dados e constraints. A base de tudo no PostgreSQL.
CREATE TABLE define a estrutura de uma tabela no banco de dados. Voce especifica o nome da tabela, as colunas com seus tipos de dados, e as constraints (regras) que os dados devem seguir. E o primeiro passo para armazenar qualquer dado.
PostgreSQL tem tipos de dados ricos e expressivos. Escolher o tipo certo e crucial: afeta performance, validacao e quanto espaco seus dados ocupam. O tipo errado causa bugs sutis (FLOAT para dinheiro) ou desperdica espaco (VARCHAR(255) desnecessario).
ALTER TABLE modifica a estrutura de uma tabela existente sem perder os dados. Voce pode adicionar, remover ou renomear colunas, mudar tipos, e adicionar ou remover constraints. E essencial para evolucao do schema em producao.
SELECT e o comando mais usado em SQL. Ele busca dados de uma ou mais tabelas, com filtros (WHERE), ordenacao (ORDER BY), paginacao (LIMIT/OFFSET) e agrupamentos (GROUP BY). Dominar SELECT e dominar SQL.
WHERE e onde a maioria das queries ganha ou perde performance. Alem do basico (=, BETWEEN, LIKE), PostgreSQL oferece operadores poderosos para filtros compostos, arrays, pattern matching regex, e comparacoes com subconjuntos (ANY/ALL). Dominar WHERE patterns e essencial para queries eficientes.
INSERT adiciona novas linhas a uma tabela. Voce pode inserir uma unica linha, multiplas de uma vez, ou ate copiar dados de outra consulta. O RETURNING e um recurso poderoso do PostgreSQL que retorna os dados inseridos sem precisar de um SELECT extra.
UPDATE modifica dados existentes em uma tabela. O WHERE e OBRIGATORIO na pratica — sem ele, voce atualiza TODAS as linhas da tabela (potencialmente desastroso). O RETURNING do PostgreSQL permite ver exatamente o que foi alterado.
DELETE remove linhas de uma tabela. Assim como UPDATE, o WHERE e OBRIGATORIO na pratica. Em muitos sistemas, preferimos "soft delete" (marcar como deletado) em vez de remover fisicamente, para manter historico e permitir recuperacao.
Relacionamentos & JOINs
Conectar tabelas e o coracao do modelo relacional. JOINs permitem combinar dados de multiplas tabelas em uma unica consulta.
Foreign Keys (chaves estrangeiras) criam relacionamentos entre tabelas, garantindo integridade referencial. Se um produto referencia uma categoria, a FK garante que essa categoria realmente existe. O comportamento ON DELETE define o que acontece quando o registro pai e removido.
INNER JOIN retorna APENAS as linhas que tem correspondencia nas DUAS tabelas. Se um produto nao tem categoria, ele nao aparece no resultado. E o JOIN mais comum e seguro — voce so ve dados completos.
LEFT JOIN retorna TODAS as linhas da tabela da esquerda, mesmo que nao tenham correspondencia na tabela da direita. Quando nao ha match, as colunas da direita vem como NULL. E essencial quando voce quer "tudo de A, com dados de B se existirem".
FULL OUTER JOIN retorna TODAS as linhas de AMBAS as tabelas, com NULL onde nao ha correspondencia. CROSS JOIN produz o produto cartesiano (cada linha de A com cada linha de B). Ambos sao menos comuns, mas tem usos importantes.
Self JOIN conecta uma tabela consigo mesma. E essencial para modelar hierarquias: funcionarios e seus gerentes, categorias e subcategorias, comentarios e respostas. A chave e que a mesma tabela aparece duas vezes com aliases diferentes.
Agregacao & Agrupamento
Funcoes de agregacao transformam muitas linhas em resumos. Essencial para relatorios, dashboards e analytics.
Funcoes de agregacao calculam um valor unico a partir de um conjunto de linhas. COUNT conta registros, SUM soma valores, AVG calcula media, MIN/MAX encontram extremos. Combinadas com GROUP BY, sao a base de qualquer relatorio.
DISTINCT ON e uma funcionalidade exclusiva do PostgreSQL que retorna a primeira linha de cada grupo, baseado na ordenacao que voce definir. E perfeito para queries tipo "o pedido mais recente de cada cliente" ou "o produto mais caro de cada categoria" — sem precisar de subqueries ou window functions.
NULL e a ausencia de valor em SQL — nao e zero, nao e string vazia, nao e false. Ele se propaga em calculos (qualquer coisa + NULL = NULL) e quebra comparacoes (NULL = NULL e FALSE!). COALESCE, NULLIF e IS NULL sao suas ferramentas para lidar com isso.
Queries Avancadas
Subqueries, CTEs, Window Functions e logica condicional. Ferramentas que separam iniciantes de profissionais.
Subqueries sao queries dentro de queries. Podem aparecer no WHERE (filtro), no FROM (tabela virtual), ou no SELECT (valor calculado). Sao poderosas, mas CTEs sao geralmente mais legiveis para queries complexas.
CTEs (Common Table Expressions) sao "tabelas temporarias nomeadas" que existem apenas durante a query. Elas tornam queries complexas legiveis, reutilizaveis e faceis de debugar. Pense nelas como variaveis para resultados intermediarios.
WITH RECURSIVE permite queries que referenciam a si mesmas, perfeito para percorrer hierarquias de profundidade desconhecida: organogramas, categorias aninhadas, grafos, e gerar series.
Window Functions calculam valores sobre um "janela" de linhas relacionadas, SEM agrupar (cada linha mantem sua identidade). Sao perfeitas para rankings, totais acumulados, comparacoes com linha anterior/seguinte, e medias moveis. E a funcionalidade mais poderosa do SQL para analytics.
CASE WHEN e o "if/else" do SQL. Permite logica condicional dentro de queries — classificar dados, criar colunas calculadas, fazer agregacoes condicionais. E extremamente versatil e usado em praticamente toda query complexa.
LATERAL JOIN permite que a subquery da direita referencie colunas da tabela da esquerda — algo impossivel com JOINs normais. E como um "for each" no SQL: para cada linha da esquerda, executa uma query. Extremamente util para "top N por grupo" e queries correlacionadas eficientes.
Operadores de conjunto combinam resultados de multiplas queries. UNION junta resultados (removendo duplicatas), INTERSECT encontra linhas em comum, EXCEPT encontra linhas que existem em A mas nao em B. Todas exigem que as queries tenham o mesmo numero e tipo de colunas.
PostgreSQL Especifico
Recursos exclusivos do PostgreSQL que nao existem em outros bancos: JSONB, full-text search, upsert, generated columns.
JSONB armazena JSON em formato binario, permitindo queries, indexacao e manipulacao eficientes. E perfeito para dados semi-estruturados: configuracoes de usuario, metadata, respostas de APIs externas. No PostgreSQL, JSONB e um cidadao de primeira classe — voce pode indexar, filtrar e transformar com performance excelente.
UPSERT (INSERT ... ON CONFLICT) e a forma atomica de "inserir se nao existir, atualizar se ja existir". E essencial para ETL idempotente, sincronizacao de dados e APIs que recebem dados repetidos. Sem UPSERT, voce precisaria de SELECT + IF + INSERT/UPDATE — mais lento e com race conditions.
Generated columns sao colunas calculadas automaticamente a partir de outras colunas da mesma linha. O valor e recalculado em todo INSERT e UPDATE. Perfeito para extrair campos de JSONB, calcular totais, ou criar campos derivados sem logica na aplicacao.
Transactions garantem que um grupo de operacoes SQL seja executado como uma unidade atomica: ou TUDO funciona, ou NADA e aplicado. Sao essenciais para manter a integridade dos dados quando multiplas tabelas precisam ser atualizadas juntas (ex: transferencia bancaria, criacao de pedido).
Producao & Performance
Indices, RLS, materialized views, particionamento e estrategias de migracao. O que separa dev de producao.
Indices sao estruturas que aceleram buscas dramaticamente — de varredura completa da tabela (Seq Scan) para busca indexada (Index Scan). Sem indices, uma tabela de 1 milhao de linhas e varrida completamente para cada query. Com indice, a mesma query encontra o resultado em milissegundos. EXPLAIN ANALYZE revela exatamente como o PostgreSQL executa sua query.
Row Level Security (RLS) restringe quais LINHAS cada usuario pode ver e modificar, diretamente no banco de dados. Em vez de filtrar no codigo da aplicacao (WHERE user_id = X), o PostgreSQL aplica automaticamente. E a forma mais segura de isolamento de dados — impossivel de esquecer ou burlar pelo frontend.
Views sao queries salvas com nome — como "tabelas virtuais". Toda vez que voce faz SELECT na view, a query e executada. Materialized Views (MVs) sao como views pre-calculadas: o resultado e armazenado fisicamente, tornando consultas instantaneas. MVs sao essenciais para dashboards com queries pesadas.
Particionamento divide uma tabela grande em pedacos menores (particoes) fisicamente separados, mas que se comportam como uma unica tabela logicamente. O PostgreSQL automaticamente direciona queries apenas para as particoes relevantes (partition pruning). Essencial para tabelas com milhoes+ de linhas, especialmente time-series.
Migrations sao scripts SQL versionados que evoluem o schema do banco de forma controlada e reproduzivel. Cada migration tem um timestamp unico e e executada apenas uma vez. Forward-only (sem rollback) e a estrategia mais segura para producao.