Patrones de base de datos PostgreSQL para LendusFind. Usar al crear migraciones, modificar schema o trabajar con JSONB.
Seguir estos patrones al crear migraciones, modificar el schema de PostgreSQL, o trabajar con campos JSONB, UUIDs o índices.
Schema::create('applications', function (Blueprint $table) {
$table->uuid('id')->primary();
$table->foreignUuid('tenant_id')->constrained()->cascadeOnDelete();
$table->foreignUuid('person_id')->nullable()->constrained();
$table->foreignUuid('product_id')->constrained();
$table->string('status')->default('DRAFT');
$table->string('applicant_type')->default('INDIVIDUAL');
$table->decimal('requested_amount', 15, 2)->nullable();
$table->integer('requested_term_months')->nullable();
$table->decimal('interest_rate', 8, 4)->nullable();
$table->jsonb('metadata')->nullable();
$table->jsonb('snapshot_data')->nullable();
$table->timestamp('submitted_at')->nullable();
$table->softDeletes();
$table->timestamps();
$table->index(['tenant_id', 'status']);
$table->index(['person_id', 'created_at']);
});
| Pattern | Implementation |
|---|---|
| Primary Keys | $table->uuid('id')->primary() — NUNCA auto-increment |
| Foreign Keys | $table->foreignUuid('tenant_id')->constrained()->cascadeOnDelete() |
| Enums | $table->string('status') — NUNCA $table->enum() |
| Money | $table->decimal('amount', 15, 2) |
| Percentages | $table->decimal('rate', 8, 4) |
| JSON | $table->jsonb('field')->nullable() |
| Booleans | $table->boolean('is_active')->default(true) |
| Dates | $table->timestamp('field_at')->nullable() |
| Audit | $table->timestamps() + $table->softDeletes() |
| Category | Tables |
|---|---|
| Tenancy | tenants, tenant_brandings, tenant_api_configs |
| Auth | staff_accounts, staff_profiles, applicant_accounts, applicant_identities |
| Person | persons, addresses, person_identifications, person_employments, person_references, bank_accounts |
| Application | applications, application_status_histories, documents, products |
| KYC | data_verifications |
| Notifications | notification_templates, notification_logs, notification_preferences, otp_codes, otp_requests, sms_logs |
| Audit | audit_logs, api_logs |
Campos JSONB comunes y su uso:
// En el modelo
protected $casts = [
'metadata' => 'array', // Datos flexibles genéricos
'snapshot_data' => 'array', // Snapshot de datos al momento de crear
'settings' => 'array', // Configuración del tenant
'extra_config' => 'array', // Config adicional de integraciones
'risk_data' => 'array', // Datos de evaluación de riesgo
'counter_offer' => 'array', // Contraoferta estructurada
'verification_checklist' => 'array', // Checklist de verificación
];
Queries sobre JSONB:
// PostgreSQL JSON operators
$query->whereJsonContains('metadata->tags', 'priority');
$query->where('extra_config->host', 'smtp.gmail.com');
TenantApiConfig almacena credenciales encriptadas usando accessors/mutators individuales:
// En el modelo — cada campo sensible tiene su propio accessor/mutator
public function getApiKeyAttribute($value): ?string
{
return $value ? Crypt::decryptString($value) : null;
}
public function setApiKeyAttribute($value): void
{
$this->attributes['api_key'] = $value ? Crypt::encryptString($value) : null;
}
// Mismo patrón para: api_secret, account_sid, auth_token
Campos sensibles: api_key, api_secret, account_sid, auth_token, webhook_secret
Patrones de índice comunes:
$table->index(['tenant_id', 'status']); // Filtro por tenant + status
$table->index(['tenant_id', 'created_at']); // Listados con orden
$table->index(['tenant_id', 'event', 'channel']); // Templates por evento/canal
$table->unique(['tenant_id', 'slug']); // Unicidad por tenant
$table->index(['documentable_type', 'documentable_id']); // Polimorfismo
La mayoría de modelos usan SoftDeletes. Queries deben ser conscientes:
// Incluir eliminados
Application::withTrashed()->find($id);
// Solo eliminados
Application::onlyTrashed()->where('tenant_id', $tenantId)->get();
DatabaseSeeder
├── TenantSeeder (creates default tenant + branding)
├── StaffAccountSeeder (admin, analyst, supervisor)
├── DemoDataSeeder / V2DemoDataSeeder
├── PersonDemoSeeder
├── SecondTenantSeeder
├── NotificationTemplateSeeder
└── ProfessionalNotificationTemplates / ProfessionalEmailTemplates
Ejecutar: php artisan db:seed