Entity Logic

📄 Overview

A Calculation Function is a server-side script written in PL/pgSQL that performs business-specific recalculation logic for a specific entity.

🖋 Function Signature

        
CREATE FUNCTION calculation_YourEntityName(
    id VARCHAR(255),
    eventType VARCHAR(255),
    userId VARCHAR(255),
    args TEXT
)
RETURNS void
LANGUAGE plpgsql
            
        

🔑 Function Parameters

⚙ Behavior

This function performs an update on the entity's data and can include business rules, calculations based on related data, and conditional logic. It does not return a value and is meant to produce side effects (like updating totals, setting status, etc).

👤 User Context & Role Access

The system provides simplified helper functions for working with user roles and permissions inside Functions. You no longer need to manually query system tables — instead, you can use concise role-checking helpers.

Use the has_role(user_id, role_id_or_code) function to check if the current user has a specific role. This allows your business logic to remain clean and readable:

        
IF has_role(userId, 'HR营销') THEN
    -- apply role-specific logic
END IF;
            
        

Similarly, inside SQL queries, you can write:

        
SELECT * FROM "Order"
WHERE has_role(userId, 'Admin') OR "AssignedToUserId" = userId;
            
        

These helper functions internally rely on the following system tables:

These helpers allow business logic to be adapted dynamically depending on the roles assigned to the executing user — without repeating boilerplate SQL joins.

📍 Geospatial Location Information

The Address table stores structured and geospatial location information. Entities can reference an address via a foreign key (e.g., Location_Fk). The Location column allows usage of spatial queries, such as finding nearby points using geospatial functions.

📚 Examples

            
CREATE FUNCTION approve_leave_request(
    id VARCHAR(255),
    eventType VARCHAR(255),
    userId VARCHAR(255),
    args TEXT
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    -- Optional: only run logic when triggered manually
    IF eventType = 'manual' THEN
        -- Check if user has the HR_Manager role
        IF has_role(userId, 'HR_Manager') THEN
            PERFORM log_debug(
                p_message := 'User authorized as HR_Manager. Approving leave request.',
                p_category := 'LeaveApproval',
                p_source := 'LeaveRequest',
                p_additional_data := json_build_object(
                    'UserId', userId,
                    'LeaveRequestId', id,
                    'EventType', eventType
                )::text
            );

            UPDATE "LeaveRequest"
            SET "Status" = 'Approved'
            WHERE "Id" = id;

            PERFORM log_debug(
                p_message := 'Leave request approved successfully.',
                p_category := 'LeaveApproval',
                p_source := 'LeaveRequest',
                p_additional_data := json_build_object(
                    'UserId', userId,
                    'LeaveRequestId', id
                )::text
            );
        ELSE
            PERFORM log_warning(
                p_message := 'User tried to approve leave without HR_Manager role.',
                p_category := 'LeaveApproval',
                p_source := 'LeaveRequest',
                p_additional_data := json_build_object(
                    'UserId', userId,
                    'LeaveRequestId', id,
                    'EventType', eventType
                )::text
            );

            RAISE EXCEPTION 'You do not have permission to approve leave requests';
        END IF;
    ELSE
        -- Optional: log ignored event types
        PERFORM log_verbose(
            p_message := 'approve_leave_request ignored: unsupported eventType',
            p_category := 'LeaveApproval',
            p_source := 'LeaveRequest',
            p_additional_data := json_build_object(
                'UserId', userId,
                'LeaveRequestId', id,
                'EventType', eventType
            )::text
        );
    END IF;
END;
$$;
                
            

            
CREATE FUNCTION calculation_order(
    id VARCHAR(255),
    eventType VARCHAR(255),
    userId VARCHAR(255),
    args TEXT
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    -- Log: starting calculation
    PERFORM log_debug(
        p_message := 'Starting order total recalculation.',
        p_category := 'OrderCalculation',
        p_source := 'Order',
        p_additional_data := json_build_object(
            'UserId', userId,
            'OrderId', id,
            'EventType', eventType
        )::text
    );

    -- Update total amount based on related items
    UPDATE "Order"
    SET "TotalAmount" = (
        SELECT COALESCE(SUM(oi."Price" * oi."Quantity"), 0)
        FROM "OrderItem" oi
        WHERE oi."OrderId" = id
    )
    WHERE "Id" = id;

    -- Log: finished calculation
    PERFORM log_debug(
        p_message := 'Order total recalculated successfully.',
        p_category := 'OrderCalculation',
        p_source := 'Order',
        p_additional_data := json_build_object(
            'UserId', userId,
            'OrderId', id
        )::text
    );
END;
$$;
                
            

            
CREATE FUNCTION calculation_invoice(
    id VARCHAR(255),
    eventType VARCHAR(255),
    userId VARCHAR(255),
    args TEXT
)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    invoiceStatus TEXT;
BEGIN
    -- Get current status of the invoice
    SELECT "Status" INTO invoiceStatus
    FROM "Invoice"
    WHERE "Id" = id;

    -- If status is Approved, check that at least one item exists
    IF invoiceStatus = 'Approved' THEN
        IF NOT EXISTS (
            SELECT 1 FROM "InvoiceItem" WHERE "InvoiceId" = id
        ) THEN
            -- Log error
            PERFORM log_error(
                p_message := 'Cannot approve invoice without at least one item.',
                p_category := 'InvoiceValidation',
                p_source := 'Invoice',
                p_additional_data := json_build_object(
                    'UserId', userId,
                    'InvoiceId', id,
                    'EventType', eventType
                )::text
            );

            -- Raise validation error
            RAISE EXCEPTION 'Cannot approve invoice without at least one item.';
        END IF;
    END IF;
END;
$$;
                
            

            
CREATE FUNCTION calculation_invoice_item(
    id VARCHAR(255),
    eventType VARCHAR(255),
    userId VARCHAR(255),
    args TEXT
)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    invoiceId VARCHAR(255);
BEGIN
    -- Get parent invoice ID from the item
    SELECT "InvoiceId" INTO invoiceId
    FROM "InvoiceItem"
    WHERE "Id" = id;

    IF invoiceId IS NULL THEN
        -- Log and raise error if item is not linked to an invoice
        PERFORM log_error(
            p_message := 'InvoiceItem must be linked to an Invoice.',
            p_category := 'InvoiceItemValidation',
            p_source := 'InvoiceItem',
            p_additional_data := json_build_object(
                'UserId', userId,
                'InvoiceItemId', id,
                'EventType', eventType
            )::text
        );
        RAISE EXCEPTION 'InvoiceItem must be linked to an Invoice.';
    END IF;

    -- Log that invoice recalculation is being triggered
    PERFORM log_debug(
        p_message := 'Triggering invoice total recalculation after invoice item change.',
        p_category := 'InvoiceCascade',
        p_source := 'InvoiceItem',
        p_additional_data := json_build_object(
            'UserId', userId,
            'InvoiceId', invoiceId,
            'InvoiceItemId', id,
            'EventType', eventType
        )::text
    );

    -- Trigger recalculation of the parent invoice
    PERFORM calculation_invoice(invoiceId, eventType, userId);
END;
$$;
                
            

            
CREATE FUNCTION calculation_entity(
    id VARCHAR(255),
    eventType VARCHAR(255),
    userId VARCHAR(255),
    args TEXT
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    -- Check if the entity is within 1 km of the reference warehouse
    IF EXISTS (
        SELECT 1
        FROM "Warehouse" w
        JOIN "Address" a ON a."Id" = w."AddressId"
        JOIN "Entity" e ON e."Location_Fk" = a."Id"
        WHERE e."Id" = id
          AND ST_DWithin(
              a."Location",
              ST_SetSRID(ST_MakePoint(21.0122, 52.2297), 4326),
              1000  -- meters
          )
    ) THEN
        -- Log: nearby warehouse detected
        PERFORM log_debug(
            p_message := 'Entity is within 1 km of warehouse. Marking as Local priority.',
            p_category := 'GeoLogic',
            p_source := 'Entity',
            p_additional_data := json_build_object(
                'UserId', userId,
                'EntityId', id,
                'EventType', eventType
            )::text
        );

        -- Apply priority change
        UPDATE "Entity"
        SET "Priority" = 'Local'
        WHERE "Id" = id;

        -- Log: priority updated
        PERFORM log_debug(
            p_message := 'Entity priority set to Local based on proximity to warehouse.',
            p_category := 'GeoLogic',
            p_source := 'Entity',
            p_additional_data := json_build_object(
                'UserId', userId,
                'EntityId', id
            )::text
        );
    END IF;
END;
$$;
                
            

📖 Further Reading

For more detailed information about the PL/pgSQL language, including all syntax and features like control structures, error handling, and more, refer to the official PostgreSQL documentation: https://www.postgresql.org/docs/current/plpgsql.html

📄 Overview

A Filter Function is a server-side PL/pgSQL function designed to return a filtered subset of records from a specific entity table. These functions do not modify data; they only filter it based on provided criteria. Filter functions are written in PostgreSQL and are used for two primary purposes:

• User Interface Filtering: Users can select a filter function (or even multiple functions) to apply to the data they view, tailoring the results to their needs.
• Security Filtering: Filters can be automatically applied based on user roles. For example, users in Role A might always have Filter F1 applied, while users in another role have a different filter.

🖋 Function Signature

        
CREATE FUNCTION filter_YourEntityName(
    userId VARCHAR(255),
    args TEXT
)
RETURNS SETOF "YourEntityName"
LANGUAGE plpgsql
STABLE;
            
        

Replace YourEntityName with the name of the entity table being filtered.

🔑 Parameters

⚙ Behavior

Filter functions must:

• Be deterministic and safe for use in SELECT statements and JOINs.
• Avoid modifying data or causing side effects—only filter and return data.
• Support flexible filtering logic based on userId (e.g., role-based or ownership-based) and args (e.g., field-based, date-based, or geo-based filters).

👤 User Context & Role Access

Filter functions can leverage the userId parameter to enforce role-based filtering. For example, a helper function like has_role(userId, role_id_or_code) can determine the user's role and adjust the filter accordingly.

        
IF has_role(userId, 'Admin') THEN
    -- No filtering for admins
    RETURN QUERY SELECT t.* FROM "YourEntityName" AS t;
ELSE
    -- Filter for regular users
    RETURN QUERY SELECT t.* FROM "YourEntityName" AS t WHERE t."Owner_Fk" = userId;
END IF;
            
        

📚 Examples

            
CREATE FUNCTION filter_ActivityName(
    userId VARCHAR(255),
    args TEXT
)
RETURNS SETOF "ActivityName" AS $$
BEGIN
    RETURN QUERY
    SELECT t.*
    FROM "ActivityName" AS t
    WHERE t."Owner_Fk" = userId;
END;
$$ LANGUAGE plpgsql STABLE;
                
            

            
CREATE FUNCTION filter_ActivityName(
    userId VARCHAR(255),
    args TEXT
)
RETURNS SETOF "ActivityName" AS $$
DECLARE
    statusFilter TEXT;
BEGIN
    statusFilter := (args::jsonb ->> 'status');
    RETURN QUERY
    SELECT t.*
    FROM "ActivityName" AS t
    WHERE (statusFilter IS NULL OR t."Status" = statusFilter);
END;
$$ LANGUAGE plpgsql STABLE;
                
            

            
CREATE FUNCTION filter_ActivityName(
    userId VARCHAR(255),
    args TEXT
)
RETURNS SETOF "ActivityName" AS $$
BEGIN
    IF has_role(userId, 'Admin') THEN
        RETURN QUERY
        SELECT t.*
        FROM "ActivityName" AS t;
    ELSE
        RETURN QUERY
        SELECT t.*
        FROM "ActivityName" AS t
        WHERE t."Owner_Fk" = userId
        AND t."CreatedDate" >= NOW() - INTERVAL '30 days';
    END IF;
END;
$$ LANGUAGE plpgsql STABLE;
                
            

🔄 Integration with Predicates

Filter Functions can leverage existing Entity Predicates to create more powerful and maintainable filtering logic. This allows you to reuse the same logical conditions across different contexts:

        
CREATE FUNCTION filter_Task(
    userId VARCHAR(255),
    args TEXT
)
RETURNS SETOF "Task" AS $$
BEGIN
    RETURN QUERY
    SELECT t.*
    FROM "Task" AS t
    WHERE 
        -- Use a predicate function directly in the filter
        is_task_accessible_to_user(t."Id", userId) 
        
        -- Additional filtering based on args
        AND (
            args IS NULL
            OR (args::jsonb ->> 'showUrgentOnly')::BOOLEAN IS NOT TRUE
            OR is_task_urgent(t."Id", userId)
        );
END;
$$ LANGUAGE plpgsql STABLE;
            
        

In this example, the filter reuses two predicates:

• is_task_accessible_to_user — Handles basic access control filtering
• is_task_urgent — Conditionally applied only when the 'showUrgentOnly' parameter is true

This approach brings several benefits:

• DRY principle — Logic defined once and reused in multiple places
• Consistency — The same condition is applied in UI highlighting, filters, and security checks
• Maintainability — Changes to conditions need to be made only in one place
• Readability — Filter logic becomes more declarative and easier to understand

Complex Filter Using Multiple Predicates

        
CREATE FUNCTION filter_Order(
    userId VARCHAR(255),
    args TEXT
)
RETURNS SETOF "Order" AS $$
DECLARE
    v_status TEXT;
    v_date_range INT;
BEGIN
    -- Extract filter parameters
    v_status := args::jsonb ->> 'status';
    v_date_range := COALESCE((args::jsonb ->> 'dateRange')::INT, 30);
    
    RETURN QUERY
    SELECT o.*
    FROM "Order" AS o
    WHERE 
        -- Basic access control using a predicate
        can_view_order(o."Id", userId)
        
        -- Apply status filter if specified
        AND (v_status IS NULL OR o."Status" = v_status)
        
        -- Apply date range filter
        AND o."OrderDate" >= CURRENT_DATE - (v_date_range || ' days')::INTERVAL
        
        -- Apply additional business rule predicates based on args
        AND (
            (args::jsonb ->> 'showEditable')::BOOLEAN IS NOT TRUE
            OR is_order_editable(o."Id", userId)
        )
        AND (
            (args::jsonb ->> 'showProblematic')::BOOLEAN IS NOT TRUE
            OR has_order_discrepancies(o."Id", userId)
        );
END;
$$ LANGUAGE plpgsql STABLE;
                
            

This more complex example demonstrates how predicates can be combined with traditional filtering conditions to create powerful filters that incorporate both data filtering and business rules.

📖 Further Reading

For more details on PL/pgSQL, see the official PostgreSQL documentation: https://www.postgresql.org/docs/current/plpgsql.html

📄 Overview

An Entity Predicate Function is a server-side PL/pgSQL function that evaluates a logical condition against a specific entity object and returns a boolean result. Predicates serve as the building blocks for dynamic security rules, conditional UI rendering, and business logic validation.

Unlike Filter Functions that return a subset of records, Predicate Functions answer simple yes/no questions about a single entity instance, making them ideal for fine-grained permission checks and conditional logic throughout the application.

A key feature of Predicate Functions is that they are context-dependent — they evaluate conditions based on the current user context, the object's state, and potentially other environmental factors. This means the same predicate may return different results for different users or at different times, enabling truly dynamic behavior.

🖋 Function Signature

    
CREATE FUNCTION [descriptive_predicate_name](
    objectId VARCHAR(255),
    userId VARCHAR(255),
    args TEXT DEFAULT NULL
)
RETURNS BOOLEAN
LANGUAGE plpgsql
STABLE;
            
        

Use a descriptive name that clearly indicates what condition the predicate checks, such as is_order_editable, can_approve_document, or has_pending_items.

🔑 Parameters

⚙ Behavior

Predicate functions must:

• Be deterministic and safe for use in WHERE clauses, CHECK constraints, and security policies.
• Return TRUE if the condition is satisfied, FALSE otherwise.
• Avoid modifying data or causing side effects—focus solely on evaluating conditions.
• Handle edge cases gracefully, such as when the object doesn't exist (typically returning FALSE for security)
• Be optimized for performance, as they may be called frequently in security checks and UI rendering.

🔄 Usage Contexts

Predicate functions can be used in various contexts throughout the application:

👤 Integration with Security System

Predicate functions work seamlessly with the role-based security system, adding fine-grained contextual rules on top of basic role permissions:

    
-- Example of using a predicate in security check
CREATE FUNCTION can_edit_order(orderId VARCHAR(255), userId VARCHAR(255))
RETURNS BOOLEAN AS $$
BEGIN
    -- Basic role check
    IF NOT has_role(userId, 'OrderEditor') THEN
        RETURN FALSE;
    END IF;

    -- Additional predicate check for object-specific conditions
    RETURN predicate_Order(orderId, userId);
END;
$$ LANGUAGE plpgsql STABLE;
            
        

This approach combines coarse-grained role-based permissions with fine-grained contextual rules for maximum flexibility.

📚 Examples

        
CREATE FUNCTION is_order_editable(
    objectId VARCHAR(255),
    userId VARCHAR(255),
    args TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $
DECLARE
    v_status TEXT;
BEGIN
    -- Get the current status of the order
    SELECT "Status" INTO v_status
    FROM "Order"
    WHERE "Id" = objectId;

    -- Return TRUE if order is still editable (not completed or canceled)
    RETURN v_status NOT IN ('Completed', 'Shipped', 'Canceled');
END;
$ LANGUAGE plpgsql STABLE;
                
            

        
CREATE FUNCTION is_document_recently_created_by_owner(
    objectId VARCHAR(255),
    userId VARCHAR(255),
    args TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $
DECLARE
    v_owner_id VARCHAR(255);
    v_created_at TIMESTAMP;
BEGIN
    -- Get document details
    SELECT "OwnerId", "CreatedAt" INTO v_owner_id, v_created_at
    FROM "Document"
    WHERE "Id" = objectId;

    -- Document doesn't exist
    IF v_owner_id IS NULL THEN
        RETURN FALSE;
    END IF;

    -- Allow if user is owner AND document was created within the last 24 hours
    RETURN v_owner_id = userId AND v_created_at > NOW() - INTERVAL '24 hours';
END;
$ LANGUAGE plpgsql STABLE;
                
            

        
CREATE FUNCTION can_access_invoice(
    objectId VARCHAR(255),
    userId VARCHAR(255),
    args TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $
DECLARE
    v_record RECORD;
    v_check_type TEXT;
    v_is_admin BOOLEAN;
BEGIN
    -- Extract check type from args
    v_check_type := CASE 
        WHEN args IS NULL THEN 'default'
        ELSE (args::jsonb ->> 'checkType')::TEXT 
    END;

    -- Get invoice details
    SELECT * INTO v_record
    FROM "Invoice"
    WHERE "Id" = objectId;

    -- Invoice doesn't exist
    IF v_record IS NULL THEN
        RETURN FALSE;
    END IF;

    -- Check if user is admin
    v_is_admin := has_role(userId, 'admin');

    -- Different conditions based on check type
    CASE v_check_type
        WHEN 'canView' THEN
            RETURN v_is_admin OR v_record."AssignedTo" = userId;

        WHEN 'canApprove' THEN
            RETURN v_is_admin AND v_record."TotalAmount" <= 10000;

        WHEN 'canCancel' THEN
            RETURN (v_is_admin OR has_role(userId, 'manager')) AND 
                  v_record."Status" = 'Draft' AND
                  v_record."CreatedAt" > NOW() - INTERVAL '30 days';

        ELSE -- default
            RETURN v_record."Status" = 'Draft';
    END CASE;
END;
$ LANGUAGE plpgsql STABLE;
                
            

        
CREATE FUNCTION is_task_urgent(
    objectId VARCHAR(255),
    userId VARCHAR(255),
    args TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $
DECLARE
    v_due_date DATE;
    v_priority TEXT;
    v_status TEXT;
BEGIN
    -- Get task details
    SELECT "DueDate", "Priority", "Status" INTO v_due_date, v_priority, v_status
    FROM "Task"
    WHERE "Id" = objectId;

    -- Task doesn't exist
    IF v_due_date IS NULL THEN
        RETURN FALSE;
    END IF;

    -- Return TRUE if task should be highlighted:
    -- 1. It's overdue but not completed, or
    -- 2. It's high priority and due today, or
    -- 3. It's assigned to current user and due within 2 days
    RETURN (v_due_date < CURRENT_DATE AND v_status != 'Completed') OR
           (v_priority = 'High' AND v_due_date = CURRENT_DATE) OR
           (v_status = 'Assigned' AND v_due_date <= CURRENT_DATE + 2);
END;
$ LANGUAGE plpgsql STABLE;
                
            

🔄 Client-Side Integration

Predicate functions are automatically exposed to the client through the OData API. Each object returned from the API includes a system collection $Predicates, which contains the results of all available predicates for that object:

    
// Example response from API
{
  "Id": "ORD-2023-00123",
  "CustomerName": "Acme Corp",
  "OrderDate": "2023-04-15",
  "Status": "Processing",
  // ... other regular fields

  // System collection of predicate results
  "$Predicates": {
    "is_editable": true,
    "is_overdue": false,
    "can_be_canceled": true,
    "requires_approval": false
  }
}
            
        

This makes it easy to use predicates in client-side UI components:

    
// In component logic
const isEditable = order.$Predicates.is_editable;
const isOverdue = order.$Predicates.is_overdue;
const rowClass = isOverdue ? "bg-red-100" : "";

// In a component template
<button v-if="isEditable" @click="editOrder">Edit Order</button>
<tr :class="rowClass">...</tr>
            
        

This pattern keeps business logic centralized in the database while allowing the UI to react to the same conditions that govern security and validation.

💡 Best Practices

• Keep it Simple: Focus each predicate on a single concern or condition. Complex logic can be composed by combining multiple predicates.
• Handle Non-existent Records: Always account for the case where the object may not exist, typically returning FALSE for security-related checks.
• Consider Performance: Optimize queries and avoid unnecessary joins, as predicates may be called frequently.
• Use Args for Flexibility: Leverage the args parameter to make predicates versatile and reusable across different contexts.
• Add Logging for Complex Cases: For debugging complex conditions, consider adding logging to help understand why a predicate evaluated to TRUE or FALSE.

📖 Further Reading

For more details on PL/pgSQL, see the official PostgreSQL documentation: https://www.postgresql.org/docs/current/plpgsql.html

📄 Overview

Entity Triggers are events defined on an entity that invoke Calculation Functions in response to specific actions, such as API requests (e.g., POST, PUT, DELETE). These triggers are non-recursive, meaning that changes made by functions within PostgreSQL do not re-trigger the same events, preventing unintended loops and ensuring predictable system behavior.

🔧 What Are Entity Triggers?

In this context, Entity Triggers are events tied to an entity's lifecycle that execute Calculation Functions only. They are invoked when a user makes an API request, such as POST, PUT, or DELETE. The key feature of these triggers is their non-recursive nature—changes made by functions within PostgreSQL, triggered by the system, will not cause these triggers to fire again. This distinction is crucial for avoiding infinite loops and maintaining system stability.

📋 When to Use Triggers

Triggers are useful in the following scenarios:

• Calculating Aggregates or Computed Properties: Automatically recalculate totals or derived fields when an entity is modified, such as updating an order total after an item change.
• Validation: Enforce business rules by returning validation errors, such as checking for uniqueness (e.g., no duplicate entries) or ensuring field completeness based on an entity’s status.
• Cross-Entity Operations: Invoke functions on related entities, e.g., an "After Update" trigger on an OrderItem entity calling a `recalculate_order` function with the `OrderId` parameter to update the associated Order entity.

⚙ Available Trigger Types

📚 Example: Order and OrderItem Entities

The following example demonstrates how triggers can be configured for the "Order" and "OrderItem" entities to maintain data integrity and enforce business rules:

Entities

• Order: Represents a customer order with fields like `Id`, `TotalAmount`, and `Status`.
• OrderItem: Represents items within an order with fields like `Id`, `OrderId`, `ProductId`, `Quantity`, and `Price`.

Trigger Configuration for "OrderItem"

        
Type: After Insert
Function: recalculate_order(OrderId)
Args: (none)

Type: After Update
Function: recalculate_order(OrderId)
Args: (none)

Type: After Delete
Function: recalculate_order(OrderId)
Args: (none)
            
        

These triggers ensure that any change to an OrderItem (insertion, update, or deletion) recalculates the `TotalAmount` of the associated Order.

Trigger Configuration for "Order"

        
Type: Before Update
Function: validate_order_status(Id)
Args: (none)

Type: After Update
Function: update_inventory(Id)
Args: (none)
            
        

The "Before Update" trigger enforces business rules, such as preventing invalid status changes (e.g., from "Shipped" to "Pending"). The "After Update" trigger updates inventory levels when the order status changes to "Shipped".

How It Works

• Adding a new OrderItem triggers `recalculate_order` to update the Order's total.
• Updating an OrderItem's quantity or price recalculates the Order's total.
• Deleting an OrderItem adjusts the Order's total accordingly.
• Before updating an Order, the system validates the status change.
• After updating an Order to "Shipped", the system updates the product inventory.

📖 Further Reading

For more details on PL/pgSQL and function creation, refer to the official PostgreSQL documentation: https://www.postgresql.org/docs/current/plpgsql.html