CultivaPulse — Kotlin Exposed ORM: Implementación Completa

Arquitectura de capas

Ktor Route Handler

↓ llamadas suspend

WorkspaceRepository

CampaignRepository

EventRepository

↓ newSuspendedTransaction { }

Exposed DSL / DAO

↓ JDBC pool

HikariCP Connection Pool

↓ migraciones Flyway al arrancar

PostgreSQL 16

Dependencias Gradle (build.gradle.kts)

exposed-core

1.0.0

DSL, tipos de columna, Table base

exposed-dao

1.0.0

Patron DAO: Entity + EntityClass

exposed-jdbc

1.0.0

Ejecucion SQL via JDBC

exposed-json

1.0.0

Soporte columnas JSONB

postgresql

42.7.5

Driver JDBC para PostgreSQL

HikariCP

6.2.1

Pool de conexiones de alto rendimiento

flyway-core

10.22.0

Migraciones de esquema versionadas

h2database

2.3.232

BD en memoria para tests

Migraciones Flyway — SQL

V1__create_workspaces.sql

SQL

-- Tabla principal de cuentas/agencias
CREATE TABLE workspaces (
    id       UUID PRIMARY KEY
                 DEFAULT gen_random_uuid(),
    slug     VARCHAR(80) NOT NULL UNIQUE,
    name     VARCHAR(120) NOT NULL,
    plan     VARCHAR(20) NOT NULL
                 DEFAULT 'starter',
    settings JSONB,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ
               NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ
               NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_ws_plan
    ON workspaces(plan);
CREATE INDEX idx_ws_active
    ON workspaces(is_active);

V2__create_campaigns.sql

SQL

CREATE TABLE campaigns (
    id           UUID PRIMARY KEY
                     DEFAULT gen_random_uuid(),
    workspace_id UUID NOT NULL
        REFERENCES workspaces(id)
        ON DELETE CASCADE,
    name         VARCHAR(200) NOT NULL,
    channel      VARCHAR(30) NOT NULL,
    status       VARCHAR(20) NOT NULL
                     DEFAULT 'draft',
    budget_cents BIGINT NOT NULL DEFAULT 0,
    starts_at    TIMESTAMPTZ,
    ends_at      TIMESTAMPTZ,
    created_at   TIMESTAMPTZ
                 NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_camp_ws
    ON campaigns(workspace_id);
CREATE INDEX idx_camp_status
    ON campaigns(status, channel);

V3__create_events.sql

SQL

CREATE TABLE events (
    id           UUID PRIMARY KEY
                     DEFAULT gen_random_uuid(),
    campaign_id  UUID NOT NULL
        REFERENCES campaigns(id)
        ON DELETE CASCADE,
    event_type   VARCHAR(40) NOT NULL,
    value_cents  BIGINT NOT NULL DEFAULT 0,
    source       VARCHAR(80),
    metadata     JSONB,
    occurred_at  TIMESTAMPTZ
                 NOT NULL DEFAULT NOW()
);
-- Indice parcial: solo conversiones
CREATE INDEX idx_ev_conv
    ON events(campaign_id, occurred_at)
    WHERE event_type = 'conversion';
CREATE INDEX idx_ev_type
    ON events(event_type);

Definicion de Tablas — Exposed DSL

tables/WorkspacesTable.kt

Kotlin

object WorkspacesTable : UUIDTable("workspaces") {
    val slug = varchar("slug", 80).uniqueIndex()
    val name = varchar("name", 120)
    val plan = enumerationByName<Plan>(
        "plan", 20
    ).default(Plan.STARTER)
    val settings = jsonb<WorkspaceSettings>(
        "settings", Json.Default
    ).nullable()
    val isActive = bool("is_active")
        .default(true)
    val createdAt = timestampWithTimeZone(
        "created_at"
    ).defaultExpression(
        CurrentTimestampWithTimeZone
    )
    val updatedAt = timestampWithTimeZone(
        "updated_at"
    ).defaultExpression(
        CurrentTimestampWithTimeZone
    )
}

@Serializable
data class WorkspaceSettings(
    val timezone: String = "UTC",
    val currency: String = "EUR",
    val maxCampaigns: Int = 10,
)

enum class Plan { STARTER, GROWTH, ENTERPRISE }

tables/CampaignsTable.kt

Kotlin

object CampaignsTable : UUIDTable("campaigns") {
    val workspaceId = uuid("workspace_id")
        .references(WorkspacesTable.id)
    val name = varchar("name", 200)
    val channel = enumerationByName<Channel>(
        "channel", 30
    )
    val status = enumerationByName<CampaignStatus>(
        "status", 20
    ).default(CampaignStatus.DRAFT)
    val budgetCents = long("budget_cents")
        .default(0L)
    val startsAt = timestampWithTimeZone(
        "starts_at").nullable()
    val endsAt = timestampWithTimeZone(
        "ends_at").nullable()
    val createdAt = timestampWithTimeZone(
        "created_at"
    ).defaultExpression(
        CurrentTimestampWithTimeZone
    )
}

enum class Channel {
    META_ADS, GOOGLE_ADS, EMAIL,
    TIKTOK_ADS, SEO, ORGANIC
}
enum class CampaignStatus {
    DRAFT, ACTIVE, PAUSED, COMPLETED
}

tables/EventsTable.kt

Kotlin

object EventsTable : UUIDTable("events") {
    val campaignId = uuid("campaign_id")
        .references(
            CampaignsTable.id,
            onDelete = ReferenceOption.CASCADE
        )
    val eventType = varchar("event_type", 40)
    val valueCents = long("value_cents")
        .default(0L)
    val source = varchar("source", 80)
        .nullable()
    val metadata = jsonb<EventMetadata>(
        "metadata", Json.Default
    ).nullable()
    val occurredAt = timestampWithTimeZone(
        "occurred_at"
    ).defaultExpression(
        CurrentTimestampWithTimeZone
    )
}

@Serializable
data class EventMetadata(
    val utm_source: String? = null,
    val utm_medium: String? = null,
    val device: String? = null,
    val country: String? = null,
)

Configuracion — HikariCP + Flyway

db/DatabaseFactory.kt

Kotlin

object DatabaseFactory {
    fun create(config: DatabaseConfig): Database {
        val hikariConfig = HikariConfig().apply {
            driverClassName = config.driver
            jdbcUrl         = config.url
            username        = config.username
            password        = config.password
            maximumPoolSize = config.maxPoolSize
            minimumIdle     = config.minIdle
            idleTimeout     = 600_000L  // 10 min
            connectionTimeout = 30_000L
            isAutoCommit    = false
            transactionIsolation =
                "TRANSACTION_READ_COMMITTED"
            // Evitar conexiones muertas en PaaS
            connectionTestQuery =
                "SELECT 1"
            validate()
        }
        return Database.connect(
            HikariDataSource(hikariConfig)
        )
    }
}

data class DatabaseConfig(
    val url: String,
    val driver: String =
        "org.postgresql.Driver",
    val username: String = "",
    val password: String = "",
    val maxPoolSize: Int = 10,
    val minIdle: Int = 2,
)

db/FlywayMigration.kt + Application.kt

Kotlin

// FlywayMigration.kt
fun runMigrations(config: DatabaseConfig) {
    Flyway.configure()
        .dataSource(
            config.url,
            config.username,
            config.password
        )
        .locations("classpath:db/migration")
        .baselineOnMigrate(true)
        .validateOnMigrate(true)
        .load()
        .migrate()
}

// Application.kt (Ktor module)
fun Application.configureDatabases() {
    val dbConfig = DatabaseConfig(
        url      = environment.config
            .property("database.url")
            .getString(),
        username = environment.config
            .property("database.username")
            .getString(),
        password = environment.config
            .property("database.password")
            .getString(),
        maxPoolSize = 10,
    )
    // 1. Migraciones PRIMERO
    runMigrations(dbConfig)
    // 2. Pool de conexiones
    val database = DatabaseFactory.create(dbConfig)
    // 3. Registrar repositorios en DI
    val campaignRepo =
        ExposedCampaignRepository(database)
    koin { modules(module {
        single<CampaignRepository> { campaignRepo }
    }) }
}

Repository Pattern — Interfaz + Implementacion Exposed

repositories/CampaignRepository.kt (interfaz)

Kotlin

interface CampaignRepository {
    suspend fun findById(id: UUID): Campaign?
    suspend fun findByWorkspace(
        workspaceId: UUID,
        page: Int,
        limit: Int,
    ): Page<Campaign>
    suspend fun findActive(
        channel: Channel? = null,
    ): List<Campaign>
    suspend fun create(
        req: CreateCampaignRequest
    ): Campaign
    suspend fun updateStatus(
        id: UUID,
        status: CampaignStatus,
    ): Boolean
    suspend fun statsForWorkspace(
        workspaceId: UUID,
    ): WorkspaceStats
    suspend fun delete(id: UUID): Boolean
}

data class WorkspaceStats(
    val totalCampaigns: Long,
    val activeCampaigns: Long,
    val totalBudgetCents: Long,
    val byChannel: Map<Channel, Long>,
)

data class CreateCampaignRequest(
    val workspaceId: UUID,
    val name: String,
    val channel: Channel,
    val budgetCents: Long,
    val startsAt: OffsetDateTime? = null,
    val endsAt: OffsetDateTime? = null,
)

repositories/ExposedCampaignRepository.kt

Kotlin

class ExposedCampaignRepository(
    private val database: Database,
) : CampaignRepository {

    override suspend fun findById(id: UUID) =
        newSuspendedTransaction(db = database) {
            CampaignsTable.selectAll()
                .where { CampaignsTable.id eq id }
                .map { it.toCampaign() }
                .singleOrNull()
        }

    override suspend fun findByWorkspace(
        workspaceId: UUID,
        page: Int,
        limit: Int,
    ): Page<Campaign> =
        newSuspendedTransaction(db = database) {
            val q = CampaignsTable.selectAll()
                .where {
                    CampaignsTable.workspaceId eq
                    workspaceId
                }
            Page(
                data = q.orderBy(
                    CampaignsTable.createdAt,
                    SortOrder.DESC
                ).limit(limit)
                 .offset(((page-1)*limit).toLong())
                 .map { it.toCampaign() },
                total = q.count(),
                page = page, limit = limit,
            )
        }

    override suspend fun findActive(
        channel: Channel?,
    ) = newSuspendedTransaction(db = database) {
        CampaignsTable.selectAll().where {
            (CampaignsTable.status eq
             CampaignStatus.ACTIVE) and
            if (channel != null)
                (CampaignsTable.channel eq channel)
            else Op.TRUE
        }.map { it.toCampaign() }
    }

    override suspend fun statsForWorkspace(
        workspaceId: UUID,
    ) = newSuspendedTransaction(db = database) {
        val rows = CampaignsTable
            .select(
                CampaignsTable.channel,
                CampaignsTable.id.count(),
                CampaignsTable.budgetCents.sum(),
                CampaignsTable.status,
            )
            .where {
                CampaignsTable.workspaceId eq
                workspaceId
            }
            .groupBy(
                CampaignsTable.channel,
                CampaignsTable.status,
            )
        WorkspaceStats(
            totalCampaigns =
                rows.sumOf {
                    it[CampaignsTable.id.count()]
                },
            activeCampaigns = rows.filter {
                it[CampaignsTable.status] ==
                CampaignStatus.ACTIVE
            }.sumOf {
                it[CampaignsTable.id.count()]
            },
            totalBudgetCents = rows.sumOf {
                it[CampaignsTable.budgetCents.sum()]
                ?: 0L
            },
            byChannel = rows.groupBy(
                { it[CampaignsTable.channel] },
                { it[CampaignsTable.id.count()] },
            ).mapValues { it.value.sum() },
        )
    }

    private fun ResultRow.toCampaign() =
        Campaign(
            id = this[CampaignsTable.id].value,
            name = this[CampaignsTable.name],
            channel = this[CampaignsTable.channel],
            status = this[CampaignsTable.status],
            budgetCents =
                this[CampaignsTable.budgetCents],
        )
}

Tests — H2 en memoria

test/CampaignRepositoryTest.kt

Kotlin · Kotest

class CampaignRepositoryTest : FunSpec({
    lateinit var database: Database
    lateinit var repo: CampaignRepository
    val wsId = UUID.randomUUID()

    beforeSpec {
        database = Database.connect(
            url    = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;MODE=PostgreSQL",
            driver = "org.h2.Driver",
        )
        transaction(database) {
            SchemaUtils.create(
                WorkspacesTable,
                CampaignsTable,
                EventsTable,
            )
            // Seed: workspace de prueba
            WorkspacesTable.insert {
                it[id] = EntityID(wsId, WorkspacesTable)
                it[slug] = "agencia-test"
                it[name] = "Agencia Test"
                it[plan] = Plan.GROWTH
            }
        }
        repo = ExposedCampaignRepository(database)
    }

    beforeTest {
        transaction(database) {
            CampaignsTable.deleteAll()
        }
    }
    test("create devuelve campaña con id") {
        val c = repo.create(CreateCampaignRequest(
            workspaceId = wsId,
            name = "Black Friday META",
            channel = Channel.META_ADS,
            budgetCents = 500_00L,
        ))
        c.name shouldBe "Black Friday META"
        c.status shouldBe CampaignStatus.DRAFT
    }

    test("paginacion correcta con 15 campanas") {
        Channel.entries.take(5).forEach { ch ->
            repeat(3) { i ->
                repo.create(CreateCampaignRequest(
                    workspaceId = wsId,
                    name = "Camp ${ch} $i",
                    channel = ch,
                    budgetCents = 100_00L,
                ))
            }
        }
        val p1 = repo.findByWorkspace(wsId, 1, 10)
        p1.data shouldHaveSize 10
        p1.total shouldBe 15
        p1.hasNext shouldBe true

        val p2 = repo.findByWorkspace(wsId, 2, 10)
        p2.data shouldHaveSize 5
        p2.hasNext shouldBe false
    }

    test("statsForWorkspace agrega correctamente") {
        listOf(
            Channel.META_ADS to 200_00L,
            Channel.GOOGLE_ADS to 350_00L,
            Channel.EMAIL to 50_00L,
        ).forEach { (ch, budget) ->
            repo.create(CreateCampaignRequest(
                workspaceId = wsId, name = "$ch Q3",
                channel = ch, budgetCents = budget,
            ))
        }
        val stats = repo.statsForWorkspace(wsId)
        stats.totalCampaigns shouldBe 3
        stats.totalBudgetCents shouldBe 600_00L
        stats.byChannel[Channel.META_ADS] shouldBe 1
    }

Referencia rapida — Patrones Exposed

Patron / Snippet	Descripcion	Cuando usarlo
object T : UUIDTable("t")	Tabla DSL con PK UUID autogenerada	Todas las tablas del dominio
newSuspendedTransaction { }	Transaccion atomica coroutine-safe	Cualquier operacion de BD en Ktor/suspend
Table.selectAll().where { }	SELECT con condicion type-safe	Consultas DSL simples y joins
Table.insertAndGetId { }	INSERT devolviendo el ID generado	Crear entidades y necesitar el UUID
Table.batchInsert(list) { }	INSERT masivo en una sola sentencia	Seeds, importaciones, eventos en bulk
innerJoin / leftJoin	Joins entre tablas Exposed	Consultas con relaciones (campaigns + events)
col.sum() / col.count() / .avg()	Funciones de agregacion	Stats de workspace, totales de budget
.limit(n).offset(k.toLong())	Paginacion offset-based	Listados API con page/limit
jsonb<T>("col", Json.Default)	Columna JSONB con serializacion kotlinx	Settings, metadata, flags dinamicos
UUIDEntity + UUIDEntityClass	Patron DAO: entidad con ciclo de vida	Cuando necesitas relaciones lazy (orders.items)
H2 jdbc:mem MODE=PostgreSQL	BD en memoria compatible con PG para tests	Tests unitarios de repositorios sin Docker
Flyway V1__xxx.sql	Migracion versionada aplicada al arrancar	Todos los cambios de esquema en produccion

CULTIVA IA · Skill: patrones-kotlin-exposed-orm · Ejemplo: CultivaPulse SaaS backend · v1.0.0