Real Production Database Design

Introduction

Production schemas evolve with the business. Plan for migrations, soft deletes, audit, multi-tenancy, indexing strategy and observability from day one.

Understanding the topic

Core concepts to understand:

• Migrations versioned in Git (Flyway, Liquibase, Prisma).
• Soft delete with deleted_at for recoverability.
• Always-on audit columns: created_at, updated_at, by_user.
• Multi-tenant ready: tenant_id everywhere.
• Observability: pg_stat_statements + APM dashboards.

Syntax reference

Visual workflow / architecture:

Repo
 └ migrations/
     V001__init.sql
     V002__add_orders.sql
     V003__index_user_id.sql
     ...
Each migration: forward + rollback, peer-reviewed.

Real-world use

Every healthy backend team has a migration framework, peer review, and observability — plus a runbook for emergencies.

Best practices

• Migrations as code.
• Forward + rollback for every change.
• Audit and observability on by default.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Migration framework you've used.
• Q2. Soft delete vs hard delete.
• Q3. Audit columns.
• Q4. Schema review process.
• Q5. Observability tools.