Database Design: Pragmatic Schema Engineering

Schema design decisions compound over time. A resilient database is built on pragmatic normalization, consistent column standards, and robust constraints.

I. Normalization and Consistency

Start in **Third Normal Form (3NF)** to eliminate data redundancy and preserve the single source of truth. Denormalize only when performance metrics demonstrate that join costs exceed the overhead of managing duplicate state.

Standard Column Infrastructure

Every mutable table should include the following standard columns:

* **Surrogate Primary Key**: `BIGINT GENERATED ALWAYS AS IDENTITY` or `UUID v7`. Do not use natural keys (emails, usernames) as primary keys; they are subject to change and reveal sensitive data.

* **Audit Timestamps**: `created_at` and `updated_at` (both `TIMESTAMPTZ NOT NULL DEFAULT NOW()`).

* **Soft Delete**: `deleted_at TIMESTAMPTZ NULL` for user-recoverable data.

* **Tenant Isolation**: `tenant_id` on every table in multi-tenant systems, enabling Row-Level Security (RLS) from day one.

---

II. Key Selection: Surrogate vs. Natural

Always favor surrogate keys for primary and foreign keys. Natural keys should be enforced via **Unique Constraints** but never used as the target for a relationship.

| Key Type | Strength | Weakness |

|---|---|---|

| **BIGINT** | Sequential, small (8 bytes), cache-friendly. | Reveals creation order; centralized sequence. |

| **UUID v4** | Distributed, random (16 bytes). | Fragmented indexes; poor locality. |

| **UUID v7** | Timestamp-prefixed (2024 standard). | **Recommended default**: Locality of BIGINT with the distributability of UUID. |

III. Data Integrity and Constraints

The database is the final arbiter of correctness. Do not rely exclusively on application-layer validation.

* **Foreign Keys**: Mandatory for all relationships; impossible to write orphan records.

* **Check Constraints**: Enforce domain-specific invariants (e.g., `status IN ('pending', 'paid')`).

* **Not Null**: Use whenever a value is required; nullable columns are weak contracts.

* **Atomic Types**: Use `NUMERIC` for currency (never `FLOAT`), `TIMESTAMPTZ` for time, and `JSONB` for semi-structured blobs.

---

IV. Architectural Patterns

Soft Delete

Use soft delete selectively for data where "undo" is expected. For derived or ephemeral data, use hard `DELETE` to maintain index performance and storage efficiency.

Audit and History

For high-integrity domains, use **History Tables**. A database trigger writes a row snapshot to a parallel table on every change, providing a verifiable audit trail with minimal application logic overhead.

Multi-Tenancy with RLS

Implement shared-schema multi-tenancy using **Postgres Row-Level Security (RLS)**. By binding the `tenant_id` to the session context, the database enforces isolation at the storage layer, preventing cross-tenant leakage even if application queries omit a `WHERE` clause.

Indexing Strategy

* **Index Foreign Keys**: Postgres does not auto-index FK columns.

* **Composite Indexes**: Use for queries frequently filtering on multiple columns (e.g., `tenant_id` + `status`).

* **Incremental Sync**: Index `updated_at` to support high-performance polling.

V. Migration Discipline

Schema changes must be versioned, immutable, and additive-by-default.

* **Safe**: Adding nullable columns, adding indexes concurrently.

* **Unsafe**: Renaming or dropping columns (requires the Expand-Contract pattern).

* **Dangerous**: Changing types on large tables (causes hours of locking).