ADR-0003: Use PostgreSQL 16 And Meilisearch For The YourBOX Data Layer
Status
Section titled “Status”Accepted
Context
Section titled “Context”YourBOX currently relies on a MySQL Flexible Server provisioned in Azure. The schema lives
as a single dump file checked into the repository and is reapplied on every deployment by
piping it into mysql -h ... < yourbox.sql. Tables have no foreign keys, votes have no
unique index on (ref, ref_id, user_id), file metadata stores absolute filesystem paths
and tags are serialized as a comma separated string. The search path executes LIKE %x%
queries on the video table and caches the rendered HTML to disk in cache/searchings/ for
one hour.
The modernization goals require a database better suited to managing user connections and
relational integrity, support for a master replica topology, and a real search experience
instead of the LIKE plus HTML cache pattern. The Azure deployment target stays a first
class environment.
PostgreSQL 16 covers the relational layer cleanly. It supports row level security, typed JSONB columns for flexible metadata, generated columns, logical replication for master replica topologies and excellent Symfony Doctrine integration. The Azure Database for PostgreSQL Flexible Server matches the existing Azure deployment target. The CloudNativePG operator runs the same engine in the homelab Kubernetes cluster.
PostgreSQL full text search ranks poorly on typos and partial words for a media catalog.
Meilisearch is dedicated to search, supports typo tolerance, faceting and an instant search
API. Running it next to the application gives a much better experience than LIKE %x% and
removes the on-disk HTML cache.
Decision
Section titled “Decision”Use PostgreSQL 16 as the primary relational database for users, files, comments and votes. Use Meilisearch as the search engine for the video catalog. PostgreSQL stays the source of truth; Meilisearch holds a denormalized projection refreshed by a Symfony Messenger consumer on every insert, update or delete of a video.
The schema rebuilds from scratch with Doctrine migrations rather than carrying the
yourbox.sql dump forward. The new schema introduces foreign keys, partial indexes, a
unique index on (ref, ref_id, user_id) for the vote table, JSONB columns for tags and
additional metadata, and a check constraint on the vote value. Filesystem paths leave the
schema in favor of object storage keys, in line with ADR-0005.
A one shot importer reads the legacy MySQL dump and writes the new schema. The importer is idempotent and runs once per environment during the migration window.
Replication is delegated to the database provider. In Azure that means a read replica attached to the flexible server. In the homelab that means CloudNativePG with a primary plus a hot standby and continuous WAL archiving to MinIO. The application reads from the primary by default and is ready to route read only queries to the replica once the read endpoint is declared in the Symfony configuration.
Consequences
Section titled “Consequences”- The application uses one Doctrine connection. Tests use a disposable PostgreSQL container seeded from the Doctrine schema, not from the legacy dump.
- The Meilisearch projection is eventually consistent. The Symfony Messenger consumer reconciles within seconds; the YourBOX search documentation explains the lag bound.
- The MySQL Terraform module is removed. The Azure provisioning surface shrinks accordingly, in line with ADR-0010.
- Operating two stateful services (PostgreSQL and Meilisearch) raises the platform footprint in the homelab. Both components are owned by the CloudNativePG operator and the Meilisearch Helm chart respectively, so platform documentation covers backups, restores and upgrade paths.
- Existing comments, votes and users import with the same identifiers when possible to preserve forensic traceability with the legacy data.