Mirrors/coolify
1
0
Fork 0
mirror of https://github.com/coollabsio/coolify.git synced 2025-12-28 05:34:50 +00:00
Code Issues Actions 11 Packages Projects Releases Wiki Activity
coolify-json-detection
coolify/.ai/patterns/database-patterns.md

13 KiB
Raw Permalink Blame History

Coolify Database Architecture & Patterns

Database Strategy

Coolify uses PostgreSQL 15 as the primary database with Redis 7 for caching and real-time features. The architecture supports managing multiple external databases across different servers.

Primary Database (PostgreSQL)

Core Tables & Models

User & Team Management

Infrastructure Management

Project Organization

Application Deployment

Service Management

Database Type Support

Standalone Database Models

Each database type has its own dedicated model with specific configurations:

SQL Databases

NoSQL & Analytics

Caching & In-Memory

Configuration Management

Environment Variables

Settings Hierarchy

Storage & Backup Systems

Storage Management

Backup Infrastructure

Task Scheduling

Notification & Integration Models

Notification Channels

Source Control Integration

OAuth & Authentication

Docker & Container Management

Container Orchestration

SSL & Security

Database Migration Strategy

Migration Location: database/migrations/

Migration Patterns

// Typical Coolify migration structure
Schema::create('applications', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('fqdn')->nullable();
    $table->json('environment_variables')->nullable();
    $table->foreignId('destination_id');
    $table->foreignId('source_id');
    $table->timestamps();
});

Schema Versioning

  • Incremental migrations for database evolution
  • Data migrations for complex transformations
  • Rollback support for deployment safety

Eloquent Model Patterns

Base Model Structure

  • BaseModel.php - Common model functionality
  • UUID primary keys for distributed systems
  • Soft deletes for audit trails
  • Activity logging with Spatie package

CRITICAL: Mass Assignment Protection

When adding new database columns, you MUST update the model's $fillable array. Without this, Laravel will silently ignore mass assignment operations like Model::create() or $model->update().

Checklist for new columns:

  1. ✅ Create migration file
  2. ✅ Run migration
  3. ✅ Add column to model's $fillable array
  4. ✅ Update any Livewire components that sync this property
  5. ✅ Test that the column can be read and written

Example:

class Server extends BaseModel
{
    protected $fillable = [
        'name',
        'ip',
        'port',
        'is_validating', // ← MUST add new columns here
    ];
}

Relationship Patterns

// Typical relationship structure in Application model
class Application extends Model
{
    public function server()
    {
        return $this->belongsTo(Server::class);
    }
    
    public function environment()
    {
        return $this->belongsTo(Environment::class);
    }
    
    public function deployments()
    {
        return $this->hasMany(ApplicationDeploymentQueue::class);
    }
    
    public function environmentVariables()
    {
        return $this->hasMany(EnvironmentVariable::class);
    }
}

Model Traits

// Common traits used across models
use SoftDeletes;
use LogsActivity;
use HasFactory;
use HasUuids;

Caching Strategy (Redis)

Cache Usage Patterns

  • Session storage - User authentication sessions
  • Queue backend - Background job processing
  • Model caching - Expensive query results
  • Real-time data - WebSocket state management

Cache Keys Structure

coolify:session:{session_id}
coolify:server:{server_id}:status
coolify:deployment:{deployment_id}:logs
coolify:user:{user_id}:teams

Query Optimization Patterns

Eager Loading

// Optimized queries with relationships
$applications = Application::with([
    'server',
    'environment.project',
    'environmentVariables',
    'deployments' => function ($query) {
        $query->latest()->limit(5);
    }
])->get();

Chunking for Large Datasets

// Processing large datasets efficiently
Server::chunk(100, function ($servers) {
    foreach ($servers as $server) {
        // Process server monitoring
    }
});

Database Indexes

  • Primary keys on all tables
  • Foreign key indexes for relationships
  • Composite indexes for common queries
  • Unique constraints for business rules

Request-Level Caching with ownedByCurrentTeamCached()

Many models have both ownedByCurrentTeam() (returns query builder) and ownedByCurrentTeamCached() (returns cached collection). Always prefer the cached version to avoid duplicate database queries within the same request.

Models with cached methods available:

  • Server, PrivateKey, Project
  • Application
  • StandalonePostgresql, StandaloneMysql, StandaloneRedis, StandaloneMariadb, StandaloneMongodb, StandaloneKeydb, StandaloneDragonfly, StandaloneClickhouse
  • Service, ServiceApplication, ServiceDatabase

Usage patterns:

// ✅ CORRECT - Uses request-level cache (via Laravel's once() helper)
$servers = Server::ownedByCurrentTeamCached();

// ❌ AVOID - Makes a new database query each time
$servers = Server::ownedByCurrentTeam()->get();

// ✅ CORRECT - Filter cached collection in memory
$activeServers = Server::ownedByCurrentTeamCached()->where('is_active', true);
$server = Server::ownedByCurrentTeamCached()->firstWhere('id', $serverId);
$serverIds = Server::ownedByCurrentTeamCached()->pluck('id');

// ❌ AVOID - Making filtered database queries when data is already cached
$activeServers = Server::ownedByCurrentTeam()->where('is_active', true)->get();

When to use which:

  • ownedByCurrentTeamCached() - Default choice for reading team data
  • ownedByCurrentTeam() - Only when you need to chain query builder methods that can't be done on collections (like with() for eager loading), or when you explicitly need a fresh database query

Implementation pattern for new models:

/**
 * Get query builder for resources owned by current team.
 * If you need all resources without further query chaining, use ownedByCurrentTeamCached() instead.
 */
public static function ownedByCurrentTeam()
{
    return self::whereTeamId(currentTeam()->id);
}

/**
 * Get all resources owned by current team (cached for request duration).
 */
public static function ownedByCurrentTeamCached()
{
    return once(function () {
        return self::ownedByCurrentTeam()->get();
    });
}

Data Consistency Patterns

Database Transactions

// Atomic operations for deployment
DB::transaction(function () {
    $application = Application::create($data);
    $application->environmentVariables()->createMany($envVars);
    $application->deployments()->create(['status' => 'queued']);
});

Model Events

// Automatic cleanup on model deletion
class Application extends Model
{
    protected static function booted()
    {
        static::deleting(function ($application) {
            $application->environmentVariables()->delete();
            $application->deployments()->delete();
        });
    }
}

Backup & Recovery

Database Backup Strategy

  • Automated PostgreSQL backups via scheduled tasks
  • Point-in-time recovery capability
  • Cross-region backup replication
  • Backup verification and testing

Data Export/Import

  • Application configurations export/import
  • Environment variable bulk operations
  • Server configurations backup and restore

Performance Monitoring

Query Performance

  • Laravel Telescope for development debugging
  • Slow query logging in production
  • Database connection pooling
  • Read replica support for scaling

Metrics Collection

  • Database size monitoring
  • Connection count tracking
  • Query execution time analysis
  • Cache hit rates monitoring

Multi-Tenancy Pattern

Team-Based Isolation

// Global scope for team-based filtering
class Application extends Model
{
    protected static function booted()
    {
        static::addGlobalScope('team', function (Builder $builder) {
            if (auth()->user()) {
                $builder->whereHas('environment.project', function ($query) {
                    $query->where('team_id', auth()->user()->currentTeam->id);
                });
            }
        });
    }
}

Data Separation

  • Team-scoped queries by default
  • Cross-team access controls
  • Admin access patterns
  • Data isolation guarantees