The Postgres features I actually lean on

The domain is inherently relational — organisations have branches, branches have programs, programs have applications, applications have documents, payments and status history. Postgres gives me strong relational integrity for that, plus native JSON columns for the genuinely flexible parts — settings, form field configs — without running a document database alongside. One database, not two, for something one person maintains.

Tables under a custom core schema, not the default public. Namespacing matters once other tools enter the picture — poolers, growing logical areas, eventually multiple apps on one instance. It's directly tied to pgBouncer's worst gotcha: search_path is session-level, and a pooler reusing connections can hand out one with the wrong path set, silently pointing queries at the wrong schema.

Native enums for fixed-vocabulary fields:

CREATE TYPE application_status_enum AS ENUM
  ('draft','submitted','under_review','accepted','enrollment_pending','enrolled','rejected','withdrawn');

Enforced at the database level — an invalid value can't be inserted from any layer — rather than trusting application code to validate a string column. The cost is a migration to add a value, which is a fair price for the guarantee.

jsonb for genuinely variable-shape data — form configs, audit before/after snapshots, per-entity metadata — where Postgres can still index and query into the structure, unlike a text column holding serialised JSON.

Composite indexes matched to real queries, not a reflex index on every foreign key:

@@index([tenant_id, status, created_at(sort: Desc)], map: "idx_app_tenant_status_created")

That supports "this organisation's applications, filtered by status, newest first" — the single most common admin query — in one index scan instead of filter-then-sort.

deleted_at indexes on every soft-deletable table, because "not deleted" queries filter on deleted_at IS NULL constantly and that has to stay fast at scale.

Partial unique indexes for fields that are optional but must be unique when present:

@@unique(map: "uq_applications_number", where: raw("(application_number IS NOT NULL)"))

A plain unique constraint has database-specific semantics around multiple NULLs; the partial index makes the actual intent explicit.