Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.snackbase.dev/llms.txt

Use this file to discover all available pages before exploring further.

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.