Databases are the backbone of SaaS systems. This chapter explains schema design, migrations, tenant isolation, scaling, backups, and observability. Expanded details include soft delete patterns, indexing strategies, read/write DB separation, and backup best practices. Applied examples show how to evolve your Todo List SaaS from a single Postgres instance to a globally distributed, compliant, and resilient data platform.

1. Schema Design & Modeling

Best Practices

• Normalize core entities (Tasks, Users, Tenants, Integrations).
• Use UUIDs for global uniqueness.
• Add audit fields (created_at, updated_at, deleted_at).
• Enforce foreign keys for integrity.
• Soft Delete:
  • Instead of deleting rows, mark them with deleted_at or is_deleted.
  • Preserves history and supports recovery.
  • Add partial indexes (WHERE is_deleted = false) for performance.

Tools

• Prisma / Sequelize / TypeORM → ORM modeling.
• dbdiagram.io / Lucidchart → Schema visualization.

Todo List SaaS Setup

• MVP: tasks table with is_deleted flag.
• Growth: Add audit logs for task lifecycle.
• Enterprise: Soft delete + archival tables for compliance.

2. Indexing & Query Optimization

Best Practices

• Index frequently queried fields (tenant_id, status, created_at).