# Scripts SQL de GoberData PostgreSQL 15 o superior. Todos los scripts son **idempotentes** (se pueden correr varias veces) y **transaccionales** (si algo falla, no queda nada a medias). --- ## Qué archivo usar **Tienes una base con datos (tu producción actual):** ```bash pg_dump -Fc -U postgres goberdata > respaldo_$(date +%F).dump # PRIMERO psql -U postgres -d goberdata -v ON_ERROR_STOP=1 -f 01-goberdata-actualizacion.sql psql -U postgres -d goberdata -v ON_ERROR_STOP=1 -f 02-goberdata-modulos.sql psql -U postgres -d goberdata -v ON_ERROR_STOP=1 -f 03-goberdata-reconciliacion.sql psql -U postgres -d goberdata -v ON_ERROR_STOP=1 -f 04-goberdata-datos-base.sql php artisan goberdata:cifrar-llaves # cifra las llaves que estaban en claro ``` En ese orden. El 02 depende de tablas que crea el 01. **Empiezas de cero (servidor nuevo, entorno de pruebas, otro cliente):** ```bash createdb -U postgres goberdata psql -U postgres -d goberdata -v ON_ERROR_STOP=1 -f 00-goberdata-esquema-completo.sql php artisan db:seed --class=RolesAndPermissionsSeeder php artisan db:seed --class=ColombiaCompleteSeeder psql -U postgres -d goberdata -f 04-goberdata-datos-base.sql ``` No mezcles: el `00` ya contiene lo que hacen el 01, 02 y 03. --- ## Qué hay en cada archivo | Archivo | Qué hace | |---|---| | `00-goberdata-esquema-completo.sql` | Las 139 tablas de una vez, con sus 228 llaves foráneas. Solo para bases nuevas. | | `01-goberdata-actualizacion.sql` | Convierte `campaigns` en tabla de ambientes, prepara la demo con credenciales, completa el modelo de membresías, cifra llaves y arregla las restricciones que impedían operar. | | `02-goberdata-modulos.sql` | Las 50 tablas de los módulos que estaban muertos: territorios, PQRS, GOTV, jornada electoral, WhatsApp, correo, afiches QR, KPIs, presupuesto, encuestas públicas. | | `03-goberdata-reconciliacion.sql` | Columnas que siete tablas existentes no tenían pese a que sus modelos las declaran. | | `04-goberdata-datos-base.sql` | Planes de membresía, categorías de PQRS y presupuesto, equipos de voluntarios. | --- ## Qué cambió y por qué ### El multi-tenant no existía `database/migrations/tenant/` tiene 22 migraciones que **nunca se ejecutaron**: eran para un esquema de base-por-cliente con `stancl/tenancy` que jamás se activó, porque su service provider no está registrado en `bootstrap/providers.php`. Consecuencia medible en tu volcado: la base tenía **85 tablas** pero los modelos de Eloquent necesitan **139**. Cincuenta y dos modelos apuntaban a tablas inexistentes, y con ellos módulos completos —GOTV, PQRS, WhatsApp, jornada electoral, KPIs, presupuesto, territorios— que no tenían dónde guardar nada. El script `02` los crea, con una diferencia clave respecto a las migraciones de tenant: **toda tabla de datos de campaña lleva `campaign_id` con FK y `ON DELETE CASCADE`**. Eso es lo que permite el aislamiento entre candidatos y lo que hace que purgar una demo vencida borre sus datos en cascada. ### Restricciones que impedían operar **`campaign_user.role`** tenía un CHECK que solo admitía `campaign_admin`, `coordinator` y `volunteer`. Pero todo el código asigna `candidate`, `admin`, `campaign_manager`… Vincular al dueño con su campaña —o sea, crear cualquier ambiente— fallaba con *violates check constraint*. Se elimina el CHECK: el conjunto de roles lo gobiernan spatie/laravel-permission y `config/goberdata_menu.php`, que es donde debe estar. **`donations.receipt_number`** era UNIQUE **global**. Dos candidatos no podían tener ambos el recibo `REC-00001`. Pasa a `UNIQUE (campaign_id, receipt_number)`, que además es como el CNE exige la numeración: consecutiva por campaña, no por plataforma. **`subscriptions.campaign_id`** era NOT NULL. Eso asume que el ambiente existe antes que la orden de pago, y en el flujo real es al revés: alguien llega a `/planes` sin cuenta, paga, y solo entonces se aprovisiona. Con la columna obligatoria, el caso más importante del embudo moría al crear la orden. **`street_surveys`** mezclaba dos conceptos bajo un mismo nombre: la tabla en producción es una *plantilla* de encuesta (`name`, `questions`, `created_by`, todos NOT NULL) mientras el modelo `StreetSurvey` representa una *encuesta de calle* hecha en terreno. Insertar una encuesta fallaba con *null value in column "name"*. Las plantillas ya viven en `survey_templates`, así que esas columnas se relajan. **`social_media_posts`** tenía tres columnas: `id`, `created_at`, `updated_at`. Su migración fue una de las duplicadas descartadas. El módulo de monitoreo de redes no podía guardar un solo post. ### Cifrado de llaves `ai_providers.api_key` y las llaves de WhatsApp, redes y SMTP en `campaigns` eran `varchar(255)` **sin cifrar**. La llave de Claude u OpenAI de cada candidato quedaba legible en la base, en cada respaldo, y —en el caso de `ai_providers`— en la respuesta JSON del endpoint de proveedores, que cualquier usuario del ambiente podía pedir. Las columnas pasan a `TEXT` porque el texto cifrado de Laravel no cabe en 255 caracteres: **si activas el cast sin ampliar la columna, el guardado falla**. Después de correr el `01`, ejecuta: ```bash php artisan goberdata:cifrar-llaves --dry-run # ver qué haría php artisan goberdata:cifrar-llaves ``` Ese comando cifra lo que ya estuviera en claro. Es seguro correrlo varias veces: lo que ya está cifrado se salta. > **Cuidado con `APP_KEY`.** Una vez cifradas las llaves, cambiar `APP_KEY` > las vuelve ilegibles para siempre. Guárdala donde guardes las > contraseñas de producción. ### Nombres de tabla de WhatsApp Los modelos se llaman `WhatsAppTemplate`, `WhatsAppCampaign`, etc. Eloquent parte el CamelCase y deduce `whats_app_templates`, con la palabra cortada por la mitad. Las tablas se crean con el nombre correcto (`whatsapp_*`) y los modelos declaran `protected $table` explícitamente. Lo mismo con `ContentAI` → `content_a_i_s` y `QrHeatmapData` → `qr_heatmap_data`, que ya existían con esos nombres. ### Un cast que faltaba `volunteers.availability` es una columna JSON, pero el modelo `Volunteer` no la casteaba. SQLite acepta cualquier texto en una columna JSON, así que en desarrollo no se notaba; PostgreSQL rechaza el INSERT con *invalid input syntax for type json*. Es decir: **registrar un voluntario fallaba en producción y funcionaba en las pruebas locales**. Corregido en el modelo. --- ## Cómo se verificó No es teoría. El procedimiento fue: 1. Restaurar tu volcado de producción (85 tablas, con datos) en un PostgreSQL limpio. 2. Aplicar `01`, `02`, `03` y `04` encima. Cero errores. 3. Comprobar con Eloquent que **los 130 modelos** encuentran su tabla y que cada columna de sus `$fillable` existe. 4. Correr el flujo completo contra esa base: solicitud de demo → aprovisionamiento con datos sembrados → aislamiento entre dos campañas → snapshot del panel → checkout → webhook → conversión de demo a cliente → cifrado de llaves → estadísticas → vencimiento. 5. Volcar el esquema resultante y crear una base **desde cero** con él: 139 tablas, 228 llaves foráneas, cero errores. Correr el mismo flujo encima, otra vez en verde. ```bash # Comprobación rápida después de aplicar los scripts psql -U postgres -d goberdata -tc \ "select count(*) from information_schema.tables where table_schema='public';" # → 139 php artisan test --filter=DemoYMembresia # → 9 passed ``` --- ## Qué falta - Las tablas nuevas están **vacías**. Los módulos que resucitan (GOTV, PQRS, WhatsApp, jornada) necesitan que sus controladores y vistas se conecten: hoy varios sirven datos de la plantilla estática. Es la tarea 2 del prompt de Cursor. - `territories` guarda `boundary` y `center` como texto. Si vas a usar el mapa con polígonos de verdad, instala PostGIS y cámbialas a `geometry(Polygon,4326)` y `geometry(Point,4326)`. Se dejaron en texto para no obligarte a la extensión. - Si creas la base con el `00`, marca las migraciones de Laravel como ejecutadas o `php artisan migrate` intentará rehacerlas.