Skip to main content
SQL Macros allow you to extend the rule expression language by injecting custom SQL queries directly into the permission engine. They are perfect for complex multi-table checks or business logic that requires joining data.

​
Overview

A SQL Macro is a predefined SQL fragment that is expanded during rule compilation. It behaves like a boolean function when called in a rule. 
# Rule using a macro
@is_project_member(project_id)

​
Why use SQL Macros?

  • Database performance: Checks are executed as part of the primary query.
  • Complexity management: Move complex logic out of rules and into reusable SQL.
  • Transactional safety: Macros execute within the same transaction as the request.
  • Context awareness: Macros have access to the currently authenticated user.

​
Core Concepts

​
1. Naming

Macro names must start with @ when called in a rule, but are defined without it. Example: Defined as owns_record, called as @owns_record().

​
2. Parameters

Macros can accept parameters. Use $1, $2, etc., in your SQL query to reference them. Parameters are passed from the rule expression.

​
3. Context Variables

Macros can also reference context variables directly in the SQL:
  • @request.auth.id: Current user ID
  • @request.auth.account_id: Current account ID

​
Built-in Macros

SnackBase comes with several high-performance macros pre-installed:
MacroSQL Equivalent (approx)Description
@has_role("name")@request.auth.role = $1Checks if user has a specific role
@has_group("name")EXISTS(SELECT 1 FROM group_members ...)Checks group membership
@owns_record()created_by = @request.auth.idChecks if user created the record
@is_creator()created_by = @request.auth.idAlias for @owns_record

​
Creating Custom Macros

​
Example: @is_project_member(project_id)

  1. Name: is_project_member
  2. Description: Checks if a user is a member of a project.
  3. SQL Query:
SELECT count(*) > 0
FROM project_members
WHERE project_id = $1
  AND user_id = @request.auth.id
  AND account_id = @request.auth.account_id
Usage in Rule: 
@is_project_member(project_id)

​
Security Constraints

To ensure system stability, SQL macros are subject to the following rules:
  • SELECT only: Only SELECT statements are allowed. INSERT, UPDATE, DELETE will be rejected.
  • Timeout: Macros have a 5-second execution timeout.
  • Parameter Binding: All parameters are bound safely to prevent SQL injection.
  • Read-Only: Macros execute in a read-only transaction context.

​
API Management

​
List Macros

GET /api/v1/macros

​
Create Macro

POST /api/v1/macros Payload: 
{
  "name": "can_edit_document",
  "description": "Checks if user has editor rights on a document",
  "sql_query": "SELECT count(*) > 0 FROM doc_access WHERE doc_id = $1 AND user_id = @request.auth.id AND role = 'editor'"
}

​
Test Macro

POST /api/v1/macros/{id}/test Allows testing a macro with specific parameter values before using it in a rule.

​
Best Practices

  1. Return Booleans: Custom SQL macros should always return a result that can be interpreted as a boolean (usually count(*) > 0 or EXISTS(...)).
  2. Index Columns: Ensure columns used in WHERE clauses (like user_id, project_id) are indexed.
  3. Account Isolation: Always include account_id = @request.auth.account_id in your queries to maintain proper isolation.
  4. Descriptive Names: Use names like is_subscriber, can_approve_invoice to make rules self-documenting.