Primera Migración - Estructura de Base de Datos

Documentación detallada de las tablas y relaciones creadas en la primera migración de la base de datos, incluyendo extensiones, tipos personalizados, y políticas de seguridad a nivel de fila.

9 min de lectura
Por Equipo 10xDev

Este documento describe las tablas creadas en la primera migración de la base de datos y sus relaciones.

Extensiones Habilitadas#

Se habilitaron las siguientes extensiones de PostgreSQL:

  • moddatetime: Proporciona una función de trigger (moddatetime) para actualizar automáticamente una columna de marca de tiempo (updated_at) cuando se modifica una fila.
  • uuid-ossp: Proporciona funciones para generar UUIDs (Universally Unique Identifiers), como uuid_generate_v4().

Tipos Personalizados (ENUMs)#

Se definieron los siguientes tipos enumerados para garantizar la consistencia de los datos:

  • pricing_type: Define los tipos de precios (one_time, recurring).
  • pricing_plan_interval: Define los intervalos para los planes de precios recurrentes (day, week, month, year).
  • subscription_status: Define los posibles estados de una suscripción (trialing, active, canceled, incomplete, incomplete_expired, past_due, unpaid, paused).
  • purchase_status: Define los posibles estados de una compra única (completed, pending, refunded).
  • user_role: Define los roles de usuario dentro de la aplicación (user, editor, admin).

Tablas y sus Relaciones#

A continuación, se describen las tablas creadas y sus relaciones:

1 users#

  • Propósito: Almacena la información de los usuarios de la aplicación.
  • Columnas:
    • id (UUID, PRIMARY KEY, NOT NULL): Identificador único del usuario. Referencia a auth.users(id) con la acción ON DELETE CASCADE, lo que significa que si un usuario es eliminado de la tabla de autenticación, también se eliminará su registro en esta tabla.
    • full_name (TEXT): Nombre completo del usuario.
    • avatar_url (TEXT): URL del avatar del usuario.
    • billing_address (JSONB): Información de la dirección de facturación del usuario en formato JSON.
    • payment_method (JSONB): Detalles del método de pago del usuario en formato JSON ¡ADVERTENCIA! Se debe considerar cuidadosamente la sensibilidad de estos datos y las implicaciones de seguridad al almacenarlos directamente en la base de datos. Es recomendable utilizar soluciones de procesamiento de pagos seguras (como Stripe) y almacenar solo los identificadores necesarios.
    • role (user_role, NOT NULL, DEFAULT 'user'): Rol del usuario, con 'user' como valor predeterminado.
    • updated_at (TIMESTAMP WITH TIME ZONE, NOT NULL, DEFAULT timezone('utc'::text, now())): Marca de tiempo de la última actualización del registro.
  • Triggers:
    • handle_updated_at: Se ejecuta antes de cada operación de UPDATE en la tabla users y actualiza automáticamente la columna updated_at con la hora actual.
  • Funciones:
    • public.get_my_role(): Función auxiliar que devuelve el rol del usuario autenticado actualmente utilizando auth.uid(). Se utiliza para implementar políticas de seguridad a nivel de fila basadas en roles.
  • Seguridad a Nivel de Fila (RLS):
    • Política "Can view own user data.": Permite a los usuarios seleccionar (ver) solo sus propios datos (auth.uid() = id).
    • Política "Can update own user data.": Permite a los usuarios actualizar solo sus propios datos (auth.uid() = id).
    • Política "Admins can view all user data.": Permite a los usuarios con el rol 'admin' seleccionar (ver) todos los datos de los usuarios (public.get_my_role() = 'admin').
    • Política "Admins and Editors can update any user data.": Permite a los usuarios con los roles 'admin' o 'editor' actualizar cualquier dato de usuario (public.get_my_role() in ('admin', 'editor')).

2 customers#

  • Propósito: Mapea los IDs de los usuarios de la aplicación con sus IDs de cliente en Stripe (u otro proveedor de pagos). Esta tabla se considera privada.
  • Columnas:
    • id (UUID, PRIMARY KEY, NOT NULL): Identificador único del cliente. Referencia a auth.users(id) con la acción ON DELETE CASCADE.
    • stripe_customer_id (TEXT): ID del cliente correspondiente en Stripe.
  • Constraints:
    • unique_stripe_customer_id: Asegura que cada stripe_customer_id sea único.
    • check_stripe_id: Valida que el stripe_customer_id tenga el formato esperado ('cus_%').
  • Seguridad a Nivel de Fila (RLS): Habilitada, pero no se definen políticas específicas en este fragmento. Generalmente, el acceso a esta tabla estaría restringido al backend.

3 products#

  • Propósito: Almacena la información de los productos, sincronizada desde Stripe (u otra plataforma) a través de webhooks.
  • Columnas:
    • id (TEXT, PRIMARY KEY): Identificador único del producto (generalmente el ID de Stripe).
    • active (BOOLEAN): Indica si el producto está activo.
    • name (TEXT): Nombre del producto.
    • description (TEXT): Descripción del producto.
    • image (TEXT): URL de la imagen del producto.
    • metadata (JSONB): Metadatos adicionales del producto en formato JSON.
    • created_at (TIMESTAMP WITH TIME ZONE, NOT NULL, DEFAULT timezone('utc'::text, now())): Marca de tiempo de la creación del registro.
    • updated_at (TIMESTAMP WITH TIME ZONE, NOT NULL, DEFAULT timezone('utc'::text, now())): Marca de tiempo de la última actualización del registro.
  • Triggers:
    • handle_updated_at: Se ejecuta antes de cada operación de UPDATE y actualiza la columna updated_at.
  • Seguridad a Nivel de Fila (RLS):
    • Política "Allow public read-only access.": Permite a cualquier usuario seleccionar (ver) los datos de los productos.

4 prices#

  • Propósito: Almacena la información de los precios asociados a los productos, sincronizada desde Stripe (u otra plataforma).
  • Columnas:
    • id (TEXT, PRIMARY KEY): Identificador único del precio (generalmente el ID de Stripe).
    • product_id (TEXT, NOT NULL): Identificador del producto al que pertenece este precio. Referencia a products(id) con la acción ON DELETE CASCADE. Si un producto es eliminado, sus precios asociados también se eliminarán.
    • active (BOOLEAN): Indica si el precio está activo.
    • description (TEXT): Descripción del precio.
    • unit_amount (BIGINT): Precio unitario en la unidad mínima de la moneda.
    • currency (TEXT, CHECK char_length(currency) = 3): Código de la moneda (ISO 4217).
    • type (pricing_type): Tipo de precio (one_time o recurring).
    • interval (pricing_plan_interval): Intervalo para precios recurrentes (day, week, month, year).
    • interval_count (INTEGER): Número de intervalos entre facturaciones para precios recurrentes.
    • trial_period_days (INTEGER): Número de días del período de prueba (si aplica).
    • metadata (JSONB): Metadatos adicionales del precio en formato JSON.
    • created_at (TIMESTAMP WITH TIME ZONE, NOT NULL, DEFAULT timezone('utc'::text, now())): Marca de tiempo de la creación del registro.
    • updated_at (TIMESTAMP WITH TIME ZONE, NOT NULL, DEFAULT timezone('utc'::text, now())): Marca de tiempo de la última actualización del registro.
  • Triggers:
    • handle_updated_at: Se ejecuta antes de cada operación de UPDATE y actualiza la columna updated_at.
  • Índices:
    • idx_prices_product_id: Índice en la columna product_id para optimizar las consultas que filtran por producto.
  • Seguridad a Nivel de Fila (RLS):
    • Política "Allow public read-only access.": Permite a cualquier usuario seleccionar (ver) los datos de los precios.

5 subscriptions#

  • Propósito: Almacena la información de las suscripciones de los usuarios, sincronizada desde Stripe (u otra plataforma).
  • Columnas:
    • id (TEXT, PRIMARY KEY): Identificador único de la suscripción (generalmente el ID de Stripe).
    • user_id (UUID, NOT NULL): Identificador del usuario al que pertenece la suscripción. Referencia a auth.users(id) con la acción ON DELETE CASCADE.
    • status (subscription_status): Estado actual de la suscripción.
    • metadata (JSONB): Metadatos adicionales de la suscripción en formato JSON.
    • price_id (TEXT, NOT NULL): Identificador del precio asociado a la suscripción. Referencia a prices(id) con la acción ON DELETE RESTRICT. No se permitirá eliminar un precio si existen suscripciones activas con ese precio.
    • quantity (INTEGER): Cantidad de unidades de la suscripción.
    • cancel_at_period_end (BOOLEAN): Indica si la suscripción se cancelará al final del período actual.
    • created (TIMESTAMP WITH TIME ZONE, NOT NULL, DEFAULT timezone('utc'::text, now())): Marca de tiempo de la creación de la suscripción.
    • current_period_start (TIMESTAMP WITH TIME ZONE, NOT NULL, DEFAULT timezone('utc'::text, now())): Marca de tiempo del inicio del período de facturación actual.
    • current_period_end (TIMESTAMP WITH TIME ZONE, NOT NULL, DEFAULT timezone('utc'::text, now())): Marca de tiempo del fin del período de facturación actual.
    • ended_at (TIMESTAMP WITH TIME ZONE): Marca de tiempo de la finalización de la suscripción (si ha terminado).
    • cancel_at (TIMESTAMP WITH TIME ZONE): Marca de tiempo en la que se cancelará la suscripción.
    • canceled_at (TIMESTAMP WITH TIME ZONE): Marca de tiempo en la que se canceló la suscripción.
    • trial_start (TIMESTAMP WITH TIME ZONE): Marca de tiempo del inicio del período de prueba (si aplica).
    • trial_end (TIMESTAMP WITH TIME ZONE): Marca de tiempo del fin del período de prueba (si aplica).
  • Índices:
    • idx_subscriptions_user_id: Índice en la columna user_id para optimizar las consultas que filtran por usuario.
  • Seguridad a Nivel de Fila (RLS):
    • Política "Can only view own subs data.": Permite a los usuarios seleccionar (ver) solo sus propias suscripciones (auth.uid() = user_id).
    • Política "Subscriptions managed via webhooks.": Impide todas las operaciones (SELECT, INSERT, UPDATE, DELETE) directamente en la tabla, asumiendo que la gestión se realiza a través de webhooks.
    • Política "Admins can view all subscriptions": Permite a los usuarios con el rol 'admin' seleccionar (ver) todas las suscripciones.

6 purchases#

  • Propósito: Registra las compras únicas de productos realizadas por los usuarios. Los registros probablemente se crean a través del backend o webhooks.
  • Columnas:
    • id (UUID, PRIMARY KEY, DEFAULT uuid_generate_v4()): Identificador único de la compra generado automáticamente.
    • user_id (UUID, NOT NULL): Identificador del usuario que realizó la compra. Referencia a auth.users(id) con la acción ON DELETE CASCADE.
    • product_id (TEXT, NOT NULL): Identificador del producto comprado. Referencia a products(id) con la acción ON DELETE RESTRICT.
    • price_id (TEXT, NOT NULL): Identificador del precio pagado por el producto. Referencia a prices(id) con la acción ON DELETE RESTRICT.
    • created_at (TIMESTAMP WITH TIME ZONE, NOT NULL, DEFAULT timezone('utc'::text, now())): Marca de tiempo de la creación del registro.
    • status (purchase_status, NOT NULL, DEFAULT 'pending'): Estado actual de la compra ('completed', 'pending', 'refunded'). El valor predeterminado es 'pending'.
    • payment_intent_id (TEXT): ID del PaymentIntent asociado (de Stripe, por ejemplo).
    • session_id (TEXT): ID de la sesión de pago (de Stripe Checkout, por ejemplo).
    • metadata (JSONB): Metadatos adicionales de la compra en formato JSON.
    • updated_at (TIMESTAMP WITH TIME ZONE, NOT NULL, DEFAULT timezone('utc'::text, now())): Marca de tiempo de la última actualización del registro.
  • Triggers:
    • handle_updated_at: Se ejecuta antes de cada operación de UPDATE y actualiza la columna updated_at.
  • Índices:
    • purchases_user_id_idx: Índice en la columna user_id.
    • purchases_product_id_idx: Índice en la columna product_id.
    • purchases_price_id_idx: Índice en la columna price_id.
  • Seguridad a Nivel de Fila (RLS):
    • Política "Users can view their own purchases": Permite a los usuarios seleccionar (ver) solo sus propias compras (auth.uid() = user_id).
    • Política "Admins can view all purchases": Permite a los usuarios con el rol 'admin' seleccionar (ver) todas las compras.
    • No se definen políticas INSERT, UPDATE o DELETE para usuarios, lo que sugiere que estas operaciones se gestionan a través del backend.

Publicación para Realtime#

Se creó una publicación llamada supabase_realtime para habilitar la escucha en tiempo real de las tablas products y prices. Esto permite a los clientes (frontend) recibir notificaciones automáticas cuando los datos de estas tablas cambian.