Multi-Tenant Blog Platform (SaaS)

A complete example of a production-ready, multi-tenant blogging platform using FraiseQL.

Repository: github.com/fraiseql/examples/saas-blog

Features Demonstrated

Multi-Tenancy: Tenant isolation with row-level security (RLS)
Authentication & Authorization: JWT-based auth with role-based access control
Real-Time Updates: WebSocket subscriptions for live blog updates
Soft Deletes: Logical deletion of posts with recovery
Audit Trail: Track all changes to posts and comments
Pagination: Cursor-based pagination for large datasets
Search: Full-text search across blog posts
Caching: Redis caching for popular posts
Federation: Support multiple databases for scale

CREATE TABLE tb_tenant (
    pk_tenant  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id         UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier TEXT NOT NULL UNIQUE,
    name       TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now(),
    subscription_tier TEXT NOT NULL DEFAULT 'free'
);

CREATE TABLE tb_user (
    pk_user    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id         UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier TEXT NOT NULL UNIQUE,
    fk_tenant BIGINT NOT NULL REFERENCES tb_tenant(pk_tenant),
    email      TEXT UNIQUE NOT NULL,
    name       TEXT NOT NULL,
    role       TEXT NOT NULL DEFAULT 'subscriber',  -- admin, editor, subscriber
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE tb_post (
    pk_post    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id         UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier TEXT NOT NULL UNIQUE,
    fk_tenant BIGINT NOT NULL REFERENCES tb_tenant(pk_tenant),
    fk_user BIGINT NOT NULL REFERENCES tb_user(pk_user),
    title      TEXT NOT NULL,
    content    TEXT NOT NULL DEFAULT '',
    published  BOOLEAN NOT NULL DEFAULT FALSE,
    deleted_at TIMESTAMPTZ,  -- Soft delete
    created_at TIMESTAMPTZ DEFAULT now(),
    updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE tb_comment (
    pk_comment BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id         UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier TEXT NOT NULL UNIQUE,
    fk_tenant BIGINT NOT NULL REFERENCES tb_tenant(pk_tenant),
    fk_post BIGINT NOT NULL REFERENCES tb_post(pk_post),
    fk_user BIGINT NOT NULL REFERENCES tb_user(pk_user),
    content    TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);

-- Views with JSONB data column
--
-- Note: v_post and v_comment expose extra columns (identifier, fk_tenant, fk_post,
-- deleted_at) beyond the standard (id, data) pair. The extra columns are used for
-- server-side RLS filtering and view composition — they are not returned to GraphQL
-- clients. In a strict two-column view setup these extra columns would be moved to
-- an inner subquery; the pattern here is a simplified example.
CREATE VIEW v_post AS
SELECT
    p.id,
    p.identifier,
    jsonb_build_object(
        'id',         p.id::text,
        'identifier', p.identifier,
        'title',      p.title,
        'content',    p.content,
        'published',  p.published,
        'deleted_at', p.deleted_at,
        'created_at', p.created_at,
        'updated_at', p.updated_at,
        'author',     jsonb_build_object(
                          'id',    u.id::text,
                          'name',  u.name,
                          'email', u.email
                      )
    ) AS data,
    p.fk_tenant,
    p.deleted_at
FROM tb_post p
JOIN tb_user u ON u.pk_user = p.fk_user
WHERE p.deleted_at IS NULL;

CREATE VIEW v_comment AS
SELECT
    c.id,
    c.identifier,
    jsonb_build_object(
        'id',         c.id::text,
        'content',    c.content,
        'created_at', c.created_at,
        'author',     jsonb_build_object(
                          'id',   u.id::text,
                          'name', u.name
                      )
    ) AS data,
    c.fk_post
FROM tb_comment c
JOIN tb_user u ON u.pk_user = c.fk_user;

import fraiseql
from fraiseql.scalars import ID
from datetime import datetime
from typing import Annotated

@fraiseql.type
class Tenant:
    id: ID
    name: str
    created_at: datetime
    subscription_tier: str  # free, pro, enterprise

@fraiseql.type
class User:
    id: ID
    email: str
    name: str
    role: str  # admin, editor, subscriber
    created_at: datetime

@fraiseql.type
class Post:
    id: ID
    title: str
    content: str
    published: bool
    deleted_at: datetime | None  # Soft delete
    created_at: datetime
    updated_at: datetime

@fraiseql.type
class Comment:
    id: ID
    content: str
    created_at: datetime


@fraiseql.query
def posts(first: int = 20, after: str | None = None) -> list[Post]:
    """Get tenant's published posts (filtered by RLS)."""
    return fraiseql.config(sql_source="v_post")

@fraiseql.query
def comments(post_id: ID, first: int = 20) -> list[Comment]:
    """Get comments for a post."""
    return fraiseql.config(sql_source="v_comment")

Key Queries

# Get tenant's published posts (with pagination)
query {
  posts(first: 20, after: "cursor123") {
    edges {
      node {
        id
        title
        author { name }
        comments(first: 5) { edges { node { content author { name } } } }
      }
    }
    pageInfo { hasNextPage endCursor }
  }
}

# Real-time subscription for new comments
subscription {
  commentCreated(postId: "post123") {
    id
    content
    author { name }
    createdAt
  }
}

# Search posts
query {
  searchPosts(query: "GraphQL", first: 10) {
    id
    title
    highlight
  }
}

Key Mutations

# Create post
mutation {
  createPost(
    title: "Getting Started with FraiseQL"
    content: "..."
  ) {
    id
    title
    author { name }
  }
}

# Delete post (soft delete)
mutation {
  deletePost(id: "post123") {
    id
    deletedAt
  }
}

# Restore deleted post
mutation {
  restorePost(id: "post123") {
    id
    deletedAt
  }
}

# Add comment
mutation {
  createComment(postId: "post123", content: "Great post!") {
    id
    content
    author { name }
  }
}

Authentication & Authorization

FraiseQL mutations are compile-time declarations backed by PostgreSQL functions. Role enforcement lives in the SQL function, and the current user’s identity is injected from the JWT via the inject parameter — no runtime Python needed.

# JWT token includes tenant and role
# {
#   "sub": "user-uuid-here",
#   "tenant_id": "tenant-uuid-here",
#   "role": "editor",
#   "scopes": ["read:posts", "write:posts", "read:comments"]
# }

# RLS automatically filters to the current tenant's rows.
# The SET ROLE and SET app.tenant_id are applied by the FraiseQL
# runtime before executing any query, driven by the compiled schema.
@fraiseql.query
def posts(first: int = 20, after: str | None = None) -> list[Post]:
    """Automatically filters to current tenant via RLS."""
    return fraiseql.config(sql_source="v_post")

# The SQL function fn_create_post checks the caller's role.
# The user_id is injected from the JWT claim 'sub' — not passed by the client.
@fraiseql.mutation(
    sql_source="fn_create_post",
    operation="CREATE",
    inject={"user_id": "jwt:sub"}
)
def create_post(title: str, content: str) -> Post:
    """Only editors can create posts. Role check enforced in fn_create_post."""
    pass

The corresponding SQL function enforces the role constraint:

CREATE FUNCTION fn_create_post(
    p_user_id UUID,
    p_title   TEXT,
    p_content TEXT
) RETURNS mutation_response AS $$
DECLARE
    v_user_role TEXT;
    v_fk_user   BIGINT;
    v_fk_tenant BIGINT;
BEGIN
    SELECT role, pk_user, fk_tenant
      INTO v_user_role, v_fk_user, v_fk_tenant
      FROM tb_user WHERE id = p_user_id;

    IF v_user_role NOT IN ('admin', 'editor') THEN
        RETURN ROW('failed:authorization', 'Only editors can create posts',
                   NULL, 'Post', NULL, NULL, NULL, NULL)::mutation_response;
    END IF;

    INSERT INTO tb_post (fk_tenant, fk_user, title, content, identifier)
    VALUES (
        v_fk_tenant,
        v_fk_user,
        p_title,
        p_content,
        lower(regexp_replace(p_title, '[^a-zA-Z0-9]+', '-', 'g'))
    );

    RETURN ROW('success', NULL, NULL, 'Post', NULL, NULL, NULL, NULL)::mutation_response;
END;
$$ LANGUAGE plpgsql;

Multi-Tenancy Implementation

Row-Level Security (PostgreSQL)

-- Enable RLS on all tenant-scoped tables
ALTER TABLE tb_post ENABLE ROW LEVEL SECURITY;
ALTER TABLE tb_comment ENABLE ROW LEVEL SECURITY;
ALTER TABLE tb_user ENABLE ROW LEVEL SECURITY;

-- Create policy for post visibility (tenant isolation)
CREATE POLICY posts_tenant_isolation ON tb_post
USING (fk_tenant = (
    SELECT pk_tenant FROM tb_tenant
    WHERE id = current_setting('app.tenant_id')::uuid
));

-- Create policy for soft-deleted posts
CREATE POLICY posts_exclude_deleted ON tb_post
USING (deleted_at IS NULL);

-- Set tenant context from request
-- FraiseQL sets this automatically from the JWT before each query:
-- SET app.tenant_id = '<tenant_id from JWT>';

Soft Deletes & Recovery

The soft-delete pattern is implemented entirely in SQL functions. FraiseQL mutations are compile-time declarations that map to these functions.

@fraiseql.query
def post(id: ID) -> Post | None:
    """Get single post (v_post already excludes soft-deleted)."""
    return fraiseql.config(sql_source="v_post")

@fraiseql.mutation(
    sql_source="fn_delete_post",
    operation="DELETE",
    inject={"user_id": "jwt:sub"}
)
def delete_post(id: ID) -> Post:
    """Soft-delete a post (sets deleted_at, does not remove row)."""
    pass

@fraiseql.mutation(
    sql_source="fn_restore_post",
    operation="UPDATE",
    inject={"user_id": "jwt:sub"}
)
def restore_post(id: ID) -> Post:
    """Restore a soft-deleted post."""
    pass

The SQL functions handle the actual data operations:

CREATE FUNCTION fn_delete_post(p_user_id UUID, p_id UUID)
RETURNS mutation_response AS $$
BEGIN
    UPDATE tb_post SET deleted_at = NOW()
    WHERE id = p_id
      AND fk_user = (SELECT pk_user FROM tb_user WHERE id = p_user_id);

    RETURN ROW('success', NULL, p_id, 'Post', NULL, NULL, NULL, NULL)::mutation_response;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION fn_restore_post(p_user_id UUID, p_id UUID)
RETURNS mutation_response AS $$
BEGIN
    UPDATE tb_post SET deleted_at = NULL
    WHERE id = p_id
      AND fk_user = (SELECT pk_user FROM tb_user WHERE id = p_user_id);

    RETURN ROW('success', NULL, p_id, 'Post', NULL, NULL, NULL, NULL)::mutation_response;
END;
$$ LANGUAGE plpgsql;

Audit Trail

Audit logging is implemented as a PostgreSQL trigger on the base tables. No application-layer middleware is needed.

@fraiseql.type
class AuditLog:
    id: ID
    entity_type: str  # "post", "comment"
    entity_id: ID
    action: str  # "create", "update", "delete"
    created_at: datetime

-- Audit trigger fires automatically on tb_post mutations
CREATE TABLE tb_audit_log (
    pk_audit_log BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id           UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier   TEXT NOT NULL UNIQUE
                     GENERATED ALWAYS AS (id::text) STORED,
    entity_type  TEXT NOT NULL,
    entity_id    UUID NOT NULL,
    action       TEXT NOT NULL,
    changes      JSONB,
    created_at   TIMESTAMPTZ DEFAULT now()
);

CREATE FUNCTION fn_audit_post_changes() RETURNS trigger AS $$
BEGIN
    INSERT INTO tb_audit_log (entity_type, entity_id, action, changes)
    VALUES (
        'post',
        COALESCE(NEW.id, OLD.id),
        TG_OP,
        jsonb_build_object('old', to_jsonb(OLD), 'new', to_jsonb(NEW))
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_post
AFTER INSERT OR UPDATE OR DELETE ON tb_post
FOR EACH ROW EXECUTE FUNCTION fn_audit_post_changes();

REST Annotations

Add rest_path and rest_method to expose blog operations as REST endpoints:

@fraiseql.query(rest_path="/posts", rest_method="GET")
def posts(limit: int = 10, offset: int = 0) -> list[Post]:
    return fraiseql.config(sql_source="v_post")

@fraiseql.query(rest_path="/posts/{id}", rest_method="GET")
def post(id: UUID) -> Post:
    return fraiseql.config(sql_source="v_post")

# REST clients (default path: /rest/v1; configure via [rest] path)
curl https://api.example.com/rest/v1/posts?limit=10
curl https://api.example.com/rest/v1/posts/abc-123

Caching Strategy

@fraiseql.query
def popular_posts(first: int = 10) -> list[Post]:
    """Cache popular posts to reduce database load."""
    return fraiseql.config(sql_source="v_post", cache_ttl_seconds=3600)  # Cache for 1 hour

@fraiseql.mutation(
    sql_source="fn_create_post",
    operation="CREATE",
    inject={"user_id": "jwt:sub"},
    invalidates_views=["v_post"]
)
def create_post(title: str, content: str) -> Post:
    """Create post and automatically invalidate the v_post cache."""
    pass

Enable caching in your configuration:

[caching]
enabled = true
backend = "redis"

Getting Started

Clone the example

git clone https://github.com/fraiseql/examples/saas-blog
cd saas-blog

Set up environment
Terminal window
```
cp .env.example .env
uv sync
```
Start database
Terminal window
```
docker-compose up -d postgres redis
```
Run migrations
Terminal window
```
fraiseql migrate
```
Start FraiseQL server
Terminal window
```
fraiseql run
```
Visit GraphQL playground
Terminal window
```
open http://localhost:8080/graphql
```

Test the API

Create a tenant:

mutation {
  createTenant(name: "Demo Blog", identifier: "demo") {
    id
    name
  }
}

Create a user:

mutation {
  createUser(
    tenantId: "demo"
    email: "admin@example.com"
    name: "Admin User"
    role: "admin"
  ) {
    id
    email
    role
  }
}

Create a post:

mutation {
  createPost(
    tenantId: "demo"
    title: "Hello World"
    content: "My first blog post with FraiseQL!"
    published: true
  ) {
    id
    title
    published
    author {
      name
    }
  }
}

Verify tenant isolation

Query posts from the first tenant:

query {
  posts(tenantId: "demo", limit: 10) {
    id
    title
  }
}

Create a second tenant and verify isolation:

mutation {
  createTenant(name: "Another Blog", identifier: "another") {
    id
  }
}

Query posts from second tenant (should be empty):

query {
  posts(tenantId: "another", limit: 10) {
    id
    title
  }
}

Run tests

pytest tests/ -v

Expected output:

tests/test_tenancy.py::test_tenant_isolation PASSED
tests/test_posts.py::test_create_post PASSED
tests/test_posts.py::test_soft_delete PASSED
tests/test_auth.py::test_role_based_access PASSED
tests/test_subscriptions.py::test_live_updates PASSED

Troubleshooting

Database Connection Error

Error: Cannot connect to database

Solutions:

Start PostgreSQL: docker-compose up -d postgres
Verify connection: pg_isready -h localhost -p 5432
Check DATABASE_URL in .env file

RLS Blocking Queries

If you get permission errors:

Ensure RLS policy is created:

CREATE POLICY tenant_isolation ON tb_post
USING (fk_tenant = current_setting('app.tenant_id')::int);

Verify tenant_id is being set in request context

Schema Compilation Errors

fraiseql compile
Error: Unknown type 'Post'

Check:

All imports are correct in schema.py
Forward references use string literals: 'Post'
Run: fraiseql validate

Subscription Not Working

If live updates don’t appear:

Check WebSocket endpoint: wscat -c ws://localhost:8080/ws
Verify subscription is defined in schema
Check browser console for WebSocket errors

Performance Issues

If queries are slow:

Add indexes:

CREATE INDEX idx_post_tenant_created ON tb_post(fk_tenant, created_at DESC);
CREATE INDEX idx_post_published ON tb_post(published) WHERE published = true;

Enable caching in fraiseql.toml (note: [caching] is unverified — it may cause a parse error; remove if startup fails):
```
[caching]
enabled = true
backend = "redis"
```

Learning Path

Basic: Create/read/delete posts for single tenant
Multi-Tenancy: Add row-level security for tenant isolation
Real-Time: Add subscriptions for live updates
Advanced: Add soft deletes, audit trail, and caching
Production: Deploy to Kubernetes with monitoring

Multi-Tenancy Guide

Row-level security and tenant isolation patterns.

Advanced Patterns

Soft deletes, audit logs, and pagination.

Deployment

Kubernetes deployment with monitoring.

Federation

Scale to multiple databases.