feat(backend): collection tagging

[fhennig]

Summary

Add a tagging system to collections on the backend: schema, DTO changes, and API updates.

Follow-up: #1266

Database schema

Add a new collection_tags table (Flyway migration V1.3__add_collection_tags.sql):

CREATE TABLE collection_tags (
    collection_id INTEGER NOT NULL REFERENCES collections_table(id) ON DELETE CASCADE,
    tag           TEXT    NOT NULL CHECK (tag = lower(tag)),
    PRIMARY KEY (collection_id, tag)
);

CREATE INDEX idx_collection_tags_tag ON collection_tags(tag);

The CHECK (tag = lower(tag)) constraint enforces lowercase at the DB level. The Kotlin layer should also call .lowercase() before inserting, so the constraint is never hit at runtime — if it is, that's a bug in the application code.

The PK (collection_id, tag) covers loading all tags for a given collection. The separate index on tag covers the filtering query (WHERE tag = ?), which would otherwise do a full table scan since tag is the second column of the PK.

Why this schema (option A — junction table with free-text tags, no separate tags table):
A separate tags table would give tags a canonical identity and make renaming atomic, but adds a join on every query and requires an upsert before linking. For free-text labels where renaming is not a current requirement, the simpler junction table is sufficient. Tags are stored normalised (lowercase) on write to avoid case-variant duplicates. Migrating to a separate tags table later is straightforward: create the table, backfill from distinct values in collection_tags, add a FK, done.

Kotlin changes

• New CollectionTagsTable Exposed table object mirroring the schema above.
• Collection response DTO gains tags: List<String>.
• CollectionRequest gains tags: List<String> (defaults to empty list).
• CollectionUpdate gains tags: List<String>? (null = do not change).
• CollectionModel: load/save tags in all CRUD methods (replace-all on update); call .lowercase() on each tag before inserting.

API changes

GET /collections — add repeatable tags query parameter.

• Semantics: AND — only collections that have all specified tags are returned.
• Example: GET /collections?tags=flu&tags=europe → collections tagged with both flu and europe.
• No tags param → behaviour unchanged (no filtering).

GET /collections/tags — new endpoint, returns all distinct tags currently in use across all users and organisms.

• Response: { "tags": ["europe", "flu", ...] } (sorted).
• No auth required (read-only, non-sensitive).
• Useful for autocomplete on the frontend; kept intentionally simple for now.

[fhennig]

While working on this I found the most challenging bit to consider: How do we fetch all collections with all variants and tags. At the moment there is a JOIN between collections and tags, but if we were to join the tags just like that, we'd get a cartesian product. Since tags are just strings, we can use array_agg probably.

But I'm thinking whether we might in the future want to define a view?