Database Schema

IResourceBuilder<PostgresServerResource> postgres = builder.AddPostgres(Components.Postgres)
    .WithEnvironment("POSTGRES_HOST_AUTH_METHOD", "trust")
    .WithDataVolume(name: "masar-postgres-data", isReadOnly: false)
    .WithPgAdmin(pgAdmin => pgAdmin.WithHostPort(5050));

IResourceBuilder<PostgresDatabaseResource> usersDb = postgres.AddDatabase(Components.Database.User);
IResourceBuilder<PostgresDatabaseResource> tripsDb = postgres.AddDatabase(Components.Database.Trip);
IResourceBuilder<PostgresDatabaseResource> notificationsDb = postgres.AddDatabase(Components.Database.Notifications);
IResourceBuilder<PostgresDatabaseResource> authDb = postgres.AddDatabase(Components.Database.Auth);

public abstract class BaseMigration : Migration
{
    protected abstract string ServiceName { get; }

    protected void CreateTableWithStandardColumns(
        string tableName, 
        Action<ICreateTableColumnOptionOrWithColumnSyntax>? additionalColumns = null)
    {
        if (!Schema.Table(tableName).Exists())
        {
            ICreateTableColumnOptionOrWithColumnSyntax? table = Create.Table(tableName)
                .WithColumn("Id").AsString(50).PrimaryKey().NotNullable()
                .WithColumn("CreatedAt").AsDateTime().NotNullable()
                    .WithDefault(SystemMethods.CurrentDateTime)
                .WithColumn("UpdatedAt").AsDateTime().NotNullable()
                    .WithDefault(SystemMethods.CurrentDateTime)
                .WithColumn("IsDeleted").AsBoolean().NotNullable().WithDefaultValue(false)
                .WithColumn("DeletedAt").AsDateTime().Nullable();

            additionalColumns?.Invoke(table);
        }
    }

    protected void CreateIndexIfNotExists(
        string tableName, 
        string[] columnNames, 
        bool isUnique = false)
    {
        string indexName = $"IX_{tableName}_{string.Join("_", columnNames)}";
        string uniqueKeyword = isUnique ? "UNIQUE" : "";
        string columnList = string.Join(", ", columnNames.Select(c => $"\"{c}\" ASC"));
        
        Execute.Sql($@"
            CREATE {uniqueKeyword} INDEX IF NOT EXISTS ""{indexName}""
            ON ""{tableName}"" ({columnList});
        ");
    }

    protected void CreateForeignKeyIfNotExists(
        string tableName,
        string columnName,
        string referencedTable,
        string referencedColumn = "Id",
        System.Data.Rule onDelete = System.Data.Rule.Cascade)
    {
        string fkName = $"FK_{tableName}_{referencedTable}";
        string onDeleteAction = onDelete switch
        {
            System.Data.Rule.Cascade => "CASCADE",
            System.Data.Rule.SetNull => "SET NULL",
            System.Data.Rule.SetDefault => "SET DEFAULT",
            _ => "NO ACTION"
        };

        Execute.Sql($@"
            DO $$
            BEGIN
                IF NOT EXISTS (
                    SELECT 1 FROM information_schema.table_constraints 
                    WHERE constraint_name = '{fkName}' 
                    AND table_name = '{tableName}'
                ) THEN
                    ALTER TABLE ""{tableName}""
                    ADD CONSTRAINT ""{fkName}""
                    FOREIGN KEY (""{columnName}"")
                    REFERENCES ""{referencedTable}"" (""{referencedColumn}"")
                    ON DELETE {onDeleteAction};
                END IF;
            END $$;
        ");
    }
}

public class DatabaseMigrationService(
    IServiceProvider serviceProvider,
    ILogger<DatabaseMigrationService> logger,
    string connectionString,
    int maxRetries = 30,
    int retryDelaySeconds = 5)
    : BackgroundService
{
    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        logger.LogInformation("🚀 Starting database migration service...");

        try
        {
            await WaitForDatabaseAsync(stoppingToken);
            await RunMigrationsAsync();
            logger.LogInformation("✅ Database migrations completed successfully");
        }
        catch (Exception ex)
        {
            logger.LogCritical(ex, "❌ Database migration failed. Application cannot start.");
            throw;
        }
    }

    private async Task WaitForDatabaseAsync(CancellationToken cancellationToken)
    {
        for (int attempt = 1; attempt <= maxRetries; attempt++)
        {
            try
            {
                // Ensure database exists
                await EnsureDatabaseExistsAsync(cancellationToken);
                
                // Test connection
                await using var dataSource = NpgsqlDataSource.Create(connectionString);
                await using NpgsqlConnection connection = await dataSource.OpenConnectionAsync(cancellationToken);
                await using var command = new NpgsqlCommand("SELECT 1", connection);
                await command.ExecuteScalarAsync(cancellationToken);

                logger.LogInformation("✅ Database is ready (attempt {Attempt}/{MaxRetries})", 
                    attempt, maxRetries);
                return;
            }
            catch (Exception ex) when (attempt < maxRetries)
            {
                logger.LogWarning("⚠️ Database not ready (attempt {Attempt}/{MaxRetries}). Retrying in {Delay}s...", 
                    attempt, maxRetries, retryDelaySeconds);
                await Task.Delay(TimeSpan.FromSeconds(retryDelaySeconds), cancellationToken);
            }
        }
    }

    private Task RunMigrationsAsync()
    {
        using IServiceScope scope = serviceProvider.CreateScope();
        IMigrationRunner runner = scope.ServiceProvider.GetRequiredService<IMigrationRunner>();

        if (runner.HasMigrationsToApplyUp())
        {
            SortedList<long, IMigrationInfo>? pendingMigrations = runner.MigrationLoader.LoadMigrations();
            logger.LogInformation("📋 Found {Count} pending migrations", pendingMigrations.Count);
        }

        runner.MigrateUp();
        logger.LogInformation("✅ All migrations applied successfully");
        return Task.CompletedTask;
    }
}

builder.Services.AddDatabaseMigrations(
    builder.Configuration,
    Assembly.GetExecutingAssembly(),
    connectionStringName: Components.Database.User);

{
  "Logging": {
    "LogLevel": {
      "LinqToDB": "Information"
    }
  }
}

wal_level = replica
archive_mode = on
archive_command = 'cp %p /archive/%f'

log_min_duration_statement = 1000  # Log queries slower than 1 second