summercms/.planning/notes/lagoon-per-query-collation.md

Decision: Per-query collation in lagoon.OrderBy

Date: 2026-10-01
Status: Accepted
Supersedes: Phase 3 D-06 (database-level ICU pl-PL locale, implemented by d0d8450 and 56be82f)

Decision

lagoon.OrderBy takes variadic lagoon.OrderOption values. lagoon.Collate(name) makes it emit ORDER BY <column> COLLATE "<name>" ASC|DESC. The collation name is validated (1-63 ASCII bytes; letters, digits and _ first, then letters, digits, _, -, ., @) and emitted as a double-quoted identifier. An invalid name is an error returned before any SQL is built. Without the option, OrderBy emits exactly the clause it emitted before.

lagoon no longer checks the database locale. lagoon.Open and lagoon.Use only ping. The exported CheckLocale is removed: it was pre-v1 API, and nothing called it directly (the application's tests only asserted its error through Open/Use). No config key replaces it.

Rationale

• The framework must know nothing about the application. The database-level requirement made every SummerCMS application, framework test container and docs example provision a Polish ICU database because one application's PHP original sorted four name columns with COLLATE utf8mb4_polish_ci.
• pl-x-icu ships with any ICU-enabled PostgreSQL, including postgres:16-alpine, so a per-query collation needs no database setup.
• The collation now sits where PHP applied COLLATE utf8mb4_polish_ci: the PolishOrder::apply call site.

Consequences

• Framework tests, READMEs and docs use plain PostgreSQL databases. docs/database/queries-and-pagination.md gains a "Sorting with a collation" section backed by ExampleCollate. scripts/check-phase8.sh keeps its ICU pl-PL initdb args because that gate boots the application, and its comment was corrected. Framework commit: 037dc53 (summercms.go).
• The application passes lagoon.Collate("pl-x-icu") at the genre list call site. A libc C database test shows the Polish order comes from the query and not from the database. The application still recommends an ICU pl-PL default for queries without an explicit collation. Application commit: 2da9482 (fonoteka.go).

Follow-ups

• Add a collated expression index on the PolishOrder columns once those tables grow. It helps only when it is built with the same collation (pl-x-icu).
• The other three PolishOrder columns (golem15_fonoteka_albums.artist_display, golem15_fonoteka_albums.name, golem15_fonoteka_artists.name) adopt lagoon.Collate when their list endpoints are ported.
• Drop ICU pl-PL from the application's test containers in a cleanup pass. They are harmless but no longer needed.
• The application's admin relation option query (albums_admin_controller.go, ORDER BY name ASC, id ASC) orders by name without a collation, so it follows the database default. Decide whether it should pass the Polish collation.
• ICU Polish and MariaDB utf8mb4_polish_ci can still differ on edge cases. That is why the recorded fixture-order parity test stays.
• Execution deviations are recorded in .planning/quick/261001-ddh-replace-lagoon-hardcoded-icu-pl-pl-datab/261001-ddh-SUMMARY.md.