🏗️ Database-First CQRS

-- tb_user: Normalized base table (Trinity pattern)
CREATE TABLE tb_user (
    pk_user INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    id UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),
    email TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    role TEXT DEFAULT 'user',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

-- v_user: SQL view exposing id + data JSONB
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 (pre-composed, sub-millisecond reads)
CREATE TABLE tv_user (
    id UUID PRIMARY KEY,
    data JSONB NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Sync function: row-level update on mutation
CREATE FUNCTION fn_sync_tv_user(p_id UUID)
RETURNS VOID AS $$
BEGIN
    INSERT INTO tv_user (id, data, updated_at)
    SELECT id, data, 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: Returns mutation_response
CREATE OR REPLACE FUNCTION fn_create_user(
    p_email TEXT,
    p_name TEXT
) RETURNS app.mutation_response AS $$
DECLARE
    v_pk_user INT;
    v_user_id UUID;
BEGIN
    -- PHASE 1: INPUT VALIDATION
    IF EXISTS (SELECT 1 FROM tb_user WHERE email = p_email) THEN
        RETURN (
            'conflict:duplicate_email',
            'Email already exists',
            NULL, 'User', NULL, NULL, NULL, NULL
        )::app.mutation_response;
    END IF;

    -- PHASE 2: BUSINESS LOGIC (INSERT)
    INSERT INTO tb_user (email, name)
    VALUES (p_email, p_name)
    RETURNING pk_user, id INTO v_pk_user, 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 (
        'created',
        'User created successfully',
        v_user_id::TEXT, 'User',
        (SELECT data FROM tv_user WHERE id = v_user_id),
        ARRAY['email', 'name'],
        NULL, NULL
    )::app.mutation_response;
END;
$$ LANGUAGE plpgsql;

import fraiseql
from fraiseql.mutations import success, failure

@fraiseql.type
class User:
    id: str
    email: str
    name: str

@success
class CreateUserSuccess:
    user: User
    message: str

@failure
class CreateUserError:
    status: str   # 'conflict:duplicate_email'
    message: str

@fraiseql.mutation
class CreateUser:
    """Calls fn_create_user($email, $name)"""
    input: CreateUserInput
    success: CreateUserSuccess
    failure: CreateUserError

# FraiseQL auto-routes status codes:
# 'created' → CreateUserSuccess
# 'conflict:*' → CreateUserError
# 'validation:*' → CreateUserError