PostgREST as a backend replacement

Spent a day exploring whether PostgreSQL + PostgREST can replace a typical backend (Express, Django, Rails) for a blog-like app with auth, moderation, likes, bans, and admin panel. Short answer: it works pretty well. The key trick that makes it practical is schema isolation.

Three schemas, two PostgREST instances

public    → tables, triggers, helpers (never exposed, invisible to any API)
external  → views + functions for public-facing API (port 3000)
internal  → views + functions for admin/intranet API (port 3001)

Each PostgREST instance points at one schema via PGRST_DB_SCHEMAS. Anything in public simply does not exist for the API -- no amount of JWT manipulation can reach public.users or public.sign(). This solves the "accidentally exposed table" problem entirely, which is nice because the alternative (REVOKE ALL on every new function) has a nasty gotcha -- ALTER DEFAULT PRIVILEGES REVOKE ALL ON FUNCTIONS FROM public does not actually work as expected. PostgreSQL falls back to the system default which grants EXECUTE to PUBLIC. Each function needs an explicit REVOKE. With separate schemas this just does not matter.

The two instances have different JWT secrets, so a token from external is cryptographically invalid on internal:

services:
  db:
    image: postgres:17
    environment:
      - POSTGRES_USER=demo
      - POSTGRES_PASSWORD=demo
      - POSTGRES_DB=demo
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD", "pg_isready", "-U", "demo"]
      interval: 2s
      timeout: 2s
      retries: 10

  migrate:
    image: amacneil/dbmate
    environment:
      - DATABASE_URL=postgres://demo:demo@db:5432/demo?sslmode=disable
    volumes:
      - ./db:/db
    command: ["--wait", "--migrations-dir", "/db/migrations", "up"]
    depends_on:
      db:
        condition: service_started

  seed:
    image: postgres:17
    environment:
      - PGPASSWORD=demo
    volumes:
      - ./db/seeds:/seeds
    command: ["psql", "-h", "db", "-U", "demo", "-d", "demo", "-f", "/seeds/dev.sql"]
    depends_on:
      migrate:
        condition: service_completed_successfully

  external:
    image: postgrest/postgrest
    restart: always
    environment:
      - PGRST_DB_URI=postgres://demo:demo@db:5432/demo
      - PGRST_DB_ANON_ROLE=anonymous
      - PGRST_DB_SCHEMAS=external
      - PGRST_OPENAPI_SECURITY_ACTIVE=true
      - PGRST_OPENAPI_SERVER_PROXY_URI=http://localhost:3000
      - |
        PGRST_JWT_SECRET={"keys":[
          {"kty":"oct","alg":"HS256","k":"ZXh0ZXJuYWwtcmVhbGx5LXJlYWxseS12ZXJ5LXNhZmUtc2VjcmV0"},
          {"kty":"oct","alg":"HS256","k":"ZXh0ZXJuYWwtb2xkLXNlY3JldC1zdGlsbC12YWxpZC1mb3Itbm93"}
        ]}
    ports:
      - "3000:3000"
    depends_on:
      seed:
        condition: service_completed_successfully

  internal:
    image: postgrest/postgrest
    restart: always
    environment:
      - PGRST_DB_URI=postgres://demo:demo@db:5432/demo
      - PGRST_DB_ANON_ROLE=anonymous
      - PGRST_DB_SCHEMAS=internal
      - PGRST_OPENAPI_SECURITY_ACTIVE=true
      - PGRST_OPENAPI_SERVER_PROXY_URI=http://localhost:3001
      - |
        PGRST_JWT_SECRET={"keys":[
          {"kty":"oct","alg":"HS256","k":"aW50ZXJuYWwtcmVhbGx5LXJlYWxseS12ZXJ5LXNhZmUtc2VjcmV0"},
          {"kty":"oct","alg":"HS256","k":"aW50ZXJuYWwtb2xkLXNlY3JldC1zdGlsbC12YWxpZC1mb3Itbm93"}
        ]}
    ports:
      - "3001:3000"
    depends_on:
      seed:
        condition: service_completed_successfully

  swagger-external:
    image: swaggerapi/swagger-ui
    restart: always
    environment:
      - API_URL=http://localhost:3000
      - PERSIST_AUTHORIZATION=true
      - TRY_IT_OUT_ENABLED=true
    ports:
      - "8080:8080"
    depends_on:
      external:
        condition: service_started

  swagger-internal:
    image: swaggerapi/swagger-ui
    restart: always
    environment:
      - API_URL=http://localhost:3001
      - PERSIST_AUTHORIZATION=true
      - TRY_IT_OUT_ENABLED=true
    ports:
      - "8081:8080"
    depends_on:
      internal:
        condition: service_started

  tests:
    image: ghcr.io/orange-opensource/hurl:latest
    entrypoint: ["hurl"]
    command:
      - "--test"
      - "--retry"
      - "5"
      - "--retry-interval"
      - "2000"
      - "--variable"
      - "external_url=http://external:3000"
      - "--variable"
      - "internal_url=http://internal:3000"
      - "/tests/external/01-login.hurl"
      - "/tests/external/02-articles.hurl"
      - "/tests/external/03-likes.hurl"
      - "/tests/external/04-permissions.hurl"
      - "/tests/external/05-refresh.hurl"
      - "/tests/internal/01-login.hurl"
      - "/tests/internal/02-articles.hurl"
      - "/tests/internal/03-moderation.hurl"
      - "/tests/internal/04-ban-user.hurl"
      - "/tests/internal/05-scopes.hurl"
      - "/tests/internal/06-impersonate.hurl"
    volumes:
      - ./tests:/tests
    depends_on:
      external:
        condition: service_started
      internal:
        condition: service_started
    profiles:
      - test

Scopes instead of Postgres roles

Only two Postgres roles: anonymous and authenticated. Instead of creating a role per permission level (client, moderator, admin...), permissions live as JWT scopes:

CREATE TABLE public.user_scopes (
  user_id    INTEGER NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  scope      TEXT NOT NULL,
  granted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  granted_by INTEGER REFERENCES public.users(id),
  PRIMARY KEY (user_id, scope)
);

Adding a new permission = inserting a row, no DDL. Scopes are checked in views and triggers via a helper:

CREATE FUNCTION public.has_scope(scope text) RETURNS BOOLEAN AS $$
  SELECT coalesce(
    (current_setting('request.jwt.claims', true)::jsonb->'scopes') ? scope,
    false
  );
$$ LANGUAGE sql STABLE;

Login collects scopes from the table and bakes them into the JWT. Refresh re-reads from db, so scope changes take effect on next refresh without waiting for token expiry.

Views as the API layer

This is where it gets interesting. Same table, different views per audience:

-- external: filtered, safe for public
CREATE VIEW external.articles AS
  SELECT p.id, p.title, u.username AS author,
         p.created_at, p.updated_at, p.views, p.likes_count
  FROM public.posts p
  JOIN public.users u ON u.id = p.created_by
  WHERE NOT p.is_deleted AND NOT u.is_banned AND NOT u.is_deleted
    AND (public.has_scope('moderation:write')    -- 👈 moderators see everything
         OR p.is_approved = true                  -- 👈 public sees only approved
         OR p.created_by = public.current_user_id()); -- 👈 authors see own pending

-- internal: everything, no filters
CREATE VIEW internal.articles AS
  SELECT p.id, p.title, u.username AS author,
         p.is_approved, p.is_deleted, p.views, p.likes_count,
         p.created_at, p.updated_at
  FROM public.posts p
  JOIN public.users u ON u.id = p.created_by;

Writes go through INSTEAD OF triggers with SECURITY DEFINER that check scopes and ownership:

-- migrate:up

CREATE TABLE public.posts (
  id           SERIAL PRIMARY KEY,
  title        TEXT NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by   INTEGER NOT NULL DEFAULT public.current_user_id() REFERENCES public.users(id),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by   INTEGER NOT NULL DEFAULT public.current_user_id() REFERENCES public.users(id),
  is_approved  BOOLEAN NOT NULL DEFAULT false,
  is_deleted   BOOLEAN NOT NULL DEFAULT false,
  views        INTEGER NOT NULL DEFAULT 0,
  likes_count  INTEGER NOT NULL DEFAULT 0
);

-- Auto-set updated_at/updated_by on UPDATE
CREATE FUNCTION public.posts_set_updated() RETURNS TRIGGER AS $$
DECLARE
  _user_id INTEGER := public.current_user_id();
BEGIN
  NEW.updated_at := now();
  IF _user_id IS NOT NULL THEN
    NEW.updated_by := _user_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER posts_set_updated
  BEFORE UPDATE ON public.posts
  FOR EACH ROW EXECUTE FUNCTION public.posts_set_updated();

-- Article moderation history
CREATE TABLE public.article_moderation (
  id           SERIAL PRIMARY KEY,
  article_id   INTEGER NOT NULL REFERENCES public.posts(id) ON DELETE CASCADE,
  comment      TEXT,
  is_approved  BOOLEAN NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  created_by   INTEGER NOT NULL DEFAULT public.current_user_id() REFERENCES public.users(id),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_by   INTEGER NOT NULL DEFAULT public.current_user_id() REFERENCES public.users(id)
);

-- Sync moderation decision to posts.is_approved
CREATE FUNCTION public.sync_article_approval() RETURNS TRIGGER AS $$
BEGIN
  UPDATE public.posts SET is_approved = NEW.is_approved WHERE id = NEW.article_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER sync_approval
  AFTER INSERT OR UPDATE ON public.article_moderation
  FOR EACH ROW EXECUTE FUNCTION public.sync_article_approval();

CREATE FUNCTION public.article_moderation_set_updated() RETURNS TRIGGER AS $$
DECLARE
  _user_id INTEGER := public.current_user_id();
BEGIN
  NEW.updated_at := now();
  IF _user_id IS NOT NULL THEN
    NEW.updated_by := _user_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER article_moderation_set_updated
  BEFORE UPDATE ON public.article_moderation
  FOR EACH ROW EXECUTE FUNCTION public.article_moderation_set_updated();

-- ===========================================
-- External: articles view (filtered by role + scopes)
-- ===========================================

CREATE VIEW external.articles AS
  SELECT p.id, p.title, u.username AS author,
         p.created_at, p.updated_at, p.views, p.likes_count
  FROM public.posts p
  JOIN public.users u ON u.id = p.created_by
  WHERE NOT p.is_deleted AND NOT u.is_banned AND NOT u.is_deleted
    AND (public.has_scope('moderation:write')
         OR p.is_approved = true
         OR p.created_by = public.current_user_id());

CREATE FUNCTION external.articles_insert() RETURNS TRIGGER AS $$
BEGIN
  IF NOT public.has_scope('articles:write') THEN
    RAISE insufficient_privilege USING MESSAGE = 'articles:write scope required';
  END IF;
  INSERT INTO public.posts (title) VALUES (NEW.title)
  RETURNING id, title, created_at, updated_at, views, likes_count
  INTO NEW.id, NEW.title, NEW.created_at, NEW.updated_at, NEW.views, NEW.likes_count;
  SELECT username INTO NEW.author FROM public.users WHERE id = public.current_user_id();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER articles_insert
  INSTEAD OF INSERT ON external.articles
  FOR EACH ROW EXECUTE FUNCTION external.articles_insert();

CREATE FUNCTION external.articles_update() RETURNS TRIGGER AS $$
BEGIN
  IF NOT public.has_scope('articles:write') THEN
    RAISE insufficient_privilege USING MESSAGE = 'articles:write scope required';
  END IF;
  IF NOT EXISTS (
    SELECT 1 FROM public.posts WHERE id = OLD.id
      AND (created_by = public.current_user_id() OR public.has_scope('moderation:write'))
  ) THEN
    RAISE insufficient_privilege USING MESSAGE = 'not the article owner';
  END IF;
  UPDATE public.posts SET title = NEW.title WHERE id = OLD.id;
  SELECT p.id, p.title, u.username, p.created_at, p.updated_at, p.views, p.likes_count
  INTO NEW.id, NEW.title, NEW.author, NEW.created_at, NEW.updated_at, NEW.views, NEW.likes_count
  FROM public.posts p JOIN public.users u ON u.id = p.created_by WHERE p.id = OLD.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER articles_update
  INSTEAD OF UPDATE ON external.articles
  FOR EACH ROW EXECUTE FUNCTION external.articles_update();

-- Soft delete (sets is_deleted=true instead of removing)
CREATE FUNCTION external.articles_delete() RETURNS TRIGGER AS $$
BEGIN
  IF NOT public.has_scope('articles:write') THEN
    RAISE insufficient_privilege USING MESSAGE = 'articles:write scope required';
  END IF;
  IF NOT EXISTS (
    SELECT 1 FROM public.posts WHERE id = OLD.id
      AND (created_by = public.current_user_id() OR public.has_scope('moderation:write'))
  ) THEN
    RAISE insufficient_privilege USING MESSAGE = 'not the article owner';
  END IF;
  UPDATE public.posts SET is_deleted = true WHERE id = OLD.id;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER articles_delete
  INSTEAD OF DELETE ON external.articles
  FOR EACH ROW EXECUTE FUNCTION external.articles_delete();

GRANT SELECT ON external.articles TO anonymous;
GRANT SELECT, INSERT, UPDATE, DELETE ON external.articles TO authenticated;

-- ===========================================
-- Internal: articles view (unfiltered, all flags visible)
-- ===========================================

CREATE VIEW internal.articles AS
  SELECT p.id, p.title, u.username AS author,
         p.is_approved, p.is_deleted, p.views, p.likes_count,
         p.created_at, p.updated_at
  FROM public.posts p
  JOIN public.users u ON u.id = p.created_by;

CREATE FUNCTION internal.articles_insert() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.posts (title) VALUES (NEW.title)
  RETURNING id, title, is_approved, is_deleted, views, likes_count, created_at, updated_at
  INTO NEW.id, NEW.title, NEW.is_approved, NEW.is_deleted, NEW.views, NEW.likes_count, NEW.created_at, NEW.updated_at;
  SELECT username INTO NEW.author FROM public.users WHERE id = public.current_user_id();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER articles_insert
  INSTEAD OF INSERT ON internal.articles
  FOR EACH ROW EXECUTE FUNCTION internal.articles_insert();

CREATE FUNCTION internal.articles_update() RETURNS TRIGGER AS $$
BEGIN
  UPDATE public.posts SET title = COALESCE(NEW.title, OLD.title) WHERE id = OLD.id;
  SELECT p.id, p.title, u.username, p.is_approved, p.is_deleted, p.views, p.likes_count, p.created_at, p.updated_at
  INTO NEW.id, NEW.title, NEW.author, NEW.is_approved, NEW.is_deleted, NEW.views, NEW.likes_count, NEW.created_at, NEW.updated_at
  FROM public.posts p JOIN public.users u ON u.id = p.created_by WHERE p.id = OLD.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER articles_update
  INSTEAD OF UPDATE ON internal.articles
  FOR EACH ROW EXECUTE FUNCTION internal.articles_update();

CREATE FUNCTION internal.articles_delete() RETURNS TRIGGER AS $$
BEGIN
  DELETE FROM public.posts WHERE id = OLD.id;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER articles_delete
  INSTEAD OF DELETE ON internal.articles
  FOR EACH ROW EXECUTE FUNCTION internal.articles_delete();

GRANT SELECT, INSERT, UPDATE, DELETE ON internal.articles TO authenticated;

-- Internal: article moderation view
CREATE VIEW internal.article_moderation AS
  SELECT m.id, m.article_id, m.comment, m.is_approved,
         u.username AS moderator, m.created_at, m.updated_at
  FROM public.article_moderation m
  JOIN public.users u ON u.id = m.created_by
  WHERE public.has_scope('moderation:write');

CREATE FUNCTION internal.article_moderation_insert() RETURNS TRIGGER AS $$
BEGIN
  IF NOT public.has_scope('moderation:write') THEN
    RAISE insufficient_privilege USING MESSAGE = 'moderation:write scope required';
  END IF;
  INSERT INTO public.article_moderation (article_id, comment, is_approved)
  VALUES (NEW.article_id, NEW.comment, NEW.is_approved)
  RETURNING id, article_id, comment, is_approved, created_at, updated_at
  INTO NEW.id, NEW.article_id, NEW.comment, NEW.is_approved, NEW.created_at, NEW.updated_at;
  SELECT username INTO NEW.moderator FROM public.users WHERE id = public.current_user_id();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER article_moderation_insert
  INSTEAD OF INSERT ON internal.article_moderation
  FOR EACH ROW EXECUTE FUNCTION internal.article_moderation_insert();

GRANT SELECT, INSERT ON internal.article_moderation TO authenticated;

-- migrate:down

DROP VIEW IF EXISTS internal.article_moderation CASCADE;
DROP VIEW IF EXISTS internal.articles CASCADE;
DROP VIEW IF EXISTS external.articles CASCADE;
DROP TABLE IF EXISTS public.article_moderation;
DROP TABLE IF EXISTS public.posts;

RPC for multi-table operations

Where views are not enough -- stored functions. like_article does insert + counter increment atomically, ban_user sets a flag and recalculates likes_count only for affected articles:

-- migrate:up

CREATE TABLE public.article_likes (
  article_id  INTEGER NOT NULL REFERENCES public.posts(id) ON DELETE CASCADE,
  user_id     INTEGER NOT NULL REFERENCES public.users(id),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (article_id, user_id)
);

-- Recalculate likes_count for all articles liked by a specific user.
-- Called by ban/unban RPCs to keep counter accurate without full table scan.
CREATE FUNCTION public.recalculate_user_likes(p_user_id INTEGER) RETURNS void AS $$
BEGIN
  UPDATE public.posts p SET likes_count = (
    SELECT count(*) FROM public.article_likes al
    JOIN public.users u ON u.id = al.user_id
    WHERE al.article_id = p.id AND NOT u.is_banned AND NOT u.is_deleted
  ) WHERE p.id IN (
    SELECT article_id FROM public.article_likes WHERE user_id = p_user_id
  );
END;
$$ LANGUAGE plpgsql;

-- Like article: insert log + increment counter atomically
CREATE FUNCTION external.like_article(article_id INTEGER) RETURNS INTEGER AS $$
DECLARE
  _user_id INTEGER := public.current_user_id();
  _count   INTEGER;
BEGIN
  IF _user_id IS NULL THEN
    RAISE insufficient_privilege USING MESSAGE = 'authentication required';
  END IF;
  IF NOT EXISTS (
    SELECT 1 FROM public.posts p JOIN public.users u ON u.id = p.created_by
    WHERE p.id = like_article.article_id
      AND NOT p.is_deleted AND NOT u.is_banned AND NOT u.is_deleted
      AND (p.is_approved OR p.created_by = _user_id)
  ) THEN
    RAISE invalid_parameter_value USING MESSAGE = 'article not found';
  END IF;
  INSERT INTO public.article_likes (article_id, user_id)
  VALUES (like_article.article_id, _user_id);
  UPDATE public.posts SET likes_count = likes_count + 1
  WHERE id = like_article.article_id RETURNING likes_count INTO _count;
  RETURN _count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Unlike article: delete log + decrement counter atomically
CREATE FUNCTION external.unlike_article(article_id INTEGER) RETURNS INTEGER AS $$
DECLARE
  _user_id INTEGER := public.current_user_id();
  _count   INTEGER;
BEGIN
  IF _user_id IS NULL THEN
    RAISE insufficient_privilege USING MESSAGE = 'authentication required';
  END IF;
  DELETE FROM public.article_likes
  WHERE article_likes.article_id = unlike_article.article_id AND user_id = _user_id;
  IF FOUND THEN
    UPDATE public.posts SET likes_count = likes_count - 1
    WHERE id = unlike_article.article_id RETURNING likes_count INTO _count;
  ELSE
    SELECT likes_count INTO _count FROM public.posts WHERE id = unlike_article.article_id;
  END IF;
  RETURN _count;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION external.like_article(INTEGER) TO authenticated;
GRANT EXECUTE ON FUNCTION external.unlike_article(INTEGER) TO authenticated;

-- Internal: likes log (read-only, shows ban status)
CREATE VIEW internal.article_likes AS
  SELECT al.article_id, u.username, u.is_banned, al.created_at
  FROM public.article_likes al
  JOIN public.users u ON u.id = al.user_id;

GRANT SELECT ON internal.article_likes TO authenticated;

-- migrate:down

DROP VIEW IF EXISTS internal.article_likes;
DROP FUNCTION IF EXISTS public.recalculate_user_likes(INTEGER);
DROP FUNCTION IF EXISTS external.unlike_article(INTEGER);
DROP FUNCTION IF EXISTS external.like_article(INTEGER);
DROP TABLE IF EXISTS public.article_likes;

Ban is reversible -- is_banned is just a filter in the view. Unban flips the flag, recalculates likes, everything reappears.

JWT signing with pgjwt

PostgREST only validates tokens, it does not sign them. The stock postgres image does not include pgjwt as an extension, so you copy the SQL functions. Login is a stored function in each schema that signs with its own secret:

CREATE FUNCTION public.build_jwt(p_user public.users, p_secret_setting text) RETURNS TEXT AS $$
DECLARE _scopes JSONB;
BEGIN
  SELECT coalesce(jsonb_agg(scope), '[]'::jsonb) INTO _scopes
  FROM public.user_scopes WHERE user_id = p_user.id;

  RETURN public.sign(json_build_object(
    'role', 'authenticated', 'user_id', p_user.id,
    'username', p_user.username, 'scopes', _scopes,
    'exp', extract(epoch FROM now())::integer + 3600
  ), current_setting(p_secret_setting));  -- 👈 reads from ALTER DATABASE ... SET
END;
$$ LANGUAGE plpgsql STABLE;

Key rotation works via JWKS -- PGRST_JWT_SECRET accepts {"keys":[...]} with multiple keys. PostgREST tries all keys when JWT has no kid claim. Add new key, update signing secret, remove old after tokens expire.

Testing with hurl

Plain text .hurl files, run in Docker. 11 files, 52 requests, 55ms:

# Anonymous: sees only approved articles
GET {{external_url}}/articles
HTTP 200
[Asserts]
jsonpath "$" count == 2

# Login, capture token
POST {{external_url}}/rpc/login
Content-Type: application/json
{"email": "alice@example.com", "pass": "alice123"}
HTTP 200
[Captures]
alice_token: jsonpath "$"

# Alice creates article
POST {{external_url}}/articles
Authorization: Bearer {{alice_token}}
Content-Type: application/json
Prefer: return=representation
{"title": "Test article"}
HTTP 201
[Asserts]
jsonpath "$[0].author" == "alice"

docker compose --profile test run --rm tests

Migrations with dbmate

docker run --rm -v ./db:/db amacneil/dbmate new add_comments  # create migration
docker compose down && docker compose up -d                     # apply (ephemeral dev)
dbmate -u $PROD_DB_URL up                                       # apply to production

Compose chain: db → dbmate migrate → seed → PostgREST → swagger. Seeds are idempotent, not applied in production.

Notes:

PostgREST does SET LOCAL ROLE per request, so views execute as the switched role, not the view owner. Helper functions must be executable by the role, not just the owner
GET requests in PostgREST = read-only transactions. Cannot do side effects (view counter increment) during reads. Only through RPC (POST)
Column-level SELECT grants technically work in PostgreSQL, but PostgREST does SELECT * on bare GET which breaks everything if any column is restricted. Use views to control visible columns instead
PGRST_OPENAPI_SERVER_PROXY_URI=http://localhost:3000 -- without this Swagger UI tries to hit 0.0.0.0:3000 and the browser blocks it
PGRST_OPENAPI_SECURITY_ACTIVE=true -- enables the Authorize button in Swagger
PostgREST container has no curl, wget, nc, or bash -- healthchecks with these tools are impossible. Use hurl's --retry for test startup ordering
DELETE through a view with a WHERE clause silently returns 204 when the row is filtered out (ownership check). Need explicit IF NOT EXISTS check + RAISE in the INSTEAD OF trigger if you want a proper error
Impersonate function signs with the external secret intentionally -- the returned token only works on the public API, preventing privilege escalation to the admin API