Saltar al contenido
·34 min de lectura

Diseño de esquemas de base de datos: Patrones que envejecen bien

Reglas de normalización, convenciones de nombres, soft deletes, audit trails, patrones multi-tenancy, estrategias de versionado y las decisiones de esquema de las que me he arrepentido. Enfocado en PostgreSQL.

databasepostgresqlarchitecturebackendsql
Compartir:X / TwitterLinkedIn

Un esquema es un contrato con tu yo futuro. Cada columna que añades, cada restricción que omites, cada "ya lo limpiaremos después" — todo se acumula. He trabajado en sistemas donde una sola mala decisión de esquema tomada hace tres años ahora le cuesta al equipo un sprint completo cada trimestre en soluciones alternativas.

La base de datos sobrevive a todo. Tu framework de frontend cambiará. Tu capa de API se reescribirá. Tu estrategia de despliegue evolucionará. ¿Pero los datos? Los datos permanecen. Y la forma que les diste el primer día te acompaña para siempre, porque migrar una tabla con 200 millones de filas no es lo mismo que refactorizar un componente de React.

Esto es lo que he aprendido sobre tomar decisiones de esquema que no te persigan. Enfocado en PostgreSQL, porque es lo que uso y en lo que confío, pero la mayoría de estos patrones aplican a cualquier base de datos relacional.

Convenciones de nombres: Lo aburrido que más importa#

He visto más discusiones sobre convenciones de nombres que sobre decisiones de arquitectura reales. Esto es lo que he establecido después de años cambiando entre proyectos:

Snake_case para todo. Tablas, columnas, índices, restricciones. Nada de camelCase, nada de PascalCase. PostgreSQL convierte los identificadores sin comillas a minúsculas de todos modos, así que createdAt se convierte en createdat a menos que lo pongas entre comillas dobles en todas partes. No luches contra la base de datos.

Nombres de tablas en plural. Una tabla contiene muchas filas. users no user. orders no order. order_items no order_item. Esto se lee naturalmente en las consultas: SELECT * FROM users WHERE ... — estás seleccionando de una colección.

Sufijo _id para claves foráneas. user_id, order_id, tenant_id. La clave primaria es simplemente id. Esto es inequívoco. Cuando haces join entre tablas, users.id = orders.user_id se lee como lenguaje natural.

Sufijo _at para timestamps. created_at, updated_at, deleted_at, published_at, expires_at. Siempre sabes que es un punto en el tiempo.

Prefijo is_ para booleanos. is_active, is_verified, is_published. Algunas personas usan has_ para booleanos de posesión (has_mfa_enabled), pero yo lo mantengo simple y uso is_ para todo.

Sufijo _count para contadores desnormalizados. comment_count, follower_count. Deja claro que es un número cacheado, no un cálculo en vivo.

Y aquí está la clave: la consistencia supera a la perfección. He visto equipos pasar semanas debatiendo si debería ser email_address o email o email_addr. Simplemente elige un patrón y aplícalo en todas partes. La peor convención de nombres es la que se aplica inconsistentemente.

sql
-- Bien: consistente, legible, sin sorpresas
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()
);
 
-- Mal: casing inconsistente, convenciones mezcladas, nombres ambiguos
CREATE TABLE User (
    UserID          SERIAL PRIMARY KEY,
    EmailAddress    VARCHAR(255),
    name            VARCHAR(100),
    passwd          VARCHAR(255),
    active          BOOLEAN,
    verified        INT,             -- ¿es esto 0/1? ¿un nivel de verificación?
    lastLogin       TIMESTAMP,       -- camelCase en un mundo snake_case
    created         TIMESTAMP        -- ¿creado qué? ¿cuándo?
);

Una cosa más: nunca uses palabras reservadas como nombres de columna. type, order, user, group, table — todas funcionan si las pones entre comillas, pero te causarán problemas en ORMs, query builders y cualquier herramienta de generación dinámica de SQL. Usa kind en vez de type, sort_order en vez de order. Tu yo futuro te lo agradecerá.

Las columnas estándar: Lo que toda tabla recibe#

Toda tabla en mis esquemas empieza con el mismo esqueleto. Sin excepciones.

sql
CREATE TABLE things (
    id          BIGSERIAL PRIMARY KEY,
    -- ... columnas del dominio ...
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

El debate UUID vs BIGSERIAL#

Esta es una de esas decisiones que genera mucho más calor que luz. Aquí está la compensación real:

BIGSERIAL (entero auto-incremental):

  • 8 bytes. Compacto. Rápido para indexar y hacer join.
  • Ordenado — puedes ordenar por id para obtener el orden de inserción.
  • Predecible — un usuario puede adivinar otros IDs incrementando el suyo.
  • No funciona bien en sistemas distribuidos (requiere coordinación).

UUID v4 (aleatorio):

  • 16 bytes. Índices más grandes, joins más lentos (pero rara vez es el cuello de botella).
  • Impredecible — no hay fuga de información.
  • Funciona en sistemas distribuidos sin coordinación.
  • Localidad de índice terrible — los UUIDs aleatorios fragmentan los índices B-tree.

UUID v7 (ordenado por tiempo, RFC 9562):

  • 16 bytes, pero ordenado por tiempo así que la localidad B-tree es excelente.
  • Suficientemente impredecible para uso externo.
  • Relativamente nuevo, pero PostgreSQL 17+ tiene gen_random_uuid() y puedes usar uuid_generate_v7() con extensiones.

Mi postura actual: BIGSERIAL para tablas internas, UUID v7 para cualquier cosa expuesta al mundo exterior. Si un ID aparece alguna vez en una URL, una respuesta de API o un payload de webhook, usa UUIDs. Si es una tabla de join pura que los usuarios nunca ven, BIGSERIAL está bien.

sql
-- Para un recurso expuesto por 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 una tabla de mapeo 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: Siempre TIMESTAMPTZ#

Usa TIMESTAMPTZ, no TIMESTAMP. La versión con "TZ" almacena el valor en UTC y lo convierte al leer basándose en la zona horaria de la sesión. La versión sin TZ almacena lo que le des sin contexto de zona horaria — lo que significa que si dos servidores en diferentes zonas horarias escriben en la misma tabla, obtienes corrupción silenciosa de datos.

sql
-- Siempre esto
created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
 
-- Nunca esto
created_at  TIMESTAMP NOT NULL DEFAULT NOW()

El trigger de updated_at#

PostgreSQL no tiene el ON UPDATE CURRENT_TIMESTAMP de MySQL. Necesitas un 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 tabla que tenga 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();

Sí, necesitas un trigger por tabla. Sí, es tedioso. Escribe un helper de migración que cree el trigger automáticamente cuando añadas una tabla. Vale la pena, porque la alternativa es recordar poner updated_at = NOW() en cada consulta UPDATE a lo largo de toda tu aplicación — y lo olvidarás.

Normalización: Cuándo romper las reglas#

Todo curso de informática enseña normalización hasta 3FN (Tercera Forma Normal). Las reglas son:

  • 1FN: Cada columna contiene un solo valor atómico. Nada de arrays, nada de listas separadas por comas.
  • 2FN: Cada columna no clave depende de la clave primaria completa (relevante para claves compuestas).
  • 3FN: Sin dependencias transitivas. Si la columna A determina la columna B, y B determina C, entonces C no debería estar en la misma tabla que A.

En la práctica, 3FN es el punto óptimo para tablas transaccionales. Deberías empezar ahí y desviarte solo cuando tengas una razón específica y medible.

Aquí tienes un sistema de pedidos correctamente 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()
);

Observa unit_price_cents en order_items. Esto es desnormalización intencional. Hacemos una instantánea del precio en el momento del pedido, porque el precio del producto podría cambiar después. Esta es una de las razones más comunes y correctas para desnormalizar.

Cuándo la desnormalización es la decisión correcta#

Tablas de reportes. Si tu panel de analíticas necesita hacer join de 8 tablas para renderizar, crea una tabla desnormalizada de reportes y llénala con un trabajo en segundo plano. Tu esquema transaccional se mantiene limpio y tus consultas de reportes se mantienen rápidas.

sql
-- Tabla de reportes desnormalizada, poblada por un 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 está bien aquí
    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()
);

Vistas materializadas. La MATERIALIZED VIEW de PostgreSQL está subestimada. Es una instantánea desnormalizada que actualizas bajo demanda. Perfecta para paneles de control.

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;
 
-- Actualizarla cada noche
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

Columnas JSON para atributos dinámicos. Cuando diferentes filas necesitan diferentes campos — variantes de producto, envíos de formularios, preferencias de usuario — una columna JSONB es a menudo mejor que una pesadilla de tabla EAV (Entity-Attribute-Value). Más sobre esto después.

La regla que nunca rompo#

Nunca desnormalices tus tablas de fuente de verdad. Desnormaliza copias, instantáneas, reportes y cachés. Los datos canónicos se mantienen normalizados. Cuando la copia desnormalizada se quede obsoleta o se corrompa (y lo hará), la reconstruyes desde la fuente normalizada.

Claves foráneas y restricciones: El mejor código que nunca escribirás#

He escuchado todas las excusas para omitir las claves foráneas. "Ralentizan las escrituras." "Lo controlamos en la aplicación." "Necesitamos flexibilidad."

Todas son incorrectas.

Las claves foráneas son lo más impactante que puedes añadir a un esquema. Previenen categorías enteras de bugs que ninguna cantidad de código de aplicación puede detectar — condiciones de carrera, fallos parciales, filas huérfanas de transacciones fallidas. Una clave foránea es una garantía del motor de base de datos en sí, aplicada a nivel de almacenamiento. El hook beforeDelete de tu ORM es una sugerencia.

sql
-- Siempre haz esto
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- La alternativa de "lo manejamos en el código":
-- Esperanza. La esperanza no es una estrategia.

Estrategias ON DELETE#

Aquí es donde se pone matizado. ¿Qué pasa cuando eliminas una fila padre?

RESTRICT (por defecto): La eliminación falla si existen filas hijas. Usa esto para la mayoría de relaciones. No puedes eliminar un cliente que tiene pedidos — eso es lógica de negocio codificada en el esquema.

sql
-- El cliente no puede eliminarse mientras tenga pedidos
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT;

CASCADE: Las filas hijas se eliminan automáticamente. Usa esto con moderación y de forma deliberada. Bueno para relaciones "parte-de" donde el hijo no tiene significado sin el padre.

sql
-- Eliminar un pedido elimina sus líneas de detalle — no tienen sentido solas
ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE CASCADE;
 
-- Eliminar un proyecto elimina sus membresías
ALTER TABLE project_members
    ADD CONSTRAINT fk_project_members_project
    FOREIGN KEY (project_id) REFERENCES projects(id)
    ON DELETE CASCADE;

SET NULL: La columna de clave foránea se establece a NULL. Usa esto cuando la relación es opcional y la fila hija sigue teniendo sentido por sí misma.

sql
-- Si un gerente se va, sus subordinados siguen existiendo — solo sin asignar
ALTER TABLE employees
    ADD CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(id)
    ON DELETE SET NULL;

Mi regla general: por defecto RESTRICT, CASCADE para relaciones de composición, SET NULL para asociaciones opcionales. Si no estás seguro, RESTRICT es siempre la opción segura — es más fácil relajar una restricción que recuperar datos eliminados.

Check constraints: Seguro barato#

Las check constraints cuestan casi nada en tiempo de escritura y previenen que datos basura entren en tu sistema para siempre:

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 check constraint que añades es un bug de validación menos que tendrás que depurar en producción. La base de datos es la última línea de defensa. Úsala.

Restricciones únicas e índices únicos parciales#

Las restricciones únicas son directas para casos simples:

sql
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Pero se ponen interesantes con índices parciales — restricciones únicas que solo aplican a ciertas filas:

sql
-- Solo una suscripción activa por usuario (pero pueden tener muchas canceladas)
CREATE UNIQUE INDEX uq_active_subscription
    ON subscriptions (user_id)
    WHERE status = 'active';
 
-- Solo una dirección principal por usuario
CREATE UNIQUE INDEX uq_primary_address
    ON addresses (user_id)
    WHERE is_primary = true;

Esta es una de las funcionalidades estrella de PostgreSQL. Úsala.

Soft deletes: El patrón que todos aman odiar#

Los soft deletes son simples en concepto: en lugar de DELETE FROM users WHERE id = 42, haces UPDATE users SET deleted_at = NOW() WHERE id = 42. La fila permanece en la base de datos pero se filtra de las consultas normales.

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()
);
 
-- El índice parcial: filtra las filas eliminadas eficientemente
CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

Cada consulta en tu aplicación ahora necesita WHERE deleted_at IS NULL. Cada. Una. De. Ellas. Los ORMs ayudan con esto (Prisma tiene middleware, Drizzle tiene .where(isNull(deletedAt))), pero es un impuesto sobre cada operación de lectura. Olvídalo una vez y estarás mostrando datos "eliminados" a los usuarios.

El problema de la restricción única#

Aquí es donde los soft deletes se ponen feos. Si tienes UNIQUE (email) y un usuario hace soft-delete de su cuenta, su email sigue en la tabla. No puede re-registrarse con el mismo email. Un nuevo usuario con ese email tampoco puede registrarse.

La solución es un índice único parcial:

sql
-- El email debe ser único, pero solo entre usuarios no eliminados
CREATE UNIQUE INDEX uq_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;

Esto funciona, pero ahora necesitas recordar este patrón para cada columna única en cada tabla con soft-delete. Es manejable con disciplina, pero es complejidad que has elegido asumir.

El problema de la clave foránea#

Los soft deletes interactúan mal con las claves foráneas. Si orders.user_id referencia users.id con ON DELETE RESTRICT, y haces soft-delete de un usuario... no pasa nada. La FK no se activa porque en realidad no eliminaste la fila. El usuario se "fue" desde la perspectiva de la aplicación pero sigue muy presente en la base de datos.

Esto significa que tu código de aplicación tiene que manejar el caso donde una entidad referenciada tiene soft-delete. Cada join, cada búsqueda, cada vez que sigues una clave foránea — necesitas verificar deleted_at IS NULL en la tabla referenciada también. O no lo haces, y tu aplicación muestra "Pedido por [usuario eliminado]" que es un bug o una funcionalidad dependiendo de a quién le preguntes.

Mi enfoque#

Uso soft deletes solo para entidades visibles al usuario donde la recuperación es un requisito de negocio — cuentas de usuario, proyectos, documentos. Cosas donde un agente de soporte podría necesitar restaurar una eliminación. Para todo lo demás, uso hard deletes con un audit trail (más sobre esto a continuación).

sql
-- Soft delete: visible al usuario, recuperable
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, no recuperable desde la UI (pero 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()
);
-- Cuando se elimina, desaparece. El audit_log registra que existió.

Audit trails: Saber qué cambió y quién lo hizo#

Toda aplicación no trivial necesita una respuesta a "¿qué pasó con este registro?" en algún momento. Los audit trails son cómo proporcionas esa respuesta sin hurgar en los logs de la aplicación.

El patrón de tabla de auditoría separada#

El enfoque más simple: una única tabla audit_log que registra cada cambio.

sql
CREATE TABLE audit_log (
    id              BIGSERIAL PRIMARY KEY,
    table_name      TEXT NOT NULL,
    record_id       TEXT NOT NULL,          -- text para manejar tanto UUID como BIGINT PKs
    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[],                 -- qué columnas cambiaron (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);

El desafío es poblarla. Puedes hacerlo en el código de la aplicación (explícito, pero fácil de olvidar) o con triggers (automático, pero más difícil de pasar contexto como changed_by).

El enfoque basado en triggers#

Los triggers capturan cada cambio automáticamente, incluso desde SQL sin procesar u operaciones de administración de base de datos:

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 cambiados
        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;
        -- Solo registrar si algo realmente cambió
        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 a las tablas que quieras 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();

La desventaja: los triggers no saben qué usuario de la aplicación hizo el cambio. Puedes solucionarlo con variables de sesión:

sql
-- En tu aplicación, antes de la consulta:
SET LOCAL app.current_user_id = 'uuid-of-current-user';
 
-- En la función del trigger:
changed_by_id := current_setting('app.current_user_id', true)::UUID;

Esto funciona pero se siente frágil. En la práctica, uso un enfoque híbrido: triggers para la captura de datos, y código de aplicación para establecer el contexto de sesión.

El patrón de tabla de historial#

Para tablas donde necesitas el historial completo de versiones (no solo "qué cambió" sino "cuál era el estado en el momento T"), una tabla de historial dedicada es más limpia:

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 actualización a documents, copia el estado actual a document_history e incrementa la versión. Ahora puedes reconstruir el documento en cualquier punto en el tiempo, mostrar diferencias entre versiones e incluso restaurar versiones antiguas.

La compensación es el almacenamiento. Si tu columna content es grande y cambia frecuentemente, la tabla de historial puede crecer rápido. Para la mayoría de aplicaciones, esto está bien — el almacenamiento es barato y puedes archivar versiones antiguas en almacenamiento frío si es necesario.

Multi-tenancy: Tres enfoques, elige tu dolor#

Multi-tenancy es una de esas cosas que es fácil de añadir al principio y casi imposible de añadir después. Si hay alguna posibilidad de que tu aplicación sirva a múltiples organizaciones, constrúyelo desde el día uno.

A nivel de fila: tenant_id en cada tabla#

El enfoque más común. Cada tabla tiene una columna tenant_id, y cada consulta filtra por ella.

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 debería incluir tenant_id para rendimiento de consultas
CREATE INDEX idx_projects_tenant ON projects (tenant_id);
CREATE INDEX idx_tasks_tenant_project ON tasks (tenant_id, project_id);

El riesgo es la fuga de datos — un WHERE tenant_id = ... olvidado y estarás mostrando los datos del Tenant A al Tenant B. La Seguridad a Nivel de Fila (RLS) de PostgreSQL elimina esta clase de bugs:

sql
-- Habilitar RLS en la tabla
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
 
-- Crear una política basada en una variable de sesión
CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
 
-- Forzar RLS incluso para propietarios de la tabla
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Ahora, incluso si tu código de aplicación olvida la cláusula WHERE tenant_id = ..., PostgreSQL la añade automáticamente. Esto es defensa en profundidad, y es uno de los argumentos más fuertes a favor de PostgreSQL en sistemas multi-tenant.

sql
-- En el middleware de conexión de tu aplicación:
SET LOCAL app.current_tenant_id = 'tenant-uuid-here';
 
-- Ahora todas las consultas en tablas con RLS habilitado se filtran automáticamente
SELECT * FROM projects;
-- PostgreSQL internamente añade: WHERE tenant_id = 'tenant-uuid-here'

Pros: Base de datos única, operaciones simples, uso eficiente de recursos, fácil hacer consultas cross-tenant para admin. Contras: Requiere disciplina (o RLS), cada consulta toca tenant_id, más difícil dar a los tenants su propio backup/restore.

Esquema por tenant#

Cada tenant obtiene su propio esquema de PostgreSQL. Todos los esquemas comparten la misma base de datos, pero las tablas están aisladas por namespace.

sql
-- Crear un esquema para cada tenant
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;
 
-- Las tablas viven en el esquema del 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()
);
 
-- Establecer el search_path para el tenant actual
SET search_path TO tenant_acme, public;
 
-- Ahora las consultas sin calificar van al esquema del tenant
SELECT * FROM projects;  -- consulta tenant_acme.projects

Pros: Aislamiento fuerte, sin riesgo de fuga de datos cross-tenant, fácil backup/restore por tenant, los tenants pueden tener variaciones de esquema. Contras: Complejidad de migración de esquema (necesitas migrar N esquemas), gestión de pool de conexiones, PostgreSQL tiene límites prácticos alrededor de ~10,000 esquemas.

Base de datos por tenant#

Cada tenant obtiene su propia base de datos. Aislamiento máximo.

Pros: Aislamiento completo, escalado independiente, fácil backup/restore, puedes colocar tenants grandes en hardware dedicado. Contras: Pesadilla de gestión de conexiones, consultas cross-tenant imposibles, la migración debe ejecutarse N veces, sobrecarga operativa significativa.

¿Cuál elegir?#

Para la mayoría de aplicaciones SaaS: empieza con nivel de fila + RLS. Es lo más simple de operar, y RLS te da un aislamiento suficientemente fuerte para la gran mayoría de casos de uso. Pasa a esquema por tenant solo si tienes requisitos contractuales de aislamiento (clientes enterprise, industrias reguladas). Base de datos por tenant es para cuando absolutamente debes garantizar aislamiento físico — e incluso entonces, considera bases de datos gestionadas donde la carga operativa se maneja por ti.

Columnas JSON/JSONB: La vía de escape#

El JSONB de PostgreSQL es notable. Te da la flexibilidad de una base de datos documental dentro de un sistema relacional. Pero como cualquier herramienta poderosa, es fácil de abusar.

Cuándo JSONB es la elección correcta#

Atributos dinámicos definidos por el usuario. Una plataforma de e-commerce donde cada categoría de producto tiene diferentes atributos — los zapatos tienen size y color, la electrónica tiene voltage y wattage. En lugar de una tabla EAV o una columna para cada atributo posible:

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()
);
 
-- Datos de ejemplo:
-- Zapato: {"size": "10", "color": "black", "material": "leather"}
-- Laptop: {"ram_gb": 16, "storage_gb": 512, "screen_inches": 14.0}

Configuración y ajustes de la aplicación. Preferencias de usuario, feature flags, configuración de notificaciones — cosas que cambian de forma frecuentemente y no necesitan integridad 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 webhooks, respuestas de API, datos de eventos. Cualquier cosa donde el esquema es controlado por un sistema externo y podría cambiar sin aviso.

Índices GIN en JSONB#

Sin índices, consultar dentro de JSONB requiere un escaneo completo de tabla. Los índices GIN lo hacen rápido:

sql
-- Indexar toda la columna JSONB (bueno para consultas de contención @>)
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);
 
-- Consulta: encontrar todos los productos con color = "black"
SELECT * FROM products
WHERE attributes @> '{"color": "black"}';
 
-- Consulta: encontrar productos con cualquiera de estos atributos
SELECT * FROM products
WHERE attributes ?| ARRAY['color', 'size'];

Para rutas específicas que consultas a menudo, un índice dirigido es más eficiente:

sql
-- Indexar una ruta específica
CREATE INDEX idx_products_color ON products ((attributes->>'color'));
 
-- Ahora esto es una búsqueda B-tree regular
SELECT * FROM products
WHERE attributes->>'color' = 'black';

La trampa de la migración#

Aquí es donde JSONB te muerde: no puedes añadir fácilmente restricciones NOT NULL o valores por defecto a campos dentro de JSON. Si añades un nuevo campo requerido a tus atributos de producto, necesitas rellenar cada fila existente. Con una columna regular, una migración maneja esto atómicamente. Con JSONB, estás escribiendo un UPDATE que toca cada fila y esperando que tu código de aplicación maneje los campos faltantes correctamente hasta que el relleno se complete.

sql
-- Añadir una nueva columna: limpio, atómico, una declaración
ALTER TABLE products ADD COLUMN weight_kg NUMERIC(10,3);
 
-- Añadir un nuevo campo JSONB: desordenado, requiere un relleno
UPDATE products
SET attributes = attributes || '{"weight_kg": null}'
WHERE NOT (attributes ? 'weight_kg');
-- Más actualizar código de aplicación para manejar weight_kg faltante
-- Más actualizar lógica de validación
-- Más actualizar cada respuesta de API que incluya attributes

Mi regla: si estás consultando un campo JSONB en una cláusula WHERE más de dos veces por semana, probablemente debería ser una columna. JSONB es una gran vía de escape. Es un terrible valor por defecto.

Migraciones de esquema: No rompas producción a las 3 AM#

Las migraciones de esquema son donde la teoría se encuentra con la realidad. Tu esquema se ve genial en papel, pero ahora necesitas alterar una tabla con 50 millones de filas durante horario laboral sin ningún tiempo de inactividad.

Herramientas de migración#

He usado la mayoría de ellas. Opiniones breves:

Drizzle (TypeScript): Mi favorita actual. Schema-as-code, consultas type-safe generadas desde el esquema, SQL de migración limpio. El comando push para desarrollo es rápido.

Prisma (TypeScript): Gran DX para esquemas simples. Tiene dificultades con funcionalidades avanzadas de PostgreSQL (índices parciales, tipos personalizados, RLS). El motor de migración puede tomar decisiones sorprendentes.

Flyway (Java/CLI): Sólido como una roca, probado en batalla, SQL-first. Si estás escribiendo migraciones SQL sin procesar, Flyway las rastrea de forma fiable. Sin magia, sin sorpresas.

golang-migrate (Go/CLI): Similar a Flyway pero más ligero. Genial para proyectos Go o cuando solo quieres un ejecutor simple de migraciones up/down.

El problema del zero-downtime#

Los cambios de esquema más peligrosos son los que bloquean la tabla. En PostgreSQL, ALTER TABLE ... ADD COLUMN con un valor por defecto solía bloquear toda la tabla durante la reescritura. Desde PostgreSQL 11, los valores por defecto simples (constantes) son solo metadatos e instantáneos. Pero otras operaciones todavía bloquean:

sql
-- SEGURO: solo metadatos, instantáneo (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users ADD COLUMN is_premium BOOLEAN NOT NULL DEFAULT false;
 
-- PELIGROSO: reescribe toda la tabla, bloqueo completo de tabla
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(500);
ALTER TABLE users ADD COLUMN score INTEGER NOT NULL DEFAULT compute_score();
 
-- PELIGROSO: escanea toda la tabla para validar
ALTER TABLE orders ADD CONSTRAINT check_total CHECK (total_cents >= 0);

Añadir columnas NOT NULL de forma segura#

No puedes simplemente añadir NOT NULL a una tabla existente con datos — falla porque las filas existentes no tienen un valor. El enfoque ingenuo:

sql
-- Esto bloquea la tabla y la reescribe. No hagas esto en una tabla grande.
ALTER TABLE users ADD COLUMN avatar_url TEXT NOT NULL DEFAULT 'https://example.com/default.png';

Desde PostgreSQL 11, esto es realmente seguro para valores por defecto constantes — es solo metadatos. Pero si tu valor por defecto es una función o necesitas rellenar con valores calculados, usa el patrón expand-contract.

El patrón Expand-Contract#

Este es el estándar de oro para cambios de esquema sin tiempo de inactividad. Tres fases:

Fase 1: Expandir — Añade la nueva columna como nullable. Despliega código de aplicación que escriba en ambas columnas, la vieja y la nueva.

sql
-- Migración 1: Añadir la columna nullable
ALTER TABLE users ADD COLUMN normalized_email TEXT;

Fase 2: Migrar — Rellena las filas existentes en lotes. Tu aplicación ya está escribiendo en la nueva columna para datos nuevos.

sql
-- Migración 2: Rellenar en lotes (no hagas esto en una sola declaración para tablas grandes)
UPDATE users SET normalized_email = LOWER(TRIM(email))
WHERE normalized_email IS NULL
LIMIT 10000;
-- Repetir hasta que todas las filas estén rellenadas

Fase 3: Contraer — Una vez que todas las filas están rellenadas, añade la restricción NOT NULL y elimina la columna vieja (si aplica).

sql
-- Migración 3: Añadir restricción (usa NOT VALID para evitar escaneo completo de tabla, luego valida por separado)
ALTER TABLE users ADD CONSTRAINT users_normalized_email_not_null
    CHECK (normalized_email IS NOT NULL) NOT VALID;
 
-- Migración 4: Validar la restricción (toma un ShareUpdateExclusiveLock, no un AccessExclusiveLock)
ALTER TABLE users VALIDATE CONSTRAINT users_normalized_email_not_null;

El truco de NOT VALID + VALIDATE CONSTRAINT es crucial. Añadir una check constraint normalmente escanea toda la tabla mientras mantiene un bloqueo pesado. NOT VALID añade la restricción sin escanear (solo aplica a nuevas escrituras), y VALIDATE CONSTRAINT escanea con un bloqueo más ligero que no bloquea lecturas ni escrituras.

Creación de índices#

Crear índices en tablas grandes bloquea las escrituras por defecto. Siempre usa CONCURRENTLY:

sql
-- BLOQUEA ESCRITURAS: no hagas esto en una tabla en vivo
CREATE INDEX idx_users_email ON users (email);
 
-- NO BLOQUEANTE: usa esto en su lugar
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

CONCURRENTLY tarda más y no puede ejecutarse dentro de una transacción, pero no bloquea la tabla. La compensación siempre vale la pena en producción.

Las decisiones de las que me he arrepentido#

Todo esquema tiene arrepentimientos. Aquí están los míos.

Usar SERIAL en lugar de UUID para IDs externos#

Al principio de un proyecto, usé claves primarias SERIAL y las expuse directamente en URLs: /users/42, /orders/1337. Esto filtraba información (los competidores podían adivinar nuestra cantidad de usuarios), hacía la paginación predecible y se rompió cuando necesitamos fusionar bases de datos de dos regiones. Cambiar a UUIDs para IDs externos requirió una migración de varios meses.

Lección: usa UUIDs para cualquier cosa expuesta fuera de tu base de datos. Usa SERIAL/BIGSERIAL para tablas internas de join si quieres, pero nunca dejes que un entero auto-incremental aparezca en una URL.

Sin restricciones ("Las añadiremos después")#

Lanzamos una tabla sin check constraints porque "nos estamos moviendo rápido y las añadiremos después." En dos semanas, los datos tenían precios negativos, nombres vacíos y un valor de order_status de "oof" que alguien escribió durante pruebas y nunca limpió.

Añadir restricciones después del hecho requirió:

  1. Encontrar todos los datos inválidos
  2. Decidir qué hacer con ellos (corregir, eliminar o abuelearlo)
  3. Escribir una migración que rellene/corrija los datos Y añada la restricción

Esto tomó más tiempo que añadir la restricción el primer día hubiera tomado. Empieza con restricciones. Relaja si debes. Nunca al revés.

Cadenas separadas por comas en lugar de arrays o tablas de join#

Una vez almacené tags como una cadena separada por comas: "javascript,react,nextjs". Consultar era una pesadilla:

sql
-- Así es como consultas valores separados por comas. No hagas esto.
SELECT * FROM posts WHERE tags LIKE '%react%';
-- Esto también coincide con "react-native" y "preact"
 
-- Lo que debería haber hecho:
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)
);

O como mínimo, usa el tipo array nativo de 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);
 
-- Consultas limpias
SELECT * FROM posts WHERE 'react' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['react', 'nextjs'];

Los arrays son aceptables para listas simples que no necesitan sus propios atributos. En el momento en que necesitas metadatos sobre la relación (como "quién añadió este tag" o "cuándo se añadió"), necesitas una tabla de join.

Usar "type" como nombre de columna#

sql
-- Parecía bien en ese momento
CREATE TABLE notifications (
    id      BIGSERIAL PRIMARY KEY,
    type    TEXT NOT NULL,  -- "email", "sms", "push"
    -- ...
);
 
-- Entonces intentas consultarlo en literalmente cualquier ORM:
-- notification.type  ← conflicta con el sistema de tipos de cada lenguaje
-- "type" es una palabra reservada en la mayoría de dialectos SQL
-- Terminas poniéndolo entre comillas en todas partes: SELECT "type" FROM notifications

Usa kind, category o notification_type. Cualquier cosa menos type. De forma similar, evita order (usa sort_order o position), user (usa account o prefíjalo) y group (usa team o group_name).

No añadir created_at desde el principio#

Una tabla no recibió created_at porque "no lo necesitamos." Tres meses después, necesitábamos depurar un problema y no teníamos idea de cuándo se crearon los registros. Añadirlo retroactivamente significó que todas las filas existentes obtuvieron el mismo timestamp (el timestamp de la migración), haciendo que los datos históricos fueran inútiles.

Toda tabla recibe created_at. Sin excepciones. El costo es una columna. El costo de no tenerlo es incalculable hasta que lo necesitas.

Poner lógica de negocio en vistas de base de datos#

Una vez creé una cadena de vistas — active_users filtraba users, premium_active_users filtraba active_users, y una vista de reportes hacía join de todas ellas. Funcionó genial hasta que alguien cambió la tabla users y las tres vistas se rompieron silenciosamente. El planificador de consultas también tenía dificultades para optimizar a través de múltiples capas de vistas.

Las vistas son geniales para conveniencia y patrones de acceso de solo lectura. Son terribles como un lugar para codificar lógica de negocio que cambia. Mantén las reglas de negocio en el código de la aplicación donde están versionadas, probadas y desplegables. Usa vistas para atajos de reportes, no como bloques de construcción arquitectónica.

Poniéndolo todo junto#

Así se ve un esquema bien diseñado cuando combinas estos patrones. Un sistema de gestión de proyectos simplificado:

sql
-- Extensiones
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- para gen_random_uuid()
 
-- Función de trigger updated_at (reutilizable)
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();
 
-- Usuarios
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);
 
-- Proyectos
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);
 
-- Tareas
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);
 
-- Comentarios de tareas
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 auditoría (sin RLS — tabla solo 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);

Observa los patrones:

  • Toda tabla tiene tenant_id y RLS habilitado (excepto tenants y audit_log).
  • Toda tabla tiene created_at y updated_at con triggers.
  • UUIDs para todas las claves primarias (entidades expuestas al exterior).
  • Check constraints en enums de estado, longitudes y rangos.
  • Claves foráneas con comportamiento ON DELETE apropiado.
  • Índices diseñados para los patrones de acceso (tenant + project, tenant + status).
  • Soft deletes solo en users (donde la recuperación de cuenta es necesaria), archivado basado en estado en projects.

Reflexiones finales#

El diseño de esquemas no es glamuroso. Nadie ha dado nunca una charla en una conferencia titulada "Añadí check constraints a cada tabla y nos ahorró seis meses de depuración." Pero eso es exactamente lo que hace un buen diseño de esquema — previene problemas tan silenciosamente que nunca sabes que habrían existido.

Los patrones en esta publicación no son novedosos. Son el resultado de años de escribir migraciones, depurar corrupción de datos y refactorizar esquemas bajo carga de producción. Cada uno de ellos existe porque yo, o alguien con quien trabajé, lo hizo de la otra forma primero y pagó el precio.

Empieza con restricciones. Usa claves foráneas. Añade created_at a todo. Elige una convención de nombres y aplícala sin piedad. Usa RLS para multi-tenancy. Ten cuidado con JSONB. Prueba tus migraciones contra datos de tamaño producción antes de desplegarlas.

La base de datos es el fundamento. Hazlo bien, y todo lo construido encima se vuelve más simple. Hazlo mal, y ninguna cantidad de código de aplicación ingenioso podrá salvarte.

Artículos relacionados