Database design patterns: padrões para modelar bancos de dados com clareza e escala

users 1 ───< project_members >─── 1 projects
               │
               └── role, joined_at

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

CREATE TABLE projects (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE project_members (
  project_id bigint NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role text NOT NULL CHECK (role IN ('owner', 'editor', 'viewer')),
  joined_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (project_id, user_id)
);

CREATE TABLE categories (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  parent_id bigint REFERENCES categories(id) ON DELETE RESTRICT,
  name text NOT NULL,
  CHECK (parent_id IS NULL OR parent_id <> id)
);

WITH RECURSIVE category_tree AS (
  SELECT id, parent_id, name, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN category_tree t ON c.parent_id = t.id
)
SELECT * FROM category_tree ORDER BY depth, name;

CREATE TABLE order_status_history (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders(id),
  from_status text,
  to_status text NOT NULL,
  changed_at timestamptz NOT NULL DEFAULT now(),
  changed_by bigint REFERENCES users(id),
  CHECK (from_status IS DISTINCT FROM to_status)
);

CREATE INDEX order_status_history_order_time_idx
  ON order_status_history (order_id, changed_at DESC);

ALTER TABLE customers
  ADD COLUMN deleted_at timestamptz;

CREATE UNIQUE INDEX customers_active_email_uidx
  ON customers (lower(email))
  WHERE deleted_at IS NULL;

SELECT id, email
FROM customers
WHERE deleted_at IS NULL;

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE product_prices (
  product_id bigint NOT NULL REFERENCES products(id),
  amount numeric(12, 2) NOT NULL CHECK (amount >= 0),
  valid_during daterange NOT NULL,
  EXCLUDE USING gist (
    product_id WITH =,
    valid_during WITH &&
  )
);

CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku text NOT NULL UNIQUE,
  name text NOT NULL
);

CREATE TABLE catalog_items (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku text NOT NULL UNIQUE,
  category_id bigint NOT NULL REFERENCES categories(id),
  name text NOT NULL,
  attributes jsonb NOT NULL DEFAULT '{}'::jsonb,
  CHECK (jsonb_typeof(attributes) = 'object')
);

-- Crie apenas se consultas reais usarem contenção em attributes.
CREATE INDEX catalog_items_attributes_gin_idx
  ON catalog_items USING gin (attributes);

tenants 1 ───< projects 1 ───< tasks
   │             │               │
   └──── tenant_id integra todas as relações

CREATE TABLE tenants (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE tenant_projects (
  tenant_id bigint NOT NULL REFERENCES tenants(id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  name text NOT NULL,
  PRIMARY KEY (tenant_id, id)
);

CREATE TABLE tenant_tasks (
  tenant_id bigint NOT NULL,
  id bigint GENERATED ALWAYS AS IDENTITY,
  project_id bigint NOT NULL,
  title text NOT NULL,
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, project_id)
    REFERENCES tenant_projects (tenant_id, id)
);

CREATE TABLE outbox_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  aggregate_type text NOT NULL,
  aggregate_id bigint NOT NULL,
  event_type text NOT NULL,
  payload jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz
);

BEGIN;

UPDATE orders
SET status = 'paid'
WHERE id = 42 AND status = 'pending';

INSERT INTO outbox_events (
  aggregate_type, aggregate_id, event_type, payload
) VALUES (
  'order', 42, 'order.paid', '{"order_id": 42}'::jsonb
);

COMMIT;

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT
  date_trunc('day', paid_at)::date AS day,
  sum(total_amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY 1;

CREATE UNIQUE INDEX daily_revenue_day_uidx
  ON daily_revenue (day);

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;