Migrations

Document Purpose

This document defines the planned migration workflow and rules for Tripinger’s PostgreSQL-backed database. It explains how schema changes should be designed, versioned, validated, and deployed across local, development, staging, and production environments, and how Prisma (or an equivalent migration tool) is expected to be used in practice. It builds on the high-level guidance in docs/DATABASE.md, docs/ARCHITECTURE.md, and docs/DEVOPS.md.

Tripinger is a transaction-heavy travel marketplace with bookings, payments, partner verification, reviews, and operational data. Migrations must protect transactional integrity, historical traceability, and environment safety while allowing the schema to evolve as the product grows.

Migration Overview

The approved documentation plan references Prisma-managed migration workflows for Tripinger, with PostgreSQL as the canonical transactional store and Redis, Elasticsearch, and media storage as supporting infrastructure.

At a high level:

• PostgreSQL is the system of record for core entities such as users, partners, hotels, vehicles, experiences, bookings, payments, reviews, notifications, and admin data.
• Prisma (or an equivalent schema management tool) is responsible for applying and tracking relational schema changes.
• Redis, Elasticsearch, and object storage are treated as derived or supporting stores rather than migration-primary targets.

Migrations must be treated as first-class changes linked to application code, not as ad hoc database edits.

Core Migration Principles

Tripinger’s database architecture document outlines several core principles that should govern migration design.

Version every schema change

• Every structural change to the database—new tables, columns, indexes, constraints, or enums—must be captured in a migration file.
• Manual changes in production are strongly discouraged; if unavoidable, they must be reflected in a tracked migration.

Align migrations with application changes

• Schema changes should be developed alongside the application code that depends on them.
• Pull requests that introduce new entities or fields should include the corresponding migration files and tests.
• Backward compatibility should be considered for staged deployments where new code and old schema may coexist briefly.

Prefer additive and non-destructive changes

• Destructive operations (such as dropping columns or tables) should be avoided in early phases and handled through staged approaches when necessary.
• Favor additive changes (new columns, tables, or indexes) and soft migrations (for example, marking columns deprecated before removal).

Preserve historical traceability

• Booking, payment, partner, and audit-related tables must preserve historical snapshots rather than overwrite financially or operationally relevant records.
• Migration plans must respect history tables and avoid erasing past events required for disputes, moderation, or compliance.

Isolate heavy data operations

• Large backfills, data rewrites, or clean-up operations should be isolated from fragile release paths and scheduled with care.
• Where practical, these operations should run in controlled maintenance windows or background jobs coordinated with application releases.

Migration Stack and Directory Layout

Tripinger’s database layer is centered on PostgreSQL as the system of record, with Prisma assumed as the primary schema/migration manager.

Tooling assumptions

• Primary DB: PostgreSQL (single logical cluster, domain-grouped schema).
• ORM / migration tool: Prisma (schema file + migration directories).
• Derived stores: Redis (cache), Elasticsearch (search), object storage (media) are not canonical for migrations.

Recommended repository layout

A practical layout that aligns with the monorepo and domain docs is:

/
├── apps/
│   └── api/
│       ├── prisma/
│       │   ├── schema.prisma
│       │   └── migrations/
│       │       ├── 20260718_add_users_and_partners/
│       │       ├── 20260725_create_hotels_and_rooms/
│       │       ├── 20260802_split_booking_status_history/
│       │       └── ...
│       └── src/
│           └── ...
└── docs/
    └── database/
        ├── README.md
        ├── ENUMS.md
        ├── MIGRATIONS.md
        └── ERD.md

• schema.prisma holds the canonical model.
• The migrations/ folder holds generated, versioned migration steps (SQL + metadata).
• docs/database/* describe the model, enum semantics, ERDs, and the workflow you are reading.

Domain-Based Migration Ownership

Tripinger organizes its database into domain modules such as users, admin roles, partners, hotels, vehicles, experiences, bookings, payments, reviews, AI planner, notifications, and future community, content, support, and audit modules.

Domain modules and migration scopes

Each domain module should own its migrations for schema areas it controls:

• docs/database/01-users.md: migrations related to user identity, sessions, preferences, and verification.
• docs/database/02-admin-roles.md: admin user and role schemas.
• docs/database/03-partners.md: partner records, documents, banking details, verification states.
• docs/database/04-hotels.md: hotels, rooms, amenities, policies, availability.
• docs/database/05-vehicles.md: vehicles, owners, pricing, availability.
• docs/database/06-experiences.md: activities, categories, slots, trails.
• docs/database/07-bookings.md: bookings, booking items, status history, cancellations.
• docs/database/08-payments.md: payments, refunds, payouts, reconciliation artifacts.
• docs/database/09-reviews.md: ratings, moderation, responses, review media.
• docs/database/10-ai-planner.md: planner sessions and itineraries.
• docs/database/11-notifications.md: templates, deliveries, push tokens, communication logs.
• Future modules for community, content, support, and audit will control their own schema once introduced.

Ownership rules

• Each major entity should have one authoritative owning domain for its schema.
• Booking tables should store snapshots needed for commercial integrity but not replace canonical inventory records owned by hotels, vehicles, or experiences.
• Payment tables should own transaction state, while bookings own booking lifecycle state.

Migration Naming Conventions

Migration naming should reflect business intent rather than only timestamps. The database architecture document explicitly recommends descriptive names.

Recommended naming pattern

Use short, intent-focused names such as:

• add_partner_bank_accounts
• split_booking_status_history
• create_vehicle_pricing_tables
• introduce_review_moderation_flags
• add_ai_planner_itinerary_tables

Avoid purely numeric or timestamp-based names like 20260718_143212 without context.

Naming rules

• Names should be stable and reflect the main structural change.
• For composite migrations, prefer names that capture the primary domain impact (for example, extend_payments_refunds_payouts rather than listing every column).

Migration Command Patterns

Local development

Local dev is allowed to be more “flexible,” but still disciplined.

Typical workflow:

# 1. Edit schema.prisma for a new feature
# 2. Generate a dev migration
npx prisma migrate dev --name add_ai_planner_itinerary_tables

# 3. Apply migrations and seed
npx prisma db seed

• Use migrate dev to iterate quickly.
• Reset local DB when needed (for example, docker compose down -v, then migrate dev + seed).

CI / Test environment

In CI (GitHub Actions):

# In a CI job
npx prisma migrate diff \
  --from-empty \
  --to-schema-file prisma/schema.prisma \
  --shadow-database-url $SHADOW_DATABASE_URL \
  --exit-code

# For test DB instance
npx prisma migrate deploy
npx prisma db seed
npm run test:e2e

Purpose:

• Confirm migrations apply cleanly from scratch.
• Confirm e2e flows still work against the new schema.

Staging environment

Staging is the dress rehearsal for production.

Deployment flow (conceptual):

# From GitHub Actions 'api-deploy.yaml' job targeting staging
npx prisma migrate deploy       # apply all pending migrations
npm run start:staging           # start API
npm run healthcheck:staging     # verify health and critical flows

• Run migrations before switching traffic to new containers.
• Verify booking, payment, and partner flows with synthetic or masked data.

Production environment

Production migrations should be:

• Applied through controlled deployment workflows (no manual psql patching).
• Coupled with backups or point-in-time recovery capability.

Example pattern:

# 0. Confirm backup / snapshot in place
# 1. Apply migrations
npx prisma migrate deploy

# 2. Start new app version (rolling or blue-green)
npm run start:prod

# 3. Run post-deploy smoke checks
npm run healthcheck:prod

If health checks fail:

• Roll back to previous image / configuration.
• Investigate logs and DB state; use restore if necessary.

Environment-Specific Migration Workflow

Tripinger operates across local, test, staging, and production environments, each with different data characteristics and migration expectations.

Local environment

• Purpose: developer-owned seed data and disposable state for daily work.
• Workflow:
• Use prisma migrate dev or equivalent to evolve the local schema during development.
• Reset and reseed data as needed for feature work.
• Experiment with new entities and relationships before formalizing migrations for shared environments.

Test / CI environments

• Purpose: deterministic fixtures, resettable datasets, repeatable migration verification.
• Workflow:
• Apply migrations automatically as part of CI pipelines using migrate deploy or equivalent.
• Validate that all migrations can apply cleanly on a fresh database instance.
• Run integration and end-to-end tests against the migrated schema.

Staging environment

• Purpose: production-like schema with restricted access and masked or synthetic data where possible.
• Workflow:
• Apply migrations through controlled deployment workflows (for example, GitHub Actions jobs triggered by release tags).
• Validate application behavior and data flows under the new schema.
• Use staging to test destructive or complex migrations before production, with appropriate rollback plans.

Production environment

• Purpose: live transactional data with stronger retention, backups, and audit discipline.
• Workflow:
• Apply migrations through carefully monitored deployment steps, ideally with pre-deployment backups or point-in-time recovery plans.
• Avoid schema changes that require prolonged downtime; prefer backward-compatible changes and background data migrations.
• Monitor application health, query performance, and error rates after migrations.

Environment rules

The database architecture document emphasizes that:

• Production data should not be copied casually into lower environments.
• Sensitive partner and financial records should be masked or synthetically generated for testing.
• Migration validation should be performed before production rollout.

DevOps and migration workflows must respect these rules.

Strongly Transactional Areas and Migration Care

Several Tripinger flows are strongly transactional and require extra care during schema evolution.

Critical domains

• User registration and verification state changes.
• Partner verification updates.
• Inventory reservation and booking creation.
• Booking state transitions.
• Payment creation and confirmation handling.
• Refund issuance and payout accounting.
• Review eligibility enforcement.

Migration considerations

• Avoid schema changes that break existing transactional workflows without careful migration planning.
• Prefer adding status or relationship tables rather than overloading existing ones with ambiguous flags.
• Use history tables or status transition tables when lifecycle reconstruction matters (for example, booking and payment histories).

Constraints, Integrity, and Enum Strategy

The database model is expected to enforce quality near the data layer through foreign keys, unique constraints, check constraints, and explicit enums.

Integrity patterns

• Foreign keys for authoritative relationships (for example, bookings → users, bookings → hotels/vehicles/experiences).
• Unique constraints for identity-like fields where appropriate (for example, user email, partner registration numbers).
• Check constraints for bounded enum-like or numeric conditions (for example, rating ranges, status codes).
• Explicit enums or controlled-domain modeling for finite state fields (for example, booking status, payment status).

Business integrity examples

Migrations that introduce or adjust constraints must preserve business rules such as:

• A review should not be accepted for an ineligible booking state.
• A payout should not exist without traceable payment and partner references.
• A booking item should reference a valid commercial source entity or preserved snapshot.
• Partner-managed inventory must remain attributable to a verified partner account.

Read Models and Derived Stores

Search indexes and cache layers are derived read models built from canonical data in PostgreSQL and related metadata.

Migration impact on derived stores

• Schema changes in PostgreSQL may require reindexing or refreshing search data, but these operations are not part of the core relational migration files.
• Redis and Elasticsearch should be treated as rebuildable; migrations should not rely on them as canonical data sources.

Read model principle

• Derived stores must remain reproducible from canonical source data, so migration workflows should ensure the relational schema still supports rebuilding indexes and caches after changes.

Backup and Recovery Considerations

Backup and disaster recovery procedures are documented later in Phase 3, but migration practices must be designed with recovery in mind from the start.

Recovery-related migration practices

• Coordinate migrations with automated backups or point-in-time recovery capabilities for production databases.
• Avoid applying large or complex migrations without the ability to revert or restore if something goes wrong.
• Treat PostgreSQL as the primary recovery-critical datastore; search indexes and caches should be reproducible from it.

Data Backfills and One-Off Scripts

Some migrations require data transforms or backfills (for example, populating new non-null fields).

Guidelines:

• Write backfill scripts as idempotent jobs (so they can be re-run safely).
• Run them in staging first; verify correctness.
• Schedule heavy backfills off-peak and monitor DB metrics.

Example (Node + Prisma, conceptual):

// scripts/backfill-booking-status-history.ts
import { prisma } from "../src/prisma";

async function run() {
  const bookings = await prisma.booking.findMany({
    where: { createdAt: { gte: new Date("2025-01-01") } },
  });

  for (const b of bookings) {
    await prisma.bookingStatusHistory.upsert({
      where: { bookingId_status: { bookingId: b.id, status: b.status } },
      update: {},
      create: {
        bookingId: b.id,
        status: b.status,
        changedAt: b.updatedAt ?? b.createdAt,
      },
    });
  }
}

run().catch((e) => {
  console.error(e);
  process.exit(1);
});

Run sequence:

npx prisma migrate deploy
node scripts/backfill-booking-status-history.js

Integration with DevOps and GitHub Actions

Database migrations do not operate in isolation. They are closely tied to DevOps workflows and the application codebase.

DevOps integration

• CI/CD pipelines must run migration validation in test environments before staging and production deployments.
• Staging and production deployments must include migration steps with monitoring and rollback hooks.

Example CI job (conceptual)

# .github/workflows/ci.yaml
jobs:
  api-tests:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 20
      - run: npm install
      - run: npx prisma migrate diff --from-empty --to-schema-file prisma/schema.prisma --exit-code
      - run: npx prisma migrate deploy
      - run: npm run test:e2e

Example staging / production job (conceptual)

# .github/workflows/api-deploy.yaml
jobs:
  deploy-staging:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - run: npm install
      - run: npx prisma migrate deploy # apply DB changes
      - run: npm run build
      - run: ./deploy-to-ecs-staging.sh # update containers

This ties migration application to deploy steps and health checks.

Tooling Assumptions and Open Decisions

The database architecture assumes the use of Prisma as the migration and schema management layer for PostgreSQL.

Under that assumption:

• Prisma schema files define the canonical application-level view of the database model.
• Prisma migrations (for example, prisma migrate dev, prisma migrate deploy) are the primary mechanism for evolving the schema.
• Migration history is stored in the database, allowing the tool to determine which migrations have been applied.

DATABASE-4.md lists open questions that affect long-term migration patterns:

1. Whether PostgreSQL schemas are physically separated by business domain or remain in a single schema with strong naming conventions.
2. Whether MongoDB or another store will be used for community/content workloads.
3. Final enum implementation strategy across Prisma and PostgreSQL.
4. Exact soft-delete versus hard-delete policy per domain.
5. Exact retention policy for audit, notification, and operational history data.
6. Whether itinerary and planner data remain fully relational or partially denormalized.
7. Final indexing priorities once real query patterns are measured.

This migration document should be updated when those decisions are formally confirmed.

Current Status

This migration strategy is part of the Tripinger Phase 1 foundation work. It translates high-level database architecture guidance into practical rules for evolving the PostgreSQL schema with Prisma-managed migrations across environments. Exact command names, folder paths, CI configuration, and scripts should be aligned with the actual implementation once scaffolding is committed, but the workflows, patterns, and safety rules described here are intended to remain stable as the platform grows.