Federation Example - E-Commerce Platform

This guide walks through building a complete e-commerce backend using FraiseQL’s federation capabilities to coordinate across three separate databases.

Architecture Overview

Each database owns a distinct domain — federation composes them into a unified API.

Database Setup

Primary DB

-- Customers
CREATE TABLE tb_customer (
    pk_customer 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,
    email       TEXT UNIQUE NOT NULL,
    phone       TEXT,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Orders
CREATE TABLE tb_order (
    pk_order    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id          UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier  TEXT NOT NULL UNIQUE,
    fk_customer BIGINT NOT NULL REFERENCES tb_customer(pk_customer),
    total       DECIMAL(10, 2) NOT NULL,
    status      TEXT NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Order Items
CREATE TABLE tb_order_item (
    pk_order_item BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id            UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier    TEXT NOT NULL UNIQUE,
    fk_order      BIGINT NOT NULL REFERENCES tb_order(pk_order),
    product_id    UUID NOT NULL,  -- FK to inventory DB
    quantity      INT NOT NULL,
    unit_price    DECIMAL(10, 2) NOT NULL,
    created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- Shipping Info
CREATE TABLE tb_shipping (
    pk_shipping     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id              UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier      TEXT NOT NULL UNIQUE,
    fk_order        BIGINT NOT NULL UNIQUE REFERENCES tb_order(pk_order),
    address         TEXT NOT NULL,
    city            TEXT NOT NULL,
    postal_code     TEXT NOT NULL,
    country         TEXT NOT NULL,
    tracking_number TEXT,
    status          TEXT NOT NULL DEFAULT 'pending',
    estimated_delivery DATE,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- Views for FraiseQL (always with JSONB data column)
CREATE VIEW v_order AS
SELECT
    o.id,
    jsonb_build_object(
        'id',          o.id::text,
        'identifier',  o.identifier,
        'total',       o.total,
        'status',      o.status,
        'created_at',  o.created_at,
        'updated_at',  o.updated_at
    ) AS data
FROM tb_order o;

CREATE VIEW v_customer AS
SELECT
    c.id,
    jsonb_build_object(
        'id',         c.id::text,
        'identifier', c.identifier,
        'name',       c.name,
        'email',      c.email,
        'phone',      c.phone,
        'created_at', c.created_at
    ) AS data
FROM tb_customer c;

Inventory DB

-- PostgreSQL (separate instance)

CREATE TABLE tb_product (
    pk_product  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id          UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier  TEXT NOT NULL UNIQUE,  -- SKU slug
    sku         TEXT UNIQUE NOT NULL,
    name        TEXT NOT NULL,
    description TEXT,
    price       DECIMAL(10, 2) NOT NULL,
    weight_grams INT,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tb_inventory (
    pk_inventory BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id           UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier   TEXT NOT NULL UNIQUE,
    fk_product   BIGINT NOT NULL REFERENCES tb_product(pk_product),
    warehouse    TEXT NOT NULL,
    quantity     INT NOT NULL DEFAULT 0,
    reserved     INT NOT NULL DEFAULT 0,
    available    INT GENERATED ALWAYS AS (quantity - reserved) STORED,
    updated_at   TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(fk_product, warehouse)
);

CREATE TABLE tb_reservation (
    pk_reservation BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id             UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier     TEXT NOT NULL UNIQUE,
    order_id       UUID NOT NULL,  -- FK to primary DB
    fk_product     BIGINT NOT NULL REFERENCES tb_product(pk_product),
    quantity       INT NOT NULL,
    status         TEXT NOT NULL DEFAULT 'reserved',
    created_at     TIMESTAMPTZ DEFAULT NOW(),
    expires_at     TIMESTAMPTZ,
    released_at    TIMESTAMPTZ
);

CREATE VIEW v_product AS
SELECT
    p.id,
    jsonb_build_object(
        'id',    p.id::text,
        'sku',   p.sku,
        'name',  p.name,
        'price', p.price
    ) AS data
FROM tb_product p;

CREATE VIEW v_inventory_status AS
SELECT
    p.id,
    jsonb_build_object(
        'id',         p.id::text,
        'sku',        p.sku,
        'name',       p.name,
        'warehouse',  i.warehouse,
        'quantity',   i.quantity,
        'reserved',   i.reserved,
        'available',  i.available
    ) AS data
FROM tb_product p
LEFT JOIN tb_inventory i ON p.pk_product = i.fk_product;

Payments DB

-- PostgreSQL (separate instance)

CREATE TABLE tb_payment_method (
    pk_payment_method BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id                UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier        TEXT NOT NULL UNIQUE,
    customer_id       UUID NOT NULL,  -- FK to primary DB
    type              TEXT NOT NULL,  -- 'card', 'bank_transfer', 'wallet'
    token             TEXT NOT NULL,  -- Tokenized payment info
    is_default        BOOLEAN DEFAULT FALSE,
    created_at        TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tb_transaction (
    pk_transaction         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id                     UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier             TEXT NOT NULL UNIQUE,
    order_id               UUID NOT NULL UNIQUE,  -- FK to primary DB
    customer_id            UUID NOT NULL,
    amount                 DECIMAL(10, 2) NOT NULL,
    status                 TEXT NOT NULL DEFAULT 'pending',
    fk_payment_method      BIGINT REFERENCES tb_payment_method(pk_payment_method),
    gateway                TEXT,  -- 'stripe', 'paypal', 'square'
    gateway_transaction_id TEXT,
    created_at             TIMESTAMPTZ DEFAULT NOW(),
    processed_at           TIMESTAMPTZ,
    failed_at              TIMESTAMPTZ
);

CREATE TABLE tb_refund (
    pk_refund      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id             UUID DEFAULT gen_random_uuid() NOT NULL UNIQUE,
    identifier     TEXT NOT NULL UNIQUE,
    fk_transaction BIGINT NOT NULL REFERENCES tb_transaction(pk_transaction),
    order_id       UUID NOT NULL,
    amount         DECIMAL(10, 2) NOT NULL,
    reason         TEXT,
    status         TEXT NOT NULL DEFAULT 'pending',
    created_at     TIMESTAMPTZ DEFAULT NOW(),
    processed_at   TIMESTAMPTZ
);

CREATE VIEW v_transaction AS
SELECT
    t.id,
    jsonb_build_object(
        'id',           t.id::text,
        'order_id',     t.order_id::text,
        'customer_id',  t.customer_id::text,
        'amount',       t.amount,
        'status',       t.status,
        'created_at',   t.created_at
    ) AS data
FROM tb_transaction t;

FraiseQL Schema with Federation

import fraiseql
from fraiseql.scalars import ID
from decimal import Decimal
from datetime import datetime


# ==================== PRIMARY DATABASE ====================

@fraiseql.type
class Customer:
    id: ID
    name: str
    email: str
    phone: str | None
    created_at: datetime
    payment_methods: list['PaymentMethod']  # Federated from payments DB


@fraiseql.type
class OrderItem:
    id: ID
    product_id: ID
    quantity: int
    unit_price: Decimal
    product: 'Product'  # Federated from inventory DB


@fraiseql.type
class Shipping:
    id: ID
    order_id: ID
    address: str
    city: str
    postal_code: str
    country: str
    tracking_number: str | None
    status: str
    estimated_delivery: str | None


@fraiseql.type
class Order:
    id: ID
    total: Decimal
    status: str
    created_at: datetime
    updated_at: datetime
    items: list[OrderItem]       # Local relation
    shipping: Shipping           # Local relation
    customer: Customer           # Federated from primary DB
    payment: 'Transaction'       # Federated from payments DB
    reservations: list['Reservation']  # Federated from inventory DB


# ==================== INVENTORY DATABASE ====================

@fraiseql.type
class Product:
    id: ID
    sku: str
    name: str
    price: Decimal


@fraiseql.type
class InventoryStatus:
    product_id: ID
    sku: str
    name: str
    warehouse: str
    quantity: int
    reserved: int
    available: int


@fraiseql.type
class Reservation:
    id: ID
    order_id: ID
    product_id: ID
    quantity: int
    status: str
    created_at: datetime
    expires_at: datetime | None
    released_at: datetime | None
    product: Product  # Federated from inventory DB (same DB, different view)


# ==================== PAYMENTS DATABASE ====================

@fraiseql.type
class PaymentMethod:
    id: ID
    customer_id: ID
    type: str
    is_default: bool


@fraiseql.type
class Transaction:
    id: ID
    order_id: ID
    customer_id: ID
    amount: Decimal
    status: str
    gateway: str | None
    created_at: datetime
    processed_at: datetime | None


@fraiseql.type
class Refund:
    id: ID
    transaction_id: ID
    order_id: ID
    amount: Decimal
    reason: str | None
    status: str
    created_at: datetime


# ==================== QUERIES ====================

@fraiseql.query
def order(id: ID) -> Order:
    """Get order by ID with all federated relations."""
    return fraiseql.config(sql_source="v_order")


@fraiseql.query
def orders(customer_id: ID | None = None, limit: int = 20) -> list[Order]:
    """Get orders, optionally filtered by customer."""
    return fraiseql.config(sql_source="v_order")


@fraiseql.query
def customer(id: ID) -> Customer:
    """Get customer with payment methods."""
    return fraiseql.config(sql_source="v_customer")


@fraiseql.query
def inventory_status(product_id: ID | None = None) -> list[InventoryStatus]:
    """Get current inventory status across warehouses."""
    return fraiseql.config(sql_source="v_inventory_status")


# ==================== MUTATIONS ====================

@fraiseql.mutation(sql_source="fn_create_order", operation="CREATE")
def create_order(
    customer_id: ID,
    shipping_address: str,
    shipping_city: str,
    shipping_postal: str,
    shipping_country: str,
    payment_method_id: ID,
) -> Order:
    """Create order. fn_create_order handles inventory reservation and payment via pg_notify."""
    pass

Note

Federation configuration — which databases exist and how fields resolve across them — belongs in fraiseql.toml under [federation], not in Python type annotations. The Python layer declares field shapes only.

Creating an Order: Transactional Mutation

The create_order mutation coordinates writes across all three databases. In FraiseQL this is handled by a PostgreSQL function — the Python decorator is a compile-time declaration only. Cross-database coordination uses pg_notify events forwarded to NATS by FraiseQL observers.

Tip

PostgreSQL transactions give you atomic multi-table writes within the primary database with automatic rollback. Cross-database steps (inventory reservation, payment) are triggered via pg_notify and processed by separate services subscribed to NATS.

db/schema/03_functions/fn_create_order.sql

CREATE OR REPLACE FUNCTION fn_create_order(
    p_customer_id        UUID,
    p_shipping_address   TEXT,
    p_shipping_city      TEXT,
    p_shipping_postal    TEXT,
    p_shipping_country   TEXT,
    p_payment_method_id  UUID
)
RETURNS mutation_response
LANGUAGE plpgsql AS $$
DECLARE
    v_pk_order   BIGINT;
    v_id         UUID;
    v_pk_cust    BIGINT;
    v_result     mutation_response;
BEGIN
    -- Resolve customer PK
    SELECT pk_customer INTO v_pk_cust
      FROM tb_customer WHERE id = p_customer_id;

    IF v_pk_cust IS NULL THEN
        v_result.status  := 'failed:not_found';
        v_result.message := 'Customer not found';
        RETURN v_result;
    END IF;

    -- Create order record
    INSERT INTO tb_order (fk_customer, total, status, identifier)
    VALUES (v_pk_cust, 0, 'pending', 'ORD-' || gen_random_uuid()::text)
    RETURNING pk_order, id INTO v_pk_order, v_id;

    -- Create shipping record
    INSERT INTO tb_shipping (fk_order, address, city, postal_code, country, status, identifier)
    VALUES (v_pk_order,
            p_shipping_address, p_shipping_city,
            p_shipping_postal, p_shipping_country, 'pending',
            'SHIP-' || gen_random_uuid()::text);

    -- Notify inventory and payment services via pg_notify.
    -- FraiseQL observers forward these to NATS topics.
    PERFORM pg_notify(
        'order_created',
        jsonb_build_object(
            'order_id',          v_id,
            'customer_id',       p_customer_id,
            'payment_method_id', p_payment_method_id
        )::text
    );

    v_result.status      := 'success';
    v_result.entity_id   := v_id;
    v_result.entity_type := 'Order';
    RETURN v_result;
END;
$$;

Configure FraiseQL to forward pg_notify channels to NATS:

fraiseql.toml

[federation]
enabled = true

# Per-table observer entries
[[observers]]
table   = "tb_order"
event   = "INSERT"
subject = "order_created"

[[observers]]
table   = "tb_order"
event   = "UPDATE"
subject = "order_cancelled"

# Global observer backend settings
[observers]
backend  = "nats"
nats_url = "${NATS_URL}"

Inventory reservation and payment processing are handled by separate services that subscribe to these NATS subjects. Each service updates its own database and emits completion/failure events, allowing independent rollback without a centralised saga framework.

Query Examples

Full Order Query

query GetOrder {
    order(id: "550e8400-e29b-41d4-a716-446655440000") {
        id
        status
        total

        customer {
            name
            email
            paymentMethods {
                type
                isDefault
            }
        }

        items {
            quantity
            unitPrice
            product {
                sku
                name
                price
            }
        }

        reservations {
            status
            product {
                name
            }
        }

        payment {
            amount
            status
            processedAt
        }
    }
}

Inventory Check

query CheckInventory {
    inventoryStatus(limit: 100) {
        productId
        sku
        name
        warehouse
        quantity
        reserved
        available
    }
}

Performance Considerations

Tip

FraiseQL automatically batches federated lookups. When fetching 100 orders, it issues one query to the primary DB and one batch query to the inventory DB — not 100 individual product lookups.

Connection Pooling

Configure separate connection pools for each database:

[databases.primary]
url = "${PRIMARY_DB}"
pool_min = 2
pool_max = 50
connect_timeout_ms = 30000

[databases.inventory]
url = "${INVENTORY_DB}"
pool_min = 2
pool_max = 30
connect_timeout_ms = 30000

[databases.payments]
url = "${PAYMENTS_DB}"
pool_min = 2
pool_max = 20
connect_timeout_ms = 30000

Timeout Configuration

[federation]
default_timeout = 5000  # 5 seconds
batch_size = 100

[federation.retry]
max_attempts = 2
backoff = "exponential"

Federation Reference

Complete federation API documentation.

NATS Integration

Event coordination across services.

Error Handling

Saga failure recovery patterns.

Performance Guide

Query optimization for federated APIs.