Database & Migrations — Magic Varsh Docs

The platform uses a single PostgreSQL instance (database portal), but each microservice owns its own schema rather than using public. Schema changes are managed with Flyway migrations that run automatically on service start.

Schemas

Schema	Service	Key tables
auth	auth-service	portal_user, portal_role, portal_user_role
page	page-service	portal_page, portal_site
asset	asset-service	portal_asset
webcontent	web-content-service	portal_web_content
portlet	portlet-service	portal_portlet
soap	soap-tracker-service	portal_soap_log
emailtemplate	email-template-service	email_template
email	email-service	email_template, email_variable_mapping, email_send_log
settings	settings-service	site_configuration, ssl_configuration, cluster_node, email_configuration, sms_configuration, auth_provider

Conventions: primary keys are BIGSERIAL; created_at/updated_at are TIMESTAMPTZ DEFAULT NOW(); multilingual columns are stored as JSON; tables are schema-qualified (e.g. auth.portal_user).

Connection

Inside Docker the host is postgres:5432; from the host it is localhost:54322. Default dev credentials: database portal, user portal_user. The seeded admin is admin / admin@portal.com with ROLE_ADMIN + ROLE_USER. Browse with pgAdmin at localhost:5050.

Migrations

Migration scripts live under each module at src/main/resources/db/migration/V*.sql and are applied in version order.

<module>/src/main/resources/db/migration/V1__init.sql
<module>/src/main/resources/db/migration/V2__add_table.sql

Never edit a migration that has already been applied. Add a new V<next>__*.sql file instead — changing applied migrations breaks Flyway's checksums.

Browsing the database

In Docker, pgAdmin is available on port 5050 for inspecting schemas and data during development.

Column-level encryption

magicvarsh-infra-service can encrypt individual columns at rest without touching the owning service's code. When the first column of a table is encrypted, the physical table s.t is renamed to s.t__enc and a view named s.t takes its place; the view decrypts encrypted columns on read (via pgcrypto), and INSTEAD OF INSERT/UPDATE/DELETE triggers encrypt values on write. Every other service keeps reading and writing plaintext through the view. Ciphertext is base64 with an enc:v1: prefix, stored in the same text column.

The master key comes from the ENCRYPTION_MASTER_KEY environment variable. Only tables with a primary key are eligible, only text-like columns can be encrypted, and primary/foreign-key and generated columns are excluded. Once the master key is set it must never change.

Recent migrations of note

page-service V22__themes.sql — the portal_theme table backing the theme system.
page-service V23__site_auto_publish.sql — lets a site opt in to auto-publishing content and portlets on save instead of requiring an explicit publish step.
page-service V24__theme_content_template.sql — adds content_template and footer_js columns to portal_theme(_aud) for the content canvas / footer JS.
asset/dynamic-form/portlet/soap-tracker-service V7/V8/V14/V15/V25 __resync_revinfo_identity.sql — realigns each schema's Envers revinfo identity sequence with MAX(rev), so a sequence left behind by a cross-environment import/restore can't collide on revinfo_pkey.