🏗️ Database-First CQRS

-- v_user: SQL view for real-time data
CREATE VIEW v_user AS
SELECT
    id,
    jsonb_build_object(
        'id', id,
        'email', email,
        'name', name,
        'role', role,
        'created_at', created_at
    ) AS data
FROM tb_user
WHERE deleted_at IS NULL;

-- tv_user: Table view with pre-composed JSONB
-- (Regular TABLE, not a view - synced via triggers)
CREATE TABLE tv_user (
    id UUID PRIMARY KEY,
    data JSONB NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Sync function updates tv_user from v_user
CREATE FUNCTION fn_sync_tv_user(p_id UUID)
RETURNS VOID AS $$
BEGIN
    INSERT INTO tv_user (id, data, updated_at)
    SELECT id, row_to_json(v_user.*)::jsonb, NOW()
    FROM v_user WHERE id = p_id
    ON CONFLICT (id) DO UPDATE SET
        data = EXCLUDED.data, updated_at = NOW();
END;
$$ LANGUAGE plpgsql;

-- fn_create_user: 5-phase mutation pattern
CREATE OR REPLACE FUNCTION fn_create_user(
    p_email TEXT,
    p_name TEXT
) RETURNS JSONB AS $$
DECLARE
    v_user_id UUID;
BEGIN
    -- PHASE 1: INPUT VALIDATION
    IF EXISTS (SELECT 1 FROM tb_user WHERE email = p_email) THEN
        RETURN jsonb_build_object(
            'status', 'error',
            'message', 'Email already exists'
        );
    END IF;

    -- PHASE 2: BUSINESS LOGIC (INSERT)
    INSERT INTO tb_user (email, data)
    VALUES (p_email, jsonb_build_object('name', p_name))
    RETURNING id INTO v_user_id;

    -- PHASE 3: CHANGE LOG
    INSERT INTO tb_entity_change_log (
        entity_type, entity_id, operation,
        after_state
    ) VALUES (
        'user', v_user_id, 'INSERT',
        (SELECT data FROM v_user WHERE id = v_user_id)
    );

    -- PHASE 4: SYNC TABLE VIEW
    PERFORM fn_sync_tv_user(v_user_id);

    -- PHASE 5: RETURN RESULT
    RETURN jsonb_build_object(
        'status', 'success',
        'message', 'User created',
        'object_data', jsonb_build_object(
            'id', v_user_id,
            'email', p_email,
            'name', p_name
        )
    );
END;
$$ LANGUAGE plpgsql;

from fraiseql import fraiseql
from fraiseql.mutations import MutationResult

# Query: Maps to tv_user (fast) or v_user (real-time)
@fraiseql.type
class User:
    id: uuid.UUID
    email: str
    name: str
    role: str

@fraiseql.query
async def users(info) -> list[User]:
    """Executes: SELECT data FROM tv_user"""
    return await info.context.db.fetch_all()

# Mutation: Calls fn_create_user function
@fraiseql.mutation
async def create_user(
    info,
    email: str,
    name: str
) -> MutationResult:
    """Executes: SELECT fn_create_user($1, $2)"""
    result = await info.context.db.call_function(
        "fn_create_user", email, name
    )
    return MutationResult(**result)

# Function handles:
# - Validation (email uniqueness)
# - Insert to tb_user
# - Change log to tb_entity_change_log
# - Sync to tv_user
# - Structured response