uplift.page
← library
prompt🗄️ Databasesv2 · updated 2026-06-12

Postgres schema with RLS

Schema design for multi-tenant Postgres: constraints, RLS, indexes, vectors.

Run it as a prompt

Paste this into any AI agent, or fetch it: curl -s https://uplift.page/api/v1/prompts/postgres-schema-design/raw

prompt.md
# Postgres schema with RLS

Design a Postgres schema that enforces tenant isolation in the database itself and stays fast as data grows.

## Recommended stack

- **Postgres 16+** - database. Constraints, RLS, generated columns, and pgvector cover almost every app need.
- **Row-Level Security** - tenant isolation. Policies make 'whose row is this' a database guarantee, not an app convention.
- **pgvector** - embeddings. Vector search next to relational data; HNSW indexes for scale.
- **SQL migrations (versioned)** - change control. Reviewable, ordered, reversible-by-forward-fix schema history.

## Build steps

1. Every tenant-owned table gets org_id; enable RLS deny-by-default, then write explicit policies per verb.
2. Wrap membership checks in `security definer` helper functions to avoid policy recursion and keep policies one-line.
3. Constrain everything: foreign keys, checks on enums, unique on natural keys, not-null by default.
4. Index for queries you actually run: composite (org_id, created_at desc) beats five single-column guesses.
5. Test isolation with two JWTs in SQL: user A literally cannot select user B's rows.

## Watch out for

- RLS policies that subquery the same table (infinite recursion) - use definer helpers.
- Forgetting service-role paths bypass RLS; guard privileged endpoints in code too.
- JSONB for data you filter on; columns are for queries, jsonb is for payloads.

## Definition of done

- Cross-tenant read attempt returns zero rows with a member JWT
- EXPLAIN shows index scans on the top five queries
- Schema rebuilds from migrations alone on a blank database

The full prompt

Design a Postgres schema that enforces tenant isolation in the database itself and stays fast as data grows.

Recommended stack

  • Postgres 16+ - database. Constraints, RLS, generated columns, and pgvector cover almost every app need.
  • Row-Level Security - tenant isolation. Policies make 'whose row is this' a database guarantee, not an app convention.
  • pgvector - embeddings. Vector search next to relational data; HNSW indexes for scale.
  • SQL migrations (versioned) - change control. Reviewable, ordered, reversible-by-forward-fix schema history.

Build steps

  1. Every tenant-owned table gets org_id; enable RLS deny-by-default, then write explicit policies per verb.
  2. Wrap membership checks in security definer helper functions to avoid policy recursion and keep policies one-line.
  3. Constrain everything: foreign keys, checks on enums, unique on natural keys, not-null by default.
  4. Index for queries you actually run: composite (org_id, created_at desc) beats five single-column guesses.
  5. Test isolation with two JWTs in SQL: user A literally cannot select user B's rows.

Watch out for

  • RLS policies that subquery the same table (infinite recursion) - use definer helpers.
  • Forgetting service-role paths bypass RLS; guard privileged endpoints in code too.
  • JSONB for data you filter on; columns are for queries, jsonb is for payloads.

Definition of done

  • Cross-tenant read attempt returns zero rows with a member JWT
  • EXPLAIN shows index scans on the top five queries
  • Schema rebuilds from migrations alone on a blank database

Served from the uplift.page library and refreshed within 5 minutes of every update.