Pattern: dynamic configuration with JSONB

Capsule description

Almost every SaaS has the same need: store configuration per entity — per-tenant settings, per-user preferences, per-organization feature flags, a job's parameters. There are three ways to model it in PostgreSQL: a table with many typed columns, a key/value table with one row per pair, or a JSONB column. Each one wins in different scenarios.

This capsule teaches you when JSONB is the right tool for dynamic configuration, when it's an anti-pattern in disguise, and how to implement it well with SQLAlchemy and Pydantic. You'll build a per-tenant settings system with feature flags, per-user customization, and validation, all with the code you already know from capsules 02-04.

By the end you'll have a clear decision matrix: faced with a ticket saying "we need to store configuration X," you'll know in 30 seconds whether it goes in JSONB, in columns, in a key/value table, or somewhere else entirely (Redis, a config file, env vars).

Mental model: three options, three trade-offs

┌─────────────────────────────────────────────────────────────────────┐
│                                                                     │
│  Option A: TYPED COLUMNS                                            │
│  CREATE TABLE tenants (                                             │
│    id ..., name ...,                                                │
│    feature_x BOOLEAN, feature_y BOOLEAN,                            │
│    max_users INT, theme VARCHAR(50), ...                            │
│  )                                                                  │
│  ✅ Native type validation      ❌ A migration per field            │
│  ✅ Simple indexes              ❌ Rigid schema                     │
│  ✅ NOT NULL constraints        ❌ Empty cells for optional fields  │
│                                                                     │
│  ─────────────────────────────────────────────────────────────────  │
│                                                                     │
│  Option B: KEY/VALUE TABLE                                          │
│  CREATE TABLE tenant_settings (                                     │
│    tenant_id BIGINT, key VARCHAR, value TEXT                        │
│  )                                                                  │
│  ✅ Flexible schema             ❌ Type safety via the app          │
│  ✅ No migrations per feature   ❌ Verbose queries (joins)          │
│  ✅ Auditable per entry         ❌ No structure per entity          │
│                                                                     │
│  ─────────────────────────────────────────────────────────────────  │
│                                                                     │
│  Option C: JSONB COLUMN                                             │
│  CREATE TABLE tenants (                                             │
│    id ..., name ..., settings JSONB                                 │
│  )                                                                  │
│  ✅ Flexible schema             ❌ Validation via Pydantic          │
│  ✅ No migrations per feature   ❌ Partial updates need             │
│  ✅ Native nested structure        func.jsonb_set                   │
│  ✅ Fast @> queries (GIN)       ❌ No DB-level constraints          │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘

When each one wins:

• Typed columns: when the fields are stable (they don't change every sprint), few (no more than 15-20), and you need strong constraints (NOT NULL, FK, CHECK). Example: a user's email, created_at, is_active.

• Key/value table: when you need granular auditing per entry (who changed which setting, when) or when the settings are completely arbitrary. Example: administrative configurations added ad-hoc.

• JSONB: when the fields change frequently without warning, there's nested structure (features.beta.x), and you accept validating at the application level (Pydantic) instead of the DB. Example: per-tenant feature flags, UI customization, user preferences, job parameters.

JSONB doesn't replace the other two. It's the tool for the specific case of "I need flexibility without migrations."

The anchor case: per-tenant settings in a SaaS

You're going to build the settings system for a multi-tenant SaaS. Each tenant has:

• Stable information (id, name, plan): typed columns.
• Dynamic settings (feature flags, UI customization, integrations, branding): JSONB.

Schema:

# models.py
from typing import Any, Literal

from sqlalchemy import BigInteger, Index
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Tenant(Base):
    __tablename__ = "tenants"

    # Stable information (typed columns)
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    name: Mapped[str] = mapped_column(unique=True)
    plan: Mapped[Literal["free", "pro", "enterprise"]] = mapped_column(default="free")

    # Dynamic configuration (JSONB)
    settings: Mapped[dict[str, Any]] = mapped_column(
        MutableDict.as_mutable(JSONB),
        nullable=False,
        default=dict,
    )

    __table_args__ = (
        # GIN on settings for containment queries (module 1, capsule 05)
        Index("ix_tenants_settings_gin", "settings", postgresql_using="gin"),
    )

Why this split:

• name and plan are stable: a tenant's name rarely changes and plan has 3 known values. Typed columns.
• settings is dynamic: the product team adds flags every sprint. Nobody wants to open a migration PR every time. JSONB.
• The GIN on settings is what you learned in module 1: @> queries get faster. The jsonb_ops vs jsonb_path_ops distinction applies too — for feature flags, where you mostly filter with settings @> {"feature_x": true}, jsonb_path_ops is more efficient (revisit module 1, capsule 05, if you're unsure).

The Pydantic settings schema

# pydantic_settings.py
from pydantic import BaseModel, Field, HttpUrl


class FeatureFlags(BaseModel):
    new_dashboard: bool = False
    ai_suggestions: bool = False
    advanced_analytics: bool = False
    beta_export: bool = False


class BrandingSettings(BaseModel):
    primary_color: str = Field(default="#0066ff", pattern=r"^#[0-9a-fA-F]{6}$")
    logo_url: HttpUrl | None = None
    company_name: str | None = Field(default=None, max_length=100)


class IntegrationSettings(BaseModel):
    slack_webhook: HttpUrl | None = None
    github_org: str | None = Field(default=None, max_length=100)
    sentry_dsn: HttpUrl | None = None


class TenantSettings(BaseModel):
    features: FeatureFlags = Field(default_factory=FeatureFlags)
    branding: BrandingSettings = Field(default_factory=BrandingSettings)
    integrations: IntegrationSettings = Field(default_factory=IntegrationSettings)

    # Allow extra keys for extensibility (experimental flags)
    model_config = {"extra": "allow"}

Schema decisions:

• Sub-models per category (features, branding, integrations) instead of a flat dict. It gives you logical grouping and better OpenAPI documentation.
• Explicit defaults on every flag (new_dashboard: bool = False). If a new tenant doesn't set features, every flag is False. Safe by default.
• extra="allow" so the team can add experimental flags without touching the schema. If experiment_x shows up in production tomorrow, it doesn't break the endpoint.

Typical operations

1. Read whether a tenant has a feature enabled

from sqlalchemy import select


async def tenant_has_feature(session, tenant_id: int, feature: str) -> bool:
    """
    Containment filter. Takes advantage of the GIN.
    Generates: WHERE id = ? AND settings @> '{"features": {"feature": true}}'
    """
    stmt = select(Tenant.id).where(
        Tenant.id == tenant_id,
        Tenant.settings.contains({"features": {feature: True}}),
    )
    result = await session.execute(stmt)
    return result.scalar_one_or_none() is not None

Generated SQL:

SELECT tenants.id
FROM tenants
WHERE tenants.id = $1
  AND tenants.settings @> '{"features": {"new_dashboard": true}}';

The GIN is used on the @>. If you have 10k tenants, this is O(log n) instead of O(n).

2. List every tenant with a feature enabled

async def list_tenants_with_feature(session, feature: str) -> list[Tenant]:
    """
    Typical case: the product team asks 'who has flag X enabled?'.
    """
    stmt = select(Tenant).where(
        Tenant.settings.contains({"features": {feature: True}})
    )
    result = await session.execute(stmt)
    return list(result.scalars().all())

Same @>, same GIN. The query over 50k tenants returns the enabled subset in milliseconds.

3. Enable a feature flag for a tenant

import json

from sqlalchemy import update, cast
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy import func


async def enable_feature(session, tenant_id: int, feature: str) -> None:
    """
    Atomic update on the path. Doesn't load the whole tenant.
    If features doesn't exist, it creates it as {}; then it sets the flag.
    """
    stmt = (
        update(Tenant)
        .where(Tenant.id == tenant_id)
        .values(
            settings=func.jsonb_set(
                func.jsonb_set(
                    Tenant.settings,
                    "{features}",
                    cast("{}", JSONB),
                    True,  # create_missing
                ),
                "{features," + feature + "}",
                cast("true", JSONB),
                True,
            )
        )
    )
    await session.execute(stmt)
    await session.commit()

For high concurrency (many admins enabling flags simultaneously on the same tenant), func.jsonb_set is the right option: no race condition, no rewriting the whole column.

For low frequency and better readability, MutableDict with a merge in Python:

async def enable_feature_simple(session, tenant_id: int, feature: str) -> None:
    tenant = (
        await session.execute(select(Tenant).where(Tenant.id == tenant_id))
    ).scalar_one()
    features = tenant.settings.get("features", {})
    tenant.settings["features"] = {**features, feature: True}  # root reassignment
    await session.commit()

4. Enable a feature for every tenant on a plan

async def enable_feature_for_plan(session, plan: str, feature: str) -> int:
    """
    Bulk update without loading objects. PostgreSQL does the merge on each row.
    """
    stmt = (
        update(Tenant)
        .where(Tenant.plan == plan)
        .values(
            settings=func.jsonb_set(
                func.jsonb_set(
                    Tenant.settings, "{features}", cast("{}", JSONB), True
                ),
                "{features," + feature + "}",
                cast("true", JSONB),
                True,
            )
        )
    )
    result = await session.execute(stmt)
    await session.commit()
    return result.rowcount

update().values(...) with no prior SELECT is the scalable way to do bulk. If you have 100k pro tenants and want to enable ai_suggestions for them, this is a single query that PostgreSQL runs over every matching row.

5. Return validated settings to the frontend

from fastapi import FastAPI, HTTPException


app = FastAPI()


@app.get("/tenants/{tenant_id}/settings", response_model=TenantSettings)
async def get_tenant_settings(tenant_id: int) -> TenantSettings:
    async with SessionLocal() as session:
        tenant = (
            await session.execute(select(Tenant).where(Tenant.id == tenant_id))
        ).scalar_one_or_none()
        if tenant is None:
            raise HTTPException(404)
        # Validate before returning — if the database has old data
        # missing some keys, the schema's defaults fill them in.
        return TenantSettings.model_validate(tenant.settings)

model_validate applies the defaults: if tenant.settings has no features, TenantSettings builds it with FeatureFlags() (every flag False). The client always receives the full schema.

Decision matrix: JSONB or not?

Here's the decision tree for when you face a ticket saying "we need to store configuration X":

                    How many fields? Do they change often?
                                   │
        ┌──────────────────────────┼──────────────────────────────┐
        │                          │                              │
   < 10 fields              10-30 fields                    > 30 fields
   STABLE                   with nesting                  or deeply nested
        │                          │                              │
        ▼                          ▼                              ▼
   ┌─────────┐               Do you need              JSONB (always)
   │ TYPED   │               DB-level                   + Pydantic
   │ COLUMNS │               constraints?                + GIN
   └─────────┘                     │
                       ┌───────────┼────────────┐
                       │                        │
                    YES (NOT NULL,          NO (validation
                    FK, UNIQUE)              via the app)
                       │                        │
                       ▼                        ▼
                    TYPED                     JSONB
                   COLUMNS                 + Pydantic
                                             + GIN

Cases where JSONB loses:

• The field shows up in queries with complex aggregations. If you need SUM(settings.amount) WHERE settings.category = 'X' over millions of rows, an amount NUMERIC column is faster and the indexes are more efficient.

• The field needs an FK. You can't have tenant.settings.parent_tenant_id with a FOREIGN KEY pointing at another table. That has to be a column.

• The field is searched with full-text. Searching free text inside JSONB is possible but limited (we'll see it in module 3 with FTS). If your primary use is textual search, a column + GIN-FTS is better.

• You need granular auditing ("who changed which setting when"). A setting_changes(tenant_id, key, old_value, new_value, changed_by, changed_at) table gives you that. Inside JSONB, the change is atomic to the whole object and reconstructing the history is hard.

• The field has critical DB-level constraints. CHECK (price > 0) applied to a column fails at INSERT. Applied to a field inside JSONB, it requires a trigger or app-level validation.

Cases where JSONB wins:

• Feature flags that change every sprint. A migration per flag is ridiculous.

• Per-entity customization that doesn't affect the core logic (theme, branding, layout).

• Configuration with nested structure that would be awkward to flatten (integrations.slack.channels.alerts.url).

• Schemas that evolve between versions (a job's settings that change between runs).

Connection with multitenancy and RLS

Important: this pattern assumes isolation between tenants is already solved. In the Backend Python path, that's covered in guide #13 (SQL Patterns for Production APIs), module 4: multitenancy with Row-Level Security (RLS).

If your app is multi-tenant, JSONB is not the isolation mechanism. RLS is. JSONB in tenants.settings stores a tenant's settings; RLS on other tables (posts, comments, etc.) guarantees that tenant A never reads tenant B's data.

Don't confuse them: RLS for security/isolation, JSONB for schema flexibility. They work together.

Worked example: a feature flags endpoint

We'll build a complete endpoint that serves the active flags to the frontend. The frontend asks "which flags does this tenant have?" and renders conditional UI.

# feature_flags_api.py
import asyncio
from typing import Any

from fastapi import FastAPI, HTTPException, status
from pydantic import BaseModel
from sqlalchemy import BigInteger, Index, cast, func, select, update
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Tenant(Base):
    __tablename__ = "tenants"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    name: Mapped[str] = mapped_column(unique=True)
    settings: Mapped[dict[str, Any]] = mapped_column(
        MutableDict.as_mutable(JSONB), nullable=False, default=dict
    )
    __table_args__ = (
        Index("ix_tenants_settings_gin", "settings", postgresql_using="gin"),
    )


class FeatureFlags(BaseModel):
    new_dashboard: bool = False
    ai_suggestions: bool = False
    advanced_analytics: bool = False
    beta_export: bool = False
    model_config = {"extra": "allow"}


class TenantSettings(BaseModel):
    features: FeatureFlags = FeatureFlags()
    model_config = {"extra": "allow"}


class FeatureToggleRequest(BaseModel):
    feature: str
    enabled: bool


engine = create_async_engine(
    "postgresql+asyncpg://postgres:postgres@localhost:5432/demo"
)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)


app = FastAPI()


@app.get(
    "/tenants/{tenant_id}/features",
    response_model=FeatureFlags,
)
async def get_features(tenant_id: int) -> FeatureFlags:
    """
    The endpoint the frontend calls when it loads the app.
    Returns only the features sub-object.
    """
    async with SessionLocal() as session:
        tenant = (
            await session.execute(select(Tenant).where(Tenant.id == tenant_id))
        ).scalar_one_or_none()
        if tenant is None:
            raise HTTPException(404, "tenant not found")
        validated = TenantSettings.model_validate(tenant.settings)
        return validated.features


@app.post(
    "/tenants/{tenant_id}/features:toggle",
    status_code=status.HTTP_200_OK,
)
async def toggle_feature(
    tenant_id: int, body: FeatureToggleRequest
) -> dict[str, Any]:
    """
    Admin endpoint that enables/disables a specific flag.
    Atomic update on the path.
    """
    json_value = "true" if body.enabled else "false"
    async with SessionLocal() as session:
        stmt = (
            update(Tenant)
            .where(Tenant.id == tenant_id)
            .values(
                settings=func.jsonb_set(
                    func.jsonb_set(
                        Tenant.settings, "{features}", cast("{}", JSONB), True
                    ),
                    "{features," + body.feature + "}",
                    cast(json_value, JSONB),
                    True,
                )
            )
            .returning(Tenant.settings["features"][body.feature].astext.label("value"))
        )
        result = await session.execute(stmt)
        row = result.first()
        if row is None:
            raise HTTPException(404, "tenant not found")
        await session.commit()
        return {"feature": body.feature, "enabled": row.value == "true"}


@app.get("/features/{feature}/tenants")
async def list_tenants_with_feature(feature: str) -> list[dict[str, Any]]:
    """
    Admin endpoint: 'who has this flag enabled?'.
    Takes advantage of the GIN.
    """
    async with SessionLocal() as session:
        stmt = (
            select(Tenant.id, Tenant.name)
            .where(Tenant.settings.contains({"features": {feature: True}}))
            .order_by(Tenant.id)
        )
        rows = (await session.execute(stmt)).all()
        return [{"id": r.id, "name": r.name} for r in rows]

What's going on:

• GET /tenants/{id}/features reads, validates, and returns only the features sub-object. Defaults applied by Pydantic — the frontend always receives the full schema even if the database doesn't have every key.
• POST /tenants/{id}/features:toggle is an atomic update with func.jsonb_set. It creates features if it doesn't exist. It returns the new value with RETURNING (no extra SELECT).
• GET /features/{feature}/tenants is the query the product team asks for sooner or later: "which tenants already have the flag enabled?". contains({"features": {feature: True}}) takes advantage of the GIN.

Why does this matter in real work?

1. Marketing asks for flags constantly. Without JSONB, every flag is a migration, a nullable field, defensive code, tests, and a coordinated deploy. With JSONB, adding a flag is: declare the field in FeatureFlags, push, deploy. Zero migrations.

2. Plan decisions as data. When a tenant goes from free to pro, you enable a set of flags for them. That can be a script with a single UPDATE ... WHERE plan = 'pro' SET settings = .... Without JSONB, it's five UPDATEs to five different boolean columns.

3. Customization without over-engineering. The enterprise client asks "we want our logo in the UI." You add it to branding.logo_url. No need for a tenant_branding table with two rows.

4. A defensible architectural decision. When the tech lead asks "why not a column?", your answer is the decision matrix. "Plan is a column because it's one of three fixed values. Features is in JSONB because we add one every sprint. Email is a column because it has a UNIQUE constraint." Clear reasoning, not preference.

5. It avoids the "settings table" anti-pattern with joins. Without JSONB, the flexible alternative is the tenant_settings(tenant_id, key, value) table. To read all of a tenant's settings, a JOIN. To filter by value, verbose queries. JSONB with a GIN is orders of magnitude simpler.

Traps and common mistakes

Mistake 1 (conceptual): putting in JSONB what should be a table

Symptom: you have tenant.settings.team_members = [{"email": "...", "role": "..."}, ...]. It starts hurting when: you need to list every member of every tenant, or add pending invitations, or audit who added whom.

Why it happens: "it's just a list, throw it in the JSON." You underestimate that you're going to need JOINs, aggregations, or auditing.

Smell test: do I need a JOIN or complex aggregations over this data? If yes, it's a table. Convert it: team_members(id, tenant_id, user_email, role, created_at) with its FK to the tenant.

Mistake 2 (conceptual): hardcoding JSONB keys in many places

Symptom: tenant.settings.get("features", {}).get("new_dashboard") repeated in 30 places. When you rename the flag (new_dashboard → dashboard_v2), you have to find/replace across 30 files.

Fix: reads always go through the Pydantic model. You define TenantSettings.features.new_dashboard and the code accesses it via attributes. Renaming is changing one place + mypy tells you where else it was.

Mistake 3 (practical): forgetting the GIN

Symptom: tenants.settings @> {"features": {"x": true}} queries are slow with 100k tenants.

Fix: Index("ix_tenants_settings_gin", "settings", postgresql_using="gin") on the model. Confirm with EXPLAIN ANALYZE that the plan is a Bitmap Index Scan and not a Seq Scan. If your queries are only @>, consider jsonb_path_ops to index more compactly and quickly (module 1, capsule 05).

Mistake 4 (conceptual): assuming JSONB means "schema-less"

Symptom: without Pydantic, the frontend receives settings.features.new_dashboard sometimes as true, sometimes as "true" (a string), sometimes absent. UI bugs.

Fix: JSONB in PostgreSQL is flexible at the storage level. The contract with the client is defined by Pydantic. Validating on save and on read keeps the shape consistent.

Mistake 5 (practical): a huge JSONB, high latency

Symptom: tenant.settings grows to 200 KB with history, long lists, etc. Every read of the tenant loads 200 KB.

Why it happens: data accumulated that isn't really "settings" (change logs, lists of past events).

Fix:

• Keep the JSONB small (typically <10 KB).
• Historical data goes into separate tables with an FK.
• If you need to store "the last 100 X" and it's already past 50 KB, that's a signal: it should be a table with a LIMIT.

Mistake 6 (conceptual): confusing per-tenant features with per-user features

Symptom: you want to give a beta to specific users within a tenant. You put it in tenant.settings. It ends up hard to scale (a tenant could have 10k users).

Fix: decide the feature flag's scope. If it's per-tenant (every user in the tenant sees it), tenants.settings. If it's per-user, users.settings. If it's per-user but rarely enabled, consider a user_feature_overrides(user_id, feature) table. The choice depends on cardinality and the query pattern.

Exercises

Exercise 1: classify the fields

For an Organization entity (multi-tenant SaaS), classify each field: typed column, JSONB, or related table.

a) name b) created_at c) subscription_status (one of: active, past_due, cancelled) d) feature_flags (changes every sprint) e) members (list of member users) f) branding_color (custom HEX color) g) slack_webhook_url h) monthly_event_count_by_type (aggregations for a dashboard, computed) i) notification_preferences (nested: per channel, per event type) j) sso_provider_metadata (depends on SAML/OIDC, variable schema)

Exercise 2: query tenants with a flag

Write a SQLAlchemy query that returns every tenant on the pro or enterprise plan that has the ai_suggestions: true flag. State the SQL it generates and whether it uses the GIN.

from sqlalchemy import select, or_

stmt = (
    select(Tenant.id, Tenant.name, Tenant.plan)
    .where(
        Tenant.plan.in_(["pro", "enterprise"]),
        Tenant.settings.contains({"features": {"ai_suggestions": True}}),
    )
    .order_by(Tenant.id)
)

SELECT tenants.id, tenants.name, tenants.plan
FROM tenants
WHERE tenants.plan IN ('pro', 'enterprise')
  AND tenants.settings @> '{"features": {"ai_suggestions": true}}'
ORDER BY tenants.id;

Exercise 3: bulk-enabling a flag

You need to enable beta_export: true for every enterprise tenant. Write the code that does it in a single query, without loading tenants into memory.

from sqlalchemy import cast, func, update
from sqlalchemy.dialects.postgresql import JSONB


async def enable_beta_export_for_enterprise(session) -> int:
    stmt = (
        update(Tenant)
        .where(Tenant.plan == "enterprise")
        .values(
            settings=func.jsonb_set(
                func.jsonb_set(
                    Tenant.settings,
                    "{features}",
                    cast("{}", JSONB),
                    True,
                ),
                "{features,beta_export}",
                cast("true", JSONB),
                True,
            )
        )
    )
    result = await session.execute(stmt)
    await session.commit()
    return result.rowcount

UPDATE tenants
SET settings = jsonb_set(
  jsonb_set(tenants.settings, '{features}', '{}'::jsonb, true),
  '{features,beta_export}',
  'true'::jsonb,
  true
)
WHERE tenants.plan = 'enterprise';

Exercise 4: the decision matrix, applied

Your product team asks you to store:

a) "Per-tenant webhook configuration": the webhook URL, custom HTTP headers (variable key/value), the events to notify (a subset of a fixed set of strings), max retries (int).

b) "Plan change history": when the tenant changed plan, from which plan, to which plan, who made the change, an optional reason.

c) "Dashboard display preferences": layout (grid or list), visible columns (a subset of columns), column order, dark mode.

For each one, decide: typed columns, related table, JSONB in settings, or a combination. Justify it.

CREATE TABLE plan_change_log (
  id BIGSERIAL PRIMARY KEY,
  tenant_id BIGINT REFERENCES tenants(id),
  from_plan VARCHAR NOT NULL,
  to_plan VARCHAR NOT NULL,
  changed_by BIGINT REFERENCES users(id),
  reason TEXT,
  changed_at TIMESTAMPTZ DEFAULT now()
);

Exercise 5: refactoring an anti-pattern

The following code is a common anti-pattern. Identify the problem and propose a refactor.

class Tenant(Base):
    __tablename__ = "tenants"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    name: Mapped[str]
    settings: Mapped[dict[str, Any]] = mapped_column(JSONB, default=dict)
    # settings.team_members = [
    #   {"user_id": 1, "role": "admin", "added_at": "2026-01-01"},
    #   {"user_id": 2, "role": "viewer", "added_at": "2026-02-15"},
    #   ... (can grow to hundreds)
    # ]


# Wanting to change a user's role requires:
async def change_role(session, tenant_id: int, user_id: int, new_role: str):
    tenant = (await session.execute(select(Tenant).where(Tenant.id == tenant_id))).scalar_one()
    members = tenant.settings.get("team_members", [])
    for i, m in enumerate(members):
        if m["user_id"] == user_id:
            members[i]["role"] = new_role
            break
    tenant.settings["team_members"] = members
    await session.commit()

class Tenant(Base):
    __tablename__ = "tenants"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    name: Mapped[str]
    settings: Mapped[dict[str, Any]] = mapped_column(
        MutableDict.as_mutable(JSONB), default=dict
    )  # settings stays, for feature flags and the rest


class TeamMember(Base):
    __tablename__ = "team_members"
    id: Mapped[int] = mapped_column(BigInteger, primary_key=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"))
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"))
    role: Mapped[str] = mapped_column(default="viewer")
    added_at: Mapped[datetime] = mapped_column(server_default=func.now())
    __table_args__ = (
        UniqueConstraint("tenant_id", "user_id"),
        Index("ix_team_members_tenant_id", "tenant_id"),
        Index("ix_team_members_user_id", "user_id"),
    )


# Changing a role — a single UPDATE
async def change_role(session, tenant_id: int, user_id: int, new_role: str):
    stmt = (
        update(TeamMember)
        .where(TeamMember.tenant_id == tenant_id, TeamMember.user_id == user_id)
        .values(role=new_role)
    )
    await session.execute(stmt)
    await session.commit()

Summary and next step

In this capsule you learned the first canonical JSONB pattern:

• JSONB for dynamic configuration when the fields change without warning and you accept app-level validation (Pydantic) instead of DB-level.
• Decision matrix: typed columns for stable fields with strong constraints; a key/value table for granular auditing; JSONB for flexibility without migrations.
• Pydantic sub-models group settings logically (features, branding, integrations).
• A GIN on settings is non-negotiable if you filter with @>. jsonb_path_ops if your queries are containment-only.
• Bulk updates with func.jsonb_set are the scalable way to propagate changes to many tenants.
• The anti-pattern smell test: do you need JOINs or aggregations over the data? If yes, it's a table, not JSONB.
• JSONB doesn't replace RLS for multitenancy. RLS for isolation (guide #13), JSONB for schema flexibility.

Before moving on you should be able to:

• Given a new field, decide in 30 seconds whether it goes in a typed column, JSONB, or a table.
• Implement per-tenant settings with SQLAlchemy + Pydantic + GIN.
• Write bulk updates with func.jsonb_set to propagate flags to a subset of rows.
• Recognize when JSONB is being used as a "list of sub-entities" and refactor it into a table.

Next capsule — Pattern: extensible metadata. The second canonical pattern. While "dynamic config" is per-entity and read a lot, "extensible metadata" is per-instance and stores information that changes with every customer of the product: custom SEO, per-industry fields, specific integrations. You'll go deeper on when metadata wins, how you design it for the Blog API case (where posts.metadata is the final project's component), and how to keep it from turning into a badly designed mini-database.

Resources

1. PostgreSQL Wiki — When to use JSONB — the classic community discussion on when to choose JSONB vs the alternatives.
2. LaunchDarkly — Feature flag architecture — a reference on feature flag patterns beyond PostgreSQL (useful for understanding the domain).
3. PostgreSQL 16 — JSONB Containment — the semantics of @>, which is the basis of the main query.
4. SQLAlchemy 2.0 — Bulk operations — update().values(...) for efficient bulk work.
5. Pydantic v2 — Settings management — a complementary reference on how Pydantic is used for config.
6. PostgreSQL 16 — jsonb_set reference — for atomic updates on a path.
7. Crunchy Data — Data modeling patterns with JSONB — an applied analysis of when JSONB fits.

Module 2 — Advanced PostgreSQL for Backend Guide

Next capsule: Pattern: extensible metadata — custom per-instance fields without a migration.