Arquitectura de datos para TrueRate

En el artículo anterior vimos el problema fundamental al que nos enfrentamos los freelancers: creemos que cobramos una tarifa por hora muy superior a la real, porque ignoramos el tiempo invisible (reuniones, gestión, revisiones) y los costes fijos de nuestras suscripciones de software.

Para solucionar esto de raíz mediante software, lo primero que necesitamos no es una interfaz bonita, sino unos cimientos de datos sólidos, limpios y a prueba de errores.

En este post te cuento cómo he diseñado el modelo relacional de TrueRate y cómo lo materializo en PostgreSQL, gestionado por Prisma con migraciones. Al final verás las consultas SQL clave para calcular la rentabilidad real.

1. Los 8 pilares del modelo de datos

Para mantener un sistema escalable, seguro y preparado para producción, estructuramos la arquitectura en 8 tablas clave:

1. auth_credentials: La caja fuerte del sistema; aísla las contraseñas y las credenciales de acceso por pura seguridad.
2. users: El perfil de negocio del freelancer (vinculado 1:1 con las credenciales) donde guardamos la moneda y la tarifa base.
3. clients: La cartera de clientes de cada profesional.
4. projects: El núcleo de todo; conecta al usuario con su cliente y define el presupuesto cerrado y el estado del trabajo.
5. time_entries: El registro de las horas dedicadas a cada tarea, guardadas siempre en minutos para no perder precisión.
6. fixed_costs: Los gastos fijos del usuario, como las suscripciones de software y herramientas que pagas cada mes.
7. project_expenses: Los costes directos y específicos de un proyecto (como plugins de pago o licencias concretas).
8. payments: El control del dinero real, registra cada anticipo o cobro fraccionado para saber qué entra de verdad.

Decisiones de diseño clave

• Separación de Auth y Perfil: Aislar auth_credentials de users nos permite blindar la seguridad y facilita una futura migración a proveedores externos (como Supabase Auth o Firebase) sin romper la lógica de negocio.
• UUIDs como Claves Primarias: Evitamos la numeración secuencial predecible por seguridad y escalabilidad futura.
• Tipos DECIMAL(10,2): Obligatorios para cualquier dato monetario, evitando los fatales errores de redondeo de los tipos FLOAT.
• TIMESTAMPTZ: Fechas y horas con zona horaria integrada para mantener consistencia global.

Gestión con Prisma (schema-first): Este modelo lo defino en schema.prisma y lo aplico a PostgreSQL mediante migraciones (prisma migrate dev). Así Prisma es la fuente de verdad del esquema: genera los UUIDs y los timestamps automáticamente, y cada cambio queda versionado en un historial de migraciones. Las consultas SQL de la siguiente sección se ejecutan contra estas tablas, pero la estructura vive en el código.

2. El reto analítico: Calculando la True Hourly Rate

El verdadero valor de la arquitectura reside en cómo cruzamos la información para calcular la tarifa real por hora. Al hacerlo mediante SQL puro, nos topamos con dos retos clásicos de reporting financiero que resolvimos con buenas prácticas:

• Proyectos sin gastos: Si usas un INNER JOIN tradicional con los gastos directos, los proyectos limpios desaparecen de la consulta. Lo solucionamos empleando un LEFT JOIN combinado con COALESCE(..., 0.00) para rellenar los huecos vacíos de forma segura.
• El peligro de la división por cero: Un proyecto recién creado en fase de planificación no tiene horas trabajadas. Dividir entre cero tumba la ejecución en PostgreSQL. La solución limpia fue combinar NULLIF (para convertir ceros en NULL) y envolverlo con un COALESCE exterior para asegurar un valor por defecto de 0.00.

La consulta definitiva (Basada en Cash-Flow)

Esta es la consulta analítica definitiva que cruza los cobros reales, los gastos directos y las horas trabajadas para darte la rentabilidad neta y la tarifa real de cada proyecto:

SELECT 
    p.name AS project_name,
    c.name AS client_name,
    p.agreed_price AS ingreso_total_pactado,
    COALESCE(SUM(DISTINCT pay.amount), 0.00) AS ingreso_total_cobrado,
    COALESCE(SUM(DISTINCT pe.amount), 0.00) AS total_direct_expenses,
    ROUND(COALESCE(SUM(DISTINCT t.duration_minutes), 0) / 60.0, 2) AS total_hours,
    ROUND(
        COALESCE(
            (COALESCE(SUM(DISTINCT pay.amount), 0.00) - COALESCE(SUM(DISTINCT pe.amount), 0.00)) / NULLIF(SUM(DISTINCT t.duration_minutes) / 60.0, 0),
            0.00
        ), 2
    ) AS true_hourly_rate_cashflow
FROM projects p
INNER JOIN clients c ON p.client_id = c.client_id
LEFT JOIN payments pay ON p.project_id = pay.project_id
LEFT JOIN project_expenses pe ON p.project_id = pe.project_id
LEFT JOIN time_entries t ON p.project_id = t.project_id
GROUP BY p.project_id, p.name, c.name, p.agreed_price;