> ## 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

> Learn how to use SQL Macros to extend SnackBase's rule engine with custom database logic

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.

```python theme={null}
# 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:

| Macro                | SQL Equivalent (approx)                   | Description                        |
| :------------------- | :---------------------------------------- | :--------------------------------- |
| `@has_role("name")`  | `@request.auth.role = $1`                 | Checks if user has a specific role |
| `@has_group("name")` | `EXISTS(SELECT 1 FROM group_members ...)` | Checks group membership            |
| `@owns_record()`     | `created_by = @request.auth.id`           | Checks if user created the record  |
| `@is_creator()`      | `created_by = @request.auth.id`           | Alias 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**:

```sql theme={null}
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**:

```python theme={null}
@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**:

```json theme={null}
{
  "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.

## Related Guides

* [Permissions Concept](/permissions)
* [Writing Rules](./guides/writing-rules)