Data Model
Megadex owns its own PostgreSQL database (megadex by default). Roles live in your BFF (Backend for Frontend) — user_role stores plain roleId integers with no foreign key, because the role table is in the BFF's database.
Users, Roles, Socials & Preferences
user and the users writable view
The "user" base table holds core profile attributes, consent timestamps, and lifecycle flags (active, archived, archivedAt). nickname and email are case-insensitively unique (lower(nickname), lower(email)).
Applications never query "user" directly. Instead, Megadex reads and writes the users view, which joins "user" with:
"rolesArrayAgg"— array ofuser_role."roleId"ordered by"roleId", empty array when none (mapped by the Express layer toroles)"socialsJsonAgg"— JSONB object ofsocial.nametouser_social.handlefor non-archived social platforms, empty object when none
An INSTEAD OF INSERT OR UPDATE trigger (users_iud_trigger, function iud_user()) makes the users view directly writable: inserting or updating a row on users writes the "user" table, reconciles user_role rows, and calls set_user_socials() to resolve platform names against social and upsert or delete user_social rows in a single transaction.
social
Catalog of supported social platforms (Facebook, Instagram, X, LinkedIn, YouTube, TikTok, GitHub, Pinterest, Discord, Reddit, Twitch seeded by default). Adding a platform requires only inserting a row into social; archiving a platform prevents it from being assigned in socialsJsonAgg.
preference and the preferences writable view
Stores table column layouts (conf) for the Admin UI (users and socials resources).
- Rows where
userId IS NULLare locked system templates (locked = true). - Rows where
userId IS NOT NULLbelong to that operator (locked = false), capped at 10 per user and per resource by triggertrg_preference_limit(functioncheck_preference_limit). preference_selectiontracks whichpreferenceIda user currently has active for eachresourceName(falling back to the"Default"template when no row exists).
The writable preferences view exposes locked and isActive as computed columns and uses an INSTEAD OF trigger (preferences_iud_trigger, function iud_preference) to implement copy-on-write when an operator customizes a locked template.
Reference Lookups & Audit Views
country_trans, civility_trans, and gender_trans hold localized labels in several languages (see Reference Lookups), keyed by the country and language domains and the civility and gender enum types.
Conventions
Every mutable table follows the same four patterns:
Soft deletion. User and social catalog rows are never hard-deleted through the HTTP API. Calling /archive calls public.set_archived(), which flags archived = true and stamps archivedAt. The nightly job hard-deletes archived rows after two months.
Audit columns. Mutable tables carry createdAt, creatorId, creatorName, updatedAt, updaterId, and updaterName, stamped from the x-consumer-user-id and x-consumer-name headers forwarded by the BFF (or -1 / system for seed data).
History triggers. Database triggers record every INSERT, UPDATE, and DELETE on "user", social and preference into log.history; role and social changes on users are logged by iud_user() when their values change. Per-row history is exposed via GET /:id/history, while log.audit pairs each entry with its previous snapshot to power POST /megadex/audit/search. History older than six months is purged nightly.
Least-privilege database roles. The migration entrypoint provisions two PostgreSQL roles: DB_USER (used by the HTTP server, with no DELETE privilege on "user", social, or log.history) and DB_JOB_USER (used only by the nightly cleanup cron jobs).
Migrations
The schema is managed by Liquibase in db/liquibase/megadex/, applied by the ghcr.io/alten-group/megadex-migration container. Changesets are grouped by purpose — 01-hist, 02-func, 03-struct, 04-view, 05-trig, and 06-data — and the migration also creates the database and both PostgreSQL roles on a fresh install. See Deployment.