Skip to main content

Database Conventions & Schema Migrations

This guide outlines our PostgreSQL schema design principles, multi-tenant isolation, and migration execution workflow.

Prisma Migration Release Strategy for Uniconnect

Overview​

This document defines the database migration strategy for Uniconnect using:

The goal is to:

  • Allow developers to create granular migrations during development
  • Avoid migration pollution in QA and Production
  • Generate clean, release-level migrations
  • Simplify enterprise and on-prem upgrades
  • Maintain deterministic production deployments

Branching Model

BranchPurpose
devActive development and feature work
qaRelease candidate testing
mainProduction-ready releases

Versioning Strategy

Production Releases​

Production releases use semantic versioning:

1.1.0
1.2.0
1.3.0

QA Builds​

QA builds use build suffixes:

1.2.0-build-1
1.2.0-build-2
1.2.0-build-3

Each QA build represents a new squashed migration generated from the latest QA state.

Migration Philosophy

Development​

During development:

  • Developers create migrations freely
  • Migrations are temporary
  • Small incremental migrations are allowed

Example:

20260501_add_ticket_ai
20260502_fix_nullable
20260503_add_indexes
20260504_fix_workflow_table

These migrations exist only in the dev branch.

QA​

Before deploying to QA:

  • Temporary development migrations are squashed
  • A single QA release migration is generated

Example:

20260601_1_2_0_build_1

Production​

Before merging into main:

  • QA migrations are squashed again
  • One clean production migration is generated

Example:

20260615_1_2_0

Production should contain only stable release migrations.

Migration Naming Convention

Development Migrations​

Format:

temp_<description>

Examples:

temp_add_ticket_tags
temp_fix_indexes
temp_workflow_changes

QA Release Migrations​

Format:

<version>-build-<number>

Examples:

1.2.0-build-1
1.2.0-build-2

Production Release Migrations​

Format:

<version>

Examples:

1.2.0
1.3.0

Repository Structure

Dev Branch​

prisma/
  migrations/
    20260101_1_1_0/
    temp_add_ticket_ai/
    temp_fix_indexes/
    temp_workflow_changes/

QA Branch​

prisma/
  migrations/
    20260101_1_1_0/
    20260601_1_2_0_build_1/

Main Branch​

prisma/
  migrations/
    20260101_1_1_0/
    20260615_1_2_0/

Full Release Process

1. Development Workflow

Developers Work in dev​

Developers:

  • create feature branches
  • generate Prisma migrations normally

Example:

npx prisma migrate dev --name temp_add_ticket_ai

Multiple Dev Migrations Are Allowed​

Example:

temp_add_ticket_ai
temp_fix_constraints
temp_add_indexes
temp_modify_workflow_table

These are considered temporary migrations.

2. QA Release Process

Merge dev → qa​

When preparing a QA release:

1.2.0-build-1

GitHub Action Responsibilities​

The GitHub Action should:

Step 1 — Checkout QA branch​

Step 2 — Keep only stable baseline migrations​

Keep:

1.1.0

Remove:

temp_*

Step 3 — Generate Squashed QA Migration​

Generate:

1.2.0-build-1

using:

npx prisma migrate diff \
  --from-migrations prisma/migrations \
  --to-schema-datamodel prisma/schema.prisma \
  --script > prisma/migrations/20260601_1_2_0_build_1/migration.sql

Step 4 — Commit Generated Migration​

GitHub Action commits:

20260601_1_2_0_build_1

into the qa branch.

3. QA Iterations

If QA finds issues:

Developers continue fixing in dev.

Additional temporary migrations may be added:

temp_fix_qa_issue
temp_add_missing_index

Next QA Build​

Next merge generates:

1.2.0-build-2

The previous QA migration is replaced with the new squashed migration.

4. Production Release Process

Merge qa → main​

When QA approves release:

1.2.0

GitHub Action Responsibilities​

Step 1 — Remove QA Build Migrations​

Remove:

1.2.0-build-*

Keep:

1.1.0

Step 2 — Generate Production Migration​

Generate:

1.2.0

using:

npx prisma migrate diff \
  --from-migrations prisma/migrations \
  --to-schema-datamodel prisma/schema.prisma \
  --script > prisma/migrations/20260615_1_2_0/migration.sql

Step 3 — Commit Production Migration​

GitHub Action commits:

20260615_1_2_0

into the main branch.

Production Deployment

Production deployments should use:

npx prisma migrate deploy

Never use:

prisma migrate dev

in shared or production environments.

GitHub Actions Workflow Design

Dev → QA Workflow

Triggered on:

Pull Request merged into qa

Responsibilities:

  • Remove temporary migrations
  • Generate QA squashed migration
  • Commit migration automatically

QA → Main Workflow

Triggered on:

Pull Request merged into main

Responsibilities:

  • Remove QA build migrations
  • Generate production migration
  • Commit production migration automatically
  • Create release tag

Important Rules

Rule 1 — Never Modify Production Migrations

Once deployed to production:

  • NEVER edit
  • NEVER rename
  • NEVER delete

production migrations.

Rule 2 — Temporary Migrations Must Never Reach Production

Temporary migrations:

temp_*

must exist only in:

  • feature branches
  • dev branch

Rule 3 — QA Builds Are Disposable

QA build migrations:

1.2.0-build-*

can be replaced freely until production release is finalized.

Rule 4 — Production Branch Contains Only Stable Migrations

The main branch should contain only:

1.0.0
1.1.0
1.2.0

No temporary or QA build migrations.

Recommended Git Tags

QA​

qa/1.2.0-build-1
qa/1.2.0-build-2

Production​

v1.2.0
v1.3.0

Advantages of This Strategy

Cleaner Production Migration History​

Production contains only release-level migrations.

Easier Enterprise Support​

Customers upgrade from:

1.1.0 → 1.2.0

instead of running dozens of incremental migrations.

Faster Deployments​

Fewer migrations:

  • reduce deployment time
  • reduce migration locking
  • reduce schema drift risks

Better DBA Review Process​

DBAs review one clean migration per release.

Better On-Prem Experience​

Enterprise customers receive:

  • deterministic upgrades
  • smaller migration chains
  • cleaner rollback points

Recommended Future Enhancements

Add Release Metadata Table​

Track:

  • release version
  • applied timestamp
  • git commit
  • build number

Automated Release Notes​

Generate release notes automatically from merged PRs.

Automated Backup Before Migration​

Production deployments should automatically:

  • backup DB
  • apply migration
  • verify schema state

Summary

This strategy ensures:

  • clean production migrations
  • safe release management
  • scalable enterprise deployment process
  • simplified QA iteration handling
  • predictable upgrade paths for on-prem customers