Supabase PostgREST: Advanced Queries

The first time I shipped a feature against PostgREST, I wrote a Node service in front of it to "translate" REST calls into something I trusted. Two weeks later I deleted that service. It turned out every endpoint I had hand-rolled was already expressible as a single Supabase query string, and my translation layer was just a slower, buggier copy of the filter grammar I had refused to learn. The embarrassment of that diff is what made me sit down and actually read the PostgREST docs end to end.

This article is the walkthrough I wish I had then. You will start from a fresh Supabase project, seed a small relational schema, and then work through filter operators (eq, gt, like, in, and or/and compositions), foreign-key embeds with nested selects, ordering and Range-header pagination, full-text search backed by tsvector and websearch_to_tsquery, RPC calls into plain Postgres functions, and finally Row Level Security policies verified per role. The client examples lean on @supabase/supabase-js and raw fetch, with SQL on the database side and a sprinkle of psql for inspection. The rule I keep coming back to: if you are writing application code to filter, join, or paginate Supabase data, you are usually writing code Postgres already wrote for you.

This is aimed at developers who have touched Supabase enough to run select * from the dashboard but stall the moment a real query needs a join, a search box, or a per-tenant guard. By the end you will have a seeded schema, a notebook of working queries from trivial equality through RLS-gated RPC, and enough confidence to delete your own translation layer.

Step 1: Standing Up the Supabase Q&A Schema and Seed Set

Every later step in this series points PostgREST at the same database, so step 1 has to lock down a relational shape that exercises the features we want to teach later — embedded resources, full-text search, computed counts, and row-level policies. We sketch a small Q&A community: authors write questions filed under categories, other authors post answers, both ends carry tags and polar votes. The shape gives us multi-step joins to flatten, a tsvector column to query, and a unique constraint that doubles as a deduplication contract.

The deliverable is a Supabase CLI project on disk: a config.toml that boots Postgres + PostgREST locally, an initial migration that creates a dedicated app schema, and a seed file with deterministic UUIDs so the article snippets in later steps quote real row ids. We also drop in a tiny Python inspector + pytest suite that parses the SQL statically, so the build verifies the shape without needing a live Postgres container during CI.

Setup

Create the layout below inside codebase/. Nothing here calls a network — the migration and seed are plain SQL, and the test suite reads them as text:

codebase/
├── pyproject.toml
├── supabase/
│   ├── config.toml
│   ├── migrations/0001_initial_schema.sql
│   └── seed.sql
├── src/supabase_practice/
│   ├── __init__.py
│   └── schema_loader.py
└── tests/
    ├── test_schema.py
    └── test_seed.py

The only runtime dependency for tests is pytest. Wire it through pyproject.toml as an optional dev extra and point pythonpath at src/ so the suite resolves the package without an editable install:

[project]
name = "supabase-postgrest-advanced-queries"
version = "0.1.0"
requires-python = ">=3.9"
dependencies = []

[project.optional-dependencies]
dev = ["pytest>=7.0"]

[tool.pytest.ini_options]
testpaths = ["tests"]
pythonpath = ["src"]
addopts = "-ra -q"

If you want to run the database side too, install the Supabase CLI and supabase start from inside codebase/supabase/. The CLI is optional for this step — every test we write here runs against the SQL text, not a live cluster.

Implementation

Start with supabase/config.toml. We pin Postgres 15, expose PostgREST on 54321, and tell the API surface to look in both public and the new app schema. The extra_search_path line matters: later steps lean on Postgres extensions (pg_trgm, pgcrypto) and PostgREST has to find their operators when it builds query plans.

project_id = "supabase-postgrest-advanced-queries"

[api]
enabled = true
port = 54321
schemas = ["public", "app"]
extra_search_path = ["public", "app", "extensions"]
max_rows = 1000

[db]
port = 54322
shadow_port = 54320
major_version = 15

[db.seed]
enabled = true
sql_paths = ["./seed.sql"]

Next, write supabase/migrations/0001_initial_schema.sql. Everything lives under a dedicated app schema so we keep the public surface clean and can later flip RLS on per-table without disturbing Supabase internals. We enable pgcrypto for gen_random_uuid() because authors are UUID-keyed — that lets later steps test idempotent inserts without a serial sequence handing out colliding ids.

create extension if not exists "pgcrypto";
create schema if not exists app;

create table app.authors (
    id uuid primary key default gen_random_uuid(),
    username text not null unique,
    display_name text not null,
    bio text,
    reputation integer not null default 0,
    joined_at timestamptz not null default now(),
    constraint authors_username_lowercase check (username = lower(username))
);

create table app.categories (
    id bigserial primary key,
    slug text not null unique,
    name text not null,
    description text not null,
    constraint categories_slug_kebab check (slug ~ '^[a-z0-9]+(-[a-z0-9]+)*$')
);

The categories_slug_kebab check enforces URL-safe slugs at the database layer rather than trusting a Node validator upstream. PostgREST will surface the constraint violation as an HTTP 400 with a readable detail, so the client never has to guess why a write failed. The same pattern repeats on app.tags.

The questions table earns the most thought because every later step queries it:

create table app.questions (
    id bigserial primary key,
    author_id uuid not null references app.authors(id) on delete cascade,
    category_id bigint not null references app.categories(id) on delete restrict,
    title text not null,
    body text not null,
    view_count integer not null default 0,
    is_closed boolean not null default false,
    created_at timestamptz not null default now(),
    search tsvector generated always as (
        setweight(to_tsvector('english', coalesce(title, '')), 'A')
        || setweight(to_tsvector('english', coalesce(body, '')), 'B')
    ) stored,
    constraint questions_title_nonempty check (length(trim(title)) > 0),
    constraint questions_body_nonempty check (length(trim(body)) > 0)
);

create index questions_search_idx on app.questions using gin (search);
create index questions_category_idx on app.questions (category_id);
create index questions_author_idx on app.questions (author_id);

Two FK choices deserve a callout. author_id uses on delete cascade because deleting an author should sweep their content; category_id uses on delete restrict because losing a category mid-flight would orphan thousands of rows. The search column is a GENERATED ALWAYS AS ... STORED tsvector that weights the title above the body, so a single GIN index serves both fields and we never have to re-derive the vector from the application layer.

Answers, tags, and votes round out the graph. The votes table is where the next step's "top answers" lesson hooks in, so we pre-bake the rules: polar value (-1 or +1) and a unique pair (author_id, answer_id) so a single author cannot vote twice on the same answer:

create table app.votes (
    id bigserial primary key,
    author_id uuid not null references app.authors(id) on delete cascade,
    answer_id bigint not null references app.answers(id) on delete cascade,
    value smallint not null,
    created_at timestamptz not null default now(),
    constraint votes_value_polar check (value in (-1, 1)),
    constraint votes_one_per_author unique (author_id, answer_id)
);

The seed file in supabase/seed.sql plants five authors with hand-picked UUIDs, five categories, eight tags, six questions, seven answers, and eleven votes. Determinism matters because later articles say things like "answer 7 is the top-voted one" — if we reseeded with random ids, every snippet in the series would drift.

insert into app.authors (id, username, display_name, reputation, joined_at) values
    ('11111111-1111-1111-1111-111111111111', 'ada',   'Ada Liu',    1840, '2024-01-12T10:00:00Z'),
    ('22222222-2222-2222-2222-222222222222', 'noor',  'Noor Patel',  920, '2024-03-04T08:30:00Z'),
    ('44444444-4444-4444-4444-444444444444', 'maria', 'Maria Cruz', 3210, '2023-09-01T09:00:00Z');

select setval(pg_get_serial_sequence('app.categories', 'id'),
              (select max(id) from app.categories));

The setval calls after each explicit-id insert reset the serial sequences so a later step writing through PostgREST does not collide with the seeded ids. Forgetting this is a classic Supabase footgun — the seed loads fine, then the first POST from the REST surface returns a duplicate-key error and the operator wastes an hour reading the wrong stack trace.

Finally, src/supabase_practice/schema_loader.py parses both files with a small recursive splitter so tests can run without a Postgres process. The parser extracts table names, primary keys, foreign keys, and rows from INSERT INTO ... VALUES blocks:

def load_schema(migrations_dir: Path = MIGRATIONS_DIR) -> Schema:
    tables: dict[str, Table] = {}
    indexes: list[str] = []
    files = sorted(migrations_dir.glob("*.sql"))
    if not files:
        raise FileNotFoundError(f"no migrations found in {migrations_dir}")
    for path in files:
        sql = _strip_comments(path.read_text(encoding="utf-8"))
        for match in _TABLE_RE.finditer(sql):
            table = _parse_table(match.group("name"), match.group("body"), match.group(0))
            tables[table.name] = table
        for match in _INDEX_RE.finditer(sql):
            indexes.append(match.group("name"))
    return Schema(tables=tables, indexes=tuple(indexes))

The loader is deliberately small — it is not a real SQL parser, only enough surface to assert "this table exists, with these columns and these FK edges." Keeping it text-only means the CI pipeline never has to spin a database for the article repo, but the tests still fail loudly when the migration drifts away from what later steps depend on.

Verification

Run pytest from inside codebase/. With nineteen targeted assertions across the schema and the seed, a green run proves both files match the contract we just defined:

pytest

...................                                                      [100%]
19 passed in 0.05s

What we built

We now have a runnable Supabase project skeleton: a CLI config that boots Postgres + PostgREST locally, a single migration that defines the entire Q&A graph under a dedicated app schema, and a seed file packed with deterministic rows for every later step to reference.

The schema bakes in invariants the rest of the series leans on — lowercase usernames, kebab-case slugs, weighted-tsvector search, polar votes, and a one-vote-per-author guard. None of these are application-level checks; PostgREST will reject violations at the database boundary and surface readable error details to clients.

A small SQL inspector plus a pytest suite pin the shape statically. The build does not need a live Postgres process to verify the schema matches the contract, which keeps the article repo cheap to clone and run.

That foundation unlocks step 2: with stable categories, questions, and votes already on disk, we can point PostgREST at the database and start composing the four basic read primitives — column selection, filter operators, ordering, and pagination — through a typed, immutable URL builder.

Repository

The state of the code after this step: 5a2c758

Step 2: Encoding PostgREST's Select, Filter, Order, and Page Surface as a Frozen Builder

Step 1 left a Supabase project on disk with a stable app schema and a deterministic seed: five authors, five categories, six questions, seven answers, eleven votes. Step 2 climbs the next rung — the read-side surface of PostgREST — and pins the four primitives every later lesson keeps reaching for: column selection, filter operators, ordering, and pagination.

The deliverable is supabase_practice.postgrest_query, a frozen PostgrestQuery dataclass that knows how to render the URLs PostgREST expects. Every later step in the series — logical filter composition, embedded resources, RPC calls — extends this module rather than re-deriving URL semantics. Mastering the basics behind a typed surface now keeps the rest of the article snippets short, honest, and quotable.

Setup

One new module joins codebase/src/supabase_practice/ and a matching pytest module joins the suite. No new third-party dependencies are pulled in — the builder rides on dataclasses and urllib.parse from the standard library, which keeps the article repo cheap to clone and run:

codebase/
├── src/supabase_practice/
│   ├── __init__.py            # re-export query/PostgrestQuery/Filter/OrderClause
│   └── postgrest_query.py     # new — the immutable builder
└── tests/
    └── test_postgrest_query.py  # new — 18 URL-shape assertions

Re-export the public surface so other modules and tests can write from supabase_practice import query instead of reaching into the submodule:

from supabase_practice.postgrest_query import (
    DEFAULT_BASE_URL,
    Filter,
    OrderClause,
    PostgrestQuery,
    query,
)

The package stays import-clean — nothing here boots Postgres, so the file lands as pure data plus URL serialisation. Step 1's schema_loader keeps living alongside it, untouched.

Implementation

PostgrestQuery is a frozen dataclass. Each fluent method returns a new instance via dataclasses.replace, so a base = query("questions").select("id", "title") can be reused across call sites without leaking later filters back into the shared base. The four primitives — columns, filters, order_by, and the (row_limit, row_offset) pair — map one-to-one onto the PostgREST query-string keys the renderer later emits.

@dataclass(frozen=True)
class PostgrestQuery:
    table: str
    base_url: str = DEFAULT_BASE_URL
    columns: tuple[str, ...] = ()
    filters: tuple[Clause, ...] = ()
    order_by: tuple[OrderClause, ...] = ()
    row_limit: int | None = None
    row_offset: int | None = None

    def select(self, *columns: str) -> "PostgrestQuery":
        return replace(self, columns=tuple(columns))

Immutability is the load-bearing choice. PostgREST URLs are easy to misread when one helper silently mutates state across a chained call, so we pay the small allocation cost to get value semantics. Every collection attribute is a tuple rather than a list so the dataclass stays hashable and trivially comparable in the test suite.

Each filter is its own dataclass that renders itself as a (key, value) pair. The negate flag prefixes not. exactly the way PostgREST wants it, and the value passes through urllib.parse.quote so spaces, commas, and quotes survive the network hop:

@dataclass(frozen=True)
class Filter:
    column: str
    operator: str
    value: str
    negate: bool = False

    def render(self) -> tuple[str, str]:
        prefix = "not." if self.negate else ""
        return self.column, f"{prefix}{self.operator}.{_encode(self.value)}"

The safe charset on the encoder matters more than it looks. PostgREST relies on bare commas inside in.(...) and parentheses around the value list, so we whitelist ,.()*" explicitly; otherwise urllib.parse.quote would escape them and the API would silently return an empty result. The same allowance covers the * wildcard PostgREST aliases for SQL LIKE's %.

The fluent surface covers the eight comparison operators (eq, neq, gt, gte, lt, lte, like, ilike) plus the two PostgREST-specific verbs is and in. The is_ method guards against typos by rejecting anything outside null, true, false, and unknown — the only four values PostgREST will accept for that operator:

def eq(self, column: str, value: object, *, negate: bool = False) -> "PostgrestQuery":
    return self.where(eq(column, value, negate=negate))

def is_(self, column: str, value: str) -> "PostgrestQuery":
    if value not in _IS_VALUES:
        raise ValueError(f"is.<value> must be one of {_IS_VALUES}, got {value!r}")
    return self.where(is_(column, value))

def in_(self, column: str, values, *, negate: bool = False) -> "PostgrestQuery":
    return self.where(in_(column, values, negate=negate))

_format_in_value is the only piece that earns a private helper: when one of the listed values contains a comma, paren, space, or quote, PostgREST requires that value to be wrapped in double quotes. Folding that logic into the builder means the article can show in_("display_name", ["Ada Liu", "Noor Patel"]) without warning readers about quoting rules first.

Ordering and pagination round out the public API. The order method accepts an explicit nulls_last so the rendered fragment matches PostgREST's column.desc.nullslast shape; page translates a (page, size) pair into the equivalent limit plus offset so callers do not have to compute the offset arithmetic themselves:

def order(self, column: str, *, descending: bool = False, nulls_last: bool | None = None) -> "PostgrestQuery":
    clause = OrderClause(column=column, descending=descending, nulls_last=nulls_last)
    return replace(self, order_by=self.order_by + (clause,))

def page(self, page: int, size: int) -> "PostgrestQuery":
    if page < 0 or size <= 0:
        raise ValueError("page must be >= 0 and size > 0")
    return self.limit(size).offset(page * size)

The final build() walks every stored fragment in a fixed order — select, filters, order, limit, offset — so the rendered URL is deterministic. That stability is the property the test suite actually pins; reordering the call chain still produces the same URL, which keeps the article snippets quotable across the rest of the series.

Verification

Run the new builder suite from inside codebase/. Eighteen targeted assertions cover bare paths, column projection, every comparison operator, is/in with negation, multi-column ordering with nulls-last handling, the pagination helper, immutability of the base builder, custom base URLs, and percent-encoding of special characters:

pytest tests/test_postgrest_query.py

============================= test session starts ==============================
collected 18 items

tests/test_postgrest_query.py ..................                         [100%]

============================== 18 passed in 0.05s ==============================

A green run proves each cell of the operator matrix matches the URL fragments quoted in this article, and that a base builder shared across two call sites does not leak filters between them.

What we built

PostgrestQuery is now the typed front door for every URL the rest of the series quotes. Column projection, every comparison operator PostgREST exposes, the is and in verbs with negation, multi-column ordering with nulls-last handling, and a page(page, size) shortcut all live behind one fluent API.

The dataclass is frozen, so a base builder can be safely reused across tests and code paths without the surprise mutations that plague string-based URL composition. Percent-encoding sits inside the Filter.render boundary, which means the article never has to teach readers to quote() values themselves before pasting a URL into a PostgREST call.

The verification step pairs the new module with eighteen targeted assertions — one per cell of the operator matrix plus the immutability check — and finishes in well under a tenth of a second. A regression in the renderer fails loudly and locally, long before any later step tries to compose logical filters or embed resources on top of it.

That gives the next step a stable substrate. Adding nested and=(...) / or=(...) clauses becomes a thin extension to the existing filter slot, and the future embed and RPC helpers can plug straight into params() without reasoning about URL escaping a second time.

Repository

The state of the code after this step: 75859c1

Step 3: Embedding Related Resources and Traversing Foreign Keys in One PostgREST Request

Step 2 froze the four basic read primitives — select, filter operators, order, page — into a typed builder. The next bottleneck a real client hits is N+1: fetching ten questions, then a separate round trip for each question's author, category, answers, and votes. PostgREST sidesteps that by letting one select= value pull related rows alongside the parent, but the grammar is dense: aliases, foreign-key hints, inner joins, multi-level nesting, and filters on embedded paths all share the same query parameter.

Step 3 grows the builder to cover that surface. We add a single Embed value object and a fluent .embed(...) method on PostgrestQuery, then prove the join semantics with a simulate(...) helper that hydrates seeded rows in pure Python. By the end of the step, an end-to-end "question thread" URL — parent question, asker, category, accepted answers, each answer's responder and votes — fits in one round trip and one quotable snippet.

Setup

The step adds one module and one test file. No new third-party dependencies are pulled in: the embedding renderer is dataclasses plus string assembly, and the simulation layer reads the seed rows that step 1's seed_data helper already exposes. The layout under codebase/ grows by exactly two files:

codebase/
├── src/supabase_practice/
│   ├── embedding.py          # new — Embed value object + simulate()
│   └── postgrest_query.py    # edited — embeds slot + _render_select()
└── tests/
    └── test_embedding.py     # new — 18 URL + semantic assertions

Re-export the new public surface from the package root so callers can write from supabase_practice import Embed, query, simulate without reaching into submodules:

from supabase_practice.embedding import Embed, simulate
from supabase_practice.postgrest_query import query

The base package still imports without booting Postgres. Step 1's schema loader, step 2's query builder, and step 3's embed renderer all coexist as pure data plus URL serialisation.

Implementation

The Embed dataclass collects every knob PostgREST exposes on a single embedded relation. Six fields — resource, alias, fk, inner, columns, children — map one-to-one onto the wire-format modifiers. Marking the dataclass frozen=True matches the immutability discipline the builder already uses; an embed produced once is reusable across call sites without mutation surprises.

@dataclass(frozen=True)
class Embed:
    resource: str
    alias: str | None = None
    fk: str | None = None
    inner: bool = False
    columns: tuple[str, ...] = ()
    children: tuple["Embed", ...] = ()

Rendering splits into a head and a body. The head emits alias:resource when an alias is set, then appends !fk and !inner in a fixed order so two parallel embeds to the same table — sender:authors!from_id and recipient:authors!to_id — read predictably. The body is the column projection plus any recursively rendered children, joined with commas, wrapped in parentheses.

def render(self) -> str:
    return f"{self._render_head()}({self._render_body()})"

def _render_head(self) -> str:
    prefix = f"{self.alias}:" if self.alias else ""
    modifiers = ""
    if self.fk:
        modifiers += f"!{self.fk}"
    if self.inner:
        modifiers += "!inner"
    return f"{prefix}{self.resource}{modifiers}"

def _render_body(self) -> str:
    parts: list[str] = list(self.columns) if self.columns else ["*"]
    parts.extend(child.render() for child in self.children)
    return ",".join(parts)

The empty-columns case falls back to * rather than emitting an empty parenthesis, which keeps the URL valid even when a caller just wants "embed this whole relation." Children render recursively, so a question can carry answers that themselves carry votes inside one select= value.

PostgrestQuery grows one new tuple field plus one fluent method. Embeds live in their own slot rather than mixing into columns because PostgREST cares about order within the select fragment — bare columns first, then each embed in call order — and keeping them separate makes that ordering trivial to enforce in _render_select().

@dataclass(frozen=True)
class PostgrestQuery:
    ...
    embeds: tuple[Embed, ...] = ()

    def embed(self, *embeds: Embed) -> "PostgrestQuery":
        return replace(self, embeds=self.embeds + tuple(embeds))

    def _render_select(self) -> str | None:
        parts: list[str] = list(self.columns)
        parts.extend(e.render() for e in self.embeds)
        if not parts:
            return None
        return ",".join(parts)

Filtering and ordering on embedded columns reuse the step 2 machinery untouched. Because PostgREST already accepts dotted paths like answers.is_accepted=eq.true and order=answers.created_at.desc, no new code is required — the existing eq(...) and order(...) methods accept the dotted column name verbatim and the renderer emits it directly. That is the load-bearing reason the embed slot stays separate from filters: each surface evolves independently.

The semantic layer lives next to the renderer. simulate(...) takes a list of parents, an Embed spec, a list of related rows, the parent-side and child-side join keys, and a many=True/False flag for to-many vs. to-one cardinality. It groups the related rows by the child key, projects them through the spec's column list, and attaches the matching subset to each parent under spec.response_key().

def simulate(parents, spec, related, *, parent_key, child_key, many=True):
    grouped: dict[Any, list[dict[str, Any]]] = {}
    for row in related:
        grouped.setdefault(row.get(child_key), []).append(_project(row, spec.columns))
    return [_attach(parent, parent_key, grouped, spec, many) for parent in parents]

Two invariants matter for the tests downstream. Parents with no matches must surface an empty list rather than a missing key — callers iterate the embed without a KeyError guard. And the many=False path returns either a single dict or None, mirroring how PostgREST collapses a to-one embed into a scalar object instead of a one-element array.

Verification

Run the new suite from inside codebase/. Eighteen targeted assertions pin every cell of the embedding matrix: plain embed, projected columns, alias rename, FK-hint disambiguation, inner join, the combined !fk!inner ordering, multi-level nesting, two parallel embeds in call order, an embed without a bare-column prefix, filter and order on embedded paths, an end-to-end thread query that combines every lever, builder immutability across a chained call, plus four semantic checks that hydrate the seeded rows.

pytest tests/test_embedding.py

============================= test session starts ==============================
collected 18 items

tests/test_embedding.py ..................                               [100%]

============================== 18 passed in 0.09s ==============================

A green run proves both faces of the contract simultaneously. The URL-shape tests confirm every fragment quoted in this article round-trips exactly. The simulation tests confirm that the same Embed spec, fed real seeded rows, attaches authors to questions, votes to answers, and answers-with-votes to questions in the shape PostgREST would return.

What we built

Embed is now the typed front door for every embedded-resource URL the rest of the series writes. Alias, FK hint, inner-join switch, column projection, and recursive nesting all flow through one dataclass that the query builder concatenates into the existing select= slot.

The builder stays immutable. A base query("questions").select("id", "title") shared across two call sites does not leak the embeds added downstream — base.embeds == () even after extended = base.embed(Embed("authors")) returns a new instance with one embed. That property is pinned by an explicit assertion in the suite, so a future regression in replace() semantics fails loudly.

The semantic simulator lets the article walk the seed graph without booting Postgres. Question 1 carries answers 1 (with three upvotes) and 2 (one downvote); answer 6 — which has no votes — surfaces an empty list rather than a missing key. Those invariants ride directly on the seeded UUIDs and bigserials from step 1, which is why determinism in the seed file paid off here.

That gives the next step a stable foundation. Logical-filter composition over embedded columns becomes a thin wrapper around the existing dotted-path filter, and a future full-text-search step can return embed-shaped JSON without re-deriving the serialisation.

Repository

The state of the code after this step: f7bf935

Step 4: Wiring the Four PostgREST Full-Text Search Operators Into the Query Builder

Step 3 left the builder with a clean read surface — select, filter operators, logical groups, ordering, pagination, and foreign-key embeds — all rendering through one URL renderer. The seam this step opens is the one that shows up the moment a caller wants to ask "find every question that mentions PostgREST". The schema already carries the right machinery — step 1 declared a search tsvector GENERATED column on app.questions plus a GIN index over it — but the builder has no vocabulary for the four PostgREST operators (fts, plfts, phfts, wfts) that target a tsvector.

Step 4 grows that vocabulary. We add a single FullTextQuery value object, four module-level constructors, four fluent methods on PostgrestQuery, and a pure-Python match_websearch helper that evaluates websearch-style queries against arbitrary text so the article's seed-row assertions stay honest without booting Postgres + PostgREST.

Setup

The step adds one new module, one test file, and a handful of fluent methods plus an import on the existing builder. No third-party dependencies are pulled in — dataclasses, re, and urllib.parse from the standard library carry the whole module. The layout under codebase/ grows by exactly two files plus an edit to postgrest_query.py:

codebase/
├── src/supabase_practice/
│   ├── fts.py                  # new — FullTextQuery, fts/plfts/phfts/wfts, match_websearch
│   ├── postgrest_query.py      # edited — fluent fts/plfts/phfts/wfts methods on the builder
│   └── __init__.py             # edited — re-export the new public surface
└── tests/
    └── test_fts.py             # new — 24 URL + websearch-semantics assertions

Re-export the new names from the package root so callers can keep their imports flat:

from supabase_practice import (
    FullTextQuery,
    fts,
    match_websearch,
    phfts,
    plfts,
    query,
    wfts,
)

The package still imports without booting Postgres. FullTextQuery is a plain @dataclass(frozen=True), and the builder edits stay inside the replace(...) immutability discipline the earlier steps locked in.

Implementation

FullTextQuery owns the wire format for all four operators. PostgREST mirrors the column-equals-operator-dot-query shape used by eq and friends, so ?search=wfts.postgrest and ?search=fts(english).postgrest%20%26%20rls are the two ends of the spectrum the renderer has to cover. The dataclass validates the operator against a small whitelist, rejects empty queries, and matches an optional config argument against a strict identifier regex — three guards that catch the typo at construction time rather than at HTTP-response time.

@dataclass(frozen=True)
class FullTextQuery:
    column: str
    operator: str
    query: str
    config: str | None = None
    negate: bool = False

    def __post_init__(self) -> None:
        if self.operator not in _FTS_OPERATORS:
            raise ValueError(
                f"operator must be one of {_FTS_OPERATORS}, got {self.operator!r}"
            )
        if not self.query.strip():
            raise ValueError("full-text query must be a non-empty string")
        if self.config is not None and not _CONFIG_RE.match(self.config):
            raise ValueError(
                f"config must match {_CONFIG_RE.pattern}, got {self.config!r}"
            )

The optional config slot is the load-bearing detail most ad-hoc clients drop on the floor. Without an explicit dictionary the server falls back to default_text_search_config, which is fine for a single caller but a silent trap as soon as two callers disagree on the configuration. Wrapping the config in parens — wfts(english).postgrest — pins the stemming chain to the same one baked into the GENERATED tsvector column at the server.

def _head(self) -> str:
    if self.config:
        return f"{self.operator}({self.config})"
    return self.operator

def render(self) -> tuple[str, str]:
    prefix = "not." if self.negate else ""
    return self.column, f"{prefix}{self._head()}.{_encode(self.query)}"

def render_nested(self) -> str:
    prefix = "not." if self.negate else ""
    return f"{self.column}.{prefix}{self._head()}.{_encode(self.query)}"

render() produces the top-level (name, value) tuple the URL renderer drops into the query string; render_nested() produces the dotted form that fits inside an and(...) / or(...) group. The percent-encoder leaves the tsquery punctuation ,.()*" intact and quotes everything else, so fts.postgrest & rls round-trips as fts.postgrest%20%26%20rls without collapsing two filter parameters into one.

Four top-level constructors mirror PostgREST's four operator flavours so callers can compose a FullTextQuery alone, drop it into a logical group, or hand it to the fluent builder. The semantics map directly to the four to_tsquery family functions: fts accepts the operator-rich grammar (&, |, !, <->), plfts AND-joins free text, phfts keeps terms adjacent for phrase matches, and wfts accepts Google-style websearch syntax with quoted phrases, the OR keyword, and -term exclusions.

def wfts(
    column: str,
    query: str,
    *,
    config: str | None = None,
    negate: bool = False,
) -> FullTextQuery:
    return FullTextQuery(
        column=column, operator="wfts", query=query, config=config, negate=negate
    )

PostgrestQuery grows a matching quartet of fluent methods. Each one delegates to the shared where(...) channel that step 2 already uses for filters and logical groups, which keeps the precedence rules, the renderer, and the immutability story uniform across every clause type the builder knows how to emit.

def wfts(
    self,
    column: str,
    query: str,
    *,
    config: str | None = None,
    negate: bool = False,
) -> "PostgrestQuery":
    return self.where(
        FullTextQuery(
            column=column,
            operator="wfts",
            query=query,
            config=config,
            negate=negate,
        )
    )

match_websearch(query, text) is the second half of the module. PostgREST's wfts operator translates to websearch_to_tsquery on the server, and the article's seed-row assertions need a way to predict which rows that operator would return without booting Postgres. The matcher tokenises the query into bare terms, quoted phrases, and -term negations, splits on the OR keyword for low-precedence disjunction, and applies each group against a lowercased haystack using word-boundary matching. Single-row helpers keep nesting at one level, in line with the codebase's no-deep-nesting rule.

def match_websearch(query: str, text: str) -> bool:
    groups = _split_or_groups(_tokenize_websearch(query))
    if not groups:
        return False
    haystack = text.lower()
    return any(_group_matches(group, haystack) for group in groups)

The matcher is not a full reimplementation of websearch_to_tsquery — there is no stemming and no stop-word list — but it captures the four operator semantics the tests care about: implicit AND between bare terms, low-precedence OR, quoted-phrase substring match, and -term negation. That is exactly enough to anchor the seed-row assertions a paragraph below.

Verification

Run the new suite from inside codebase/. Twenty-four targeted assertions pin every cell of the new surface, split across two halves.

Fifteen assertions cover the URL renderer: the four operators each emit their dotted URL fragment, the optional config wraps in parens at the right position whether or not negate is set, and percent-encoding survives & and whitespace. Construction guards reject bogus operators, empty queries, and unsafe config identifiers; the nested form drops the right way inside logical groups; the four module-level constructors return the right FullTextQuery; and a full chain composes wfts(english).postgrest with gte, order, and limit from step 2.

The remaining nine assertions exercise match_websearch against the seed rows. They cover single-term lookup against title or body, case insensitivity, implicit AND between two bare terms, the OR keyword picking the union, and the precedence rule that parses a b OR c d as (a AND b) OR (c AND d). The last four pin the -term exclusion, the quoted-phrase substring match, the rejection of a phrase in the wrong word order, word-boundary matching that distinguishes "embed" from "embedding", and the empty-query edge case.

pytest tests/test_fts.py

============================= test session starts ==============================
platform darwin -- Python 3.12.5, pytest-9.0.3, pluggy-1.6.0
rootdir: /codebase
configfile: pyproject.toml
collected 24 items

tests/test_fts.py ........................                               [100%]

============================== 24 passed in 0.10s ==============================

A green run proves both halves of the contract at once. The URL-shape assertions guarantee that every snippet quoting ?search=wfts(english).<query> in this article round-trips exactly through the helper. The seed-row assertions pin the websearch matcher against the same app.questions rows the migration seeded in step 1, so the matcher's view of which questions contain "postgrest" or "service role" or service -rls agrees with what the server's wfts operator would return against the GENERATED tsvector column.

What we built

PostgrestQuery now ships with a complete typed surface for the four PostgREST full-text search operators. fts, plfts, phfts, and wfts each render to the exact column=op.query URL fragment PostgREST expects, the optional config argument wraps in parens at the right position to pin the dictionary, and the negate flag flows uniformly through both top-level and nested forms.

FullTextQuery is the underlying value object that makes the whole surface composable. Because it is a frozen dataclass with the same render() / render_nested() shape as Filter, it drops into the same logical-group machinery from step 2 — a wfts clause can sit inside an or_(...) next to two eq filters and the renderer treats them identically.

match_websearch closes the test loop without leaning on a live Postgres. The matcher is small enough to fit on a page and captures the four operator semantics the seed assertions care about — implicit AND, low-precedence OR, quoted phrases, and -term negation — which is exactly what the article needs to claim a specific row set without standing up Supabase first.

That sets up the next step cleanly. With the four FTS operators wired through the same where(...) channel as every other filter, the upcoming computed-and-generated-columns step can layer on the next slice of PostgREST's column vocabulary — exposing GENERATED columns, computed view columns, and SQL functions through select — without re-deriving how a wfts clause and a gte filter end up next to each other in one URL.

Repository

The state of the code after this step: 8f17535

Step 5: Running Websearch Queries End-to-End Against the Seeded tsvector Column

Step 4 wired the four PostgREST full-text operators (fts, plfts, phfts, wfts) into the query builder, validated their URL rendering against twenty-four assertions, and shipped a pure-Python match_websearch helper that mirrors websearch_to_tsquery closely enough to predict which seeded rows the server would return. What it did not do is exercise those operators end-to-end against the seed. The wiring is a contract; the contract is only worth what we can prove it returns.

Step 5 closes that loop for the operator most user-facing clients actually reach for — wfts, which translates to websearch_to_tsquery on the server. We take a small catalogue of user-typed queries (postgrest, "service role", service -rls, paginate postgrest OR service role), render each one through the step-4 builder, and pair the URL with the exact row set the GENERATED search tsvector column on app.questions would return. The same match_websearch function from step 4 plays the role of the server, which keeps the demonstration honest without booting Postgres + PostgREST.

Setup

No new modules are introduced and no new dependencies are pulled in. The two files that already exist after step 4 carry the entire surface this step exercises, and the migration from step 1 supplies the GENERATED tsvector column the URL targets:

codebase/
├── supabase/migrations/
│   └── 0001_initial_schema.sql   # GENERATED search column on app.questions + GIN index
├── src/supabase_practice/
│   └── fts.py                    # FullTextQuery, wfts(...), match_websearch
└── tests/
    └── test_fts.py               # URL-shape + seed-row websearch assertions

The seed rows pinned by step 1's 0001_initial_schema.sql migration are the fixed input to every assertion in this step. The search column is declared generated always as (setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B')) stored, which means a wfts(english).<query> URL hits a single column through the GIN index whether the match lives in the title or the body, and title hits carry stronger lexemes than body hits for any future ranking step.

The relevant subset for the websearch demonstrations is the six app.questions rows — question 1 mentions "PostgREST" and "paginate", question 3 mentions "service" plus "RLS", question 4 mentions "tsvector", and question 5 mentions "PostgREST" and "computed total". The matcher and the GENERATED tsvector both target the concatenation of title and body, so a websearch run sees the same haystack in both worlds.

Implementation

The shape of an end-to-end run is short on purpose: build the URL through the fluent surface from step 4, then ask match_websearch which seeded rows that URL would select. The two halves stay decoupled so the URL renderer never accidentally drifts away from the matcher's interpretation of the same query string.

from supabase_practice import match_websearch, query, rows_for


def run_websearch(user_input: str) -> tuple[str, set[int]]:
    url = (
        query("questions")
        .select("id", "title")
        .wfts("search", user_input, config="english")
        .build()
    )
    questions = rows_for("app.questions")
    matched = {
        q["id"]
        for q in questions
        if match_websearch(user_input, f"{q['title']} {q['body']}")
    }
    return url, matched

The config="english" argument is the load-bearing detail. It wraps the operator as wfts(english).<query>, which pins the server's lexer to the same dictionary the GENERATED column was built with at setweight(to_tsvector('english', ...), 'A') || setweight(to_tsvector('english', ...), 'B'). Dropping the argument silently delegates to default_text_search_config on the server — fine until two callers disagree on the dictionary and the same URL starts returning different rows on different deploys.

match_websearch carries the four websearch semantics the server's wfts commits to: implicit AND between bare terms, low-precedence OR, quoted-phrase substring match, and a -term exclusion. It is not a full reimplementation — there is no stemming and no stop-word list — but every assertion in test_fts.py is written against a haystack where stemming would not change the answer, so the local matcher and the server agree on the row set.

def match_websearch(query: str, text: str) -> bool:
    groups = _split_or_groups(_tokenize_websearch(query))
    if not groups:
        return False
    haystack = text.lower()
    return any(_group_matches(group, haystack) for group in groups)

The four scripted runs that exercise the full operator surface look like this. Each one pairs the user input, the URL the builder emits, and the row set the matcher claims the server would return. The URL goes on the wire; the row set goes into the test assertion that pins the contract.

runs = [
    "postgrest",
    '"service role"',
    "service -rls",
    "paginate postgrest OR service role",
]
for user_input in runs:
    url, matched = run_websearch(user_input)
    print(f"{user_input!r:42} -> {url}  -> {sorted(matched)}")

The first run picks up questions 1 and 5 — both mention "PostgREST" somewhere in the title or body. The second narrows the haystack to phrase matches and lands on question 3 alone. The third demonstrates -term exclusion: question 3 mentions "service" but also "RLS", so the negation drops it and the result is empty. The fourth shows that OR has lower precedence than implicit AND, so the parser reads (paginate AND postgrest) OR (service AND role) and returns questions 1 and 3.

The full chain composes the websearch clause with the filter, ordering, and pagination patterns from earlier steps without re-deriving any of them. The same where(...) channel that carries an eq filter carries a wfts clause, so a single URL can ask "the top five most-viewed PostgREST questions" without any new syntax:

url = (
    query("questions")
    .select("id", "title", "view_count")
    .wfts("search", "postgrest", config="english")
    .gte("view_count", 200)
    .order("view_count", descending=True)
    .limit(5)
    .build()
)
# /rest/v1/questions
#   ?select=id,title,view_count
#   &search=wfts(english).postgrest
#   &view_count=gte.200
#   &order=view_count.desc
#   &limit=5

That one URL is the running-state equivalent of the article's claim about how websearch composes. On the server, the GIN index narrows the candidate set for wfts(english).postgrest, the gte filter trims further, and the final sort plus limit pick the top five — the article never has to claim that order is preserved across operator categories because the builder lays them out in the order PostgREST documents.

Verification

Re-run the full test_fts.py suite from inside codebase/. The twenty-four assertions cover both the URL-shape and the seeded-row halves of the step-5 contract — nine of them are the seed-row websearch tests that pin the exact row sets the scripted runs above will produce, and one of them is the full-chain assertion that pins the combined wfts + gte + order + limit URL byte-for-byte.

pytest tests/test_fts.py

============================= test session starts ==============================
platform darwin -- Python 3.12.5, pytest-9.0.3, pluggy-1.6.0
rootdir: /codebase
configfile: pyproject.toml
collected 24 items

tests/test_fts.py ........................                               [100%]

============================== 24 passed in 0.08s ==============================

A green run is the proof that every URL the step-5 demonstration prints round-trips through the builder unchanged, and that the row set printed alongside each URL is the row set websearch_to_tsquery would have returned on the server. The two halves cannot drift apart silently because they share the same FullTextQuery value object and the same _tokenize_websearch parser.

What we built

End-to-end execution of websearch_to_tsquery queries through the step-4 builder is now a single function call. A user-typed input flows through query(...).wfts("search", input, config="english").build() to produce the exact URL fragment PostgREST expects, and the same input flows through match_websearch(input, text) to predict which seeded rows the server would return.

The four canonical run shapes — single bare term, quoted phrase, -term exclusion, low-precedence OR — are each pinned by an assertion in test_fts.py against the seeded app.questions rows. That is enough to claim a specific result set in plain prose without standing up Supabase first: when this article says "searching for service -rls returns no questions", the matcher proves it on every commit.

The config="english" argument has earned its keep. Wrapping the operator as wfts(english).<query> keeps the server's dictionary pinned to the same one baked into the GENERATED search column at step 1, so the same URL produces the same row set regardless of what default_text_search_config happens to be set to on a given deploy. The A/B weighting on title versus body falls out of the same migration, so a title hit and a body hit both flow through one index without forcing the query layer to know they came from different columns.

That clears the runway for the next step. The websearch URL already composes cleanly with gte, order, and limit through the shared where(...) channel from step 2, so the upcoming step layering computed and generated columns onto the select= clause can keep building on the same uniform vocabulary without revisiting how a wfts clause and a filter end up next to each other in one URL.

Repository

The state of the code after this step: f80c5ab

Step 6: Lifting Postgres Functions Onto the PostgREST URL Grammar via /rpc

Step 5 ran the four full-text operators end-to-end against the seeded tsvector column, proving that a user-typed query flows through the builder and selects the exact row set websearch_to_tsquery would have returned on the server. With every read-side URL surface — select=, filter operators, embeds, full-text search, ordering, pagination — now modelled as a value object, the next surface to lift is the one where PostgREST stops pretending the request is a table read and exposes the function directly at /rest/v1/rpc/<name>. That endpoint is the only honest answer for queries the URL grammar genuinely cannot express: multi-statement writes, aggregates parameterised by the caller, reports with hand-shaped record types.

Step 6 builds the value-object that models that endpoint. We ship an RpcCall dataclass that renders the exact (method, url, headers, body) a PostgREST client would put on the wire for each of the three return shapes Postgres lets a function declare — returns setof <table>, returns table(...), and a bare scalar — and pin every flavour with local evaluators against the same seeded Q&A rows step 1 planted. The migration carries three live examples that anchor the trade space; the Python surface mirrors them; the test suite proves the two halves agree.

Setup

Two new files land under codebase/, and one already-shipped module grows a small extension. The migration declares the three RPC functions PostgREST will mount; the rpc.py module renders the wire calls and ships a local-evaluator registry; the schema loader from step 1 grows enough regex to surface standalone functions on Schema.rpc_functions:

codebase/
├── supabase/migrations/
│   └── 0003_rpc_functions.sql       # three RPCs: setof, table(...), scalar
├── src/supabase_practice/
│   ├── rpc.py                       # RpcCall, RpcRegistry, evaluators
│   └── schema_loader.py             # +RpcArg, +RpcFunction, +Schema.rpc(...)
└── tests/
    └── test_rpc.py                  # 40 assertions across the three angles

No new third-party dependency is pulled in. RpcCall reuses the Clause / OrderClause / CountMode vocabulary already shared by PostgrestQuery from steps 2 through 5, so a post-RPC .eq(...) or .order(...) reads the same as it would on a plain table call. The local evaluators sit inside rpc.py next to the value object so a reader can compare the URL the call renders with the Python the evaluator runs in a single file.

The migration 0003_rpc_functions.sql declares three functions inside the app schema so the parser surfaces them on Schema.rpc_functions rather than confusing them with the row-bound score / answer_count / is_popular computed columns from step 5. The three shapes together cover the response trade space the article wants to anchor: a stable set-returning read, a stable record-returning read, and a volatile writer.

Implementation

The migration is the cleanest place to start because the three function declarations encode the three trade-offs the value object has to honour. The first is a stable set-returning function — PostgREST will surface it as if it were a virtual /<table> resource, which means callers can append filters / order / limit after the RPC path to refine the result set:

create or replace function app.top_questions(min_views integer, max_rows integer)
returns setof app.questions
language sql stable as $$
    select *
    from app.questions
    where view_count >= min_views
    order by view_count desc, id asc
    limit max_rows;
$$;

The second function uses returns table(...) to declare a custom record shape. The wire response is a JSON array of objects with exactly the three columns the function names — no select= projection on the call site can widen or narrow it. This matters when the underlying join touches half a dozen tables but the caller only needs a three-number breakdown:

create or replace function app.author_reputation_breakdown(target_author uuid)
returns table(direct_reputation integer, vote_reputation integer, total integer)
language sql stable as $$ /* sums direct reputation + net votes on owned answers */ $$;

The third is the volatile-writer pattern. language plpgsql volatile tells PostgREST that GET is illegal — the method picker forces every call to POST so a misconfigured proxy that caches GET responses cannot accidentally lose the side effect. Returning the new value (here, view_count + 1) saves the caller a follow-up read on the row they just mutated:

create or replace function app.bump_view_count(target_question bigint)
returns integer
language plpgsql volatile as $$
declare new_count integer;
begin
    update app.questions
        set view_count = view_count + 1
        where id = target_question
        returning view_count into new_count;
    return new_count;
end;
$$;

The Python side is one immutable dataclass. RpcCall carries the function name, the named arguments in insertion order, the chosen HTTP method, an optional single_object flag, an optional schema profile, and the post-RPC refinement quartet (filters, order_by, row_limit, row_offset). Every mutator returns a new instance via dataclasses.replace, so a base call can be shared and refined per call site without leaking state:

@dataclass(frozen=True)
class RpcCall:
    function: str
    base_url: str = "/rest/v1"
    args: tuple[tuple[str, Any], ...] = ()
    http_method: str = "POST"
    single_object: bool = False
    schema_profile: str | None = None
    count_mode: CountMode | None = None
    filters: tuple[Clause, ...] = ()
    order_by: tuple[OrderClause, ...] = ()
    row_limit: int | None = None
    row_offset: int | None = None

The wire renderers split the value four ways. url() always starts with {base_url}/rpc/{function}; on GET, the named arguments ride first as query parameters, then the post-RPC filters / order / limit / offset trail behind. On POST, the URL never sees the arguments — they go into body() as a JSON object, and the filter / order / limit grammar still rides in the URL because PostgREST applies it to the result of the function, not its inputs.

def url(self) -> str:
    params = self._url_params()
    if not params:
        return self.path()
    qs = "&".join(f"{name}={value}" for name, value in params)
    return f"{self.path()}?{qs}"

def body(self) -> str | None:
    if self.http_method == "GET":
        return None
    payload = self._body_payload()
    return json.dumps(payload, default=_json_default, separators=(",", ":"))

Single-object mode is the small but load-bearing extra. When a function declares a single json / jsonb parameter and the caller wants to ship an arbitrary payload, .as_single_object() flips the body interpretation: instead of {"payload": {...}} the wire sees the inner value directly, and Prefer: params=single-object rides in the headers so PostgREST agrees. The constructor refuses to compose the mode with more than one argument because the wire contract has no way to disambiguate.

def as_single_object(self) -> "RpcCall":
    return replace(self, single_object=True)

def __post_init__(self) -> None:
    if self.single_object and len(self.args) > 1:
        raise ValueError("single_object mode requires exactly one argument (or none)")

The schema profile is the other header-side concern. in_schema("app") attaches Content-Profile: app on POST and Accept-Profile: app on GET, which is how PostgREST resolves the function inside a non-default schema without forcing every caller to URL-encode app.top_questions. The two header names are not interchangeable — POST writes through Content-Profile, GET reads through Accept-Profile — so the renderer picks the right one based on the method.

The local-evaluator side mirrors the wire side. QA_RPC is an RpcRegistry of three Rpc entries, each pairing the function name with a pure-Python evaluator that takes the named arguments plus an EvalContext of related tables and returns the value PostgREST would have shipped. _top_questions filters the seeded questions by min_views, sorts by (view_count desc, id asc), and slices to max_rows; _author_reputation_breakdown sums the author's direct reputation with their answers' net votes; _bump_view_count returns view_count + 1 or None when the question id is unknown.

def _top_questions(min_views, max_rows, *, context):
    questions = context.get("app.questions", [])
    filtered = [q for q in questions if int(q.get("view_count", 0)) >= int(min_views)]
    filtered.sort(key=lambda q: (-int(q.get("view_count", 0)), int(q.get("id", 0))))
    return [dict(row) for row in filtered[: int(max_rows)]]

That keeps the two halves of the contract honest. The wire renderer claims a specific URL + body the server would receive; the evaluator claims the specific JSON the server would return for the seeded rows. The test suite asserts both halves against the same RpcCall value, so a regression in either one breaks the build before it ever reaches Postgres.

Verification

Run the new test module from inside codebase/. Forty assertions cover the three angles of the contract: the migration parser surfaces every function shape correctly, the value object renders the wire quartet for every combination of method / single-object / schema-profile / post-RPC refinement, and the local evaluators agree with the migration on what each function returns for the seeded rows.

pytest tests/test_rpc.py

============================= test session starts ==============================
platform darwin -- Python 3.12.5, pytest-9.0.3, pluggy-1.6.0
rootdir: /codebase
configfile: pyproject.toml
collected 40 items

tests/test_rpc.py ........................................                [100%]

============================== 40 passed in 0.11s ==============================

A green run is the proof that rpc("top_questions").with_args(min_views=500, max_rows=5).gte("view_count", 700).order("view_count", descending=True).limit(2).count("exact").in_schema("app") renders the exact URL + body + headers PostgREST documents — and that the evaluator returns the same three question ids top_questions(500, 5) would over the seeded rows. The two assertions cannot drift apart because both halves consume the same RpcCall value.

What we built

RpcCall is now the single value-object for everything PostgREST exposes at /rest/v1/rpc/<name>. A caller chooses a function name, layers named arguments through .with_args(...), picks a method through .get() / .post(), optionally flips on .as_single_object() and .in_schema(...), and refines the result set through the same .eq / .gte / .order / .limit / .offset channel the table-side PostgrestQuery exposes.

The migration 0003_rpc_functions.sql anchors the three return shapes in real SQL. top_questions is the canonical setof <table> read — PostgREST treats the response as a virtual collection so callers can stack a filter, an order, and a limit after the RPC path. author_reputation_breakdown is the returns table(...) flavour, where the response shape is hand-declared so the caller never has to project columns at the call site. bump_view_count is the volatile-writer pattern: POST-only, returns the new value so callers skip the follow-up read.

The local-evaluator side closes the loop without booting Postgres. QA_RPC holds one evaluator per migration-declared function, each one driven by the seeded rows from step 1, so the test suite can pin concrete return values for every flavour. When a reader of this article wants to know what top_questions(500, 5) returns over the seed, the test test_top_questions_filters_then_orders_then_limits has the answer in the assertion [3, 6, 4].

This step is also the last one that broadens the URL surface itself. The remaining work — row-level security, server-side authorisation, error handling on the wire — does not invent new URL grammar; it changes which rows the same URLs are allowed to see and which methods the same RpcCall is allowed to invoke. The next step picks up there, with the value objects from steps 2 through 6 already in place as the unchanging public face of the client.

Repository

The state of the code after this step: 0f569b2

Step 7: Pushing Visibility Into the Planner With Row-Level Security Policies

Step 6 finished the URL grammar. Every PostgREST shape the article cares about — filtered table reads, embeds, full-text search, pagination, computed columns, RPC calls — is now a value object that renders the exact wire payload. What it does not yet model is the only thing Postgres actually cares about by the time those payloads land: which rows is this caller allowed to see, and which mutations is this caller allowed to issue. Up to now every test has implicitly run as a god-mode operator with full read across every table.

Step 7 closes that gap by moving the visibility filter inside the planner. We enable Row-Level Security on the four user-data tables, declare permissive policies that key off the per-request role and auth.uid(), and ship a local evaluator that mirrors what Postgres would have returned for each (role, uid) pair against the seeded rows. The result is that any earlier value object — a step 2 filter, a step 3 embed, a step 6 RPC — can be replayed under a chosen AuthContext and the result set shrinks the same way the database would have shrunk it.

Setup

Two new files land under codebase/, one already-shipped module grows a small extension, and the test suite gains a dedicated file that pins the role-by-role visibility table. The migration declares which tables turn RLS on and the eleven policies that key off auth.uid(); the new rls.py module models the policy machine and the per-request AuthContext; the schema loader from step 1 grows enough regex to surface enable row level security toggles plus create policy declarations on Schema.policies:

codebase/
├── supabase/migrations/
│   └── 0004_rls_policies.sql       # RLS toggles + 11 policies across 4 tables
├── src/supabase_practice/
│   ├── rls.py                      # Policy, AuthContext, PolicyRegistry, evaluate_select, can_insert
│   └── schema_loader.py            # +Schema.policies, +Schema.rls_enabled, +Schema.policy(...)
└── tests/
    └── test_rls.py                 # 30 assertions across migration parsing + per-role visibility

No new third-party dependency joins the project. The Policy record reuses the same frozen-dataclass discipline RpcCall and PostgrestQuery already follow, so a policy is interchangeable with the other migration-anchored value objects: hashable, comparable, and safe to share across tests. The local evaluate_select and can_insert helpers live next to the registry so a reader can trace one policy from its create policy line, through its Python predicate, to the test assertion that pins its behaviour.

The migration 0004_rls_policies.sql toggles RLS only on tables that hold per-user data: app.authors, app.questions, app.answers, and app.votes. The lookup tables — app.categories, app.tags, app.question_tags — keep RLS off because they hold no caller-specific rows and the anon read path is hot. Leaving the planner out of the policy round-trip on those reads is the right default; flipping RLS on later if a multi-tenant requirement appears is a one-line change.

Implementation

The migration is the right starting point because the eleven policy declarations encode the visibility contract every later layer inherits. The first block enables RLS on the four user-data tables — once that toggle flips, no policy means no rows, which is the secure-by-default posture Postgres has held since 9.5:

alter table app.authors   enable row level security;
alter table app.questions enable row level security;
alter table app.answers   enable row level security;
alter table app.votes     enable row level security;

The next batch attaches a to public select policy to the three resources every visitor is allowed to read. to public is the policy-side wildcard — every role rides it, including anon, authenticated, and any custom role the project later defines. The using (true) predicate says "no row-level filter", so the policy effectively re-opens the table to readers while leaving the per-row write policies in force:

create policy authors_select_all on app.authors
    for select to public
    using (true);

create policy questions_select_all on app.questions
    for select to public
    using (true);

create policy answers_select_all on app.answers
    for select to public
    using (true);

The write policies invert the shape. They are scoped to authenticated callers only, and they key the visibility predicate off auth.uid() so ownership lives in the database rather than the application. questions_update_own carries both a USING and a WITH CHECK clause — USING decides which rows the caller is allowed to target, and WITH CHECK decides what the row is allowed to become after the update lands. Splitting the two clauses lets the policy stop a caller from re-owning a row to someone else:

create policy questions_update_own on app.questions
    for update to authenticated
    using (author_id = auth.uid())
    with check (author_id = auth.uid());

create policy questions_insert_own on app.questions
    for insert to authenticated
    with check (author_id = auth.uid());

create policy questions_delete_own on app.questions
    for delete to authenticated
    using (author_id = auth.uid());

The app.votes table demonstrates the private-resource pattern. Its select policy is not to public; it is scoped to authenticated callers and constrained to rows the JWT owns. The effect is the visibility demo the article needs — replaying a step 2 filter on app.votes under anon returns an empty array, while the same filter under authenticated returns only the rows where author_id = auth.uid():

create policy votes_select_own on app.votes
    for select to authenticated
    using (author_id = auth.uid());

The Python side mirrors the migration with three frozen dataclasses. Policy captures one create policy line — the name, target table, command keyword, role list, and the raw USING / WITH CHECK expression text. AuthContext is the per-request identity that drives the policy machine — role picks the policy bucket, uid answers auth.uid() inside the predicates, and service_role flips the bypasses_rls flag because Supabase grants that role BYPASSRLS at the role attribute level:

@dataclass(frozen=True)
class Policy:
    name: str
    table: str
    command: str
    roles: tuple[str, ...]
    using_expr: str | None = None
    check_expr: str | None = None
    permissive: bool = True

@dataclass(frozen=True)
class AuthContext:
    role: str
    uid: str | None = None

PolicyRegistry pairs each policy with a Python predicate that mirrors its USING clause. The migration uses three predicate shapes — true (the public-read flavour), id = auth.uid() (the own-author-row flavour), and author_id = auth.uid() (the own-owned-row flavour) — so the registry only needs three helpers: _allow_all, _row_id_is_self, and _author_id_is_self. Keeping the predicate count small is the point; each one anchors a clear migration-level invariant rather than re-implementing SQL inside Python.

def _allow_all(_row, _auth):
    return True

def _row_id_is_self(row, auth):
    return auth.uid is not None and row.get("id") == auth.uid

def _author_id_is_self(row, auth):
    return auth.uid is not None and row.get("author_id") == auth.uid

evaluate_select is the local mirror of what Postgres does when a SELECT passes through the planner. service_role short-circuits to the unfiltered list. Otherwise the function collects the select-bearing policies whose role list covers the caller — public covers every role, including unknown ones, which matches the policy-side wildcard semantics — and keeps each row for which at least one permissive predicate returns true. No matching policy means an empty result, which is the only secure default for an RLS-enabled table:

def evaluate_select(rows, table, auth, registry):
    materialised = [dict(row) for row in rows]
    if auth.bypasses_rls:
        return materialised
    relevant = [
        (policy, predicate)
        for policy, predicate in registry.for_table(table)
        if policy.covers_select() and policy.applies_to(auth.role)
    ]
    if not relevant:
        return []
    return [
        row
        for row in materialised
        if any(predicate(row, auth) for _policy, predicate in relevant)
    ]

can_insert is the symmetric helper for writes. The migration's insert policies carry only a WITH CHECK expression — USING does not run on insert because there is no pre-existing row to inspect — so the helper picks the insert-bearing policies, filters to the role list that covers the caller, and accepts the candidate row only if at least one permissive predicate returns true. The same service_role short-circuit keeps background workers and admin tooling moving without weakening the rules that protect interactive callers.

Verification

Run the new test module from inside codebase/. Thirty assertions cover the contract from three angles: the schema loader extracts every enable row level security toggle and every create policy declaration the migration ships; the Policy value object rejects invalid command keywords and exposes the right covers_select / covers_insert / covers_update / covers_delete answers; and evaluate_select / can_insert reproduce, row for row, what Postgres would have returned for each AuthContext over the seeded data.

pytest tests/test_rls.py

============================= test session starts ==============================
platform darwin -- Python 3.12.5, pytest-9.0.3, pluggy-1.6.0
rootdir: /codebase
configfile: pyproject.toml
collected 30 items

tests/test_rls.py ..............................                         [100%]

============================== 30 passed in 0.11s ==============================

The green run pins the visibility table that matters for the article: service_role sees every row in every table; anon rides the to public select policies on authors, questions, and answers but reads zero votes; an authenticated caller still sees every author / question / answer through the to public policies and additionally sees only the votes whose author_id equals their JWT uid. The per-user vote count assertion ({ADA: 3, NOOR: 3, KENJI: 2, MARIA: 1, DEVON: 2}) is the concrete number a reader can match against the seed.

What we built

Policy, AuthContext, and PolicyRegistry are now the value objects that model PostgREST's per-request visibility filter. A test picks a role, optionally pins a uid, and feeds a list of seeded rows through evaluate_select to learn exactly which rows the same role would see through the wire. The same registry answers can_insert for write-side policy checks, so the article can show side-by-side what happens when an authenticated caller tries to insert a question with someone else's author_id.

The migration 0004_rls_policies.sql anchors the contract in real SQL. RLS turns on for the four user-data tables and stays off for the three lookup tables that hold no per-user state. Eleven policies cover the read and write paths: three to public select policies for the resources every visitor can read, a private select on app.votes scoped to the voter, and per-row write policies on every mutating path keyed off author_id = auth.uid() (or id = auth.uid() on the authors table itself).

The local evaluator is the lever that lets the article replay the earlier steps without booting Postgres. Take any PostgrestQuery from step 2, run the seed rows through evaluate_select(rows, table, auth, QA_RLS) first, then apply the filter — that two-line composition is exactly what PostgREST does on the wire, just rearranged so each half can be inspected in isolation. The same composition works for the step 3 embed simulator and the step 6 RPC evaluators because every layer agrees that the rows it receives have already passed the policy filter.

This step locks in the security posture for everything the value-object stack has built so far. The URL grammar from steps 2 through 6 is unchanged; what changes is which rows the same URLs are allowed to see. That separation — same wire grammar, different per-role result set — is the design point Row-Level Security buys: the security boundary lives inside the database, the client code never has to know.

Repository

The state of the code after this step: 8725395

Repository

Full source at https://github.com/vytharion/supabase-postgrest-advanced-queries.

Walk the lessons by stepping through the git commits in the repo — each major step has its own commit you can git checkout and rerun.