Base de datos de control — ProvisioningControl

Documentación de la infraestructura de base de datos que soporta el flujo de Autenticación OAuth y Aprovisionamiento Automático de Bases de Datos.

Infraestructura

Elemento	Valor
Motor	SQL Server 2022 (contenedor Docker)
Nombre del contenedor	`sqlserver2022`
Base de datos	`ProvisioningControl`
Red Docker	`sql_server_databases` (interna, sin puertos publicados al host)
Acceso externo	Ninguno — solo alcanzable por contenedores en la misma red Docker

Por qué así

El backend debe correr en un contenedor conectado a la red sql_server_databases para resolver el host sqlserver2022 por nombre. Al no publicar puertos al host, la base de datos de control queda inalcanzable desde fuera de la red interna de Docker.

Modelo de datos

`Users`

Registro de usuarios autenticados vía OAuth (Google / GitHub).

Columna	Tipo	Notas
`Id`	`INT IDENTITY(1,1)`	PK
`FullName`	`NVARCHAR(150)`
`Email`	`NVARCHAR(200)`	No es única por sí sola
`Avatar`	`NVARCHAR(500)`	URL de la foto de perfil
`Provider`	`NVARCHAR(20)`	`google` \| `github`
`ProviderUserId`	`NVARCHAR(200)`	ID externo real del proveedor OAuth
`CreatedAt`	`DATETIME2`	Default `SYSUTCDATETIME()`
`LastLoginAt`	`DATETIME2`	Nullable, se actualiza en cada login
`IsActive`	`BIT`	Default `1`

Constraint clave: UNIQUE (Provider, ProviderUserId) — la deduplicación se hace por proveedor + id externo, no por email.

`Databases`

Registro de aprovisionamiento de bases de datos MySQL por usuario.

Columna	Tipo	Notas
`Id`	`INT IDENTITY(1,1)`	PK
`UserId`	`INT`	FK → `Users(Id)`
`Host`	`NVARCHAR(200)`	Host interno de MySQL
`Port`	`INT`	Default `3306`
`DatabaseName`	`NVARCHAR(100)`	Única
`MysqlUsername`	`NVARCHAR(100)`	Única
`EncryptedPassword`	`VARBINARY(MAX)`	Cifrada con AES_256 — nunca en texto plano
`Engine`	`NVARCHAR(20)`	Default `'MySQL'`
`CreatedAt`	`DATETIME2`	Default `SYSUTCDATETIME()`
`Status`	`NVARCHAR(20)`	`Pending` \| `Active` \| `Error` \| `Suspended` \| `Deleted`
`UsedSpaceMB`	`DECIMAL(10,2)`	Actualizado por job periódico
`MaxSpaceMB`	`DECIMAL(10,2)`	Default `500`
`LastActivityAt`	`DATETIME2`	Nullable

`LoginHistory`

Historial de inicios de sesión (base para métricas de landing y dashboard).

Columna	Tipo	Notas
`Id`	`INT IDENTITY(1,1)`	PK
`UserId`	`INT`	FK → `Users(Id)`
`LoginAt`	`DATETIME2`	Default `SYSUTCDATETIME()`
`Provider`	`NVARCHAR(20)`
`IpAddress`	`NVARCHAR(50)`	Nullable

`AuditLog`

Auditoría de operaciones importantes del sistema.

Columna	Tipo	Notas
`Id`	`INT IDENTITY(1,1)`	PK
`UserId`	`INT`	Nullable (acciones de sistema)
`Action`	`NVARCHAR(100)`	Ej: `USER_REGISTERED`, `DB_PROVISIONED`, `CREDENTIALS_VIEWED`
`Entity`	`NVARCHAR(50)`	Ej: `User`, `Database`
`EntityId`	`INT`	Nullable
`Details`	`NVARCHAR(MAX)`	Nullable
`OccurredAt`	`DATETIME2`	Default `SYSUTCDATETIME()`
`IpAddress`	`NVARCHAR(50)`	Nullable
`Result`	`NVARCHAR(20)`	`Success` \| `Failure`

Índices

IX_LoginHistory_UserId  ON LoginHistory(UserId)
IX_AuditLog_UserId      ON AuditLog(UserId)
IX_Databases_UserId     ON Databases(UserId)
IX_Databases_Status     ON Databases(Status)

Cifrado

Para proteger Databases.EncryptedPassword (cifrado reversible, no hash):

Objeto	Nombre
Certificado	`ProvisioningCert`
Symmetric Key	`ProvisioningSymmetricKey` (AES_256)

Patrón de uso dentro de un SP:

OPEN SYMMETRIC KEY ProvisioningSymmetricKey
    DECRYPTION BY CERTIFICATE ProvisioningCert;

-- Cifrar al insertar:
EncryptByKey(Key_GUID('ProvisioningSymmetricKey'), @PlainPassword)

-- Descifrar al consultar:
CONVERT(NVARCHAR(200), DecryptByKey(EncryptedPassword))

CLOSE SYMMETRIC KEY ProvisioningSymmetricKey;

Por qué así

El backend nunca cifra/descifra directamente — esa lógica vive únicamente dentro de los Stored Procedures. Esto evita que la clave de cifrado o la contraseña en claro pasen por capas intermedias más expuestas que el propio motor de base de datos.

Functions

`fn_UserExists(@Provider, @ProviderUserId) → BIT`

Verifica si un usuario ya existe por proveedor + id externo. Retorna 1 si existe, 0 si no. Usada por sp_RegisterOrUpdateUser para decidir si insertar o actualizar.

`fn_GenerateDbName(@UserId) → NVARCHAR(100)`

Genera un nombre único de base de datos MySQL concatenando db_user_ + UserId + timestamp (yyyyMMddHHmmss). Garantiza unicidad por tiempo de ejecución.

`fn_CalculateServiceAvailability() → DECIMAL(5,2)`

Calcula el porcentaje de disponibilidad del servicio como (BDs activas / BDs totales) * 100. Retorna 100.00 si no hay bases registradas aún.

`fn_ActiveUsers(@Days) → INT`

Cuenta usuarios distintos con al menos un login en los últimos @Days días. Usada por vw_LandingMetrics.

Views

`vw_UserDashboard`

Join entre Databases y Users. Expone toda la información que necesita el dashboard del usuario: host, puerto, nombre de BD, usuario MySQL, motor, fechas, estado, espacio usado y máximo.

`vw_LandingMetrics`

Agrega todas las estadísticas de la landing page en una sola fila: total usuarios, total BDs, BDs activas, total logins, usuarios activos (últimos 30 días) y disponibilidad del servicio. Usa fn_ActiveUsers y fn_CalculateServiceAvailability internamente.

`vw_ConnectionCredentials`

Vista restringida con los datos de conexión de cada base de datos. La contraseña se expone cifrada (EncryptedPassword) — la desencriptación ocurre únicamente dentro de sp_GetCredentials.

Stored Procedures

`sp_RegisterOrUpdateUser`

Parámetros: @Provider, @ProviderUserId, @FullName, @Email, @Avatar, @IpAddress
Qué hace: Si el usuario no existe lo inserta en Users y registra USER_REGISTERED en AuditLog. Si ya existe, actualiza LastLoginAt, FullName y Avatar, y registra USER_LOGIN. En ambos casos inserta en LoginHistory. Retorna UserId e IsExistingUser para que el backend sepa si debe disparar el aprovisionamiento.
Cuándo lo llama el backend: En cada login OAuth exitoso.

`sp_ProvisionDatabase`

Parámetros: @UserId, @Host, @PlainPassword, @IpAddress
Qué hace: Genera nombre de BD y usuario MySQL con fn_GenerateDbName, cifra la contraseña con ProvisioningSymmetricKey, inserta en Databases con estado Pending, registra en AuditLog. Retorna las credenciales en claro una sola vez para que el backend ejecute el CREATE DATABASE / CREATE USER / GRANT en MySQL.
Cuándo lo llama el backend: Inmediatamente después de sp_RegisterOrUpdateUser cuando IsExistingUser = 0.

`sp_ConfirmProvisioning`

Parámetros: @DatabaseId, @Success, @ErrorDetail (opcional)
Qué hace: Actualiza el Status de la base de datos a Active si @Success = 1, o a Error si @Success = 0. Registra el resultado en AuditLog. Retorna el FinalStatus.
Cuándo lo llama el backend: Después de ejecutar el CREATE DATABASE en MySQL, para confirmar si resultó exitoso o falló.

`sp_GetDashboard`

Parámetros: @UserId
Qué hace: Consulta vw_UserDashboard filtrando por UserId. Retorna toda la información del panel del usuario: conexión, estado, espacio, fechas.
Cuándo lo llama el backend: Cuando el usuario carga su dashboard.

`sp_GetCredentials`

Parámetros: @UserId, @IpAddress
Qué hace: Abre ProvisioningSymmetricKey, consulta vw_ConnectionCredentials desencriptando EncryptedPassword, cierra la clave. Registra CREDENTIALS_VIEWED en AuditLog con la IP del usuario.
Cuándo lo llama el backend: Cuando el usuario solicita ver sus credenciales de conexión.

`sp_GetLandingMetrics`

Parámetros: ninguno
Qué hace: Consulta vw_LandingMetrics y retorna una sola fila con todas las estadísticas de la plataforma.
Cuándo lo llama el backend: Al cargar la landing page (puede cachearse en el backend si el tráfico lo justifica).

`sp_UpdateSpaceAndActivity`

Parámetros: @DatabaseName, @UsedSpaceMB
Qué hace: Busca la base de datos por nombre, actualiza UsedSpaceMB y LastActivityAt, registra en AuditLog. Lanza error si el nombre no existe.
Cuándo lo llama el backend: Desde un job periódico que consulta information_schema.TABLES en MySQL para obtener el uso real de disco y lo reporta aquí.

Usuario de servicio para el backend

Elemento	Valor
Login (nivel servidor)	`backend_service`
Usuario (nivel base)	`backend_service`
Permisos sobre tablas	Ninguno
Permisos sobre views	`SELECT` en `vw_UserDashboard`, `vw_LandingMetrics`, `vw_ConnectionCredentials`
Permisos sobre SPs	`EXECUTE` en los 7 SPs

Por qué así

El usuario de servicio no tiene acceso directo a las tablas: toda la superficie de lectura pasa por views y toda la escritura pasa por Stored Procedures. Aunque el backend fuera comprometido, no podría ejecutar queries arbitrarios contra Users o Databases.

Conexión desde el backend

Connection string:

Server=sqlserver2022,1433;Database=ProvisioningControl;User Id=backend_service;Password=<password>;TrustServerCertificate=True;

Host: sqlserver2022 (nombre del contenedor, resuelto por DNS interno de Docker).
Puerto: 1433 interno — no publicado al host.
El backend debe declarar sql_server_databases en su docker-compose.yml.
Todas las llamadas van por EXEC dbo.NombreDelSP @param = @valor — nunca queries directos a tablas.

Infraestructura​

Modelo de datos​

Users​

Databases​

LoginHistory​

AuditLog​

Índices​

Cifrado​

Functions​

fn_UserExists(@Provider, @ProviderUserId) → BIT​

fn_GenerateDbName(@UserId) → NVARCHAR(100)​

fn_CalculateServiceAvailability() → DECIMAL(5,2)​

fn_ActiveUsers(@Days) → INT​

Views​

vw_UserDashboard​

vw_LandingMetrics​

vw_ConnectionCredentials​

Stored Procedures​

sp_RegisterOrUpdateUser​

sp_ProvisionDatabase​

sp_ConfirmProvisioning​

sp_GetDashboard​

sp_GetCredentials​

sp_GetLandingMetrics​

sp_UpdateSpaceAndActivity​

Usuario de servicio para el backend​

Conexión desde el backend​

Flujo completo de llamadas al backend​

Infraestructura

Modelo de datos

`Users`

`Databases`

`LoginHistory`

`AuditLog`

Índices

Cifrado

Functions

`fn_UserExists(@Provider, @ProviderUserId) → BIT`

`fn_GenerateDbName(@UserId) → NVARCHAR(100)`

`fn_CalculateServiceAvailability() → DECIMAL(5,2)`

`fn_ActiveUsers(@Days) → INT`

Views

`vw_UserDashboard`

`vw_LandingMetrics`

`vw_ConnectionCredentials`

Stored Procedures

`sp_RegisterOrUpdateUser`

`sp_ProvisionDatabase`

`sp_ConfirmProvisioning`

`sp_GetDashboard`

`sp_GetCredentials`

`sp_GetLandingMetrics`

`sp_UpdateSpaceAndActivity`

Usuario de servicio para el backend

Conexión desde el backend

Flujo completo de llamadas al backend