Bluetape4k Exposed Part 4: JSON, Encryption, and Dialects

Small robotic builders handling data modules, instrumentation panels, and analytics devices on a lab-style workbench — A column looks small until serialization, encryption, and dialect behavior turn it into a tiny framework.

2026-05-31 · bluetape4k-exposed series · Part 4

Part 4 moves below the repository layer. Real services keep running into small problems near the table definition: should JSON be carried as a string, should an encrypted value remain searchable, and should database-specific types such as coordinates or vectors be handled directly in service code?

I added JSON columns, encrypted columns, measured columns, and database-specific spatial/vector functions so those details do not leak across service code. These pieces should stay close to the table definition, where the intent is visible. Writing them by hand every time is where the risk starts.

Columns and dialects map from domain model through JSON codecs, encrypted columns, measured columns, PostgreSQL, MySQL8, and analytics dialects to SQL DSL — Repeated serialization, encryption, and dialect details belong in column types and extension functions.

JSON Columns: Types, Not Strings

JSON column support is not an argument for using JSON everywhere. It exists for the cases where JSON/JSONB is already the right storage shape and the service should not pass raw strings around. exposed-jackson2, exposed-jackson3, and exposed-fastjson2 let JSON/JSONB columns be read and written as Kotlin types.

data class UserSettings(
    val theme: String = "light",
    val notifications: Boolean = true,
    val language: String = "ko",
)

object Users : IdTable<Long>("users") {
    val name = varchar("name", 100)
    val settings = jackson<UserSettings>("settings")
}

Keeping JSON as String looks convenient at first. After that, validation, migration, path queries, and serialization settings start spreading across services. A column type keeps the intent visible in the table definition.

val theme = Users.settings.jsonPath<String>("$.theme")

val query = Users
    .selectAll()
    .where { Users.settings.jsonContains("theme", "dark") }

These helpers do not solve JSON schema evolution. That remains a design problem. They do, however, keep one important boundary together: where serialization happens, where path queries are built, and where a row is restored as a typed value.

Tink Encrypted Columns

Sensitive data is easy to postpone with “we can add encryption later.” Usually that later moment arrives after production data already exists. Then it is not a migration anymore; it is a data move, and it is not pleasant.

That is why exposed-tink includes Google Tink based AEAD and Deterministic AEAD columns.

object Users : IntIdTable("users") {
    val name = varchar("name", 100)

    // No lookup needed: different ciphertext each time
    val memo = tinkAeadVarChar("memo", 512).nullable()

    // Lookup needed: deterministic ciphertext, indexable
    val email = tinkDaeadVarChar("email", 512).index()
}

The first decision is whether the value needs AEAD or DAEAD.

Mode	Strength	Limit
AEAD	Same plaintext produces different ciphertext, reducing pattern exposure	Cannot use `WHERE col = value` lookup
DAEAD	Same plaintext produces same ciphertext, so equality lookup and indexes are possible	Deterministic output still leaves pattern-analysis risk

For memo, note, or token payload fields that do not need lookup, AEAD is the right default. For values such as email or a national identifier that need equality lookup, DAEAD may be necessary. Security needs more than an “encrypted” checkbox; it also includes the queries the application must support.

The difference is clearer in code. A DAEAD column uses deterministic encryption, so the same plaintext maps to the same ciphertext and equality lookup can use an index.

transaction {
    Users.insert {
        it[name] = "홍길동"
        it[memo] = "VIP customer"      // AEAD: sensitive data without lookup
        it[email] = "hong@example.com" // DAEAD: searchable identifier
    }

    val byEmail = Users
        .selectAll()
        .where { Users.email eq "hong@example.com" }
        .singleOrNull()
}

By contrast, an AEAD column produces different ciphertext even for the same plaintext. The query below is not a reliable search condition.

transaction {
    // memo is a non-deterministic encrypted column declared with tinkAeadVarChar.
    // It is encrypted again with a fresh nonce, so it must not be used for equality lookup.
    val notReliable = Users
        .selectAll()
        .where { Users.memo eq "VIP customer" }
        .toList()
}

Measured Columns

Measured columns exist for the same reason. Values such as money, length, weight, and duration do not carry enough meaning as bare numbers. If reviewers must repeatedly guess whether 10 means 10 won, 10 dollars, 10 meters, or 10 centimeters, the code is already doing too little.

A measured column moves the model toward value plus unit. The database column may still be represented as numbers and strings, but the domain code can read it as a typed value.

PostgreSQL: PostGIS, pgvector, and Range

PostgreSQL has many useful features, and those features come with details that easily leak into service code. If every PostGIS, pgvector, or range query starts hand-writing SQL literals and JDBC type handling, the glue code becomes more visible than the query intent. exposed-postgresql wraps those PostgreSQL-specific features in small pieces.

Feature	Example
PostGIS	`geoPoint`, `geoPolygon`, `stDistance`, `stWithin`, `stContains`
pgvector	`vector("embedding", 384)`, cosine-distance lookup
Range	Time-range columns and query helpers

object DocumentTable : Table("documents") {
    val id = integer("id").primaryKey()
    val title = varchar("title", 200)
    val embedding = vector("embedding", 384)
}

PostGIS can stay readable beside the table definition as well.

object LocationTable : Table("locations") {
    val id = integer("id").primaryKey()
    val name = varchar("name", 100)
    val point = geoPoint("point")
    val area = geoPolygon("area")
}

transaction {
    val nearby = LocationTable
        .select(LocationTable.name)
        .where { LocationTable.point.stDWithin(searchPoint, 0.5) }
        .toList()

    val inside = LocationTable
        .select(LocationTable.name)
        .where { LocationTable.point.stWithin(polygonArea) }
        .toList()
}

PostgreSQL features are not the problem. The problem appears when SQL literals and JDBC type registration leak into service code. I keep dialect modules so those details stay below the query intent.

MySQL 8 Spatial

MySQL 8 also needs a single place for spatial rules. exposed-mysql8 handles MySQL 8.0+ spatial columns and predicates with JTS geometry types. The default coordinate system is WGS84, SRID 4326.

object Locations : LongIdTable("locations") {
    val name = varchar("name", 255)
    val point = geoPoint("point")
    val area = geoPolygon("area")
}

Locations
    .selectAll()
    .where { Locations.area.stContains(Locations.point) }
    .toList()

Small rules such as coordinate order are good bug factories. That is why helpers fix the longitude and latitude order. Map bugs are irritating because the numbers often look reasonable.

Dialects for Analytics Databases

Analytics databases such as BigQuery, ClickHouse, Trino, and DuckDB behave differently from OLTP databases. If connector, type, literal, pagination, and function differences enter service code, application logic gets mixed with analytics SQL glue.

The direction I chose in bluetape4k-exposed is to keep those differences close to where they are used, while moving the repeated code into extensions.

Extension	What to inspect
`exposed-bigquery`	BigQuery REST API execution and pageToken-based Flow reads
`exposed-clickhouse`	ClickHouse connector and type behavior
`exposed-trino`	Federation-query boundaries
`exposed-duckdb`	Embedded analytics and local tests
`exposed-timefold-solver-persistence`	Solver persistence integration

BigQuery is not an extension that provides JDBC transaction semantics. It reuses Exposed DSL as a SQL generator, while execution is handled by the BigQuery REST API.

val context = BigQueryContext.create(
    bigquery = bigqueryClient,
    projectId = "my-project",
    datasetId = "my-dataset",
)

with(context) {
    val rows = Events
        .selectAll()
        .where { Events.region eq "kr" }
        .withBigQuery()
        .toList()

    Events.selectAll()
        .withBigQuery()
        .toFlow()
        .collect { row ->
            println(row[Events.region])
        }
}

Closing

Part 4 is not saying “avoid advanced database features.” It says the opposite: if you use them, do not scatter their details across service code. Keep them near table definitions and query helpers.

JSON should read as a domain type, not a raw string.
Encryption should separate AEAD and DAEAD by lookup requirements.
Measured values should not be scattered as unitless numbers.
PostgreSQL, MySQL, and analytics database features should be isolated in dialect modules.

Part 5 connects these pieces with Spring Boot, cache, multi-tenancy, and production examples. That is also why the examples exist: type safety is not enough by itself. Tenant leakage tests and performance charts need to sit next to the code.